I run an AI image generation SaaS. Users buy credits, each generation burns some, and failed generations get refunded automatically. Sounds like a solved problem — until you try to build it on a serverless Postgres driver that doesn't support multi-statement transactions.
Neon's HTTP driver is one of those. Every query is a separate round trip. BEGIN; ... COMMIT;
isn't available. Which means every billing operation has to be correct as a single statement, or not at all.
Here are the four races that actually bit me, and how each got fixed. All four are the kind of bug that doesn't show up in testing and does show up in your support inbox.
The naive version everyone writes first:
const balance = await getBalance(userId);
if (balance < cost) throw new Error("Insufficient credits");
await setBalance(userId, balance - cost);
Two concurrent requests both read balance = 5
, both pass the check, both write balance = 4
. The user got two generations for the price of one.
With transactions you'd wrap this in SERIALIZABLE
and move on. Without them, the fix is to make the check and the write the same statement:
UPDATE credits_balance
SET balance = balance - $amount
WHERE user_id = $userId
AND balance >= $amount
RETURNING balance
A single UPDATE
is atomic in Postgres. If the balance is insufficient, WHERE
matches nothing, zero rows return, and you know the deduction failed. No transaction needed.
The general pattern: move your invariant into the WHERE
clause. If the row doesn't match, the write doesn't happen.
Stripe retries webhooks. On timeouts, on 500s, on network hiccups. It also occasionally delivers the same event twice under normal operation. If your handler grants credits, "at least once" delivery means "at least once granted."
The usual fix is a processed_events
table plus a transaction:
await tx.insert(processedEvents).values({ id: event.id }); // throws on duplicate
await tx.update(balance).set({ credits: sql`credits + ${amount}` });
No transactions, no dice — a crash between those two statements either double-grants on retry or loses the grant entirely.
The fix that works in one statement is a data-modifying CTE where the ledger insert acts as a gate:
WITH gate AS (
INSERT INTO credits_transactions (user_id, delta, type, ref_id, balance_after)
SELECT $userId, $amount, 'pack_purchase', $refId, ...
ON CONFLICT (ref_id) WHERE type IN ('plan_grant', 'pack_purchase') DO NOTHING
RETURNING id
)
INSERT INTO credits_balance (user_id, topup_balance)
SELECT $userId, $amount WHERE EXISTS (SELECT 1 FROM gate)
ON CONFLICT (user_id) DO UPDATE
SET topup_balance = credits_balance.topup_balance + EXCLUDED.topup_balance
Backed by a partial unique index:
CREATE UNIQUE INDEX credits_tx_grant_idem_idx
ON credits_transactions (ref_id)
WHERE type IN ('plan_grant', 'pack_purchase');
ref_id
is the Stripe checkout session or invoice id. On a redelivery the INSERT
hits the conflict, RETURNING
yields nothing, EXISTS(gate)
is false, and the balance update is skipped. Postgres statement atomicity means a crash mid-statement leaves nothing applied — so Stripe's retry completes cleanly.
Two details worth stealing:
admin_adjust
grants use free-text ref_id
s from a CLI script, and signup grants have ref_id = NULL
. Both would collide with a naive global unique index. Scoping it to the two Stripe-driven types keeps idempotency where it matters and stays out of the way everywhere else.This one is my favourite, because it's not a concurrency bug at all — it's a modeling bug that only appears once you have two kinds of credit.
My balance has two buckets:
| Bucket | Source | Expires? |
|---|---|---|
monthly_balance |
||
| subscription grant | yes, at cycle end | |
topup_balance |
||
| one-time pack purchase | never |
Spending drains monthly
first, since it expires anyway. Straightforward.
Then a generation fails and we refund. The original code did this:
await db.update(creditsBalance)
.set({ topupBalance: sql`topup_balance + ${amount}` })
.where(eq(creditsBalance.userId, userId));
Refund to topup
. Simple, and wrong in a way that costs real money.
If the charge came out of monthly
— credits that were going to expire in nine days — and the refund lands in topup
, those credits are now permanent. A user can generate, fail, and get refunded into a bucket that never expires. Repeat, and expiring credits quietly convert into a perpetual balance. It's a laundering machine, and every cycle of it is revenue you already recognized and now owe indefinitely.
The fix has two halves.
First, record how the charge split at deduction time. This is where a single statement gets genuinely tricky, because you need the pre-update values to compute the split:
WITH pre AS (
SELECT
CASE WHEN monthly_expires_at IS NOT NULL AND monthly_expires_at <= NOW()
THEN 0 ELSE monthly_balance END AS m,
topup_balance AS t
FROM credits_balance WHERE user_id = $userId
),
upd AS (
UPDATE credits_balance cb
SET monthly_balance = GREATEST(pre.m - $amount, 0),
topup_balance = GREATEST(cb.topup_balance - GREATEST($amount - pre.m, 0), 0)
FROM pre
WHERE cb.user_id = $userId
AND (pre.m + cb.topup_balance) >= $amount
RETURNING cb.monthly_balance, cb.topup_balance
)
SELECT
LEAST($amount, pre.m) AS spent_monthly,
$amount - LEAST($amount, pre.m) AS spent_topup
FROM upd, pre
CTEs see the snapshot from the start of the statement, so pre
still holds the old values even though upd
has already written. That's what makes computing the split possible without a second round trip. Persist spent_monthly
/ spent_topup
on the generation row.
Second, refund each bucket what it gave up — with one caveat:
monthlyBalance: sql`monthly_balance + (
CASE WHEN monthly_expires_at IS NOT NULL AND monthly_expires_at > NOW()
THEN ${refundMonthly} ELSE 0 END
)`,
topupBalance: sql`topup_balance + ${refundTopup}`
If the monthly window already rolled over, those credits would have expired anyway. Resurrecting them into a fresh cycle is the same laundering bug wearing a different hat. So the monthly portion is dropped when the window has closed; the topup portion always refunds.
There's a bonus in that pre
CTE, by the way: CASE WHEN monthly_expires_at <= NOW() THEN 0
means an expired bucket can't satisfy the sufficiency check, can't be spent, and gets swept to zero by GREATEST(pre.m - amount, 0)
— all in the same atomic statement. No cron job needed to clean up expired balances. The next spend does it lazily.
Generation takes 60–90 seconds, so the client polls a status endpoint. Open the app in two tabs and you get two pollers hitting the same task. The provider reports failure. Both pollers see it. Both refund.
Same trick as race 1 — put the invariant in the WHERE
, and use the status column itself as the claim:
const updated = await db.update(generations)
.set({ status: "failed", completedAt: new Date() })
.where(and(
eq(generations.taskId, taskId),
eq(generations.status, "pending"), // <-- the claim
))
.returning({ creditsCharged, spentMonthly, spentTopup });
if (updated.length === 0) return false; // someone else already finalized
await refundCredits(userId, creditsCharged, `gen-failed:${taskId}`, {
monthly: spentMonthly,
topup: spentTopup,
});
Only the poller that successfully flips pending → failed
gets rows back, and only that one issues the refund. Everyone else short-circuits.
Note that returning()
hands back the split recorded during deduction, so the refund lands in the right buckets — race 3 and race 4 fix each other's blind spots.
Four bugs, one shape:
Express the precondition as part of the write, then check whether the write happened.
WHERE balance >= amount
ON CONFLICT DO NOTHING
EXISTS(gate)
WHERE status = 'pending'
Row count becomes your concurrency primitive. You don't need transactions for any of this — you need every operation to be one statement, and every invariant to live inside it.
Three things I'd tell myself at the start:
The system this came from is T-Shirt Design AI, which turns a text prompt into print-ready t-shirt artwork. The billing is the least visible part of it and easily took the most debugging.
If you're building credit billing on Neon, Supabase, or anything else where transactions are awkward — steal the CTE gate pattern. It's the one that saved me the most grief.