My orders appeared twice in Square pos-sync idempotency claims — QQ F3 (PR #551)

Aoibhinn Dublin Temple Bar 38-yo 14-yr The Brazen Head Gastropub 55-cover Irish gastropub off Bridge Street Beef and Guinness pie + house-aged Irish whiskey + late-night live trad sessions thMenu Pro 19 months + Square POS 7 months. Saturday 21:00 Dublin tourist rush closes Square dashboard 124 orders today yesterday 118 4-5 morning today doesn't add up transaction list 18 orders twice same amount same items same table_id 30-60 seconds apart. Square processing fee 2× per duplicate 18 × £4.50 = £81 fee Square accounting end-of-day reconciliation thMenu vs Square gross mismatched VAT HMRC reporting overcounted. Support email investigate. Engineering 7-day POS sync log cron retries push same order twice 30 minutes pending POS drain Square API timeout 5xx retry UPDATE pos_sync_queue attempts +1 no push idempotency Square API idempotency-key header thMenu wasn't setting first push hung pending retry fresh request Square two separate transactions. 3 wrong theories (1) Square API debounce Square no debounce expects idempotency-key wrong layer; (2) exponential backoff retry-after timing slows occurrence doesn't prevent duplicate risk remains; (3) UNIQUE constraint pos_sync_queue helpful alone insufficient blocks INSERTs doesn't gate retry attempts API side both layers needed Square idempotent payloads. Correct pattern Square API idempotency-key header + UNIQUE constraint + claim-before-push PR #310 + PR #341 + PR #606 CCC F1 cron_idempotency_claims sibling. Forensic cloudflare/src/cron-jobs/pos-sync.ts pending SELECT 50 row push try/catch update attempts pushToSquare no Square idempotency-key Square Idempotency-Key UUID 24h server-side dedup retries same key single transaction. pos_sync_queue UNIQUE (order_id, provider) didn't exist migration 0027 added previous schema id PK + order_id INDEX two parallel cron ticks INSERT same order theoretically duplicate rare practice. Aoibhinn 7-day audit 18 duplicates all cron retry Square API timeout + retry duplicate transaction production-wide 90-day 47 restaurants 312 duplicate Square transactions. PR #551 batch QQ F3 3-layer fix Layer 1 Square API idempotency-key header pushToSquare const idempotencyKey = posSync:order_id:provider stable 24h Square dedup retries single transaction. Layer 2 D1_OPS migration 0027 UNIQUE partial WHERE status IN (pending, failed) success/dead duplicates allowed historical race-condition INSERTs blocked DB layer. Layer 3 claim-before-push PR #310 sibling cron_idempotency_claims claimKey pos-sync:order_id:provider:day INSERT OR IGNORE meta.changes=0 skip daily claim defense-in-depth Square idempotency-key. Production audit + backfill Aoibhinn 18 duplicates Square Refunds API full refunds + processing fees clawed back 47 restaurants 312 duplicates 287 Square refund cleanup window + 25 dispute fast-track Square EU engineering special channel ~£1400 processing fees recovered apology email + 1-month Pro tier credit. Post-deploy 30-day 0 new duplicate Square transactions cron retries correctly idempotency-key + claim-before-push UNIQUE catches race-condition INSERTs DB layer. Behlul Adana Seyhan Ziyapasa Bulvari 43-yo 19-yr Adana Sissi Bey Adana Kebabi 50-cover + Square POS 6-month parallel Saturday evening 6 duplicates $30 fee same fix shipped + 1-month Pro credit + Square refund cleanup. Pattern Cloudflare cron at-least-once delivery external API write (Stripe Square Wise Resend Lightspeed) MUST BOTH idempotency-key header + DB UNIQUE constraint + claim-before-push defense-in-depth one alone insufficient. Sibling sweep pos-sync QQ F3 + daily-ops-digest PR #310 + ingredient-reorder-alert PR #341 + email-drips PR #341 + inventory-predict-notify PR #341 + PR #606 CCC F1 + aff-postback-retry PR #493 + PR #609 CCC-B + apply-loyalty PR #553 QQ-B F2 + scanFor1099Threshold PR #536 LL. Implementation at-least-once cron identify + stable idempotency-key encoding restaurant_id/order_id/resource_id + DB partial UNIQUE WHERE active + claim-before-push cron_idempotency_claims + sync-exception releaseClaim PR #606 CCC F1 + audit log + PR template checkbox + quarterly cron retry audit. PR #551 reference.

Related articles