Designing an Append-Only Audit Log Schema
This page answers one narrow operational question: your circulation pipeline writes an “audit” table that is supposed to prove what happened — which patron checked out which item, which report was generated, which record was purged — but that table is a plain relational table, so any process with a connection can UPDATE or DELETE its rows and leave no trace. At audit time the log proves nothing, because nothing stops it from having been rewritten. This sits under Compliance Reporting for Circulation Data, which covers how circulation activity is reported to regulators and consortium partners, and within the broader Patron Validation & Privacy Data Routing architecture. If a state auditor or a consortium data-sharing agreement ever asks you to demonstrate that a circulation event log has not been altered, the problem below is the one you will need to have solved in advance.
Problem Framing
The symptom is not an error. Everything about the log looks correct: rows arrive in order, timestamps are monotonic, the compliance report queries run. The failure only becomes visible when you try to prove the log is trustworthy — and discover that the database will happily let you rewrite it. The one-line reproduction is a statement that should be impossible against an immutable audit record but instead returns success:
circdb=> UPDATE circulation_audit
circdb-> SET patron_ref = 'REDACTED', event_type = 'renewal'
circdb-> WHERE audit_id = 48213;
UPDATE 1
circdb=> DELETE FROM circulation_audit WHERE audit_id = 48214;
DELETE 1
Both statements succeeded. A row that recorded a real checkout was silently rewritten to look like a renewal, and the row after it was erased outright — and the table has no way to know either thing happened. There is no error, no flag, no residue. When an auditor later asks “can you demonstrate this event log is complete and unaltered?”, the honest answer is no: any account with write access, any migration script, any compromised service, or any well-meaning developer running a one-off cleanup could have changed history, and the table itself would look exactly the same afterward.
Root Cause
The root cause is that a plain table is not an audit log. A conventional relational table is designed to be mutable — that is its purpose — and it offers zero tamper evidence. Three properties are missing at once. First, the schema grants UPDATE and DELETE to the same role that performs INSERT, so nothing at the privilege layer distinguishes appending a new fact from altering an old one. Second, rows are independent: each row stands alone, so deleting the middle of a sequence leaves no gap you can detect and editing a field leaves no fingerprint. Third, there is no cryptographic linkage — nothing binds row N to row N−1, so a rewritten row is indistinguishable from a genuine one.
Naming a table audit or adding a created_at column does not fix any of this; it just labels a mutable table with an aspirational name. An append-only log needs the opposite defaults from an operational table: writes must be restricted to appends, and the historical integrity of the sequence must be self-verifying without trusting the database administrator, the application, or the backups. The disposition-per-decision discipline the Compliance Reporting for Circulation Data contract already applies to what gets logged has to be matched by a schema that governs whether a logged row can ever change.
Solution
Make the log tamper-evident with a hash chain, and make it append-only at the privilege layer. Each row stores the hash of the previous row (prev_hash) and its own hash (row_hash), where row_hash = sha256(prev_hash + canonical_event_bytes). This binds every row to the entire history before it: change any earlier field and every subsequent row_hash no longer recomputes, so a single edit invalidates the whole tail of the chain. On top of the chain, the schema must forbid mutation — revoke UPDATE and DELETE from the writer role, and back it with a trigger (or a WORM object store) so that even a privileged mistake cannot silently rewrite history.
The DDL establishes the shape and the append-only guarantee:
CREATE TABLE circulation_audit (
seq BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
logged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
event_type TEXT NOT NULL,
patron_ref TEXT NOT NULL, -- HMAC surrogate, never raw PII
item_barcode TEXT NOT NULL,
payload JSONB NOT NULL,
prev_hash CHAR(64) NOT NULL, -- row_hash of seq - 1
row_hash CHAR(64) NOT NULL UNIQUE
);
-- Append-only: the writer may INSERT, nothing more.
REVOKE UPDATE, DELETE, TRUNCATE ON circulation_audit FROM audit_writer;
GRANT INSERT, SELECT ON circulation_audit TO audit_writer;
-- Belt and suspenders: reject mutation even from a privileged slip.
CREATE FUNCTION reject_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'circulation_audit is append-only (attempted %)', TG_OP;
END;
$$;
CREATE TRIGGER no_update_delete
BEFORE UPDATE OR DELETE OR TRUNCATE ON circulation_audit
FOR EACH STATEMENT EXECUTE FUNCTION reject_mutation();
The writer computes the chain deterministically. The key detail is canonicalization: the bytes you hash must be reproducible, so serialize the event with sorted keys and a fixed separator, or two verifiers will disagree over whitespace and every check will fail spuriously.
from __future__ import annotations
import hashlib
import json
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
logger = logging.getLogger("audit.chain")
GENESIS_HASH = "0" * 64 # prev_hash of the first row
class AuditChainError(RuntimeError):
"""Raised when the audit chain cannot be extended or fails to verify."""
@dataclass(frozen=True)
class AuditEvent:
event_type: str
patron_ref: str # already an HMAC surrogate, not a raw patron id
item_barcode: str
payload: dict[str, str]
logged_at: datetime = field(default_factory=lambda: datetime.now(timezone.utc))
def canonical_bytes(self, prev_hash: str) -> bytes:
"""Deterministic serialization: sorted keys, no incidental whitespace."""
body = {
"event_type": self.event_type,
"patron_ref": self.patron_ref,
"item_barcode": self.item_barcode,
"payload": self.payload,
"logged_at": self.logged_at.isoformat(),
"prev_hash": prev_hash,
}
return json.dumps(body, sort_keys=True, separators=(",", ":")).encode("utf-8")
def row_hash(self, prev_hash: str) -> str:
return hashlib.sha256(self.canonical_bytes(prev_hash)).hexdigest()
def append_event(conn, event: AuditEvent) -> str:
"""Chain a new event onto the tail of the log inside one transaction.
Reads the current tail hash, computes the new row_hash, and inserts. The
UNIQUE constraint on row_hash plus a SELECT ... FOR UPDATE tail lock keep
concurrent appenders from forking the chain.
"""
with conn: # transaction: commit on success, roll back on exception
with conn.cursor() as cur:
cur.execute(
"SELECT row_hash FROM circulation_audit "
"ORDER BY seq DESC LIMIT 1 FOR UPDATE"
)
row = cur.fetchone()
prev_hash = row[0] if row else GENESIS_HASH
row_hash = event.row_hash(prev_hash)
try:
cur.execute(
"INSERT INTO circulation_audit "
"(event_type, patron_ref, item_barcode, payload, "
" prev_hash, row_hash, logged_at) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)",
(
event.event_type, event.patron_ref, event.item_barcode,
json.dumps(event.payload, sort_keys=True),
prev_hash, row_hash, event.logged_at,
),
)
except Exception as exc: # unique violation, trigger, connection loss
logger.error(
"audit_append_failed",
extra={"event_type": event.event_type, "prev_hash": prev_hash},
)
raise AuditChainError("could not extend audit chain") from exc
logger.info(
"audit_appended",
extra={"event_type": event.event_type, "row_hash": row_hash},
)
return row_hash
The verifier walks the chain from genesis and recomputes every hash, which is what turns “we hope nobody edited it” into a provable statement. A break tells you not just that the log was altered but where:
def verify_chain(conn) -> None:
"""Walk the whole log; raise AuditChainError at the first broken link."""
expected_prev = GENESIS_HASH
with conn.cursor() as cur:
cur.execute(
"SELECT seq, event_type, patron_ref, item_barcode, payload, "
" logged_at, prev_hash, row_hash "
"FROM circulation_audit ORDER BY seq ASC"
)
for (seq, event_type, patron_ref, item_barcode, payload,
logged_at, prev_hash, row_hash) in cur:
if prev_hash != expected_prev:
raise AuditChainError(
f"chain break at seq={seq}: prev_hash does not match tail"
)
event = AuditEvent(
event_type=event_type, patron_ref=patron_ref,
item_barcode=item_barcode, payload=payload, logged_at=logged_at,
)
if event.row_hash(prev_hash) != row_hash:
raise AuditChainError(f"row tampered at seq={seq}: hash mismatch")
expected_prev = row_hash
logger.info("audit_chain_verified", extra={"tail_hash": expected_prev})
The behavioural change is decisive. Before, the UPDATE and DELETE from the reproduction succeeded and vanished. After, the trigger rejects them outright — and even if an attacker bypasses the application and edits the raw storage, the recompute walk in verify_chain fails at the exact seq they touched, because that row’s row_hash no longer matches and every downstream prev_hash is now wrong. A deleted middle row breaks the chain at the row that followed it. For an offsite anchor, periodically publish the current tail hash to an external WORM store or a notarization service; anyone holding a past tail hash can then prove the log has not been rewound behind that point.
Compliance or Privacy Impact
A tamper-evident log is a privacy liability if you chain the wrong bytes, so the schema’s rule is that the log stores hashed or tokenized identifiers, never raw PII. The patron_ref column holds an HMAC surrogate produced by the same tokenization scheme the export-masking contract uses, so a checkout can be correlated across events without the log itself becoming a plaintext record of who borrowed what — apply the field dispositions from PII Masking in Patron Data Exports before an event ever reaches append_event. This is not optional hygiene: because the log is by design impossible to edit or delete, any raw identifier written into it is effectively permanent, which turns a careless field into a retention violation you cannot remediate by deleting the row.
That permanence forces an explicit retention design. Immutability and a retention schedule are in tension — you cannot honor a right-to-erasure request by deleting an audit row without shattering the chain. Resolve it the way the Data Retention Policies for Public Libraries schedule prescribes: keep only tokenized references in the log, hold the token-to-patron mapping in a separate, mutable, access-controlled store, and satisfy erasure by destroying the mapping key so the surrogate becomes irreversibly anonymous while the chain stays intact. Age out entire chain segments by sealing a closed period (publish its final tail hash, then archive the whole segment to WORM storage) rather than by deleting individual rows.
Verification
Confirm both guarantees with a test: mutation must be impossible, and a forced tamper must be detected at the right position.
import pytest
def test_append_only_rejects_mutation(conn) -> None:
append_event(conn, AuditEvent(
event_type="checkout", patron_ref="hmac:ab12",
item_barcode="31234000000001", payload={"branch": "MAIN"},
))
# The trigger must refuse both UPDATE and DELETE.
with conn.cursor() as cur:
with pytest.raises(Exception):
cur.execute("UPDATE circulation_audit SET event_type = 'renewal'")
with pytest.raises(Exception):
cur.execute("DELETE FROM circulation_audit")
def test_verifier_detects_a_forced_edit(conn) -> None:
for i in range(3):
append_event(conn, AuditEvent(
event_type="checkout", patron_ref=f"hmac:{i:04x}",
item_barcode=f"3123400000000{i}", payload={"n": str(i)},
))
verify_chain(conn) # intact chain verifies cleanly
# Bypass the trigger as a superuser to simulate storage-level tampering.
with conn.cursor() as cur:
cur.execute("ALTER TABLE circulation_audit DISABLE TRIGGER no_update_delete")
cur.execute("UPDATE circulation_audit SET patron_ref = 'forged' WHERE seq = 2")
cur.execute("ALTER TABLE circulation_audit ENABLE TRIGGER no_update_delete")
with pytest.raises(AuditChainError) as excinfo:
verify_chain(conn)
assert "seq=2" in str(excinfo.value) # detected at the exact tampered row
For a running pipeline, schedule verify_chain as a periodic job and alert on any AuditChainError, and record each successful run’s tail hash so consecutive verifications form their own audit trail. A verification that was passing and now fails at a known seq is unambiguous evidence of tampering and its location — the property the plain table could never give you. Track the append rate alongside the verified tail hash so a stalled writer (a chain that stops growing) is as visible as a broken one.
Related
- Compliance Reporting for Circulation Data — the parent guide to reporting circulation activity that this audit schema underpins.
- Patron Validation & Privacy Data Routing — the architecture governing how patron data is validated, masked, and routed.
- Building Batch Reconciliation Reports for Circulation — reconciling circulation totals against the events this log records.
- Generating State-Specific Retention Exports — producing the retention exports whose provenance this chain proves.