cd /news/ai-products/why-a-simple-credits-column-breaks-i… · home › topics › ai-products › article
[ARTICLE · art-174366] src=dev.to ↗ pub= topic=ai-products verified=true sentiment=· neutral

Why a Simple `credits` Column Breaks in AI Apps (Two-Phase Holds, FIFO Lots & Stripe Refunds)

A developer outlined a four-table schema (wallets, credit_lots, credit_holds, ledger_events) for handling AI app billing, arguing that a single integer credits column fails under concurrency, provider failures, expiring credits, Stripe refunds and per-action margins. The approach uses two-phase holds (reserve, then settle or refund with a TTL sweep), a FIFO credit-lot ledger that spends soonest-expiring credits first and restores refunds to their original lots, and per-action cost tracking to catch negative-margin generations.

recommendation

Replace read-then-write credit decrements with a locked, atomic reserve/settle/refund hold, and track per-action API cost against your cheapest pack's net price to catch negative-margin generations.

more in playbook: Controlling LLM cost
by read4 min views2 publishedNov 22, 2026

Every AI app that moves past flat subscriptions starts the same way:

ALTER TABLE users ADD COLUMN credits INTEGER NOT NULL DEFAULT 50;

You wire up a Stripe Checkout webhook that runs UPDATE users SET credits = credits + 500, and on every /api/generate route you run:

if (user.credits < 10) return Response.json({ error: 'Out of credits' }, { status: 402 });
await db.query('UPDATE users SET credits = credits - 10 WHERE id = $1', [user.id]);
const result = await callOpenAI(prompt);

It works cleanly in development. Then real users show up, and five failure modes hit back-to-back.

A user with 10 credits double-clicks "Generate" (or opens two tabs and hits both within 40ms). Both requests read credits = 10, both pass if (user.credits < 10), and both fire an expensive video/image generation call.

Now your user got two generations for the price of one—or their balance went negative (-10).

The fix: Never read-then-write in application code. Lock the wallet row inside a single database transaction (SELECT ... FOR UPDATE) or use an atomic conditional decrement before calling the upstream model.

AI providers fail constantly—rate limits, 502s, dropped streams, and 60-second timeouts.

The fix: Two-Phase Holds (Reserve → Settle / Refund)

Model every AI call like a hotel pre-authorization:

1. Reserve  ──▶ Lock wallet row in Postgres, hold 10 credits (status = 'pending', TTL = 5m)
2. Execute  ──▶ Call OpenAI / Anthropic / Replicate / Fal
3a. Success ──▶ Settle hold (deduct exact usage, refund any unused reserved credits)
3b. Error   ──▶ Refund hold (restore credits back to the exact original lots)
3c. Crash   ──▶ Background TTL sweep releases expired holds automatically

status = 'pending', with an expires_at TTL). Almost every mature AI product ends up mixing at least two of these in the same wallet:

If you store all of those in a single users.credits integer, you cannot know which credits should burn first. Worse, if you burn permanent paid credits before expiring free credits, users feel cheated the moment their free allowance expires.

The fix: A FIFO Credit Lot Ledger

Instead of one integer, store credit lots with remaining_amount and expires_at (NULL for permanent packs), and deduct soonest-expiring first:

SELECT id, remaining_amount
FROM credit_lots
WHERE wallet_id = $1
  AND remaining_amount > 0
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY
  expires_at ASC NULLS LAST,
  created_at ASC
FOR UPDATE;

When a hold is refunded after an upstream crash, you restore the exact amounts back to the exact lot_id s they came from—preserving every lot's original expiration date.

Suppose a customer buys a 1,000-credit pack for $20, spends 200 credits, and asks for a 50% refund ($10) in Stripe.

Without lot-level attribution (linking each credit_lots row to its stripe_payment_intent_id / stripe_charge_id), your charge.refunded webhook has no clean way to claw back the exact proportional 500 credits—or freeze the wallet automatically on charge.dispute.created so a chargeback user can't keep burning your GPU bill.

Most AI apps charge one credit currency across multiple features (chat = 1 cr, generate_image = 5 cr, generate_video = 25 cr).

When you discount your largest credit pack (say $29 for 5,000 credits = 0.58¢ per credit), after Stripe's 2.9% + 30¢, a 25-credit video call only nets you ~13¢. If your video model costs 18¢ per run, your best customers lose you money on every single click.

Tracking your_api_cost_cents per action alongside your cheapest pack's effective net price per credit catches negative-margin actions before launch.

If you want to build this yourself, the core schema is 4 tables (wallets, credit_lots, credit_holds, ledger_events) plus a Stripe webhook handler with idempotency keys on event.id.

If you'd rather skip building and maintaining all of that yourself, I packaged this exact architecture into CreditKit—with a Postgres FIFO ledger, two-phase holds, per-action margin alerts, a drop-in paywall, and Stripe Direct Charges straight to your own Stripe account ($0/mo + 3% only when users buy).

In a Next.js App Router route, the entire reserve → execute → settle/refund loop collapses to one call:

// app/api/generate/route.ts
import { credits } from '@/lib/creditkit';
import { getCurrentUser } from '@/lib/current-user';

export async function POST(req: Request) {
  const user = await getCurrentUser();
  if (!user) return Response.json({ error: 'Sign in first' }, { status: 401 });

  const run = await credits.withCrashProofHold(
    { req, userId: user.id, email: user.email, action: 'generate_image' },
    async () => {
      return await callYourAiModel(); // Throws? Credits auto-refund to exact lots.
    }
  );

  if (run.outcome === 'insufficient_credits') {
    return Response.json(run, { status: 402 }); // Opens the paywall automatically
  }
  if (run.outcome !== 'ok') {
    return Response.json({ error: 'Generation failed — credits refunded.' }, { status: 500 });
  }
  return Response.json(run.data);
}

If you want to try the full pattern locally in 2 minutes (including a "Simulate AI crash → auto-refund" button), I open-sourced a clean Next.js App Router starter here:

How are you handling failed LLM runs and credit expiration in your AI apps today? Happy to trade notes in the comments.

── more in #ai-products 4 stories · sorted by recency
── more on @stripe 3 stories trending now
sponsored brought to you by zahid.host 4,200+ EU-deployed projects
reading about agents? ship yours in a single git push.

Run your AI side-project on zahid.host

EU-based hosting, git-push deploys, automatic HTTPS, no cold starts. Free tier with a custom domain — perfect for shipping the agent you just read about.

$git push zahid main
→ Live at https://your-agent.zahid.host ✓
Get free account → Pricing
from €0/mo · no card required
LIVE [news/why-a-simple-credits…] indexed:0 read:4min 2026-11-22 · —