Cheat sheet · Business processes
Audit log
The problem
Someone in the back office changed a customer's bank account, the next payout went to a stranger, and nobody can prove who did it. This lesson builds an audit trail that answers who did what, when, from where, and what it looked like before and after, and that survives crashes, a growing table and an insider with database access.
What you learn
- What an audit event holds and why it is written in the same transaction as the change
- Moving audit to its own append-only store through an outbox, partitioned by month
- Making tampering visible with a hash chain and signed checkpoints
- Personal data in the audit, a separate reader role, retention on write-once storage and an investigation by request id
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_admin(("Back-office user"))
n_api["Back-office API"]
n_db[("Customer database")]
n_relay("Audit relay")
n_auditdb[("Audit store")]
n_signer>"Signing service"]
n_archive[("WORM archive")]
n_investigator(("Investigator"))
n_admin --> n_api
n_api --> n_db
n_db --> n_relay
n_relay --> n_auditdb
n_auditdb --> n_signer
n_auditdb --> n_archive
n_investigator --> n_auditdbMermaid: request sequence
sequenceDiagram
participant n_api as Back-office API
participant n_db as Customer database
participant n_relay as Audit relay
participant n_auditdb as Audit store
actor n_insider as Insider
participant n_signer as Signing service
actor n_investigator as Investigator
participant n_archive as WORM archive
Note over n_api,n_db: Bank details changed, and nobody can say who
n_api->>n_db: UPDATE customers
n_db-->>n_api: UPDATE 1
Note over n_api,n_db: One audit event per business action
n_api->>n_db: audit_event
Note over n_api,n_db: Two years later the audit table owns the database
n_api->>n_db: db stats, two years later
Note over n_api,n_db: Same transaction, separate store: the outbox
n_api->>n_db: BEGIN#59;
n_db-->>n_api: COMMIT
Note over n_relay,n_auditdb: Append-only, one partition per month
n_relay->>n_auditdb: CREATE TABLE audit_events (... , at timestamptz)
Note over n_insider,n_auditdb: Someone with superuser access deletes a row
n_insider->>n_auditdb: -- superuser session, 23:52
n_auditdb-->>n_insider: DELETE 1
Note over n_relay,n_auditdb: Each record carries the hash of the previous one
n_relay->>n_auditdb: audit-verify partition=2026_11 (hourly)
Note over n_auditdb,n_signer: Signed checkpoints anchor the chain
n_auditdb->>n_signer: POST /v1/sign HTTP/1.1
n_signer-->>n_auditdb: HTTP/1.1 200 OK
Note over n_investigator,n_auditdb: The audit contains personal data: minimise it and lock reading
n_investigator->>n_auditdb: CREATE ROLE audit_reader#59;
Note over n_auditdb,n_archive: Retention: old months go to write-once storage
n_auditdb->>n_archive: export audit_events_2025_10 -#62; archive/2025/10/events.jsonl.zst
Note over n_investigator,n_auditdb: The investigation: one request id, the whole story
n_investigator->>n_auditdb: SELECT at, actor_id, action, target, ip
n_auditdb-->>n_investigator: 09:41:07.312 #124; u_17 #124; customer.bank_account.update #124; customer:c_1187D2
direction: right
n_admin: "Back-office user" {
shape: person
}
n_api: "Back-office API" {
shape: rectangle
}
n_db: "Customer database" {
shape: cylinder
}
n_relay: "Audit relay" {
shape: rectangle
style.multiple: true
}
n_auditdb: "Audit store" {
shape: cylinder
}
n_signer: "Signing service" {
shape: cloud
}
n_archive: "WORM archive" {
shape: stored_data
}
n_investigator: "Investigator" {
shape: person
}
n_admin -> n_api
n_api -> n_db
n_db -> n_relay
n_relay -> n_auditdb
n_auditdb -> n_signer
n_auditdb -> n_archive
n_investigator -> n_auditdbPlantUML: request sequence
@startuml
participant "Back-office API" as n_api
database "Customer database" as n_db
participant "Audit relay" as n_relay
database "Audit store" as n_auditdb
actor "Insider" as n_insider
boundary "Signing service" as n_signer
actor "Investigator" as n_investigator
database "WORM archive" as n_archive
note over n_api, n_db : Bank details changed, and nobody can say who
n_api -> n_db : UPDATE customers
n_db --> n_api : UPDATE 1
note over n_api, n_db : One audit event per business action
n_api -> n_db : audit_event
note over n_api, n_db : Two years later the audit table owns the database
n_api -> n_db : db stats, two years later
note over n_api, n_db : Same transaction, separate store: the outbox
n_api -> n_db : BEGIN;
n_db --> n_api : COMMIT
note over n_relay, n_auditdb : Append-only, one partition per month
n_relay -> n_auditdb : CREATE TABLE audit_events (... , at timestamptz)
note over n_insider, n_auditdb : Someone with superuser access deletes a row
n_insider -> n_auditdb : -- superuser session, 23:52
n_auditdb --> n_insider : DELETE 1
note over n_relay, n_auditdb : Each record carries the hash of the previous one
n_relay -> n_auditdb : audit-verify partition=2026_11 (hourly)
note over n_auditdb, n_signer : Signed checkpoints anchor the chain
n_auditdb -> n_signer : POST /v1/sign HTTP/1.1
n_signer --> n_auditdb : HTTP/1.1 200 OK
note over n_investigator, n_auditdb : The audit contains personal data: minimise it and lock reading
n_investigator -> n_auditdb : CREATE ROLE audit_reader;
note over n_auditdb, n_archive : Retention: old months go to write-once storage
n_auditdb -> n_archive : export audit_events_2025_10 -> archive/2025/10/events.jsonl.zst
note over n_investigator, n_auditdb : The investigation: one request id, the whole story
n_investigator -> n_auditdb : SELECT at, actor_id, action, target, ip
n_auditdb --> n_investigator : 09:41:07.312 | u_17 | customer.bank_account.update | customer:c_1187
@endumlPlantUML: components
@startuml
left to right direction
actor "Back-office user" as n_admin
component "Back-office API" as n_api
database "Customer database" as n_db
collections "Audit relay" as n_relay
database "Audit store" as n_auditdb
cloud "Signing service" as n_signer
storage "WORM archive" as n_archive
actor "Investigator" as n_investigator
n_admin --> n_api
n_api --> n_db
n_db --> n_relay
n_relay --> n_auditdb
n_auditdb --> n_signer
n_auditdb --> n_archive
n_investigator --> n_auditdb
@endumlRecap
Six rules for an audit log.
1) One event per business action: actor, action, target, time, before and after, request id. 2) Write it in the same transaction as the change; ship it through an outbox, because a lost record is worse than a slow one. 3) Keep it in its own append-only store, partitioned by month. 4) Chain the records by hash and sign the head regularly with a key outside the database. 5) Minimise personal data and give reading its own audited role. 6) Move old months to write-once storage, verified against the checkpoints.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Changes without an actor | 14/day | 0/day |
| API p95 | 45 ms | 58 ms |
| Audit on fast disks | 0 GB | 380 GB |
| Unnoticed tampering | 0 | 0 |
Steps
- Problem Bank details changed, and nobody can say who
A customer complains that this month's payout never arrived.
- Fix One audit event per business action
Every business action now writes an event: who did it, what action, on what, when, the value before and after, the request it came from and the network.
- Problem Two years later the audit table owns the database
Every action writes a row and nothing is ever deleted.
- Fix Same transaction, separate store: the outbox
The API still writes the event in the business transaction, but into a small
audit_outboxtable. - Fix Append-only, one partition per month
The audit store has one table split into monthly partitions.
- Problem Someone with superuser access deletes a row
Permissions stop applications, not a database superuser.
- Fix Each record carries the hash of the previous one
Each event stores
prev_hash, and its own hash covers the previous hash plus the event's canonical bytes. - Fix Signed checkpoints anchor the chain
A careful insider could delete a row and rebuild every hash after it.
- Fix The audit contains personal data: minimise it and lock reading
An audit trail collects who did what to whom, so it is personal data itself.
- Fix Retention: old months go to write-once storage
Rules require keeping the audit for several years, but nobody needs last year's events on fast disks.
- Case The investigation: one request id, the whole story
Back to the payout.
- Recap Recap
Six rules for an audit log.
Terms
- Audit log
- An append-only record of business actions: who did what, to what, when, from where, before and after.
- Hash chain
- Each record stores the previous record's hash, so changing or deleting one record breaks every later link.
- WORM storage
- Write once, read many: storage with an object lock. Until the retention date nobody can delete or overwrite the data.