Postgres schema lessons from a GovTech AMS

Append-only audits

For the DVLA asset system, the audit table has no UPDATE or DELETE path — every action inserts a row with actor, action, document id, timestamp, and a hash of the previous row. Reporting and forensics read one ordered log instead of reconstructing history from mutable state.

It costs storage. It repays in trust: when a supervisor asks who touched a sensitive driver record, the answer is a query, not an investigation.

Role scopes per document class

Clerk, supervisor, admin is not enough — each document class carries its own allowed scopes. A clerk who can register vehicles still cannot open restricted identity documents. Scopes live beside the document definition so access reviews read like a table, not a code search.

Reporting without N+1 hell

Asset to document to user is a graph that explodes under naive ORM loading. I used selectin loading for list endpoints, composite indexes on (status, assignee), and separate read shapes for dashboards versus detail views. The dashboard query plan, not the row count, is what I review in PRs.