Two Thousand Extra Cases
Fabrikam's partner portal sends service requests to a REST endpoint on the case table. Partners submit about 4,000 requests a month; 9,000 cases appear. The integration retries on any timeout, and the payload's short description embeds a client-generated timestamp, so the 'find existing by description' check never matches. Partner IDs arrive in a custom field that is not indexed. Each partner sees their own case list through the portal. The monthly dashboard reports 9,000 cases and 11,200 'case events', and the service director no longer trusts either number. Agents have started merging duplicates by hand, which breaks the partner's link to the case.
1. Design idempotency for this integration.
Read the worked answer
Key on a partner-supplied request identifier, not on content.
- Contract change: every partner POST includes a
request_id(UUID generated once per business request) and the partner account ID. Retries reuse the samerequest_id. - Storage: a custom indexed field on the case, with a unique index on (partner account, request_id).
- Server behaviour: lookup before insert. If a case exists for that key, return HTTP 200 with the existing
sys_idinstead of creating a second one. Partners treat that as success, which stops the retry storm at its source. - Race handling: two concurrent retries can both miss the lookup. Catch the unique-constraint error and re-read, returning the surviving record.
- Backstop: an insert-time duplicate check so a badly behaved partner cannot flood the table.
What not to do: matching on short description, timestamp, or any payload content. Content-based matching fails the moment a client adds a timestamp - which is exactly what happened here - and produces both false negatives and false positives.
2. Why does the dashboard show 9,000 cases and 11,200 case events, and which number should be reported?
Read the worked answer
Two different distortions, one table.
- 9,000 is the case table count and includes retry duplicates. The true request volume is roughly 4,000, matching what the partners submitted.
- 11,200 is almost certainly a fan-out: the report joins case to a child table (case tasks, SLA records, or activity/event rows), so each case contributes multiple rows. A case with two tasks and one SLA appears three times.
Which to report: neither, as defined. Define the metric explicitly - 'partner requests received' (count of cases with a distinct request_id, or count of requests at the integration layer) and 'cases per request'. If you must report from a joined query, use a distinct count of case sys_id and verify it against the partner's submission log.
Sanity check: reconcile against the partner's own monthly submission report and the integration's success response count. If those three do not agree within a small tolerance, the report definition is wrong, not the business.
3. What do you do about the manual merges and the broken partner links?
Read the worked answer
Stop the bleeding, then repair with a map.
- Freeze manual merges immediately and route all duplicates to a controlled cleanup. Every further merge widens the damage while you are still measuring it.
- Build an old → new
sys_idmapping from the merges already performed, using the audit/merge log where available and the unique key where it is not. This mapping is the only way to answer 'where did my case go?' for a partner. - Repoint and communicate: update the partner portal reference to the surviving case, notify partners whose case ID changed, and keep the mapping queryable for the retention period.
- Fix the source - idempotency as designed - before resuming normal volume, otherwise the cleanup queue refills faster than it drains.
- Only then bulk-merge the remaining duplicates, in batches, with a rollback plan per batch.
Treat the merge rate as a temporary KPI: it should trend to zero once the integration stops creating duplicates.