DEV Community

Cover image for Your Outbox Can Still Deliver Twice
SMTasker Team for SMTasker

Posted on

Your Outbox Can Still Deliver Twice

A receiver commits its work. The reply disappears. The sender sees a timeout and sends the same event again.

An outbox can preserve the event through that sequence. It cannot, by itself, tell the receiver that the work already happened.

Here is a small reproduction with two SQLite databases and ten tests. The unsafe receiver adds two rows for one event. The receiver with an inbox adds one row and returns its stored receipt on the second delivery.

This continues the previous example about recovering a lost response. That example kept a mutation and its operation receipt in one database. This time the handoff crosses between two separate databases.

Three commits to keep separate

Imagine a service that accepts a report request and hands it to a worker service. The fixture models a completed worker effect by inserting a row into report_runs; it does not generate a file or call an external API.

Step Durable state What can still be unknown?
Producer commits Report request and outgoing event exist together Whether the receiver has seen the event
Receiver commits Local effect and inbox receipt exist together Whether the sender received the reply
Sender records the receipt The matching outbox row is marked delivered Nothing about later work outside this fixture

The transactional outbox explanation from AWS describes the first boundary: save the business change and outgoing intent in the same database transaction, then publish separately.

There is still a gap between the second and third rows of the table. If the receiver commits but its reply is lost, the sender must be able to deliver the event again.

Make the receiver remember the event

The inbox key in this example is:

(source, workspace_id, event_id)
Enter fullscreen mode Exit fullscreen mode

The event ID identifies one intent. The producer creates it once, persists it in the outbox, and reuses it for delivery retries. Two genuinely new requests can have identical payloads and still need different event IDs.

For a new key, the receiver inserts the local effect and its receipt in one transaction. For an existing key, it compares the payload and returns the saved receipt. A reused key with a changed payload is refused.

The transaction starts before the inbox lookup. With SQLite's BEGIN IMMEDIATE, this fixture acquires the write transaction before doing the read-then-write decision. SQLite allows only one simultaneous writer. A concurrent duplicate waits for the first transaction, then sees its receipt. The primary key remains a database constraint on event identity.

This is a deliberately small SQLite design. Another database needs its own concurrency argument; copying an existence check into two ordinary concurrent transactions is insufficient.

Reproduce the missing reply

The test injects a failure after the receiver has committed and closed its connection:

def lose_reply(event):
    consume(receiver, event)
    raise TimeoutError("receiver committed; reply lost")
Enter fullscreen mode Exit fullscreen mode

The first delivery leaves the sender's outbox row pending. A fresh dispatch reads the same persisted event and sends it again. With the unsafe control, that creates a second effect. With the inbox, it returns the original receipt and leaves the effect count at one.

The send callback runs outside the producer transaction. The sender opens a new transaction only after receiving a receipt that identifies the event it sent. This keeps the example from holding a producer write lock across the simulated remote call.

Run the fixture

Save the complete file below as outbox_inbox.py and run:

python outbox_inbox.py
Enter fullscreen mode Exit fullscreen mode

It requires Python 3.12 or later, uses the standard library, and creates disposable databases under a test-db folder beside the file. Each test closes its connections and removes its own temporary subdirectory.

The ten tests passed locally with Python 3.12.14 and SQLite 3.53.1. They test these outcomes:

Injected condition Assertion
Outbox insertion fails Neither the report request nor its outgoing event commits
Reply is lost, without an inbox Retrying creates two local effects
Reply is lost, with an inbox Retrying returns a stored receipt; one effect remains
Inbox receipt insertion fails The receiver's effect also rolls back
Sender cannot save the acknowledgement The receiver stays committed; retry replays its receipt
Two relays read the same pending event One fresh receipt and one replay, with the same receipt ID
Same event key, changed payload Refusal, without another effect
Same event ID in distinct trusted scopes Separate receipts for each source/workspace key
Two new requests with identical payloads Two distinct event IDs and two effects
Reply identifies an unrelated event The outgoing row stays pending

The concurrent test places a barrier after both relays have read the pending row. It therefore tests a duplicate delivery, rather than relying on thread timing to happen to create one.

Complete runnable example and all ten tests

"""Two local SQLite databases; no broker, network, MCP transport or external effect.

Python 3.12+. Run: python outbox_inbox.py
"""
from contextlib import closing, contextmanager
from dataclasses import dataclass, replace
from pathlib import Path
from concurrent.futures import ThreadPoolExecutor
from threading import Barrier
import json
import sqlite3
import tempfile
import unittest
import uuid


@dataclass(frozen=True)
class Event:
    source: str
    workspace_id: str
    event_id: str
    payload: str

    @property
    def key(self):
        return self.source, self.workspace_id, self.event_id


@dataclass(frozen=True)
class Receipt:
    key: tuple[str, str, str]
    receipt_id: str
    replayed: bool


class PayloadMismatch(Exception):
    pass


class BadReceipt(Exception):
    pass


def connect(path):
    db = sqlite3.connect(path, timeout=5, autocommit=True)
    db.row_factory = sqlite3.Row
    return db


def one(db, sql, args=()):
    with closing(db.execute(sql, args)) as cursor:
        return cursor.fetchone()


@contextmanager
def transaction(path):
    with closing(connect(path)) as db:
        try:
            db.execute("BEGIN IMMEDIATE").close()
            yield db
            db.execute("COMMIT").close()
        except BaseException:
            if db.in_transaction:
                db.execute("ROLLBACK").close()
            raise


def initialize(producer, receiver):
    with closing(connect(producer)) as db:
        db.executescript("""
        CREATE TABLE report_requests (
            id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL,
            report_name TEXT NOT NULL
        );
        CREATE TABLE outbox (
            seq INTEGER PRIMARY KEY AUTOINCREMENT,
            source TEXT NOT NULL, workspace_id TEXT NOT NULL,
            event_id TEXT NOT NULL UNIQUE, payload TEXT NOT NULL,
            delivered INTEGER NOT NULL DEFAULT 0
        );
        """).close()
    with closing(connect(receiver)) as db:
        db.executescript("""
        CREATE TABLE report_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            source TEXT NOT NULL, workspace_id TEXT NOT NULL,
            event_id TEXT NOT NULL, report_name TEXT NOT NULL
        );
        CREATE TABLE inbox (
            source TEXT NOT NULL, workspace_id TEXT NOT NULL,
            event_id TEXT NOT NULL, payload TEXT NOT NULL,
            receipt_id TEXT NOT NULL,
            PRIMARY KEY (source, workspace_id, event_id)
        );
        """).close()


def payload_for(report_name):
    if not isinstance(report_name, str) or len(report_name) not in range(1, 81):
        raise ValueError("report_name must contain 1..80 characters")
    return json.dumps({"format": "csv", "report_name": report_name},
                      sort_keys=True, separators=(",", ":"))


def request_report(producer, workspace_id, report_name):
    # Trusted, already-authorized context in this fixture. This is a NEW intent.
    event = Event("report-service", workspace_id, str(uuid.uuid4()),
                  payload_for(report_name))
    with transaction(producer) as db:
        db.execute("INSERT INTO report_requests VALUES (?, ?, ?)",
                   (str(uuid.uuid4()), workspace_id, report_name)).close()
        db.execute("""INSERT INTO outbox
            (source, workspace_id, event_id, payload) VALUES (?, ?, ?, ?)""",
                   (*event.key, event.payload)).close()
    return event


def insert_run(db, event):
    body = json.loads(event.payload)
    if (not isinstance(body, dict) or set(body) != {"format", "report_name"}
            or body["format"] != "csv"
            or payload_for(body["report_name"]) != event.payload):
        raise ValueError("unsupported payload; use the fixture serialization")
    with closing(db.execute("INSERT INTO report_runs "
            "(source, workspace_id, event_id, report_name) VALUES (?, ?, ?, ?)",
            (*event.key, body["report_name"]))) as cursor:
        return f"receipt-{cursor.lastrowid}"


def consume(receiver, event):
    with transaction(receiver) as db:
        old = one(db, """SELECT payload, receipt_id FROM inbox
            WHERE source = ? AND workspace_id = ? AND event_id = ?""",
                  event.key)
        if old:
            if old["payload"] != event.payload:
                raise PayloadMismatch(event.key)
            return Receipt(event.key, old["receipt_id"], True)
        # The modeled effect and its deduplication receipt commit together.
        receipt_id = insert_run(db, event)
        db.execute("INSERT INTO inbox VALUES (?, ?, ?, ?, ?)",
                   (*event.key, event.payload, receipt_id)).close()
        return Receipt(event.key, receipt_id, False)


def consume_without_inbox(receiver, event):
    # Deliberately unsafe control: every delivery adds a local effect.
    with transaction(receiver) as db:
        return Receipt(event.key, insert_run(db, event), False)


def next_event(producer):
    with closing(connect(producer)) as db:
        row = one(db, """SELECT source, workspace_id, event_id, payload
            FROM outbox WHERE delivered = 0 ORDER BY seq LIMIT 1""")
        return Event(**dict(row)) if row else None


def dispatch_one(producer, send):
    event = next_event(producer)
    if event is None:
        return None
    # No producer transaction is held across this external-call seam.
    receipt = send(event)
    if (not isinstance(receipt, Receipt) or receipt.key != event.key
            or not isinstance(receipt.receipt_id, str) or not receipt.receipt_id):
        raise BadReceipt("acknowledgement does not identify the sent event")
    with transaction(producer) as db:
        with closing(db.execute("""UPDATE outbox SET delivered = 1
            WHERE source = ? AND workspace_id = ? AND event_id = ?
            AND payload = ?""", (*event.key, event.payload))) as cursor:
            if cursor.rowcount != 1:
                raise BadReceipt("outbox intent changed or disappeared")
    return receipt


class HandoffTests(unittest.TestCase):
    def setUp(self):
        base = Path(__file__).resolve().parent / "test-db"
        base.mkdir(exist_ok=True)
        self.temp = tempfile.TemporaryDirectory(prefix="handoff-", dir=base)
        self.folder = Path(self.temp.name).resolve()
        assert self.folder.is_relative_to(base.resolve())
        self.addCleanup(self.temp.cleanup)
        self.producer = self.folder / "producer.sqlite"
        self.receiver = self.folder / "receiver.sqlite"
        initialize(self.producer, self.receiver)

    def sql(self, path, statement):
        with closing(connect(path)) as db:
            db.execute(statement).close()

    def count(self, path, table, where="1"):
        # All table/where arguments below are fixed test constants.
        with closing(connect(path)) as db:
            return one(db, f"SELECT count(*) FROM {table} WHERE {where}")[0]

    def pending(self):
        return self.count(self.producer, "outbox", "delivered = 0")

    def send(self, event):
        return consume(self.receiver, event)

    def test_producer_failure_rolls_back_business_and_intent(self):
        self.sql(self.producer, """CREATE TRIGGER reject_outbox
            BEFORE INSERT ON outbox BEGIN SELECT RAISE(ABORT, 'injected'); END""")
        with self.assertRaises(sqlite3.IntegrityError):
            request_report(self.producer, "team-a", "weekly")
        self.assertEqual(self.count(self.producer, "report_requests"), 0)
        self.assertEqual(self.count(self.producer, "outbox"), 0)

    def test_unsafe_receiver_applies_twice_after_lost_reply(self):
        request_report(self.producer, "team-a", "weekly")
        def lose_reply(event):
            consume_without_inbox(self.receiver, event)
            raise TimeoutError("receiver committed; reply lost")
        with self.assertRaises(TimeoutError):
            dispatch_one(self.producer, lose_reply)
        self.assertEqual(self.pending(), 1)
        dispatch_one(self.producer,
                     lambda event: consume_without_inbox(self.receiver, event))
        self.assertEqual(self.count(self.receiver, "report_runs"), 2)
        self.assertEqual(self.pending(), 0)

    def test_inbox_recovers_lost_reply_with_one_local_effect(self):
        event = request_report(self.producer, "team-a", "weekly")
        def lose_reply(sent):
            self.send(sent)
            raise TimeoutError("receiver committed; reply lost")
        with self.assertRaises(TimeoutError):
            dispatch_one(self.producer, lose_reply)
        self.assertEqual(self.pending(), 1)
        receipt = dispatch_one(self.producer, self.send)
        self.assertTrue(receipt.replayed)
        self.assertEqual(receipt.key, event.key)
        self.assertEqual(self.count(self.receiver, "report_runs"), 1)
        self.assertEqual(self.count(self.receiver, "inbox"), 1)
        self.assertEqual(self.pending(), 0)

    def test_receiver_receipt_failure_rolls_back_effect(self):
        request_report(self.producer, "team-a", "weekly")
        self.sql(self.receiver, """CREATE TRIGGER reject_receipt
            BEFORE INSERT ON inbox BEGIN SELECT RAISE(ABORT, 'injected'); END""")
        with self.assertRaises(sqlite3.IntegrityError):
            dispatch_one(self.producer, self.send)
        self.assertEqual(self.count(self.receiver, "report_runs"), 0)
        self.assertEqual(self.count(self.receiver, "inbox"), 0)
        self.assertEqual(self.pending(), 1)
        self.sql(self.receiver, "DROP TRIGGER reject_receipt")
        self.assertFalse(dispatch_one(self.producer, self.send).replayed)

    def test_sender_ack_failure_replays_committed_receipt(self):
        request_report(self.producer, "team-a", "weekly")
        self.sql(self.producer, """CREATE TRIGGER reject_ack
            BEFORE UPDATE ON outbox BEGIN SELECT RAISE(ABORT, 'injected'); END""")
        with self.assertRaises(sqlite3.IntegrityError):
            dispatch_one(self.producer, self.send)
        self.assertEqual(self.pending(), 1)
        self.assertEqual(self.count(self.receiver, "report_runs"), 1)
        self.sql(self.producer, "DROP TRIGGER reject_ack")
        self.assertTrue(dispatch_one(self.producer, self.send).replayed)
        self.assertEqual(self.count(self.receiver, "report_runs"), 1)
        self.assertEqual(self.pending(), 0)

    def test_two_relays_deliver_same_pending_event_concurrently(self):
        request_report(self.producer, "team-a", "weekly")
        barrier = Barrier(2, timeout=5)
        def simultaneous_send(event):
            barrier.wait()  # Both relays have already read the pending row.
            return self.send(event)
        with ThreadPoolExecutor(max_workers=2) as pool:
            futures = [pool.submit(dispatch_one, self.producer,
                                   simultaneous_send) for _ in range(2)]
            receipts = [future.result(timeout=10) for future in futures]
        self.assertEqual({r.replayed for r in receipts}, {False, True})
        self.assertEqual(receipts[0].receipt_id, receipts[1].receipt_id)
        self.assertEqual(self.count(self.receiver, "report_runs"), 1)
        self.assertEqual(self.count(self.receiver, "inbox"), 1)
        self.assertEqual(self.pending(), 0)

    def test_same_identity_with_changed_payload_is_refused(self):
        event = request_report(self.producer, "team-a", "weekly")
        self.send(event)
        with self.assertRaises(PayloadMismatch):
            self.send(replace(event, payload=payload_for("monthly")))
        self.assertEqual(self.count(self.receiver, "report_runs"), 1)

    def test_receipts_are_scoped_to_source_and_workspace(self):
        event = request_report(self.producer, "team-a", "weekly")
        events = [event, replace(event, workspace_id="team-b"),
                  replace(event, source="other-trusted-service")]
        receipts = [self.send(item) for item in events]
        self.assertEqual(len({r.key for r in receipts}), 3)
        self.assertEqual(self.count(self.receiver, "report_runs"), 3)
        for item in events:
            self.assertTrue(self.send(item).replayed)
        self.assertEqual(self.count(self.receiver, "report_runs"), 3)

    def test_new_intents_with_identical_payload_are_not_deduplicated(self):
        first = request_report(self.producer, "team-a", "weekly")
        second = request_report(self.producer, "team-a", "weekly")
        self.assertEqual(first.payload, second.payload)
        self.assertNotEqual(first.event_id, second.event_id)
        self.assertEqual(dispatch_one(self.producer, self.send).key, first.key)
        self.assertEqual(next_event(self.producer), second)
        self.assertEqual(self.pending(), 1)
        self.assertEqual(dispatch_one(self.producer, self.send).key, second.key)
        self.assertEqual(self.count(self.receiver, "report_runs"), 2)
        self.assertIsNone(dispatch_one(self.producer, self.send))

    def test_unrelated_receipt_does_not_acknowledge_the_event(self):
        request_report(self.producer, "team-a", "weekly")
        def wrong_receipt(event):
            return Receipt((event.source, event.workspace_id, "different-id"),
                           "receipt-from-another-event", False)
        with self.assertRaises(BadReceipt):
            dispatch_one(self.producer, wrong_receipt)
        self.assertEqual(self.pending(), 1)
        self.assertEqual(self.count(self.receiver, "report_runs"), 0)


if __name__ == "__main__":
    import sys
    print(f"Python {sys.version.split()[0]}; SQLite {sqlite3.sqlite_version}",
          flush=True)
    unittest.main(verbosity=2)
Enter fullscreen mode Exit fullscreen mode

What the receipt actually proves

Here, a receipt means the receiver committed a report_runs row and the corresponding inbox row. It does not mean an email was sent, a payment settled, a social post appeared, or a generated report reached storage. Those effects are absent from the fixture.

Moving send_email() inside the receiver's SQLite transaction would not make the email roll back with the database. For another external handoff, the receiving API needs an appropriate idempotency or reconciliation contract of its own.

There are a few other boundaries worth keeping explicit:

  • Identity is trusted context here. The source and workspace fields are fixtures, not an authorization system. A real receiver must derive or validate that context from its authenticated caller before consulting the inbox. A receipt is not permission to act.
  • Creating an intent is separate from retrying delivery. Calling request_report() again creates a new request. Deduplicating a retried client request at that earlier entry point is a separate boundary, covered by the previous article.
  • Payload comparison is narrow. The producer uses one serialization for this small report schema. This is not a general JSON canonicalization scheme. The receiver compares the persisted payload on replay and rejects a changed one.
  • Retention is part of correctness. Deleting an inbox receipt while its event can still be replayed makes that delivery look new. Define the replay window and restore procedure before pruning receipts.
  • Deduplication does not impose business ordering. Event B can arrive before event A. Version checks, ordering rules and stale-event handling are separate decisions.

The fixture also omits relay leases, backoff, retry budgets, poison-event handling, monitoring and transport authentication. Its five-second SQLite busy timeout is a test setting, not a throughput claim. It does not simulate a power failure or prove a particular broker's delivery guarantees.

The useful result is narrower: a retried event can produce one modeled local effect when the receiver commits that effect and its deduplication receipt together. The outbox keeps an intent available for delivery; the receiver's transaction makes a repeated delivery recognizable.

Which failure boundary would you test first in your own worker: a lost reply after commit, a failed acknowledgement write, or two relays delivering the same event? Leave the sequence in the comments—especially what durable state each side can inspect after the timeout.

Top comments (0)