How do you design a system so an auditor can prove who accessed a record and why?

A log and an audit trail are different artefacts

Application logs exist to help engineers understand behaviour. They are written on a best-effort path, sampled under load, schema-free by convention, owned by whichever team wrote the line, rotated away in weeks, and freely readable by anyone with access to the observability stack. Every one of those properties is correct for debugging and disqualifying for evidence.

An audit trail is a record with obligations attached. Its capture is guaranteed, not best-effort: if the audit write cannot be made, the access it describes must not be permitted to complete, which usually means the audit record and the data access share a transaction or the access is gated on a durable append. Its schema is fixed and versioned, because an auditor will ask the same question of records written three years apart. Its retention is set by regulation rather than by disk cost. And its access is narrower than the application's, not wider.

The practical consequence is that "we log everything to the SIEM" is not an answer to this question. It may be a useful input to it. But a log you can lose under load, whose fields drifted when someone refactored a controller, cannot support the sentence an auditor needs, which is: this named person read this record at this time for this stated reason, and here is why you should believe that record is complete.

Tamper-evidence versus tamper-proofing

Be honest about this distinction, because interviewers use it to separate people who have thought about it from people repeating vocabulary. Tamper-proofing, in the sense of making modification impossible, is not achievable by software that runs on infrastructure someone administers. Anyone with sufficient privilege over the storage can, in principle, rewrite it. What you can achieve is tamper-evidence: making undetected modification require capabilities that no single party holds.

Hash chaining is the mechanism. Each record includes a hash over its own canonical form plus the hash of the previous record, so altering any historic record invalidates every hash after it. That alone is insufficient, because an attacker with write access can recompute the whole chain. It becomes meaningful when the chain head is periodically published somewhere the attacker does not control: signed with a key held in a hardware module they cannot use, written to append-only storage with an object-lock retention period that the storage administrator cannot shorten, exported to a system owned by a different team, or countersigned by an external timestamping service. Now rewriting history requires colluding across boundaries, and the discrepancy between the published head and the recomputed chain is the evidence.

Detecting alteration is the easier half. Detecting deletion of the most recent records, or non-recording of an access that happened, is harder and needs the chain to be dense and continuously anchored, plus a monotonically increasing sequence so a missing record leaves a hole rather than no trace at all.

What has to be captured

Five things, and the fifth is the one people forget.

The actor must be the authenticated human identity, not the connection pool's database user and not the service account. Where one principal acts for another, capture both the acting identity and the on-behalf-of identity, because a support agent using a customer's session is a materially different event from the customer using it. The subject is the identifier of the record touched, plus its type, at a granularity that matches the question being asked: "queried the patients table" is not evidence about a patient. The action comes from a closed vocabulary and must distinguish read from export, because bulk extraction is the event that regulators care about most and it looks identical to a read if you only record verbs loosely. The time is taken server-side in UTC from a synchronised clock, and it is worth also recording the sequence position, since two events in the same millisecond still have an order.

The fifth is justification, and it has to be a captured field rather than something reconstructed. In practice this is a purpose code from a controlled list, tied to the lawful basis or business process that authorises the access, with a mandatory free-text note for exceptional paths such as break-glass. Purpose is the field that makes the difference between proving that access occurred and proving it was legitimate, which is what an auditor is really asking. Capture the outcome too: denied attempts are often the most interesting rows in the table.

The copy you did not mean to make

The thing that separates a strong answer here is refusing to store the record itself. It is tempting, and it feels helpful: put the row's before-and-after values in the audit entry so investigators can see exactly what was seen. Under many privacy and health-data regimes it is the wrong call, and sometimes prohibited.

The reasoning is structural. An audit store holding payloads is a second copy of the sensitive data, and it is a copy with the worst possible properties: broader read access, because compliance and security teams need it; longer retention, because audit retention outlives record retention; weaker application-level controls, because the fine-grained authorisation logic lives in the application and not in the audit query path; and immunity from deletion, because you have deliberately made it append-only. You have taken data you are obliged to minimise, retain briefly and be able to erase, and put it somewhere maximised, retained for years and undeletable.

Store identifiers and metadata. If an investigation genuinely needs to know what a value was at a point in time, that is a versioning or change-data-capture requirement on the data store, held under the same protections as the record itself, and referenced from the audit trail rather than embedded in it. Field names are usually fine to record; field values usually are not. The same rule kills the other common defect, which is audit entries containing credentials, tokens or full card numbers because someone serialised a whole request object.

Retention against the right to erasure

These obligations look contradictory and are not. Privacy regimes such as GDPR give a data subject a right to erasure, but that right is not absolute: it yields where continued processing is necessary to comply with a legal obligation. An audit trail that a financial or health-sector regulation requires you to keep is exactly that case, so erasing the underlying record does not oblige you to erase the evidence that it was accessed.

Design for the two to coexist rather than arguing about it later. Reference subjects by a stable internal identifier or pseudonym instead of by name, email or national identifier, so the audit trail retains referential value without holding directly identifying data. Keep the mapping from pseudonym to person in the operational store, where erasure applies to it. Where a regime does require a personal detail inside the audit record, hold it encrypted under a per-subject key so the key can be destroyed, which renders that field unrecoverable while leaving the chain intact. And write the retention schedule down per record class, because "keep forever" is itself a compliance failure in several regimes and an auditor will ask who authorised it.

Separation of duties

The design fails if one person can both change a record and change its history, so make that structurally impossible rather than a matter of policy. The application's database role gets insert and select on the audit table and nothing else, with update, delete and truncate revoked, enforced at the database rather than in code. The table owner, who can always bypass such grants, is a role no engineer and no application holds interactively; schema changes to it go through a change process with a second approver. Better still, the durable copy lives outside the operational database entirely, in append-only storage or a different account, so compromising the application database does not reach it.

-- The application can only ever add rows. Enforced by grants, not by
-- discipline: the code path that could delete does not have the privilege.
GRANT  INSERT, SELECT ON access_audit TO app_role;
REVOKE UPDATE, DELETE, TRUNCATE ON access_audit FROM app_role;

CREATE TABLE access_audit (
  seq          bigserial PRIMARY KEY,   -- a gap is itself evidence
  occurred_at  timestamptz NOT NULL,    -- server clock, UTC, never client-supplied
  actor_id     text        NOT NULL,    -- the human, not the pool user
  on_behalf_of text,                    -- set when acting for another principal
  action       text        NOT NULL,    -- READ | EXPORT | AMEND | DENY
  subject_type text        NOT NULL,
  subject_id   text        NOT NULL,    -- the identifier only, never the record
  purpose_code text        NOT NULL,    -- closed vocabulary; the "why"
  purpose_note text,                    -- mandatory for break-glass paths
  outcome      text        NOT NULL,
  request_id   text        NOT NULL,    -- ties the row back to the trace
  prev_hash    bytea       NOT NULL,
  row_hash     bytea       NOT NULL     -- H(prev_hash || canonical form of this row)
);

Two more separations complete it. The people who review the audit trail should not be the people whose actions dominate it, which is why review typically sits with compliance or an internal audit function rather than with the engineering team. And reads of the audit trail are themselves auditable events, recorded to a different sink so the recursion terminates.

Enforcement point matters as much as structure. If each application call site is responsible for remembering to write the audit record, the trail is complete only where developers remembered. Put capture at a chokepoint that cannot be bypassed: a data-access layer every query goes through, a database-side trigger, or change-data-capture off the write-ahead log for amendments. Reads are harder, since they leave no trace in the log, which is the argument for routing all access to protected records through a single service rather than letting reporting tools connect directly.

An auditor is not asking whether your records are intact; they are asking whether they are complete and whether their integrity depends on trusting the person being audited. Design so that the answer to both is demonstrable without your word for it.