The Check Is Not the Idempotency Guarantee

A background job often begins with a reasonable question: “Has this business event already been handled?” If the answer is yes, skip it. If not, write the result.

That check is useful. It is also not a guarantee.

I recently inspected a committed processing path that combined several defensive layers. It preloaded previously handled business keys, coordinated scheduled execution to avoid overlapping runs, enforced a filtered composite unique rule in the database, and narrowly recognised the expected uniqueness conflict through wrapped exceptions.

The useful lesson was not any single technique. It was the responsibility assigned to each one.

I inspected the relevant tests but did not run them, so the observations below describe the committed design and test intent rather than a fresh test result.

Why check-before-insert loses the race

Imagine two workers processing the same logical event:

Worker A: check for key -> not found
Worker B: check for key -> not found
Worker A: insert
Worker B: insert

Both checks reported the truth at the moment they ran. Neither could reserve the future.

This is a time-of-check-to-time-of-use race. Making the check faster does not remove it. Loading all handled keys into memory can reduce database traffic, but each worker may still begin with the same snapshot.

Scheduler coordination helps by preventing expected overlap between job runs. That is useful operational protection. It does not necessarily cover every execution path. A retry, manual trigger, second process, or future caller can still reach the write.

The check and the scheduler reduce the likelihood of a collision. They do not own the integrity rule.

Give each layer one job

A robust design separates efficiency, coordination, and correctness:

Preloaded handled keys  -> avoid unnecessary work
Scheduler coordination  -> reduce concurrent execution
Database unique rule    -> prevent duplicate committed state
Exception classifier    -> identify one safe duplicate outcome

This distinction matters because optimisations are allowed to become stale. Invariants are not.

The preloaded set can answer, “Is this probably worth attempting?” The scheduler can answer, “Should another scheduled run start now?” Only the database can answer, “May these two rows coexist in committed state?”

Putting that final decision in the database also protects callers that do not yet exist. A new service path does not need to remember every application-level convention before the invariant applies.

Let the database arbitrate

The generalised database rule might look like this:

UNIQUE (BusinessKeyPartA, BusinessKeyPartB)
WHERE RecordQualifiesForUniqueness

The composite key represents the identity of the logical operation. The filter limits the rule to the states where duplicates are forbidden.

That filter deserves careful thought. It is not merely a performance tweak; it forms part of the invariant. If ordinary, provisional, or superseded records have different semantics, the database rule should express those semantics deliberately.

With the rule in place, concurrent attempts have a deterministic boundary:

attempt insert
database accepts one valid row
database rejects a conflicting row

The application no longer needs to make the race impossible. It needs to interpret the database result correctly.

Catch only the duplicate you expected

The tempting implementation is broad:

try:
    insert
catch persistence_exception:
    return already_handled

That can turn broken relationships or an unrelated uniqueness failure into apparent success.

A safer pattern is narrow:

try:
    insert
    return completed
catch persistence_exception error
    when is_expected_business_key_conflict(error):
        return already_handled

The classifier may need to inspect wrapped inner exceptions because the persistence layer often adds abstraction around the provider error. It should recognise the particular rule that proves the intended row already exists.

Conceptually:

function is_expected_business_key_conflict(error):
    for each error in wrapped_exception_chain:
        if error identifies expected unique rule:
            return true

    return false

Everything else must continue to fail.

That includes a violation of a different unique rule, a foreign-key failure, and a persistence exception with no recognised database cause. Those failures are not evidence that the intended work was already completed.

Test the error boundary honestly

The tests I inspected made this classification boundary explicit.

A positive test supplied a wrapped exception representing the expected uniqueness conflict. Negative tests supplied a different uniqueness conflict, a foreign-key diagnostic, and a bare persistence exception. Only the intended duplicate case was classified as “already done.” A separate rerun test also showed that sequential execution did not create the logical occurrence twice.

There was a practical testing limitation: the lightweight test database could not reproduce the production provider’s diagnostic shape. Representative-message unit tests were therefore used to exercise the classifier directly.

That is a focused technique, provided its limits are understood. It verifies application classification logic, not the production database’s actual diagnostic behaviour. Where feasible, a small integration test against the production database engine should complement it.

These tests were inspected, not executed during this review.

The trade-off is explicit

Provider-specific classification introduces coupling. Diagnostic formats can change, and the identifier used to recognise a rule must remain aligned with the database definition.

That maintenance cost is real. Broadly swallowing persistence exceptions is still the more dangerous option because it converts unrelated integrity failures into silent success.

The coupling can be contained by centralising the classifier, keeping its purpose narrow, testing both positive and negative cases, and maintaining a production-provider integration check where practical.

Practical takeaway

For idempotent writes:

1. Check first when it saves work.
2. Coordinate scheduled execution when overlap is wasteful.
3. Put the real invariant in a database uniqueness rule.
4. Treat only the precisely identified expected conflict as already completed.
5. Let every other failure remain visible.

The check is an optimisation. The database rule is the guarantee.