Your ERP Integration Will Double-Post. Here's the Idempotency Design.

Every message in your integration will be delivered more than once. Not might — will.

The network times out after the receiver has already committed. A retry fires against an operation that already succeeded. A replay happens during recovery and covers a window that was already processed. Your queue guarantees at-least-once delivery, which is a promise about the lower bound only.

If processing the same order message twice creates two orders in the ERP, your integration is already broken and you have simply not noticed. This post is the design that prevents it.

The Key

Idempotency means processing the same logical event twice produces the same result as processing it once. It requires a stable key, and the key has to come from the business event, not from the transport.

❌ message_id from the queue        — changes on redelivery
❌ uuid generated by the producer   — changes if the producer retries
❌ hash of the payload              — changes if a timestamp is included

✅ {source_system}:{entity_type}:{entity_id}:{event_type}:{version}
   e.g. "shopify:order:1024:created:1"

The property you need: two deliveries of the same business fact produce the same key, and two genuinely different facts never collide. Derive it from the identifiers the source system already owns.

For events that can legitimately repeat — an order updated three times — include a monotonic version or the source's updated_at in the key, so each distinct update is its own operation while each redelivery of that update is a duplicate.

Storage

CREATE TABLE processed_events (
  idempotency_key text        PRIMARY KEY,
  result_ref      text,
  processed_at    timestamptz NOT NULL DEFAULT now()
);

The consumer pattern:

BEGIN;
  INSERT INTO processed_events (idempotency_key, result_ref)
  VALUES ($1, NULL)
  ON CONFLICT (idempotency_key) DO NOTHING;
  -- if 0 rows affected, this is a duplicate: commit and ack
  -- otherwise do the work, update result_ref
COMMIT;

Two details that matter more than they look.

Insert the key in the same transaction as the work. If you write the key first and commit, then crash before doing the work, the event is lost forever — recorded as processed, never actually processed. That failure is much worse than a duplicate because it is silent.

Store result_ref. When a duplicate arrives, callers often need the identifier of the record created the first time. Without it you return success with no reference, and the caller retries in a different way.

Retention: keep keys for at least as long as your longest possible replay window, plus a margin. Deleting after seven days when your dead-letter queue can be replayed after thirty reintroduces the problem you solved.

Ordering Is the Second Trap

Most queues guarantee ordering only within a partition. Across partitions, an order.updated can overtake the order.created it depends on. Your consumer receives an update for a record that does not exist yet.

Two viable approaches.

Partition by entity key. All events for order 1024 land in the same partition and therefore stay ordered relative to each other. Simple and effective. The cost is that a single hot entity can create a partition hotspot, and you lose parallelism within that key.

Make consumers order-tolerant. Carry a version on each event and have the consumer ignore anything older than what it has already applied:

UPDATE orders
   SET status = $2, source_version = $3
 WHERE id = $1
   AND source_version < $3;

Combine this with upsert semantics so an update arriving before its create simply creates the record with the data it has. This is more robust and more work, and it is the right answer when you cannot control partitioning.

Do not assume global ordering because it held during testing. It holds under low load and stops holding exactly when load rises.

The Rest of the Reliability Checklist

Exponential backoff with jitter. Without jitter, all your consumers retry in lockstep and hammer a recovering downstream system at precisely the wrong moment.

Dead-letter queues with a named owner. A DLQ nobody watches is a data-loss mechanism with extra steps. Name the person. Alert on depth, not just on new arrivals.

Circuit breakers. When the ERP is down, stop hammering it. Fail fast, queue locally, and resume when the probe succeeds. Otherwise a downstream outage becomes an upstream outage.

Correlation IDs through every hop. A single business transaction spans several services. Without a correlation ID propagated end to end, debugging means reading five log streams and guessing at timestamps.

Reconciliation. Everything above prevents specific failures. Reconciliation catches the ones you did not anticipate: compare counts and value totals across systems on a schedule, alert on divergence. This is the highest-return item on the list and the one most often cut.

Why Retrofitting Is So Expensive

Adding idempotency to a running integration is not a code change. It is a code change plus a data cleanup.

You have to find the duplicates already created — which requires knowing what "the same event" means retrospectively, without the key you never generated — then merge or void them, then correct everything downstream that consumed them. In a financial context that means journal adjustments and possibly customer-facing corrections.

The design above costs perhaps two days at the start of a project. The retrofit costs weeks and carries reputational risk. This is one of the clearest cases in software where doing it up front is unambiguously cheaper.

The wider architecture — the four integration patterns, canonical data models, master data and identity, security, and cost — is in our guide to ERP integrations.

Frequently Asked Questions

Can we use the queue's message ID as an idempotency key?

No. Message IDs typically change on redelivery, which is precisely the case you are defending against. Derive the key from the business event using identifiers the source system owns — system, entity type, entity ID, event type and version — so two deliveries of the same fact always produce the same key.

How long should we retain idempotency keys?

At least as long as your longest possible replay window plus a margin. If your dead-letter queue can be replayed after thirty days, retaining keys for seven reintroduces the duplicates you prevented. Storage is cheap; make the retention window explicit rather than incidental.

Is partitioning by entity key enough to guarantee ordering?

It guarantees ordering within that key, which is usually what you need, but it introduces hotspots when one entity is unusually active and it costs you parallelism within the key. For estates where you cannot control partitioning, order-tolerant consumers using a version comparison plus upsert semantics are more robust.

What happens if we insert the idempotency key before doing the work?

You create a silent data-loss path: a crash between the key insert and the work leaves the event recorded as processed but never actually processed, and nothing will retry it. Always insert the key in the same transaction as the work so both commit or neither does.

Do we still need reconciliation if the integration is idempotent?

Yes. Idempotency prevents a specific known failure. Reconciliation catches the ones you did not anticipate — mapping drift, silently defaulted values, schema changes on either side. It is the only control that detects failures whose shape you have not predicted, which is why it has the highest return of anything on the reliability list.