Audit as a schema feature

Auditability usually arrives late. The system ships, and somewhere near the end someone adds a logging table — a generic events sink that records that something happened, when, and roughly to what. It feels responsible. It survives until the first time a real person asks a precise historical question, and then it falls apart, because logging tells you an event occurred but not what the world looked like when it did.

The question that breaks the bolt-on approach is always some version of: what did this thing look like at the moment it was decided? What did this payslip show the day it was approved? Which leave rule was in force when this request was filed? If your answer is “let me recompute it from the current rules and hope nothing changed in between,” you do not have an audit trail. You have a guess with a timestamp.

I learned to treat audit as a schema feature — not a side log — building an internal-ops platform I built for a Vienna-based international organization. It is an internal-ops ERP: about a dozen back-office modules — timesheets, leave, payroll preparation, invoicing, IT assets, and more — under one single sign-on, one PostgreSQL database, a Next.js monolith with hand-written parameterized SQL and no ORM, on Azure App Service, maintained largely by one developer. The kind of system where money, approvals, and a future auditor all converge. The pattern below is what kept it answerable.

1. Freeze the answer, don’t recompute it

The most disputed records in any internal system are the derived ones — the payslip, the cost report, the invoice line — because they are computed from rules and rates that change over time. Recompute one a year later and you get today’s answer to yesterday’s question. So on approval the system serialises the fully-rendered derived state — the whole payslip, every line, every figure as displayed — into an immutable JSONB snapshot row. The write is deliberately defensive:

• Immutable on first issue. The insert uses INSERT ... ON CONFLICT (entry_id) DO NOTHING, so the version captured the first time something is issued is the version that stands. Re-issue it later and the original snapshot is untouched — the row that exists is the one from the moment it first mattered.
• A snapshot means “approved.” Per-period cost snapshots are written on approval and deleted on reopen. The presence of a snapshot row is itself a fact: it exists only for approved state. There is no ambiguous half-snapshot for something still in flight.
• Rule changes are versioned in the same breath. When a rule or rate changes, the change and a row in its *_history table are written inside the same transaction. You can never end up with a changed rate and no record of what it used to be, because the two facts commit together or not at all.

The result: “what did this payslip look like the day it was approved?” is a single read against the snapshot row. No recomputation, no reconstructing the rule set from memory, no caveats.

2. Guarded state machines, append-only audit

Every approval in the system is a state transition, and every state transition is a place something can go wrong — a stage skipped, a step taken by the wrong role, a record moved on stale information. The defence is to make the workflow explicit and to never trust the client about where a record currently is.

Each workflow lives in one service with an explicit transition table — a plain Record<status, Record<role, status[]>> that says, for a given current status and a given actor role, which next statuses are legal. A timesheet moves Staff → Supervisor → HR; a payroll run walks a longer multi-stage chain. The table is the single source of truth for what is allowed, and it reads like a specification because it is one.

The part that actually holds the line is duller: the source status is re-read and re-validated inside the mutation. The client may say “move this from Supervisor-approved to HR-approved,” but the service re-reads the row, confirms it is genuinely in Supervisor-approved now, checks the actor’s role against the transition table, and only then writes. A stale tab, a replayed request, a hand-crafted payload — none of them can push a record through a transition that the database doesn’t currently permit.

And every transition that succeeds writes an append-only audit row: actor, action, old status, new status, timestamp. Not “something changed” — the specific before, the specific after, and who. Asking “how did this record reach its current state, and who moved it at each step?” becomes an ordered read of that table. The audit trail isn’t a parallel system you maintain; it is a byproduct of every legal move.

3. One queryable place for a cross-module invariant

The hardest truths to keep honest are the ones that span modules. Leave and timesheets are separate parts of the system, but they share a fact: you cannot cancel a day of leave that has already been baked into a submitted timesheet. If the two modules each enforce their own version of that rule, they will drift, and the gap between them is where the silent inconsistency lives.

So the invariant lives in one guard, computed one way. The tricky part is months. Leave can span a month boundary, and a timesheet is a monthly artifact, so “does this leave overlap a submitted timesheet?” has to be checked against every month the leave touches, not just the month it starts in. The guard collapses that to a contiguous month index — year * 12 + month — so a stretch of leave becomes a simple integer range, and a boundary-spanning request is checked against each month in that range. No off-by-one at year-end, no December/January blind spot.

• Date math on real date columns. All of it runs against PostgreSQL date columns, not application-side timestamps, so a timezone offset can never silently shift a day across a boundary.
• One place to read, one place to fix. Because the invariant is computed in a single guard, “is this cancellation allowed?” has exactly one answer, and a future change to the rule has exactly one home.
• Human-readable refusals. When the guard blocks, the message says which month and which submitted timesheet stand in the way — so the person hits an explanation, not a stack trace.

4. The honest tradeoff: duplicate on purpose

Snapshotting is not free, and it is not always right. Freezing a fully-rendered payslip into a JSONB row duplicates data that you could, in principle, recompute — you pay in storage, and in the mild discomfort of holding the same figures in two places. The orthodox instinct — never store what you can derive — pushes hard against it.

The dull-but-right call is to duplicate anyway, because the alternative is worse in exactly the way that matters. Recomputing historical money over rules that have since changed is how you get silent, unauditable drift: a number that was correct when it was issued and is quietly wrong every time you regenerate it, with nothing in the system to tell you it moved. Storage is the cheapest thing in the building. A figure you cannot defend to an auditor is one of the most expensive.

So the rule is not “snapshot everything.” It is: snapshot the things someone will dispute later. Money. Approvals. Anything a stakeholder, an auditor, or a court might one day ask you to reconstruct as it was. For those, duplicate without apology. For everything else — a draft, a filter, a value with no consequence past today — recompute freely and keep the schema lean. The skill is not snapshotting; it is knowing which records have a future argument attached to them.

The pattern, reused

Strip away the specifics and the pattern is portable to any system where decisions have consequences. Capture derived state at the moment of decision rather than promising to recompute it. Make state transitions explicit, re-validate them server-side against the live row, and write an append-only trail of every legal move. Put cross-module invariants in one guarded place, computed one way, on storage-native types. And duplicate deliberately wherever a record might be disputed, because the storage is cheap and the dispute is not.

None of this is clever. There is no new database, no event-sourcing framework, no audit-as-a-service vendor. It is a handful of dull decisions — written down, enforced in the schema, paid for in a little extra storage — made early, before anyone asked the question they answer. That is the whole of it: the dull, written-down, auditable version is the one that survives the question you can’t predict. The flashy system that recomputes on demand looks identical in the demo and falls silent the first time someone needs to know exactly what was true on a Tuesday last March.