Building a Hash-Chained Audit Log in PostgreSQL

The exact task this guide solves is making the compliance audit trail tamper-evident inside the database, so that any retroactive edit to a scheduling override or a documented exception is detectable rather than silent. An inspector will demand to see why the engine believed a pairing was legal six months ago, and the answer is only trustworthy if the record cannot have been quietly altered since. A hash chain — where each entry commits to the SHA-256 digest of its predecessor — turns any edit into a break that propagates through every later digest. This page builds the append-only table, a trigger that computes and enforces the chain, and a verification walk. It implements the audit requirements from the system security and access boundaries section and records the verdicts produced by the duty time validation rule engines.

Prerequisites

Step 1 — Model the append-only chain

Each entry stores its payload, its own digest, and the digest of the previous entry. The genesis entry commits to a fixed sentinel. Storing the previous hash explicitly, rather than recomputing it on read, lets the verification walk confirm the chain without reconstructing history.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE audit_log (
    seq         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    written_utc timestamptz NOT NULL DEFAULT now(),
    operator    text        NOT NULL,
    action      text        NOT NULL,
    payload     jsonb       NOT NULL,
    prev_hash   bytea       NOT NULL,
    entry_hash  bytea       NOT NULL
);

-- Revoke mutation; the application role may only append.
REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC;

Verify: the table accepts INSERT from the application role but rejects UPDATE and DELETE, so the chain can only grow.

Step 2 — Compute the chain in a trigger

A BEFORE INSERT trigger reads the current chain tip, sets the new entry’s prev_hash to it, and computes entry_hash over the previous hash concatenated with a canonical serialisation of the payload. Doing this in the trigger means the application cannot forge a hash — the database owns the chain.

CREATE FUNCTION audit_chain() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    tip bytea;
BEGIN
    SELECT entry_hash INTO tip FROM audit_log ORDER BY seq DESC LIMIT 1;
    NEW.prev_hash := COALESCE(tip, digest('genesis', 'sha256'));
    NEW.entry_hash := digest(
        NEW.prev_hash
        || convert_to(
            jsonb_build_object(
                'operator', NEW.operator,
                'action',   NEW.action,
                'payload',  NEW.payload
            )::text,
            'UTF8'
        ),
        'sha256'
    );
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_chain
    BEFORE INSERT ON audit_log
    FOR EACH ROW EXECUTE FUNCTION audit_chain();

Verify: inserting two entries yields a second whose prev_hash equals the first’s entry_hash; the genesis entry’s prev_hash equals digest('genesis', 'sha256').

Step 3 — Walk the chain to detect tampering

Verification re-computes each entry’s expected hash from its stored predecessor and payload and compares it to the stored entry_hash. The first row where they diverge is the point of tampering — and every row after it also fails, because the break propagates.

CREATE FUNCTION verify_audit_chain()
RETURNS TABLE (seq bigint, ok boolean) LANGUAGE sql STABLE AS $$
    SELECT
        a.seq,
        a.entry_hash = digest(
            a.prev_hash
            || convert_to(
                jsonb_build_object('operator', a.operator, 'action', a.action, 'payload', a.payload)::text,
                'UTF8'
            ),
            'sha256'
        )
        AND a.prev_hash = COALESCE(
            lag(a.entry_hash) OVER (ORDER BY a.seq),
            digest('genesis', 'sha256')
        ) AS ok
    FROM audit_log AS a
    ORDER BY a.seq;
$$;

Verify: SELECT * FROM verify_audit_chain() WHERE NOT ok returns no rows for an untampered log; after any row’s payload is altered (by a superuser bypassing the revoke), that row and all later rows appear.

Tamper propagation through a hash chain Three audit entries, each committing to the previous entry's SHA-256 digest. Editing the payload of the middle entry recomputes its digest, which no longer matches what the third entry recorded as its previous hash, so the verification walk flags the middle entry and every entry after it. Entry 1 · ok prev = genesis hash = 8c2f…a11 Entry 2 · edited prev = 8c2f…a11 hash = 4f9a…c1e → 91b0…7dd Entry 3 · breaks prev = 4f9a…c1e ≠ recomputed 91b0…7dd Editing entry 2 changes its digest; entry 3's stored prev no longer matches → chain breaks from here on.
Tamper propagation: altering one entry recomputes its digest, so every later entry's recorded previous hash no longer matches — the verification walk pinpoints the first broken link.

Failure modes and troubleshooting

Frequently Asked Questions

Does a hash chain prevent tampering?

No — it makes tampering detectable, not impossible. A sufficiently privileged actor can still edit a row, but doing so breaks the chain from that row onward, and the verification walk surfaces exactly where. Combined with append-only privileges and off-site digest anchoring, that is what auditors accept as tamper-evidence.

Why compute the hash in a trigger instead of the application?

Because the chain must be owned by the trust boundary that stores it. If the application computes the hash, a compromised or buggy client can write a self-consistent but forged entry. The trigger guarantees every entry is chained by the database itself.

How often should the chain be verified?

On a schedule — commonly nightly — and on demand before any audit export. The walk is a single pass over the table, so it is cheap enough to run frequently, and frequent runs narrow the window in which undetected tampering could exist.

Can the log be anchored beyond the database?

Yes. Periodically publishing the current chain tip to an append-only external store or a timestamping authority anchors the whole history: even a full-database rewrite cannot reproduce a tip that was already published elsewhere.

Back to System Security & Access Boundaries.