ERP Sync Lag Causes Overselling on Limited Inventory
A specialty retailer sells 60 high-value SKUs with tight stock, fulfilled from a single warehouse. Adobe Commerce is the storefront and order system; an ERP is the system of record for inventory. A nightly batch pushes ERP on-hand quantities into Commerce via integration. During a weekend promotion, two SKUs oversold: Commerce showed 4 and 3 units available when the warehouse had 1 and 0. The ERP also receives orders back from Commerce only in the same nightly batch. Customer service had to cancel and refund six orders, and two customers posted publicly. The client wants 'real-time inventory' but has an ERP with no event streaming and a limited middleware budget. Orders currently sync to ERP once daily.
1. Explain why the nightly batch architecture produced overselling, distinguishing the two directions of the data flow.
Read the worked answer
Two independent lag problems compound:
- Inbound (ERP→Commerce): on-hand only refreshes nightly, so Commerce's displayed/available quantity is up to ~24h stale. Weekend sales deplete real stock while Commerce still believes the morning's numbers. The 4/3 shown were stale snapshots.
- Outbound (Commerce→ERP): orders return to the ERP only in the same nightly batch, so the ERP doesn't learn about sales until the next run. Even if inbound were fresher, the ERP's own on-hand isn't decremented in near-real-time either, so the next inbound push can still be wrong.
Additionally, Commerce's own inventory decrement on order placement protects against overselling within Commerce, but it can't know about warehouse/ERP truth or other channels that draw the same stock. So the oversell is a synchronization-latency and system-of-record problem, not a storefront bug. The fix must address both directions and the single-warehouse source of truth.
2. Given no ERP event streaming and a limited middleware budget, propose a pragmatic architecture and operational controls to prevent recurrence, and set expectations on 'real-time'.
Read the worked answer
Be honest: true real-time requires ERP-side events or a message bus the client doesn't have. Propose a tiered, affordable approach:
- Increase sync frequency where cheap: move inbound inventory and outbound orders from nightly to near-real-time polling (e.g., every 5–15 min) via scheduled jobs/API calls - often achievable without new middleware. This shrinks the window dramatically.
- Safety buffer: apply a configurable stock buffer on the 60 tight SKUs (e.g., hold back 1–2 units or set a max sellable quantity) so small lags don't oversell. For high-value/low-count SKUs this is high leverage.
- Reserve on order: ensure Commerce decrements and, where feasible, reserve stock at order placement; sync order acknowledgements back faster than full batch.
- Exception handling: alert when Commerce available quantity exceeds ERP on-hand, and a manual reconciliation queue for the tight SKUs during promotions.
- Promotion guardrails: for flash events, consider manual stock caps or pausing the riskiest SKUs.
Set expectations: this delivers 'near-real-time' (minutes) with buffers, not true real-time. If the client later needs true real-time, that's an ERP event/streaming investment - scope it as a separate decision. Document the residual risk and get sign-off.