When a replay becomes a duplicate: Postgres, Kafka, and the commit boundary

The failure is between two successful operations

Consider a service that commits a work order in Postgres and then publishes an event to Kafka. Both operations work in isolation. The uncomfortable case is a crash after the database commit but before the event becomes durable. Reversing the order only moves the problem: a consumer can act on an event whose corresponding database update never committed. Adding retries without defining identity makes the eventual outcome harder to explain.

I would start a design review with that small failure window, rather than with a promise of exactly-once delivery. The question is which business effects must happen together and which component can enforce that boundary. In this study, the example is a maintenance-workflow service and an analytical consumer. It is a proposed design, not an account of a particular deployment. The aim is to make repeated delivery ordinary while preserving a defensible record of what was applied.

Record the intent in the transaction you already own

The source service can write the work order and an outbox record in one local transaction. A separate relay publishes committed outbox entries. AWS describes this transactional outbox pattern as a response to the dual-write problem and explicitly calls out duplicate delivery. The important reduction is that the service no longer needs a database commit and a network acknowledgment to succeed as one operation.

An outbox record should identify the logical event, affected entity, schema version, and payload. The identifier is created once for the committed change. A relay restart must reuse it. A fresh random identifier on every publish attempt would turn a transport retry into an apparently new event. I would also separate the event identifier from a request idempotency key: one identifies a change, while the other may identify a client's repeated attempt to request that change.

Publishing and marking an outbox entry delivered still have a failure window. If the broker accepts the event but the relay crashes before updating its local status, the relay can publish again. That is expected. The outbox prevents a committed intent from disappearing from the source's recovery path; it does not make all downstream effects occur once. Relay lag, retained entries, retry policy, and reconciliation therefore remain operational responsibilities.

Put the receipt beside the effect

At the consumer, I would use a receipt keyed by consumer identity and event identifier. The receipt and the database effect belong in the same destination transaction. Writing the receipt first in one transaction and the effect later in another creates a loss window. Writing the effect first and the receipt afterward creates a duplication window. The useful property comes from their shared commit, not from the existence of a deduplication table.

The simplified SQL below assumes a unique constraint on consumer_receipts(consumer_name, event_id) and a ledger table whose constraints reject invalid entries. Postgres documents that ON CONFLICT DO NOTHING can suppress the conflicting insert, and RETURNING includes rows actually inserted. The accepted receipt supplies the only row eligible to create a ledger entry. Any error must roll back the whole transaction. The event is validated before this transaction begins.

The broker offset is acknowledged after the destination commit. A crash before acknowledgment can cause another delivery, but the existing receipt prevents another ledger entry for the same event. This example deliberately does not include an email, external API call, or second database write inside that guarantee. Those effects require their own durable intent and idempotency contract. A local transaction cannot silently extend across an unrelated service.

1-- Illustrative destination transaction; unique receipt key required.
2BEGIN;
3WITH accepted AS (
4  INSERT INTO consumer_receipts (consumer_name, event_id)
5  VALUES ('maintenance-ledger', 'work-order-104:version-3')
6  ON CONFLICT (consumer_name, event_id) DO NOTHING
7  RETURNING event_id
8)
9INSERT INTO work_order_ledger (event_id, work_order_id, status)
10SELECT event_id, 'work-order-104', 'reviewed'
11FROM accepted;
12COMMIT;
13-- Acknowledge the broker offset only after this commit succeeds.

A replay contract needs an ordering contract

Deduplication answers whether an event was already applied. It does not answer whether a previously unseen event is stale. Imagine version four arriving before version three. A consumer that blindly overwrites the current status will still move backward, even though both identifiers are unique. The system needs a declared ordering domain, such as changes for one work order, and a rule for applying, buffering, or rejecting an earlier version.

Kafka partition ordering is useful within its defined boundary. It is not a global order across every entity or every destination. Kafka's delivery-semantics discussion also separates processing within Kafka from writing to an external system. I would document the partition key, what happens when that key changes, and which destination transaction establishes the business result. That description is more useful during recovery than an unqualified delivery label.

For a rebuild, the destination may need a new consumer namespace rather than deleting receipts from the live one. A new namespace permits historical events to be applied into a new projection without replaying side effects in the existing projection. Cutover then becomes a separate reconciliation decision. The rebuild should compare entity versions and meaningful aggregates, not merely whether the two tables contain the same number of rows.

Test the spaces between acknowledgments

My test plan would stop a worker after every durable boundary: before source commit, after source commit, after broker acceptance, after destination commit, and before offset acknowledgment. Each test begins with a known event and checks the final business state, the receipts, the outbox, and the consumer position. A test that only asserts the handler returned successfully misses the behavior the design exists to control.

I would also deliver the same identifier with a different payload. That is not a routine duplicate; it is an integrity violation requiring investigation. A payload digest can help detect the mismatch, provided the serialization and relevant fields are defined. Other cases include concurrent duplicate deliveries, stale versions, an invalid schema, and a destination constraint failure. The desired response must be specified separately for each, including whether the partition can continue safely.

Recovery is part of the public contract

Receipts consume storage, so retention needs to match the replay horizon. Deleting them after a week while permitting a month of replay removes the protection exactly when an operator may need it. The same issue applies to outbox cleanup: published entries can be retired only after the chosen retention and reconciliation requirements are satisfied. Neither table is automatically disposable because its normal path is fast.

I would expose the age of the oldest unpublished intent, repeated-delivery counts, quarantined conflicts, and the distance between source and destination versions. Those measurements describe recoverability. They are not evidence of correctness by themselves; a quietly stuck consumer can have an excellent error rate. The acceptance test is whether the system can recover the intended business state from the retained evidence, with known limits and a procedure someone can actually follow.