Audit Logging System Design

Somebody deleted a customer record. Six weeks later it matters who, and when, and what the record said. Without an audit log there is no answer: the row is gone, the application logs rotated after fourteen days, and the only remaining evidence is a backup that shows the record existed and nothing about its removal.

An audit log solves that, and then introduces a question that an ordinary log does not have to answer: can the log itself be trusted? An audit trail that an administrator can edit proves nothing against an administrator, which is a large share of what an audit trail exists for. The same applies to the application: a bug or an injected statement that can delete audit rows makes every other row meaningless, because there is no way to tell a complete log from a pruned one.

The answer is a hash chain. Each entry includes the hash of the one before it, so changing or removing any entry invalidates every entry after it, and that invalidation is detectable with one pass. It does not prevent tampering, because nothing in the database can. It makes tampering visible, which is a different and achievable goal.

Getting that right turned out to depend on a detail nobody expects. The chain originally hashed a timestamp more precise than Postgres and MySQL actually store, so the value written and the value read back differed and the chain failed verification on its very first row, on every such deployment.

The system has to be able to:

• Record every action worth attributing: the actor, the action, the target, the time and the context.
• Chain the entries, so an alteration anywhere is detectable.
• Verify the chain on demand, and say where it breaks.
• Make a table append-only, so updates and deletes are refused at the database layer.
• Record a reseal, when a chain genuinely has to be rebuilt, as an entry in the chain.
• Query the log like any other resource: filter by actor, action, target and date.
• Keep the record through a soft delete, so the deletion itself is the thing recorded.

Functional requirements

• An activity log row per recorded action, with actor, method, path, target and time.
• A SHA-256 hash of each entry including the previous entry’s hash.
• A verification pass reporting whether the chain holds and where it first fails.
• An append-only guard, installed on registered models, refusing updates and deletes.
• A reseal operation, recorded as a SECURITY entry naming itself.
• An admin page over the log, with filters and the integrity check.
• Hashed values normalised to what the column actually stores, so precision does not break the chain.

Non-functional requirements

• Tamper evident, not tamper proof. nothing inside the database can stop a sufficiently privileged write. Making it detectable is achievable and is what the chain delivers.
• Append only. the guard is on the model, so an update or delete is refused wherever it comes from: a handler, a job, a migration, the studio.
• Precision-safe hashing. every hashed value is normalised to what the column stores. Postgres keeps microseconds and MySQL milliseconds, and a nanosecond timestamp in the hash input made verification fail on row one.
• Verifiable in bounded time. verification is a single ordered pass. It gets slower as the log grows, which is why the recent tail is what gets checked routinely.
• Queryable. the log is a table with indexes, not a text file. The question is always "what did this person do" or "what happened to this record", and both need an index.
• Honest about a reseal. a chain that genuinely must be rebuilt records the rebuild in itself. A silent reseal would make the chain worthless.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

An append-only table, a hash over each entry that includes its predecessor, and a verification pass.

Core components

• Activity log model. the table, declared like any other so migrations and backups cover it. Actor, action, target, time, context and the chain fields.
• Chain writer. reads the previous hash, computes this entry’s, inserts. Normalises every hashed value to what the column stores.
• Verifier. walks the entries in order, recomputing each hash, and reports the first row where the chain breaks.
• Append-only guard. a GORM hook installed on registered models, refusing updates and deletes. Installed in every project whether or not anything uses it yet, so the feature works the day somebody needs it.
• Reseal. rebuilds a chain that cannot verify, and records that it did so as an entry in the new chain.
• Admin view. the log with filters, and the integrity check as a button.

Request flow

1. 1Something happens that is worth attributing: a record deleted, a role granted, a setting changed, a sign-in from a new device.
2. 2The chain writer reads the hash of the most recent entry. This is what links the new entry to everything before it, and it is also why appends serialise.
3. 3The entry’s own hash is computed over its fields together with the previous hash. Changing any earlier entry changes every hash after it, which is the entire mechanism.
4. 4Every hashed value is first normalised to what the column will actually store. Postgres keeps microseconds and MySQL milliseconds, and hashing a nanosecond timestamp meant the value read back never matched the value hashed. The chain failed on its first row on every such deployment until this was fixed.
5. 5The row is appended, carrying its own hash and its predecessor’s.
6. 6The append-only guard refuses any update or delete on the table. It is a model hook rather than a service rule, so it applies to a handler, a job, a migration and the database browser alike.
7. 7Verification walks the entries in order and recomputes each hash. A chain that holds proves no entry was altered or removed since it was written.
8. 8A break is reported with the row it starts at, which is the useful answer: everything before it is intact, and something happened at that point. A genuine rebuild is possible, and it writes a SECURITY entry naming itself, because a silent reseal would make the whole chain worthless.

Data flow

• The chain links entries by hash, so the detection property survives a backup and restore: the restored log verifies, and it verifies as of the backup.
• Append-only is enforced at the model layer, which is the only layer every writer passes through.
• A soft delete of an audited record keeps the record and adds an entry about the deletion, so the deletion is itself the audited fact.
• Normalising hashed values to column precision is not a detail. It is the difference between a chain that verifies on Postgres and one that never has.

Where it lives

• In the application database because it is queried with application data, by actor and by target.
• Append-only in practice and enforced in code, so a restore of a backup produces a log that verifies as of that backup.
• The fastest growing table in most projects, and the clearest candidate for partitioning by month.

Core types

Design principles applied

• Detect rather than prevent. nothing in the database can stop a privileged write. A chain makes the write visible, which is the achievable version of the goal.
• Enforce where every writer passes. a model hook covers handlers, jobs, migrations and the database browser. A service rule covers the callers who remembered.
• Hash what will be stored. the hash input must survive the round trip through the column. This one cost every Postgres and MySQL deployment its chain until it was found.
• Record the exception. a reseal is written into the chain. An audit system whose own repairs are invisible is not an audit system.
• Ship the mechanism before the use. the append-only package is in every project, inert. Infrastructure that has to be added before a feature can be used is infrastructure the feature does without.

Patterns

• Writes serialise on reading the previous hash, which caps the append rate. For audit volumes that is not a constraint; for per-request logging of everything it would be.
• Verification is linear in the log, so full verification becomes an offline job and the routine check is of the recent tail.
• Partitioning by month keeps queries and verification bounded, with the chain verified per partition and the boundary hash linking them.
• The table is the fastest growing in most projects, and the retention period is usually a compliance decision rather than a technical one.
• Archiving old partitions to object storage keeps the database small while keeping the record, which is what long retention periods actually require.
• Hashing itself never matters: SHA-256 over 500 bytes is microseconds, and nothing about the cost of the chain is the hash.

What breaks first

• Serialised appends. every entry needs the previous hash, so the chain has a single writer by construction and a burst of audited actions queues.
• Verification time. a full pass over tens of millions of rows takes minutes, which makes it unusable as an on-demand check from a button.
• A break with no explanation. the chain says where it first fails and cannot say why. A restore from backup, a replication artefact and a deliberate edit all look identical.
• Table growth under long retention. seven years of audit data in the operational database competes with the application for everything.
• Nothing anchored outside. an attacker who can rewrite the whole table can recompute the whole chain. Internal consistency is not external proof.

What to do about it

• Chain per partition. monthly chains linked by their boundary hashes. Appends within a month still serialise, but verification and archival both become bounded.
• Verify the tail routinely, the whole thing nightly. the last few thousand rows from a button, the full pass as a scheduled job that alerts on a break.
• Record the operational events too. a restore, a reseal and a migration written into the chain turn an unexplained break into an explained one.
• Archive to object storage. closed partitions moved out with their verification result, keeping the record without keeping the rows in the hot database.
• Anchor the chain externally. publish the periodic head hash somewhere outside the system, to a log service or a signed note. That is what makes the chain evidence against somebody who controls the database.