-- Suspension details and appeal tickets. Apply ONLY to ZeyadsStuff-Test first. begin; create table private.suspension_appeals ( id bigint generated always as identity primary key, user_id uuid not null references auth.users(id) on delete cascade, suspension_started_at timestamptz not null, message text not null check (char_length(btrim(message)) between 20 and 2000), state text not null default 'open' check (state in ('open','reviewing','accepted','denied')), 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() ); alter table private.suspension_appeals enable row level security; revoke all on private.suspension_appeals from public,anon,authenticated; create unique index suspension_appeals_one_active on private.suspension_appeals(user_id,suspension_started_at) where state in ('open','reviewing'); -- This RPC is callable even when account access is blocked. It only exposes -- the current user's own active suspension. create function public.my_suspension() returns table(active boolean,reason text,suspended_at timestamptz,suspended_until timestamptz) language sql stable security definer set search_path = '' as $$ select true,s.reason,s.suspended_at,s.suspended_until 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()); $$; create function public.my_suspension_appeals(p_limit integer default 10) returns table(id bigint,message text,state text,created_at timestamptz,updated_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 20 then raise exception 'Invalid limit'; end if; return query select a.id,a.message,a.state,a.created_at,a.updated_at from private.suspension_appeals a where a.user_id=auth.uid() order by a.created_at desc limit p_limit; end $$; create function public.submit_suspension_appeal(p_message text) returns bigint language plpgsql security definer set search_path = '' as $$ declare s_started timestamptz; v_id bigint; begin if auth.uid() is null then raise exception 'Sign in required' using errcode='42501'; end if; if p_message is null or char_length(btrim(p_message)) not between 20 and 2000 then raise exception 'Appeal must be 20–2000 characters'; end if; -- Serialize submissions from the same account, including concurrent requests. perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtext(auth.uid()::text)); select s.suspended_at into s_started 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()) for update; if s_started is null then raise exception 'No active suspension to appeal'; end if; if exists(select 1 from private.suspension_appeals a where a.user_id=auth.uid() and a.suspension_started_at=s_started and a.state in ('open','reviewing')) then raise exception 'You already have an active appeal for this suspension'; end if; if exists(select 1 from private.suspension_appeals a where a.user_id=auth.uid() and a.created_at>now()-interval '24 hours') then raise exception 'Please wait 24 hours before sending another appeal'; end if; insert into private.suspension_appeals(user_id,suspension_started_at,message) values(auth.uid(),s_started,btrim(p_message)) returning id into v_id; return v_id; end $$; create function public.admin_suspension_appeals(p_limit integer default 50) returns table(id bigint,user_id uuid,email text,message text,state text,staff_note text, suspension_until timestamptz,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 o where o.user_id=auth.uid()) then raise exception 'Owner access required' using errcode='42501'; end if; if p_limit is null or p_limit not between 1 and 100 then raise exception 'Invalid limit'; end if; return query select a.id,a.user_id,u.email::text,a.message,a.state,a.staff_note, s.suspended_until,a.created_at from private.suspension_appeals a join auth.users u on u.id=a.user_id left join private.hub_suspensions s on s.user_id=a.user_id order by a.created_at desc limit p_limit; end $$; create function public.admin_review_suspension_appeal(p_id bigint,p_state text,p_note text) returns void language plpgsql security definer set search_path = '' as $$ declare v_user uuid; 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_id is null or p_state is null or p_state not in ('open','reviewing','accepted','denied') or p_note is null or char_length(btrim(p_note)) not between 5 and 2000 then raise exception 'Provide a status and staff note (5–2000 characters)'; end if; update private.suspension_appeals set state=p_state,staff_note=btrim(p_note), reviewed_by=auth.uid(),updated_at=now() where id=p_id returning user_id into v_user; if v_user is null then raise exception 'Appeal not found'; end if; -- Acceptance records the decision; unsuspension stays an explicit admin action. insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),'review_suspension_appeal', pg_catalog.jsonb_build_object('appeal_id',p_id,'user_id',v_user,'state',p_state)::text, btrim(p_note)); end $$; revoke all on function public.my_suspension(), public.my_suspension_appeals(integer),public.submit_suspension_appeal(text), public.admin_suspension_appeals(integer), public.admin_review_suspension_appeal(bigint,text,text) from public,anon,authenticated; grant execute on function public.my_suspension(), public.my_suspension_appeals(integer),public.submit_suspension_appeal(text), public.admin_suspension_appeals(integer), public.admin_review_suspension_appeal(bigint,text,text) to authenticated; commit;