Skip to content

ADR-003 — PostgreSQL as the only stateful component, access through an isolated Drizzle ORM

Status

Accepted (2026-07-03, phase 3).

Amended 2026-10-09: §2 records the uuid v4 identifiers the code generates (randomUUID()), not v7; §4 drops the repository layer that was never built (the modules import drizzle-orm). §5 (audit immutability): enforced by the trigger of migration 0096_audit_log_immutable, not by SQL roles (the application connects as the owner role, so no GRANT or REVOKE could take the privileges away); account pseudonymization is not a Quiz requirement (NFR-07 is heig-classroom's and was not carried over; Quiz keeps data until the teacher deletes it, N-DATA-03), and issue #681, in the backlog, keeps its analysis.

Context

The platform is the source of truth for accounts, roster, assignment configuration, API keys, audit and frozen grades (C-01); GitHub is the source of truth for Git content and runs. We need transactions (atomic CSV import AU-14), uniqueness constraints as the idempotency mechanism (NFR-09), revocable sessions (AU-06), a durable job queue and a simple backup (NFR-16: RPO 24 h, RTO 4 h).

Decision

  1. PostgreSQL 17 is the only stateful component: business data, sessions (hashed), the pg-boss job queue, webhook deduplication, audit. A single pg_dump covers the whole NFR-16 scope.
  2. timestamptz timestamps in UTC everywhere, converted to Europe/Zurich for display (C-02); uuid v4 primary keys, generated by the application (randomUUID()).
  3. UNIQUE constraints are the idempotency mechanism: every replay ends in ON CONFLICT DO NOTHING, never in a duplicate.
  4. Access through Drizzle ORM + drizzle-kit (close to SQL, versioned SQL migrations, run at startup under a lock), with pinned versions. The modules use Drizzle directly; there is no repository layer. The pre-1.0 risk of Drizzle is contained by the migrations being raw SQL files, independent of the ORM's API, and by the pure rules living in packages/domain, which never touches the database.
  5. Audit immutability at the database level: the trigger audit_log_immutable (migration 0096_audit_log_immutable) refuses every UPDATE and DELETE statement on audit_log, whatever the role (NFR-05); INSERT and SELECT are untouched. It guards against the application and against mistakes, not against an owner who drops the trigger; TRUNCATE fires no such trigger and nothing truncates the table. Quiz has no account pseudonymization routine (issue #681, backlog); if one is ever specified, it decides whether it must rewrite identity fields here and how it passes the trigger. (Scoped amendment 2026-10-11: the staging copy is pseudonymized on a scratch database, the trigger lifted there only, never on production's record: ADR-028, Update 2026-10-11.)

Consequences

  • A single brick to back up, monitor and restore; the restore runbook fits on one page and the semester test validates the RTO.
  • When a job misbehaves, diagnosis happens in SQL directly on the pg-boss tables — no opaque layer between the maintainer and their data.
  • Upgrading Drizzle is a targeted piece of work, never a blocker: the migrations are raw SQL files, independent of the ORM API.

Rejected alternatives

  1. Prisma (productivity proposal): productive, but adds a binary engine and a generation chain between the maintainer and their SQL; when a query locks, you read SQL, not Prisma.
  2. Redis as a second stateful component (sessions or queue): one more moving part, one more backup and one more failure mode, with no need justified by an NFR (see ADR-004).
  3. JWT sessions: AU-06 requires server-side invalidation; a sessions table (hashed token) is enough and avoids any token revocation machinery.

Addendum (2026-09-23): the audit log is read with psql

audit_log is append-only forensics. No route of the application reads it, and none is planned until an admin screen needs one: the maintainer reads it with psql on the VM when something has to be explained after the fact. Two indexes serve the two questions asked there, and nothing else (migration 0009_audit_log_indexes, audit D-15):

  • audit_log_created_idx (created_at DESC) — "what happened lately";
  • audit_log_subject_idx (subject_type, subject_id) — "what happened to this evaluation, this attempt, this user".

An admin read route, when it comes, is a new decision: it must go through staffAccess (or an admin check) like every other read and must not weaken the §5 trigger.