Duplicate orders and undiagnosable failures in a 60,000-order integration
Litware's three e-commerce channels push about 60,000 sales orders per day into Finance and Operations, peaking at 400 messages per minute. The current integration is a custom X++ SOAP service. When the middleware replays a batch after a timeout, duplicate sales order headers and lines appear, because the channel's order number is stored but never enforced as unique. Failures return a generic 'request failed' message with no record-level detail, and support cannot tell which of the 500 messages in a batch failed. Finance also wants a daily report of rejected messages with reasons. The integration runs under a System administrator service account because, according to the developer, 'it kept hitting permission errors'.
1. Choose the integration pattern and justify it against the volume, latency and error-detail requirements.
Read the worked answer
Match the pattern to each flow rather than keeping one service for everything:
- Bulk order intake (60,000/day, bursty) -> data management framework with a custom data entity, run as a recurring or triggered batch job. It gives staging, entity-level validation, per-record error capture and batch execution without hand-writing a service, and its throughput is much better than one-record-per-call SOAP. The caller gets a job/execution id, not an instant success - which is acceptable for order intake, provided the middleware is told the SLA.
- Real-time single-record changes (status updates, stock queries) -> OData on a purpose-built entity; on-demand, but do not put 60,000 records a day through it.
- Outbound near-real-time notification to the channels -> business events, not polling.
A custom service is justified only where the business logic cannot be expressed as an entity operation. Here it is not. The decision point is that staging gives record-level error detail for free, whereas the SOAP service must implement it, and evidently did not.
2. How should duplicates be prevented so middleware replays are harmless?
Read the worked answer
Make the replay a no-op by design: idempotency belongs in the data model, not in retry logic.
- Create a unique index on the natural key - channel plus channel order number (plus line number on the line table). This is the only guarantee that survives concurrent processes; an existence check before insert is a race.
- Expose an alternate key on the data entity so the import can resolve an existing record and update it instead of inserting. Then choose the import mode deliberately: 'Create' fails on existing keys and is loud, 'Update' ignores unknown keys silently, and upsert semantics need the alternate key plus the right entity mode - the wrong mix is how duplicates and silent drops both happen.
- Keep the original message id on the header so support can trace which replay created which version, and make the middleware treat a duplicate-key rejection as success.
- Generate sales lines with the same key scheme, otherwise a header-level fix still duplicates lines - the symptom in this setup.
Finally, test it: replay the same 500-message batch twice in the sandbox and assert the order and line counts are unchanged.
3. Address the service account, and design how rejected messages reach finance with reasons.
Read the worked answer
The System administrator account is both a security finding and the reason nobody understands the failures: it masks missing privileges and makes row-level filtering invisible.
- Create a dedicated integration user with a custom role containing only the duties/privileges for the entities and tables involved (the sales order entity plus its backing tables and the staging tables). Least privilege turns a permission gap into an immediate, readable error in the sandbox instead of an unexplained 'request failed' under an all-powerful account.
- Verify the role also satisfies any extensible data security policy covering the sales order tables. A policy can silently restrict rows for an integration user, so a batch will look like it 'worked' while records were rejected or never seen.
- Keep the credentials out of code; use a key vault-backed secret and rotate it.
For reporting: the data management staging tables retain errors per execution id with entity, record key and message. Build a data entity over the staging error tables and a report or Power BI view filtered by date and execution id, so finance sees 'order 12345 rejected: customer 0042 does not exist' rather than a batch count. Route the same events to an alert so support reacts the same day rather than the next morning.