Balance Update Atomicity Automation: Spending Every Last Cent Was the One Case That Broke

A single database statement designed to either insert a new row or update an existing one on conflict is a genuinely elegant, efficient way to express "make sure this record reflects the latest value," and it is entirely correct for the overwhelming majority of writes that use it. Its specific, narrow blind spot is a matter of validation ORDER, not logic: the database has to decide what row it is proposing to write before it can know whether that proposal will collide with something already there and get redirected into an update instead — and any constraint checked at that proposal stage is checked against a row that, in the conflict case, will never actually be the row that ends up stored.

A single "insert, or update on conflict" statement is specifically designed to be safe against a RACE — two callers attempting to create or update the identical row at nearly the same instant — which makes it an attractive, natural choice for exactly the kind of running-balance update a ledger needs. What that pattern does not automatically guarantee is that every constraint on the target table is validated against the value that will actually END UP stored; a constraint checked at the PROPOSAL stage, before the database has resolved whether an insert or an update will actually happen, is validated against a candidate row that may bear little resemblance to the row that is about to be written for real. The gap is invisible for as long as the delta alone happens to satisfy the same constraint the final, combined value would — and becomes visible specifically at the boundary where a raw delta and its correctly-combined final result diverge, such as any debit whose SIZE happens to exactly match the balance it is being applied against.

Centriu Loop's own balance-update mechanism applies every ledger movement — a credit, a debit, an adjustment, an expiration, a chargeback — to a customer's single running-balance row using a database trigger. Before this fix, that trigger issued one combined "insert a new balance row, or update the existing one on conflict" statement per movement, proposing an insert row that carried only the incoming movement's own raw value.

Alongside this same fix, a new database-level constraint was added requiring a customer's available balance to never go negative — closing a separate, real gap where the balance had previously carried no such guarantee at all. The two changes interacted in one specific, previously-untested way: a legitimate debit that would reduce an EXISTING positive balance down to precisely zero proposed an insert row carrying the raw, negative delta on its own — a value the new non-negative-balance constraint correctly rejects in isolation, even though the transaction that would actually execute, once the database resolved the conflict against the existing row, was an UPDATE adding that same negative delta to the existing positive balance and landing at exactly zero, a value the same constraint fully permits. Because PostgreSQL validates a statement's CHECK constraints against the row it is proposing to insert BEFORE resolving whether that proposal will instead become an update, the constraint saw only the doomed, isolated proposal — never the correct, combined value that would genuinely result.

The fix replaces the single combined statement with an explicit two-step sequence: attempt an UPDATE against the existing row first, applying the movement's delta directly to whatever value is already stored; only if no existing row is found for that customer does the logic fall back to an INSERT, seeded with the movement's own values as the customer's very first record. An UPDATE's own CHECK validation is always performed against the row's actual, final resulting values — never against a hypothetical proposed insert that, for an existing customer, will never actually be the row that gets written — which eliminates the specific gap entirely rather than working around it for this one balance's specific shape of transaction. A remaining, narrow race between two truly simultaneous FIRST-EVER movements for the same brand-new customer (where neither has yet created the row for the other to update against) is handled by retrying the loop once the concurrent insert is detected, rather than by reverting to the original combined statement.

The fix was caught, not guessed at: a real PostgreSQL test battery exercising every legitimate transaction type found the failure on its very first attempt at redeeming a customer's entire remaining balance — an ordinary, common, everyday transaction, not an unusual or adversarial one.

Before the fix, redeeming an entire $100 balance down to zero was rejected, because the system checked whether "-$100 on its own" was a valid balance — which it is not — rather than checking what the balance would actually become once combined with the $100 already on record. After the fix, the same redemption correctly checks the ACTUAL resulting balance ($100 minus $100, equal to zero) rather than the standalone delta, and succeeds exactly as it should.

Centriu Loop's balance-update mechanism now applies every ledger movement through an explicit update-then-insert-if-needed sequence, closing a gap where redeeming a customer's entire remaining balance down to exactly zero — an ordinary, common transaction — was wrongly rejected by a non-negative-balance constraint validating an isolated, hypothetical proposed row rather than the transaction's genuine final result.

This automation covers specifically how Centriu Loop's own balance-update mechanism resolves a database conflict correctly under a non-negative-balance constraint. It uses the same general SQL pattern (insert-or-update-on-conflict) this pillar's separate wave 93 Helix rate-limit page and an earlier Axiom quota-counter page both use CORRECTLY, as the FIX for an entirely different problem (a race condition) — this page instead documents a case where that same pattern, combined with a NEW constraint, produced a false rejection, the opposite direction of finding.

A single "insert, or update on conflict" statement is more compact to write and is entirely correct for the many cases where a constraint being validated does not depend on values already present on an existing row. It becomes incorrect specifically when a constraint's outcome genuinely depends on combining an incoming delta with an existing stored value — because the database validates that constraint against the PROPOSED insert alone, before any conflict is resolved. An explicit update-first sequence, falling back to insert only when nothing exists yet, guarantees every constraint check happens against the row's real, final values in every case — the only version of the two that is correct regardless of what a specific constraint happens to depend on.