supabase-brand-studio-byo-keys

-- BRING-YOUR-OWN-KEYS: one encrypted row per account per provider.
--
-- ADDITIVE ONLY. This file creates one new table and its policies. It alters
-- nothing that exists, 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-open-beta.sql.
--
-- WHAT IS STORED, AND WHAT IS NOT.
--
-- `secrets` is a jsonb map of {ENV_VAR_NAME: "bsk1.<iv>.<tag>.<ciphertext>"},
-- every value encrypted with AES-256-GCM by the APPLICATION before it is sent
-- (src/local/byo-keys-crypto.mjs). Postgres never sees a plaintext key and
-- cannot decrypt one: the secret is BRAND_STUDIO_BYO_SECRET, an env var on the
-- server, and it is deliberately not in this database.
--
-- WHY NOT pgcrypto. Encrypting here would mean the secret reaching the
-- database session, and this deployment talks to Supabase over PostgREST with
-- no stored procedures — the secret would travel as a query parameter, which
-- is a secret in a URL, in a log, in a proxy. The tradeoff is written out in
-- full at the top of src/local/byo-keys-crypto.mjs.
--
-- `last4` is stored SEPARATELY and in the clear, on purpose. The settings page
-- has to show a customer which key is on file, and deriving that from the
-- ciphertext would mean decrypting on every page load. Four characters
-- identify a key to the person who owns it and reconstruct nothing.
--
-- WHAT IS DELIBERATELY ABSENT: any column a customer could set that changes
-- what they are charged. Whether a pull is free is decided server-side from
-- whether every required provider resolved to an account key (byoCoversPull),
-- never from a flag on this table.

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

  -- The provider id from BYO_PROVIDERS in src/local/byo-keys.mjs.
  -- Text rather than an enum: adding a provider is then a code change and a
  -- deploy, not a code change and a migration.
  provider text not null,

  -- {ENV_VAR: ciphertext}. Never plaintext. See the header.
  secrets jsonb not null default '{}'::jsonb,

  -- {ENV_VAR: "last four"}. Display only.
  last4 jsonb not null default '{}'::jsonb,

  -- The result of the cheapest real provider call, made BEFORE the row was
  -- written. A key is never stored unvalidated, so this is always 'passed' on
  -- insert; it exists so a LATER background re-check has somewhere to record a
  -- key that has since been revoked, and so the settings page can say
  -- "verified 3 August" instead of "we think this works".
  validated_at timestamptz not null default now(),
  validation_status text not null default 'passed'
    check (validation_status in ('passed', 'failed')),
  validation_reason text not null default '',

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),

  -- ONE ROW PER ACCOUNT PER PROVIDER. The upsert in the settings route targets
  -- this constraint (on_conflict=user_id,provider), so saving a new DataForSEO
  -- password REPLACES the old one rather than accumulating rows that a reader
  -- would then have to pick between. Two rows for one provider is two answers
  -- to "which key runs my pull".
  unique (user_id, provider)
);

create index if not exists brand_studio_byo_keys_user_idx
  on public.brand_studio_byo_keys (user_id);

alter table public.brand_studio_byo_keys enable row level security;

-- ZERO CLIENT POLICIES, and that is the whole security design.
--
-- The same pattern brand_studio_credit_ledger uses for writes, applied here to
-- BOTH directions. RLS is enabled and NO policy grants anon or authenticated
-- anything, so a browser holding a user's own JWT still cannot select this
-- table — 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.
--
-- The reason reads are locked down too, unlike the pulls table: a policy that
-- let a user select their own row would let a compromised or malicious client
-- read back its own ciphertext at will. That ciphertext is useless without the
-- server secret, but "useless without another secret" is a weaker guarantee
-- than "never sent to a browser", and there is no feature that needs the
-- weaker one — the settings page renders from last4, which the server hands it
-- explicitly.
--
-- A policy added here later must be justified against that sentence.

-- Keep updated_at honest without the application having to remember.
create or replace function public.brand_studio_byo_keys_touch()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists brand_studio_byo_keys_touch on public.brand_studio_byo_keys;
create trigger brand_studio_byo_keys_touch
  before update on public.brand_studio_byo_keys
  for each row execute function public.brand_studio_byo_keys_touch();

comment on table public.brand_studio_byo_keys is
  'Customer-supplied provider credentials, encrypted app-side (AES-256-GCM, src/local/byo-keys-crypto.mjs). RLS on with no client policies: service role only. A pull that resolves every required provider to a row here is charged 0 credits.';