# V1-1 Clients and animals: contract Status: business decisions approved by the owner on 2026-10-25 (brief « V1-1 : ratification des décisions métier et préparation de l'implémentation », BDR-073). The architecture of Parts 4 to 9 and the technical choices N1 to N9 were validated by the owner the same day (brief « Validation du contrat V1-1 et autorisation de V1-1.1 Workspaces », BDR-074, §13.2), with the precisions written into §5.3, §9 and §10. The interface choices of §13.3 stay proposals until their UI steps. V1-1.1 was validated by the owner on 2026-10-25 (brief « Validation V1-1.1 et autorisation V1-1.2 Données clients », BDR-075), which authorised V1-1.2. V1-1.2 and V1-1.3 were validated on 2026-10-25 and 2026-10-26 (BDR-076; brief « Validation V1-1.3 et autorisation V1-1.4 Animaux et relations », BDR-077), which authorised V1-1.4. Migrations M1 (§8.1), M2 (§8.2) and M3 (§8.3) are written and tested on the isolated stack (`pnpm db:sandbox`, SUPABASE.md §5.1); none is applied to Supabase DEV (owner procedure: SUPABASE.md §3.3). V1-1.4 was validated on 2026-10-26 (brief « Validation V1-1.4 et autorisation V1-1.5 Interface Clients »), which authorised the client screens of V1-1.5 (§11.7), built and tested on the isolated stack. V1-1.5 was validated on 2026-10-26 (brief « Validation V1-1.5 et autorisation V1-1.6 Interface Animaux et clôture »), which authorised the animal screens and the relations of V1-1.6 (§11.8), built and tested on the isolated stack, and the closure inventory (§15). Branch: `feature/v1-1-clients-animals`. Roadmap position: `IMPLEMENTATION_PLAN.md` §0; this step is a reduced plan Step 12 that also lays the permission frame of Step 10. The file name keeps "OWNERS" so the documents that cite it keep working. Parts: 1 facts, 2 scope, 3 nomenclature, 4 workspaces, 5 entities, 6 permissions, 7 archive, deletion and data protection, 8 SQL schema, 9 policies, helpers and RPCs, 10 technical limits, 11 screens and journeys, 12 tests and Definition of Done, 13 decisions, 14 implementation steps and risks, 15 closure inventory. --- ## 1. Facts the contract rests on | Area | State on `develop` (5502617) | |---|---| | Client and animal data | Text inside a bilan only: report v3 `owner = { name }`, `animal = { speciesId, name, breed?, age? }` (`crates/core/src/domain/report/identity.rs`). No table. | | Species | `public.species` (`id` slug, `name_fr`, `name_en`, `sort_order`, `active`), one row `equine` / Cheval / Horse; SELECT for `authenticated` on active rows; no API write path. The application never reads this table (the editor uses the bundled sheet). | | Anatomical sheets | Only the equine DRAFT exists, unpublished until business review (BDR-034, BDR-037). Its content changes only through `curation/changes.json` and the Rust pipeline until plan Step 17. | | Accounts | Email/password sign-up, `enable_confirmations = true` (no session before the email is confirmed, `auth.users.email_confirmed_at`), anonymous sign-in disabled, `profiles.active` switched by staff only, `private.require_actor()` refuses a missing or deactivated actor. | | Roles | `platform_role` = owner, admin, moderator, support (BDR-026). No professional status and no cabinet table exist. | | Audit | `audit.audit_logs` append-only, written by `private.log_event()`, action format `domain.verb`; platform owners and admins read every row (`audit_logs_select_admin`). | | Files | `client-files` and `animal-files` buckets exist, closed (row only, no policy). `assets.owner_id` is the account that owns a file (Step 8). | | UI | `/app` shell (sidebar, drawer, top bar), `Dashboard`, `Account`. The validated mockup (`docs/design/`) shows Clients and Animaux sidebar entries and a mobile tab bar Accueil / Bilans / Agenda / Profil. The shared `Dialog` has no focus trap or focus restore (RR-7). | | Form validation | D-R7: form constraints stay TypeScript (Zod schemas returning i18n keys); the SQL CHECK is the server boundary; a constants module checked against `pg_constraint` by a PGlite test is planned (TODO chore D-R7), none exists yet. | | Tests | pgTAP on PGlite (0.3.16, which ships `unaccent` and `pg_trgm` as contrib; the harness loads only pgTAP today) and on the real stack; real-stack e2e pattern in `packages/supabase/tests/e2e`; no real-browser test runner (RR-8). | --- ## 2. Scope In V1-1, a signed-in practitioner with a confirmed email: 1. creates, edits, lists, searches, archives and restores clients, persons and structures; 2. creates, edits, lists, searches, archives and restores animals, each linked to zero, one or several clients with a role per link; 3. sees a client's animals and an animal's clients, current and former, and records a change of owner or keeper without losing the history; 4. does all of it on a phone (375 px wide) and on a computer, in French and English, with the keyboard alone and with a screen reader. Out of scope, each named so nobody builds it early: cabinet creation, membership management and invitations, sharing between practitioners, client portal and client accounts, any directory or matching across practitioners, consent records, animal weight, photo, detailed identification, coat colour, neutering status, date of death and notes (D6, deferred), client notes (§13.2 N3), files, imports (CSV, PDF), notifications, any link with bilans (V1-3), account closure, deletion, anonymisation and export (D9), quotas and billing, the professional verification process (plan Step 9), any staff access to the records (D16), any editing of the anatomical reference. The local tool and the bilan format do not change; annotating a bilan never changes `curation/changes.json`. --- ## 3. Nomenclature Owner requirement: one explicit name per concept, the same in SQL, in the generated TypeScript types and in the documents. The generated types carry the SQL names unchanged; TypeScript variables follow them (`relationRole`, `memberRole`, `platformRole`, never a bare `role` or `owner`). | Concept | SQL | UI (FR / EN) | |---|---|---| | Perimeter that owns and isolates records | table `workspaces`; column `workspace_id` on every record table | not shown in V1-1 | | Kind of workspace | `workspaces.kind`, enum `workspace_kind`: `personal` (`cabinet` added by the cabinet step) | espace personnel / personal workspace | | Account whose personal workspace it is | `workspaces.personal_user_id` (unique, null for a future cabinet) | not shown | | Membership and rights of a member | table `workspace_members` (`workspace_id`, `user_id`, `member_role`), enum `workspace_member_role`: `holder` in V1-1 | titulaire / holder | | Responsible of a workspace | personal workspace: the account of `personal_user_id`, its only member, with `member_role = holder`; cabinet: defined by the cabinet step (Step 10) | not shown | | Technical platform role | enum `platform_role` (`owner`, `admin`, `moderator`, `support`), table `profile_roles` (existing, BDR-026) | not shown | | Client | table `clients`, `client_id`; enum `client_kind`: `person`, `organization` | client: personne, structure / client: person, organization | | Animal | table `animals`, `animal_id`; enum `animal_sex`: `male`, `female`, `unknown` | animal: mâle, femelle, inconnu / animal: male, female, unknown | | Relation between a client and an animal | table `animal_client_relations`; column `relation_role`, enum `animal_relation_role`: `owner`, `keeper`, `contact` | propriétaire, détenteur, contact / owner, keeper, contact | | File owner (existing, Step 8) | `assets.owner_id` | not reused by V1-1 | Rules: - `owner` exists as `platform_role.owner` (the Faunosteo platform owner) and as `animal_relation_role.owner` (the owner of an animal). It is never a workspace member role: the person responsible for a personal workspace is its `holder`. - No record table (`clients`, `animals`, `animal_client_relations`) has an `owner_id`, `user_id` or `created_by` column. Placement is `workspace_id`; authorship lives in the audit log. - Future client or animal files are placed by workspace, never by `assets.owner_id`. - The schema↔types test pins every name of this table. --- ## 4. Workspaces (D2 / C4, approved) Clients and animals belong to a workspace. In V1-1 each account has exactly one personal workspace, created automatically. There is no global directory of clients shared between practitioners, no data visible from one practitioner to another, and no sharing between practitioners of a cabinet. Invitations and cabinet permissions are not built. - Creation: a controlled server initialisation, never a client call. A trigger after insert on `profiles` creates the personal workspace and its `holder` membership in the transaction that creates the account (GoTrue inserts `auth.users`, `private.handle_new_auth_user` inserts the profile, the trigger creates the workspace): a failure anywhere rolls back the whole sign-up, so no account exists without its workspace and no workspace without its account. The workspace exists before the email is confirmed and stays unreadable until then (§9.1). The migration backfills existing profiles through the same write path. - Uniqueness: `personal_user_id` is unique, so an account never has two personal workspaces. The single write path inserts with `on conflict do nothing` on that key: a retry, a replay or two concurrent calls create one workspace and one membership, never two (§8.1). - Consistency: a personal workspace has exactly one member, its account, as `holder`. A deferred constraint trigger checks it at commit, whatever role writes, so neither row survives alone and no other account can be added. - Deletion: no archive state for a workspace. A personal workspace follows its account: deleting an empty account deletes it; from M2 on, records refuse the deletion (D9a, §7.2). - Context: every read and write names its workspace (RPC parameter or filter). The app reads its personal workspace once after sign-in and passes its id explicitly to the access layer; nothing reads a global "current workspace" (plan Step 10 risk: context leakage). - Policies and RPCs never compare `auth.uid()` with a column of the record: they ask the workspace helpers (§9.1). The cabinet step adds members and roles, not new policies on every table. - Evolution without destructive transfer: a cabinet is a workspace of kind `cabinet`. Either a practitioner's workspace gains members and changes kind (no record moves), or a later step moves the records of one workspace to another in one transaction (the composite keys of §8 can then be made deferrable with `ALTER CONSTRAINT`). Records never belong to a user column, so neither path rewrites ownership. The first path leaves the practitioner without a personal workspace (`personal_user_id` is null on a cabinet), so it must also create a new one for them; the cabinet step chooses. Why not an `owner_user_id` column: every policy of clients, animals and later bilans would need a second branch for cabinets, and attaching a practitioner's records to a cabinet would rewrite ownership columns. The workspace costs two small tables now. --- ## 5. Entities and fields Text rule for every text column: no leading or trailing space, no control character, never an empty string (an absent value is `null`). The control set is written as explicit ranges, the same literal in SQL and in TypeScript (U+0000 to U+001F, U+007F to U+009F, U+2028, U+2029), because `[[:cntrl:]]` differs between the C libraries of PGlite and of the real stack. SQL checks U+0020 at both ends (`btrim`); the form trims all Unicode whitespace before sending, which only ever sends values SQL accepts. Lengths count characters as PostgreSQL's `char_length` does (code points); §12.2 states how TypeScript counts the same. Email pattern, shared by SQL and TypeScript: `^[^@\s]+@[^@\s]+\.[^@\s]+$` on the lower-cased value. Country codes come from a committed ISO 3166-1 alpha-2 list; their FR and EN labels from `Intl.DisplayNames` (no dependency). ### 5.1 Client (D3, D4, D4a, approved) | Field | Rule | Required | |---|---|---| | `kind` | `person` (personne physique) or `organization` (structure: écurie, haras, association, entreprise, other legal entity); the form shows both with « Personne » visibly selected | yes | | `display_name` | person: the name as the practitioner writes it (« Marie Dupont », « Mme Dupont »); organization: its name (« Écurie du Lac »); the label follows the kind: « Nom affiché » or « Dénomination »; 1 to 120 characters | yes, the only required text | | `email` | practical format, at most 254 characters, stored lower-case, not unique | no | | `phone` | E.164 like `profiles.phone`; entry rule §11.3 | no | | `address_line1`, `address_line2` | at most 120 characters each | no | | `postal_code` | at most 16 characters, no format check (Belgium, Switzerland) | no | | `city` | at most 80 characters | no | | `country` | ISO 3166-1 alpha-2, upper-case; no default in SQL or in the form: empty until the practitioner picks one | no | | `revision`, `archived_at`, `created_at`, `updated_at` | server-maintained | n/a | No uniqueness on email or phone: two clients may share a household address or phone. Contact details are optional at creation; what a booking, an invoice or a regulated document requires is set by the step that introduces it, never by a NOT NULL here. Changed from the proposal: `notes` is not in V1-1 (§13.2 N3) and `archived_by` is dropped (the audit log records who archived; a cabinet step adds the column if a screen shows it). Name rule (D4a): `display_name` is the single reference for display, search, sorting and documents. V1-1 stores no separate first and last name (recommendation §13.2 N2). If a later step adds optional `given_name` and `family_name` for persons, the rule is priority, not synchronisation: `display_name` stays required and wins wherever a name is shown; the parts are optional complements read only where a step requires them (a formal invoice heading, a sort by family name); an organization never carries them (CHECK); the server never derives one representation from the other; the form proposes `display_name` from the parts only while `display_name` is empty and never rewrites it afterwards. ### 5.2 Animal (D5, D6, D6a, D6b, approved) | Field | Rule | Required | |---|---|---| | `name` | 1 to 80 characters | yes | | `species_id` | an active row of `species` (§5.4) | yes | | `species_detail` | 1 to 80 characters; required when `species_id = 'other'`, refused with any other species | with « Autre » | | `sex` | `male`, `female` or `unknown`; no default: the practitioner picks one, « Inconnu » included (§13.2 N8) | yes | | `breed` | free text, at most 80 characters (no breed list) | no | | `birth_year` | 1900 to the year of tomorrow's UTC date (same tolerance as the dates, §9.3) | no | | `birth_date` | the full date, only when known; not in the future; requires `birth_year` equal to its year | no | | `revision`, `archived_at`, `created_at`, `updated_at` | server-maintained | n/a | Birth representation (D6b): each state has one meaning, and a year never becomes a date. | `birth_year` | `birth_date` | Meaning | |---|---|---| | null | null | birth unknown | | 2015 | null | born in 2015, day unknown | | 2015 | 2015-04-12 | born on 12 April 2015 | | null | 2015-04-12 | refused (CHECK) | | 2016 | 2015-04-12 | refused (CHECK) | The year of a full date is stored again in `birth_year`, so sorting and filtering by year read one column. The age shown is computed for display only: from the date when present, else « environ N ans » from the year. Deferred to a later step (D6), each one an additive nullable column when its step needs it: weight (it belongs to a visit), photo (it needs the `animal-files` rules and a worker job), detailed identification (microchip, UELN, tattoo), coat colour, neutering status, date of death, general notes. No complete veterinary record is created. ### 5.3 Client and animal relation (D7, D7a, approved) Table `animal_client_relations`: | Field | Rule | |---|---| | `animal_id`, `client_id` | same workspace, enforced by composite foreign keys | | `relation_role` | `owner`, `keeper` or `contact` | | `started_on` | date the relation began, when known; null when unknown; never filled by default | | `ended_at` | null while the relation is current; set to the recording time when the end is recorded (§13.2 N5); a technical time, never shown as the end date | | `ended_on` | date the relation ended, when known; only on an ended relation; not before `started_on` | | `voided_at` | null for a relation that existed; set when a relation entered by mistake is voided (§13.2 N4) | | `revision`, `created_at`, `updated_at` | server-maintained | Rules: - many-to-many, one role per relation: co-ownership is several current `owner` relations; owner and keeper are separate relations; the same client may hold two roles for one animal (an owner who keeps the horse at home); - an animal may have no owner because the owner is unknown or not entered yet; no SQL constraint and no RPC refuses it, the app never creates a placeholder client and shows « Propriétaire non renseigné »; whether a later business operation (bilan, invoice, sharing) needs an owner is decided by the step that introduces it; - an animal always belongs to one valid workspace, with or without relations; - client and animal of a relation are always in the same workspace: the composite foreign keys make any other row impossible, whatever writes it; - at most one current relation per (animal, client, role): a partial unique index on rows with no `ended_at`; successive relations of the same triple are allowed once the previous one has ended; - historical periods may overlap (dates are optional and approximate): no constraint on overlaps; - `animal_id`, `client_id` and `relation_role` never change after creation; dates can be corrected; - change of owner or keeper: add the relation of the new client, then end the previous one with its end date when known. Both rows stay; the former owner keeps their other animals and appears as « ancien propriétaire » on the animal's page. Between the two actions the animal has two current owners or none, two valid states, so no atomic transfer operation is needed; - ending is not deleting: an ended relation stays readable as a former relation, with its dates; - voiding is not ending (N4): a relation that never existed (wrong client, wrong animal, wrong role) is voided, never ended, so it never appears as a former owner or keeper and no end date is invented for a period that never was. The row keeps every value as entered, gains `voided_at`, and becomes frozen (no end, no date change, no un-voiding); the audit log records who voided it and when (§9.3). Voided relations are left out of every list, of the current-relation index, of the derived values (§9.3) and of the current cap; they count in the 200 total per animal, so a create-and-void loop cannot grow a table without bound. No V1-1 screen shows them; the row stays for the lifecycle contract (export, erasure) and incident work; - an end recorded on the wrong relation is voided like any wrong relation, then the right one is created. An end recorded with a wrong date is corrected by `update_animal_client_relation`. Re-opening a relation whose end never happened (clearing `ended_at`) is not offered in V1-1: the relation is voided and created again as current, with the same start date; the owner may ask for an explicit reopening at V1-1.4. Date rules (N5), all enforced by SQL CHECK except « not in the future », which needs the clock and is checked by the RPCs (tomorrow's UTC date, §9.3): | Constraint | SQL | |---|---| | dates optional, no default | `started_on date`, `ended_on date`, both nullable, no `default` | | plausible lower bound | `started_on >= '1900-01-01'`, `ended_on >= '1900-01-01'` | | an end date only on an ended relation | `ended_on is null or ended_at is not null` | | order when both are known | `started_on is null or ended_on is null or started_on <= ended_on` | | a voided relation keeps its state | `voided_at` set only by the void RPC; every other write RPC refuses a voided row (`invalid_input`) | | one current relation per triple | unique index on `(animal_id, client_id, relation_role) where ended_at is null and voided_at is null` | ### 5.4 Species (D5, approved) Reference data written by migration: the nine species of the contract (the existing `equine` row plus eight) and « Autre ». No other species is added. Slugs, names and order approved by the owner (§13.2 N1). Compatibility, checked on 2026-10-25: species are rows of `public.species` (text `id`, CHECK `^[a-z0-9][a-z0-9_-]{0,63}$`, which every slug below satisfies), never a SQL enum, so M3 adds rows and freezes no new enumeration; the Rust core carries `species_id` / `speciesId` as a free string (report identity, sheet document, snapshot, sheet index) with no species list, and the only existing id, `equine`, is kept unchanged: | `id` | `name_fr` | `name_en` | `sort_order` | |---|---|---|---| | `equine` (existing) | Cheval | Horse | 10 | | `asinine` | Âne | Donkey | 20 | | `canine` | Chien | Dog | 30 | | `feline` | Chat | Cat | 40 | | `bovine` | Bovin | Cattle | 50 | | `caprine` | Chèvre | Goat | 60 | | `ovine` | Mouton | Sheep | 70 | | `porcine` | Porc | Pig | 80 | | `leporine` | Lapin | Rabbit | 90 | | `other` | Autre | Other | 1000 | A recordable species does not mean an anatomical sheet exists. Only the equine content exists, as an unpublished draft, and nothing in V1-1 publishes it or links a species to a sheet. V1-1 shows no sheet information; V1-3 decides what a bilan does for an animal whose species has no sheet. The migration updates the table comment (« species of animals and of anatomical sheets »). A sheet for `other` makes no sense; the sheet RPCs are platform-admin only and V1-2 adds the refusal if it opens sheet creation. --- ## 6. Permissions ### 6.1 Matrix (C5, D16, approved) | Actor | Read | Create | Edit | Archive / restore | Delete | |---|---|---|---|---|---| | Member with role `holder` of the workspace (V1-1: the account of its personal workspace), active profile, confirmed email | yes | yes | yes, active records only | yes | no; a relation entered by mistake is voided, never deleted (§13.2 N4) | | Future cabinet roles | defined by the cabinet step | | | | | | The same account with an unconfirmed email | no | no | no | no | no | | Another signed-in account | no | no | no | no | no | | Platform owner, admin, moderator, support | no | no | no | no | no | | Deactivated profile | no | no | no | no | no | | Anonymous | no | no | no | no | no | | Service key (`service_role`: worker, operator scripts) | no table privilege (§13.2 N7) | no | no | no | no | C5: writes belong to the members who hold a write right in the workspace concerned; in V1-1 that is only the holder of the personal workspace. Future cabinet roles (business architecture §2.2: a secretary manages customers and animals) plug into the helpers of §9.1 and are built with the cabinet step. ### 6.2 Email, verification and platform roles (C6, approved) | Notion | Where it lives | Effect in V1-1 | |---|---|---| | Email confirmed | `auth.users.email_confirmed_at`, set by GoTrue | required for every V1-1 read and write. GoTrue gives no session before confirmation, and the workspace helpers check the column again in SQL. Limit: with `enable_confirmations` off, GoTrue stamps the column at sign-up, so the SQL check proves only that GoTrue considers the address confirmed; a config test pins `enable_confirmations = true`. A GoTrue ban (`banned_until`) does not end an issued token before it expires (`jwt_expiry` 3 600 s); `profiles.active = false` cuts access at the next request | | Professional status verified | plan Step 9, not built; no column exists | none; never granted by default; required later by public features that present an account as a verified practitioner (public profile, community publication, booking) | | Platform role | `profile_roles`, `platform_role` | none: it grants no access to clients, animals or bilans (D16) | Professional statuses (practitioner, student, professor) come from verification and are never platform roles (BDR-026). BDR-041 said Step 11 for verification; BDR-072 corrects it to Step 9. ### 6.3 Platform staff (D16, approved) Faunosteo staff have no ordinary right to read clients, animals or bilans. No policy or RPC has a platform-role branch, no admin screen lists or opens these records, and V1-1 builds no impersonation, support access or break-glass mechanism. No worker job reads these tables. Residual technical paths, named so nobody believes them closed: - the service key gets no table or RPC privilege on V1-1 (§13.2 N7), but it is also the GoTrue admin key: its holder can generate a magic link or set a password for any account and then read that workspace as its practitioner, leaving no trace in the application. The worker and the operator scripts hold it. The service key and the JWT secret are therefore full-access credentials, bounded only by their custody; the exceptional-access contract covers them, with two follow-ups in §14 (a narrower worker credential, monitoring of GoTrue admin actions in `auth.audit_log_entries`); - the table owner (`postgres`, not a superuser on Supabase, but holding `BYPASSRLS` on the Supabase image, checked on 17.11.0.004) and any database superuser read past RLS: maintenance, backup restore and incident response are exceptional privileged operations that the separate contract frames, justifies and audits before any publication. Both paths were exercised on the isolated stack of V1-1.1 (§8.1): the service key reads nothing through PostgREST (`permission denied`), yet it obtains a session for any account through GoTrue's admin link generation and then reads that account's workspace like its practitioner. Removing table privileges narrows the direct path only; the residual risk stays on the security track before production (RR-4), as the owner required (N7). The existing audit policy lets platform owners and admins read audit rows. The V1-1 rows carry the actor, the resource id, the action, the time and the workspace id. Read with the staff policy on profiles, that would reveal per-practitioner volumes, working hours and events such as « relation ended »: activity metadata, never names or contacts. N9 (§13.2): platform staff get no general view of the private records, so M2 removes the `client.*`, `animal.*` and `animal_client_relation.*` actions from `audit_logs_select_admin`. ### 6.4 Edits and finished bilans (C5, approved) V1-1 holds no bilan. The rule binds V1-3: a bilan keeps its own identity snapshot (already true of the report v3 format); editing an animal or a client never rewrites a finished bilan, retroactively or silently. Any refresh of a draft bilan from the animal record is an explicit user action. --- ## 7. Archive, deletion and data protection ### 7.1 Archive (D8, approved) Archiving is a functional state, read-only and reversible. - An archived record is hidden from default lists and search, stays readable from its page and under the « archivés » filter, and refuses every edit (`archived` failure) until it is restored. Restore is always possible. - Relations are kept. Archiving a client archives none of its animals; archiving an animal ends none of its relations. An archived client stays on its animals' pages, marked archived. - Relation writes: creating a relation needs both the animal and the client active; ending a relation, correcting its dates or removing it needs the animal active (the client may be archived, so the history of an active animal stays maintainable); the relations of an archived animal are frozen. - The confirmation before archiving a client lists the active animals for which it is the only current owner. - Archiving erases nothing. It is not the deletion or anonymisation of personal data, and no retention duration is attached to it. ### 7.2 Account deletion and the data lifecycle (D9, D9a, approved) V1-1 implements no account closure, deletion, anonymisation or export, and sets no retention duration. Temporary DEV protection (D9a): the records reference their workspace with `on delete restrict`, and the personal workspace follows its account. Deleting an account whose workspace holds at least one client or animal is therefore refused by the database; an account whose workspace holds nothing is deleted as today, its workspace with it. In DEV a test account with records is removed by deleting its records first from a privileged SQL session, or by `pnpm db:reset`. The protection is not a ban: the lifecycle contract (plan Step 26) will process the records first (export, erasure or anonymisation, retention where justified, reassignment inside a cabinet) and the deletion then passes. It forbids one thing permanently: records disappearing as a side effect of deleting an account. The future lifecycle distinguishes closing an account, deleting or anonymising data, legally justified retention, records of a workspace shared by a cabinet, and reassignment of responsibilities in a cabinet. What V1-1 does so that it stays possible: - records belong to a workspace, never to a user column, so a cabinet's records survive a member leaving and a personal workspace can be exported, anonymised or erased as one unit; - authorship is ids in the audit log only, so anonymising a profile rewrites no record; - a client's personal data sits in identified columns of one table; the folded keys and phone digits (§8) derive from the same row and follow any rewrite; no copy goes into audit rows, logs, error messages or URLs, so erasure or anonymisation is a bounded update. ### 7.3 Data protection (principles approved; legal framing before exposure) Nothing here is a legal policy. The owner frames the legal basis, the information texts, the retention durations and the rights procedures with counsel before any public exposure (§13.4). | Category | V1-1 data | Whom it concerns | Stored in | Read by | |---|---|---|---|---| | Practitioner's professional data | profile (existing): name, practice name, title, SIRET, email | the practitioner, as a user | `profiles`, `auth.users` | the practitioner; staff per the existing policies | | Clients' personal data | display name, email, phone, postal address | clients who are natural persons, and in practice structures too (a sole trader, an écurie named after a person, a named contact) | `clients` | members of the workspace only | | Animal information | name, species, sex, breed, birth | the animal, and through its link the client | `animals` | members of the workspace only | | Relations | owner, keeper or contact, dates | links a person to animals | `animal_client_relations` | members of the workspace only | | Technical traces | audit rows (actor, ids, actions), server logs | pseudonymised data, still personal data | `audit.audit_logs`, logs | platform owner and admin for audit rows (§6.3) | Design rule: the lifecycle contract treats every client row, organizations included, every relation and every audit row as personal data, whatever counsel later concludes about each category. Elements to prepare for the information of the persons concerned (GDPR articles 13 and 14), to be written with counsel (D10): identity of the controller, purposes, legal basis per purpose, recipients (Faunosteo as host), retention, rights and how to exercise them, and, under article 14, the source of the data when the practitioner records a person's details received from someone else (a keeper giving the owner's phone). Hypotheses to submit to counsel, not decisions: the practitioner or the cabinet is the controller of client and animal records and Faunosteo its processor (processor terms in the conditions of use); consent is not presumed to be the legal basis; the care relationship and the practitioner's legal obligations are the likely bases for identity and contact data; consent would cover optional purposes only (marketing, sharing with another practitioner), none of which V1-1 implements; data of a structure without a named person may fall outside personal data, and animal data may be personal data only through its link to a client. Rights in V1-1: rectification is the edit form. Access, export, erasure, restriction and portability come with the lifecycle contract. Until the legal framing exists, DEV holds fictitious data only: no real client is entered in V1-1 before the framing (recommendation, §14 risks). Minimisation: the field list of §5 is closed; no free-text notes in V1-1; the fields of D6 are deferred. No personal data in URLs (ids are uuids, the search term travels in a POST body), audit rows, application logs or error messages (§10.6). --- ## 8. SQL schema (M1, M2 and M3 written) Three forward-only migrations, one per data step, each with its pgTAP files, the regenerated `database.types.ts` and the census updates in the same branch (plan principle 2). Timestamps are set when written. M1 as built, with its differences from the sketch below: §8.1. | Migration | Step | Content | |---|---|---| | M1 `20261025100000_workspaces.sql` (written, V1-1.1) | V1-1.1 | enums `workspace_kind`, `workspace_member_role`; `workspaces`, `workspace_members`; helpers; trigger; backfill function called once; `private.log_event` redefined (same signature) to keep `x-correlation-id` only when it matches `^[A-Za-z0-9-]{1,64}$`, plus a NOT VALID CHECK on `audit_logs.correlation_id`; grants; RLS | | M2 `20261025110000_clients.sql` (written, V1-1.2) | V1-1.2 | extension `unaccent`; `private.fold_text`; enum `client_kind`; `clients`; write budget helper; client RPCs and searches; grants; RLS | | M3 `20261026100000_animals_relations.sql` (written, V1-1.4) | V1-1.4 | species rows; enums `animal_sex`, `animal_relation_role`; `animals`, `animal_client_relations`; animal and relation RPCs; grants; RLS | Why the correlation header in M1: `private.log_event` copies that request header verbatim today (foundation migration), the app never sends it, and PostgREST exposes every header, so a caller chooses the size of each append-only audit row. V1-1 raises the audited write rate to 1 000 an hour per workspace (§10), which turns that into a storage risk. The change keeps the function's signature and callers. Sketch of the tables. `(c)` abbreviates the text rule of §5: `c = btrim(c)` and no character of the explicit control ranges. Constraint names follow the existing `__` form. ```sql -- M1 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, personal_user_id uuid unique references public.profiles (id) on delete cascade, created_at timestamptz not null default now(), constraint workspaces_personal_user_iff_personal check ((kind = 'personal') = (personal_user_id is not null)) ); 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) ); create index workspace_members_user_idx on public.workspace_members (user_id); -- M2 create extension if not exists unaccent with schema extensions; -- lower + unaccent with an explicit dictionary, declared immutable so a -- generated column can use it. -- private.fold_text(p_value text) returns text create type public.client_kind as enum ('person', 'organization'); create table public.clients ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete restrict, kind public.client_kind not null, display_name text not null, -- 1..120, email text, -- <= 254, = lower(email), practical format phone text, -- '^\+[1-9][0-9]{6,14}$' address_line1 text, -- 1..120, address_line2 text, -- 1..120, postal_code text, -- 1..16, city text, -- 1..80, country text, -- '^[A-Z]{2}$' name_key text collate "C" generated always as (private.fold_text(display_name)) stored, search_key text generated always as (private.fold_text(concat_ws(' ', display_name, city, email))) stored, phone_digits text generated always as (ltrim(phone, '+')) stored, create_request_id uuid not null, -- idempotency key sent by the form revision integer not null default 1, archived_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint clients_workspace_id_unique unique (workspace_id, id), constraint clients_create_request_unique unique (workspace_id, create_request_id) ); create index clients_workspace_name_idx on public.clients (workspace_id, name_key, id); -- M3 create type public.animal_sex as enum ('male', 'female', 'unknown'); create table public.animals ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null references public.workspaces (id) on delete restrict, name text not null, -- 1..80, species_id text not null references public.species (id) on delete restrict, species_detail text, -- 1..80, sex public.animal_sex not null, -- no default breed text, -- 1..80, birth_year smallint, -- 1900..2100 (CHECK); <= current year (RPC) birth_date date, -- >= 1900-01-01 (CHECK); not in the future (RPC) name_key text collate "C" generated always as (private.fold_text(name)) stored, search_key text generated always as (private.fold_text(concat_ws(' ', name, breed, species_detail))) stored, create_request_id uuid not null, revision integer not null default 1, archived_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint animals_workspace_id_unique unique (workspace_id, id), constraint animals_create_request_unique unique (workspace_id, create_request_id), constraint animals_species_detail_iff_other check ((species_id = 'other') = (species_detail is not null)), constraint animals_birth_date_year check (birth_date is null or (birth_year is not null and birth_year = extract(year from birth_date)::integer)) ); create index animals_workspace_name_idx on public.animals (workspace_id, name_key, id); -- M3 (continued) create type public.animal_relation_role as enum ('owner', 'keeper', 'contact'); create table public.animal_client_relations ( id uuid primary key default gen_random_uuid(), workspace_id uuid not null, animal_id uuid not null, client_id uuid not null, relation_role public.animal_relation_role not null, started_on date, -- >= 1900-01-01 (CHECK); not in the future (RPC) ended_on date, -- idem ended_at timestamptz, voided_at timestamptz, -- set once by the void RPC (N4) create_request_id uuid not null, revision integer not null default 1, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint animal_client_relations_animal_fk foreign key (workspace_id, animal_id) references public.animals (workspace_id, id) on delete restrict, constraint animal_client_relations_client_fk foreign key (workspace_id, client_id) references public.clients (workspace_id, id) on delete restrict, constraint animal_client_relations_ended_on_needs_end check (ended_on is null or ended_at is not null), constraint animal_client_relations_dates_order check (started_on is null or ended_on is null or started_on <= ended_on), constraint animal_client_relations_started_on_range check (started_on >= '1900-01-01'), constraint animal_client_relations_ended_on_range check (ended_on >= '1900-01-01'), constraint animal_client_relations_create_request_unique unique (workspace_id, create_request_id) ); create unique index animal_client_relations_current_unique on public.animal_client_relations (animal_id, client_id, relation_role) where ended_at is null and voided_at is null; create index animal_client_relations_animal_idx on public.animal_client_relations (workspace_id, animal_id); create index animal_client_relations_client_idx on public.animal_client_relations (workspace_id, client_id); ``` Every table with `updated_at` gets the existing `private.set_updated_at()` trigger. `revision` changes only through the RPCs: every successful state-changing write (update, archive, restore, end, date correction) adds one; a call that changes nothing does not. `create_request_id` makes creation idempotent: the form generates one uuid per create attempt and resends the same one on retry; a replay returns the ids of the row already created instead of a duplicate (clients and animals can be archived, never deleted, so a duplicate would stay forever and block the account's deletion under D9a). The backfill of M1 is a private function the migration calls once (`private.backfill_personal_workspaces()`, idempotent), so pgTAP can prove it on data: the migrations run on an empty `profiles` table in both test runners. Indexes and why: | Index | Serves | |---|---| | `workspace_members_user_idx` | the membership lookup of every policy | | `clients_workspace_name_idx`, `animals_workspace_name_idx` | workspace filter, keyset pagination in name order, search within one workspace | | unique `(workspace_id, id)` on clients and animals | targets of the composite foreign keys | | `animal_client_relations_current_unique` | one current relation per (animal, client, role) | | unique `(workspace_id, create_request_id)` on the three tables | idempotent creation | | `animal_client_relations_animal_idx`, `_client_idx` | an animal's relations, a client's relations, foreign key checks | Search reads stored columns only: `search_key` (folded name, city and email of a client; folded name, breed and species detail of an animal) and `phone_digits`. Nothing is folded per row at query time. No trigram index in V1-1: a workspace holds at most 20 000 rows per table (§10) and the search scans one workspace through the name index. The measurement of step V1-1.2 decides: above 100 ms at p95 on 20 000 rows, a `pg_trgm` GIN index on `search_key` is added (PGlite ships `pg_trgm`). `name_key` is stored with `collate "C"`, so the order is the same on PGlite and on the real stack (rule of the pgTAP files) and, accents and case being folded, follows French alphabetical order closely enough for a list. If the dictionary of `unaccent` ever changed, stored keys would differ from new ones until their rows are rewritten: recorded, low risk. ### 8.1 M1 as built (V1-1.1, 2026-10-25) `supabase/migrations/20261025100000_workspaces.sql`, tested by `supabase/tests/012_workspaces.sql` (85 checks) and the census of `001_schema.sql`. Differences from the sketch and from the plan, each deliberate: | Point | Sketch or plan | As built | Why | |---|---|---|---| | Uniqueness of the personal workspace | inline `unique` | named constraint `workspaces_personal_user_unique` | the single write path targets it with `on conflict`, tests name it | | Write path | trigger and backfill | both call `private.ensure_personal_workspace(uuid)`: workspace and holder membership inserted by one statement, `on conflict (personal_user_id) do nothing`, returns whether it created | one place for atomicity and idempotence; a concurrent call waits for the first commit, then writes nothing | | Consistency workspace ↔ holder | not in the sketch | deferred constraint triggers `workspaces_personal_holder` and `workspace_members_personal_holder` on `private.check_personal_workspace()`: at commit, a personal workspace has exactly its account as `holder` | the owner asked for a coherence constraint between the workspace's account and its member; it holds for any writer, the table owner included; cabinets are not constrained | | Trigger name | not named | `profiles_create_personal_workspace` | | | `private.writable_workspace_ids()` | M1 | created in M1, unused until M2's write RPCs | the helper set of §9.1 ships and is tested once | | Access layer `packages/supabase/src/workspaces.ts` | planned in V1-1.1 | deferred to V1-1.2 | no consumer before the clients access layer; the brief authorised M1 and its tests | | `pnpm db:reset && pnpm db:test` | planned gate | not run: the owner kept the DEV stack untouched | replaced by an isolated real stack (below) | Not changed by M1: the privileges of existing tables. `service_role` keeps the Supabase default privileges it holds today on `profiles`, `profile_roles` and `assets` (only the new tables and `private.object_purge_queue` revoke it); narrowing them is outside V1-1.1 and needs the owner's decision (N7). Isolated real-stack verification. A throwaway stack built from the DEV stack's images (PostgreSQL 17.11.0.004, GoTrue v2.197.0, Storage v1.79.36, PostgREST v16.4, Mailpit v1.31.3) on its own Docker network, ports on the loopback only, no Docker volume, DEV containers untouched. Two runs: from zero (all migrations then the seeds, as `supabase db reset`), and the DEV path (the migrations before M1, the seeds and two accounts created through GoTrue, then M1, whose backfill created the five missing workspaces). In both: the twelve pgTAP files green (531 checks); real concurrent sessions (two creations of one account's workspace: the second waited for the first commit and wrote nothing; two racing backfills created 40 workspaces between them, no duplicate; commits of a workspace without its holder or with a foreign member refused at COMMIT; a rolled-back creation left nothing and the retry created one); ten parallel GoTrue sign-ups each with one workspace; 33 HTTP checks through GoTrue and PostgREST (sign-up without session, confirmation by the mailed link, reads limited to the own workspace, `count=exact` of one, refusals with `permission denied` for writes, for the anon key and for the service key, `private` functions unreachable, deactivation effective on the next request with the same token, the correlation header capped through a real request, an empty account deleted by the GoTrue admin API taking its workspace along, and the residual admin path of §6.3). The types were generated from that database: the diff with the committed file is exactly the M1 additions. ### 8.2 M2 as built (V1-1.2, 2026-10-25) `supabase/migrations/20261025110000_clients.sql`, tested by `supabase/tests/013_clients.sql` (130 checks), the census of `001_schema.sql`, the parity test `packages/supabase/tests/db/field-limits.test.ts` and the sandbox suite `packages/supabase/tests/sandbox/clients.sandbox.ts`. Differences from the sketch and the plan: | Point | Sketch or plan | As built | Why | |---|---|---|---| | Replay with other content | not covered | column `create_request_digest` (SHA-256 of the normalised creation content, not granted to any API role); a replay with the same id and another digest is refused `request_conflict` (§9.4), never applied | owner brief §11: a reused request id must not silently accept a divergent operation | | Text rule in CHECKs | written inline | `private.is_clean_text(value, max)`, immutable, EXECUTE for nobody, used by every text CHECK (M3 reuses it) | one literal for the control ranges | | Folding | `lower + unaccent` | `lower(unaccent('extensions.unaccent', value) collate "C")`: accents removed, then ASCII lower case only, identical on PGlite and the real image (checked); Cyrillic or Greek capitals keep their case | the database collations differ (C on PGlite, ICU on the image) | | `search_key` | `concat_ws` | `display_name || ' ' || coalesce(city, '') || ' ' || coalesce(email, '')` | a generated column needs an immutable expression; `concat_ws` is stable | | Write budget | `require_write_budget(p_actor)`, rolling windows | `private.consume_write_budget(p_workspace_id)` over `private.workspace_write_budgets` (one row per workspace), fixed UTC windows (§10) | owner decision N6 and brief §9: transactional counter per workspace, UTC windows | | `search_clients` output | with `current_animal_count` | without it | the relations table comes with M3, which recreates the function with the column (return type change) | | Access layer `clients.ts` | V1-1.2 | moved to V1-1.5, its first consumer, together with the personal workspace read | the brief's V1-1.2 scope lists no access layer; `field-limits.ts` (D-R7) is written now because the parity test needs it | | Real-stack e2e | `clients-animals.e2e.ts` on the DEV stack | the sandbox suite, on the isolated stack | DEV stays untouched (owner) | Search measurement (owner requirement, §8): 20 000 clients in one workspace, 21 POST calls through PostgREST mixing name, city, email and phone terms: median 9.8 ms, p95 21.4 ms, max 21.9 ms (isolated stack, this host). Under the 100 ms threshold, so no trigram index is added. Residual disclosure found on the real image: a refused direct table access (`permission denied for table clients`) carries a hint added by the Supabase image, « Grant the required privileges to the current role with: GRANT INSERT ON public.clients TO authenticated; ». It names the table, the role and the privilege, never a value, and the app never sends such a request. The RPC refusals carry no hint (checked on both engines). ### 8.3 M3 as built (V1-1.4, 2026-10-26) `supabase/migrations/20261026100000_animals_relations.sql`, tested by `supabase/tests/014_animals_relations.sql` (258 checks), the census of `001_schema.sql` and `013_clients.sql`, the parity test (animal text fields, year and date floors) and the sandbox suite `packages/supabase/tests/sandbox/animals.sandbox.ts` (18 tests). Differences from the sketch and from §9: | Point | Sketch or contract | As built | Why | |---|---|---|---| | Replay with other content | clients only (§8.2) | `create_request_digest` on animals and relations too, never granted to an API role; a divergent replay is `request_conflict` | the same rule for every create | | Initial relation of `create_animal` | "in the same transaction" | the relation reuses the animal's `create_request_id` (unique per table), so a replay answers the same `relation_id`; it counts as two audited writes in the budget (two audit rows) | idempotence of the two-row create; the budget counts audited writes (§10) | | Species filter of `search_animals` | parameter `species_id` | `species_filter` | PostgreSQL refuses a parameter named like the `species_id` output column | | Cursor of `list_client_animals` | `after_sort` | each row returns `sort_key`: `0` (current) or `1` (former) followed by the animal's `name_key`; keyset on `(sort_key, relation_id)` | one text cursor covers both groups | | Output of `list_client_animals` | "relation and animal columns" | `relation_id`, `relation_role`, `started_on`, `ended_on`, `is_current`, `relation_revision`, `animal_id`, `animal_name`, `species_id`, `species_detail`, `sex`, `breed`, `birth_year`, `animal_archived_at`, `sort_key` | what the client page needs to show and to end or void a relation | | `client_archive_preview` | `{count, animals}` | `{count, animals: [{id, name}]}`, the first 10 in name order | | | Replay of a relation create | archived, then revision, then rules | the replay is answered right after the locks, before `archived`: a retry of a committed create returns it even if the animal or the client was archived since | idempotence | | Dates and years | parsed in each body | `private.parse_record_date`, `private.parse_birth_year`, and the bound `private.utc_tomorrow(timestamptz)`, pure, so pgTAP pins « 1 January accepted on 31 December UTC » without the clock | one rule, testable | | Opening of the relation writes | per RPC | `private.lock_relation_for_write`: authorise, lock the animal then the relation, refuse `archived`, `conflict`, then a voided row (`invalid_input`) | the same order in the three RPCs | | `search_clients` | "recreated with the column" | dropped and created again with the same parameters, rules and privileges, plus `current_animal_count` | a return type change needs a drop | | Access layer `animals.ts`, `relations.ts` | V1-1.4 (TODO) | moved to the UI steps | first consumer, as `clients.ts` at V1-1.2 | Relation states. `ended_at` tells an ended relation from a current one, so a relation whose end is recorded without a known date is unambiguous; the contract needed no change (owner brief §7). | State | `ended_at` | `ended_on` | `voided_at` | Shown | Current index, cap of 20 | Totals of 200 and 100 000 | |---|---|---|---|---|---|---| | current | null | null | null | current | yes | yes | | ended, date known | set | the date | null | former, with its date | no | yes | | ended, date unknown | set | null | null | former, no end date | no | yes | | voided (entered by mistake, current or ended) | as entered | as entered | set | nowhere | no | yes | Caps: a current relation (`ended_at` and `voided_at` null) counts against 20 per animal; every row, ended and voided included, counts against 200 per animal and 100 000 per workspace, so a create-and-void loop is bounded. The 200 and 20 are counted under the animal row lock, the 100 000 and the 20 000 animals under the workspace row lock. Lock order as built, M2 included. Every write RPC takes the budget row last and the others in the order workspace, animal, relation, client; none takes two of them the other way round, so no wait cycle can form: | RPC | Locks, in order | |---|---| | `create_client` | workspace (no key update), budget | | `update_client`, `set_client_archived` | client (update), budget | | `create_animal` | workspace (no key update), client (share, with a relation), budget (once, twice with a relation) | | `update_animal`, `set_animal_archived` | animal (update), budget | | `create_animal_client_relation` | workspace (no key update), animal (update), client (share), budget | | `end_`, `update_`, `void_animal_client_relation` | animal (update), relation (update), budget | The relation RPCs first read the animal's (or the relation's) workspace among the caller's writable ones without a lock, so a caller outside the workspace takes none: with A's client, animal and relation rows held for 2 s, B's four refusals answered in 15 ms. Results on the isolated stack: 8 identical `create_animal` calls with a relation gave one animal, one relation and two counted writes; 10 identical relations, one row and nine `duplicate_relation`; end and void racing on one revision (6 rounds of 3 calls), exactly one applied each time; 6 links at 18 current relations, 2 passed; a relation create racing the archive of its client or of its animal (10 rounds each), created before or refused `archived`; at 990 writes, 30 mixed client, animal and relation writes, exactly 10 passed; 5 `create_animal` at 19 998 animals and 5 links at 99 998 relations, 2 each (49 ms for the five links); 96 mixed M2 and M3 writes in one workspace, no deadlock (109 ms). Reads over 20 000 animals, 20 000 relations and 1 000 clients (24 POST calls): median 7.9 ms, p95 11.8 ms. Migration states, each from an empty sandbox: from zero; a pre-M1 base (seeds, GoTrue accounts) then M1 to M3; the same base, M1 (backfill of five workspaces), then M2 and M3 (`pnpm db:sandbox up upgrade 20261025100000 20261025110000`); an M1 and M2 base then M3. In each: the 14 pgTAP files green (919 checks), the generated types identical to the committed file, the sandbox suites 46/46. Fourteen deliberate faults of M3 were each caught; two only after the tests were strengthened (a client whose only link was voided while current; B linking its own animal to a client of A, which made B wait on A's lock). Not built: reopening an ended relation (§5.3: void it and create it again). Residual: a privileged SQL writer (the table owner) can still change a relation's `animal_id`, `client_id` or `relation_role` inside one workspace, and move an animal without relations to another workspace (the evolution path of §4); the RPCs never do, and the composite keys refuse any row across workspaces whatever writes it. --- ## 9. Policies, helpers and RPCs ### 9.1 Helpers (schema `private`) | Helper | Returns | Rule | EXECUTE | |---|---|---|---| | `private.caller_workspace_ids()` | `setof uuid` | the single source of the access rule: workspaces where `auth.uid()` is a member, with an active profile and a confirmed email | `authenticated` (policies evaluate it) | | `private.writable_workspace_ids()` | `setof uuid` | `caller_workspace_ids()` restricted to a write role (V1-1: `holder`) | none (called inside RPCs) | | `private.consume_write_budget(p_workspace_id uuid)` | `void` | counts one write of the workspace in its UTC hour and day (upsert of its row in `private.workspace_write_budgets`, row lock), raises P0001 `rate_limited` past 1 000 / 5 000 (§10); called last by every write RPC (M2, §8.2) | none | | `private.is_clean_text(p_value text, p_max integer)` | `boolean` | the text rule of §5 for CHECK constraints (M2) | none | | `private.fold_text(p_value text)` | `text` | without accents (`unaccent` with an explicit dictionary), then ASCII lower case (collation "C"), declared immutable for the generated columns (§8.2) | `authenticated` (the invoker search folds the term) | | `private.ensure_personal_workspace(p_user_id uuid)` | `boolean` | the single write path: workspace and `holder` membership in one statement, `on conflict do nothing` on `personal_user_id`; true when it created (M1, §8.1) | none | | `private.create_personal_workspace()` | trigger | after insert on `profiles`: calls `ensure_personal_workspace(new.id)` | none | | `private.backfill_personal_workspaces()` | `integer` | same for every profile without a personal workspace; idempotent | none | | `private.check_personal_workspace()` | constraint trigger | deferred to commit: a personal workspace has exactly its account as `holder` (M1, §8.1) | none | | `private.utc_tomorrow(p_now timestamptz)` | `date` | the latest date a record may carry when written at `p_now`: tomorrow's UTC date (M3, §8.3) | none | | `private.parse_record_date(p_value text)`, `private.parse_birth_year(p_value text)` | `date`, `smallint` | `YYYY-MM-DD` or four digits, not after `utc_tomorrow(now())` (its year), else `invalid_input` without the value; null stays null (M3) | none | | `private.lock_relation_for_write(p_relation_id uuid, p_expected_revision integer)` | relation row | authorises and locks animal then relation; `not_found`, `archived`, `conflict`, voided `invalid_input` (M3) | none | All are `security definer` (except `fold_text`, `is_clean_text`, `utc_tomorrow` and the two parsers, which read no table), `set search_path = ''`, `revoke all ... from public, anon, authenticated, service_role` then the grants of the table, and listed in the census of `001_schema.sql`. The write RPCs do not call `private.require_actor()`: its two messages differ, and V1-1 refusals must be identical (§9.4). ### 9.2 Row-level security and grants | Table | SELECT policy (role `authenticated`) | Write policies | Column grants to `authenticated` | |---|---|---|---| | `workspaces` | `id in (select private.caller_workspace_ids())` | none | `id`, `kind`, `personal_user_id`, `created_at` | | `workspace_members` | `workspace_id in (select private.caller_workspace_ids())` | none | all columns | | `clients` | `workspace_id in (select private.caller_workspace_ids())` | none | all columns | | `animals` | same | none | all columns | | `animal_client_relations` | same | none | all columns | - RLS enabled on every table in the migration that creates it. - `revoke all ... from public, anon, authenticated, service_role`, then the column grants above. `anon` and `service_role` hold nothing (§13.2 N7). - No INSERT, UPDATE or DELETE grant or policy for any API role: every write is an RPC. - No policy names a platform role (D16); no `using (true)` (census test). - The `in (select ...)` form evaluates the helper once per query, not per row. - The app selects explicit column lists, never `select=*`, and never filters a text column in a GET URL (`ilike`, `eq` on a name, a keyset cursor on `name_key`): API URLs land in access logs. Text filters and name-ordered pages go through the POST RPCs below; GET reads filter by id only. ### 9.3 RPCs Write RPCs: `language plpgsql`, `security definer`, volatile, `#variable_conflict use_column`, parameters referenced as `.`. Each one, in this order: 1. authorises and locks in one statement: the target row or the workspace is selected `where id = ... and workspace_id in (select private.writable_workspace_ids())` with its lock, so a caller outside the workspace never takes a lock and never waits on one; no row found raises 42501 `not_found`; 2. checks, in this order, archived (`archived`), then revision (`conflict`), then the rules of the table below; a replayed create or a call that changes nothing returns here, uncounted; 3. calls `private.consume_write_budget` (as built in M2: last, after the checks, so its lock is the last one taken); 4. writes, inside a block that turns `check_violation`, `not_null_violation`, `string_data_right_truncation` and `invalid_text_representation` into `invalid_input`, `unique_violation` on the current-relation index into `duplicate_relation` and `foreign_key_violation` into `not_found`; 5. writes one audit row and returns ids and the new revision only. Locks: create RPCs of clients and animals lock the workspace row `for no key update` (cap, replay check and insert serialised per workspace, foreign key checks of other writes not blocked); every relation RPC locks the animal row `for update` then the client row `for share`, in that order; the archive RPCs lock their row `for update`. So caps, « both active » and « animal active » cannot be passed by a concurrent call. The write budget and the 100 000 relations per workspace count rows that writes on different animals or clients can add at the same time: they are counted under a lock that serialises those writes per workspace (owner, N6: never a bare count followed by an unprotected insert). As built in M2: the budget row of the workspace (`private.workspace_write_budgets`) is locked by the upsert of `private.consume_write_budget`, which every write RPC calls last, after its entity locks and checks; no lock is taken after it, so no cycle can form. The lock order of every write RPC is therefore: workspace row (creates), animal row, client row, budget row. M3 keeps it: a relation create locks the workspace row first (its 100 000 cap), then the animal, then the client, then the budget. The budget lock is per workspace: measured on the isolated stack, a budget row held for 2 s delayed the create of its workspace by 1.75 s while nine creates in nine other workspaces finished in 8 ms; 60 mixed writes in one workspace raised no deadlock. Parameters: dates, years and enum values arrive as `text` and are parsed inside the body, so a malformed value (« 2015-02-30 ») is answered `invalid_input` without being echoed in the error or the server log. Create RPCs give optional fields a `null` default, after the required ones; update RPCs have no defaults, so an omitted field is an error and never a silent erasure. An update that changes nothing writes nothing and returns the current revision. | RPC | Parameters | Returns | Specific rules | Audit action | |---|---|---|---|---| | `create_client` | `workspace_id`, `create_request_id`, `kind`, `display_name`, then optional `email`, `phone`, `address_line1`, `address_line2`, `postal_code`, `city`, `country` | `{id, revision}` | replay of a known `create_request_id` returns the existing row; cap 20 000 clients | `client.created` | | `update_client` | `client_id`, `expected_revision`, the nine fields | `{revision}` | | `client.updated` | | `set_client_archived` | `client_id`, `archived` | `{revision}` | same state: no write | `client.archived` / `client.restored` | | `create_animal` | `workspace_id`, `create_request_id`, `name`, `species_id`, `sex`, then optional `species_detail`, `breed`, `birth_year`, `birth_date`, and optional initial relation `client_id`, `relation_role`, `started_on` | `{id, revision, relation_id}` | replay returns the existing row; cap 20 000 animals; species active; birth not in the future; the optional relation follows the rules of `create_animal_client_relation` in the same transaction | `animal.created` (+ `animal_client_relation.created`) | | `update_animal` | `animal_id`, `expected_revision`, the seven fields | `{revision}` | species active or unchanged; birth not in the future | `animal.updated` | | `set_animal_archived` | `animal_id`, `archived` | `{revision}` | same state: no write | `animal.archived` / `animal.restored` | | `create_animal_client_relation` | `animal_id`, `client_id`, `create_request_id`, `relation_role`, optional `started_on` | `{id, revision}` | both in one writable workspace (else 42501); both active; no current duplicate; at most 20 current and 200 total relations per animal and 100 000 per workspace (`limit_reached`); `started_on` not in the future | `animal_client_relation.created` | | `end_animal_client_relation` | `relation_id`, `expected_revision`, optional `ended_on` | `{revision}` | animal active; not voided; current only (`already_ended`); sets `ended_at = now()`; `ended_on` not in the future and not before `started_on` | `animal_client_relation.ended` | | `update_animal_client_relation` | `relation_id`, `expected_revision`, `started_on`, `ended_on` | `{revision}` | animal active; not voided; `ended_on` only on an ended relation; same date rules | `animal_client_relation.updated` | | `void_animal_client_relation` | `relation_id`, `expected_revision` | `{revision}` | N4: a relation entered by mistake, current or ended; animal active; not already voided; sets `voided_at = now()`, changes no other column; explicit confirmation in the UI; no deletion | `animal_client_relation.voided` | The combined journeys use the atomic `create_animal` with its optional relation. « New client with a first animal » is two calls (`create_client`, then `create_animal` with the relation); each carries its own `create_request_id`, the form keeps the ids it has received, and a retry after a failure resumes at the missing call and never creates a second client. "Not in the future" allows up to the UTC date of tomorrow (and its year for `birth_year`), so a date that is today in any time zone is accepted; the form checks against the device's local date. Audit rows (N9) hold the technical ids (actor, resource, workspace in `metadata` as `{workspace_id}`), the action and its time: no relation role, no changed-field list, no name, contact, date or search term. A row exists only for a committed write, so its presence is the result; refusals write nothing, which keeps a caller from growing the log by failing on purpose. Read RPCs: `language plpgsql`, `security invoker` (RLS applies), stable, `set search_path = ''`, `#variable_conflict use_column`, parameters referenced as `.`, EXECUTE for `authenticated`. The app calls them with POST, so terms and cursors stay out of URLs and access logs. | RPC | Parameters | Returns (one row per record) | Rules | |---|---|---|---| | `search_clients` | `workspace_id`, `query`, `archived`, `page_size`, `after_name_key`, `after_id` | `id`, `kind`, `display_name`, `city`, `phone`, `email`, `archived_at`, `name_key`; `current_animal_count` added by M3 (§8.2) | see below; also serves the client picker of the relation forms | | `search_animals` | `workspace_id`, `query`, `species_filter` (§8.3), `archived`, `page_size`, `after_name_key`, `after_id` | `id`, `name`, `species_id`, `species_detail`, `sex`, `breed`, `birth_year`, `archived_at`, `name_key`, `current_owners`, `current_owner_count` | see below; optional species filter | | `list_client_animals` | `client_id`, `page_size`, `after_sort`, `after_id` | relation and animal columns | a client's relations with their animals, current first then former, each group by animal `name_key`, then `id`; keyset on that order; 50 by default, 100 at most | | `client_archive_preview` | `client_id` | `{count, animals}` | active animals for which this client holds the only current `owner` relation: their count and the first 10 by name | Search rules: `query` trimmed, 0 to 100 characters, empty lists everything. The term is folded, then `\`, `%` and `_` are escaped (backslash first), and it must appear inside `search_key`. Phone: when the term holds at least four digits and only digits, spaces, dots, hyphens and `+`, its digits are also looked for inside `phone_digits`, after dropping one leading `0` (so « 06 12 », « 0612345678 » and « +33 6 12 » all find `+33612345678`). `archived = false` lists active records, `true` archived ones. `page_size` 1 to 100, 25 by default; keyset on `(name_key, id)`. Derived values: | Value | Exact rule | |---|---| | `current_animal_count` | `count(distinct animal_id)` over the client's relations with `ended_at is null` and `voided_at is null`, any role, animals not archived | | `current_owners` | the animal's relations with `relation_role = 'owner'`, `ended_at is null` and `voided_at is null`, archived clients included and flagged, ordered by client `name_key` then `id`, first three as `{client_id, display_name, archived}` | | `current_owner_count` | the count of those relations | | « only current owner » (archive preview) | the animal is active, this client has a current `owner` relation, and no other client, archived or not, has one | The animal page reads its relations with one select by animal id (at most 200 by the cap, ids in the URL only). Dashboard counts (D12) use `count=exact` with `head=true` filtered by workspace id and `archived_at`. The app finds its personal workspace with `workspaces?personal_user_id=eq.`. Nested selects are bounded by the per-animal cap and by the role's `statement_timeout`, pinned by a test. ### 9.4 Failures Closed kinds, mapped by the access layer (`packages/supabase`), phrased by i18n in FR and EN, like the auth and asset failures: | From the server | Kind | When | |---|---|---| | 42501 `not_found` | `not_found` | inactive account, unconfirmed email, not a member, unknown id, id of another workspace: one SQLSTATE and one message for all of them (`anon` gets PostgREST's « permission denied », having no EXECUTE) | | P0001 `archived` | `archived` | write on an archived record (checked before the revision) | | P0001 `conflict` | `conflict` | `expected_revision` differs: the form shows the saved values beside the typed ones and keeps the typed ones | | P0001 `limit_reached` | `limit_reached` | a cap of §10 | | P0001 `rate_limited` | `rate_limited` | the write budget of §10 | | P0001 `duplicate_relation` | `duplicate_relation` | same current (animal, client, role), including the loser of a race | | P0001 `already_ended` | `already_ended` | ending an ended relation | | P0001 `invalid_input` | `invalid_input` | an RPC rule or a constraint or parse error caught inside the RPC | | P0001 `request_conflict` | `request_conflict` | a create replayed with a known `create_request_id` but other content (§8.2); the first record is kept unchanged | | anything else | `unavailable` | network, server | pgTAP compares the whole error (SQLSTATE, message, detail, hint) of a foreign id and of a random uuid. An `invalid_input` from a CHECK means the TypeScript rules drifted from the SQL: the parity test of §12.2 exists to catch it first. ### 9.5 SECURITY DEFINER functions: owner, path, privileges, trust - Owner: the role that runs the migrations and owns the tables (`postgres` on the CLI stack, the same on any Supabase project; a table owner, not a superuser on Supabase), like every existing function; no `alter function ... owner to`. RLS is not forced on these tables, so the owner reads past the policies: the function body is the only authorisation and checks the workspace role before reading, locking or writing any record. - `set search_path = ''`; every object is schema-qualified; no dynamic SQL, no `execute`, no `format()`. - EXECUTE: `revoke all ... from public, anon, authenticated, service_role`, then granted to `authenticated` for the RPCs and for the two helpers that policies or the invoker searches evaluate, to nobody for the internal helpers. `service_role` gets no EXECUTE. - Trust boundary: the function trusts `auth.uid()` (a JWT verified by PostgREST) and its own checks, nothing else from the caller; every parameter is untrusted. It returns ids, revisions and closed codes, never a record of another workspace, and has no platform-role branch. - pgTAP pins `prosecdef`, the `search_path` setting and the ACL of each one. --- ## 10. Technical limits (D15, principle approved, thresholds proposed) These caps protect the service. They are not commercial quotas: any quota comes from the entitlement step (plan Step 11), and changing a cap here is a migration. | Limit | Proposed value | Justification | Enforced by | |---|---|---|---| | Text fields | display name 120, email 254, phone E.164, address lines 120, postal code 16, city 80, country 2; animal name 80, breed 80, species detail 80; years 1900 to tomorrow's UTC year | long structure names fit in 120; 254 is the SMTP path limit; the rest bounds a record to about 3 KB | SQL CHECK; same constants in the forms (§12.2); years and dates also in the RPC | | Request size | one record per call (plus one optional relation in `create_animal`), about 3 KB of text at most by the field caps | no bulk operation in V1-1 | SQL; reverse proxy body cap of 64 KiB on `/rest/v1` in production (deploy notes, RR-5) | | Items per operation | one record and its audit row per write RPC; two in `create_animal` with a relation | imports are out of scope | RPC design | | Pagination | search pages of 25 by default, 100 at most; a client's animals 50 by default, 100 at most; an animal's relations at most 200 by the cap; PostgREST `max_rows = 1000` limits the top level of a direct select, and `statement_timeout` bounds nested ones | a phone list needs 25; 100 bounds the work of one call | RPC; `config.toml`; role setting | | Search term | 0 to 100 characters after trimming | a name or a phone fits; longer is noise | RPC | | Records per workspace | 20 000 clients, 20 000 animals, 100 000 relations | a full-time practitioner sees roughly 1 000 to 2 000 visits a year, so 20 000 animals is more than a decade of distinct animals for one person | RPC, under the workspace or animal row lock | | Relations per animal | 20 current, 200 in total | co-owners, keeper and contacts fit in 20; 200 covers decades of sales; the animal page reads them in one select | RPC | | Write rate | 1 000 audited writes per workspace per UTC hour and 5 000 per UTC day (owner, N6), fixed windows: the hour from hh:00:00 to hh:59:59.999 UTC, the day from 00:00:00 UTC; the first write of a new window restarts its count at 1; the 1 000th write of the hour passes, the 1 001st is refused `rate_limited`. Across an hour boundary up to 2 000 writes can land in 2 minutes; the daily 5 000 still holds. Counted per workspace under the budget row lock (§9.3); a refused write, a replayed create and a call that changes nothing are not counted (the count is rolled back with the refusal) | typing a whole client base by hand stays under 200 an hour; bounds the growth of the append-only audit log and the damage of a stolen session | RPC (`private.consume_write_budget`) | | Audit row size | `correlation_id` kept only when it matches `^[A-Za-z0-9-]{1,64}$`; metadata limited to the workspace id (N9) | the caller no longer chooses the size of an append-only row | M1 (`private.log_event`, CHECK) | | Concurrent writes | optimistic `revision` on every update; locks of §9.3 (workspace row for creates, animal then client row for relations, the row itself for archive); idempotent creates through `create_request_id` | no silent overwrite between two tabs or devices, no cap overshoot, no duplicate on retry | SQL and RPC | | Enumeration | random uuid ids; one 42501 `not_found` for an unknown and a forbidden id in every RPC, with no lock taken outside the caller's workspaces; reads filtered by RLS return empty, never an error that says "exists"; searches limited to one workspace of the caller; closed codes, never values, in errors | | RLS, RPC | | Login, sign-up, email sending | existing GoTrue limits (`[auth.rate_limit]` in `config.toml`); per-IP limits on `/rest/v1` at the production reverse proxy | | GoTrue; infrastructure (deploy notes) | Growth bound of one misused confirmed account, from the figures above: at most 20 000 clients, 20 000 animals and 100 000 relations (about 150 MB with their indexes at the field caps), and at most 5 000 audit rows a day of a few hundred bytes each (about 0.5 GB a year). The audit log is append-only and never cleaned today: its retention belongs to the lifecycle contract. GoTrue's sign-up limits bound the number of accounts. §10.6 Logs and errors: inside the write RPCs, constraint and parse errors become `invalid_input` (§9.3), so PostgreSQL never logs a failing row, whose detail would contain names, and no submitted value is echoed. PostgreSQL does not log statement parameters on error by default (`log_parameter_max_length_on_error = 0`). Residual: a value PostgREST cannot cast before the function runs (a non-uuid id) is echoed in the response; ids are not personal data. The production log settings go to the deploy notes with RR-5. The app logs no record content. --- ## 11. Screens and journeys ### 11.1 Routes English, like the existing `/app/dashboard` and `/app/account`; identifiers only, never a name or a search term. | Route | Content | |---|---| | `/app/clients` | list (name, kind, city, phone, number of animals), search, « archivés » filter, « Nouveau client » | | `/app/clients/new` | client form; optional « Ajouter un premier animal » section in the same flow | | `/app/clients/:clientId` | identity and contact (phone as a `tel:` link, email as `mailto:`), animals with their role, current then former, edit, archive or restore, add an animal | | `/app/clients/:clientId/edit` | client form | | `/app/animals` | list (name, species, breed, sex, current owners or « Propriétaire non renseigné »), search, species filter, « archivés » filter, « Nouvel animal » | | `/app/animals/new` | animal form with optional client selection or inline client creation; prefilled when opened from a client | | `/app/animals/:animalId` | identity, owners, keepers and contacts, current then former, with dates; add, end, correct the dates of, or void (N4) a relation; edit, archive or restore | | `/app/animals/:animalId/edit` | animal form | An unknown id and a forbidden id show the same « introuvable » screen. ### 11.2 Journeys 1. New client calling about a horse: Clients → Nouveau client → type the name, add the first animal in the same flow → save → client page with the animal linked as owner. 2. Find an animal from a phone in a stable: Animaux → type part of the name → open the animal → owners and keeper with their phone as a `tel:` link. 3. Horse sold: animal page → add the new owner → end the former owner's relation with the sale date if known → the former owner keeps their other animals and appears as « ancien propriétaire ». 4. Owner unknown: a rider brings a horse without the owner's details → animal created with no client → its page and the list show « Propriétaire non renseigné », no placeholder exists → the owner is added later. 5. Client archived: the confirmation lists the animals for which it is the only current owner → archived → hidden from lists, visible under the « archivés » filter, still shown on its animals' pages, restorable. 6. A stable as keeper: create the structure « Écurie du Lac », link it as keeper of a horse whose owner is a person. 7. Species « Autre »: create a ferret as « Autre » with the detail « Furet ». Journeys run on desktop and phone, in FR and EN, and journey 1 also with the keyboard only. ### 11.3 Forms - Client: « Personne / Structure » control at the top (« Personne » visibly selected), then the name field whose label follows the kind, then contact and address. Phone: international entry (`+32…`) accepted as typed; a French national entry (`06 12 34 56 78`) is converted to `+33…` only when the country is empty or France, and the converted value is shown in the field before saving; with another country a national entry is refused with « Indiquez le numéro avec son indicatif (+32…) ». Country: empty select, never prefilled. - Animal: name, species (with the detail field shown and required for « Autre »), sex as three explicit choices with none preselected (N8), breed, birth as a year field plus an optional full date: entering the date fills and locks the year, clearing it unlocks the year. - Relation: client picker with search, role, start date (optional); ending asks for an optional end date. - Every form: labels on every field, required fields marked in the label, errors under the field linked with `aria-describedby` and announced, focus on the first error on submit; unsaved changes protected by a `beforeunload` guard and by a confirmation on the form's own Cancel and back actions (the app's declarative router has no navigation blocker, and a data-router migration is out of scope); `conflict` shows the saved values beside the typed ones and keeps the typed ones; after a partial failure of a two-call creation the form keeps what was saved and retries the missing call with the same request ids (§9.3). ### 11.4 Layout and navigation Desktop: the sidebar entries Clients and Animaux of the validated mockup become active, in the mockup's order; list and detail use the existing `Card`, `FormField`, `Button` and `Badge` components; no new visual direction. Mobile: single column, list rows as cards with 44 px targets, forms as full pages (no modal form), the save action reachable with the keyboard open, « Afficher plus » at the end of a list (keyset pages), the drawer as navigation (D11). Dashboard tiles: D12. The screens follow `docs/design/` and are built with the project's design workflow. ### 11.5 Accessibility, applied in V1-1 Every field labelled; errors announced; focus to the page heading on navigation and to the first error on submit; visible focus; nothing reachable only by pointer; confirmation dialogs with focus containment and focus restored to the opener, no new dependency (D13, final behaviour in §11.6); status changes (saved, archived) announced through a live region; an axe check per screen in the browser tests. ### 11.6 V1-1.3 as built (2026-10-25) D13, final behaviour. `components/ui/ModalDialog.tsx` is the one modal primitive, on the platform's `` and `showModal()` (no dependency: none of the project's dependencies holds an accessibility primitive, and the browser's modal dialog is the proven one). `Dialog` and the mobile drawer are built on it. | Need | Behaviour | |---|---| | Name and description | the `` is named by its visible title (`aria-labelledby`); `Dialog`'s optional `description` is wired with `aria-describedby`; the four confirmations pass their message there (photo removal, replace session, held session, sheet disclaimer); long bodies (forms, drawings) carry no description; the drawer is named « Navigation principale » | | Opening | focus on the first control of the dialog (the close button for `Dialog`, the brand link for the drawer), set explicitly: Chromium would otherwise focus a scrollable panel when the content is long | | Containment | the rest of the page is inert (top layer): no focus, no click, no screen-reader access; the page does not scroll behind (`html:has(dialog:modal)`); Tab past the last control reaches the browser's own interface for one step, then the first control again, never the page (the platform's choice, kept: no hand-written trap) | | Escape and close requests | a request to the consumer through `onClose`, never a close by itself: the dialog leaves only when the consumer stops rendering it. The Escape keydown is cancelled, so Chromium's rule that closes a dialog on a second Escape after a cancelled one never applies; a close the platform forces anyway is reported to the consumer | | Closing controls | the close button (« Fermer » / « Close »), the backdrop and the consumer's own buttons, all through the same request | | Focus return | after the commit, to the opener when it can still take focus; otherwise to the first control of the opener's nearest container still on the page; nothing when the dialog opened without a focused opener (the disclaimer at editor load) | | Pending actions | a confirmation whose action is in flight refuses to close (Escape, Cancel, backdrop) and closes when the action settles: the photo removal now does so, so focus moves past the vanished button instead of falling on the page | Closing a dialog is never a save or a confirmed cancel: the consumer decides what a request means. The V1-1 forms are full pages (§11.4), so the unsaved changes confirmation of §11.3 belongs to the page, not to a dialog. D14, as built. `@playwright/test` 1.64.1 and `@axe-core/playwright` 4.14.0 (axe-core 4.14.0), dev dependencies of `apps/app`, pinned; five packages in the lockfile (Apache-2.0 for Playwright, MPL-2.0 for axe, used unmodified). `pnpm e2e:ui` (no backend) and `pnpm e2e:shell` (the isolated stack), Chromium desktop and 375 × 667; details, guarantees and limits in `docs/development/BROWSER_TESTS.md`. Deviation from §12.5: the suites run against the isolated stack, not against DEV, which holds neither M1 nor M2 and stays untouched (owner choice, 2026-10-25). D11, the mobile navigation conflict, documented for the decision before V1-1.6 (owner, 2026-10-25: no tab bar in V1-1.3, current navigation kept, the validated mockups unchanged). | Source | Mobile navigation | |---|---| | Implemented | top bar (menu button, brand, language switcher, avatar link to Mon compte); the drawer (modal since V1-1.3) with the sidebar entries Tableau de bord, Mon compte, Outil de bilan, then the user badge and « Se déconnecter »; no bottom tab bar | | Validated mockups (`docs/design/README.md` items 5 to 7) | the same drawer, plus a bottom tab bar on the dashboard and the profile: Accueil, Bilans, Agenda, Profil; the README places the bar « with the first list screens » | | D11 recommendation (§13.3) | drawer only in V1-1; the mockup's tab bar arrives with Bilans (V1-3), when its entries lead somewhere | | Option for the Clients and Animaux routes | Consequence | |---|---| | Drawer only (D11) | Clients and Animaux join the drawer and the sidebar; two taps from any page; no destination that leads nowhere; the README line moves to V1-3 once decided | | The mockup's bar now | Bilans and Agenda lead nowhere until V1-3 and later (hidden or disabled tabs); Clients and Animaux stay in the drawer anyway; a fixed bottom bar competes with the save action that §11.4 keeps reachable with the keyboard open | | A bar adapted to V1-1 (Accueil, Clients, Animaux, Profil) | one tap to the V1-1 screens; departs from the validated mockup, so it needs a new validated design file first; the bar changes again when Bilans and Agenda arrive | ### 11.7 V1-1.5 as built (2026-10-26) Owner choices of 2026-10-26, asked before the screens: U1 « Afficher plus » (keyset pages of 25, no numbered page), U2 one « Afficher les archivés » toggle, the client page shows its animals read-only (no link, no « ajouter » until the animal screens), the « Ajouter un premier animal » section of `/app/clients/new` moves to V1-1.6 with the animal screens. | Part | As built | |---|---| | Access layer | `packages/supabase/src/workspaces.ts` (`fetchPersonalWorkspaceId`), `clients.ts` (search with one probe row for « Afficher plus », read by id with an explicit column list and no request for an id that is not a uuid, create with a request id, update sending every field, archive, client animals, archive preview), `species.ts` (labels), `records.ts`: the closed kinds of §9.4 plus `session_expired` (HTTP 401, PGRST30x) and `network` (no answer); every RPC answer checked by Zod. Proven by unit tests and by `tests/sandbox/clients-access.sandbox.ts`, the same functions through supabase-js against the sandbox's real GoTrue and PostgREST | | Personal workspace | read by `state/authStore.ts` with the account: `loading`, `ready`, `inactive` (profile deactivated), `unconfirmed` (no confirmed email), `missing`, `error`; never created in the browser. A change of account drops profile, roles, avatar and workspace in the same update, and an answer that arrives for the previous account is ignored. `app/guards/RequireWorkspace.tsx` renders the client routes only when ready and remounts them per workspace | | Routes | `/app/clients`, `/app/clients/new`, `/app/clients/:clientId`, `/app/clients/:clientId/edit` (`app/routes/app/clients/`); an unknown, foreign or malformed id shows the same « Client introuvable » inside the shell | | List | search sent after 300 ms without typing (POST, the term never in a URL), the archived toggle, rows with kind, city, phone and `current_animal_count`, « Afficher plus », loading, empty, no-result, error and retry states, the row count announced | | Form | `clients/validation.ts` on `field-limits.ts` (D-R7: code points, control ranges, email pattern, ISO country list); the phone entry rule of §11.3, shown converted when the field is left; « Personne » checked, the name label follows the kind, required and optional marked in the labels, the first error focused; one request id per creation intent, sent again on a retry; on `conflict` the stored values appear under the fields that differ, the typed ones stay, the next save applies them knowingly; submit guarded against double clicks; the save bar stays reachable at the bottom on a phone | | Page | identity, kind and archived badges, phone as `tel:`, email as `mailto:`, address and country name, the animals read-only (current then former, role, dates when known, species name); archive through a confirmation listing the active animals for which the client is the only current owner (it refuses to close while the request runs), restore by one button, outcomes announced | | Leave guard | `useLeaveGuard`: `beforeunload` while the form holds changes, and `AppLayout` asks before following any in-app link (sidebar, drawer, top bar, the form's own « Annuler »); the browser's back and forward buttons are not intercepted (declarative router, §11.3) | | Navigation | « Clients » in the sidebar and the drawer after « Tableau de bord » (the validated mockup's order); no tab bar (D11 unchanged) | | Focus | each client page focuses its heading when it opens, also when opened from the drawer (after the drawer closed and returned focus) | Deviations and limits: the contract's `beforeunload` plus Cancel guard is extended to every in-app link; the search term and the archived toggle are not kept when returning from a client page (no storage of terms); phones show in E.164 as stored; the dashboard tiles (D12) are not built. The shell's top bar already carried an `h1` with the page title on large screens before V1-1.5; the client pages add their own `h1` in `main`, as the existing pages do. Tests: supabase vitest 92 (access layer 33), sandbox suites 54 (access layer 8); app vitest 509 (client screens 22, form schema 20, store 16); Chromium `e2e:shell` 42 (client journeys 36: create, search, edit, archive, archived filter, restore, pages, keyboard only, two accounts, concurrent edit, retry after a lost answer, network failures, leave guard, change of language; FR and EN, desktop and 375 px, axe on every state with no exception of RR-7 inherited). Report check: the shell suite's HTML report kept the typed passwords of its throwaway accounts; it was removed (`BROWSER_TESTS.md` §3). Gates also found: a re-read of the same account (`USER_UPDATED`) set the workspace back to loading and unmounted an open form (fixed: a ready workspace stays on screen until the answer); the « introuvable » screen did not focus its heading (fixed). ### 11.8 V1-1.6 as built (2026-10-26) Owner decisions of 2026-10-26: D11 drawer only for V1-1 (the mockup's tab bar is not reproduced, stays the direction for a later UX step, the mockup is unchanged); the first animal of a client is created from the client page (« Ajouter un premier animal ») with that client pre-associated, in one `create_animal` call, so the client form does not change; no relation role is preselected; linking a client or an animal is a panel inside the page, ending, correcting dates and voiding are confirmations; U3 as recommended (a full date fills and locks the year). The dashboard tiles (D12) are outside the brief's scope and not built. | Part | As built | |---|---| | Access layer | `packages/supabase/src/animals.ts`: `searchAnimals` (POST, 25 + one probe row, `species_filter`), `fetchAnimalRecord` (explicit columns, no request for a non-uuid id), `createAnimalRecord` (the optional first relation in the same call, `{id, revision, relationId}`), `updateAnimalRecord` (the seven fields, null included), `setAnimalArchived`. `relations.ts`: `fetchAnimalRelations` (one select by animal id, the client embedded through `animal_client_relations_client_fk`, `voided_at is null`, at most 200), `createAnimalClientRelation`, `endAnimalClientRelation`, `updateAnimalClientRelation`, `voidAnimalClientRelation`. `records.ts`: kinds `duplicate_relation`, `already_ended`; every answer checked by Zod | | Routes | `/app/animals`, `/app/animals/new`, `/app/animals/:animalId`, `/app/animals/:animalId/edit`, under the same `RequireWorkspace` as the client routes (one gate, remount per workspace); an unknown, foreign or malformed id shows « Animal introuvable » | | Species | read once per workspace, when a page needs them (`SpeciesProvider` inside `RequireWorkspace`), dropped with the workspace on a change of account | | List | search after 300 ms (name, breed, species detail; POST), species filter over the active rows, « Afficher les archivés », « Afficher plus »; rows with species (and detail), breed, sex, the current owners (three names and the rest counted) or « Propriétaire non renseigné » | | Form | identity: name; species from the active rows; detail shown and required for « Autre », dropped for any other species; sex as three choices, none checked; breed. Birth: a year, or a full date that fills and locks the year (clearing it leaves the year editable); not after the device's date, not before 1900. On a creation, the optional client: a search among the active clients, then the role (required, none preselected) and the start date. One request id per form, sent again on a retry; `conflict` shows the stored values; leave guard | | First animal | « Ajouter un (premier) animal » on an active client's page opens the form with the client id in the navigation state, never in the URL; the form reads the client again and pre-associates it only when it is active in this workspace, else says so; the client can be removed before saving; the save returns to the client page | | Animal page | identity (species, sex, breed, birth with its precision: a date and the age, a year and « environ », or « Inconnue »; status), then « Clients liés »: current relations by role then name, the history by end, each with the client's page link, an archived badge, the phone as `tel:` and the dates when known; « Propriétaire non renseigné » without a current owner. On an active animal: « Ajouter un client » (panel), « Terminer » (optional end date, not before the start), « Corriger les dates », « Erreur de saisie » (voiding, explained as for a relation that never existed, final). An archived animal shows no action. Voided relations are never read | | Confirmations | `Dialog` on `ModalDialog`; while the write and the reload that shows it run, the dialog refuses to close, so focus returns once the row has moved (to the opener, or the first control of its section when the opener left); `conflict` or `not_found` reloads the relations and says the relation changed elsewhere; any other failure stays in the dialog with the typed date | | Client page | the animals once per animal and group (current, former) with every role the client holds and the dates, each a link to the animal; « Ajouter un premier animal » / « Ajouter un animal » and « Associer un animal existant » (panel: search among the active animals, role, start date); neither on an archived client | | Navigation | « Animaux » after « Clients » in the sidebar and the drawer (the mockup's order); no tab bar | | Shared components | `Card` now passes `aria-labelledby`: it dropped it silently, so the section cards of V1-1.5 had no accessible name (found by the browser suite); `Button` takes `ref` (a React 19 prop); `ChoiceRadios` (the client kind's look) for sex and role | | Technology boundary | no new business rule outside SQL: caps, duplicates, archived states, dates, revisions and idempotence are the M3 RPCs'; the TypeScript schemas are the D-R7 form checks on the constants proven equal to the CHECKs; age, grouping and order are display formatting. Nothing was added to `crates/`: no rule here is shared with a service or worth a wasm call | Deviations and limits: a write on a voided relation is `invalid_input` on the server, but a page that still shows the relation holds an older revision and gets `conflict` first (lock order archived, revision, voided), so the page reloads and the row is gone, with no separate « annulée » message; the confirmations leave without their exit animation; the picker lists active records only (an archived client or animal cannot be linked, §7.1); the browser's own date control shows the browser's date format; the limits of V1-1.5 stay (search not kept on return, E.164 display, back and forward not guarded). Tests: supabase vitest 127 (animal and relation access 35); sandbox suites 61 (`animals-access.sandbox.ts` 7: the same functions through supabase-js against the sandbox's GoTrue and PostgREST); app vitest 566 (animal screens 35, form schema and display rules 22); Chromium `e2e:shell` 82 (animal journeys 40: an « Autre » without owner, search, species filter, edit, archive and restore; pages of 25; the first animal from a client page and an existing animal linked; co-owners, a keeper structure, a refused duplicate, a voided mistake, the sale ended with its date; two accounts and a change of session; keyboard only; a concurrent edit; a retry after a lost answer; network failures and an archived client refused; a change of language; FR and EN, desktop and 375 px, axe on every state). Counter-checks: 12 deliberate faults (request id per attempt, voided rows read, a detail kept for any species, a date that does not fill the year, a dialog closing on failure, a preselected role, an archived origin client, no grouping per animal, the species filter dropped, a default sex, an end before the start, a conflict without reload) caught by the unit suites; `Card` dropping its name and a request id per attempt caught by the browser journeys. Gates: security audit PASS (no HIGH; LOW: an operator can still ask Playwright for a trace on the command line); independent verifier CONFORME 12/12 at the third round. It found the client journey failing after its end on a late reload (fixed for every shell suite) and a pgTAP check of V1-1.4 reading a workspace uuid as a date (« …2012-… »), which would have stopped `db:test` in the DEV procedure (fixed in the test, no migration). --- ## 12. Tests and Definition of Done ### 12.1 SQL (pgTAP, on PGlite and on the real stack) Files: `012_workspaces.sql`, `013_clients.sql`, `014_animals_relations.sql` (258 checks); updates to `001_schema.sql` (tables, RLS, privileges, helper census) and to `packages/supabase/tests/db/schema-types.test.ts`; the PGlite harness loads `unaccent`. Actors: A and B, two confirmed active accounts with their workspaces WA and WB; C, an account with an unconfirmed email; D, an account deactivated after creating records; S, a platform owner and admin; `anon`; `service_role`. | Owner's case | Proof | |---|---| | Two accounts, two workspaces | A and B each get exactly one personal workspace with a `holder` membership (trigger); the backfill function, called on a profile inserted with the trigger disabled inside the test transaction, creates one and is idempotent on a second call; a second personal workspace for A is refused | | Read allowed | A reads its workspace, membership, clients, animals and relations | | Read forbidden | B, C, D and S read zero rows of WA in every table, through `search_clients`, `search_animals`, `list_client_animals` and `client_archive_preview` with WA's ids, and through a `count` query; `anon` and `service_role` get `permission denied` | | Write allowed | A runs every RPC on WA records | | Write forbidden | B, C, D and S calling every RPC on WA ids get the same whole error (SQLSTATE, message, detail, hint) as with a random uuid, and take no lock on WA rows; `anon` cannot execute any RPC | | Reference between workspaces refused | B linking its animal to A's client gets 42501; a direct insert as `postgres` with mismatched workspaces fails on the composite foreign key | | Archive and restore | an archived client or animal refuses updates (`archived`), keeps its relations, leaves search by default and appears with the archived filter; restore re-enables edits; archiving a client changes none of its animals | | Historical relations | ending keeps the row with `ended_at`; a second current identical relation is refused; a successive one after the end is accepted; `ended_on` refused on a current relation and before `started_on`; ending an ended relation gives `already_ended`; relations of an archived animal are frozen | Also: a client with only a name and a kind accepted; the five birth states of §5.2 (bounds of the fields with a TypeScript twin are proven by §12.2); `species_detail` with and without `other`; an inactive species refused; dates in the future refused, and 1 January accepted on 31 December UTC; a malformed date answered `invalid_input` without its value; stale revision refused, and `archived` reported before `conflict`; a replayed `create_request_id` returns the first row; caps reached with rows inserted by `generate_series`; the write budget (hour and day) reached with audit rows inserted as `postgres`; one audit row per write with no name, contact or date in it; an oversized or malformed `x-correlation-id` stored as null; search folding accents and case, escaping `\`, `%` and `_`, matching « 06 12 », « 0612345678 » and « +33 6 12 », the species filter excluding a row, keyset pages without gaps or duplicates; derived values of §9.3 (two roles on one animal counted once, archived owner shown); two concurrent identical relations giving one row and one `duplicate_relation`; D9a (an account with records cannot be deleted, an empty one is, with its workspace); each definer function pinned (owner context, search path, ACL); a config test pinning `enable_confirmations = true`. ### 12.2 Constraint parity (D-R7) `packages/supabase/src/field-limits.ts` holds the V1-1 bounds and patterns (lengths, years, email and phone patterns, control ranges), read by the app's Zod schemas. `packages/supabase/tests/db/field-limits.test.ts` proves them by behaviour on PGlite: for each constant it inserts a value at the bound and one step past it and expects acceptance then refusal, so no SQL text is parsed. Counting rule: `char_length` counts code points, so the TypeScript check counts `[...value].length`; Zod's `.max()` counts UTF-16 units and is not used alone on these fields. The existing auth and account forms stay with the separate D-R7 chore. ### 12.3 Real stack (integration) `packages/supabase/tests/e2e/clients-animals.e2e.ts`, beside `avatar-flow.e2e.ts`, run by a root script on the DEV origin: A and B created through the admin API; A creates a client, an animal and a relation; B lists nothing, reads nothing by id, and every RPC answers 42501; the anon key reads nothing; the service key's select on `clients` is refused; an unconfirmed user cannot obtain a session; A deactivated by SQL loses access; deleting A through the GoTrue admin API while A holds records fails and leaves user, profile and records intact. Seeding (20 000 clients for the measurement) and teardown go through the privileged SQL helper the avatar e2e already uses (`docker exec ... psql`): `afterAll` deletes the run's records, then its users. The search time on 20 000 clients is a recorded measurement, not a pass/fail assertion. ### 12.4 Application Unit and component tests: form schemas per rule with i18n keys and the code point count; phone conversion rule; birth input states; list and search states (empty, results, error, more pages); archive and restore flow with the dialog focus trap; conflict message; archived read-only state; « Propriétaire non renseigné »; not-found screen for unknown and forbidden ids; route guard; i18n key parity. ### 12.5 Real browsers (Playwright and axe, D14) A suite under `apps/app/e2e`, run against the isolated stack of `pnpm db:sandbox` and a loopback dev server of its own (V1-1.3 deviation: DEV holds neither M1 nor M2 and stays untouched, §11.6). In V1-1: Chromium, desktop and a 375 × 667 phone viewport (Firefox, WebKit and 412 × 915 join at V1-5: WebKit's system libraries need `sudo`, a user action, and desktop WebKit is not iOS Safari, which the owner's real phone covers); journeys 1 to 7 of §11.2; the creation journey with the keyboard only; two browser contexts where B opens A's client URL and gets « introuvable »; every request the page sends (`page.on('request')`) checked to hold no fixture name or search term in its URL; an axe scan on each screen with zero serious or critical violation. Then the owner's manual check on a real phone and a computer over the LAN. ### 12.6 Definition of Done All tests above green; `pnpm check:all`; `pnpm db:reset && pnpm db:test`; `pnpm db:types:check`; fresh verifier and security gates; documents updated (this contract's "as built" notes, `CLAUDE.md` folder map and architecture, `docs/design/README.md` owners table, `SUPABASE.md`); report format, PDF, sheets and local tool unchanged; nothing deployed; no merge into `develop` without the owner's go. --- ## 13. Decisions ### 13.1 Approved by the owner on 2026-10-25 (BDR-073) | Id | Decision | |---|---| | D1 | Interface term « Clients » (EN "Clients"); a client is a natural person or a professional or associative structure; Propriétaire, Détenteur and Contact are roles of its relation to an animal, distinct from its identity; consistent FR and EN terms; no platform role reused as the owner of an animal | | D2 / C4 | `workspaces` and `workspace_members`; one personal workspace per practitioner in V1-1; cabinet workspaces possible later without destructive transfer; no global client directory; no data visible between practitioners; no sharing, invitations or cabinet permissions in V1-1 | | D3 | Two client kinds: natural person, structure | | D4 | Required: a display name for a person, a name (dénomination) for a structure; every other detail optional at creation; no country imposed silently; no email or phone imposed; billing, booking and regulatory needs belong to their steps | | D4a | Optional separate first and last names allowed for persons; the display name stays the required reference; the contract defines the rule between them (§5.1) | | D5 | Exactly the nine species of the contract plus « Autre » with a required detail; no invented species; a recordable species does not mean a sheet exists; the equine draft is never published automatically | | D6 | Required: name, species, sex with an explicit `unknown`; optional: breed, full birth date when known, birth year alone when only the year is known; weight, photo, detailed identification, coat colour, neutering status, date of death and further notes deferred; no complete veterinary record | | D6a | The fields beyond the owner's list (neutering, identification, colour, notes, date of death) are deferred, not built in V1-1 | | D6b | A year is never turned into a date; the representation keeps the precision given, with coherence constraints and tests (§5.2) | | D7 | Many-to-many relation with an explicit role (owner, keeper, contact); co-ownership allowed; an animal may have no identified owner and always belongs to a valid workspace; no fictitious client to satisfy a constraint; never a relation across workspaces | | D7a | Relation history with a start date when known and a nullable end (`ended_at`); no incoherent current duplicates, successive relations allowed; a change of owner or keeper never deletes history silently | | D8 | Functional archive, read-only, reversible; historical relations kept; archiving a client does not archive its animals; archiving an animal does not delete its relations; archive is not erasure; no retention duration | | D9 / D9a | A temporary DEV protection may block an account deletion that would leave incoherent data, never a permanent ban; the future lifecycle distinguishes closure, deletion or anonymisation, justified retention, shared records and reassignment in cabinets; V1-1 implements none of it and makes none of it impossible; no legal retention invented | | C5 | V1-1 writes only by the authorised practitioner in their personal workspace; data isolated between workspaces; model ready for cabinet permissions; current-data edits never change finished bilans silently | | C6 | Email confirmed, professional status verified and platform role are three distinct notions; a confirmed email gives private use of the workspace; verification required later for public features; no verified status by default; verification stays plan Step 9 | | D16 | No ordinary staff access to clients, animals or private bilans; no general admin screen on them; exceptional privileged access framed and audited by a separate policy before publication; no impersonation or exceptional support access in V1-1 | | D15 | Principle of technical caps protecting the service, distinct from commercial quotas; thresholds proposed in §10 for validation before the RPCs | | Naming | Explicit nomenclature (§3); no generic `owner_id` | ### 13.2 Technical choices, decided by the owner on 2026-10-25 (BDR-074) The owner validated N1 to N9 with the precisions of the last column; they prevail over the recommendation where they differ (N4, N6, N9). | Id | Question | Recommendation (preparation) | Owner decision, 2026-10-25 | |---|---|---|---| | N1 | Species slugs, names and order (§5.4) | Latin adjectives like the existing `equine` (`asinine`, `canine`, `feline`, `bovine`, `caprine`, `ovine`, `porcine`, `leporine`) plus `other`; names Âne, Chien, Chat, Bovin, Chèvre, Mouton, Porc, Lapin, Autre | approved: exactly the nine species of the contract plus `other` with a required detail, no invented species, no historical anatomical id renamed; compatibility with the Rust model and Supabase checked before freezing anything (§5.4: rows, not an enum) | | N2 | First and last names for persons (D4a) | not in V1-1 | approved: no separate names in V1-1; `display_name` is the required reference and the single source of truth | | N3 | Client notes | defer, like animal notes (minimisation) | approved: deferred; no free-text field without an identified business need | | N4 | Correcting a relation entered by mistake | an explicit, audited deletion | changed: a traceable logical voiding (`voided_at`, `void_animal_client_relation`), distinct from the normal end of a relation; a voided relation stops appearing as current or former without its history being falsified; `ended_on` never represents a period that never existed; minimised audit row (§5.3, §9.3); nothing built before V1-1.4 | | N5 | Relation dates | optional `started_on` and `ended_on`, plus `ended_at` recording time | approved: both dates optional, never invented, ordered when both are known; an end date is not a deletion; SQL constraints written in §5.3 before V1-1.4 | | N6 | Thresholds of §10 | as proposed, write budget per actor | approved as technical caps (not commercial quotas), with the write budget per workspace: 1 000 an hour, 5 000 a day; caps enforced consistently under concurrency, never a bare count then an unprotected insert; counters and locks documented when the RPCs are written; no quota system in V1-1.1 | | N7 | Service key on the V1-1 tables | no table or RPC privilege for `service_role` | approved as minimisation, without claiming it removes every administrative path: effective grants examined, direct privileges removed, EXECUTE limited, definer owners checked, access tested for anon, authenticated and service_role; global Supabase privileges and existing tables unchanged without a specific decision; the GoTrue admin residual risk stays on the pre-production security track (§6.3) | | N8 | Sex in the form | three explicit choices, none preselected | approved: nothing preselected, « Inconnu » chosen explicitly, a missing answer never turned into a value | | N9 | Audit rows read by platform owners and admins | exclude the V1-1 actions from `audit_logs_select_admin` (M2) | approved: no general staff view of private client and animal data; audit rows limited to technical id, operation, time, result and bounded validated correlation ids; no name, contact, observation or business data; append-only kept; caller-controlled growth bounded (correlation cap in M1, §8.1) | ### 13.3 Interface decisions still open Not ratified; recommendation and impact given. | Id | Decision | Recommendation | Impact | |---|---|---|---| | D10 | Client information notice: text, and whether the app records that it was given | decide with the legal texts; nothing shown or recorded in V1-1 | none in V1-1; recording it later adds a column; required before public exposure | | D11 | Mobile navigation | decided by the owner on 2026-10-26: the drawer only for V1-1, Clients and Animaux in it; the mockup's tab bar is not reproduced and stays the direction for a later UX step (§11.8, `docs/design/README.md`) | UI | | D12 | Dashboard tiles | open: outside the V1-1.6 brief, not built; recommendation unchanged (the two real tiles from live counts when the owner asks; bilans and appointments when their tables exist) | UI | | D13 | Shared `Dialog` focus containment and restore | approved 2026-10-25, built in V1-1.3 on the native modal dialog (§11.6) | shared component | | D14 | Real-browser test tooling | approved 2026-10-25, built in V1-1.3: Playwright 1.64.1 and axe 4.14.0, Chromium desktop and 375 px, against the isolated stack (§11.6) | manifest and lockfile; Chromium build 1248, no `sudo` | | D17 | Bridge to the local tool (« start a bilan for this animal », prefill only) | no: V1-3 links bilans to animals properly; a prefill now would be a second path to delete later | none | | U1 | Long lists | « Afficher plus » (keyset pages of 25), no numbered pages | UI | | U2 | Archived records | one « archivés » toggle on each list, not a separate tab | UI | | U3 | Birth input | decided 2026-10-26 as recommended: year field plus optional full date that fills and locks the year (§11.8) | UI | ### 13.4 Never assumed, framed before any public exposure The legal basis of each processing (consent is not presumed to be required), the information given to clients, the retention durations, the deletion and anonymisation rules, the legal qualification of each party. None of them blocks a DEV implementation with fictitious data; each blocks any public exposure. --- ## 14. Implementation steps and risks Each step is a vertical, testable unit on `feature/v1-1-clients-animals`, detailed in `.claude/tasks/TODO.md` (objective, dependencies, files, migration, tests, acceptance, security checks, stop conditions). The owner validates after V1-1.1 and after the first UI step; nothing merges into `develop` without the owner's go. | Step | Content | |---|---| | V1-1.1 | Workspaces: M1 (with the correlation header cap), helpers, trigger, backfill, RLS, pgTAP `012`. Written and tested on an isolated stack (§8.1), validated 2026-10-25; not applied to DEV | | V1-1.2 | Clients data: M2, RPCs, searches, D15 guards (budget per workspace, its lock), N9 audit policy, field limits and parity test, sandbox suite. Written and tested on the isolated stack (§8.2), validated 2026-10-25; not applied to DEV | | V1-1.3 | UI foundations: Playwright Chromium and axe (D14), `Dialog` containment and restore (D13). Built (§11.6), validated 2026-10-26 | | V1-1.4 | Animals and relations data: M3 with the species rows, RPCs, history rules, D9a proof. Written and tested on the isolated stack (§8.3), validated 2026-10-26; not applied to DEV | | V1-1.5 | Clients UI: routes, forms, list and search, archive, navigation entry. Built and tested on the isolated stack (§11.7), validated 2026-10-26 | | V1-1.6 | Animals and relations UI, navigation (D11: drawer only), documents, closure inventory (§15). Built and tested on the isolated stack (§11.8), validated 2026-10-26; D12 not in its brief; the owner's check over the LAN needs M1 to M3 on DEV | Recommended first step: V1-1.1. It is SQL only, has no visible choice, and every later step depends on its helpers. Risks and dependencies: - the service key and the JWT secret give full access through GoTrue admin (§6.3); follow-ups outside V1-1, on the release track (RR-4): a narrower worker credential (a dedicated Postgres role behind a custom-role JWT, which GoTrue admin refuses) and monitoring of `auth.audit_log_entries`; - `unaccent` must load on PGlite (the contrib ships with 0.3.16; the harness needs it) and on the CLI stack; otherwise the fold falls back to `translate()` over a committed accent table, a decision for the owner; - the D9a guard makes GoTrue's admin delete fail (« Database error deleting user ») for an account with records: expected, documented, tested; - the audit log stays append-only with no retention: its growth is bounded (§10), its retention belongs to the lifecycle contract; - DEV holds fictitious data only until the legal framing (§7.3); - `docs/design/README.md` places the mobile tab bar « with the first list screens », which D11 contradicts until the owner decides; - the RPC surface grows by fourteen functions (RR-4 review); N4 replaces the delete RPC by `void_animal_client_relation`, so the count is unchanged; - the D-R7 chore for the existing forms reuses the V1-1 constants module; - no CI runs on the forge: every gate is local; - two old R5.6 rehearsal worktrees in an earlier session's scratchpad hold uncommitted edits; they are not V1-1 work and are left to the owner. --- ## 15. Closure inventory (V1-1.6, 2026-10-26) Owner brief §21. Status words: **done** (built, tested on the isolated stack), **debt** (non-blocking, documented, kept in the product follow-up), **deferred** (left out on purpose, with who decided), **blocking** (before a merge into `develop`), **DEV** (to verify once M1 to M3 are applied, SUPABASE.md §3.3). Nothing below is verified on DEV: no V1-1 migration is applied there. The read-only preflight of DEV (2026-10-26) found M1 to M3 pending and nothing else, and none of their objects present (SUPABASE.md §3.3). | Area | Item | Status | Proof or reason | |---|---|---|---| | Workspaces | creation at sign-up, one per account | done | trigger and backfill (§8.1); pgTAP 012; `workspaces.sandbox.ts` (sign-up, parallel GoTrue sign-ups, races, rollback) | | Workspaces | read, isolation, account change in the app | done | RLS helpers; `authStore` states and reset; `RequireWorkspace` remount; two-account and change-of-session journeys | | Workspaces | catch-up of existing accounts (backfill) | done; DEV | rehearsed in three migration states on the sandbox; the DEV run is the §3.3 count « 0 profiles without exactly one workspace » | | Clients | create, search, read, edit, archive, restore | done | §11.7; `clients.spec.ts` 36 runs | | Animals | create (with or without a client), search, species filter, read, edit, archive, restore | done | §11.8; `animals.spec.ts` | | Animals | « Autre » with its detail; birth year or full date (D6b, U3) | done | form schema tests; the « Autre » journey | | Relations | link from both pages, several roles, co-ownership, a keeper structure | done | `animals.spec.ts` co-owner journey; sandbox duplicate race | | Relations | history, normal end with or without a date, date correction | done | end and dates dialogs; the sale journey; client page « Ancien propriétaire » | | Relations | voiding a mistake (N4), never deleted, shown nowhere | done | void dialog; the row checked voided in SQL; absent from both pages and from the counts | | Relations | no link across workspaces | done | composite keys; pgTAP 014; `animals.sandbox.ts` (B linking to A's client: `not_found`, no lock) | | Quality | SQL | done | pgTAP 919 on the real image and on PGlite; types identical | | Quality | integration (real GoTrue and PostgREST) | done | sandbox suites 61, in place of the DEV-origin file of §12.3 (`clients-animals.e2e.ts` not written: DEV holds none of M1 to M3, and the sandbox suites cover its cases: two accounts, refusals per role, anon and service keys, unconfirmed and deactivated accounts, D9a) | | Quality | browsers, accessibility, FR/EN, desktop and 375 px | done | `e2e:shell` 82, axe on every state; Chromium only until V1-5 (§12.5) | | Quality | `pnpm db:reset && pnpm db:test` of §12.6 | replaced | `db:reset` is forbidden on DEV (owner); the same proof runs on a fresh sandbox (`db:sandbox up fresh`, `test-db`) | | Promised, not built | « Ajouter un premier animal » section inside `/app/clients/new` (§11.1, §11.3 two-call creation) | deferred | owner decision 2026-10-26: the first animal is created from the client page in one atomic call | | Promised, not built | inline client creation from `/app/animals/new` (§11.1) | deferred | the V1-1.6 brief asks for a choice among existing clients only | | Promised, not built | dashboard tiles (D12) | deferred | outside the V1-1.6 brief; D12 stays open | | Promised, not built | mobile tab bar of the mockup | deferred | owner decision D11, 2026-10-26: drawer only in V1-1 | | Debt | search and archived toggle not kept on return; phones in E.164; back and forward not guarded | debt | accepted by the owner for V1-1 (brief V1-1.6 §1) | | Debt | no separate message for a relation voided elsewhere (seen as `conflict`) | debt | §11.8 | | Debt | pre-existing axe exceptions of RR-7 (language switcher, top bar account link, dashboard `dl`) | debt | scoped to their element and route; no V1-1 page relies on them for its own elements | | Debt | GoTrue admin with the service key, `service_role` on the species table, no read limit of its own, partial Unicode folding | debt | release track RR-3, RR-4 (§14) | | DEV | M1 to M3 applied, workspaces and ten active species counted, pgTAP, types | DEV | SUPABASE.md §3.3, owner action; read-only preflight conform 2026-10-26, backup, restore check and migration not run | | DEV | browser acceptance on a phone and a computer over the LAN | DEV | SUPABASE.md §3.3 « Browser acceptance on DEV »; not done | | Merge | owner's go after the V1-1.6 checkpoint and the DEV acceptance | blocking | §14; nothing merges into `develop` without it |