"We need an audit trail" is not a requirement. This is what it has to become
before anyone can build or test it.
Auditable entity: payment instruction
WHAT IS RECORDED - one immutable row per state transition
event id uuid, unique, generated at write time
entity payment_instruction id and version
what changed field, before value, after value. Before value is
mandatory. A null before value is a defect, not
an empty case.
actor authenticated user id AND the role in effect at
that moment, because roles change
on whose authority the approval reference, where the action needed one
source channel and client ip, or the batch job id and the
file it was reading
when event time from the application, in UTC, plus the
local timezone it was performed in
why reason code from a controlled list, free text only
in addition to it, never instead
IMMUTABLE HOW
- append-only table, no UPDATE or DELETE grant to the application role
- write is in the same transaction as the change it describes, so a
change without its audit row is impossible rather than unlikely
- hash chain over the previous row, verified by a nightly job that
alerts on a break
- backups of this table are restore-tested quarterly
RETAINED HOW LONG
7 years after the instruction's final state, not 7 years after
creation. So the clock starts at closure, and the row cannot be
deleted while the parent could still be reopened.
WHAT THIS BLOCKS
- a subject-access deletion request does not remove these rows. It
pseudonymises the actor for staff, and the legal basis for
retention is recorded against the exemption.
- no in-place UPDATE on payment_instruction anywhere in the codebase.
This is enforced by grants, not by review.
Every line here exists because a vaguer version failed an audit somewhere. The
one to notice first is the mandatory before-value: without it the trail tells you
a change occurred and not what the state was, which answers none of the questions
an auditor asks.
The role-in-effect field is the detail candidates never mention. Recording the
user identifier alone means that when someone's permissions change, the trail can
no longer establish whether they were entitled to act at the time — and that is
precisely the question asked when something has gone wrong.
The immutability section is where "non-negotiable" becomes engineering. Immutable
by convention means mutable, so the guarantee has to rest on grants the
application does not hold and on a hash chain that makes tampering detectable.
Writing the audit row in the same transaction is what turns a completeness
requirement into an invariant.
The retention clock is the subtle one. Starting it at creation is the intuitive
implementation and it is wrong for anything that can reopen, because the parent
outlives its own retention period. That single mistake is a common finding.
The last block is the honest part: this requirement removes options elsewhere,
including the right-to-erasure path, and those conflicts are resolved by legal
basis rather than by engineering preference.