CS Fundamentals Trainer · Guides

Event sourcing and CQRS

Storing facts instead of state: the event store, optimistic concurrency, replay and snapshots, read models and projectors, eventual consistency, event design and upcasting, and when it is not worth it.

38 min read

Most applications store the current state: one row per account holding the balance, overwritten on every change. That is simple, and it throws information away. You can see that the balance is 20, but not that it was 100 a minute ago, who changed it, or why. Event sourcing keeps the history instead: the system stores every change as an immutable event ("deposited 100", "withdrew 80"), and the current state is derived by replaying them. CQRS (command query responsibility segregation) goes with it naturally: separate the model that decides and changes things from the models that answer queries.

Both are powerful, widely misapplied, and often described through vague slogans. In this guide I built a small event store on SQLite and measured what replay, snapshots, concurrency control and read-model projections really do (code in the appendix). It connects to message queues and sagas for integration, to microservices for the architecture around it, and to the log-structured ideas of storage engines and consensus, where a replicated log is also the heart of the system.

1. State or events?

Take a bank account. With state storage the table has one row:

accountbalance
A20

With event sourcing the table holds what happened, in order, and nothing is ever updated or deleted:

streamversionevent
account A1Opened
account A2Deposited 100
account A3Withdrew 80

The balance, 20, is a fold over the events: start with nothing, apply each event in order. The log is the source of truth; the balance is a view of it that can always be recomputed.

Why would you do this?

And the price, which this guide also spells out: complexity, eventual consistency in the read side, schema evolution that never ends, and the fact that most applications do not need any of the benefits above.

2. The event store

An event store is an append-only log with a few strict rules:

Diagram: The write side: commands become events

A command is a request to do something and may be refused ("withdraw 200"); an event is a fact that already happened and cannot be refused ("withdrew 80"). The handler loads the stream, rebuilds the state, checks the business rules, and if they hold, appends the new events. Name events in the past tense, in business language (OrderPlaced, AddressChanged), not as table changes (OrderRowUpdated): the meaning is the value.

Optimistic concurrency

What if two commands for the same account run at the same time? Each loads the stream, sees a balance of 100, and decides that withdrawing 80 is allowed. Without protection both would append, and the balance would be -60. The store prevents this with an expected version: the append says "add this event if the stream is still at version 2". I ran this with two threads:

ClientResult
writer 1accepted (stream moves to version 3)
writer 2conflict: the stream is at version 3, expected 2

The final balance was 20, not -60. The loser reloads the stream, sees the new balance of 20, and now correctly rejects the withdrawal (or retries if the command still makes sense). This is optimistic concurrency control: no locks, a conflict is detected at write time, and the unique (stream, version) constraint makes it hold even under races. The consistency boundary is the stream: invariants must be enforced within one aggregate, which is why aggregates should be small (an account, an order) and not "the whole customer database". Rules that span aggregates need sagas and eventual consistency (the distributed transactions guide).

3. Rebuilding state, and snapshots

To rebuild an account you read its events and apply them in order. Step through the process, then switch on the snapshot:

Interactive animation: event replay

Replaying is cheap for a stream with ten events and expensive for one with millions. I measured it for a Python fold over events in SQLite:

Events in the streamFull replayReplay from a snapshot taken 100 events before the end
1000.4 ms0.3 ms
10,000about 22 ms0.25 ms (100 events read)
1,000,000about 2.4 to 2.7 s0.4 ms (100 events read)

A snapshot is a saved copy of the state at some version. To load the aggregate, read the latest snapshot and replay only the events after it. The cost of loading becomes constant, which is why snapshots are the standard answer when streams grow long. Two cautions: the events remain the source of truth (a snapshot is a cache and can be thrown away and rebuilt), and a snapshot is written in a shape of the state, so when you change the state class you must invalidate or migrate old snapshots. Often you do not need snapshots at all: streams per entity are usually short, and a stream that grows without bound is a sign that the aggregate is too big (close the books and start a new stream, for example one per month for an account).

4. CQRS: separate models for writing and reading

Event sourcing makes the write side excellent at deciding and poor at answering questions: "all accounts with a balance over 1,000" would mean replaying every stream. So you build read models (projections): tables or indexes shaped for the queries you need, kept up to date by a projector that subscribes to the events. That separation is CQRS.

Diagram: CQRS with event sourcing

Each read model can use the technology that suits it: a relational table, a search index (see search and full-text indexing), a cache, a graph. You can add a new one at any time and build it from the history.

A projector, built carefully

A projector reads events from a checkpoint (the last global position it handled), applies them to the read model, and stores the new checkpoint. The detail that matters is that the read-model change and the checkpoint move in the same transaction, so that a crash cannot apply an event twice or skip one. I wrote one that maintains a table of orders per customer:

That rebuild is a major benefit: a bug in a projection is fixed by correcting the code and replaying, not by repairing data by hand. For a large history, rebuilds take time, so teams build the new version of a read model next to the old one, switch queries over when it has caught up, and drop the old one.

Eventual consistency

The read model is updated after the event is committed, so for a moment a query can return old data. How stale depends on how the projector gets its events. A simple model, in which the projector polls every interval and needs 5 ms to process a batch, gives:

Poll intervalMedian staleness99th percentile
10 ms10 ms15 ms
100 ms55 ms104 ms
1,000 ms502 ms995 ms

Subscribing to a push notification instead of polling shrinks it to the processing time plus the network, usually milliseconds, but never zero. The consequences have to be designed in:

CQRS without event sourcing is common and often enough: a normalised write database plus denormalised read tables, materialised views or a search index fed by change data capture. Event sourcing without CQRS is rarely practical, because of the query problem above.

4.1 Running projectors in production: crashes, poison events and rebuilds

A projector is a small program that must run for years, and the interesting problems are not in the happy path. I tested four of them on an event store in SQLite (script in the appendix; small data sets, so read the ratios and the failure modes, not the milliseconds).

Recovering after a crash. A projector reads a batch, updates the read model and records how far it got (the checkpoint). If it dies between the two, a restart re-reads the batch. With increments such as balance = balance + amount, applying the batch twice double-counts. I injected a crash with 4% probability per batch into 300 runs over 2,000 events; 163 runs had at least one crash:

DesignRuns with a crashRuns that ended with a wrong read model
read model committed, then the checkpoint written separately163163
read model and checkpoint in one transaction1630

If the read model lives in the same database as the checkpoint, use one transaction. If it does not (a search index, another store), make every update idempotent (set a value computed from the event, or store the event position on the row and skip anything not newer) so that re-applying a batch changes nothing.

A poison event is one that the projector cannot process: malformed data, an unknown type, a bug. I put 5,000 events through a projector with one event at position 2,500 that had text ("142 EUR") where a number was expected:

StrategyWhat happened
halt on the errorstopped at event 2,500; the other 2,501 events (50%) never reached the read model, and all 50 accounts were stale or wrong
skip it silently4,999 applied; one account permanently wrong, and nothing says so
retry three times, then park the event and its stream in a dead-letter table and carry on4,932 applied; 68 events parked (the poison event and every later event of that one account, to keep that account's events in order); the other 49 accounts stay current; an alert fires

After fixing the cause (here an upcaster that reads "142 EUR" as 142) and re-driving the 68 parked events in order, 0 of 50 balances were wrong. The pattern is: retry for transient errors, quarantine at the smallest scope that preserves order (the stream, not the whole log), alert on the dead-letter count, keep the failing payload for diagnosis, and re-drive after the fix. Halting is the right choice only when order across streams matters or you would rather be down than wrong. Silent skipping is almost never right. Prevent poison events at the source by validating events when they are written, and by testing every stored version of every event type (next section).

Rebuilding a read model without disrupting production. You rebuild when you change a projection, fix a bug or add a new view. The naive way is to empty the table and replay. I did that with 150,000 events while a reader queried the total every few milliseconds:

RebuildTimeReads that returned a wrong total
in place: delete the rows, then replay1.0 s407 of 407 (100%)
side by side: replay into a new table balances_v2, then swap the names in one transaction1.0 s0 of 449

The naive rebuild shows users partial data for as long as it runs; with a real log (hours of replay) that is an outage. The safe procedure is the blue/green one: (1) build the new read model next to the old one, from position 0, while the old one keeps serving; (2) let the live projector feed both, or catch up from the last position until the lag is a few events; (3) compare the two where they should agree (row counts, checksums, a sample of queries); (4) switch readers in one atomic step (rename, a view or an alias, a feature flag), under a short write lock so that no event falls between catching up and swapping; (5) keep the old one for a while for rollback, then drop it. The script measures steps 1 and 4; steps 2, 3 and 5 are the operating procedure and are not measured. Also rate-limit the replay so it does not starve the live system, and remember it needs capacity for reading the whole log.

5. Designing events

Schema evolution: upcasting

You cannot rewrite history, yet the shape of events changes. The common technique is upcasting: when an old event is read, a small function converts it to the current shape. In my test, version 1 of Opened stored the owner as one string, version 2 as first and last names:

StoredRead as (current shape)
schema 1: {"owner": "Ada Lovelace"}{"owner": {"first": "Ada", "last": "Lovelace"}}
schema 2: {"owner": {"first": "Grace", "last": "Hopper"}}unchanged

Rules: store a schema version with each event; only make additive changes where possible; write upcasters for the rest; keep every old version testable. The alternative, a one-off migration that rewrites the stored events, is possible but defeats the idea of an immutable log and is risky.

Evolving events and replaying after code changes

Upcasting handles the simple case. The wider rules, because events are kept for the life of the system and every replay runs today's code over yesterday's data:

Ways to change an event, from safest to most disruptive

ChangeHowNotes
add an optional fieldreaders use a default when it is missing ("tolerant reader")no migration; the preferred change
change a field's shape or meaninga new schema version plus an upcaster from the old one; chain them (v1 to v2 to v3) so any stored version reaches the current shapeevery old version must stay readable; keep one sample event of each version in the tests
the business meaning changeda new event type (AddressChanged vs AddressCorrected), never a changed meaning under the old nameold events keep their old meaning forever
split or merge streams, or restructure heavilycopy-transform: read the old log and write a new one in the new shape, run both until verified, then cut overa project, not an edit; the old log is kept as the archive
rewrite stored events in placeavoid; if unavoidable, back up, do it as a versioned migration, and verify by rebuilding the state before and afterbreaks the "immutable history" promise and any hash chain

Replaying after a code change. The state of an aggregate is fold(apply, events). That only stays true if apply is a pure function of the event: no clock, no random numbers, no database or service lookups, and no business decisions in it. Decisions belong in the command handler, and their outcome is recorded in the event. I showed the difference with a withdrawal fee. When the fee was computed inside apply at 1%, the balance was 444 when the events happened; the business changed the rule to a flat 5 and a replay produced 435: history changed silently. When the fee was stored in each event, the balance stayed 444 after the rule changed, and the new rule applied only to new commands.

Related rules:

Personal data and deletion

Immutability collides with the right to erasure (GDPR). Common strategies: keep personal data out of the events, storing only an identifier and keeping the data in a separate, deletable store; or use crypto-shredding: encrypt each person's data in the events with a per-person key, and delete the key when the person asks to be forgotten, leaving unreadable bytes. Plan this at the start; it is very hard to add later.

Is an event log an audit trail?

It is tempting to say "we store every change, so we have an audit trail". An event log gives you a good start, not something that meets a legal or regulatory requirement by itself. The gaps:

The honest summary: event sourcing makes a complete record of state changes cheap, which helps an audit. Making that record trustworthy as evidence needs tamper-evidence, anchoring, trusted identity and time, access logging, and an agreement with whoever audits you.

6. Integrating with the rest of the system

Other services want to react to your events. Two rules keep this reliable:

A process manager or saga listens to events and issues commands to coordinate a multi-step business process across aggregates and services, with compensating commands when something fails.

7. When to use it, and when not

Good fitPoor fit
a domain where history, audit and "why" matter: ledgers, orders, inventory, claims, collaborative editingsimple CRUD, content management, settings screens
complex business rules and workflows that benefit from "given events, when command, then events" testsreporting and ad-hoc querying over current state as the main need
several different views of the same data, built and rebuilt over timea small team with no experience, little time and no need for the benefits
a need to subscribe to what happened rather than polldata with strong, immediate read-after-write needs everywhere

Cheaper alternatives for part of the benefit: an audit table written in the same transaction as the change, temporal tables (system-versioned tables) in some databases, or change data capture to stream row changes to other systems. Use event sourcing for an aggregate or a bounded context that really needs it, not for a whole system by default. If you do, use a proven store (EventStoreDB, or an append-only table in a relational database with the constraints shown above, or a log system such as Kafka for the distribution side) and a framework that handles snapshots, projections and upcasting, rather than inventing them.

8. Common mistakes

9. Things to mention in an interview

Quick recap

Appendix: the experiment code

One Python 3 script using only the standard library (SQLite in memory). It builds the event store with the unique constraint on (stream, version), appends with an expected version, runs two threads that withdraw from the same account, replays streams of 100, 10,000 and 1,000,000 events with and without a snapshot, runs a projector with a checkpoint and rebuilds it from the log, and shows upcasting. Replay times are real measurements on a small virtual machine and vary from run to run (the figures in the guide are rounded); the staleness table is a calculation from the polling interval and a 5 ms batch time, not a measurement of a real system. SQLite runs in a single process here, so the lock around append stands in for the database transaction a real store would use; the (stream, version) constraint is what guarantees correctness in a real database.

An event store on SQLite: replay, snapshots, concurrency, a projector and upcasting

import sqlite3, json, time, random, threading

# ---------- an append-only event store on SQLite, with optimistic concurrency per stream
db = sqlite3.connect(":memory:", check_same_thread=False, isolation_level=None)
db.execute("pragma journal_mode=off"); db.execute("pragma synchronous=off")
db.execute("""create table events(global_pos integer primary key autoincrement, stream text not null, version int not null,
              type text not null, data text not null, schema int not null default 1, unique(stream, version))""")
db.execute("create table snapshots(stream text primary key, version int, state text)")
lock = threading.Lock()

class Conflict(Exception): pass
def append(stream, expected_version, events):
    """Appends events only if the stream is still at expected_version (optimistic concurrency)."""
    with lock:
        try:
            db.execute("begin")
            v = db.execute("select coalesce(max(version), 0) from events where stream=?", (stream,)).fetchone()[0]
            if v != expected_version: raise Conflict(f"stream {stream} is at version {v}, expected {expected_version}")
            for i, (t, d) in enumerate(events): db.execute("insert into events(stream, version, type, data) values (?,?,?,?)", (stream, v + i + 1, t, json.dumps(d)))
            db.execute("commit"); return v + len(events)
        except Exception: db.execute("rollback"); raise

def apply(state, t, d):
    if t == "Opened": return {"balance": 0, "open": True}
    if t == "Deposited": return {**state, "balance": state["balance"] + d["amount"]}
    if t == "Withdrew": return {**state, "balance": state["balance"] - d["amount"]}
    return state
def load(stream, snapshot=True):
    state, v = {}, 0
    if snapshot:
        row = db.execute("select version, state from snapshots where stream=?", (stream,)).fetchone()
        if row: v, state = row[0], json.loads(row[1])
    n = 0
    for t, d, ver in db.execute("select type, data, version from events where stream=? and version>? order by version", (stream, v)):
        state = apply(state, t, json.loads(d)); v = ver; n += 1
    return state, v, n

# ---------- 1. cost of rebuilding state, with and without snapshots
rnd = random.Random(1)
def fill(stream, n):
    rows = [(stream, 1, "Opened", "{}")] + [(stream, i + 2, rnd.choice(["Deposited", "Withdrew"]), json.dumps({"amount": rnd.randint(1, 100)})) for i in range(n - 1)]
    db.execute("begin"); db.executemany("insert into events(stream, version, type, data) values (?,?,?,?)", rows); db.execute("commit")
print("rebuilding one account by replaying its events (Python fold, SQLite in memory):")
for n in (100, 10_000, 1_000_000):
    s = f"acct-{n}"; fill(s, n)
    t = time.perf_counter(); st, v, replayed = load(s, snapshot=False); full = time.perf_counter() - t
    # build a real snapshot 100 events before the end
    st2 = {}
    for t_, d_, ver in db.execute("select type, data, version from events where stream=? and version<=? order by version", (s, v - 100)): st2 = apply(st2, t_, json.loads(d_))
    db.execute("insert or replace into snapshots values (?,?,?)", (s, v - 100, json.dumps(st2)))
    t = time.perf_counter(); st3, v3, replayed3 = load(s, snapshot=True); snap = time.perf_counter() - t
    assert st3 == st
    print(f"  {n:>9,} events: full replay {full*1000:>9.1f} ms ({replayed:,} events read), from a snapshot {snap*1000:>6.2f} ms ({replayed3} events read)")

# ---------- 2. optimistic concurrency: two writers, one decision each
append("acct-A", 0, [("Opened", {}), ("Deposited", {"amount": 100})])
out = []
def withdraw(name, amount):
    state, v, _ = load("acct-A", snapshot=False)
    time.sleep(0.01)                                  # both read the state before either one writes
    if state["balance"] < amount: out.append((name, "rejected: insufficient funds")); return
    try: append("acct-A", v, [("Withdrew", {"amount": amount})]); out.append((name, "accepted"))
    except Conflict as e: out.append((name, f"conflict: {e}"))
ths = [threading.Thread(target=withdraw, args=(n, 80)) for n in ("writer 1", "writer 2")]
[t.start() for t in ths]; [t.join() for t in ths]
print("\ntwo clients withdraw 80 from a balance of 100 at the same moment:"); [print("  ", o) for o in sorted(out)]
print("   final balance:", load("acct-A", False)[0]["balance"])

# ---------- 3. a projection (read model) with a checkpoint, run in batches: how stale is it?
db.execute("create table orders_by_customer(customer text primary key, orders int, total int)")
db.execute("create table checkpoint(name text primary key, pos int)"); db.execute("insert into checkpoint values ('orders', 0)")
def project(batch=100):
    pos = db.execute("select pos from checkpoint where name='orders'").fetchone()[0]
    rows = db.execute("select global_pos, type, data from events where global_pos>? and type='OrderPlaced' order by global_pos limit ?", (pos, batch)).fetchall()
    last = pos
    db.execute("begin")
    for gp, t, d in rows:
        d = json.loads(d)
        db.execute("insert into orders_by_customer values (?,1,?) on conflict(customer) do update set orders=orders+1, total=total+excluded.total", (d["customer"], d["total"])); last = gp
    db.execute("update checkpoint set pos=? where name='orders'", (last,)); db.execute("commit")   # the read model and the checkpoint move together
    return len(rows)
for i in range(1000): append(f"order-{i}", 0, [("OrderPlaced", {"customer": f"c{i % 50}", "total": 10 + i % 7})])
while project(): pass
print("\nread model built from", db.execute("select count(*) from events where type='OrderPlaced'").fetchone()[0], "OrderPlaced events:", db.execute("select count(*), sum(orders), sum(total) from orders_by_customer").fetchone(), "(customers, orders, total)")
# the projection can be dropped and rebuilt from the log at any time, with the same result
before = db.execute("select * from orders_by_customer order by customer").fetchall()
db.execute("delete from orders_by_customer"); db.execute("update checkpoint set pos=0")
t = time.perf_counter(); n = 0
while True:
    k = project(500); n += k
    if not k: break
print(f"rebuilt from scratch in {1000*(time.perf_counter()-t):.0f} ms; identical to the original: {db.execute('select * from orders_by_customer order by customer').fetchall() == before}")
# replaying a batch twice must not change the result: the checkpoint and the data change in one transaction
pos = db.execute("select pos from checkpoint where name='orders'").fetchone()[0]
print("running the projector again with nothing new applies", project(), "events")

# staleness of the read model if the projector polls every `interval` ms and a write is committed at a random moment
rnd2 = random.Random(5)
print("\nread model staleness (time from commit to visible), projector polling every interval, 5 ms to process a batch:")
for interval in (10, 100, 1000):
    lags = sorted(rnd2.uniform(0, interval) + 5 for _ in range(100000))
    print(f"  poll every {interval:>4} ms: median {lags[50000]:.0f} ms, 99th percentile {lags[99000]:.0f} ms, worst {lags[-1]:.0f} ms")

# ---------- 4. schema evolution: events are forever, so old shapes must still be readable
db.execute("insert into events(stream, version, type, data, schema) values ('acct-B', 1, 'Opened', ?, 1)", (json.dumps({"owner": "Ada Lovelace"}),))
db.execute("insert into events(stream, version, type, data, schema) values ('acct-B', 2, 'Opened', ?, 2)", (json.dumps({"owner": {"first": "Grace", "last": "Hopper"}}),))
def upcast(t, d, schema):
    if t == "Opened" and schema == 1: first, _, last = d["owner"].partition(" "); return {"owner": {"first": first, "last": last}}
    return d
print("\nupcasting version 1 events to the current shape when they are read:")
for t, d, s in db.execute("select type, data, schema from events where stream='acct-B' order by version"): print("  schema", s, "->", upcast(t, json.loads(d), s))

# ---------- 5. the temporal query that a state table cannot answer
state = {}; 
print("\nbalance of acct-A after each event (a state table keeps only the last row):")
for t, d, v in db.execute("select type, data, version from events where stream='acct-A' order by version"):
    state = apply(state, t, json.loads(d)); print(f"  version {v}: {t:<10} balance {state['balance']}")

Operating an event-sourced system: replay, poison events, crashes, rebuilds and hash chains

Python 3, standard library only, SQLite in a temporary file. Timings depend on the machine.

"""Operating an event-sourced system: replay after code changes, poison events, projector crashes, rebuilding a read model while it is being read,
and what a hash chain can and cannot prove. SQLite and the standard library only; numbers are from one small machine."""
import hashlib, json, os, random, sqlite3, threading, time

DB = "/var/tmp/es_ops.db"
def fresh():
    for ext in ("", "-wal", "-shm"):
        try: os.remove(DB + ext)
        except FileNotFoundError: pass
    c = sqlite3.connect(DB, isolation_level=None); c.execute("pragma journal_mode=wal"); c.execute("pragma synchronous=off")
    c.execute("create table events(pos integer primary key autoincrement, stream text, type text, data text)")
    return c

def add(c, stream, type, **data): c.execute("insert into events(stream,type,data) values (?,?,?)", (stream, type, json.dumps(data)))

def workload(c, n_streams=200, n=20_000, seed=1):
    r = random.Random(seed); bal = {f"acct-{i}": 0 for i in range(n_streams)}
    c.execute("begin")
    for _ in range(n):
        s = r.choice(list(bal))
        if bal[s] < 50 or r.random() < .6: a = r.randrange(10, 200); add(c, s, "Deposited", amount=a); bal[s] += a
        else: a = r.randrange(1, min(bal[s], 150) + 1); add(c, s, "Withdrawn", amount=a); bal[s] -= a
    c.execute("commit"); return bal

# ---------------------------------------------------------------------------------------------------------------- A. replay after a code change
def part_a():
    print("A. Replay after the business rules change")
    events = [("Deposited", 1000), ("Withdrawn", 200), ("Withdrawn", 300), ("Deposited", 50), ("Withdrawn", 100)]
    fee_v1 = lambda amount: round(amount * 0.01)             # the rule when the events were written: 1 percent
    fee_v2 = lambda amount: 5                                # the rule after a change in the business: a flat 5
    def fold_fee_in_apply(fee):                              # BAD: the fee is computed inside `apply`, from today's code
        b = 0
        for t, a in events: b += a if t == "Deposited" else -(a + fee(a))
        return b
    live = fold_fee_in_apply(fee_v1); replayed = fold_fee_in_apply(fee_v2)
    print(f"   fee computed in apply:  balance when the events happened {live}, balance after replaying with the new rule {replayed}  -> history silently changed")
    stored = []                                              # GOOD: the decision (including the fee) is recorded in the event
    for t, a in events: stored.append((t, a, 0 if t == "Deposited" else fee_v1(a)))
    def fold_recorded(stored): return sum(a if t == "Deposited" else -(a + f) for t, a, f in stored)
    print(f"   fee recorded in the event: balance {fold_recorded(stored)} now, and after the new rule is deployed {fold_recorded(stored)} (the new rule only applies to new commands)")

# ---------------------------------------------------------------------------------------------------------------- B. poison events
def project(c, events, strategy, max_retries=3):
    """A projector that keeps `balances`. `events` come in order. Returns (balances, dead_letters, applied_count, halted_at)."""
    bal = {}; dead = []; quarantined = set(); applied = 0
    def apply(stream, t, d):
        a = d["amount"]
        if not isinstance(a, int): raise TypeError(f"bad amount {a!r}")
        bal[stream] = bal.get(stream, 0) + (a if t == "Deposited" else -a)
    for pos, stream, t, data in events:
        d = json.loads(data)
        if strategy == "dead-letter-per-stream" and stream in quarantined: dead.append((pos, stream, t, data)); continue   # keep the order of that stream
        for attempt in range(max_retries if strategy != "skip" else 1):
            try: apply(stream, t, d); applied += 1; break
            except Exception:
                if strategy == "halt": return bal, dead, applied, pos
        else:
            if strategy == "dead-letter-per-stream": dead.append((pos, stream, t, data)); quarantined.add(stream)
    return bal, dead, applied, None

def part_b():
    print("B. A poison event in the middle of the log")
    c = fresh(); truth = workload(c, n_streams=50, n=5_000); poison_pos = 2500
    row = c.execute("select stream, type, data from events where pos=?", (poison_pos,)).fetchone()
    true_amount = json.loads(row[2])["amount"]
    c.execute("update events set data=? where pos=?", (json.dumps({"amount": f"{true_amount} EUR"}), poison_pos))      # a bad producer wrote text instead of a number
    events = c.execute("select pos, stream, type, data from events order by pos").fetchall(); total = len(events)
    for strategy in ("halt", "skip", "dead-letter-per-stream"):
        bal, dead, applied, halted = project(c, events, strategy)
        wrong = sum(1 for s in truth if bal.get(s, 0) != truth[s])
        extra = f", halted at position {halted}: {total - applied} events ({100*(total-applied)/total:.0f}%) not visible" if halted else ""
        print(f"   {strategy:<22} applied {applied}/{total}, accounts with a wrong balance {wrong}/{len(truth)}, parked {len(dead)}{extra}")
    # the fix: an upcaster that understands the bad format, then re-drive the parked events in order
    bal, dead, applied, _ = project(c, events, "dead-letter-per-stream")
    fixed = []
    for pos, stream, t, data in dead:
        d = json.loads(data)
        if isinstance(d["amount"], str): d["amount"] = int(d["amount"].split()[0])
        fixed.append((pos, stream, t, json.dumps(d)))
    for pos, stream, t, data in fixed:
        d = json.loads(data); bal[stream] = bal.get(stream, 0) + (d["amount"] if t == "Deposited" else -d["amount"])
    print(f"   after the fix and re-driving {len(fixed)} parked events: accounts with a wrong balance {sum(1 for s in truth if bal.get(s, 0) != truth[s])}/{len(truth)}")

# ---------------------------------------------------------------------------------------------------------------- C. crash between the read-model update and the checkpoint
def part_c(trials=300):
    print("C. The projector crashes at a random moment")
    c = fresh(); truth = workload(c, n_streams=20, n=2_000, seed=3)
    events = c.execute("select pos, stream, type, data from events order by pos").fetchall()
    def run(design, rnd):
        m = sqlite3.connect(":memory:", isolation_level=None)
        m.execute("create table balances(stream text primary key, balance int)"); m.execute("create table checkpoint(pos int)"); m.execute("insert into checkpoint values (0)")
        crashes = 0
        while True:
            pos0 = m.execute("select pos from checkpoint").fetchone()[0]
            batch = [e for e in events if e[0] > pos0][:100]
            if not batch: break
            crash_at = rnd.randrange(len(batch) + 2) if rnd.random() < .04 else None
            try:
                if design == "separate":                              # update the read model, commit, THEN write the checkpoint
                    for i, (pos, s, t, d) in enumerate(batch):
                        a = json.loads(d)["amount"]; m.execute("insert into balances values (?,0) on conflict(stream) do nothing", (s,))
                        m.execute("update balances set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
                    if crash_at is not None: raise RuntimeError("crash after the read model, before the checkpoint")
                    m.execute("update checkpoint set pos=?", (batch[-1][0],))
                else:                                                 # one transaction: read model and checkpoint together
                    m.execute("begin")
                    for i, (pos, s, t, d) in enumerate(batch):
                        a = json.loads(d)["amount"]; m.execute("insert into balances values (?,0) on conflict(stream) do nothing", (s,))
                        m.execute("update balances set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
                    if crash_at is not None: raise RuntimeError("crash before commit")
                    m.execute("update checkpoint set pos=?", (batch[-1][0],)); m.execute("commit")
            except RuntimeError:
                crashes += 1
                if design == "atomic": m.execute("rollback")
        return dict(m.execute("select stream, balance from balances")), crashes
    for design in ("separate", "atomic"):
        wrong = 0; crashed = 0
        for k in range(trials):
            got, cr = run(design, random.Random(k)); crashed += cr > 0; wrong += got != truth
        print(f"   {design:<8}: {crashed} of {trials} runs had a crash, {wrong} runs ended with a wrong read model")

# ---------------------------------------------------------------------------------------------------------------- D. rebuilding a read model that is being read
def rebuild_batches(c, table, start=0, batch=5000):
    pos = start
    while True:
        rows = c.execute("select pos, stream, type, data from events where pos>? order by pos limit ?", (pos, batch)).fetchall()
        if not rows: return pos
        c.execute("begin")
        for p, s, t, d in rows:
            a = json.loads(d)["amount"]; c.execute(f"insert into {table} values (?,0) on conflict(stream) do nothing", (s,))
            c.execute(f"update {table} set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
        c.execute("commit"); pos = rows[-1][0]

def part_d():
    print("D. Rebuilding a read model while the application keeps reading it")
    for mode in ("in place", "side by side, then swap"):
        c = fresh(); workload(c, n_streams=200, n=150_000, seed=5)
        c.execute("create table balances(stream text primary key, balance int)"); last = rebuild_batches(c, "balances")
        expected = c.execute("select sum(balance) from balances").fetchone()[0]
        stop = threading.Event(); stats = {"reads": 0, "wrong": 0, "worst": 0.0}
        def reader():
            r = sqlite3.connect(DB, isolation_level=None, timeout=30)
            while not stop.is_set():
                t = time.perf_counter()
                try: total = r.execute("select coalesce(sum(balance),0) from balances").fetchone()[0]
                except sqlite3.OperationalError: continue
                stats["worst"] = max(stats["worst"], time.perf_counter() - t); stats["reads"] += 1; stats["wrong"] += total != expected
                time.sleep(0.002)
        th = threading.Thread(target=reader); th.start(); t0 = time.perf_counter()
        if mode == "in place":
            c.execute("delete from balances"); rebuild_batches(c, "balances")
        else:
            c.execute("create table balances_v2(stream text primary key, balance int)"); rebuild_batches(c, "balances_v2")
            c.execute("begin immediate"); c.execute("alter table balances rename to balances_old"); c.execute("alter table balances_v2 rename to balances"); c.execute("commit")
        took = time.perf_counter() - t0; stop.set(); th.join()
        print(f"   {mode:<24}: rebuild took {took:4.1f} s; {stats['reads']} reads, {stats['wrong']} of them ({100*stats['wrong']/max(1,stats['reads']):.0f}%) returned a wrong total; slowest read {stats['worst']*1000:.0f} ms")

# ---------------------------------------------------------------------------------------------------------------- E. a hash chain: what it proves
def part_e(n=100_000):
    print("E. Tamper evidence with a hash chain")
    h = "0" * 64; chain = []
    rows = [(i, f"acct-{i % 50}", "Deposited" if i % 3 else "Withdrawn", json.dumps({"amount": 10 + i % 90})) for i in range(1, n + 1)]
    t = time.perf_counter()
    for pos, s, ty, d in rows: h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest(); chain.append(h)
    write = time.perf_counter() - t; anchored_head = chain[-1]                          # the head hash is published somewhere the database admin cannot change
    def verify(rows, anchor=None):
        h = "0" * 64
        for k, (pos, s, ty, d) in enumerate(rows):
            h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest()
            if h != chain[k]: return f"first broken link at position {pos}"
        return "chain consistent" + ("" if anchor is None else ("; head matches the anchor" if h == anchor else "; HEAD DIFFERS FROM THE ANCHOR"))
    t = time.perf_counter(); r0 = verify(rows, anchored_head); vt = time.perf_counter() - t
    print(f"   {n:,} events: chaining took {write*1000:.0f} ms, verifying {vt*1000:.0f} ms ({r0})")
    tampered = list(rows); tampered[49_999] = (50_000, rows[49_999][1], rows[49_999][2], json.dumps({"amount": 99999}))
    print(f"   a stored amount changed:  plain log -> nothing notices; hash chain -> {verify(tampered, anchored_head)}")
    h = "0" * 64; chain2 = []                                                            # the attacker has write access to the table AND recomputes every later hash
    for pos, s, ty, d in tampered: h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest(); chain2.append(h)
    chain_backup, chain[:] = chain[:], chain2
    print(f"   same edit with the chain recomputed: chain alone -> {verify(tampered).split(';')[0]}; with the head anchored outside -> {verify(tampered, anchored_head).split('; ')[-1]}")
    chain[:] = chain_backup

if __name__ == "__main__":
    part_a(); part_b(); part_c(); part_d(); part_e()

Sources

Practise this topic: Databases

More guides