59 lines
5.2 KiB
Markdown
59 lines
5.2 KiB
Markdown
# 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.
|