{"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":"35c3caade85fda12ce2c64592534178d6fd77fdfd3e3c7c84e1b83f6a19af9b3","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 \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\n## Playground\n\n
Database Migration PlannerExample · SkillSlap
Input: Schema change request
Current: users table has a single\n  `name` VARCHAR(255) column.\n\nTarget: split into `first_name`\n  and `last_name`, keep `name`\n  as a generated column for\n  backwards compatibility.
Output: Migration plan
-- Step 1: Add new columns (safe)\nALTER TABLE users\n  ADD COLUMN first_name TEXT,\n  ADD COLUMN last_name  TEXT;\n\n-- Step 2: Backfill (batched)\nUPDATE users SET\n  first_name = split_part(name,' ',1),\n  last_name  = split_part(name,' ',2)\nWHERE first_name IS NULL LIMIT 10000;\n\n-- Step 3: Generated column default\nALTER TABLE users ALTER COLUMN name\n  SET DEFAULT first_name||' '||last_name;\n\n⚠ Do NOT drop `name` yet.\n  Deprecate after 2 deploy cycles.
"}}