Event Sourcing & Append-Only Tables
You'll learn to
- -Model a value that changes over time (an exchange rate, a risk score) as an append-only history instead of a mutable row
- -Reconstruct current state and point-in-time snapshots from an event-sourced schema
Event sourcing stores every change to a piece of data as an immutable, timestamped event, rather than overwriting a single mutable row - current state is derived by replaying events, not stored directly as the source of truth. This chapter extends the append-only pattern from the previous chapter's audit trail into a full alternative way of modeling data that genuinely needs its complete history, not just its current value.
Multi-Currency Trading: Rates That Change Constantly
An exchange rate is not one fact - it is a continuously changing series of facts, and a trade executed at 2:47pm needs to be evaluated against the rate that was actually in effect at 2:47pm, not whatever the rate happens to be when someone later looks it up. A mutable exchange_rates table with one row per currency pair, overwritten on every update, destroys exactly the information needed to correctly value a historical trade.
CREATE TABLE exchange_rate_events (
id INTEGER PRIMARY KEY,
currency_pair TEXT NOT NULL, -- 'USD/EUR'
rate DECIMAL(12, 6) NOT NULL,
effective_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-- never UPDATEd - a rate change is always a new row, never a modification
);
-- "what was the USD/EUR rate at the moment this trade executed?"
SELECT rate FROM exchange_rate_events
WHERE currency_pair = 'USD/EUR' AND effective_at <= :trade_timestamp
ORDER BY effective_at DESC LIMIT 1;Risk Snapshots: Point-in-Time State, Not Just the Latest Value
A margin/risk system needs to answer "what was this account's risk exposure at 9:00am, before the market moved" for compliance and dispute-resolution purposes, not just "what is it right now." The same append-only pattern applies: each recalculation writes a new risk_snapshots row rather than updating an existing one, and the current value is simply the most recent snapshot - a special case of the general query above, not a separately-maintained value.
CREATE TABLE risk_snapshots (
id INTEGER PRIMARY KEY,
account_id INTEGER NOT NULL REFERENCES accounts(id),
exposure_amount DECIMAL(15, 2) NOT NULL,
margin_ratio DECIMAL(6, 4) NOT NULL,
calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- "current" risk is just the most recent snapshot for that account:
SELECT * FROM risk_snapshots WHERE account_id = :account_id
ORDER BY calculated_at DESC LIMIT 1;The IPO Pipeline as a Workflow of Events
A multi-stage workflow like an IPO pipeline (filing, roadshow, pricing, allotment) combines this chapter's technique with the previous chapter's state machine: each stage transition is an appended event (matching this chapter's pattern), while the set of valid stage transitions is still governed by a transitions table (matching the previous chapter's pattern) - a direct example of how these techniques compose rather than compete.
Append-only tables grow without bound by design - a real system needs an explicit plan for this (periodic archiving of old events to cold storage, or materialized "current state" tables refreshed from the event stream) rather than assuming an unbounded table stays fast forever.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Multi-Currency Trading, the Risk & Margin System, and the IPO Pipeline in the LLD Lab's Wolf of Wall Street act.
An append-only event table is exactly the shape a running-total window function is built for - SQL Lab's "The Million Mark" (SUM OVER) computes a cumulative total across rows without collapsing them the way a plain GROUP BY would, which is what querying this chapter's event log for "balance as of any point in time" actually needs.