database-migration

What this skill does

# 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.

```bash
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:
```bash
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**:

```bash
# 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
```bash
# 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**:
```typescript
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
```bash
# Prisma uses schema.prisma + migrate commands
# Update schema.prisma with new models/fields
# Then run: npx prisma migrate dev --name ${MIGRATION_NAME}
```

**Show Prisma workflow**:
```
Prisma detected - updating schema.prisma

1. I'll update prisma/schema.prisma with:
   ${SCHEMA_CHANGES}

2. Run migration:
   npx prisma migrate dev --name ${MIGRATION_NAME}

3. Generate client:
   npx prisma generate
```

#### TypeORM Migration
```bash
TIMESTAMP=$(date +%Y%m%d%H%M%S)
FILENAME="${TIMESTAMP}-${MIGRATION_NAME}.ts"
```

**TypeORM template**:
```typescript
import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class ${MIGRATION_CLASS_NAME}${TIMESTAMP} implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    ${UP_MIGRATION}
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    ${DOWN_MIGRATION}
  }
}
```

### Step 4: Generate Rollback Logic

**Ensure every UP has a corresponding DOWN**:

| UP Operation | DOWN Operation |
|--------------|----------------|
| CREATE TABLE | DROP TABLE |
| ADD COLUMN | DROP COLUMN |
| ADD INDEX | DROP INDEX |
| ADD CONSTRAINT | DROP CONSTRAINT |
| RENAME | RENAME (reverse) |
| ALTER COLUMN | ALTER COLUMN (reverse) |

**Warning for destructive operations**:
```
⚠️  DROP COLUMN in DOWN migration will lose data!

Column: {COLUMN_NAME}
Type: {COLUMN_TYPE}

If this column has data, consider:
1. Backup data before migration
2. Add data migration step
3. Keep column but deprecate

Understood? [Y/n]
```

### Step 5: Validate Migration Safety

**Check for common issues**:

```
Migration Safety Check:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
✅ Rollback defined (can undo changes)
✅ No DROP TABLE without backup warning
✅ No ALTER on large tables without consideration
⚠️  Adding NOT NULL column - needs DEFAULT value
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
```

**Safety warnings to check**:
- Adding NOT NULL without DEFAULT (will fail on existing rows)
- Dropping columns with data
- Renaming columns (may break application code)
- Adding UNIQUE constraint (may fail if duplicates exist)
- Large table alterations (may lock table)

### Step 6: Show Migration Summary

**Display completed migration**:

```
✅ Migration Created: {MIGRATION_NAME}

File: {MIGRATION_PATH}/{FILENAME}
Framework: {FRAMEWORK}
Timestamp: {TIMESTAMP}

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━