Idempotency for ERP Event Handlers

ERP event handlers — whether it's Acumatica business events, a webhook consumer, or a message queue subscriber — will eventually receive the same event more than once. Networks retry, message brokers guarantee at-least-once delivery rather than exactly-once, and a consumer that crashes after processing but before acknowledging will see its message redelivered. If your handler isn't idempotent, that duplicate delivery becomes a duplicate invoice, a double stock adjustment, or a customer charged twice.

Why at-least-once delivery is the norm, not the exception

Exactly-once delivery across a network is a famously hard problem — most messaging systems (SQS, Service Bus, Kafka with default settings, webhook retries from any SaaS platform) explicitly guarantee at-least-once instead, because guaranteeing exactly-once would require coordination that kills throughput and availability. This means your handler will see the same logical event twice under normal operation, not just as a rare edge case: a consumer that processes a message and crashes before sending the acknowledgment causes the broker to redeliver it, a webhook sender that times out waiting for your 200 response retries the same payload, and a manual reprocessing of a dead-letter queue resends events that may have partially succeeded the first time.

The idempotency key pattern

The fix is to make handling the same event twice produce the same result as handling it once. The mechanism is an idempotency key: a unique identifier for the logical operation (not the delivery attempt) that you check against a record of already-processed operations before doing any work. If the event has a natural unique ID (Acumatica business events include one; most webhook payloads include an event ID), use it directly. If not, derive one deterministically from the event's content — for example, hashing the source system's document reference plus the operation type.

CREATE TABLE ProcessedEvents (
    EventId       VARCHAR(100) PRIMARY KEY,
    ProcessedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Outcome       VARCHAR(20) NOT NULL  -- 'succeeded' / 'failed'
);

-- Handler logic, pseudocode:
-- BEGIN TRANSACTION
--   IF EXISTS (SELECT 1 FROM ProcessedEvents WHERE EventId = @eventId)
--     RETURN  -- already handled, skip silently (or return cached result)
--   -- do the actual work: create AP bill, adjust inventory, etc.
--   INSERT INTO ProcessedEvents (EventId, Outcome) VALUES (@eventId, 'succeeded')
-- COMMIT

The critical detail is that the insert into the processed-events table and the actual business operation (creating the bill, adjusting inventory) need to happen in the same transaction. If you record the event as processed before doing the work, a crash between those two steps means the retry never happens and the event is silently lost. If you do the work first and record it second, a crash between those steps means a retry re-executes the business operation — exactly the duplicate you were trying to prevent.

Naturally idempotent operations vs. ones that need a key

Some operations are idempotent by nature and don't need a tracking table at all — setting a field to an absolute value ("set status to Closed") produces the same end state no matter how many times it runs. Others are inherently non-idempotent by nature — "increment inventory count by 10" or "create a new AP bill" — and these are exactly the ones that need explicit deduplication, because running them twice changes the outcome. Before reaching for an idempotency table, check whether you can reframe the operation as absolute rather than relative; it's often less code than building deduplication infrastructure.

What happens when the handler fails partway through

A handler that touches multiple systems — say, creating an AP bill in the ERP and then notifying a downstream approval system — can fail after the first step succeeds but before the second completes. On retry, naive idempotency (checking "have I seen this event ID before") would skip the whole handler and never send the notification. The more robust approach tracks state per sub-step, not just per event, so a retry can resume from where it actually failed rather than either re-doing everything or skipping everything.

Wrapping up

Treat at-least-once delivery as the guaranteed behavior of any event pipeline, not an edge case to handle later. Record the idempotency key and the business effect in the same transaction, prefer operations that are naturally idempotent when the design allows it, and give the dedup table a retention window instead of letting it grow forever.