Design an audit log system

The naive approach is: update the database, then call the audit service. If the process crashes between the two, or the audit call times out, the action happened with no record, which is exactly the gap an auditor or attacker cares about. Calling audit first is also wrong: the business transaction may then roll back, leaving a record of something that never happened. The transactional outbox fixes both: the business row and the audit row are written in one local transaction, and a separate relay publishes outbox rows to Kafka. Delivery is at-least-once, so the event_id (generated once at write time) is the dedupe key downstream. For actions that do not touch a database (a read of sensitive data, a data export, a support agent viewing a customer's KYC document), the service must record the audit event synchronously before returning the data and fail the request if it cannot, which is a deliberate fail-closed choice for high-risk reads. For low-risk events you may choose fail-open with a local disk spool. Also cover the edges: actions done directly in the database by engineers (break-glass access) must go through a bastion or proxy that records sessions, otherwise the most powerful actions escape the audit trail.

Append-only tables are not tamper-proof: a DBA or an attacker with admin access can still edit rows. The goal is tamper evidence: any change is detectable. Give each stream (e.g. one per tenant per partition) a strict sequence number, and store hash_n = SHA-256(hash_(n-1) || canonical_bytes(event_n)). Editing or deleting any event changes every later hash and leaves a gap in sequence numbers. A chain alone is not enough, because someone with full access could recompute the whole chain. So periodically (every minute) compute a Merkle root over new events, sign it with a key held in KMS/HSM that application admins cannot export, and write the checkpoint to S3 with Object Lock in compliance mode, which even the root account cannot delete before the retention date. For stronger guarantees, publish checkpoints outside your control (send daily roots to customers or an external timestamping service). Merkle trees also give small inclusion proofs: you can prove one event is in a checkpoint without revealing others. Canonical serialisation matters: hash a fixed byte encoding (e.g. deterministic Protobuf or sorted JSON), not whatever the JSON library outputs today.

Audit logs contain personal data: names, emails, IPs, sometimes PAN or account numbers. Privacy laws (GDPR, India's DPDP Act 2023) give people rights to erasure, while financial regulations require keeping transaction records for years. These conflict with an immutable log. The approach: minimise first, store references (user_id, resource_id) rather than copies of personal data, and redact secrets and full document contents at capture time. For the personal fields that must be stored, encrypt them with a per-data-subject key held in KMS, and include only the ciphertext in the hashed record. When an erasure request is valid and no legal retention applies, delete the key (crypto-shredding): the data becomes unreadable but the hash chain still verifies, because the ciphertext did not change. Legal holds override deletion. Access to the audit log itself is a sensitive action: restrict it by role, scope customers strictly to their tenant (tenant_id filter enforced by the query service, not the UI), and audit every search and export of audit data.

Postgres is fine up to a few hundred million rows with partitioning by month and an index on (tenant_id, ts). ClickHouse handles billions of rows cheaply with columnar compression and fast filtered scans on (tenant_id, ts). Elasticsearch gives flexible full-text search on details but costs more in storage and operations. Audit queries are mostly structured filters, so ClickHouse is a good default; keep the search store as a rebuildable projection, with Kafka plus the archive as the source of truth.

Fail-closed (refuse the action if audit cannot be written) gives completeness but makes the audit pipeline a hard dependency for the product. The outbox gives both for DB-backed actions: the write is local and always succeeds with the transaction. Use fail-closed only for sensitive reads and exports; fail-open with a durable local spool and alerting for low-risk events.

One global chain is simplest to verify but serialises all writes through one sequencer. A chain per tenant (or per partition) parallelises and lets you hand one tenant its own proofs. A global Merkle root per minute over all per-tenant chain heads ties them together.

One enterprise tenant generating 50% of events makes its Kafka partition and chain hot. Split its stream into several sub-chains (tenant#0..7) by hashing the resource id, each with its own sequence; the minute Merkle root covers all of them.

A 5-year query cannot run on the hot store. Route old ranges to Athena/Trino over Parquet in S3 as an async job with results delivered by link, and tell users upfront that archive queries take minutes.

Outbox rows accumulate in each service's database; nothing is lost. Alert on outbox lag; when Kafka returns, relays publish the backlog in order. For services using the direct API, a local disk spool buffers events.

The search store is a projection. Fix the bug, drop the affected partitions and replay from Kafka (recent) or the Parquet archive (older). The chain verification also detects silent corruption.

On restart it reads its last committed checkpoint (sequence and hash) and continues from the next offset; events are hashed deterministically, so reprocessing yields the same hashes and duplicate checkpoints are idempotent by (stream, seq range).