An audit trail that cannot be quietly edited

Every compliance framework asks for an audit log, and most products have one: a table of who did what, when. The question auditors increasingly ask next is harder. How do you know the table is what was written? A database administrator can update a row. An attacker who got as far as the database can delete the entries that show how they got there. A log that can be edited silently is a record of what someone wants you to believe, not of what happened.

Sapherbox’s audit log is designed so that editing it is not silent. This article explains the mechanism, which is not complicated, and the parts around it that turned out to matter more than the mechanism.

Two layers, because one is not enough

The first layer is ordinary database hygiene. The role the application connects with can insert audit rows and read them. It cannot update or delete them. That stops the application itself, and anyone who has only the application’s credentials, from altering history.

The second layer exists because the first has an obvious gap: whoever administers the database has a more powerful role, and so does anyone who steals it. For them, a plain table is editable. So every audit entry carries a hash, and every hash includes the hash of the entry before it. The chain is keyed with a secret that the database never sees.

The chain

When an audit event is written, the server takes the fields that make up the entry: the identifier, who acted, what they did, on which resource, the masked resource name, the client address if one was recorded, the timestamp, and a canonical serialisation of the entry’s detail fields. It prepends the hash of the previous entry, separates the fields with a byte that cannot occur inside them, and computes an HMAC-SHA-256 over the whole thing with a signing key. The result is stored in the row as its hash, and becomes the “previous hash” for the next entry.

Two properties follow. Changing any field of any entry changes its hash, so the stored hash no longer matches. And because each entry’s hash is an input to the next, a changed entry also breaks the entry after it, and deleting an entry breaks the link between its neighbours. The chain does not prevent tampering. It makes tampering visible at the first entry that no longer verifies.

The signing key is what makes this hold against a database administrator. It is a secret the application process receives at startup, delivered as a container secret, and it is never written to the database. Someone with full database access can recompute hashes without it only by guessing a 256-bit key. Someone with the key and the database could rewrite history, which is why the two are kept apart: the people who operate the database are not handed the application’s secrets, and the application’s role cannot touch the rows.

Writing an entry locks a single row that holds the current head of the chain, computes the new hash, inserts, and advances the head, all in one transaction. Two events written at the same instant are serialised, so there is exactly one chain with no forks.

Verification is a button

A chain nobody checks is decoration. An administrator can verify it from the audit settings page at any time, and the API exposes the same check for anyone who wants to script it. Verification reads every retained entry in order, recomputes each hash from the stored fields with the signing key, and compares. It reports either “chain intact” with the number of entries verified, or the exact entry where the chain first fails and why: a hash that does not match its fields, a link that does not match the previous entry, or an anchor that has been moved.

A broken chain is reported as a security incident, in those words, because that is what it is. Either the data was altered, or the key was changed without a re-anchor, and both deserve an investigation rather than a shrug.

Retention is where it gets interesting

The mechanism above is the easy part. The hard part is that an audit log is not append-only forever. Data protection law asks you to keep logs no longer than necessary, and Sapherbox purges entries older than the tenant’s retention setting, 365 days by default, every day.

A purge deletes the oldest entries, which means the chain no longer starts at its original beginning. A naive verifier would report a break at the purge boundary every day. So the purge re-anchors: it records the previous-hash value of the oldest surviving entry as the chain’s genesis, and verification starts there.

That creates a new attack. If the anchor can move, an attacker with database access could delete inconvenient early entries and move the anchor past them, and the chain would still verify. The design closes this by making every purge write an audit entry of its own, chained like any other, whose details state the genesis it set and how many entries it removed. Verification checks that the head’s genesis equals the one attested by the most recent purge entry. An anchor moved by hand, without a corresponding purge entry, fails as a genesis mismatch. Deleting the purge entry too breaks the chain at that point. The purge is a recorded, authenticated event, not a hole.

What the chain does not do

It does not hide the log. The audit log is plaintext on the server, by design, because an administrator has to be able to read it and a verifier has to be able to recompute it. The resource names in it are masked, as described in our article on what the server can see, but the events are readable.

It does not protect against an attacker who holds both the database and the signing key. Keeping those apart is an operational control, not a cryptographic one, and we say so.

It does not replace an external copy. The chain proves that the log you have was not altered in place. It cannot restore entries that were deleted along with everything else. The CSV export exists for that: shipping the log to a system the tenant’s administrators do not control is the standard answer, and the chain makes the exported copy checkable against the original.

And it does not keep entries beyond the retention you set. That is the point of retention, and the design makes the removal itself part of the record.

For the compliance file

If you are answering a NIS2, DORA or ISO 27001 question about log integrity, the short form is: entries are HMAC-SHA-256 chained with a key held outside the database, the application role cannot modify or delete entries, the retention purge is an attested event, and an administrator can verify the entire retained chain on demand or by API. The longer form is this article, and the whitepaper maps the control to the frameworks’ clauses.

Questions?

If your auditors want the hash construction, the verifier’s failure modes or the re-anchor protocol in writing, get in touch at hello@sapherbox.com.