Source: ocean/docs/runbooks/database-rollback.md | ✏️ Edit on GitHub

Database Migration Rollback Runbook

Overview

This runbook provides step-by-step instructions for rolling back database migrations in case of deployment failure or critical issues.

When to Use This Runbook

• Migration deployment failed midway
• Application errors after migration deployment
• Data corruption detected
• Performance degradation after migration

Prerequisites

• Access to Supabase dashboard
• SUPABASE_DB_PASSWORD environment variable
• Production database connection details
• Backup or snapshot available

Rollback Procedures

Severity Level 1: Immediate Rollback Required

Symptoms: Complete application failure, data loss risk, auth broken

1. Stop all deployments

   # Cancel any running GitHub Actions
   gh run cancel <run-id>

2. Connect to production database

   export SUPABASE_DB_PASSWORD='your-password'
   psql "postgresql://postgres:$SUPABASE_DB_PASSWORD@db.fldiayolmgphysdwgsuk.supabase.co:5432/postgres"

3. Identify problematic migration

   -- Check recent migrations
   SELECT version, name, executed_at
   FROM supabase_migrations.schema_migrations
   WHERE executed_at > NOW() - INTERVAL '1 hour'
   ORDER BY executed_at DESC;

4. Execute rollback script

   -- If rollback script exists
   \i supabase/migrations/<version>_rollback.sql
   
   -- Mark migration as not applied
   DELETE FROM supabase_migrations.schema_migrations
   WHERE version = '<version>';

5. Verify rollback

   pnpm tsx scripts/verify-migrations.ts --env=production

Severity Level 2: Controlled Rollback

Symptoms: Partial functionality affected, no data loss risk

1. Create maintenance window

   • Notify users via status page
   • Enable maintenance mode if available

2. Take backup snapshot

   -- Create backup point
   SELECT pg_create_restore_point('pre_rollback_' || NOW()::text);

3. Review migration content

   cat supabase/migrations/<version>_*.sql

4. Generate reverse migration

   pnpm tsx scripts/generate-rollback.ts <version>

5. Test rollback in staging

   supabase link --project-ref $STAGING_PROJECT_ID
   supabase db push --file rollback_<version>.sql

6. Apply rollback to production

   supabase link --project-ref $PRODUCTION_PROJECT_ID
   supabase db push --file rollback_<version>.sql

Severity Level 3: Partial Rollback

Symptoms: Minor issues, specific features affected

1. Identify specific objects to rollback

   -- Find objects created by migration
   SELECT * FROM pg_event_trigger_ddl_commands()
   WHERE command_tag LIKE '%CREATE%'
   AND object_identity LIKE '%recent_object%';

2. Create targeted rollback

   BEGIN;
   -- Drop specific objects only
   DROP TABLE IF EXISTS problematic_table CASCADE;
   DROP FUNCTION IF EXISTS problematic_function CASCADE;
   
   -- Restore previous version if needed
   CREATE OR REPLACE FUNCTION ...
   COMMIT;

Post-Rollback Actions

1. Update migration history

   -- Record rollback event
   INSERT INTO provisioning_events (
     event_type, resource_type, status, metadata
   ) VALUES (
     'migration_rollback', 'database', 'completed',
     jsonb_build_object('version', '<version>', 'reason', '<reason>')
   );

2. Verify application health

   # Run smoke tests
   pnpm test:production-smoke
   
   # Check error rates
   pnpm run health:check

3. Update codebase

   # Create fix branch
   git checkout -b fix/migration-<version>
   
   # Remove or fix problematic migration
   rm supabase/migrations/<version>_*.sql
   # or
   vim supabase/migrations/<version>_*.sql
   
   # Commit and push
   git add -A
   git commit -m "fix: rollback migration <version>"
   git push origin fix/migration-<version>

4. Create incident report

   • Document what happened
   • Root cause analysis
   • Prevention measures
   • Update this runbook if needed

Emergency Contacts

• Database Admin: Contact via Slack #database-emergency
• On-call Engineer: PagerDuty escalation
• Supabase Support: support@supabase.io (Enterprise support)

Recovery Validation

After rollback, verify:

• Application is accessible
• Authentication works
• Critical user flows work
• No error spikes in monitoring
• Database schema matches expected state
• Migration history is accurate

Prevention Checklist

To prevent future rollback scenarios:

• Always test migrations in staging first
• Include rollback scripts for breaking changes
• Use feature flags for gradual rollout
• Monitor error rates after deployment
• Have backup strategy in place