supabase-brand-studio-plan-decisions

-- Brand Studio — the OWNER'S DECISIONS on the upgrade plan: what he approved,
-- what he declined, who decided, and when.
--
-- ADDITIVE AND ONLY ADDITIVE. Run in any order relative to the other
-- migrations: this file creates ONE new table and depends on no existing one.
-- It drops nothing, renames nothing, alters no existing table, and changes no
-- existing policy. Every statement is `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. The owner asked (2026-08-18) to see every proposed addition to
-- the report on his own console and approve it there, before any of it gets
-- built: "add this to the admin/owner page ... so I can visually see it all and
-- approve it first, before we start the plan in full."
--
-- The proposals themselves are CODE, not data — src/local/upgrade-proposals.mjs
-- holds every row with its cost, its evidence and its caveats, reviewed in a
-- commit like the provider registry beside it. What cannot live in code is the
-- decision: it is made by a human, after the code shipped, and it changes
-- without a deploy. So the code carries the DEFAULT status and this table
-- carries the OVERLAY. A decision row for a proposal that no longer exists is
-- ignored by resolveStatus() rather than resurrected, and deleting a decision
-- row returns that proposal to the status the code shipped with — never to
-- nothing.
--
-- WHY A TABLE AND NOT A SINGLE-ROW JSONB DOCUMENT. A jsonb settings document
-- keyed by proposal id was the other candidate and is how a lot of admin
-- settings get stored. It loses here for one reason: approving two proposals
-- from two tabs is a read-modify-write race, and the loser's decision vanishes
-- silently with no error anywhere. A primary key per proposal makes each
-- decision its own row, so two concurrent approvals cannot overwrite each
-- other, and an `on conflict (proposal_id) do update` makes a repeated click
-- land on the same row instead of a second one. That is the same instinct as
-- the credit ledger's idempotency index: let Postgres hold the guarantee rather
-- than hoping the application checked first.
--
-- WHAT THIS TABLE DOES NOT DO. Approving a proposal RECORDS A DECISION. It does
-- not schedule work, does not enable a feature flag, does not change what any
-- pull costs, and does not cause a single provider call. Nothing in the capture
-- path reads this table. It exists so the owner's yes and no are written down
-- with his name on them, and so the Plan tab can total what he has said yes to.

-- ------------------------------------------------------------- the table ----
create table if not exists public.brand_studio_plan_decisions (
  -- The proposal's id from UPGRADE_PROPOSALS in
  -- src/local/upgrade-proposals.mjs (e.g. 'dfs_content_analysis').
  --
  -- TEXT PRIMARY KEY, not a foreign key to anything: the proposal list lives in
  -- code, so there is no table to reference. That also means this column can
  -- hold an id the code no longer defines — after a proposal is renamed or
  -- removed — which is deliberate. The row stays as a record that a decision
  -- was once made, and the reader ignores it. Deleting history to keep a join
  -- clean would be the wrong trade on an audit surface.
  proposal_id text primary key,

  -- 'approved' or 'declined'. The check names the vocabulary at the database
  -- rather than trusting every future writer to remember it.
  --
  -- Only two values, though PROPOSAL_STATUSES in the code has four: 'proposed'
  -- is the absence of a decision (so it is expressed by having no row, not by a
  -- row saying "undecided"), and 'shipped' is a fact about the codebase that a
  -- human cannot declare by clicking — it is set in code when the work lands.
  -- Letting either of those be written here would let the console assert
  -- something untrue about what exists.
  status text not null check (status in ('approved', 'declined')),

  -- WHO decided. The admin's email, as verified server-side against Supabase's
  -- own /auth/v1/user — never a value the browser supplied.
  --
  -- Not a foreign key to auth.users, for the reason the credit ledger's `actor`
  -- column is not one: an audit trail that disappears when the deciding
  -- account is deleted is not an audit trail.
  decided_by text not null,

  -- WHEN. Server clock, never the client's.
  decided_at timestamptz not null default now()
);

-- The Plan tab reads every decision at once to overlay them on the registry —
-- there is one row per proposal and the proposal list is dozens long, so this
-- table is small by construction and needs no index beyond its primary key.
-- Stating that here so the absence of one reads as a decision rather than an
-- oversight.

-- ------------------------------------------------------------------ RLS ----
-- ENABLED WITH ZERO POLICIES. That is not an unfinished migration: it is the
-- whole access model. Row-level security with no policy denies every client
-- read and every client write, and the service-role key bypasses RLS by design
-- — so the ONLY way to read or write this table is server-side, through
-- /api/admin, behind the same gate that already answers 404 to everyone who is
-- not on BRAND_STUDIO_ADMIN_EMAILS.
--
-- The alternative — a select policy for authenticated users — would publish the
-- product roadmap and the owner's commercial decisions to every signed-up
-- customer. There is no version of this table a customer should be able to
-- read, so there is no policy.
alter table public.brand_studio_plan_decisions enable row level security;