Idempotent Upsert v2

Use a stable business key and deterministic create-or-update rules so retrying the same input does not create a duplicate or apply an unsafe transition twice.

Define identity

Choose an immutable source identifier or a deliberate composite business key. Do not use a run ID, timestamp, row position, or generated target ID as the only match for a retried source record.

Normalize the key consistently and enforce uniqueness in the target when possible.

Apply the operation

  1. Validate the key and required fields.
  2. Look up the target using a parameterized, bounded query.
  3. Create the target when no match exists.
  4. Update only allowed fields when exactly one match exists.
  5. Return a conflict when duplicate matches or an invalid state transition are found.
  6. Record whether the outcome was created, updated, unchanged, or rejected.

Use a provider-native atomic upsert or idempotency key when its discovered Method contract supports one. A separate lookup followed by create can race under concurrency, so combine it with target uniqueness and conflict handling.

Retry safely

Classify transient dependency errors separately from validation and conflict errors. Retry only bounded transient failures. Reprocessing the same input should converge on the same target state.

Test duplicate delivery, concurrent delivery, a partial timeout after target success, a changed payload for the same key, and provider conflict responses.

See also