WritingCompliance
An audit log that proves a compliance decision was not edited later
How to build an append-only, hash-chained audit log, sealed outside the database each day, so an auditor can prove a decision record was never altered.
At 14:02 UTC on a Tuesday, reviewer R-114 approves business case C-88213 with a risk score of 31 and the note "director matches the registry filing". Eighteen months later a regulator asks whether the score or the note changed after a complaint arrived. A cases row with an updated_at column cannot answer that. The system in this post exists to answer it: an audit log that every decision is written into, that anyone with read access can verify, and that the people who run the database cannot quietly rewrite.
The constraint that shapes the design is that the log has to live in the same database as the decisions, because the only way to guarantee that a decision and its audit event both exist, or neither does, is one transaction. That same database is administered by people who can edit any row. The claim I will defend: a hash-chained audit log is only evidence if its chain heads are anchored outside the database every day. Without the anchor, the chain proves nothing except that whoever edited a row forgot to recompute the hashes after it.
flowchart TD A[Case service] --> Q[Audit writer] Q --> D[Case tables] Q --> E[Audit events]:::accent A --> V[Evidence store] E --> S[Daily sealer] S --> W[Anchor store] R[Verifier] --> E R --> W
One transaction, two writes
SELECT ... FOR UPDATE. It updates the case tables, then calls the audit writer in the same process (never over the network) with the action case.decision.approved, the actor user:R-114 and the payload {"decision":"approved","riskScore":"31","note":"..."}. Both writes commit or roll back together; if the event insert fails, the reviewer sees an error and tries again. I have built centralised KYB, KYC and AML dashboards, and the rule I apply is that a decision without its event is not a decision, so the code offers no path that writes one without the other.The event row and its chain
audit_events row carries case_id, seq (1, 2, 3 per case, unique on the pair), occurred_at as an ISO 8601 UTC string, actor_id, action, payload_json as the exact canonical bytes, payload_hash, prev_hash and hash. The writer reads the previous head under the case lock, so two reviewers acting on one case in the same second produce seq 7 and seq 8, never two seq 7s. The application's database role holds INSERT and SELECT on this table and nothing else, and a trigger rejects UPDATE and DELETE. That stops the application and most bugs, not an administrator.Documents stay out of the log
document.attached with {"documentId":"doc_3f9","sha256":"...","bytes":"412033","mime":"image/jpeg"}. When the retention schedule deletes the scan years later, the deletion is itself an event, so the chain records that the document existed, what it hashed to, and when it went. A sweeper removes objects with no referencing event after one hour.The daily seal
seq), sorts the pairs by case_id and hashes the list into a day root. It writes one JSON object named by the date, 2026-04-14.json, holding the sorted heads, the event count and the root, into an object store bucket with a retention lock set to the full retention period. The sealer's role can create objects there and can neither overwrite nor delete one, so a second run for the same day fails on the existing object instead of replacing it.Verifying a case
case_id, reads its events in seq order from a read replica, recomputes payload_hash from the stored bytes and hash from the fields, and checks each prev_hash against the row before it. It then loads the anchor object for the date of the last event, recomputes the root from the stored heads, and checks that this case's head appears in the list. The output is either verified against anchor 2026-04-14 or the first seq at which a hash disagrees. A weekly job sweeps every case and opens an incident on the first mismatch.Hash the bytes you stored, not the object you parsed
A chain like this is broken more often by a serialiser than by an attacker. If the verifier re-parses payload_json, rebuilds the object and stringifies it again, a library upgrade that changes key order or number formatting fails every event written before the deploy. So the writer canonicalises once (sorted keys, no whitespace, every number carried as a string), hashes those exact bytes and stores those exact bytes. The verifier hashes what is in the column and never re-serialises.
// Simplified: hashing one audit event inside the write transaction.
import { createHash } from "node:crypto";
const ZERO = "0".repeat(64);
const sha256 = (s: string) =>
createHash("sha256").update(s, "utf8").digest("hex");
export function canonical(value: unknown): string {
const sort = (v: unknown): unknown =>
Array.isArray(v)
? v.map(sort)
: v && typeof v === "object"
? Object.fromEntries(
Object.keys(v as object)
.sort()
.map((k) => [k, sort((v as Record<string, unknown>)[k])]),
)
: v;
return JSON.stringify(sort(value));
}
export function eventHash(e: {
caseId: string; seq: number; occurredAt: string; actorId: string;
action: string; payloadJson: string; prevHash: string | null;
}): string {
const payloadHash = sha256(e.payloadJson);
// Fields joined by newline: none of them can contain one.
return sha256([
e.caseId, String(e.seq), e.occurredAt, e.actorId,
e.action, payloadHash, e.prevHash ?? ZERO,
].join("\n"));
}The newline separator matters: without one, seq 12 with actor 3 and seq 1 with actor 23 share a prefix. Pin a fixture in CI, one event with known fields and its expected hash, so a change to canonical or eventHash fails the build rather than the next audit.
When someone edits seq 7
Take the approval from the opening. On 14 April it is written as seq 7 on case C-88213, and that night the sealer records the case's head, which by then is seq 9 after two notes were added. In October a complaint lands and someone with administrator access changes payload_json on seq 7 from "riskScore":"31" to "riskScore":"68". The application notices nothing: it never updates that table, and the reviewer sees the case exactly as before.
On its weekly sweep the verifier recomputes payload_hash for seq 7 from the edited bytes and gets a value that disagrees with the stored one, and the report names C-88213 at seq 7. Suppose the editor recomputed payload_hash and hash for seq 7 as well. Now seq 8 carries a prev_hash that no longer matches, so the editor must rewrite seq 8 and seq 9 too, which the same access allows. At that point every row in the database agrees with itself.
The anchor ends the game. The head stored for C-88213 inside 2026-04-14.json was computed from the original seq 9, the bucket refuses overwrites, and the day's root would have to change as well. The verifier reports a chain that is internally consistent but whose head does not match the sealed head, which localises the alteration to C-88213 at some point after 14 April. The case is frozen and the question becomes who had administrator access.
Trade-offs
| Choice | What it buys | What it costs |
|---|---|---|
| One chain per case rather than one global chain | Writes to different cases never wait on each other | No order across cases, so the daily seal is what ties them together |
| Hashing inside the write transaction rather than a background chainer | No window in which an unchained event can be edited | Each write holds the case lock while hashing |
| Sealing once a day rather than per event | One small object a day, simple to check by hand | An edit made and reverted before the seal leaves no trace |
Application role without UPDATE or DELETE grants | Stops the application and most bugs rewriting history | Does nothing against an administrator |
Failure modes
- The decision commits without its event. You observe cases in
approvedstate with nocase.decision.approvedrow. Contained by one transaction for both writes, and a nightly count of decided cases against decision events. - Two writers take the same
seq. You observe a unique violation on(case_id, seq). Contained by taking the case row lock before reading the head, and retrying the whole transaction rather than picking the next free number. - A serialiser change breaks every old hash. You observe the verifier failing on all events before one deploy date. Contained by hashing stored bytes, never re-serialised objects, and by the CI fixture that pins a known hash.
- The sealer misses a day. You observe a gap in anchor object names. Contained by having each run seal every unsealed day since the latest anchor, and an alert when the newest anchor is older than 26 hours.
Build the audit_events table, the write path inside the decision transaction and the verifier first, with the CI fixture pinning one known hash. Add the sealer in the same week, before anyone treats the log as evidence, because every unsealed day is a day whose chain can still be rewritten end to end without anyone knowing.