-- V1-1.1 Workspaces (M1): the perimeter that will own and isolate clients, -- animals and later bilans. Contract: docs/development/V1-1_OWNERS_ANIMALS.md -- §4 (model), §8 (schema), §9 (helpers, RLS), §13.2 N7 / N9 (privileges, audit). -- -- In V1-1 every account has exactly one personal workspace, created by the -- server in the transaction that creates its profile (sign-up), never by a -- client call. Its only member is the account itself, with member_role -- holder. Nothing is shared between accounts; cabinets come with plan Step 10. -- -- Invariants pinned by supabase/tests/012_workspaces.sql: -- * one personal workspace per profile (unique personal_user_id), created -- with its holder membership in one statement, idempotent under retries -- and concurrent calls (ON CONFLICT on the unique key); -- * a personal workspace has exactly one member, its account as holder, -- checked at commit, so neither row ever survives alone; -- * a workspace is readable only by its members whose profile is active and -- whose email is confirmed; auth.uid() is the only input of that rule; -- * no API role writes either table; anon and service_role hold no -- privilege on them; no policy names a platform role (D16); -- * the audit correlation id is kept only when it is a short token, so a -- caller no longer chooses the size of an append-only audit row. -- --------------------------------------------------------------------------- -- Tables -- --------------------------------------------------------------------------- -- cabinet joins workspace_kind with the cabinet step; holder is the only -- member role until then (no unused cabinet role). create type public.workspace_kind as enum ('personal'); create type public.workspace_member_role as enum ('holder'); create table public.workspaces ( id uuid primary key default gen_random_uuid(), kind public.workspace_kind not null, -- The account whose personal workspace this is; null for a future cabinet. -- Deleting the account deletes its personal workspace, unless a record -- table refuses it (D9a, `on delete restrict` from M2 on). personal_user_id uuid references public.profiles (id) on delete cascade, created_at timestamptz not null default now(), constraint workspaces_personal_user_unique unique (personal_user_id), constraint workspaces_personal_user_iff_personal check ((kind = 'personal') = (personal_user_id is not null)) ); comment on table public.workspaces is 'Perimeter that owns and isolates records. V1-1: one personal workspace per account, created with its profile; no API write path; no archive (it follows its account).'; comment on column public.workspaces.personal_user_id is 'Account of a personal workspace (unique); null for a cabinet.'; create table public.workspace_members ( workspace_id uuid not null references public.workspaces (id) on delete cascade, user_id uuid not null references public.profiles (id) on delete cascade, member_role public.workspace_member_role not null, created_at timestamptz not null default now(), primary key (workspace_id, user_id) ); comment on table public.workspace_members is 'Membership and rights of an account in a workspace. V1-1: the holder of each personal workspace only; no API write path.'; -- The membership lookup of every workspace policy (by caller). create index workspace_members_user_idx on public.workspace_members (user_id); -- --------------------------------------------------------------------------- -- Invariant: a personal workspace has exactly one member, its account as -- holder. Deferred to commit so the two rows may be written in either order -- inside one transaction; a workspace or a membership left alone, or a -- foreign member added to a personal workspace, fails the commit whatever -- role writes it. Cabinets are not constrained here. -- --------------------------------------------------------------------------- create or replace function private.check_personal_workspace() returns trigger language plpgsql security definer set search_path = '' as $$ declare v_ids uuid[]; begin if tg_table_name = 'workspaces' then v_ids := array[new.id]; elsif tg_op = 'INSERT' then v_ids := array[new.workspace_id]; elsif tg_op = 'DELETE' then v_ids := array[old.workspace_id]; else v_ids := array[old.workspace_id, new.workspace_id]; end if; -- A deleted workspace has nothing left to check (account deletion cascades). if exists ( select 1 from public.workspaces w where w.id = any (v_ids) and w.kind = 'personal' and ( not exists ( select 1 from public.workspace_members m where m.workspace_id = w.id and m.user_id = w.personal_user_id and m.member_role = 'holder' ) or exists ( select 1 from public.workspace_members m where m.workspace_id = w.id and m.user_id <> w.personal_user_id ) ) ) then raise exception 'personal workspace must have exactly its account as holder' using errcode = '23514', constraint = 'workspaces_personal_holder'; end if; return null; end; $$; comment on function private.check_personal_workspace() is 'Deferred constraint trigger: a personal workspace has exactly one member, personal_user_id as holder.'; create constraint trigger workspaces_personal_holder after insert or update on public.workspaces deferrable initially deferred for each row execute function private.check_personal_workspace(); create constraint trigger workspace_members_personal_holder after insert or update or delete on public.workspace_members deferrable initially deferred for each row execute function private.check_personal_workspace(); -- --------------------------------------------------------------------------- -- Creation of the personal workspace -- --------------------------------------------------------------------------- -- The single write path: workspace and holder membership in one statement, -- so both rows exist or neither does. A replay, or a call racing another -- one, waits on the unique personal_user_id, then writes nothing and -- returns false. The id comes from the server (a new profile, the backfill), -- never from an API role: EXECUTE is granted to nobody. create or replace function private.ensure_personal_workspace(p_user_id uuid) returns boolean language sql volatile security definer set search_path = '' as $$ with created as ( insert into public.workspaces (kind, personal_user_id) values ('personal', p_user_id) on conflict (personal_user_id) do nothing returning id, personal_user_id ), joined as ( insert into public.workspace_members (workspace_id, user_id, member_role) select id, personal_user_id, 'holder' from created returning workspace_id ) select exists (select 1 from joined); $$; comment on function private.ensure_personal_workspace(uuid) is 'Creates the personal workspace of an existing profile with its holder membership, atomically; idempotent (false when it already exists).'; -- Sign-up: GoTrue inserts auth.users, private.handle_new_auth_user inserts the -- profile, this trigger creates the workspace, all in one transaction. The -- workspace exists before the email is confirmed but stays unreadable until -- then (private.caller_workspace_ids). create or replace function private.create_personal_workspace() returns trigger language plpgsql security definer set search_path = '' as $$ begin perform private.ensure_personal_workspace(new.id); return null; end; $$; comment on function private.create_personal_workspace() is 'AFTER INSERT on profiles: creates the personal workspace and its holder membership.'; create trigger profiles_create_personal_workspace after insert on public.profiles for each row execute function private.create_personal_workspace(); -- Accounts created before this migration. Called once below; idempotent, so -- a second call (or a call racing a sign-up) creates nothing twice. create or replace function private.backfill_personal_workspaces() returns integer language plpgsql security definer set search_path = '' as $$ declare v_profile_id uuid; v_created integer := 0; begin for v_profile_id in select p.id from public.profiles p where not exists ( select 1 from public.workspaces w where w.personal_user_id = p.id ) order by p.id loop if private.ensure_personal_workspace(v_profile_id) then v_created := v_created + 1; end if; end loop; return v_created; end; $$; comment on function private.backfill_personal_workspaces() is 'Creates the missing personal workspaces of existing profiles; returns how many were created. Idempotent.'; -- --------------------------------------------------------------------------- -- Access helpers (contract §9.1) -- --------------------------------------------------------------------------- -- The single source of the access rule of every workspace-scoped table: the -- workspaces where the caller is a member, with an active profile and a -- confirmed email. No parameter: auth.uid() (a JWT verified by PostgREST) is -- the only input, so a caller can name nobody else, and a direct call returns -- what the caller may already read. Definer, so it reads auth.users and -- workspace_members past their privileges and policies (no RLS recursion). create or replace function private.caller_workspace_ids() returns setof uuid language sql stable security definer set search_path = '' as $$ select m.workspace_id from public.workspace_members m join public.profiles p on p.id = m.user_id join auth.users u on u.id = m.user_id where m.user_id = (select auth.uid()) and p.active and u.email_confirmed_at is not null; $$; comment on function private.caller_workspace_ids() is 'Workspaces of auth.uid() as a member, active profile and confirmed email required. Evaluated by the workspace policies.'; -- The same set restricted to the member roles that may write records (V1-1: -- holder). Called inside the write RPCs of M2 and M3 only. create or replace function private.writable_workspace_ids() returns setof uuid language sql stable security definer set search_path = '' as $$ select m.workspace_id from public.workspace_members m where m.user_id = (select auth.uid()) and m.member_role = 'holder' and m.workspace_id in (select private.caller_workspace_ids()); $$; comment on function private.writable_workspace_ids() is 'caller_workspace_ids() restricted to a write role (holder). For definer RPCs only.'; -- --------------------------------------------------------------------------- -- Audit: cap the caller-controlled correlation id (contract §8, §10, N9) -- --------------------------------------------------------------------------- -- PostgREST exposes every request header, so x-correlation-id is chosen by -- the caller. Kept only when it is a short token, else stored as null. Same -- signature, privileges and callers as the foundation version. create or replace function private.log_event( p_actor_user_id uuid, p_action text, p_resource_type text, p_resource_id uuid default null, p_target_user_id uuid default null, p_organization_id uuid default null, p_metadata jsonb default '{}'::jsonb ) returns uuid language plpgsql security definer set search_path = '' as $$ declare v_id uuid; v_correlation text; begin -- PostgREST exposes request headers as a JSON setting; absent elsewhere. v_correlation := nullif(current_setting('request.headers', true), '')::jsonb ->> 'x-correlation-id'; if v_correlation !~ '^[A-Za-z0-9-]{1,64}$' then v_correlation := null; end if; insert into audit.audit_logs ( actor_user_id, action, resource_type, resource_id, target_user_id, organization_id, metadata, correlation_id ) values ( p_actor_user_id, p_action, p_resource_type, p_resource_id, p_target_user_id, p_organization_id, coalesce(p_metadata, '{}'::jsonb), v_correlation ) returning id into v_id; return v_id; end; $$; -- New rows only: rows written before this migration keep whatever they hold. alter table audit.audit_logs add constraint audit_logs_correlation_id_format check (correlation_id ~ '^[A-Za-z0-9-]{1,64}$') not valid; -- --------------------------------------------------------------------------- -- Privileges + RLS. Supabase default privileges grant ALL on new public -- tables to anon, authenticated and service_role: start from zero. The -- service key reads nothing here (N7); it remains a GoTrue admin credential -- (contract §6.3), which no table privilege can bound. -- --------------------------------------------------------------------------- revoke all on public.workspaces from public, anon, authenticated, service_role; revoke all on public.workspace_members from public, anon, authenticated, service_role; grant select (id, kind, personal_user_id, created_at) on public.workspaces to authenticated; grant select (workspace_id, user_id, member_role, created_at) on public.workspace_members to authenticated; alter table public.workspaces enable row level security; alter table public.workspace_members enable row level security; -- Members read their workspaces and the memberships of those workspaces. -- No write policy: every write is server-side (trigger, backfill, RPCs). create policy workspaces_select_member on public.workspaces for select to authenticated using (id in (select private.caller_workspace_ids())); create policy workspace_members_select_member on public.workspace_members for select to authenticated using (workspace_id in (select private.caller_workspace_ids())); revoke all on function private.check_personal_workspace() from public, anon, authenticated, service_role; revoke all on function private.ensure_personal_workspace(uuid) from public, anon, authenticated, service_role; revoke all on function private.create_personal_workspace() from public, anon, authenticated, service_role; revoke all on function private.backfill_personal_workspaces() from public, anon, authenticated, service_role; revoke all on function private.writable_workspace_ids() from public, anon, authenticated, service_role; revoke all on function private.caller_workspace_ids() from public, anon, authenticated, service_role; -- Policies run as the API role: it needs EXECUTE on the predicate. grant execute on function private.caller_workspace_ids() to authenticated; -- --------------------------------------------------------------------------- -- Existing accounts. Runs after the trigger exists, so a profile created -- while the migration runs is covered by one or the other; a double pass -- creates nothing twice (ensure_personal_workspace). -- --------------------------------------------------------------------------- select private.backfill_personal_workspaces();