Correct by construction: data models that survive an audit

Most schemas are designed against the queries a product needs this quarter. That is a reasonable default, and it is why so many platforms discover, somewhere around the first serious audit, that their data can answer what the product asks but not what a regulator asks.

The two question sets are genuinely different. A product asks what is true now. An auditor asks what was true then, who changed it, and how do you know. A schema optimised only for the first will answer the second slowly, approximately, or not at all.

Current state is a projection, not the record

The single most consequential decision is whether the row you update is the record or a view of it. If a table holds only the latest values, every previous state is gone, and reconstructing history means mining application logs that were never designed as evidence.

Treating change as the primary artefact — and current state as something derived from it — costs more up front and settles the question permanently. It also tends to make the product better, because the history becomes queryable rather than merely retained.

Design for the questions, not just the reports

“Audit-ready” is often interpreted as an export: some scheduled job that produces a CSV nobody reads until it matters. That misses the point. The requirement is not a report, it is the ability to answer arbitrary retrospective questions with confidence, including ones nobody anticipated.

• Every material change is attributable. Actor, timestamp, and the reason it happened, captured as data rather than inferred from a log line.
• Deletion is a state, not an absence.Rows that vanish take their history with them, and “we no longer have that” is a poor answer to a regulator.
• Schema changes are part of the record. A field that changed meaning in March makes every comparison across March wrong unless the change itself is captured.

Governance systems don't get second chances. The platform had to be correct by construction, and stay that way as the regulatory ground kept moving.

The throughput objection

The usual pushback is that all of this costs performance, and at low volume it is easy to wave away. At tens of millions of events a day it is a real design constraint: append-only tables grow fast, and a naive implementation turns every write into several.

It is also mostly a solved problem, provided you decide early. Partition on the axis you will actually query, usually time. Index for the retrospective questions rather than only the product ones. Keep the hot path narrow and let the audit path be a different set of reads with different latency expectations, because an auditor does not need a 50-millisecond response.

The expensive version is not the one that keeps history. It is the one that adds history to a schema that assumed it would never need any, after the data is already large.

Correct by construction means the wrong state is unrepresentable

The phrase gets used loosely, so it is worth being concrete. It does not mean validated on write. It means the shape of the data does not permit the invalid case to exist in the first place.

A status field that is a free-form string will eventually contain a typo; one constrained to an enumerated set will not. A relationship enforced by application code will eventually be violated by a migration script; one enforced by a foreign key will not. A monetary amount stored as a float will eventually disagree with itself; stored as numeric it will not.

Each of these is unremarkable in isolation. The compounding effect is that after a few years the platform has no reconciliation problem, because the states requiring reconciliation were never expressible.

The test

A useful exercise, and one that takes an afternoon: pick a record that changed six months ago and reconstruct, from the database alone, exactly what it looked like before the change, who changed it, and why. If that requires reading application logs, joining across systems, or asking an engineer to remember something, the schema is not audit-ready yet.

Better to find that out on a quiet afternoon than during diligence.