4520 lines
238 KiB
PL/PgSQL
4520 lines
238 KiB
PL/PgSQL
-- Caterium fresh recovery; target usfjwhztqoopzzfmfbis only.
|
||
-- Do not run against an existing database.
|
||
begin;
|
||
set local lock_timeout='10s';
|
||
set local statement_timeout='120s';
|
||
do $$begin if to_regclass('public.sun_workspaces') is not null then raise exception 'Fresh recovery requires an empty Caterium schema'; end if; end $$;
|
||
create extension if not exists pg_cron;
|
||
|
||
-- Source: ops/sql/SUPABASE-SETUP.sql
|
||
-- Sun Catering Cloud v2
|
||
-- Run this entire file once in Supabase -> SQL Editor.
|
||
-- Safe to re-run: objects are created with IF NOT EXISTS where possible.
|
||
|
||
create extension if not exists pgcrypto;
|
||
|
||
create table if not exists public.sun_workspaces (
|
||
id uuid primary key default gen_random_uuid(),
|
||
name text not null check (char_length(trim(name)) between 1 and 120),
|
||
created_by uuid not null references auth.users(id) on delete cascade,
|
||
created_at timestamptz not null default now()
|
||
);
|
||
|
||
create table if not exists public.sun_workspace_members (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
user_id uuid not null references auth.users(id) on delete cascade,
|
||
role text not null default 'manager' check (role in ('owner','manager','viewer')),
|
||
created_at timestamptz not null default now(),
|
||
primary key (workspace_id, user_id)
|
||
);
|
||
|
||
create table if not exists public.sun_app_state (
|
||
workspace_id uuid primary key references public.sun_workspaces(id) on delete cascade,
|
||
payload jsonb not null default '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,
|
||
revision bigint not null default 0,
|
||
client_id text,
|
||
updated_by uuid references auth.users(id) on delete set null,
|
||
updated_at timestamptz not null default now()
|
||
);
|
||
|
||
create table if not exists public.sun_workspace_invites (
|
||
token uuid primary key default gen_random_uuid(),
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
role text not null default 'manager' check (role in ('manager','viewer')),
|
||
created_by uuid not null references auth.users(id) on delete cascade,
|
||
created_at timestamptz not null default now(),
|
||
expires_at timestamptz not null default (now() + interval '7 days'),
|
||
used_by uuid references auth.users(id) on delete set null,
|
||
used_at timestamptz
|
||
);
|
||
|
||
create index if not exists sun_workspace_members_user_idx on public.sun_workspace_members(user_id);
|
||
create index if not exists sun_workspace_invites_workspace_idx on public.sun_workspace_invites(workspace_id);
|
||
|
||
create or replace function public.sun_member_role(p_workspace uuid)
|
||
returns text
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
select m.role
|
||
from public.sun_workspace_members m
|
||
where m.workspace_id = p_workspace
|
||
and m.user_id = auth.uid()
|
||
limit 1;
|
||
$$;
|
||
|
||
revoke all on function public.sun_member_role(uuid) from public;
|
||
grant execute on function public.sun_member_role(uuid) to authenticated;
|
||
|
||
create or replace function public.sun_touch_app_state()
|
||
returns trigger
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
begin
|
||
new.updated_at := now();
|
||
if tg_op = 'UPDATE' then
|
||
new.revision := old.revision + 1;
|
||
end if;
|
||
new.updated_by := auth.uid();
|
||
return new;
|
||
end;
|
||
$$;
|
||
|
||
drop trigger if exists sun_app_state_touch on public.sun_app_state;
|
||
create trigger sun_app_state_touch
|
||
before insert or update on public.sun_app_state
|
||
for each row execute function public.sun_touch_app_state();
|
||
|
||
revoke all on function public.sun_touch_app_state() from public;
|
||
revoke all on function public.sun_touch_app_state() from anon;
|
||
revoke all on function public.sun_touch_app_state() from authenticated;
|
||
|
||
create or replace function public.sun_create_workspace(p_name text default 'Солнце Кейтеринг')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_workspace uuid;
|
||
begin
|
||
if v_user is null then
|
||
raise exception 'Authentication required';
|
||
end if;
|
||
|
||
insert into public.sun_workspaces(name, created_by)
|
||
values (coalesce(nullif(trim(p_name),''),'Солнце Кейтеринг'), v_user)
|
||
returning id into v_workspace;
|
||
|
||
insert into public.sun_workspace_members(workspace_id, user_id, role)
|
||
values (v_workspace, v_user, 'owner');
|
||
|
||
insert into public.sun_app_state(workspace_id, payload, client_id)
|
||
values (v_workspace, '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb, 'bootstrap')
|
||
on conflict (workspace_id) do nothing;
|
||
|
||
return v_workspace;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_create_workspace(text) from public;
|
||
grant execute on function public.sun_create_workspace(text) to authenticated;
|
||
|
||
create or replace function public.sun_create_invite(p_workspace uuid, p_role text default 'manager')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text := lower(coalesce(p_role,'manager'));
|
||
begin
|
||
if public.sun_member_role(p_workspace) <> 'owner' then
|
||
raise exception 'Owner role required';
|
||
end if;
|
||
if v_role not in ('manager','viewer') then
|
||
raise exception 'Invalid role';
|
||
end if;
|
||
|
||
insert into public.sun_workspace_invites(workspace_id, role, created_by)
|
||
values (p_workspace, v_role, auth.uid())
|
||
returning token into v_token;
|
||
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_create_invite(uuid,text) from public;
|
||
grant execute on function public.sun_create_invite(uuid,text) to authenticated;
|
||
|
||
create or replace function public.sun_accept_invite(p_token uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_invite public.sun_workspace_invites%rowtype;
|
||
begin
|
||
if v_user is null then
|
||
raise exception 'Authentication required';
|
||
end if;
|
||
|
||
select * into v_invite
|
||
from public.sun_workspace_invites
|
||
where token = p_token
|
||
for update;
|
||
|
||
if not found then raise exception 'Invite not found'; end if;
|
||
if v_invite.used_at is not null then raise exception 'Invite already used'; end if;
|
||
if v_invite.expires_at < now() then raise exception 'Invite expired'; end if;
|
||
|
||
insert into public.sun_workspace_members(workspace_id, user_id, role)
|
||
values (v_invite.workspace_id, v_user, v_invite.role)
|
||
on conflict (workspace_id, user_id)
|
||
do update set role = excluded.role;
|
||
|
||
update public.sun_workspace_invites
|
||
set used_by = v_user, used_at = now()
|
||
where token = p_token;
|
||
|
||
return v_invite.workspace_id;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_accept_invite(uuid) from public;
|
||
grant execute on function public.sun_accept_invite(uuid) to authenticated;
|
||
|
||
alter table public.sun_workspaces enable row level security;
|
||
alter table public.sun_workspace_members enable row level security;
|
||
alter table public.sun_app_state enable row level security;
|
||
alter table public.sun_workspace_invites enable row level security;
|
||
|
||
-- Remove overly broad browser grants, then grant only what authenticated clients need.
|
||
revoke all on public.sun_workspaces from anon;
|
||
revoke all on public.sun_workspace_members from anon;
|
||
revoke all on public.sun_app_state from anon;
|
||
revoke all on public.sun_workspace_invites from anon;
|
||
|
||
grant select on public.sun_workspaces to authenticated;
|
||
grant select on public.sun_workspace_members to authenticated;
|
||
grant select, insert, update on public.sun_app_state to authenticated;
|
||
grant select on public.sun_workspace_invites to authenticated;
|
||
|
||
-- Recreate policies idempotently.
|
||
drop policy if exists sun_workspaces_read on public.sun_workspaces;
|
||
create policy sun_workspaces_read on public.sun_workspaces
|
||
for select to authenticated
|
||
using (public.sun_member_role(id) is not null);
|
||
|
||
drop policy if exists sun_members_read on public.sun_workspace_members;
|
||
create policy sun_members_read on public.sun_workspace_members
|
||
for select to authenticated
|
||
using (user_id = auth.uid() or public.sun_member_role(workspace_id) = 'owner');
|
||
|
||
drop policy if exists sun_state_read on public.sun_app_state;
|
||
create policy sun_state_read on public.sun_app_state
|
||
for select to authenticated
|
||
using (public.sun_member_role(workspace_id) is not null);
|
||
|
||
drop policy if exists sun_state_insert on public.sun_app_state;
|
||
create policy sun_state_insert on public.sun_app_state
|
||
for insert to authenticated
|
||
with check (public.sun_member_role(workspace_id) in ('owner','manager'));
|
||
|
||
drop policy if exists sun_state_update on public.sun_app_state;
|
||
create policy sun_state_update on public.sun_app_state
|
||
for update to authenticated
|
||
using (public.sun_member_role(workspace_id) in ('owner','manager'))
|
||
with check (public.sun_member_role(workspace_id) in ('owner','manager'));
|
||
|
||
drop policy if exists sun_invites_read on public.sun_workspace_invites;
|
||
create policy sun_invites_read on public.sun_workspace_invites
|
||
for select to authenticated
|
||
using (public.sun_member_role(workspace_id) = 'owner');
|
||
|
||
-- Private media bucket. The first folder is always the workspace UUID.
|
||
insert into storage.buckets(id, name, public, file_size_limit, allowed_mime_types)
|
||
values (
|
||
'sun-media',
|
||
'sun-media',
|
||
false,
|
||
15728640,
|
||
array['image/jpeg','image/png','image/webp','image/gif']
|
||
)
|
||
on conflict (id) do update set
|
||
public = excluded.public,
|
||
file_size_limit = excluded.file_size_limit,
|
||
allowed_mime_types = excluded.allowed_mime_types;
|
||
|
||
-- Storage policies.
|
||
drop policy if exists sun_media_read on storage.objects;
|
||
create policy sun_media_read on storage.objects
|
||
for select to authenticated
|
||
using (
|
||
bucket_id = 'sun-media'
|
||
and public.sun_member_role(((storage.foldername(name))[1])::uuid) is not null
|
||
);
|
||
|
||
drop policy if exists sun_media_insert on storage.objects;
|
||
create policy sun_media_insert on storage.objects
|
||
for insert to authenticated
|
||
with check (
|
||
bucket_id = 'sun-media'
|
||
and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
|
||
);
|
||
|
||
drop policy if exists sun_media_update on storage.objects;
|
||
create policy sun_media_update on storage.objects
|
||
for update to authenticated
|
||
using (
|
||
bucket_id = 'sun-media'
|
||
and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
|
||
)
|
||
with check (
|
||
bucket_id = 'sun-media'
|
||
and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
|
||
);
|
||
|
||
drop policy if exists sun_media_delete on storage.objects;
|
||
create policy sun_media_delete on storage.objects
|
||
for delete to authenticated
|
||
using (
|
||
bucket_id = 'sun-media'
|
||
and public.sun_member_role(((storage.foldername(name))[1])::uuid) in ('owner','manager')
|
||
);
|
||
|
||
-- Realtime publication for the single workspace-state row.
|
||
do $$
|
||
begin
|
||
if not exists (
|
||
select 1 from pg_publication_tables
|
||
where pubname = 'supabase_realtime'
|
||
and schemaname = 'public'
|
||
and tablename = 'sun_app_state'
|
||
) then
|
||
alter publication supabase_realtime add table public.sun_app_state;
|
||
end if;
|
||
end $$;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-RBAC-V3.sql
|
||
-- Sun Catering Cloud RBAC v3
|
||
-- Roles, granular permissions, administrator tools, secured state RPCs and realtime sync events.
|
||
|
||
alter table public.sun_workspace_members add column if not exists display_name text;
|
||
alter table public.sun_workspace_members add column if not exists is_active boolean not null default true;
|
||
alter table public.sun_workspace_members add column if not exists permissions jsonb not null default '{}'::jsonb;
|
||
alter table public.sun_workspace_members add column if not exists updated_at timestamptz not null default now();
|
||
|
||
alter table public.sun_workspace_invites add column if not exists permissions jsonb;
|
||
|
||
alter table public.sun_workspace_members drop constraint if exists sun_workspace_members_role_check;
|
||
update public.sun_workspace_members set role='admin' where role='owner';
|
||
alter table public.sun_workspace_members add constraint sun_workspace_members_role_check
|
||
check (role in ('admin','manager','kitchen','courier','viewer'));
|
||
|
||
alter table public.sun_workspace_invites drop constraint if exists sun_workspace_invites_role_check;
|
||
alter table public.sun_workspace_invites add constraint sun_workspace_invites_role_check
|
||
check (role in ('admin','manager','kitchen','courier','viewer'));
|
||
|
||
create or replace function public.sun_role_default_permissions(p_role text)
|
||
returns jsonb
|
||
language sql
|
||
immutable
|
||
set search_path = public
|
||
as $$
|
||
select case lower(coalesce(p_role,''))
|
||
when 'admin' then jsonb_build_object(
|
||
'app.read',true,
|
||
'orders.view',true,'orders.create',true,'orders.edit',true,'orders.delete',true,
|
||
'clients.view',true,'clients.edit',true,
|
||
'catalog.view',true,'catalog.edit',true,
|
||
'calendar.view',true,'map.view',true,
|
||
'production.view',true,'production.edit',true,
|
||
'shopping.view',true,'shopping.edit',true,
|
||
'stock.view',true,'stock.edit',true,
|
||
'routes.view',true,'routes.edit',true,
|
||
'mailings.view',true,'mailings.edit',true,
|
||
'money.view',true,'money.edit',true,
|
||
'stats.view',true,
|
||
'team.view',true,'team.edit',true,
|
||
'suppliers.view',true,'suppliers.edit',true,
|
||
'print.view',true,
|
||
'settings.view',true,'settings.edit',true,
|
||
'users.manage',true,'audit.view',true,'backups.manage',true
|
||
)
|
||
when 'manager' then jsonb_build_object(
|
||
'app.read',true,
|
||
'orders.view',true,'orders.create',true,'orders.edit',true,'orders.delete',false,
|
||
'clients.view',true,'clients.edit',true,
|
||
'catalog.view',true,'catalog.edit',false,
|
||
'calendar.view',true,'map.view',true,
|
||
'production.view',false,'production.edit',false,
|
||
'shopping.view',false,'shopping.edit',false,
|
||
'stock.view',false,'stock.edit',false,
|
||
'routes.view',true,'routes.edit',false,
|
||
'mailings.view',true,'mailings.edit',true,
|
||
'money.view',false,'money.edit',false,
|
||
'stats.view',true,
|
||
'team.view',true,'team.edit',false,
|
||
'suppliers.view',false,'suppliers.edit',false,
|
||
'print.view',true,
|
||
'settings.view',false,'settings.edit',false,
|
||
'users.manage',false,'audit.view',false,'backups.manage',false
|
||
)
|
||
when 'kitchen' then jsonb_build_object(
|
||
'app.read',true,
|
||
'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
|
||
'clients.view',false,'clients.edit',false,
|
||
'catalog.view',true,'catalog.edit',false,
|
||
'calendar.view',true,'map.view',false,
|
||
'production.view',true,'production.edit',true,
|
||
'shopping.view',true,'shopping.edit',false,
|
||
'stock.view',true,'stock.edit',true,
|
||
'routes.view',false,'routes.edit',false,
|
||
'mailings.view',false,'mailings.edit',false,
|
||
'money.view',false,'money.edit',false,
|
||
'stats.view',false,
|
||
'team.view',true,'team.edit',false,
|
||
'suppliers.view',false,'suppliers.edit',false,
|
||
'print.view',true,
|
||
'settings.view',false,'settings.edit',false,
|
||
'users.manage',false,'audit.view',false,'backups.manage',false
|
||
)
|
||
when 'courier' then jsonb_build_object(
|
||
'app.read',true,
|
||
'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
|
||
'clients.view',false,'clients.edit',false,
|
||
'catalog.view',false,'catalog.edit',false,
|
||
'calendar.view',true,'map.view',true,
|
||
'production.view',false,'production.edit',false,
|
||
'shopping.view',false,'shopping.edit',false,
|
||
'stock.view',false,'stock.edit',false,
|
||
'routes.view',true,'routes.edit',true,
|
||
'mailings.view',false,'mailings.edit',false,
|
||
'money.view',false,'money.edit',false,
|
||
'stats.view',false,
|
||
'team.view',false,'team.edit',false,
|
||
'suppliers.view',false,'suppliers.edit',false,
|
||
'print.view',false,
|
||
'settings.view',false,'settings.edit',false,
|
||
'users.manage',false,'audit.view',false,'backups.manage',false
|
||
)
|
||
else jsonb_build_object(
|
||
'app.read',true,
|
||
'orders.view',true,'orders.create',false,'orders.edit',false,'orders.delete',false,
|
||
'clients.view',true,'clients.edit',false,
|
||
'catalog.view',true,'catalog.edit',false,
|
||
'calendar.view',true,'map.view',true,
|
||
'production.view',false,'production.edit',false,
|
||
'shopping.view',false,'shopping.edit',false,
|
||
'stock.view',false,'stock.edit',false,
|
||
'routes.view',false,'routes.edit',false,
|
||
'mailings.view',false,'mailings.edit',false,
|
||
'money.view',false,'money.edit',false,
|
||
'stats.view',true,
|
||
'team.view',false,'team.edit',false,
|
||
'suppliers.view',false,'suppliers.edit',false,
|
||
'print.view',true,
|
||
'settings.view',false,'settings.edit',false,
|
||
'users.manage',false,'audit.view',false,'backups.manage',false
|
||
)
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_member_role(p_workspace uuid)
|
||
returns text
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
select m.role
|
||
from public.sun_workspace_members m
|
||
where m.workspace_id = p_workspace
|
||
and m.user_id = auth.uid()
|
||
and m.is_active = true
|
||
limit 1;
|
||
$$;
|
||
|
||
create or replace function public.sun_has_permission(p_workspace uuid, p_permission text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_role text;
|
||
v_permissions jsonb;
|
||
v_active boolean;
|
||
begin
|
||
select role, permissions, is_active
|
||
into v_role, v_permissions, v_active
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=auth.uid()
|
||
limit 1;
|
||
|
||
if not coalesce(v_active,false) then return false; end if;
|
||
if v_role='admin' then return true; end if;
|
||
if v_permissions ? p_permission then
|
||
return coalesce((v_permissions->>p_permission)::boolean,false);
|
||
end if;
|
||
return coalesce((public.sun_role_default_permissions(v_role)->>p_permission)::boolean,false);
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_member_role(uuid) from public, anon;
|
||
grant execute on function public.sun_member_role(uuid) to authenticated;
|
||
revoke all on function public.sun_has_permission(uuid,text) from public, anon;
|
||
grant execute on function public.sun_has_permission(uuid,text) to authenticated;
|
||
revoke all on function public.sun_role_default_permissions(text) from public, anon;
|
||
grant execute on function public.sun_role_default_permissions(text) to authenticated;
|
||
|
||
create or replace function public.sun_create_workspace(p_name text default 'Солнце Кейтеринг')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_workspace uuid;
|
||
v_name text;
|
||
begin
|
||
if v_user is null then raise exception 'Authentication required'; end if;
|
||
v_name := coalesce(nullif(trim(p_name),''),'Солнце Кейтеринг');
|
||
insert into public.sun_workspaces(name, created_by)
|
||
values (v_name, v_user)
|
||
returning id into v_workspace;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
|
||
values (v_workspace,v_user,'admin',coalesce((select raw_user_meta_data->>'name' from auth.users where id=v_user),split_part(coalesce((select email from auth.users where id=v_user),'Администратор'),'@',1)),true,public.sun_role_default_permissions('admin'));
|
||
insert into public.sun_app_state(workspace_id,payload,client_id)
|
||
values (v_workspace,'{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,'bootstrap')
|
||
on conflict (workspace_id) do nothing;
|
||
return v_workspace;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_create_workspace(text) from public, anon;
|
||
grant execute on function public.sun_create_workspace(text) to authenticated;
|
||
|
||
create or replace function public.sun_create_invite(p_workspace uuid, p_role text default 'manager')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text := lower(coalesce(p_role,'manager'));
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
|
||
insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by)
|
||
values (p_workspace,v_role,public.sun_role_default_permissions(v_role),auth.uid())
|
||
returning token into v_token;
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_create_invite(uuid,text) from public, anon;
|
||
grant execute on function public.sun_create_invite(uuid,text) to authenticated;
|
||
|
||
create or replace function public.sun_accept_invite(p_token uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_invite public.sun_workspace_invites%rowtype;
|
||
v_display text;
|
||
begin
|
||
if v_user is null then raise exception 'Authentication required'; end if;
|
||
select * into v_invite from public.sun_workspace_invites where token=p_token for update;
|
||
if not found then raise exception 'Invite not found'; end if;
|
||
if v_invite.used_at is not null then raise exception 'Invite already used'; end if;
|
||
if v_invite.expires_at < now() then raise exception 'Invite expired'; end if;
|
||
select coalesce(nullif(raw_user_meta_data->>'name',''),split_part(coalesce(email,'Сотрудник'),'@',1)) into v_display from auth.users where id=v_user;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
|
||
values (v_invite.workspace_id,v_user,v_invite.role,v_display,true,coalesce(v_invite.permissions,public.sun_role_default_permissions(v_invite.role)),now())
|
||
on conflict (workspace_id,user_id) do update set role=excluded.role,display_name=coalesce(public.sun_workspace_members.display_name,excluded.display_name),is_active=true,permissions=excluded.permissions,updated_at=now();
|
||
update public.sun_workspace_invites set used_by=v_user,used_at=now() where token=p_token;
|
||
return v_invite.workspace_id;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_accept_invite(uuid) from public, anon;
|
||
grant execute on function public.sun_accept_invite(uuid) to authenticated;
|
||
|
||
create or replace function public.sun_list_workspace_members(p_workspace uuid)
|
||
returns table(user_id uuid,email text,display_name text,role text,is_active boolean,permissions jsonb,created_at timestamptz,updated_at timestamptz)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
return query
|
||
select m.user_id,u.email,m.display_name,m.role,m.is_active,
|
||
coalesce(m.permissions,public.sun_role_default_permissions(m.role)),m.created_at,m.updated_at
|
||
from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=p_workspace
|
||
order by (m.role='admin') desc,coalesce(m.display_name,u.email);
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_list_workspace_members(uuid) from public, anon;
|
||
grant execute on function public.sun_list_workspace_members(uuid) to authenticated;
|
||
|
||
create or replace function public.sun_admin_update_member(
|
||
p_workspace uuid,
|
||
p_user uuid,
|
||
p_display_name text,
|
||
p_role text,
|
||
p_is_active boolean,
|
||
p_permissions jsonb
|
||
)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_old_role text;
|
||
v_old_active boolean;
|
||
v_admins int;
|
||
v_role text := lower(coalesce(p_role,''));
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
|
||
if p_permissions is null or jsonb_typeof(p_permissions) <> 'object' then raise exception 'Permissions must be an object'; end if;
|
||
|
||
select role,is_active into v_old_role,v_old_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
|
||
if not found then raise exception 'Member not found'; end if;
|
||
|
||
if v_old_role='admin' and coalesce(v_old_active,false) and (v_role<>'admin' or not coalesce(p_is_active,false)) then
|
||
select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
|
||
if v_admins <= 1 then raise exception 'Нельзя отключить или понизить последнего администратора'; end if;
|
||
end if;
|
||
|
||
update public.sun_workspace_members
|
||
set display_name=nullif(trim(coalesce(p_display_name,'')),''),role=v_role,is_active=coalesce(p_is_active,false),permissions=p_permissions,updated_at=now()
|
||
where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) from public, anon;
|
||
grant execute on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) to authenticated;
|
||
|
||
create or replace function public.sun_admin_remove_member(p_workspace uuid,p_user uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_role text;
|
||
v_active boolean;
|
||
v_admins int;
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
select role,is_active into v_role,v_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
|
||
if not found then return; end if;
|
||
if v_role='admin' and coalesce(v_active,false) then
|
||
select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
|
||
if v_admins <= 1 then raise exception 'Нельзя удалить последнего администратора'; end if;
|
||
end if;
|
||
delete from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_admin_remove_member(uuid,uuid) from public, anon;
|
||
grant execute on function public.sun_admin_remove_member(uuid,uuid) to authenticated;
|
||
|
||
create or replace function public.sun_can_read_storage_key(p_workspace uuid,p_key text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then return false; end if;
|
||
return case
|
||
when p_key='sunOrders' then public.sun_has_permission(p_workspace,'orders.view') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'orders.edit')
|
||
when p_key='sunBoxes' then public.sun_has_permission(p_workspace,'catalog.view') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'production.view') or public.sun_has_permission(p_workspace,'stock.view')
|
||
when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then public.sun_has_permission(p_workspace,'clients.view') or public.sun_has_permission(p_workspace,'clients.edit') or public.sun_has_permission(p_workspace,'orders.create') or public.sun_has_permission(p_workspace,'orders.edit')
|
||
when p_key='sunFinanceRecordsV2' then public.sun_has_permission(p_workspace,'money.view') or public.sun_has_permission(p_workspace,'money.edit')
|
||
when p_key in ('sunStock','sunStockMoves') then public.sun_has_permission(p_workspace,'stock.view') or public.sun_has_permission(p_workspace,'stock.edit') or public.sun_has_permission(p_workspace,'shopping.view') or public.sun_has_permission(p_workspace,'production.view')
|
||
when p_key='sunEmployees' then public.sun_has_permission(p_workspace,'team.view') or public.sun_has_permission(p_workspace,'team.edit') or public.sun_has_permission(p_workspace,'production.view') or public.sun_has_permission(p_workspace,'routes.view')
|
||
when p_key='sunSuppliers' then public.sun_has_permission(p_workspace,'suppliers.view') or public.sun_has_permission(p_workspace,'suppliers.edit') or public.sun_has_permission(p_workspace,'shopping.view')
|
||
when p_key like 'sunRoute%' then public.sun_has_permission(p_workspace,'routes.view') or public.sun_has_permission(p_workspace,'routes.edit') or public.sun_has_permission(p_workspace,'map.view')
|
||
when p_key like 'sunMarketing%' then public.sun_has_permission(p_workspace,'mailings.view') or public.sun_has_permission(p_workspace,'mailings.edit')
|
||
when p_key='sunAuditLogV1' then public.sun_has_permission(p_workspace,'audit.view')
|
||
else public.sun_has_permission(p_workspace,'app.read')
|
||
end;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_can_read_storage_key(uuid,text) from public, anon, authenticated;
|
||
|
||
create or replace function public.sun_required_write_permission(p_key text)
|
||
returns text
|
||
language sql
|
||
immutable
|
||
as $$
|
||
select case
|
||
when p_key='sunBoxes' then 'catalog.edit'
|
||
when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients.edit'
|
||
when p_key='sunFinanceRecordsV2' then 'money.edit'
|
||
when p_key in ('sunStock','sunStockMoves') then 'stock.edit'
|
||
when p_key='sunEmployees' then 'team.edit'
|
||
when p_key='sunSuppliers' then 'suppliers.edit'
|
||
when p_key like 'sunRoute%' then 'routes.edit'
|
||
when p_key like 'sunMarketing%' then 'mailings.edit'
|
||
when p_key in ('sunPromoCodesV1') then 'mailings.edit'
|
||
when p_key in ('sunCatalogCategoriesV2','sunDefaultOrderQrV1','sunLeadSourcesV1','sunEventTypesV1','sunOrderPaymentColorsV1','sunReceiptSettingsV2','sunPrintSettingsV1','sunYandexMapsSettings','sunEnterpriseSettingsV1','sunOrderStatusFeatureEnabledV1') then 'settings.edit'
|
||
when p_key='sunOfficialCatalogVersion' then 'catalog.edit'
|
||
when p_key='sunAuditLogV1' then '_member'
|
||
else 'settings.edit'
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_required_write_permission(text) from public, anon, authenticated;
|
||
|
||
create or replace function public.sun_fetch_app_state(p_workspace uuid)
|
||
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_row public.sun_app_state%rowtype;
|
||
v_storage jsonb := '{}'::jsonb;
|
||
kv record;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
select * into v_row from public.sun_app_state where sun_app_state.workspace_id=p_workspace;
|
||
if not found then return; end if;
|
||
for kv in select key,value from jsonb_each(coalesce(v_row.payload->'storage','{}'::jsonb)) loop
|
||
if public.sun_can_read_storage_key(p_workspace,kv.key) then
|
||
v_storage := v_storage || jsonb_build_object(kv.key,kv.value);
|
||
end if;
|
||
end loop;
|
||
workspace_id := v_row.workspace_id;
|
||
payload := jsonb_build_object('format',coalesce(v_row.payload->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(v_row.payload->'version','2'::jsonb),'storage',v_storage);
|
||
revision := v_row.revision;
|
||
updated_at := v_row.updated_at;
|
||
client_id := v_row.client_id;
|
||
return next;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_fetch_app_state(uuid) from public, anon;
|
||
grant execute on function public.sun_fetch_app_state(uuid) to authenticated;
|
||
|
||
create or replace function public.sun_save_app_state(p_workspace uuid,p_payload jsonb,p_client_id text)
|
||
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_old jsonb := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb;
|
||
v_old_storage jsonb;
|
||
v_new_storage jsonb;
|
||
v_key text;
|
||
v_perm text;
|
||
v_old_orders jsonb;
|
||
v_new_orders jsonb;
|
||
v_create boolean := false;
|
||
v_delete boolean := false;
|
||
v_edit boolean := false;
|
||
v_saved public.sun_app_state%rowtype;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if p_payload is null or jsonb_typeof(p_payload) <> 'object' or jsonb_typeof(coalesce(p_payload->'storage','{}'::jsonb)) <> 'object' then raise exception 'Invalid payload'; end if;
|
||
|
||
select a.payload into v_old from public.sun_app_state a where a.workspace_id=p_workspace;
|
||
if v_old is null then v_old := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb; end if;
|
||
v_old_storage := coalesce(v_old->'storage','{}'::jsonb);
|
||
v_new_storage := coalesce(p_payload->'storage','{}'::jsonb);
|
||
|
||
for v_key in
|
||
select key from (select jsonb_object_keys(v_old_storage) key union select jsonb_object_keys(v_new_storage) key) q
|
||
loop
|
||
if coalesce(v_old_storage->v_key,'null'::jsonb) = coalesce(v_new_storage->v_key,'null'::jsonb) then continue; end if;
|
||
if v_key='sunOrders' then
|
||
v_old_orders := coalesce(v_old_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
v_new_orders := coalesce(v_new_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
if jsonb_typeof(v_old_orders)<>'array' or jsonb_typeof(v_new_orders)<>'array' then raise exception 'Invalid orders payload'; end if;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n where not exists(select 1 from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id')) into v_create;
|
||
select exists(select 1 from jsonb_array_elements(v_old_orders) o where not exists(select 1 from jsonb_array_elements(v_new_orders) n where n->>'id'=o->>'id')) into v_delete;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n join lateral (select o from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id' limit 1) x on true where x.o<>n) into v_edit;
|
||
if v_create and not public.sun_has_permission(p_workspace,'orders.create') then raise exception 'Нет права создавать заказы'; end if;
|
||
if v_edit and not public.sun_has_permission(p_workspace,'orders.edit') then raise exception 'Нет права изменять заказы'; end if;
|
||
if v_delete and not public.sun_has_permission(p_workspace,'orders.delete') then raise exception 'Нет права удалять заказы'; end if;
|
||
else
|
||
v_perm := public.sun_required_write_permission(v_key);
|
||
if v_perm='_member' then
|
||
null;
|
||
elsif not public.sun_has_permission(p_workspace,v_perm) then
|
||
raise exception 'Нет права изменять раздел: %',v_key;
|
||
end if;
|
||
end if;
|
||
end loop;
|
||
|
||
insert into public.sun_app_state(workspace_id,payload,client_id)
|
||
values (p_workspace,p_payload,p_client_id)
|
||
on conflict (workspace_id) do update set payload=excluded.payload,client_id=excluded.client_id
|
||
returning * into v_saved;
|
||
|
||
insert into public.sun_sync_events(workspace_id,revision,client_id) values (p_workspace,v_saved.revision,p_client_id);
|
||
|
||
return query select * from public.sun_fetch_app_state(p_workspace);
|
||
end;
|
||
$$;
|
||
|
||
create table if not exists public.sun_sync_events (
|
||
id bigint generated by default as identity primary key,
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
revision bigint not null,
|
||
client_id text,
|
||
created_at timestamptz not null default now()
|
||
);
|
||
create index if not exists sun_sync_events_workspace_idx on public.sun_sync_events(workspace_id,id desc);
|
||
alter table public.sun_sync_events enable row level security;
|
||
revoke all on public.sun_sync_events from anon;
|
||
grant select on public.sun_sync_events to authenticated;
|
||
drop policy if exists sun_sync_events_read on public.sun_sync_events;
|
||
create policy sun_sync_events_read on public.sun_sync_events for select to authenticated using (public.sun_member_role(workspace_id) is not null);
|
||
|
||
revoke all on function public.sun_save_app_state(uuid,jsonb,text) from public, anon;
|
||
grant execute on function public.sun_save_app_state(uuid,jsonb,text) to authenticated;
|
||
|
||
-- The app state itself is accessible only through permission-aware RPCs.
|
||
revoke select,insert,update,delete on public.sun_app_state from authenticated, anon;
|
||
drop policy if exists sun_state_read on public.sun_app_state;
|
||
drop policy if exists sun_state_insert on public.sun_app_state;
|
||
drop policy if exists sun_state_update on public.sun_app_state;
|
||
|
||
-- Members can read their own membership; administrators can read their workspace members.
|
||
drop policy if exists sun_members_read on public.sun_workspace_members;
|
||
create policy sun_members_read on public.sun_workspace_members for select to authenticated
|
||
using (user_id=auth.uid() or public.sun_has_permission(workspace_id,'users.manage'));
|
||
|
||
-- Workspaces are visible only to active members.
|
||
drop policy if exists sun_workspaces_read on public.sun_workspaces;
|
||
create policy sun_workspaces_read on public.sun_workspaces for select to authenticated
|
||
using (public.sun_member_role(id) is not null);
|
||
|
||
-- Invitations visible only to administrators.
|
||
drop policy if exists sun_invites_read on public.sun_workspace_invites;
|
||
create policy sun_invites_read on public.sun_workspace_invites for select to authenticated
|
||
using (public.sun_has_permission(workspace_id,'users.manage'));
|
||
|
||
-- Storage: active members may read, catalogue editors may upload/change media.
|
||
drop policy if exists sun_media_read on storage.objects;
|
||
create policy sun_media_read on storage.objects for select to authenticated
|
||
using (bucket_id='sun-media' and public.sun_member_role(((storage.foldername(name))[1])::uuid) is not null);
|
||
drop policy if exists sun_media_insert on storage.objects;
|
||
create policy sun_media_insert on storage.objects for insert to authenticated
|
||
with check (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));
|
||
drop policy if exists sun_media_update on storage.objects;
|
||
create policy sun_media_update on storage.objects for update to authenticated
|
||
using (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'))
|
||
with check (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));
|
||
drop policy if exists sun_media_delete on storage.objects;
|
||
create policy sun_media_delete on storage.objects for delete to authenticated
|
||
using (bucket_id='sun-media' and public.sun_has_permission(((storage.foldername(name))[1])::uuid,'catalog.edit'));
|
||
|
||
-- Realtime events, not the full payload, are published.
|
||
do $$
|
||
begin
|
||
if exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sun_app_state') then
|
||
alter publication supabase_realtime drop table public.sun_app_state;
|
||
end if;
|
||
if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sun_sync_events') then
|
||
alter publication supabase_realtime add table public.sun_sync_events;
|
||
end if;
|
||
end $$;
|
||
|
||
-- Bootstrap the approved administrator account if it has no workspace yet.
|
||
do $$
|
||
declare
|
||
v_user uuid;
|
||
v_workspace uuid;
|
||
v_display text;
|
||
begin
|
||
select id,coalesce(nullif(raw_user_meta_data->>'name',''),split_part(email,'@',1)) into v_user,v_display
|
||
from auth.users where lower(email)=lower('dpavlov346@bk.ru') limit 1;
|
||
if v_user is not null then
|
||
update public.sun_workspace_members
|
||
set role='admin',is_active=true,permissions=public.sun_role_default_permissions('admin'),updated_at=now()
|
||
where user_id=v_user;
|
||
if not exists(select 1 from public.sun_workspace_members where user_id=v_user) then
|
||
insert into public.sun_workspaces(name,created_by) values ('Солнце Кейтеринг',v_user) returning id into v_workspace;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
|
||
values (v_workspace,v_user,'admin',v_display,true,public.sun_role_default_permissions('admin'));
|
||
insert into public.sun_app_state(workspace_id,payload,client_id)
|
||
values (v_workspace,'{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,'bootstrap')
|
||
on conflict (workspace_id) do nothing;
|
||
end if;
|
||
end if;
|
||
end $$;
|
||
|
||
-- Normalize existing members' empty permissions to their role template.
|
||
update public.sun_workspace_members
|
||
set permissions=public.sun_role_default_permissions(role),updated_at=now()
|
||
where permissions='{}'::jsonb or permissions is null;
|
||
|
||
-- FINAL RUNTIME FIXES (keep at end of file)
|
||
create or replace function public.sun_list_workspace_members(p_workspace uuid)
|
||
returns table(user_id uuid,email text,display_name text,role text,is_active boolean,permissions jsonb,created_at timestamptz,updated_at timestamptz)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
return query
|
||
select m.user_id,u.email::text,m.display_name,m.role,m.is_active,
|
||
coalesce(m.permissions,public.sun_role_default_permissions(m.role)),m.created_at,m.updated_at
|
||
from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=p_workspace
|
||
order by (m.role='admin') desc,coalesce(m.display_name,u.email::text);
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_list_workspace_members(uuid) from public, anon;
|
||
grant execute on function public.sun_list_workspace_members(uuid) to authenticated;
|
||
|
||
create or replace function public.sun_required_write_permission(p_key text)
|
||
returns text
|
||
language sql
|
||
immutable
|
||
set search_path = public
|
||
as $$
|
||
select case
|
||
when p_key='sunBoxes' then 'catalog.edit'
|
||
when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients.edit'
|
||
when p_key='sunFinanceRecordsV2' then 'money.edit'
|
||
when p_key in ('sunStock','sunStockMoves') then 'stock.edit'
|
||
when p_key='sunEmployees' then 'team.edit'
|
||
when p_key='sunSuppliers' then 'suppliers.edit'
|
||
when p_key like 'sunRoute%' then 'routes.edit'
|
||
when p_key like 'sunMarketing%' then 'mailings.edit'
|
||
when p_key in ('sunPromoCodesV1') then 'mailings.edit'
|
||
when p_key in ('sunCatalogCategoriesV2','sunDefaultOrderQrV1','sunLeadSourcesV1','sunEventTypesV1','sunOrderPaymentColorsV1','sunReceiptSettingsV2','sunPrintSettingsV1','sunYandexMapsSettings','sunEnterpriseSettingsV1','sunOrderStatusFeatureEnabledV1') then 'settings.edit'
|
||
when p_key='sunOfficialCatalogVersion' then 'catalog.edit'
|
||
when p_key='sunAuditLogV1' then '_member'
|
||
else 'settings.edit'
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_save_app_state(p_workspace uuid,p_payload jsonb,p_client_id text)
|
||
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $$
|
||
declare
|
||
v_old jsonb := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb;
|
||
v_old_storage jsonb;
|
||
v_new_storage jsonb;
|
||
v_final_storage jsonb;
|
||
v_final_payload jsonb;
|
||
v_key text;
|
||
v_perm text;
|
||
v_old_orders jsonb;
|
||
v_new_orders jsonb;
|
||
v_create boolean;
|
||
v_delete boolean;
|
||
v_edit boolean;
|
||
v_saved public.sun_app_state%rowtype;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if p_payload is null or jsonb_typeof(p_payload) <> 'object' or jsonb_typeof(coalesce(p_payload->'storage','{}'::jsonb)) <> 'object' then raise exception 'Invalid payload'; end if;
|
||
select a.payload into v_old from public.sun_app_state a where a.workspace_id=p_workspace;
|
||
if v_old is null then v_old := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb; end if;
|
||
v_old_storage := coalesce(v_old->'storage','{}'::jsonb);
|
||
v_new_storage := coalesce(p_payload->'storage','{}'::jsonb);
|
||
v_final_storage := v_old_storage;
|
||
for v_key in select key from (select jsonb_object_keys(v_old_storage) key union select jsonb_object_keys(v_new_storage) key) q loop
|
||
if not public.sun_can_read_storage_key(p_workspace,v_key) and not (v_new_storage ? v_key) then continue; end if;
|
||
if coalesce(v_old_storage->v_key,'null'::jsonb) = coalesce(v_new_storage->v_key,'null'::jsonb) then continue; end if;
|
||
if v_key='sunOrders' then
|
||
v_old_orders := coalesce(v_old_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
v_new_orders := coalesce(v_new_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
if jsonb_typeof(v_old_orders)<>'array' or jsonb_typeof(v_new_orders)<>'array' then raise exception 'Invalid orders payload'; end if;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n where not exists(select 1 from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id')) into v_create;
|
||
select exists(select 1 from jsonb_array_elements(v_old_orders) o where not exists(select 1 from jsonb_array_elements(v_new_orders) n where n->>'id'=o->>'id')) into v_delete;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n join lateral (select o from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id' limit 1) x on true where x.o<>n) into v_edit;
|
||
if v_create and not public.sun_has_permission(p_workspace,'orders.create') then raise exception 'Нет права создавать заказы'; end if;
|
||
if v_edit and not public.sun_has_permission(p_workspace,'orders.edit') then raise exception 'Нет права изменять заказы'; end if;
|
||
if v_delete and not public.sun_has_permission(p_workspace,'orders.delete') then raise exception 'Нет права удалять заказы'; end if;
|
||
else
|
||
v_perm := public.sun_required_write_permission(v_key);
|
||
if v_perm='_member' then null;
|
||
elsif not public.sun_has_permission(p_workspace,v_perm) then raise exception 'Нет права изменять раздел: %',v_key;
|
||
end if;
|
||
end if;
|
||
if v_new_storage ? v_key then v_final_storage := v_final_storage || jsonb_build_object(v_key,v_new_storage->v_key);
|
||
else v_final_storage := v_final_storage - v_key;
|
||
end if;
|
||
end loop;
|
||
v_final_payload := jsonb_build_object('format',coalesce(p_payload->'format',v_old->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(p_payload->'version',v_old->'version','2'::jsonb),'storage',v_final_storage);
|
||
insert into public.sun_app_state as app_state(workspace_id,payload,client_id)
|
||
values (p_workspace,v_final_payload,p_client_id)
|
||
on conflict on constraint sun_app_state_pkey do update set payload=excluded.payload,client_id=excluded.client_id
|
||
returning app_state.* into v_saved;
|
||
insert into public.sun_sync_events(workspace_id,revision,client_id) values (p_workspace,v_saved.revision,p_client_id);
|
||
return query select * from public.sun_fetch_app_state(p_workspace);
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_save_app_state(uuid,jsonb,text) from public, anon;
|
||
grant execute on function public.sun_save_app_state(uuid,jsonb,text) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-SAAS-V16.sql
|
||
-- Sun Catering SaaS foundation v16
|
||
-- Additive subscription/entitlement layer. Existing workspace remains active on Full.
|
||
|
||
create table if not exists public.sun_plans (
|
||
id text primary key,
|
||
name text not null,
|
||
description text not null default '',
|
||
max_members integer null check (max_members is null or max_members >= 1),
|
||
sort_order integer not null default 0,
|
||
is_active boolean not null default true,
|
||
created_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now()
|
||
);
|
||
|
||
create table if not exists public.sun_plan_features (
|
||
plan_id text not null references public.sun_plans(id) on delete cascade,
|
||
feature_key text not null,
|
||
enabled boolean not null default false,
|
||
primary key (plan_id, feature_key)
|
||
);
|
||
|
||
create table if not exists public.sun_workspace_subscriptions (
|
||
workspace_id uuid primary key references public.sun_workspaces(id) on delete cascade,
|
||
plan_id text not null references public.sun_plans(id),
|
||
status text not null default 'trialing' check (status in ('trialing','active','past_due','canceled','expired')),
|
||
trial_started_at timestamptz null,
|
||
trial_ends_at timestamptz null,
|
||
current_period_start timestamptz null,
|
||
current_period_end timestamptz null,
|
||
grace_until timestamptz null,
|
||
cancel_at_period_end boolean not null default false,
|
||
source text not null default 'manual',
|
||
external_customer_id text null,
|
||
external_subscription_id text null,
|
||
note text null,
|
||
created_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now()
|
||
);
|
||
|
||
create table if not exists public.sun_workspace_feature_overrides (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
feature_key text not null,
|
||
enabled boolean not null,
|
||
expires_at timestamptz null,
|
||
note text null,
|
||
created_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now(),
|
||
primary key (workspace_id, feature_key)
|
||
);
|
||
|
||
create table if not exists public.sun_platform_admins (
|
||
user_id uuid primary key references auth.users(id) on delete cascade,
|
||
created_at timestamptz not null default now()
|
||
);
|
||
|
||
insert into public.sun_plans(id,name,description,max_members,sort_order,is_active) values
|
||
('basic','Базовый','Заказы, календарь, клиенты, просмотр каталога и базовая статистика.',1,10,true),
|
||
('professional','Профессиональный','Основная работа команды, финансы, склад, поставщики, маршруты, рассылки и фирменное оформление.',3,20,true),
|
||
('full','Полный','Все функции, включая редактирование боксов и предложения клиентам.',null,30,true)
|
||
on conflict (id) do update set name=excluded.name,description=excluded.description,max_members=excluded.max_members,sort_order=excluded.sort_order,is_active=excluded.is_active,updated_at=now();
|
||
|
||
-- Reset only built-in feature matrix for the three standard plans.
|
||
delete from public.sun_plan_features where plan_id in ('basic','professional','full');
|
||
|
||
insert into public.sun_plan_features(plan_id,feature_key,enabled) values
|
||
-- Basic
|
||
('basic','orders',true),('basic','calendar',true),('basic','map',true),('basic','clients',true),('basic','catalog_view',true),
|
||
('basic','catalog_edit',false),('basic','production',false),('basic','shopping',false),('basic','stock',false),('basic','routes',false),
|
||
('basic','mailings',false),('basic','money',false),('basic','stats_basic',true),('basic','stats_advanced',false),('basic','team',false),
|
||
('basic','suppliers',false),('basic','print',true),('basic','settings',true),('basic','branding',false),('basic','client_offers',false),
|
||
('basic','offer_templates',false),('basic','backups',false),('basic','audit',false),('basic','users_manage',false),
|
||
-- Professional
|
||
('professional','orders',true),('professional','calendar',true),('professional','map',true),('professional','clients',true),('professional','catalog_view',true),
|
||
('professional','catalog_edit',false),('professional','production',true),('professional','shopping',true),('professional','stock',true),('professional','routes',true),
|
||
('professional','mailings',true),('professional','money',true),('professional','stats_basic',true),('professional','stats_advanced',true),('professional','team',true),
|
||
('professional','suppliers',true),('professional','print',true),('professional','settings',true),('professional','branding',true),('professional','client_offers',false),
|
||
('professional','offer_templates',false),('professional','backups',true),('professional','audit',false),('professional','users_manage',true),
|
||
-- Full
|
||
('full','orders',true),('full','calendar',true),('full','map',true),('full','clients',true),('full','catalog_view',true),
|
||
('full','catalog_edit',true),('full','production',true),('full','shopping',true),('full','stock',true),('full','routes',true),
|
||
('full','mailings',true),('full','money',true),('full','stats_basic',true),('full','stats_advanced',true),('full','team',true),
|
||
('full','suppliers',true),('full','print',true),('full','settings',true),('full','branding',true),('full','client_offers',true),
|
||
('full','offer_templates',true),('full','backups',true),('full','audit',true),('full','users_manage',true);
|
||
|
||
-- The creator of the oldest/current production workspace is the initial SaaS platform owner.
|
||
insert into public.sun_platform_admins(user_id)
|
||
select created_by from public.sun_workspaces where created_by is not null order by created_at asc limit 1
|
||
on conflict (user_id) do nothing;
|
||
|
||
-- Existing workspaces are never disrupted by this migration: they receive Full active access.
|
||
insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,current_period_start,current_period_end,grace_until,source,note)
|
||
select id,'full','active',now(),timestamptz '2099-12-31 23:59:59+00',timestamptz '2100-01-07 23:59:59+00','migration','Existing workspace preserved during SaaS migration'
|
||
from public.sun_workspaces
|
||
on conflict (workspace_id) do nothing;
|
||
|
||
alter table public.sun_plans enable row level security;
|
||
alter table public.sun_plan_features enable row level security;
|
||
alter table public.sun_workspace_subscriptions enable row level security;
|
||
alter table public.sun_workspace_feature_overrides enable row level security;
|
||
alter table public.sun_platform_admins enable row level security;
|
||
|
||
-- Recreate policies idempotently.
|
||
drop policy if exists sun_plans_read on public.sun_plans;
|
||
create policy sun_plans_read on public.sun_plans for select to authenticated using (is_active = true);
|
||
|
||
drop policy if exists sun_plan_features_read on public.sun_plan_features;
|
||
create policy sun_plan_features_read on public.sun_plan_features for select to authenticated using (true);
|
||
|
||
drop policy if exists sun_workspace_subscriptions_member_read on public.sun_workspace_subscriptions;
|
||
create policy sun_workspace_subscriptions_member_read on public.sun_workspace_subscriptions for select to authenticated
|
||
using (exists(select 1 from public.sun_workspace_members m where m.workspace_id=sun_workspace_subscriptions.workspace_id and m.user_id=auth.uid() and m.is_active=true));
|
||
|
||
drop policy if exists sun_workspace_overrides_member_read on public.sun_workspace_feature_overrides;
|
||
create policy sun_workspace_overrides_member_read on public.sun_workspace_feature_overrides for select to authenticated
|
||
using (exists(select 1 from public.sun_workspace_members m where m.workspace_id=sun_workspace_feature_overrides.workspace_id and m.user_id=auth.uid() and m.is_active=true));
|
||
|
||
-- No direct client access to platform-admin rows. Use checked RPCs only.
|
||
revoke all on public.sun_platform_admins from anon, authenticated;
|
||
|
||
create or replace function public.sun_is_platform_admin()
|
||
returns boolean
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
select auth.uid() is not null and exists(select 1 from public.sun_platform_admins a where a.user_id=auth.uid());
|
||
$$;
|
||
|
||
create or replace function public.sun_subscription_access_mode(p_workspace uuid)
|
||
returns text
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
s public.sun_workspace_subscriptions%rowtype;
|
||
v_end timestamptz;
|
||
v_grace timestamptz;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then return 'blocked'; end if;
|
||
select * into s from public.sun_workspace_subscriptions where workspace_id=p_workspace;
|
||
if not found then return 'blocked'; end if;
|
||
|
||
if s.status='trialing' then
|
||
v_end:=s.trial_ends_at;
|
||
else
|
||
v_end:=s.current_period_end;
|
||
end if;
|
||
v_grace:=coalesce(s.grace_until, case when v_end is not null then v_end + interval '7 days' else null end);
|
||
|
||
if s.status in ('trialing','active','canceled') and (v_end is null or now() <= v_end) then return 'full'; end if;
|
||
if v_grace is not null and now() <= v_grace then return 'read_only'; end if;
|
||
return 'blocked';
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_workspace_has_feature(p_workspace uuid,p_feature text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_plan text;
|
||
v_value boolean := false;
|
||
v_override boolean;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then return false; end if;
|
||
select plan_id into v_plan from public.sun_workspace_subscriptions where workspace_id=p_workspace;
|
||
if v_plan is null then return false; end if;
|
||
select enabled into v_value from public.sun_plan_features where plan_id=v_plan and feature_key=p_feature;
|
||
select enabled into v_override from public.sun_workspace_feature_overrides
|
||
where workspace_id=p_workspace and feature_key=p_feature and (expires_at is null or expires_at>now());
|
||
if found then return coalesce(v_override,false); end if;
|
||
return coalesce(v_value,false);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_subscription_snapshot(p_workspace uuid)
|
||
returns table(
|
||
workspace_id uuid,
|
||
plan_id text,
|
||
plan_name text,
|
||
status text,
|
||
access_mode text,
|
||
trial_ends_at timestamptz,
|
||
current_period_end timestamptz,
|
||
grace_until timestamptz,
|
||
max_members integer,
|
||
member_count integer,
|
||
features jsonb,
|
||
platform_admin boolean
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
s public.sun_workspace_subscriptions%rowtype;
|
||
p public.sun_plans%rowtype;
|
||
f jsonb := '{}'::jsonb;
|
||
o jsonb := '{}'::jsonb;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then raise exception 'Access denied'; end if;
|
||
select * into s from public.sun_workspace_subscriptions where sun_workspace_subscriptions.workspace_id=p_workspace;
|
||
if not found then raise exception 'Subscription not found'; end if;
|
||
select * into p from public.sun_plans where id=s.plan_id;
|
||
select coalesce(jsonb_object_agg(feature_key,enabled),'{}'::jsonb) into f from public.sun_plan_features where sun_plan_features.plan_id=s.plan_id;
|
||
select coalesce(jsonb_object_agg(feature_key,enabled),'{}'::jsonb) into o from public.sun_workspace_feature_overrides
|
||
where sun_workspace_feature_overrides.workspace_id=p_workspace and (expires_at is null or expires_at>now());
|
||
workspace_id:=p_workspace; plan_id:=s.plan_id; plan_name:=p.name; status:=s.status; access_mode:=public.sun_subscription_access_mode(p_workspace);
|
||
trial_ends_at:=s.trial_ends_at; current_period_end:=s.current_period_end; grace_until:=coalesce(s.grace_until,coalesce(s.trial_ends_at,s.current_period_end)+interval '7 days');
|
||
max_members:=p.max_members;
|
||
select count(*)::int into member_count from public.sun_workspace_members m where m.workspace_id=p_workspace and m.is_active=true;
|
||
features:=f||o; platform_admin:=public.sun_is_platform_admin();
|
||
return next;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_feature_for_storage_key(p_key text)
|
||
returns text
|
||
language sql
|
||
immutable
|
||
set search_path='public'
|
||
as $$
|
||
select case
|
||
when p_key='sunOrders' then 'orders'
|
||
when p_key in ('sunBoxes','sunCatalogCategoriesV2','sunOfficialCatalogVersion') then 'catalog_edit'
|
||
when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients'
|
||
when p_key='sunFinanceRecordsV2' then 'money'
|
||
when p_key in ('sunStock','sunStockMoves') then 'stock'
|
||
when p_key='sunEmployees' then 'team'
|
||
when p_key='sunSuppliers' then 'suppliers'
|
||
when p_key like 'sunRoute%' then 'routes'
|
||
when p_key like 'sunMarketing%' or p_key='sunPromoCodesV1' then 'mailings'
|
||
when p_key='sunBrandThemeV1' then 'branding'
|
||
when p_key='sunClientOfferSettingsV1' then 'client_offers'
|
||
when p_key='sunOfferTemplateV1' then 'offer_templates'
|
||
else 'settings'
|
||
end;
|
||
$$;
|
||
|
||
-- New workspaces automatically receive a 14-day Full trial.
|
||
create or replace function public.sun_create_workspace(p_name text default 'Солнце Кейтеринг'::text)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_workspace uuid;
|
||
v_name text;
|
||
begin
|
||
if v_user is null then raise exception 'Authentication required'; end if;
|
||
v_name := coalesce(nullif(trim(p_name),''),'Солнце Кейтеринг');
|
||
insert into public.sun_workspaces(name, created_by) values (v_name, v_user) returning id into v_workspace;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
|
||
values (v_workspace,v_user,'admin',coalesce((select raw_user_meta_data->>'name' from auth.users where id=v_user),split_part(coalesce((select email from auth.users where id=v_user),'Администратор'),'@',1)),true,public.sun_role_default_permissions('admin'));
|
||
insert into public.sun_app_state(workspace_id,payload,client_id)
|
||
values (v_workspace,'{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb,'bootstrap')
|
||
on conflict (workspace_id) do nothing;
|
||
insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,trial_started_at,trial_ends_at,grace_until,source,note)
|
||
values(v_workspace,'full','trialing',now(),now()+interval '14 days',now()+interval '21 days','trial','14-day Full trial')
|
||
on conflict(workspace_id) do nothing;
|
||
return v_workspace;
|
||
end;
|
||
$$;
|
||
|
||
-- Workspace member limits are enforced server-side.
|
||
create or replace function public.sun_create_invite(p_workspace uuid, p_role text default 'manager'::text)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text := lower(coalesce(p_role,'manager'));
|
||
v_max integer;
|
||
v_count integer;
|
||
begin
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
|
||
select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; end if;
|
||
insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by)
|
||
values (p_workspace,v_role,public.sun_role_default_permissions(v_role),auth.uid()) returning token into v_token;
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_accept_invite(p_token uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_invite public.sun_workspace_invites%rowtype;
|
||
v_display text;
|
||
v_max integer;
|
||
v_count integer;
|
||
begin
|
||
if v_user is null then raise exception 'Authentication required'; end if;
|
||
select * into v_invite from public.sun_workspace_invites where token=p_token for update;
|
||
if not found then raise exception 'Invite not found'; end if;
|
||
if v_invite.used_at is not null then raise exception 'Invite already used'; end if;
|
||
if v_invite.expires_at < now() then raise exception 'Invite expired'; end if;
|
||
if public.sun_subscription_access_mode(v_invite.workspace_id)<>'full' then raise exception 'Подписка компании неактивна'; end if;
|
||
if not public.sun_workspace_has_feature(v_invite.workspace_id,'users_manage') then raise exception 'Сотрудники недоступны на текущем тарифе'; end if;
|
||
select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=v_invite.workspace_id;
|
||
select count(*)::int into v_count from public.sun_workspace_members where workspace_id=v_invite.workspace_id and is_active=true;
|
||
if not exists(select 1 from public.sun_workspace_members where workspace_id=v_invite.workspace_id and user_id=v_user and is_active=true) then
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; end if;
|
||
end if;
|
||
select coalesce(nullif(raw_user_meta_data->>'name',''),split_part(coalesce(email,'Сотрудник'),'@',1)) into v_display from auth.users where id=v_user;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
|
||
values (v_invite.workspace_id,v_user,v_invite.role,v_display,true,coalesce(v_invite.permissions,public.sun_role_default_permissions(v_invite.role)),now())
|
||
on conflict (workspace_id,user_id) do update set role=excluded.role,display_name=coalesce(public.sun_workspace_members.display_name,excluded.display_name),is_active=true,permissions=excluded.permissions,updated_at=now();
|
||
update public.sun_workspace_invites set used_by=v_user,used_at=now() where token=p_token;
|
||
return v_invite.workspace_id;
|
||
end;
|
||
$$;
|
||
|
||
-- Subscription gating is enforced on the cloud save RPC, not only in the browser.
|
||
create or replace function public.sun_save_app_state(p_workspace uuid, p_payload jsonb, p_client_id text)
|
||
returns table(workspace_id uuid, payload jsonb, revision bigint, updated_at timestamptz, client_id text)
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_old jsonb := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb;
|
||
v_old_storage jsonb;
|
||
v_new_storage jsonb;
|
||
v_final_storage jsonb;
|
||
v_final_payload jsonb;
|
||
v_key text;
|
||
v_perm text;
|
||
v_feature text;
|
||
v_old_orders jsonb;
|
||
v_new_orders jsonb;
|
||
v_create boolean;
|
||
v_delete boolean;
|
||
v_edit boolean;
|
||
v_saved public.sun_app_state%rowtype;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка истекла: база доступна только для просмотра'; end if;
|
||
if p_payload is null or jsonb_typeof(p_payload) <> 'object' or jsonb_typeof(coalesce(p_payload->'storage','{}'::jsonb)) <> 'object' then raise exception 'Invalid payload'; end if;
|
||
|
||
select a.payload into v_old from public.sun_app_state a where a.workspace_id=p_workspace;
|
||
if v_old is null then v_old := '{"format":"sun-cloud-v2","version":2,"storage":{}}'::jsonb; end if;
|
||
v_old_storage := coalesce(v_old->'storage','{}'::jsonb);
|
||
v_new_storage := coalesce(p_payload->'storage','{}'::jsonb);
|
||
v_final_storage := v_old_storage;
|
||
|
||
for v_key in select key from (select jsonb_object_keys(v_old_storage) key union select jsonb_object_keys(v_new_storage) key) q loop
|
||
if not public.sun_can_read_storage_key(p_workspace,v_key) and not (v_new_storage ? v_key) then continue; end if;
|
||
if coalesce(v_old_storage->v_key,'null'::jsonb) = coalesce(v_new_storage->v_key,'null'::jsonb) then continue; end if;
|
||
|
||
if v_key='sunOrders' then
|
||
if not public.sun_workspace_has_feature(p_workspace,'orders') then raise exception 'Заказы недоступны на текущем тарифе'; end if;
|
||
v_old_orders := coalesce(v_old_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
v_new_orders := coalesce(v_new_storage #> array['sunOrders','v'],'[]'::jsonb);
|
||
if jsonb_typeof(v_old_orders)<>'array' or jsonb_typeof(v_new_orders)<>'array' then raise exception 'Invalid orders payload'; end if;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n where not exists(select 1 from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id')) into v_create;
|
||
select exists(select 1 from jsonb_array_elements(v_old_orders) o where not exists(select 1 from jsonb_array_elements(v_new_orders) n where n->>'id'=o->>'id')) into v_delete;
|
||
select exists(select 1 from jsonb_array_elements(v_new_orders) n join lateral (select o from jsonb_array_elements(v_old_orders) o where o->>'id'=n->>'id' limit 1) x on true where x.o<>n) into v_edit;
|
||
if v_create and not public.sun_has_permission(p_workspace,'orders.create') then raise exception 'Нет права создавать заказы'; end if;
|
||
if v_edit and not public.sun_has_permission(p_workspace,'orders.edit') then raise exception 'Нет права изменять заказы'; end if;
|
||
if v_delete and not public.sun_has_permission(p_workspace,'orders.delete') then raise exception 'Нет права удалять заказы'; end if;
|
||
else
|
||
v_feature := public.sun_feature_for_storage_key(v_key);
|
||
if v_feature is not null and not public.sun_workspace_has_feature(p_workspace,v_feature) then raise exception 'Функция недоступна на текущем тарифе: %',v_feature; end if;
|
||
v_perm := public.sun_required_write_permission(v_key);
|
||
if v_perm='_member' then null;
|
||
elsif not public.sun_has_permission(p_workspace,v_perm) then raise exception 'Нет права изменять раздел: %',v_key;
|
||
end if;
|
||
end if;
|
||
|
||
if v_new_storage ? v_key then v_final_storage := v_final_storage || jsonb_build_object(v_key,v_new_storage->v_key);
|
||
else v_final_storage := v_final_storage - v_key; end if;
|
||
end loop;
|
||
|
||
v_final_payload := jsonb_build_object('format',coalesce(p_payload->'format',v_old->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(p_payload->'version',v_old->'version','2'::jsonb),'storage',v_final_storage);
|
||
insert into public.sun_app_state as app_state(workspace_id,payload,client_id)
|
||
values (p_workspace,v_final_payload,p_client_id)
|
||
on conflict on constraint sun_app_state_pkey do update set payload=excluded.payload,client_id=excluded.client_id
|
||
returning app_state.* into v_saved;
|
||
insert into public.sun_sync_events(workspace_id,revision,client_id) values (p_workspace,v_saved.revision,p_client_id);
|
||
return query select * from public.sun_fetch_app_state(p_workspace);
|
||
end;
|
||
$$;
|
||
|
||
-- Platform owner APIs. Direct table access remains blocked for normal users.
|
||
create or replace function public.sun_platform_list_workspaces()
|
||
returns table(workspace_id uuid,workspace_name text,created_at timestamptz,plan_id text,plan_name text,status text,access_mode text,trial_ends_at timestamptz,current_period_end timestamptz,grace_until timestamptz,member_count integer,max_members integer)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
return query
|
||
select w.id,w.name,w.created_at,s.plan_id,p.name,s.status,public.sun_subscription_access_mode(w.id),s.trial_ends_at,s.current_period_end,
|
||
coalesce(s.grace_until,coalesce(s.trial_ends_at,s.current_period_end)+interval '7 days'),
|
||
(select count(*)::int from public.sun_workspace_members m where m.workspace_id=w.id and m.is_active=true),p.max_members
|
||
from public.sun_workspaces w
|
||
left join public.sun_workspace_subscriptions s on s.workspace_id=w.id
|
||
left join public.sun_plans p on p.id=s.plan_id
|
||
order by w.created_at desc;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_set_subscription(p_workspace uuid,p_plan text,p_days integer default 30,p_status text default 'active')
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_status text:=lower(coalesce(p_status,'active'));
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if not exists(select 1 from public.sun_plans where id=p_plan and is_active=true) then raise exception 'Unknown plan'; end if;
|
||
if v_status not in ('trialing','active','past_due','canceled','expired') then raise exception 'Invalid status'; end if;
|
||
if p_days is null or p_days<0 or p_days>3650 then raise exception 'Invalid duration'; end if;
|
||
insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,current_period_start,current_period_end,grace_until,source,updated_at)
|
||
values(p_workspace,p_plan,v_status,now(),case when v_status='expired' then now() else now()+make_interval(days=>p_days) end,
|
||
case when v_status='expired' then now() else now()+make_interval(days=>p_days+7) end,'manual',now())
|
||
on conflict(workspace_id) do update set plan_id=excluded.plan_id,status=excluded.status,current_period_start=excluded.current_period_start,current_period_end=excluded.current_period_end,grace_until=excluded.grace_until,source='manual',updated_at=now();
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_set_feature_override(p_workspace uuid,p_feature text,p_enabled boolean,p_days integer default null,p_note text default null)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if not exists(select 1 from public.sun_plan_features where feature_key=p_feature) then raise exception 'Unknown feature'; end if;
|
||
insert into public.sun_workspace_feature_overrides(workspace_id,feature_key,enabled,expires_at,note,updated_at)
|
||
values(p_workspace,p_feature,p_enabled,case when p_days is null then null else now()+make_interval(days=>p_days) end,p_note,now())
|
||
on conflict(workspace_id,feature_key) do update set enabled=excluded.enabled,expires_at=excluded.expires_at,note=excluded.note,updated_at=now();
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_is_platform_admin() from public;
|
||
revoke all on function public.sun_subscription_access_mode(uuid) from public;
|
||
revoke all on function public.sun_workspace_has_feature(uuid,text) from public;
|
||
revoke all on function public.sun_subscription_snapshot(uuid) from public;
|
||
revoke all on function public.sun_platform_list_workspaces() from public;
|
||
revoke all on function public.sun_platform_set_subscription(uuid,text,integer,text) from public;
|
||
revoke all on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) from public;
|
||
|
||
grant execute on function public.sun_is_platform_admin() to authenticated;
|
||
grant execute on function public.sun_subscription_access_mode(uuid) to authenticated;
|
||
grant execute on function public.sun_workspace_has_feature(uuid,text) to authenticated;
|
||
grant execute on function public.sun_subscription_snapshot(uuid) to authenticated;
|
||
grant execute on function public.sun_platform_list_workspaces() to authenticated;
|
||
grant execute on function public.sun_platform_set_subscription(uuid,text,integer,text) to authenticated;
|
||
grant execute on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-SAAS-V16-FINALIZE.sql
|
||
-- Sun Catering SaaS v16 finalization
|
||
-- Run after SUPABASE-SAAS-V16.sql on a fresh project.
|
||
|
||
create or replace function public.sun_feature_for_read_storage_key(p_key text)
|
||
returns text
|
||
language sql
|
||
immutable
|
||
set search_path='public'
|
||
as $$
|
||
select case
|
||
when p_key='sunOrders' then 'orders'
|
||
when p_key in ('sunBoxes','sunCatalogCategoriesV2','sunOfficialCatalogVersion') then 'catalog_view'
|
||
when p_key in ('sunClientLoyaltyV1','sunClientCommunicationV1') then 'clients'
|
||
when p_key='sunFinanceRecordsV2' then 'money'
|
||
when p_key in ('sunStock','sunStockMoves') then 'stock'
|
||
when p_key='sunEmployees' then 'team'
|
||
when p_key='sunSuppliers' then 'suppliers'
|
||
when p_key like 'sunRoute%' then 'routes'
|
||
when p_key like 'sunMarketing%' or p_key='sunPromoCodesV1' then 'mailings'
|
||
when p_key='sunBrandThemeV1' then 'branding'
|
||
when p_key='sunClientOfferSettingsV1' then 'client_offers'
|
||
when p_key='sunOfferTemplateV1' then 'offer_templates'
|
||
when p_key='sunAuditLogV1' then 'audit'
|
||
else 'settings'
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_fetch_app_state(p_workspace uuid)
|
||
returns table(workspace_id uuid, payload jsonb, revision bigint, updated_at timestamptz, client_id text)
|
||
language plpgsql
|
||
stable security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_row public.sun_app_state%rowtype;
|
||
v_storage jsonb := '{}'::jsonb;
|
||
kv record;
|
||
v_feature text;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)='blocked' then raise exception 'Подписка закончилась. Данные сохранены, продлите подписку для доступа.'; end if;
|
||
select * into v_row from public.sun_app_state where sun_app_state.workspace_id=p_workspace;
|
||
if not found then return; end if;
|
||
for kv in select key,value from jsonb_each(coalesce(v_row.payload->'storage','{}'::jsonb)) loop
|
||
v_feature := public.sun_feature_for_read_storage_key(kv.key);
|
||
if public.sun_can_read_storage_key(p_workspace,kv.key)
|
||
and (v_feature is null or public.sun_workspace_has_feature(p_workspace,v_feature)) then
|
||
v_storage := v_storage || jsonb_build_object(kv.key,kv.value);
|
||
end if;
|
||
end loop;
|
||
workspace_id := v_row.workspace_id;
|
||
payload := jsonb_build_object('format',coalesce(v_row.payload->'format','"sun-cloud-v2"'::jsonb),'version',coalesce(v_row.payload->'version','2'::jsonb),'storage',v_storage);
|
||
revision := v_row.revision;
|
||
updated_at := v_row.updated_at;
|
||
client_id := v_row.client_id;
|
||
return next;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_update_member(p_workspace uuid, p_user uuid, p_display_name text, p_role text, p_is_active boolean, p_permissions jsonb)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_old_role text;
|
||
v_old_active boolean;
|
||
v_admins int;
|
||
v_role text := lower(coalesce(p_role,''));
|
||
v_max integer;
|
||
v_active_count integer;
|
||
begin
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Invalid role'; end if;
|
||
if p_permissions is null or jsonb_typeof(p_permissions) <> 'object' then raise exception 'Permissions must be an object'; end if;
|
||
select role,is_active into v_old_role,v_old_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
|
||
if not found then raise exception 'Member not found'; end if;
|
||
if v_old_role='admin' and coalesce(v_old_active,false) and (v_role<>'admin' or not coalesce(p_is_active,false)) then
|
||
select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
|
||
if v_admins <= 1 then raise exception 'Нельзя отключить или понизить последнего администратора'; end if;
|
||
end if;
|
||
if coalesce(p_is_active,false) and not coalesce(v_old_active,false) then
|
||
select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_active_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
if v_max is not null and v_active_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; end if;
|
||
end if;
|
||
update public.sun_workspace_members set display_name=nullif(trim(coalesce(p_display_name,'')),''),role=v_role,is_active=coalesce(p_is_active,false),permissions=p_permissions,updated_at=now()
|
||
where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_remove_member(p_workspace uuid, p_user uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_role text;
|
||
v_active boolean;
|
||
v_admins int;
|
||
begin
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Administrator permission required'; end if;
|
||
select role,is_active into v_role,v_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user for update;
|
||
if not found then return; end if;
|
||
if v_role='admin' and coalesce(v_active,false) then
|
||
select count(*) into v_admins from public.sun_workspace_members where workspace_id=p_workspace and role='admin' and is_active=true;
|
||
if v_admins <= 1 then raise exception 'Нельзя удалить последнего администратора'; end if;
|
||
end if;
|
||
delete from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
-- New SaaS helper RPCs are authenticated-only.
|
||
revoke execute on function public.sun_is_platform_admin() from public, anon;
|
||
revoke execute on function public.sun_subscription_access_mode(uuid) from public, anon;
|
||
revoke execute on function public.sun_workspace_has_feature(uuid,text) from public, anon;
|
||
revoke execute on function public.sun_subscription_snapshot(uuid) from public, anon;
|
||
revoke execute on function public.sun_platform_list_workspaces() from public, anon;
|
||
revoke execute on function public.sun_platform_set_subscription(uuid,text,integer,text) from public, anon;
|
||
revoke execute on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) from public, anon;
|
||
|
||
grant execute on function public.sun_is_platform_admin() to authenticated;
|
||
grant execute on function public.sun_subscription_access_mode(uuid) to authenticated;
|
||
grant execute on function public.sun_workspace_has_feature(uuid,text) to authenticated;
|
||
grant execute on function public.sun_subscription_snapshot(uuid) to authenticated;
|
||
grant execute on function public.sun_platform_list_workspaces() to authenticated;
|
||
grant execute on function public.sun_platform_set_subscription(uuid,text,integer,text) to authenticated;
|
||
grant execute on function public.sun_platform_set_feature_override(uuid,text,boolean,integer,text) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-FRESH-V17-FOUNDATION.sql
|
||
-- Reconstructed from the production client and retained SQL, 2026-09-17.
|
||
-- Empty-project recovery only; original v17 foundation was not in Git history.
|
||
create table public.sun_v17_orders (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
order_id text not null, data jsonb not null, version bigint not null default 1,
|
||
created_at timestamptz not null default now(), updated_at timestamptz not null default now(),
|
||
updated_by uuid references auth.users(id) on delete set null, primary key(workspace_id,order_id)
|
||
);
|
||
create table public.sun_v17_catalog_items (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
item_id text not null, data jsonb not null, version bigint not null default 1,
|
||
created_at timestamptz not null default now(), updated_at timestamptz not null default now(),
|
||
updated_by uuid references auth.users(id) on delete set null, primary key(workspace_id,item_id)
|
||
);
|
||
create table public.sun_v17_clients (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
client_key text not null,name text,phone text,latest_address text,data jsonb not null default '{}',
|
||
version bigint not null default 1,created_at timestamptz not null default now(),updated_at timestamptz not null default now(),
|
||
primary key(workspace_id,client_key)
|
||
);
|
||
create table public.sun_v17_settings (
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
key text not null,value jsonb,version bigint not null default 1,updated_at timestamptz not null default now(),
|
||
primary key(workspace_id,key)
|
||
);
|
||
create table public.sun_v17_workspace_meta (
|
||
workspace_id uuid primary key references public.sun_workspaces(id) on delete cascade,
|
||
schema_version integer not null default 17,last_backup_on date,legacy_revision bigint not null default 0,
|
||
migrated_at timestamptz not null default now(),updated_at timestamptz not null default now()
|
||
);
|
||
create table public.sun_v17_change_events (
|
||
id bigint generated by default as identity primary key,
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
entity text not null,entity_key text,operation text not null,version bigint,client_id text,
|
||
created_by uuid references auth.users(id) on delete set null,created_at timestamptz not null default now()
|
||
);
|
||
create index sun_v17_changes_workspace_id_idx on public.sun_v17_change_events(workspace_id,id);
|
||
create table public.sun_v17_backups (
|
||
id uuid primary key default gen_random_uuid(),workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
kind text not null,label text not null default '',snapshot jsonb not null,
|
||
created_by uuid references auth.users(id) on delete set null,created_at timestamptz not null default now()
|
||
);
|
||
create index sun_v17_backups_workspace_created_idx on public.sun_v17_backups(workspace_id,created_at desc);
|
||
create table public.sun_v17_error_events (
|
||
id uuid primary key default gen_random_uuid(),workspace_id uuid references public.sun_workspaces(id) on delete cascade,
|
||
user_id uuid references auth.users(id) on delete set null,client_id text,app_version text,level text,message text,
|
||
stack text,context jsonb not null default '{}',created_at timestamptz not null default now()
|
||
);
|
||
create index sun_v17_errors_workspace_created_idx on public.sun_v17_error_events(workspace_id,created_at desc);
|
||
create table public.caterium_company_owner_invites (
|
||
token uuid primary key default gen_random_uuid(),workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
email text not null,created_at timestamptz not null default now(),expires_at timestamptz not null default now()+interval '7 days',
|
||
used_at timestamptz,used_by uuid references auth.users(id) on delete set null
|
||
);
|
||
|
||
do $$ declare t text; begin
|
||
foreach t in array array['sun_v17_orders','sun_v17_catalog_items','sun_v17_clients','sun_v17_settings','sun_v17_workspace_meta','sun_v17_change_events','sun_v17_backups','sun_v17_error_events','caterium_company_owner_invites'] loop
|
||
execute format('alter table public.%I enable row level security',t);
|
||
execute format('revoke all on public.%I from anon,authenticated',t);
|
||
end loop;
|
||
end $$;
|
||
-- Browser data access uses the permission-checked RPCs. Realtime only reveals an event.
|
||
grant select on public.sun_v17_change_events to authenticated;
|
||
create policy sun_v17_change_read on public.sun_v17_change_events for select to authenticated using(public.sun_member_role(workspace_id) is not null);
|
||
alter publication supabase_realtime add table public.sun_v17_change_events;
|
||
|
||
create function public.sun_my_workspaces()
|
||
returns table(id uuid,name text,role text,display_name text,is_active boolean,permissions jsonb)
|
||
language sql stable security definer set search_path=public as $$
|
||
select w.id,w.name,m.role,m.display_name,m.is_active,public.sun_role_default_permissions(m.role)||coalesce(m.permissions,'{}')
|
||
from public.sun_workspace_members m join public.sun_workspaces w on w.id=m.workspace_id
|
||
where m.user_id=auth.uid() and m.is_active order by w.created_at,w.id
|
||
$$;
|
||
|
||
create function public.sun_v17_mirror_legacy(p_workspace uuid,p_payload jsonb,p_client_id text default null)
|
||
returns void language plpgsql security definer set search_path=public as $$
|
||
declare canonical jsonb; r record; v_data jsonb; v_ids text[]; v_key text; v_rev bigint;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then raise exception 'Access denied'; end if;
|
||
perform 1 from public.sun_workspaces where id=p_workspace for update;
|
||
select payload,revision into canonical,v_rev from public.sun_app_state where workspace_id=p_workspace;
|
||
if canonical is null then return; end if;
|
||
-- Always mirror the validated server row. The supplied legacy argument is never trusted.
|
||
for r in select * from (values ('sunOrders','sun_v17_orders','order_id','order'),('sunBoxes','sun_v17_catalog_items','item_id','catalog')) as x(storage_key,table_name,id_column,entity) loop
|
||
v_data:=coalesce(canonical#>array['storage',r.storage_key,'v'],'[]'::jsonb);
|
||
if jsonb_typeof(v_data)<>'array' then raise exception 'Invalid entity array: %',r.storage_key; end if;
|
||
if exists(select 1 from jsonb_array_elements(v_data) e where nullif(e->>'id','') is null) then raise exception 'Entity ID is required'; end if;
|
||
if (select count(*) from jsonb_array_elements(v_data))<>(select count(distinct e->>'id') from jsonb_array_elements(v_data) e) then raise exception 'Duplicate entity ID'; end if;
|
||
select coalesce(array_agg(e->>'id'),'{}') into v_ids from jsonb_array_elements(v_data) e;
|
||
execute format('with gone as (delete from public.%I where workspace_id=$1 and not (%I=any($2)) returning %I,version) insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,version,client_id,created_by) select $1,$3,%I,''delete'',version+1,$4,auth.uid() from gone',r.table_name,r.id_column,r.id_column,r.id_column) using p_workspace,v_ids,r.entity,p_client_id;
|
||
execute format('with saved as (insert into public.%I as dest(workspace_id,%I,data,updated_by) select $1,e->>''id'',e,auth.uid() from jsonb_array_elements($2) e on conflict(workspace_id,%I) do update set data=excluded.data,version=dest.version+1,updated_at=now(),updated_by=auth.uid() where dest.data is distinct from excluded.data returning %I,version) insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,version,client_id,created_by) select $1,$3,%I,''upsert'',version,$4,auth.uid() from saved',r.table_name,r.id_column,r.id_column,r.id_column,r.id_column) using p_workspace,v_data,r.entity,p_client_id;
|
||
end loop;
|
||
delete from public.sun_v17_settings where workspace_id=p_workspace and not (canonical->'storage' ? key);
|
||
insert into public.sun_v17_settings as dest(workspace_id,key,value)
|
||
select p_workspace,key,value from jsonb_each(coalesce(canonical->'storage','{}')) where key not in ('sunOrders','sunBoxes')
|
||
on conflict(workspace_id,key) do update set value=excluded.value,version=dest.version+1,updated_at=now() where dest.value is distinct from excluded.value;
|
||
insert into public.sun_v17_workspace_meta(workspace_id,legacy_revision) values(p_workspace,v_rev)
|
||
on conflict(workspace_id) do update set legacy_revision=excluded.legacy_revision,updated_at=now();
|
||
end $$;
|
||
|
||
create function public.sun_save_app_state_v17(p_workspace uuid,p_payload jsonb,p_client_id text,p_expected_revision bigint default null)
|
||
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
|
||
language plpgsql security definer set search_path=public as $$
|
||
declare current_revision bigint;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
perform 1 from public.sun_workspaces w where w.id=p_workspace for update;
|
||
select s.revision into current_revision from public.sun_app_state s where s.workspace_id=p_workspace;
|
||
if p_expected_revision is not null and coalesce(current_revision,0)<>p_expected_revision then
|
||
raise exception using errcode='40001',message=format('SUN_CONFLICT expected=%s actual=%s',p_expected_revision,coalesce(current_revision,0));
|
||
end if;
|
||
perform public.sun_save_app_state(p_workspace,p_payload,p_client_id);
|
||
perform public.sun_v17_mirror_legacy(p_workspace,p_payload,p_client_id);
|
||
return query select * from public.sun_fetch_app_state(p_workspace);
|
||
end $$;
|
||
|
||
create function public.sun_v17_build_snapshot(p_workspace uuid)
|
||
returns jsonb language sql stable security definer set search_path=public as $$
|
||
select jsonb_build_object('version',17,'legacy',payload,'revision',revision,'created_at',now()) from public.sun_app_state where workspace_id=p_workspace
|
||
$$;
|
||
create function public.sun_v17_prune_backups(p_workspace uuid,p_keep integer default 30)
|
||
returns integer language plpgsql security definer set search_path=public as $$
|
||
declare n integer;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then raise exception 'Access denied'; end if;
|
||
delete from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' and id in
|
||
(select id from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' order by created_at desc offset greatest(30,coalesce(p_keep,30)));
|
||
get diagnostics n=row_count; return n;
|
||
end $$;
|
||
|
||
create function public.sun_workspace_access_mode_internal_v28(p_workspace uuid) returns text language sql stable security definer set search_path=public as $$select public.sun_subscription_access_mode(p_workspace)$$;
|
||
create function public.sun_workspace_feature_internal_v28(p_workspace uuid,p_feature text) returns boolean language sql stable security definer set search_path=public as $$select public.sun_workspace_has_feature(p_workspace,p_feature)$$;
|
||
|
||
revoke all on function public.sun_v17_build_snapshot(uuid),public.sun_workspace_access_mode_internal_v28(uuid),public.sun_workspace_feature_internal_v28(uuid,text) from public,anon,authenticated;
|
||
revoke all on function public.sun_my_workspaces(),public.sun_save_app_state_v17(uuid,jsonb,text,bigint),public.sun_v17_mirror_legacy(uuid,jsonb,text),public.sun_v17_prune_backups(uuid,integer) from public,anon;
|
||
grant execute on function public.sun_my_workspaces(),public.sun_save_app_state_v17(uuid,jsonb,text,bigint),public.sun_v17_mirror_legacy(uuid,jsonb,text),public.sun_v17_prune_backups(uuid,integer) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.8-DEVELOPER-PANEL.sql
|
||
-- Sun Catering v17.8 developer-panel additions.
|
||
-- Applied to the current project on 2026-09-01.
|
||
|
||
create or replace function public.sun_platform_list_users()
|
||
returns table(
|
||
user_id uuid,
|
||
email text,
|
||
last_sign_in_at timestamptz,
|
||
created_at timestamptz,
|
||
workspace_id uuid,
|
||
workspace_name text,
|
||
role text,
|
||
display_name text,
|
||
is_active boolean,
|
||
is_platform_admin boolean
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
return query
|
||
select u.id,u.email::text,u.last_sign_in_at,u.created_at,m.workspace_id,w.name,m.role,m.display_name,m.is_active,
|
||
exists(select 1 from public.sun_platform_admins p where p.user_id=u.id)
|
||
from auth.users u
|
||
left join public.sun_workspace_members m on m.user_id=u.id
|
||
left join public.sun_workspaces w on w.id=m.workspace_id
|
||
order by coalesce(w.name,''),coalesce(m.display_name,u.email),u.email;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_platform_list_users() from public;
|
||
grant execute on function public.sun_platform_list_users() to authenticated;
|
||
|
||
create or replace function public.sun_v17_create_backup(p_workspace uuid, p_kind text default 'manual'::text, p_label text default ''::text)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare v_id uuid; v_kind text:=lower(coalesce(p_kind,'manual'));
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then raise exception 'Access denied'; end if;
|
||
if v_kind not in ('daily','manual','pre_restore','migration') then raise exception 'Invalid backup kind'; end if;
|
||
if v_kind<>'daily' and not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if v_kind='daily' and exists(select 1 from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' and created_at::date=current_date) then
|
||
select id into v_id from public.sun_v17_backups where workspace_id=p_workspace and kind='daily' and created_at::date=current_date order by created_at desc limit 1;
|
||
return v_id;
|
||
end if;
|
||
insert into public.sun_v17_backups(workspace_id,kind,label,snapshot,created_by)
|
||
values(p_workspace,v_kind,left(coalesce(p_label,''),240),public.sun_v17_build_snapshot(p_workspace),auth.uid()) returning id into v_id;
|
||
insert into public.sun_v17_workspace_meta(workspace_id,last_backup_on,updated_at) values(p_workspace,current_date,now())
|
||
on conflict(workspace_id) do update set last_backup_on=current_date,updated_at=now();
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_v17_restore_backup(p_workspace uuid, p_backup uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare s jsonb; legacy jsonb;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
perform public.sun_v17_create_backup(p_workspace,'pre_restore','Автоматически перед восстановлением');
|
||
select snapshot into s from public.sun_v17_backups where id=p_backup and workspace_id=p_workspace;
|
||
if s is null then raise exception 'Backup not found'; end if;
|
||
legacy:=s->'legacy';
|
||
if legacy is null or jsonb_typeof(legacy)<>'object' then raise exception 'Backup has no legacy state'; end if;
|
||
perform public.sun_save_app_state(p_workspace,legacy,'backup-restore');
|
||
perform public.sun_v17_mirror_legacy(p_workspace,legacy,'backup-restore');
|
||
insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,client_id,created_by)
|
||
values(p_workspace,'workspace','backup','restore','backup-restore',auth.uid());
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_v17_create_backup(uuid,text,text) from public;
|
||
revoke all on function public.sun_v17_restore_backup(uuid,uuid) from public;
|
||
grant execute on function public.sun_v17_create_backup(uuid,text,text) to authenticated;
|
||
grant execute on function public.sun_v17_restore_backup(uuid,uuid) to authenticated;
|
||
|
||
-- Company owner can attach an already registered account by email without invite codes.
|
||
create or replace function public.sun_owner_add_existing_member(p_workspace uuid,p_email text,p_role text default 'manager')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_user uuid;
|
||
v_role text:=lower(coalesce(p_role,'manager'));
|
||
v_display text;
|
||
v_max integer;
|
||
v_count integer;
|
||
begin
|
||
if auth.uid() is null then raise exception 'Authentication required'; end if;
|
||
if public.sun_member_role(p_workspace)<>'admin' and not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Нет права управлять сотрудниками'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Сотрудники недоступны на текущем тарифе'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Для сотрудника выберите рабочую роль'; end if;
|
||
select id,coalesce(nullif(raw_user_meta_data->>'name',''),split_part(email,'@',1)) into v_user,v_display
|
||
from auth.users where lower(email)=lower(trim(p_email)) limit 1;
|
||
if v_user is null then raise exception 'Аккаунт с таким email ещё не зарегистрирован'; end if;
|
||
select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
if not exists(select 1 from public.sun_workspace_members where workspace_id=p_workspace and user_id=v_user and is_active=true) then
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%)',v_max; end if;
|
||
end if;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
|
||
values(p_workspace,v_user,v_role,v_display,true,public.sun_role_default_permissions(v_role),now())
|
||
on conflict(workspace_id,user_id) do update set role=excluded.role,display_name=coalesce(public.sun_workspace_members.display_name,excluded.display_name),is_active=true,permissions=excluded.permissions,updated_at=now();
|
||
return v_user;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_owner_add_existing_member(uuid,text,text) from public;
|
||
grant execute on function public.sun_owner_add_existing_member(uuid,text,text) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.9-DEVELOPER-SETTINGS.sql
|
||
-- Sun Catering v17.9 developer settings.
|
||
-- Safe, additive migration for the existing v17 schema.
|
||
|
||
-- The platform administrator can inspect normalized health for any company.
|
||
-- Ordinary users remain limited to companies where they are active members.
|
||
create or replace function public.sun_v17_entity_snapshot(p_workspace uuid)
|
||
returns jsonb
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then
|
||
raise exception 'Access denied';
|
||
end if;
|
||
return jsonb_build_object(
|
||
'orders',coalesce((
|
||
select jsonb_agg(jsonb_build_object('id',order_id,'version',version,'data',data,'updated_at',updated_at) order by order_id)
|
||
from public.sun_v17_orders where workspace_id=p_workspace
|
||
),'[]'::jsonb),
|
||
'catalog',coalesce((
|
||
select jsonb_agg(jsonb_build_object('id',item_id,'version',version,'data',data,'updated_at',updated_at) order by item_id)
|
||
from public.sun_v17_catalog_items where workspace_id=p_workspace
|
||
),'[]'::jsonb),
|
||
'meta',coalesce((
|
||
select to_jsonb(meta) from public.sun_v17_workspace_meta meta where workspace_id=p_workspace
|
||
),'{}'::jsonb)
|
||
);
|
||
end;
|
||
$$;
|
||
|
||
-- The platform administrator can list backups for any company from the
|
||
-- protected developer panel. Members can still list their own company's rows.
|
||
create or replace function public.sun_v17_list_backups(p_workspace uuid, p_limit integer default 30)
|
||
returns table(id uuid, kind text, label text, created_at timestamptz, created_by uuid)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null and not public.sun_is_platform_admin() then
|
||
raise exception 'Access denied';
|
||
end if;
|
||
return query
|
||
select backup.id,backup.kind,backup.label,backup.created_at,backup.created_by
|
||
from public.sun_v17_backups backup
|
||
where backup.workspace_id=p_workspace
|
||
order by backup.created_at desc
|
||
limit greatest(1,least(coalesce(p_limit,30),100));
|
||
end;
|
||
$$;
|
||
|
||
-- Global technical error directory. It intentionally excludes stack/context
|
||
-- from the browser result; those fields can contain sensitive implementation data.
|
||
create or replace function public.sun_platform_list_errors(p_workspace uuid default null, p_limit integer default 80)
|
||
returns table(
|
||
id uuid,
|
||
workspace_id uuid,
|
||
workspace_name text,
|
||
user_id uuid,
|
||
client_id text,
|
||
app_version text,
|
||
level text,
|
||
message text,
|
||
created_at timestamptz
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then
|
||
raise exception 'Platform administrator required';
|
||
end if;
|
||
return query
|
||
select err.id,err.workspace_id,company.name,err.user_id,err.client_id,err.app_version,err.level,err.message,err.created_at
|
||
from public.sun_v17_error_events err
|
||
left join public.sun_workspaces company on company.id=err.workspace_id
|
||
where p_workspace is null or err.workspace_id=p_workspace
|
||
order by err.created_at desc
|
||
limit greatest(1,least(coalesce(p_limit,80),200));
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_v17_entity_snapshot(uuid) from public, anon;
|
||
revoke all on function public.sun_v17_list_backups(uuid,integer) from public, anon;
|
||
revoke all on function public.sun_platform_list_errors(uuid,integer) from public, anon;
|
||
grant execute on function public.sun_v17_entity_snapshot(uuid) to authenticated;
|
||
grant execute on function public.sun_v17_list_backups(uuid,integer) to authenticated;
|
||
grant execute on function public.sun_platform_list_errors(uuid,integer) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-DEVELOPER-V22.sql
|
||
-- Caterium / Sun Catering v17.5.22 developer console
|
||
-- Additive only. Gives platform admins a server-enforced developer console API.
|
||
|
||
create table if not exists public.sun_platform_audit_events (
|
||
id bigint generated by default as identity primary key,
|
||
actor_user_id uuid null references auth.users(id) on delete set null,
|
||
action text not null,
|
||
target_workspace_id uuid null references public.sun_workspaces(id) on delete set null,
|
||
target_user_id uuid null references auth.users(id) on delete set null,
|
||
details jsonb not null default '{}'::jsonb,
|
||
created_at timestamptz not null default now()
|
||
);
|
||
|
||
create index if not exists sun_platform_audit_events_created_idx on public.sun_platform_audit_events(created_at desc);
|
||
create index if not exists sun_platform_audit_events_workspace_idx on public.sun_platform_audit_events(target_workspace_id,created_at desc);
|
||
create index if not exists sun_platform_audit_events_user_idx on public.sun_platform_audit_events(target_user_id,created_at desc);
|
||
alter table public.sun_platform_audit_events enable row level security;
|
||
revoke all on public.sun_platform_audit_events from anon, authenticated;
|
||
|
||
create or replace function public.sun_platform_log_event(
|
||
p_action text,
|
||
p_workspace uuid default null,
|
||
p_user uuid default null,
|
||
p_details jsonb default '{}'::jsonb
|
||
) returns bigint
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_id bigint;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if nullif(trim(coalesce(p_action,'')),'') is null then raise exception 'Action required'; end if;
|
||
insert into public.sun_platform_audit_events(actor_user_id,action,target_workspace_id,target_user_id,details)
|
||
values(auth.uid(),left(trim(p_action),120),p_workspace,p_user,coalesce(p_details,'{}'::jsonb)) returning id into v_id;
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_dashboard()
|
||
returns jsonb
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_companies bigint; v_accounts bigint; v_admins bigint; v_active bigint; v_trial bigint; v_locked bigint;
|
||
v_errors bigint; v_backups bigint; v_members bigint; v_last_state timestamptz; v_last_backup timestamptz;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
select count(*) into v_companies from public.sun_workspaces;
|
||
select count(*) into v_accounts from auth.users;
|
||
select count(*) into v_admins from public.sun_platform_admins;
|
||
select count(*) into v_members from public.sun_workspace_members where is_active=true;
|
||
select count(*) into v_active from public.sun_workspace_subscriptions where status='active' and coalesce(current_period_end,'infinity'::timestamptz)>now();
|
||
select count(*) into v_trial from public.sun_workspace_subscriptions where status='trialing' and coalesce(trial_ends_at,'infinity'::timestamptz)>now();
|
||
select count(*) into v_locked from public.sun_workspaces w where public.sun_subscription_access_mode(w.id) in ('read_only','blocked');
|
||
select count(*) into v_errors from public.sun_v17_error_events where created_at>now()-interval '24 hours';
|
||
select count(*) into v_backups from public.sun_v17_backups where created_at>now()-interval '24 hours';
|
||
select max(updated_at) into v_last_state from public.sun_app_state;
|
||
select max(created_at) into v_last_backup from public.sun_v17_backups;
|
||
return jsonb_build_object(
|
||
'companies',v_companies,'accounts',v_accounts,'platform_admins',v_admins,'memberships',v_members,
|
||
'active_subscriptions',v_active,'trials',v_trial,'restricted_companies',v_locked,
|
||
'errors_24h',v_errors,'backups_24h',v_backups,'last_state_at',v_last_state,'last_backup_at',v_last_backup,
|
||
'database_size',pg_size_pretty(pg_database_size(current_database())),
|
||
'postgres_version',current_setting('server_version')
|
||
);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_list_activity(p_limit integer default 120)
|
||
returns table(
|
||
id bigint, action text, actor_user_id uuid, actor_email text,
|
||
workspace_id uuid, workspace_name text, target_user_id uuid, target_email text,
|
||
details jsonb, created_at timestamptz
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
return query
|
||
select a.id,a.action,a.actor_user_id,au.email::text,a.target_workspace_id,w.name,a.target_user_id,tu.email::text,a.details,a.created_at
|
||
from public.sun_platform_audit_events a
|
||
left join auth.users au on au.id=a.actor_user_id
|
||
left join public.sun_workspaces w on w.id=a.target_workspace_id
|
||
left join auth.users tu on tu.id=a.target_user_id
|
||
order by a.created_at desc
|
||
limit greatest(1,least(coalesce(p_limit,120),500));
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_support_snapshot(p_workspace uuid)
|
||
returns table(workspace_id uuid,payload jsonb,revision bigint,updated_at timestamptz,client_id text)
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if not exists(select 1 from public.sun_workspaces where id=p_workspace) then raise exception 'Workspace not found'; end if;
|
||
perform public.sun_platform_log_event('support.open',p_workspace,null,jsonb_build_object('mode','read_only'));
|
||
return query
|
||
select s.workspace_id,s.payload,s.revision,s.updated_at,s.client_id
|
||
from public.sun_app_state s where s.workspace_id=p_workspace;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_workspace_diagnostics(p_workspace uuid)
|
||
returns jsonb
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare v jsonb;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if not exists(select 1 from public.sun_workspaces where id=p_workspace) then raise exception 'Workspace not found'; end if;
|
||
select jsonb_build_object(
|
||
'workspace_id',w.id,'name',w.name,'created_at',w.created_at,
|
||
'revision',coalesce(s.revision,0),'state_updated_at',s.updated_at,'state_client_id',s.client_id,
|
||
'members',(select count(*) from public.sun_workspace_members m where m.workspace_id=w.id and m.is_active),
|
||
'orders',(select count(*) from public.sun_v17_orders o where o.workspace_id=w.id),
|
||
'clients',(select count(*) from public.sun_v17_clients c where c.workspace_id=w.id),
|
||
'catalog_items',(select count(*) from public.sun_v17_catalog_items c where c.workspace_id=w.id),
|
||
'settings',(select count(*) from public.sun_v17_settings x where x.workspace_id=w.id),
|
||
'backups',(select count(*) from public.sun_v17_backups b where b.workspace_id=w.id),
|
||
'latest_backup',(select max(created_at) from public.sun_v17_backups b where b.workspace_id=w.id),
|
||
'errors_24h',(select count(*) from public.sun_v17_error_events e where e.workspace_id=w.id and e.created_at>now()-interval '24 hours'),
|
||
'latest_error',(select max(created_at) from public.sun_v17_error_events e where e.workspace_id=w.id),
|
||
'access_mode',public.sun_subscription_access_mode(w.id)
|
||
) into v
|
||
from public.sun_workspaces w left join public.sun_app_state s on s.workspace_id=w.id
|
||
where w.id=p_workspace;
|
||
return v;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_list_workspace_features(p_workspace uuid)
|
||
returns table(
|
||
feature_key text, plan_enabled boolean, override_enabled boolean,
|
||
override_expires_at timestamptz, effective_enabled boolean, note text
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare v_plan text;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
select plan_id into v_plan from public.sun_workspace_subscriptions where workspace_id=p_workspace;
|
||
return query
|
||
with features as (
|
||
select distinct pf.feature_key from public.sun_plan_features pf
|
||
)
|
||
select f.feature_key,
|
||
coalesce(pf.enabled,false),
|
||
case when o.expires_at is null or o.expires_at>now() then o.enabled else null end,
|
||
o.expires_at,
|
||
case when o.feature_key is not null and (o.expires_at is null or o.expires_at>now()) then o.enabled else coalesce(pf.enabled,false) end,
|
||
o.note
|
||
from features f
|
||
left join public.sun_plan_features pf on pf.plan_id=v_plan and pf.feature_key=f.feature_key
|
||
left join public.sun_workspace_feature_overrides o on o.workspace_id=p_workspace and o.feature_key=f.feature_key
|
||
order by f.feature_key;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_set_plan_feature(p_plan text,p_feature text,p_enabled boolean)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if not exists(select 1 from public.sun_plans where id=p_plan) then raise exception 'Unknown plan'; end if;
|
||
if nullif(trim(coalesce(p_feature,'')),'') is null then raise exception 'Feature required'; end if;
|
||
insert into public.sun_plan_features(plan_id,feature_key,enabled) values(p_plan,p_feature,p_enabled)
|
||
on conflict(plan_id,feature_key) do update set enabled=excluded.enabled;
|
||
perform public.sun_platform_log_event('plan.feature.set',null,null,jsonb_build_object('plan',p_plan,'feature',p_feature,'enabled',p_enabled));
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_set_plan_max_members(p_plan text,p_max_members integer)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if p_max_members is not null and (p_max_members<1 or p_max_members>10000) then raise exception 'Invalid member limit'; end if;
|
||
update public.sun_plans set max_members=p_max_members,updated_at=now() where id=p_plan;
|
||
if not found then raise exception 'Unknown plan'; end if;
|
||
perform public.sun_platform_log_event('plan.members.set',null,null,jsonb_build_object('plan',p_plan,'max_members',p_max_members));
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_seed_workspace_catalog(
|
||
p_workspace uuid,
|
||
p_boxes jsonb,
|
||
p_catalog_version text default '',
|
||
p_replace boolean default false
|
||
) returns integer
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_payload jsonb; v_existing jsonb; v_entry jsonb; v_item jsonb; v_id text; v_count integer:=0;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if jsonb_typeof(p_boxes)<>'array' then raise exception 'Catalog must be an array'; end if;
|
||
if not exists(select 1 from public.sun_workspaces where id=p_workspace) then raise exception 'Workspace not found'; end if;
|
||
|
||
select payload into v_payload from public.sun_app_state where workspace_id=p_workspace for update;
|
||
if v_payload is null then
|
||
v_payload:=jsonb_build_object('format','sun-cloud-v2','version',2,'storage','{}'::jsonb);
|
||
insert into public.sun_app_state(workspace_id,payload,revision,updated_by)
|
||
values(p_workspace,v_payload,0,auth.uid())
|
||
on conflict(workspace_id) do nothing;
|
||
end if;
|
||
|
||
v_existing:=coalesce(v_payload->'storage'->'sunBoxes'->'v','[]'::jsonb);
|
||
if not p_replace and jsonb_typeof(v_existing)='array' and jsonb_array_length(v_existing)>0 then
|
||
raise exception 'Catalog already populated';
|
||
end if;
|
||
|
||
v_entry:=jsonb_build_object('t','j','v',p_boxes);
|
||
v_payload:=jsonb_set(coalesce(v_payload,'{}'::jsonb),'{storage,sunBoxes}',v_entry,true);
|
||
if nullif(coalesce(p_catalog_version,''),'') is not null then
|
||
v_payload:=jsonb_set(v_payload,'{storage,sunOfficialCatalogVersion}',jsonb_build_object('t','s','v',p_catalog_version),true);
|
||
end if;
|
||
update public.sun_app_state set payload=v_payload,revision=revision+1,client_id='platform-catalog',updated_by=auth.uid(),updated_at=now() where workspace_id=p_workspace;
|
||
|
||
if p_replace then delete from public.sun_v17_catalog_items where workspace_id=p_workspace; end if;
|
||
for v_item in select value from jsonb_array_elements(p_boxes) loop
|
||
v_id:=coalesce(nullif(v_item->>'id',''),gen_random_uuid()::text);
|
||
insert into public.sun_v17_catalog_items(workspace_id,item_id,data,version,created_by,updated_by)
|
||
values(p_workspace,v_id,v_item,1,auth.uid(),auth.uid())
|
||
on conflict(workspace_id,item_id) do update set data=excluded.data,version=sun_v17_catalog_items.version+1,updated_by=auth.uid(),updated_at=now();
|
||
v_count:=v_count+1;
|
||
end loop;
|
||
|
||
insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,client_id,created_by)
|
||
values(p_workspace,'catalog','starter',case when p_replace then 'replace' else 'seed' end,'platform-catalog',auth.uid());
|
||
perform public.sun_platform_log_event(case when p_replace then 'catalog.force_apply' else 'catalog.seed' end,p_workspace,null,jsonb_build_object('count',v_count,'version',p_catalog_version));
|
||
return v_count;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_platform_log_event(text,uuid,uuid,jsonb) from public, anon;
|
||
revoke all on function public.sun_platform_dashboard() from public, anon;
|
||
revoke all on function public.sun_platform_list_activity(integer) from public, anon;
|
||
revoke all on function public.sun_platform_support_snapshot(uuid) from public, anon;
|
||
revoke all on function public.sun_platform_workspace_diagnostics(uuid) from public, anon;
|
||
revoke all on function public.sun_platform_list_workspace_features(uuid) from public, anon;
|
||
revoke all on function public.sun_platform_set_plan_feature(text,text,boolean) from public, anon;
|
||
revoke all on function public.sun_platform_set_plan_max_members(text,integer) from public, anon;
|
||
revoke all on function public.sun_platform_seed_workspace_catalog(uuid,jsonb,text,boolean) from public, anon;
|
||
|
||
grant execute on function public.sun_platform_log_event(text,uuid,uuid,jsonb) to authenticated;
|
||
grant execute on function public.sun_platform_dashboard() to authenticated;
|
||
grant execute on function public.sun_platform_list_activity(integer) to authenticated;
|
||
grant execute on function public.sun_platform_support_snapshot(uuid) to authenticated;
|
||
grant execute on function public.sun_platform_workspace_diagnostics(uuid) to authenticated;
|
||
grant execute on function public.sun_platform_list_workspace_features(uuid) to authenticated;
|
||
grant execute on function public.sun_platform_set_plan_feature(text,text,boolean) to authenticated;
|
||
grant execute on function public.sun_platform_set_plan_max_members(text,integer) to authenticated;
|
||
grant execute on function public.sun_platform_seed_workspace_catalog(uuid,jsonb,text,boolean) to authenticated;
|
||
|
||
create or replace function public.sun_platform_reset_feature_override(p_workspace uuid,p_feature text)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
delete from public.sun_workspace_feature_overrides where workspace_id=p_workspace and feature_key=p_feature;
|
||
perform public.sun_platform_log_event('feature.override.reset',p_workspace,null,jsonb_build_object('feature',p_feature));
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_platform_create_company_v22(
|
||
p_name text,
|
||
p_owner_email text default null,
|
||
p_plan text default 'full',
|
||
p_days integer default 30,
|
||
p_boxes jsonb default '[]'::jsonb,
|
||
p_catalog_version text default ''
|
||
) returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_base jsonb; v_ws uuid; v_profile jsonb; v_payload jsonb; v_count integer;
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
select public.caterium_platform_create_company(p_name,p_owner_email,p_plan,p_days,'empty') into v_base;
|
||
v_ws:=(v_base->>'workspace_id')::uuid;
|
||
v_profile:=jsonb_build_object('name',coalesce(nullif(trim(p_name),''),'Новая компания'),'ownerEmail',coalesce(p_owner_email,''),'createdAt',now());
|
||
select payload into v_payload from public.sun_app_state where workspace_id=v_ws for update;
|
||
v_payload:=jsonb_set(v_payload,'{storage,sunCompanyProfileV1}',jsonb_build_object('t','j','v',v_profile),true);
|
||
v_payload:=jsonb_set(v_payload,'{storage,sunOrders}',jsonb_build_object('t','j','v','[]'::jsonb),true);
|
||
v_payload:=jsonb_set(v_payload,'{storage,sunClientsV2}',jsonb_build_object('t','j','v','[]'::jsonb),true);
|
||
update public.sun_app_state set payload=v_payload,updated_by=auth.uid(),updated_at=now() where workspace_id=v_ws;
|
||
select public.sun_platform_seed_workspace_catalog(v_ws,coalesce(p_boxes,'[]'::jsonb),p_catalog_version,true) into v_count;
|
||
perform public.sun_platform_log_event('company.create',v_ws,null,jsonb_build_object('name',p_name,'owner_email',p_owner_email,'plan',p_plan,'days',p_days,'catalog_count',v_count));
|
||
return coalesce(v_base,'{}'::jsonb)||jsonb_build_object('catalog_count',v_count);
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_platform_reset_feature_override(uuid,text) from public, anon;
|
||
revoke all on function public.sun_platform_create_company_v22(text,text,text,integer,jsonb,text) from public, anon;
|
||
grant execute on function public.sun_platform_reset_feature_override(uuid,text) to authenticated;
|
||
grant execute on function public.sun_platform_create_company_v22(text,text,text,integer,jsonb,text) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-DEVELOPER-V22-AAL2.sql
|
||
-- Developer console v22 hardening: platform-wide actions require MFA AAL2.
|
||
|
||
create or replace function public.sun_require_platform_admin_aal2()
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.sun_is_platform_admin() then raise exception 'Platform administrator required'; end if;
|
||
if coalesce(auth.jwt()->>'aal','aal1') <> 'aal2' then raise exception 'Developer MFA AAL2 required'; end if;
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_require_platform_admin_aal2() from public, anon, authenticated;
|
||
|
||
create or replace function public.sun_platform_list_companies_v22()
|
||
returns table(workspace_id uuid,workspace_name text,created_at timestamptz,plan_id text,plan_name text,status text,access_mode text,trial_ends_at timestamptz,current_period_end timestamptz,grace_until timestamptz,member_count bigint,max_members integer,owner_email text,pending_owner_email text)
|
||
language plpgsql stable security definer set search_path='public','auth' as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query
|
||
select w.id,w.name,w.created_at,s.plan_id,p.name,s.status,public.sun_subscription_access_mode(w.id),s.trial_ends_at,s.current_period_end,s.grace_until,
|
||
(select count(*) from public.sun_workspace_members m where m.workspace_id=w.id and m.is_active),p.max_members,
|
||
(select u.email::text from public.sun_workspace_members m join auth.users u on u.id=m.user_id where m.workspace_id=w.id and m.role='admin' and m.is_active order by m.created_at asc limit 1),
|
||
(select i.email from public.caterium_company_owner_invites i where i.workspace_id=w.id and i.used_at is null and i.expires_at>now() order by i.created_at desc limit 1)
|
||
from public.sun_workspaces w left join public.sun_workspace_subscriptions s on s.workspace_id=w.id left join public.sun_plans p on p.id=s.plan_id order by w.created_at desc;
|
||
end;$$;
|
||
|
||
create or replace function public.sun_platform_list_users_v22()
|
||
returns table(user_id uuid,email text,last_sign_in_at timestamptz,created_at timestamptz,workspace_id uuid,workspace_name text,role text,display_name text,is_active boolean,is_platform_admin boolean)
|
||
language plpgsql stable security definer set search_path='public','auth' as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query select u.id,u.email::text,u.last_sign_in_at,u.created_at,m.workspace_id,w.name,m.role,m.display_name,m.is_active,
|
||
exists(select 1 from public.sun_platform_admins p where p.user_id=u.id)
|
||
from auth.users u left join public.sun_workspace_members m on m.user_id=u.id left join public.sun_workspaces w on w.id=m.workspace_id
|
||
order by coalesce(w.name,''),coalesce(m.display_name,u.email),u.email;
|
||
end;$$;
|
||
|
||
create or replace function public.sun_platform_list_errors_v22(p_workspace uuid default null,p_limit integer default 80)
|
||
returns table(id uuid,workspace_id uuid,workspace_name text,user_id uuid,client_id text,app_version text,level text,message text,created_at timestamptz)
|
||
language plpgsql stable security definer set search_path='public' as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query select err.id,err.workspace_id,company.name,err.user_id,err.client_id,err.app_version,err.level,err.message,err.created_at
|
||
from public.sun_v17_error_events err left join public.sun_workspaces company on company.id=err.workspace_id
|
||
where p_workspace is null or err.workspace_id=p_workspace order by err.created_at desc limit greatest(1,least(coalesce(p_limit,80),200));
|
||
end;$$;
|
||
|
||
-- Harden v22 developer functions.
|
||
create or replace function public.sun_platform_dashboard()
|
||
returns jsonb language plpgsql stable security definer set search_path='public','auth' as $$
|
||
declare v_companies bigint; v_accounts bigint; v_admins bigint; v_active bigint; v_trial bigint; v_locked bigint; v_errors bigint; v_backups bigint; v_members bigint; v_last_state timestamptz; v_last_backup timestamptz;
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
select count(*) into v_companies from public.sun_workspaces; select count(*) into v_accounts from auth.users; select count(*) into v_admins from public.sun_platform_admins; select count(*) into v_members from public.sun_workspace_members where is_active=true;
|
||
select count(*) into v_active from public.sun_workspace_subscriptions where status='active' and coalesce(current_period_end,'infinity'::timestamptz)>now(); select count(*) into v_trial from public.sun_workspace_subscriptions where status='trialing' and coalesce(trial_ends_at,'infinity'::timestamptz)>now();
|
||
select count(*) into v_locked from public.sun_workspaces w where public.sun_subscription_access_mode(w.id) in ('read_only','blocked'); select count(*) into v_errors from public.sun_v17_error_events where created_at>now()-interval '24 hours'; select count(*) into v_backups from public.sun_v17_backups where created_at>now()-interval '24 hours'; select max(updated_at) into v_last_state from public.sun_app_state; select max(created_at) into v_last_backup from public.sun_v17_backups;
|
||
return jsonb_build_object('companies',v_companies,'accounts',v_accounts,'platform_admins',v_admins,'memberships',v_members,'active_subscriptions',v_active,'trials',v_trial,'restricted_companies',v_locked,'errors_24h',v_errors,'backups_24h',v_backups,'last_state_at',v_last_state,'last_backup_at',v_last_backup,'database_size',pg_size_pretty(pg_database_size(current_database())),'postgres_version',current_setting('server_version'));
|
||
end;$$;
|
||
|
||
-- Change the v22 function bodies by replacing the first platform check with the AAL2 guard.
|
||
-- Definitions are kept in SUPABASE-DEVELOPER-V22.sql; this block updates privileges for secure wrappers.
|
||
revoke all on function public.sun_platform_list_users() from anon;
|
||
revoke all on function public.sun_owner_add_existing_member(uuid,text,text) from anon;
|
||
revoke all on function public.sun_platform_list_companies_v22() from public,anon;
|
||
revoke all on function public.sun_platform_list_users_v22() from public,anon;
|
||
revoke all on function public.sun_platform_list_errors_v22(uuid,integer) from public,anon;
|
||
grant execute on function public.sun_platform_list_companies_v22() to authenticated;
|
||
grant execute on function public.sun_platform_list_users_v22() to authenticated;
|
||
grant execute on function public.sun_platform_list_errors_v22(uuid,integer) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-REGISTRATION-USERS-V27.sql
|
||
-- Caterium v17.5.27: clear registration + email-bound employee invitations
|
||
|
||
alter table public.sun_workspace_invites add column if not exists email text;
|
||
alter table public.sun_workspace_invites add column if not exists display_name text;
|
||
|
||
create index if not exists sun_workspace_invites_pending_email_v27_idx
|
||
on public.sun_workspace_invites(workspace_id, lower(email))
|
||
where used_at is null;
|
||
|
||
-- Public Caterium signups and invite signups do not require clicking an email-confirmation link.
|
||
-- This keeps the existing Auth signup endpoint/rate limits while marking these app-originated users verified.
|
||
create or replace function public.caterium_autoconfirm_signup_v25()
|
||
returns trigger
|
||
language plpgsql
|
||
security definer
|
||
set search_path = 'auth','public'
|
||
as $$
|
||
begin
|
||
if coalesce(new.raw_user_meta_data->>'registration_source','') in ('caterium_public_signup','caterium_invite_signup') then
|
||
new.email_confirmed_at := coalesce(new.email_confirmed_at, now());
|
||
new.raw_user_meta_data := jsonb_set(coalesce(new.raw_user_meta_data,'{}'::jsonb),'{email_verified}','true'::jsonb,true);
|
||
end if;
|
||
return new;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.caterium_autoverify_identity_v25()
|
||
returns trigger
|
||
language plpgsql
|
||
security definer
|
||
set search_path = 'auth','public'
|
||
as $$
|
||
begin
|
||
if new.provider='email' and exists (
|
||
select 1 from auth.users u
|
||
where u.id=new.user_id
|
||
and coalesce(u.raw_user_meta_data->>'registration_source','') in ('caterium_public_signup','caterium_invite_signup')
|
||
) then
|
||
new.identity_data := jsonb_set(coalesce(new.identity_data,'{}'::jsonb),'{email_verified}','true'::jsonb,true);
|
||
end if;
|
||
return new;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_create_invite_v27(
|
||
p_workspace uuid,
|
||
p_email text,
|
||
p_display_name text default '',
|
||
p_role text default 'manager'
|
||
)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text := lower(coalesce(p_role,'manager'));
|
||
v_email text := lower(trim(coalesce(p_email,'')));
|
||
v_name text := trim(coalesce(p_display_name,''));
|
||
v_max integer;
|
||
v_active integer;
|
||
v_pending integer;
|
||
begin
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Недостаточно прав для добавления пользователей'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Некорректная роль'; end if;
|
||
if v_email='' or v_email !~* '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$' then raise exception 'Введите корректный email сотрудника'; end if;
|
||
if v_name='' then v_name:=split_part(v_email,'@',1); end if;
|
||
|
||
if exists(
|
||
select 1
|
||
from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=p_workspace and m.is_active=true and lower(coalesce(u.email,''))=v_email
|
||
) then
|
||
raise exception 'Пользователь с этим email уже добавлен в компанию';
|
||
end if;
|
||
|
||
-- Remove stale invites and replace the previous pending invite for the same email.
|
||
delete from public.sun_workspace_invites
|
||
where workspace_id=p_workspace and used_at is null
|
||
and (expires_at<=now() or lower(coalesce(email,''))=v_email);
|
||
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s
|
||
join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
|
||
if v_max is not null then
|
||
select count(*)::int into v_active
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and is_active=true;
|
||
select count(*)::int into v_pending
|
||
from public.sun_workspace_invites
|
||
where workspace_id=p_workspace and used_at is null and expires_at>now();
|
||
if v_active+v_pending>=v_max then
|
||
raise exception 'Достигнут лимит пользователей тарифа (%)',v_max;
|
||
end if;
|
||
end if;
|
||
|
||
insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by,email,display_name)
|
||
values(p_workspace,v_role,public.sun_role_default_permissions(v_role),auth.uid(),v_email,left(v_name,120))
|
||
returning token into v_token;
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_list_workspace_invites_v27(p_workspace uuid)
|
||
returns table(
|
||
token uuid,
|
||
email text,
|
||
display_name text,
|
||
role text,
|
||
created_at timestamptz,
|
||
expires_at timestamptz,
|
||
status text
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Недостаточно прав для просмотра приглашений'; end if;
|
||
return query
|
||
select i.token,i.email,i.display_name,i.role,i.created_at,i.expires_at,
|
||
case when i.expires_at<=now() then 'expired' else 'pending' end::text
|
||
from public.sun_workspace_invites i
|
||
where i.workspace_id=p_workspace and i.used_at is null
|
||
order by i.created_at desc;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_cancel_invite_v27(p_workspace uuid,p_token uuid)
|
||
returns boolean
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare v_deleted integer;
|
||
begin
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Недостаточно прав для отмены приглашения'; end if;
|
||
delete from public.sun_workspace_invites
|
||
where workspace_id=p_workspace and token=p_token and used_at is null;
|
||
get diagnostics v_deleted = row_count;
|
||
return v_deleted>0;
|
||
end;
|
||
$$;
|
||
|
||
-- Token is the secret. This preview intentionally reveals only the invited company/name/email/role.
|
||
create or replace function public.sun_invite_preview_v27(p_token uuid)
|
||
returns table(
|
||
workspace_id uuid,
|
||
workspace_name text,
|
||
email text,
|
||
display_name text,
|
||
role text,
|
||
expires_at timestamptz,
|
||
is_valid boolean
|
||
)
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
select w.id,w.name,i.email,i.display_name,i.role,i.expires_at,
|
||
(i.used_at is null and i.expires_at>now()) as is_valid
|
||
from public.sun_workspace_invites i
|
||
join public.sun_workspaces w on w.id=i.workspace_id
|
||
where i.token=p_token
|
||
limit 1
|
||
$$;
|
||
|
||
-- Keep the existing RPC name for backward compatibility, but make new email-bound invites safe.
|
||
create or replace function public.sun_accept_invite(p_token uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_user uuid := auth.uid();
|
||
v_invite public.sun_workspace_invites%rowtype;
|
||
v_display text;
|
||
v_user_email text;
|
||
v_max integer;
|
||
v_count integer;
|
||
begin
|
||
if v_user is null then raise exception 'Сначала войдите в Caterium'; end if;
|
||
select * into v_invite from public.sun_workspace_invites where token=p_token for update;
|
||
if not found then raise exception 'Приглашение не найдено'; end if;
|
||
if v_invite.used_at is not null then raise exception 'Приглашение уже использовано'; end if;
|
||
if v_invite.expires_at < now() then raise exception 'Срок действия приглашения истёк'; end if;
|
||
|
||
select lower(coalesce(email,'')),
|
||
coalesce(nullif(v_invite.display_name,''),nullif(raw_user_meta_data->>'name',''),split_part(coalesce(email,'Сотрудник'),'@',1))
|
||
into v_user_email,v_display
|
||
from auth.users where id=v_user;
|
||
|
||
if nullif(lower(trim(coalesce(v_invite.email,''))),'') is not null
|
||
and lower(trim(v_invite.email))<>v_user_email then
|
||
raise exception 'Это приглашение создано для другого email';
|
||
end if;
|
||
|
||
if public.sun_subscription_access_mode(v_invite.workspace_id)<>'full' then raise exception 'Подписка компании неактивна'; end if;
|
||
if not public.sun_workspace_has_feature(v_invite.workspace_id,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s
|
||
join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=v_invite.workspace_id;
|
||
select count(*)::int into v_count
|
||
from public.sun_workspace_members
|
||
where workspace_id=v_invite.workspace_id and is_active=true;
|
||
if not exists(select 1 from public.sun_workspace_members where workspace_id=v_invite.workspace_id and user_id=v_user and is_active=true) then
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if;
|
||
end if;
|
||
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
|
||
values(v_invite.workspace_id,v_user,v_invite.role,v_display,true,coalesce(v_invite.permissions,public.sun_role_default_permissions(v_invite.role)),now())
|
||
on conflict(workspace_id,user_id) do update
|
||
set role=excluded.role,
|
||
display_name=coalesce(nullif(public.sun_workspace_members.display_name,''),excluded.display_name),
|
||
is_active=true,
|
||
permissions=excluded.permissions,
|
||
updated_at=now();
|
||
|
||
update public.sun_workspace_invites set used_by=v_user,used_at=now() where token=p_token;
|
||
return v_invite.workspace_id;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_create_invite_v27(uuid,text,text,text) from public,anon;
|
||
revoke all on function public.sun_list_workspace_invites_v27(uuid) from public,anon;
|
||
revoke all on function public.sun_cancel_invite_v27(uuid,uuid) from public,anon;
|
||
revoke all on function public.sun_invite_preview_v27(uuid) from public;
|
||
revoke all on function public.sun_accept_invite(uuid) from public,anon;
|
||
|
||
grant execute on function public.sun_create_invite_v27(uuid,text,text,text) to authenticated;
|
||
grant execute on function public.sun_list_workspace_invites_v27(uuid) to authenticated;
|
||
grant execute on function public.sun_cancel_invite_v27(uuid,uuid) to authenticated;
|
||
grant execute on function public.sun_invite_preview_v27(uuid) to anon,authenticated;
|
||
grant execute on function public.sun_accept_invite(uuid) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-EMPLOYEE-MANAGEMENT-V28.sql
|
||
-- Caterium v17.7.4-ish: employee invitation/creation RPCs backing the
|
||
-- caterium-create-employee Edge Function.
|
||
--
|
||
-- These were applied directly to the production database and were missing
|
||
-- from the repository (found during the 2026-09-12 security/infra audit).
|
||
-- Recorded here for auditability, verbatim via pg_get_functiondef() against
|
||
-- the live database on 2026-09-12 — not re-applied as part of this commit.
|
||
--
|
||
-- Authorization model: both are SECURITY DEFINER but re-check the caller's
|
||
-- own privileges internally (auth.uid(), caterium_is_workspace_owner /
|
||
-- sun_is_platform_admin, plan/feature gates, member limits) before doing
|
||
-- anything privileged - the Edge Function's service_role client only ever
|
||
-- calls auth.admin.createUser; workspace-membership authorization always
|
||
-- happens here, in Postgres, as the calling user.
|
||
|
||
create or replace function public.sun_employee_prepare_v28(p_workspace uuid, p_email text, p_display_name text DEFAULT ''::text, p_role text DEFAULT 'manager'::text) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public', 'auth' AS $function$ declare v_email text:=lower(trim(coalesce(p_email,''))); v_name text:=trim(coalesce(p_display_name,'')); v_role text:=lower(coalesce(p_role,'manager')); v_user uuid; v_member public.sun_workspace_members%rowtype; v_max integer; v_count integer; begin if auth.uid() is null then raise exception 'Сначала войдите в Caterium'; end if; if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if; if v_email='' or v_email !~* '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$' then raise exception 'Введите корректный email сотрудника'; end if; if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Сотруднику нельзя назначить роль владельца'; end if; if v_name='' then v_name:=split_part(v_email,'@',1); end if; if public.sun_workspace_access_mode_internal_v28(p_workspace)<>'full' then raise exception 'Подписка компании не позволяет добавлять сотрудников'; end if; if not public.sun_workspace_feature_internal_v28(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if; select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace; select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true; select u.id into v_user from auth.users u where lower(coalesce(u.email,''))=v_email order by u.created_at asc limit 1; if v_user is not null then select * into v_member from public.sun_workspace_members where workspace_id=p_workspace and user_id=v_user limit 1; if found and v_member.is_active then return jsonb_build_object('status','already_member','user_id',v_user,'email',v_email,'display_name',coalesce(nullif(v_member.display_name,''),v_name),'role',v_member.role); end if; end if; if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if; return jsonb_build_object('status',case when v_user is null then 'new' else 'existing' end,'user_id',v_user,'email',v_email,'display_name',v_name,'role',v_role,'max_members',v_max,'active_members',v_count); end; $function$;
|
||
|
||
create or replace function public.sun_employee_finalize_v28(p_workspace uuid, p_user_id uuid, p_display_name text DEFAULT ''::text, p_role text DEFAULT 'manager'::text) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public', 'auth' AS $function$ declare v_name text:=trim(coalesce(p_display_name,'')); v_role text:=lower(coalesce(p_role,'manager')); v_email text; v_max integer; v_count integer; v_already boolean:=false; begin if auth.uid() is null then raise exception 'Сначала войдите в Caterium'; end if; if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if; if public.sun_workspace_access_mode_internal_v28(p_workspace)<>'full' then raise exception 'Подписка компании не позволяет добавлять сотрудников'; end if; if not public.sun_workspace_feature_internal_v28(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if; if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Сотруднику нельзя назначить роль владельца'; end if; select lower(email) into v_email from auth.users where id=p_user_id; if v_email is null then raise exception 'Аккаунт сотрудника не найден'; end if; if v_name='' then v_name:=split_part(v_email,'@',1); end if; select exists(select 1 from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user_id and is_active=true) into v_already; select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace; select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true; if not v_already and v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if; insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at) values(p_workspace,p_user_id,v_role,left(v_name,120),true,public.sun_role_default_permissions(v_role)||jsonb_build_object('users.manage',false),now()) on conflict(workspace_id,user_id) do update set role=excluded.role,display_name=excluded.display_name,is_active=true,permissions=excluded.permissions,updated_at=now(); delete from public.sun_workspace_invites where workspace_id=p_workspace and used_at is null and lower(coalesce(email,''))=v_email; return jsonb_build_object('status',case when v_already then 'updated' else 'added' end,'user_id',p_user_id,'email',v_email,'display_name',v_name,'role',v_role); end; $function$;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-CHAT-V29.sql
|
||
create table if not exists public.sun_chat_threads (
|
||
id uuid primary key default gen_random_uuid(),
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
kind text not null check (kind in ('company','direct','order')),
|
||
title text,
|
||
order_id text,
|
||
direct_key text,
|
||
created_by uuid references auth.users(id) on delete set null,
|
||
created_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now(),
|
||
last_message_at timestamptz
|
||
);
|
||
create unique index if not exists sun_chat_threads_company_uq on public.sun_chat_threads(workspace_id) where kind='company';
|
||
create unique index if not exists sun_chat_threads_order_uq on public.sun_chat_threads(workspace_id,order_id) where kind='order';
|
||
create unique index if not exists sun_chat_threads_direct_uq on public.sun_chat_threads(workspace_id,direct_key) where kind='direct';
|
||
create index if not exists sun_chat_threads_workspace_idx on public.sun_chat_threads(workspace_id,coalesce(last_message_at,created_at) desc);
|
||
|
||
create table if not exists public.sun_chat_participants (
|
||
thread_id uuid not null references public.sun_chat_threads(id) on delete cascade,
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
user_id uuid not null references auth.users(id) on delete cascade,
|
||
created_at timestamptz not null default now(),
|
||
primary key(thread_id,user_id)
|
||
);
|
||
create index if not exists sun_chat_participants_user_idx on public.sun_chat_participants(user_id,workspace_id);
|
||
|
||
create table if not exists public.sun_chat_messages (
|
||
id uuid primary key default gen_random_uuid(),
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
thread_id uuid not null references public.sun_chat_threads(id) on delete cascade,
|
||
sender_user_id uuid not null references auth.users(id) on delete cascade,
|
||
body text not null default '',
|
||
attachments jsonb not null default '[]'::jsonb,
|
||
created_at timestamptz not null default now(),
|
||
edited_at timestamptz,
|
||
deleted_at timestamptz
|
||
);
|
||
create index if not exists sun_chat_messages_thread_idx on public.sun_chat_messages(thread_id,created_at desc);
|
||
create index if not exists sun_chat_messages_workspace_idx on public.sun_chat_messages(workspace_id,created_at desc);
|
||
|
||
create table if not exists public.sun_chat_reads (
|
||
thread_id uuid not null references public.sun_chat_threads(id) on delete cascade,
|
||
workspace_id uuid not null references public.sun_workspaces(id) on delete cascade,
|
||
user_id uuid not null references auth.users(id) on delete cascade,
|
||
last_read_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now(),
|
||
primary key(thread_id,user_id)
|
||
);
|
||
create index if not exists sun_chat_reads_user_idx on public.sun_chat_reads(user_id,workspace_id);
|
||
|
||
alter table public.sun_chat_threads enable row level security;
|
||
alter table public.sun_chat_participants enable row level security;
|
||
alter table public.sun_chat_messages enable row level security;
|
||
alter table public.sun_chat_reads enable row level security;
|
||
|
||
create or replace function public.sun_chat_is_member_v29(p_workspace uuid,p_user uuid default null)
|
||
returns boolean
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
select exists(
|
||
select 1 from public.sun_workspace_members m
|
||
where m.workspace_id=p_workspace
|
||
and m.user_id=coalesce(p_user,auth.uid())
|
||
and m.is_active=true
|
||
)
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_can_access_thread_as_v29(p_thread uuid,p_user uuid)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v public.sun_chat_threads%rowtype;
|
||
begin
|
||
if p_user is null then return false; end if;
|
||
select * into v from public.sun_chat_threads where id=p_thread;
|
||
if not found then return false; end if;
|
||
if not public.sun_chat_is_member_v29(v.workspace_id,p_user) then return false; end if;
|
||
if v.kind='direct' then
|
||
return exists(select 1 from public.sun_chat_participants p where p.thread_id=v.id and p.user_id=p_user);
|
||
end if;
|
||
return true;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_can_access_thread_v29(p_thread uuid)
|
||
returns boolean
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$ select public.sun_chat_can_access_thread_as_v29(p_thread,auth.uid()) $$;
|
||
|
||
create or replace function public.sun_chat_realtime_topic_access_v29(p_topic text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_id uuid;
|
||
begin
|
||
if p_topic like 'sun-chat-workspace:%' then
|
||
begin v_id:=substring(p_topic from length('sun-chat-workspace:')+1)::uuid; exception when others then return false; end;
|
||
return public.sun_chat_is_member_v29(v_id,auth.uid());
|
||
elsif p_topic like 'sun-chat-thread:%' then
|
||
begin v_id:=substring(p_topic from length('sun-chat-thread:')+1)::uuid; exception when others then return false; end;
|
||
return public.sun_chat_can_access_thread_v29(v_id);
|
||
end if;
|
||
return false;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_storage_access_v29(p_name text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth','storage'
|
||
as $$
|
||
declare parts text[]; v_ws uuid; v_thread uuid; v_thread_ws uuid;
|
||
begin
|
||
parts:=storage.foldername(p_name);
|
||
if array_length(parts,1)<2 then return false; end if;
|
||
begin v_ws:=parts[1]::uuid; v_thread:=parts[2]::uuid; exception when others then return false; end;
|
||
select workspace_id into v_thread_ws from public.sun_chat_threads where id=v_thread;
|
||
if v_thread_ws is null or v_thread_ws<>v_ws then return false; end if;
|
||
return public.sun_chat_can_access_thread_as_v29(v_thread,auth.uid());
|
||
end;
|
||
$$;
|
||
|
||
-- Read-only table access for authenticated users; writes go through RPCs.
|
||
revoke all on public.sun_chat_threads,public.sun_chat_participants,public.sun_chat_messages,public.sun_chat_reads from anon,authenticated;
|
||
grant select on public.sun_chat_threads,public.sun_chat_participants,public.sun_chat_messages,public.sun_chat_reads to authenticated;
|
||
|
||
-- RLS read policies.
|
||
drop policy if exists sun_chat_threads_read_v29 on public.sun_chat_threads;
|
||
create policy sun_chat_threads_read_v29 on public.sun_chat_threads for select to authenticated using (public.sun_chat_can_access_thread_v29(id));
|
||
drop policy if exists sun_chat_participants_read_v29 on public.sun_chat_participants;
|
||
create policy sun_chat_participants_read_v29 on public.sun_chat_participants for select to authenticated using (public.sun_chat_can_access_thread_v29(thread_id));
|
||
drop policy if exists sun_chat_messages_read_v29 on public.sun_chat_messages;
|
||
create policy sun_chat_messages_read_v29 on public.sun_chat_messages for select to authenticated using (public.sun_chat_can_access_thread_v29(thread_id));
|
||
drop policy if exists sun_chat_reads_read_v29 on public.sun_chat_reads;
|
||
create policy sun_chat_reads_read_v29 on public.sun_chat_reads for select to authenticated using (user_id=auth.uid() and public.sun_chat_can_access_thread_v29(thread_id));
|
||
|
||
create or replace function public.sun_chat_get_company_thread_v29(p_workspace uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_id uuid;
|
||
begin
|
||
if not public.sun_chat_is_member_v29(p_workspace,auth.uid()) then raise exception 'Нет доступа к чату компании'; end if;
|
||
select id into v_id from public.sun_chat_threads where workspace_id=p_workspace and kind='company' limit 1;
|
||
if v_id is null then
|
||
insert into public.sun_chat_threads(workspace_id,kind,title,created_by) values(p_workspace,'company','Общий чат',auth.uid())
|
||
on conflict (workspace_id) where kind='company' do update set updated_at=excluded.updated_at returning id into v_id;
|
||
end if;
|
||
insert into public.sun_chat_reads(thread_id,workspace_id,user_id,last_read_at,updated_at)
|
||
values(v_id,p_workspace,auth.uid(),'epoch'::timestamptz,now()) on conflict(thread_id,user_id) do nothing;
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_get_order_thread_v29(p_workspace uuid,p_order_id text)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_id uuid; v_order text:=trim(coalesce(p_order_id,''));
|
||
begin
|
||
if not public.sun_chat_is_member_v29(p_workspace,auth.uid()) then raise exception 'Нет доступа к обсуждениям заказов'; end if;
|
||
if v_order='' then raise exception 'Номер заказа не указан'; end if;
|
||
select id into v_id from public.sun_chat_threads where workspace_id=p_workspace and kind='order' and order_id=v_order limit 1;
|
||
if v_id is null then
|
||
insert into public.sun_chat_threads(workspace_id,kind,title,order_id,created_by)
|
||
values(p_workspace,'order','Заказ № '||v_order,v_order,auth.uid())
|
||
on conflict (workspace_id,order_id) where kind='order' do update set updated_at=excluded.updated_at returning id into v_id;
|
||
end if;
|
||
insert into public.sun_chat_reads(thread_id,workspace_id,user_id,last_read_at,updated_at)
|
||
values(v_id,p_workspace,auth.uid(),'epoch'::timestamptz,now()) on conflict(thread_id,user_id) do nothing;
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_get_direct_thread_v29(p_workspace uuid,p_other_user uuid)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_me uuid:=auth.uid(); v_key text; v_id uuid;
|
||
begin
|
||
if v_me is null or not public.sun_chat_is_member_v29(p_workspace,v_me) then raise exception 'Нет доступа к чату компании'; end if;
|
||
if p_other_user is null or p_other_user=v_me then raise exception 'Выберите другого сотрудника'; end if;
|
||
if not public.sun_chat_is_member_v29(p_workspace,p_other_user) then raise exception 'Сотрудник больше не состоит в компании'; end if;
|
||
v_key:=least(v_me::text,p_other_user::text)||':'||greatest(v_me::text,p_other_user::text);
|
||
select id into v_id from public.sun_chat_threads where workspace_id=p_workspace and kind='direct' and direct_key=v_key limit 1;
|
||
if v_id is null then
|
||
insert into public.sun_chat_threads(workspace_id,kind,direct_key,created_by)
|
||
values(p_workspace,'direct',v_key,v_me)
|
||
on conflict (workspace_id,direct_key) where kind='direct' do update set updated_at=excluded.updated_at returning id into v_id;
|
||
end if;
|
||
insert into public.sun_chat_participants(thread_id,workspace_id,user_id) values(v_id,p_workspace,v_me) on conflict do nothing;
|
||
insert into public.sun_chat_participants(thread_id,workspace_id,user_id) values(v_id,p_workspace,p_other_user) on conflict do nothing;
|
||
insert into public.sun_chat_reads(thread_id,workspace_id,user_id,last_read_at,updated_at)
|
||
values(v_id,p_workspace,v_me,'epoch'::timestamptz,now()) on conflict(thread_id,user_id) do nothing;
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_list_members_v29(p_workspace uuid)
|
||
returns table(user_id uuid,display_name text,email text,role text)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.sun_chat_is_member_v29(p_workspace,auth.uid()) then raise exception 'Нет доступа к сотрудникам компании'; end if;
|
||
return query select m.user_id,coalesce(nullif(m.display_name,''),split_part(coalesce(u.email,''),'@',1)),u.email::text,m.role
|
||
from public.sun_workspace_members m join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=p_workspace and m.is_active=true
|
||
order by (m.user_id=auth.uid()) desc,coalesce(nullif(m.display_name,''),u.email::text);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_list_threads_v29(p_workspace uuid)
|
||
returns table(thread_id uuid,kind text,order_id text,title text,other_user_id uuid,last_message text,last_message_at timestamptz,unread_count bigint)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.sun_chat_is_member_v29(p_workspace,auth.uid()) then raise exception 'Нет доступа к чатам компании'; end if;
|
||
return query
|
||
select t.id,t.kind,t.order_id,
|
||
case when t.kind='direct' then coalesce(nullif(om.display_name,''),split_part(coalesce(ou.email,''),'@',1),'Сотрудник') else coalesce(t.title,case when t.kind='company' then 'Общий чат' else 'Обсуждение' end) end,
|
||
case when t.kind='direct' then op.user_id else null end,
|
||
case when lm.id is null then '' when nullif(trim(lm.body),'') is not null then left(lm.body,110) when jsonb_array_length(coalesce(lm.attachments,'[]'::jsonb))>0 then 'Вложение' else '' end,
|
||
lm.created_at,
|
||
(select count(*) from public.sun_chat_messages um where um.thread_id=t.id and um.deleted_at is null and um.sender_user_id<>auth.uid() and um.created_at>coalesce(r.last_read_at,'epoch'::timestamptz))
|
||
from public.sun_chat_threads t
|
||
left join public.sun_chat_reads r on r.thread_id=t.id and r.user_id=auth.uid()
|
||
left join lateral (select m.* from public.sun_chat_messages m where m.thread_id=t.id and m.deleted_at is null order by m.created_at desc limit 1) lm on true
|
||
left join lateral (select p.user_id from public.sun_chat_participants p where p.thread_id=t.id and p.user_id<>auth.uid() limit 1) op on t.kind='direct'
|
||
left join public.sun_workspace_members om on om.workspace_id=t.workspace_id and om.user_id=op.user_id
|
||
left join auth.users ou on ou.id=op.user_id
|
||
where t.workspace_id=p_workspace and public.sun_chat_can_access_thread_as_v29(t.id,auth.uid())
|
||
order by case t.kind when 'company' then 0 when 'direct' then 1 else 2 end,coalesce(t.last_message_at,t.created_at) desc;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_list_messages_v29(p_thread uuid,p_limit integer default 100,p_before timestamptz default null)
|
||
returns table(message_id uuid,sender_user_id uuid,sender_name text,sender_email text,body text,attachments jsonb,created_at timestamptz,read_by_count bigint)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.sun_chat_can_access_thread_as_v29(p_thread,auth.uid()) then raise exception 'Нет доступа к переписке'; end if;
|
||
return query
|
||
with recent as (
|
||
select m.* from public.sun_chat_messages m
|
||
where m.thread_id=p_thread and m.deleted_at is null and (p_before is null or m.created_at<p_before)
|
||
order by m.created_at desc limit greatest(1,least(coalesce(p_limit,100),200))
|
||
)
|
||
select m.id,m.sender_user_id,coalesce(nullif(sm.display_name,''),split_part(coalesce(su.email,''),'@',1),'Сотрудник'),su.email::text,m.body,m.attachments,m.created_at,
|
||
(select count(*) from public.sun_chat_reads rr where rr.thread_id=m.thread_id and rr.user_id<>m.sender_user_id and rr.last_read_at>=m.created_at)
|
||
from recent m
|
||
left join public.sun_workspace_members sm on sm.workspace_id=m.workspace_id and sm.user_id=m.sender_user_id
|
||
left join auth.users su on su.id=m.sender_user_id
|
||
order by m.created_at asc;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_send_message_v29(p_thread uuid,p_body text default '',p_attachments jsonb default '[]'::jsonb)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth','realtime'
|
||
as $$
|
||
declare v public.sun_chat_threads%rowtype; v_id uuid; v_body text:=trim(coalesce(p_body,'')); v_att jsonb:=coalesce(p_attachments,'[]'::jsonb);
|
||
begin
|
||
if not public.sun_chat_can_access_thread_as_v29(p_thread,auth.uid()) then raise exception 'Нет доступа к переписке'; end if;
|
||
select * into v from public.sun_chat_threads where id=p_thread;
|
||
if length(v_body)>4000 then raise exception 'Сообщение слишком длинное'; end if;
|
||
if jsonb_typeof(v_att)<>'array' then raise exception 'Некорректные вложения'; end if;
|
||
if jsonb_array_length(v_att)>5 then raise exception 'Можно отправить не более 5 файлов за раз'; end if;
|
||
if v_body='' and jsonb_array_length(v_att)=0 then raise exception 'Введите сообщение или добавьте файл'; end if;
|
||
insert into public.sun_chat_messages(workspace_id,thread_id,sender_user_id,body,attachments)
|
||
values(v.workspace_id,v.id,auth.uid(),v_body,v_att) returning id into v_id;
|
||
update public.sun_chat_threads set last_message_at=now(),updated_at=now() where id=v.id;
|
||
insert into public.sun_chat_reads(thread_id,workspace_id,user_id,last_read_at,updated_at)
|
||
values(v.id,v.workspace_id,auth.uid(),now(),now())
|
||
on conflict(thread_id,user_id) do update set last_read_at=excluded.last_read_at,updated_at=now();
|
||
perform realtime.send('{}'::jsonb,'chat_changed','sun-chat-workspace:'||v.workspace_id::text,true);
|
||
perform realtime.send(jsonb_build_object('message_id',v_id),'message','sun-chat-thread:'||v.id::text,true);
|
||
return v_id;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_mark_read_v29(p_thread uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth','realtime'
|
||
as $$
|
||
declare v_ws uuid; v_old timestamptz; v_changed boolean:=false;
|
||
begin
|
||
if not public.sun_chat_can_access_thread_as_v29(p_thread,auth.uid()) then raise exception 'Нет доступа к переписке'; end if;
|
||
select workspace_id into v_ws from public.sun_chat_threads where id=p_thread;
|
||
select last_read_at into v_old from public.sun_chat_reads where thread_id=p_thread and user_id=auth.uid();
|
||
select exists(
|
||
select 1 from public.sun_chat_messages m
|
||
where m.thread_id=p_thread and m.deleted_at is null and m.sender_user_id<>auth.uid()
|
||
and m.created_at>coalesce(v_old,'epoch'::timestamptz)
|
||
) into v_changed;
|
||
insert into public.sun_chat_reads(thread_id,workspace_id,user_id,last_read_at,updated_at)
|
||
values(p_thread,v_ws,auth.uid(),now(),now())
|
||
on conflict(thread_id,user_id) do update set last_read_at=excluded.last_read_at,updated_at=now();
|
||
if v_changed then
|
||
perform realtime.send(jsonb_build_object('thread_id',p_thread),'read','sun-chat-thread:'||p_thread::text,true);
|
||
perform realtime.send('{}'::jsonb,'chat_changed','sun-chat-workspace:'||v_ws::text,true);
|
||
end if;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_chat_unread_total_v29(p_workspace uuid)
|
||
returns bigint
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare v_total bigint;
|
||
begin
|
||
if not public.sun_chat_is_member_v29(p_workspace,auth.uid()) then return 0; end if;
|
||
select count(*) into v_total
|
||
from public.sun_chat_messages m
|
||
join public.sun_chat_threads t on t.id=m.thread_id
|
||
left join public.sun_chat_reads r on r.thread_id=t.id and r.user_id=auth.uid()
|
||
where t.workspace_id=p_workspace and m.deleted_at is null and m.sender_user_id<>auth.uid()
|
||
and public.sun_chat_can_access_thread_as_v29(t.id,auth.uid())
|
||
and m.created_at>coalesce(r.last_read_at,'epoch'::timestamptz);
|
||
return coalesce(v_total,0);
|
||
end;
|
||
$$;
|
||
|
||
-- Private Storage bucket for chat files.
|
||
insert into storage.buckets(id,name,public,file_size_limit)
|
||
values('sun-chat','sun-chat',false,15728640)
|
||
on conflict(id) do update set public=false,file_size_limit=15728640;
|
||
|
||
drop policy if exists sun_chat_storage_read_v29 on storage.objects;
|
||
create policy sun_chat_storage_read_v29 on storage.objects for select to authenticated
|
||
using(bucket_id='sun-chat' and public.sun_chat_storage_access_v29(name));
|
||
drop policy if exists sun_chat_storage_insert_v29 on storage.objects;
|
||
create policy sun_chat_storage_insert_v29 on storage.objects for insert to authenticated
|
||
with check(bucket_id='sun-chat' and public.sun_chat_storage_access_v29(name));
|
||
|
||
-- Realtime private-channel authorization for chat workspace/thread topics.
|
||
drop policy if exists sun_chat_realtime_read_v29 on realtime.messages;
|
||
create policy sun_chat_realtime_read_v29 on realtime.messages for select to authenticated
|
||
using(realtime.messages.extension in ('broadcast','presence') and public.sun_chat_realtime_topic_access_v29((select realtime.topic())));
|
||
drop policy if exists sun_chat_realtime_write_v29 on realtime.messages;
|
||
create policy sun_chat_realtime_write_v29 on realtime.messages for insert to authenticated
|
||
with check(realtime.messages.extension in ('broadcast','presence') and public.sun_chat_realtime_topic_access_v29((select realtime.topic())));
|
||
|
||
revoke all on function public.sun_chat_is_member_v29(uuid,uuid) from public,anon;
|
||
revoke all on function public.sun_chat_can_access_thread_as_v29(uuid,uuid) from public,anon;
|
||
revoke all on function public.sun_chat_can_access_thread_v29(uuid) from public,anon;
|
||
revoke all on function public.sun_chat_realtime_topic_access_v29(text) from public,anon;
|
||
revoke all on function public.sun_chat_storage_access_v29(text) from public,anon;
|
||
revoke all on function public.sun_chat_get_company_thread_v29(uuid) from public,anon;
|
||
revoke all on function public.sun_chat_get_order_thread_v29(uuid,text) from public,anon;
|
||
revoke all on function public.sun_chat_get_direct_thread_v29(uuid,uuid) from public,anon;
|
||
revoke all on function public.sun_chat_list_members_v29(uuid) from public,anon;
|
||
revoke all on function public.sun_chat_list_threads_v29(uuid) from public,anon;
|
||
revoke all on function public.sun_chat_list_messages_v29(uuid,integer,timestamptz) from public,anon;
|
||
revoke all on function public.sun_chat_send_message_v29(uuid,text,jsonb) from public,anon;
|
||
revoke all on function public.sun_chat_mark_read_v29(uuid) from public,anon;
|
||
revoke all on function public.sun_chat_unread_total_v29(uuid) from public,anon;
|
||
|
||
grant execute on function public.sun_chat_realtime_topic_access_v29(text) to authenticated;
|
||
grant execute on function public.sun_chat_storage_access_v29(text) to authenticated;
|
||
grant execute on function public.sun_chat_get_company_thread_v29(uuid) to authenticated;
|
||
grant execute on function public.sun_chat_get_order_thread_v29(uuid,text) to authenticated;
|
||
grant execute on function public.sun_chat_get_direct_thread_v29(uuid,uuid) to authenticated;
|
||
grant execute on function public.sun_chat_list_members_v29(uuid) to authenticated;
|
||
grant execute on function public.sun_chat_list_threads_v29(uuid) to authenticated;
|
||
grant execute on function public.sun_chat_list_messages_v29(uuid,integer,timestamptz) to authenticated;
|
||
grant execute on function public.sun_chat_send_message_v29(uuid,text,jsonb) to authenticated;
|
||
grant execute on function public.sun_chat_mark_read_v29(uuid) to authenticated;
|
||
grant execute on function public.sun_chat_unread_total_v29(uuid) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-ADMIN-RIGHTS-V30.sql
|
||
-- Caterium v17.5.30 - granular administrator permissions and correct last-admin checks.
|
||
|
||
create or replace function public.sun_has_permission(p_workspace uuid, p_permission text)
|
||
returns boolean
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_role text;
|
||
v_permissions jsonb;
|
||
v_active boolean;
|
||
begin
|
||
select role,permissions,is_active
|
||
into v_role,v_permissions,v_active
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=auth.uid()
|
||
limit 1;
|
||
|
||
if not coalesce(v_active,false) then return false; end if;
|
||
|
||
-- Explicit member permissions override the role template for every role,
|
||
-- including administrators. Missing keys fall back to the role defaults.
|
||
if coalesce(v_permissions,'{}'::jsonb) ? p_permission then
|
||
return coalesce((v_permissions->>p_permission)::boolean,false);
|
||
end if;
|
||
return coalesce((public.sun_role_default_permissions(v_role)->>p_permission)::boolean,false);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_update_member(
|
||
p_workspace uuid,
|
||
p_user uuid,
|
||
p_display_name text,
|
||
p_role text,
|
||
p_is_active boolean,
|
||
p_permissions jsonb
|
||
)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_old_role text;
|
||
v_old_active boolean;
|
||
v_role text:=lower(coalesce(p_role,''));
|
||
v_other_admins integer:=0;
|
||
v_other_managing_admins integer:=0;
|
||
v_target_will_manage boolean:=false;
|
||
v_max integer;
|
||
v_active_count integer;
|
||
begin
|
||
-- Serialize membership administration inside one workspace so two admins
|
||
-- cannot simultaneously remove/demote the last administrators.
|
||
perform pg_advisory_xact_lock(hashtext(p_workspace::text));
|
||
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Недостаточно прав для управления сотрудниками'; end if;
|
||
if v_role not in ('admin','manager','kitchen','courier','viewer') then raise exception 'Некорректная роль'; end if;
|
||
if p_permissions is null or jsonb_typeof(p_permissions)<>'object' then raise exception 'Некорректные права пользователя'; end if;
|
||
|
||
select role,is_active into v_old_role,v_old_active
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=p_user
|
||
for update;
|
||
if not found then raise exception 'Пользователь не найден'; end if;
|
||
|
||
select count(*)::int into v_other_admins
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace
|
||
and user_id<>p_user
|
||
and role='admin'
|
||
and is_active=true;
|
||
|
||
if v_old_role='admin' and coalesce(v_old_active,false)
|
||
and (v_role<>'admin' or not coalesce(p_is_active,false))
|
||
and v_other_admins=0 then
|
||
raise exception 'Нельзя отключить или понизить последнего активного администратора';
|
||
end if;
|
||
|
||
select count(*)::int into v_other_managing_admins
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace
|
||
and user_id<>p_user
|
||
and role='admin'
|
||
and is_active=true
|
||
and coalesce(
|
||
case when coalesce(permissions,'{}'::jsonb) ? 'users.manage'
|
||
then (permissions->>'users.manage')::boolean
|
||
else null end,
|
||
true
|
||
)=true;
|
||
|
||
v_target_will_manage := v_role='admin'
|
||
and coalesce(p_is_active,false)
|
||
and coalesce(
|
||
case when p_permissions ? 'users.manage'
|
||
then (p_permissions->>'users.manage')::boolean
|
||
else null end,
|
||
true
|
||
);
|
||
|
||
if not v_target_will_manage and v_other_managing_admins=0 then
|
||
raise exception 'У хотя бы одного активного администратора должно оставаться право «Пользователи и права»';
|
||
end if;
|
||
|
||
if coalesce(p_is_active,false) and not coalesce(v_old_active,false) then
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s
|
||
join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_active_count
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and is_active=true;
|
||
if v_max is not null and v_active_count>=v_max then
|
||
raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max;
|
||
end if;
|
||
end if;
|
||
|
||
update public.sun_workspace_members
|
||
set display_name=nullif(trim(coalesce(p_display_name,'')),''),
|
||
role=v_role,
|
||
is_active=coalesce(p_is_active,false),
|
||
permissions=p_permissions,
|
||
updated_at=now()
|
||
where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_remove_member(p_workspace uuid,p_user uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_role text;
|
||
v_active boolean;
|
||
v_other_admins integer:=0;
|
||
v_other_managing_admins integer:=0;
|
||
begin
|
||
perform pg_advisory_xact_lock(hashtext(p_workspace::text));
|
||
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
if not public.sun_has_permission(p_workspace,'users.manage') then raise exception 'Недостаточно прав для управления сотрудниками'; end if;
|
||
|
||
select role,is_active into v_role,v_active
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=p_user
|
||
for update;
|
||
if not found then return; end if;
|
||
|
||
if v_role='admin' and coalesce(v_active,false) then
|
||
select count(*)::int into v_other_admins
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id<>p_user and role='admin' and is_active=true;
|
||
if v_other_admins=0 then raise exception 'Нельзя удалить последнего активного администратора'; end if;
|
||
|
||
select count(*)::int into v_other_managing_admins
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace
|
||
and user_id<>p_user
|
||
and role='admin'
|
||
and is_active=true
|
||
and coalesce(
|
||
case when coalesce(permissions,'{}'::jsonb) ? 'users.manage'
|
||
then (permissions->>'users.manage')::boolean
|
||
else null end,
|
||
true
|
||
)=true;
|
||
if v_other_managing_admins=0 then
|
||
raise exception 'Нельзя удалить администратора: после удаления никто не сможет управлять пользователями';
|
||
end if;
|
||
end if;
|
||
|
||
delete from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_has_permission(uuid,text) from public,anon;
|
||
revoke all on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) from public,anon;
|
||
revoke all on function public.sun_admin_remove_member(uuid,uuid) from public,anon;
|
||
grant execute on function public.sun_has_permission(uuid,text) to authenticated;
|
||
grant execute on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) to authenticated;
|
||
grant execute on function public.sun_admin_remove_member(uuid,uuid) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.6.1-CHAT-STORAGE-GUARD.sql
|
||
-- Caterium v17.6.1: server memory counters for Developer Console
|
||
create or replace function public.sun_platform_dashboard()
|
||
returns jsonb
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_companies bigint; v_accounts bigint; v_admins bigint; v_active bigint; v_trial bigint;
|
||
v_locked bigint; v_errors bigint; v_backups bigint; v_members bigint;
|
||
v_last_state timestamptz; v_last_backup timestamptz;
|
||
v_database_bytes bigint := 0; v_storage_bytes bigint := 0; v_storage_objects bigint := 0;
|
||
v_storage_buckets jsonb := '{}'::jsonb;
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
select count(*) into v_companies from public.sun_workspaces;
|
||
select count(*) into v_accounts from auth.users;
|
||
select count(*) into v_admins from public.sun_platform_admins;
|
||
select count(*) into v_members from public.sun_workspace_members where is_active=true;
|
||
select count(*) into v_active from public.sun_workspace_subscriptions where status='active' and coalesce(current_period_end,'infinity'::timestamptz)>now();
|
||
select count(*) into v_trial from public.sun_workspace_subscriptions where status='trialing' and coalesce(trial_ends_at,'infinity'::timestamptz)>now();
|
||
select count(*) into v_locked from public.sun_workspaces w where public.sun_subscription_access_mode(w.id) in ('read_only','blocked');
|
||
select count(*) into v_errors from public.sun_v17_error_events where created_at>now()-interval '24 hours';
|
||
select count(*) into v_backups from public.sun_v17_backups where created_at>now()-interval '24 hours';
|
||
select max(updated_at) into v_last_state from public.sun_app_state;
|
||
select max(created_at) into v_last_backup from public.sun_v17_backups;
|
||
v_database_bytes := pg_database_size(current_database());
|
||
select count(*), coalesce(sum(case when coalesce(metadata->>'size','') ~ '^[0-9]+$' then (metadata->>'size')::bigint else 0 end),0)
|
||
into v_storage_objects, v_storage_bytes from storage.objects;
|
||
select coalesce(jsonb_object_agg(bucket_id,jsonb_build_object('objects',object_count,'bytes',bucket_bytes,'size',pg_size_pretty(bucket_bytes))),'{}'::jsonb)
|
||
into v_storage_buckets
|
||
from (select bucket_id,count(*)::bigint as object_count,coalesce(sum(case when coalesce(metadata->>'size','') ~ '^[0-9]+$' then (metadata->>'size')::bigint else 0 end),0)::bigint as bucket_bytes from storage.objects group by bucket_id) s;
|
||
return jsonb_build_object(
|
||
'companies',v_companies,'accounts',v_accounts,'platform_admins',v_admins,'memberships',v_members,
|
||
'active_subscriptions',v_active,'trials',v_trial,'restricted_companies',v_locked,
|
||
'errors_24h',v_errors,'backups_24h',v_backups,'last_state_at',v_last_state,'last_backup_at',v_last_backup,
|
||
'database_bytes',v_database_bytes,'database_size',pg_size_pretty(v_database_bytes),
|
||
'storage_bytes',v_storage_bytes,'storage_size',pg_size_pretty(v_storage_bytes),'storage_objects',v_storage_objects,'storage_buckets',v_storage_buckets,
|
||
'server_bytes',v_database_bytes+v_storage_bytes,'server_size',pg_size_pretty(v_database_bytes+v_storage_bytes),
|
||
'postgres_version',current_setting('server_version'));
|
||
end;
|
||
$$;
|
||
revoke all on function public.sun_platform_dashboard() from public, anon;
|
||
grant execute on function public.sun_platform_dashboard() to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.6.8-DEVELOPER-CONSOLE-UX.sql
|
||
-- Caterium v17.6.8 Developer Console UX
|
||
-- Persistent company numbers, safe company deletion and grouped production error journal.
|
||
|
||
create sequence if not exists public.sun_workspace_company_number_seq;
|
||
|
||
alter table public.sun_workspaces
|
||
add column if not exists company_number bigint;
|
||
|
||
with numbered as (
|
||
select id,row_number() over(order by created_at,id)::bigint as n
|
||
from public.sun_workspaces
|
||
where company_number is null
|
||
), existing as (
|
||
select coalesce(max(company_number),0)::bigint as max_n from public.sun_workspaces
|
||
)
|
||
update public.sun_workspaces w
|
||
set company_number = numbered.n + existing.max_n
|
||
from numbered,existing
|
||
where w.id=numbered.id and w.company_number is null;
|
||
|
||
select setval(
|
||
'public.sun_workspace_company_number_seq',
|
||
greatest(1,coalesce((select max(company_number) from public.sun_workspaces),0)),
|
||
coalesce((select max(company_number) from public.sun_workspaces),0)>0
|
||
);
|
||
|
||
alter table public.sun_workspaces
|
||
alter column company_number set default nextval('public.sun_workspace_company_number_seq');
|
||
|
||
update public.sun_workspaces
|
||
set company_number=nextval('public.sun_workspace_company_number_seq')
|
||
where company_number is null;
|
||
|
||
alter table public.sun_workspaces
|
||
alter column company_number set not null;
|
||
|
||
create unique index if not exists sun_workspaces_company_number_uq
|
||
on public.sun_workspaces(company_number);
|
||
|
||
create or replace function public.sun_dev_list_companies_v1768()
|
||
returns table(
|
||
workspace_id uuid,
|
||
workspace_name text,
|
||
company_number bigint,
|
||
created_at timestamptz,
|
||
plan_id text,
|
||
plan_name text,
|
||
status text,
|
||
access_mode text,
|
||
trial_ends_at timestamptz,
|
||
current_period_end timestamptz,
|
||
grace_until timestamptz,
|
||
member_count bigint,
|
||
max_members integer,
|
||
owner_email text,
|
||
pending_owner_email text
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query
|
||
select
|
||
w.id,
|
||
w.name,
|
||
w.company_number,
|
||
w.created_at,
|
||
s.plan_id,
|
||
p.name,
|
||
s.status,
|
||
public.sun_subscription_access_mode(w.id),
|
||
s.trial_ends_at,
|
||
s.current_period_end,
|
||
s.grace_until,
|
||
(select count(*) from public.sun_workspace_members m where m.workspace_id=w.id and m.is_active),
|
||
p.max_members,
|
||
(select u.email::text
|
||
from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=w.id and m.role='admin' and m.is_active
|
||
order by m.created_at asc
|
||
limit 1),
|
||
(select i.email
|
||
from public.caterium_company_owner_invites i
|
||
where i.workspace_id=w.id and i.used_at is null and i.expires_at>now()
|
||
order by i.created_at desc
|
||
limit 1)
|
||
from public.sun_workspaces w
|
||
left join public.sun_workspace_subscriptions s on s.workspace_id=w.id
|
||
left join public.sun_plans p on p.id=s.plan_id
|
||
order by w.company_number;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_dev_delete_company_v1768(
|
||
p_workspace uuid,
|
||
p_confirm_name text
|
||
) returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_name text;
|
||
v_number bigint;
|
||
v_members bigint;
|
||
v_orders bigint;
|
||
v_clients bigint;
|
||
v_catalog bigint;
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
|
||
select w.name,w.company_number
|
||
into v_name,v_number
|
||
from public.sun_workspaces w
|
||
where w.id=p_workspace
|
||
for update;
|
||
|
||
if v_name is null then raise exception 'Workspace not found'; end if;
|
||
if trim(coalesce(p_confirm_name,'')) <> v_name then raise exception 'Company name confirmation mismatch'; end if;
|
||
|
||
select count(*) into v_members from public.sun_workspace_members where workspace_id=p_workspace;
|
||
select count(*) into v_orders from public.sun_v17_orders where workspace_id=p_workspace;
|
||
select count(*) into v_clients from public.sun_v17_clients where workspace_id=p_workspace;
|
||
select count(*) into v_catalog from public.sun_v17_catalog_items where workspace_id=p_workspace;
|
||
|
||
perform public.sun_platform_log_event(
|
||
'company.delete',
|
||
p_workspace,
|
||
null,
|
||
jsonb_build_object(
|
||
'company',v_name,
|
||
'company_number',v_number,
|
||
'members',v_members,
|
||
'orders',v_orders,
|
||
'clients',v_clients,
|
||
'catalog_items',v_catalog,
|
||
'auth_accounts_preserved',true
|
||
)
|
||
);
|
||
|
||
delete from public.sun_workspaces where id=p_workspace;
|
||
|
||
return jsonb_build_object(
|
||
'deleted',true,
|
||
'workspace_id',p_workspace,
|
||
'workspace_name',v_name,
|
||
'company_number',v_number,
|
||
'memberships_removed',v_members,
|
||
'auth_accounts_preserved',true
|
||
);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_dev_error_groups_v1768(
|
||
p_hours integer default 168,
|
||
p_limit integer default 100
|
||
) returns table(
|
||
workspace_name text,
|
||
app_version text,
|
||
level text,
|
||
message text,
|
||
occurrences bigint,
|
||
first_seen timestamptz,
|
||
last_seen timestamptz,
|
||
sample_stack text,
|
||
source_url text
|
||
)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_hours integer:=greatest(1,least(coalesce(p_hours,168),720));
|
||
v_limit integer:=greatest(1,least(coalesce(p_limit,100),200));
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query
|
||
with scoped as (
|
||
select
|
||
e.*,
|
||
w.name as company_name,
|
||
nullif(e.context->>'url','') as event_url
|
||
from public.sun_v17_error_events e
|
||
left join public.sun_workspaces w on w.id=e.workspace_id
|
||
where e.created_at >= now()-make_interval(hours=>v_hours)
|
||
and coalesce(e.context->>'url','') !~* '^file:'
|
||
), grouped as (
|
||
select
|
||
company_name,
|
||
coalesce(app_version,'') as app_version,
|
||
coalesce(level,'error') as level,
|
||
coalesce(message,'Unknown error') as message,
|
||
count(*)::bigint as occurrences,
|
||
min(created_at) as first_seen,
|
||
max(created_at) as last_seen
|
||
from scoped
|
||
group by company_name,coalesce(app_version,''),coalesce(level,'error'),coalesce(message,'Unknown error')
|
||
)
|
||
select
|
||
g.company_name,
|
||
nullif(g.app_version,''),
|
||
g.level,
|
||
g.message,
|
||
g.occurrences,
|
||
g.first_seen,
|
||
g.last_seen,
|
||
s.stack,
|
||
s.event_url
|
||
from grouped g
|
||
left join lateral (
|
||
select x.stack,x.event_url
|
||
from scoped x
|
||
where x.company_name is not distinct from g.company_name
|
||
and coalesce(x.app_version,'')=g.app_version
|
||
and coalesce(x.level,'error')=g.level
|
||
and coalesce(x.message,'Unknown error')=g.message
|
||
order by x.created_at desc
|
||
limit 1
|
||
) s on true
|
||
order by g.last_seen desc
|
||
limit v_limit;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_dev_list_companies_v1768() from public,anon;
|
||
revoke all on function public.sun_dev_delete_company_v1768(uuid,text) from public,anon;
|
||
revoke all on function public.sun_dev_error_groups_v1768(integer,integer) from public,anon;
|
||
|
||
grant execute on function public.sun_dev_list_companies_v1768() to authenticated;
|
||
grant execute on function public.sun_dev_delete_company_v1768(uuid,text) to authenticated;
|
||
grant execute on function public.sun_dev_error_groups_v1768(integer,integer) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/CATERIUM-OWNER-ACCOUNTS-V31.sql
|
||
-- Caterium v31: one company owner account, owner-only employee administration,
|
||
-- and a self-service profile for every authenticated user.
|
||
|
||
create or replace function public.caterium_is_workspace_owner(p_workspace uuid)
|
||
returns boolean
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
select auth.uid() is not null
|
||
and exists(
|
||
select 1
|
||
from public.sun_workspaces w
|
||
join public.sun_workspace_members m
|
||
on m.workspace_id=w.id
|
||
and m.user_id=auth.uid()
|
||
and m.is_active=true
|
||
where w.id=p_workspace
|
||
and w.created_by=auth.uid()
|
||
);
|
||
$$;
|
||
|
||
revoke all on function public.caterium_is_workspace_owner(uuid) from public,anon;
|
||
grant execute on function public.caterium_is_workspace_owner(uuid) to authenticated;
|
||
|
||
create or replace function public.caterium_account_snapshot(p_workspace uuid)
|
||
returns jsonb
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_user uuid:=auth.uid();
|
||
v_row record;
|
||
begin
|
||
if v_user is null then raise exception 'Сначала войдите в Caterium'; end if;
|
||
|
||
select w.id as workspace_id,
|
||
w.name as company_name,
|
||
w.company_number,
|
||
w.created_by,
|
||
m.display_name,
|
||
m.role,
|
||
m.is_active,
|
||
coalesce(m.permissions,'{}'::jsonb) as permissions,
|
||
u.email::text as email
|
||
into v_row
|
||
from public.sun_workspaces w
|
||
join public.sun_workspace_members m
|
||
on m.workspace_id=w.id
|
||
and m.user_id=v_user
|
||
join auth.users u on u.id=v_user
|
||
where w.id=p_workspace
|
||
and m.is_active=true
|
||
limit 1;
|
||
|
||
if not found then raise exception 'Нет доступа к компании'; end if;
|
||
|
||
return jsonb_build_object(
|
||
'user_id',v_user,
|
||
'email',v_row.email,
|
||
'display_name',coalesce(nullif(v_row.display_name,''),split_part(coalesce(v_row.email,''),'@',1)),
|
||
'role',v_row.role,
|
||
'is_owner',(v_row.created_by=v_user),
|
||
'workspace_id',v_row.workspace_id,
|
||
'company_name',v_row.company_name,
|
||
'company_number',v_row.company_number,
|
||
'permissions',v_row.permissions
|
||
);
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.caterium_account_snapshot(uuid) from public,anon;
|
||
grant execute on function public.caterium_account_snapshot(uuid) to authenticated;
|
||
|
||
create or replace function public.caterium_update_my_name(p_display_name text)
|
||
returns text
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_user uuid:=auth.uid();
|
||
v_name text:=trim(coalesce(p_display_name,''));
|
||
begin
|
||
if v_user is null then raise exception 'Сначала войдите в Caterium'; end if;
|
||
if char_length(v_name)<1 then raise exception 'Введите имя'; end if;
|
||
if char_length(v_name)>120 then raise exception 'Имя слишком длинное'; end if;
|
||
|
||
update public.sun_workspace_members
|
||
set display_name=v_name,
|
||
updated_at=now()
|
||
where user_id=v_user;
|
||
|
||
update auth.users
|
||
set raw_user_meta_data=jsonb_set(coalesce(raw_user_meta_data,'{}'::jsonb),'{name}',to_jsonb(v_name),true)
|
||
where id=v_user;
|
||
|
||
return v_name;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.caterium_update_my_name(text) from public,anon;
|
||
grant execute on function public.caterium_update_my_name(text) to authenticated;
|
||
|
||
-- Existing company creators are the only company administrators.
|
||
update public.sun_workspace_members m
|
||
set role='admin',
|
||
permissions=jsonb_set(coalesce(m.permissions,public.sun_role_default_permissions('admin')),'{users.manage}','true'::jsonb,true),
|
||
updated_at=now()
|
||
from public.sun_workspaces w
|
||
where w.id=m.workspace_id
|
||
and m.user_id=w.created_by;
|
||
|
||
-- Any non-owner account is an employee account and can never manage users.
|
||
update public.sun_workspace_members m
|
||
set role=case when m.role='admin' then 'manager' else m.role end,
|
||
permissions=jsonb_set(coalesce(m.permissions,public.sun_role_default_permissions(case when m.role='admin' then 'manager' else m.role end)),'{users.manage}','false'::jsonb,true),
|
||
updated_at=now()
|
||
from public.sun_workspaces w
|
||
where w.id=m.workspace_id
|
||
and m.user_id<>w.created_by;
|
||
|
||
-- No pending invitation may create a second company administrator.
|
||
delete from public.sun_workspace_invites where used_at is null and role='admin';
|
||
|
||
create or replace function public.sun_list_workspace_members(p_workspace uuid)
|
||
returns table(user_id uuid,email text,display_name text,role text,is_active boolean,permissions jsonb,created_at timestamptz,updated_at timestamptz)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
begin
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then
|
||
raise exception 'Только владелец компании может управлять пользователями';
|
||
end if;
|
||
return query
|
||
select m.user_id,u.email::text,m.display_name,m.role,m.is_active,
|
||
coalesce(m.permissions,public.sun_role_default_permissions(m.role)),m.created_at,m.updated_at
|
||
from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
join public.sun_workspaces w on w.id=m.workspace_id
|
||
where m.workspace_id=p_workspace
|
||
order by (m.user_id=w.created_by) desc,coalesce(m.display_name,u.email::text);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_update_member(
|
||
p_workspace uuid,
|
||
p_user uuid,
|
||
p_display_name text,
|
||
p_role text,
|
||
p_is_active boolean,
|
||
p_permissions jsonb
|
||
)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_role text:=lower(coalesce(p_role,''));
|
||
v_owner uuid;
|
||
v_max integer;
|
||
v_active_count integer;
|
||
v_permissions jsonb;
|
||
begin
|
||
perform pg_advisory_xact_lock(hashtext(p_workspace::text));
|
||
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then
|
||
raise exception 'Только владелец компании может управлять сотрудниками';
|
||
end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Сотруднику нельзя назначить роль владельца'; end if;
|
||
if p_permissions is null or jsonb_typeof(p_permissions)<>'object' then raise exception 'Некорректные права пользователя'; end if;
|
||
|
||
select created_by into v_owner from public.sun_workspaces where id=p_workspace;
|
||
if v_owner is null then raise exception 'Компания не найдена'; end if;
|
||
if p_user=v_owner then raise exception 'Главный аккаунт компании нельзя изменить через управление сотрудниками'; end if;
|
||
if not exists(select 1 from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user) then raise exception 'Пользователь не найден'; end if;
|
||
|
||
if coalesce(p_is_active,false)
|
||
and not coalesce((select is_active from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user),false) then
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s
|
||
join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_active_count
|
||
from public.sun_workspace_members
|
||
where workspace_id=p_workspace and is_active=true;
|
||
if v_max is not null and v_active_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; end if;
|
||
end if;
|
||
|
||
v_permissions:=coalesce(p_permissions,'{}'::jsonb)||jsonb_build_object('users.manage',false);
|
||
|
||
update public.sun_workspace_members
|
||
set display_name=nullif(trim(coalesce(p_display_name,'')),''),
|
||
role=v_role,
|
||
is_active=coalesce(p_is_active,false),
|
||
permissions=v_permissions,
|
||
updated_at=now()
|
||
where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_admin_remove_member(p_workspace uuid,p_user uuid)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_owner uuid;
|
||
begin
|
||
perform pg_advisory_xact_lock(hashtext(p_workspace::text));
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then
|
||
raise exception 'Только владелец компании может удалять сотрудников';
|
||
end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Управление сотрудниками недоступно на текущем тарифе'; end if;
|
||
select created_by into v_owner from public.sun_workspaces where id=p_workspace;
|
||
if p_user=v_owner then raise exception 'Главный аккаунт компании удалить нельзя'; end if;
|
||
delete from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_employee_prepare_v28(
|
||
p_workspace uuid,
|
||
p_email text,
|
||
p_display_name text default '',
|
||
p_role text default 'manager'
|
||
)
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_email text:=lower(trim(coalesce(p_email,'')));
|
||
v_name text:=trim(coalesce(p_display_name,''));
|
||
v_role text:=lower(coalesce(p_role,'manager'));
|
||
v_user uuid;
|
||
v_member public.sun_workspace_members%rowtype;
|
||
v_max integer;
|
||
v_count integer;
|
||
v_mode text;
|
||
begin
|
||
if auth.uid() is null then raise exception 'Сначала войдите в Caterium'; end if;
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if;
|
||
if v_email='' or v_email !~* '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$' then raise exception 'Введите корректный email сотрудника'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Сотруднику нельзя назначить роль владельца'; end if;
|
||
if v_name='' then v_name:=split_part(v_email,'@',1); end if;
|
||
|
||
v_mode:=public.sun_workspace_access_mode_internal_v28(p_workspace);
|
||
if v_mode<>'full' then raise exception 'Подписка компании не позволяет добавлять сотрудников'; end if;
|
||
if not public.sun_workspace_feature_internal_v28(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
|
||
select count(*)::int into v_count from public.sun_workspace_members
|
||
where workspace_id=p_workspace and is_active=true;
|
||
|
||
select u.id into v_user from auth.users u where lower(coalesce(u.email,''))=v_email order by u.created_at asc limit 1;
|
||
|
||
if v_user is not null then
|
||
select * into v_member from public.sun_workspace_members
|
||
where workspace_id=p_workspace and user_id=v_user limit 1;
|
||
if found and v_member.is_active then
|
||
return jsonb_build_object('status','already_member','user_id',v_user,'email',v_email,'display_name',coalesce(nullif(v_member.display_name,''),v_name),'role',v_member.role);
|
||
end if;
|
||
end if;
|
||
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if;
|
||
|
||
return jsonb_build_object(
|
||
'status',case when v_user is null then 'new' else 'existing' end,
|
||
'user_id',v_user,
|
||
'email',v_email,
|
||
'display_name',v_name,
|
||
'role',v_role,
|
||
'max_members',v_max,
|
||
'active_members',v_count
|
||
);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_employee_finalize_v28(
|
||
p_workspace uuid,
|
||
p_user_id uuid,
|
||
p_display_name text default '',
|
||
p_role text default 'manager'
|
||
)
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_name text:=trim(coalesce(p_display_name,''));
|
||
v_role text:=lower(coalesce(p_role,'manager'));
|
||
v_email text;
|
||
v_max integer;
|
||
v_count integer;
|
||
v_already boolean:=false;
|
||
begin
|
||
if auth.uid() is null then raise exception 'Сначала войдите в Caterium'; end if;
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if;
|
||
if public.sun_workspace_access_mode_internal_v28(p_workspace)<>'full' then raise exception 'Подписка компании не позволяет добавлять сотрудников'; end if;
|
||
if not public.sun_workspace_feature_internal_v28(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Сотруднику нельзя назначить роль владельца'; end if;
|
||
|
||
select lower(email) into v_email from auth.users where id=p_user_id;
|
||
if v_email is null then raise exception 'Аккаунт сотрудника не найден'; end if;
|
||
if v_name='' then v_name:=split_part(v_email,'@',1); end if;
|
||
|
||
select exists(select 1 from public.sun_workspace_members where workspace_id=p_workspace and user_id=p_user_id and is_active=true) into v_already;
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
if not v_already and v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if;
|
||
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions,updated_at)
|
||
values(p_workspace,p_user_id,v_role,left(v_name,120),true,public.sun_role_default_permissions(v_role)||jsonb_build_object('users.manage',false),now())
|
||
on conflict(workspace_id,user_id) do update
|
||
set role=excluded.role,display_name=excluded.display_name,is_active=true,permissions=excluded.permissions,updated_at=now();
|
||
|
||
delete from public.sun_workspace_invites where workspace_id=p_workspace and used_at is null and lower(coalesce(email,''))=v_email;
|
||
|
||
return jsonb_build_object('status',case when v_already then 'updated' else 'added' end,'user_id',p_user_id,'email',v_email,'display_name',v_name,'role',v_role);
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_create_invite(p_workspace uuid,p_role text default 'manager')
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text:=lower(coalesce(p_role,'manager'));
|
||
v_max integer;
|
||
v_count integer;
|
||
begin
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Некорректная роль сотрудника'; end if;
|
||
select p.max_members into v_max from public.sun_workspace_subscriptions s join public.sun_plans p on p.id=s.plan_id where s.workspace_id=p_workspace;
|
||
select count(*)::int into v_count from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
if v_max is not null and v_count>=v_max then raise exception 'Достигнут лимит сотрудников тарифа (%).',v_max; end if;
|
||
insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by)
|
||
values(p_workspace,v_role,public.sun_role_default_permissions(v_role)||jsonb_build_object('users.manage',false),auth.uid()) returning token into v_token;
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_create_invite_v27(
|
||
p_workspace uuid,
|
||
p_email text,
|
||
p_display_name text default '',
|
||
p_role text default 'manager'
|
||
)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public','auth'
|
||
as $$
|
||
declare
|
||
v_token uuid;
|
||
v_role text:=lower(coalesce(p_role,'manager'));
|
||
v_email text:=lower(trim(coalesce(p_email,'')));
|
||
v_name text:=trim(coalesce(p_display_name,''));
|
||
v_max integer;
|
||
v_active integer;
|
||
v_pending integer;
|
||
begin
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может добавлять сотрудников'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)<>'full' then raise exception 'Подписка не позволяет изменять пользователей'; end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'users_manage') then raise exception 'Добавление сотрудников недоступно на текущем тарифе'; end if;
|
||
if v_role not in ('manager','kitchen','courier','viewer') then raise exception 'Некорректная роль сотрудника'; end if;
|
||
if v_email='' or v_email !~* '^[^[:space:]@]+@[^[:space:]@]+\.[^[:space:]@]+$' then raise exception 'Введите корректный email сотрудника'; end if;
|
||
if v_name='' then v_name:=split_part(v_email,'@',1); end if;
|
||
|
||
if exists(
|
||
select 1 from public.sun_workspace_members m
|
||
join auth.users u on u.id=m.user_id
|
||
where m.workspace_id=p_workspace and m.is_active=true and lower(coalesce(u.email,''))=v_email
|
||
) then raise exception 'Пользователь с этим email уже добавлен в компанию'; end if;
|
||
|
||
delete from public.sun_workspace_invites
|
||
where workspace_id=p_workspace and used_at is null
|
||
and (expires_at<=now() or lower(coalesce(email,''))=v_email);
|
||
|
||
select p.max_members into v_max
|
||
from public.sun_workspace_subscriptions s
|
||
join public.sun_plans p on p.id=s.plan_id
|
||
where s.workspace_id=p_workspace;
|
||
|
||
if v_max is not null then
|
||
select count(*)::int into v_active from public.sun_workspace_members where workspace_id=p_workspace and is_active=true;
|
||
select count(*)::int into v_pending from public.sun_workspace_invites where workspace_id=p_workspace and used_at is null and expires_at>now();
|
||
if v_active+v_pending>=v_max then raise exception 'Достигнут лимит пользователей тарифа (%)',v_max; end if;
|
||
end if;
|
||
|
||
insert into public.sun_workspace_invites(workspace_id,role,permissions,created_by,email,display_name)
|
||
values(p_workspace,v_role,public.sun_role_default_permissions(v_role)||jsonb_build_object('users.manage',false),auth.uid(),v_email,left(v_name,120))
|
||
returning token into v_token;
|
||
return v_token;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_list_workspace_invites_v27(p_workspace uuid)
|
||
returns table(token uuid,email text,display_name text,role text,created_at timestamptz,expires_at timestamptz,status text)
|
||
language plpgsql
|
||
stable
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
begin
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может просматривать приглашения'; end if;
|
||
return query
|
||
select i.token,i.email,i.display_name,i.role,i.created_at,i.expires_at,
|
||
case when i.expires_at<=now() then 'expired' else 'pending' end::text
|
||
from public.sun_workspace_invites i
|
||
where i.workspace_id=p_workspace and i.used_at is null
|
||
order by i.created_at desc;
|
||
end;
|
||
$$;
|
||
|
||
create or replace function public.sun_cancel_invite_v27(p_workspace uuid,p_token uuid)
|
||
returns boolean
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public'
|
||
as $$
|
||
declare v_deleted integer;
|
||
begin
|
||
if not public.caterium_is_workspace_owner(p_workspace) and not public.sun_is_platform_admin() then raise exception 'Только владелец компании может отменять приглашения'; end if;
|
||
delete from public.sun_workspace_invites where workspace_id=p_workspace and token=p_token and used_at is null;
|
||
get diagnostics v_deleted=row_count;
|
||
return v_deleted>0;
|
||
end;
|
||
$$;
|
||
|
||
revoke all on function public.sun_list_workspace_members(uuid) from public,anon;
|
||
revoke all on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) from public,anon;
|
||
revoke all on function public.sun_admin_remove_member(uuid,uuid) from public,anon;
|
||
revoke all on function public.sun_employee_prepare_v28(uuid,text,text,text) from public,anon;
|
||
revoke all on function public.sun_employee_finalize_v28(uuid,uuid,text,text) from public,anon;
|
||
revoke all on function public.sun_create_invite(uuid,text) from public,anon;
|
||
revoke all on function public.sun_create_invite_v27(uuid,text,text,text) from public,anon;
|
||
revoke all on function public.sun_list_workspace_invites_v27(uuid) from public,anon;
|
||
revoke all on function public.sun_cancel_invite_v27(uuid,uuid) from public,anon;
|
||
|
||
grant execute on function public.sun_list_workspace_members(uuid) to authenticated;
|
||
grant execute on function public.sun_admin_update_member(uuid,uuid,text,text,boolean,jsonb) to authenticated;
|
||
grant execute on function public.sun_admin_remove_member(uuid,uuid) to authenticated;
|
||
grant execute on function public.sun_employee_prepare_v28(uuid,text,text,text) to authenticated;
|
||
grant execute on function public.sun_employee_finalize_v28(uuid,uuid,text,text) to authenticated;
|
||
grant execute on function public.sun_create_invite(uuid,text) to authenticated;
|
||
grant execute on function public.sun_create_invite_v27(uuid,text,text,text) to authenticated;
|
||
grant execute on function public.sun_list_workspace_invites_v27(uuid) to authenticated;
|
||
grant execute on function public.sun_cancel_invite_v27(uuid,uuid) to authenticated;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.0-SERVER-ORDER-AUTOMATION.sql
|
||
alter table public.sun_workspaces add column if not exists timezone text not null default 'Europe/Moscow';
|
||
|
||
create or replace function public.sun_order_due_at(p_data jsonb,p_timezone text)
|
||
returns timestamptz
|
||
language plpgsql
|
||
stable
|
||
set search_path='public' as $$
|
||
declare v_date date; v_time time; v_tz text;
|
||
begin
|
||
if coalesce(p_data->>'date','') !~ '^\d{4}-\d{2}-\d{2}$' or coalesce(p_data->>'time','') !~ '^\d{2}:\d{2}$' then return null; end if;
|
||
v_date := (p_data->>'date')::date;
|
||
v_time := (p_data->>'time')::time;
|
||
v_tz := coalesce(nullif(trim(p_timezone),''),'Europe/Moscow');
|
||
return ((v_date::timestamp + v_time) at time zone v_tz) + interval '1 minute';
|
||
exception when others then return null;
|
||
end;$$;
|
||
|
||
create or replace function public.sun_process_due_orders_internal(p_workspace uuid,p_now timestamptz default now())
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public' as $$
|
||
declare v_tz text; v_changed integer:=0; v_ids text[]:=array[]::text[]; r record; v_total numeric; v_iso text; v_orders jsonb;
|
||
begin
|
||
select timezone into v_tz from public.sun_workspaces where id=p_workspace;
|
||
if v_tz is null then return jsonb_build_object('changed',0,'ids','[]'::jsonb); end if;
|
||
v_iso:=to_char(p_now at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.MS"Z"');
|
||
for r in
|
||
select order_id,data,version from public.sun_v17_orders
|
||
where workspace_id=p_workspace
|
||
and lower(coalesce(data->>'status','')) not in ('отменён','отменен','отдан заказчику','завершён','завершен')
|
||
and coalesce(data->>'sunAutoCompletedAt','')=''
|
||
and public.sun_order_due_at(data,v_tz) is not null
|
||
and public.sun_order_due_at(data,v_tz) <= p_now
|
||
for update
|
||
loop
|
||
v_total:=case when coalesce(r.data->>'total','') ~ '^-?\d+(\.\d+)?$' then greatest(0,(r.data->>'total')::numeric) else 0 end;
|
||
update public.sun_v17_orders
|
||
set data=r.data||jsonb_build_object('prepayment',v_total,'balance',0,'status','Отдан заказчику','paymentStatus','paid','completedAt',coalesce(nullif(r.data->>'completedAt',''),v_iso),'paymentCompletedAt',coalesce(nullif(r.data->>'paymentCompletedAt',''),v_iso),'sunAutoCompletedAt',v_iso,'sunAutoCompletedV1770',true,'sunAutoCompletedVersion','17.7.0'),version=version+1,updated_at=p_now,updated_by=null
|
||
where workspace_id=p_workspace and order_id=r.order_id;
|
||
insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,version,client_id,created_by)
|
||
values(p_workspace,'order',r.order_id,'upsert',r.version+1,'server:auto-complete',null);
|
||
v_changed:=v_changed+1;v_ids:=array_append(v_ids,r.order_id);
|
||
end loop;
|
||
if v_changed>0 then
|
||
select coalesce(jsonb_agg(o.data order by case when o.order_id ~ '^\d+$' then o.order_id::bigint else null end,o.order_id),'[]'::jsonb) into v_orders from public.sun_v17_orders o where o.workspace_id=p_workspace;
|
||
update public.sun_app_state s set payload=jsonb_set(coalesce(s.payload,'{}'::jsonb),'{storage,sunOrders}',jsonb_build_object('t','j','v',v_orders),true),revision=s.revision+1,updated_at=p_now,client_id='server:auto-complete' where s.workspace_id=p_workspace;
|
||
if not found then insert into public.sun_app_state(workspace_id,payload,revision,updated_at,client_id) values(p_workspace,jsonb_build_object('format','sun-cloud-v2','version',2,'storage',jsonb_build_object('sunOrders',jsonb_build_object('t','j','v',v_orders))),1,p_now,'server:auto-complete'); end if;
|
||
end if;
|
||
return jsonb_build_object('changed',v_changed,'ids',to_jsonb(v_ids),'workspace',p_workspace,'timezone',v_tz,'processed_at',v_iso);
|
||
end;$$;
|
||
|
||
create or replace function public.sun_run_order_automation(p_workspace uuid)
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public' as $$
|
||
declare v_result jsonb; v_ids text[]; v_orders jsonb;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if public.sun_subscription_access_mode(p_workspace)='blocked' then raise exception 'Подписка закончилась'; end if;
|
||
v_result:=public.sun_process_due_orders_internal(p_workspace,now());
|
||
select coalesce(array_agg(value::text),'{}'::text[]) into v_ids from jsonb_array_elements_text(coalesce(v_result->'ids','[]'::jsonb));
|
||
if coalesce(array_length(v_ids,1),0)>0 then select coalesce(jsonb_agg(o.data order by o.order_id),'[]'::jsonb) into v_orders from public.sun_v17_orders o where o.workspace_id=p_workspace and o.order_id=any(v_ids); else v_orders:='[]'::jsonb; end if;
|
||
return v_result||jsonb_build_object('orders',v_orders);
|
||
end;$$;
|
||
|
||
create or replace function public.caterium_process_due_orders_all()
|
||
returns jsonb
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public' as $$
|
||
declare r record; v_total integer:=0; v_result jsonb;
|
||
begin
|
||
for r in select id from public.sun_workspaces loop v_result:=public.sun_process_due_orders_internal(r.id,now());v_total:=v_total+coalesce((v_result->>'changed')::integer,0);end loop;
|
||
return jsonb_build_object('changed',v_total,'processed_at',now());
|
||
end;$$;
|
||
|
||
revoke all on function public.sun_process_due_orders_internal(uuid,timestamptz) from public,anon,authenticated;
|
||
revoke all on function public.caterium_process_due_orders_all() from public,anon,authenticated;
|
||
revoke all on function public.sun_order_due_at(jsonb,text) from anon;
|
||
grant execute on function public.sun_run_order_automation(uuid) to authenticated;
|
||
|
||
do $$ declare v_job bigint; begin
|
||
select jobid into v_job from cron.job where jobname='caterium-order-auto-complete';
|
||
if v_job is not null then perform cron.unschedule(v_job); end if;
|
||
perform cron.schedule('caterium-order-auto-complete','* * * * *','select public.caterium_process_due_orders_all();');
|
||
end$$;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.0-ERROR-TELEMETRY-HYGIENE.sql
|
||
create or replace function public.sun_v17_log_error(p_workspace uuid, p_client_id text, p_app_version text, p_level text, p_message text, p_stack text, p_context jsonb default '{}'::jsonb)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path='public' as $$
|
||
declare v_id uuid; v_url text:=coalesce(p_context->>'url',''); v_message text:=left(coalesce(p_message,'Unknown error'),4000); v_level text:=left(coalesce(p_level,'error'),30); v_version text:=left(coalesce(p_app_version,''),80);
|
||
begin
|
||
if p_workspace is not null and public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if v_url ~* '^file:' then return null; end if;
|
||
select e.id into v_id from public.sun_v17_error_events e
|
||
where e.workspace_id is not distinct from p_workspace
|
||
and e.user_id is not distinct from auth.uid()
|
||
and coalesce(e.app_version,'')=v_version
|
||
and coalesce(e.level,'error')=v_level
|
||
and e.message=v_message
|
||
and e.created_at>=now()-interval '5 minutes'
|
||
order by e.created_at desc limit 1;
|
||
if v_id is not null then return v_id; end if;
|
||
insert into public.sun_v17_error_events(workspace_id,user_id,client_id,app_version,level,message,stack,context)
|
||
values(p_workspace,auth.uid(),left(p_client_id,120),v_version,v_level,v_message,left(coalesce(p_stack,''),12000),coalesce(p_context,'{}'::jsonb)) returning id into v_id;
|
||
return v_id;
|
||
end;$$;
|
||
|
||
delete from public.sun_v17_error_events where coalesce(context->>'url','') ~* '^file:';
|
||
|
||
do $$ declare v_job bigint; begin
|
||
select jobid into v_job from cron.job where jobname='caterium-error-retention';
|
||
if v_job is not null then perform cron.unschedule(v_job); end if;
|
||
perform cron.schedule('caterium-error-retention','23 3 * * *',$cron$delete from public.sun_v17_error_events where created_at < now()-interval '30 days';$cron$);
|
||
end$$;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.2-CLIENTS-FOUNDATION.sql
|
||
-- Caterium v17.7.2 — normalized client foundation
|
||
-- Additive migration: preserves legacy state and existing client rows.
|
||
|
||
create or replace function public.sun_v17_save_client_v1772(
|
||
p_workspace uuid,
|
||
p_client_key text,
|
||
p_profile jsonb,
|
||
p_expected_version bigint default null,
|
||
p_client_id text default null
|
||
)
|
||
returns table(client_key text, version bigint, data jsonb, updated_at timestamptz)
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $function$
|
||
declare
|
||
cur bigint;
|
||
saved public.sun_v17_clients%rowtype;
|
||
next_name text;
|
||
next_phone text;
|
||
next_address text;
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then
|
||
raise exception 'Access denied';
|
||
end if;
|
||
if public.sun_subscription_access_mode(p_workspace) <> 'full'
|
||
or not public.sun_workspace_has_feature(p_workspace,'clients') then
|
||
raise exception 'Клиенты недоступны для изменения';
|
||
end if;
|
||
if not public.sun_has_permission(p_workspace,'clients.edit') then
|
||
raise exception 'Нет права изменять клиентов';
|
||
end if;
|
||
if coalesce(nullif(trim(p_client_key),''),'') = '' then
|
||
raise exception 'Client key is required';
|
||
end if;
|
||
if jsonb_typeof(coalesce(p_profile,'{}'::jsonb)) <> 'object' then
|
||
raise exception 'Invalid client profile';
|
||
end if;
|
||
|
||
select c.version into cur
|
||
from public.sun_v17_clients c
|
||
where c.workspace_id=p_workspace and c.client_key=p_client_key
|
||
for update;
|
||
|
||
if found and p_expected_version is not null and cur <> p_expected_version then
|
||
raise exception using errcode='40001',message=format('SUN_CLIENT_CONFLICT expected=%s actual=%s',p_expected_version,cur);
|
||
end if;
|
||
|
||
next_name=nullif(trim(coalesce(p_profile#>>'{identity,name}',p_profile->>'name','')),'');
|
||
next_phone=nullif(trim(coalesce(p_profile#>>'{identity,phone}',p_profile->>'phone','')),'');
|
||
next_address=nullif(trim(coalesce(p_profile#>>'{identity,latestAddress}',p_profile->>'latestAddress','')),'');
|
||
|
||
insert into public.sun_v17_clients(workspace_id,client_key,name,phone,latest_address,data,version,created_at,updated_at)
|
||
values(p_workspace,p_client_key,next_name,next_phone,next_address,p_profile,1,now(),now())
|
||
on conflict on constraint sun_v17_clients_pkey do update set
|
||
name=coalesce(excluded.name,sun_v17_clients.name),
|
||
phone=coalesce(excluded.phone,sun_v17_clients.phone),
|
||
latest_address=coalesce(excluded.latest_address,sun_v17_clients.latest_address),
|
||
data=excluded.data,
|
||
version=case when sun_v17_clients.data is distinct from excluded.data then sun_v17_clients.version+1 else sun_v17_clients.version end,
|
||
updated_at=case when sun_v17_clients.data is distinct from excluded.data then now() else sun_v17_clients.updated_at end
|
||
returning * into saved;
|
||
|
||
if not found or cur is distinct from saved.version then
|
||
insert into public.sun_v17_change_events(workspace_id,entity,entity_key,operation,version,client_id,created_by)
|
||
values(p_workspace,'client',p_client_key,'upsert',saved.version,p_client_id,auth.uid());
|
||
end if;
|
||
|
||
return query select saved.client_key,saved.version,saved.data,saved.updated_at;
|
||
end;
|
||
$function$;
|
||
|
||
revoke execute on function public.sun_v17_save_client_v1772(uuid,text,jsonb,bigint,text) from public, anon;
|
||
grant execute on function public.sun_v17_save_client_v1772(uuid,text,jsonb,bigint,text) to authenticated, service_role;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.3-CLIENTS-SERVER-READ.sql
|
||
-- Caterium v17.7.3 — canonical client server-read snapshot
|
||
-- Read-only/additive migration. Existing p:/n:/o: rows are preserved.
|
||
|
||
create or replace function public.sun_v17_clients_snapshot_v1773(p_workspace uuid)
|
||
returns table(
|
||
client_key text,
|
||
name text,
|
||
phone text,
|
||
latest_address text,
|
||
data jsonb,
|
||
version bigint,
|
||
updated_at timestamptz,
|
||
source_keys text[],
|
||
source_count bigint,
|
||
canonical_row_present boolean
|
||
)
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as $function$
|
||
begin
|
||
if public.sun_member_role(p_workspace) is null then
|
||
raise exception 'Access denied';
|
||
end if;
|
||
if not public.sun_workspace_has_feature(p_workspace,'clients') then
|
||
raise exception 'Клиенты недоступны';
|
||
end if;
|
||
|
||
return query
|
||
with prepared as (
|
||
select c.*,
|
||
regexp_replace(coalesce(nullif(trim(c.phone),''),nullif(trim(c.data#>>'{identity,phone}'),''),nullif(trim(c.data->>'phone'),''),''),'[^0-9]','','g') as phone_digits,
|
||
lower(regexp_replace(coalesce(nullif(trim(c.name),''),nullif(trim(c.data#>>'{identity,name}'),''),nullif(trim(c.data->>'name'),''),''),'[[:space:]]+',' ','g')) as name_norm
|
||
from public.sun_v17_clients c
|
||
where c.workspace_id=p_workspace
|
||
), canonical as (
|
||
select p.*,case when p.phone_digits<>'' then 'p:'||p.phone_digits when p.name_norm<>'' then 'n:'||p.name_norm else p.client_key end as canonical_key
|
||
from prepared p
|
||
), ranked as (
|
||
select c.*,row_number() over(partition by c.canonical_key order by (c.client_key=c.canonical_key) desc,c.updated_at desc,c.version desc,c.client_key) as rn
|
||
from canonical c
|
||
), grouped as (
|
||
select c.canonical_key,array_agg(c.client_key order by (c.client_key=c.canonical_key) desc,c.updated_at desc,c.client_key) as source_keys,count(*)::bigint as source_count,bool_or(c.client_key=c.canonical_key) as canonical_row_present
|
||
from canonical c group by c.canonical_key
|
||
)
|
||
select r.canonical_key,
|
||
coalesce(nullif(trim(r.name),''),nullif(trim(r.data#>>'{identity,name}'),''),nullif(trim(r.data->>'name'),'')),
|
||
coalesce(nullif(trim(r.phone),''),nullif(trim(r.data#>>'{identity,phone}'),''),nullif(trim(r.data->>'phone'),'')),
|
||
coalesce(nullif(trim(r.latest_address),''),nullif(trim(r.data#>>'{identity,latestAddress}'),''),nullif(trim(r.data->>'latestAddress'),'')),
|
||
coalesce(r.data,'{}'::jsonb),r.version,r.updated_at,g.source_keys,g.source_count,g.canonical_row_present
|
||
from ranked r join grouped g on g.canonical_key=r.canonical_key
|
||
where r.rn=1
|
||
order by r.updated_at desc,r.canonical_key;
|
||
end;
|
||
$function$;
|
||
|
||
revoke execute on function public.sun_v17_clients_snapshot_v1773(uuid) from public, anon;
|
||
grant execute on function public.sun_v17_clients_snapshot_v1773(uuid) to authenticated, service_role;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.4-AUTH-EMAIL-VERIFICATION.sql
|
||
-- Caterium v17.7.4: require real Supabase email verification for client-created accounts.
|
||
-- The removed triggers trusted raw_user_meta_data.registration_source, which is client-controlled.
|
||
drop trigger if exists caterium_autoconfirm_signup_v25 on auth.users;
|
||
drop trigger if exists caterium_autoverify_identity_v25 on auth.identities;
|
||
|
||
drop function if exists public.caterium_autoconfirm_signup_v25();
|
||
drop function if exists public.caterium_autoverify_identity_v25();
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.7.4-ADVISOR-HARDENING.sql
|
||
|
||
-- Keep the same RLS semantics while evaluating auth.uid() once per statement.
|
||
alter policy sun_chat_reads_read_v29 on public.sun_chat_reads
|
||
using ((user_id = (select auth.uid())) and public.sun_chat_can_access_thread_v29(thread_id));
|
||
|
||
-- Cover foreign keys used by chat joins/deletes.
|
||
create index if not exists sun_chat_messages_sender_user_id_idx on public.sun_chat_messages(sender_user_id);
|
||
create index if not exists sun_chat_participants_workspace_id_idx on public.sun_chat_participants(workspace_id);
|
||
create index if not exists sun_chat_reads_workspace_id_idx on public.sun_chat_reads(workspace_id);
|
||
create index if not exists sun_chat_threads_created_by_idx on public.sun_chat_threads(created_by);
|
||
|
||
-- Remove exact duplicate indexes, retaining the clearer canonical names.
|
||
drop index if exists public.sun_v17_error_workspace_created_idx;
|
||
drop index if exists public.sun_v17_orders_due_idx;
|
||
|
||
-- Server order automation is for signed-in workspace members only.
|
||
revoke execute on function public.sun_run_order_automation(uuid) from public;
|
||
revoke execute on function public.sun_run_order_automation(uuid) from anon;
|
||
grant execute on function public.sun_run_order_automation(uuid) to authenticated;
|
||
grant execute on function public.sun_run_order_automation(uuid) to service_role;
|
||
|
||
-- Source: ops/sql/SUPABASE-FRESH-PROMOS-AND-ADMIN.sql
|
||
-- Missing production RPC contracts reconstructed for the empty Caterium project.
|
||
create table public.caterium_trial_promos (
|
||
id uuid primary key default gen_random_uuid(),code text not null unique,
|
||
client_email text,trial_days integer not null default 14 check(trial_days between 1 and 365),
|
||
plan_id text not null default 'full' references public.sun_plans(id),
|
||
max_uses integer not null default 1 check(max_uses between 1 and 10000),use_count integer not null default 0,
|
||
is_active boolean not null default true,valid_until timestamptz,note text,
|
||
created_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 public.caterium_trial_redemptions (
|
||
id uuid primary key default gen_random_uuid(),promo_id uuid not null references public.caterium_trial_promos(id),
|
||
workspace_id uuid not null unique references public.sun_workspaces(id) on delete cascade,
|
||
user_id uuid not null unique references auth.users(id) on delete cascade,email text,trial_ends_at timestamptz,
|
||
created_at timestamptz not null default now()
|
||
);
|
||
alter table public.caterium_trial_promos enable row level security;
|
||
alter table public.caterium_trial_redemptions enable row level security;
|
||
revoke all on public.caterium_trial_promos,public.caterium_trial_redemptions from anon,authenticated;
|
||
create function public.caterium_normalize_trial_code(p_code text) returns text language sql immutable set search_path=public as $$select upper(regexp_replace(trim(coalesce(p_code,'')),'[[:space:]]','','g'))$$;
|
||
create function public.caterium_trial_promo_preview(p_code text,p_email text default null)
|
||
returns jsonb language plpgsql stable security definer set search_path=public as $$
|
||
declare p public.caterium_trial_promos%rowtype;
|
||
begin
|
||
select * into p from public.caterium_trial_promos where code=public.caterium_normalize_trial_code(p_code);
|
||
if not found or not p.is_active or (p.valid_until is not null and p.valid_until<=now()) or p.use_count>=p.max_uses
|
||
or (p.client_email is not null and p.client_email<>lower(trim(coalesce(p_email,'')))) then
|
||
return jsonb_build_object('valid',false,'reason','Промокод недействителен для этого email или срок его действия истёк');
|
||
end if;
|
||
return jsonb_build_object('valid',true,'trial_days',p.trial_days,'plan',p.plan_id);
|
||
end $$;
|
||
create function public.sun_dev_create_trial_promo(p_code text default null,p_email text default null,p_trial_days integer default 14,p_valid_days integer default 7,p_max_uses integer default 1,p_plan text default 'full',p_note text default null)
|
||
returns jsonb language plpgsql security definer set search_path=public as $$
|
||
declare p public.caterium_trial_promos%rowtype; c text;
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
c:=coalesce(nullif(public.caterium_normalize_trial_code(p_code),''),'CTM-'||upper(replace(gen_random_uuid()::text,'-',''))::varchar(16));
|
||
if c !~ '^[A-Z0-9-]{3,32}$' then raise exception 'Некорректный промокод'; end if;
|
||
if p_valid_days not between 1 and 365 then raise exception 'Некорректный срок'; end if;
|
||
insert into public.caterium_trial_promos(code,client_email,trial_days,valid_until,max_uses,plan_id,note,created_by)
|
||
values(c,nullif(lower(trim(p_email)),''),p_trial_days,now()+make_interval(days=>p_valid_days),p_max_uses,p_plan,p_note,auth.uid()) returning * into p;
|
||
perform public.sun_platform_log_event('trial_promo.create',null,null,jsonb_build_object('promo_id',p.id));
|
||
return jsonb_build_object('promo_id',p.id,'code',p.code,'trial_days',p.trial_days,'valid_until',p.valid_until);
|
||
end $$;
|
||
create function public.sun_dev_list_trial_promos(p_limit integer default 300)
|
||
returns table(promo_id uuid,code text,client_email text,trial_days integer,valid_until timestamptz,max_uses integer,use_count integer,is_active boolean,last_redeemed_at timestamptz,last_workspace_name text,last_redeemed_email text)
|
||
language plpgsql stable security definer set search_path=public as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
return query select p.id,p.code,p.client_email,p.trial_days,p.valid_until,p.max_uses,p.use_count,p.is_active,r.created_at,w.name,r.email
|
||
from public.caterium_trial_promos p left join lateral (select x.* from public.caterium_trial_redemptions x where x.promo_id=p.id order by x.created_at desc limit 1) r on true
|
||
left join public.sun_workspaces w on w.id=r.workspace_id order by p.created_at desc limit greatest(1,least(coalesce(p_limit,300),1000));
|
||
end $$;
|
||
create function public.sun_dev_set_trial_promo_active(p_promo uuid,p_active boolean)
|
||
returns void language plpgsql security definer set search_path=public as $$
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
update public.caterium_trial_promos set is_active=p_active,updated_at=now() where id=p_promo;
|
||
if not found then raise exception 'Промокод не найден'; end if;
|
||
perform public.sun_platform_log_event('trial_promo.set_active',null,null,jsonb_build_object('promo_id',p_promo,'active',p_active));
|
||
end $$;
|
||
|
||
create function public.caterium_platform_create_company(p_name text,p_owner_email text default null,p_plan text default 'full',p_days integer default 30,p_mode text default 'empty')
|
||
returns jsonb language plpgsql security definer set search_path=public,auth as $$
|
||
declare v_ws uuid; v_owner uuid; v_email text:=nullif(lower(trim(p_owner_email)),''); v_token uuid;
|
||
begin
|
||
perform public.sun_require_platform_admin_aal2();
|
||
if p_days not between 1 and 3650 then raise exception 'Invalid subscription duration'; end if;
|
||
select id into v_owner from auth.users where lower(email)=v_email and email_confirmed_at is not null;
|
||
insert into public.sun_workspaces(name,created_by) values(coalesce(nullif(trim(p_name),''),'Новая компания'),coalesce(v_owner,auth.uid())) returning id into v_ws;
|
||
if v_owner is not null then
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,is_active,permissions,display_name)
|
||
values(v_ws,v_owner,'admin',true,public.sun_role_default_permissions('admin'),split_part(v_email,'@',1));
|
||
elsif v_email is not null then
|
||
insert into public.caterium_company_owner_invites(workspace_id,email) values(v_ws,v_email) returning token into v_token;
|
||
end if;
|
||
insert into public.sun_app_state(workspace_id,client_id) values(v_ws,'platform-bootstrap');
|
||
insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,current_period_start,current_period_end,grace_until,source)
|
||
values(v_ws,p_plan,'active',now(),now()+make_interval(days=>p_days),now()+make_interval(days=>p_days+7),'platform');
|
||
return jsonb_build_object('workspace_id',v_ws,'owner_user_id',v_owner,'owner_email',v_email,'owner_invite_token',v_token);
|
||
end $$;
|
||
|
||
-- Recover the sun_dev_* names used by the client, preserving the retained
|
||
-- server implementation and requiring AAL2 at every platform boundary.
|
||
do $recovery$
|
||
declare entry record; f record; call_args text; command text;
|
||
begin
|
||
for entry in select * from (values
|
||
('sun_dev_dashboard','sun_platform_dashboard'),('sun_dev_list_activity','sun_platform_list_activity'),
|
||
('sun_dev_list_companies','sun_platform_list_companies_v22'),('sun_dev_list_users','sun_platform_list_users_v22'),
|
||
('sun_dev_list_errors','sun_platform_list_errors_v22'),('sun_dev_support_snapshot','sun_platform_support_snapshot'),
|
||
('sun_dev_workspace_diagnostics','sun_platform_workspace_diagnostics'),('sun_dev_list_workspace_features','sun_platform_list_workspace_features'),
|
||
('sun_dev_log_event','sun_platform_log_event'),('sun_dev_set_plan_feature','sun_platform_set_plan_feature'),
|
||
('sun_dev_set_plan_max_members','sun_platform_set_plan_max_members'),('sun_dev_seed_workspace_catalog','sun_platform_seed_workspace_catalog'),
|
||
('sun_dev_reset_feature_override','sun_platform_reset_feature_override'),('sun_dev_create_company','sun_platform_create_company_v22'),
|
||
('sun_dev_set_subscription','sun_platform_set_subscription'),('sun_dev_set_feature_override','sun_platform_set_feature_override')
|
||
) names(alias_name,source_name) loop
|
||
select p.*,pg_get_function_arguments(p.oid) as args,pg_get_function_result(p.oid) as result into strict f
|
||
from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname=entry.source_name;
|
||
select coalesce(string_agg('$'||i,',' order by i),'') into call_args from generate_series(1,f.pronargs) i;
|
||
command:=case when f.proretset then 'return query select * from' when f.prorettype='void'::regtype then 'perform' else 'return' end;
|
||
execute format('create function public.%I(%s) returns %s language plpgsql security definer set search_path=public,auth as $body$ begin perform public.sun_require_platform_admin_aal2(); %s public.%I(%s); end $body$',entry.alias_name,f.args,f.result,command,entry.source_name,call_args);
|
||
end loop;
|
||
end $recovery$;
|
||
|
||
revoke all on function public.caterium_normalize_trial_code(text),public.caterium_trial_promo_preview(text,text),public.caterium_platform_create_company(text,text,text,integer,text) from public,anon,authenticated;
|
||
grant execute on function public.caterium_trial_promo_preview(text,text) to anon,authenticated;
|
||
do $$declare f record;begin
|
||
for f in select p.oid::regprocedure as signature from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname like 'sun_dev_%' loop
|
||
execute format('revoke all on function %s from public,anon',f.signature);
|
||
execute format('grant execute on function %s to authenticated',f.signature);
|
||
end loop;
|
||
end $$;
|
||
|
||
|
||
-- Source: ops/sql/SUPABASE-V17.8.2-OWNER-WORKSPACE-ONBOARDING.sql
|
||
-- Caterium v17.8.2
|
||
-- Public company creation belongs only to a new owner redeeming a trial promo.
|
||
-- Employees are attached to an existing workspace through membership/invite flows.
|
||
create or replace function public.sun_create_workspace(p_name text default 'Новая компания'::text)
|
||
returns uuid
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public, auth, pg_temp
|
||
as $function$
|
||
declare
|
||
v_user uuid:=auth.uid();
|
||
v_workspace uuid;
|
||
v_name text:='Новая компания';
|
||
v_email text;
|
||
v_profile jsonb;
|
||
v_storage jsonb;
|
||
v_code text;
|
||
v_promo public.caterium_trial_promos%rowtype;
|
||
v_trial_end timestamptz;
|
||
begin
|
||
if v_user is null then raise exception 'Authentication required'; end if;
|
||
if not exists(select 1 from auth.users where id=v_user and email_confirmed_at is not null) then raise exception 'Подтвердите email'; end if;
|
||
if exists(select 1 from public.sun_workspace_members where user_id=v_user and is_active=true) then
|
||
raise exception 'Аккаунт уже относится к компании';
|
||
end if;
|
||
|
||
select lower(email),public.caterium_normalize_trial_code(raw_user_meta_data->>'promo_code')
|
||
into v_email,v_code from auth.users where id=v_user;
|
||
if coalesce(v_code,'')='' then raise exception 'Для создания новой компании нужен промокод пробной версии'; end if;
|
||
if exists(select 1 from public.caterium_trial_redemptions where user_id=v_user) then raise exception 'Пробный период для этого аккаунта уже использован'; end if;
|
||
|
||
select * into v_promo from public.caterium_trial_promos where code=v_code for update;
|
||
if not found then raise exception 'Промокод не найден'; end if;
|
||
if not v_promo.is_active then raise exception 'Промокод отключён'; end if;
|
||
if v_promo.valid_until is not null and v_promo.valid_until<=now() then raise exception 'Срок действия промокода истёк'; end if;
|
||
if v_promo.use_count>=v_promo.max_uses then raise exception 'Промокод уже использован'; end if;
|
||
if v_promo.client_email is not null and lower(v_promo.client_email)<>v_email then raise exception 'Промокод предназначен для другого email'; end if;
|
||
|
||
v_trial_end:=now()+make_interval(days=>v_promo.trial_days);
|
||
insert into public.sun_workspaces(name,created_by) values(v_name,v_user) returning id into v_workspace;
|
||
insert into public.sun_workspace_members(workspace_id,user_id,role,display_name,is_active,permissions)
|
||
values(v_workspace,v_user,'admin',coalesce((select raw_user_meta_data->>'name' from auth.users where id=v_user),split_part(coalesce(v_email,'Администратор'),'@',1)),true,public.sun_role_default_permissions('admin'));
|
||
|
||
v_profile:=jsonb_build_object('name','','shortName','','logo','','tagline','','city','','phone','','email',coalesce(v_email,''),'website','','address','','legalName','','inn','','kpp','','ogrn','','legalAddress','','bank','','bik','','account','','corrAccount','','legacyLocked',false);
|
||
v_storage:=jsonb_build_object('sunCompanyProfileV1',v_profile::text);
|
||
insert into public.sun_app_state(workspace_id,payload,client_id)
|
||
values(v_workspace,jsonb_build_object('format','sun-cloud-v2','version',2,'storage',v_storage),'bootstrap') on conflict(workspace_id) do nothing;
|
||
|
||
insert into public.sun_workspace_subscriptions(workspace_id,plan_id,status,trial_started_at,trial_ends_at,grace_until,source,note)
|
||
values(v_workspace,v_promo.plan_id,'trialing',now(),v_trial_end,v_trial_end+interval '7 days','promo_trial','Trial by promo '||v_promo.code)
|
||
on conflict(workspace_id) do update set plan_id=excluded.plan_id,status=excluded.status,trial_started_at=excluded.trial_started_at,trial_ends_at=excluded.trial_ends_at,grace_until=excluded.grace_until,source=excluded.source,note=excluded.note,updated_at=now();
|
||
|
||
insert into public.caterium_trial_redemptions(promo_id,workspace_id,user_id,email,trial_ends_at)
|
||
values(v_promo.id,v_workspace,v_user,v_email,v_trial_end);
|
||
update public.caterium_trial_promos set use_count=use_count+1,updated_at=now() where id=v_promo.id;
|
||
update auth.users set raw_user_meta_data=coalesce(raw_user_meta_data,'{}'::jsonb)-'promo_code'-'company_name' where id=v_user;
|
||
insert into public.sun_platform_audit_events(actor_user_id,action,target_workspace_id,target_user_id,details)
|
||
values(v_user,'trial_promo.redeem',v_workspace,v_user,jsonb_build_object('promo_id',v_promo.id,'code',v_promo.code,'trial_days',v_promo.trial_days,'plan',v_promo.plan_id));
|
||
return v_workspace;
|
||
end;
|
||
$function$;
|
||
|
||
revoke all on function public.sun_create_workspace(text) from public,anon;
|
||
grant execute on function public.sun_create_workspace(text) to authenticated,service_role;
|
||
|
||
-- Source: ops/sql/SUPABASE-FRESH-FINAL-GUARDS.sql
|
||
-- Explicit API grants, with RLS on every application table.
|
||
do $$ declare f record;begin
|
||
for f in select p.oid::regprocedure as signature from pg_proc p join pg_namespace n on n.oid=p.pronamespace
|
||
where n.nspname='public' and (p.proname like 'sun_%' or p.proname like 'caterium_%') loop
|
||
execute format('revoke execute on function %s from public,anon',f.signature);
|
||
end loop;
|
||
end $$;
|
||
grant execute on function public.caterium_trial_promo_preview(text,text),public.sun_invite_preview_v27(uuid) to anon,authenticated;
|
||
grant execute on function public.sun_v17_log_error(uuid,text,text,text,text,text,jsonb) to authenticated;
|
||
|
||
-- No direct full-state writes/reads: retain the subscription/permission checks.
|
||
revoke all on public.sun_app_state from anon,authenticated;
|
||
|
||
-- Enforce MFA even if a caller uses an older public platform RPC name.
|
||
do $$ declare f record;definition text;begin
|
||
for f in select p.oid from pg_proc p join pg_namespace n on n.oid=p.pronamespace
|
||
where n.nspname='public' and p.proname like 'sun_platform_%' and p.prosrc like '%if not public.sun_is_platform_admin() then raise exception ''Platform administrator required''; end if;%' loop
|
||
definition:=replace(pg_get_functiondef(f.oid),'if not public.sun_is_platform_admin() then raise exception ''Platform administrator required''; end if;','perform public.sun_require_platform_admin_aal2();');
|
||
execute definition;
|
||
end loop;
|
||
end $$;
|
||
|
||
create or replace function public.sun_v17_entity_snapshot(p_workspace uuid)
|
||
returns jsonb language plpgsql stable security definer set search_path=public as $$
|
||
declare is_admin boolean:=public.sun_is_platform_admin();
|
||
begin
|
||
if is_admin then perform public.sun_require_platform_admin_aal2();
|
||
elsif public.sun_member_role(p_workspace) is null then raise exception 'Access denied'; end if;
|
||
if not is_admin and public.sun_subscription_access_mode(p_workspace)='blocked' then raise exception 'Подписка закончилась'; end if;
|
||
return jsonb_build_object(
|
||
'orders',case when is_admin or (public.sun_has_permission(p_workspace,'orders.view') and public.sun_workspace_has_feature(p_workspace,'orders')) then coalesce((select jsonb_agg(jsonb_build_object('id',order_id,'version',version,'data',data,'updated_at',updated_at) order by order_id) from public.sun_v17_orders where workspace_id=p_workspace),'[]') else '[]'::jsonb end,
|
||
'catalog',case when is_admin or (public.sun_has_permission(p_workspace,'catalog.view') and public.sun_workspace_has_feature(p_workspace,'catalog_view')) then coalesce((select jsonb_agg(jsonb_build_object('id',item_id,'version',version,'data',data,'updated_at',updated_at) order by item_id) from public.sun_v17_catalog_items where workspace_id=p_workspace),'[]') else '[]'::jsonb end,
|
||
'meta',coalesce((select to_jsonb(m) from public.sun_v17_workspace_meta m where workspace_id=p_workspace),'{}'));
|
||
end $$;
|
||
|
||
-- Authenticated roles can invoke public API guards but cannot invoke snapshot internals.
|
||
grant execute on function public.sun_v17_entity_snapshot(uuid) to authenticated;
|
||
revoke all on function public.sun_v17_build_snapshot(uuid),public.sun_require_platform_admin_aal2() from public,anon,authenticated;
|
||
|
||
-- Keep owner-registration metadata in the same typed format as the sync client.
|
||
do $$declare definition text;begin
|
||
definition:=pg_get_functiondef('public.sun_create_workspace(text)'::regprocedure);
|
||
definition:=replace(definition,'''sunCompanyProfileV1'',v_profile::text','''sunCompanyProfileV1'',jsonb_build_object(''t'',''j'',''v'',v_profile)');
|
||
execute definition;
|
||
end $$;
|
||
|
||
-- Match client visibility to the same granular permission as state reads.
|
||
do $$declare definition text;begin
|
||
definition:=pg_get_functiondef('public.sun_v17_clients_snapshot_v1773(uuid)'::regprocedure);
|
||
definition:=replace(definition,'if not public.sun_workspace_has_feature(p_workspace,''clients'') then','if not public.sun_has_permission(p_workspace,''clients.view'') or public.sun_subscription_access_mode(p_workspace)=''blocked'' or not public.sun_workspace_has_feature(p_workspace,''clients'') then');
|
||
execute definition;
|
||
end $$;
|
||
|
||
|
||
notify pgrst,'reload schema';
|
||
commit;
|
||
select (select count(*) from pg_tables where schemaname='public') as tables,(select count(*) from public.sun_workspaces) as workspaces,(select count(*) from auth.users) as users;
|