My ABC analysis told me which sauce to reorder on Sunday — physical inventory count showed 47 kg of sauce missing (cross-DB ledger drift)

Carlos (38), three-location pintxos bar in Bilbao Casco Viejo, gets a "we're out of brava" emergency call Friday despite ABC saying 18 kg/week + ordering 22 kg. Saturday physical count: 88 kg delivered over 4 weeks, 23 kg remaining, actual consumption 65 kg/week. System showed 47 kg/week gap, ~€7,300 phantom inventory over 6 months. Forensic: `apps/web-menu/src/app/api/orders/route.ts:1148-1158` order POST decremented `products.stock_qty` in D1_MENU AND inserted `inventory_movements` in D1_OPS as separate batches. D1 batches are per-database — cross-DB transactions impossible. Ledger INSERT `try/catch` silently swallowed "non-fatal" failures. Carlos's peak-hour traffic lost ~70-100 ledger rows/week to D1_OPS contention. **PR #654 batch VIII F1** fix two-layered: (1) loud `console.error` + structured fields → Logpush alerting; (2) new `inventory-ledger-reconcile` cron at daily 04:30 UTC walks 7-day window, diffs against existing ledger, back-fills missing (order_id, product_id) pairs with `reason='reconcile_backfill'` + `stock_after=NULL`. First tick back-filled 247 rows for Carlos's three restaurants. Pattern: cross-database atomicity lives in the cron layer, not the request layer.

Related articles