When a setting changes unexpectedly, engineers need three answers: what changed, who changed it, and what was there before? Recent investigations and older compliance queries have different latency and cost needs. This article presents a generic design using two short-lived online tables and a long-term warehouse. The examples are fictional and the numbers illustrative. Requirements and alternatives Assume a service manages project settings. The common request is “show the latest changes to this project, then let me inspect one.” That fast path also supports urgent operations: an operator can find the latest event, inspect its before value, and use it to prepare an immediate, authorized rollback. The audit record supplies evidence quickly; rollback remains a separate write with authorization, validation, and concurrency checks. Older requests may cover months of history or many projects. They can tolerate more latency and benefit from SQL. The system therefore needs fast recent entity lookup, independent long-term retention, one history API, and explicit errors when coverage is missing. Approach Strength Tradeoff One relational table Simple SQL and transactional writes Long retention grows indexes and competes with business traffic Database auditing Captures sessions and database objects Does not automatically explain the business action or end user Search logging Flexible exploration Indexing and retention are costly to operate Managed audit service Ingestion and export are provided Schema, isolation, limits, completeness, and price may not fit Event stream Durable transport and replay Still needs a serving model for entity history Warehouse only Efficient long-range analysis Freshness and per-entity latency depend on ingestion and compute Two online tables + warehouse Fast recent browsing and affordable history Extra writes, archive validation, and mixed-source pagination Why two online tables? Finding events and reading their full contents are different operations. A page may list 30 events whose payloads are large nested documents. Grouping every payload under an entity key makes that partition grow with both event count and payload size. The timeline table is a compact access path: partition key: (tenant_id, entity_id) order: occurred_at DESC, event_id ASC value: event_id and minimal listing fields The payload table is keyed by event identity: key: (tenant_id, event_id) value: entity, time, actor, action, before, after, schema version The payload is self-contained so it can be archived without joining to an expired timeline. One event ID must always identify the same contents; conflicting duplicates are errors. The split reduces entity-partition growth, but it does not cap total bytes or write rate. It costs two writes and an additional payload fetch. If one relational table meets measured targets, use it. A payload write can succeed while the pointer write fails, leaving a readable event that is not listed. The reverse creates a dangling pointer. Payload-first ordering reduces the second risk, but only a transaction or repairable protocol removes the gap. Retries must reuse the same event ID. Short TTL and long retention Let R be events per day, P payload bytes, I timeline bytes, and T online-retention days. Online logical storage is approximately R × T × (P + I). Keeping a year online instead of a week multiplies the retained online window by about 52 under steady traffic. That is not a total-cost promise: archived bytes, ingestion, queries, and operations still cost money. Apply short TTLs to both online tables. Physical deletion is often asynchronous, and a retry may refresh a fixed write-time TTL. Readers should separately apply an event-time visibility rule such as occurred_at + T > now. The archive must preserve events rather than mirror a shrinking source table. Landing or staging receives exported payloads; retained history inserts unseen (tenant_id, event_id) records and applies independent retention. A keyed merge is idempotent only when it checks conflicting contents. A latest-state export can miss events that disappear before capture. Successful upload is not proof of complete coverage. Use offsets, reconciliation, or a documented snapshot and recovery contract. Do not copy source deletes into retained history automatically. Online expiry is not a request to erase historical records. The archive is another data store, with its own authorization, retention, and deletion controls. One history API Do not hard-code “recent is online, old is archived.” During overlap, an event exists in both places; during lag, coverage may be uncertain. For a tenant and entity: Establish which sources cover the requested range. Read online and archived candidates. Deduplicate by (tenant_id, event_id) and reject conflicts. Order by occurred_at DESC, event_id ASC. Refill until there are N + 1 distinct readable events. Return N events and an opaque, scope-bound cursor. For cursor (t, id), the next page contains occurred_at < t OR (occurred_at = t AND event_id > id). If a pointer has no payload, try the archive; if neither source can provide it, return an incomplete result. If the warehouse is unavailable for an old range, do not return an empty page. Deterministic ordering is not a frozen snapshot: late events can sort before a cursor. Capture and safety boundaries Publishing before the business commit can record a change that never happened. Publishing afterward can lose it in a crash. A transactional outbox or durable change feed provides a stronger boundary; otherwise call capture best effort. If capture reads an object and then performs an unconditional update, another writer may commit between those operations. Exact before/after evidence needs a transaction, conditional version check, or storage change record. Caller-supplied service names are context, not authenticated identity. Test duplicate delivery, conflicting IDs, partial writes, concurrent updates, online expiry, delayed export, store overlap, equal timestamps, warehouse outages, and unauthorized payload reads. Measure latency, archive lag, missing-pointer rates, conflicts, and per-entity bytes. Decision Two short-lived online tables plus a warehouse fit workloads that need immediate lookup—such as preparing a safe rollback from the latest before-value—and affordable historical investigation. The timeline keeps entity reads light; event-keyed payloads preserve details; the archive keeps history after online expiry. The price is extra writes, hydration, coverage tracking, and careful pagination. Choose a simpler or managed alternative when measured requirements favor it. Whatever the storage, retries must preserve event identity, audit records must mean what they claim, and missing history must never masquerade as no history.