Three Idempotency Design Patterns: Unique Keys, State Machines and Dedup Tables

Why Idempotency Is the Foundation of Integration

In cross-system data sync, network timeouts, platform rate limits and downstream outages all trigger retries. If a write operation is not idempotent, a single retried timeout can cause duplicate orders, double-counted inventory or double-posted accounting entries — damage that far outweighs a bit of sync latency. An idempotent operation produces exactly the same business outcome whether it runs once or N times.

Pattern 1: Unique-Key Constraints

The simplest and most reliable approach: add a unique index such as (platform, platform_order_no) to the business table. A duplicate insert hits the constraint and is caught, then converted into an update or ignored. It cannot prevent duplicate updates, but it is the non-negotiable baseline for every table a sync pipeline writes to.

Pattern 2: State Machines

Ideal for documents with a clear lifecycle (order: created → paid → shipped → completed). Each write carries a target state, and the update only applies when the current state permits the transition. Repeating the same event then matches zero rows and has no side effect. A state machine also intercepts out-of-order events — for example, receiving "shipped" before "paid" can be rejected or queued per business rules.

Pattern 3: Dedup Tables

Best for event-stream scenarios: assign every event a unique ID, and before consuming it, insert (consumer, event_id) into a dedup table. A conflict means the event was already processed. The dedup insert must live in the same transaction as the business write, otherwise a crash window creates false positives where the dedup record exists but the business write never happened.

Choosing (Spoiler: Combine Them)

The three patterns are complementary, not exclusive: the dedup table blocks duplicate events, the state machine constrains transitions, and the unique key is the final backstop. In Qeasy's visual integration pipelines, every data flow enables key-based idempotent writes by default, with pre-built transition validation for order-type entities.