ediverse

Type to search across standards, messages, tools and glossary.

ediverse Explore the platform

Spotlight PEPPOL BIS Billing 3.0 The EU e-invoicing mandate is here — France Sept 2026, Belgium Jan 2026, Germany 2025.

— 01

Foundations

— 02

Standards

— 03

Flagship messages

— 04

Tools

Transactional Outbox (deep dive)

A detailed variant: how to size the table, choose polling vs CDC, order events, guarantee idempotency, and purge without losing history.

Problem

The basic Outbox solves dual-write (DB + broker). In real production, several practical sub-problems emerge: How to order messages? How to size the poller for 10k events/s? How to purge without losing audit? How to avoid a poller retry re-publishing 100 already-emitted messages?

Forces

  • The poller may lag — an unprocessed backlog increases end-to-end latency.
  • Several poller instances in HA can duplicate without locking.
  • Event order matters for some aggregates (OrderConfirmed must precede OrderShipped).
  • The table grows: without purge, it degrades application queries sharing the same DB.
  • A unique message_id is required for broker-side idempotency.

Solution

Detailed Outbox has three distinct roles: (1) the business transaction inserts into outbox(id, aggregate_id, payload, status, created_at, headers); (2) a relay (periodic polling or CDC on WAL) reads PENDING rows and publishes; (3) a purge job archives SENT rows older than N days into cold storage (S3) and deletes them. The relay uses SELECT ... FOR UPDATE SKIP LOCKED (Postgres) for HA. Order by aggregate_id + created_at is guaranteed if the broker is partitioned by that key.

Outbox table schema

CREATE TABLE outbox (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  aggregate_type TEXT NOT NULL,            -- 'Order', 'Invoice', ...
  aggregate_id TEXT NOT NULL,            -- routing key for broker
  event_type   TEXT NOT NULL,            -- 'OrderConfirmed'
  payload      JSONB NOT NULL,
  headers      JSONB,                    -- correlation, source
  status       TEXT NOT NULL DEFAULT 'PENDING'
                          CHECK (status IN ('PENDING','SENT','FAILED')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  sent_at      TIMESTAMPTZ,
  attempts     INT NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMPTZ
);
CREATE INDEX outbox_pending_idx ON outbox (status, next_attempt_at)
  WHERE status = 'PENDING';
CREATE INDEX outbox_agg_idx ON outbox (aggregate_id, created_at);

Polling vs CDC

Two relay families exist:

  • Polling — a job reads SELECT * FROM outbox WHERE status = 'PENDING' AND next_attempt_at <= NOW() FOR UPDATE SKIP LOCKED LIMIT 100 every 1-2 s. Simple, but latency ≥ interval. Used by modest EDI hubs (≤ 100 msg/s).
  • CDC — Debezium or a Kafka connector reads Postgres WAL / MySQL binlog in push. Sub-second latency. Operational complexity grows (replication slot, lag monitoring). Standard at Walmart, Stellantis, Vinted for hubs > 1 000 msg/s.

EDI implementation

Typical EDI hub case: an EDIFACT INVOIC arriving over AS2 is persisted in DB; in the same transaction we insert an outbox record { eventType: 'EdiMessageReceived', payload: { unbRef, sender, recipient, raw: claim_check_url }, headers: { correlationId } }. Debezium publishes to topic edi.inbound.received. A transform consumer translates the INVOIC into canonical JSON; an archive consumer stores it in immutable S3 for 10-year fiscal audit. No risk that the AS2 acknowledgment (MDN) is sent without the internal hub having journaled the message.

Anti-patterns

  • Outbox in the same DB as the application without isolation — a long poller blocks application writes.
  • No FOR UPDATE SKIP LOCKED in HA — two pollers publish the same message.
  • No aggregate_id in broker partitioning — order is lost.
  • Direct purge without archive — fiscal audit is no longer reconstructable.
  • Reusing the outbox id as message_id without an attempt suffix — a SENT message reset to PENDING duplicates broker-side.

Sources