A credit ledger is a deceptively simple problem. Add on top-up, subtract on usage, refund on dispute. Most teams reach for a balance column, increment it on a webhook, and move on.
That works until your payment processor re-delivers a webhook, your job runner crashes mid-charge, or a refund arrives for a charge you didn't record. Then your balance becomes a story, and the only honest way to fix it is to throw everything away.
We didn't want to write that incident report. So Amy's credits are an append-only ledger, not a balance.
The shape
Every change is an immutable row. The current balance is a derived view.
create table credit_entries (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references auth.users(id),
delta integer not null, -- + topup, - usage
reason text not null, -- 'topup' | 'usage' | 'refund' | 'adjustment'
external_id text unique, -- Paddle event id, job id, ...
metadata jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now()
);
create index on credit_entries (user_id, created_at desc);The unique external_id is what makes Paddle webhook re-deliveries safe. The same event always produces the same row, or it produces nothing.
Writing it from a Server Action
import 'server-only';
import { createServerClient } from '@/lib/supabase/server';
type CreditReason = 'topup' | 'usage' | 'refund' | 'adjustment';
export async function recordCredit(input: {
userId: string;
delta: number;
reason: CreditReason;
externalId: string;
metadata?: Record<string, unknown>;
}): Promise<{ inserted: boolean }> {
const supabase = await createServerClient();
const { error } = await supabase
.from('credit_entries')
.insert({
user_id: input.userId,
delta: input.delta,
reason: input.reason,
external_id: input.externalId,
metadata: input.metadata ?? {},
});
if (error?.code === '23505') return { inserted: false }; // duplicate external_id
if (error) throw error;
return { inserted: true };
}The unique-constraint violation is expected on retry. We swallow it and report inserted: false so callers can branch without alarm.
Reading the balance
create or replace view credit_balances as
select user_id, sum(delta)::int as balance
from credit_entries
group by user_id;That's the entire balance system. There is no row to update, so there is no row to corrupt.
What we gave up
Reads are slightly more expensive (a sum, not a fetch). On the volumes we run, the difference vanishes against a single index seek. If it ever stops vanishing, we'll add a materialized snapshot — but only when measurement says we have to.
That's the takeaway, not the SQL: make the destructive thing impossible, then stop optimizing for the bug you can no longer ship.