Transactional Outbox & Idempotent Consumers

The dual-write problem#

When an order is placed, the service must do two things: save the order in its database and publish an OrderPlaced event to the broker. These are two different systems, and there's no transaction that spans both:

save order ✔ → publish event ✘ (broker down)   → order exists, nobody is notified
publish event ✔ → save order ✘ (DB error)      → everyone reacts to an order that doesn't exist

Either way, your system becomes inconsistent. This is the dual-write problem.

The transactional outbox pattern#

Write the event to an outbox table in the same database, in the same transaction as the business data. A separate relay process reads the outbox and publishes to the broker, marking rows as sent.

┌──────── one DB transaction ────────┐
│ INSERT INTO orders ...             │      relay (polling or CDC)
│ INSERT INTO outbox (event) ...     │ ───────────────────────────▶ broker
└────────────────────────────────────┘

Because both inserts commit or roll back together, an event exists if and only if the order exists.

import json
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER);
CREATE TABLE outbox (id INTEGER PRIMARY KEY, type TEXT, payload TEXT, published INTEGER DEFAULT 0);
""")

def place_order(customer: str, total: int) -> int:
    with db:                                   # one transaction: both rows or neither
        cur = db.execute("INSERT INTO orders (customer, total) VALUES (?, ?)", (customer, total))
        order_id = cur.lastrowid
        db.execute(
            "INSERT INTO outbox (type, payload) VALUES (?, ?)",
            ("OrderPlaced", json.dumps({"order_id": order_id, "customer": customer, "total": total})),
        )
    return order_id

def relay(publish) -> int:
    """Publish unsent outbox rows, then mark them published."""
    rows = db.execute("SELECT id, type, payload FROM outbox WHERE published = 0 ORDER BY id").fetchall()
    for row_id, event_type, payload in rows:
        publish(event_type, json.loads(payload))          # may raise → row stays unpublished, retried later
        with db:
            db.execute("UPDATE outbox SET published = 1 WHERE id = ?", (row_id,))
    return len(rows)

place_order("ayesha", 2500)
place_order("bilal", 4000)

sent = []
print("published:", relay(lambda t, p: sent.append((t, p["order_id"]))))
print(sent)
print("published on second run:", relay(lambda t, p: sent.append((t, p["order_id"]))))

Output:

published: 2
[('OrderPlaced', 1), ('OrderPlaced', 2)]
published on second run: 0

In production the relay is either a polling worker (SELECT … FOR UPDATE SKIP LOCKED for multiple workers) or change data capture (CDC) — a tool such as Debezium that streams new outbox rows from the database log to Kafka.

The catch: at-least-once delivery#

If the relay publishes an event and then crashes before marking the row as sent, it will publish the same event again after restarting. The outbox gives you at-least-once delivery — never zero times, but sometimes twice. Brokers and consumer retries add more duplicates.

So every consumer must be idempotent.

Idempotent consumers#

An operation is idempotent if doing it twice has the same effect as doing it once. Strategies:

1. Naturally idempotent operations — SET status = 'paid' can run twice safely; balance = balance + 100 cannot.
2. Deduplication table — record each processed event ID and skip repeats, in the same transaction as the side effect.
3. Unique constraints — let the database reject duplicates (e.g. a unique order_id on the invoices table).
4. Version checks — ignore events older than the version you already applied.

import sqlite3

store = sqlite3.connect(":memory:")
store.executescript("""
CREATE TABLE processed_events (event_id TEXT PRIMARY KEY);
CREATE TABLE loyalty (customer TEXT PRIMARY KEY, points INTEGER);
""")

def handle_order_placed(event: dict) -> str:
    try:
        with store:   # dedup record and side effect commit together
            store.execute("INSERT INTO processed_events (event_id) VALUES (?)", (event["event_id"],))
            store.execute(
                "INSERT INTO loyalty (customer, points) VALUES (?, ?) "
                "ON CONFLICT(customer) DO UPDATE SET points = points + excluded.points",
                (event["customer"], event["total"] // 100),
            )
        return "processed"
    except sqlite3.IntegrityError:
        return "duplicate ignored"

event = {"event_id": "evt-001", "customer": "ayesha", "total": 2500}
print(handle_order_placed(event))
print(handle_order_placed(event))        # redelivered by the broker
print(store.execute("SELECT points FROM loyalty WHERE customer = 'ayesha'").fetchone()[0])

Output:

processed
duplicate ignored
25

Ayesha gets 25 points exactly once, even though the event arrived twice.

The inbox pattern#

The deduplication table above is often called an inbox: the consumer records incoming message IDs in its own database. Outbox on the producer side plus inbox on the consumer side gives effectively-once processing on top of at-least-once delivery.

Event IDs and ordering#

• Give every event a globally unique ID (UUID) and an occurred-at timestamp.
• If order matters per entity, publish to a partition keyed by entity ID (e.g. Kafka partition by order_id) and include a sequence/version number so consumers can detect out-of-order events.

Common mistakes#

• Publishing to the broker inside the database transaction — a slow or failed broker blocks or breaks writes.
• Deduplicating in memory only — a restart forgets which events were processed.
• Recording the event as processed in a separate transaction from the side effect.
• Never cleaning the outbox — archive or delete published rows periodically.

Key takeaways#

• The dual-write problem makes "save then publish" unreliable.
• The transactional outbox writes events in the same transaction, and a relay publishes them.
• Delivery is at-least-once, so consumers must be idempotent.
• Deduplicate with an inbox table or unique constraints in the same transaction as the side effect.