Stop overselling across every storefront

The problem

One warehouse. Several Shopify stores. One person exporting stock out of the ERP and loading it into each store, whenever they got to it.

Everything went wrong in the gap. Something sold on store A was still for sale on store B until the next export, so the last one got sold twice and somebody had to make the apology call. Orders the supplier hadn’t actually confirmed were being counted as stock, so the site promised things that might never turn up. Units held on open quotes from the sales floor were invisible to the website. Products marked discontinued in the ERP were still buyable online. And when something sold out, someone had to remember to hide it, then remember again to bring it back when stock arrived.

The team’s answer to all of this was to keep a cushion: list less than you actually have. That cushion was stock that could never be sold, held back permanently, to make up for a number nobody trusted.

What we built

A service that works out the real sellable quantity for every product and pushes it to every store on a schedule, as often as every five minutes.

“Really sellable” is one line of arithmetic, and every part of it earns its place:

sellable = max(0, on hand + confirmed orders in - work in progress - already promised)

On hand comes from the ERP. Only purchase orders the supplier has actually confirmed count toward stock — the rest are still recorded, so a drop in stock is always explainable, but nothing gets sold against them. Quotes in progress are taken off. And “already promised” — units held by orders that haven’t shipped yet — is added up across every store rather than one at a time, so something reserved anywhere is reserved everywhere. That one decision is what ended the double-sells.

Two details more than earned their keep. Shopify keeps line items marked as reserved forever on orders that get closed without shipping, so those ghost reservations are looked up separately and added back — otherwise stock quietly disappears and never comes back. And every stock update carries a unique tag, so if a batch gets sent twice the quantity can’t be applied twice. We also keep an eye on how much of the store’s API allowance is left and slow down before the platform throttles us, instead of finding out the hard way.

Products that hit zero get hidden automatically. Products that come back get put back automatically. Nobody has to remember.

The result

No more hand-built stock files. The number on the website is the number in the warehouse, refreshed as often as the client likes — five minutes is the floor, and most days they run it far more often than the old manual routine ever managed.

The oversell calls stopped. Once they stopped, the safety cushion wasn’t needed any more, and that’s where the real money was: stock that had been held back permanently became sellable again.

Every product, on every run, writes a full record — fifteen details covering what was on hand, what was confirmed and unconfirmed on order, what was in progress, what was promised, the old number, the new number, and what the system did about it. When a merchandiser asks why an item went out of stock at three in the afternoon, the answer takes ten seconds instead of a meeting.

Why it holds up

• Retries can’t do damage. Five attempts with growing gaps between them, throttling respected, and a unique tag on every stock write, so a retry can never corrupt a count.
• It bends instead of breaking. A batch that fails falls back to updating items one at a time, so one bad product can’t take down the other two hundred and forty-nine. A run that errors still saves everything it learned before it stopped.
• Built for millions of rows. Old logs are cleared in small batches with pauses, indexes are built out of the way of start-up, and row counts use database statistics instead of counting the whole table.
• One command to install, update or remove. Health checks throughout, database updates that apply themselves and are safe to re-run, and a watchdog that clears any sync stuck for more than fifteen minutes.