Database Migration Planner

Purpose: Transform a database schema change request into a complete, production-safe migration plan. Generates forward SQL, rollback SQL, index strategy, and a pre-deployment checklist. Prevents the "oh no, ALTER TABLE took a lock for 20 minutes" incident.

Invocation

code

/migration <change-description>

Examples:

/migration Add soft-delete to the users table
/migration Add a posts table with author foreign key and full-text search
/migration Rename the email column to email_address across 3 tables

Process

Step 1: Classify the Change

Change Type	Lock Risk	Duration
Add nullable column	Low	Instant
Add NOT NULL column with DEFAULT	Medium	Instant
Add NOT NULL column without DEFAULT	HIGH	Full table rewrite
Drop column	Low	Instant
Create index	HIGH	Proportional to table size
Create index CONCURRENTLY	None	Proportional (but non-blocking)
Add foreign key	Medium	Table size
Rename column	Low	Instant
Rename table	Low	Instant
Backfill data (UPDATE all rows)	HIGH	Rows × write speed

Step 2: Generate Forward Migration

sql

-- Migration: [Description]
-- Date: [today]
-- Author: [git config user.name]
-- Lock risk: [LOW/MEDIUM/HIGH]
-- Estimated duration: [instant / seconds / minutes]

BEGIN;

-- Step 1: Schema changes
[DDL statements with comments]

-- Step 2: Data backfill (if needed, in batches for large tables)
[DML statements]

COMMIT;

-- Note: Run AFTER the transaction if using PostgreSQL
-- CREATE INDEX CONCURRENTLY cannot run inside a transaction
[CONCURRENTLY indexes, if any]

Step 3: Generate Rollback SQL

sql

-- ROLLBACK: [Description]
-- ⚠️  WARNING: Data added since the migration will be lost if columns are dropped

BEGIN;

[Exact reverse of forward migration — drop added columns, restore renamed columns, etc.]

COMMIT;

Step 4: Production Safety Checklist

code

Pre-migration:
□ Migration tested on staging with production-scale data?
□ Estimated duration acceptable (rule: < 30s for unscheduled, < 2min for maintenance window)?
□ Table lock risk analyzed?
□ Rollback tested on staging?
□ App code deployed first (if adding nullable columns app code won't use yet)?

During migration:
□ Monitoring dashboard open?
□ Alert thresholds temporarily adjusted?
□ On-call engineer aware?

Post-migration:
□ Query plan verified for new indexes?
□ Application behavior verified in staging?
□ Rollback plan ready if production issues emerge?

Step 5: For Large Table Operations

If the affected table has > 1M rows, provide a batched backfill script:

sql

-- Batch backfill: update 1000 rows at a time
DO $backfill$
DECLARE
  batch_size INT := 1000;
  updated INT;
BEGIN
  LOOP
    UPDATE [table]
    SET [column] = [value]
    WHERE [condition] AND [column] IS NULL
    LIMIT batch_size;

    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;
    PERFORM pg_sleep(0.1); -- breathe between batches
  END LOOP;
END;
$backfill$;

Rules

Always wrap DDL in a transaction unless using CREATE INDEX CONCURRENTLY
Flag HIGH lock risk changes with an explicit ⚠️ WARNING comment in the SQL
Never drop a column in the same deploy that removes app code references — two-step deploy
Adding NOT NULL without a DEFAULT is a full table rewrite — flag this as requiring maintenance window
Backfill updates on tables > 100K rows must be batched — never UPDATE all rows in one statement

Database Migration Planner

Database Migration Planner

Invocation

Process

Step 1: Classify the Change

Step 2: Generate Forward Migration

Step 3: Generate Rollback SQL

Step 4: Production Safety Checklist

Step 5: For Large Table Operations

Rules

Created by

Info

Embed

Export