supabase-brand-studio-billing

-- Brand Studio — Stripe billing: plans, subscriptions, and the credit ledger.
--
-- ADDITIVE. Run AFTER docs/supabase-brand-studio-auth.sql (needs auth.users)
-- and docs/supabase-brand-studio-pulls.sql (the ledger's `pull_id` lines up
-- with brand_studio_pulls.id, though it is intentionally NOT a foreign key —
-- see the comment on that column below). Nothing here alters an existing
-- table.
--
-- The one structural decision this file exists to enforce (see
-- src/local/credits.mjs): a credit balance is the SUM OF AN APPEND-ONLY
-- LEDGER, never a mutable counter. Owner decision recorded here too: paid
-- tier = plans that bundle credits at a better effective rate, PLUS
-- à-la-carte top-ups. Local pulls stay free forever — the ledger only ever
-- meters hosted pulls (see PULL_COST in credits.mjs).

create extension if not exists pgcrypto;

-- ----------------------------------------------------------------- plans ----
-- The sellable catalog. One row per Stripe Price. `active = false` retires a
-- plan without deleting the row that existing subscriptions still reference.
create table if not exists public.brand_studio_plans (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  -- Stripe's price id (price_...). Unique so a Stripe-side price can never be
  -- wired to two local plans by accident.
  stripe_price_id text not null unique,
  monthly_cents integer not null check (monthly_cents >= 0),
  -- Credits granted per billing period at this plan's rate. See
  -- planGrantEntry() in credits.mjs, which turns this into a ledger row.
  included_credits integer not null default 0 check (included_credits >= 0),
  -- Price of an à-la-carte top-up credit pack bought outside the
  -- subscription, in cents. Zero means this plan does not sell top-ups
  -- separately (e.g. a free/trial tier) — not "top-ups are free".
  credit_topup_cents integer not null default 0 check (credit_topup_cents >= 0),
  active boolean not null default true,
  created_at timestamptz not null default now()
);

-- ---------------------------------------------------------- subscriptions ----
-- One row per user: their CURRENT subscription state, mirrored from Stripe.
-- This table is a cache of Stripe's truth, not a second source of it — every
-- write here should trace back to a webhook event. It is deliberately NOT the
-- ledger: a subscription can be active with zero credits left, and a canceled
-- subscription's already-granted credits still spend normally.
create table if not exists public.brand_studio_subscriptions (
  user_id uuid primary key references auth.users(id) on delete cascade,
  plan_id uuid references public.brand_studio_plans(id),
  -- Stripe Customer and Subscription ids (cus_..., sub_...). Unique so two
  -- local accounts can never be mapped to the same Stripe subscription.
  stripe_customer_id text not null unique,
  stripe_subscription_id text unique,
  -- Mirrors Stripe Subscription.status verbatim (e.g. "active", "trialing",
  -- "past_due", "canceled", "unpaid") rather than a local enum — Stripe adds
  -- statuses over time and re-deriving our own would drift from theirs.
  status text not null default 'incomplete',
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean not null default false,
  updated_at timestamptz not null default now()
);

-- --------------------------------------------------------- credit ledger ----
-- APPEND-ONLY. Every grant, top-up, refund, adjustment, spend and expiry is a
-- row; the balance is a fold over them (see balanceFrom() in credits.mjs).
-- This table has no UPDATE policy for anyone but service_role, and no DELETE
-- policy at all — see RLS below. A row, once written, is history.
create table if not exists public.brand_studio_credit_ledger (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,

  -- Mirrors ENTRY_KINDS in credits.mjs exactly. Keep these two lists in sync
  -- by hand — there is no shared source between SQL and JS to generate this
  -- constraint from.
  kind text not null check (kind in ('plan_grant', 'topup', 'refund', 'adjustment', 'spend', 'expiry')),

  -- Signed per credits.mjs's balanceFrom(): debit kinds (spend, expiry) are
  -- forced negative there regardless of the sign stored here, but credit
  -- kinds (plan_grant, topup, refund, adjustment) KEEP WHATEVER SIGN IS
  -- WRITTEN. A negative `adjustment` or `refund` is a clawback, not a grant —
  -- do not "normalize" this column to always-positive, that is the exact bug
  -- credits.mjs's balanceFrom() comment warns about (Math.abs() on a credit
  -- kind turns a clawback into a grant).
  amount numeric not null,

  reason text not null default '',

  -- Which pull this entry charges for (kind = 'spend' only, otherwise null).
  -- Deliberately not a foreign key to brand_studio_pulls(id): the ledger must
  -- be writable by the webhook/finalizer path even if that migration were
  -- ever rolled back or the pull row is in a different logical lifecycle
  -- (e.g. billing must survive a pull being purged by retention). The
  -- uniqueness guarantee below is what actually matters for correctness, not
  -- referential integrity to the pull row.
  pull_id uuid,

  -- Idempotency key for period-scoped grants (see planGrantEntry() in
  -- credits.mjs) — e.g. "2026-08" for a monthly plan_grant. Null for entries
  -- that are not period-scoped (topup, refund, adjustment, spend).
  period_key text,

  -- The Stripe event id (evt_...) that produced this row, when applicable.
  -- Carrying it lets a webhook handler ask "have I already written this
  -- event" before inserting, on top of the DB-level uniqueness below.
  stripe_event_id text,

  created_at timestamptz not null default now()
);

-- IDEMPOTENCY — the critical part. Stripe redelivers webhooks (network
-- blips, handler timeouts, manual replays from the dashboard); without these
-- two indexes a retried `invoice.paid` grants the plan's credits twice.
-- Postgres has no inline "unique where" column syntax, so both guarantees
-- are separate partial unique indexes rather than table constraints:
--
--   1. One grant per (user, period). Two different Stripe event ids for the
--      same billing period (e.g. a retried invoice with a NEW event id but
--      the SAME period) must still collapse to one row.
--   2. One row per Stripe event id, full stop — catches an exact-same-event
--      redelivery even where no period_key applies (e.g. a topup).
--
-- Both are partial ("where ... is not null") because most rows (spend,
-- adjustment) legitimately have neither value populated, and a plain unique
-- constraint over a nullable column in Postgres already treats NULLs as
-- distinct from each other — the `where` clause here is for readability of
-- intent, not a workaround for that behavior.
create unique index if not exists brand_studio_credit_ledger_user_period_idx
  on public.brand_studio_credit_ledger (user_id, period_key)
  where period_key is not null;

create unique index if not exists brand_studio_credit_ledger_stripe_event_idx
  on public.brand_studio_credit_ledger (stripe_event_id)
  where stripe_event_id is not null;

-- A retried pull finalizer must not double-charge. credits.mjs's
-- debitForPull() is called from a finalizer that can itself run twice (crash
-- and resume, dual-delivery of a completion event, a redeployed worker
-- re-processing the same in-flight pull) — debitForPull() is a pure function
-- with no memory of what it already produced, so nothing in credits.mjs
-- stops two calls for the same pull from both returning a valid 'spend'
-- entry. This partial unique index is what closes that gap: the SECOND
-- insert for the same pull_id fails at the database rather than silently
-- billing the customer twice for one pull.
create unique index if not exists brand_studio_credit_ledger_spend_pull_idx
  on public.brand_studio_credit_ledger (pull_id)
  where pull_id is not null and kind = 'spend';

-- Every balance computation folds a user's rows in created_at order (or at
-- least scans all of them) — this index is what keeps that scan cheap as the
-- ledger grows instead of degrading with account age.
create index if not exists brand_studio_credit_ledger_user_created_idx
  on public.brand_studio_credit_ledger (user_id, created_at);

-- ------------------------------------------------------------------ RLS ----
alter table public.brand_studio_plans enable row level security;
alter table public.brand_studio_subscriptions enable row level security;
alter table public.brand_studio_credit_ledger enable row level security;

-- Plans are the public price list: any authenticated user may read them,
-- nobody but the owner (via the dashboard / migrations, i.e. no policy at
-- all beyond service_role's implicit bypass) writes them from the client.
drop policy if exists brand_studio_plans_select_authenticated on public.brand_studio_plans;
create policy brand_studio_plans_select_authenticated on public.brand_studio_plans
  for select to authenticated using (true);

drop policy if exists brand_studio_subscriptions_select_own on public.brand_studio_subscriptions;
create policy brand_studio_subscriptions_select_own on public.brand_studio_subscriptions
  for select using (user_id = auth.uid());
-- No insert/update/delete policy for subscriptions: this table is written
-- exclusively by the webhook handler running as service_role (which bypasses
-- RLS entirely), same as the ledger below. A client-writable subscription
-- row would let a user grant themselves "active" status without ever paying.

drop policy if exists brand_studio_credit_ledger_select_own on public.brand_studio_credit_ledger;
create policy brand_studio_credit_ledger_select_own on public.brand_studio_credit_ledger
  for select using (user_id = auth.uid());
-- NO insert/update/delete policy exists for any role but service_role, and
-- service_role's writes bypass RLS by design rather than needing a policy
-- here — so this table intentionally has ZERO client write policies. A
-- ledger a client can write to is not a ledger: a user who can insert their
-- own 'plan_grant' row has just given themselves free credits, and a user
-- who can update a 'spend' row has just refunded themselves without a
-- refund. Every write to this table must originate server-side, from code
-- that has verified a real Stripe event or a real completed pull.

-- ------------------------------------------------------------ reference ----
-- Uncertain Stripe field names are called out in src/local/billing-stripe.mjs
-- itself (searchable comments), not silently guessed here. This migration
-- only stores the values already used elsewhere in the app (credits.mjs,
-- brand_studio_pulls) plus the minimum Stripe identifiers needed to trace a
-- row back to a real event or object in the Stripe dashboard.