The situation
A national equipment distributor sold and shipped its machines through its ERP, but the service and warranty side of the business ran in a CRM, where every unit that left the warehouse needed to exist as an asset record tied to the customer that bought it and the serial number stamped on the machine. A nightly job read the day’s shipped orders out of the ERP and wrote the matching assets into the CRM. On day one it looked right: every shipment produced exactly one asset.
The trouble showed up on the second run. The job was scheduled nightly, and it was also the kind of job someone reran by hand after a timeout, unsure whether the first attempt had finished. It inserted a new asset for every order it read, so a repeat pass over the same shipment created a second asset, and a third pass created a third. Within a few weeks the CRM held asset records nobody could reconcile against what had actually shipped, and the service team had stopped trusting the list in front of them.
What we built
Insert-only logic is the default first version of almost any sync, and it holds up right until the job runs twice. Retries make a second run inevitable: networks drop, a batch times out halfway, someone reruns a job unsure whether the first pass finished. None of that is unusual, and insert semantics turn every one of those ordinary events into a duplicate.
We rebuilt the sync around a stable key instead of a stream of inserts. The serial number stamped on each machine was the one identifier that meant the same thing in both the ERP and the CRM and travelled with the record for its whole life, so matching went through that rather than through arrival order. Every write became an upsert: create the asset if nothing matched that serial, update it in place if one already did. Invoices and customer records synced the same way, each on its own stable key, and no code path was left that blindly inserted.
Diagram: an ERP sends shipped orders to a sync engine running inside the CRM. The engine matches records by serial number and upserts them into asset records. Because matching is create-or-update, running the sync again reconciles existing records rather than creating duplicates.
That made the whole run idempotent: running the job once and running it five times left the CRM in the same state. That property is what let the team retry a failed run without cleanup and rerun a full sync on demand to fix drift. We kept a per-run audit trail alongside it, recording what was created, what was updated and what was skipped, so a question about why an asset looked a certain way had an answer on file instead of a guess.
The real cost sat upfront. The serial number had to be present, unique and formatted the same way on both sides, and closing the gaps where it wasn’t took more of the early work than the sync logic itself.
The outcome
Reruns stopped creating duplicates, so a failed nightly job went from a manual cleanup exercise to simply running it again. The service team could trust the asset list against what had actually shipped, and the nightly job became routine enough that the client’s own team now runs and monitors it without us on call.