FastAPI Production

Checklist/Docs/Outbox, inbox, and idempotency

Guide 04

Outbox, inbox, and idempotency

Audience: Engineers shipping reliable dual-writes (DB + broker). Guarantee: Brokers deliver at least once. Networks fail between commit and publish.

TL;DR

Transactional outbox: commit then publish safely
APIDB transactionjob + outbox rowOutbox relaySKIP LOCKEDRabbitMQ / Kafkapublishsame commit → no dual-write gapmark published_at
ProblemPattern
Commit then publish can drop the publishTransactional outbox
At-least-once delivery double-runs handlersInbox / natural keys
Client double-clicks submitIdempotency-Key on enqueue

Contents

  1. Dual-write problem
  2. Transactional outbox
  3. Relay design
  4. Inbox (consumer dedupe)
  5. Idempotent handlers
  6. When outbox is optional
  7. Pseudocode end-to-end
  8. Ops

---

1. Dual-write problem

text
A) INSERT job OK → publish FAIL → stuck pending, client may retry
B) publish OK → INSERT FAIL → worker has message, no job row

Exactly-once across DB and broker without an extra pattern is a myth for most stacks. Design for at-least-once + idempotency.

2. Transactional outbox

Write business state and “message to send” in one DB transaction.

sql
-- PSEUDOCODE
CREATE TABLE outbox (
 id UUID PRIMARY KEY,
 aggregate_id TEXT NOT NULL,
 destination TEXT NOT NULL, -- queue or topic
 headers JSONB,
 payload JSONB NOT NULL,
 created_at TIMESTAMPTZ NOT NULL,
 published_at TIMESTAMPTZ,
 attempts INT NOT NULL DEFAULT 0
);
CREATE INDEX outbox_unpublished_idx ON outbox (created_at)
 WHERE published_at IS NULL;
python
# PSEUDOCODE : API
async with db.transaction():
 job = await db.insert_job(...)
 await db.insert_outbox(
 destination="jobs.default",
 payload={"job_id": str(job.id), "type": job.type},
 )
return job # 202 even if broker is down; relay catches up

3. Relay design

python
# PSEUDOCODE : single-leader or SKIP LOCKED workers
async def outbox_relay_tick():
 rows = await db.fetch_unpublished(limit=100, for_update_skip_locked=True)
 for row in rows:
 try:
 await broker.publish(row.destination, row.payload, headers=row.headers)
 await db.mark_published(row.id)
 except TransientBrokerError:
 await db.bump_outbox_attempt(row.id)

Rules:

  • Prefer one leader or safe concurrent pollers with SKIP LOCKED
  • Metric: unpublished age p95
  • Alert if outbox lag exceeds SLO

4. Inbox (consumer dedupe)

sql
CREATE TABLE inbox (
 event_id TEXT PRIMARY KEY,
 processed_at TIMESTAMPTZ NOT NULL,
 consumer TEXT NOT NULL
);
python
# PSEUDOCODE
async def on_message(event):
 if await db.inbox_exists(event["event_id"], consumer="billing"):
 return # already done
 await apply_side_effect(event)
 await db.inbox_insert(event["event_id"], consumer="billing")

Order matters: for some side effects you need insert inbox first with a unique constraint and only then external I/O, or use a unique business key (invoice_id paid).

5. Idempotent handlers

StrategyExample
Natural unique keyUPDATE payments SET status='paid' WHERE id=? AND status='issued'
Inbox tableStore event_id
Job status guardSkip if succeeded
Upsert projectionINSERT … ON CONFLICT UPDATE

Handlers must tolerate replay of the same message.

6. When outbox is optional

Direct publish after insert can be OK if:

  • Job is non-critical, or
  • You have a reconciler that re-publishes pending jobs older than N seconds, and
  • Product accepts brief delay

Still implement idempotent workers.

7. Pseudocode end-to-end

text
Client POST /pay
 → TX: mark invoice issued→paid intent + outbox event invoice.paid.v1
 → 200/202
Relay → Kafka
Notifications consumer → inbox → send email command → RabbitMQ
Email worker → send → mark job succeeded

8. Ops

  • Dashboards: outbox lag, inbox size growth, duplicate suppress rate
  • Redrive unpublished outbox after broker outage
  • Never delete outbox rows before retention policy (audit optional)

See also: 11 Event-driven design · 09 Errors

← All docs