Bidirectional ERP ↔ website sync, the pitfalls of real-time connectors

Bidirectional sync between an ERP and a website looks simple, until the first echo loop, the first duplicate, the first negative stock. Here are the pitfalls of real-time connectors: idempotency, conflict resolution, webhooks, reconciliation, and the code to avoid them.

Why bidirectional sync is a distributed-systems problem

A one-way sync (ERP → site) is trivial: a single source of truth, a single flow direction. When in doubt, re-export everything from the ERP and overwrite. Bidirectional sync is a different beast: it introduces the fundamental distributed-systems problem. Two systems can modify the same data at the same moment, with no shared lock, no shared clock, each convinced it's right.

The moment you accept writes from both sides, you inherit three uncomfortable properties for free:

• No global order. ERP events and site events have no shared clock. "Most recent" depends on which clock timestamped it, and clocks drift.
• At-least-once delivery. A network timeout never tells you whether the message went through. The only safe behaviour is to replay, which means receiving duplicates.
• Temporary partitions. The ERP restarts, the site is in maintenance, a queue falls behind. The connector must converge after the outage, not freeze during it.

I built this kind of connector to sync a car rental company's fleet with its booking site: vehicle availability, pricing, bookings, all of it both ways and in near real time. The pitfalls below show up in every bidirectional integration, whatever the ERP.

Takeaway: a bidirectional sync is not "two one-way syncs." It's a distributed system in its own right, with conflicts, duplicates and event ordering: design it as such from day one.

Pitfall #1: the echo loop

The site updates a stock value → sends it to the ERP → the ERP emits a "stock changed" event → sent back to the site → which sends it back to the ERP… the classic infinite loop that saturates both systems within seconds.

The fix: tag the origin of each write and ignore your own echoes. Concretely, you store on the entity the id of the system behind the last write, and you filter out any inbound event that comes back to you.

async function applyRemoteChange(event: SyncEvent) {
  // 1. Ignore events WE generated (anti-echo)
  if (event.origin === SYSTEM_ID) return;

  // 2. Apply while tracking the origin so we don't re-emit to the source
  await db.update(event.entityId, {
    ...event.payload,
    lastSyncOrigin: event.origin,
  });
}

Origin tagging is enough for a two-system exchange. Beyond that (ERP ↔ site ↔ PIM), you need to propagate a propagation trail (the list of systems already crossed) so an event doesn't loop around a triangle. The principle stays the same: an event must never be re-emitted to a system that has already seen it.

Pitfall #2: idempotency and at-least-once delivery

A webhook can be delivered twice: network retry after a timeout, redeployment of the sender, manual replay after an incident. This is a guarantee, not an accident: most event systems promise at-least-once, never exactly-once. Without protection, a "+1 booking" event applied twice creates a duplicate, and a replayed "stock = stock − 1" decrements twice.

The solution: a stable idempotency key per event (generated by the sender, identical on every replay), stored and checked before processing. The retention window must cover the worst realistic replay delay.

async function handleWebhook(event: SyncEvent) {
  // SET NX = set the lock only if it doesn't exist (atomic)
  const firstTime = await redis.set(
    `evt:${event.idempotencyKey}`, '1',
    'NX', 'EX', 86400, // once per 24h window
  );
  if (firstTime === null) {
    return; // already processed → acknowledge silently (HTTP 200)
  }
  await processEvent(event);
}

Two subtleties that make the difference in production:

• Always acknowledge a duplicate with a 2xx. Returning an error on an already-processed duplicate restarts the sender's retry loop.
• Prefer business idempotency where possible. A naturally idempotent operation (stock = 12 rather than stock −= 1) stays correct even if deduplication fails. The idempotency key is a safety net, not an excuse to write non-replayable operations.

Takeaway: every webhook handler must be idempotent. Assume each message will be delivered at least twice, and write operations that stay correct on replay.

Pitfall #3: conflict resolution

Two concurrent edits of the same record. Who wins? You need an explicit strategy, chosen field by field, not a timing coincidence.

Strategy	Principle	Upside	Limit	When to use
Last-write-wins	Most recent timestamp wins	Simple, stateless	Silent data loss; sensitive to clock drift	Low-criticality data (labels, notes)
Source of truth per field	ERP wins on price and stock, site wins on marketing content	No real conflict, deterministic	Requires fine per-field mapping	The most common and most robust case
Optimistic versioning / locking	Write rejected if the version changed in the meantime	No loss; conflict detected, not hidden	Requires application-level replay	Critical, concurrent data (stock)

For stock (critical and highly concurrent data), I use optimistic locking: each entity carries a version number, and a write fails if the version moved between the read and the write. You never lose an update silently: you detect the conflict and replay against fresh data.

async function updateStock(id: string, qty: number, expectedVersion: number) {
  const result = await db.update(
    { id, version: expectedVersion },            // condition (compare-and-swap)
    { stock: qty, version: expectedVersion + 1 }, // mutation
  );
  if (result.matchedCount === 0) {
    // Stale version: another writer slipped in between.
    // Re-read the fresh state and replay the business decision.
    throw new ConflictError(id);
  }
}

The golden rule: the last writer should not automatically win. For a price or a stock value, the "right" value isn't the most recent one; it's the one from the system that owns it. The per-field strategy encodes that ownership once and for all, and eliminates most conflicts before they happen.

Webhooks or polling?

Criterion	Webhooks	Polling
Latency	Real-time (push)	Depends on the interval
Load	Low, event-driven	High, repeated empty requests
Delivery reliability	Must be secured (retries, signature)	Guaranteed by design
Delete detection	Hard (event must be emitted)	Easy (full-state diff)
Event ordering	Not guaranteed	Controlled (consistent snapshot)
Coupling	Sender must know the target URL	Consumer pulls when it wants

In practice it's not an exclusive choice: webhooks for responsiveness, backed by reconciliation polling at a regular interval that catches lost events and detects divergence. Webhooks give you freshness; polling gives you the convergence guarantee.

Periodic reconciliation, your safety net

No real-time connector is 100% reliable. Lost webhook, handler crashed before commit, network outage during a partition: sooner or later the two states diverge without anyone knowing. Periodic reconciliation is the mechanism that guarantees convergence: at a regular interval, you compare both states and repair the gaps per the conflict strategy.

// Safety net: periodically, compare and repair.
async function reconcile() {
  const [siteState, erpState] = await Promise.all([
    fetchSiteSnapshot(),
    fetchErpSnapshot(),
  ]);

  for (const diff of computeDiffs(siteState, erpState)) {
    await resolveByStrategy(diff); // replay per the conflict strategy
    await audit.log('reconcile.repair', diff); // every gap is traced
  }
}

Two settings matter: the frequency (hourly for stock, nightly for a stable catalog) and the scope (a full diff is expensive; an incremental diff on updatedAt is enough most of the time, complemented by a weekly full diff). A connector without reconciliation always ends up diverging; the only question is how long before it shows.

Retries and exponential backoff

When a remote write fails (5xx, timeout, rate limit), retrying immediately only worsens an outage that's already underway. You retry with exponential backoff plus jitter (randomness) so that not all clients retry at the same instant and create a synchronized spike.

async function withRetry<T>(fn: () => Promise<T>, max = 5): Promise<T> {
  for (let attempt = 0; ; attempt++) {
    try {
      return await fn();
    } catch (err) {
      if (attempt >= max || !isRetryable(err)) throw err;
      const base = Math.min(1000 * 2 ** attempt, 30_000); // capped at 30s
      const jitter = Math.random() * base;                // avoids the thundering herd
      await sleep(base / 2 + jitter);
    }
  }
}

Three guardrails: only retry transient errors (a 400 will never become a 200), cap both the delay and the number of attempts, and send permanently failed events to a dead-letter queue for manual handling rather than losing them.

Handling deletes: the tombstone trap

Deletes are the Achilles' heel of bidirectional sync. When a record disappears, its absence triggers no "changed" event, and a naive diff can read "missing on one side" as "needs recreating", endlessly resurrecting what you're trying to delete.

The fix is the tombstone: instead of physically removing the row, you mark it deleted (deletedAt) and propagate that state like any other change. The tombstone lives long enough for both systems to have seen it, then gets purged.

async function applyDeletion(event: SyncEvent) {
  if (event.origin === SYSTEM_ID) return; // anti-echo, even on deletes

  // Soft delete: keep a trace to propagate and avoid resurrection
  await db.update(event.entityId, {
    deletedAt: event.occurredAt,
    lastSyncOrigin: event.origin,
  });
}

Without tombstones, reconciliation and deletion go to war: one erases, the other recreates. It's one of the most baffling bugs to diagnose in production.

Event ordering

Webhooks don't arrive in emission order: retries, parallelism, multiple queues. If "stock = 0" arrives before "stock = 3" even though it was emitted afterwards, you publish wrong availability. The defense: timestamp at the source (occurredAt) and reject any event older than the last applied write.

async function applyOrdered(event: SyncEvent) {
  const current = await db.get(event.entityId);
  // Only apply if the event is strictly newer
  if (current && event.occurredAt <= current.updatedAt) {
    return; // late event → ignored, current state is fresher
  }
  await db.update(event.entityId, { ...event.payload, updatedAt: event.occurredAt });
}

Data mapping and observability

Two quiet but decisive building blocks for a connector's lifespan:

• Data mapping. ERP and site never speak the same language: identifiers, units, enums ("DISPO" vs available), time zones, currencies. Centralize the transformation in an explicit, tested mapping layer, never scattered across handlers. Validate the shape at the boundary (a Zod-style schema): a malformed payload must be rejected before it reaches the database, not after.
• Observability and audit. Every state transition must be traceable: event received, idempotency key, decision (applied / ignored / conflict), outcome. Without that log, a "why is this vehicle unavailable?" turns into a multi-hour investigation. With it, it's a single query. Also expose metrics: duplicate rate, conflicts per hour, gaps detected during reconciliation, dead-letter queue depth.

The reliable bidirectional connector checklist

• Origin tagging (and a propagation trail beyond two systems) to break echo loops
• Stable idempotency key on every event, with 2xx acknowledgement of duplicates
• Business-idempotent operations where possible (absolute value rather than delta)
• Explicit conflict strategy per field (source of truth, optimistic locking on critical data)
• Ordering guard via source timestamp (occurredAt)
• Deletes handled with tombstones, never immediate physical delete
• Retries with exponential backoff and jitter, capped, dead-letter queue at the end of the chain
• Periodic reconciliation as a convergence guarantee
• Tested mapping layer and schema validation at the boundary
• Audit log and metrics on every state transition

Learn more

The full implementation and business context (a car rental company's fleet synced with its booking site) are described in the case study: Automated bidirectional synchronization connector.

An ERP, PIM or CRM to connect to your site without breaking everything? Let's talk about your integration.