Append-Only Audit Logs in Postgres: Your Trigger Misses TRUNCATE

---

Second post from the compliance-ready backend kit, after nested JWTs. This one is about the audit log, which is the control an assessor actually leans on.

The bar is not “we write rows to an audit_events table”. The bar is: show me every event for this tenant, in order, and demonstrate that nobody edited one. Those are different problems. The first is a table. The second needs the log to be append-only, and needs any edit that slips past that to be detectable afterwards.

Here is the summary I wish I had started from: append-only and immutable are not the same claim, and most implementations quietly make the weaker one.

The chain

Every event carries the hash of the one before it:

hash = sha256("crbk.audit.v1" || prev_hash || canonical_form(event))

Alter any event and its hash changes, which changes the input to the next hash, and so on to the head. One edit invalidates every link after it.

Two details in that line matter more than they look.

The domain separator. "crbk.audit.v1" is prefixed so a digest computed for one purpose cannot be replayed as one computed for another, and so the construction can be changed later without invalidating history: bump the version, and old rows keep verifying under the old label.

Genesis is 32 zero bytes, not NULL. This is the schema decision I would most likely have got wrong. There is a UNIQUE index on prev_hash, which is what stops two events claiming the same predecessor. But Postgres permits many NULLs in a unique index. Use NULL as the genesis marker and that constraint silently stops covering the one row it most needs to: the first. A real value makes it mean what it says, which is that exactly one row may follow any given hash, including the start.

Canonical form has to be injective

The event gets serialised before hashing, and JSON.stringify is not good enough. It preserves insertion order, so two callers building the same event with fields assigned in a different order produce different bytes for identical evidence. So keys are sorted.

Sorting is the obvious half. Here is the half I did not see coming.

If you join sorted fields with a delimiter, the encoding is ambiguous, because a value can contain the delimiter:

action = "a"     actorId = "b|c"    ->  "a|b|c"
action = "a|b"   actorId = "c"      ->  "a|b|c"

Two genuinely different events, one hash. That is a collision you built yourself, with no cryptographic weakness involved. The fix is to prefix every value with its byte length, which makes the encoding injective:

crbk.audit.v1
action:1:a
actorId:3:b|c

Nulls are encoded distinctly from empty strings too (actorId:null, not actorId:0:). Otherwise “no actor recorded” and “an actor whose id is the empty string” hash identically, and the first is a perfectly normal state for an unauthenticated event.

One more deliberate omission: seq is not hashed. It is assigned by a database sequence at insert time, so it is not known while the hash is being computed. It orders rows for reading. The chain, not the column, carries the integrity.

Metadata is a flat map of strings, and that is not laziness

The hash is computed by the application, then verified against what Postgres returns. So anything the database normalises in between shows up as a broken chain.

jsonb normalises more than people expect. It sorts keys, discards whitespace, drops duplicates, and rewrites numbers: store 1e2 and you get back 100. Canonical serialisation handles key order and whitespace. It cannot handle a value the database is free to rewrite.

Think about how that failure presents. Not as corruption. As tampering: the recomputed hash differs, the chain reads as broken, and it happens intermittently, only on events that happened to carry a number in an unusual form. Someone investigating a real incident gets handed a false positive at exactly the moment they most need to trust the tool.

So metadata values must be strings, and the database enforces it rather than trusting the caller:

CREATE OR REPLACE FUNCTION audit_metadata_is_flat_strings(m jsonb) RETURNS boolean AS $fn$
  SELECT jsonb_typeof(m) = 'object'
     AND NOT EXISTS (
       SELECT 1 FROM jsonb_each(m) AS entry(key, value)
       WHERE jsonb_typeof(entry.value) <> 'string'
     );
$fn$ LANGUAGE sql IMMUTABLE;

ALTER TABLE audit_events ADD CONSTRAINT audit_events_metadata_flat_ck
  CHECK (audit_metadata_is_flat_strings(metadata));

It has to be a function because a CHECK cannot contain a subquery, and detecting a non-string value means iterating the object. IMMUTABLE is what makes it legal in a CHECK: the result depends only on the argument.

Nesting is refused as well as non-string scalars, because a nested object would serialise into the hash as whatever the canonical form happened to do with it, and that is a rule nobody wrote down.

The lock has to come first

This is the part worth stealing.

Appending is a read-modify-write: read the current head, hash the new event onto it, insert. Two concurrent callers both read head H, both produce an event whose prev_hash is H, and the chain forks into two histories that each verify perfectly on their own. Nothing afterwards can tell you which one is real.

So the append is one transaction with three steps, and the order is the whole thing:

-- 1. FIRST
SELECT pg_advisory_xact_lock($1);

-- 2. head and timestamp, one round trip
SELECT now() AS now_at,
       (SELECT hash FROM audit_events ORDER BY seq DESC LIMIT 1) AS prev_hash;

-- 3. insert with prev_hash and the computed hash

A lock taken after the read protects nothing, because the value it was meant to protect has already been observed. That is not a subtle ordering preference, it is the difference between working and silently broken. Being the _xact_ variant, it releases when the transaction ends, including on rollback, so a failed append cannot leave the chain locked.

If you have used SKIP LOCKED to turn Postgres into a queue, this is the same family of reasoning pointed at the opposite goal: there you want workers to skip past each other, here you want them strictly serialised.

UNIQUE(prev_hash) stays in the schema as the second line of defence, deliberately. If the lock is ever removed, mis-keyed, or bypassed by another code path, the database refuses the second insert rather than accepting a fork. Belt and braces, because a fork is the one failure the chain cannot detect afterwards.

One Prisma-specific trap, since it cost me a confusing error: the lock goes through $executeRawUnsafe, not $queryRawUnsafe. pg_advisory_xact_lock returns void, and Prisma’s query path tries to deserialize every column, failing with Failed to deserialize column of type 'void'.

The timestamp comes from the database

now() is read in the same round trip as the head, for two reasons.

It has to be known at append time because it is part of the hash, so a column default could not work: the value would not be chosen yet.

And taking it from the appending process would put every instance’s clock into the evidence. Two machines a second apart would write events whose recorded order contradicted their chain order, and NTP drift on one box would silently skew a whole tenant’s timeline. now() is transaction start time, so it is stable between the read and the insert.

Append-only enforcement: three layers, and the second one surprises people

All of this lives in one SQL file applied to every database that carries a chain, because two copies of a security control eventually disagree and the drift is invisible until someone modifies a row and succeeds.

1. A row trigger on UPDATE and DELETE. BEFORE, so the statement is refused rather than performed and rolled back. It fires for every role including superusers, which makes it the strongest layer in a setup where the service and the migrations connect as the same privileged role.

2. A statement trigger on TRUNCATE. Here is the trap in the title.

Row triggers do not fire for TRUNCATE at all. It deallocates the underlying files without visiting rows, so a FOR EACH ROW trigger never runs. If your append-only enforcement is a row trigger on UPDATE and DELETE, and you stopped there, then this:

TRUNCATE audit_events;

erases your entire audit log in one statement, without tripping anything. That is precisely the operation someone covering their tracks reaches for. The trigger has to be FOR EACH STATEMENT, and that is not a style choice: TRUNCATE triggers cannot be per-row.

CREATE TRIGGER audit_events_no_truncate
  BEFORE TRUNCATE ON audit_events
  FOR EACH STATEMENT EXECUTE FUNCTION audit_events_reject_mutation();

3. REVOKE UPDATE, DELETE, TRUNCATE ... FROM PUBLIC. The layer that keeps working if a trigger is ever dropped. Being honest about it: this only bites for a role that is neither the table owner nor a superuser, so in a default single-role deployment it is documentation of intent. In a deployment that runs the service as a restricted role, it becomes the real boundary.

INSERT and SELECT remain, which is the entire intended surface.

What this does not do

A superuser can bypass all of it. ALTER TABLE ... DISABLE TRIGGER ALL, or SET session_replication_role = 'replica' to skip triggers for their session, or simply DROP TRIGGER.

So this is enforcement against the application, against an ordinary compromise of the service, and against an operator’s mistake. It is not enforcement against someone holding superuser on the database. Two things close that gap and neither belongs in SQL: restrict who holds superuser, and ship the head hash off the box.

Which brings us to the two things the chain itself cannot catch.

A full tail rewrite. Someone who can edit rows and recompute every hash from their edit forward produces a chain that verifies perfectly. The only thing that closes this is comparing the head hash against a value stored somewhere the database cannot reach, and nothing inside the database can do that for you. The kit prints the head hash for exactly this purpose and does not anchor it anywhere. That is a real gap, and it is on you.

A missing event. A chain over events 1..n verifies whether or not something that was never written belonged between them. Which matters, because of the next part.

Appends fail open, loudly

This is the decision most worth arguing with.

The writer itself does not swallow errors. The call site does: a failed append is logged at error level and the request proceeds.

Failing the request instead is stricter and defensible, and I think it is wrong here. It makes the audit chain a hard dependency for logging in, so an unreachable tenant database locks every user out of an otherwise healthy service. Turning an evidence-recording problem into an availability outage is a bad trade in both directions.

What that costs, stated rather than buried: during an append failure the service performs actions it does not record, and the hash chain cannot reveal that gap. Detecting absence needs something the chain does not provide.

So the failure log carries every field needed to reconstruct the lost event, because that line is the only remaining record that the action happened:

AUDIT APPEND FAILED on tenant acme, the action proceeded UNRECORDED.
action=... actorType=... actorId=... resource=.../... metadata={...}

A deployment that genuinely must not lose events should write ahead to a durable queue and drain it into the chain rather than appending inline. That is the outbox pattern applied to evidence instead of to integration events.

Proving the enforcement, not just the chain

A verifier that walks the chain is the obvious tool, and it reports the first break rather than all of them, because after a break every later link fails too and a verifier that listed them all would bury the one row that matters under thousands of consequences. Three distinct failures, kept distinct because they mean different things:

• a recomputed hash that does not match the stored one means the event’s own fields were altered
• a prev_hash that does not match the previous row’s hash means an event was removed or reordered
• a first row whose prev_hash is not genesis means events were removed from the start

Beyond that, two probes, each covering something the other structurally cannot. Both are worth copying, mostly for their pass conditions.

The immutability probe attempts UPDATE, DELETE and TRUNCATE plus a duplicate prev_hash, and requires each to be refused with the right SQLSTATE. Then it confirms the chain is unchanged and that a normal append still works, so a table that refused everything cannot pass.

Every destructive attempt runs inside a transaction that is rolled back. If a trigger has been dropped, TRUNCATE audit_events is exactly the erasure the trigger exists to prevent, and a probe that executed it would destroy the evidence it was asked to check, in the situation where that evidence matters most. TRUNCATE is transactional in Postgres, so a ROLLBACK genuinely undoes it.

It also refuses to run against an empty chain, because UPDATE and DELETE triggers are per-row and never fire when nothing matches. Both checks would pass against a table with no protection at all.

The contention probe fires concurrent appends and asserts UNIQUE(prev_hash) never fires. That is the pass condition, not merely “no errors”. A run where appends hit the unique constraint and retried would look healthy while proving the opposite: that the lock did not serialise, and the database caught what the application should have prevented.

One chain per database, not one central chain

Worth a note, because it was the open design question. Each tenant database carries its own chain, and the master database has a separate one for control-plane events. N+1 chains.

Audit records about a tenant’s users are that tenant’s data, so centralising them breaks the property the rest of the architecture rests on, and takes per-tenant export, residency and deletion with it. A central head also makes the master a single point of failure for every audited write. And pg_advisory_xact_lock is per-database, so a per-tenant chain can be serialised correctly and cheaply, while a chain spanning tenants cannot be serialised with one lock at all.

The cost is no global total ordering across tenants, and it is small, because the question an assessor asks is “show me the events for this tenant”, which per-tenant ordering answers exactly. If database-per-tenant is new to you, I wrote about the weaker isolation tier and where it breaks separately.

The part worth taking with you

Three things, in order of how likely they are to be wrong in code you already run:

1. If your append-only enforcement is a row trigger, TRUNCATE walks straight through it. Add a statement-level trigger. Test it.
2. Take the lock before you read the head, or concurrent appends fork the chain into two histories that both verify.
3. Say “append-only and tamper-evident”, not “immutable”, unless you have restricted superuser and anchored the head hash somewhere the database cannot reach. The honest claim is the defensible one, and an assessor will find the difference faster than you expect.

The implementation is in packages/db/sql/audit-immutability.sql, packages/db/src/audit/audit-writer.ts and packages/crypto/src/audit-hash.ts in the kit repo. Next in this series: key rotation without downtime, and the pending/active/retiring/revoked lifecycle that a database constraint enforces rather than a comment.