Files
faunosteo/.claude/tasks/contracts/2026-10-25-v1-1-1-workspaces.md

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 commit 7bf4fb6
  • 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; scripts up.sh, tap.sh, concurrency.sh, http.mjs, down.sh; containers faunosteo_m1sandbox_* running, same images as the DEV stack). tap.sh and node http.mjs may be re-run (rolled back / unique emails); concurrency.sh inserts 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.