Temporal Tables vs. Audit Logs: Pick the Right History Model

For: A backend engineering lead at a Series B SaaS company who just got a compliance or legal request they cannot answer — 'show me the exact state of this record on this date' — and is realizing their current event log or updated_at timestamp column cannot reconstruct point-in-time state without a painful manual query

Short answer: if you need to answer who changed this and when, use an audit log. If you need to answer what did this record look like on March 14, use SQL temporal tables (system-versioned tables). If you need both — and most SaaS teams eventually do — build a bitemporal data model on top of temporal tables and keep the audit log as a thin actor/intent record. Do not try to reconstruct point-in-time state from an audit log. Teams that try end up rewriting the same replay logic in every new report.

This post is written for the backend lead who just got a legal or compliance request against a production system that has an updated_at column and maybe a half-hearted changes table — and is now realizing those artifacts cannot cleanly reproduce a row's state at an arbitrary timestamp.

The distinction that trips teams up

Audit logs and temporal tables look similar on a whiteboard. Both store history. Both have timestamps. Both can be queried. But they are optimized for opposite questions.

• Audit logs are append-only records of actions. Row identity is the event. Schema is usually (event_id, actor_id, entity_type, entity_id, action, diff, occurred_at). You read them forward: "tell me every change to invoice 8821."
• Temporal tables are versioned copies of entity state. Row identity is the entity. Every version has a valid_from / valid_to range. You read them by point-in-time predicate: "give me invoice 8821 as of 2024-03-14 09:00 UTC."

The failure mode is subtle. An audit log with a full row snapshot in the diff column looks like it can answer point-in-time questions. It can, in principle. But you have to scan every event before time T, replay them in order, and materialize state. That query gets written once, works fine on invoice 8821, and then someone asks for it across 400,000 invoices for a SOC 2 report. Now you are writing recursive CTEs at 11pm.

Slowly changing dimensions (SCD Type 2, in the warehouse vocabulary) are the same shape as temporal tables — valid_from, valid_to, is_current. If your team already runs a warehouse with SCD2 dims, temporal tables are the OLTP-side equivalent of that discipline.

The three real options

Ignore the greenfield event-sourcing evangelism for a minute. You have a production Postgres or SQL Server database with existing rows. Your realistic choices are:

1. Application-level audit log table — a changes table populated by ORM hooks or triggers.
2. SQL-native temporal tables — SQL Server's SYSTEM_VERSIONING, or in Postgres via periods extension, temporal_tables extension, or hand-rolled history tables with triggers.
3. Event sourcing — the domain state is derived from an append-only event stream; there is no "current" table, only projections.

Head-to-head comparison

Option 1: Audit log table

Right for: teams whose compliance requirement is "prove we know who did what." Wrong for: teams whose next request will be "reproduce this customer's account state on the day the dispute started."

The typical implementation is a changes table with entity_type, entity_id, actor_id, action, before (JSONB), after (JSONB), created_at. Populated via ORM callbacks (Django signals, ActiveRecord callbacks, SQLAlchemy events) or database triggers.

Where it breaks: schema evolution. Your after JSONB from 18 months ago references customer.plan_tier as an integer. Today it's a string enum. Your replay logic has to know that. Every schema change adds a branch to the replay function. Nobody documents these branches. Two years in, the audit log is technically correct and functionally unqueryable.

Option 2: Temporal tables

Right for: teams whose primary pain is point-in-time queries — regulatory reporting, dispute resolution, "show me the pricing this customer saw when they signed." Wrong for: teams that need rich actor/intent metadata on every change (temporal tables tell you the row changed at 09:14:22 UTC, not why).

SQL Server has had SYSTEM_VERSIONING since 2016. It's one of the most underrated features in the product. You declare a history table, and the engine transparently maintains it. Queries use SELECT ... FROM Invoices FOR SYSTEM_TIME AS OF '2024-03-14 09:00:00'. The optimizer handles the rest.

Postgres does not have this natively. You get it via the temporal_tables extension (Vlad Arkhipov's, still the reference implementation), the newer periods extension, or hand-rolled triggers that copy the OLD row to a _history table with valid_from and valid_to on every UPDATE and DELETE. Hand-rolled is not hard. It is about 40 lines of PL/pgSQL per table. The tradeoff: no standard syntax, so every query for historical state is a bespoke join with a BETWEEN predicate.

Where it breaks: capturing who and why. Temporal tables preserve state changes but not the actor context. You solve this by joining the history table to a session/request log on transaction time, or by adding changed_by and change_reason columns to the base table itself so they get versioned along with everything else. The latter is uglier but far easier to query later.

Option 3: Event sourcing

Right for: greenfield systems where the domain is genuinely event-shaped (payments, orders, ledgers). Wrong for: retrofitting onto an existing CRUD SaaS with 40 tables and a working ORM.

Event sourcing is the correct answer to "we need perfect history" and the wrong answer to "we need it by next quarter." The migration cost is real. Every read path becomes a projection. Every schema change is an event-versioning problem. GDPR right-to-erasure fights the append-only nature of the store, and the workarounds (crypto-shredding, tombstones) are their own project.

If you are on the fence: you are not the team for event sourcing. Teams that need it know they need it because their domain experts talk in events already.

The bitemporal answer for teams that need both

Most SaaS companies past Series B need both questions answered. Compliance auditors want actor trails. Product and finance want point-in-time state. The clean solution is a bitemporal data model:

• System time (transaction time) — when the database recorded a fact. Managed by temporal tables.
• Valid time (business time) — when the fact was true in the real world. Managed by explicit columns on the entity.

These are not the same. A contract signed on March 1 but backdated in the system on March 5 has a valid_from of March 1 and a system-recorded_at of March 5. Auditors care about both. Temporal tables handle system time natively. Valid time is your domain model's job.

The practical stack for a Postgres shop looks like this:

1. Enable trigger-based history tables (or use periods) for every table where point-in-time queries matter. Not every table needs this — users, orgs, contracts, invoices, feature flags yes; join tables and derived caches no.
2. Add valid_from and valid_to to entities where the business concept of "when this became true" differs from the write time.
3. Keep a slim audit log for actor and intent: (actor_id, entity_type, entity_id, action, request_id, occurred_at). No before/after payload — that lives in the history table.
4. Correlate the two with request_id or transaction ID so you can join "who did this" to "what changed."

This gives you a clean separation. State reconstruction is a temporal query. Accountability is an audit log query. Neither has to do the other's job.

Migration reality check

You have production data. The retrofit path that actually works:

1. Pick one high-value table first — usually the one the compliance ticket is about. Do not boil the ocean.
2. Create the history table with matching schema plus valid_from, valid_to, and operation (I/U/D).
3. Backfill: insert every current row as the first version, with valid_from = COALESCE(updated_at, created_at) and valid_to = 'infinity'. Yes, you lose the pre-existing history. Document that explicitly.
4. Add the trigger. Deploy behind a feature flag if you can.
5. Write one canonical point-in-time function per table: get_invoice_as_of(invoice_id, at_timestamp). Force every consumer through it. This is how you prevent the ad-hoc SQL debt from returning.

The teams that get this right treat the history table like a public API. Versioned, tested, with a single query interface. The teams that don't end up with seven variations of the same replay CTE scattered across the reporting codebase.

When to bring in help

Retrofitting history onto a system that was not designed for it is a data modeling problem first and a schema migration second. If the tables in question hold financial, medical, or regulated data — for example, an accounting SaaS like GimBooks or a lending platform where credit decisions must be reproducible like Cashpo — the design decisions get harder because "lose the pre-existing history" is not an acceptable answer. That's usually the point at which teams engage outside help, either to audit the model or to run the migration alongside their in-house engineers. Our digital transformation practice handles this class of retrofit, but the more important thing is to make the temporal-vs-audit-log call correctly before you write any migration code.

Frequently Asked Questions

Can I use Postgres logical replication or WAL as an audit log?

You can capture changes from the WAL using tools like Debezium, but the WAL is a physical change stream, not a business-level audit log. It lacks actor and intent context, and it is not designed to be queried for historical state. Use it to feed a downstream history store, not as the store itself.

Do temporal tables slow down writes noticeably?

Every UPDATE and DELETE becomes an UPDATE plus an INSERT into the history table. In practice this is a small percentage overhead for OLTP workloads and is dominated by index maintenance costs. The bigger operational concern is storage growth on high-churn tables — a settings table that gets updated every minute per tenant will produce a large history. Partition the history table by month or archive to cold storage.

How do temporal tables interact with GDPR right-to-erasure?

Badly, if you do nothing. A deletion request means the personal data has to be removed from both the current table and every historical version. The common patterns are: crypto-shredding (encrypt personal fields per-user, delete the key on erasure), or a documented policy of hard-deleting matching history rows with a separate audit entry recording that the erasure occurred. Talk to counsel before choosing; both are defensible but the choice depends on your data.

Should I use JSONB snapshots or column-per-column history?

Column-per-column mirroring the base table. JSONB looks flexible but breaks under schema evolution — you cannot cleanly query "what was the plan_tier on this date" if the field's type has changed twice. Mirror the schema and let the history table evolve with ALTER TABLE statements applied to both.

How long should I retain history?

Governed by the strictest applicable regulation, not by storage cost. SOC 2 typically implies at least one audit cycle; HIPAA is six years; financial records vary by jurisdiction. Retention is a policy decision, not an engineering one — get it in writing from legal, then partition and archive accordingly. For a personalized assessment of what history model fits your compliance profile, contact CodeNicely.