> ## 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. # Database > Database schema and seeding for api-events api-events owns PostgreSQL DDL. Canonical schema and migrations live under `api-events/src/db/`. Encore applies migrations on boot; explicit local apply uses `npm run db:migrate`. ## Schema ``` api-events/src/db/ ├── schema.prisma # Canonical Prisma schema ├── migrations/ # Timestamped migration SQL └── prisma.ts # Shared Prisma client (DATABASE_URL) ``` ### Core models | Model | Description | | - | - | | User | User profiles and authentication | | Organization | Bands, venues, production companies | | Event | Gigs, rehearsals, shows | | Invitation | Event invitations with acceptance status | | Calllist | Reusable team rosters | | Setlist | Song lists and sets | | Position | Musician roles | | Subscription | Billing and plans | *** ## Migrations ### Apply migrations ```bash theme={null} cd api-events export DATABASE_URL='postgresql://postgres:postgres@localhost:5433/soundcheck?schema=public' npm run db:migrate # Status / reset (development only) npm run db:migrate:status npm run db:migrate:reset ``` Migrations also run automatically when Encore starts (`migrateDeployOrThrow()` in `encore.service.ts`). ### Create a new migration 1. Edit `api-events/src/db/schema.prisma`. 2. Generate migration SQL with the Prisma workflow used in this repo (see `api-events/scripts/migrate.mjs`). 3. Apply locally with `npm run db:migrate` before opening a PR. Do **not** declare Encore `SQLDatabase` — that would add a second migration applier. New migrations (stamp on or after `requiredFrom` in `api-events/src/db/migrationIdempotence.policy.json`) must replay safely after `migrate deploy`. Older folders are grandfathered; do not edit them to become idempotent. Gate: `npm run test:integration -- src/db/migrationIdempotence.integration.test.ts`. Skill: `.agents/skills/prisma-migrations/SKILL.md`. > [!WARNING] > > `audit_log` is partitioned, and partitioning is invisible to Prisma. Adding an > index to it later requires the `CREATE INDEX ON ONLY` → `CREATE INDEX > CONCURRENTLY` per leaf → `ALTER INDEX … ATTACH PARTITION` per leaf dance, or the > statement takes `ACCESS EXCLUSIVE` on all 168+ partitions at once. Its > partitions also must stay in the `audit` schema, and its primary key must keep > both partition-key columns. All three rules, with the reasoning, are in > `.agents/skills/prisma-migrations/SKILL.md`. Do **not** edit SQL in a migration that qa or prod has already applied. Prisma records a checksum and does not re-run the file. Clock and ledger date repairs go in a **new** migration. See the [Event clock writeup](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md), [problem canvas](https://docs.soundchecklive.io/engineering/backend/event-clock-problem-canvas.md), [cohort alignment](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md), [active intervals](https://docs.soundchecklive.io/engineering/backend/event-clock-active-intervals.md), and [availability semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md). *** ## Seeding Seed scripts live in `api-events/scripts/seed/`. ### Seed modes | npm script | Mode | Description | | - | - | - | | `db:seed` / `db:seed:baseline` | `baseline` | Roles, positions, subscription plans | | `db:seed:demo-full` | `demo-full` | **Recommended for local dev.** Persona-driven dataset with Clerk-shaped IDs | ### Run seeding ```bash theme={null} cd api-events export DATABASE_URL='postgresql://postgres:postgres@localhost:5433/soundcheck?schema=public' npm run db:seed:baseline npm run db:seed:demo-full ``` After `demo-full`, restart the Clerk emulator so personas appear in sign-in: ```bash theme={null} docker compose -p api -f dev/docker-compose.yml restart clerk-emulator ``` Seed manifest: `api-events/scripts/seed/manifest/local-personas.json` See `api-events/scripts/seed/README.md` for advanced flags and partial seed options. *** ## Connection ```env theme={null} DATABASE_URL=postgresql://postgres:postgres@localhost:5433/soundcheck?schema=public ``` | Port | Purpose | | - | - | | 5432 | Reserved for native PostgreSQL | | 5433 | Docker Postgres (`npm run dev:infra`) | | 5434 | Integration/e2e test database | ```bash theme={null} psql -h localhost -p 5433 -U postgres -d soundcheck ``` *** ## Connection budget Every Postgres connection an api-events instance opens comes from one of these pools (`api-events/src/db/prisma.ts`, `sessionLock.ts`), except the gig lifecycle lock described below: | Pool | Used by | Env var | Default | | - | - | - | - | | CRUD | Prisma queries and transactions. Keeps 2 connections warm. | `DB_POOL_CRUD_MAX` (2–50) | 6 | | Lock lease | `leasePrismaConnection` on a request path: event chat, Stripe subscription sync and organization renewal advisory locks | `DB_POOL_LOCK_MAX` (1–20) | 2 | | Job lock | Scheduled-job single-run locks (`withScheduledJobLock`, including cloud imports) and the travel backfill | `DB_POOL_JOB_LOCK_MAX` (1–20) | 2 | | Nested lock | Any session lock taken inside a job lock (Slack sweep, cloud-import file locks) | `DB_POOL_JOB_LOCK_MAX` | 2 | | Job lease | `leasePrismaConnection` called inside a job lock (chat and organization sweeps) | `DB_POOL_JOB_LOCK_MAX` | 2 | | Slack sync, Tally edit | One pool each for the request-path session locks of that family | `DB_POOL_REQUEST_LOCK_MAX` (1–20, per family) | 2 | | Import commit | The per-organization import commit lock (`withImportCommitLock`, `api-events/src/db/importCommitLock.ts`). Each commit holds one connection while it writes, and a commit that cannot get the lock within 30 s, or a connection within the 10 s checkout wait, gets a retryable 409. | `DB_POOL_IMPORT_COMMIT_LOCK_MAX` (1–20) | 4 | `DB_POOL_IDLE_TIMEOUT_MS` (1000–600000, default 30000) closes idle connections above the CRUD pool's warm minimum, so connections opened during a burst or by a revision that is scaling down are released quickly. An out-of-range or non-integer value falls back to the default. A checkout waits at most 10 s (`PG_CONNECTION_TIMEOUT_MS`). When every connection in a pool stays busy for that long, the caller gets `DatabasePoolExhaustedError` ("Postgres crud pool exhausted: all 6 connections stayed busy for 10000ms") instead of waiting forever. The pools are separate so that one kind of holder cannot starve another: * A held advisory-lock lease still gets a CRUD connection. * A scheduled job holds its single-run lock for the whole run, so job locks, and every lock or lease taken inside one, use job-only pools. A request-path lock never waits behind a job. * Request-path lock families do not share a pool, so a slow Slack call cannot delay a Tally edit. * A lock nested inside a job draws from the nested pool. A chain holds at most one nested connection and nothing nests deeper (`SessionLockDepthExceededError`), so the nested pool never waits. Gig delete, restore and purge (including the purges run by the `eventDeletionSweep` job) are serialized per gig by `withEventLifecycleLock` (`api-events/src/db/eventLifecycleLock.ts`). It opens its own short-lived, unpooled connection per in-flight operation, holds a session advisory lock on it through the operation's email, SMS and calendar sends, and closes it when the operation ends. These operations are rare and mostly request-path, so the formula counts them as `G`, the number running at once on one instance. The Slack sync, Tally edit, event chat, Stripe subscription sync and organization renewal locks exist to serialize their provider calls, so those calls stay inside their locks in their own pools. ### Budget formula ``` per instance = CRUD max + lock max + 3 × job-lock max + 2 × request-lock max + import-commit max + G api-events = per instance × Cloud Run max instances × 2 (old and new revisions overlap during a rollout) total = api-events + legacy Go API pool ``` `total` must stay below the server's `max_connections` minus `superuser_reserved_connections` and the connections Azure keeps for itself. When it does not, Postgres refuses new sessions for every client with `53300 remaining connection slots are reserved for roles with the SUPERUSER attribute`. That is what happened in production on 2026-10-02. With the defaults, per instance = 6 + 2 + 3 × 2 + 2 × 2 + 4 + G = 22 + G, a worst case that needs every pool full at once; `G` is usually 0 or 1. Pools open lazily and close idle connections after 30 s. At 5 instances during a rollout, the pooled worst case is 22 × 5 × 2 = 220 plus the gig lifecycle connections and the Go API. Production runs on Azure Flexible Server `B_Standard_B2s`, resized from `B_Standard_B1ms` on 2026-10-10. Azure does not change `max_connections` when the SKU changes: after the resize it still read 50, the B1ms default, so prod pins it to 429, the B2s default, in `server_parameters` in `infra/environments/prod/main.tf`. The parameter is static and a new value applies only after a server restart, so before you raise a pool, confirm the server is running with it: `az postgres flexible-server parameter show -g prod-cus-database-rg -s prod-cus-core-postgres --name max_connections` must show `isConfigPendingRestart: false`, or run `SHOW max_connections;`. Set Postgres server parameters only through the database module's `server_parameters` map, never only in the portal or with `az`, so every environment can be rebuilt with them. The azurerm provider restarts the server after it creates, updates or removes a static parameter, even when the value is unchanged, so a static entry restarts that environment's database during its `terraform-apply`. To record a value a server already runs with, add an `import` block for it, as prod does for `max_connections`; the import leaves the server alone. Azure keeps 15 connections for itself (`superuser_reserved_connections` 10 and `reserved_connections` 5), which leaves 414. The GCP Cloud SQL instances set `max_connections` with the `max_connections` input of `infra-gcp/modules/database` (default 100), which writes the database flag and moves the open-connections alert to 80% of it. Size it to the tier's memory. Cloud SQL sets the default from the instance memory ([flags reference](https://cloud.google.com/sql/docs/postgres/flags), `max_connections`): 25 for 0.6 GB, 50 for 1.7 GB, 100 from 3.75 GB, 200 from 6 GB, 400 from 7.5 GB and 500 from 15 GB. A value above the default for the memory leaves less memory per connection, so raise the memory with it. Cloud SQL keeps 3 `superuser_reserved_connections`. | Environment | Tier | Memory | `max_connections` | | - | - | - | - | | prod | `db-custom-1-6144` | 6 GB | 200 | | qa, dev | `db-g1-small` | 1.7 GB | 100 (the module default, above the 50 Cloud SQL gives 1.7 GB) | qa and dev cap api-events at 19 connections per instance (`DB_POOL_*` in `extra_cloud_run_env`: 6 CRUD + 1 lock + 3 × 2 job-lock + 2 × 1 request-lock + 4 import-commit) and at `cloud_run_max_instances = 2`, which equals their high-availability minimum. A rollout with both revisions at max opens 19 × 2 × 2 = 76 of their 100 (97 usable), which leaves about 21 for `G`, the legacy Go API and admin sessions. Keep that worst case under about 85. Production api-events opens at most 25 connections per instance (12 CRUD + 1 lock + 3 × 2 job-lock + 2 × 1 request-lock + 4 import-commit). With `cloud_run_max_instances = 2` and both revisions at max during a rollout, that is 25 × 2 × 2 = 100. Against a limit of 200 (197 usable), that leaves about 97 for the legacy Go API pool, the gig lifecycle connections (`G`), DMS and admin sessions. Changing `max_connections` or the tier restarts a Cloud SQL instance, so apply either one only in an owner-approved maintenance window. Keep production inside the limit by setting the `DB_POOL_*` variables through `extra_cloud_run_env` in `infra-gcp/environments/prod/main.tf` and capping `cloud_run_max_instances` to match the formula. The legacy Go API (`soundcheck-api-production`, App Service `prod-cus-api-app`) shares the database and has its own pool. It runs on one App Service instance (per-app scaling, `worker_count = 1` in `infra/environments/prod/main.tf`), so it holds one pool, and retiring it frees that too. To see where connections go, run `SELECT application_name, state, count(*) FROM pg_stat_activity GROUP BY 1, 2;`. api-events pools report as `api-events-crud`, `api-events-lock`, `api-events-job-lock` and `api-events-session-lock-`, and the gig lifecycle lock as `api-events-event-lifecycle-lock`. *** ## Prisma client Generate after schema changes: ```bash theme={null} cd api-events && npm run prisma:generate ``` Also runs on `npm install` (`postinstall`). Use the shared client from `src/db/prisma.ts` in operations — do not instantiate ad-hoc clients. *** ## Best practices * Edit **only** `api-events/src/db/schema.prisma` for DDL changes. * Never edit applied migration SQL — add a new migration instead. Prisma does not re-run an applied file; see [Ledger payable dates](https://docs.soundchecklive.io/engineering/backend/ledger-payable-dates.md) and pitfalls #16. * Run `npm run db:migrate` locally before pushing schema changes. * Money columns use `Decimal @db.Decimal(12,2)` — see `.agents/skills/monetary-handling/SKILL.md`. *** ## Troubleshooting > [accordion] Connection refused > > Ensure dev infra is running: > > ```bash theme={null} > npm run dev:infra > docker compose -p api -f dev/docker-compose.yml ps > ``` > [accordion] Schema out of sync > > ```bash theme={null} > cd api-events && npm run prisma:generate > cd api-events && npm run db:migrate:status > cd api-events && npm run db:migrate > ``` > [accordion] Migration failed (development) > > Reset only in local dev: > > ```bash theme={null} > cd api-events && npm run db:migrate:reset > npm run db:seed:demo-full > ``` ## Related * [Ledger payable dates](https://docs.soundchecklive.io/engineering/backend/ledger-payable-dates.md) — `LedgerItem.date` semantics, reschedule snap, leftover inventory * [Recurring pitfalls](https://docs.soundchecklive.io/engineering/reference/pitfalls.md) — applied Prisma migrations are checksummed This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.