Locks, Idempotency, and Partial Indexes  —  Part 2 of 3

If Part 1 is the map, this article is the guardrails. Users will double-click pay. Webhooks arrive twice. Two tabs will submit the same withdrawal. Your database must make “get paid twice for one intent” structurally difficult.

Two Race Windows

Window A — Reservation (create pending)

Two debits might both read balance = 100 and pending_debits = 0 before either insert commits. Both create pending rows for 80. You have 160 of commitments on 100 of cash.

Window B — Settlement (finalize)

Two workers finalize the same transaction id, or finalize two different debits that together exceed balance.

Fix A with wallet row locks during debit creation and pending-debit math. Fix B with transaction row locks, status guards, and balance re-check at finalize.

Serialize with FOR UPDATE

In PostgreSQL, SELECT … FOR UPDATE locks the row until the transaction commits. Use it in your SECURITY DEFINER routines, not in app code that does read-modify-write across round trips.

On Finalize (Single-Leg)

1. SELECT * FROM transactions WHERE id = $1 FOR UPDATE

2. If status is not pending/held → return (idempotent no-op)

3. For debits completing: SELECT * FROM wallets WHERE id = $wallet FOR UPDATE

4. Verify balance >= amount

5. Update transaction status

6. Insert ledger row

7. Update wallet balance

Concurrent finalizes on the same wallet queue on step 3. The second debit sees the reduced balance and fails or no-ops.

On Create Pending (Debit)

Lock the wallet before summing pending debits and comparing to amount. That closes Window A for debits created through the same code path.

Idempotency Is a Status Machine, Not a Cache Header

The simplest replay protection: if status NOT IN ('PENDING', 'HELD'), finalize does nothing.

HTTP idempotency keys help at the API edge, but the database must still defend against duplicate workers.

For webhooks, store processed event ids in meta (e.g. processed_webhook_ids: []). Skip work if the transaction is already completed, even if the provider sends the event again.

Database Constraints as Last Line of Defense

Application logic drifts. Constraints do not.

One Ledger Row Per Transaction

CREATE UNIQUE INDEX ledgers_transaction_id_unique

  ON ledgers (transaction_id)

  WHERE transaction_id IS NOT NULL;

If a bug double-inserts ledger rows for the same completion, the second insert fails loudly.

One In-Flight Row Per Payment Reference Per Wallet

Split payments legitimately share a gateway reference across different wallets (platform fee wallet + seller wallet). Duplicates to prevent are on the same wallet:

CREATE UNIQUE INDEX transactions_reference_wallet_inflight_unique

  ON transactions (reference, wallet_id)

  WHERE reference IS NOT NULL

    AND status IN ('PENDING', 'HELD');

Use a partial index so historical duplicate COMPLETED rows (legacy bugs) do not block migration. New duplicate pending fundings are rejected.

Non-Negative Balance

ALTER TABLE wallets ADD CONSTRAINT wallets_balance_non_negative

  CHECK (balance >= 0);

Even if logic regresses, the database refuses to go negative.

Online Credits: Verify Before You Mint

Attackers do not break your ledger math; they fake completed credits.

Rules that work in practice:

8. Client creates pending credit with gateway reference.

9. Only service role (webhook handler or trusted API) may finalize to completed.

10. Finalize calls provider verification or validates signed webhook payload inside the database routine or immediately before it.

11. Authenticated end-users cannot pass synthetic verificationData in JSON to mint money.

Amount and currency on the transaction should match what the gateway reports.

Paired Transfers

Peer-to-peer transfer is two transactions (debit + credit) finalized together in one routine:

• Lock both transaction rows

• Lock both wallets

• Set both to completed

• Insert two ledger rows

• Update both balances

Half-applied transfers are worse than failed transfers.

Reviewer Checklist

Before you ship wallet v2, ask:

☐  Can any code path update wallets.balance without a ledger insert?

☐  Does finalize no-op when status is already terminal?

☐  Are debits serialized with FOR UPDATE on the wallet at reserve time?

☐  Are debits re-validated at finalize time?

☐  Is there a unique constraint on ledger transaction_id?

☐  Is there a partial unique on in-flight (reference, wallet_id)?

☐  Can clients finalize online credits without server verification?

☐  Do webhooks record processed ids and tolerate replay?

Summary

Concurrency control for wallets is boring on purpose: lock the rows you read for decisions, treat finalize as idempotent, and let constraints catch what code misses.

Next: Part 3 — Transfers, splits, and operating a ledger in production