Data model

Prisma 7 on PostgreSQL 17. Schema at prisma/schema.prisma, about 1,090 lines, 40 models and 25 enums.

Where the database is

Each environment's box runs its own postgres:17 container. It publishes port 5432 on loopback only: the app reaches it over the Compose network, and nothing off the box can open a connection at all. Administrative access is an SSH tunnel.

Backups are deploy/backup-db.sh: an hourly pg_dump in custom format to that environment's S3 bucket, kept for 30 days. Production also has a daily Lightsail snapshot.

Prisma 7 specifics

The generator is prisma-client, not prisma-client-js, and it outputs to src/generated/prisma/ rather than node_modules. That directory is gitignored; postinstall regenerates it.

The datasource block has no url. Configuration lives in prisma.config.ts, which reads process.env.DIRECT_URL directly rather than through Prisma's env(). That is what lets prisma generate succeed with no database, so CI and the Docker build need no credentials.

Runtime uses a driver adapter: @prisma/adapter-pg over a pg pool, in src/lib/prisma.ts. Pool size comes from DATABASE_POOL_MAX, default 15; the boxes set 5 against Postgres's max_connections=50.

The model groups

Enums worth knowing

Role            ADMIN MAINTAINER MEMBER AUTHOR REGISTERER SUB_MAINTAINER
AppStatus       REGISTERED ALLOTTED PAYMENT_SENT CONFIRMED WAITLISTED CANCELLED
PayStatus       PENDING SENT PAID FAILED COMPED OFFLINE
CandidateStage  INTAKE GD_PENDING GD_ACTIVE GD_COMPLETE GD_BYPASSED
                PI_PENDING PI_ACTIVE PI_COMPLETE DECISION CLOSED
CandidateResult PENDING ON_HOLD SELECTED REJECTED WITHDRAWN DISQUALIFIED
CycleState      DRAFT OPEN IN_PROGRESS PAUSED FINALISATION COMPLETED ARCHIVED CANCELLED

CandidateStage and CandidateResult are separate on purpose: where someone is in the process and what was decided about them are different facts, and the result is stored, never derived.

Integrity that lives in the database

Some invariants are constraints rather than application checks, because application checks lose races.

• RecruitmentCandidateLock has the candidate id as its primary key, so a candidate cannot be in two live sessions.
• RecruitmentAuditEvent has a trigger that refuses UPDATE. Append-only is enforced by Postgres, not by convention.
• Response is unique on session, slide and nickname, so a quiz answer cannot be scored twice.
• RecruitmentEvaluation carries a unique idempotency key.
• Delegate.email is unique; a duplicate surfaces as P2002 and becomes a readable message.

Concurrency patterns

Optimistic locking. RecruitmentSession has a version; mutations pass expectedVersion.

Serializable transactions. Registration re-checks that intake is open inside the transaction that creates the row. Admin role changes run serializable to guarantee an admin survives.

Soft holds. A portfolio is held with a token for two minutes during allotment.

Migrations

Twenty-one so far. Never edit an applied one; add a new one.

npm run db:deploy    # apply existing migrations
npm run db:status    # show state

prisma migrate dev is interactive, so it will not run from a script or an agent. To produce a migration non-interactively, generate the SQL and apply it:

npx prisma migrate diff --from-config-datasource --to-schema prisma/schema.prisma --script

Staging applies migrations automatically; production is manual through a tunnel, and must go before the code that needs it. See CI and deployment.