Problem RestatementProblem
Design an audit log for a SaaS product (asked at Snowflake). Every security-relevant action is recorded: logins, permission changes, data exports, settings changes, API key creation. It says who did what to which resource, when, and from where. Customers (tenants) search their own audit logs and export them to their security tools (SIEM), and auditors must be able to trust the logs were not changed or deleted.
RequirementsRequirements
- Ingest audit events from all services. No event may be lost.
- Query by tenant, time range, actor, action and resource, with pagination.
- Tamper-evident: any change or deletion can be detected.
- Retention per tenant or plan (e.g., 1 year, 7 years), then deletion.
- Export and stream to external SIEMs (Splunk, Datadog).
- Strict access: only authorized tenant admins can read their own logs.
1.1 Scale
- 10K tenants, 2B events/day (~25K/sec). Each ~1 KB → 2 TB/day raw, compressed about 5–10x.
Event Format
{ "event_id": "uuid", "tenant_id": "t-42", "ts": "2026-09-19T10:15:02Z",
"actor": { "type": "user", "id": "u-7", "ip": "203.0.113.5", "user_agent": "..." },
"action": "role.grant", "resource": { "type": "warehouse", "id": "wh-3" },
"result": "success", "details": { "role": "admin", "grantee": "u-9" },
"request_id": "r-abc" }A shared schema with a fixed list of action names, so queries and exports are consistent across services.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
S["Product services"] -->|"same transaction"| OB[("Outbox table")]
OB --> REL["Outbox relay"]
REL --> K[("Kafka - audit topic, by tenant")]
K --> W["Writer - hash chain per tenant"]
W --> HOT[("Hot store - search, 90 days")]
W --> ARC[("Object storage - WORM, long retention")]
W --> DIG[("Signed daily digests")]
K --> EXP["SIEM exporters"]
ADM["Tenant admin UI / API"] --> Q["Query service - authz"]
Q --> HOT
Q --> ARCDeep Dive — An audit log an auditor will acceptDeep dive
An audit log's value is entirely in whether it can be trusted. A log an administrator can quietly edit is documentation, not evidence.
Insert audit rows in the application database
The service writes the action and an audit_events row.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
ACT["Permission change"] --> DB[("App database")]
DB --> ROW["audit_events row"]
ROW --> EDIT["Anyone with write access can UPDATE or DELETE it"]
ROW --> LOSS["Action commits, audit insert fails - no record at all"]
EDIT --> USE["An attacker who gets admin erases their own trail first"]Two independent failures. The record can be changed by exactly the people an audit log exists to hold accountable. And because the two writes are separate, an action can succeed while its audit record does not — the gap is small and it is the gap an attacker aims for.
Append-only table, published to a log
Revoke update and delete on the audit table, and publish events to Kafka for downstream storage and search.
Casual tampering is now blocked and the data leaves the application's blast radius, which is real progress. But database permissions are enforced by the same database an administrator controls, so a sufficiently privileged user can still alter history and nothing would show it. And publishing after the action commits means an event can still be lost if the publish fails in between.
Write it inside the transaction, then make changes detectable
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
TX["One transaction: the action + an outbox row"] --> C["Commit"]
C --> RELAY["Relay publishes outbox rows"]
RELAY --> K[("Kafka - dedup by event_id")]
K --> STORE["Event store - hash = SHA256(prev_hash + event), per tenant"]
STORE --> DIG["Daily digest of the latest hash - signed, stored separately"]
STORE --> ARCH[("Archive - object lock, WORM, retention period")]
DIG --> VERIFY["Auditor: recompute the chain, compare with signed digests"]- The outbox pattern makes the audit record atomic with the action. Both commit or neither does, so "it happened but was not logged" stops being possible. The relay then publishes at least once, and
event_idremoves duplicates. - A hash chain per tenant makes tampering detectable rather than merely difficult. Each event stores
SHA256(prev_hash + event), so altering or removing any event breaks every hash after it. Sign the latest hash daily and store that digest somewhere the same administrators do not control — the signature is what converts "the chain is intact" into evidence. - Write-once archival storage with object lock means even an account administrator cannot delete archives before their retention expires.
The distinction worth stating: you cannot prevent a sufficiently privileged insider from changing data they control. You can make any change provably visible, and that is what an auditor is actually asking for.
Partition by tenant_id and day, and require the tenant in every query — it keeps queries fast and makes cross-tenant leakage a schema-level impossibility rather than a code review responsibility.
Querying and Export
- The API requires
tenant_id(from the caller's auth, never from user input), plus filters. It uses cursor pagination by(ts, event_id). - Reading audit logs is itself audited ("admin X exported logs").
- SIEM streaming: per-tenant exporters push events (HTTPS, syslog) with retries, and track a per-tenant cursor so they resume after failures.
Retention and Deletion
- Retention per tenant plan. When expired, delete whole daily partitions (cheap), and let WORM locks expire first.
- Personal data inside audit logs may need special handling for privacy laws. Keep a minimal actor ID and resolve names at read time where possible.
Trade-offs & AlternativesTrade-offs
| Decision | Choice | Why | Alternative |
|---|---|---|---|
| Delivery | Outbox + Kafka | No lost events tied to real actions | Fire-and-forget logging: gaps |
| Integrity | Hash chain + signed digests + WORM | Tampering detectable and prevented | Plain DB rows: silently editable |
| Storage | Hot search + cold object storage | Fast recent queries, cheap history | All in search cluster: expensive |
| Isolation | Partition by tenant | Security and speed | Mixed data: risky queries |
Wrap-UpWrap-up
Have services write audit events through a transactional outbox into Kafka, so every committed action is logged. A writer adds a per-tenant hash chain and daily signed digests, storing events in a hot search store for recent queries and in write-once object storage for long retention. Serve tenant-scoped, audited queries with cursor pagination, stream to customer SIEMs with resumable exporters, and apply retention by dropping expired partitions.