begin; create schema if not exists private; revoke all on schema private from public, anon, authenticated; create table private.hub_owners ( user_id uuid primary key references auth.users(id) on delete cascade ); create table public.hub_access ( user_id uuid primary key references auth.users(id) on delete cascade, status text not null default 'pending' check (status in ('pending','approved','blocked')), created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create table private.access_audit ( id bigint generated always as identity primary key, actor uuid not null, target uuid not null, previous_status text not null, next_status text not null, changed_at timestamptz not null default now() ); alter table public.hub_access enable row level security; revoke all on public.hub_access from anon, authenticated; create function private.create_hub_access() returns trigger language plpgsql security definer set search_path = '' as $$ begin insert into public.hub_access(user_id) values (new.id) on conflict do nothing; return new; end $$; create trigger hub_account_created after insert on auth.users for each row execute function private.create_hub_access(); insert into public.hub_access(user_id) select id from auth.users on conflict do nothing; create function public.my_access() returns table(user_id uuid, status text, owner boolean) language sql stable security definer set search_path = '' as $$ select a.user_id,a.status,exists(select 1 from private.hub_owners o where o.user_id = auth.uid()) from public.hub_access a where a.user_id = auth.uid(); $$; create function public.review_access(target_user uuid, next_status text) returns void language plpgsql security definer set search_path = '' as $$ declare previous text; begin if 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 next_status not in ('pending','approved','blocked') or next_status is null then raise exception 'Invalid access status'; end if; if exists(select 1 from private.hub_owners o where o.user_id = target_user) then raise exception 'Owner access must be managed outside the app'; end if; select status into strict previous from public.hub_access where user_id = target_user for update; update public.hub_access set status = next_status, updated_at = now() where user_id = target_user; insert into private.access_audit(actor,target,previous_status,next_status) values(auth.uid(),target_user,previous,next_status); end $$; create function public.access_queue() returns table(user_id uuid,email text,status text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if 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; return query select a.user_id,u.email::text,a.status,a.created_at from public.hub_access a join auth.users u on u.id = a.user_id order by a.created_at desc limit 200; end $$; revoke all on function private.create_hub_access() from public,anon,authenticated; revoke all on function public.my_access() from public,anon; revoke all on function public.review_access(uuid,text) from public,anon; revoke all on function public.access_queue() from public,anon; grant execute on function public.my_access() to authenticated; grant execute on function public.review_access(uuid,text) to authenticated; grant execute on function public.access_queue() to authenticated; commit;