2PC · Transactional Outbox

Atomic Postgres + Kafka writes. What ships today, what's next.

The problem

Every service has to atomically update Postgres and publish to Kafka. Naïve dual-writes fail in one direction — the DB says a claim exists but nothing downstream hears about it. The outbox pattern guards against silent event loss after a successful commit.

The pattern

In one Postgres transaction, write the domain row and an outbox row.
A relay process reads unpublished outbox rows and publishes to Kafka.
Once Kafka acks, the relay marks published_at.

Atomicity is local ACID — no distributed coordinator. Tradeoff: at-least-once delivery; correctness gets pushed to consumer-side idempotency.

Status

Capability	Status
Outbox table in 6 services (claims, billing, enrollment, consent, care, document-service)	shipped
Polling relay worker, partial index on unpublished rows	shipped
Atomic `Enqueue(tx, topic, key, payload)` per service	shipped
OTel trace_context on row, propagated via Kafka headers	shipped
Granular outbox spans (tick / fetch / publish_entry)	shipped
Redpanda as broker	shipped
Consumer-side idempotency (dedup)	partial — only `notifications`
Shared `packages/go/outbox`	partial — copy-pasted in 6 services
Partitioning, hash chain, ClickHouse, S3 WORM	proposed

Outbox schema

CREATE TABLE outbox (
  id            UUID PRIMARY KEY,
  topic         TEXT NOT NULL,
  key           TEXT NOT NULL,
  payload       JSONB NOT NULL,
  trace_context JSONB,                -- OTel propagation carrier
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ           -- NULL until relay acks
);
CREATE INDEX outbox_unpublished_idx
  ON outbox (created_at)
  WHERE published_at IS NULL;          -- partial index, the hot path

Happy path

sequenceDiagram
  participant H as HTTP Handler
  participant PG as Postgres
  participant W as Outbox Worker
  participant K as Redpanda
  participant C as Consumer

  H->>PG: BEGIN
  H->>PG: INSERT domain row + outbox row (with trace_context)
  H->>PG: COMMIT
  Note right of PG: Atomic — both land or neither does.
  H-->>H: 200 OK

  loop every tick
    W->>PG: SELECT ... WHERE published_at IS NULL
    PG-->>W: unpublished rows
    W->>K: produce(topic, key, payload) + traceparent header
    K-->>W: ack
    W->>PG: UPDATE published_at = now()
  end

  K->>C: deliver
  Note right of C: Consumer extracts traceparent → same trace.

OTel trace propagation

The Kafka publish happens later, often on a different process. Without the saved carrier on the row, the consumer span would be orphaned. With it, one trace spans seven spans across two processes and a broker — one Loki query pulls it all.

flowchart LR
  req["HTTP /claims
span: claims.submit"] --> enq["Enqueue tx
span: outbox.enqueue"]
  enq --> row[("outbox row
+ trace_context")]
  row --> worker["Worker
tick / fetch / publish spans"]
  worker --> kmsg(["Kafka msg
traceparent header"])
  kmsg --> cons["Consumer span
linked to trace"]

The at-least-once window

If the relay crashes after Kafka acks but before the UPDATE commits, the row gets picked up again on restart and Kafka sees the duplicate. Eliminating this would need XA across Postgres and Kafka — much more operational cost than making consumers idempotent.

sequenceDiagram
  participant W as Worker
  participant PG as Postgres
  participant K as Redpanda

  W->>PG: SELECT WHERE published_at IS NULL
  PG-->>W: row id=42
  W->>K: produce(42)
  K-->>W: ack
  Note right of W: Crash (OOM / SIGKILL)
UPDATE never runs

  W->>PG: (restart) SELECT WHERE published_at IS NULL
  PG-->>W: row id=42 again
  W->>K: produce(42) — DUPLICATE
  K-->>W: ack
  W->>PG: UPDATE published_at

Resolution: consumer dedup in one transaction

sequenceDiagram
  participant K as Kafka
  participant C as Consumer
  participant DB as Consumer DB

  K->>C: poll() → event_id=42
  C->>DB: BEGIN
  C->>DB: INSERT processed_events ON CONFLICT DO NOTHING
  DB-->>C: 1 row
  C->>DB: business logic
  C->>DB: COMMIT
  C->>K: commit offset

  K->>C: poll() → event_id=42 (DUPLICATE)
  C->>DB: BEGIN
  C->>DB: INSERT ... ON CONFLICT DO NOTHING
  DB-->>C: 0 rows — skip
  C->>DB: ROLLBACK
  C->>K: commit offset

Gap. Only notifications does this (gorm_log.go · InsertIdempotent). Other services rely on naturally-idempotent effects (upserts, state transitions) — blast radius is small, but this is the next correctness investment.

Target architecture

What the picture looks like once the gaps close. Green = shipped; dashed = proposed.

flowchart LR
  svc["Domain service"] -->|COMMIT| pg[("Postgres
domain + outbox")]
  pg --> relay["Polling relay"]
  relay --> rp[("Redpanda
source of truth")]
  rp --> biz["Business consumers"]
  rp -.-> ch[("ClickHouse
queryable audit")]
  rp -.-> s3[("S3 Object Lock
WORM backstop")]

  classDef shipped fill:#12151c,stroke:#22c55e,stroke-width:2px;
  classDef proposed fill:#12151c,stroke:#7a8298,stroke-dasharray:4 3;
  class svc,pg,relay,rp,biz shipped;
  class ch,s3 proposed;

Three audit surfaces, all from the same stream:

Outbox proves intent — the service committed the event
Redpanda proves delivery — the event was published
ClickHouse (proposed) proves processing — queryable by auditors