Database

PostgreSQL through Prisma. prisma/schema.prisma is the single source of truth — not a migration file, not a diagram, not a document.

Model conventions

Convention	Rule
Primary keys	`cuid()`.
Timestamps	Every model has `createdAt` and `updatedAt`.
Indexes	Index foreign keys and anything used in a `WHERE`.
Money	`Decimal`, never `Float`. See ADR-007.
Enums	Prefer an enum over a free-text status column.

The migration workflow

Development uses db push for speed; staging and production use prisma migrate deploy, which only applies migration files. Both schema.prisma and a migration file must be committed for every schema change.

Edit the schema

prisma/schema.prisma.

Sync your local database

docker compose exec app npx prisma db push

Develop and test against it

Generate the migration before committing

docker compose exec app npm run db:migrate:create -- describe_your_change

Review the generated SQL

In prisma/migrations/<timestamp>_describe_your_change/migration.sql.

Split enum additions

If the migration contains ALTER TYPE … ADD VALUE, put the enum change in its own preceding migration. PostgreSQL will not let a new enum value be used in the same transaction that adds it.

Check for duplicates

If the column or table already exists in a prior migration, do not create a second one. Look in prisma/migrations/ first.

Commit both

CI verifies that migrations are in sync with the schema and blocks a PR with drift.

Never run prisma migrate dev without --create-only in Docker — it can drop your development database.

Transactions

Conditional logic requires the interactive callback form:

// WRONG — the batch form cannot branch
await db.$transaction([db.a.create(...), db.b.update(...)]);

// CORRECT
await db.$transaction(async (tx) => {
  const a = await tx.a.create(...);
  if (a.requiresB) await tx.b.update(...);
});

The batch array form evaluates its arguments before the transaction opens, so a condition depending on an earlier statement’s result is evaluated against stale data.

Never do these

Anti-pattern	Why
`as any` to silence a Prisma type error	The query is wrong; the cast hides it until runtime.
`JSON.parse()` without try/catch	Malformed JSON from the database crashes the process.
Raw SQL with string interpolation	Injection. Use parameterised Prisma queries.
Unbounded `findMany` in an API route	Blocked in CI. Every one needs `take` or `cursor`.
A plaintext credential column	Encrypt at the application layer with the Key Vault utilities.

Encryption coverage

A CI guard checks that fields holding credentials, tokens or secrets go through the application-layer encryption utilities. A field storing a raw token as String? fails the guard unless it is explicitly approved and documented as a known limitation with a follow-up ticket.

The guards

Command	Enforces
`check:findmany-bounds`	Every `findMany` in an API route is bounded.
`check:encryption-coverage`	Sensitive fields are encrypted.
`check:audit-coverage`	State-changing routes write an audit entry.
`db:migrate:check`	The database matches the schema.

All of them run in npm run check:all, which the quality gate runs.

Seeding

docker compose exec app npm run db:seed        # baseline reference data
docker compose exec app npm run db:seed-demo   # demo content
docker compose exec app npm run db:unseed-demo # remove it again

Demo seeding is reversible by design — it is used on real installations for training, so it has to come back out cleanly.

Getting set up

Conventions

Process

Model conventions

The migration workflow

Transactions

Never do these

Encryption coverage

The guards

Seeding

​Model conventions

​The migration workflow

​Transactions

​Never do these

​Encryption coverage

​The guards

​Seeding

Model conventions

The migration workflow

Transactions

Never do these

Encryption coverage

The guards

Seeding