Case study: the ERP sync oversold 400 units

Field notes — a real production bug, project name withheld. Symptom → investigation → fix → lesson.

The symptom

The bestseller went viral on a Thursday. By Friday morning the warehouse was 400 units short: Magento had accepted 400 more orders than physical stock could cover. Customers got confirmation emails, payment was captured, and then came the cancellation wave, the refunds, the one-star reviews quoting "sold me something you did not have." The inventory admin showed a negative salable quantity of minus 380 on the default stock, while the ERP insisted the on-hand figure it sent that morning was correct. Both systems were sure of themselves and they disagreed by exactly the oversell.

The cruel detail was that Multi-Source Inventory was enabled and the reservations table was working. inventory_reservation held a clean ledger of every order placement, each row subtracting exactly what it should. The salable quantity computation was correct. And yet the storefront kept showing the product as in stock all through Thursday evening while the warehouse shelves emptied. The oversell was not a display glitch or a race between two checkouts: the stock number itself was wrong at the source, refreshed nightly from a file that overwrote reality.

The ERP team pointed at Magento. The Magento team pointed at the ERP. The CSV file in the middle, all 40 megabytes of it, had the answer, and nobody had read it line by line.

Investigation

The investigation started with the sync script, a cron job nobody had touched in eighteen months:

0 2 * * * /usr/bin/php /var/www/html/bin/magento erp:stock:sync /var/import/stock.csv

The script read the ERP flat file (SKU plus absolute on-hand quantity per row) and wrote it straight into inventory:

$sourceItem = $this->sourceItemRepository->get('default', $sku);
$sourceItem->setQuantity((float)$row['qty']);
$sourceItem->setStatus((float)$row['qty'] > 0);
$this->sourceItemRepository->save($sourceItem);

Three defects stacked in those five lines. First, the file carried absolute on-hand counts, and the script treated on-hand as the full truth, ignoring the inventory_reservation ledger completely. MSI computes salable quantity as source quantity minus outstanding reservations; overwriting the source quantity each night without reconciling open reservations double-counts every order placed since the last sync. Second, the script ran as a full snapshot: every SKU in the file was rewritten whether it changed or not, so even slow sellers got their source rows touched nightly, resetting any intra-day stock movements the warehouse team entered directly in Magento. Third, there was no delta guard, no negative-stock check, and no alert. A row reading qty: 1200 for a SKU the warehouse knew had 800 units sailed through, because the script trusted the file over the ledger.

Reconstruction of Thursday made the 400-unit gap exact. The ERP exported its file at 18:00 with on-hand 1,000 for the viral SKU. Between 18:00 and the 02:00 sync, the store took 420 orders (reservations correctly recorded, salable quantity correctly declining through the evening, storefront correctly showing diminishing stock). At 02:00 the sync wrote qty: 980 from a stale ERP snapshot that had not yet seen the evening sales, because the ERP batch lagged Magento by design. That single write erased the 420 reservations from the salable computation in one stroke: source 980 minus zero reconciled reservations reads as plenty, when the true position was 980 minus 420 already promised to customers. Friday morning shoppers bought against phantom stock until the warehouse counted shelves at noon. The inventory_reservation table proved it after the fact:

SELECT sku, SUM(quantity) AS reserved
FROM inventory_reservation
WHERE sku = 'VIRAL-SKU' AND stock_id = 1;
-- reserved: -420.0000 against a source qty the sync had just set to 980

Two aggravating factors widened the hole. The product was configured with backorders effectively open (use_config_backorders resolving to allow below-zero salable without notice on the Hyva theme override), so nothing stopped checkout when salable went negative after the sync. And the sync ran in a single database transaction committed at the end, meaning the bad quantity for all 40,000 SKUs landed atomically with no per-row validation, no dry-run mode, and no rollback report. The log line for the night read ERP sync complete: 39812 updated, 0 errors, green to the point of fiction.

The ERP export itself had a subtler flaw worth naming: it counted warehouse on-hand but excluded units already picked and packed for Magento orders that the warehouse system had not yet marked shipped. So even a same-minute file would have overstated available stock by the packing-bench backlog, typically 30-50 units on a busy day. The snapshot architecture could never converge; at best it oscillated around the truth with a lag-shaped error term.

Fix

The fix rebuilt the sync as a reservation-aware delta import, in four steps.

First, stop the overwrite the same day: the cron was switched to a --report-only mode that computed what it would write and emailed the diff instead of saving. The next morning's report flagged 37 SKUs where the file quantity exceeded Magento salable plus open reservations by more than 10 units, including the viral SKU. That report became the permanent pre-commit gate.

Second, rewrite the import to reconcile reservations instead of ignoring them. For each row the script now loads open reservations, computes the expected on-hand, and only writes when the file disagrees beyond tolerance:

$reserved = $this->reservationLedger->getOpenQuantity($sku, $stockId);
$expectedOnHand = (float)$row['qty'];
$currentSource = $this->sourceItemRepository->get('default', $sku)->getQuantity();
if (abs($expectedOnHand - ($currentSource + $reservedPlacedSinceExport)) > $tolerance) {
    // write with audit row, never silently
    $this->stockWriter->setSourceQuantity('default', $sku, $expectedOnHand, $auditRef);
}

Rows that arrive while reservations are open get flagged for daytime review instead of auto-applied. The viral-SKU scenario now resolves as: file says 980, open reservations say 420 promised, effective available 560, storefront decrements honestly through the evening instead of resurrecting stock at 02:00.

Third, close the configuration holes. Backorders were set to deny below zero with an explicit out-of-stock message on the product page, the sync transaction was broken into per-SKU saves with row-level error capture, and any write that would take salable negative now quarantines the SKU (sets stock status false, pages the merchandiser) instead of publishing phantom availability. The --report-only diff email stayed on as the permanent pre-flight: ops reviews the top movers every morning before the applying run.

Fourth, fix the clock skew at the organizational level. The ERP export moved from a lagged 18:00 batch to a 01:30 snapshot with packing-bench states included, shrinking the lag window from eight hours to under one, and the Magento sync moved to 02:00 against that fresher file. Long term the team scoped an MSI SourceDeductionService-driven push API to replace the flat file, but the reconciling importer made the file safe first, which was the correct order: fix the accounting, then fix the transport.

Verification covered two promos after the change: reservation ledger and source quantities reconciled to within single units every morning, zero quarantined SKUs on normal days, and the one genuine supplier shortfall quarantined before a single oversell order could land.

Lesson

An absolute-quantity feed that ignores reservations is an oversell machine with a timer. MSI salable quantity is source minus the reservation ledger, so any importer that writes source quantities without reading open reservations erases every order since the last export in a single stroke. Reconcile before you write, quarantine instead of publishing negative salable, gate the run with a report-only diff, and treat export lag as a first-class input: an eight-hour-old file applied blindly is not a sync, it is a rollback of the evening's sales. The ledger in inventory_reservation is the ground truth of what you already promised; no feed is allowed to outvote it.

This case illustrates the reservation mechanics in /blog/magento2-msi-reservations.