How to Prevent Duplicate Orders in an ERP Integration

Duplicate orders are the most common complaint about ecommerce ERP integrations, and the most avoidable. They are almost never caused by a single bug. They come from normal, documented behavior — retried deliveries, network timeouts, concurrent processing — meeting a design with only one line of defense.

Where duplicates come from

1. The same event arrives more than once

Webhook systems deliver at least once, not exactly once. Shopify documents that apps should ignore duplicate deliveries; BigCommerce documents that duplicate callbacks can occasionally occur. A polling job with an overlap window also finds the same order on consecutive runs.

2. A retry after a lost response

The worker sends a create request to the ERP. The ERP creates the order, but the response is lost — a timeout, a dropped connection, a proxy restart. The worker sees a failure and retries. The ERP creates a second order.

This is the most dangerous source because every component behaved “correctly.”

3. Two workers, one order

A webhook and a polling job both enqueue the same order. Two workers pick them up at the same moment, both check the ERP, both see nothing, and both create.

4. Manual re-entry

Someone sees an order “missing” in the ERP (it is still in the queue) and enters it by hand. The integration then delivers it as well.

The layered defense

No single layer is sufficient. Together they make duplicates practically impossible.

Layer 1: Deduplicate events at intake

Store incoming events with a unique key — X-Shopify-Webhook-Id for Shopify, an equivalent delivery ID elsewhere — and ignore inserts that conflict. Then deduplicate jobs by resource: a job for “order 1234” that is already pending does not need a twin.

This removes noise but does not handle sources 2–4.

Layer 2: A unique external reference in the ERP

Every ERP order created by the integration stores the ecommerce order ID in a dedicated field, and the ERP enforces uniqueness on that field (per sales channel or shop, if you have several).

-- In a custom ERP database, for example:
ALTER TABLE sales_orders ADD COLUMN external_ref VARCHAR(100);
CREATE UNIQUE INDEX ux_sales_orders_channel_ref
  ON sales_orders (sales_channel, external_ref)
  WHERE external_ref IS NOT NULL;

With this constraint, sources 2 and 3 produce an error instead of a duplicate. It is the single most important defense, and for a custom ERP it is usually a small change.

If the ERP exposes an API, add a corresponding behavior: a create request with an existing external reference returns the existing order (or a clear conflict response) instead of creating a new one.

Layer 3: Check before create

Before creating, the worker looks the order up by external reference. If it exists, the worker records the link and finishes. This handles the lost-response case gracefully on the retry.

async function ensureErpOrder(order: ChannelOrder): Promise<string> {
  const found = await erp.findOrderByExternalRef(order.channel, order.id);
  if (found) return found.number;

  try {
    const created = await erp.createOrder(mapOrder(order));
    return created.number;
  } catch (err) {
    if (isUniqueViolation(err)) {
      // Another worker or a previous attempt won the race.
      const existing = await erp.findOrderByExternalRef(order.channel, order.id);
      if (existing) return existing.number;
    }
    throw err;
  }
}

Check-before-create alone has a race window (source 3). Layer 2 closes it.

Layer 4: Serialize per order

Make sure only one worker handles a given order at a time: partition the queue by order ID, take an advisory lock, or use a “claim” row with a unique constraint. This reduces contention and makes logs easier to read, even though Layer 2 already prevents the duplicate.

-- PostgreSQL advisory lock keyed by a hash of the order ID
SELECT pg_try_advisory_xact_lock(hashtext('order:' || $1));

Layer 5: Idempotency keys on the ERP API

If your ERP API supports idempotency keys, send one derived deterministically from the order (for example, order-create:shopify:1234567890). A retry with the same key returns the original result. For a custom ERP, implementing this is straightforward — see retries and idempotency.

Layer 6: Detect and report

Run a daily check for ERP orders sharing an external reference or an ecommerce order number, and for ecommerce orders with more than one link. If the layers above work, this report is empty — and an empty report is evidence worth having.

Handling manual entry

Give operations a way to see that an order is queued or failed, so they do not re-enter it. If manual entry is unavoidable, require staff to fill the external reference field; the unique constraint then blocks the later automated create, and the integration links to the manual order instead.

Error handling

• Unique violation on create: look up and link; treat as success.
• Lookup fails (ERP unavailable): retry the whole job later. Never create without a successful lookup.
• Two ERP orders found for one reference: stop, alert, and require human review. Do not choose one automatically.

Testing

• Deliver the same webhook twice; confirm one ERP order.
• Inject a timeout after the ERP commits but before the response; confirm the retry links instead of creating.
• Enqueue the same order twice and run two workers in parallel; confirm one ERP order and one handled unique violation at most.
• Enter an order manually with the external reference, then let the integration process it; confirm it links.

Common mistakes

• Relying on webhook deduplication alone.
• Storing the external reference without a uniqueness constraint.
• Using a non-deterministic idempotency key (a new UUID on every attempt).
• Treating a detected duplicate as an error and retrying it.
• Matching on order number across multiple stores that can share the same numbers.

Summary

Duplicates are the predictable result of at-least-once delivery, lost responses, and concurrency. Deduplicate events at intake, enforce a unique external reference in the ERP, look up before creating, serialize per order, use deterministic idempotency keys where the ERP supports them, and report daily. The unique external reference is the layer that makes the rest safe.