A consumer applies each 'account balance updated' event using SQL like UPDATE accounts SET balance = balance + :delta WHERE id = :id. Why is this operation NOT naturally idempotent under redelivery, and what change would make it safe to replay?

## Why a relative write is unsafe to replay An increment-style write like `UPDATE accounts SET balance = balance + :delta WHERE id = :id` computes its result **relative to whatever value is currently stored**, which means the operation's effect depends on how many times it has already run. - Applying it once adds delta. - Applying it twice, because the underlying event got redelivered, adds delta twice. - The SQL statement has no memory of whether it has already applied this specific event's delta, so from the database's point of view, two identical UPDATE statements carrying the same delta are indistinguishable from two genuinely different deposits of the same amount. This is the core reason relative or delta-style writes are the classic counterexample when explaining non-idempotent operations, contrasted with an absolute write like `SET status = 'SHIPPED'`, which produces the same end state no matter how many times it runs. ## Why the delta shape is so tempting The problem exists because business events are frequently naturally expressed as deltas — debit fifty dollars, increment inventory by three, add one point — because that's how the domain thinks about the change, and it's tempting to translate that directly into a relative SQL statement. That translation is fine under exactly-once processing, but under **at-least-once delivery**, the near-universal guarantee for message queues, any consumer applying a delta write is exposed to double-application on redelivery, independent of whatever outer idempotency mechanisms exist elsewhere in the system. ## Fix one — an absolute, version-guarded write **Fix one, make the write itself absolute:** instead of shipping 'apply a fifty dollar delta' as the event payload, the producer computes and ships the resulting state, for example 'balance is now four hundred fifty dollars as of event version seven,' and the consumer applies it conditionally: - `UPDATE accounts SET balance = :newBalance, version = :newVersion WHERE id = :id AND version = :expectedVersion` - If the event is redelivered after having already been applied, the WHERE clause's version check fails since the row's version has already moved past the expected value, zero rows are updated, and the redelivery becomes a **safe no-op**. This pattern, sometimes called optimistic concurrency control combined with idempotent replay, pushes the 'have I seen this before' check into the row's own version number rather than a separate dedup table, which is elegant when the entity already has a natural version or sequence field, as event-sourced or CQRS-style aggregates typically do. ## Fix two — a per-event guard table **Fix two, keep delta semantics but gate application behind a per-event idempotency key:** 1. Create a table, say `applied_balance_deltas(account_id, event_id, delta, applied_at)`, with a unique constraint on `(account_id, event_id)`. 2. In one transaction insert into that table and then run the relative UPDATE. 3. If the insert violates the uniqueness constraint, meaning this exact event was already applied to this account, skip the UPDATE entirely. The `event_id` acts as an idempotency token scoped tightly to the specific delta being guarded, which also gives an audit trail of every individual delta ever applied. ## Choosing between them **The trade-off between the two fixes:** | Approach | What it buys | What it costs | |---|---|---| | The absolute/version-based approach | avoids a separate audit table and is cheaper per write, one UPDATE with a WHERE clause instead of an INSERT plus an UPDATE | but it requires the producer to compute and ship the resulting absolute value, which pushes complexity upstream and can be awkward if multiple independent producers legitimately need to apply deltas to the same entity concurrently, since version conflicts then require retry logic | | The per-event-key delta-guard approach | keeps the simpler ship-a-delta event contract and gives a full audit log | but costs an extra table, an extra write per event, and, like any dedup table, needs a retention/cleanup policy so it doesn't grow unbounded | ## What goes wrong in practice **Failure modes in production:** - **A very common one is a team applying fix two's idea halfway**, checking whether an `event_id` has been seen in a separate dedup table but not doing so in the same transaction as the delta UPDATE, reintroducing the check-then-act race under concurrent redelivery. - **Another is silently mixing delta and absolute updates for the same field from different code paths** — one path increments while an unrelated migration script sets an absolute value — which makes reasoning about idempotency impossible because the guarantee from fix one only holds if all writers respect the version check. ## How ledgers sidestep the problem entirely **A concrete real-world scenario:** this is exactly why event-sourced ledger systems, such as many payment ledgers and banking cores, never let a consumer directly increment a stored balance from an event stream. Instead: - each event carries its own immutable identity, - gets appended to an immutable list of postings keyed by event ID, - and the current balance is either the latest computed running total with the version-guard pattern, or, more conservatively, always derived fresh by summing all postings, a design that makes double-application of the same posting event structurally impossible rather than merely guarded against.