Expense Audit Guide Python · Pipelines · Compliance

Building idempotent ERP sync with retry-safe exports

A lost acknowledgement is indistinguishable from a failed post, so the moment your exporter retries a timed-out request it risks booking the same reimbursement journal twice — the root cause of almost every double-posting incident in accounts-payable automation. This guide details the retry-safe export path delegated by the ERP Export Synchronization stage inside the broader Reimbursement Routing & Approval Sync framework: how a deterministic idempotency key, a transactional outbox, and ERP-side deduplication combine so that a replay after a partial failure collapses onto the original document instead of creating a second liability. The parent stage owns field mapping and acknowledgement reconciliation; here we own the exactly-once contract that makes every retry safe to run.

Retry-safe export where the same idempotency key collapses a replay onto one ERP document A dispatcher reads a pending outbox row and posts it to the ERP carrying a deterministic idempotency key. On the first attempt the ERP has not seen the key, so it books a new journal and returns a document id, which marks the outbox row posted. On a retry after a lost acknowledgement, the ERP recognizes the same key in its dedup store and returns the original document id without booking a second journal. A permanent validation error instead routes the row to a dead-letter queue. The lower band contrasts the unsafe path, where a naive retry with no key books a duplicate journal, against the safe path, where the key guarantees one document regardless of how many times the post is replayed. One idempotency key, one ERP document — no matter how many retries Outbox row PENDING · idem key Dispatcher post key sent every attempt ERP dedup store key seen? no → book · yes → return One document id stable across replays lost ack → safe replay, same key permanent error → dead-letter Unsafe vs safe retry under a lost acknowledgement Retry, no key every attempt is "new" ERP books again two journals, one expense Duplicate liability restatement risk Retry, same key deterministic identity ERP recognizes key returns original doc Exactly one liability replay is a no-op The key is the contract: the ERP, not the dispatcher, is the authority on whether a post already happened.

Why Standard Approaches Fail

The instinct to wrap the ERP call in a try/except with a retry loop is correct — but a retry loop is only safe when the operation it repeats is idempotent, and an unadorned journal post is not.

  • The lost-acknowledgement trap. A dispatcher posts a 250-dollar lodging reimbursement, the ERP books it, and the TCP connection drops before the 200 OK returns. From the dispatcher’s side this is a timeout, indistinguishable from a request the ERP never received. Retry, and the ERP — having no memory of the first attempt — books a second identical journal. The employee is now a 500-dollar liability for a 250-dollar expense, and the error only surfaces at trial-balance reconciliation.
  • Client-generated keys that aren’t deterministic. Some teams add an idempotency key but generate it fresh per attempt (a new UUID each retry) or per process. That defeats the entire mechanism: two attempts at the same expense carry different keys, so the ERP treats them as distinct. The key must be a pure function of the record’s immutable identity, computed once and stored, so every replay of that record re-sends the identical value.
  • Dedup on the wrong side. Deduplicating in the dispatcher — “have I sent this before?” — cannot be correct, because the dispatcher’s own state is exactly what a crash destroys. Only the ERP knows for certain whether a document was booked. Idempotency has to be enforced where the write lands, with the dispatcher merely presenting a stable claim ticket.

The fix is a three-part contract: a deterministic key derived from record identity, a transactional outbox so the intent to export survives a crash, and ERP-side deduplication so the authority on “already posted” is the system that actually holds the ledger.

Architecture & Algorithm

The retry-safe export is built from a key derivation that never changes for a given approval, a poster that treats a recognized-key response as success, and a replay loop with bounded exponential backoff. The poster’s core insight is that two ERP responses — “I just booked this” and “I already had this” — are both success; only a validation rejection is a failure.

from __future__ import annotations

import hashlib
import logging
import time
from dataclasses import dataclass
from typing import Protocol

logger = logging.getLogger("expense.erp.idempotent")


class DuplicatePost(Exception):
    """ERP signalled the idempotency key was already booked (HTTP 409-style)."""
    def __init__(self, document_id: str) -> None:
        super().__init__(f"already posted as {document_id}")
        self.document_id = document_id


class PermanentPostError(Exception):
    """Validation rejection a retry cannot fix (bad account, unbalanced)."""


class LedgerApi(Protocol):
    def create_journal(self, *, idempotency_key: str, journal: dict) -> dict: ...


def derive_idempotency_key(expense_id: str, approved_at: str, policy_version: str) -> str:
    """Pure function of immutable record identity: identical on every retry.

    Never seed this with time.time(), a UUID, or an attempt counter — those
    make each attempt a distinct key and reintroduce double-posting.
    """
    material = f"{expense_id}|{approved_at}|{policy_version}"
    return "exp-" + hashlib.sha256(material.encode("utf-8")).hexdigest()[:32]


@dataclass
class RetryPolicy:
    max_attempts: int = 6
    base_delay_s: float = 0.5
    max_delay_s: float = 30.0

    def delay_for(self, attempt: int) -> float:
        """Exponential backoff, capped, deterministic (no jitter here)."""
        return min(self.base_delay_s * (2 ** (attempt - 1)), self.max_delay_s)


def post_retry_safe(api: LedgerApi, key: str, journal: dict,
                    policy: RetryPolicy) -> dict:
    """Post a journal until it is confirmed booked exactly once.

    A recognized-key duplicate response is SUCCESS, not failure: it proves a
    prior attempt already landed, and we adopt that document id. Only a
    permanent validation error stops the loop; transient errors back off.
    """
    last_error: Exception | None = None
    for attempt in range(1, policy.max_attempts + 1):
        try:
            ack = api.create_journal(idempotency_key=key, journal=journal)
            logger.info("erp_booked", extra={"key": key,
                                             "document_id": ack["document_id"],
                                             "attempt": attempt})
            return {"document_id": ack["document_id"], "attempts": attempt,
                    "outcome": "BOOKED"}
        except DuplicatePost as dup:
            logger.info("erp_dedup_hit", extra={"key": key,
                                                "document_id": dup.document_id})
            return {"document_id": dup.document_id, "attempts": attempt,
                    "outcome": "ALREADY_POSTED"}
        except PermanentPostError:
            logger.error("erp_permanent_reject", extra={"key": key})
            raise
        except Exception as exc:  # transient network / 5xx: back off and replay
            last_error = exc
            wait = policy.delay_for(attempt)
            logger.warning("erp_transient_retry", extra={"key": key,
                                                         "attempt": attempt,
                                                         "wait_s": wait})
            time.sleep(wait)
    raise RuntimeError(f"exhausted {policy.max_attempts} attempts for {key}: {last_error}")

On the ERP side — or in an adapter in front of an ERP that lacks native idempotency — deduplication is a keyed insert into a store the ledger write shares a transaction with. The store maps each idempotency key to the document it produced, so a replay is answered from memory rather than re-executed.

from __future__ import annotations

from typing import Any


def create_journal_dedup(conn: Any, idempotency_key: str, journal: dict) -> dict:
    """Idempotent journal creation guarded by a keyed dedup table.

    The INSERT ... ON CONFLICT resolves the race: the first writer books the
    journal and records the key; any concurrent or later replay reads back the
    already-booked document id instead of writing a second journal.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT document_id FROM erp_idempotency WHERE idempotency_key = %s",
            (idempotency_key,),
        )
        row = cur.fetchone()
        if row is not None:
            return {"document_id": row[0], "deduplicated": True}

        document_id = _book_journal(cur, journal)   # the real ledger write
        cur.execute(
            "INSERT INTO erp_idempotency (idempotency_key, document_id)"
            " VALUES (%s, %s) ON CONFLICT (idempotency_key) DO NOTHING",
            (idempotency_key, document_id),
        )
        # Re-read to adopt the winner if a concurrent writer raced us.
        cur.execute(
            "SELECT document_id FROM erp_idempotency WHERE idempotency_key = %s",
            (idempotency_key,),
        )
        winner = cur.fetchone()[0]
        conn.commit()
        return {"document_id": winner, "deduplicated": winner != document_id}


def _book_journal(cur: Any, journal: dict) -> str:
    """Insert the balanced journal and return the ERP document id."""
    cur.execute(
        "INSERT INTO gl_journal (external_ref, currency, memo) VALUES (%s, %s, %s)"
        " RETURNING document_id",
        (journal["external_ref"], journal["currency"], journal["memo"]),
    )
    return str(cur.fetchone()[0])

The dedup table and the journal write commit in one transaction, so a crash between them is impossible: either both land or neither does. That is the same atomicity guarantee the transactional outbox provides on the sending side, applied on the receiving side.

Step-by-Step Integration

  1. Derive the key once, at approval time. Compute derive_idempotency_key when the record is approved and persist it on the outbox row; never recompute it per attempt. This is the same key the parent ERP Export Synchronization stage stages the outbox event under.

  2. Send the key on every attempt. Pass it as an HTTP header or SDK parameter on each post; the ERP or your adapter uses it as the dedup lookup.

  3. Treat a duplicate response as success. Map the ERP’s “already exists” response (typically HTTP 409 with the original document id) to adoption of that id, not to an error.

  4. Bound the replay loop. Cap attempts and back off exponentially so a genuinely down ERP does not spin; dead-letter after the ceiling.

  5. Verify the exactly-once property before wiring it in. Prove a replay is a no-op against a stub:

    class StubLedger:
    def __init__(self) -> None:
        self._booked: dict[str, str] = {}
        self.calls = 0

    def create_journal(self, *, idempotency_key: str, journal: dict) -> dict:
        self.calls += 1
        if idempotency_key in self._booked:
            raise DuplicatePost(self._booked[idempotency_key])
        doc = f"JE-{len(self._booked) + 1}"
        self._booked[idempotency_key] = doc
        return {"document_id": doc}

api = StubLedger()
key = derive_idempotency_key("E-1", "2026-07-02T10:00:00+00:00", "v3")
journal = {"external_ref": "E-1", "currency": "USD", "memo": "test"}
first = post_retry_safe(api, key, journal, RetryPolicy())
second = post_retry_safe(api, key, journal, RetryPolicy())   # simulated replay
assert first["document_id"] == second["document_id"]
assert second["outcome"] == "ALREADY_POSTED"
assert len({first["document_id"], second["document_id"]}) == 1

  6. Reconcile after posting. Hand confirmed document ids to the parent stage’s acknowledgement sweep, and forward settled postings to Payment Batch Reconciliation only once the document id is confirmed.

Edge Cases & Gotchas

Edge condition What breaks Mitigation
Key seeded with a UUID or timestamp Each retry is a distinct key; the ERP double-books Derive the key purely from immutable record identity; compute once, persist on the outbox row
Re-approval under a new policy version Same expense_id must post again, but the old key blocks it Include policy_version in the key so a legitimate re-post is a distinct document
Concurrent dispatcher replicas Two workers race the same key and both book Enforce dedup with INSERT ... ON CONFLICT and claim outbox rows with FOR UPDATE SKIP LOCKED
ERP without native idempotency No 409 on replay; the adapter must dedup Front the ERP with a keyed dedup table that shares the ledger write’s transaction
Backoff with no cap A down ERP makes the loop spin or block a worker Cap max_attempts and max_delay_s; dead-letter past the ceiling
Dead-letter row mutated in place Editing a posted row desyncs the ledger from local state Correct the source record and re-stage under a fresh approval, never rewrite the posted row
Amount stored as float A replayed journal fails the ERP balance check inconsistently Carry money as Decimal or integer minor units end to end

FAQ

Why must the idempotency key be deterministic instead of a random UUID?

Because the whole point is that two attempts at the same expense present the same key so the ERP recognizes the replay. A random UUID generated per attempt makes every retry look like a brand-new post, which reintroduces exactly the double-posting the key was meant to prevent. Derive it as a pure function of the record’s immutable identity — the expense id, approval timestamp, and policy version — compute it once, and persist it so every replay re-sends the identical value.

Where should deduplication actually happen, the dispatcher or the ERP?

At the ERP, or in an adapter directly in front of it, never in the dispatcher alone. The dispatcher’s local memory of “I already sent this” is precisely what a crash destroys, so it cannot be the authority on whether a document was booked. Only the system that holds the ledger knows for certain, so the dedup lookup must live where the write lands, guarded by a keyed table that shares a transaction with the journal insert.

Is a duplicate response from the ERP an error I should alert on?

No — a recognized-key response is success and should be treated as confirmation that a prior attempt already landed. Map it to adoption of the returned document id and continue. Alerting on it would page your team every time a retry works exactly as designed; reserve alerts for permanent validation rejections and for rows that exhaust the retry ceiling.

How does the transactional outbox relate to the idempotency key?

They solve two halves of the same problem. The outbox guarantees the intent to export survives a crash by committing the export event in the same transaction as the approval, so nothing is lost. The idempotency key guarantees the execution is safe to repeat, so replaying that surviving intent never double-books. Together they give end-to-end exactly-once: durable on the send side, deduplicated on the receive side.

What happens to a record that fails permanently after retries?

It routes to a dead-letter exception queue rather than being retried forever or silently dropped. A permanent failure means a validation problem a retry cannot fix — an unmapped account, an unbalanced journal, a closed period — so a human corrects the underlying record or mapping. The corrected record is then re-staged under a fresh approval, which produces a new idempotency key; the original dead-letter row is never mutated in place.