# Credit Metering / Usage Tracking Playbook **Portable spec for shipping atomic credit metering on a SaaS app whose unit cost is variable AI / external API spend.** Built from the BKE / Kompozy implementation. Reference repo: `reference app`. --- ## 0. Use this spec **Inputs:** - `SUPABASE_URL` + `SUPABASE_ANON_KEY` + `SUPABASE_SERVICE_ROLE_KEY` - List of billable output types and their real provider unit costs (HeyGen invoice line, OpenAI usage row, fal/Kie invoice, etc.) - Margin multiplier (default: `` → your target gross margin) - Beta tier sentinel balance + founding-tier price (BKE: `999_999` cr / `/mo`) - Daily quota cap if applicable (defaults to "none") **Outputs:** - `credit_pricing` admin matrix (provider_cost + infra_cost + margin → computed `credits_charged`) - `credit_transactions` ledger + `credit_refunds` idempotent refund table - `spend_render_credit(...)` Postgres RPC (atomic, soft-FK, advisory-lock-serialized) - `spendRenderCredit()` / `refundCreditOnTaskFailure()` server helpers - Cost-analytics views (per-user, per-output-type, per-day) — service-role-only - Beta auto-refill + founding-tier BYO bypass - Audit-orphan-charges cron (safety net for NULL-fk debits) --- ## 1. Architecture ``` src/ ├── lib/ │ ├── credits/ │ │ ├── pricing.ts # In-code fallback rate card (consumed by dispatch routes) │ │ ├── pricing-store.ts # Cache layer over credit_pricing table (live admin overrides) │ │ ├── refund.ts # refundCreditOnTaskFailure() — idempotent on taskRunId │ │ ├── post-spend-hooks.ts # Low-balance / depleted notification fire-and-forget │ │ └── ledger-labels.ts # Format → human-readable label for the billing UI │ └── render/ │ └── credits.ts # spendRenderCredit() — RPC call + beta auto-refill │ ├── app/ │ └── api/ │ ├── billing/balance-status/route.ts # GET balance + low-balance flag │ └── render//route.ts # Dispatch: charge BEFORE triggering worker │ └── trigger/ ├── render-.ts # Worker — wraps body in refund-on-failure └── audit-orphan-charges.ts # Hourly cron — sweeps NULL-fk debits ``` **Spend timing rule:** charge in the **dispatch route**, refund in the **worker** on failure. Never refund in the dispatch route — retries would double-refund. --- ## 2. Schema ```sql -- 2.1 credits — per-grant balance rows. FIFO-consumed by the spend RPC. create table credits ( id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id) on delete cascade, balance integer not null check (balance >= 0), source text not null check (source in ('purchase','admin','trial','refund','beta_refill')), created_at timestamptz not null default now() ); create index on credits (user_id) where balance > 0; -- 2.2 credit_transactions — append-only ledger. Soft FK to the output row. create table credit_transactions ( id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id) on delete cascade, amount integer not null, -- negative = debit, positive = credit/refund type text not null check (type in ('debit','credit')), -- 'refund' rejected — use type='credit' + note='refund:' note text, generated_content_id uuid references generated_content(id) on delete set null, -- soft link, see §4 created_at timestamptz not null default now() ); create index on credit_transactions (user_id, created_at desc); create index on credit_transactions (generated_content_id) where generated_content_id is not null; -- 2.3 credit_refunds — idempotent refund table. UNIQUE on task_run_id is the dedup gate. create table credit_refunds ( id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id) on delete cascade, task_run_id text not null, format text not null, amount integer not null check (amount > 0), reason text, refunded_at timestamptz not null default now(), constraint credit_refunds_task_run_id_unique unique (task_run_id) ); -- 2.4 credit_pricing — admin-editable matrix; credits_charged is a generated column. create table credit_pricing ( format_key text primary key, label text not null, notes text, provider_cost_usd numeric(10,6) not null default 0 check (provider_cost_usd >= 0), calls_per_generation integer not null default 1 check (calls_per_generation >= 0), infra_cost_usd numeric(10,6) not null default 0 check (infra_cost_usd >= 0), margin_multiplier numeric(4,2) not null default 2.00 check (margin_multiplier > 0), sort_order integer not null default 100, credits_charged integer generated always as ( greatest( 1, ceil( ((provider_cost_usd * calls_per_generation) + infra_cost_usd) * margin_multiplier / 0.005 )::int ) ) stored, updated_at timestamptz not null default now(), updated_by uuid references auth.users(id) on delete set null ); ``` **Pricing rule (locked):** `credits = ceil( (provider_cost × calls + infra_cost) × margin / )` — so 1 credit ≈ of estimated provider load, and your multiplier yields your target gross margin. Reference: `src/lib/credits/pricing.ts`, `supabase/migrations/00053_credit_pricing.sql`. --- ## 3. Pricing table seed Seed `credit_pricing` with `on conflict (format_key) do update set` that refreshes **metadata only** — never blow away admin cost overrides: ```sql insert into credit_pricing (format_key, label, notes, provider_cost_usd, calls_per_generation, infra_cost_usd, margin_multiplier, sort_order) values ('text', 'Text Post', '1 LLM call ($0.017 loaded)', 0.017500, 1, 0.000000, 2.00, 10), ('image', 'Photo Post', '1 LLM + 1 DALL-E ($0.057 loaded)', 0.057500, 1, 0.000000, 2.00, 20), ('persona_short', 'Persona Short', 'HeyGen 30s + ElevenLabs + Whisper + ffmpeg', 1.310000, 1, 0.090000, 2.00, 70) on conflict (format_key) do update set label = excluded.label, notes = excluded.notes, sort_order = excluded.sort_order; -- DO NOT touch provider_cost_usd / infra_cost_usd / margin_multiplier — admin edits live. ``` **Calibrate against real invoices, not vendor spec sheets.** BKE under-priced HeyGen formats 2.5–9× for two weeks because the seed assumed $0.15–$0.56/render — real invoice data showed $1.31/render average. Audit cost values quarterly. (Lab note 2026-05-05.) --- ## 4. Atomic spend RPC The RPC owns the read → check → write → ledger insert in a single transaction behind a per-user advisory lock. Without the lock, parallel renders (e.g. 5 fan-out children at 224 cr each) can each pass the balance check against the same starting balance and over-debit into a negative position. ```sql create or replace function spend_render_credit( p_user_id uuid, p_amount integer, p_note text, p_generated_content_id uuid default null ) returns table (ok boolean, remaining integer, reason text) language plpgsql security definer set search_path = public as $$ declare v_total integer; v_outstanding integer := p_amount; v_row record; v_gc_id uuid; begin if p_user_id is null then return query select false, 0, 'no_user'::text; return; end if; if p_amount <= 0 then return query select true, 0, null::text; return; end if; perform pg_advisory_xact_lock(hashtext(p_user_id::text)); select coalesce(sum(balance), 0) into v_total from credits where user_id = p_user_id and balance > 0; if v_total < p_amount then return query select false, v_total, 'no_credits'::text; return; end if; -- FIFO cascade across credit rows. for v_row in select id, balance from credits where user_id = p_user_id and balance > 0 order by created_at asc loop exit when v_outstanding <= 0; if v_row.balance >= v_outstanding then update credits set balance = balance - v_outstanding where id = v_row.id; v_outstanding := 0; else update credits set balance = 0 where id = v_row.id; v_outstanding := v_outstanding - v_row.balance; end if; end loop; -- Soft-FK link: if the referenced gc row doesn't exist yet, store NULL. -- Closes the race between debounced client persist and worker dispatch. v_gc_id := null; if p_generated_content_id is not null and exists (select 1 from generated_content where id = p_generated_content_id) then v_gc_id := p_generated_content_id; end if; insert into credit_transactions (user_id, amount, type, note, generated_content_id) values (p_user_id, -p_amount, 'debit', p_note, v_gc_id); return query select true, v_total - p_amount, null::text; end; $$; grant execute on function spend_render_credit(uuid, integer, text, uuid) to service_role; revoke execute on function spend_render_credit(uuid, integer, text, uuid) from anon, authenticated; ``` Reference: `supabase/migrations/00031_credit_atomic_spend.sql` + `supabase/migrations/00035_credit_spend_soft_gc_link.sql`. --- ## 5. Server spend helper + pre-spend validation `src/lib/render/credits.ts` wraps the RPC with three checks the SQL function can't do: 1. **Account-status gate.** Trial-expired / suspended users can't spend even with a positive balance. 2. **Beta auto-refill.** On `reason='no_credits'`, if `profiles.plan = 'beta'`, insert a fresh `BETA_SEED` row and retry the spend once. 3. **Post-spend hooks.** Fire-and-forget low-balance / depleted threshold notifications (must not block the spend path). ```ts export async function spendRenderCredit( userId: string, note: string, amount = 1, generatedContentId?: string, ): Promise { if (!userId) return { ok: false, remaining: 0, reason: 'no_user' }; if (amount <= 0) return { ok: true, remaining: 0 }; const admin = createAdminSupabase(); const { data: gate } = await admin.from('profiles').select('account_status').eq('id', userId).maybeSingle(); if (gate?.account_status === 'trial_expired' || gate?.account_status === 'suspended') { return { ok: false, remaining: 0, reason: 'trial_expired' }; } let { data, error } = await admin.rpc('spend_render_credit', { p_user_id: userId, p_amount: amount, p_note: note, p_generated_content_id: generatedContentId ?? null, }); // ... beta-refill + retry, return shape ... } ``` **Pre-spend balance check (optional UI guard):** before dispatching from the client, hit `/api/billing/balance-status` and surface "insufficient credits" inline — saves a round trip and gives a better UX than waiting for the dispatch route to 402. The RPC is still the source of truth; the UI check is a hint. --- ## 6. Refund-on-failure (worker pattern) Every paid worker wraps its body in a try/catch that refunds before re-throwing. Idempotency is enforced by the `credit_refunds_task_run_id_unique` constraint — retries / operator manual reruns / watchdog re-fires all hit `23505` and bail cleanly. ```ts // src/trigger/render-.ts export const renderXTask = task({ id: 'render-x', run: async (payload, { ctx }) => { try { return await doTheWork(payload); } catch (e) { await refundCreditOnTaskFailure({ userId: payload.userId, taskRunId: ctx.run.id, // Trigger.dev's stable run id format: 'persona_short', amount: payload.creditCost, reason: (e as Error).message, generatedContentId: payload.generatedContentId, }); throw e; } }, }); ``` `src/lib/credits/refund.ts` handles the three-step refund: (1) insert into `credit_refunds` (unique gate), (2) credit oldest active `credits` row, (3) write a positive `credit_transactions` row with `type='credit'` + `note='refund:'`. **Do not** use `type='refund'` — the CHECK constraint rejects it and the row silently never lands. --- ## 7. Beta auto-refill + founding tier **Beta tier** (`profiles.plan='beta'`): when `spend_render_credit` returns `no_credits`, insert a fresh `credits` row at `BETA_SEED` (BKE: `999_999`) with `source='admin'` and retry the RPC once. Logs a transaction so the billing UI shows the refill. **Founding tier** (/mo lifetime, BYO API keys, beta-tier feature lock): bypass `spendRenderCredit` entirely — founding members provide their own provider keys via `getEffectiveApiKeys()`, so there's nothing for the SaaS to meter. The check happens in the dispatch route: ```ts if (await isFoundingTier(userId)) { // Skip spend — user is on BYO keys. } else { const spend = await spendRenderCredit(userId, note, cost, gcId); if (!spend.ok) return Response.json({ error: spend.reason }, { status: 402 }); } ``` Founding-tier signups should be capped (BKE: closed 2026-08-31). Existing founding members keep BYO regardless of any later beta-mode toggle — load-bearing invariant in `getEffectiveApiKeys`. --- ## 8. Daily quota gate (optional) If you need a per-user-per-day cap (anti-abuse, free-trial protection), enforce **before** the spend: ```sql create or replace function check_daily_quota(p_user_id uuid, p_cap integer) returns boolean language sql stable as $$ select coalesce(sum(-amount), 0) < p_cap from credit_transactions where user_id = p_user_id and type = 'debit' and created_at >= date_trunc('day', now() at time zone 'utc'); $$; ``` Call it in `spendRenderCredit` after the account-status gate. Return `reason='rate_limited'` to distinguish from `no_credits` (different UI copy + different support response). --- ## 9. Cost analytics views Three views, all `security invoker`, SELECT granted to `service_role` only (admin dashboard reads via service role): ```sql create view v_admin_cost_by_user as select user_id, sum(-amount) as credits_spent, count(*) as n_debits, max(created_at) as last_spend from credit_transactions where type = 'debit' group by user_id; create view v_admin_cost_by_output_type as select split_part(note, ':', 1) as format_key, sum(-amount) as credits_spent, count(*) as n_debits from credit_transactions where type = 'debit' group by 1; create view v_admin_cost_by_day as select date_trunc('day', created_at) as day, sum(-amount) filter (where type='debit') as debit_credits, sum( amount) filter (where type='credit' and note like 'refund:%') as refund_credits, count(*) filter (where type='debit') as n_debits from credit_transactions group by 1 order by 1 desc; revoke all on v_admin_cost_by_user, v_admin_cost_by_output_type, v_admin_cost_by_day from public, anon, authenticated; grant select on v_admin_cost_by_user, v_admin_cost_by_output_type, v_admin_cost_by_day to service_role; ``` Join `v_admin_cost_by_output_type` to `credit_pricing` (on `format_key`) to compute real-dollar cost = `credits_spent × ` and compare against `provider_cost_usd × n_debits` for live margin tracking. --- ## 10. Audit-orphan-charges cron (safety net) A `credit_transactions` row with `generated_content_id IS NULL` older than 30 minutes is suspect: the spend happened, the worker may have completed (and self-healed its gc row), but the FK never got backfilled. Hourly cron sweeps these and reconciles: ```ts // src/trigger/audit-orphan-charges.ts — runs every 60 min // For each NULL-fk debit older than 30 min: // - Search generated_content for a matching user_id + format + created_at window // - If found: UPDATE credit_transactions SET generated_content_id = // - If not found AND no successful worker run logged: refund via refundCreditOnTaskFailure ``` This is what saved the Vara incident (8 paid blog renders → 0 rows → invisible data loss). See BKE lab note 2026-05-05. --- ## 11. Common pitfalls - **Don't catch all PG errors as `no_credits`.** Map by SQLSTATE. FK violations (`23503`), statement timeouts (`57014`), RLS denials (`42501`) are NOT "out of credits" — the user sees a misleading top-up CTA. Either return raw SQLSTATE or wrap with a soft-FK fallback inside the RPC (the §4 pattern). (Lab note 2026-04-29.) - **Don't tighten the FK between `credit_transactions` and `generated_content`.** Keep `on delete set null` and use the soft-link semantics inside the PG function — `INSERT NULL` when the gc row doesn't exist yet. The transaction log's job is "did the spend happen", not "was the linked output finalized". Tightening the FK turns every fan-out race into a `no_credits` error. - **Don't write float values to integer columns.** PostgREST rejects floats AND its fallback chain. `Math.ceil` / `Math.floor` before any `.eq` / `.gte` / `.lte` against `int` columns. The pricing matrix uses `numeric(10,6)` for cost columns but `integer` for `credits_charged` and balance — float drift will crash the entire query plus its fallbacks. (BKE memory `feedback_postgrest_int_columns.md`.) - **Don't refund in the dispatch route.** Dispatch only charges. Refunds live in the worker, behind the `credit_refunds_task_run_id_unique` idempotency gate — otherwise Trigger.dev retries (or manual operator reruns) double-refund. - **Don't recompute credit cost live from the pricing table.** The `credits_charged` column is computed at seed/edit time as a `stored` generated column. Dispatch routes read it as-is. Admin can override `margin_multiplier` on a specific row without breaking the formula. - **Don't omit the audit-orphan-charges cron.** It's the safety net for the bridge-orphan vector (tab closes mid-render → worker self-heals gc row → ledger fk never backfilled). NULL fks accumulate silently otherwise. - **Don't use `type='refund'` on credit_transactions.** The CHECK constraint accepts only `debit` / `credit`. Refunds are `type='credit'` + `note='refund:'`; the matching `credit_refunds` row keyed on `task_run_id` is the discriminator. - **Don't let beta refill fire on non-`no_credits` reasons.** Beta auto-refill is gated specifically on `row?.reason === 'no_credits'` — refilling on `rate_limited` or `trial_expired` would let an account-status block silently top up and continue spending. --- ## 12. Kickoff prompt > Execute the Credit Metering Playbook at `/templates/s-tier/CREDIT-METERING-PLAYBOOK.md` for this new app. Mirror the BKE implementation. Use the BKE codebase at `/` as the reference — when in doubt, read the BKE file rather than re-deriving. > > Inputs: > - Billable output types + real provider unit costs (paste from latest invoices, not spec sheets): [list] > - Margin multiplier: [default ] > - Beta tier sentinel + founding-tier price: [default 999_999 cr / /mo, or "skip"] > - Daily quota cap: [number, or "none"] > > Pause after the `spend_render_credit` RPC + `credit_refunds` table ship for review. --- **Reference files in BKE:** - `src/lib/credits/pricing.ts` — in-code fallback rate card - `src/lib/credits/pricing-store.ts` — live admin-override cache - `src/lib/credits/refund.ts` — idempotent refund helper - `src/lib/credits/post-spend-hooks.ts` — low-balance notifications - `src/lib/credits/ledger-labels.ts` — billing UI labels - `src/lib/render/credits.ts` — `spendRenderCredit()` wrapper - `src/app/api/billing/balance-status/route.ts` — balance + low-balance flag - `src/trigger/audit-orphan-charges.ts` — hourly NULL-fk sweeper - `supabase/migrations/00030_credit_refunds.sql` — refund table - `supabase/migrations/00031_credit_atomic_spend.sql` — `spend_render_credit` RPC - `supabase/migrations/00035_credit_spend_soft_gc_link.sql` — soft-FK fix - `supabase/migrations/00053_credit_pricing.sql` — admin matrix + seed