{"manifest":{"name":"Database Migration Planner","version":"1.0.0","description":"Transform a schema change request into a complete migration plan: forward SQL, rollback SQL, index strategy, and a production deployment safety checklist.","tags":["database","sql","migration","postgresql","devops","workflow"],"standard":"agentskills.io","standard_version":"1.0","content_checksum":"674551c657b3f87cb49a74cf0e030377cee0426ac1ad80db25abaece8628caa3","bundle_checksum":null,"metadata":{},"files":[]},"files":{"SKILL.md":"# Database Migration Planner\n\n> **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.\n\n---\n\n## Invocation\n\n```\n/migration <change-description>\n```\n\n**Examples:**\n- `/migration Add soft-delete to the users table`\n- `/migration Add a posts table with author foreign key and full-text search`\n- `/migration Rename the email column to email_address across 3 tables`\n\n---\n\n## Process\n\n### Step 1: Classify the Change\n\n| Change Type | Lock Risk | Duration |\n|-------------|-----------|----------|\n| Add nullable column | Low | Instant |\n| Add NOT NULL column with DEFAULT | Medium | Instant |\n| Add NOT NULL column without DEFAULT | **HIGH** | Full table rewrite |\n| Drop column | Low | Instant |\n| Create index | **HIGH** | Proportional to table size |\n| Create index CONCURRENTLY | None | Proportional (but non-blocking) |\n| Add foreign key | Medium | Table size |\n| Rename column | Low | Instant |\n| Rename table | Low | Instant |\n| Backfill data (UPDATE all rows) | **HIGH** | Rows × write speed |\n\n---\n\n### Step 2: Generate Forward Migration\n\n```sql\n-- Migration: [Description]\n-- Date: [today]\n-- Author: [git config user.name]\n-- Lock risk: [LOW/MEDIUM/HIGH]\n-- Estimated duration: [instant / seconds / minutes]\n\nBEGIN;\n\n-- Step 1: Schema changes\n[DDL statements with comments]\n\n-- Step 2: Data backfill (if needed, in batches for large tables)\n[DML statements]\n\nCOMMIT;\n\n-- Note: Run AFTER the transaction if using PostgreSQL\n-- CREATE INDEX CONCURRENTLY cannot run inside a transaction\n[CONCURRENTLY indexes, if any]\n```\n\n---\n\n### Step 3: Generate Rollback SQL\n\n```sql\n-- ROLLBACK: [Description]\n-- ⚠️  WARNING: Data added since the migration will be lost if columns are dropped\n\nBEGIN;\n\n[Exact reverse of forward migration — drop added columns, restore renamed columns, etc.]\n\nCOMMIT;\n```\n\n---\n\n### Step 4: Production Safety Checklist\n\n```\nPre-migration:\n□ Migration tested on staging with production-scale data?\n□ Estimated duration acceptable (rule: < 30s for unscheduled, < 2min for maintenance window)?\n□ Table lock risk analyzed?\n□ Rollback tested on staging?\n□ App code deployed first (if adding nullable columns app code won't use yet)?\n\nDuring migration:\n□ Monitoring dashboard open?\n□ Alert thresholds temporarily adjusted?\n□ On-call engineer aware?\n\nPost-migration:\n□ Query plan verified for new indexes?\n□ Application behavior verified in staging?\n□ Rollback plan ready if production issues emerge?\n```\n\n---\n\n### Step 5: For Large Table Operations\n\nIf the affected table has > 1M rows, provide a **batched backfill script**:\n\n```sql\n-- Batch backfill: update 1000 rows at a time\nDO $backfill$\nDECLARE\n  batch_size INT := 1000;\n  updated INT;\nBEGIN\n  LOOP\n    UPDATE [table]\n    SET [column] = [value]\n    WHERE [condition] AND [column] IS NULL\n    LIMIT batch_size;\n\n    GET DIAGNOSTICS updated = ROW_COUNT;\n    EXIT WHEN updated = 0;\n    PERFORM pg_sleep(0.1); -- breathe between batches\n  END LOOP;\nEND;\n$backfill$;\n```\n\n---\n\n## Rules\n\n- Always wrap DDL in a transaction **unless** using `CREATE INDEX CONCURRENTLY`\n- Flag `HIGH` lock risk changes with an explicit `⚠️ WARNING` comment in the SQL\n- Never drop a column in the same deploy that removes app code references — two-step deploy\n- Adding `NOT NULL` without a `DEFAULT` is a full table rewrite — flag this as requiring maintenance window\n- Backfill updates on tables > 100K rows must be batched — never `UPDATE all rows` in one statement\n"}}