Subjects

-- better-supabase module: subjects
-- @bs-module subjects@1 managed
-- The app-wide subject registry from config.subjects: subject_title, subject_url, can_subject and can_subject_for for every registered type.
-- Managed by `better-supabase sql add`; re-running it overwrites this file.
-- Change it through `sql.modules` in better-supabase.config.ts and the module's SQL hooks.

create schema if not exists better_supabase;
grant usage on schema better_supabase to anon, authenticated, service_role;
-- The app's subject registry (config.subjects, ADR 0020). Each function runs
-- as the caller, so the subject table's own policies decide what is visible.
create or replace function "better_supabase"."subject_types"()
returns text[]
language sql
immutable
set search_path = ''
as $$
  select '{}'::text[]
$$;
revoke execute on function "better_supabase"."subject_types"() from public, anon;
grant execute on function "better_supabase"."subject_types"() to authenticated, service_role;

create or replace function "better_supabase"."subject_title"(subject_type text, subject_id text)
returns text
language plpgsql
stable
security invoker
set search_path = ''
as $$
begin
  case subject_title.subject_type
    when '' then null;
    else
      return null;
  end case;
  return null;
end;
$$;
revoke execute on function "better_supabase"."subject_title"(text, text) from public, anon;
grant execute on function "better_supabase"."subject_title"(text, text) to authenticated, service_role;

create or replace function "better_supabase"."subject_url"(subject_type text, subject_id text)
returns text
language plpgsql
stable
security invoker
set search_path = ''
as $$
begin
  case subject_url.subject_type
    when '' then null;
    else
      return null;
  end case;
  return null;
end;
$$;
revoke execute on function "better_supabase"."subject_url"(text, text) from public, anon;
grant execute on function "better_supabase"."subject_url"(text, text) to authenticated, service_role;

-- True when the caller can see the subject row and, for an action with a
-- key in config.subjects.<type>.permissions, holds that key in the
-- subject's tenant (or on the record, through the subject's scope).
create or replace function "better_supabase"."can_subject"(subject_type text, subject_id text, action text)
returns boolean
language plpgsql
stable
security invoker
set search_path = ''
as $$
begin
  case can_subject.subject_type
    when '' then null;
    else
      return false;
  end case;
  return false;
end;
$$;
revoke execute on function "better_supabase"."can_subject"(text, text, text) from public, anon;
grant execute on function "better_supabase"."can_subject"(text, text, text) to authenticated, service_role;

-- can_subject for another user, for server code that decides who receives
-- an event. It reads the row past its policies and asks can_user for the
-- action's key in config.subjects.<type>.permissions, so an action without
-- a key is false and a share-link grant never counts. A provider that
-- can't answer for others (no idsWithFor) gives false.
create or replace function "better_supabase"."can_subject_for"(member uuid, subject_type text, subject_id text, action text)
returns boolean
language plpgsql
stable
security definer
set search_path = ''
as $$
begin
  case can_subject_for.subject_type
    when '' then null;
    else
      return false;
  end case;
  return false;
exception
  when feature_not_supported then
    return false;
end;
$$;
revoke execute on function "better_supabase"."can_subject_for"(uuid, text, text, text) from public, anon, authenticated;
grant execute on function "better_supabase"."can_subject_for"(uuid, text, text, text) to service_role;

create schema if not exists better_supabase;
create table if not exists better_supabase.modules (
  name text primary key,
  version integer not null,
  mode text not null,
  installed_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table better_supabase.modules enable row level security;
revoke all on better_supabase.modules from anon, authenticated;
grant select on better_supabase.modules to service_role;

-- better-supabase module: subjects
-- @bs-module-data subjects
-- Rows and settings a schema diff doesn't capture. Run `better-supabase sql data`
-- after the schema migration to put them in a migration.
-- Managed by `better-supabase sql add`; re-running it overwrites this file.
-- Change it through `sql.modules` in better-supabase.config.ts and the module's SQL hooks.

insert into better_supabase.modules (name, version, mode)
values ('subjects', 1, 'managed')
on conflict (name) do update
  set version = excluded.version, mode = excluded.mode, updated_at = now();