The Excuse Is Also in the Chain
Every audit row is signed over its own content and the signature of the row before it, so an edit, a deletion or an insertion all stop the sequence matching. The interesting part is not the chain. It is that a legitimate deletion has to be explained by a record which is itself in the chain.
An audit trail that can be edited by whoever is being audited is a filing cabinet, not a control.
This is the awkward fact underneath most audit logging. The log is a table in the same database as everything else. Anyone with write access to that database — a database administrator, a compromised application account, anyone who has got in far enough to matter — can change a row, remove a row, or add one that describes something that never happened. And the resulting table looks exactly like a table nobody has touched.
You cannot stop that from inside the database. What you can do is make it impossible to do quietly.
What a chain actually does
Each audit row carries a signature computed over two things: the content of that row, and the signature of the row immediately before it. The rows are numbered in sequence. That arrangement makes each of the three ways of tampering visible, and it makes them visible in three different ways.
| What was done | What stops matching | What it is reported as |
|---|---|---|
| A row was edited | Its own signature no longer matches its content | Hash mismatch |
| A row was deleted | The next row's reference to its predecessor points at nothing that exists | Broken link |
| A row was inserted | The sequence numbers collide or skip | Sequence gap |
| The tail was re-signed to hide one of the above | Nothing — but re-signing needs the signing key | Not possible from the database alone |
That last row is the one that makes the arrangement worth anything. Chaining alone would be defeated by simply recalculating every signature after the change, which is arithmetic anybody can do. What stops it is that the signature is not a plain hash of the content but a keyed one, and the key is derived from the application's own secret. It lives in the deployment configuration, not in the database somebody just got into.
It makes tampering detectable, not impossible. The deletion can still happen. It cannot be made to look like it never happened.
That distinction is worth insisting on, because the marketing version of this feature always overstates it. Nothing here prevents anybody from doing anything. What it produces is a verification that comes back red, and a red verification is a fact somebody has to explain.
The problem this creates, which is the interesting one
Now the difficulty that makes tamper-evidence hard to build properly rather than merely hard to describe.
Audit logs are deleted legitimately, all the time. A retention policy removes rows older than its window, because keeping a permanent record of everything anybody ever did is itself a liability. So the oldest rows go, on a schedule, on purpose.
To the chain, that is a sequence gap. Exactly the same signal as somebody removing the row that recorded them changing a payment destination.
The naive fix is to keep a list of ranges that were purged legitimately, and treat gaps inside those ranges as explained. Which works, and introduces a hole so obvious it is almost funny: anybody who can delete an audit row can also add a note saying the deletion was a retention purge.
A purge cannot be invented after the fact to excuse a gap without breaking the chain at the point the excuse was inserted.
The record of the purge is itself an audit row, in the same chain, with its own signature bound to its own predecessor. So forging an excuse means inserting a row into the chain — and inserting a row into the chain is the third kind of tampering, detected by the same verification, at the position where the excuse was placed.
The excuse and the thing it excuses are held to the same standard. That is the whole design, and it is the part most implementations miss.
Three things the verification counts rather than complains about
A verification that reports everything unusual as a problem gets ignored within a month. Three situations look like damage and are not, and each is handled deliberately.
-
Rows written before chaining existed
The columns were added to a table that already held audit rows, so those rows have no signature and never will. They are counted and reported as unchained rather than flagged, because an honest number — "this many of your rows predate the protection" — is more useful than a wall of red. There is a backfill for them, and the count is what tells you whether it has been run.
-
A chain that does not start at one
Retention purges take from the oldest end, so the earliest surviving row inevitably refers to a predecessor that is gone. That is normal, not a break. The first surviving row is accepted as an anchor and its position reported, rather than being flagged every single time a retention policy does its job.
-
Rows the retention policy anonymised
Some retention rules redact a row rather than removing it — the event stays, the personal details go. Counted separately. And the field that records when redaction happened is deliberately left out of the signature, because redaction is not a change to what happened.
One of these was nearly a silent false negative
Rows with no sequence number are counted with their own separate query, and the reason is worth knowing. The verification walks the chain by sequence number, so a row without one can never appear in that walk. Counting unchained rows from inside the walk would therefore have reported zero of them however many existed — handing back a clean result for a trail that was largely unprotected. A monitoring check that cannot see what it is missing reports success, which is the worst failure a verification can have.
Two decisions that are easy to get wrong
The chain is per workspace rather than one chain for everybody. Two reasons, and both are structural. One organisation must be able to hand its own audit export to an auditor and have it verify on its own — a shared chain would mean the missing links are other people's rows, which both breaks the verification and discloses that those rows exist. And with one global chain, any organisation's retention purge would tear a gap through everybody else's.
The write has to be serialised. Two requests arriving at the same instant both read the current last sequence number, both add one, and both write the same position. That is a fork in the chain, and it looks like tampering permanently afterwards — a self-inflicted red verification caused by nothing worse than ordinary traffic. So the read of the previous row and the write of the new one happen inside a transaction with a lock, per workspace. The cost is one indexed lookup and one insert while holding it, against audit writes that already happen about once per request.
The field-order contract
Seventeen fields go into each signature, in a fixed order: who acted and under what identity, what they did, in which module, by which route and method, at which URL, from which address and browser and session, on which record, with what description and metadata, and when.
The order is part of the contract. Append a field to the end and old signatures still verify. Reorder or remove one and every signature ever written becomes invalid at once — the next verification turns the entire table red, and the table is now indistinguishable from one that was genuinely tampered with. It is the single most destructive change available in that file and it looks like tidying.
Two fields are deliberately absent, and both absences are load-bearing. The row's own identifier is not signed, because it does not exist yet at the moment the signature is computed — the sequence number carries position instead. And the last-modified timestamp is not signed, because redaction touches it and redaction is not a change to the content.
The subtlest one: sign what is stored, not what was passed
The workspace identifier on an audit row is set by a trait as the row is created, which can override whatever the caller supplied. Signing the caller's value would produce a signature over a value that never reached the database — every row would verify as mismatched, immediately, for a reason nobody would find quickly. The same applies to the timestamp, which is fixed at the point of signing rather than left to the model, so the instant signed is the instant stored, to the second.
What to ask, if you have to prove this to somebody
Audit trails get taken seriously in exactly one situation: a dispute. Somebody says a figure was changed, or a payment was redirected, or an approval was granted that should not have been. What decides it is not whether you have a log — everybody has a log — but whether the log can be shown not to have been edited since.
Five questions for any system claiming an audit trail
Can somebody with database access edit an audit row?
A good answer sounds like
Yes, and here is how you would find out.
What ours actually is
Yes, and the row's signature stops matching. Be suspicious of a flat no — it usually means the question has not been thought about, since the answer is yes for almost every system that keeps its log in its own database.
What stops somebody re-signing the log after changing it?
A good answer sounds like
A key held outside the database.
What ours actually is
The signature is keyed on the application secret, which lives in deployment configuration rather than in the database. This is the question that separates a real chain from a decorative one.
How do you tell a retention purge from a deletion?
A good answer sounds like
A record of the purge that is itself protected.
What ours actually is
The purge writes its own chained audit row naming the range it removed, so inventing an excuse means inserting a row and breaking the chain where the excuse sits.
Can I verify my own export without your help?
A good answer sounds like
Yes, the chain is scoped to us.
What ours actually is
Yes — the chain is per workspace precisely so an export stands on its own.
How many of my rows are actually covered?
A good answer sounds like
A number, not a yes.
What ours actually is
A count of unchained rows is part of every verification, because the protection was added to a table that already had history in it. Ask for the number: "we have tamper-evident logging" is compatible with almost none of your rows being covered.
What AWRA OpsHub does today
- A keyed signature on every audit row, computed over seventeen fields of its content bound to the signature of the preceding row.
- A signing key derived from the application secret and held in deployment configuration rather than in the database the log lives in.
- Detection of all three tampering shapes: an edited row, a removed row and an inserted row, each reported distinctly rather than as one undifferentiated failure.
- A chain scoped per workspace, so an export verifies on its own without reference to any other organisation's rows.
- Serialised appends under a per-workspace lock inside a transaction, so concurrent requests cannot fork the chain and produce a permanent false positive.
- The signed workspace identifier and timestamp taken from the values that actually land in the row, rather than from what the caller passed.
- Retention purges recorded as chained audit rows naming the sequence range they removed, so the explanation for a gap is held to the same standard as the gap.
- Rows predating the protection counted as unchained rather than flagged, with a separate query so the count cannot silently report zero.
- A chain whose first surviving row is treated as an anchor, so an ordinary retention purge does not read as damage.
- Redacted rows counted separately, with the redaction timestamp excluded from the signature because redaction is not a content change.
- Keyset paging through the chain during verification, so the walk is bounded on a large trail.
- A backfill that signs rows written before the protection existed.
More we can add to your workspace
- A periodic verification that runs on its own and reports its result, so a red chain announces itself rather than waiting for somebody to run a check.
- An external anchor for the chain — the current head signature published somewhere outside the deployment at intervals, which is what would close the one remaining hole: an attacker holding both the database and the application secret could otherwise rebuild a consistent chain.
- A workspace-facing verification report, so an organisation can satisfy itself rather than asking us.
- A signed export format an auditor can verify independently, with the fields, the order and the algorithm documented alongside the data.
- Chaining for the other evidence tables, so the same protection covers the support-access trail and the webhook record rather than the general audit log alone.
- A named difference report on a hash mismatch, showing which of the seventeen fields no longer agrees with the signature.
Where we point you to a specialist
- We hold that tamper-evidence is evidence and not prevention, and we will keep describing it that way. A vendor telling you their audit log cannot be altered is describing something no system keeping its log in its own database can offer, and we would rather be less impressive and accurate.
- Where a regulator specifies a form of immutable record — a write-once store, a notarised timestamp, an external ledger — that requirement governs and we will implement it rather than offering the chain as an equivalent. Which regime applies to you is a question for your own advisers.
- We would decline to hold the signing key anywhere the application database can reach. That would restore the property this whole design exists to remove, and the inconvenience of key management is the price of the guarantee being real.
A scheduled verification that reports its own result, and an external anchor for the chain head, are the two that turn this from a control you can invoke into one that watches itself. Both build on the verification that already exists.
Anything above that you need, we can build for you
Everything listed above as something we can add describes what ships in the standard product today — it is a starting point, not a limit on what AWRA OpsHub can do for your organisation. Kenya's eTIMS integration and its maintained payroll engine are both in the product because clients needed them and commissioned them; neither appeared by itself, and the same door is open for whatever you just read about. One qualification so this is worth what it claims: a small number of things on this blog we deliberately leave to a specialist rather than build — a statutory ledger we will not sign our name to, a rule that would decide a tax question for you, a clinical or member-funds record that belongs in a regulated system — and where that is true the post says so in those words. Everything else is a scope, a timeline and a price.
The operational work, which is what most commissions actually are
An extra approval stage in a chain that does not match the standard one, a custom field set on employees or assets that only your sector needs, an expiry that has to block an order rather than send an email, a report your board asks for in a shape nothing produces, or a scanner or weighbridge feeding the goods-in door. These are the commissions we are asked for most often and the smallest ones we quote — and unlike a revenue-authority pipeline, none of them waits on a regulator.
The module-shaped additions, which are the ones readers ask for most often
A price list with real discount authority, a customer-facing quotation that expires, a bill of materials or recipe costing, a staff advance that is issued, acquitted and chased, a member or unit ledger, a matching rule that holds a payment. Each of these is a build rather than a setting, and each has been quoted before — a bigger piece of work than a custom field, with a written spec and a date instead of a roadmap slide.
The report, document or pack nothing currently produces
The board pack in the shape your board actually asks for, a donor or funder layout, an invoice or receipt template carrying what your regulator or your customer expects, a dataset the report builder cannot reach yet. Usually the fastest thing on this list to deliver, because the data is already in the system.
Systems, rails and hardware you already run
The accounting package, CRM, online store, core banking or custom database you intend to keep — connected through our API so a fact is entered once and appears everywhere it is needed. Plus the physical edge: a scanner, a scale, a weighbridge or a till peripheral feeding the door it belongs to.
How it works: you describe the requirement, we return a written scope, timeline and cost, and once agreed it is built into your environment and maintained as part of the product. Nothing here waits on a regulator or a published specification, which is why operational builds are the ones we quote fastest. Tell us the requirement that would otherwise rule us out — that is a better first conversation than a demo.
Tell us what your operation needsOur take
The chain is the easy half, and any competent team can build one in a week. What separates a real implementation from a demonstration is everything around it: that the signature is keyed rather than plain, that the key lives somewhere the database does not reach, that concurrent writes cannot fork it, that the value signed is the value stored rather than the value passed, that a legitimate purge is distinguishable from a deletion, and — the one almost nobody does — that the record excusing a purge is itself inside the chain it excuses. The honest limit is worth repeating because it is the thing most likely to be oversold to you: none of this prevents anybody from deleting anything. It means a deletion leaves a mark that has to be accounted for, and being unable to account for a gap is a very different position to being able to say nothing happened.
Ask what proportion of your trail is actually covered
It is the question with the least reassuring answer and the most useful one, and it applies to any system you run. Protection added to a table that already had history in it covers what came after, and the count of what came before is the number worth knowing.
Talk through audit evidenceFrequently asked questions
Does this stop somebody deleting an audit row?
No, and any system claiming otherwise while keeping its log in its own database is overstating what it does. Anybody with write access can still remove a row. What changes is that the removal leaves the following row referring to a predecessor that no longer exists, and the sequence numbers skip, so the verification reports a break at that exact position. The deletion happens and becomes a thing somebody has to explain.
Why is a keyed signature better than an ordinary hash?
Because an ordinary hash can be recomputed by anybody. Change a row, recalculate its hash and every hash after it, and the chain is consistent again — arithmetic, requiring nothing but the data. A keyed signature needs the key, which is derived from the application secret and held in deployment configuration rather than in the database. Somebody who has reached the database has not thereby reached the key.
What happens when the retention policy deletes old rows?
The purge writes its own audit row recording the range of sequence numbers it removed, and the verification treats gaps inside a recorded range as accounted for. The reason this is not a loophole is that the record of the purge sits in the same chain as everything else, so forging one means inserting a row, and inserting a row is detected at the position where it was inserted.
Why is the chain per workspace rather than one for the whole system?
So that an organisation can hand its own audit export to an auditor and have it verify standing alone. Under a single shared chain the missing links in any one organisation export would be other organisations rows, which both breaks the verification and reveals that those rows exist. It also means one organisation retention purge cannot tear a gap through everybody else chain.
What are unchained rows, and should I worry about them?
Rows written before the protection was added. The columns were introduced to a table that already held history, so those rows have no signature and cannot acquire a meaningful one retrospectively without the events being re-derived. They are counted and reported rather than flagged as damage, and there is a backfill that signs them going forward. The count is the useful number: it tells you what proportion of your trail the protection actually covers.
Could somebody who controls both the database and the application rebuild the chain?
Yes, and it is the one remaining hole, which is why an external anchor appears on the list of work we can add rather than being claimed as done. An attacker holding the signing key as well as the data could recompute a consistent chain over an altered history. Publishing the head signature outside the deployment at intervals closes it, because a rebuilt chain would disagree with a value already recorded elsewhere.
