What Building a Fintech Ledger Taught Me About Idempotency

I built a double-entry payments ledger on AWS EKS — six FastAPI services, Terraform, an SNS/SQS async pipeline, the whole thing. Going in, I assumed the hard parts would be infrastructure: Fargate quirks, Terraform state, getting Prometheus to scrape anything.

Those were annoying. But they were findable — something crashes, you read logs, you fix it.

The genuinely hard part was a question that sounds too simple to be interesting:

What happens if the same "send money" request arrives twice?

That question turned out to have four different answers depending on where in the system you ask it, and getting it wrong in any one of those places would have silently moved someone's money twice.

---

The version that looks right and isn't

My first instinct was the obvious one: check whether it already happened.

existing = db.query("SELECT * FROM transactions WHERE idempotency_key = %s", key)
if existing:
    return existing
# ...otherwise process the transfer

This reads fine. It's also broken, and it's broken in the way that's hardest to catch: only under concurrency.

Between the SELECT and the INSERT there's a window. If two requests carrying the same idempotency key arrive close enough together — the original, plus a retry fired because the client's HTTP request timed out — both run that SELECT before either has committed anything. Both see nothing. Both proceed. Two debits.

And client retries aren't an edge case. They're the normal path: a mobile app on a flaky connection, a load balancer timing out, a user double-tapping "Send" because the spinner hasn't resolved. A payments system that can't survive a retry isn't handling a rare scenario badly — it's handling the common one badly.

---

The guarantee has to live in the database

The thing that took me embarrassingly long to internalize: no amount of application logic fixes this. Any "check, then act" sequence has a window unless something below it makes the two steps atomic.

So the actual mechanism is four words:

CREATE TABLE transactions (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key  TEXT NOT NULL UNIQUE,
    ...
);

UNIQUE. That's it. That's the guarantee. Everything else in my idempotency story is an optimization sitting on top of it.

INSERT INTO transactions (idempotency_key, ...)
VALUES ($1, ...)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;

No row returned means someone else already claimed that key — go fetch their result and return it. A row returned means you own the work, and nobody can take it from you, because Postgres enforces that at the storage layer rather than trusting that your Python checked first.

Redis is a shortcut, not the promise

I do cache idempotency keys in ElastiCache to skip the database on hot retries. But two rules kept that cache from quietly becoming a liability:

1. It is never the source of truth. A cache miss, an evicted key, a Redis node dying — none of it changes correctness, because the UNIQUE constraint is still there underneath. Losing Redis costs latency, not money.
2. Keys are written only after the database transaction commits. Caching before commit means caching an outcome that might still roll back — a bug that would be nearly impossible to reproduce and catastrophic when it fired.

---

The test that actually proves it

I could have written "added a unique constraint, therefore idempotent" in the README and moved on. Plenty of projects do.

The problem: a sequential test — send the request, wait for the response, send it again — passes even against my broken check-then-insert version. There's no race, because the first INSERT has long since committed by the time the second SELECT runs. You get a green checkmark that proves nothing.

So the test fires both requests at once:

with ThreadPoolExecutor(max_workers=2) as pool:
    f1 = pool.submit(fire_transfer, same_idempotency_key)
    f2 = pool.submit(fire_transfer, same_idempotency_key)
    r1, r2 = f1.result(), f2.result()

assert r1.json()["id"] == r2.json()["id"]
entries = get_ledger_entries(r1.json()["id"])
assert len(entries) == 2   # one debit, one credit. never four.

Only the concurrent version touches the actual race window. Writing the sequential one first and feeling good about it would have been a false sense of security I'd have carried all the way to production.

---

The bug that taught me idempotency has a failure path too

This one I found by accident, weeks later, while adding Prometheus metrics — and it's the part I'd most want to talk through in an interview.

Failed transfers (insufficient funds, frozen account) were doing this:

with conn:                                  # commits on exit, rolls back on exception
    ...
    cur.execute("UPDATE transactions SET status = 'failed' WHERE id = %s", (txn_id,))
    raise HTTPException(422, "insufficient funds")   # <-- inside the block

Raising inside with conn: rolls back the transaction — including the "mark as failed" update I'd just written. So a failed transfer left zero trace in the database. The idempotency key was never durably claimed. Which means a retry of that request looked brand new, and got processed from scratch.

I'd built idempotency for the success path and completely forgotten the failure path. The fix was to stash the exception and raise it after the transaction block exits cleanly, so the failure status actually commits:

pending_error = None
with conn:
    ...
    if insufficient_funds:
        cur.execute("UPDATE transactions SET status = 'failed' ...")
        pending_error = HTTPException(422, "insufficient funds")
# block exited normally -> the 'failed' row is committed
if pending_error:
    raise pending_error

The lesson generalizes past this bug: "what happens if this arrives twice" has to be answered for every outcome, not just the happy one. A rejected request is still a request that can be retried.

---

Idempotency at the seam between two systems

Once transfers were safe, I hit the same problem one layer out. A completed transfer needs to trigger fraud scoring and a notification. The obvious implementation:

db.commit()                    # write the ledger entries
sns.publish(event)             # tell everyone about it

That's a dual write — two independent systems, two independent failure modes. If the commit succeeds and the publish fails, the money moved and nothing downstream ever hears about it. Reverse the order and you announce a transfer that then fails to commit. There is no ordering of those two lines that is safe, because they can't be one atomic operation.

The fix is the Outbox Pattern, and it's the design decision I'm happiest with in the whole project. The transfer writes an outbox_events row in the same database transaction as the ledger entries:

BEGIN;
  INSERT INTO ledger_entries ...;   -- debit + credit
  INSERT INTO outbox_events  ...;   -- "this happened"
COMMIT;

Now the event's existence is exactly as reliable as the money movement — same transaction, same atomicity, no gap. A separate poller is the only thing in the entire system that talks to SNS:

SELECT * FROM outbox_events WHERE published = false
ORDER BY created_at LIMIT 20
FOR UPDATE SKIP LOCKED;

SKIP LOCKED is what makes that poller safe to run as multiple replicas with zero coordination logic — each instance grabs rows nobody else currently holds. It runs as one replica today, but the scaling path needs no redesign.

And then the duplicates come back

Here's the part that ties it all together: SQS is at-least-once. A consumer will occasionally see the same message twice — a visibility timeout expiring mid-processing, a redrive, an ack that didn't land.

So the exact question from the very beginning shows up again, in a completely different place, for completely different reasons. "What if this arrives twice" isn't a property of your HTTP API. It's a property of every boundary in a distributed system, and each one needs its own answer.

---

Where the discipline paid off

The payoff wasn't during normal operation. It was during the failure testing.

When I deliberately drained both Fargate nodes running a service back-to-back, and when I exercised an RDS Multi-AZ failover, requests in flight at the wrong moment failed — connections dropped, queries timed out. In most systems that's genuinely alarming: did the transfer happen? Is it safe to retry? Could I double-charge someone?

Here the answer was boring, which is the highest compliment you can pay a failure mode. Retry with the same idempotency key. If it committed before the disruption, the retry finds the existing row and returns it. If it didn't, the retry processes it fresh. Exactly once, either way.

Same story with the poison-message test: I deliberately pushed a malformed event into the fraud queue and watched it fail, retry, fail again, and land in the dead-letter queue after real SQS retries — while valid messages behind it kept processing normally. No head-of-line blocking, nothing silently lost.

That's the real return on this work. Idempotency isn't just politeness toward flaky mobile clients. It's what converts "we had a database failover" from a forensic investigation into what state the system might be in into a thing that happened for ninety seconds and then stopped happening. You answer the duplicate question once, up front, instead of re-litigating it during every incident for the rest of the system's life.

---

The takeaway

If I compressed this whole project into one paragraph for someone building something similar:

Put the correctness guarantee in a database constraint, not in application logic. Write a test that genuinely races it, because a sequential test will lie to you. Answer the duplicate question for failures too, not just successes. And at every boundary between two systems, ask it again — because the answer doesn't carry over.

The Kubernetes manifests, the Terraform modules, the Grafana dashboards — that's all real work, and I learned a lot building it. But it's work you can course-correct on later. A ledger that silently double-debits someone under concurrent retries is not something you course-correct on later.

You get that right at the schema level, or you don't get it right at all.

---

FinLedger is open source: github.com/saurabhg4356/finledger — including the full design doc, the chaos-engineering runbooks, and a written list of the nine production bugs I hit and diagnosed along the way.