Database Migration Governance

This document defines the source of truth and process for database schema changes.

🎯 Principles

1. Migrations are the source of truth - Not dev databases, not production, not schema.ts alone
2. Linear history - Migrations form a single, ordered sequence
3. Fail fast - CI detects drift before merge, not after deploy
4. Expand-then-contract - Destructive changes happen in phases

📜 Source of Truth Hierarchy

1. libs/database/migrations/*.sql - Authoritative migration history
2. libs/database/canonical-schema.sql - Snapshot derived from migrations (auto-generated)
3. libs/database/src/schema.ts - TypeScript schema (must match migrations)

Rule: Never trust a local dev DB as canonical. Always regenerate from migrations.

🔄 Workflow

Creating a Migration

## 1. Make changes to schema.ts
## 2. Generate migration
cd libs/database
pnpm drizzle-kit generate
 
## 3. Review generated SQL
cat migrations/0004_new_feature.sql
 
## 4. Test locally
pnpm drizzle-kit push
 
## 5. Run governance check
cd ../..
./scripts/migrate-check.sh

Before Opening PR

• Run ./scripts/migrate-check.sh (catches 90% of issues)
• Rebase on main if it's been >1 day
• Review MIGRATION_CHECKLIST.md

Resolving Concurrent Migrations

If CI detects concurrent migrations:

## 1. Fetch latest main
git fetch origin main
 
## 2. Rebase
git rebase origin/main
 
## 3. Renumber your migration if needed
mv migrations/0004_my_feature.sql migrations/0005_my_feature.sql
 
## 4. Update any references
## 5. Test again
./scripts/migrate-check.sh

🚨 Destructive Changes (Expand-Then-Contract)

For column drops, table drops, or type changes:

Phase 1: Expand (Week 1)

-- Add new column, keep old
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;
-- Backfill in application code

Deploy code that writes to both old and new columns.

Phase 2: Switch Reads (Week 2)

-- No migration, just deploy code

Deploy code that reads from new column, still writes to both.

Phase 3: Contract (Week 3)

-- Drop old column (safe now)
ALTER TABLE users DROP COLUMN old_email_verified;

Deploy code that only uses new column.

🤖 CI Automation

CI runs on every PR touching migrations:

1. Create ephemeral DB (Docker Postgres)
2. Apply all migrations in order
3. Dump schema to /tmp/ci_schema.sql
4. Compare to canonical-schema.sql
5. Detect concurrent migrations on main
6. FAIL PR if drift or conflicts detected

See .github/workflows/schema-check.yml for details.

📊 Canonical Schema Snapshot

After every merge to main, CI regenerates:

## Manual regeneration (if needed)
cd libs/database
docker run -d --name pg-temp postgres:15
pnpm drizzle-kit push
docker exec pg-temp pg_dump --schema-only > canonical-schema.sql
docker stop pg-temp && docker rm pg-temp

This file is committed to repo for fast comparisons.

🛑 Emergency: Fixing Drift

If production has drifted from migrations:

1. Dump production schema: pg_dump --schema-only > prod_schema.sql
2. Compare to canonical: diff canonical-schema.sql prod_schema.sql
3. Create reconciliation migration: Write SQL to make prod match canonical
4. Test on staging first
5. Document in migration header why this exists

📋 Migration Naming Convention

0001_initial_schema.sql
0002_add_user_roles.sql
0003_org_scim_support.sql
0004_pkce_required.sql

• 4-digit sequential number
• Underscore-separated
• Descriptive name
• .sql extension

🔍 Daily Drift Detection

Nightly job compares staging DB (built from migrations) to canonical:

## .github/workflows/nightly-drift-check.yml
schedule:
  - cron: '0 2 * * *'  # 2 AM UTC daily

If drift detected → auto-create GitHub issue with diff.

✅ Pre-Merge Checklist

See MIGRATION_CHECKLIST.md for the full checklist template.

🎓 Training

New developers: Read this doc + run through examples/migration-tutorial/ before first schema change.