Implementing idempotency, retries and partial-failure recovery for mid-market payroll integrations — ready-to-copy code, DB schemas and chunking strategy

If you've already read the payroll integration runbook covering connector testing and safe cutover steps, you know the why. This is the how — the actual code, the DDL, the retry tables. The stuff you copy into your repo and adapt.

The specific failure this article prevents: an employee gets paid twice because a batch POST timed out, your code retried it, and the payroll vendor processed both attempts. Or the opposite — a timeout made you skip a line, and 14 people got shorted. Both come down to the same thing: your integration doesn't have a deterministic way to say "I've already done this exact thing, don't do it again."

The core problem: non-deterministic keys

Most duplicate-pay incidents trace back to one bad decision — using a timestamp, a random UUID generated at send time, or an auto-increment row ID as the idempotency key.

1. 1
   Deterministic — same logical operation always produces the same key
2. 2
   Scoped — includes the pay period and employee/line so two different periods never collide
3. 3
   Stable across retries — generated once, stored, and reused for every attempt

The key should answer: "What business event is this, exactly?" Not "when did I send it."

Python: canonical key generation

``python import hashlib import json def payrolllinekey(tenantid, payperiodid, employeeid, lineversion): """ Deterministic idempotency key for a single payroll line. lineversion increments only when the HR-approved amount changes, so a corrected line gets a NEW key (intentionally reprocessable). """ canonical = { "t": str(tenantid), "pp": str(payperiodid), "emp": str(employeeid), "v": int(lineversion), } # sortkeys ensures byte-stable serialization across runs/machines blob = json.dumps(canonical, sortkeys=True, separators=(",", ":")) digest = hashlib.sha256(blob.encode("utf-8")).hexdigest() return f"pl{tenantid}{payperiodid}{digest[:24]}" def batchkey(tenantid, payperiodid, runattemptgroup): """ Key for the whole batch envelope. runattemptgroup stays constant across network retries but changes if HR re-opens the run. """ raw = f"{tenantid}:{payperiodid}:{runattemptgroup}" digest = hashlib.sha256(raw.encode("utf-8")).hexdigest() return f"batch{payperiodid}{digest[:20]}" ``

The line_version field is the detail people miss. You want a corrected line to be reprocessable — if HR fixes someone's overtime, that's a genuinely new operation. Bumping the version bumps the key. A plain network retry keeps the version, keeps the key, and the vendor deduplicates it. One field does both jobs.

Node.js: same logic, same hashes

``javascript const crypto = require("crypto"); function payrollLineKey(tenantId, payPeriodId, employeeId, lineVersion) { const canonical = { t: String(tenantId), pp: String(payPeriodId), emp: String(employeeId), v: Number(lineVersion), }; // Key order here must match Python's sortkeys output const ordered = {}; Object.keys(canonical).sort().forEach((k) => (ordered[k] = canonical[k])); const blob = JSON.stringify(ordered); const digest = crypto.createHash("sha256").update(blob, "utf8").digest("hex"); return pl${tenantId}${payPeriodId}${digest.slice(0, 24)}; } ``

If part of your stack is Python and part is Node, verify both produce identical digests for the same inputs. A one-character difference in JSON serialization — spaces after colons, key ordering — produces a completely different hash, and your dedup breaks silently. Write a cross-language test that asserts the same key string. Teams have lost a full day to a ", " vs "," separator mismatch. It's not a fun debugging session.

SQL: deriving the key in the database

``sql -- Postgres: deterministic line key using built-in digest() -- requires: CREATE EXTENSION IF NOT EXISTS pgcrypto; SELECT 'pl' || tenantid || '' || payperiodid || '' || substr( encode( digest( tenantid || ':' || payperiodid || ':' || employeeid || ':' || lineversion, 'sha256' ), 'hex' ), 1, 24 ) AS idempotencykey FROM payrolllines WHERE payperiodid = :payperiod_id; ``

One caution: the SQL concatenation order and delimiters must match whatever your app code uses, if both can generate keys for the same line. Pick a single source of truth. Mixing app-generated and DB-generated keys for the same records is how you end up with two valid-looking keys for one payment.

Where the keys live: Redis and Postgres schemas

You need two storage layers for different jobs. Redis gives you a fast "have I seen this key in-flight?" check with automatic expiry. Postgres gives you the durable, auditable record that survives a Redis flush.

Redis: short-lived in-flight dedup

`` # Set the key only if it doesn't exist, with a TTL. # NX = only set if not present. EX = expire in seconds. SET idemp:pl422024Q1_a1b2c3... "processing" NX EX 172800 # 172800 = 48 hours. Long enough to cover a stuck retry window, # short enough that reopened runs weeks later aren't blocked. ``

The 48-hour TTL is deliberate. Too short and a batch that's legitimately retrying across a long backoff window loses its lock. Too long and a genuinely new, corrected run for the same line hits a stale key. Two days comfortably covers almost every retry-and-resume cycle without lingering.

In application code the flow is: SET ... NX returns success → you own the operation, proceed. Returns nil → someone's already handling it (or finished), check Postgres for the final result before doing anything.

Postgres: durable idempotency ledger

``sql CREATE TABLE idempotencyrecords ( idempotencykey TEXT PRIMARY KEY, tenantid BIGINT NOT NULL, payperiodid TEXT NOT NULL, employeeid BIGINT, requesthash TEXT NOT NULL, -- hash of full payload sent responsestatus INT, -- final HTTP status we accepted responsebody JSONB, -- vendor's response, for resume state TEXT NOT NULL DEFAULT 'pending' -- pending|succeeded|failed CHECK (state IN ('pending','succeeded','failed')), attemptcount INT NOT NULL DEFAULT 0, createdat TIMESTAMPTZ NOT NULL DEFAULT now(), updatedat TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Prevents two different payloads from reusing one key by accident. -- If requesthash differs for an existing key, that's a bug — alert on it. CREATE UNIQUE INDEX idxidempkeyhash ON idempotencyrecords (idempotencykey, requesthash); -- Retention / cleanup support: find old settled records to archive. CREATE INDEX idxidempsettled ON idempotencyrecords (state, updated_at) WHERE state IN ('succeeded','failed'); ``

The request_hash column catches a specific class of bug: the same key sent with a different payload. That should never happen in correct code, so when the unique index on (key, hash) rejects it, you've found a logic error before it becomes a payroll error. Treat that constraint violation as a loud alert, not a silent catch.

For retention, settled records don't need to live in the hot table forever. A nightly job can move anything succeeded/failed older than roughly 90 days to cold storage. You keep the audit trail without bloating dedup lookups.

Retry policy: status codes mapped to exact parameters

Vague retry logic — "try 3 times" — is where duplication sneaks in. Different HTTP responses mean genuinely different things and your retry behavior has to match. Here's the table to hand a developer on day one.

Two rules that do most of the work: never retry a 409 (the vendor is telling you it's already done — retrying just hammers them and risks confusing your own state), and always honor Retry-After on a 429 instead of running your own backoff curve. If the vendor says "wait 30 seconds," your 2-second exponential math is wrong and you'll keep getting throttled.

The backoff implementation

``python import random import time RETRYABLE = {408, 429, 500, 502, 503, 504} def computedelay(attempt, base, multiplier, jitterpct, retryafter=None): if retryafter is not None: # Vendor told us exactly how long to wait. Add small jitter # so a fleet of workers doesn't all wake at the same instant. return retryafter + random.uniform(0, retryafter 0.1) raw = base (multiplier * (attempt - 1)) jitter = raw jitterpct return raw + random.uniform(-jitter, jitter) def sendwithretry(sendfn, idempotencykey, maxattempts=5, base=2.0, multiplier=2.0, jitterpct=0.2): for attempt in range(1, maxattempts + 1): resp = sendfn(idempotencykey=idempotencykey) # SAME key every time if resp.statuscode < 400: return resp if resp.statuscode == 409: # Already processed — treat as success, fetch prior result return resp if resp.statuscode not in RETRYABLE: raise PermanentError(resp.statuscode, resp.text) if attempt == maxattempts: raise RetriesExhausted(idempotencykey, resp.statuscode) retryafter = None if resp.statuscode == 429 and "Retry-After" in resp.headers: retryafter = float(resp.headers["Retry-After"]) time.sleep(computedelay(attempt, base, multiplier, jitterpct, retryafter)) ``

The idempotency_key is passed in and reused on every attempt — never regenerated inside the loop. That single discipline is what makes retries safe. The jitter matters more than it looks: without it, a hundred parallel workers that all hit a 503 at once will retry at exactly 2s, 4s, 8s — synchronized thundering herds that keep the vendor down. The ±20% spread breaks the sync.

Partial-success batch handling with per-line retries and resume tokens

This is where most integrations get it wrong. You POST a batch of 500 lines. The vendor returns 207 Multi-Status saying 487 succeeded, 11 failed with 503, and 2 failed with 422. Teams retry the whole batch — re-sending the 487 that already worked. Even with idempotency keys protecting you, you're wasting throughput and risking rate limits.

Parse the per-line results and retry only the retryable failures, keyed individually.

``python def processbatchresponse(respbody, linesbykey): """ respbody["results"] = [{ "key": ..., "status": 200|422|503, ... }] Returns (succeededkeys, retrylines, deadletterlines) """ succeeded, retrylines, deadletter = [], [], [] for result in respbody["results"]: key = result["key"] status = result["status"] if status < 400 or status == 409: succeeded.append(key) elif status in RETRYABLE: retrylines.append(linesbykey[key]) # keep original line+key else: # 422/400 — data problem, don't retry blindly deadletter.append({"line": linesbykey[key], "status": status, "detail": result.get("detail")}) return succeeded, retrylines, dead_letter ``

The resume token ties this together across process restarts. Before you send a batch, write a resume record. After each response, update which keys have settled. If your worker crashes mid-run, you pick up from the token instead of resending everything.

``sql CREATE TABLE batchresumetokens ( token UUID PRIMARY KEY DEFAULT genrandomuuid(), batchkey TEXT NOT NULL, payperiodid TEXT NOT NULL, totallines INT NOT NULL, settledkeys JSONB NOT NULL DEFAULT '[]', -- keys already done lastcursor INT NOT NULL DEFAULT 0, -- offset into line list state TEXT NOT NULL DEFAULT 'inprogress', createdat TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ``

On resume: load the token, skip any line whose key is in settledkeys, continue from lastcursor.

Because the line keys are deterministic, a resumed run can't accidentally re-pay a settled line even if the token is slightly stale — the vendor's idempotency check is your backstop. Belt and suspenders.

The resume workflow, step by step

1. 1
   Assemble all lines for the pay period, compute a deterministic key for each.
2. 2
   Create a batchresumetoken with totallines and empty settledkeys.
3. 3
   Chunk lines into batches (sizing below) and send each with SET NX Redis locks.
4. 4
   On each response, append settled keys to the token, record failures in the dead-letter set, advance last_cursor.
5. 5
   For retryable failures, run sendwithretry per line with its original key.
6. 6
   If the worker dies, reload the token, filter out settled_keys, resume from cursor.
7. 7
   When settled + deadletter == totallines, mark the token complete and alert on any dead-letter rows.

When settled + deadletter == totallines, mark the token complete and alert on any dead-letter rows.

Batch sizing and backpressure during resume and cutover

Batch size is a tradeoff nobody tunes until it hurts. Too large and a single partial failure forces you to parse and re-chunk hundreds of lines. Too small and request volume alone triggers rate limits.

For most mid-market payroll volumes — a few hundred to a few thousand employees — chunks of 100–250 lines tend to work well. Big enough to be efficient, small enough that retrying the failed subset is cheap.

1. 1
   Cap concurrent in-flight batches per pay period. One worker owns a period at a time, or use a Redis lock on batch{payperiod_id} so a second worker can't start the same period.
2. 2
   Respect a token bucket for requests/second sized below the vendor's documented rate limit, not at it — leave roughly 20% headroom for retries.
3. 3
   Pause intake on sustained 429s. Three 429s in a short window should stop new batches until in-flight ones drain.
4. 4
   Never re-enqueue a batch whose resume token is still in_progress. Check token state before any requeue.

When smaller batches make sense

If your vendor returns opaque all-or-nothing responses with no per-line status, shrink batches aggressively — down to 25–50 lines — so a failure blast radius stays small. You're trading request overhead for containment, which is the right trade when you can't surgically retry.

When this is overkill

If you're pushing a single payroll run of 30 employees once a month to a vendor with a clean idempotent API, you don't need resume tokens and token buckets. A deterministic key plus the retry table is plenty. Don't build the full machinery for volume you'll never have — the resume-token layer earns its keep around the point where a batch failure means re-sending enough lines that manual recovery stops being realistic.

A real scenario

A regional staffing firm running payroll for roughly 1,400 field workers across three clients kept getting duplicate-pay tickets — usually two or three per cycle, but one month it hit 19 people double-paid because a batch POST timed out during a vendor maintenance window and their job retried the entire file with freshly generated UUID keys.

The fix was exactly what's above: deterministic keys derived from tenant + payperiod + employee + lineversion, a Postgres idempotency ledger with the (key, request_hash) unique index, and per-line retries off a 207 response instead of whole-batch replays. Batch size dropped from a single 1,400-line push to chunks of about 200, with a Redis lock per pay period.

Duplicate-pay incidents dropped to zero across the next several cycles. Recovery time after a vendor hiccup went from "re-run and manually reconcile the whole file" to "resume from token in a few minutes." The reconciliation team stopped dreading maintenance windows.

Final thought

Idempotency in payroll isn't a library you bolt on — it's a small set of disciplined choices: derive keys from the business event, store them durably with a request-hash guard, map every status code to a specific retry rule, and never retry a whole batch when you can retry a line. Get those four right and the duplicate-pay tickets simply stop arriving.

The code above is meant to be copied and adapted, not admired — paste it, change the field names to match your schema, and write the cross-language key test before you ship.

Idempotency in payroll isn't a library you bolt on — it's a small set of disciplined choices: derive keys from the business event, store them durably with a request-hash guard, map every status code to a specific retry rule, and never retry a whole batch when you can retry a line. Get those four right and the duplicate-pay tickets simply stop arriving.

The code above is meant to be copied and adapted, not admired — paste it, change the field names to match your schema, and write the cross-language key test before you ship.