Exactly-once crediting is a unique index, not a queue setting

When money moves on a client event, every failure mode you can think of tries to pay twice. The client retries after a timeout. The server crashes after the write but before the response. A user opens two tabs. A recovery job runs while the live request is still in flight. You cannot make the transport deliver exactly once. What you can do is make at-least-once delivery land on an operation that is safe to repeat.

Derive the key; don't accept it

The familiar pattern is a client-supplied Idempotency-Key header. It is right when the server cannot tell two operations apart on its own. It is wrong for earning money, because a misbehaving client can mint a fresh key per request and opt out of de-duplication entirely.

If the operation has a natural identity, derive the key on the server from fields the client does not control:

key = "adwatch:" + userId + ":" + adId + ":" + istDate

Now a retry must collide with the original, and so must a recovery job: it recomputes the same key from the row's own fields and hits the same constraint as the live path. That is what makes it safe to run reconciliation while traffic is flowing.

The guarantee lives in Postgres

Redis is a fine fast path for “have I seen this?”, but it is not the guarantee. The guarantee is a UNIQUE constraint on the ledger's idempotency key, inserted in the same transaction as the balance update. Either both commit or neither does, and a second insert with the same key fails with SQLSTATE 23505.

That failure is not an error. It is the contracted answer “already credited”, and every caller has to treat it as success.

The bug that made all of this decorative

In one service, six catch blocks looked for SQLSTATE 23505 and returned “already credited”. None of them ever matched. The data-access layer wraps the database error, so the code sits one level down the cause chain, and a replayed credit escaped as a 500. The constraint did its job; the code around it turned a correct refusal into an outage-shaped response, and the caller retried.

The fix is boring: one helper that walks the cause chain, with a depth cap so a self-referencing error cannot loop, used at every site. No catch block reads the error code directly any more.

Spring has the same trap in both directions. The duplicate reaches you as a DataIntegrityViolationException wrapping Hibernate's ConstraintViolationException wrapping the driver's SQLException, so a check on the outer exception's fields finds nothing. And catching DataIntegrityViolationException alone is too broad: a CHECK or foreign-key failure is one too, and must never be reported as “already credited”. Walk to the SQLException, require state 23505 and the idempotency constraint's name, and only then treat it as a replay.

A test that passes on broken code is not a test

The original bug lived in the gap between a mocked error shape and the one the driver actually throws, so the duplicate-fire tests run against real Postgres and Redis. Before claiming they defend anything, I ran them with the helper limited to a depth of one, which is the old behaviour. All six failed. Restored, all six passed. That negative control is the evidence that the tests reach the fix, not just the happy path.

The hardest race got the same treatment. The reconciler checks for an existing credit before writing, so the unique violation is normally unreachable from it. The test opens a second connection, holds a competing insert on the same key in an open transaction, and waits on pg_stat_activity until the reconciler is blocked behind it rather than sleeping and hoping.

Caps are a reservation, not a read

The same thinking applies to limits. A daily cap implemented as read-counter, credit, increment lets every request in a burst read the same stale value. Reproduced locally, a user at 45 of a 50-coin cap firing 50 parallel credits earned 295 coins.

The cap became a reservation: INCRBY first, so exactly one request can observe a value under the cap, and a matching release on every path where the credit does not land, including an idempotent replay. A cold counter is seeded from the ledger with SET NX, because a plain SET from a late seeder would erase reservations other requests had already made.

What I keep coming back to

• Put each guarantee in exactly one place, and make it the durable store.
• Make the duplicate path return the same answer as the first path.
• Derive keys from the operation's identity so recovery jobs collide with live traffic instead of racing it.
• Prove a test reaches the fix by breaking the fix and watching the test fail.