Audit Log Design and Event Sourcing

Audit log basics

Append-only record of significant actions: who, what, when, on which object, with what payload, what outcome. Requirements: immutable (no edits), tamper-evident (hash chain or signing), queryable (by user/time/object), retained per policy, performant (must not slow the action being audited).

The two-store architecture

Query patterns conflict with append-only storage. So I use two:

• Cold append-only log: Postgres with INSERT-only role, S3 with object lock, AWS QLDB, or Kafka with infinite retention. Source of truth, hash-chained.
• Search index: Elasticsearch denormalized by user/time/target/action. Rebuildable from the cold log.

Queries hit the index; detail clicks fetch the full event by event_id from cold storage.

Schema essentials

event_id, occurred_at, recorded_at (skew detection), actor_type/id, action, target_type/id, before_value, after_value, metadata (IP/UA/request-id), outcome, prev_hash, hash.

Tamper-evidence

Hash chain: each event's hash = SHA256(prev_hash || payload). Tampering breaks all subsequent hashes. Periodically publish latest hash externally.

For regulated industries: signed events + Merkle root in a notary store. For most enterprise: hash chain + immutable storage is enough.

Writing reliably — outbox or CDC

Audit writes must be atomic with the business action. Two patterns:

• Outbox: app writes business change + audit event to its own DB in one transaction; separate worker drains outbox to Kafka. Atomic for business; eventual for audit pipeline.
• CDC (Debezium → Kafka): listen to the WAL; produce automatically. No explicit publish in code.

Never direct dual-write (DB then Kafka separately) — if app crashes between, audit is lost.

Event Sourcing — the next step

In ES, the event log IS the source of truth. No current-state table is authoritative; current state is fold(events). Read models (current-state tables) are projections, rebuildable from events.

Benefits

• Perfect history — you know exactly what happened and when
• Time travel — reconstruct state at any past moment
• New views for free — project from events
• Bug fixes via replay
• Natural CQRS fit — events are C, projections are Q

Costs

• Schema evolution is hard — events are immutable, but interpretation evolves; need upcasters
• Performance — replaying millions of events is slow without snapshots
• Complexity — think in events, not state mutations
• Eventual consistency between command and query

When to use ES

Use for:

• Natural-event domains (order lifecycle, account ledger, document edits)
• Temporal queries are core ('what did account X look like on Dec 31?')
• Audit must be perfect (banking, healthcare, accounting)
• Multiple read views from same data
• Undo/redo as a feature

Don't use for:

• Simple CRUD with no history
• Performance-critical paths sensitive to projection lag
• Teams new to the pattern (steep curve, expensive mistakes)
• Unstable schemas (event versioning becomes a nightmare)

Pragmatic middle ground: rich audit logging for most things; full ES only for bounded contexts that genuinely need it (the payments ledger, the order workflow).

ES implementation details

Snapshots: replay millions of events is slow; periodically snapshot current state, replay only events since the snapshot.

Projections: subscribers consume events, update denormalized read stores. Each projection is independent and rebuildable from the log.

Event versioning: events are immutable but understanding evolves. Use upcasters — functions transforming old event versions to new at read time. Or versioned event types (OrderCreatedV1, V2). Never edit historical events.

Idempotent handlers: projections might re-process; upsert keyed by event_id.

Tooling: EventStoreDB, Marten, Axon Framework handle snapshots / projections / versioning out of the box.

Failure handling

• Outbox lag: alert if backlog > N minutes; scale workers
• Indexer lag: show 'last indexed at' in audit UI
• Hash chain break: periodic verification job; alert on mismatch
• Storage growth: tier to S3 Glacier; honor retention policy
• ES projection corruption: drop the table, replay with fixed handler — this is THE superpower
• ES schema break: upcaster transforms; never modify history

Recommendation

For most systems: rich audit logging — immutable append-only log (Postgres/S3/QLDB) + Elasticsearch index, written reliably via outbox or CDC, hash-chained for tamper evidence. Reserve full Event Sourcing for bounded contexts where temporal queries, perfect history, or multiple read models are the value (financial ledgers, complex workflows). Plain audit logging covers 90% of compliance and security forensics needs without taking on ES complexity. When you do reach for ES, lean on snapshots, design for upcasters from day one, and use battle-tested tooling like EventStoreDB or Axon.