External-ID Upsert vs Custom Idempotency Table for Retry-Safe Writes in Salesforce

Question

ContextWhen Apex callouts or asynchronous jobs fail transiently and are retried, the same logical write can execute multiple times. Two documented patterns prevent duplicate records: marking a field as an External ID and using upsert, or maintaining a custom Idempotency Key object that stores a hash of the request payload with a processed flag.Trade-offsExternal-ID upserts rely on the platform’s native matching logic and require the field to be Unique or Case-Sensitive Unique; they add no extra storage but constrain the deduplication key to a single field on the target object. A custom idempotency table decouples the deduplication key from the business schema, supports composite keys, and works across objects, yet it consumes additional data storage, demands a cleanup strategy, and adds a read-before-write round trip.Open DecisionBoth approaches handle at-least-once delivery from Platform Events or Bulk API retries, but neither is a silver bullet. The choice hinges on whether the team prefers zero-schema-overhead simplicity or cross-object flexibility with operational overhead.Which pattern scales better when the deduplication key must span multiple fields across unrelated objects? How do storage costs and cleanup complexity compare at millions of monthly retries? Does the lack of a native idempotency header in the REST API shift the balance toward one approach?

Tasadduq Burney · Answer

Direct answerWhen the deduplication key must span multiple fields across unrelated objects the custom idempotency table is the only pattern that fits; external-ID upsert is constrained to a single unique field on one target object and cannot express composite or cross-object keys.Likely explanation vs confirmed factsLikely: External-ID upsert relies on Salesforces native matching on one Unique field; a custom key table can hash any combination of fields and target object into a unique key.Confirmed: Salesforce enforces one unique external ID per object; the platform does not support composite external IDs or cross-object matching in a single upsert call.Steps for this caseIf your key is single-field on one object use external-ID upsert it is atomic storage-free and requires no cleanup.If your key must span multiple fields or unrelated objects create a custom idempotency object (e.g. Idempotency__c) with a unique Key__c text field and a Processed__c flag. Hash the full payload including target object type with SHA-256 and store the digest as Key__c.Make Key__c a unique field; on duplicate insert treat the resulting duplicate-value error as 'already processed' rather than a failure. This turns the unique constraint into the actual guard and the read-before-write query into a fast-path optimization.Schedule a batch Apex job to delete Idempotency__c records older than your retention window size the window to exceed your maximum retry horizon including dead-letter replays of Platform Events. Use chunked scope to stay under the 10000 DML-rows-per-transaction governor limit.Monitor data storage via Setup Data Management Storage Usage and adjust the retention window and key-hash algorithm if collision risk is a concern.Missing diagnostic detailTo finalize the retention window size what is your organizations maximum retry horizon e.g. Platform Event retention period dead-letter replay window? This determines whether premature deletion could allow a duplicate write.

External-ID Upsert vs Custom Idempotency Table for Retry-Safe Writes in Salesforce

Context

Trade-offs

Open Decision

1 answer

Direct answer

Likely explanation vs confirmed facts

Steps for this case

Missing diagnostic detail

Your perspective belongs here.

0 question comments