av-planner/supabase/migrations/20260910010000_admin_review_queue.sql

-- Admin review queue support, per organized-ideas.md §3:
--   * a soft per-user cap on pending submissions (abuse prevention, §2)
--   * an impact-check an Admin can run before approving an edit to an
--     already-public entry — "how many diagrams reference this, and a short
--     sample" — computed by a privileged, aggregate-only function so regular
--     Admins (who don't have diagram visibility, only Super Admins do, per
--     §6) never see raw diagram content, just the blast-radius numbers.

-- ----------------------------------------------------------------------
-- Per-user pending-submission cap (10 — organized-ideas.md §3's "exact
-- number TBD when this is built").
-- ----------------------------------------------------------------------

drop policy "catalog_submissions_insert" on public.catalog_submissions;

create policy "catalog_submissions_insert" on public.catalog_submissions for insert
  with check (
    submitter_id = auth.uid()
    and status = 'pending'
    and (
      select count(*) from public.catalog_submissions
      where submitter_id = auth.uid() and status = 'pending'
    ) < 10
  );

-- ----------------------------------------------------------------------
-- Usage-impact aggregate function.
--
-- A diagram's `data` JSONB mirrors the exported Diagram shape (see
-- data/exportImport.ts / domain/types.ts): devices[].templateId,
-- devices[].category, devices[].ports[].portTypeId, and
-- connections[].cableTypeId are the four places a catalog entity id can be
-- referenced. security definer so it can read every diagram regardless of
-- the caller's own diagrams RLS visibility — the is_admin() check below is
-- what keeps this from being an open door, and the return shape (a count
-- plus up to 5 {id, name, ownerUsername} samples) is deliberately far short
-- of full diagram content.
-- ----------------------------------------------------------------------

create or replace function public.catalog_entity_usage_impact(p_entity_type text, p_entity_id text)
returns table(diagram_count integer, sample jsonb)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.is_admin() then
    raise exception 'insufficient_privilege' using errcode = '42501';
  end if;

  return query
    select count(*)::int, coalesce(jsonb_agg(jsonb_build_object('id', s.id, 'name', s.name, 'ownerUsername', s.owner_username) order by s.rn) filter (where s.rn <= 5), '[]'::jsonb)
    from (
      select d.id, d.name, p.username as owner_username,
             row_number() over (order by d.updated_at desc) as rn
      from public.diagrams d
      join public.profiles p on p.id = d.owner_id
      where case p_entity_type
        when 'device_template' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev
          where dev ->> 'templateId' = p_entity_id
        )
        when 'device_category' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev
          where dev ->> 'category' = p_entity_id
        )
        when 'port_type' then exists (
          select 1
          from jsonb_array_elements(coalesce(d.data -> 'devices', '[]'::jsonb)) dev,
               jsonb_array_elements(coalesce(dev -> 'ports', '[]'::jsonb)) port
          where port ->> 'portTypeId' = p_entity_id
        )
        when 'cable_type' then exists (
          select 1 from jsonb_array_elements(coalesce(d.data -> 'connections', '[]'::jsonb)) conn
          where conn ->> 'cableTypeId' = p_entity_id
        )
        else false
      end
    ) s;
end;
$$;

-- ----------------------------------------------------------------------
-- Batched submitter-username lookup for the review queue list.
--
-- profiles_select_self_or_super_admin deliberately keeps a regular Admin
-- from browsing other users' profiles directly — but an Admin reviewing a
-- submission already sees its content, so knowing *who* submitted it isn't
-- a bigger exposure than the usage-impact function's owner usernames
-- above; it's just gated the same way (admin-only, minimal fields, no
-- broader profile browsing). Batched (array in, rows out) so listing a
-- whole queue costs one round trip, not one per submission.
-- ----------------------------------------------------------------------

create or replace function public.catalog_submission_submitters(p_submission_ids uuid[])
returns table(submission_id uuid, username text)
language plpgsql
stable
security definer
set search_path = public
as $$
begin
  if not public.is_admin() then
    raise exception 'insufficient_privilege' using errcode = '42501';
  end if;

  return query
    select s.id, p.username
    from public.catalog_submissions s
    join public.profiles p on p.id = s.submitter_id
    where s.id = any(p_submission_ids);
end;
$$;