curiositech/database-migration-planner

Database Migration Planner

Plans safe, sequenced database migrations before any SQL is written. Produces migration plans with risk scores, dependency graphs, rollback budgets, and communication timelines.

When to Use

✅ Use for: Deciding migration strategy, risk-scoring schema changes, sequencing migrations across multiple services, estimating rollback windows, planning communication to dependent teams, assessing lock contention risk, choosing between expand-contract vs. blue-green vs. big-bang.

❌ NOT for: Writing DDL/migration files (→ database-migration-manager), executing data pipelines (→ data-migration-specialist), query performance (→ postgresql-optimization), ORM config (→ drizzle-migrations).

Core Process

flowchart TD
  A[Describe the change] --> B{Single service?}
  B -->|Yes| C[Assess risk level]
  B -->|No| D[Map service dependencies]
  D --> E[Determine migration order]
  E --> C
  C --> F{Risk level}
  F -->|Low: additive only| G[Direct migration plan]
  F -->|Medium: modify existing| H[Expand-contract plan]
  F -->|High: destructive or cross-service| I[Phased rollout plan]
  G --> J[Estimate rollback budget]
  H --> J
  I --> J
  J --> K[Communication plan]
  K --> L[Final migration plan document]

Risk Classification

| Risk Level | Characteristics | Strategy | Rollback Budget | |-----------|----------------|----------|-----------------| | Low | Additive only (new table, new nullable column, new index on small table) | Direct apply | Minutes | | Medium | Modifies existing (column type change, NOT NULL addition, index on large table) | Expand-contract | Hours | | High | Destructive (drop column/table, rename across services, data type narrowing) | Phased rollout with feature flags | Days | | Critical | Cross-database, multi-region, or involves PII restructuring | Blue-green database with traffic replay | Weeks |

Decision Trees

Strategy Selection

flowchart TD
  Q1{Can old code work with new schema?}
  Q1 -->|Yes| Q2{Is the change additive?}
  Q1 -->|No| EXPAND[Expand-Contract required]
  Q2 -->|Yes| DIRECT[Direct migration]
  Q2 -->|No| Q3{Table size > 1M rows?}
  Q3 -->|Yes| ONLINE[Online DDL tool required]
  Q3 -->|No| EXPAND
  EXPAND --> Q4{Multiple services read this table?}
  Q4 -->|Yes| PHASED[Phased: migrate consumers first]
  Q4 -->|No| SINGLE[Single expand-contract cycle]

Lock Contention Assessment

flowchart TD
  L1{Operation type?}
  L1 -->|ADD COLUMN nullable| SAFE[No lock concern]
  L1 -->|ADD COLUMN NOT NULL + default| PG{Postgres ≥ 11?}
  PG -->|Yes| SAFE
  PG -->|No| REWRITE[Full table rewrite - dangerous]
  L1 -->|ALTER TYPE| REWRITE
  L1 -->|CREATE INDEX| CONC{Use CONCURRENTLY?}
  CONC -->|Yes| SAFE
  CONC -->|No| BLOCKS[Blocks writes for duration]
  L1 -->|DROP COLUMN| SAFE2[Instant in PG, rewrite in MySQL < 8.0]
  L1 -->|RENAME| INSTANT[Instant but breaks queries]

Anti-Patterns

Planning by DDL Complexity

Novice: "It's just one ALTER TABLE, ship it directly." Expert: Risk comes from service coupling and table size, not DDL line count. A single ALTER TABLE ADD COLUMN NOT NULL on a 500M-row table with 12 consuming services is Critical-risk regardless of its syntactic simplicity. Detection: Plan mentions "simple change" without noting row count or consumer count.

Rollback as Afterthought

Novice: "We'll figure out rollback if something goes wrong." Expert: Rollback is designed first. Every migration plan starts with: "How do I undo this in under X minutes?" If the answer is "you can't," the migration needs a different strategy (expand-contract, blue-green). Timeline: Pre-2020: rollback was optional for many teams. Post-incident-culture (2020+): rollback budget is a hard requirement.

Ignoring Read Replicas and Caches

Novice: Plans migration for primary only. Expert: Read replicas lag. Caches have stale schemas. A migration plan must account for: replica lag window, cache TTL expiry, connection pool recycling, and ORM schema cache invalidation. Miss any one and you get 500s from readers even though the primary migrated cleanly.

Migration Plan Template

When producing a plan, output this structure:

# Migration Plan: [Name]

## Summary
- **Change**: [What's changing]
- **Risk Level**: Low / Medium / High / Critical
- **Estimated Duration**: [Time from start to verified-complete]
- **Rollback Budget**: [Max time to fully reverse]
- **Affected Services**: [List]

## Dependencies
- [ ] Service A must deploy read-compatibility code first
- [ ] Cache TTL must expire (12h) before dropping old column
- [ ] Feature flag `new_schema_v2` must be enabled in staging first

## Sequence
| Step | Action | Duration | Rollback |
|------|--------|----------|----------|
| 1 | Deploy app code that reads both old+new | 30m | Revert deploy |
| 2 | Run forward migration | 5m | Run rollback SQL |
| 3 | Backfill new column from old | 2h | Truncate new column |
| 4 | Verify data integrity | 30m | N/A |
| 5 | Switch reads to new column | 15m | Feature flag off |
| 6 | Remove old column (after bake period) | 1 week | N/A (point of no return) |

## Rollback Plan
- **Trigger**: Error rate > 1% OR p99 latency > 2x baseline
- **Procedure**: [Specific steps]
- **Point of no return**: Step 6 (old column dropped)

## Communication
- [ ] Notify dependent teams 1 week before
- [ ] Post in #migrations channel day-of
- [ ] Update API docs if schema is exposed

Multi-Service Sequencing

When a migration spans multiple services:

1. Map the dependency graph — Which services read/write the affected tables?
2. Identify the critical path — Which service must migrate first (usually the writer)?
3. Design compatibility windows — Period where both old and new schemas are valid
4. Plan the rollout order:
   • Writers deploy dual-write code
   • Run schema migration (expand phase)
   • Readers deploy new-schema code
   • Verify all services healthy
   • Remove old-schema code (contract phase)
   • Drop old columns (after bake period)

sequenceDiagram
  participant Writer as Service A (Writer)
  participant DB as Database
  participant Reader1 as Service B (Reader)
  participant Reader2 as Service C (Reader)

  Note over Writer,Reader2: Phase 1: Expand
  Writer->>Writer: Deploy dual-write code
  Writer->>DB: ALTER TABLE ADD new_column
  Writer->>DB: Backfill new_column FROM old_column

  Note over Writer,Reader2: Phase 2: Migrate Readers
  Reader1->>Reader1: Deploy: read from new_column
  Reader2->>Reader2: Deploy: read from new_column

  Note over Writer,Reader2: Phase 3: Contract (after bake)
  Writer->>Writer: Remove old_column writes
  Writer->>DB: ALTER TABLE DROP old_column

Estimation Heuristics

| Factor | Impact on Duration | |--------|-------------------| | Table rows > 10M | +1h per 100M rows for backfill | | Consuming services > 3 | +1 week bake time per additional service | | PII/compliance columns | +1 week for legal review | | Multi-region database | 2x total duration (replica sync) | | No staging environment | 3x risk score (can't rehearse) | | Active on-call incident | STOP. Do not plan migrations during incidents. |

References

• references/risk-matrix.md — Detailed risk scoring with weighted factors for table size, consumer count, data sensitivity, and region topology
• references/communication-templates.md — Pre-written Slack/email templates for migration announcements, go/no-go decisions, and incident escalation
• references/platform-quirks.md — Database-specific gotchas: Postgres online DDL limitations, MySQL metadata locks, CockroachDB schema change jobs, PlanetScale branching model