mufassall

Cheat sheet · Business processes

Audit log

14 min · Intermediate · mufassall.dtwin.uz/en/l/audit-log/

← Open the lesson

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
Final scene

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_auditdb

Mermaid: 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_1187

D2

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_auditdb

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Changes without an actor14/day0/day
API p9545 ms58 ms
Audit on fast disks0 GB380 GB
Unnoticed tampering00

Steps

  1. Problem Bank details changed, and nobody can say who

    A customer complains that this month's payout never arrived.

  2. 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.

  3. Problem Two years later the audit table owns the database

    Every action writes a row and nothing is ever deleted.

  4. Fix Same transaction, separate store: the outbox

    The API still writes the event in the business transaction, but into a small audit_outbox table.

  5. Fix Append-only, one partition per month

    The audit store has one table split into monthly partitions.

  6. Problem Someone with superuser access deletes a row

    Permissions stop applications, not a database superuser.

  7. 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.

  8. Fix Signed checkpoints anchor the chain

    A careful insider could delete a row and rebuild every hash after it.

  9. 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.

  10. 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.

  11. Case The investigation: one request id, the whole story

    Back to the payout.

  12. 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.