Akka ReliableDelivery retries: where should deduplication state live to keep writes unique?

Answer

A database unique constraint on the message ID alone guarantees that no duplicate write can succeed, provided the write and the constraint check happen in the same transaction.

Confirmed facts

• Akka ReliableDelivery provides at‑least‑once delivery; duplicates are expected.
• The producer’s internal sequence numbers (seqNr) live in memory and are lost on a producer restart unless you enable the durable‑queue variant (EventSourcedProducerQueue).
• Consumer‑side tracking of seqNr (via the controller’s confirm flow) only helps with in‑flight deduplication while the producer stays alive; it does not survive producer restarts or rebalancing.
• If the consumer stores processed IDs in its own Akka Persistence state, that works but adds journal load and requires a retention policy.

Recommended steps for this case

1. Generate a stable, application‑level identifier (e.g., UUID v4 or a business key) once when the command is created and include it in every retry of that command.
2. In the consumer actor, attempt the database write inside a transaction that includes a column with a UNIQUE constraint on that identifier.
3. On a constraint‑violation exception, treat the message as already processed, acknowledge it to ReliableDelivery (via ConsumerController.confirm), and do not retry the write.
4. Optionally keep the ReliableDelivery flow control enabled so the consumer can back‑pressure and avoid unnecessary redelivery while the producer is alive.
5. No separate persistent deduplication log is required; the database row itself serves as the record of processed IDs.

Retention guidance

Because the DB row persists until you explicitly delete it, the identifier is retained for as long as the row exists. You may safely purge old IDs only after you are certain the producer will never resend that command – for example, after a configured maximum number of redelivery attempts multiplied by the retry interval, or after a business‑defined “safe window”.

Missing diagnostic detail

Does your write operation execute the uniqueness check and the actual insert/update in a single database transaction? If not, a duplicate could slip through between the check and the write, and you would need to move the constraint into the transaction or use a retry‑on‑conflict loop.