SKILL.md
Safe Schema Migrations
Change the schema without downtime, locks on hot tables, or irreversible steps. Treat the migration and the code deploy as coordinated steps, not one big switch. Pair with financial-invariants for data correctness and scale-readiness-review for sizing impact.
Workflow
1. Classify the change
- Additive (nullable column, new table/index): lowest risk.
- Backfill: risk is runtime and lock duration.
- Constraint/type change (NOT NULL, FK, widen/narrow): risk is table locks and rewrites.
- Rename/move: never in place under a running app — expand/contract instead.
- Destructive (drop): only after no code references it for a full deploy cycle.
2. Expand / contract
Each phase ships independently and is reversible; the app is never broken between steps:
- Expand: add the new structure without removing the old.
- Migrate code: write both, read old (or read new behind a flag).
- Backfill: populate existing rows in batches.
- Switch reads: read new; keep dual-write briefly.
- Contract: stop writing old; add constraints; drop old structure last.
3. Keep DDL online
- Create indexes concurrently/online where supported.
- NOT NULL via checked constraint validated separately, or default-then-enforce.
- FKs as NOT VALID, then VALIDATE separately.
- Keep DDL transactions short; never hold locks during heavy work.
4. Backfill safely
- Bounded, ordered batches (by id/date) with a small sleep between; resumable and idempotent.
- Run as a job/script, never inside the schema migration transaction.
- Monitor lock contention, replication lag, batch duration; throttle if they grow.
5. Rollback plan
- Every step has a down path or documented forward-fix.
- Destructive steps live in a separate, later migration so rolling back the latest deploy loses no data.
6. Verify
- Test on production-scale row counts; measure lock time and duration.
- Run the test suite against the migrated schema at each phase; apply down-then-up where a down path exists.
- Financial tables: reconcile row counts and key sums before and after backfill.
Guardrails
- Never rename or drop a column in the same deploy that stops using it.
- Never backfill a large table in one transaction.
- Never add NOT NULL or a validating FK that full-table-locks a hot table.
- Never run destructive DDL while running code references the old structure.