Database Migration Manager

Database Migration Manager

Expert in safe, reversible database schema migrations with zero-downtime deployment strategies.

Activation Triggers

Activate on: "database migration", "schema change", "alter table", "add column", "drop column", "zero-downtime DDL", "rollback plan", "Prisma migrate", "Drizzle kit", "Flyway", "migration strategy"

NOT for: Query optimization → database-optimizer | ORM data modeling → data-pipeline-engineer | Backup/restore → devops-automator

Quick Start

1. Assess the change — additive (safe), modification (needs strategy), or destructive (needs expand-contract)
2. Write the migration — forward migration with explicit rollback SQL
3. Test on staging — run against a copy of production data volume
4. Plan zero-downtime — use expand-contract for breaking changes
5. Execute with monitoring — deploy migration, watch error rates, rollback if needed

Core Capabilities

Architecture Patterns

Expand-Contract Pattern (Zero-Downtime Column Rename)

Phase 1 — EXPAND (deploy migration, app reads both):
  ├─ Add new column `full_name`
  ├─ Backfill: UPDATE users SET full_name = name
  ├─ Add trigger: sync writes to both columns
  └─ Deploy app reading `full_name`, falling back to `name`

Phase 2 — MIGRATE (app writes to new only):
  ├─ Deploy app writing only to `full_name`
  └─ Verify no reads/writes to old column (query logs)

Phase 3 — CONTRACT (remove old):
  ├─ Drop trigger
  ├─ Drop old column `name`
  └─ Clean migration: one final migration file

Safe Migration Template (PostgreSQL)

-- migrations/20260320_001_add_email_verified.sql
-- FORWARD
BEGIN;
SET lock_timeout = '5s';  -- Fail fast if table locked

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS email_verified boolean
  DEFAULT false NOT NULL;

CREATE INDEX CONCURRENTLY IF NOT EXISTS
  idx_users_email_verified ON users(email_verified)
  WHERE email_verified = true;

COMMIT;

-- ROLLBACK (in companion file or comment block)
-- BEGIN;
-- DROP INDEX CONCURRENTLY IF EXISTS idx_users_email_verified;
-- ALTER TABLE users DROP COLUMN IF EXISTS email_verified;
-- COMMIT;

Migration Safety Ladder

Risk Level 1 (Safe):    ADD COLUMN (nullable), CREATE INDEX CONCURRENTLY
Risk Level 2 (Caution): ADD COLUMN (with default), ADD NOT NULL constraint
Risk Level 3 (Danger):  ALTER COLUMN TYPE, RENAME COLUMN
Risk Level 4 (Critical): DROP COLUMN, DROP TABLE
                          ↓
                 Requires expand-contract pattern

Anti-Patterns

1. Running migrations in application startup — if the migration fails, the app cannot start and cannot roll back. Run migrations as a separate CI/CD step with its own rollback.
2. No lock_timeout — ALTER TABLE acquires an ACCESS EXCLUSIVE lock. Without timeout, it queues behind long queries and blocks all subsequent queries. Always set lock_timeout.
3. Destructive changes without expand-contract — dropping or renaming columns while the old app version still reads them causes errors. Always use the expand-contract pattern for breaking changes.
4. Missing rollback scripts — every forward migration needs a tested rollback. If you cannot roll back, the migration is not safe for production.
5. Backfilling in the migration transaction — large backfills in a single transaction lock the table for minutes. Backfill in batches outside the DDL transaction.

Quality Checklist

[ ] Migration has explicit rollback SQL
[ ] lock_timeout set for all DDL statements
[ ] CREATE INDEX uses CONCURRENTLY
[ ] Breaking changes use expand-contract pattern
[ ] Backfills run in batches (1000-10000 rows per batch)
[ ] Migration tested against production-volume staging data
[ ] No data loss — dropped columns backed up or archived
[ ] Migration is idempotent (IF NOT EXISTS / IF EXISTS guards)
[ ] Application code deployed before destructive phase
[ ] Monitoring dashboards checked during and after migration
[ ] Migration numbered/timestamped for ordering
[ ] Rollback tested independently on staging