supabase-brand-studio-mcp-oauth

-- Read-only MCP OAuth adapter. Apply separately; this file is not an automatic
-- migration. Access/refresh/code values are NEVER stored, only SHA-256 hashes.
-- All mutation RPCs lock the grant first, so owner revocation, code issuance,
-- and refresh rotation serialize on one row. No browser/client RLS policies.
begin;

create table if not exists public.brand_studio_mcp_oauth_grants (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  client_id text not null check (length(client_id) between 1 and 200),
  scopes text[] not null check (scopes = array['read_reports']::text[]),
  resource text not null check (resource ~ '^https://[^/?#]+/api/mcp$'),
  created_at timestamptz not null default now(),
  expires_at timestamptz not null,
  revoked_at timestamptz,
  check (expires_at > created_at and expires_at <= created_at + interval '14 days')
);
create index if not exists brand_studio_mcp_oauth_grants_owner_idx
  on public.brand_studio_mcp_oauth_grants (user_id, created_at desc, id desc);

create table if not exists public.brand_studio_mcp_oauth_codes (
  code_hash text primary key check (code_hash ~ '^[a-f0-9]{64}$'),
  grant_id uuid not null unique references public.brand_studio_mcp_oauth_grants(id) on delete cascade,
  redirect_uri text not null check (length(redirect_uri) <= 2048 and redirect_uri like 'https://%'),
  code_challenge text not null check (code_challenge ~ '^[A-Za-z0-9_-]{43}$'),
  code_challenge_method text not null check (code_challenge_method = 'S256'),
  expires_at timestamptz not null,
  consumed_at timestamptz,
  claim_hash text check (claim_hash ~ '^[a-f0-9]{64}$'),
  issued_at timestamptz,
  check ((consumed_at is null) = (claim_hash is null)),
  check (issued_at is null or consumed_at is not null)
);

create table if not exists public.brand_studio_mcp_oauth_tokens (
  access_hash text primary key check (access_hash ~ '^[a-f0-9]{64}$'),
  refresh_hash text not null unique check (refresh_hash ~ '^[a-f0-9]{64}$'),
  grant_id uuid not null references public.brand_studio_mcp_oauth_grants(id) on delete cascade,
  access_expires_at timestamptz not null,
  refresh_expires_at timestamptz not null,
  rotated_at timestamptz,
  check (access_expires_at <= refresh_expires_at)
);
create index if not exists brand_studio_mcp_oauth_tokens_grant_idx
  on public.brand_studio_mcp_oauth_tokens (grant_id);

alter table public.brand_studio_mcp_oauth_grants enable row level security;
alter table public.brand_studio_mcp_oauth_codes enable row level security;
alter table public.brand_studio_mcp_oauth_tokens enable row level security;
revoke all on public.brand_studio_mcp_oauth_grants, public.brand_studio_mcp_oauth_codes,
  public.brand_studio_mcp_oauth_tokens from public, anon, authenticated;
grant select, insert, update, delete on public.brand_studio_mcp_oauth_grants,
  public.brand_studio_mcp_oauth_codes, public.brand_studio_mcp_oauth_tokens to service_role;

create or replace function public.brand_studio_mcp_oauth_authorize(p_grant jsonb, p_code jsonb)
returns boolean language plpgsql security invoker set search_path = '' as $$
declare
  v_now timestamptz := clock_timestamp();
  v_expiry timestamptz := (p_grant->>'expires_at')::timestamptz;
  v_code_expiry timestamptz := (p_code->>'expires_at')::timestamptz;
begin
  if v_expiry is null or v_expiry <= v_now or v_expiry > v_now + interval '14 days'
      or v_code_expiry is null or v_code_expiry <= v_now or v_code_expiry > v_now + interval '5 minutes'
      or p_code->>'grant_id' is distinct from p_grant->>'id' then return false; end if;
  -- Serialize consent creation per owner, including concurrent approvals from
  -- different clients. Revoked/expired grants remain paginated history only.
  perform pg_advisory_xact_lock(hashtextextended((p_grant->>'user_id')::uuid::text, 0));
  if (select count(*) from public.brand_studio_mcp_oauth_grants
      where user_id = (p_grant->>'user_id')::uuid and revoked_at is null
        and expires_at > clock_timestamp()) >= 20 then return false; end if;
  insert into public.brand_studio_mcp_oauth_grants
    (id, user_id, client_id, scopes, resource, created_at, expires_at)
    values ((p_grant->>'id')::uuid, (p_grant->>'user_id')::uuid, p_grant->>'client_id',
      array(select jsonb_array_elements_text(p_grant->'scopes')), p_grant->>'resource', v_now, v_expiry);
  insert into public.brand_studio_mcp_oauth_codes
    (code_hash, grant_id, redirect_uri, code_challenge, code_challenge_method, expires_at)
    values (p_code->>'code_hash', (p_grant->>'id')::uuid, p_code->>'redirect_uri',
      p_code->>'code_challenge', p_code->>'code_challenge_method', v_code_expiry);
  return true;
end;
$$;

create or replace function public.brand_studio_mcp_oauth_consume_code(
  p_hash text, p_client_id text, p_resource text, p_claim_hash text)
returns boolean language plpgsql security invoker set search_path = '' as $$
declare
  v_grant public.brand_studio_mcp_oauth_grants%rowtype;
begin
  select g.* into v_grant from public.brand_studio_mcp_oauth_grants g
    join public.brand_studio_mcp_oauth_codes c on c.grant_id = g.id
    where c.code_hash = p_hash for update of g;
  if not found or v_grant.revoked_at is not null or v_grant.expires_at <= clock_timestamp()
      or v_grant.client_id is distinct from p_client_id or v_grant.resource is distinct from p_resource
      or v_grant.scopes <> array['read_reports']::text[] then return false; end if;
  update public.brand_studio_mcp_oauth_codes set consumed_at = clock_timestamp(), claim_hash = p_claim_hash
    where code_hash = p_hash and consumed_at is null and expires_at > clock_timestamp();
  return found;
end;
$$;

-- Never raise an exception for refresh replay: returning {ok:false} commits the
-- family revocation. Raising would roll it back and keep stolen tokens alive.
create or replace function public.brand_studio_mcp_oauth_issue_tokens(
  p_kind text, p_hash text, p_claim_hash text, p_client_id text, p_resource text, p_token jsonb)
returns jsonb language plpgsql security invoker set search_path = '' as $$
declare
  v_grant_id uuid;
  v_grant public.brand_studio_mcp_oauth_grants%rowtype;
  v_code public.brand_studio_mcp_oauth_codes%rowtype;
  v_old public.brand_studio_mcp_oauth_tokens%rowtype;
  v_now timestamptz;
  v_access_expiry timestamptz := (p_token->>'access_expires_at')::timestamptz;
  v_refresh_expiry timestamptz := (p_token->>'refresh_expires_at')::timestamptz;
begin
  if p_kind = 'code' then
    select grant_id into v_grant_id from public.brand_studio_mcp_oauth_codes where code_hash = p_hash;
  elsif p_kind = 'refresh' then
    select grant_id into v_grant_id from public.brand_studio_mcp_oauth_tokens where refresh_hash = p_hash;
  else return jsonb_build_object('ok', false); end if;
  select * into v_grant from public.brand_studio_mcp_oauth_grants where id = v_grant_id for update;
  v_now := clock_timestamp();
  if not found or v_grant.revoked_at is not null or v_grant.expires_at <= v_now
      or v_grant.client_id is distinct from p_client_id or v_grant.resource is distinct from p_resource
      or v_grant.scopes <> array['read_reports']::text[] then return jsonb_build_object('ok', false); end if;
  if p_kind = 'code' then
    select * into v_code from public.brand_studio_mcp_oauth_codes where code_hash = p_hash;
    if v_code.consumed_at is null or v_code.issued_at is not null or v_code.expires_at <= v_now
        or p_claim_hash is null or v_code.claim_hash is distinct from p_claim_hash then return jsonb_build_object('ok', false); end if;
  else
    select * into v_old from public.brand_studio_mcp_oauth_tokens where refresh_hash = p_hash;
    if v_old.rotated_at is not null then
      update public.brand_studio_mcp_oauth_grants set revoked_at = v_now where id = v_grant_id;
      return jsonb_build_object('ok', false);
    end if;
    if v_old.refresh_expires_at <= v_now then return jsonb_build_object('ok', false); end if;
  end if;
  if v_access_expiry is null or v_refresh_expiry is null
      or v_access_expiry <= v_now or v_access_expiry > v_now + interval '15 minutes'
      or v_refresh_expiry <= v_now or v_refresh_expiry > v_grant.expires_at
      or v_access_expiry > v_refresh_expiry then return jsonb_build_object('ok', false); end if;
  insert into public.brand_studio_mcp_oauth_tokens
    (access_hash, refresh_hash, grant_id, access_expires_at, refresh_expires_at)
    values (p_token->>'access_hash', p_token->>'refresh_hash', v_grant_id, v_access_expiry, v_refresh_expiry);
  if p_kind = 'code' then
    update public.brand_studio_mcp_oauth_codes set issued_at = v_now where code_hash = p_hash;
  else
    update public.brand_studio_mcp_oauth_tokens set rotated_at = v_now where refresh_hash = p_hash;
  end if;
  return jsonb_build_object('ok', true);
end;
$$;

create or replace function public.brand_studio_mcp_oauth_revoke_token(p_hash text, p_client_id text)
returns boolean language plpgsql security invoker set search_path = '' as $$
begin
  update public.brand_studio_mcp_oauth_grants g set revoked_at = clock_timestamp()
    where g.client_id = p_client_id and g.revoked_at is null and exists (
      select 1 from public.brand_studio_mcp_oauth_tokens t where t.grant_id = g.id
        and (t.access_hash = p_hash or t.refresh_hash = p_hash));
  return true;
end;
$$;

revoke all on function public.brand_studio_mcp_oauth_authorize(jsonb,jsonb),
  public.brand_studio_mcp_oauth_consume_code(text,text,text,text),
  public.brand_studio_mcp_oauth_issue_tokens(text,text,text,text,text,jsonb),
  public.brand_studio_mcp_oauth_revoke_token(text,text) from public, anon, authenticated;
grant execute on function public.brand_studio_mcp_oauth_authorize(jsonb,jsonb),
  public.brand_studio_mcp_oauth_consume_code(text,text,text,text),
  public.brand_studio_mcp_oauth_issue_tokens(text,text,text,text,text,jsonb),
  public.brand_studio_mcp_oauth_revoke_token(text,text) to service_role;
commit;