Duplicate orders and stock that never matches: why data sync between systems drifts

Integrations drift because message delivery over a network is at best "at least once", and most integrations are not designed around that fact. The same event arrives twice, events arrive in a different order than they were produced, and some never arrive at all. Each symptom has its own fix: an idempotency key per event with a unique constraint enforced by the database, a handler that writes absolute values instead of increments, an outbox table on the sending side, and a reconciliation job that compares both systems on a schedule. Without those four, an integration looks healthy for months and then fails on the busiest day of the month.

There is no such thing as exactly-once delivery

The sender faces an ambiguity it cannot resolve. It sent the request and no response came back. Did the request never arrive, or did it arrive, get processed, and the response was lost on the way home? There is no way to tell from the outside. So the sender does the only sensible thing and retries. You receive the same event a second time.

This is documented behaviour, not a defect. Stripe states plainly that your endpoint might occasionally receive the same event more than once. Shopify says the same, that it minimizes duplicate deliveries but your app might still receive a webhook more than once. The duplicate order row in your database is a property of the protocol, not a sign that your infrastructure is weak. The pattern the industry settled on is straightforward: at-least-once delivery plus idempotent processing adds up to effectively exactly once.

Ordering is not guaranteed either, and that one is less obvious

At least a duplicate record is visible. Out-of-order delivery quietly produces wrong data. Stripe does not guarantee that events are delivered in the order they were generated, and gives its own example: creating a subscription generates customer.subscription.created, invoice.created, invoice.paid and charge.created, and they can reach you in any sequence. On Shopify, a products/update webhook can be delivered before the products/create webhook for the same product.

What that means in practice: if the "stock 40" message produced at 10:00 lands after the "stock 37" message produced at 10:05, you rewind your stock level. Nothing throws an exception, the logs look clean, the number is just wrong. This kind of drift usually gets noticed weeks later, when someone counts the shelf by hand.

Write absolute state, never deltas

Both problems share a one-line antidote. A handler that writes stock = 40 produces the right answer even if it processes the same message twice. A handler that writes stock = stock - 3 corrupts the data on the second delivery. The same applies to balances, loyalty points, counters and status fields: wherever you have the choice, write the final value rather than adjusting the current one.

For ordering, carry the source system's version with the record. Store a source_version (or the update timestamp the source itself generated) and refuse any write whose version is lower than what you already hold. The important detail is which clock you compare against. Use the moment the source produced the event, not the moment the message reached you, because arrival order is precisely the thing you cannot trust.

Idempotency keys, and what actually enforces them

Use the sender's delivery identifier as the key rather than one you generate yourself. Stripe sends an event ID (evt_...), Shopify sends the X-Shopify-Webhook-Id header, GitHub sends X-GitHub-Delivery. When a provider gives you nothing usable, compute a stable hash over the immutable fields of the payload.

How you check that key matters more than which key you picked. The common mistake is a SELECT to ask "have I seen this ID" followed by processing. When two copies arrive at the same moment, both get told no, and both process. The guarantee comes from the database: write the key into a table with a UNIQUE index, do that write inside the same transaction as the business logic, and treat a constraint violation as a quiet success. Push the check down to the storage layer instead of keeping it in application code.

Pick the retention window from the sender's retry policy. Shopify retries a failed call up to eight times over four hours. Stripe retries for up to three days in live mode with exponential backoff. A system that prunes dedup records after an hour will eventually produce duplicates against a sender that retries for three days. One more detail worth building in early: if the webhook triggers a downstream call of your own (a payment, an invoice, a shipping label), forward your event ID as that API's Idempotency-Key. The header is still an IETF draft rather than a published RFC, so check the exact name and semantics per provider instead of assuming.

You have five seconds, and that matters more than it sounds

Shopify allows one second to establish the connection and five seconds for the whole request. Miss that and the delivery is marked failed, which means retried, which means duplicated. Notice the direction of causation: a slow handler is not the victim of duplicate deliveries, it is the cause.

The correct shape is simple. The handler verifies the signature, writes the payload to a queue or a table, and returns 200. The ERP call, the email, the PDF generation all happen in an asynchronous step after that response. Stripe gives the same advice, to return a successful status code before any complex logic that could time out, and to process events through an asynchronous queue. There is a second benefit: when every subscription renews on the first of the month, the resulting spike topples a synchronous endpoint and barely registers on one that just enqueues.

One warning that catches teams off guard. Shopify removes the webhook subscription entirely after repeated failures within a 24-hour period. A bad Friday deploy can show up on Monday morning as "we stopped receiving notifications", and the cause is not your code but a subscription that no longer exists.

The mirror image on the sending side: dual writes

Everything so far was about receiving. There is a symmetric trap when you send. Your code writes the order to the database, then calls the ERP API. If the second step fails, the two systems disagree. Reverse the order and you can emit an event for a transaction that later rolls back. AWS calls this the dual write problem in its cloud design patterns guidance: when a single logical step writes to two systems, a failure in either one leaves inconsistent data behind.

The fix is the transactional outbox. You write the business row and the outgoing event into the same database inside one local transaction. A separate relay process reads the outbox table, publishes the message to a queue, and clears the row. If the transaction rolls back, the outbox row rolls back with it, so no event escapes for work that never happened. Keep AWS's own caveat in view: the relay can still deliver a message more than once, so the consumer has to be idempotent regardless. The outbox solves lost events, not duplicate ones. When you cannot modify the writing code at all, change data capture is the alternative, generating events from the database log instead.

Retry policy is the easiest way to turn a hiccup into an outage

If the ERP is already saturated and timing out, the retries you pile on top increase the load and finish the job. The formulation in Amazon's engineering library is direct: retries can amplify load on a dependency, so exponential backoff needs a cap, and jitter needs to be injected so that clients do not all come back at the same instant. Without jitter, the retry wave simply repeats itself.

The second rule gets broken more often. Do not retry at every layer of the stack. If the HTTP client retries three times, the queue five times, and the business layer above them twice, one failed request becomes thirty. Amazon's practice is to retry at a single point in the stack. Define the end of the road too: after N attempts the message belongs in a dead letter queue, and the depth of that queue belongs on a screen a human looks at. An error that only reaches a log file has not really been reported.

Partial success is the number one cause of silent data loss

Bulk endpoints hide a common trap. You send 200 products, 197 pass, 3 fail validation. The server returns HTTP 200, because the request itself was processed fine, and the failures are listed row by row in the response body. An integration that checks only the status code loses those three products silently, and nobody notices for months.

The rule: on bulk endpoints the status code is not the result. Read the success and failure counts in the body, compare them against the number of records you sent, and treat any mismatch as an error condition.

Not every mismatch is a delivery bug, some are semantic

Some inconsistencies have nothing to do with retries. The two systems simply mean different things by a field with the same name. Is "available stock" the physical count, the count minus reservations, or does it include inbound purchase orders? Does the price include tax, and where does rounding happen? What timezone is that date field in? These differences do not fail loudly. They leak as a small, steady drift.

The remedy is written rather than technical: a field-by-field mapping with exactly one owning system per field. If the ERP is the record of truth for a field, the storefront never writes it and only reads it. Leave ownership ambiguous and the two systems start echoing each other's updates in a loop that never settles. This is the part of an integration project that consumes the most effort and gets documented the least. We covered the wider case for designing contracts up front in our post on the API-first approach.

A reconciliation job is not optional

Even with everything above done correctly, an event will go missing. A server restarts, a subscription gets removed, a deploy window swallows five minutes of traffic. Shopify is unusually candid here, advising that your app should not rely on receiving data from webhooks and that building a reconciliation job to periodically pull anything you missed through the API is common practice.

A workable setup: a nightly job pulls the last seven days from both sides, compares IDs and a few critical fields, lists the differences and backfills what is missing. The one condition is that its output becomes a number. "Three orders diverged yesterday" is a metric, so you can chart it, set a threshold and alert on it. The same information sitting in a log file is worthless, because a day when the job found nothing looks identical to a day when the job never ran. Put both the divergence count and the job's last run time on your monitoring screen; the failure mode of collecting logs nobody reads applies here too.

The first step that fits in this week

Pick your busiest integration and try to answer four questions in one sentence each. One: what is the idempotency key for this integration, and is there a unique index on it? Two: how many times and over how long does the sender retry, and how long do you keep the dedup record? Three: is there a reconciliation job, did it run yesterday, and how many differences did it find? Four: for every shared field, which system owns it?

Whichever question you cannot answer in a sentence is where your next incident comes from. If you are designing a new endpoint, our post on API security covers the authentication and authorization side, and migrating a legacy system incrementally shows how these patterns fit together during a phased move.