A customer order syncs to the ERP, the HTTP call times out at 10 seconds, and your worker retries. The ERP had already created the order. Now finance sees two invoices for one purchase. This duplicate-write failure is the most common defect we see in ERP integration services, and it appears wherever a webhook, queue, or cron job can deliver the same message twice. This guide shows how ERP integration services handle retries safely with an idempotency table, classified errors, and a dead-letter state in Node.js and PostgreSQL.
Context and Setup
Idempotency means that processing the same message many times produces the same result as processing it once. Without it, at-least-once delivery, which every queue and webhook provider uses, turns into duplicate records.
The scenario: a Node.js 20 worker reads order events from your storefront and posts them to an ERP REST API. You need PostgreSQL 14 or later, the pg package, and an ERP endpoint that accepts JSON. The problem is widespread because systems rarely share a single delivery guarantee. The Salesforce MuleSoft Connectivity Benchmark Report (2026) found that the average organization runs 957 applications and integrates only 27 percent of them. Each unintegrated gap tends to get a quick script, and quick scripts rarely dedupe.
Building Idempotent ERP Integration Services
The pattern has three parts: a deterministic key, an atomic claim, and a classified retry loop.
Step 1: Derive a Deterministic Key and Claim It
A key built from the source system, entity ID, and entity version means a replay always produces the same key. We store it in a table with a primary key, so the database decides who sends.
sql
CREATE TABLE sync_log (
idem_key text PRIMARY KEY, -- Why: uniqueness is enforced by the database, not app code
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'pending', -- pending | sent | dead
attempts int NOT NULL DEFAULT 0,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
js
import pg from "pg";
import { createHash } from "node:crypto";
const pool = new pg.Pool();
// Why: same order and same version always give the same key, so replays collapse into one row
export const syncKey = (source, id, version) =>
createHash("sha256").update(${source}:${id}:${version}).digest("hex");
export async function claim(key, payload) {
const { rowCount } = await pool.query(
INSERT INTO sync_log (idem_key, payload) VALUES ($1, $2), // Why: atomic under concurrent workers
ON CONFLICT (idem_key) DO NOTHING
[key, payload]
);
return rowCount === 1; // true means this worker owns the send
}
Step 2: Send with Classified Retries
Retrying everything wastes time and hammers a struggling ERP. Retry only failures that can succeed later: timeouts, network errors, HTTP 429, and 5xx. Send anything else to the dead state for a human.
Post the payload with the key in an Idempotency-Key header.
On 2xx, mark the row sent.
On other 4xx, mark it dead with the response body.
On timeout, 429, or 5xx, wait with exponential backoff and retry.
js
async function mark(key, status, err = null) {
await pool.query(
"UPDATE sync_log SET status=$2, last_error=$3 WHERE idem_key=$1", [key, status, err]);
}
export async function send(key, payload, erpUrl, token) {
for (let attempt = 0; attempt < 5; attempt++) {
try {
const res = await fetch(erpUrl, {
method: "POST",
headers: { "Content-Type": "application/json",
Authorization: Bearer ${token},
"Idempotency-Key": key }, // Why: lets the ERP dedupe if it supports it
body: JSON.stringify(payload),
signal: AbortSignal.timeout(10_000), // Why: a hung socket must not hold the worker
});
if (res.ok) return mark(key, "sent");
// Why: a validation error will fail identically on every retry
if (res.status < 500 && res.status !== 429) return mark(key, "dead", await res.text());
} catch { /* timeout or network error: the ERP may have processed it, so retry with the same key / }
await pool.query("UPDATE sync_log SET attempts=$2 WHERE idem_key=$1", [key, attempt + 1]);
await new Promise(r => setTimeout(r, 2 * attempt * 500)); // Why: backoff protects the ERP
}
return mark(key, "dead", "retries exhausted");
}
Step 3: Weigh the Trade-Offs in ERP Integration Services
A database table beats Redis SETNX for this job because the claim and the audit trail live in one transactional place. Redis is faster, but a key expiry or restart can silently reopen the window for duplicates.
The table has one gap. A worker that crashes after claim leaves a pending row that no replay will resend. Run a sweeper that retries rows stuck in pending for more than a few minutes. If your ERP honors Idempotency-Key natively, keep the table anyway, since it also gives you the dead-letter queue and a reconciliation report.
Real-World Application
In one of our ERP integration services projects at Oodles, system and ERP, for example a storefront syncing orders to an ERP over REST produced duplicate records after timeouts. We added an idempotency table, error classification, and a pending sweeper.
Key Takeaways
Build the idempotency key from source, entity ID, and version so replays always collide.
Let a database primary key decide ownership of a send, not application checks.
Treat a timeout as unknown, not failed, and always retry with the same key.
Send non-retryable 4xx responses to a dead state instead of looping.
Sweep stale pending rows, or crashed workers will lose messages quietly.
Questions about your own sync design? Share them in the comments, or talk to us about ERP integration services.
What are ERP integration services?
ERP integration services connect an ERP system to other applications such as storefronts, CRMs, and warehouse tools. They include data mapping, API or middleware development, synchronization, error handling, testing, and monitoring so records stay consistent across systems.
Why do ERP syncs create duplicate records?
Queues, webhooks, and retry loops deliver at least once, not exactly once. If a request times out after the ERP already processed it, the retry creates a second record unless the sender attaches a stable idempotency key.
Should a timeout be retried?
Yes, but only with the same idempotency key. A timeout means the outcome is unknown, since the ERP may have committed the write. Reusing the key lets the database or ERP reject the duplicate.
Which HTTP errors should not be retried?
Most 4xx responses, such as 400 and 422, indicate invalid data that will fail identically each time. Retry only 429, 5xx, timeouts, and network errors. Route the rest to a dead-letter state for review.
Is a database table better than Redis for idempotency?
For ERP syncs, usually yes. A PostgreSQL primary key gives an atomic claim, durable storage, and an audit trail together. Redis is faster but loses guarantees if keys expire or the instance restarts.
Top comments (0)