--- description: "Transform a schema change request into a complete migration plan: forward SQL, rollback SQL, index strategy, and a production deployment safety checklist." alwaysApply: true --- # 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 ``` **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 ``` 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 ## Playground

Input: Schema change request

Current: users table has a single
  `name` VARCHAR(255) column.

Target: split into `first_name`
  and `last_name`, keep `name`
  as a generated column for
  backwards compatibility.

Output: Migration plan

-- Step 1: Add new columns (safe)
ALTER TABLE users
  ADD COLUMN first_name TEXT,
  ADD COLUMN last_name  TEXT;

-- Step 2: Backfill (batched)
UPDATE users SET
  first_name = split_part(name,' ',1),
  last_name  = split_part(name,' ',2)
WHERE first_name IS NULL LIMIT 10000;

-- Step 3: Generated column default
ALTER TABLE users ALTER COLUMN name
  SET DEFAULT first_name||' '||last_name;

⚠ Do NOT drop `name` yet.
  Deprecate after 2 deploy cycles.