-- Approved accounts receive a private default profile; public member search includes safe framing. -- Uses additive v2 directory RPC to avoid changing the existing function's return signature. begin; create function private.ensure_approved_profile() returns trigger language plpgsql security definer set search_path = '' as $$ begin if new.status='approved' then insert into public.community_profiles(user_id,username,display_name,public_profile) values(new.user_id,'member_' || left(replace(new.user_id::text,'-',''),17), 'New member',false) on conflict (user_id) do nothing; end if; return new; end $$; create trigger community_profile_on_approval after insert or update of status on public.hub_access for each row execute function private.ensure_approved_profile(); -- Existing approved users are not overwritten, including their custom avatars or bios. insert into public.community_profiles(user_id,username,display_name,public_profile) select a.user_id,'member_' || left(replace(a.user_id::text,'-',''),17),'New member',false from public.hub_access a where a.status='approved' on conflict (user_id) do nothing; create function public.community_search_profiles_v2( 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, banner_url text,avatar_x smallint,avatar_y smallint,avatar_zoom numeric, banner_x smallint,banner_y smallint,banner_zoom numeric,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,p.banner_url, coalesce(l.avatar_x,50)::smallint,coalesce(l.avatar_y,50)::smallint, coalesce(l.avatar_zoom,1.00)::numeric, coalesce(l.banner_x,50)::smallint,coalesce(l.banner_y,50)::smallint, coalesce(l.banner_zoom,1.00)::numeric, 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 left join public.community_profile_layout l on l.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 (btrim(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 $$; revoke all on function public.community_search_profiles_v2(text,integer,integer) from public,anon,authenticated; grant execute on function public.community_search_profiles_v2(text,integer,integer) to anon,authenticated; commit;