Event Sourcing and Bitemporal Data for Financial Audit Trails

Educational systems analysis only. Nothing here is financial, legal or compliance advice, and nothing is a recommendation about any product or instrument.

Ask a finance system what an account balance was last March and you will usually get one answer. Ask it what the balance was last March according to the books as they stood on 31 March, before a mispriced fee was found and fixed in April, and a surprising number of systems cannot answer at all. They overwrote the old value, so the earlier belief is gone. Auditors, regulators and your own reconciliation team all ask the second question, and they ask it precisely when something has gone wrong.

Event sourcing financial systems exist to make that second question cheap. Instead of storing the latest state, you store every change as an immutable event and derive state by replay. Adding a second time axis, so that each fact records both when it was true in the business and when the system learned it, gives you bitemporal data, which turns “restate the past without destroying it” from a heroic migration into a query.

This article is a reference architecture. It covers the event store, aggregates, snapshots and schema evolution, CQRS projections and their consistency, the two time axes, retroactive corrections, erasure under GDPR, concurrency and the outbox pattern, with runnable Postgres SQL and a Python replay sketch on synthetic data.

What this covers: the event store and its invariants, CQRS read models, valid time vs transaction time, corrections without mutation, crypto-shredding, failure modes, and a decision matrix for when the pattern is worth its cost.

Context and Background

Event sourcing is older than most of the tooling around it. Martin Fowler’s pattern write-up states the idea in one sentence: all changes to application state are stored as a sequence of events, so you can rebuild past states, answer temporal questions and replay history. Accountants will recognise this immediately. A double-entry ledger is an event-sourced system that predates computers by centuries: you never erase an entry, you post a correcting one, and the balance is a fold over the journal.

The bitemporal half of the story comes from the temporal database literature. Fowler’s article on bitemporal history names the two axes “actual history” and “record history”, noting they are also called valid time (or effective time) and transaction time. Actual history is what really happened in the world. Record history is what we knew, and when. The article explains that he preferred “actual” and “record” because audiences found the formal names confusing, and the choice is a fair warning for this article too: the terms are easy to confuse, so definitions come before code.

Standards and databases moved slowly in this direction. SQL:2011 introduced temporal features, including application-time periods (valid time) and system-versioned tables (transaction time). PostgreSQL has long supported the building blocks, range types and GiST exclusion constraints, and PostgreSQL 18’s release notes add temporal constraints: WITHOUT OVERLAPS on PRIMARY KEY and UNIQUE, and PERIOD on foreign keys, applied to the last column. Postgres does not implement the full SQL:2011 system-versioning syntax natively, which is why many teams build the transaction-time axis themselves, as this article does.

Why does this matter for finance specifically? Because financial records have three properties that most application data lacks. They must be reconstructible as of an arbitrary past moment. They are corrected retroactively all the time: late trades, reversed payments, repriced fees, restated positions. And the correction itself is evidence, so it must be kept rather than hidden. A mutable row with an updated_at column satisfies none of these. A purpose-built ledger database gets you double-entry integrity; event sourcing and bitemporality get you history and provenance on top. If your concern is raw debit-credit throughput rather than history, compare the TigerBeetle versus Postgres ledger design before reaching for either pattern.

This article describes engineering patterns. How long you must retain records, which fields regulators expect, and how erasure obligations interact with retention are legal questions that depend on jurisdiction and product. Treat the regulatory comments below as general orientation and take advice from your compliance and legal functions.

The Reference Architecture: Event Store, Aggregates and CQRS

Direct answer: An event-sourced financial system appends immutable events to an ordered store, rebuilds each aggregate’s state by replaying its own stream, and feeds read-optimised projections (CQRS) through a transactional outbox. Reads are eventually consistent with writes; the event log is the single source of truth and every balance is derived from it.

Figure 1: Commands are validated by an aggregate, appended to the event store, relayed through an outbox and projected into read models.

Figure 1 shows the write path on the left and the read path on the right, joined by the outbox. The write side answers one question: is this command legal given the current state of this aggregate, and if so, which events does it produce? The read side answers every other question, from balance lookups to regulatory extracts, from models built for exactly that question. The separation is Command Query Responsibility Segregation (CQRS), and it is the reason event sourcing is practical: replaying a stream to answer a query would be far too slow, but nobody needs to, because projections exist.

The event store and its invariants

An event store is an append-only table, or log, with a small number of non-negotiable invariants. Every event belongs to a stream (usually one aggregate instance, such as one account). Every event has a per-stream version number that increases by one. Events are never updated or deleted. And there is a global order, a sequence that projections can use as a checkpoint.

The per-stream version is what gives you concurrency control. Two writers who both loaded account 1001 at version 7 will both try to append version 8, and a unique constraint on (stream_id, version) lets exactly one win. The loser reloads, re-validates against the new state and retries or fails. This is optimistic concurrency, and it is cheaper and safer than holding row locks across a user interaction. It also encodes the aggregate boundary: a stream is the unit within which invariants are enforced atomically. Anything that spans two streams, a transfer between two accounts for example, needs either a single stream that models both sides, a saga with compensating events, or a ledger engine designed for atomic multi-account postings.

Events must also be self-describing enough to survive years. A good event carries its type, a schema version, a payload, the business effective date, the system recording timestamp, and correlation and causation identifiers that link it to the command and request that produced it. The last two are cheap to add and invaluable during an investigation, which is why the audit logging patterns for AI agents emphasise identity and causation metadata on every record.

Name events in the past tense and in business language: PaymentPosted, FeeCorrected, LimitRaised. An event is a fact that already happened and cannot be refused. Commands, in contrast, are requests and may be rejected. Teams that blur the two end up storing intentions as facts, then discovering they cannot replay them deterministically.

Aggregates, replay and snapshots

An aggregate is a consistency boundary with a behaviour. To handle a command, the application loads the aggregate by folding its stream through a pure function: state = apply(state, event) for each event in order. The decision logic then looks at that state and emits new events or rejects the command. Because apply is pure and has no I/O, replay is deterministic, which is what makes the audit story credible: the same events always produce the same balance.

Replay cost grows with stream length. A current account with a few thousand postings replays in milliseconds; a high-volume merchant settlement account with tens of millions does not. The standard fix is the snapshot: periodically persist the folded state together with the version it covers, then load the latest snapshot and replay only the tail. A snapshot is a cache, never a source of truth. It can be deleted and rebuilt, and it should carry the aggregate code version that produced it so a logic change invalidates it. A common cadence is every N events or every M minutes of activity, tuned by measurement rather than by rule.

There is a subtler design point. Long-lived financial streams are usually a symptom of choosing too coarse an aggregate. If a “customer” aggregate absorbs every posting for ten years, it will be slow and contended. Splitting into account-level streams, and into period-level streams for the highest-volume accounts (one stream per account per day or month), keeps replay bounded without snapshots. The ledger itself can still be queried as a continuous history through projections.

CQRS projections and eventual consistency

A projection is a function that consumes events in global order and maintains a read model: a balance table, a statement view, a regulatory extract, a search index. Projections are disposable by design. If one is wrong, you fix the code, drop the table and rebuild from the log. That property is the main operational benefit, and it is also why projection handlers must be idempotent and deterministic.

Eventual consistency is the price. After a command succeeds, there is a window, typically milliseconds to seconds but unbounded under failure, when the read model has not yet caught up. Systems cope in three ways. They return the new version number from the command and let clients wait until the projection reports that version (read-your-writes by token). They serve the confirmation screen from the command result rather than the read model. Or they accept staleness for views where it is harmless, such as monthly statements, and use the write side for decisions where it is not, such as a funds check, which must read the aggregate, not the projection.

That last rule matters in finance. Never authorise a debit against a projected balance. Projected balances are for display and analytics; authorisation reads the aggregate’s own stream, inside the optimistic-concurrency check. A related caution applies to projection storage choice. Time-series and columnar engines are excellent read models for tick-level history, as the Postgres 18, TimescaleDB and ClickHouse comparison discusses for IoT data, but they are not where you enforce invariants.

The transactional outbox

The most common source of silent data loss in event-driven systems is the dual write: the service commits to its database and then publishes to a broker, and a crash between the two loses the publication. The outbox pattern fixes it by writing the event and an outbox row in the same database transaction. A relay process then reads unpublished outbox rows, publishes them and marks them done, or a log-based change-data-capture tool tails the write-ahead log instead.

The delivery guarantee is at-least-once, so consumers must be idempotent. The usual technique is for each projection to store the highest global sequence it has applied, in the same transaction as its own update, and to ignore anything at or below that mark. With that in place, duplicate delivery is harmless, and restart simply resumes from the checkpoint.

Bitemporal Modeling: Valid Time vs Transaction Time

Event sourcing alone gives you one time axis for free: the order in which the system recorded things. That axis is transaction time. It answers “what did the system know at moment T?” because you can replay the log up to T. What it does not give you is a first-class business axis. A fee charged on 10 March but recorded on 3 April has a recording time of April and a business effect date of March, and queries that conflate them produce the wrong answer for one audience or the other.

Bitemporal data keeps both axes on every fact. Valid time is the period during which a fact was true in the business world. Transaction time is the period during which the system held that fact as its current belief. Fowler’s terms, actual history and record history, map directly onto these. Every question becomes a pair of coordinates: “as of valid date V, as known at transaction time T”.

Figure 2: A bitemporal fact sits at the intersection of what was true and what was known. Corrections add new beliefs and never overwrite old ones.

Why two axes are needed

Consider a savings account whose overdraft limit was 5,000 from 1 January. On 3 April an analyst discovers that a limit increase to 7,000 approved on 1 February was never entered. A single-axis system now has two bad options. Updating the row to say 7,000 from 1 February is correct about the world and erases the fact that, for two months, the books said 5,000, which is what every statement and interest calculation in that window used. Leaving the row alone keeps the record honest and leaves the world wrong.

A bitemporal store does both. The old belief is closed by setting its transaction-time end to 3 April. New rows are written: 5,000 valid from 1 January to 1 February, and 7,000 valid from 1 February onwards, both known from 3 April. A report run in March, asking for the limit on 15 February, correctly returns 5,000. The same query run today returns 7,000. Both answers are right for their frame of reference, and the difference between them is the audit trail of the correction.

This is also why bitemporality matters for the restatements regulators and counterparties see. When a firm resubmits a corrected figure, the question “what did you report originally, and what changed?” is a transaction-time question. The business value of both axes shows up when two reports disagree and someone has to explain why.

Implementing it in Postgres

PostgreSQL’s range types express both axes cleanly: daterange for valid time and tstzrange for transaction time. An exclusion constraint on a GiST index prevents two beliefs from overlapping on both axes for the same entity, which is the bitemporal analogue of a primary key. The constraint needs the btree_gist extension so a plain equality on account_id can sit alongside range overlap operators in one index, as the PostgreSQL range-type documentation shows with its room-reservation example. The following was run against PostgreSQL 16 and returns 5,000 and 7,000 for the two queries respectively.

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 1. The event store: append-only, one global order, per-stream versions.
CREATE TABLE event_store (
    seq          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    stream_id    text        NOT NULL,
    version      integer     NOT NULL CHECK (version >= 1),
    event_type   text        NOT NULL,
    schema_ver   smallint    NOT NULL DEFAULT 1,
    payload      jsonb       NOT NULL,
    valid_date   date        NOT NULL,                  -- valid time
    recorded_at  timestamptz NOT NULL DEFAULT clock_timestamp(), -- transaction time
    UNIQUE (stream_id, version)                         -- optimistic concurrency
);

CREATE FUNCTION forbid_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'event_store is append-only';
END $$;

CREATE TRIGGER event_store_immutable
BEFORE UPDATE OR DELETE ON event_store
FOR EACH ROW EXECUTE FUNCTION forbid_mutation();

-- 2. Transactional outbox: written in the same transaction as the event.
CREATE TABLE outbox (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_seq    bigint NOT NULL REFERENCES event_store(seq),
    topic        text   NOT NULL,
    published_at timestamptz
);

-- 3. A bitemporal read model with both time axes.
CREATE TABLE account_limit_bt (
    account_id     text          NOT NULL,
    limit_amt      numeric(18,2) NOT NULL,
    valid_during   daterange     NOT NULL,
    known_during   tstzrange     NOT NULL DEFAULT tstzrange(clock_timestamp(), NULL),
    EXCLUDE USING gist (
        account_id   WITH =,
        valid_during WITH &&,
        known_during WITH &&
    )
);

INSERT INTO account_limit_bt (account_id, limit_amt, valid_during, known_during)
VALUES ('A1', 5000, '[2026-01-01,)', '[2026-01-02 09:00+00,)');

-- Restatement on 3 April: close the old belief, then write the corrected picture.
UPDATE account_limit_bt
   SET known_during = tstzrange(lower(known_during), '2026-04-03 09:00+00')
 WHERE account_id = 'A1' AND upper_inf(known_during);

INSERT INTO account_limit_bt VALUES
 ('A1', 5000, '[2026-01-01,2026-02-01)', '[2026-04-03 09:00+00,)'),
 ('A1', 7000, '[2026-02-01,)',           '[2026-04-03 09:00+00,)');

-- What did we believe on 15 March about the limit on 15 February?  -> 5000
SELECT limit_amt FROM account_limit_bt
 WHERE account_id = 'A1'
   AND valid_during @> DATE '2026-02-15'
   AND known_during @> TIMESTAMPTZ '2026-03-15 00:00+00';

-- What do we believe today about the same day?  -> 7000
SELECT limit_amt FROM account_limit_bt
 WHERE account_id = 'A1'
   AND valid_during @> DATE '2026-02-15'
   AND known_during @> TIMESTAMPTZ '2026-10-10 00:00+00';

Two details deserve emphasis. First, the read model’s UPDATE that closes a transaction-time range is a legitimate mutation of a projection, not of the event store. The projection is derived data; the source of truth, the event log, never changes. If you want the read model itself to be insert-only, store the closing as a separate row or rebuild from events. Second, the immutability trigger in the event store is a guard against accidents, not against a privileged operator. A database superuser can disable it. Real tamper resistance comes from permissions, replication to a store the application cannot write to, and periodic hash anchoring, covered below.

PostgreSQL 18’s temporal constraints reduce boilerplate on the valid-time axis: a primary key declared with WITHOUT OVERLAPS on a range column behaves like the exclusion constraint for that axis alone, and PERIOD foreign keys check that a referenced range covers the referencing one. For the full bitemporal case with two ranges you will probably still write the exclusion constraint by hand, since the new syntax is described in the release notes as applying to the last specified column. Check the current documentation for your version before relying on either form.

A Python replay sketch with corrections

The database view is only half of the picture. The other half is the replay function the aggregate and the projections share. The sketch below uses synthetic data, folds a single account’s stream and answers a bitemporal query. A correction event does not edit the earlier posting; it points at it through corrects, and the replay skips the superseded event once the correction is visible.

from dataclasses import dataclass
from datetime import date, datetime, timezone
from decimal import Decimal

@dataclass(frozen=True)
class Event:
    seq: int                 # global order, assigned by the store
    stream: str              # e.g. "acct-1001"
    version: int             # per-stream version, starts at 1
    type: str                # "Posted" or "Corrected"
    amount: Decimal          # signed: credit positive, debit negative
    valid_date: date         # business effective date (valid time)
    recorded_at: datetime    # when the store accepted it (transaction time)
    corrects: int | None = None  # seq of the event this one supersedes

def balance_as_of(events, stream, valid_as_of, known_as_of):
    """Balance the business believed on `known_as_of`
    about the world on `valid_as_of`."""
    visible = [e for e in events
               if e.stream == stream and e.recorded_at <= known_as_of]
    superseded = {e.corrects for e in visible if e.corrects is not None}
    total = Decimal("0")
    for e in visible:
        if e.seq in superseded:
            continue
        if e.valid_date <= valid_as_of:
            total += e.amount
    return total

def utc(y, m, d, h=0):
    return datetime(y, m, d, h, tzinfo=timezone.utc)

events = [
    Event(1, "acct-1001", 1, "Posted", Decimal("1000.00"), date(2026, 3, 1),  utc(2026, 3, 1, 9)),
    Event(2, "acct-1001", 2, "Posted", Decimal("-250.00"), date(2026, 3, 10), utc(2026, 3, 10, 9)),
    # Discovered on 3 April: the 10 March debit was really 205.00
    Event(3, "acct-1001", 3, "Corrected", Decimal("-205.00"), date(2026, 3, 10),
          utc(2026, 4, 3, 9), corrects=2),
]

q = date(2026, 3, 31)
print("as believed on 31 Mar:", balance_as_of(events, "acct-1001", q, utc(2026, 3, 31, 23)))
print("as known on 5 Apr    :", balance_as_of(events, "acct-1001", q, utc(2026, 4, 5)))
# as believed on 31 Mar: 750.00
# as known on 5 Apr    : 795.00

The 45.00 difference between the two outputs is exactly the correction, and it is reproducible forever: anyone can rerun the query for either frame of reference. Notice that the correction carries its own valid date (10 March, when the debit took effect) and its own recording time (3 April, when we learned better). That pair is what lets the March statement stay as issued while the restated balance flows into April’s figures.

Figure 3: A correction event travels through the outbox to the projection, which closes the old belief and opens the restated one. Two reports then see two different, both correct, answers.

Retroactive Corrections, Schema Evolution and Concurrency

Three engineering problems decide whether an event-sourced financial system survives its fifth year. They are how corrections are expressed, how event schemas change, and how concurrent writers are kept honest.

Correcting without mutating

There are three common correction idioms. The compensating event reverses the effect: a PaymentPosted of 250 is answered by a PaymentReversed of 250 and then a fresh PaymentPosted of 205. This is the accounting default and the closest to double-entry practice, because every step remains visible as its own entry. The superseding event, used in the sketch, links the new event to the one it replaces so that replay can ignore the original in the corrected frame of reference. And the retroactive event, a term Fowler’s pattern catalogue uses, inserts an event with an earlier valid date and recomputes everything after it.

Compensating events are the safest because they require no recomputation of history: later events were true when they were made, and the correction is a new fact. The cost is that downstream consumers must understand reversals. Superseding events make the corrected balance simple to read, at the price of a more complex replay. Retroactive events with full recomputation matter when a back-dated event changes the outcome of later events, such as interest accrued on a balance that was wrong for two months. In that case you cannot simply replace the one event; you need to re-run the dependent logic from the effective date forward and emit the resulting adjustments as new events. Fowler’s catalogue notes that such replacement works cleanly only when events can be reversed in both buggy and correct forms.

Whichever you pick, three rules keep corrections auditable. The correction must reference what it corrects. It must carry a reason and an actor. And it must never hide the original from queries that ask about the earlier transaction time.

Upcasting and schema evolution

Events outlive code. A payload written in 2026 must still be readable in 2034 by code that has changed shape several times. Because you cannot rewrite history, you evolve schemas by reading, not by migrating. The standard technique is upcasting: a chain of small pure functions that transform an old payload version into the current one at read time. An event stored as schema_ver = 1 with an amount in minor units and no currency might be upcast to version 2 by adding currency = "USD" from a documented default, and version 3 by splitting a single name into structured fields.

Rules that prevent regret: add fields rather than repurposing them; never change a field’s meaning under the same name; make new fields optional with an explicit default; keep upcasters as deterministic code under version control with tests that run old fixtures through them; and never delete an upcaster as long as any stored event might need it. Where a genuinely breaking change is unavoidable, use a new event type. Some teams also periodically rewrite a stream into a new store through a controlled “copy and transform” migration, but this is a serious operation that needs its own audit story, since you are producing a new log and must prove equivalence with the old one.

Floating-point amounts are a classic trap in payloads. Store money as integer minor units or fixed-point decimals with an explicit currency, and never as binary floating point. Replay must produce bit-identical results, and floating-point accumulation order can differ across code versions and platforms.

Concurrency and ordering

Optimistic versioning, described above, protects a single stream. Two further ordering issues appear when projections read the global sequence. A global identity column assigns numbers at insert time, but transactions commit in a different order from the one in which they obtained their numbers. A projection reading “everything above sequence 100” can see 101 before the still-open transaction that holds 100 commits, and then skip 100 forever. Solutions include a short settle delay with a gap-aware reader, tracking the oldest in-flight transaction identifier, serialising appends through a single writer, or relying on a log-based consumer that reads in commit order from the write-ahead log. This is a real source of “lost” events in homemade stores and deserves an explicit test.

Idempotency is the third guard. Commands arriving twice from a retrying client must not post twice. Store the client-supplied idempotency key with the command result, in the same transaction as the events, and return the stored result on repeat. For multi-step business processes, a saga or process manager reacts to events and issues further commands, with compensating events as the undo mechanism.

What auditors tend to ask for, in general terms

Regulators and auditors differ by jurisdiction and sector, so this is orientation, not advice. Across many regimes the recurring themes are retention for defined periods, the ability to reconstruct a record as it stood at a given time, evidence of who changed what and why, and controls showing records were not altered after the fact. Some regimes use phrases such as write-once or non-rewriteable storage for certain broker-dealer records, or audit trails that capture both the original entry and any modification. Whether a specific implementation satisfies a specific rule is a decision for your compliance and legal advisers. What the architecture offers is a natural fit to the technical shape of those requirements: an immutable log, explicit actors and reasons, and queries along both time axes.

Making immutability verifiable

A trigger that forbids updates stops mistakes; it does not convince a sceptic. Tamper evidence means a third party can detect alteration. A hash chain does this cheaply: each event stores the hash of its predecessor concatenated with its own canonical serialisation, so changing any record breaks every later hash. Periodically publish the latest chain head to a place the application operators cannot rewrite, such as an independent system, a write-once object store with retention locks, or a signed timestamp from a trusted service. Verification then replays the chain and compares against the anchors.

Hash chaining has a cost: it forces a total order on appends within a chain, which constrains write parallelism. Many designs chain per stream or per partition, then roll partition heads into a periodic Merkle root that is anchored. Be honest in documentation about what this proves: integrity since the anchor, not correctness of the data originally entered.

The tension is direct. An immutable log promises never to delete; the EU General Data Protection Regulation (Regulation 2016/679) grants individuals a right to erasure in Article 17, subject to exceptions that include compliance with a legal obligation, which is why retention rules for financial records and erasure requests must be reconciled case by case with legal input. The engineering question is how to keep the ledger intact while making personal data unreadable when erasure applies.

Figure 4: Keep personal data out of the event body where possible, encrypt what must be there with a per-subject key, and erase by destroying the key.

The first line of defence is data minimisation: keep personal data out of events. A payment event needs an account identifier, an amount and a counterparty reference, not a name and address. Store the personal data in a separate mutable system keyed by a subject identifier, and let events hold only the identifier. Erasure then deletes or anonymises the mutable record, and the log carries nothing identifying.

Where personal data must be inside an event, crypto-shredding applies. Generate a data encryption key per data subject, encrypt the personal fields before the event is appended, and store the key in a vault or key management service under a separate trust boundary. To erase, destroy the key. The events remain, the financial facts and totals still replay, but the encrypted fields are unrecoverable. Projections that copied the plaintext must be rebuilt or purged, and backups must be considered, because a backup of the key vault silently defeats the scheme.

Caveats matter. Whether destroying a key amounts to erasure under the law is a question regulators and courts have treated with nuance, and guidance has varied; take advice. Encrypted data that could be decrypted in future, for example under cryptographic weakness, may be argued not to be truly gone. Also, shredding removes the ability to explain old records, so decide up front which fields are shreddable and what the audit report shows in their place, such as a stable pseudonymous token. Finally, keys per subject create a key-management problem at scale: millions of keys, caching of keys for hot paths, and recovery procedures that do not recreate shredded keys.

Operating It: Rebuilds, Capacity and a Decision Matrix

Rebuilding projections safely

The operational payoff of the pattern is that read models are disposable, but a rebuild is an event with its own risks. A rebuild reads the entire log, which can mean billions of events, while the production system keeps appending. The safe approach builds the new projection side by side in a separate schema, replays to the current head while tracking its checkpoint, validates it against the live projection (row counts, control totals, sampled balances), and switches readers atomically through a view or a routing flag. Keep the old projection for a defined window so you can roll back.

Control totals deserve special mention in finance. Sum of all postings must be zero across a double-entry ledger, and the sum of projected balances must equal the sum of event amounts. Running these two assertions continuously, not only at rebuild, catches projection bugs and lost events early. An end-of-day reconciliation job that compares projections with a fresh replay of a sample of streams catches slower drift.

Capacity thinking

Event stores grow without bound, so plan for it. Illustrative arithmetic, not a measurement: a platform that posts 20 million events per day, averaging 400 bytes of payload plus 100 bytes of metadata and index overhead, adds on the order of 10 GB per day, or a few terabytes per year before replication. That is modest for modern storage but significant for index maintenance and backup windows. Partitioning the table by time, using native declarative partitioning on recorded_at, keeps indexes small, lets you detach old partitions to cheaper storage, and makes retention enforcement a partition operation rather than a delete, which also fits better with immutability.

Snapshots trade storage for read latency. Projections trade write amplification for query speed: every event triggers writes in every interested projection. Count them. A design with eight projections multiplies the effective write load, and a slow projection that falls behind can hold back the outbox or retention of the broker. Monitor projection lag per consumer as a first-class service-level indicator.

Decision matrix

Event sourcing is not the right default. The matrix compares four approaches for the same audit-sensitive domain. The ratings are qualitative judgements from the reasoning above, not benchmark results.

Criterion	Mutable table plus audit trigger	Temporal table, no event sourcing	Event sourcing, single time axis	Event sourcing plus bitemporal projections
Reconstruct state as known at time T	Partial, depends on trigger coverage	Good for system time	Good by replay	Good, any T
Business-effective-date queries	Poor, ad hoc columns	Good if application time used	Weak, mixed with record time	Good, explicit axis
Retroactive correction story	Overwrite or manual notes	Close and reopen rows	Compensating events	Compensating events and restated views
Operational complexity	Low	Medium	High	Highest
Eventual consistency to manage	None	None	Yes	Yes
Erasure handling	Simple delete	Delete plus history purge	Needs crypto-shredding	Needs crypto-shredding
Best fit	Low-risk internal data	Reference data, limits, pricing	Core ledgers with workflow	Regulated books needing restatements

Reading the table: most organisations should use bitemporal tables without full event sourcing for reference data like limits, fee schedules and customer terms, where history matters but replaying behaviour does not. Reserve event sourcing for the transaction core, where the sequence of facts is the product. A system can legitimately do both: event-sourced postings, with bitemporal reference data feeding the rating and validation logic.

Trade-offs, Gotchas, and What Goes Wrong

The pattern’s costs are real and most teams underestimate them. The first is cognitive load. Developers must think in events, projections and eventual consistency, and every new engineer needs onboarding in that style. Debugging means reading logs of facts rather than inspecting a row, and tooling for that rarely comes for free.

The second is that ad hoc queries become projects. “Show me all accounts whose balance exceeded X on any day last quarter” is trivial against a table and a project against a log, until someone builds the projection. Plan an analytical sink early, and decide who owns it.

The third is the temptation to treat the log as a message queue or a general database. An event store optimised for append and stream reads is bad at secondary-index lookups; a broker optimised for fan-out is not a durable system of record unless configured as one. Blurring these roles produces systems that do neither job well.

Failure modes worth naming:

Non-deterministic apply functions. Reading the clock, a random number or a mutable lookup inside apply makes replay diverge from history. Pass everything the function needs inside the event.
Projection poison events. One malformed event crashes a projection and blocks the stream behind it. Define a dead-letter path and an alert, and keep the choice between skip and halt explicit per projection.
Event design by CRUD. Events named AccountUpdated with a diff of fields tell you nothing about intent and make every consumer re-derive meaning. Model business facts.
Time confusion. Using the server clock for valid time, or accepting client clocks for transaction time. Valid time is business data supplied by the command; transaction time must come from the store, ideally from the database’s own clock inside the appending transaction.
Unbounded retroactivity. If anyone can back-date any event forever, closed accounting periods are meaningless. Add period locks that reject or route back-dated events to an adjustment in the open period, as accountants do.
Hash-chain theatre. A chain nobody verifies and whose anchors the same team controls adds ceremony, not assurance.
Replay at deploy time. Rebuilding projections on every release hides performance problems until the log is large.

None of these is fatal, and all are cheaper to design out than to retrofit. The pattern earns its keep when your domain truly consists of facts that are corrected later. It becomes pure overhead when the domain is simple state that nobody ever asks about historically.

Practical Recommendations

Start by deciding which questions you must answer in five years. If they are “what is the balance now” and “who changed it”, an audit table may be enough. If they include “what did we report on that date and how did it change”, you need record history, and if they include “what was true on that business date regardless of when we learned it”, you need both axes. Write those questions down as acceptance tests before choosing technology.

Keep the aggregate small, the events factual and the amounts exact. Put personal data outside events by default, and design crypto-shredding in from the first release if you must carry it inside. Enforce append-only in the database, and add permissions and external anchoring if tamper evidence is a requirement rather than a nice property. Make every projection idempotent and checkpointed, expose its lag, and test a full rebuild on a copy of production-sized data before you need it in anger.

Finally, adopt the pattern selectively. Event-sourced postings with bitemporal reference tables is a defensible architecture; event sourcing everything is usually not.

A short checklist:

Write the as-of questions you must answer, along both time axes, as tests.
Use unique (stream_id, version) for optimistic concurrency and an idempotency key per command.
Store valid time from the command and transaction time from the store clock.
Use the outbox for publication and idempotent, checkpointed projections.
Version event schemas and keep tested upcasters indefinitely.
Keep personal data out of events or encrypt it with per-subject keys.
Authorise against the aggregate, never a projection.
Run continuous control-total checks and rehearse a projection rebuild.
Take legal advice on retention and erasure before finalising the design.

Frequently Asked Questions

What is the difference between event sourcing and an audit log?

An audit log is a side record of changes to state that lives elsewhere; the state table remains the source of truth, so the two can drift. In event sourcing the event log is the source of truth and state is derived from it. That makes the history complete by construction, because nothing can change state without producing an event, and replayable, because the same events always rebuild the same state.

What is bitemporal data in simple terms?

Bitemporal data records two timelines for every fact. Valid time is when the fact was true in the real world. Transaction time is when the system stored it as its belief. Together they let you ask what you believed on one date about another date, and show how a correction changed the answer without erasing the earlier belief. Fowler calls the axes actual history and record history.

Do I need CQRS to do event sourcing?

In practice, yes or nearly so. Replaying a stream to answer every query is too slow and cannot serve cross-stream questions, so you maintain separate read models fed from the events. Strictly, the two patterns are independent, and you can use CQRS without event sourcing. The cost of the combination is eventual consistency between the write side and the read side, which you manage with version tokens and by authorising from the aggregate.

Prefer to keep personal data out of events and reference it by identifier. Where it must be present, encrypt the personal fields with a per-subject key held in a separate key store, and destroy the key when erasure applies. This is called crypto-shredding. The financial facts remain replayable. Whether it satisfies a specific legal obligation, and how it interacts with retention duties, needs legal advice.

Does PostgreSQL support bitemporal tables natively?

Not as a single feature. PostgreSQL offers range types, GiST exclusion constraints through btree_gist, and, from version 18, temporal primary and unique keys with WITHOUT OVERLAPS and PERIOD foreign keys. These cover valid-time integrity well. The transaction-time axis, meaning system-versioned history, is typically built with your own tables, triggers or an event log, or with extensions, so verify the support for your version.

When should I not use event sourcing?

Avoid it when the domain is simple state with little historical interest, when the team cannot absorb the operational learning curve, or when you need strongly consistent reads everywhere. It is also a poor fit for data that is mostly overwritten, such as session or cache state. In those cases a bitemporal table, or a plain table with a well-designed audit trail, delivers most of the benefit at a fraction of the complexity.

Event Sourcing and Bitemporal Data for Financial Audit Trails

Event Sourcing and Bitemporal Data for Financial Audit Trails

Context and Background