-- Moderation/search upgrade; apply to ZeyadsStuff-Test after 202611120006_zoom_out.sql. begin; -- Profile directory includes ONLY public approved accounts that are not suspended. create function public.community_search_profiles(p_search text default '',p_limit integer default 24,p_offset integer default 0) returns table(username text,display_name text,bio text,avatar_url text,badges text[]) language plpgsql stable security definer set search_path = '' as $$ begin if p_search is null or char_length(p_search)>60 or p_limit is null or p_limit not between 1 and 50 or p_offset is null or p_offset not between 0 and 10000 then raise exception 'Invalid directory query'; end if; return query select p.username,p.display_name,p.bio,p.avatar_url, coalesce((select array_agg(b.code order by b.code) from public.community_user_badges b where b.user_id=p.user_id),array[]::text[]) from public.community_profiles p join public.hub_access a on a.user_id=p.user_id where p.public_profile and a.status='approved' and not exists(select 1 from private.hub_suspensions s where s.user_id=p.user_id and s.lifted_at is null and (s.suspended_until is null or s.suspended_until>now())) and (p_search='' or position(lower(btrim(p_search)) in lower(p.username))>0 or position(lower(btrim(p_search)) in lower(p.display_name))>0) order by p.username limit p_limit offset p_offset; end $$; -- Moderation is strictly owner-only, reason-required, and audited. Do not return private media URLs. create function public.community_moderate_profile(p_target uuid,p_action text,p_reason text) returns void language plpgsql security definer set search_path = '' as $$ declare v_username text; begin if auth.uid() is null or not exists(select 1 from private.hub_owners where user_id=auth.uid()) then raise exception 'Owner access required' using errcode='42501'; end if; if p_target is null or p_action is null or p_action not in ('clear_bio','clear_avatar','clear_banner','hide_profile') or p_reason is null or char_length(btrim(p_reason)) not between 10 and 500 then raise exception 'Invalid moderation request'; end if; select username into v_username from public.community_profiles where user_id=p_target for update; if v_username is null then raise exception 'Profile not found'; end if; if p_action='clear_bio' then update public.community_profiles set bio='',updated_at=now() where user_id=p_target; elsif p_action='clear_avatar' then update public.community_profiles set avatar_url=null,updated_at=now() where user_id=p_target; elsif p_action='clear_banner' then update public.community_profiles set banner_url=null,updated_at=now() where user_id=p_target; elsif p_action='hide_profile' then update public.community_profiles set public_profile=false,updated_at=now() where user_id=p_target; end if; insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),p_action,jsonb_build_object('user_id',p_target,'username',v_username)::text,btrim(p_reason)); end $$; -- Report details must refer to an existing public profile and reporters must be approved. -- Controls stop unauthenticated reports, self-reporting, duplicate open cases, and bursts. create or replace function public.community_report(p_target uuid,p_category text,p_details text) returns bigint language plpgsql security definer set search_path = '' as $$ declare report_id bigint; begin if auth.uid() is null or not exists(select 1 from public.hub_access a where a.user_id=auth.uid() and a.status='approved') or exists(select 1 from private.hub_suspensions s where s.user_id=auth.uid() and s.lifted_at is null and (s.suspended_until is null or s.suspended_until>now())) then raise exception 'Approved account required' using errcode='42501'; end if; if p_target is null or p_target=auth.uid() or p_category is null or p_category not in ('bio','avatar','banner','behavior','other') or p_details is null or char_length(btrim(p_details)) not between 10 and 2000 then raise exception 'Invalid report'; end if; -- Serialize reports by the same user to prevent concurrent requests evading limits. perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtext(auth.uid()::text)); if not exists(select 1 from public.community_profiles p join public.hub_access a on a.user_id=p.user_id where p.user_id=p_target and p.public_profile and a.status='approved' and not exists(select 1 from private.hub_suspensions s where s.user_id=p.user_id and s.lifted_at is null and (s.suspended_until is null or s.suspended_until>now()))) then raise exception 'Profile is not available for reporting'; end if; if exists(select 1 from private.community_reports r where r.reporter=auth.uid() and r.created_at>now()-interval '60 seconds') then raise exception 'Please wait before sending another report'; end if; if (select count(*) from private.community_reports r where r.reporter=auth.uid() and r.created_at>now()-interval '24 hours')>=5 then raise exception 'Daily report limit reached'; end if; if exists(select 1 from private.community_reports r where r.reporter=auth.uid() and r.target=p_target and r.category=p_category and r.state in ('open','reviewing')) then raise exception 'You already have an active report for this profile and category'; end if; insert into private.community_reports(reporter,target,category,details) values(auth.uid(),p_target,p_category,btrim(p_details)) returning id into report_id; return report_id; end $$; -- Reporters see their own report status, but never staff-only notes, reviews, or other users' reports. create function public.community_my_reports(p_limit integer default 20) returns table(id bigint,target_username text,category text,details text,state text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if auth.uid() is null then raise exception 'Sign in required' using errcode='42501'; end if; if p_limit is null or p_limit not between 1 and 50 then raise exception 'Invalid limit'; end if; return query select r.id,p.username,r.category,r.details,r.state,r.created_at from private.community_reports r left join public.community_profiles p on p.user_id=r.target where r.reporter=auth.uid() order by r.created_at desc limit p_limit; end $$; -- Protect all RPCs from direct table access. No broader table grants are made. revoke all on function public.community_search_profiles(text,integer,integer), public.community_moderate_profile(uuid,text,text), public.community_my_reports(integer) from public,anon,authenticated; grant execute on function public.community_search_profiles(text,integer,integer) to anon,authenticated; grant execute on function public.community_moderate_profile(uuid,text,text), public.community_my_reports(integer) to authenticated; commit;