If you've ever tried to sync order data from an e-commerce platform into an ERP like NetSuite, you know the pain. Products have different IDs on each system. Prices change mid-sync. Timestamps conflict. What looked like a simple data bridge turns into a three-month integration project.
This is one of the most common problems in mid-market operations: connecting an ERP to external APIs without creating a mess of duplicate records, missed updates, and manual cleanup.
Here's a framework that actually works.
The Core Problem: State Mismatch
Most ERP sync issues come down to state mismatch. System A thinks an order is confirmed. System B still shows it as pending. Neither system knows the other exists.
The naive fix is polling: every 5 minutes, pull all orders from the external system and push them into the ERP. This creates more problems than it solves:
- You're constantly re-processing records that haven't changed
- Race conditions when two systems update the same record at the same time
- No audit trail when something goes wrong
A Better Pattern: Event-Driven Middleware
Instead of polling, treat every change as an event. When an order ships, that's an event. When inventory drops below threshold, that's an event. Your middleware layer listens for these events and applies them to the ERP in order.
The pattern looks like this:
- Source system fires a webhook when something changes
- Middleware receives the event and validates the payload
- A transformation layer maps external field names to ERP field names
- An idempotency check ensures you're not applying the same event twice
- The ERP API call is made with retry logic
- Confirmation is logged with both the external ID and the ERP record ID
The idempotency check in step 4 is what most implementations skip. Store a hash of the event payload. Before processing, check if that hash has been seen before. If yes, skip it. This alone eliminates most duplicate record issues.
Field Mapping Is Where Most Projects Break
Every ERP has its own field naming. NetSuite uses tranId for transaction ID. Salesforce uses OrderNumber. Your marketplace might use order_ref. A rigid mapping table that assumes one-to-one matches will fail as soon as either system changes.
Build your transformation layer as a config file, not hardcoded logic. Something like:
{
"source_field": "order_ref",
"target_field": "tranId",
"transform": "uppercase"
}
When the source system changes their field name, you update a config entry, not a code deployment.
Handling Failures Without Data Loss
ERP APIs fail. Rate limits get hit. Sessions expire. Your middleware needs to handle this without dropping records.
The solution is a dead-letter queue: when an API call fails after N retries, move the event to a separate queue for manual review rather than silently discarding it. Pair this with alerting so your team knows when records are backing up.
When to Build vs. Buy
If you're connecting two well-known systems (Shopify to NetSuite, Salesforce to HubSpot), there are often off-the-shelf connectors that handle most of this. Use them.
Where custom middleware pays off is in unusual combinations, high-volume edge cases, or when you need the transformation layer to contain business logic specific to your operation.
At Othex Corp, we spend most of our time on exactly this kind of work: building reliable integrations between ERPs, marketplaces, and external APIs for mid-market businesses that have outgrown their off-the-shelf connectors. If you're hitting these problems, othexcorp.com is a good place to start.
Top comments (0)