-- Community foundation. Apply AFTER 202611120002_admin_moderation.sql in a test project. begin; create table public.community_profiles ( user_id uuid primary key references auth.users(id) on delete cascade, username text unique not null check(username ~ '^[a-z0-9_]{3,24}$'), display_name text not null check(char_length(display_name) between 1 and 60), bio text not null default '' check(char_length(bio) <= 500), avatar_url text check(avatar_url is null or (char_length(avatar_url) <= 1024 and avatar_url ~ '^https://')), banner_url text check(banner_url is null or (char_length(banner_url) <= 1024 and banner_url ~ '^https://')), public_profile boolean not null default false, updated_at timestamptz not null default now() ); alter table public.community_profiles enable row level security; revoke all on public.community_profiles from public,anon,authenticated; create table public.community_badges ( code text primary key check(code ~ '^[a-z0-9_-]{2,40}$'), label text not null, description text not null ); create table public.community_user_badges ( user_id uuid not null references auth.users(id) on delete cascade, code text not null references public.community_badges(code), assigned_by uuid references auth.users(id) on delete set null, assigned_at timestamptz not null default now(), primary key(user_id,code) ); alter table public.community_badges enable row level security; alter table public.community_user_badges enable row level security; revoke all on public.community_badges,public.community_user_badges from public,anon,authenticated; insert into public.community_badges(code,label,description) values ('owner','Owner','Website owner'),('administrator','Administrator','Site administration'), ('developer','Developer','Project contributor'),('moderator','Moderator','Community moderation'), ('beta-tester','Beta Tester','Early feature testing'),('bug-hunter','Bug Hunter','Reported useful bugs'), ('early-member','Early Member','Joined during launch'),('contributor','Contributor','Helped the project'); create table private.community_reports ( id bigint generated always as identity primary key, reporter uuid not null references auth.users(id) on delete cascade, target uuid references auth.users(id) on delete set null, category text not null check(category in ('bio','avatar','banner','behavior','other','appeal')), details text not null check(char_length(btrim(details)) between 10 and 2000), state text not null default 'open' check(state in ('open','reviewing','resolved','dismissed')), staff_note text not null default '' check(char_length(staff_note) <= 2000), reviewed_by uuid references auth.users(id) on delete set null, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create table private.community_audit ( id bigint generated always as identity primary key, actor uuid not null, action text not null, subject text not null, detail text, created_at timestamptz not null default now() ); alter table private.community_reports enable row level security; alter table private.community_audit enable row level security; revoke all on private.community_reports,private.community_audit from public,anon,authenticated; create table public.community_announcements ( id bigint generated always as identity primary key, title text not null check(char_length(title) between 1 and 120), body text not null check(char_length(body) between 1 and 2000), starts_at timestamptz not null default now(), ends_at timestamptz, created_by uuid not null references auth.users(id), check(ends_at is null or ends_at > starts_at) ); create table public.community_flags ( key text primary key check(key ~ '^[a-z0-9_-]{2,64}$'), enabled boolean not null default false, description text not null default '', changed_at timestamptz not null default now() ); alter table public.community_announcements enable row level security; alter table public.community_flags enable row level security; revoke all on public.community_announcements,public.community_flags from public,anon,authenticated; create function public.community_profile(p_username text) returns table(user_id uuid,username text,display_name text,bio text,avatar_url text,banner_url text,joined_at timestamptz,badges text[]) language sql stable security definer set search_path = '' as $$ select p.user_id,p.username,p.display_name,p.bio,p.avatar_url,p.banner_url,a.created_at, 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.username=lower(p_username) and (p.public_profile or p.user_id=auth.uid()) 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())); $$; create function public.community_my_profile() returns table(user_id uuid,username text,display_name text,bio text,avatar_url text,banner_url text,public_profile boolean) language sql stable security definer set search_path = '' as $$ select p.user_id,p.username,p.display_name,p.bio,p.avatar_url,p.banner_url,p.public_profile from public.community_profiles p where p.user_id=auth.uid(); $$; create function public.community_save_profile(p_username text,p_display_name text,p_bio text, p_avatar_url text default null,p_banner_url text default null,p_public_profile boolean default false) returns void language plpgsql security definer set search_path = '' as $$ 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_username is null or p_username !~ '^[a-z0-9_]{3,24}$' or p_display_name is null or char_length(btrim(p_display_name)) not between 1 and 60 or p_bio is null or char_length(p_bio)>500 or p_public_profile is null or (p_avatar_url is not null and (char_length(p_avatar_url)>1024 or p_avatar_url !~ '^https://')) or (p_banner_url is not null and (char_length(p_banner_url)>1024 or p_banner_url !~ '^https://')) then raise exception 'Invalid profile field'; end if; insert into public.community_profiles(user_id,username,display_name,bio,avatar_url,banner_url,public_profile) values(auth.uid(),p_username,btrim(p_display_name),p_bio,p_avatar_url,p_banner_url,p_public_profile) on conflict(user_id) do update set username=excluded.username,display_name=excluded.display_name, bio=excluded.bio,avatar_url=excluded.avatar_url,banner_url=excluded.banner_url, public_profile=excluded.public_profile,updated_at=now(); end $$; create 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 then raise exception 'Sign in required' using errcode='42501'; end if; if p_category is null or p_category not in ('bio','avatar','banner','behavior','other','appeal') or p_details is null or char_length(btrim(p_details)) not between 10 and 2000 then raise exception 'Invalid report'; 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 $$; create function public.community_admin(p_operation text,p_payload jsonb default '{}'::jsonb) returns jsonb language plpgsql security definer set search_path = '' as $$ declare v_id bigint; v_user uuid; v_code text; v_state text; v_action text; begin if auth.uid() is null or not exists(select 1 from private.hub_owners o where o.user_id=auth.uid()) then raise exception 'Owner access required' using errcode='42501'; end if; if p_payload is null or jsonb_typeof(p_payload)<>'object' then raise exception 'Invalid payload'; end if; if p_operation='assign_badge' or p_operation='remove_badge' then v_user := (p_payload->>'user_id')::uuid; v_code := p_payload->>'code'; if v_user is null or not exists(select 1 from public.community_badges where code=v_code) then raise exception 'Invalid badge'; end if; if p_operation='assign_badge' then insert into public.community_user_badges(user_id,code,assigned_by) values(v_user,v_code,auth.uid()) on conflict do nothing; else delete from public.community_user_badges where user_id=v_user and code=v_code; end if; v_action:=p_operation; elsif p_operation='moderate_bio' then v_user := (p_payload->>'user_id')::uuid; if v_user is null or char_length(coalesce(p_payload->>'bio',''))>500 then raise exception 'Invalid bio'; end if; update public.community_profiles set bio=coalesce(p_payload->>'bio',''),updated_at=now() where user_id=v_user; if not found then raise exception 'Profile not found'; end if; v_action:=p_operation; elsif p_operation='review_report' then v_id := (p_payload->>'id')::bigint; v_state:=p_payload->>'state'; if v_state is null or v_state not in ('open','reviewing','resolved','dismissed') or char_length(coalesce(p_payload->>'note',''))>2000 then raise exception 'Invalid report update'; end if; update private.community_reports set state=v_state,staff_note=coalesce(p_payload->>'note',''), reviewed_by=auth.uid(),updated_at=now() where id=v_id; if not found then raise exception 'Report not found'; end if; v_action:=p_operation; elsif p_operation='set_flag' then if (p_payload->>'key') !~ '^[a-z0-9_-]{2,64}$' or jsonb_typeof(p_payload->'enabled')<>'boolean' then raise exception 'Invalid flag'; end if; insert into public.community_flags(key,enabled) values(p_payload->>'key',(p_payload->>'enabled')::boolean) on conflict(key) do update set enabled=excluded.enabled,changed_at=now(); v_action:=p_operation; elsif p_operation='announce' then if char_length(coalesce(p_payload->>'title','')) not between 1 and 120 or char_length(coalesce(p_payload->>'body','')) not between 1 and 2000 then raise exception 'Invalid announcement'; end if; insert into public.community_announcements(title,body,created_by) values(p_payload->>'title',p_payload->>'body',auth.uid()) returning id into v_id; v_action:=p_operation; else raise exception 'Unknown operation'; end if; insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),v_action,left(p_payload::text,300),null); return jsonb_build_object('ok',true,'id',v_id); end $$; create function public.community_admin_reports(p_limit integer default 50) returns table(id bigint,reporter uuid,target uuid,category text,details text,state text,staff_note text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ 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_limit is null or p_limit<1 or p_limit>100 then raise exception 'Invalid limit'; end if; return query select r.id,r.reporter,r.target,r.category,r.details,r.state,r.staff_note,r.created_at from private.community_reports r order by r.created_at desc limit p_limit; end $$; create function public.community_admin_audit(p_limit integer default 50) returns table(actor uuid,action text,subject text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ 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_limit is null or p_limit<1 or p_limit>100 then raise exception 'Invalid limit'; end if; return query select a.actor,a.action,a.subject,a.created_at from private.community_audit a order by a.created_at desc limit p_limit; end $$; create function public.community_public_config() returns jsonb language sql stable security definer set search_path = '' as $$ select jsonb_build_object( 'announcements',coalesce((select jsonb_agg(jsonb_build_object('title',a.title,'body',a.body)) from public.community_announcements a where a.starts_at<=now() and (a.ends_at is null or a.ends_at>now())),'[]'::jsonb), 'flags',coalesce((select jsonb_object_agg(f.key,f.enabled) from public.community_flags f),'{}'::jsonb)); $$; revoke all on function public.community_profile(text),public.community_public_config() from public; grant execute on function public.community_profile(text),public.community_public_config() to anon,authenticated; revoke all on function public.community_my_profile(),public.community_save_profile(text,text,text,text,text,boolean), public.community_report(uuid,text,text),public.community_admin(text,jsonb), public.community_admin_reports(integer),public.community_admin_audit(integer) from public,anon; grant execute on function public.community_my_profile(),public.community_save_profile(text,text,text,text,text,boolean), public.community_report(uuid,text,text),public.community_admin(text,jsonb), public.community_admin_reports(integer),public.community_admin_audit(integer) to authenticated; -- Exact global account counts, independent of the current pagination/filter. create function public.admin_global_stats() returns table(total_accounts bigint,approved_accounts bigint,pending_accounts bigint, blocked_accounts bigint,active_suspensions bigint) language plpgsql stable security definer set search_path = '' as $$ 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; return query select count(*)::bigint, count(*) filter(where a.status='approved')::bigint, count(*) filter(where a.status='pending')::bigint, count(*) filter(where a.status='blocked')::bigint, count(*) filter(where s.user_id is not null and s.lifted_at is null and (s.suspended_until is null or s.suspended_until>now()))::bigint from public.hub_access a left join private.hub_suspensions s on s.user_id=a.user_id; end $$; revoke all on function public.admin_global_stats() from public,anon; grant execute on function public.admin_global_stats() to authenticated; commit;