Audit trails that hold up

There is a pattern we see in regulated systems that looks compliant and is not.

The application writes access events to an audit_log table. The table has good columns — actor, resource, action, timestamp. It is indexed. There is a dashboard. And the application's database user has UPDATE and DELETE on it.

That is not an audit trail. It is a log the audited party can rewrite.

The distinction matters exactly when the record matters: a subject access request, a regulator asking who viewed a file, a dispute about whether a record was altered. At that moment, "we have logs" is not the claim you need to make. The claim is "this record could not have been changed, and here is why."

What "could not have been changed" requires

Separate the write path from the application's privileges. The service that records events should not be the service that can remove them. In practice: a dedicated append-only store, or a table where the application role holds INSERT only and no UPDATE or DELETE grant exists. This is a database permission, not a code convention — code conventions are what change under deadline pressure.

Chain the entries. Each record includes a hash of its own contents plus the previous record's hash. Deleting or editing anything mid-chain breaks verification from that point forward. It does not make tampering impossible; it makes tampering detectable, which is the achievable property and the one that matters evidentially.

Record the read, not just the write. Most systems log mutations because mutations are what developers think of as events. In healthcare and finance, the question asked after the fact is almost always who looked at this. A trail with no read events cannot answer the question it exists for.

Capture enough context to be useful later. Actor identity, the specific resource, the action, a trustworthy timestamp, and the reason or access basis if your domain has one. "User 4471 read patient 20293" is a start; "under an active care relationship, from this session" is what closes an enquiry.

Two decisions that cause trouble later

Putting audit events in the operational database with everything else. It couples audit retention to the schema you migrate most often, and it puts audit writes in contention with production traffic. A separate store is easier to grant narrowly and easier to retain for years.

Deriving the timestamp from application servers. Clock drift across instances produces sequences that are provably wrong, which is worse than an approximate sequence — it invites the argument that the whole record is unreliable. Use the database's transaction time, from one clock.

The cost, honestly

Read auditing generates a lot of rows. A clinician opening twenty records in a shift produces twenty events, every shift, for every user, retained for years.

That is a real cost, and it is why this needs deciding at design time rather than after: the storage strategy, the retention period, and the partitioning that keeps queries usable at volume are all architectural. Retrofitting append-only guarantees onto a mutable table with three years of data in it is a migration project, not a patch.

A useful test

Ask the team a single question: if someone with production database access wanted to remove an audit entry, what would stop them, and how would you know?

If the answer is "nobody would do that," the trail is a convention. If the answer is "the role has no delete grant, and the hash chain would fail verification at that point," it is a control.

The first answer is what most systems have. The second is what regulated systems are asked for.