Skip to main content
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

​
Core models

​
Migrations

​
Apply migrations

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.
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, problem canvas, cohort alignment, active intervals, and availability semantics.

​
Seeding

Seed scripts live in api-events/scripts/seed/.

​
Seed modes

​
Run seeding

After demo-full, restart the Clerk emulator so personas appear in sign-in:
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

​
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: 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

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, 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. 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-<pool>, and the gig lifecycle lock as api-events-event-lifecycle-lock.

​
Prisma client

Generate after schema changes:
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 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

Ensure dev infra is running:
Reset only in local dev: