Append-only audit log model

Entity model

Entity-relationship model

Each event records the actor, the action, the entity affected, and the full before/after state, plus the hash of the previous event and its own hash. Events form a chain per tenant: every entry commits to the one before it.

Isolation policy

Append-only enforcement

Append-only is a database guarantee, not an application convention. The application role is granted INSERT and SELECT only; UPDATE and DELETE are revoked, and a trigger rejects them outright so even a privileged path cannot rewrite history. The log can grow and be read — never modified.

REVOKE UPDATE, DELETE ON audit_events FROM app_role;

CREATE OR REPLACE FUNCTION reject_mutation() RETURNS trigger AS $body$
BEGIN
    RAISE EXCEPTION 'audit_events is append-only';
END;
$body$ LANGUAGE plpgsql;

CREATE TRIGGER audit_no_mutation
    BEFORE UPDATE OR DELETE ON audit_events
    FOR EACH ROW EXECUTE FUNCTION reject_mutation();

Definition

Tamper-evidence via hash chaining

Each event's hash is computed over the previous event's hash and this event's canonical payload. A single altered or removed row breaks the chain from that point forward, so tampering is detectable by re-walking the chain — you cannot change one entry without recomputing every entry after it.

-- Recompute each row's hash from the prior hash + payload and
-- assert it matches the stored hash. Any mismatch = tampering.
WITH ordered AS (
    SELECT id, prev_hash, hash,
           digest(COALESCE(prev_hash, '') ||
                  entity_type || entity_id::text ||
                  COALESCE(before::text,'') || COALESCE(after::text,''),
                  'sha256') AS recomputed
    FROM audit_events
    ORDER BY created_at
)
SELECT id FROM ordered WHERE hash <> recomputed;   -- expect zero rows

Contract

What every event must capture