A fills consumer reads from a partitioned log and writes into a positions table, and the broker guarantees at least once delivery. Give the idempotency key that makes the write effectively exactly once, and state precisely which failures it covers and which it does not.

A fills consumer reads from a partitioned log and writes into a positions table, and the broker guarantees at least once delivery. Give the idempotency key that makes the write effectively exactly once, and state precisely which failures it covers and which it does not.

Approach: Ask what makes two deliveries of the same fill identical, then place the uniqueness at the point where the write commits rather than where the message is read.

Use the producer's own fill identity, (venue, execution_id), as the idempotency key on the write, apply the fill and the position delta in one transaction, and perform the offset commit only after that transaction commits. At least once delivery means the same message is redelivered after a crash between processing and the offset commit, so the second delivery has to be recognised as the same fill. A surrogate key generated by the consumer cannot do that, and neither can (symbol, ts, qty), because two genuine fills can share all three. A unique constraint on (venue, execution_id) turns the redelivery into a conflict the consumer swallows through INSERT ... ON CONFLICT DO NOTHING, and the position delta is derived inside that same transaction so it cannot be applied twice. This covers duplicate delivery and a consumer crash at any point. It does not cover a producer that assigns a fresh execution_id when it resends the same economic fill, a reader of the positions table under an isolation level weak enough to see the transaction partly applied, or any effect that leaves the database such as an outbound order or an email, since no database constraint can retract those. Each of those needs its own dedup keyed on the same identity.

Follow-up: How do you keep the same guarantee when the write target is an object store with no unique constraint?

Key concepts: idempotency key, at least once delivery, unique constraint, offset commit.