How to build a credit system for a Next.js AI app (Stripe + Supabase)

// DON'T: read-then-write has a race condition

const { balance } = await getBalance(userId);

if (balance < cost) throw new Error("insufficient");

await setBalance(userId, balance - cost); // two concurrent requests both pass the check

-- balances: one row per user

create table credit_balances (

  user_id    uuid primary key references auth.users(id) on delete cascade,

  balance    integer not null default 0 check (balance >= 0),

  updated_at timestamptz not null default now()

);



-- append-only ledger = audit log + idempotency guard (see Part 2)

create table credit_ledger (

  id              bigint generated always as identity primary key,

  user_id         uuid not null references auth.users(id),

  delta           integer not null,

  reason          text not null,

  idempotency_key text unique,            -- the secret weapon

  created_at      timestamptz not null default now()

);



create or replace function spend_credits(p_user uuid, p_amount int, p_key text)

returns integer language plpgsql security definer set search_path = public as $$

declare new_balance int;

begin

  update credit_balances

    set balance = balance - p_amount, updated_at = now()

    where user_id = p_user and balance >= p_amount   -- the guard

    returning balance into new_balance;

  if new_balance is null then raise exception 'insufficient_credits'; end if;

  insert into credit_ledger (user_id, delta, reason, idempotency_key)

    values (p_user, -p_amount, 'spend', p_key);

  return new_balance;

end $$;

// app/api/stripe/webhook/route.ts

export async function POST(req: Request) {

  const body = await req.text(); // raw body, required for signature check

  const event = stripe.webhooks.constructEvent(

    body, req.headers.get("stripe-signature")!, process.env.STRIPE_WEBHOOK_SECRET!

  );



  if (event.type === "checkout.session.completed") {

    const s = event.data.object as Stripe.Checkout.Session;

    const userId = s.metadata?.userId;

    const credits = creditsForSession(s); // map your Price to credits

    // idempotencyKey = event.id, so a retry is a no-op

    await grantCredits({ userId, amount: credits, idempotencyKey: event.id });

  }

  return Response.json({ received: true });

}

create or replace function grant_credits(p_user uuid, p_amount int, p_key text)

returns integer language plpgsql security definer set search_path = public as $$

declare new_balance int;

begin

  -- already applied this event? return current balance unchanged.

  if exists (select 1 from credit_ledger where idempotency_key = p_key) then

    select balance into new_balance from credit_balances where user_id = p_user;

    return coalesce(new_balance, 0);

  end if;

  insert into credit_balances (user_id, balance) values (p_user, p_amount)

    on conflict (user_id) do update set balance = credit_balances.balance + p_amount

    returning balance into new_balance;

  insert into credit_ledger (user_id, delta, reason, idempotency_key)

    values (p_user, p_amount, 'purchase', p_key);

  return new_balance;

end $$;

// app/api/jobs/route.ts: spend first, then enqueue

const jobId = crypto.randomUUID();

try {

  await spendCredits({ userId, amount: 5, idempotencyKey: jobId }); // keyed on jobId

} catch (e) {

  return Response.json({ error: "insufficient_credits" }, { status: 402 });

}

await db.from("jobs").insert({ id: jobId, user_id: userId, type, status: "queued", credits_cost: 5 });

// worker.ts: claim, process, refund on failure

const { data: job } = await db.rpc("claim_next_job"); // FOR UPDATE SKIP LOCKED

try {

  const output = await runYourModel(job);

  await db.from("jobs").update({ status: "done", output }).eq("id", job.id);

} catch (err) {

  await db.from("jobs").update({ status: "failed", error: String(err) }).eq("id", job.id);

  await refundCredits({ userId: job.user_id, amount: job.credits_cost,

                        idempotencyKey: `refund:${job.id}` }); // distinct key, never double-refunds

}