5.2 KiB
CONTRACT: V1-1.1 Workspaces (M1), owner brief 2026-10-25 « Validation du contrat V1-1 et autorisation de V1-1.1 Workspaces »
- branch
feature/v1-1-clients-animals, base 5d734be; code commit7bf4fb6 - functional contract
docs/development/V1-1_OWNERS_ANIMALS.md§3, §4, §8 (M1 sketch), §9.1, §9.2, §9.5, §10, §12.1 - diff under review:
git diff 5d734be..HEAD -- supabase packages/supabase - isolated real-stack evidence:
/tmp/claude-1000/-home-bchanot-Documents-faunosteo/55c1fd3e-1c33-4c77-8509-9cd3ca683a18/scratchpad/sandbox/(RESULTS.md; scriptsup.sh,tap.sh,concurrency.sh,http.mjs,down.sh; containersfaunosteo_m1sandbox_*running, same images as the DEV stack).tap.shandnode http.mjsmay be re-run (rolled back / unique emails);concurrency.shinserts fixed ids and runs once per sandbox. The DEV stack (supabase_*_faunosteo) must not be touched.
Clauses
C1. Scope. One migration only, supabase/migrations/20261025100000_workspaces.sql (M1). No M2/M3 object (no clients,
animals, relations, species rows, unaccent, fold_text, write budget, write RPC), no React screen, no Playwright, no cabinet
feature, no change to crates/, no change to an existing migration file, no change to existing tables' grants or policies
except the private.log_event redefinition and the NOT VALID CHECK on audit.audit_logs.correlation_id.
C2. Model. Enums workspace_kind = {personal} and workspace_member_role = {holder} only (no unused cabinet role);
workspaces(id, kind, personal_user_id unique → profiles on delete cascade, created_at) with CHECK personal ⇔
personal_user_id not null; workspace_members(workspace_id → workspaces cascade, user_id → profiles cascade, member_role, created_at, PK (workspace_id, user_id)), index on user_id. No owner_id; names as in contract §3. No archive column:
a workspace follows its account (deletion cascades; M2's on delete restrict will block it when records exist, D9a).
C3. Creation. Server-side only: trigger after insert on profiles → private.ensure_personal_workspace(uuid), which
writes workspace and holder membership in one statement with ON CONFLICT on the unique key (atomic, idempotent, race-safe);
private.backfill_personal_workspaces() covers existing profiles, idempotent, called once by the migration after the
trigger exists. No API role can call either. Works with the existing sign-up chain (GoTrue → handle_new_auth_user →
profile → workspace, one transaction); the workspace exists before email confirmation and stays unreadable until then.
C4. Integrity. A deferred constraint trigger refuses at commit, whatever role writes: a personal workspace without its holder, a foreign member in a personal workspace, the removal of the holder alone, the transfer of a personal workspace to another account. Cabinets are not constrained. Error messages carry no id or personal data.
C5. Access rule. private.caller_workspace_ids(): no parameter, auth.uid() only, member + profiles.active +
auth.users.email_confirmed_at is not null; security definer, search_path = '', owner postgres, EXECUTE for
authenticated only. private.writable_workspace_ids(): same restricted to holder, EXECUTE for nobody. No platform-role
branch anywhere (D16). The private schema stays out of PostgREST (config.toml schemas = public, pinned by a test).
C6. RLS and privileges. RLS enabled on both tables; one SELECT policy each, for authenticated, through
caller_workspace_ids(), no using (true); no write policy. revoke all from public, anon, authenticated, service_role,
then column SELECT grants to authenticated only. Every new function: revoke all from public, anon, authenticated,
service_role; security definer; search_path = ''; owner postgres. Effective privileges tested for anon, authenticated and
service_role (SQL refusal 42501 distinct from an empty RLS result).
C7. Audit (N9). private.log_event keeps its signature, privileges and callers; x-correlation-id stored only when it
matches ^[A-Za-z0-9-]{1,64}$, else null; audit_logs_correlation_id_format CHECK NOT VALID on new rows. Append-only kept.
C8. Tests. pgTAP supabase/tests/012_workspaces.sql covers: creation with the account, holder membership, failed sign-up
leaves nothing, idempotence, uniqueness, FK/PK refusals, backfill (trigger disabled) and its idempotence, the commit
invariant, A/B isolation, refusal for unconfirmed (then access after confirmation), deactivated, platform staff, anon,
service_role, parameter usurpation (filters by other ids, inserts naming others), direct calls of private functions, the
privilege/definer/owner census, the empty account deletion, the correlation cap. 001_schema.sql census updated;
schema-types.test.ts and database.types.ts (regenerated, only M1 additions); config.test.ts pins
enable_confirmations = true, anonymous sign-ins off, schemas = public. All green on PGlite (pnpm test:run) and on the
isolated real stack from zero and on the upgrade path (sandbox RESULTS.md), with real concurrency evidence.
C9. Limits. Nothing applied to the DEV stack; no merge into develop; main and release/0.2.0 untouched; no deploy; no real personal data in fixtures.