Supabase CLI migration repair after a failed db push: when is it safe to mark a migration reverted?

Decision Criteria for Migration Recovery

Marking a migration as reverted using supabase migration repair is safe only if no DDL statements from that migration file were committed to the database. Because migration repair only modifies the schema_migrations tracking table and does not execute or undo SQL, marking a partially applied migration as reverted will leave orphaned objects in your database, leading to "already exists" errors during the next push.

Recovery Path Selection

• Use migration repair --status reverted: When a schema audit confirms that zero tables, columns, constraints, or types defined in the failing migration exist in the database.
• Use migration repair --status applied: When a schema audit confirms that 100% of the intended changes were successfully committed, despite the CLI reporting a failure.
• Use PITR (Point-in-Time Recovery): When the audit reveals a "dirty" state (some objects exist, others do not) and the manual effort to reverse those specific changes is high or risky.
• Use supabase db reset: In local development environments where data persistence is not required and a clean slate is preferred over manual repair.

Auditing Partial DDL Execution

Before choosing a repair status, you must verify the actual state of the database against the SQL in the migration file. Since most PostgreSQL DDL is transactional, a failure usually rolls back the entire block—unless you used non-transactional operations like CREATE INDEX CONCURRENTLY.

Run targeted queries against the information_schema for every object introduced in the failed file:

-- Check for a table introduced in the migration
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name = 'your_new_table';

-- Check for a column added to an existing table
SELECT column_name FROM information_schema.columns 
WHERE table_schema = 'public' AND table_name = 'existing_table' AND column_name = 'new_column';

Testing Recovery Flows in CI

To validate your team's recovery procedure without risking production, use a local Docker-based Supabase stack in your CI pipeline:

1. Initialize the environment with supabase start.
2. Apply migrations up to the stable version preceding the failure.
3. Manually execute the failing migration script and intentionally trigger a failure (e.g., by inserting a syntax error halfway through the file).
4. Perform the information_schema audit to verify the partial state.
5. Apply the migration repair command and verify that subsequent migrations can be pushed without conflict.

Required Diagnostic Detail

To provide a more specific recommendation, it is necessary to know if the failed migration contained non-transactional DDL (such as CONCURRENTLY keywords). If it did, the likelihood of a partial state is significantly higher, making PITR the safer default over migration repair.