supabase-brand-studio-api-keys

-- Brand Studio — the "doorknob" round (owner-direction-2026-08-18):
-- service-account API keys, and per-pull idempotency.
--
-- ADDITIVE ONLY. This file creates ONE new table (brand_studio_api_keys) and
-- its policies, plus ONE new index on a table that already exists
-- (brand_studio_pulls, from docs/supabase-brand-studio-pulls.sql). It alters
-- no existing column, drops nothing, and backfills nothing — applying it to a
-- live project changes the behaviour of no existing query. Same posture as
-- docs/supabase-brand-studio-byo-keys.sql and docs/supabase-brand-studio-
-- open-beta.sql.
--
-- Two rows on src/local/upgrade-proposals.mjs (tier 0, provider in_house,
-- source owner-direction-2026-08-18) are what this file exists to serve:
--
--   api_keys_service_accounts — "A way to authorise a script or an agent
--   without handing it a human's login." The table below.
--
--   pull_idempotency_keys — "Certainty that a request sent twice ... produces
--   one report and one charge." The index at the bottom.
--
-- ======================================================================
-- PART 1 — brand_studio_api_keys
-- ======================================================================
--
-- ONE ROW PER ISSUED KEY. `bsk_live_<random>` is shown to the caller exactly
-- once, at creation (hosted-app/lib/api-keys-store.mjs's createApiKey); this
-- table never stores that value. It stores the SHA-256 digest instead
-- (src/local/api-keys.mjs's hashApiKey), which is a ONE-WAY function —
-- unlike brand_studio_byo_keys' AES-256-GCM ciphertext, there is no secret
-- anywhere that decrypts this column back to a usable key. See
-- src/local/api-keys.mjs's header for the full reasoning on why a service
-- account's identity is hashed rather than encrypted, and why that is a
-- different problem from a customer's provider credentials.
--
-- `last4` is stored separately, in the clear, for display — same reasoning
-- as brand_studio_byo_keys' last4: four characters identify a key to the
-- person who owns it and reconstruct nothing.

create table if not exists public.brand_studio_api_keys (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,

  -- A label the owner chose ("CI pipeline", "my agent"). Free text, cleaned
  -- and length-capped by cleanKeyName() before it ever reaches this column.
  name text not null default 'unnamed key',

  -- THE WHOLE SCOPE VOCABULARY IS TWO STRINGS. Mirrors API_KEY_SCOPES in
  -- src/local/api-keys.mjs exactly — keep these two lists in sync by hand,
  -- there is no shared source between SQL and JS to generate this constraint
  -- from (same caveat brand_studio_credit_ledger's `kind` check carries).
  -- `<@` is Postgres's array-containment operator: every element of `scopes`
  -- must be one of the two named here. A key created with an empty array is
  -- refused by the APPLICATION before it reaches this insert (createApiKey's
  -- no_valid_scopes reason) — the constraint below is the backstop, not the
  -- first line of defence.
  scopes text[] not null default '{}'::text[]
    check (scopes <@ array['run_pulls', 'read_reports']::text[]),

  -- SHA-256 hex digest, 64 characters. The ONLY form of the key ever
  -- persisted. Unique so two keys can never hash-collide onto the same row —
  -- a practical impossibility for SHA-256, stated here as a real constraint
  -- rather than assumed.
  key_hash text not null unique,

  -- Last four characters of the plaintext, in the clear. Display only.
  last4 text not null default '',

  created_at timestamptz not null default now(),

  -- Set by touchApiKeyLastUsed() on every successful authentication. Null
  -- means "issued, never used" — a real and useful fact (a key nobody has
  -- ever called might be safe to revoke), not an absence to hide.
  last_used_at timestamptz,

  -- Null while active. Set once, forever — there is no un-revoke, only
  -- issuing a new key. A revoked key's row is kept rather than deleted, so
  -- "when was this cut off" stays answerable after the fact.
  revoked_at timestamptz
);

create index if not exists brand_studio_api_keys_user_idx
  on public.brand_studio_api_keys (user_id);

-- The lookup findApiKeyByHash() performs on every request bearing an API-key-
-- shaped bearer token. `key_hash` is already unique above, which Postgres
-- backs with an index automatically — this comment exists so a reader does
-- not go looking for a second index that would be redundant.

alter table public.brand_studio_api_keys enable row level security;

-- ZERO CLIENT POLICIES — the identical posture brand_studio_byo_keys uses,
-- for the identical reason. RLS is enabled and NO policy grants anon or
-- authenticated anything, so a browser holding a user's own JWT cannot select
-- this table at all — not their own row, not anyone's. Only the service
-- role, which bypasses RLS, can read or write it, and the only code holding
-- that key is the server (hosted-app/lib/api-keys-store.mjs).
--
-- Reads are locked down too, same reasoning as byo-keys: findApiKeyByHash
-- must work for a caller who does not yet know who they are (it is resolving
-- IDENTITY from a hash), which is the opposite of what an auth.uid()-scoped
-- policy could ever answer, and the settings page renders from the app-level
-- listApiKeys() call, which the server already scopes to the signed-in user.
--
-- A policy added here later must be justified against that sentence.

comment on table public.brand_studio_api_keys is
  'Service-account API keys ("bsk_live_..."), hashed SHA-256 (src/local/api-keys.mjs). RLS on with no client policies: service role only. Scoped to run_pulls and/or read_reports; resolveApiKeyContext (hosted-app/lib/comprehensive-pull.mjs) is the one reader.';

-- ======================================================================
-- PART 2 — per-pull idempotency
-- ======================================================================
--
-- ROW pull_idempotency_keys. "A retry that does not charge twice ... The
-- queue already leases jobs, so the dedupe has somewhere natural to live."
--
-- The key travels in `brand_studio_pulls.signals.idempotency_key` — a jsonb
-- field on a column that already exists (docs/supabase-brand-studio-
-- pulls.sql), not a new one, for the identical reason the queue envelope and
-- the pull tier ride there: it needs no migration beyond this index.
--
-- SCOPED TO THE ACCOUNT, not globally — the upgrade-proposals row is
-- explicit: "two customers must be able to send the same key without
-- colliding." The composite (user_id, ...) is what makes that true.
--
-- THE RACE THIS CLOSES. authorizeAndReserveComprehensivePull
-- (hosted-app/lib/comprehensive-pull-handler.mjs) checks for an existing row
-- with this key BEFORE inserting, as a fast path — but "check then insert" is
-- two statements, and two concurrent requests carrying the same key can both
-- pass the check before either has inserted. This partial unique index is
-- what makes the SECOND insert fail at the database with SQLSTATE 23505
-- rather than silently creating two pulls (and, eventually, two charges) for
-- one idempotency key — the identical shape
-- brand_studio_credit_ledger_idempotency_idx closes for the credit ledger,
-- applied here to the row a retried agent actually resends.
--
-- Partial ("where ... is not null") for the same reason every other
-- idempotency index in this repo is: most pulls carry no idempotency key at
-- all, and a plain unique constraint over a nullable expression already
-- treats NULLs as distinct — the predicate states the intent rather than
-- working around that behaviour.
create unique index if not exists brand_studio_pulls_idempotency_idx
  on public.brand_studio_pulls (user_id, (signals ->> 'idempotency_key'))
  where signals ->> 'idempotency_key' is not null;