Retail data flows: preventing duplicate orders with idempotency

Start with the business effect to protect

First establish what a duplicate would actually cause: a second ERP order, an additional reservation, a repeated stock movement or another shipping request. A file imported only once can contain two occurrences of the same operation. Conversely, two identical messages can represent two legitimate purchases. Similar data is therefore insufficient grounds for deciding.

Scope one specific journey from its source to its final effect. At each stage, identify the business owner, the authoritative system and the evidence that confirms application. A successful technical response does not always establish that an order is usable: an interface may accept a request before processing it.

• Define the protected operation: creation, amendment, reservation, cancellation or shipment.
• Identify external effects that must also be controlled.
• Distinguish receipt, business validation and actual application in the status model.

Make operation identity part of the data contract

The contract should distinguish the order identifier, the operation identifier and the technical attempt identifier. An order goes through several legitimate operations; an attempt identifier supports troubleshooting. Combining these three levels either allows duplicates through or blocks an ordinary amendment.

The Amazon Builders’ Library describes using a stable request identifier to recognise retries. For your interface, specify its scope: source system, retail brand where applicable, and operation type. State who generates it and how it survives a manual rerun.

Add the fields needed to interpret the request: schema version, business reference, object version and operation payload. Also define what happens when a key arrives with different content: explicit rejection and investigation, rather than a silent correction. A payload fingerprint can support that check, but it does not replace business identity.

Specify this behaviour in the contract and acceptance record

Hypothetical example: a reservation with a lost response

Consider an entirely fictional scenario. A shop sends order C-482 to a reservation service. Operation R-482 reserves two units of product P-17 in warehouse NORTH. The reservation succeeds, but the connection drops before confirmation returns. Monitoring now shows an unknown outcome.

The expected behaviour is agreed before development: retrying R-482 retrieves the reservation and its result; it does not subtract another two units. If the customer subsequently adds one unit to the order, that amendment receives a new operation identity and follows the agreed modification rules.

The acceptance test record should show the order, reservation and expected stock after each step. It should also include two legitimate reservations for the same product and quantity. This checks that the protection does not discard separate operations simply because their payloads look alike.

• Initial state: ten available units and no R-482 reservation.
• First R-482 request for two units: reservation recorded, stock becomes eight; confirmation is lost.
• R-482 retried with the same content: same reservation and outcome, stock remains eight.
• R-482 received for three units: explicit conflict, no additional business write.
• New R-483 operation for one unit: a legitimate second reservation, stock becomes seven.
• Two concurrent R-482 attempts: one reservation; the constraint and transaction prevent two effects.

Place the protection close to the write

Request a review of the transaction boundary: which writes succeed or fail together? In a proposed design, the receiving system keeps a record of applied operations and performs the business write in the same local transaction. The technical documentation should make this boundary visible, especially when several workers can receive an operation simultaneously.

PostgreSQL documents an atomic outcome for INSERT with ON CONFLICT DO UPDATE. That provides a concurrency building block, but an update that subtracts a quantity on every execution still repeats the subtraction. Review the business expression and uniqueness constraints as well as the SQL statement selected.

For a .NET service or an integration job, ask for a demonstration involving two concurrent executions and a deliberately timed interruption. A preliminary existence check alone is insufficient evidence. Also document what happens after failure: waiting, another attempt or an incident requiring attention.

Separate reliable publication from duplicate detection

Microsoft presents the Transactional Outbox pattern as a way to save a business change and its event in one transaction, then publish that event separately. It addresses the risk of recording an order without ever sending its notification. Adopting it also introduces a publication process that needs operation and monitoring.

Azure Service Bus duplicate detection uses message identity within a configured window. It therefore does not replace a business rule that covers an older replay. Explicitly separate publication guarantees, transport filtering and recipient protection in the architecture documentation.

If the ERP exposes only a remote API, a transaction in your database does not include the ERP write. Require remote idempotency support or a suitable reconciliation procedure. When the outcome remains ambiguous and reliable verification is unavailable, provide an investigation state before attempting another creation.

Adapt the rule for stock and catalogue data

For stock, distinguish publishing an absolute quantity from posting a movement. Reapplying a specified quantity and replaying a stock deduction require different logic. Also define how to handle an old value arriving after a newer one. Version information must have a meaning established by the source; receipt time alone is insufficient.

For catalogue data, define identity at the appropriate level: product, variant, channel and language, depending on the model. A descriptive correction should not be confused with deletion followed by recreation. In particular, specify what happens when an old publication arrives after a product has been withdrawn.

These decisions belong in the interface contract, whether execution uses .NET code, a Semarchy xDI/Stambia flow or another tool. They should remain understandable to the team responsible for operating the data flows.

Balance operating costs and avoid blind replays

Set evidence retention according to the replays you actually permit: recent incidents, archive reprocessing or restoration. Longer retention increases storage and administration requirements. Short retention requires another way to recognise historical operations.

For a large replay, prepare an explicit selection of operations, a preview of expected effects and stop conditions. Separate unknown outcomes from established business rejections. Correcting an invalid product reference calls for a different decision from handling a network interruption.

Common mistakes include generating a fresh key for every retry, purging the operation register without considering archives, and treating every duplicate as an error. Provide separate indicators for recognised repeats, payload conflicts, unknown outcomes and the age of pending operations. Interpreting those indicators should lead to a defined operational action.

Acceptance checklist before production

Associate every scenario with an expected business state and evidence that someone can inspect. The job status alone is insufficient. Run these tests with the teams responsible for the source, integration and destination.

• Repeat a completed operation and verify that no additional business effect occurs.
• Send two attempts concurrently and inspect orders, reservations and stock movements.
• Interrupt the response after application, then verify recovery of the result.
• Reuse a key with different content and obtain an explicit diagnostic.
• Process two legitimate operations with identical content without losing either one.
• Receive an older version after an amendment and verify the agreed handling rule.
• Test a historical replay after transport protections have expired.
• Check downstream effects, alerts, reconciliation and stop conditions.

Define a scope that can be verified

To prepare an engagement with Sculpture Binaire, gather the existing contract, an anonymised example of an ambiguous outcome and the list of downstream writes. An initial scope can cover a single order journey. The expected deliverables are then concrete: identity rules, transaction boundaries, a replay procedure and acceptance evidence. That boundary makes it possible to discuss effort and limitations without promising a blanket guarantee across the entire system.

Scope the journey and ownership before estimating the engagement

Implement and test a .NET business interface

Improve order reliability between Mirakl and your ERP