My analytics overcounted by 50% this week — customer_activity retry double-count (PR #639 IV F3)

Alessandro (44) runs Bottega del Forno 90-cover Neapolitan pizzeria 2 locations Spaccanapoli + Vomero 22-strong staff. 5 seasons thMenu Pro+, 18 months heavy Today s Pulse + Menu ABC weekly menu-layout A/B tests. 18 May Monday morning numbers off: last week product_view 14,302 vs prior week (similar Bay-of-Naples tourist flow even with rainy weekend) 9,108 = **+57% jump**. Alessandro checked council pedestrian counter (flat) + Bookings + POS sales (up only 3%). "Numbers don t make sense." emailed thMenu support. Engineering wrong theories busted: (1) tourist surge — flat data. (2) Bot traffic — UA + IP audit normal. (3) D1 inflation cron — INSERT timestamps normal pattern. **(4) Right theory retry double-count**: 11-15 May 90-minute D1 regional partition window (CF status page confirms). 50% 5xx failure rate customer_activity batch POSTs. Client SDK 30sec retry. Each retry server **inserted: 50** but actually 0 rows landed (prior attempt already INSERT ed). Dashboard counts trusted every retry response → inflation. **Worse: client-side localStorage data loss**: Client SDK flushes 50-event batches from localStorage + deletes that many based on server inserted. Retry server inserted: 50 → client deletes 50 events from localStorage. But those 50 events already deleted on prior successful retry; the 50 events deleted this retry are FRESH ones (last 30 seconds not yet sent). Customer offline 8 product_view, network reconnect batch 5xx retry retry success inserted: 8, client deletes 8 fresh events from localStorage. 5min later customer 4 more events, fresh 4 never reach D1 because prior inflated response purged them. **PR #639 batch IV F3** fix single concept: count from meta.changes. `const inserted = Array.isArray(results) ? results.reduce((acc, r) => acc + (r?.meta?.changes ?? 0), 0) : 0;` — D1 batch result includes meta.changes per statement. First attempt 50 statements each affects 1 row → inserted: 50. Retry duplicate state 50 statements each affects 0 rows → inserted: 0. Mixed 30 new + 20 duplicate → inserted: 30. Defense-in-depth received: statements.length observability (client 0/N ratio duplicate-retry detect). 90-day customer_activity timestamps + CF 5xx cross-reference: 5 partition events last 6 months. 18 restaurants platform-wide affected 30-80% inflation. Retro-compute job phantom event count subtracted via adjusted_dashboard_metric. Alessandro dashboard 14,302 → adjusted 8,975. A/B test results reliable. Proactive email 18 affected restaurants 50% Pro tier renewal discount. Alessandro tweet "thMenu watching data integrity carefully — staying." Pattern: **for INSERT OR IGNORE + partial UNIQUE index batch endpoints, response inserted field must be computed from sum of each statement s meta.changes — NOT from count of prepared statements. Retry-safe semantics require it — duplicate retry must return inserted=0, never statements.length.** Implementation checklist: (1) inserted aggregation meta.changes; (2) received field observability; (3) retry-safe semantic test 3x POST same set; (4) client SDK purge logic only inserted > 0 delete; (5) network instability sim test; (6) historical adjustment query; (7) quarterly cross-reference D1 vs analytics provider. Cengiz Bodrum Bitez Beach Lounge + Yalikavak + Turkbuku version with same Aegean tourist-season flow.

Related articles