Audit Log with Short-Lived Online Data
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 operation