adaptador-planilhas/migrations/0005_maintenance_roles.sql

-- Papéis e funções de manutenção da homologação.
-- Não altera os schemas auth e storage do Supabase.
-- Não concede BYPASSRLS nem SUPERUSER.
-- Os papéis nascem NOLOGIN. A senha e o LOGIN ficam para um passo autorizado no projeto de homologação.
-- As funções são SECURITY DEFINER, com search_path travado, e EXECUTE só para adaptador_worker.
-- O dono é quem aplica a migration. No Supabase gerenciado esse dono é o postgres,
-- com rolsuper = false e rolbypassrls = true. O BYPASSRLS do dono faz a função
-- atravessar FORCE RLS; SUPERUSER não é necessário para isso. BYPASSRLS não identifica o projeto.
-- adaptador_web e adaptador_worker são NOINHERIT: sem SET ROLE app_user não herdam os grants do papel da aplicação.

do $$ begin
  create role adaptador_web nologin nosuperuser nobypassrls noinherit;
exception when duplicate_object then null;
end $$;

do $$ begin
  create role adaptador_worker nologin nosuperuser nobypassrls noinherit;
exception when duplicate_object then null;
end $$;

grant app_user to adaptador_web;
grant app_user to adaptador_worker;
grant usage on schema public to adaptador_web, adaptador_worker;
grant select on table public.schema_migrations to adaptador_web, adaptador_worker;

create or replace function public.adapter_list_queued_job_ids()
returns table (id uuid)
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select j.id
  from public.spreadsheet_jobs j
  where j.status = 'queued'
  order by j.created_at asc
  limit 5
$$;

create or replace function public.adapter_read_queued_job(p_job uuid)
returns table (user_id uuid, workspace_id uuid, progress jsonb)
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select j.user_id, j.workspace_id, j.progress
  from public.spreadsheet_jobs j
  where j.id = p_job and j.status = 'queued'
$$;

create or replace function public.adapter_worker_context(p_user uuid, p_workspace uuid)
returns table (
  display_name text,
  is_superadmin boolean,
  role text,
  workspace_name text,
  module_enabled boolean,
  allow_upload boolean
)
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select p.display_name,
         p.is_superadmin,
         m.role,
         w.name,
         coalesce(f.enabled, false),
         coalesce(s.allow_non_admin_model_upload, false)
  from public.profiles p
  join public.workspace_members m on m.user_id = p.id and m.workspace_id = p_workspace
  join public.workspaces w on w.id = p_workspace
  left join public.workspace_module_flags f
    on f.workspace_id = w.id and f.module_key = 'spreadsheet_adapter'
  left join public.spreadsheet_adapter_settings s on s.workspace_id = w.id
  where p.id = p_user
$$;

create or replace function public.adapter_list_expired_sources(p_now timestamptz)
returns table (
  id uuid,
  workspace_id uuid,
  temp_storage_key text,
  output_csv_key text,
  output_xlsx_key text
)
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select s.id, j.workspace_id, s.temp_storage_key, s.output_csv_key, s.output_xlsx_key
  from public.spreadsheet_job_sources s
  join public.spreadsheet_jobs j on j.id = s.job_id
  where s.expires_at < p_now and s.status <> 'expired'
$$;

create or replace function public.adapter_mark_source_expired(p_source uuid)
returns void
language plpgsql
security definer
set search_path = pg_catalog
as $$
begin
  update public.spreadsheet_job_sources
  set status = 'expired', output_csv_key = null, output_xlsx_key = null
  where id = p_source and status <> 'expired';
end;
$$;

create or replace function public.adapter_list_processing_jobs()
returns table (id uuid, user_id uuid, workspace_id uuid)
language sql
stable
security definer
set search_path = pg_catalog
as $$
  select j.id, j.user_id, j.workspace_id
  from public.spreadsheet_jobs j
  where j.status = 'processing'
$$;

create or replace function public.adapter_mark_job_interrupted(p_job uuid)
returns void
language plpgsql
security definer
set search_path = pg_catalog
as $$
begin
  update public.spreadsheet_jobs
  set status = 'failed', error_code = 'INTERRUPTED', updated_at = now()
  where id = p_job and status = 'processing';
end;
$$;

revoke all on function public.adapter_list_queued_job_ids() from public, app_user, adaptador_web;
revoke all on function public.adapter_read_queued_job(uuid) from public, app_user, adaptador_web;
revoke all on function public.adapter_worker_context(uuid, uuid) from public, app_user, adaptador_web;
revoke all on function public.adapter_list_expired_sources(timestamptz) from public, app_user, adaptador_web;
revoke all on function public.adapter_mark_source_expired(uuid) from public, app_user, adaptador_web;
revoke all on function public.adapter_list_processing_jobs() from public, app_user, adaptador_web;
revoke all on function public.adapter_mark_job_interrupted(uuid) from public, app_user, adaptador_web;

grant execute on function public.adapter_list_queued_job_ids() to adaptador_worker;
grant execute on function public.adapter_read_queued_job(uuid) to adaptador_worker;
grant execute on function public.adapter_worker_context(uuid, uuid) to adaptador_worker;
grant execute on function public.adapter_list_expired_sources(timestamptz) to adaptador_worker;
grant execute on function public.adapter_mark_source_expired(uuid) to adaptador_worker;
grant execute on function public.adapter_list_processing_jobs() to adaptador_worker;
grant execute on function public.adapter_mark_job_interrupted(uuid) to adaptador_worker;