begin; create table private.hub_games ( id uuid primary key, title text not null check(length(title) between 1 and 60), description text not null default '' check(length(description)<=180), category text not null check(category in ('arcade','puzzle','platformer','other')), html_path text not null, thumbnail_path text, published boolean not null default false, version integer not null default 1, created_by uuid not null references auth.users(id), created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create table private.hub_cloud ( user_id uuid primary key references auth.users(id) on delete cascade, settings jsonb not null default '{}', favorites jsonb not null default '[]', revision integer not null default 0, updated_at timestamptz not null default now() ); create table private.hub_recent ( user_id uuid not null references auth.users(id) on delete cascade, item_id text not null check(item_id ~ '^[A-Za-z0-9_-]{1,100}$'), played_at timestamptz not null default now(), primary key(user_id,item_id) ); create table private.hub_game_progress ( user_id uuid not null references auth.users(id) on delete cascade, game_id uuid not null references private.hub_games(id), slot text not null check(slot ~ '^[a-zA-Z0-9_-]{1,32}$'), value jsonb not null, revision integer not null default 1, updated_at timestamptz not null default now(), primary key(user_id,game_id,slot) ); create table private.hub_friends ( id uuid primary key default gen_random_uuid(), requester uuid not null references auth.users(id) on delete cascade, recipient uuid not null references auth.users(id) on delete cascade, state text not null default 'pending' check(state in ('pending','accepted','blocked')), blocked_by uuid references auth.users(id) on delete cascade, created_at timestamptz not null default now(), check(requester<>recipient),check((state='blocked' and blocked_by in (requester,recipient)) or (state<>'blocked' and blocked_by is null)) ); create unique index hub_friend_pair on private.hub_friends(least(requester,recipient),greatest(requester,recipient)); create index hub_friend_recipient on private.hub_friends(recipient,state); create table private.hub_suggestions ( id uuid primary key default gen_random_uuid(), user_id uuid not null references auth.users(id) on delete cascade, kind text not null check(kind in ('game','bug','idea')), title text not null check(length(title) between 3 and 80), body text not null check(length(body) between 10 and 2000), state text not null default 'open' check(state in ('open','reviewing','planned','done','closed')), reply text not null default '' check(length(reply)<=1000), created_at timestamptz not null default now(),updated_at timestamptz not null default now() ); create index hub_suggestions_user_time on private.hub_suggestions(user_id,created_at desc); create table private.hub_profile_themes ( user_id uuid primary key references auth.users(id) on delete cascade, preset text not null default 'slate' check(preset in ('slate','forest','violet','ocean','sunset')), accent text not null default '#76fa74' check(accent ~ '^#[0-9a-fA-F]{6}$'), effect text not null default 'none' check(effect in ('none','glow','stripes')), updated_at timestamptz not null default now() ); alter table private.hub_games enable row level security; alter table private.hub_cloud enable row level security; alter table private.hub_recent enable row level security; alter table private.hub_game_progress enable row level security; alter table private.hub_friends enable row level security; alter table private.hub_suggestions enable row level security; alter table private.hub_profile_themes enable row level security; revoke all on private.hub_games,private.hub_cloud,private.hub_recent,private.hub_game_progress,private.hub_friends,private.hub_suggestions,private.hub_profile_themes from public,anon,authenticated; create function public.hub_library_owner() returns boolean language sql stable security definer set search_path='' as $$ select auth.uid() is not null and private.staff_rank()=3; $$; revoke all on function public.hub_library_owner() from public,anon; grant execute on function public.hub_library_owner() to authenticated; insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('hub-games','hub-games',false,10485760,array['text/html','image/png','image/jpeg','image/webp']); create policy hub_games_insert on storage.objects for insert to authenticated with check(bucket_id='hub-games' and (select public.hub_library_owner()) and name ~ '^[0-9a-f-]{36}/[0-9a-f-]{36}\.(html|png|jpg|webp)$'); create policy hub_games_delete on storage.objects for delete to authenticated using(bucket_id='hub-games' and (select public.hub_library_owner())); create function public.hub_game_file_allowed(p_name text) returns boolean language sql stable security definer set search_path='' as $$ select public.community_media_upload_allowed() and (private.staff_rank()=3 or exists(select 1 from private.hub_games g where g.published and (g.html_path=p_name or g.thumbnail_path=p_name))); $$; revoke all on function public.hub_game_file_allowed(text) from public,anon; grant execute on function public.hub_game_file_allowed(text) to authenticated; create policy hub_games_read on storage.objects for select to authenticated using(bucket_id='hub-games' and (select public.hub_game_file_allowed(name))); create table private.hub_action_limits(user_id uuid not null references auth.users(id) on delete cascade,action text not null,started_at timestamptz not null default now(),count integer not null default 1,primary key(user_id,action)); alter table private.hub_action_limits enable row level security; revoke all on private.hub_action_limits from public,anon,authenticated; create function private.hub_rate(p_user uuid,p_action text,p_max int,p_seconds int) returns void language plpgsql set search_path='' as $$ declare n int; begin insert into private.hub_action_limits(user_id,action) values(p_user,p_action) on conflict(user_id,action) do update set count=case when private.hub_action_limits.started_atp_seconds) then 1 else private.hub_action_limits.count+1 end, started_at=case when private.hub_action_limits.started_atp_seconds) then now() else private.hub_action_limits.started_at end returning count into n; if n>p_max then raise exception 'Too many requests. Try again later'; end if; end $$; revoke all on function private.hub_rate(uuid,text,int,int) from public,anon,authenticated; -- A narrowly scoped RPC is the only client entry to these private tables. create function public.hub_feature(p_action text,p_data jsonb default '{}') returns jsonb language plpgsql security definer set search_path='' as $$ declare u uuid:=auth.uid(); target uuid; v_id uuid; row_game private.hub_games%rowtype; rel private.hub_friends%rowtype; cloud private.hub_cloud%rowtype; old_revision integer; supplied_revision integer; slot_name text; result jsonb; state_name text; begin if u is null or not public.community_media_upload_allowed() then raise exception 'Approved access required' using errcode='42501'; end if; if jsonb_typeof(p_data)<>'object' or octet_length(p_data::text)>100000 then raise exception 'Invalid request'; end if; if p_action in ('friend_send','request_send','progress_put','cloud_put','recent_put','library_save','theme_put') then perform private.hub_rate(u,p_action,case p_action when 'friend_send' then 10 when 'request_send' then 3 when 'recent_put' then 120 else 60 end,case when p_action in ('friend_send','request_send') then 3600 else 60 end); end if; if p_action='library_list' then return coalesce((select jsonb_agg(to_jsonb(g)) from (select * from private.hub_games where published or private.staff_rank()=3 order by created_at desc limit 100 offset greatest(0,least(10000,coalesce((p_data->>'offset')::int,0)))) g),'[]'); elsif p_action='library_save' then if private.staff_rank()<>3 then raise exception 'Owner access required' using errcode='42501'; end if; v_id:=(p_data->>'id')::uuid; if v_id is null or p_data->>'title' is null or p_data->>'category' is null or p_data->>'html_path' is null then raise exception 'Missing game details'; end if; if p_data->>'html_path' !~ ('^'||v_id::text||'/[0-9a-f-]{36}\.html$') or not exists(select 1 from storage.objects where bucket_id='hub-games' and name=p_data->>'html_path') then raise exception 'Upload the HTML first'; end if; if nullif(p_data->>'thumbnail_path','') is not null and (p_data->>'thumbnail_path' !~ ('^'||v_id::text||'/[0-9a-f-]{36}\.(png|jpg|webp)$') or not exists(select 1 from storage.objects where bucket_id='hub-games' and name=p_data->>'thumbnail_path')) then raise exception 'Invalid thumbnail'; end if; insert into private.hub_games(id,title,description,category,html_path,thumbnail_path,published,created_by) values(v_id,btrim(p_data->>'title'),coalesce(p_data->>'description',''),p_data->>'category',p_data->>'html_path',nullif(p_data->>'thumbnail_path',''),coalesce((p_data->>'published')::boolean,false),u) on conflict(id) do update set title=excluded.title,description=excluded.description,category=excluded.category,html_path=excluded.html_path,thumbnail_path=excluded.thumbnail_path,published=excluded.published,version=private.hub_games.version+1,updated_at=now(); return jsonb_build_object('saved',true); elsif p_action='library_visibility' then if private.staff_rank()<>3 then raise exception 'Owner access required' using errcode='42501'; end if; update private.hub_games set published=coalesce((p_data->>'published')::boolean,false),updated_at=now() where id=(p_data->>'id')::uuid; return jsonb_build_object('saved',found); elsif p_action='cloud_get' then select * into cloud from private.hub_cloud where user_id=u; return case when found then to_jsonb(cloud)-'user_id' else jsonb_build_object('settings','{}'::jsonb,'favorites','[]'::jsonb,'revision',0) end; elsif p_action='cloud_put' then if jsonb_typeof(p_data->'settings') is distinct from 'object' or jsonb_typeof(p_data->'favorites') is distinct from 'array' or octet_length((p_data->'settings')::text)>20000 or jsonb_array_length(p_data->'favorites')>500 then raise exception 'Invalid cloud settings'; end if; if exists(select 1 from jsonb_array_elements(p_data->'favorites') e where jsonb_typeof(e)<>'string' or length(e#>>'{}')>100) then raise exception 'Invalid favorites'; end if; supplied_revision:=(p_data->>'revision')::int; if supplied_revision is null or supplied_revision<0 then raise exception 'Revision required'; end if; insert into private.hub_cloud(user_id) values(u) on conflict do nothing; select * into cloud from private.hub_cloud where user_id=u for update; if cloud.revision<>supplied_revision then return jsonb_build_object('conflict',true,'revision',cloud.revision); end if; update private.hub_cloud set settings=p_data->'settings',favorites=p_data->'favorites',revision=revision+1,updated_at=now() where user_id=u returning * into cloud; return to_jsonb(cloud)-'user_id'; elsif p_action='recent_put' then insert into private.hub_recent(user_id,item_id) values(u,p_data->>'id') on conflict(user_id,item_id) do update set played_at=now(); delete from private.hub_recent where user_id=u and item_id not in(select item_id from private.hub_recent where user_id=u order by played_at desc limit 50); return jsonb_build_object('saved',true); elsif p_action='recent_get' then return coalesce((select jsonb_agg(to_jsonb(r)) from(select item_id,played_at from private.hub_recent where user_id=u order by played_at desc limit 50) r),'[]'); elsif p_action in ('progress_get','progress_put') then v_id:=(p_data->>'game_id')::uuid;slot_name:=coalesce(p_data->>'slot','main'); if slot_name !~ '^[a-zA-Z0-9_-]{1,32}$' or not exists(select 1 from private.hub_games where id=v_id and (published or private.staff_rank()=3)) then raise exception 'Game unavailable'; end if; if p_action='progress_get' then select jsonb_build_object('value',g.value,'revision',g.revision) into result from private.hub_game_progress g where g.user_id=u and g.game_id=v_id and g.slot=slot_name; return coalesce(result,jsonb_build_object('value',null,'revision',0)); end if; if not p_data?'value' or octet_length((p_data->'value')::text)>65536 then raise exception 'Save must fit within 64 KB'; end if; supplied_revision:=(p_data->>'revision')::int; if supplied_revision is null or supplied_revision<0 then raise exception 'Revision required'; end if; perform pg_advisory_xact_lock(hashtextextended(u::text||v_id::text||slot_name,0)); select g.revision into old_revision from private.hub_game_progress g where g.user_id=u and g.game_id=v_id and g.slot=slot_name; if coalesce(old_revision,0)<>supplied_revision then return jsonb_build_object('conflict',true); end if; if exists(select 1 from private.hub_game_progress where user_id=u and game_id=v_id and slot=slot_name and updated_at>now()-interval '1 second') then raise exception 'Please wait before saving again'; end if; if old_revision is null and (select count(*) from private.hub_game_progress where user_id=u and game_id=v_id)>=10 then raise exception 'Save slot limit reached'; end if; insert into private.hub_game_progress(user_id,game_id,slot,value) values(u,v_id,slot_name,p_data->'value') on conflict(user_id,game_id,slot) do update set value=excluded.value,revision=private.hub_game_progress.revision+1,updated_at=now(); return jsonb_build_object('revision',coalesce(old_revision,0)+1,'saved',true); elsif p_action='friends_list' then return coalesce((select jsonb_agg(to_jsonb(f)) from(select c.id,c.state,c.requester=u as outgoing,c.blocked_by=u as blocked_by_me, case when p.public_profile then p.username end as username,case when p.public_profile then p.display_name else 'Private member' end as display_name from private.hub_friends c left join public.community_profiles p on p.user_id=case when c.requester=u then c.recipient else c.requester end where u in(c.requester,c.recipient) and(c.state<>'blocked' or c.blocked_by=u) order by c.created_at desc limit 200) f),'[]'); elsif p_action='friend_send' then select p.user_id into target from public.community_profiles p join public.hub_access a on a.user_id=p.user_id and a.status='approved' where p.username=lower(btrim(p_data->>'username')) and p.public_profile 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())); if target is null or target=u then raise exception 'That profile is unavailable'; end if; perform pg_advisory_xact_lock(hashtextextended(least(u,target)::text||greatest(u,target)::text,0)); if exists(select 1 from private.hub_friends where (requester=u and recipient=target) or(requester=target and recipient=u)) then raise exception 'A request or connection already exists, or requests are unavailable'; end if; if (select count(*) from private.hub_friends where requester=u and created_at>now()-interval '1 hour')>=10 or(select count(*) from private.hub_friends where requester=u and state='pending')>=20 then raise exception 'Request limit reached. Try later'; end if; if (select count(*) from private.hub_friends where recipient=target and state='pending')>=50 then raise exception 'Requests are unavailable'; end if; insert into private.hub_friends(requester,recipient) values(u,target);return jsonb_build_object('sent',true); elsif p_action='friend_update' then select * into rel from private.hub_friends where id=(p_data->>'id')::uuid and u in(requester,recipient) for update; if not found then raise exception 'Request unavailable'; end if; state_name:=p_data->>'state'; if state_name='accepted' and rel.state='pending' and rel.recipient=u then if(select count(*) from private.hub_friends where state='accepted' and u in(requester,recipient))>=100 or(select count(*) from private.hub_friends where state='accepted' and rel.requester in(requester,recipient))>=100 then raise exception 'Friend limit reached'; end if; update private.hub_friends set state='accepted' where id=rel.id; elsif state_name='blocked' and rel.state<>'blocked' then update private.hub_friends set state='blocked',blocked_by=u where id=rel.id; elsif state_name='remove' and(rel.state<>'blocked' or rel.blocked_by=u) then delete from private.hub_friends where id=rel.id; else raise exception 'Action unavailable'; end if; return jsonb_build_object('saved',true); elsif p_action='request_send' then perform pg_advisory_xact_lock(hashtextextended(u::text||'suggestions',0)); if(select count(*) from private.hub_suggestions where user_id=u and created_at>now()-interval '1 hour')>=3 or(select count(*) from private.hub_suggestions where user_id=u and state in('open','reviewing','planned'))>=25 then raise exception 'Request limit reached. Try later'; end if; insert into private.hub_suggestions(user_id,kind,title,body) values(u,p_data->>'kind',btrim(p_data->>'title'),btrim(p_data->>'body')) returning id into v_id; return jsonb_build_object('id',v_id); elsif p_action in ('requests_mine','requests_staff') then if p_action='requests_staff' and private.staff_rank()<1 then raise exception 'Staff access required' using errcode='42501'; end if; return coalesce((select jsonb_agg(to_jsonb(r)) from(select id,kind,title,body,state,reply,created_at,updated_at from private.hub_suggestions where p_action='requests_staff' or user_id=u order by created_at desc limit 50 offset greatest(0,least(10000,coalesce((p_data->>'offset')::int,0)))) r),'[]'); elsif p_action='request_review' then if private.staff_rank()<1 then raise exception 'Staff access required' using errcode='42501'; end if; update private.hub_suggestions set state=p_data->>'state',reply=coalesce(p_data->>'reply',''),updated_at=now() where id=(p_data->>'id')::uuid; if not found then raise exception 'Request unavailable'; end if; insert into private.community_audit(actor,action,subject,detail) values(u,'request_review',p_data->>'id',p_data->>'state'); return jsonb_build_object('saved',true); elsif p_action='theme_put' then insert into private.hub_profile_themes(user_id,preset,accent,effect) values(u,p_data->>'preset',p_data->>'accent',p_data->>'effect') on conflict(user_id) do update set preset=excluded.preset,accent=excluded.accent,effect=excluded.effect,updated_at=now(); return jsonb_build_object('saved',true); else raise exception 'Unknown action'; end if; end $$; revoke all on function public.hub_feature(text,jsonb) from public,anon; grant execute on function public.hub_feature(text,jsonb) to authenticated; create function public.hub_profile_theme(p_username text) returns jsonb language sql stable security definer set search_path='' as $$ select coalesce((select jsonb_build_object('preset',t.preset,'accent',t.accent,'effect',t.effect) from private.hub_profile_themes t join public.community_profiles p on p.user_id=t.user_id join public.hub_access a on a.user_id=p.user_id and a.status='approved' where p.username=p_username and (p.public_profile or p.user_id=auth.uid()) 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()))),'{}'::jsonb); $$; revoke all on function public.hub_profile_theme(text) from public; grant execute on function public.hub_profile_theme(text) to anon,authenticated; commit;