<!--
Stripe Billing + Pricing Tiers Playbook

Part of the free Claude Code template library from BILT AI — https://biltai.io/claude-templates

These are the actual playbooks used to build and ship production SaaS apps with
Claude Code. They're written to be handed to an agent as-is: point Claude at one
and it has the full pattern, the schema, the gotchas, and the "don't do this"
list. Paths, project refs, and org names have been replaced with placeholders
(<your-app>, <supabase-project-ref>, <your-github-org>) — search for "<" and
fill in your own before running.

Free to use, modify, and ship. No attribution required.
-->

# Stripe Billing + Pricing Tiers Playbook

**Portable spec for shipping Stripe Billing (subscriptions + one-time credit packs + customer portal + plan-gated features + idempotent webhook ledger) in a new SaaS app.**

Built from the BKE / Kompozy implementation. Reference repo: `reference app`.

Pairs with `AUTH-ONBOARDING-PLAYBOOK.md` (depends on `profiles.account_status`) and `EMAIL-INFRA-PLAYBOOK.md` (consumes `subscription_canceled` / `payment_failed` trigger events).

---

## 0. Use this spec

**Inputs:**
- `STRIPE_SECRET_KEY` (test + live)
- `STRIPE_WEBHOOK_SECRET` (test + live — separate values per Stripe endpoint)
- `STRIPE_PRICE_*` env vars (one per plan + one per credit pack)
- Plan tier table (price USD, monthly credit allocation, BYO-keys allowed?)
- A founding/launch promo? (lifetime pricing, sunset date, BYO-key carve-out)

**Outputs:**
- `/api/billing/checkout` — creates Stripe checkout session (subscription or one-time pack)
- `/api/billing/portal` — returns customer-portal session URL
- `/api/billing/balance-status` — current plan, credit balance, payment_failed banner state
- `/api/stripe/webhook` — idempotent event handler (subscription lifecycle, invoice payments, packs)
- Stripe Customer ↔ Supabase profile linkage via `profiles.stripe_customer_id`
- Plan-gated feature enforcement helper (`getEffectivePlan(userId)`)
- Credit allocation: initial grant on first invoice, monthly top-up on renewal, refund-on-failure for spend hooks

---

## 1. Architecture

```
src/
├── lib/
│   ├── stripe.ts                  # getStripe() singleton
│   ├── plans.ts                   # PLANS + PACKS tables + planFromPriceId() / packFromPriceId()
│   ├── plan.ts                    # getEffectivePlan() — DB → in-memory plan resolution
│   └── credits/
│       ├── pricing.ts             # per-action credit costs
│       ├── pricing-store.ts       # spend / grant / read balance
│       ├── post-spend-hooks.ts    # observability after successful spend
│       └── refund.ts              # idempotent refund on worker failure
│
├── app/api/
│   ├── stripe/webhook/route.ts    # THE webhook handler (§3)
│   └── billing/
│       ├── checkout/route.ts      # POST → Stripe Checkout session URL
│       ├── portal/route.ts        # POST → Stripe Customer Portal URL
│       └── balance-status/route.ts # GET → { plan, credits, paymentFailedAt }
```

---

## 2. Schema

Reference migrations: `00012_stripe_webhook_idempotency.sql`, `00013_plan_creator_tier.sql`, `00017_plan_history.sql`, `00045_plan_founding_tier.sql`.

```sql
-- profiles columns (extend from Auth playbook §2)
alter table profiles
  add column plan text default 'free'
    check (plan in ('free','founding','creator','pro','agency')),
  add column stripe_customer_id text unique,
  add column payment_failed_at timestamptz,
  add column payment_failed_invoice_id text,
  add column addons jsonb default '[]'::jsonb;

-- subscriptions: 1-per-user live row (Stripe is source of truth)
create table subscriptions (
  user_id uuid primary key references profiles(id) on delete cascade,
  stripe_subscription_id text not null unique,
  stripe_customer_id text not null,
  plan text not null
    check (plan in ('free','founding','creator','pro','agency')),
  status text not null, -- trialing | active | past_due | canceled | unpaid | incomplete
  posts_limit int,
  current_period_start timestamptz,
  current_period_end timestamptz,
  updated_at timestamptz default now()
);

-- Webhook idempotency ledger — written at end of successful handler run
create table stripe_webhook_events (
  event_id text primary key,
  event_type text not null,
  livemode boolean not null,
  processed_at timestamptz default now()
);

-- Credits ledger — every grant/spend rides stripe_event_id for dedupe
create table credits (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references profiles(id) on delete cascade,
  stripe_event_id text unique, -- partial unique: null allowed for manual grants
  balance int not null,
  source text not null,        -- subscription | purchase | manual | refund
  created_at timestamptz default now()
);

create table credit_transactions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references profiles(id) on delete cascade,
  stripe_event_id text unique,
  generated_content_id uuid references generated_content(id) on delete set null, -- soft FK
  amount int not null,
  type text not null check (type in ('credit','debit')),
  note text,
  created_at timestamptz default now()
);

-- Plan change audit trail (DB trigger on profiles.plan updates, refined by webhook)
create table plan_history (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references profiles(id) on delete cascade,
  from_plan text,
  to_plan text not null,
  source text default 'unknown', -- stripe | admin | self-serve | unknown
  created_at timestamptz default now()
);
```

**Hard rule:** the plan CHECK constraint must match `PLANS` in `src/lib/plans.ts` EXACTLY. Adding a plan = migration + code change in the same PR.

---

## 3. The webhook handler — the load-bearing file

Reference: `src/app/api/stripe/webhook/route.ts`.

**Required structure (in order):**

1. **Read raw body** via `await req.text()` — NEVER `.json()`. Stripe signs the raw bytes; JSON-parse mangles the hash.
2. **Verify signature** with `stripe.webhooks.constructEvent(raw, sig, secret)`. 400 on failure.
3. **Livemode guard.** Compare `event.livemode` to `STRIPE_SECRET_KEY.startsWith('sk_live_')`. Reject mismatch with 400. Prevents test events hitting live DB and vice versa.
4. **Idempotency check.** `SELECT event_id FROM stripe_webhook_events WHERE event_id = $1` — early-return 200 `{duplicate: true}` if already processed.
5. **Switch on `event.type`** — handle the 5 events below.
6. **Write `stripe_webhook_events` row at the END** — only after every ledger insert succeeded. Stripe retries (3 days, exponential) will safely re-enter.
7. **Every ledger insert uses `stripe_event_id` UNIQUE + `onConflict: 'stripe_event_id', ignoreDuplicates: true`** — so a partial-failure retry no-ops the already-written rows and re-runs only the failed step.

### 5 events to handle

| Event | What to do |
|---|---|
| `checkout.session.completed` | One-time credit pack only (`session.mode === 'payment'`). Grant credits + insert `credit_packs`/`credit_transactions`/`admin_revenue`. **Skip subscription mode here** — handled by `invoice.paid` to avoid double-credit on first month. |
| `customer.subscription.created` + `.updated` | Find the plan line in `sub.items.data` via `planFromPriceId(price.id)`. Upsert `subscriptions` (by `stripe_subscription_id`). On `active`/`trialing`: update `profiles.plan` + `account_status='active'`. If plan changed from free/null → paid, insert `plan_history` (source='stripe') + exit `trial_signup` / `inactive_7d` email enrollments + enroll `paid_signup`. Sync `profiles.addons` by walking addon line items. |
| `customer.subscription.deleted` | Set `subscriptions.status='canceled'`, `profiles.plan='free'`, clear `payment_failed_at`, clear addons. **Do NOT zero credit balance** — users keep what they paid for (policy decision; document it). Enroll `subscription_canceled` campaign (repeatable=true). |
| `invoice.payment_failed` | Stamp `profiles.payment_failed_at = now()` + `payment_failed_invoice_id`. **Do NOT cut access** — Stripe Smart Retries runs ~1 week. Dashboard renders a "fix payment" banner with portal link. Enroll `payment_failed` campaign (repeatable=true). Final retry exhaustion lands as `customer.subscription.deleted` separately. |
| `invoice.paid` | Monthly refill. Look up plan via `planFromPriceId(line.pricing.price_details.price)`. Insert `credits` (source='subscription') + `credit_transactions` (type='credit', note='Monthly refill: <plan>'). Clear `payment_failed_at`. Exit `payment_failed` enrollment with reason `payment_recovered`. First invoice doubles as activation grant — no separate branch needed. |

**Customer → user resolution:** subscription/invoice events only give Stripe `customer_id`. Walk back via `subscriptions.stripe_customer_id` first (fast path), then fall back to `stripe.customers.retrieve(customerId).metadata.<your_user_id_key>` (the metadata is set at checkout creation in §4).

---

## 4. Checkout + portal

### `/api/billing/checkout`
- `requireUser()` first (auth playbook §3).
- If `profiles.stripe_customer_id` is null, `stripe.customers.create({ email, metadata: { <your_app>_user_id: userId } })` and persist.
- `stripe.checkout.sessions.create({ customer, mode: 'subscription' | 'payment', line_items: [{ price: resolvePriceId(plan), quantity: 1 }], client_reference_id: userId, success_url, cancel_url, allow_promotion_codes: true })`.
- For founding tier: gate on `signups_close_at` env constant — return 403 if past sunset.

### `/api/billing/portal`
- `stripe.billingPortal.sessions.create({ customer: stripe_customer_id, return_url })`. Return the URL. Done. Stripe owns the upgrade/downgrade/cancel UX — do not reimplement.

### `/api/billing/balance-status`
Reference: `src/app/api/billing/balance-status/route.ts`. Returns `{ plan, credits, paymentFailedAt, paymentFailedInvoiceId, subscriptionStatus, currentPeriodEnd }` from DB only — no live Stripe API calls in the hot path.

---

## 5. Plan-gated feature enforcement

Single helper: `getEffectivePlan(userId): Promise<PlanId>` — reads `profiles.plan` + checks `subscriptions.status` is live, falls back to `'free'` if cancelled/unpaid.

**Gate pattern (server-side, every protected route):**
```ts
const plan = await getEffectivePlan(userId);
if (!PLANS[plan].features.includes('autopilot')) {
  return NextResponse.json({ error: 'plan_gate', upgradeTo: 'pro' }, { status: 402 });
}
```

**Client-side:** read from `/api/billing/balance-status`, dim/disable the CTA, link to `/dashboard/settings/billing`. Do NOT trust client-side gates alone — duplicate every gate server-side.

**Founding-tier carve-out** (BKE memory `bke_founding_tier.md`): `getEffectiveApiKeys(userId)` short-circuits `plan === 'founding'` to user-provided BYO keys regardless of the global `betaMode` toggle. This is a load-bearing invariant — founding members were sold "lifetime BYO." Reference: `src/lib/admin.ts`.

---

## 6. Credit allocation pattern

**Grant sources:**
- **Initial / monthly refill** — `invoice.paid` handler upserts `credits { source: 'subscription', stripe_event_id }`. Idempotent on event_id.
- **One-time pack** — `checkout.session.completed` (mode=payment) inserts `credits { source: 'purchase' }`.
- **Manual admin grant** — admin UI inserts with `stripe_event_id = null`, `source = 'manual'`.
- **Refund-on-failure** — when a worker errors after charging, `src/lib/credits/refund.ts` inserts a reverse `credit_transactions` row, idempotent on `taskRunId`.

**Spend hook (the soft-FK rule):** the spend RPC writes `credit_transactions.generated_content_id`. If the parent gc row doesn't exist yet (client-side debounced persist hasn't flushed), the FK must be soft (`on delete set null`) AND the RPC must catch the FK violation and write NULL instead of throwing. Reference: BKE lab note 2026-04-29 — hard FK + caller mapping every PG error to "no credits" cost a beta tester 8 paid renders that looked like a billing bug.

**Hard rule:** `credit_transactions.stripe_event_id` UNIQUE is the only protection against double-grants. Every webhook insert MUST set it.

---

## 7. Payment failed → account_status

`invoice.payment_failed` stamps `profiles.payment_failed_at`. Dashboard `layout.tsx` (auth playbook §6) checks this field — if set, renders a non-blocking banner with "Update payment method" → `/api/billing/portal` redirect.

If a user reaches `customer.subscription.deleted` (Smart Retries exhausted), the handler flips `plan='free'` AND clears `payment_failed_at` (the plan change is now the source of truth). Optionally set `account_status='billing_paused'` for a soft 30-day read-only window before full free-tier downgrade — decision is product-level.

Cross-reference: `AUTH-ONBOARDING-PLAYBOOK.md` §6 enumerates `account_status` values; the Stripe webhook is the only writer for `billing_paused`.

---

## 8. Email triggers (cross-reference)

The webhook is the system-of-record for billing email enrollments. Calls into the email-infra helpers from `src/lib/email/enroll.ts`:

| Stripe transition | Enroll | Exit |
|---|---|---|
| free → paid (subscription.created) | `paid_signup` | `trial_signup`, `inactive_7d`, `trial_ending_3d` (reason: `converted_to_paid`) |
| subscription.deleted | `subscription_canceled` (repeatable) | `paid_signup` (reason: `subscription_canceled`) |
| invoice.payment_failed | `payment_failed` (repeatable) | — |
| invoice.paid (after failed) | — | `payment_failed` (reason: `payment_recovered`) |

All email calls are best-effort — wrap in `try/catch` and `console.warn`; an email-system blip must not break the webhook handler.

---

## 9. Founding-member / lifetime-pricing pattern

**Schema:** add the tier to the CHECK constraint (e.g. `'founding'`). Store the Stripe price ID in its own env (`STRIPE_PRICE_FOUNDING`).

**Sunset enforcement:** `/api/billing/checkout` checks `new Date() > FOUNDING_SUNSET` (env constant, e.g. `2026-08-31`) BEFORE creating the session. Existing members stay grandfathered — Stripe never auto-cancels.

**BYO-key carve-out:** `getEffectiveApiKeys()` short-circuits `plan === 'founding'` to user keys regardless of any global "use managed keys" toggle. Document this as a load-bearing invariant in code comments AND in a memory note.

**Credit allocation:** set founding credits to a very high number (e.g. 999_999) to render as "unlimited" in the UI bar, while still tracking spend for analytics.

Reference: `supabase/migrations/00045_plan_founding_tier.sql`.

---

## 10. Downgrade handling

**Self-serve downgrades go through the Stripe Customer Portal** — never reimplement. The portal posts `customer.subscription.updated` with the new price line; your webhook handler upserts the new plan, and `plan_history` captures the transition with `source='stripe'`.

**Mid-cycle downgrade:** Stripe prorates by default. The next `invoice.paid` fires with the new (smaller) plan's credit allocation; existing balance survives (additive ledger).

**HARD RULE — never cancel-and-recreate a subscription for a plan change.** Use `stripe.subscriptions.update(subId, { items: [{ id: itemId, price: newPriceId }], proration_behavior: 'create_prorations' })`. Cancel-and-recreate loses prorations, double-charges trial users, and emits a misleading `subscription.deleted` event that downgrades the user to free for ~1 second before the new sub fires — long enough to trigger the cancellation email campaign.

---

## 11. Refund flow

Two refund paths:

1. **Credit refund (worker failure)** — automatic. Worker wraps body in `refundCreditOnTaskFailure(taskRunId)` which inserts a credit_transactions row keyed by `taskRunId` (unique). Idempotent across Trigger.dev retries / manual reruns. Reference: `src/lib/credits/refund.ts`.

2. **Stripe refund (cash back)** — admin-initiated only. `stripe.refunds.create({ payment_intent, amount })`. The resulting `charge.refunded` event should fire a `credit_transactions` debit equal to the refunded credit allocation, source='refund'. Do NOT issue Stripe refunds programmatically from user-facing routes — admin gate only.

---

## 12. Common pitfalls

- **❌ Don't `req.json()` before signature verification.** Use `req.text()` → `stripe.webhooks.constructEvent`. JSON-parse mangles the signed bytes.
- **❌ Don't grant credits in BOTH `checkout.session.completed` AND `invoice.paid` for subscription mode.** Subscriptions: skip the checkout event, let `invoice.paid` handle first-month + every renewal uniformly. One-time packs: only `checkout.session.completed` (no invoice).
- **❌ Don't trust client-side success-page UI to mark a subscription active.** The webhook is source of truth. Success page polls `/api/billing/balance-status` until plan flips; never write DB state from the success page.
- **❌ Don't share `STRIPE_WEBHOOK_SECRET` across test and live endpoints.** Two endpoints in Stripe dashboard, two distinct env vars, livemode guard in handler.
- **❌ Don't cancel-and-recreate a subscription for plan changes.** Use `subscriptions.update`. See §10.
- **❌ Don't put a hard FK between `credit_transactions.generated_content_id` and `generated_content.id` without a soft fallback.** Client-side debounced persists race the spend RPC; make the FK `on delete set null` AND have the spend RPC catch FK violations and store NULL. BKE lab note 2026-04-29.
- **❌ Don't return `no_credits` for every PG error from the spend RPC.** Map by SQLSTATE — FK violation, RLS denial, statement_timeout all need distinct surfaces, not "buy more credits."
- **❌ Don't zero credit balance on `subscription.deleted` without a policy decision.** Default: keep balance (additive ledger philosophy). Document either way in code comments.
- **❌ Don't omit the livemode guard.** A test webhook event hitting the live endpoint will silently corrupt prod plan state until you notice.
- **❌ Don't rely on `event.data.object` having `.metadata.<user_id>` for every event type.** Invoice + subscription events only carry customer_id — resolve via §3 walk-back.
- **❌ Don't make the webhook handler non-idempotent on the assumption Stripe won't retry.** Stripe retries on 5xx, timeouts, and any 2xx that takes >30s.

---

## 13. Kickoff prompt

> Execute the Stripe Billing Playbook at `<workspace>/templates/s-tier/STRIPE-BILLING-PLAYBOOK.md` for this new app. Mirror the BKE webhook at `src/app/api/stripe/webhook/route.ts` — when in doubt about an event handler shape, read the BKE file rather than re-deriving.
>
> Inputs:
> - Plan tiers: [name + price USD + monthly credits + features list per tier]
> - Founding/promo tier? [Y/N, sunset date, BYO-key carve-out?]
> - Credit packs: [list, or "none"]
> - Email trigger events to wire: [paid_signup / subscription_canceled / payment_failed]
>
> Pause after the webhook handler + idempotency ledger ship for review (before checkout/portal routes).

---

## 14. Reference files in BKE

- `src/app/api/stripe/webhook/route.ts` — the load-bearing webhook handler
- `src/lib/stripe.ts` — Stripe client singleton
- `src/lib/plans.ts` — PLANS + PACKS + `planFromPriceId()` / `packFromPriceId()`
- `src/lib/plan.ts` — `getEffectivePlan()` resolver
- `src/lib/credits/pricing-store.ts` — spend/grant/read balance
- `src/lib/credits/refund.ts` — idempotent refund-on-failure
- `src/lib/credits/post-spend-hooks.ts` — observability after spend
- `src/app/api/billing/balance-status/route.ts` — DB-only billing snapshot
- `src/app/api/billing/checkout/route.ts` — checkout session creator
- `src/app/api/billing/portal/route.ts` — portal session creator
- `supabase/migrations/00012_stripe_webhook_idempotency.sql` — `stripe_webhook_events` ledger
- `supabase/migrations/00013_plan_creator_tier.sql` — adding tiers to CHECK constraints
- `supabase/migrations/00017_plan_history.sql` — plan transitions audit
- `supabase/migrations/00045_plan_founding_tier.sql` — founding-tier CHECK + carve-out