I stored AI SaaS credits as a single integer. Then the refunds started.

An engineer at an AI SaaS company rebuilt their billing system after discovering that a single integer column for user credits made it impossible to trace refunds, duplicate charges, and usage history. The original design stored only the current balance, overwriting all transaction details. The replacement uses an append-only ledger table that records every credit change with a reason, reference ID, and idempotency key, enabling full auditability and resolving customer disputes with transaction history.

Once in a while someone on the team writes up a mistake worth keeping. This one is about billing, from the engineer who lived it. The scary part of launching an AI SaaS is not the empty launch day. It's the first week when real people start paying, burning credits, retrying requests, asking for refunds, running cron jobs, and finding every shortcut you left in the billing code. Usage-based billing sounds simple when it's a pricing page. It gets messy when every AI action costs money, every retry can double-charge someone, and every support ticket starts with a number your database cannot explain. My first credit system was one column on the users table: credits . Buy a pack, it goes up. Run a generation, it goes down. I built it in an afternoon and felt clever about how little code it took. It held up right until real money showed up. One Tuesday I opened the billing dashboard to a refund, a chargeback from a name I didn't recognize, and a buyer who'd been charged twice because his request timed out and retried. Then an email landed that I still remember word for word: "Why do I have 37 credits? I bought 500 and I've barely touched this." I opened a psql shell and ran the only query I had: select credits from users where id = '...'; -- 37 Thirty-seven. That was the whole story my database could tell me. Not where the other 463 went. Not when. Not why. Every credits = credits - 1 had overwritten the last number and dropped the reason on the floor. I started a reply with "let me look into this" and realized there was nothing to look into. That email is why I tore the whole thing out and rebuilt it as a ledger. Here's the model, and the exact moment that taught me each part of it. With a counter, a customer's entire financial history with your product is one mutable integer that any code path can stomp on. The grant, the spend, the refund, the bug, the fix for the bug. They all collapse into a single number, and the moment they do, you've thrown away every question worth asking: A single number answers none of them. Once refunds exist, you don't want the balance. You want the list of things that produced it. The fix is boring, which turns out to be the point. You never update a balance again. You write one signed row per change, and you add them up when you need the number. create table credit entries id bigint generated always as identity primary key, user id uuid not null references users id , amount integer not null, -- +grant, -spend, never zero reason text not null, -- 'purchase' | 'generation' | 'refund' | 'signup bonus' | 'clawback' ref type text, -- 'stripe payment' | 'job' | 'credit entry' ref id text, -- the thing that caused this row idempotency key text, -- dedupe retries created at timestamptz not null default now ; create unique index on credit entries idempotency key where idempotency key is not null; No updated at . Rows are written once and never touched again. The balance is a sum: select coalesce sum amount , 0 as balance from credit entries where user id = $1; Remember the 37-credit guy? On the ledger, his mystery is just his history, in order: +500 purchase 2026-05-02 09:14 -463 generation x463 entries 2026-05-02 - 05-09 ----- 37 balance Four hundred and sixty-three generations in a week from someone who'd "barely touched it." I pasted the history into my reply. He wrote back: "oh no. that's my cron job." Closed in one message, because the data could finally speak for itself. This is the one that actually cost me money. Stripe's webhook delivery is at-least-once, not exactly-once. Duplicate deliveries are the contract, not a bug. One afternoon Stripe fired checkout.session.completed , my handler granted 500 credits, my server was slow to send back its 200, so Stripe assumed the delivery failed and fired the same evt id again. Then a third time. The buyer pinged me an hour later: "lol why do I suddenly have 1,500 credits" Same event, same signature, processed three times, and I had paid for it. The tempting fix is to check before you write. Don't. That's a race, and two deliveries can both pass the check: js // DON'T: two concurrent deliveries both read "not seen" and both grant const seen = await db.query.creditEntries.findFirst { where: eq idempotencyKey, eventId , } ; if seen await grant userId, 500 ; Let the database be the referee. Derive a key from the source event and let a unique index throw the duplicate away: await db.insert creditEntries .values { userId, amount: 500, reason: "purchase", refType: "stripe payment", refId: paymentId, idempotencyKey: stripeEventId, } .onConflictDoNothing { target: creditEntries.idempotencyKey, } ; The second and third deliveries hit the index and do nothing. The grant happens once. You didn't have to make your webhook perfectly exactly-once. The data model swallowed the retry for you. Picture a user on hotel wifi mashing "Generate" twice, or someone with two tabs open. They have one credit left. Request A reads the balance: 1. Ten milliseconds later, before A has written anything, Request B reads the balance: 1. Both decide they're fine. Both generate. The balance lands at -1, and they got two images for the price of less than one. The bug is reading the balance in app code and then writing based on a number that's already stale. Do the check and the write in one transaction, so the read only sees committed rows: js await db.transaction async tx = { const { balance } = await tx .select { balance: sql coalesce sum amount , 0 } .from creditEntries .where eq creditEntries.userId, userId ; if balance < cost throw new InsufficientCredits ; await tx.insert creditEntries .values { userId, amount: -cost, reason: "generation", refType: "job", refId: jobId, idempotencyKey: jobId, } ; } ; Run it at serializable, or take a per-user advisory lock, so two transactions can't both clear the check against the same starting balance. Using jobId as the key also means a retried job won't get billed twice. Forty days after a sale, a chargeback rolls in. The credits it paid for were spent two weeks ago. With a counter you're stuck choosing between editing a history you no longer have and pretending it didn't happen. On a ledger you append: insert into credit entries user id, amount, reason, ref type, ref id values $user, -500, 'refund', 'credit entry', $original entry id ; The balance drops honestly, goes negative if it has to, and the new row points straight at the purchase it cancels. Same move when you catch a farmed signup: append a negative clawback instead of reaching into a column and hoping you did the arithmetic right. Refund, chargeback, fraud, a manual fix from support. All the same operation. Write one more row. Boring on purpose, and boring is exactly what you want anywhere near money. At launch, that balance query was 2ms and I never thought about it. Somewhere around four million rows it was 200ms, and the p95 on every endpoint that checked a balance started to crawl. The fix isn't to abandon the ledger. You periodically write a checkpoint row that snapshots the balance up to a point in time, then sum only what came after it. The ledger stays the source of truth, and the checkpoint is a cache you can rebuild any time you stop trusting it. Don't build it before a profiler asks you to. An index on user id, created at carries you further than you'd guess. One more habit worth starting early: the day you pay affiliates or hand out referral bonuses, make the movement double-entry. Every credit out of one account is a credit into another, so the books always sum to zero. The first time mine didn't, it was off by a single cent, and double-entry meant I caught it that afternoon instead of six weeks later in a reconciliation nobody had scheduled. 0.000001 that drift over time. DELETE breaks the audit trail for good.The honest tradeoff: this is more code than credits -= 1 . It's also the difference between reading a customer his own history back and typing "let me look into this" with nothing to look into. We kept rebuilding this same stack for AI products: Stripe checkout, usage-based billing, usage metering, customer credits, refunds, idempotent webhooks, and a ledger that support can actually explain. So we open-sourced it as Harness, MIT-licensed: github.com/velobase/velobase-harness https://github.com/velobase/velobase-harness . If you're building an AI SaaS and don't want your first real users to become your billing test suite, it may save you a few painful weeks. If you've run this in production: how do you handle the SUM-vs-snapshot tradeoff? Running totals, checkpoint rows, a cached balance you reconcile on a cron? I'd genuinely like to hear what held up.