476 lines
27 KiB
PL/PgSQL
476 lines
27 KiB
PL/PgSQL
-- 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;
|