Golden path: diagnose or recover a failed migration

When to use

Use this path when dbmate or server startup cannot apply migrations, a deployment waits on database work, the migration version and physical schema disagree, or the application fails immediately after a recorded migration.

The objective is to classify the failure before changing state. A failed health check alone does not identify a migration failure.

Files and boundaries

• apps/server/migrations/*.sql: expected up/down SQL.
• apps/server/src/infra/db/migrate.ts: startup runner, per-file transaction, and version recording.
• schema_migrations: evidence of recorded versions; not a manual repair switch.
• API/deployment logs: connection, startup, migration, and application-query errors.
• Postgres pg_stat_activity, pg_locks, catalog tables, and constraints: physical database facts.
• the currently deployed and previous application artifacts: compatibility evidence.
• backups/restore verification: recovery boundary for destructive changes.

Procedure

1. Stop rollout automation and prevent additional replicas from racing the same migration. Keep the last known compatible artifact available.

2. Identify the exact environment, database host/name (without printing credentials), application version, migration artifact/commit, last recorded migration, first failing file, and timestamp.

3. Check connection separately from schema:

   DATABASE_URL='postgres://safe-test-host/database?sslmode=require' \
     dbmate --migrations-dir apps/server/migrations status

   A DNS, TCP, TLS, authentication, or database-not-found error occurs before migration SQL. Fix the connection/deployment mapping; do not edit SQL.

4. If connected, inspect recorded versions and the target objects:

   SELECT version FROM schema_migrations ORDER BY version;
   SELECT to_regclass('public.expected_table');

5. If the command waits, inspect blockers from a privileged operational session:

   SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event_type,
     wait_event,
     pg_blocking_pids(pid) AS blocked_by,
     query_start,
     left(query, 200) AS query
   FROM pg_stat_activity
   WHERE datname = current_database()
   ORDER BY query_start;

   Identify the owner and business operation before canceling anything. A long-running customer transaction is not safe to terminate merely because a deploy is waiting.

6. Classify the evidence:

   Class	Version row	Physical schema	Typical evidence	Response
   Connection	unknown	unknown	DNS/TCP/TLS/auth error	repair deployment/database connectivity
   Lock/wait	unchanged	prior schema	wait event and blocking PID	coordinate owner; retry after safe release
   Transactional DDL failure	absent	failed file rolled back	SQL error; no new objects/version	correct the unreleased/failing artifact or publish a reviewed repair release
   Non-transactional/manual partial state	absent or inconsistent	some objects remain	catalog differs from file	back up; write explicit idempotent reconciliation; do not forge version
   Application incompatibility	present	expected new schema	repository/query error after migration	roll application or ship forward-compatible code/schema

7. Compare the failed statement with Postgres facts: existing rows, nulls, duplicates, foreign-key or check violations, enum casts, extension availability, lock level, and disk/connection limits.

8. Choose recovery:

   • retry unchanged only for a transient connection or safely released lock;
   • use one reviewed down only when the version is recorded, reversal is data-safe, and the compatible application order is known;
   • use a forward repair when reversal loses data, other environments may have applied the version, or the schema must remain compatible;
   • restore a tested backup only for a declared destructive incident.

9. Reproduce on an isolated snapshot or fixture, then run up → down → up, DB integration tests, and the compatible application artifact before resuming rollout.

10. Record the root cause, exact evidence, recovery, affected versions, and prevention test without copying credentials or customer data.

Verification

The repository contains an intentionally failing transactional fixture:

RUN_DB_INTEGRATION_TESTS=1 \
  DATABASE_URL='postgres://localhost:5432/app_migration_proof?sslmode=disable' \
  bun test --cwd apps/server src/infra/db/migrate.integration.test.ts
vp check
vp run -r test

It proves that a successful preceding migration is recorded, the failing file's DDL is rolled back, and the failing version is not recorded. Also test a connection failure against a disposable non-listening endpoint and confirm no secret value appears in captured output.

Security constraints

• Redact passwords, tokens, private hostnames, full customer queries, and row data from logs and issue comments.
• Do not cancel or terminate a blocking backend until its owner, transaction, and data-loss impact are understood.
• Take and verify a backup before manual reconciliation or destructive rollback.
• Use least-privilege diagnostic access; schema repair does not justify broad customer-data access.
• Preserve evidence before changing migration files, version rows, or physical objects.

Failure modes

• Treating every startup failure as bad SQL and changing a correct migration.
• Retrying a lock indefinitely while multiple replicas amplify contention.
• Assuming transactional rollback when SQL or an external/manual step ran outside the transaction.
• Running down while the new application is serving traffic that requires the new schema.
• Editing a released version that some environments already recorded.
• Deleting a schema_migrations row or manually creating an object to silence the runner.
• Publishing raw connection strings or customer queries during incident coordination.

Rollback and diagnosis

Rollback is a deployment sequence, not just dbmate down: quiesce incompatible traffic, verify backup, deploy or retain the compatible application, execute one reviewed down section, assert the schema and data, then reopen traffic. If any step is unsafe or the version is already divergent across environments, prefer an idempotent forward repair and rehearse it on a snapshot.

For a missing migration or template directory in a standalone binary, do not alter the database. Verify the artifact layout and MIGRATIONS_DIR/EMAIL_TEMPLATES_DIR; explicit missing overrides are designed to fail startup.

Acceptance criteria

• The failure is classified as connection, lock, transactional/partial DDL, or application incompatibility with recorded evidence.
• Recovery preserves version history and has a verified backup/forward or down strategy.
• Reproduction passes on an isolated database, including relevant schema assertions and application tests.
• Rollout order and compatible application versions are explicit.
• Incident output contains no secret or customer data.
• A prevention test or documented contract closes the discovered gap.

Agent prohibitions

• Do not edit a released migration before determining where it has been applied.
• Do not delete, insert, or update schema_migrations manually to bypass the runner.
• Do not terminate blockers, drop objects, run down, or restore backups without exact environment and impact confirmation.
• Do not retry a deterministic SQL error as though it were transient.
• Do not expose connection strings, credentials, private hosts, customer rows, or raw provider data.
• Do not resume rollout from a green health endpoint without schema and application proof.