Skip to main content

Source: ocean/docs/adr/0053-schema-drift-prevention.md | ✏️ Edit on GitHub

ADR-053: Schema Drift Prevention Strategy

Status​

Accepted

Context​

We discovered significant schema drift between local and production databases:

  • Migrations were applied directly to production without version control
  • Column names differed (error vs error_message)
  • Missing columns in various tables
  • Constraint mismatches
  • Function permission issues

This caused "Database error saving new user" for all new signups.

Decision​

1. Comprehensive Migration Applied​

Created migration 20250903100000_fix_schema_drift_comprehensive.sql that:

  • Fixes all column naming inconsistencies
  • Adds missing columns with proper defaults
  • Ensures nullable constraints are correct
  • Updates functions with proper permissions
  • Creates verification functions

2. Prevention Mechanisms​

Automated Schema Verification​

  • Script: scripts/verify-schema.ts - Runs health checks on schema
  • Script: scripts/detect-schema-drift.ts - Compares environments
  • Commands:
    • pnpm schema:verify - Verify schema health
    • pnpm schema:detect-drift - Detect differences

CI/CD Integration​

  • Workflow: .github/workflows/schema-verification.yml
    • Runs on every PR with migration changes
    • Verifies migrations apply cleanly
    • Tests user creation flow
    • Generates schema snapshots on main branch

Git Hooks​

  • Pre-push hook: Automatically verifies schema when migrations change
  • Prevents pushing broken migrations
  • Ensures local testing before deployment

3. Migration Best Practices​

Naming Convention​

YYYYMMDDHHMMSS_descriptive_name.sql

Example: 20250903100000_fix_schema_drift_comprehensive.sql

Migration Structure​

-- Always use IF EXISTS checks
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'table_name'
        AND column_name = 'column_name'
    ) THEN
        ALTER TABLE public.table_name ADD COLUMN column_name TYPE;
    END IF;
END $$;

Testing Requirements​

  1. Apply locally first: pnpm supabase migration up --local
  2. Run verification: pnpm schema:verify
  3. Test critical flows (user creation)
  4. Only then push to repository

4. Production Deployment Process​

  1. Never apply migrations directly to production

  2. Always commit to version control first

  3. Follow deployment flow:

    Local → Git → CI/CD → Staging → Production

5. Recovery Procedures​

If drift is detected:

  1. Run pnpm schema:detect-drift to identify differences
  2. Create a comprehensive fix migration
  3. Test thoroughly in local environment
  4. Deploy through normal channels

Consequences​

Positive​

  • No more schema drift between environments
  • Automated detection of issues
  • Clear audit trail of all schema changes
  • Safer deployments with verification

Negative​

  • Slightly slower push process when migrations change
  • Requires local Supabase for development
  • More rigid deployment process

Trade-offs​

  • We prioritize consistency over deployment speed
  • Automation reduces human error
  • Early detection prevents production issues

Implementation Status​

✅ Comprehensive migration applied ✅ Verification scripts created ✅ CI/CD workflow implemented ✅ Pre-push hooks updated ✅ Local environment fixed and working ✅ Commands added to package.json

Lessons Learned​

  1. Schema drift is silent but deadly - It only shows up when users try to sign up
  2. Version control everything - Including migrations
  3. Automate verification - Humans forget to check
  4. Test the critical path - User signup must always work
  5. Use idempotent migrations - IF EXISTS prevents double-application