Event logs, audit trails, and replays in SQL
For many systems, you don’t just need the current state — you need to know how you got there. Errors, compliance, and debugging are all easier when you can replay a timeline.
1. Separate current state from history
I usually keep:
- A “current” table with the latest snapshot.
- An “events” or “history” table with append-only changes.
This makes queries for “now” fast, while still letting you reconstruct the past if needed.
2. Design the event table for append-only writes
A typical event log includes:
- Aggregate ID (e.g., user ID, order ID).
- Event type (Created, Updated, StatusChanged, etc.).
- Payload (JSON or typed columns).
- Metadata: timestamp, actor, correlation ID, source system.
3. Support efficient replays
For replays, I like:
- An index on (AggregateId, EventTimestamp).
- Optional “version” field to detect gaps.
- Idempotent replay logic in the consuming service.
4. Balance normalization vs. JSON
For simple systems, a JSON payload per event is fine. As the schema stabilizes, moving high-value fields into typed columns makes reporting and querying easier.