Credit database variants

Read this reference after detecting the database. Implement one compatible variant and keep all balance/ledger mutations transactional.

Contents

• Shared invariants
• PostgreSQL and Supabase
• Prisma
• MySQL and PlanetScale
• MongoDB
• Verification

Shared invariants

• Amounts are positive integers at the operation boundary; ledger deltas carry the sign.
• Every logical operation has a unique idempotency key.
• Ledger rows are append-only and link reversals/refunds to the original operation.
• Balance and ledger update in one transaction, or balance is derived from the ledger.
• Reservation status supports pending/captured/released where concurrent or variable-cost work exists.
• Database constraints enforce the chosen no-debt/debt policy.

PostgreSQL and Supabase

alter table public.users
  add column if not exists credit_balance bigint not null default 0,
  add constraint users_credit_balance_nonnegative check (credit_balance >= 0);

create table public.credit_transactions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.users(id),
  idempotency_key text not null unique,
  kind text not null check (kind in (
    'grant', 'reserve', 'capture', 'release', 'refund', 'reverse', 'expire', 'rollover'
  )),
  amount bigint not null check (amount <> 0),
  balance_after bigint not null,
  original_transaction_id uuid references public.credit_transactions(id),
  external_reference text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index credit_transactions_user_created_idx
  on public.credit_transactions (user_id, created_at desc);
create unique index credit_transactions_external_reference_idx
  on public.credit_transactions (external_reference)
  where external_reference is not null;

Implement a database function or application transaction that locks/conditionally updates the balance and inserts the ledger row atomically. For spend/reserve, use a conditional update such as ... where credit_balance >= amount and confirm one affected row inside the transaction.

Apply RLS/grants based on the real auth model. Users may read their own history; only trusted server paths should mutate financial rows.

Prisma

Model the same fields and unique constraints in the provider-compatible Prisma schema. Use an interactive transaction for conditional balance update plus ledger insert. Raw conditional SQL may be necessary for strict atomic spend; do not implement read-then-write in separate calls. JSON defaults and UUID generation differ by database provider, so avoid copying PostgreSQL annotations into MySQL.

MySQL and PlanetScale

Use MySQL-compatible types (char(36)/binary UUID choice, json, datetime) and migrations supported by the project. gen_random_uuid(), jsonb, partial indexes, and ADD COLUMN IF NOT EXISTS are PostgreSQL-specific. Verify foreign-key support/configuration on the actual PlanetScale project. Preserve idempotency with unique indexes and use a transaction or single conditional update supported by the database.

MongoDB

Store balance on the user/account document and ledger in a separate collection with unique indexes on idempotencyKey and optional externalReference. Use a replica-set transaction for balance+ledger atomicity, or a rigorously designed single-document ledger/balance model. A transaction schema alone is insufficient.

Verification

• Concurrent spends cannot cross the permitted balance boundary.
• Replaying an idempotency key returns the original result without another delta.
• Ledger and cached balances reconcile after grants, capture/release, and refund.
• Mutation attempts from ordinary client credentials are denied.
• Migration applies from a clean database and upgrades an existing fixture safely.

Credit payment providers

Read this reference after detecting the existing provider and installed SDK version. Implement one provider path and verify API/event names against current primary documentation.

Contents

• Shared checkout contract
• Stripe
• Lemon-Squeezy
• Dodo Payments
• Webhook processing
• Verification

Shared checkout contract

The browser submits only a server-owned pack key. The server maps that key to provider price/product ID, currency, and credit amount.

POST /api/billing/credit-checkout
authenticated body: { pack: "starter" }
server lookup: starter -> provider product/price -> 100 credits

Attach stable internal account/user ID and pack key as provider metadata. Never accept credits, amount_cents, price IDs outside an allow-list, or user identity as authoritative browser input.

Stripe

Use the installed Stripe SDK’s Checkout Session pattern and a server-owned price mapping. Process the current successful-payment event appropriate to the checkout mode and payment status. Verify stripe-signature over the raw body with the endpoint secret. Store both event ID and payment/session reference as unique identifiers where useful.

Lemon Squeezy

Use the current Lemon Squeezy checkout API/SDK with a server-owned variant mapping and custom_data containing stable account/user ID plus pack key. Verify the webhook signature exactly as current primary docs specify and handle only the paid/order state that guarantees funds.

Dodo Payments

Use the current official dodopayments SDK and Checkout Sessions API with a server-owned product_cart. Store account/user ID plus pack key in metadata. Prefer the official SDK webhook verification helper and current event guide. Dodo also has provider-managed credit capabilities; decide deliberately whether the application ledger or Dodo wallet is the source of truth—do not update both without reconciliation.

Webhook processing

1. Read the raw body and verify signature/timestamp with the provider-supported helper.
2. Insert a webhook-inbox/event row with a unique provider event ID.
3. If processing synchronously, grant credits and mark the event complete in a database transaction. Return non-2xx for transient failure so the provider retries.
4. If acknowledging immediately, first durably enqueue/store the event, then return success and process with retry/dead-letter monitoring.
5. Resolve pack value from the server-owned mapping, not mutable metadata credit amounts alone. Cross-check product/price, currency, amount, payment status, and environment.
6. Grant with the payment/event reference as the ledger idempotency key.
7. Handle refunds/disputes according to the explicit clawback/debt policy and link reversal entries to the purchase.

Do not “always return 200” after a database error in a synchronous handler; that permanently discards the provider’s retry opportunity.

Verification

• Unknown/tampered pack keys are rejected before checkout.
• Test and live product IDs cannot cross environments.
• Valid, invalid, duplicate, concurrent, and out-of-order events behave deterministically.
• A transient database failure is retried or remains in a durable queue.
• Refund/dispute behavior matches the documented balance policy.
• Ledger, cached balance, provider payment, and UI reconcile.