> ## Documentation Index
> Fetch the complete documentation index at: https://docs.soundchecklive.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Migration lock safety

> How boot-time Prisma migrations take locks, the BEGIN / lock_timeout / DO-retry pattern for risky DDL, and how to recover from a P3009 failed-migration row

api-events applies pending migrations when the process boots (`migrateDeployOrThrow()` in `api-events/src/encore.service.ts`, which runs `prisma migrate deploy`). A migration that waits on a lock holds the boot, and a migration that fails leaves a row that blocks every later deploy. This page is the rule for writing lock-taking DDL and the runbook for the failure.

## What Prisma does (verified on Prisma 7.8.0, Postgres 14 and 18)

* **`migrate deploy` does not wrap a file in one transaction.** Unless the file has an explicit `BEGIN` and `COMMIT`, each statement runs on its own. A bare `SET LOCAL lock_timeout = '5s';` at the top of a file has no effect: `SHOW lock_timeout` on the next statement returns `0` (wait forever).
* **A `DROP TABLE` of a table with a foreign key to a live table takes `ACCESS EXCLUSIVE` on the referenced table**, and waits indefinitely when something holds a conflicting lock. Every request that touches the referenced table queues behind it.
* **With an explicit `BEGIN; SET LOCAL lock_timeout = '...'; ... COMMIT;`** the timeout fires and the script rolls back.
* **A rolled-back script still leaves a failed `_prisma_migrations` row** (`finished_at` and `rolled_back_at` both null). Every later `migrate deploy` then fails with `P3009` until someone runs `prisma migrate resolve --rolled-back <name>`.
* **Prisma reports the aborted transaction, not the timeout.** The surfaced error is `current transaction is aborted, commands ignored until end of transaction block`, not the `lock timeout` text. Do not grep for the cause in the Prisma output; read the Postgres log for `canceling statement due to lock timeout`.
* **`migrateDeploy.ts` retries only lock-contention text (P1002, advisory lock) and P1001.** It does not retry a lock-timeout failure, and since the failed row blocks the next attempt, it could not help if it did. `P3009` is logged with the migration name and a pointer to this page, never retried, and the process exits 1 (`encore.service.ts`), so the revision fails to start.

The existing bare-`SET LOCAL` migrations are grandfathered in `api-events/src/db/migrationLockSafety.baseline.json`. They already ran. Do not edit them (see pitfall 17); add a new migration.

## Required pattern for lock-taking DDL

Any migration that takes a lock stronger than `ROW EXCLUSIVE` on a live table (`DROP TABLE`, `ALTER TABLE ... DROP`, `ALTER TABLE ... ADD CONSTRAINT`, `CREATE INDEX` without `CONCURRENTLY`, type changes) uses all of these:

1. An explicit `BEGIN;` as the first statement and `COMMIT;` as the last. `SET LOCAL` is only valid inside it.
2. `SET LOCAL lock_timeout = '2s';` right after `BEGIN`.
3. Each risky statement in its own `DO` block that retries a bounded number of times on `lock_not_available` (SQLSTATE `55P03`) or `deadlock_detected` (`40P01`), with `pg_sleep` between attempts. The `DO` block's inner `BEGIN ... EXCEPTION` is a subtransaction, so a caught deadlock rolls back only that attempt and the next one starts clean.
4. A defined outcome when the attempts run out, chosen by whether the change is optional:
   * **Optional cleanup** (dropping an obsolete table or column that no running code reads): a non-fatal `RAISE NOTICE`. The object stays, and a later idempotent migration drops it once the table is quiet. The migration never fails on lock contention. The template below does this.
   * **Required change** (an `ADD CONSTRAINT`, a type change, or a drop that the new code depends on): never skip silently. A skipped statement still commits, Prisma records the migration as applied, and it is never retried, so the schema the new code needs would be missing. Replace the `RAISE NOTICE` with `RAISE EXCEPTION 'users_email_key not added after % attempts', attempt;`. The transaction rolls back, the deploy fails, and you recover with the P3009 runbook below. Where a failed deploy is not acceptable, split the work: ship the change in a later migration or a separate step that you confirm has finished.
5. `IF EXISTS` / `IF NOT EXISTS` on every statement, so a replay and a later retry are no-ops (`.cursor/rules/sql-migration-idempotence.mdc`).

Notes:

* `lock_timeout` applies to each lock wait, not to the whole script. `statement_timeout` is a separate setting.
* Locks acquired earlier in the transaction are held until `COMMIT`, including across the `pg_sleep`. Keep the transaction small, and put the lock-taking statements first.
* The `EXCEPTION` block rolls back only its own subtransaction, so the transaction stays usable and `COMMIT` still succeeds.
* Put statements that need no retry (backfills, `CREATE TABLE IF NOT EXISTS`) in the same transaction, outside the `DO` blocks.
* A `DROP TABLE` that has a foreign key to a live table: drop the constraint in its own retrying `DO` block first, so the wait is on the child table.

### Copy-paste template (optional cleanup)

```sql theme={null}
BEGIN;
SET LOCAL lock_timeout = '2s';

DO $$
DECLARE
  max_attempts constant int := 5;
  attempt int := 0;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      DROP TABLE IF EXISTS "old_thing";
      EXIT;
    EXCEPTION WHEN lock_not_available OR deadlock_detected THEN
      IF attempt >= max_attempts THEN
        RAISE NOTICE 'old_thing not dropped after % attempts; a later migration retries it', attempt;
        EXIT;
      END IF;
      PERFORM pg_sleep(attempt);
    END;
  END LOOP;
END
$$;

COMMIT;
```

For several risky statements, repeat the whole `DO` block once per statement so each gets its own attempt budget.

### Audit `table_config` seeds

`api-events/scripts/audit/generateTableConfigSeed.ts` writes every `*_audit_table_config_seed` migration. Its last step re-bakes the audit triggers with `audit.sync_triggers()`, which drops and recreates a trigger on every audited table and so takes `ACCESS EXCLUSIVE` on each one. The re-bake is a required change, so a seed generated from this template fails rather than skips:

* The seed runs in one `BEGIN` ... `COMMIT` with `SET LOCAL lock_timeout = '2s'`.
* Before the re-bake it locks every table the re-bake touches, in name order: the tables with an `audit_log_change` trigger, the re-attach list, and the `org_parent_path` parents.
* A lock timeout rolls back that round's subtransaction, which releases every lock taken in the round. The block then sleeps for `min(attempts on that table, 5)` seconds and starts a new round. No lock is held during the sleep.
* Each table gets 5 attempts and the rounds stop at 20. When either budget runs out, the block raises `audit trigger refresh could not lock <table> ...` (SQLSTATE `55P03`). The whole seed rolls back and the deploy fails. Recover with the P3009 runbook below.

Seeds that have already shipped keep the old unbounded re-bake. Do not edit them; the next seed you generate with `--new` uses the template above. `npm run check:audit-table-config-seed` accepts the newest seed in either shape, as long as its manifest rows match. `scripts/audit/generateTableConfigSeed.test.ts` checks the emitted SQL, and `src/db/auditTableConfigSeedLock.integration.test.ts` replays it, holds a lock against it and checks that it fails or finishes.

## Check

`npm run check:migration-lock-safety` (in `api-events`, part of `checks:static`, pre-commit and CI) fails on a migration not in the baseline that:

* uses `SET LOCAL` in a statement that is not between an explicit `BEGIN` and its `COMMIT`, or
* contains `DROP TABLE` or `ALTER TABLE ... DROP` outside a `DO` block and outside a `BEGIN` ... `COMMIT` span.

The check follows `BEGIN` / `START TRANSACTION` and `COMMIT` / `END` / `ROLLBACK` statement by statement, so a `SET LOCAL` or `DROP` before the `BEGIN` or after the `COMMIT` is still reported. Text inside single-quoted literals is ignored.

`SET LOCAL` and `DROP` inside a stored function body (`CREATE FUNCTION ... AS $$ ... $$`) or a string do not run during the migration and are not reported. A `DO` block runs, so its `SET LOCAL` is.

The baseline can only fall. `FROZEN_CUTOFF` and `FROZEN_GRANDFATHERED_COUNT` live in `migrationLockSafety.ts`, not in the JSON, and the check fails unless the JSON matches both. An entry whose file now passes must be removed from `migrationLockSafety.baseline.json`, and the same change lowers the count constant. Adding an entry, or moving the cutoff, is a code change a reviewer sees, and it fails the check. Do not add a new migration to the baseline; fix the migration.

## Recover from P3009

Symptom: a new api-events revision exits during boot, and the log has

```text theme={null}
[api-events] prisma migrate deploy BLOCKED (P3009): failed migration(s) in _prisma_migrations: <name>. Not retrying; ...
```

The Prisma output above that line says `P3009` and `migrate found failed migrations in the target database`.

### 1. Reach the environment's database

The repo has no wrapper, proxy script or bastion for dev, qa or prod databases, and CI must not run `prisma migrate deploy` against them (`docs/engineering/ci-cd/overview.mdx`). What it does define is where each environment's connection string lives. Terraform (`infra-gcp/modules/events/main.tf`) mounts the Secret Manager secret `${env}-DatabaseUrl` as `DATABASE_URL`:

| Env | GCP project | Secret |
| - | - | - |
| dev | `dev-soundcheck-live` | `dev-DatabaseUrl` |
| qa | `qa-soundcheck-live` | `qa-DatabaseUrl` |
| prod | `soundcheck-live` | `prod-DatabaseUrl` |

```bash theme={null}
ENV=qa
GCP_PROJECT=qa-soundcheck-live
export DATABASE_URL="$(gcloud secrets versions access latest --secret="${ENV}-DatabaseUrl" --project="$GCP_PROJECT")"
```

Network reachability from your machine (firewall rule, VPN, private endpoint) is not defined anywhere in the repo. Production Postgres is documented as an Azure Flexible Server (`docs/engineering/backend/database.mdx`). Get access from whoever owns that network path before an incident, not during one.

Locally the database is the shared Docker Postgres: `DATABASE_URL=postgresql://postgres:postgres@localhost:5433/soundcheck?schema=public`.

`psql` rejects the Prisma-only `?schema=public` query parameter. Use `psql "${DATABASE_URL%%\?*}"`.

### 2. Identify the failed migration

The boot log names it. To confirm against the table:

```sql theme={null}
SELECT migration_name, started_at, logs
  FROM _prisma_migrations
 WHERE finished_at IS NULL AND rolled_back_at IS NULL;
```

### 3. Decide what the failed script left behind

* A script with an explicit `BEGIN` / `COMMIT` rolled back completely. Nothing it did is in the database.
* A legacy script without `BEGIN` ran statement by statement. Earlier statements committed. Inspect the schema, then either finish the rest by hand and mark it applied (`--applied` in step 4), or roll it back and let it re-run (it must be replay-safe, which the idempotence policy requires of new migrations).

### 4. Resolve, verify, redeploy

Run from `api-events/` with `DATABASE_URL` exported. Use the pinned local CLI, not an unpinned `npx prisma`. `scripts/migrate.mjs` only wraps `deploy`, `status` and `reset`, so `resolve` is run directly.

```bash theme={null}
cd api-events
./node_modules/.bin/prisma migrate resolve --rolled-back <migration_name> --config prisma.config.ts
npm run db:migrate:status
```

`db:migrate:status` should list `<migration_name>` as pending and no failed migrations. Then redeploy: start a new revision (`api-events-deploy.yml`, or restart the service), which boots and runs `migrate deploy`, or run `npm run db:migrate` yourself.

Before redeploying, make sure the script cannot fail the same way:

* **No environment has applied it.** The rolled-back migration was never applied, so you may edit its SQL to the pattern above, then redeploy.
* **Another environment already applied it** (so the file must stay as it is, pitfall 17), and it keeps failing in this one. A later repair migration cannot help: after `--rolled-back`, `migrate deploy` runs the unchanged file again before any newer migration. Pick one:
  * Remove the blocking condition, then redeploy the unchanged file. For a lock timeout, find the holder with `SELECT pid, state, query, xact_start FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start;` (the Postgres log line is `canceling statement due to lock timeout`), end that transaction or wait for it, and redeploy in a quiet window.
  * Finish the change by hand in this database (run the file's statements yourself in one `BEGIN` ... `COMMIT`, with the same lock handling), then mark it applied with `./node_modules/.bin/prisma migrate resolve --applied <migration_name> --config prisma.config.ts` instead of `--rolled-back`. Confirm the resulting schema matches what the other environments have before you do.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.