SD System Design In Depth
0%
From-Scratch Build

Transactional Outbox & Idempotent CDC Relay

Solves the dual-write problem by atomically committing domain state + outbox records in a single local transaction, paired with a retry-safe CDC relay and idempotent consumer deduplication store.

~35 min Intermediate Lessons: Case: Stripe's idempotency keys, Retries, timeouts, and idempotency, Design: a payment system, Event contracts, Search index synchronization
Interactive Build Lab 0/4 Steps Completed

Code & Verify in the Browser Workbench

Complete 4 progressive engineering steps in JavaScript, TypeScript, or C++20 (WebAssembly) against an in-browser Node.js v22, Bun v1.1 & Clang/WASI runtime (virtual filesystem, loopback TCP/HTTP, and automated unit tests) — zero local setup required.

1. The Dual-Write Anti-Pattern

When a microservice updates its relational database (INSERT INTO orders) and directly publishes an event to Kafka (producer.send("OrderCreated")), two separate storage engines are mutated without a shared transaction:

  • If the process crashes after the DB commit but before Kafka ack, downstream services never learn the order exists (silent data loss).
  • If the process publishes to Kafka before committing to the DB and the DB transaction rolls back, downstream services act on a phantom order.
sequenceDiagram participant API as Order Service participant DB as Local SQL Transaction (Orders + Outbox) participant Relay as CDC / Outbox Relay participant Broker as Kafka / Event Stream participant Cons as Idempotent Consumer (Inbox Table) API->>DB: BEGIN; INSERT orders; INSERT outbox (PENDING); COMMIT; Relay->>DB: SELECT * FROM outbox WHERE status = 'PENDING' Relay->>Broker: Publish Event (id = evt-1) Note over Relay,DB: If Relay crashes before UPDATE outbox, evt-1 is sent twice Relay->>DB: UPDATE outbox SET status = 'PUBLISHED' Broker->>Cons: Deliver evt-1 (Attempt 1 & Attempt 2) Cons->>Cons: Deduplicate by eventId in processedEventIds

How the Transactional Outbox + Idempotent Inbox Achieves End-to-End Exactly-Once Effects

  1. Atomic Local Transaction (TransactionalStore.createOrderWithOutbox): Both the domain entity (orders) and the event record (outbox) are written inside the same local ACID transaction. Either both commit or neither commits.
  2. At-Least-Once Relay (OutboxRelay.pollAndPublish): An asynchronous relay tails or polls PENDING outbox records and publishes them to the broker. Only after the broker acknowledges receipt does the relay mark the row PUBLISHED.
  3. Idempotent Consumer (IdempotentConsumer.handleEvent): Because the relay guarantees at-least-once delivery (it may crash between broker ack and markPublished), every consumer records eventId in an inbox/deduplication table so duplicate deliveries are safely ignored.

2. Verification & Running the Lab

bash
node demo.js
node test.js