790 products missing from the shop: how stock drifts in an accounting sync

A trading company had a working sync with its accounting system. Clean logs, scheduled jobs running, no errors. Nobody suspected anything until we reconciled the catalogue in full.

It turned out that 161 live items were hidden on the site as archived, 353 items showed the wrong stock level, and an entire section of 790 products had not been visible to customers since March. For six months those goods sat in the warehouse and were not sold through the site — simply because nobody could see them there.

Why stock figures drift: the exchange cannot see everything

The sync was incremental: every few minutes it asked the accounting system "what changed after this timestamp" and updated those items. Fast, economical, sensible. And structurally blind.

The catch is that the accounting system only returns active products for that kind of query. Archived ones never appear in the result set at all. Which means:

• an item gets archived — the sync never sees it, and the site keeps showing it as before;
• an item comes back from the archive — also invisible, so the site keeps it hidden;
• an item gets deleted — invisible again, and the product page stays up.

An incremental exchange can do exactly one thing: add and update whatever somebody touched. It cannot notice disappearance. And it never fails with an error — the divergence builds up silently, and the only way to spot it is a full reconciliation.

How you find it

What you need is not a check that "the sync is running" but a full comparison of two lists. Take every product identifier from the accounting system — active, archived and bundles separately — plus the stock report. Compare that with what sits in the website database and look at three things: the archived flag, whether the product page exists, and the stock level.

This is where it is easy to delete too much. In our case the first pass reported "1,687 ghosts" — product pages supposedly missing from the accounting system. In reality those were bundles: they live in a different entity and have to be requested separately. Had we trusted the number and cleaned up the catalogue, we would have wiped out fifteen hundred live items.

The rule is simple: before deleting anything based on a reconciliation, check which source each group of rows comes from. A discrepancy more often means a flaw in the comparison than junk in the data.

What we did

• Kept the fast incremental sync — it is what makes new items and edits appear on the site within minutes.
• Added a nightly full reconciliation that brings archived flags and stock levels across the whole catalogue back in line.
• Items that vanish are marked as archived rather than deleted — otherwise links from old orders break.
• Built a report into the reconciliation itself: how many items were restored, how many hidden, how many had their stock corrected.

After the very first run 790 products came back to the site, and customers stopped seeing items that were not in stock.

How to check your own setup

If you run an online shop connected to an accounting system, answer three questions.

• How many active items are in the accounting system right now, and how many product pages does the site show? The numbers should match.
• What happens on the site when a product is sent to the archive? Test it by hand on a single item.
• When was the last time stock levels were reconciled in full, rather than only for changed products?

If you cannot answer any of them, the divergence is almost certainly already there. It does not announce itself by taking the site down: part of your range simply does not sell, and part of your orders arrive for goods you do not have.

The most expensive failures are not the ones that take the site down, but the ones that run for years while quietly hiding half the range.