Outbox Pattern, Dual-Write Failure, Idempotent Consumers, and Ordering Keys

1. The dual-write gap is a correctness gap

AtlasMart captures a payment and must update its order database and publish OrderPaid. If it writes the database first and crashes before the broker publish, the order is paid but downstream shipment/search/analytics never learn about it. If it publishes first and the database transaction later rolls back, consumers can observe an event for a state that never committed. Two independent durable systems create a dual-write problem unless one atomic protocol spans both.

A transactional outbox avoids that cross-system atomic write by storing the business mutation and a durable publication-intent record in the same local database transaction. A relay later publishes unsent outbox rows. The relay can poll the table or receive those rows through CDC.

2. Outbox mechanics and observable states

3. Idempotency, ordering keys, and poison handling

Because the relay can crash after publish but before recording completion, duplicate delivery is an expected design condition. Every durable event needs a stable event ID. Consumers either record that ID in a deduplication store or apply an operation whose result is naturally idempotent. The dedup record must live at least as long as duplicate delivery remains plausible for the business consequence.

Ordering is normally scoped, not global. Using order_id as a partition/order key can keep events for one order together: OrderPaid(seq=1) before OrderShipped(seq=2). It does not impose a meaningful total order between unrelated orders. Poison events need bounded retry and a dead-letter/quarantine path rather than blocking a whole partition forever.

4. Deliberately wrong approach: mark the outbox sent before publishing

If the relay changes sent=1 first and then crashes before broker publication, the publication intent is lost even though the row appears complete. Reversing the order—publish, then checkpoint/mark—is safer for loss but creates possible duplicates. That is exactly why the downstream consumer must be idempotent.

5. AtlasMart lab: local SQLite transaction + duplicate relay

import sqlite3

print("BROKEN DUAL WRITE")
orders = {}
broker = []
orders["o-19"] = {"status":"PAID", "version":3}   # DB commit succeeds
print("database committed:", orders["o-19"])
print("process crashes before broker publish")
print("broker events:", broker, "<- downstream never learns about payment")

print("\nTRANSACTIONAL OUTBOX")
con = sqlite3.connect(":memory:")
con.execute("create table orders(id text primary key, status text, version integer)")
con.execute("create table outbox(event_id text primary key, aggregate_id text, seq integer, payload text, sent integer default 0)")
with con:
    con.execute("insert into orders values(?,?,?)", ("o-20", "PAID", 1))
    con.execute("insert into outbox(event_id,aggregate_id,seq,payload) values(?,?,?,?)",
                ("evt-o20-1", "o-20", 1, "OrderPaid"))
print("order + publication intent committed atomically:", con.execute("select * from orders").fetchall(), con.execute("select event_id,sent from outbox").fetchall())

print("\nRELAY CRASH AFTER PUBLISH, BEFORE MARK-SENT")
broker=[]
row=con.execute("select event_id,aggregate_id,seq,payload from outbox where sent=0").fetchone()
broker.append(row)
print("published once:", broker)
print("relay crashes before sent=1; restart publishes same outbox row again")
broker.append(row)
print("broker deliveries:", [x[0] for x in broker])

print("\nIDEMPOTENT CONSUMER")
seen=set(); projection={}
for event_id, order_id, seq, payload in broker:
    if event_id in seen:
        print("deduplicated", event_id)
        continue
    seen.add(event_id)
    projection[order_id] = {"seq":seq, "state":payload}
print("projection:", projection)

print("\nORDERING KEY")
events=[("o-20",1,"OrderPaid"),("o-21",1,"OrderCreated"),("o-20",2,"OrderShipped")]
partitions={0:[],1:[]}
for e in events:
    p=sum(e[0].encode()) % 2       # deterministic teaching partitioner
    partitions[p].append(e)
print("partitions:", partitions)
print("same aggregate key keeps o-20 seq 1 then 2 in one partition")
print("outbox guarantees publication intent; downstream effects still need idempotency")

6. Production judgment

Persist outbox event ID, aggregate ID, aggregate sequence/version, event type/schema version, creation time, and a payload or stable reference. Decide whether rows are deleted, archived, or retained after relay acknowledgement. Monitor oldest unsent age, relay throughput, publish failures, duplicate rate, dead-letter count, per-key sequence gaps, broker acknowledgement latency, and outbox-table growth. Treat the outbox as sensitive business data and restrict who can forge publication records.

If you use a connector, pin and test versions rather than assuming compatibility. Debezium 3.6.1.Final is a current stable optional implementation reference; its 3.6 series is tested with Kafka Connect/brokers in the documented compatibility matrix. The mandatory design remains independent of any specific connector.

The next lesson moves from event publication to stream consumption at scale: partitions, consumer groups, replay, retention, lag, and backpressure.

References

Foundational claims use primary specifications/research or current official documentation where practical. Product references are optional implementation anchors; the mandatory labs are vendor-neutral.

• Transactional Outbox pattern — Canonical pattern explanation of the database/message dual-write problem and outbox relay.
• PostgreSQL 18 logical decoding — Official example of log-derived relay/change-stream infrastructure.
• Debezium 3.6 release series — Current stable connector series and compatibility details.
• Debezium documentation — Official connector and change-event documentation for optional implementation study.