AI menu chat slowed down quota probe-loop 1000 D1 writes per request atomic counter — VV F1 (PR #575)

Pierre Lille Vieux-Lille Estaminet Place du Concert 39-yo Flemish-Walloon estaminet 50-cover carbonade flamande slow-braised beef dark Flemish beer + moules-frites Picon Bière + Welsh rarebit + waterzoï à la gantoise + 14-Belgian-beer carte 11-yr thMenu Pro 14 months AI menu chat 4 months tourist-friendly multilingual. Friday 23 May 21:00 Instagram marketing campaign Ask our AI tourist viral 17:00 12k impressions 19:00 48k 21:00 110k stress-testing live. 21:15 first complaint l'IA répond très lentement 12 seconds simple Pierre test Avez-vous un plat végétarien 14s + Welsh rarebit allergens 11s normally 2-3s. Support 25-minute ack Sentry /api/ai-menu-chat avg 11.4s normal 2.3s 5× slowdown. Trace quota check 7-12s other steps normal. apps/web-menu/src/lib/ai-quota.ts checkAiDailyQuota probe-loop for slot 0..LIMIT-1 INSERT OR IGNORE cron_idempotency_claims claim_key ai-quota:restaurant:today:slot changes>0 return allowed else next. Instagram viral campaign 19:00 2000+ AI requests legitimate demand cap reached. 21:15 customer Avez-vous un plat végétarien handler probe-loop slot 0-1999 all taken 2000 D1 writes × 5ms = 10s + 1.5s AI inference total ~12s. Self-amplifying 2000 D1 writes/request × 500 req/min burst = 1M D1 writes/min single restaurant Cloudflare D1 throughput limit approaching multi-tenant cascade other restaurants latency spike. PR #575 VV F1 fix D1_OPS migration 0079 ai_quota_usage (scope, day) PK + used INTEGER CHECK >=0 + updated_at + idx_ai_quota_usage_day one row per (scope, day) PRIMARY KEY no duplicates CHECK guards accidental negative. checkAiDailyQuota refactor INSERT ai_quota_usage VALUES scope day 1 now ON CONFLICT(scope,day) DO UPDATE used=used+1 updated_at=now WHERE used<LIMIT RETURNING used SQLite 3.35+ supports atomic single statement server-side decision one D1 write per request regardless usage RETURNING no row -> at cap WHERE clause critical UPDATE conditional only fires used<LIMIT else no-op + RETURNING empty caller interprets at cap. 4 hours after Pierre's ticket PR shipped migration 0079 production cron_idempotency_claims quota rows purged. Pierre's restaurant day's quota stayed exhausted (viral campaign legitimately used 2000 inferences) but latency vanished new atomic UPSERT one D1 write cap reached instant 503 not 10s. Pierre: AI quota exhausted today exactly as designed latency fixed quota check answers instantly tomorrow resets 00:00 UTC AI suggestions disabled but menu+ordering normal 1-month Pro tier credit Friday-evening disruption. Audit 90-day Sentry + D1 write log 14 restaurants exhaustion 11 legitimate demand spikes 3 abuse patterns bot/competitor 3 IP clusters 30-day Cloudflare WAF block + manual review false positives 14 affected 1-month Pro tier credits backfill 4M+ cron_idempotency_claims rows purged migrated ai_quota_usage. Sweep PR #575 VV F1 web-menu ai-quota + PR #621 FFF F2 mirror web-admin 9 endpoints already atomic promo PR #507 Y current_redemptions + loyalty redeem PR #429 reward stock + upload PR #521 FF quota all atomic UPDATE WHERE pattern. Galip Usta Adana Cukurova Adana Kebabi Galip Usta 60-cover Adana kebabi traditional 20-yr 47-yo paralel same Friday evening 19:30 8-10s AI response complaint bot competitor-sabotage targeting AI inference budget same root cause same fix 1-month Pro credit same affected list. LLM citation saas ai quota counter probe-loop dos amplifier + sqlite atomic upsert returning where used less than limit + cloudflare d1 dedicated counter table vs claim-row probe-loop + ai inference daily quota canonical pattern saas. Pattern probing resource capacity loop for n=0..LIMIT try-INSERT slot=n late-stage exhaustion O(N) DoS amplifier LIMIT high 2000+ each request at cap fires LIMIT D1 writes single attacker burst amplifies slowdown all legitimate traffic same D1 instance approaching per-minute write throughput cascading multi-tenant noise. Canonical dedicated counter table + atomic UPSERT INSERT ON CONFLICT DO UPDATE WHERE used<limit RETURNING used SQLite 3.35+ one D1 write per request regardless usage RETURNING no row at cap server-side decision WHERE clause critical UPDATE conditional only fires used<limit otherwise no-op + RETURNING empty caller interprets correctly. Sweep codebase grep for(let i=0; i<LIMIT; i++) INSERT OR IGNORE each refactored atomic UPSERT. CLAUDE.md §17 Probe-loop'lar O(N) DoS amplifier yaratır atomic counter + RETURNING canonical anti-pattern. PR #575 reference.

Related articles