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

/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

Step 2: Generate Forward Migration

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

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

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:

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

Playground

<span style='color:#8b949e'> name VARCHAR(255) column.</span>

<span style='color:#8b949e'>Target: split into first_name</span> <span style='color:#8b949e'> and last_name, keep name</span> <span style='color:#8b949e'> as a generated column for</span> <span style='color:#8b949e'> backwards compatibility.</span></pre></div><div class='panel'><div class='label'>Output: Migration plan</div><pre><span style='color:#58a6ff'>-- Step 1: Add new columns (safe)</span> <span style='color:#3fb950'>ALTER TABLE users</span> <span style='color:#3fb950'> ADD COLUMN first_name TEXT,</span> <span style='color:#3fb950'> ADD COLUMN last_name TEXT;</span>

<span style='color:#58a6ff'>-- Step 2: Backfill (batched)</span> <span style='color:#3fb950'>UPDATE users SET</span> <span style='color:#3fb950'> first_name = split_part(name,' ',1),</span> <span style='color:#3fb950'> last_name = split_part(name,' ',2)</span> <span style='color:#3fb950'>WHERE first_name IS NULL LIMIT 10000;</span>

<span style='color:#58a6ff'>-- Step 3: Generated column default</span> <span style='color:#3fb950'>ALTER TABLE users ALTER COLUMN name</span> <span style='color:#3fb950'> SET DEFAULT first_name||' '||last_name;</span>

<span style='color:#e3b341'>⚠ Do NOT drop name yet.</span> <span style='color:#e3b341'> Deprecate after 2 deploy cycles.</span></pre></div></div></body></html>