An audit trail you can edit is not evidence

The question always comes at the same moment: afterwards. A price changed on an order that was already confirmed, a document marked as sent that never left, a customer record edited by someone who shouldn’t have. “Who did this?” The system usually gives an answer. The question is whether that answer is worth anything.

A log is for diagnosing, a record is for proving

These are two different things that often end up in the same table. The technical log exists for developers: it’s verbose, it rotates every week, it gets cleared when the disk fills up, and nobody is scandalised if someone touches it. The record of operations exists for the business: it describes facts — who, what, when, what it was before and what it is now — and its entire value rests on the fact that it cannot be reworked.

Confusing the two is convenient right up until it isn’t. The day it matters, the cost arrives all at once: a dispute with a customer, an inspection, an incident to reconstruct. And reconstructing what happened from backups and application logs takes weeks, only to produce an answer nobody is willing to sign off on.

The test is trivial: who can change it?

If a row can be updated or deleted — by the application, by an administrator, or by a direct query against the database — then that record describes what happened only for as long as nobody has an interest in the contrary. Three properties turn it into something defensible, and none of the three is expensive if decided up front:

• Append only. Writes add, never update. A correction is a new event that amends an earlier one, not a replacement: both the error and the amendment stay visible.
• A verifiable chain. Each event carries a fingerprint of the one before it. Removing or altering a row breaks the chain from that point on, and tampering becomes visible rather than invisible.
• Writing separated from reading. Whoever uses the application must have no way of writing to the record other than by producing the event. If direct database access bypasses the mechanism, the mechanism doesn’t exist.

What to actually record

The useful list is shorter than it looks, but it has two entries that are almost always missing. The first is denials: a rejected attempt is a security signal, and a record that only notes successes tells half the story. The second is effective identity. In integrations, operations arrive from a technical user, and tracking that is equivalent to tracking nothing: you need the human actor, or the calling system, behind the request — propagated all the way to the write.

The rest is obvious, and needs doing properly anyway: value before and after, timestamp with an explicit time zone, reference to the entity touched.

This is not a twelve-month project

You don’t need to track everything — in fact, tracking everything is the most reliable way to end up with a record nobody consults. What you need is the short list of operations that someone will, one day, ask you to account for: amounts, authorisations, data sent outside, deletions. Thirty well-chosen, unalterable events are worth more than complete tracking that can be rewritten.