A tamper-evident audit log in PostgreSQL with hash chains and HMAC

Building an audit log whose history can’t be quietly rewritten, layer by layer, plus detecting truncation and clearing IPs without breaking the chain.

· Published · Updated · 7 min read · Versions: PostgreSQL 16 · About the project →

In an app where data is edited from several devices, sooner or later you'll need to answer "who changed this, when, and from where?". An audit_log table answers that. But there's a harder question behind it: how do you know the audit log itself hasn't been edited?

This post walks through building a tamper-evident audit log in PostgreSQL, layer by layer, each one closing the gap left by the previous. The examples come from Financial Plan, my budgeting app (its sync is covered in local-first PWA sync), where every record a device pushes is logged with its before and after versions, the device, the time and the outcome.

Layer 1: append only

The first step is to have the database itself refuse any update or delete on the log table:

CREATE FUNCTION audit_log_append_only() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log is append-only';
END $$;

CREATE TRIGGER audit_log_no_change BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION audit_log_append_only();
CREATE TRIGGER audit_log_no_truncate BEFORE TRUNCATE ON audit_log
  FOR EACH STATEMENT EXECUTE FUNCTION audit_log_append_only();

Don't forget TRUNCATE: as the PostgreSQL TRUNCATE docs note, it doesn't fire DELETE triggers, so it needs its own statement-level trigger.

Log entries should be written in the same transaction as the data change. Then no change can be saved without its log entry, and no entry can exist for a change that was rolled back.

What this stops: bugs in code, a broken migration, an UPDATE missing its WHERE. What it doesn't: whoever owns the table, since they can disable triggers. Hence the next layer.

Layer 2: a hash chain

Each row also stores the hash of the row before it, and its own hash covers its content plus that previous hash:

hash = sha256(prevHash + content)

Edit a row in the middle and its hash changes, and every row after it stops matching. To verify, walk the chain from the start and you'll see exactly where it breaks.

Keep one chain per user rather than one for the whole table. Writes from different users then don't queue behind each other, and checking one user's history doesn't mean reading everyone's.

The gap: anyone can compute SHA-256. Someone with write access to the database can edit a row and then recompute the whole chain after it. The new chain is perfectly valid.

Layer 3: HMAC with a key outside the database

Replace SHA-256 with HMAC (RFC 2104), a hash keyed with a secret, here a key that isn't stored in the database:

import { createHmac } from "node:crypto"

const entryHash = (key: string, prevHash: string, content: string) =>
  createHmac("sha256", key).update(prevHash).update(content).digest("hex")

Now someone with only database write access (a leaked database password, a stolen backup, a compromised DB admin account) can't produce valid hashes for a rewritten history. To rewrite it unnoticed, they need both write access to the database and the key.

Each row is checked again when it's read, and an endpoint verifies the whole chain and points to exactly which row breaks it.

Layer 4: detecting a cut-off end

Hash chains have a blind spot: if someone deletes the last N rows, what remains is still a perfectly valid chain. Nothing is "broken".

The fix: each device remembers the latest entry it has verified. On the next check, that entry must still be there, unchanged. If it's gone, the history has been truncated. It's the idea of anchoring state somewhere outside the database's reach, the same way transparency logs ask clients to remember what they've seen, and how the E2EE chat app detects a server hiding messages.

Diagram of a hash chain of four rows, each linked to the previous row's hash. Row 2 is edited, so its hash changes; row 3's stored previous hash no longer matches and the chain breaks there, and every later row stops matching too. A separate note shows the blind spot: deleting rows 3 and 4 leaves a valid chain, so each device remembers the last row it verified.Row 1hash = H(start, content)prevRow 2, editedcontent changed, so its hash changesprevRow 3stored prev ≠ new hash of row 2chain breaks hereprevRow 4every later row stops matching tooCut-off enddeleting rows 3–4 leaves a valid chain, so eachdevice remembers the last row it verified
A chain catches edits in the middle; only an anchor kept outside the database catches a cut-off end.

Upgrading the algorithm without opening a hole

Adding HMAC after the app had been running a while meant the table held both old (SHA-256) and new (HMAC) rows. Each row carries a chain_version column saying how to verify it.

But watch out for a downgrade attack: if old-version rows are accepted anywhere, an attacker just appends more "old-version" rows, whose hashes anyone can compute. The rule that blocks it is simple: once the chain has a new-version row, any old-version row after it is forged.

const forged = seenV2 && row.chainVersion < 2

Privacy and immutability pull in opposite directions

An audit log should record IP addresses and browsers to spot unfamiliar devices. But keeping IPs forever isn't great for privacy; they should be cleared after a while. Yet the table is append-only, and the hash covers the IP.

The answer has two parts:

  1. The hash doesn't cover the raw IP and browser, but an HMAC of them. Clear the raw values and the hash still matches, because the HMAC is still there.
  2. The trigger allows exactly one kind of update: setting ip and user_agent to NULL, provided every other column stays the same.
CREATE OR REPLACE FUNCTION audit_log_append_only() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'UPDATE' AND OLD.chain_version >= 2
     AND NEW.ip IS NULL AND NEW.user_agent IS NULL
     AND (to_jsonb(NEW) - 'ip' - 'user_agent') = (to_jsonb(OLD) - 'ip' - 'user_agent') THEN
    RETURN NEW;
  END IF;
  RAISE EXCEPTION 'audit_log is append-only';
END $$;

The to_jsonb(row) - 'column' trick (the jsonb - text operator removes a key from a JSON object) compares "every column except these two" without listing them, so the trigger doesn't go stale when the table gains a column.

From a log to user-facing features

Once the log is solid, features on top of it are cheap:

  • A History page: filter by data type, record, device and date; see who changed what, from where, and restore the previous version.
  • Grouped changes: one action that writes many records at once (say, an expense applied to every following month) shows as one entry and can be restored as a group.
  • Unfamiliar device alerts: when a device never seen before edits data, the other devices are notified.

Limits

It's worth being clear about whom this design protects against. It protects against someone with access to the database but not the key. The application server holds the key, so if the server itself is compromised, history can still be rewritten. To cover that case too, stricter systems periodically publish the chain's head hash somewhere independent, or sign with a key held in a key management service (KMS) that the server can call but never read.

For a personal app, the first three layers already make rewriting history far more expensive, at the cost of one HMAC per write.

Summary

  • Make the log append-only with triggers on UPDATE, DELETE and TRUNCATE, and write each entry in the same transaction as the change it records.
  • A hash chain per user shows exactly where history was edited, but a plain SHA-256 chain can be recomputed by anyone with write access.
  • HMAC with a key kept outside the database means rewriting history unnoticed takes both database write access and the key.
  • A chain can't see a cut-off end, so each device remembers the latest entry it verified and checks it's still there.
  • When upgrading the algorithm, treat any old-version row after a new-version row as forged.
  • Hash an HMAC of the IP and browser instead of the raw values, so the raw values can be cleared later through the one update the trigger allows.
  • None of this protects against a compromised application server, because it holds the key.

If you've built a tamper-evident log a different way, I'd love to compare notes. My email is on the home page.