A few years into working on a utility billing SaaS platform, I ended up owning a feature that sounds simple on a slide and is genuinely nasty underneath: "what would my bill look like on a different rate plan?"
Utilities offer customers a handful of rate plans — flat rate, time-of-use, tiered, demand-based, seasonal variants — and one of the most-requested features from account holders (and from the utilities themselves, who want to nudge customers toward plans that reduce peak load) is a side-by-side comparison: take my actual usage, keep my current plan as the baseline, and show me what I'd have paid on plans B, C, and D instead.
That's not a lookup. It's re-running the full bill calculation engine, once per candidate rate plan, against a customer's interval usage data for the billing period — a genuinely expensive, synchronous REST call. And because it's expensive and synchronous, it's also a good case study in a design decision that doesn't get talked about much: when your idempotency key is already sitting in your domain model, you don't need a client-generated token to get idempotency right.
Why this endpoint had to be idempotent
The rate-comparison endpoint was a plain REST API: client calls in, server computes N bill calculations — one per rate plan being compared, with the customer's current plan as the baseline — server returns a comparison result.
Because each calculation walks a customer's interval-usage records for the full billing period and applies a rate plan's pricing rules to them, a single request could take long enough that clients — mobile apps, web dashboards, other backend services — would occasionally time out and do exactly what well-behaved clients are supposed to do: retry.
Without protection, a naive implementation would kick off a second full multi-plan comparison run for a request that was probably still in flight server-side, potentially write two comparison result records for the same logical request, and in the worst case show a customer two different numbers for "what you'd pay on Plan B" if the retry landed slightly differently (say, a rate table got updated between the two runs). For a feature whose entire job is to be trusted enough that a customer switches rate plans based on it, that's not a cosmetic bug — it's the kind of thing that erodes trust in the number on the screen.
The usual answer, and why we skipped it
The textbook approach here is an idempotency-key header: the client generates a UUID before making the request, sends it on every attempt including retries, and the server keeps a store mapping keys to results — first request executes and caches its result under that key, every subsequent request with the same key gets the cached result back without re-executing anything.
It's a solid, general-purpose pattern, and it's the right call when there's no natural way to identify "this is logically the same request" from the request's own content — think payment submissions, where two purchases of the same product on the same day by the same user are legitimately different events, not duplicates.
Our situation was different. A rate comparison request is fully described by three things: which account, which billing period, which set of rate plans are being compared. Two requests with the same three values aren't just similar — they're asking the exact same question. That's a natural key sitting right there in the domain model, which meant we didn't need to invent a token, hand it to the client, and build a store to track it. We could let the database enforce uniqueness on data that already had to exist.
The design: a unique constraint on a derived natural key
We derived a deterministic key from the request itself:
// Simplified illustration of the pattern
String comparisonKey = accountId + ":" + billingPeriodId + ":"
+ sortedRatePlanIds.stream()
.map(String::valueOf)
.collect(Collectors.joining(","));
Sorting the rate plan IDs before joining them matters more than it looks — without it, comparing [planA, planB] and [planB, planA] would produce two different keys for what's semantically the same comparison, and you'd have silently reintroduced the duplicate problem you were trying to solve.
The request handler runs the multi-plan calculation, then attempts to insert the result row against a uniqueness guarantee on that key. On success, that's a new comparison, and it's returned. On a violation, the handler catches the persistence exception, fetches the existing row by the same natural key, and returns that instead.
That last part is the detail that makes this actually safe under concurrency, not just under sequential retries. Two identical requests arriving milliseconds apart — a genuine race, not a retry-after-timeout — will both attempt the insert; the database guarantees exactly one of them succeeds and the other gets the violation. That's a correctness guarantee an application-level "check if it exists, then insert if not" pattern can't give you on its own, because there's a window between the check and the insert where a second request can slip through. Letting the database's own uniqueness enforcement be the source of truth removes that window entirely, instead of trying to close it with an extra layer of locking.
The edge case a natural key can't distinguish on its own
Here's the trade-off worth being honest about: this approach only works because the natural key happens to be meaningful and stable. It breaks down the moment there's a legitimate reason to recompute the same (account, billing_period, rate_plan_set) tuple — for example, a customer's usage data gets corrected after a meter re-read, and the existing comparison is now built on stale numbers.
A pure natural-key constraint can't tell "this is an unwanted duplicate" apart from "this is the same identifiers, but the underlying data actually changed and a new answer is needed." Both look identical from the key's perspective, so the schema needs to make room for a deliberate recompute as a distinct, first-class event rather than trying to make the key itself smart enough to infer intent it can't see.
The approach we used: add a superseded_at timestamp to the row, and instead of a plain unique constraint on the natural key, scope uniqueness to only the current (non-superseded) row for that key:
CREATE UNIQUE INDEX uq_rate_comparison_current
ON rate_comparison_run (account_id, billing_period_id, rate_plan_set_key)
WHERE superseded_at IS NULL;
A recompute becomes a transaction: mark the existing current row superseded_at = now() — which drops it out of the index — then insert the new row as the current one. Old rows stay in the table as history rather than being overwritten, which is genuinely useful in a billing context: "what number did we actually show this customer, and when" is a question support and compliance will ask eventually.
A first pass at this might reach for putting a version number directly into a plain multi-column unique constraint instead of using a partial index. That doesn't work cleanly: a flat UNIQUE (account_id, billing_period_id, rate_plan_set_key, version) means every plain request — not just recomputes — now needs an extra read to determine "what's the current version" before it can even attempt the insert, since a normal retry has no version of its own to supply. Scoping the index to current rows avoids that: ordinary requests attempt the insert directly and fall into the same catch-violation-and-fetch path already in place, with no extra lookup on the common path.
One portability caveat worth knowing before reaching for this: the WHERE clause on CREATE INDEX is Postgres/SQLite/SQL Server syntax (SQL Server calls the equivalent a "filtered index"). MySQL and Oracle don't support a WHERE clause on index creation at all. Both have workarounds — MySQL via a generated column that evaluates to NULL for superseded rows and a real value for current ones (leaning on the same "NULLs don't collide in a unique index" behavior discussed above, deliberately this time), Oracle via a function-based index doing roughly the same thing — but the mechanism differs enough by engine that it's worth checking rather than assuming the syntax above just ports.
What actually triggers a recompute is a separate, open question
Notice what this section hasn't answered: what decides that a comparison is stale and a recompute is needed? In our case the trigger was something upstream — a correction to previously-submitted interval data — but the honest answer is that "what marks something as needing recompute, and how that signal reaches this endpoint" is its own design problem, and not a small one. It could be an event from whatever system corrects the underlying data, a scheduled reconciliation job, a manual trigger from support tooling, or something else entirely, and the right choice depends heavily on how often the underlying data actually changes, how expensive a recompute is, and who needs to know the result is stale and when. That's genuinely a per-product decision rather than something this pattern hands you for free — the natural-key-plus-supersede design gives you a clean place to record a recompute once you've decided one is needed; it doesn't tell you when to decide that.
Testing it
The way we actually validated the core idempotency guarantee wasn't a unit test against the constraint definition — it was firing concurrent duplicate requests at a real (test) instance and asserting exactly one row landed in the table and both callers got the same result back. A unit test can confirm the SQL is syntactically what you meant; it can't tell you whether your application code handles the violation path correctly under real concurrency, which is the part that's actually easy to get wrong (catching the wrong exception type, catching it in the wrong place, or forgetting the "then fetch and return the existing row" half entirely and just surfacing a 500).
Takeaways
- Look for a natural key before reaching for an idempotency-key header. If a request is fully identified by data that already has to be in the request — account, period, resource set — a database-enforced uniqueness guarantee on that data is simpler than a client-generated token and a separate store to track it.
- Canonicalize before deriving the key. Any set-like or order-independent part of the request (a list of IDs, in our case) needs to be normalized — sorted, deduplicated — before it goes into the key, or logically identical requests will produce different keys.
- Let the database close the race, don't try to close it yourself. An app-level check-then-insert has a window a concurrent duplicate can slip through. Database-enforced uniqueness, combined with catching the violation and fetching the existing row, doesn't.
- A natural key can't distinguish "duplicate" from "same identifiers, genuinely new data." If that case is possible in your domain, give recomputation an explicit, distinct path (a supersede-and-reinsert pattern, in our case) instead of trying to make the key smart enough to infer intent it can't see.
- Scoping uniqueness to "current" rows isn't universal SQL — Postgres, SQLite, and SQL Server support it natively; MySQL and Oracle need a different mechanism to get the same effect. Check before assuming a snippet ports across engines.
- Deciding when something needs recomputing is a separate problem from how to record a recomputation. This pattern solves the second; the first depends on your system's specific data-freshness and cost tradeoffs and deserves its own design pass rather than an assumed answer.
- Test the concurrent path, not just the constraint. The SQL is the easy part to get right; the application code's handling of the violation is where idempotency bugs actually hide.
