Idempotency in ERP Integrations: Keys, Deduplication, and Upsert Patterns

TL;DR

• Bottom line: Every ERP integration that creates or updates records must be idempotent — use upsert with external IDs as the default pattern, add idempotency keys for non-upsertable operations, and implement an idempotent consumer for event-driven flows.
• Key limit: Salesforce allows max 7 External ID fields per object; NetSuite external IDs are case-insensitive; Dynamics 365 alternate keys support max 5 fields per entity.
• Watch out for: Bulk APIs (Salesforce Bulk API 2.0, NetSuite CSV Import) do NOT support idempotency key headers natively — you must deduplicate before submission or use external ID upsert mode.
• Best for: Any integration that retries on failure, runs on a schedule, or processes messages from an at-least-once delivery queue (Kafka, SQS, Platform Events).
• Authentication: Not applicable — this is a cross-system architecture pattern. See individual system cards for auth details.

System Profile

This is a cross-system architecture pattern card covering idempotency implementation across the four major ERP platforms. Each ERP provides different native mechanisms for achieving idempotency, ranging from built-in upsert operations (Salesforce, NetSuite) to middleware-based duplicate detection (SAP) to alternate key upserts (Dynamics 365).

API Surfaces & Capabilities

Rate Limits & Quotas

Idempotency-Specific Limits

Key Generation Overhead

Authentication

Not applicable to this architecture pattern card. See individual system cards:

• Salesforce OAuth Authentication
• SAP Authentication Methods
• NetSuite TBA vs OAuth 2.0
• Dynamics 365 Authentication

Constraints

• Salesforce External ID limit: Max 7 External ID fields per sObject. Use a formula field concatenating multiple fields as a single External ID if you need more. [src4]
• NetSuite case insensitivity: External IDs are NOT case-sensitive. ORD-001 and ord-001 collide with a 400 error. Normalize before assignment. [src7]
• SAP OData statelessness: Standard SAP S/4HANA OData services have no built-in idempotency. You MUST use SAP Integration Suite's Idempotent Process Call or implement your own dedup store. [src8]
• Dynamics 365 alternate key limit: Max 5 fields per alternate key definition. Complex composite keys must use custom duplicate detection rules.
• Bulk API gap: Salesforce Bulk API 2.0 and NetSuite CSV Import do not accept idempotency key headers. Use externalIdFieldName parameter on Bulk API upsert jobs. [src4]
• OData batch limitation: OData Repeatable Requests cannot be applied to batch requests — only to individual requests within change sets. [src3]
• ACID requirement: Idempotency check and business logic MUST run in the same database transaction. Separate transactions risk recording the key without executing the operation. [src2]

Integration Pattern Decision Tree

START — User needs idempotent ERP integration
├── What type of operation?
│   ├── Create or Update records (most common)
│   │   ├── Does the target ERP support upsert with external ID?
│   │   │   ├── YES (Salesforce, NetSuite, Dynamics 365)
│   │   │   │   ├── Stable business key? → Native upsert with business key as External ID
│   │   │   │   └── No stable key? → Generate deterministic key (hash) or use source system ID
│   │   │   └── NO (SAP S/4HANA OData, custom APIs)
│   │   │       ├── Using SAP Integration Suite? → Enable Idempotent Process Call
│   │   │       ├── OData supports Repeatable Requests? → Use Repeatability-Request-ID
│   │   │       └── Neither? → Implement middleware dedup store (Redis/DB + key header)
│   ├── Event-driven / message consumer
│   │   └── At-least-once broker? → Idempotent Consumer pattern (track message IDs in DB)
│   └── Delete operations → Naturally idempotent (deleting already-deleted = no-op)
├── What key generation strategy?
│   ├── Natural business key → Use it (order_id, invoice_number)
│   ├── Source system ID → Use source_system:record_id format
│   ├── No stable key → Generate UUIDv7 at origin
│   └── CDC pipeline → Derive from transaction log position (LSN)
└── What TTL for idempotency keys?
    ├── Real-time sync → 24 hours
    ├── Daily batch → 7 days
    ├── Monthly close → 35 days
    └── Data migration → Duration + buffer

Quick Reference

Step-by-Step Integration Guide

1. Choose your idempotency strategy based on the target ERP

Evaluate whether the target ERP supports native upsert — if yes, use upsert with an External ID as your primary pattern. [src4, src7]

Decision matrix:
Target supports upsert?  → Use native upsert + External ID (90% of cases)
POST-only API?           → Add Idempotency-Key header
OData with Repeatable?   → Use Repeatability-Request-ID
Event consumer?          → Idempotent Consumer pattern

Verify: Check target ERP API docs for "upsert", "external ID", or "alternate key" support.

2. Design your external ID / idempotency key

The key must be deterministic, unique, and stable. Prefer natural business keys over synthetic ones. [src1, src5]

Good keys:
  order_id: "ORD-2026-00042"                    # Natural business key
  source_ref: "shopify:order:9876543210"         # Source system + type + ID
  composite: "INV-2026-003:LINE-007"             # Parent + child reference

Bad keys:
  timestamp: "2026-03-02T10:30:00Z"              # Not unique across concurrent ops
  random_per_retry: uuid()                        # Different on each retry
  mutable_field: customer.email                   # Can change between retries

Verify: Test uniqueness with SELECT external_id, COUNT(*) GROUP BY external_id HAVING COUNT(*) > 1

3. Implement the upsert call (Salesforce example)

Salesforce upsert via External ID is the gold standard. The endpoint creates if the External ID value does not exist, or updates if it does. [src4]

# Salesforce REST API upsert — PATCH to the External ID endpoint
curl -X PATCH \
  "$INSTANCE_URL/services/data/v62.0/sobjects/Invoice__c/Vendor_Invoice_Id__c/INV-2026-003" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"Amount__c": 1500.00, "Vendor__c": "ACME Corp", "Due_Date__c": "2026-04-01"}'

# CREATE: HTTP 201 + {"id": "a0B..."}
# UPDATE: HTTP 204 No Content
# CONFLICT: HTTP 300 Multiple Choices (duplicate External IDs — data quality issue)

Verify: Query the record by External ID and confirm field values match.

4. Implement idempotency key pattern (POST-only APIs)

When the target does not support upsert, add a client-generated idempotency key. The server stores key + result and returns cached result on replay. [src1, src2]

import hashlib, json, time, random, requests

def call_with_idempotency(endpoint, payload, idempotency_key=None, max_retries=5):
    if idempotency_key is None:
        payload_hash = hashlib.sha256(
            json.dumps(payload, sort_keys=True).encode()
        ).hexdigest()[:32]
        idempotency_key = f"idem-{payload_hash}"

    headers = {"Content-Type": "application/json", "Idempotency-Key": idempotency_key}
    for attempt in range(max_retries):
        try:
            response = requests.post(endpoint, json=payload, headers=headers)
            if response.status_code == 409:  # Concurrent request
                time.sleep(2 ** attempt + random.uniform(0, 1))
                continue
            return response
        except requests.exceptions.ConnectionError:
            if attempt < max_retries - 1:
                time.sleep(2 ** attempt + random.uniform(0, 1))
                continue
            raise

Verify: Send same request twice with same key — second call returns identical response without creating a duplicate.

5. Implement server-side idempotency store

The key requirement is atomicity — check and business logic must execute in a single DB transaction. [src2, src6]

CREATE TABLE idempotency_keys (
    idempotency_key  VARCHAR(255) PRIMARY KEY,
    request_path     VARCHAR(500) NOT NULL,
    request_hash     VARCHAR(64) NOT NULL,
    status           VARCHAR(20) NOT NULL DEFAULT 'in_progress',
    response_code    INTEGER,
    response_body    JSONB,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at       TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours'
);
CREATE INDEX idx_idempotency_expires ON idempotency_keys (expires_at) WHERE status = 'completed';

Verify: SELECT COUNT(*) FROM idempotency_keys WHERE status = 'in_progress' AND created_at < NOW() - INTERVAL '1 hour' should be 0.

6. Implement Idempotent Consumer for event-driven flows

Track processed message IDs in a DB table within the same transaction as business logic. [src6]

def process_message_idempotently(conn, message):
    message_id = message["id"]
    subscriber_id = "erp-sync-worker"
    with conn.cursor() as cur:
        try:
            cur.execute("""
                INSERT INTO processed_messages (subscriber_id, message_id, received_at)
                VALUES (%s, %s, NOW())
            """, (subscriber_id, message_id))
            result = upsert_to_erp(message["payload"])  # Business logic
            cur.execute("""
                UPDATE processed_messages SET status = 'completed', processed_at = NOW()
                WHERE subscriber_id = %s AND message_id = %s
            """, (subscriber_id, message_id))
            conn.commit()
            return result
        except psycopg2.errors.UniqueViolation:
            conn.rollback()
            return {"status": "duplicate", "message_id": message_id}

Verify: Send same message ID twice — second returns {"status": "duplicate"}.

Code Examples

Python: Salesforce batch upsert with External ID and retry logic

# Input:  List of records with external IDs, Salesforce credentials
# Output: Upsert results (created/updated/error counts)

import requests, time, random

def salesforce_upsert_batch(instance_url, access_token, object_name,
                            ext_id_field, records, batch_size=200):
    headers = {"Authorization": f"Bearer {access_token}", "Content-Type": "application/json"}
    results = {"created": 0, "updated": 0, "errors": []}
    for i in range(0, len(records), batch_size):
        batch = records[i:i + batch_size]
        composite_body = {
            "allOrNone": False,
            "compositeRequest": [
                {
                    "method": "PATCH",
                    "url": f"/services/data/v62.0/sobjects/{object_name}/{ext_id_field}/{rec[ext_id_field]}",
                    "referenceId": f"ref_{idx}",
                    "body": {k: v for k, v in rec.items() if k != ext_id_field},
                }
                for idx, rec in enumerate(batch)
            ],
        }
        for attempt in range(5):
            resp = requests.post(f"{instance_url}/services/data/v62.0/composite",
                                 json=composite_body, headers=headers, timeout=120)
            if resp.status_code == 429:
                time.sleep(2 ** attempt + random.uniform(0, 1))
                continue
            break
        for sub in resp.json().get("compositeResponse", []):
            if sub["httpStatusCode"] == 201: results["created"] += 1
            elif sub["httpStatusCode"] == 204: results["updated"] += 1
            else: results["errors"].append(sub)
    return results

JavaScript/Node.js: Generic idempotency middleware for Express

// Input:  Express request with Idempotency-Key header
// Output: Cached response on duplicate, or proceeds to handler

const { Pool } = require("pg");
const crypto = require("crypto");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function idempotencyMiddleware(req, res, next) {
  const key = req.headers["idempotency-key"];
  if (!key || req.method === "GET") return next();
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const hash = crypto.createHash("sha256").update(JSON.stringify(req.body)).digest("hex");
    const { rowCount } = await client.query(
      `INSERT INTO idempotency_keys (idempotency_key, request_path, request_hash, status)
       VALUES ($1, $2, $3, 'in_progress') ON CONFLICT DO NOTHING RETURNING idempotency_key`,
      [key, req.path, hash]);
    if (rowCount === 0) {
      const { rows } = await client.query(
        `SELECT status, response_code, response_body FROM idempotency_keys WHERE idempotency_key = $1`, [key]);
      await client.query("COMMIT");
      if (rows[0].status === "completed") return res.status(rows[0].response_code).json(rows[0].response_body);
      return res.status(409).json({ error: "Request in progress" });
    }
    await client.query("COMMIT");
    const origJson = res.json.bind(res);
    res.json = async (body) => {
      await pool.query(`UPDATE idempotency_keys SET status='completed', response_code=$1, response_body=$2
         WHERE idempotency_key=$3`, [res.statusCode, JSON.stringify(body), key]);
      return origJson(body);
    };
    next();
  } catch (err) { await client.query("ROLLBACK"); next(err); }
  finally { client.release(); }
}

cURL: OData Repeatable Request with idempotency headers

# Input:  OData v4 endpoint, bearer token, UUIDv4 for request ID
# Output: Idempotent POST — server skips on replay

REQUEST_ID=$(uuidgen)

# First request — creates the sales order
curl -X POST \
  "https://your-s4hana.sap/sap/opu/odata4/sap/API_SALES_ORDER_SRV/A_SalesOrder" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -H "Repeatability-Request-ID: $REQUEST_ID" \
  -H "Repeatability-First-Sent: $(date -u '+%a, %d %b %Y %H:%M:%S GMT')" \
  -d '{"SalesOrderType": "OR", "SoldToParty": "10100001"}'

# Retry with SAME headers — server returns: Repeatability-Result: accepted

Data Mapping

Idempotency Key Mapping Across Systems

Data Type Gotchas

• Salesforce External IDs are case-sensitive but NetSuite External IDs are case-INsensitive. Normalize to single case at middleware layer. [src4, src7]
• Dynamics 365 alternate key lookups with NULL values fail silently — all key fields must be non-null or the upsert falls back to create.
• SAP document numbers may have leading zeros that get stripped by JavaScript/Python — always preserve original format.
• Timestamps as key components are dangerous — clock skew between systems creates different keys for the same operation.

Error Handling & Failure Points

Common Error Codes

Failure Points in Production

• Orphaned in-progress keys: Process crashes between recording key and completing operation. Fix: Implement TTL-based cleanup job that resets stuck in_progress keys after N minutes. [src1]
• Key collision across environments: Same keys used in staging and production. Fix: Prefix keys with environment: "prod:ORD-001" vs "staging:ORD-001". [src2]
• Bulk API silent duplicates: Malformed External ID values in Bulk API create new records. Fix: Validate all External IDs are non-null/non-empty before submission. Post-job duplicate check. [src4]
• NetSuite case collision: Two integrations assign ORD-001 and ord-001 to different records. Fix: Normalize all external IDs to uppercase at assignment. Add lint check. [src7]
• Transaction boundary violation: Key recorded in one transaction, business logic in another. Fix: Always wrap check + logic in single ACID transaction. Use Outbox pattern for microservices. [src2]
• Key reuse with different payload: Developer reuses a key for a different operation. Fix: Store request payload hash alongside key. Reject with 422 if hash doesn't match. [src1]

Anti-Patterns

Wrong: POST without idempotency for record creation

# BAD — every retry creates a duplicate record
def create_order(api_client, order_data):
    response = api_client.post("/api/orders", json=order_data)
    return response  # Network timeout → retry → duplicate order!

Correct: Use upsert with External ID or idempotency key

# GOOD — retries are safe, no duplicates
def create_order(api_client, order_data):
    ext_id = order_data["order_reference"]  # Stable business key
    response = api_client.patch(f"/api/orders/external/{ext_id}", json=order_data)
    return response  # Retry 100 times — same result every time

Wrong: Pre-check GET then POST (check-then-act race condition)

# BAD — race condition between check and create
def create_if_not_exists(api_client, record):
    existing = api_client.get(f"/api/records?ext_id={record['ext_id']}")
    if not existing.json()["records"]:
        # Another thread creates the record HERE → duplicate!
        api_client.post("/api/records", json=record)

Correct: Atomic upsert or database-level constraint

# GOOD — atomic operation, no race condition
def create_if_not_exists(api_client, record):
    api_client.patch(f"/api/records/ext_id/{record['ext_id']}", json=record)

Wrong: Random values as idempotency keys

# BAD — different key on every retry
import uuid
def create_payment(api_client, payment_data):
    headers = {"Idempotency-Key": str(uuid.uuid4())}  # Random! New every call!
    return api_client.post("/api/payments", json=payment_data, headers=headers)

Correct: Deterministic key generated at point of origin

# GOOD — same key on every retry
def create_payment(api_client, payment_data, idempotency_key):
    headers = {"Idempotency-Key": idempotency_key}  # Generated ONCE by caller
    return api_client.post("/api/payments", json=payment_data, headers=headers)

Common Pitfalls

• Confusing idempotency keys with deduplication IDs: An idempotency key identifies a unique intent (client-side). A deduplication ID identifies a unique message (server-side). Use both when integrating through a message broker to an ERP API. [src5]
• Forgetting HTTP 300 on Salesforce upsert: When an External ID matches multiple records, Salesforce returns 300 instead of updating. Fix: Run duplicate detection reports periodically and merge records. [src4]
• TTL too short for batch jobs: A 24h TTL works for real-time, but weekly batch retry on Monday finds Friday's keys expired. Fix: Set TTL = max retry window + buffer. Weekly: 10 days. Monthly: 35 days. [src1]
• Key collisions across integration flows: Two flows using the same key format creates cross-flow collisions. Fix: Prefix keys with flow identifier: "order-sync:ORD-001" vs "invoice-sync:INV-001". [src2]
• Ignoring partial success in bulk upserts: Bulk operations may partially succeed. Retrying the entire batch wastes API quota. Fix: Parse per-record results and retry only failed records. [src4]
• Using mutable fields as External IDs: Customer email as External ID fails when the email changes. Fix: Use immutable identifiers (source system ID, UUID) as External IDs. [src7]

Diagnostic Commands

# Check for duplicate External IDs in Salesforce (SOQL via REST API)
curl -s "$INSTANCE_URL/services/data/v62.0/query?q=SELECT+Ext_Id__c,COUNT(Id)+FROM+MyObject__c+GROUP+BY+Ext_Id__c+HAVING+COUNT(Id)>1" \
  -H "Authorization: Bearer $TOKEN"

# Check idempotency key store for stuck in-progress keys
psql -c "SELECT idempotency_key, request_path, created_at
         FROM idempotency_keys
         WHERE status = 'in_progress'
         AND created_at < NOW() - INTERVAL '30 minutes';"

# Verify NetSuite external ID assignment
curl -s "https://{account_id}.suitetalk.api.netsuite.com/services/rest/record/v1/salesOrder?q=externalId IS 'ORD-2026-001'" \
  -H "Authorization: Bearer $TOKEN"

# Check Dynamics 365 alternate key definition
curl -s "$D365_URL/api/data/v9.2/EntityDefinitions(LogicalName='salesorder')/Keys" \
  -H "Authorization: Bearer $TOKEN"

# Monitor idempotency key store growth
psql -c "SELECT status, COUNT(*), MIN(created_at), MAX(created_at)
         FROM idempotency_keys GROUP BY status;"

Version History & Compatibility

When to Use / When Not to Use

Cross-System Comparison

Important Caveats

• Idempotency does NOT equal ordering: Two idempotent operations applied out of order can produce wrong results. Combine idempotency with sequence numbers if order matters.
• Key storage grows continuously: Without TTL-based pruning, the idempotency key table becomes a bottleneck. Run daily cleanup. Monitor table size weekly.
• Sandbox vs production differences: Salesforce sandboxes may have stale External ID indexes. NetSuite sandboxes have independent namespaces. Verify behavior in full-copy sandbox.
• Payload consistency across retries: If the client changes the payload between retries (e.g., updated amount), the server should reject with 422. Verify your implementation enforces this.
• Multi-tenant ERP isolation: Ensure idempotency keys are scoped to the tenant. Shared dedup stores risk cross-tenant key collision.
• API version changes do not affect idempotency: Upgrading API versions does not change upsert behavior, but new validation rules or triggers may cause previously successful upserts to fail.