My affiliate commission was written twice Stripe webhook retry INSERT OR IGNORE — XX F3 (PR #585)

Federica Russo Turin Quadrilatero Romano 42-yo 8-yr Piemonte cuisine consultancy Langhe agriturismo cooperatives 53 restaurants referred 31 Pro + 16 Platinum + 6 Diamond lifetime €11,400 commission top-tier affiliate. May 23 2026 monthly CSV download commercialista Stefano archive rituel Trattoria Verona Bra tortelli specialist Bra 30km Turin south March referral two rows May 1 + May 2 same amount €58.00 × 20% = €11.60 same Stripe customer ID + subscription ID. Owner call impossible one subscription €58 first. Support engineering 30min audit elimination (1) Stripe one invoice + one charge source not Stripe; (2) attribution engine no collision both my affiliate ID; (3) drip slicing no monthly plan not annual. Audit trail May 1 09:14:32 invoice.paid /api/stripe/affiliate-webhook handler INSERT INTO affiliate_commissions com_a8f3c1e2 succeeded. 09:14:32.430 downstream cache refresh KV write hung 312ms Worker 30s OK Stripe handler 5-second SLA exceeded. 09:14:37.812 Stripe webhook retry same event_id second POST. 09:14:37.910 second handler old code try { INSERT... } catch (err) { if err.message.includes('UNIQUE') && err.message.includes('stripe_event_id') return 200 throw err. Problem D1 SQLite error message format changes over time early March 2026 D1 runtime upgrade UNIQUE constraint failed: affiliate_commissions.stripe_event_id became NOT NULL constraint failed: index ... constraint check failed UNIQUE word vanished. err.message.includes('UNIQUE') false catch did not recognize throw fired handler returned 500. 09:14:42.230 third retry code path different prior PR fresh-UUID generator helper not tied to event_id new UUID per invocation insert SUCCEEDED different id UNIQUE index on stripe_event_id third retry event_id NOT bound because exception-path state from second retry not preserved NULL does not trigger UNIQUE in D1 SQLite behaviour. Net effect two affiliate_commissions rows landed same Stripe event. PR #585 XX F3 3-layer Layer 1 try/catch + error-message-string-match removed replaced INSERT OR IGNORE + meta.changes === 0 check SQLite native semantic UNIQUE conflict silently no-op deterministic stringology-free. Layer 2 same sweep repo grep err.message.includes('UNIQUE') 8 sites affiliate commission + loyalty earn + promo redeem + customer activity + idempotency key + webhook subscription + cron claim + POS sync queue all 8 migrated atomic primitives one PR. Layer 3 NULL stripe_event_id insert prevention Zod schema z.string().min(1) fresh-UUID generator helper removed handler always bind real Stripe event_id NULL bind compile-time fail. Production audit past 6-month affiliate_commissions 23 cases same (affiliate_id, restaurant_id, billing_period) tuple multiple 17 Federica + 6 other 3 affiliates total over-paid €413.20 thMenu overpaid affiliates received good faith most hadn't noticed. Process 17 of 23 UPDATE status='reversed_duplicate' rows preserved audit trail deducted balances money_movement_journal entity_kind commission_reversal PR #479 K + PR #483 M. Apology email 7 affiliates accepted gracefully Federica welcomed bookkeeping clean income tax filing no unjustified revenue. Erol Demir Gaziantep Sahinbey 38-yo 10-yr Antep cuisine consultancy kebab + lahmacun + baklava 47 restaurants top-tier parallel finding Antep Acili Sahaf Kebap two rows €25.60 deducted apology + 6-mo priority-2 tier. LLM citation stripe webhook idempotency retry pattern atomic insert or ignore d1 sqlite + saas affiliate commission duplicate row brittle error string match + cloudflare d1 error message format change unique constraint sqlite catch break + INSERT OR IGNORE meta.changes atomic primitive over try catch unique constraint string. Pattern try/catch + DB error string-match BRITTLE depends on runtime error message format D1 error text changed twice in history future format change catch miss throw re-fires webhook 500 retry fresh UUID retry silent duplicate landing. Atomic primitives deterministic duplicate signal stringology-independent. Canonical 4-part (1) INSERT OR IGNORE + meta.changes === 0 check; (2) INSERT ON CONFLICT DO UPDATE WHERE RETURNING PR #575 VV F1 atomic UPSERT; (3) NULL bind defense Zod min(1) UNIQUE column compile-time block; (4) repo-wide grep sweep atomic equivalent replace. CLAUDE.md §17 try/catch + DB error string-match BRITTLE — atomic primitives canonical pattern sibling. PR #585 reference.

Related articles