Building an audit trail that survives an audit

In an earlier article I described what the regulation requires from an audit trail. This one is the part you actually write: what the data model looks like.

The difference between an audit trail that passes an audit and one that fails is not the volume of information. It is whether you can convincingly answer a single question: “how do we know this record was not modified afterwards?”

What should it actually capture?

The useful minimum for each entry:

• who — the user’s identity, not the application’s technical account;
• when — a timestamp from a single source, with an explicit time zone;
• what — the entity and its identifier;
• what changed — the old value and the new one, not just “was modified”;
• why, where it makes sense — the reason for the change, if the process requires it.

The critical part is “the old value”. An audit trail that only says “field X was modified at time Y” does not let you reconstruct state. And reconstruction is exactly what you will be asked for.

What are the three common patterns?

Pattern	How it works	When it is worth it
Shadow table	A parallel _audit table, written by a trigger or in the data-access layer	Simplest to add; good when only a few entities are regulated
Temporal tables	The database keeps row history automatically (system-versioned)	Low cost, history kept by the engine — but it captures neither who nor why
Event sourcing	Current state is derived from an immutable stream of events	Most powerful; high architectural cost — rarely justified for auditing alone

Watch the middle row, because it is the trap: temporal tables record SysStartTime/SysEndTime and nothing about the actor. To satisfy the “who” requirement above, you still need an identity column written on every modification path — exactly the fragile pattern I warn about below. On top of that, anyone with ALTER rights can switch versioning off, edit the history and switch it back on, so “guaranteed by the engine” is too strong without tight permissions.

In practice, for business systems, temporal tables usually give the best effort-to-benefit ratio — provided you add attribution, and the shadow table remains the pragmatic option when only part of the model is regulated. Event sourcing should be chosen for domain reasons, not to tick a compliance box.

How do you make it credible, not just complete?

An audit trail is credible if it is hard to falsify and visibly hard to falsify.

• Written by the system, not the application. Database-level triggers are harder to bypass than application code, where a developer can miss a path. They are not infallible, though: TRUNCATE, bulk loads and explicitly disabling the trigger all get around them — and under regulation the trigger is itself code that must be verified.
• No UPDATE, no DELETE. The account the application runs under should not have modify rights on audit tables. If it can change them, the default assumption at audit is that it might have.
• Time from a single source. The database server’s clock — not the application’s, and certainly not the client’s.
• Real identity. If everything is written by app_user, the audit trail attributes nothing. The user’s identity must be carried down to the layer that writes.
• Bound to the concrete record, through its stable key — not through free text.

Optional but valuable: hash chaining (each entry contains the hash of the previous one). It does not prevent modification, but it makes modification detectable — provided you anchor the chain outside the database (a periodically published digest, WORM storage). Without an anchor, anyone who can rewrite a row can recompute the entire chain, and detectability is zero.

What about personal data?

Here is the real tension: the regulation demands retention, while GDPR demands minimisation and, sometimes, erasure.

What works in practice:

• do not write more into the audit trail than you need — references, not full copies of sensitive data;
• redact secrets (passwords, keys, tokens) before writing, always;
• for personal data, consider pseudonymisation — the audit trail keeps an identifier, and the mapping to the person lives separately, with its own access control.

Worth knowing that GDPR provides exceptions to erasure where processing is necessary for compliance with a legal obligation (Art. 17(3)(b)) — precisely the regulated-retention case. There is no universal answer; it is a decision made together with legal. But if you wrote raw sensitive data into the audit trail from day one, your later options are very limited.

What does it cost in performance?

An audit trail roughly doubles write volume, often more — a shadow table holding old and new values, plus its own indexes, easily exceeds 2×. What helps:

• limit the scope — you do not audit everything, only what is regulated;
• do not audit derived columns or “last accessed” style fields;
• partition or archive by age, while keeping retrievability;
• index for the real queries — usually “the history of entity X” and “what user Y did in period Z”.

The last one is underrated: an audit trail you cannot query quickly is effectively unusable during an inspection.

The mistakes that cost the most

1. Audit trail bolted on after the model was built around destructive updates — the history before the change simply does not exist.
2. Writing from the application on every path but one. That one path is exactly what gets found.
3. Audit tables editable by the application account.
4. No time zone, or timestamps taken from different sources.
5. No tests. The audit trail is functionality; untested, it breaks silently at the first refactor.

If you are working on a system where you need to prove the history of your data and want a second opinion on the model, let’s talk.