Consumer-side dedup: the cheapest path to exactly-once processing

You added a dedup check to your payment consumer — a quick SELECT before charging. Duplicates dropped from dozens per week to zero. Then, three weeks later, a DB connection pool exhaustion caused the check to fail silently and the charge ran twice again. The check was outside the transaction. One line in the wrong place.

The naive dedup pattern and why it fails

The first instinct is a two-step check:

SELECT 1 FROM processed WHERE msg_id = 'msg-7a3f';
-- if found: skip
-- if not found: call Stripe, then INSERT into processed

This fails under concurrent delivery. Two consumers receive the same message simultaneously (possible during a rebalance or after a visibility timeout). Both run the SELECT at the same moment, both see “not found”, both call Stripe. Race condition. Two charges.

The correct pattern: INSERT-first, single transaction

The fix: put the dedup INSERT and the side effect in one atomic DB transaction, and INSERT first:

BEGIN;
  INSERT INTO processed (msg_id, created_at)
  VALUES ('msg-7a3f', now());
  -- on UNIQUE violation: ROLLBACK and skip
  -- if insert succeeded: do the side effect
  UPDATE orders SET status = 'paid' WHERE id = 'O-123';
COMMIT;

On a unique-constraint violation, the transaction rolls back — the side effect never runs. On commit, both the record and the side effect are written together. No crash window between them.

The key property: if the consumer crashes after the DB transaction commits but before it acks the broker, the broker redelivers. The next consumer tries to INSERT msg-7a3f again, hits the unique constraint, rolls back, and acks the broker. The side effect was already done once; the duplicate is silently discarded.

External APIs: the Stripe Idempotency-Key

The transaction trick only works when the side effect is a DB write inside the same transaction. What about external API calls — Stripe, SES, Twilio? You cannot include an HTTP call in a Postgres transaction.

The pattern for external APIs: pass an Idempotency-Key header derived from the message ID.

POST /v1/charges
Idempotency-Key: msg-7a3f

Stripe stores the key and the first response for 24 hours. If you call Stripe again with the same key (because the broker redelivered), Stripe returns the cached response without charging the card again. PayPal, Square, and most payment APIs follow the same convention.

When the external API supports idempotency keys, the pattern is:

1. INSERT a pending row: INSERT INTO stripe_intents (msg_id, status='pending') — in a transaction. This is the intent log.
2. Call the external API with Idempotency-Key = msg_id.
3. On success: UPDATE the row to status='completed', charge_id=....

If the consumer crashes between steps 2 and 3, redelivery re-calls Stripe with the same key (Stripe returns the cached charge_id), then completes the UPDATE. No double charge.