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.

python3 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 --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

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

TypeORM template:

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:

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}

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

Schema Changes:
┌─────────────────────────────────────────────┐
│ UP (Apply)                                  │
├─────────────────────────────────────────────┤
│ {UP_MIGRATION_SUMMARY}                      │
└─────────────────────────────────────────────┘

┌─────────────────────────────────────────────┐
│ DOWN (Rollback)                             │
├─────────────────────────────────────────────┤
│ {DOWN_MIGRATION_SUMMARY}                    │
└─────────────────────────────────────────────┘

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

Run Migration:
{RUN_COMMAND}

Test Rollback:
{ROLLBACK_COMMAND}

Next Steps:
1. Review migration file
2. Test on development database
3. Run migration: {RUN_COMMAND}
4. Verify schema changes
5. Commit migration file

Step 7: Emit Execution Summary (Graph Ingestion)

After Step 6, emit an execution_summary JSON block. Database migrations are the highest-stakes execution path — recording the patterns and decisions here is the most valuable. This block flows into the knowledge graph via execution_to_graph.py.

Output the block verbatim (replace placeholders with actual values):

{
  "execution_summary": {
    "skill": "database-migration",
    "task": "{MIGRATION_NAME}",
    "files_created": ["{migration file path}"],
    "files_modified": [],
    "tests_added": [],
    "stack_detected": "{e.g. knex+postgres or prisma+postgres}",
    "patterns_followed": [
      {"summary": "{convention applied — e.g. timestamp-prefixed filenames, UUID primary keys}", "concepts": ["database"], "confidence": 0.8}
    ],
    "decisions_made": [
      {"summary": "{non-obvious choice — e.g. used VARCHAR(255) over TEXT for indexability}", "concepts": ["database"], "confidence": 0.75, "evidence": "{path:line}"}
    ],
    "pitfalls_avoided": [
      {"summary": "{e.g. added DEFAULT to NOT NULL column to avoid failing on existing rows}", "concepts": ["database"], "confidence": 0.9}
    ],
    "assumptions_made": ["{e.g. PostgreSQL 14+ — used gen_random_uuid()}"]
  }
}

Ingestion (run from project root):

echo '<execution_summary JSON>' | python3 skills/nav-graph/functions/execution_to_graph.py -

Migration-specific rules:

• Always record pitfalls — DB changes are easy to get wrong, future agents need the warning.
• Stack detection should include both the migration framework AND the target DB (e.g. knex+postgres).
• Decisions about column types, indexes, and constraints are valuable to capture even when they feel obvious.

---

Schema Change Templates

Create Table (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('${TABLE_NAME}', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    ${COLUMN_DEFINITIONS}
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('${TABLE_NAME}');
}

Add Column (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.${COLUMN_TYPE}('${COLUMN_NAME}')${MODIFIERS};
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.dropColumn('${COLUMN_NAME}');
  });
}

Add Index (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.index(['${COLUMN_NAME}'], '${INDEX_NAME}');
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.dropIndex(['${COLUMN_NAME}'], '${INDEX_NAME}');
  });
}

---

Framework-Specific Commands

Knex

# Run pending migrations
npx knex migrate:latest

# Rollback last batch
npx knex migrate:rollback

# Run specific migration
npx knex migrate:up ${MIGRATION_NAME}

# Check status
npx knex migrate:status

Prisma

# Create and apply migration
npx prisma migrate dev --name ${MIGRATION_NAME}

# Apply in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Check status
npx prisma migrate status

TypeORM

# Run pending migrations
npx typeorm migration:run

# Revert last migration
npx typeorm migration:revert

# Generate migration from entities
npx typeorm migration:generate -n ${MIGRATION_NAME}

# Show migrations
npx typeorm migration:show

---

Error Handling

Framework not detected:

⚠️  No migration framework detected in project

Please set up a migration framework first:
- Knex: npm install knex && npx knex init
- Prisma: npm install prisma && npx prisma init
- TypeORM: npm install typeorm && create ormconfig

Migration name conflict:

⚠️  Migration with similar name already exists

Existing: 20251209_add_users_table.ts
Requested: add_users_table

Options:
1. Use different name
2. Add version suffix (add_users_table_v2)
3. Check if existing migration is sufficient

Your choice [1-3]:

Validation failure:

❌ Migration validation failed

Issues:
- Column 'status' is NOT NULL but has no DEFAULT
- Table 'orders' doesn't exist (referenced in foreign key)

Fix these issues before generating migration.

---

Success Criteria

Migration is successful when:

• Migration file generated with unique timestamp
• Framework conventions followed
• UP migration creates/modifies schema correctly
• DOWN migration rolls back changes completely
• ToM verification passed (user confirmed understanding)
• Safety checks passed
• Commands shown for running migration

---

Best Practices

Naming Conventions

• create_users_table - for new tables
• add_email_to_users - for adding columns
• add_index_on_users_email - for indexes
• change_status_type_in_orders - for modifications

Safety

• Always test on development database first
• Backup production before running migrations
• Use transactions where supported
• Consider data migration for non-null columns

Code Review

• Review generated SQL before running
• Check rollback logic is complete
• Verify no data loss in DOWN migration
• Test full rollback cycle

---

Database migrations affect production data - ToM verification is mandatory for this skill 🗄️