Transactional Inbox (deep dive)

The consumer-side counterpart of Outbox: record the received message key in an inbox table within the same transaction as its effects, ensuring a retry applies the effect at most once.

Problem

All modern brokers (Kafka, RabbitMQ, NATS, SQS) guarantee at-least-once: a message may be delivered multiple times. If the consumer writes to DB on each reception, a broker retry doubles the write (two invoices, two stock movements). How to guarantee side effects of a message are applied at most once?

Forces

• Brokers rarely offer end-to-end exactly-once; an application-level mechanism is required.
• An in-memory Set doesn't survive restarts and doesn't work multi-instance.
• The inbox write + the business write must be atomic for the guarantee.
• The inbox table grows; a retention (dedup window) is required.
• The message_id must be unique on the producer side (cf. Idempotency Key).

Solution

Before any side effect, the consumer attempts INSERT INTO inbox (message_id, ...) VALUES (...); if INSERT fails on unique constraint, the message has already been processed — just ACK with no action. Otherwise, apply the business effect in the same transaction as the inbox INSERT. On COMMIT: either both apply and journal, or neither. The broker can retry — we are safe.

Inbox table schema

CREATE TABLE inbox (
  message_id   TEXT PRIMARY KEY,         -- producer-side stable id
  source       TEXT NOT NULL,            -- 'order-service', 'as2-partner-x'
  event_type   TEXT NOT NULL,
  received_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  processed_at TIMESTAMPTZ,
  result       TEXT
);
-- Optional GC partition by month to keep deletes cheap.
CREATE INDEX inbox_received_idx ON inbox (received_at);

Processing flow

Broker delivers message (message_id, payload)
        │
        ▼
BEGIN TX
  INSERT INTO inbox (message_id, ...) ── conflict? ──► ROLLBACK + ACK (no-op)
        │ no conflict
  apply business effect (INSERT order, UPDATE stock, ...)
COMMIT  ── success ──► ACK broker
        │ failure
        ▼
   ROLLBACK + NACK (broker retries)

EDI implementation

An AS2 receiver gets two identical MDNs (the partner resent after a timeout). The consumer of the MdnReceived event computes the key { as2_message_id, mdn_disposition_hash } and attempts inbox INSERT. The second pass hits the unique constraint; we ACK silently. Consequence: a single "delivered" status transition on the order, not two. Same for CONTRL/997 received twice after a partner retry — inbox dedupes.

Anti-patterns

• Inbox without retention — table bloats, inserts slow down.
• Inbox not in same transaction as the effect — a crash between the two breaks the guarantee.
• Too-short dedup window — a message replayed after the window is re-applied.
• message_id not stable on producer side — a producer retry produces a different id, no dedup.
• Inbox shared across different services without source prefix — accidental collisions.

Related patterns

• Inbox (architectural) — synthetic version.
• Transactional Outbox (deep dive) — producer-side counterpart.
• Idempotent Receiver — parent EIP pattern.
• Idempotency (receiver) — EDI-side formulation.

Sources

• Microservices.io — Idempotent Consumer pattern (Chris Richardson). microservices.io/patterns/communication-style/idempotent-consumer.html
• Kleppmann M. — Designing Data-Intensive Applications, O'Reilly 2017, ch. 11 "Stream Processing" (section "Fault tolerance").
• Confluent — Exactly Once Semantics in Kafka. confluent.io — Exactly Once Semantics