# Migration Planner

> Plans database schema migrations safely — produces up/down SQL, identifies risky operations (locks, downtime, data loss), and suggests zero-downtime patterns (expand/contract, backfills, dual-writes). Use this skill when the user asks to "add a column", "rename a table", "change a constraint", needs a migration script, or asks how to migrate without downtime.

- Skill: `kakarot-oncloud/migration-planner` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add kakarot-oncloud/migration-planner`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kakarot-oncloud/migration-planner/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: kakarot-oncloud (https://skillmd.com/u/kakarot-oncloud)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/kakarot-oncloud/migration-planner

---


# Migration Planner

You plan database schema changes that ship safely to production.

## Default dialect

PostgreSQL unless specified. Call out dialect-specific risk (MySQL `ALTER TABLE` locking behavior differs significantly).

## Output template

```markdown
## Goal
<one sentence: what changes>

## Risk assessment
- Lock duration: <none / brief / blocking — and on what tables>
- Downtime required: <yes / no>
- Reversible: <yes / no, until X>
- Data loss risk: <none / partial / full>

## Up migration
```sql
<DDL/DML in order>
```

## Down migration
```sql
<reverse>
```

## Rollout plan
1. <step>
2. <step>

## Verification
- <query to confirm new state>
- <query to confirm old data preserved>
```

## Risk matrix

| Operation | Risk | Mitigation |
|---|---|---|
| `ADD COLUMN` (nullable, no default) | Low | Direct |
| `ADD COLUMN NOT NULL DEFAULT` | High on large tables (rewrites table in older Postgres / MySQL) | Add nullable → backfill → set NOT NULL |
| `DROP COLUMN` | Medium (irreversible, breaks readers still selecting it) | Stop reading first, deploy, then drop |
| `RENAME COLUMN` | High (atomic break) | Add new → dual-write → backfill → switch reads → drop old |
| `CHANGE TYPE` | High (rewrites + may fail on existing data) | New column + backfill + switch |
| `ADD INDEX` | High lock on MySQL, low on Postgres with `CONCURRENTLY` | Use `CREATE INDEX CONCURRENTLY` (Postgres) |
| `ADD FOREIGN KEY` | High (validates all rows) | Add `NOT VALID` → `VALIDATE CONSTRAINT` separately |
| `DROP TABLE` | Catastrophic if wrong | Rename first, drop later |

## Zero-downtime patterns

### Expand / Contract (the safe rename)
1. **Expand**: add the new column/table alongside the old.
2. **Dual-write**: app writes both old and new.
3. **Backfill**: copy historical data in batches.
4. **Switch reads**: app reads from new.
5. **Stop dual-write**: app writes only to new.
6. **Contract**: drop old column/table.

Each step deploys independently. Stop at any point and roll back without data loss.

### Backfill in batches
Never `UPDATE huge_table SET ...` in one statement — locks and bloats. Use:
```sql
-- Postgres
DO $$
DECLARE
  batch_size INT := 1000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE huge_table
    SET new_col = old_col
    WHERE id IN (
      SELECT id FROM huge_table WHERE new_col IS NULL LIMIT batch_size
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;
```

## Rules

1. **Always provide a down migration**, even if it's a no-op (note why).
2. **Flag irreversible operations** in bold red prose.
3. **Never combine destructive and additive** in one migration — split them.
4. **Wrap in a transaction** (`BEGIN; ... COMMIT;`) when the dialect supports DDL transactions (Postgres yes, MySQL mostly no).
5. **Recommend testing on a prod-sized snapshot** before any operation touching > 1M rows.
6. **For app-coupled changes**, specify the deploy order: code-then-migration or migration-then-code.