Two-way sync with a legacy ERP: what broke in production, and what fixed it

The setup

OrderOps is a B2B ordering platform I built for wholesale distributors. Sales agents take orders on their phones, the warehouse picks them, finance books them. The catch: the company's ERP stays the source of truth for products, prices, stock, invoices and tax, and it was never designed to talk to anything. One of the companies also runs a legacy point-of-sale back office on an old SQL Server version.

So the platform is mostly a translation layer: a modern app on one side, two legacy databases on the other, and a sync worker in between. Here is what broke along the way, and the patterns that made it boring again.

Lesson 1: map the schema before you write a single row

Neither database came with documentation. Before writing any sync code I wrote discovery scripts that list tables, columns, row counts and sample values, and kept a mapping document with a confidence rating for every field: confirmed, likely or guessed. For the POS I read the vendor's stored procedures, more than 500 of them, because that is where the real business rules live.

That document paid for itself many times. Catalogue prices include VAT while document lines exclude it. The exchange rate that matters sits in the second rate column, not the first. One edition of the ERP cuts text fields at different lengths than the other, so the writer reads the column limits at runtime instead of hard-coding them.

Lesson 2: incremental reads need an overlap

The first version re-read whole tables every few minutes. It worked until the invoice table passed two million lines and every run upserted around 163,000 rows that had not changed.

The fix was to stop asking "what changed?" and start asking "what could have changed?":

1. New documents are found by their auto-increment ID, starting from the highest ID already synced minus an overlap margin, because IDs are not always committed in order.
2. Closed fiscal years are treated as frozen. Their totals are computed once and stored as a baseline; only the open period is recalculated.
3. Where rows can change in place, a per-day checksum over the source rows tells the worker which days to read again.

// Read only what could have changed since the last run
const floor = Math.max(0, lastSyncedId - OVERLAP);
const rows = await erp.query(
  "SELECT * FROM Documents WHERE Id > @floor ORDER BY Id",
  { floor },
);
await upsertInBatches(rows, { concurrency: 4 });

In steady state the invoice sync now writes zero rows. Stock synchronisation went from about 11 seconds to about 2, with zero differences across 65,000 rows when compared with a full recalculation.

Lesson 3: write-back must be safe to retry

Reading is forgiving; writing into someone else's ERP is not. Networks drop, users tap twice, workers restart halfway through a transaction. Each of those used to be a potential duplicate order in the ERP.

Two rules fixed it:

1. Every order carries a marker that is written together with the ERP document, in the same transaction. Before inserting, the writer looks for that marker under an update lock. If it exists, the order was already booked and the writer returns the existing document instead of creating a second one.
2. The legacy POS assigns document numbers with an unlocked MAX + 1, so two writers at the same moment produce the same number. The write therefore runs in a serializable transaction that first takes an exclusive table lock, and the lines go through the vendor's own stored procedure.

await erp.transaction("SERIALIZABLE", async (tx) => {
  const existing = await tx.findDocumentByMarker(order.marker); // UPDLOCK, HOLDLOCK
  if (existing) return existing; // already booked: a retry changes nothing
  return tx.insertDocument(order); // header and lines in one transaction
});

On the phone the same idea applies: the app sends each order with a request ID that is unique per company, so a resend after a lost response is ignored instead of duplicated.

Lesson 4: the sync worker is a product, not a cron job

The ERPs live on office networks and sometimes simply disappear. Early on, a slow query could pile up behind another one, and a single outage produced an alert every minute.

The worker now has:

1. One FIFO queue for every sync job, with a duplicate guard per domain and company, so jobs never overlap.
2. A hang guard per run and a query timeout, so one stuck query cannot block the rest.
3. Backoff while an ERP is unreachable (1, 2, 5, 10 and 15 minutes) and exactly one alert per outage.
4. A status row per domain: when it ran, how many records, how long it took, the last error. Admins can start "sync now" or a full resync from the UI.

Least privilege matters too. For the POS, the platform connects with its own low-privilege login instead of the administrator account the office software uses.

What I would do the same way again

Treat the legacy system as a partner you cannot change, not as a database you own. Write down what you learn about its schema. Make every read incremental and every write idempotent. And make the sync worker observable from day one: the first outage arrives before the first feature request.

I'm turning these patterns into an open-source reference implementation, erp-sync-kit, built against Microsoft's public WideWorldImporters sample database. If you are connecting an ERP that was never meant to be connected, I'd be glad to talk.