Skip to main content

Source: ocean/docs/adr/0050-database-migration-strategy.md | ✏️ Edit on GitHub

ADR-050: Database Migration Strategy and Production Deployment

Status​

Proposed

Context​

We are experiencing critical issues with database migrations in production:

  1. Schema Drift: Production database has migrations that don't exist in the repository
  2. CI/CD Workarounds: Current deployment creates "placeholder" migrations for remote-only migrations
  3. Failed User Signups: Schema mismatches cause runtime errors (e.g., error vs error_message column)
  4. Lack of Visibility: No clear way to verify migration state between environments
  5. Manual Interventions: Migrations applied directly to production without version control

This approach is dangerous and unsustainable as it:

  • Breaks reproducibility across environments
  • Makes rollbacks impossible
  • Causes production incidents
  • Violates infrastructure-as-code principles

Decision​

We will implement a strict, future-proof migration strategy based on these principles:

1. Single Source of Truth​

  • All migrations MUST be in version control before being applied anywhere
  • No direct SQL execution in production outside of emergencies
  • Repository is authoritative - if it's not in git, it doesn't exist

2. Migration Verification System​

Create a comprehensive verification system that:

  • Compares schema between environments
  • Detects drift before deployment
  • Provides clear remediation steps
  • Prevents deployment when issues are detected

3. Environments and Migration Flow​

Local Development → Staging → Production
      ↓               ↓           ↓
   (immediate)    (on merge)  (manual approval)

4. CI/CD Pipeline Changes​

  • Remove automatic migration deployment to production
  • Add migration verification step that blocks on drift
  • Require manual approval for production migrations
  • Generate migration reports for each deployment

5. Emergency Procedures​

For production-only fixes:

  1. Create migration file locally first
  2. Test in staging environment
  3. Apply to production via approved process
  4. Commit to repository immediately

Implementation​

Phase 1: Migration Verification System​

// scripts/verify-migrations.ts
interface MigrationStatus {
  environment: string
  applied: string[]
  pending: string[]
  missing: string[]
  drift: boolean
}

async function verifyMigrations(): Promise<MigrationStatus> {
  const localMigrations = await getLocalMigrations()
  const remoteMigrations = await getRemoteMigrations()

  return {
    environment: process.env.ENVIRONMENT,
    applied: remoteMigrations,
    pending: localMigrations.filter((m) => !remoteMigrations.includes(m)),
    missing: remoteMigrations.filter((m) => !localMigrations.includes(m)),
    drift: remoteMigrations.some((m) => !localMigrations.includes(m)),
  }
}

Phase 2: Migration Manifest​

Create supabase/migrations/manifest.json:

{
  "version": "1.0",
  "migrations": [
    {
      "id": "20250812233110_initial_schema",
      "description": "Initial database schema",
      "breaking": false,
      "rollback": null
    },
    {
      "id": "20250826000001_fix_handle_new_user_error_column",
      "description": "Fix error_message column in provisioning_events",
      "breaking": true,
      "rollback": "20250826000001_rollback.sql"
    }
  ],
  "checksum": "sha256:..."
}

Phase 3: No Staging Environment (Cost Optimization)​

Decision: Skip staging environment due to cost and lack of users

  • All testing done locally before commit
  • Pre-commit hooks enforce local migration testing
  • Production deploys require manual approval via GitHub environments

Phase 4: Simplified CI/CD Pipeline​

name: Database Deployment

on:
  push:
    branches: [main]
    paths: ['supabase/migrations/**']

jobs:
  deploy-production:
    runs-on: ubuntu-latest
    environment: production # Requires manual approval
    if: github.ref == 'refs/heads/main'
    steps:
      - name: Deploy to production
        run: |
          supabase link --project-ref ${{ secrets.SUPABASE_PROJECT_ID }}
          supabase db push --include-all

      - name: Deploy Edge Functions
        run: |
          for func in supabase/functions/*/; do
            name=$(basename "$func")
            [ "$name" != "_shared" ] && supabase functions deploy "$name" --no-verify-jwt
          done

Key Changes:

  • Removed staging environment steps
  • Relies on pre-commit hooks for local testing
  • GitHub environment protection provides manual approval gate
  • Simplified deployment process

Phase 5: Migration Tools​

Create management scripts:

  • migrations:verify - Check for drift
  • migrations:sync - Generate missing migration files from remote
  • migrations:report - Generate deployment report
  • migrations:rollback - Apply rollback migration
  • migrations:validate - Validate SQL syntax and safety

Consequences​

Positive​

  • Eliminates schema drift between environments
  • Prevents production incidents from untested migrations
  • Enables safe rollbacks with versioned migrations
  • Improves visibility into database state
  • Enforces best practices through automation
  • Maintains audit trail of all changes

Negative​

  • Slower deployment due to staging requirement
  • Manual approval needed for production
  • Initial setup complexity for staging environment
  • Learning curve for new processes

Risks Mitigated​

  • No more "Database error saving new user" incidents
  • No more emergency production fixes
  • No more lost migrations
  • No more inconsistent environments

Alternatives Considered​

  1. Continue with current approach: Rejected due to ongoing incidents
  2. Database branching: Not supported by Supabase
  3. Blue-green deployments: Too complex for current needs
  4. GitOps with Flux/ArgoCD: Overkill for database migrations

Migration Plan​

  1. Week 1: Implement verification system
  2. Week 2: Set up staging environment
  3. Week 3: Update CI/CD pipeline
  4. Week 4: Team training and documentation
  5. Week 5: Full cutover to new process

References​

Decision Outcomes​

Once implemented, we will measure success by:

  • Zero migration-related production incidents
  • 100% migration coverage in version control
  • Deployment confidence score > 95%
  • Mean time to recovery < 15 minutes