Db Migration Governance

Expand-Deploy-Migrate-Contract Pattern

For any column or table change, follow this 3-phase pattern:

Phase 1 — Expand

• Add new columns/tables as NULLABLE or with defaults
• No data loss, no breaking changes
• Deploy code that can handle both old and new schema

Phase 2 — Migrate

• Backfill data into new columns
• Run via Worker job (not inline migration)
• Must be idempotent

Phase 3 — Contract

• Remove old columns/tables
• Only after Phase 2 is verified complete
• Separate migration file from Phase 1

Tenant Fan-Out Safety

When applying tenant migrations:

• Migrations run against ALL tenant databases in the pool
• A failure on ONE tenant must not prevent other tenants from continuing
• Each tenant migration must be wrapped in a transaction
• Migration status must be tracked per-tenant (success/failure/pending)
• Failed tenant migrations must be retried with exponential backoff

Lock Risk Analysis

Before writing any migration, AI must evaluate:

For HIGH-risk operations:

• Use CREATE INDEX CONCURRENTLY instead of CREATE INDEX
• Break ALTER COLUMN TYPE into expand-migrate-contract phases
• Add NOT NULL via CHECK constraint first, then convert

Forbidden Migration Patterns

• No DROP TABLE without explicit approval — archived data must be preserved
• No TRUNCATE in migration files
• No DELETE without WHERE in migration files
• No schema changes outside migration files — no ad-hoc SQL via MCP
• No modifying closed/hardened stage migrations
• No cross-tenant data operations in a single migration

Drizzle ORM Integration

Migrations are generated via Drizzle Kit but may be hand-edited for safety:

bun drizzle-kit generate    # Generate migration from schema changes
bun drizzle-kit push        # Apply to development database (NEVER production)

AI must review generated migrations before committing — Drizzle may generate unsafe operations.

Pre-Commit Validation

Before committing any migration:

1. Verify migration is in the correct directory (master vs tenant)
2. Verify sequential numbering
3. Verify schema_version increment
4. Verify no modification of existing migration files
5. Run bun run arch:audit to validate architecture compliance

Verdict Protocol

VERDICT: PASS   — migration follows all rules
VERDICT: BLOCKED — migration violates safety rules (specify which)