Two identical claims. Same member, same date of service, same procedure code, same everything. Processed twice. Not because anyone submitted the form twice on purpose. Because a network retry fired, the receiving system acknowledged too slowly, and the queue decided "maybe it didn't work" and tried again. Both went through. Both wrote records. Both triggered downstream workflows. The system was perfectly correct about each one individually, and catastrophically wrong in aggregate.
That moment is the reason I think about idempotency differently now than I did early in my career. Back then, it felt like a theoretical computer science concept. Now it keeps me from being paged at 2am.
The Deceptive Simplicity of "Just Check First"
The obvious answer is: before you process a claim, check whether it already exists. If it does, skip it. Simple.
Except it isn't.
The check and the write are two separate operations. Between the moment you check and the moment you write, another process can sneak in. In a busy production environment, that gap is milliseconds, which sounds like nothing right up until you have a queue consuming messages with eight concurrent workers. Now the same claim can pass the "does this exist" check in two workers simultaneously, both conclude "no, it's new," and both proceed to write. You have not solved the problem. You have given it a race condition.
You can tighten the gap with locks. A pessimistic lock before the check prevents concurrent reads, which prevents the race. But now you have serialized your entire processing pipeline on a single bottleneck, and your throughput drops as fast as your retry budget climbs. The cure starts to feel like a disease.
There is another failure mode that does not get talked about enough: partial processing. A claim does not land in one atomic moment. It triggers a chain: validation, benefit lookup, adjudication, writing the determination, notifying downstream systems. If a failure happens at step four, you have three steps' worth of state in your database and a half-processed claim that will come back around and fail your "does this exist" check in confusing ways. The system is neither fully processed nor safely reprocessable. It is stuck.
The naive approach assumes the failure modes are simple and linear. Production systems are neither.
Setting the Savepoint Before You Act
The insight that changed my thinking came from asking a different question. Instead of "how do I know if I've already done this?" I started asking "how do I make it safe to do this twice?"
That shift is not just phrasing. The first question treats duplication as a defect to intercept before it enters the pipeline. The second accepts that duplication will happen and designs the processing to survive it. Everything that follows from that second framing is different.
The mechanism has two parts that work together, and understanding why both are necessary is the part that took me the longest to internalize.
The first part is a stable idempotency key derived from the claim's identifying fields: member, date of service, procedure code, and anything else that makes this claim distinct from every other claim. In practice you compute a SHA hash of those fields and store it in a dedicated column backed by a unique database constraint. The hash is not just a key. It is the claim's identity collapsed into a form the database can filter on. Any two executions processing the same claim will derive the same hash, which means the unique constraint always sees them as the same thing, regardless of when they arrive or which worker handles them. That determinism is the actual enforcement layer. It is the thing that makes a double-write physically impossible at the database level, regardless of how many concurrent workers are racing toward the same insert.
The second part is where transaction savepoints come in. You set a savepoint before you begin any state-changing work. Then you run the full processing chain: validation, adjudication, writing the determination record. At the end, you attempt the insert that carries the idempotency key. One of two things happens. If no prior transaction has committed a matching record, the insert succeeds and you commit. If another concurrent execution already committed the same key, the unique constraint fires. You catch that exception, roll back to your savepoint, and query for the record that won the race. The database returns to exactly the state it was in before you started, and you return the result from the prior run as if your own processing had produced it.
It is worth being precise about why both pieces are necessary. Transaction isolation means that while your transaction is in progress, you cannot see another concurrent transaction's uncommitted writes. Two workers running the full processing chain simultaneously will both reach the final insert step, and both will be genuinely unaware of each other's work in progress. The unique constraint, not the transaction boundary, is what collapses that race: only one commit can win, and the constraint violation tells the other worker that it lost. The savepoint is what makes losing recoverable, but not for the reason people first assume. Catching a DML exception does not roll back the outer transaction. Prior DML persists. That means every intermediate record written before the failing insert is still in the database, attached to nothing, with no idempotency key to tie it to a completed run. The savepoint is how you undo that deliberately. Because you set it before any state-changing work began, rolling back to it clears the determination record and the intermediate state, all of it, while leaving the outer transaction intact. The worker then queries for the record from the winning execution and returns it as its own result.
Neither part is sufficient alone. The unique constraint without a savepoint does stop the second write, but catching the constraint exception leaves prior DML untouched, so the losing worker's intermediate records remain in the database with nothing to anchor them. The savepoint without a unique constraint gives you graceful rollback semantics but no actual prevention of double-writes when two transactions commit in sequence without visibility into each other. The SHA key plus the savepoint together close both gaps.
The duplicate arrival is no longer a bug. It is a retry that the system handles gracefully. The second arrival triggers the full processing path, reaches the final insert, loses the constraint race, rolls back cleanly to the savepoint, queries for the existing record, and returns it to the caller. The caller gets the right answer. The database has one record, not two. The downstream workflows fire once, not twice.
Running this through your head a few times, it almost feels too clean. You are doing real work, writing real records, burning real transaction budget, only to undo it. But consider the alternative: fragile check-then-act with a race window, or a serializing lock that kills throughput, or a half-processed state machine with no clear recovery path. The savepoint approach pays a transaction cost in the duplicate case. It buys correctness in every case, including the ones you have not thought of yet.
The pattern also changes how you think about error recovery. If processing fails partway through, you roll back to the savepoint, the claim returns to the queue, and the next attempt starts from a clean slate. No partial state to clean up, no compensating transactions to write, no "where was it when it failed" audit trail to reconstruct. The database is always consistent.
The Confidence That Follows
I think about the engineer who would have stared at those logs for five minutes instead of twenty. The version of me who had this pattern in place from the start. Not tracing which downstream workflow fired twice, not calculating whether records need to be voided. Just: checking the idempotency log, confirming the second arrival rolled back cleanly to the savepoint, verifying it returned the first result. Bounded. Done.
That is what the pattern actually delivers. Not just duplicate prevention as a feature, but a changed relationship with the entire failure surface of the system.
When you know a claim can arrive ten times and the outcome will be the same every time, your relationship with retries changes entirely. You stop being afraid of them. You can tune retry policies aggressively because you know they cannot corrupt state. You can add observability tooling that replays events for debugging without worrying about side effects. You can restore from a backup, replay the queue, and walk away knowing the records will be right. The system earns a property, and that property propagates outward into every operational decision you make.
It also changes oncall. The middle-of-the-night alert about a claim that seems to have "run twice" becomes a different kind of conversation. Not "how much damage was done and how do we fix it" but "let's confirm the idempotency key matched and which copy is canonical." Still a conversation you might have. But smaller, faster, and bounded.
Idempotency is not a feature you add to a system. It is a commitment the system makes about how it behaves under conditions it did not expect. Building toward that commitment with tools that enforce it at the transaction level is one of the more satisfying engineering problems I have worked on. Because the hard part is not understanding the concept. The hard part is being honest about every place in your pipeline where the assumption breaks down, and then closing those gaps one by one until the commitment holds everywhere.
The claim can arrive twice. That is fine. The system knows what to do.