/database-migration

name: database-migration
description: Create database migration with schema changes and rollback. Auto-invoke when user says "create migration", "add table", "modify schema", or "change database".
allowed-tools: Read, Write, Edit, Grep, Glob, Bash
version: 2.0.0

Database Migration Generator

Generate database migrations with rollback capability for schema changes, with built-in ToM verification for safe database operations.

> **Implementation note**: This skill uses direct `Write()` calls with inline templates (see Steps 3–4 and "Schema Change Templates" section below). Unlike `frontend-component` and `backend-endpoint`, there are no Python helper functions — the skill is prose-driven by design, since migration generation is highly schema-specific and benefits from inline framework templates. Future work (tracked for v6.6.0) may extract a timestamp generator and per-framework templates.

When to Invoke

Auto-invoke when user mentions:

• "Create migration"
• "Add table"
• "Modify schema"
• "Change database"
• "Database migration for [change]"
• "Add column to [table]"
• "Rename [table/column]"

What This Does

1. Detects migration framework (Knex, Prisma, TypeORM, raw SQL) 2. Gathers migration requirements 3. **Verifies understanding before generating** (ToM checkpoint - critical for DB changes) 4. Generates migration file with timestamp 5. Creates schema change (up migration) 6. Creates rollback (down migration) 7. Validates migration safety 8. Shows migration summary

Execution Steps

Step 0: Check Existing Patterns (Phase 0)

Before detecting the framework, query the knowledge graph for what we already know about database work in this project. For migrations — the highest-stakes execution path — Phase 0 is especially valuable because past pitfalls (failed NOT NULL adds, bad rollback assumptions, naming collisions) often repeat.

PLUGIN_DIR="${CLAUDE_PLUGIN_ROOT:-$HOME/.claude/plugins/cache/navigator-marketplace/navigator}"
[ -d "$PLUGIN_DIR" ] || PLUGIN_DIR="$HOME/.claude/plugins/marketplaces/navigator-marketplace"
python3 "$PLUGIN_DIR/skills/nav-graph/functions/graph_manager.py" \
  --action query --concept database \
  --graph-path .agent/knowledge/graph.json 2>/dev/null | head -40

Also check `migration`, `schema`, and `performance` (for index decisions).

If memories surface (`PATTERN`, `PITFALL`, `DECISION` entries), read the full memory files for any relevant ones:

ls .agent/knowledge/memories/{patterns,pitfalls,decisions}/ 2>/dev/null

**What to do with what you find**:

• **Patterns**: apply them (e.g. "we always use UUIDs over auto-increment IDs")
• **Pitfalls**: avoid them — these are the most important for migrations (record what you avoided in `pitfalls_avoided` in Step 7)
• **Decisions**: respect them (e.g. "we chose JSONB over separate tables for tags")

Skip this step only if the knowledge graph is disabled in `.agent/.nav-config.json`.

Step 1: Detect Migration Framework

**Check project for migration tool**:

# Check for Knex
if [ -f "knexfile.js" ] || [ -f "knexfile.ts" ] || grep -q '"knex"' package.json 2>/dev/null; then
  echo "Knex detected"
fi

# Check for Prisma
if [ -f "prisma/schema.prisma" ]; then
  echo "Prisma detected"
fi

# Check for TypeORM
if [ -f "ormconfig.json" ] || [ -f "ormconfig.ts" ] || grep -q '"typeorm"' package.json 2>/dev/null; then
  echo "TypeORM detected"
fi

# Check for Drizzle
if grep -q '"drizzle-orm"' package.json 2>/dev/null; then
  echo "Drizzle detected"
fi

**Framework detection result**:

Detected: {FRAMEWORK}
Migration directory: {MIGRATION_PATH}
Naming convention: {CONVENTION}

**If no framework detected**:

⚠️  No migration framework detected

Options:
1. Generate raw SQL migrations
2. Set up Knex (recommended for flexibility)
3. Set up Prisma (recommended for type safety)

Your choice [1-3]:

Step 2: Gather Migration Requirements

**Ask user for migration details**:

Migration name: [e.g., add_user_verification_columns]
Change type:
  - create_table (new table)
  - add_column (add to existing table)
  - modify_column (change existing column)
  - drop_column (remove column)
  - rename (rename table or column)
  - add_index (create index)
  - add_constraint (foreign key, unique, etc.)

Target table: [e.g., users]

Schema details: [describe the changes]

Step 2.5: Verify Understanding (ToM Checkpoint - ALWAYS for DB) [EXECUTE]

**CRITICAL**: This step MUST ALWAYS be executed for database migrations. No exceptions.

**Database migrations are high-stakes - ALWAYS verify before generating**.

**Display verification**:

I understood you want:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Migration: {MIGRATION_NAME}
Framework: {FRAMEWORK} (detected)
Type: {CHANGE_TYPE}
Target: {TABLE_NAME}
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Schema Changes (UP):
{SCHEMA_CHANGE_PREVIEW}

Rollback (DOWN):
{ROLLBACK_PREVIEW}

⚠️  Database migrations affect production data
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Assumptions I'm making:
- Column types match existing conventions
- Indexes will use default naming
- No data migration needed (schema only)

Proceed with generation? [Y/n]

**Never skip verification for database migrations** - they can cause data loss.

Step 3: Generate Migration File

**Based on detected framework**:

Knex Migration

# Generate filename
TIMESTAMP=$(date +%Y%m%d%H%M%S)
FILENAME="${TIMESTAMP}_${MIGRATION_NAME}.ts"

# Create migration file
Write(
  file_path: "migrations/${FILENAME}",
  content: [knex migration template]
)

**Knex template**:

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  ${UP_MIGRATION}
}

export async function down(knex: Knex): Promise<void> {
  ${DOWN_MIGRATION}
}

Prisma Migration

# Prisma uses schema.prisma + migrate commands
# Update schema.prisma with new models/fields
# Then run: npx prisma migrate dev -