Make it append-only and immutable, chain each entry to the previous one with a hash so tampering is detectable, partition by time so old data ages out cheaply, and archive sealed partitions to WORM object storage (write-once) with a retention policy — never allow UPDATE/ on live records.
DELETEservice ──emit event──▶ append-only writer
│ hash = H(prev_hash || payload)
▼
audit_2026_07 (hot partition) ◀── query by actor/time
│ month sealed
▼
WORM object store (S3 Object Lock) ── retention: 7y
│
periodic anchor: publish latest hash externally
An audit log's whole value is that you can trust it later, so the schema must forbid mutation. Grant the app INSERT only — no UPDATE/DELETE — and enforce it at the DB role level, not just in code. On top of that, build a hash chain: each row stores hash = SHA256(prev_hash || this_row). Change any historical row and every subsequent hash breaks, so tampering is detectable even by someone with DB access. Periodically anchor the latest hash somewhere you don't control (a notary, a public ledger, a signed email) so even an admin who rewrites the whole chain can't match the anchored value.
Multi-year logs grow without bound, so partition by time (e.g. monthly range partitions). Benefits: queries filter by partition, and retiring old data is a metadata DETACH/DROP PARTITION — instant, not a giant DELETE that bloats the table. Sealed partitions move to WORM storage — S3 Object Lock in compliance mode physically prevents deletion until the retention date, satisfying regulations like SOX/HIPAA. Keep recent months hot in Postgres for fast queries; everything older is cold in object storage, queried on demand.
The probe is whether you understand that "audit log" means immutability and provability, not just "a table with a timestamp". Good: append-only table. Great: append-only enforced by DB role, hash chain with external anchoring, time partitioning for retention, WORM for compliance. Common trap: storing audit rows in the same mutable app table (a bug or a malicious DBA can silently rewrite history) — auditors will reject it.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate