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 enforced at the database, not by application convention. The application role is granted INSERT and SELECT only; UPDATE and DELETE are revoked and a trigger rejects them, so no application code path — privileged or not — can rewrite history. A database superuser with direct access is outside this control, which is why the hash chain and external anchoring below provide a second, detection-based layer. Through the application the log can grow and be read, not 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

Hash chaining is tamper-evident, not tamper-proof: an actor with write access to the whole table and the hashing logic can rewrite the entire chain consistently. To detect that, periodically anchor the head hash to an external append-only store (a notary, another system's log); a mismatch against the anchor reveals a full-chain rewrite that the internal check alone cannot.

Contract

External anchoring

The head hash of each chain MUST be periodically written to an external append-only store the application cannot rewrite — an object store with immutability enabled (e.g. S3 Object Lock in compliance mode), a managed audit trail (e.g. CloudTrail), or a timestamping/notary service. The anchor is what turns a full-chain rewrite from undetectable into detectable: a rewritten chain will not match a head hash already committed elsewhere.

Anchoring runs on a schedule — default hourly — recording the current head hash, its event id, and a timestamp. The interval bounds the exposure: a rewrite is detectable up to the last anchor, so a shorter interval narrows how much recent history could be silently altered.

Anchoring MUST NOT block the append path. If the external store is unavailable, the failure is logged and retried with backoff while appends continue, and a sustained anchoring outage alerts — an un-anchored chain is a degraded state, not a stopped one. Verification compares the anchored head hash at each point against the chain recomputed from the database; a mismatch is tampering.

Anchoring MUST NOT sit on the synchronous append path; an anchor-store outage degrades detectability (logged, retried, alerted) but never blocks writing new events.

Contract

What every event must capture

Contract

Before/after state format

The `before` and `after` fields are JSON and MUST carry a `schema_version`, so an entry written under an older shape can still be interpreted. They are full snapshots of the entity on each side of the change, not diffs — reconstructing any point reads one entry, never a replay of deltas from the beginning.

Large values are not inlined: an object above a size threshold is stored externally and the entry records a reference plus a content hash of the stored bytes, so the entry stays bounded and the referenced object is itself tamper-evident. The reference and hash are part of the canonical payload, so they participate in the chain.

Sensitive fields — passwords, secrets, tokens, and regulated PII — MUST NOT be written in clear. They are redacted or tokenised before the entry is hashed, and the redaction is deterministic so the hash is stable and reproducible; that a field was redacted is itself recorded, so the audit shows a value changed without exposing it.

{
  "schema_version": 3,
  "id": "acc_9f2…",
  "name": "Acme Ltd",
  "password": "«redacted»",
  "id_number": "«tokenised:tok_9f2…»",
  "contract_pdf": { "$ref": "s3://audit-blobs/…", "sha256": "3b1f…" }
}

before/after are full snapshots, never diffs; sensitive fields MUST be redacted deterministically before hashing, and large values stored by reference-plus-hash rather than inlined.

Contract

Performance and scalability

A hash chain serialises writes: each event's hash depends on the previous one, so a single chain has a single writer. Append is a sequential insert — cheap — but per-chain throughput is bounded by that serialisation. Chains are therefore partitioned (below) so independent chains append in parallel, and high-rate producers batch events into one transaction to amortise the round-trip while preserving order within the batch.

Full verification is O(n) — a re-walk recomputing every hash. For large logs it runs in segments (a bounded range verified against its boundary hashes) and off-peak. Where cheap inclusion proofs are needed, a Merkle tree over each segment gives an O(log n) proof that a given event belongs to a verified segment without re-walking the whole chain.

Partitioning follows natural boundaries: one chain per tenant (isolation and parallelism together), and date partitions within a tenant (a chain per day or month) so a partition is a bounded, independently-verifiable, independently-anchorable segment. Each partition carries the prior partition's closing hash as its opening link, so the partitions still form one logical chain across the boundary.

Contract

Rotation and retention

Entries are retained for a defined period — default seven years, or whatever the governing regulation requires — and retention is a floor: an entry MUST NOT be purged before it, regardless of storage pressure. Rotation moves closed partitions from the hot database to cheaper archival storage; it does not delete them.

Archiving preserves chain integrity: a partition is verified and anchored before it moves, and it is archived together with its opening and closing hashes, so the archived segment still verifies on its own and still links to the partitions on either side. The archive store is itself append-only — the same immutability the anchor relies on.

Purging past the retention period MUST NOT break the surviving chain. A purged segment is replaced by a retained checkpoint — its closing hash and external anchor — so verification of everything after it still starts from a proven link even though the purged events are gone. The checkpoint proves the purged history existed and was intact when it was removed.

Retention is a floor (default 7 years); entries MUST NOT be purged early, and a purge past retention leaves a signed checkpoint (closing hash + anchor) so the surviving chain still verifies from a proven link.

Contract

Verification API and process

Verification is exposed as a read-only endpoint — `GET /audit/verify?tenant=&from=&to=` — that re-walks the requested range, compares each recomputed hash against the stored hash and against the external anchors covering the range, and returns a structured result: overall status, the id of the first broken link if any, and the anchors checked. It reads; it never mutates the log.

Verification runs on a schedule as well as on demand: incremental verification of new events continuously, and a full (or full-segmented) re-walk daily, each compared against the external anchors so both internal breaks and full-chain rewrites are caught.

A verification failure is an incident, not a warning. It MUST alert and page immediately, the affected range and failing id are captured as evidence, and the response follows the security incident-response path — the audit log's own integrity is a security-critical signal, and a break in it is treated as potential compromise until proven otherwise.

{
  "status": "ok",                 // or "broken"
  "range": { "from": "…", "to": "…", "events": 48213 },
  "first_broken_id": null,        // earliest event whose hash mismatches
  "anchors_checked": 12,          // external head-hash anchors in range
  "verified_at": "2026-08-25T09:00:00Z"
}

Verification is read-only and runs on demand and on a schedule (incremental continuously, full daily), always compared against the external anchors; a failure MUST page and enter incident response, not merely log.