Why a legacy WMS integration is a different problem

A legacy WMS integration rarely begins with a specification. It begins with a database nobody has catalogued, a scheduled task on a server under a desk, and an operations manager who knows that order lines appear in a table called ORDHDR2 because a colleague told them so in 2014. The warehouse system itself is usually not broken. It has run the pick face, the bin logic and the despatch paperwork reliably for years, and the people using it trust it more than they trust anything newer. That trust is the asset you are protecting, and it is the reason a Bespoke / in-house WMS deserves more care than a vendor product with a published endpoint.

The contrast with a packaged integration is sharp. When you connect to a supported platform you inherit a contract: documented fields, a change log, a support route when something is wrong. With an in-house warehouse system there is no contract, only observed behaviour. A column named STATUS may hold a single character whose meanings were agreed verbally. A field named QTY may be the quantity ordered on one table and the quantity remaining on another. Nothing is wrong with this; it is what happens when software grows alongside a business. It simply means the discovery stage has to produce the contract that was never written down.

The second difference is change control. A vendor platform versions its API and tells you when something will be deprecated. An in-house system is changed by whoever is available, often at short notice, to solve an immediate warehouse problem. A new despatch status added on a Tuesday afternoon to handle a courier change is not a release event to the warehouse team, but it can silently stall every fulfilment sync. Any legacy WMS integration therefore has to assume the source will change without notice, and has to fail in a way that is visible on the morning it happens rather than at the month end.

Finding a seam you can read without touching the warehouse

The first technical decision is where the bridge attaches. The guiding principle is that the warehouse system must not become less reliable because of the integration. Pickers cannot wait while a lock clears, and no despatch run should be delayed by a query running against a production table at eleven in the morning. In practice there are four or five realistic seams, and they differ considerably in risk, latency and the amount of cooperation required from whoever still maintains the WMS.

We normally evaluate each option during discovery with the database to hand, timing representative queries against a restored copy rather than guessing. The output is a written decision with the reasoning recorded, because in two years somebody will ask why the integration polls rather than subscribes, and the answer should not be lost.

Whatever seam is chosen, the integration should read a projection, not a raw table. Build a view or a staging query that names things properly: order_reference, line_sku, quantity_picked, despatch_timestamp, consignment_number. The translation from the warehouse's internal vocabulary into those names happens once, in a place you control, and the rest of the bridge is written against a stable shape. When the WMS adds a column or renames one, the repair is confined to a single layer.

  • Timestamp polling on a read replica — The safest default where the WMS runs on SQL Server or similar. Poll a last-modified column every one to five minutes against a replica or a nightly-restored copy for lower-urgency data. It fails only where rows are updated without touching the timestamp, which is worth testing explicitly.
  • An outbox table populated by triggers — A small table that records order reference, event type and timestamp whenever a relevant row changes. It gives near-real-time capture and a clear audit trail, but it means writing to the warehouse database, which requires agreement and a rollback plan.
  • Flat file exports — Many bespoke systems already produce a despatch file for a courier or a stock file for the accounts package. Consuming an existing file is unobtrusive and often well understood by the warehouse team, at the cost of batch latency and fragile parsing if the layout drifts.
  • Change data capture at the database level — Where licensing allows, native CDC gives ordered change events without application changes. It is the cleanest option technically and the one most likely to be blocked by an older edition or a hosting arrangement.
  • Screen or report scraping — Occasionally the only route into a system with no database access. Treat it as a temporary bridge, document it as such, and plan its replacement rather than pretending it is an interface.

Translating state models, not just field names

The hardest part of this work is not moving data, it is reconciling two different views of what an order is. A storefront such as Shopify models an order as a set of line items that are progressively fulfilled, with each fulfilment carrying a tracking number and a notification to the customer. A warehouse system models the same transaction as an allocation against stock, a pick instruction that may be grouped into a wave with forty other orders, a pack confirmation, and a consignment handed to a carrier. These are not the same objects, and a field-by-field mapping will not bridge them.

Partial despatch is where the mismatch becomes expensive. A warehouse routinely splits an order across two pick faces, two days or two carriers without considering it exceptional. The storefront treats each of those as a separate fulfilment event with its own customer email. If the bridge posts a fulfilment for every pack confirmation, a customer ordering six items can receive five despatch notifications in an afternoon. If it waits for the order to be complete, tracking arrives late and the service desk takes the calls. The correct rule is a commercial decision, not a technical one, and it should be agreed and written down before any code is produced.

Carrier data deserves the same attention. A consignment number in the WMS is not always the tracking reference the customer needs. Royal Mail barcodes, multi-parcel consignments and returns labels all have their own identifiers, and a bespoke system often stores whichever one the despatch printer needed at the time. Confirm what the carrier's tracking URL actually expects, and confirm it with a live despatch during acceptance testing rather than a sample from a document.

Finally, map the states that have no counterpart. Quarantine, awaiting goods-in, on hold for credit check, short-picked pending replenishment: each needs a defined treatment. Some become storefront notes, some become held fulfilments, some are deliberately invisible to the customer. Leaving them undefined means the integration will encounter one at two in the morning and make a decision nobody chose.

Deciding who owns the stock number

Most bespoke warehouse systems hold several quantities for a given SKU, and only one of them is the number the storefront should see. There is on-hand, which includes stock in quarantine and in goods-in bins. There is allocated, which is committed to picks not yet despatched. There may be stock in a returns bin awaiting inspection, stock in a damages location, and stock belonging to a trade customer on consignment. Publishing on-hand to a storefront oversells; publishing a conservative figure loses sales on the lines that matter most.

The sellable figure usually has to be derived, and the derivation should live in the integration layer where it can be read and changed, not buried in a stored procedure. Write it as an explicit expression: on-hand in nominated pickable locations, less allocated, less a per-SKU buffer, floored at zero. Then validate it against reality by taking a dozen SKUs across fast and slow movers and reconciling the derived number to a physical count during discovery. Disagreements found at that point are cheap; the same disagreements found after go-live are oversells.

Direction matters too. Stock flows from the warehouse to the storefront, and orders flow back the other way. Resist any temptation to let the storefront adjust warehouse quantities directly. If the storefront is also publishing to a marketplace or a second sales channel, the warehouse remains the single source of on-hand and the integration becomes responsible for distributing a sellable figure to each channel on an agreed cadence, with the cadence chosen against the real movement rate of the catalogue rather than a default.

Proving the bridge before anyone depends on it

A legacy WMS integration cannot be accepted on a demonstration. The failure modes that matter are the ones that appear under a full despatch run: a pick wave of three hundred orders completing within four minutes, a courier file arriving late, a row updated twice in the same second. Acceptance testing should therefore include a shadow run, where the bridge reads live warehouse data and writes nowhere, producing a report of the fulfilments and stock updates it would have made. Operations compare that report against what actually happened. Two weeks of agreement is worth more than any amount of unit testing.

Build a reconciliation report and keep it after go-live. Each day it should state the number of despatches recorded in the WMS, the number of fulfilments created on the storefront, and the exceptions in both directions with reasons. A finance or operations manager should be able to read it in under a minute and know whether yesterday was clean. If the only way to answer that question is to ask a developer to run a query, the integration is not finished.

Plan explicitly for the WMS changing underneath you. Schema checks that run on a schedule and alert when a column disappears, a type changes or an unexpected status code arrives will convert a silent data loss into a Monday morning email. Alerting should name the specific condition rather than reporting a generic failure, because the person reading it at eight in the morning is usually not the person who wrote it.

Handover completes the work. On our projects that means the code, the schema documentation produced during discovery, the agreed state mapping, the runbook for common exceptions, and the reasoning behind the seam that was chosen. The purpose is straightforward: the business that paid for the bridge should be able to hand it to a different supplier, or to its own developer, without starting the archaeology again. If a bespoke warehouse system taught the organisation anything, it is the cost of undocumented software, and there is no reason to repeat it in the integration layer.

Key points

  • Discovery on a bespoke warehouse system has to produce the interface contract that was never written: observed table behaviour, status code meanings and verified timings against a restored copy.
  • The real work is reconciling state models — pick waves, part-despatches and consignments against storefront fulfilments — and the rules for partial despatch notification are a commercial decision made before coding starts.
  • Accept the bridge on a shadow run against live despatch volumes, keep a daily reconciliation report, and alert on schema drift so a change in the WMS surfaces immediately rather than at month end.