supabase-brand-studio-open-beta

-- Brand Studio — the OPEN BETA's ledger columns: who did it, and once only.
--
-- ADDITIVE AND ONLY ADDITIVE. Run AFTER docs/supabase-brand-studio-billing.sql
-- (which creates brand_studio_credit_ledger). This file adds two nullable-or-
-- defaulted columns and one partial unique index to that ONE table. It drops
-- nothing, renames nothing, rewrites no existing row, and touches no other
-- table. Every statement is `if not exists` / `add column if not exists`, so
-- running it twice is a no-op and running it against a database that already
-- has it is safe.
--
-- WHY IT EXISTS. Open beta (2026-08-18, owner's ask) gave the credit ledger
-- two writers it never had:
--
--   1. A SIGNUP that grants three credits nobody paid for. That grant must
--      happen exactly once per account, ever — not once per signup attempt,
--      not once per browser tab, not once per retry of a request that
--      actually succeeded. Application code cannot promise that: "check then
--      insert" is two statements and a race lives between them.
--
--   2. AN OWNER who can add fifty credits to any account from a dashboard
--      button. A button can be double-clicked, and a fetch can be retried by
--      the browser after the row already landed.
--
-- Both are the same problem — an idempotency key — and Postgres already
-- solves it in this table for Stripe (see the period_key and stripe_event_id
-- indexes in the billing migration). This adds the third key, general enough
-- for both new writers and namespaced per user so a forged or reused token
-- can only ever collide with a grant to the SAME account.
--
-- WHY NOT REUSE period_key. It is unique per (user_id, period_key), so it can
-- express "one signup grant per user" — but it cannot express "one admin
-- top-up per confirmed click", because a user may receive many of those and
-- they would all collide on the one key. Overloading a column that already
-- means "which billing period" with "which button press" would also break the
-- monthly plan grant the moment it ships.

-- ---------------------------------------------------------------- actor ----
-- WHO caused this row: 'system' (a finalizer, a signup, a scheduled job) or
-- 'admin:<email>' (a named human using the dashboard). See the actors block
-- in src/local/credits.mjs, which is the only place those two shapes are
-- written.
--
-- NOT NULL DEFAULT 'system' is deliberate and is the reason this is safe to
-- run on a live table: Postgres 11+ stores the default in the catalogue
-- rather than rewriting every existing row, so this is a metadata-only change
-- however large the table is, and every row written before today reads back
-- as 'system' — which is precisely what those rows were. Nothing is
-- backfilled with a guess.
--
-- Not an enum and not a foreign key to auth.users: an audit trail that
-- disappears when the admin's account is deleted is not an audit trail, and
-- an enum would need a migration every time the vocabulary grows.
alter table public.brand_studio_credit_ledger
  add column if not exists actor text not null default 'system';

-- ------------------------------------------------------- idempotency key ----
-- Null for every row that does not need one (a pull's spend row is already
-- protected by brand_studio_credit_ledger_spend_pull_idx; a Stripe event by
-- its own index). Populated by exactly three writers today:
--
--   open_beta_signup:<user_id>            once per account, forever
--   admin_topup:<user_id>:<request_key>   once per confirmed click
--   no_charge:<pull_id>                   once per pull that was NOT charged
--
-- The shapes live in src/local/credits.mjs (signupGrantEntry,
-- adminTopupEntry, noChargeEntryForPull) rather than being reconstructed at
-- each call site.
alter table public.brand_studio_credit_ledger
  add column if not exists idempotency_key text;

-- The guarantee itself. Partial ("where ... is not null") for the same reason
-- the billing migration's two indexes are: most rows legitimately have no
-- key, and a plain unique constraint over a nullable column already treats
-- NULLs as distinct — the predicate states the intent rather than working
-- around that.
--
-- The SECOND insert with a given key fails at the database with SQLSTATE
-- 23505, which the writing code reads as "this already landed" and reports as
-- success, exactly as the spend path already does for a retried finalizer.
create unique index if not exists brand_studio_credit_ledger_idempotency_idx
  on public.brand_studio_credit_ledger (idempotency_key)
  where idempotency_key is not null;

-- The admin dashboard reads the whole ledger to fold granted-vs-spent per
-- account. The existing (user_id, created_at) index serves a single account's
-- balance; this one keeps the owner's cross-account read cheap as accounts
-- accumulate, and is the only index added for a read rather than a
-- constraint.
create index if not exists brand_studio_credit_ledger_created_idx
  on public.brand_studio_credit_ledger (created_at desc);

-- ------------------------------------------------------------------ RLS ----
-- UNCHANGED, deliberately, and stated so nobody has to diff the policies to
-- be sure. brand_studio_credit_ledger still has exactly one client policy
-- (select your own rows) and ZERO client write policies; every write in this
-- round — the signup grant, the admin top-up, the no-charge marker — is made
-- server-side with the service-role key, which bypasses RLS by design. A
-- ledger a client can write to is not a ledger.
--
-- The two new columns inherit that posture for free: a customer can read
-- `actor` and `idempotency_key` on their OWN rows (which discloses nothing —
-- they already know they got the grant) and can write neither.