Why do mutable audit tables fail under scrutiny?
Most systems have an audit table. Most of those tables are ordinary tables. The application writes audit rows with the same database role it uses for everything else, which means the same credentials that record an event can rewrite it. Under normal operation nobody notices. Under scrutiny it is the first thing that falls apart.
The failure is structural, not moral. An audit trail is evidence, and evidence is only worth something if the party producing it could not have altered it. When the application role holds UPDATE and DELETE on the audit table, any compromise of the application, any engineer with production access, any migration script with a careless WHERE clause can change history. An assessor does not have to prove that happened. They only have to observe that it could, and at that point the trail stops being proof and becomes an assertion.
The patterns that fail are familiar. Audit rows written by the ORM under full data-manipulation rights. Soft-delete flags that make records vanish from every query. Logging left to the application layer, so the one code path that forgets to log leaves no trace at all. Audit data living beside business state, so the fix-up script that repairs the data quietly repairs the record of what the data used to say.
What makes a ledger append-only?
The property has to be enforced where the data lives, at the database, not in the code that writes it. Code changes weekly. Grants do not change unless someone changes them, and that change is itself visible.
Concretely, in PostgreSQL terms, though the pattern ports anywhere: the ledger sits in its own schema. The application role is granted INSERT and SELECT and nothing else. UPDATE, DELETE, and TRUNCATE are explicitly revoked, so there is no path through the grant structure by which the running system can modify or remove a row once it is written. The role that runs schema migrations is a different role from the one that serves traffic, so a deploy cannot casually reach the ledger either.
Grants get a second layer: a trigger on the ledger that fires before any UPDATE or DELETE and raises an exception unconditionally. The grants stop the application. The trigger stops a privileged session, a future migration, or an operator working from muscle memory. Both layers fail loudly, which is the point. A blocked write that errors is a control working; a blocked write that silently no-ops is a bug waiting for an incident review.
The last piece is where the entry gets written. The ledger row commits in the same transaction as the state change it records, so the change and its evidence land together or not at all. There is no log-shipping job that can fall behind and no code path that mutates state without emitting an entry. That is what machine-enforced on every state change means in the portal we built and operate for GRM LAW, a Brisbane law firm: no state change lands without its ledger entry, and the enforcement is a property of the system, not a discipline the code is asked to remember.
What should every entry capture?
Three things, at minimum: actor, action, and the before and after state. The compliance audit log we built for Stone Leaf Capital, an Australian capital-markets firm, captures exactly that on every change, structured around the firm's AFSL perimeter and its retention obligations.
Actor means the authenticated principal, a named person, never a pooled service account. A ledger where every row says the application did it answers nothing. Action means the operation in domain language: a conflict check cleared, a matter opened, a policy acknowledged. Row updated is not an action; it is an implementation detail, and an assessor will say so.
Before and after state is the part teams most often skip and most often regret. A log that says a field changed, without recording what it changed from, forces you to reconstruct history through backup archaeology, restoring snapshots and diffing them under time pressure. Recording the full prior state and the full new state, structured, means any question of the form what did this record say before Tuesday is answered by reading one row. Add the database's own clock for the timestamp and the identity of the affected record, and each entry stands on its own.
Machine enforcement beats the policy document
A policy says staff must not alter records. A grant structure takes that ability away from every role the running system holds. Those are not the same class of control, and an assessor weighs them accordingly: one is a promise about behaviour, the other is a property of the system that can be demonstrated on the spot.
Policy controls depend on people reading the policy, remembering it, and complying with it under pressure. Machine controls hold at three in the morning during an incident. They hold against the contractor who never read the wiki, and against the well-meaning engineer trying to tidy up after a mistake. That last case matters more than the malicious one, because it is far more common: most history gets rewritten by someone trying to help.
The enforcement also protects the honest operator. When a client or a regulator asks what happened, the firm answers from a record whose protections it can demonstrate on the spot rather than assert. That is the same reasoning behind the rest of how we work, least-privilege access and claims a buyer can verify rather than take on faith; our approach and trust pages set that posture out in full.
What does an assessor actually ask for?
The questions are concrete. Who can change this data: show me the grants. What happens if someone tries: run the UPDATE and let me watch it refuse. Show me the entry for this specific change: who made it, when, what the record said before and what it says now. How do I know an entry cannot be removed or inserted after the fact. How long is this retained, and does that match the obligation.
The strong answers are demonstrations, not documents. Pull the grant list live. Attempt the revoked write and read the error. Open the ledger entry and walk through the before and after state. A binder of policy PDFs answers the same questions in the weakest available form, and an experienced assessor treats it that way.
The obligations converge on the same shape from different directions. A law firm preparing for AML/CTF obligations needs to show its program is operating, not just written, which means evidence of who did what and when. An AFSL holder carries record-keeping obligations that run for years and a regulator entitled to test them. In both cases the underlying question is identical: prove the record, and prove nobody could have quietly edited it.
Where we run this in production
This is not a reference architecture we read about. The operations and compliance portal we built and run for GRM LAW carries intake, conflicts checks, a matter register, and AML/CTF readiness over an append-only audit ledger, machine-enforced on every state change. The staff portal we built for Stone Leaf Capital carries critical-event tracking and policy modules over a compliance audit log capturing actor, action, and before and after state on every change. Aurii, our own clinical-software venture, runs tamper-evident audit trails over tenant health data on Azure in Australia. Legal, clinical, and capital markets: three regulated settings where the audit trail has to hold.
If your audit table can be edited by the same credentials that write your business data, that is a finding, and it is cheaper to fix before an assessor writes it down.