Files
faunosteo/supabase/migrations/20261025100000_workspaces.sql
bchanot 7bf4fb6079 feat(supabase): workspaces M1, one personal workspace per account
V1-1.1 (contract V1-1_OWNERS_ANIMALS.md §4, §8, §9). Tables workspaces and workspace_members, created with the profile by trigger through one atomic, idempotent write path; deferred invariant (a personal workspace has exactly its account as holder); backfill of existing profiles; access helpers caller_workspace_ids (active profile, confirmed email) and writable_workspace_ids; RLS select for members only; no privilege for anon or service_role. log_event keeps x-correlation-id only as a short token. pgTAP 012 (85 checks), census and types updated, config test.
2026-10-25 19:53:16 +01:00

354 lines
15 KiB
PL/PgSQL

-- 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();