One barcode truth across every system

The problem

Several trading companies, each with its own back-office database. Several storefronts sitting on top of them. One physical catalog underneath all of it — and no agreement anywhere about what a given product’s barcode actually was.

Changing a barcode meant opening every system and editing it by hand, then finding out that history didn’t follow. Quotes, purchase orders, invoices, credit notes, returns and bin locations all kept the old barcode forever. So old invoices pointed at items the catalog could no longer find, and nobody could tie the two together.

Prices were the same story in a different key. A new cost meant re-keying a price ladder into five places, each with its own markup, worked out on a calculator, and getting it identical every time. It was never identical.

And the question the owner most wanted answered — what did we actually sell last month, across all of it — couldn’t be answered at all, because the two halves of the business counted differently and neither knew about the other.

What we built

One screen. Type a barcode, see everywhere it exists, change it once.

Behind that, a search fans out across every connected back office and checks eight tables in each one — the product list plus every kind of paperwork that has ever mentioned that barcode — along with every storefront listing. The stores are checked at the same time rather than one after another, so the wait is however long the slowest system takes, not all of them added up. The change itself is applied in batches, wrapped up so that any error rolls the whole thing back.

The behavior that took longest to get right is the one that matters most day to day. If the new barcode already exists on one storefront, that one store is skipped with the reason written down, and every other system still gets the update. Blocking the whole job because of one clash was the original design, and it was wrong. A partial success, reported honestly, is worth far more than an all-or-nothing failure.

There’s also a clean-up tool for the mess that already exists. It works through the paperwork tables a thousand rows at a time looking for orphans — barcodes that old records point at but the catalog has lost — and repairs them by matching on product ID or description, saving each one as it goes so a single bad record can’t undo the rest. Progress streams onto the screen while it runs.

Prices work the same way: one entry, four tiers, every back office and every storefront, with mirror stores following along automatically and a full before-and-after record for each.

The result

The catalog tells one story. Fixing a barcode takes one action instead of five, and it reaches the old paperwork as well as the current record, which is what makes historic invoices add up again. Price changes are entered once and land everywhere, each at that store’s own markup.

Because the system keeps a local copy of storefront orders and customers alongside the back-office invoices, the reporting screens can finally put both halves of the business on one page, using one consistent rule for what counts as a completed sale — in the shop’s own time zone, which is the detail that quietly ruins most attempts at this.

It’s the longest-running system in our portfolio and it’s still growing. That’s not a boast about volume. It’s what a system looks like when the client keeps finding new things worth asking it.

Why it holds up

• Every change is on the record. Barcode and price updates are grouped under one batch, and each one saves the old value, the new value, where it went, what it touched and — for anything skipped or failed — why. Price history keeps before and after for all four tiers.
• The health check tells the truth. It reports how busy the database connections actually are and reports a failure when they’re maxed out, so an overloaded system reads as unhealthy instead of just hanging. A watchdog restarts it when that happens.
• API limits are learned, not guessed. The storefront connection tracks the allowance the platform reports back, keeps a reserve, and backs off with a bit of randomness so parallel jobs don’t all wake up together and get throttled again.
• Twenty-nine database updates applied in order on every deploy, safe to re-run, with the data preserved — and one command to install, update or remove the whole thing.