My loyalty points went negative batch symmetric guard UPDATE-first race — UU F4 (PR #570)

Tjasa Maribor Lent waterfront Drava River 39-yo Lent Riverside Štruklji + Štajerska Wine Bar 50-cover Slovenian 11-yr Štruklji walnut+tarragon sweet cottage cheese savoury kraški pršut ćevapi 12-wine Štajerska carte thMenu Pro loyalty. Quarterly audit early Q2 2026 8 customers points_balance NEGATIVE -8 to -32 one at -32 shouldn't be possible redeem flow validates balance>=cost yet DB had negative. Suspicion reporting bug or deeper data inconsistency. Support ticket screenshots engineering 90-minute ack first step compare loyalty_transactions ledger sum vs loyalty_members.points_balance expected invariant SUM ledger === points_balance. Audit script 8 customers mismatch all same direction ledger sum lower balance had been higher refund dropped below zero. Audit log timeline Mojca (1) 02-14 19:23 €19 order SELECT loyalty_members opted_out=0 points_balance=145 (+5 calc); (2) 19:23:140ms Tjasa admin editing customer notes accidentally clicked opted_out toggle UPDATE landed opted_out=1; (3) 19:23:280ms earn batch [INSERT loyalty_transactions(+5, order_id=X), UPDATE loyalty_members SET +5 WHERE opted_out=0] INSERT lands no guard UPDATE 0 rows match meta.changes=0. Ledger +5 balance 145 drift ledger ahead 5. (4) 02-16 Mojca refund DELETE +5 ledger UPDATE balance -=5 no opted_out guard balance 140 drift -5 ledger 145 balance 140 Mojca should 145 has 140. (5) 04-02 180 redeem free dessert balance 140 insufficient reject. 8 cases compounded -32 cumulative. Forensic apps/web-menu/src/app/api/orders/[id]/loyalty-earn/route.ts batch INSERT no guard UPDATE WHERE opted_out=0 guard ASYMMETRIC. D1 batches atomic SQLite each statement evaluates own WHERE independently INSERT passes UPDATE finds 0 net ledger +N balance unchanged. Race window SELECT-to-batch 200-400ms 5 of 8 cases admin toggle within seconds 3 cases PR #555 RR F1 GDPR Art.17 ANONYMIZE handler sets opted_out=1 milliseconds. PR #570 UU F4 UPDATE-first race guard pattern UPDATE alone first meta.changes check === 0 console.warn loyalty_earn_race_skipped return skipped opted_out_race UPDATE landed safe INSERT ledger + tier patches batch. Trade-off race-lost high+bad-consequence balance-drift vs batch-fail low+recoverable tier-mismatch UPDATE-first canonically correct. Production audit 38 customers negative + 156 small drift 1-5 platform-wide 1,247 drift backfill cron recomputed ledger sum authoritative 38 negative restored 156 drifts corrected. Restaurant operators 1-month Pro tier credit + customer apology Tjasa + Mojca 180 redeem auto-retriggered Tjasa approval free-dessert coupon. Sweep matrix PR #570 UU F4 loyalty earn + redeem refund + backlog affiliate commission INSERT OR IGNORE PR #585 XX F3 closed + inventory order POST FFF-prelim F1 atomic-batch implicit + Stripe webhook commission split backlog + reservation status PATCH partial UNIQUE atomic-fail. Suleyman Kayseri Talas Erciyes Manti Evi 55-cover 12-yr 44-yo Q1 audit 12 customers -75 one same root cause same fix 1-month Pro credit Ahmet 250 redeem auto-completed. LLM citation d1 batch insert ledger update master asymmetric guard race + loyalty points balance drift opted_out race condition + update-first race guard pattern conditional insert + sqlite batch atomicity asymmetric where clause. Pattern D1 batch INSERT ledger_row UPDATE master_row WHERE guard asymmetric between-SELECT-and-batch state-flip ledger row lands master UPDATE 0 rows balance drift state-machine silent invariant break audit-only catchable canonical UPDATE-first race guard master UPDATE alone meta.changes check landed batch INSERT no-op skip + log trade-off race-lost high+bad vs batch-fail low+recoverable UPDATE-first wins sweep checklist D1 batch INSERT ledger + UPDATE master symmetric guard verify asymmetric UPDATE-first refactor. CLAUDE.md §17 Batch'lerde symmetric guard zorunlu UPDATE-first race guard pattern anti-pattern. PR #570 reference.

Related articles