Credit Ledger Design for Next.js AI SaaS

Usage-based AI products need two things at once: a balance that can be checked quickly and an audit trail that can explain every change. A single number cannot do both jobs.

NimBuild’s credit system uses a dual-write pattern with three concepts.

The Three Records Behind One Balance

Fast Balance

user.credits stores the current spendable balance. It exists for fast access and simple UI reads.

Spendable Buckets

credit_balance_bucket stores the source, remaining amount, priority, and optional expiry for a grant. A subscription cycle, registration bonus, daily grant, and manual adjustment can coexist without losing their identity.

Deductions consume non-expired buckets by expiry date first. That makes promotional credits expire naturally without making every purchased credit confusing.

Immutable Ledger

credit_ledger records every grant, deduction, refund, adjustment, and expiry. It is the history an admin or support agent uses to reconstruct what happened.

All related records are updated in one database transaction. A partially applied credit mutation is not considered success.

Why Sources Matter

The documented starter credits come from:

Source	Trigger	Amount
Registration bonus	First Firebase user sync	2,000 credits
Daily login	First successful login each Asia/Shanghai day	200 credits, expiring at next midnight
Subscription	Webhook or scheduled grant	Per plan
Admin adjustment	Manual operation	Custom

Different sources have different business meanings. A registration bonus may be free, a subscription grant is earned, and an admin adjustment may be a repair. If they all mutate one opaque number, later decisions become guesswork.

Use Explicit Ledger Reasons

Every ledger entry carries a reason. NimBuild uses names such as:

• registration_bonus
• daily_grant
• subscription_cycle
• subscription_schedule
• adjustment
• refund
• credit_expired
• ai_generation
• ai_generation_refund

When adding a product workflow, avoid a generic “spend” reason. For example, the documented copy-generation charge and a future export workflow should be distinguishable in customer history and admin review.

Expiring Credits Should Be Boring

Expiring grants store expiresAt in their bucket. The /api/cron/credit-expiry route processes expired buckets, subtracts remaining amounts from the fast balance, and writes credit_expired ledger entries.

That gives you three properties:

1. The user sees an accurate balance.
2. The bucket no longer remains spendable.
3. The disappearance is auditable.

Refunds Are Ledger Events

For AI work, NimBuild may deduct credits before calling the provider. If provider work fails, compensation invokes the refund path rather than editing the balance in place. The result is an ai_generation_refund entry tied to the failed operation’s reference.

This distinction matters for trust. Users can see that a failure was compensated, and operators can separate product refunds from provider-failure compensation.

Design Rules for Your Own Workflow

When adding credits to a new feature:

1. Check affordability before provider work.
2. Deduct transactionally.
3. Use a specific consumption reason.
4. Pass a stable reference identifier.
5. Compensate through the refund path on failure.
6. Persist generation or workflow history.
7. Expose the result in admin history.

An auditable ledger is not bureaucracy. It is what lets a small team safely answer billing questions, run promotions, repair data, and add pricing plans without guessing.

A Practical Relational Shape

The implementation pattern fits a normal PostgreSQL deployment. The important rule is that the balance, bucket, and ledger write are bounded by one transaction. A simplified schema looks like this:

create table user_credit_state (
  user_id uuid primary key references "user"(id),
  credits integer not null,
  updated_at timestamptz not null default now()
);

create table credit_balance_bucket (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references "user"(id),
  source_type text not null,
  remaining_amount integer not null check (remaining_amount >= 0),
  priority integer not null default 100,
  expires_at timestamptz
);

create table credit_ledger (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references "user"(id),
  amount integer not null,
  reason text not null,
  reference_id text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index credit_balance_bucket_spend_idx
  on credit_balance_bucket (user_id, expires_at nulls last, priority);

create index credit_ledger_user_created_idx
  on credit_ledger (user_id, created_at desc);

The exact production names and Drizzle definitions are documented in the starter, but the invariants are universal. A negative ledger row must match the buckets consumed. A refund must point back to the operation being compensated. An expiry run must reduce both the bucket and the fast balance.

Deduction Pseudocode

The following example shows the transaction boundary more explicitly than product UI can:

begin;

select credits
from user_credit_state
where user_id = $userId
for update;

-- Stop if the balance cannot satisfy the request.
-- Select buckets ordered by expiry date, then priority.
-- Update each selected bucket's remaining_amount.
-- Insert one negative ledger row for the total.
-- Update user_credit_state.credits by the same total.

commit;

for update is important. Two concurrent generations must not read the same 20 credits and both start provider work. The row lock serializes the affordability decision for that user without locking the entire credits table.

The TypeScript API exposed by the starter wraps these invariants:

const credits = await getUserCredits(userId);
const canAfford = await canUserAfford(userId, creditsNeeded);

if (!canAfford) {
  throw new Error("INSUFFICIENT_CREDITS");
}

await deductCredits(userId, creditsNeeded, "ai_generation", generationId);

The reference identifier should be stable across retries. If the workflow writes a generation row first, use its ID. If provider execution is the next step, that same ID can tie together the charge, history, and any refund.

Admin Repair Needs the Same Rules

Manual adjustments are sometimes necessary. A payment may have failed halfway through provisioning, or support may decide to restore credits after an outage. The repair should still go through a ledger reason such as adjustment, not through an ad hoc SQL update.

For an admin repair, capture:

1. The operator who made the change.
2. The user and balance before the change.
3. The exact amount and reason.
4. A support ticket or incident reference.
5. The resulting balance and ledger entry.

This turns a risky support action into an accountable operational event. It also makes later audits much easier: you can distinguish a bug fix from a goodwill credit or a refund.

Reporting Without Corrupting Operational Tables

Analytics should read from replicas, materialized views, or export jobs. Do not add a reporting column to the live balance row and update it in a separate transaction. Eventually that column will disagree with the ledger.

Useful reporting views include:

• credits granted by source and period
• credits consumed by product workflow
• refund rate by provider and failure type
• expired credits by campaign
• average balance before and after generation

These metrics do not need to mutate accounting state. They can be computed from ledger history while the transactional tables remain small and predictable.

A Deployment Checklist for Ledger Changes

Before shipping a new credit-consuming feature, test four scenarios at minimum:

1. A normal successful generation.
2. A rejected request before deduction.
3. A provider failure after deduction.
4. Two concurrent requests that can only afford one execution.

Then compare four records: fast balance, bucket rows, ledger rows, and generation history. If any one disagrees, the transaction boundary is wrong.

That is the practical meaning of an auditable credit ledger. It is not a design ornament; it is the control system for money-adjacent product usage.

Migration and Rollout

If you are moving an existing product from a bare balance column, avoid a big-bang rewrite. Add the bucket and ledger tables first, then introduce a read-through service that still trusts the old balance while writing every new event to the ledger. Once event capture is stable, backfill buckets and reconcile the fast balance from the ledger.

The backfill should be idempotent. For each historical event, store a migration batch ID in the ledger reference or metadata so running the job again does not create a second grant. Reconcile in small user cohorts, compare the computed balance with the old balance, and stop on any mismatch larger than zero.

Feature flags also help. You can enable ledger-only writes for internal users, then for a percentage of production traffic, and finally deduct through buckets for everyone. During the rollout, keep an emergency path that can pause the new workflow without blocking sign-in or checkout.

Finally, add database constraints even if application code looks correct. Non-negative buckets, non-zero event reasons, foreign keys, and composite indexes make mistakes loud and make common queries predictable. A ledger is only useful while the database refuses impossible states.