Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Python SQLite outbox project: commit a receipt and its pending event together

Last updated: 30 Sept 20264 min read
tutorial
IntermediateBy AITrove Editorial

An outbox stores an application state change and its pending event in the same database transaction so they cannot commit independently.

Download Python source kit

Operation contract

The receipt writer inserts a receipt and one stable event ID together. A forced failure after the receipt insert rolls back both tables. The local consumer reads pending events and records an acknowledgement separately. Reading again before acknowledgement returns the same event, exposing the duplicate-delivery boundary rather than pretending a database transaction controls an external recipient.

Failure and ownership boundary

No network delivery occurs here. A worker can crash after an external recipient accepts an event but before the acknowledgement commits, so the recipient still needs a deduplication/idempotency policy. Concurrent workers require claiming or lease rules absent from this sequential fixture. Python SQLite job project: reject conflicting replays by request identity, Python SQLite writer contention: reject a busy transaction before changing state and Spring transaction events: run a listener after commit without claiming durability connect the same failure model.

Working program

python
import sqlite3

def open_outbox():
    database = sqlite3.connect(":memory:")
    database.executescript("CREATE TABLE receipts (id TEXT PRIMARY KEY, amount INTEGER NOT NULL); CREATE TABLE outbox (id TEXT PRIMARY KEY, receipt_id TEXT NOT NULL, acknowledged INTEGER NOT NULL DEFAULT 0);")
    return database

def create_receipt(database, identifier, amount, fail=False):
    if not isinstance(identifier, str) or identifier not in {"R-0041", "R-0042"} or type(amount) is not int or not 0 <= amount <= 1000000:
        raise ValueError("receipt rejected")
    with database:
        database.execute("INSERT INTO receipts VALUES (?,?)", (identifier, amount))
        if fail: raise RuntimeError("staged failure")
        database.execute("INSERT INTO outbox(id,receipt_id) VALUES (?,?)", ("created:" + identifier, identifier))

def pending_events(database):
    return database.execute("SELECT id,receipt_id FROM outbox WHERE acknowledged=0 ORDER BY id LIMIT 16").fetchall()

with open_outbox() as database:
    create_receipt(database, "R-0041", 125)
    try: create_receipt(database, "R-0042", 250, fail=True)
    except RuntimeError: print("failed receipt rolled back")
    print("receipts:", database.execute("SELECT count(*) FROM receipts").fetchone()[0])
    first = pending_events(database)
    print("pending:", first)
    print("replay before acknowledgement:", pending_events(database) == first)
    with database:
        database.execute("UPDATE outbox SET acknowledged=1 WHERE id=?", (first[0][0],))
    print("pending after acknowledgement:", pending_events(database))
database.close()

Output

Output
failed receipt rolled back
receipts: 1
pending: [('created:R-0041', 'R-0041')]
replay before acknowledgement: True
pending after acknowledgement: []

Costs and limits

The fixture performs fixed-size inserts and bounded pending reads. A production outbox needs retention, indexes, retry limits and metrics. The in-memory database proves transaction behavior here, not clean-reopen durability or exactly-once external effects.

Common Mistakes

  • Store the event inside the same transaction as the state change.
  • An external delivery and its later acknowledgement are separate failure points.

Connected lessons

Python SQLite job project: reject conflicting replays by request identity, Python SQLite writer contention: reject a busy transaction before changing state, Spring transaction events: run a listener after commit without claiming durability.

Follow the service contract

Python SQLite outbox leases: stale workers must not acknowledge a newer claim.

python
outbox-project
Storage details