Case Study 1: 1,420 Orders That Never Happened

"The reconciliation had been passing for two years. It compared the warehouse to the event stream, and both of them were missing the same orders."

Executive Summary

Kestrel's checkout service wrote an order to Postgres and then published an OrderPlaced event. Two writes, no transaction between them.

Over ninety days, 1,420 orders committed to the database and never reached the event log — 0.24% of 591,750 orders, or about sixteen a day.

Every downstream consumer under-reported by 0.24%, including gold.daily_revenue, which understated by $107,678.60 over the period.

Nothing detected it for two years, and the reason is the finding: the reconciliation that existed compared the warehouse against the event stream, and the warehouse was built from the event stream. Two derived numbers agreeing tells you nothing about the source.

The fix was a transactional outbox: zero losses since, at the cost of duplicates that idempotent consumers absorb.

Skills applied: the dual write (§36.5); why publish-first is worse; the outbox's loss-for-duplication trade; and Chapter 23's reconciliation done against the right thing.

Background

The code was four lines and had been reviewed by three people.

def place_order(order):
    with db.transaction():
        db.execute("INSERT INTO orders ...")
        db.execute("INSERT INTO order_lines ...")
    kafka.publish("OrderPlaced", to_event(order))     # <- outside
    return 200

The transaction is correct. The order and its lines commit atomically. The publish is outside it, because a Kafka publish cannot be inside a Postgres transaction — which is true, is the reason the code is written this way, and is the entire problem.

Everything downstream consumed the event stream, which was a deliberate architectural decision made two years earlier and is the right one:

Postgres orders ──> OrderPlaced ──> Kafka ──> bronze.orders_raw
                                          └─> silver.stg_orders
                                              └─> gold.fct_order_line
                                                  └─> gold.daily_revenue

The service had a retry. If the publish raised, it retried three times with backoff, and if all three failed it logged an error. In two years the error had been logged eleven times, each investigated and resolved.

The Problem

The retry handles the publish failing. It does not handle the process not existing.

Three ways the process dies between the commit and the publish, and all three are routine:

a deploy rolls the pod                    ~40 times/month
an OOM kill                               ~6 times/month
a node preemption or health-check restart ~12 times/month

Each one kills whatever requests are in flight. A request that has committed and not yet published loses its event, silently, permanently — the customer's order exists, the confirmation email sends, the warehouse ships it, and no event was ever written.

⚠️ Failure Mode — every layer reported success

Trace the request and notice that nothing anywhere is wrong:

text the customer saw a confirmation page ok the service returned 200 ok Postgres committed the transaction ok the load balancer recorded a 2xx ok the error log nothing to report ok Kafka never heard of this order <- and cannot know

Kafka's absence of an event is not an error condition. There is no consumer waiting for order 889201 specifically; the topic simply has one fewer message than it should, and no observer has a reference point that would reveal it.

This is what "silent" means precisely: there is no component with both halves of the picture. The service knows it committed and does not know it failed to publish (it was dead). Kafka knows what it received and not what it should have. The warehouse knows what Kafka sent it.

The only component that could detect it is one that compares Postgres to Kafka, and Kestrel did not have one — which is not an oversight so much as a consequence: once you decide the event stream is the source of truth, comparing it to the database feels like comparing the source to a copy.

And there was a reconciliation. Chapter 23 §23.11's, running daily:

-- daily reconciliation, passing for two years
SELECT count(*) FROM gold.fct_order_line WHERE order_date = :d
-- compared against
SELECT count(*) FROM bronze.orders_raw   WHERE order_date = :d

🔎 Read the Plan — two derived numbers agreeing tells you nothing about the source

The reconciliation compared gold against bronze. Both are built from the event stream.

text Postgres ──X──> Kafka ──> bronze ──> silver ──> gold └──────── the reconciliation compares here ────┘

It was testing that the transformation chain preserved row counts, which is a real and useful thing to test and is not what anybody thought it was testing. An order lost before Kafka is absent from both sides, so both sides agree, and the check passes.

The general form, and it is worth memorizing: a reconciliation is only as good as the independence of its two sides. Two numbers derived from a common ancestor agree on everything the ancestor got wrong.

The check that would have found it in a day:

sql -- Postgres, the actual system of record for order creation SELECT count(*) FROM orders WHERE placed_at::date = :d -- against SELECT count(*) FROM bronze.orders_raw WHERE order_date = :d

One side is the operational database and the other is the platform. They share nothing but the business event, which is exactly the property a reconciliation needs, and Kestrel had built the harder version and skipped the easy one.

The reason it was skipped is worth stating because it will apply to you: reading production Postgres from the warehouse felt like a layering violation. It is a small daily count query against a read replica, and it is the only check in the platform with genuine independence.

The Analysis

The discovery was accidental and came from the finance side.

A month-end close showed a 0.2% gap between the payment processor's settled transaction count and gold.daily_revenue's order count. The gap had been there for two years and had been attributed to timing differences at the month boundary — plausible, since a small boundary effect is real, and it had absorbed the explanation in the same way Chapter 34 Case Study 2's five-hour timezone gap did.

What made this month different: the gap was 0.24% and the boundary effect was known to be about 0.02%.

The independent count settled it in twenty minutes:

Postgres `orders`, 90 days              591,750
bronze.orders_raw, same 90 days         590,330
                                        ───────
missing                                   1,420   (0.240%)

Then the correlation that identified the mechanism. The missing orders were plotted by minute:

missing orders, by minute of day
  clustered at 09:14, 13:47, 16:02, 21:30...
  -> matched the deploy log exactly

Every cluster was a deployment. The remainder scattered across OOM kills and node restarts.

The Decision

Four changes, and the second is the one that generalizes.

One: a transactional outbox.

BEGIN;
  INSERT INTO orders (...);
  INSERT INTO order_lines (...);
  INSERT INTO outbox (aggregate_id, aggregate_type, event_type,
                      payload, created_at)
       VALUES (:order_id, 'order', 'OrderPlaced', :payload, now());
COMMIT;

A relay publishes unsent outbox rows and marks them sent. Kestrel used Debezium CDC on the outbox table (Chapter 14), which removed the relay entirely — the write-ahead log becomes the relay.

Two: the reconciliation is rebuilt against an independent source.

📐 Design Decision — reconcile against the least-derived thing you can reach

Kestrel's rule after this incident, and it applies well beyond event streams:

text for every gold table, name the most INDEPENDENT source you can reconcile it against, and reconcile against THAT -- not against whatever is convenient.

Ranked, for gold.daily_revenue:

Source	Independence	Available?
the payment processor's settlement file	highest	monthly
Postgres orders	high	daily ← chosen
Kafka topic offsets	medium	daily
bronze.orders_raw	none (common ancestor)	daily ← was used

The processor's file is the best check and arrives monthly, so Kestrel runs both: Postgres daily, the processor monthly. The monthly one is what would eventually have caught this; the daily one catches it in a day.

The cost of the daily check is a count(*) against a read replica — a few hundred milliseconds — which is why the layering objection did not survive being priced.

And the general warning the rule encodes: convenience selects for dependence. The easiest thing to reconcile against is always something already in your warehouse, and everything already in your warehouse shares your warehouse's ancestors.

Three: every consumer becomes idempotent, keyed on (aggregate_id, version), enforced by a shared base class. This is the price of the outbox — the relay can publish twice — and it had to be paid before the outbox could ship.

Four: backfill the 1,420. They existed in Postgres; the events were synthesized from the rows, published with their original timestamps, and the projections rebuilt.

🔁 Idempotency Check — the backfill that could only be run because consumers were idempotent

The ordering here is the operational lesson. The team's first plan was to fix the outbox and backfill in the same release. That plan would have failed, and the reason took a whiteboard to see.

Synthesizing 1,420 events and publishing them means republishing into a topic that consumers are still reading. Without idempotency, a consumer that had partially processed an affected order — which several had, because ItemAdded events were published successfully even when OrderPlaced was lost — would double-count.

So the sequence had to be:

text 1. make every consumer idempotent 3 weeks 2. verify with a deliberate double-publish 1 day <- the step nobody plans 3. ship the outbox 1 week 4. backfill the 1,420 1 day

Step 2 is the one worth copying. Kestrel republished one day's events deliberately, into production, and asserted that every projection was unchanged. It was not — one consumer's dedup keyed on order_id alone rather than (order_id, version), so it discarded legitimate later events for orders it had already seen. That bug would have silently dropped data during the backfill, and it was found by a test that took one day and that a plan without step 2 would never have run.

The general rule: before you rely on idempotency, exercise it on purpose. It is Chapter 34's "a capability you never exercise is one you do not have," applied to a property rather than to a rebuild.

What Happened

The reconciliation has fired twice since, in eighteen months:

Once for a genuine outbox relay failure. The relay pod could not reach Kafka for forty minutes. The outbox rows were still there — this is the guarantee — and the relay published them on recovery. The reconciliation was red for one day and self-healed, which is exactly the intended behaviour and which took a conversation to accept, because a self-healing red check feels wrong.

Once for a real bug elsewhere. A schema change to orders added a column that the outbox payload builder did not include; the events were published, were valid, and were missing a field the warehouse required, so bronze.orders_raw rejected 340 rows into quarantine (Chapter 34 §34.12). The count mismatch surfaced it in a day.

Lessons

1. A commit and a publish are two writes with no transaction between them. 1,420 orders lost in 90 days at a 0.24% rate — about sixteen a day, from deploys, OOM kills, and restarts.

2. ⚠️ Every layer reported success. The customer saw a confirmation, the service returned 200, Postgres committed, the error log was empty. "Silent" means precisely that no component has both halves of the picture.

3. A retry handles the publish failing and not the process ceasing to exist, which is the case that matters.

4. 🔎 A reconciliation is only as good as the independence of its two sides. Kestrel compared gold to bronze — both built from the event stream — so an order lost before Kafka was absent from both and the check passed for two years.

5. 📐 Reconcile against the least-derived source you can reach. Ranked by independence, and convenience selects for dependence: the easiest thing to compare against is always already in your warehouse, and everything there shares your warehouse's ancestors.

6. The layering objection did not survive being priced — a daily count(*) against a read replica.

7. The gap had an explanation for two years. A 0.2% month-end discrepancy attributed to boundary timing, plausibly, in the same way Chapter 34 Case Study 2's timezone gap was. What broke it was knowing the boundary effect's actual size (0.02%).

8. Plotting the missing orders by minute matched the deploy log exactly, which identified the mechanism in one chart.

9. 🔁 Make consumers idempotent before shipping the outbox, and exercise it deliberately. Kestrel's intentional double-publish found a consumer deduping on order_id alone — a bug that would have silently dropped data during the backfill.

10. A self-healing red check feels wrong and is correct. The outbox's guarantee is that the rows survive; a forty-minute relay outage should show red and then resolve itself.

11. The new reconciliation caught an unrelated bug in its first eighteen months — an outbox payload missing a newly-added column — which is the usual return on an independent check.

Questions for Discussion

1. The reconciliation compared two derived numbers for two years. Audit your own reconciliations: for each, are the two sides genuinely independent?

2. Reading production Postgres from the warehouse felt like a layering violation. Is it? What would make it one?

3. The 0.2% gap had a plausible explanation for two years. What would have made somebody check the explanation's magnitude sooner?

4. Kestrel used CDC on the outbox table rather than a relay process. What does that buy, and what does it cost?

5. The intentional double-publish found a real bug. What else would you deliberately break in production to verify a property you depend on?

6. The backfill synthesized events from database rows. Are those real events? What is different about them, and should they be marked?

7. Should the service have failed the request if the publish failed — returning 500 to a customer whose order committed? Argue both sides.