# R3 Sheet List and Administration Home Implementation Plan (V1-2.6), rev 2 > **For agentic workers:** REQUIRED SUB-SKILL: Use subagent-driven-development to implement this plan task-by-task. > Steps use checkbox (`- [ ]`) syntax for tracking. **Status:** VALIDATED by the owner, 2026-11-16 (brief « Validation du plan R3 révision 2 »): D1 to D4, D6 and D7 yes as recommended, D5 no (out of R3, kept as a later navigation improvement), every « Recorded unless the owner objects » item accepted. Development and checks on the sandbox authorised within this plan. `pnpm db:trial migrate` is NOT authorised: Task 2 Step 6 stops on an operational checkpoint (exact operations, backup, downtime, checks after restart) and waits for the owner's go. DEV unchanged. Owner precisions, binding: (P1) the tests cover the real computation of `last_modified_at` on PostgreSQL, drafts and working copies included; (P2) the migration is reviewed and frozen before its first application; (P3) the application never shows an old or wrong date as a reliable last modification; (P4) exact test results kept, the known BLK-015 failures included. Rev 2 = rev 1 + three fresh challenges of 2026-11-16 (correctness CONCERNS(3), robustness CONCERNS(5), simplicity CONCERNS(2)); their findings and what became of them are in « Challenge round » at the end. Rev 2b = the owner's answers: Task 1 gains a dated working copy (P1), Task 2 a whole-stack recomputation check on the sandbox (P1). **Goal:** the official sheet list gets a name search, a sort by true last modification (default) or by name, a visible date and a result count; the Administration home card lists the five most recently modified sheets. **Architecture:** one SQL read change adds `last_modified_at` to `private.official_sheet_admin_summary` (design accepted, `V1-2-6_R3_SQL_CHECKPOINT.md`). The typed client requires the key. Search, sort and date formatting are pure TypeScript display functions over the loaded list (same class as the R2 Références tab), read by the list page and the home card. No table, policy, privilege, Rust core or worker change. **Tech Stack:** PostgreSQL 17 (pgTAP, PGlite), `@faunosteo/supabase` (Zod 3.25), React 19, Tailwind 4, vitest, Playwright + axe. **Spec:** `docs/development/V1-2-6_U1_CORRECTIONS.md` §R3 (D7, A7 to A9), `docs/development/V1-2-6_R3_SQL_CHECKPOINT.md`, boards `docs/design/v1-2-6-r0/28-liste-fiches-desktop.png` and `29-liste-fiches-telephone-accueil.png`, labels `docs/design/v1-2-6-r0/REVUE.md:82-84`, rules REVUE.md points 4, 6, 7, 8. Board 30 belongs to R4, not to this plan. Plan form (deviation from writing-plans « full code », stated, as the R2 plan did): the SQL, the pgTAP assertions and the pure functions are given in full; the React tasks give files, behaviour, test cases and verification, and the executor writes the JSX under TDD against boards 28 and 29. ## Audit findings (2026-11-16, read-only, checked again by the correctness challenge at 98178de) Worktree `/home/bchanot/Documents/faunosteo-v1-2`, branch `feature/v1-2-anatomical-sheets` at 98178de (R2 closed). | # | Finding | Evidence | Consequence | |---|---|---|---| | F1 | The typed client rejects an answer whose shape differs: `parseSheetAnswer` throws `unavailable` on a schema miss, and `OfficialSheetAdminSchema` (sheet page, editor draft route, save and conflict reads) extends the summary schema. The reverse is harmless: the schema is a non-strict `z.object`, so today's code drops an extra key. | `packages/supabase/src/sheets.ts:150-154`, `:381-420`, `:454` | Once the client requires `last_modified_at`, the admin screens and the editor answer « indisponible » on a stack without the migration. Stacks are migrated before the app code, never after. | | F2 | The shell suite runs on the sandbox, which has no forward migration command; the checkpoint recommends applying to the trial stack only. | `scripts/db-sandbox.sh:237-262` (`up fresh`, `up upgrade` only), checkpoint §4 | With path a alone `admin.spec` and every `editor-*` spec fail at load (F1) and nothing proves the date on a real PostgreSQL. Path b (a `migrate` command) is needed too; `migrate_up` already exists (`:188-190`). | | F3 | Checkpoint §4 says « PGlite and `pnpm db:test` need no existing stack ». `pnpm db:test` runs `supabase test db` on the DEV stack, and V1-2 is « sandbox only, not on DEV ». | `package.json:34`, `CLAUDE.md:60` | The real-PostgreSQL pgTAP run is `pnpm db:sandbox test-db` after the sandbox migrate, never `pnpm db:test`. DEV stays untouched. | | F4 | pgTAP files run in one transaction, where `now()` is frozen; `set_updated_at` stamps `now()`. | `supabase/tests/022_sheet_admin_reads.sql:6`, `:229`, `20261008100000_foundation.sql:30-38` | « A draft save moves `last_modified_at` and not `updated_at` » cannot be seen with rows dated `now()`. The new fixture rows are dated in the past at insert (the trigger stamps updates only). | | F5 | RR-13 as recorded covers the rights list only (`list_catalog_grants`, `GrantList`). Two R3 documents lean on it to page the sheet list. | `.claude/tasks/TODO.md` RR-13 line, BDR-106; `V1-2-6_U1_CORRECTIONS.md:616`, checkpoint :32 | The `shortcut:` of the browser-side search and sort points to a track whose written scope does not include the sheet list (decision D4). | | F6 | `admin.spec.ts` « admin screens » visits `/app/admin/access` before `/app/admin/sheets` and is red on the accumulated sandbox (BLK-015). It also holds the only axe scan of `/app/admin`. | `apps/app/e2e/shell/admin.spec.ts:733-750`, `.claude/memory/blockers.md` BLK-015 | R3's browser proof gets its own spec, which never opens the rights screen, scans a searched (short) list and the home page. | | F7 | The date moves, or fails to, in ways the checkpoint does not list: (i) `delete_sheet_version` removes the draft row, so the date can go back (no screen calls it today); (ii) deleting an account that created a draft sets `created_by` to null, an update that stamps the version; (iii) review rounds never move it (by design); (iv) a future data migration that rewrites drafts re-dates every such sheet to the deploy time; (v) at the autumn clock change two saves an hour apart read the same « 02:30 » and sort differently. | `20261028110000_sheet_review_workflow.sql:1304-1332`, `20261008110000_anatomical_sheets.sql:111`, `:143-145`, `20261008100000_foundation.sql:30-38` | Known limits, recorded in the checkpoint document. Only (iii) is reachable from the application today. | | F8 | The home card link is located in three shell tests by the fixed text of `admin.home.all_sheets`, inside the sheets card. | `admin.spec.ts:460-461`, `:671-672`, `editor-journey.spec.ts:58-61` | « Voir les {n} fiches » changes the accessible name: three locators rewritten one for one (R-24), through one helper that keeps the card scope. | | F9 | The sort « Dernière modification » puts the historical sheet last most of the time, so it leaves the home card's five, and with it the card's « Créer une copie de travail » shortcut. | board 29 note « Une fiche historique qui entre dans les cinq garde son action », `AdminHomeRoute.tsx:65-72`, `AdminSheetRoute.tsx:295` | Validated behaviour (A9). The action stays on the historical sheet's own page. Named in the owner trial text. | | F10 | Board 28's note « Sans résultat » settles the empty result with a filter active: the existing filters sentence stays. | board 28 | Followed as drawn. No decision. | | F11 | Search, sort and filters live in component state: opening a sheet and coming back resets them. The owner filed « search not kept on back » as an app-wide product follow-up (BDR-079), and R2 rejected address parameters for view state (BDR-116). | `AdminSheetsRoute.tsx:143`, `.claude/memory/decisions.md` BDR-079, BDR-116 | Out of R3 unless the owner decides otherwise (D5). | | F12 | The trial app is running now (a listener on port 5194) and follows the worktree live (Vite HMR); `pnpm db:trial migrate` refuses while the app runs. | `ss -ltn` 2026-11-16, `scripts/db-trial.sh:641`, `:1455`, `:1884-1887` | From the client change (Task 3) the owner's trial shows « indisponible » on the admin screens and the editor until the trial stack is migrated. The trial is migrated before Task 3, the app stopped during it. | | F13 | A7 is ratified as a delivery in two steps (search and name sort without a date first; the date after the SQL change). | `V1-2-6_U1_CORRECTIONS.md:613-615`, `:635-636`, checkpoint §5, BDR-108, BDR-109 | Building the final state in one lot supersedes that split: an explicit owner decision (D2). | | F14 | Boards 28 and 29 draw a magnifier in the search field. `Icon` has no such glyph, and the two search fields already shipped and validated (catalogue, Références tab) render none. | `components/ui/Icon.tsx:3-23`, `CatalogRoute.tsx:32-40`, `ReferenceToolbar.tsx:93-124` | No magnifier, as the two shipped searches; recorded as a deviation in `docs/design/README.md` unless the owner asks for the glyph. | Checked and found sound: the proposed expression (`greatest` ignores a null; both columns are trigger-kept; the unique index `(sheet_id, version_number)` serves the lookup), no PostgREST reload needed (same signature, private function, `jsonb` return, generated types unchanged), the fixture inserts of Task 1 against every insert-time trigger and constraint, `cmd_migrate` against the script's real names, the trial `migrate` command (cold backup first, A15), the ratified date format against Node 24 ICU (`fr-FR` « 9 nov. 2026, 12:06 », `en-GB` « 9 Nov 2026, 12:06 »; `en-US` does not), Zod `datetime({ offset: true })` against the jsonb timestamp format, soft deletion of a draft-only official sheet by an editor. ## Owner decisions pending | # | Question | Recommendation | If answered otherwise | |---|---|---|---| | D1 | Go to write the migration M9 and its pgTAP (PGlite only, no stack touched) | Yes | The ratified « before » state only: search, sort by name, no date, the card's first five by name (Tasks 4 to 7 without the date). | | D2 | One delivery in the final state, superseding A7's two steps | Yes: with D1 and D3 the « before » state is never shown | Step one ships first as A7 says, the date in a second lot. | | D3 | Stacks and order | After M9 passes PGlite and a fresh security review of its two-line diff: the sandbox through a new `pnpm db:sandbox migrate` (forward only, no reset, no row changed), then the trial stack through `pnpm db:trial migrate` on the owner's go at that moment (the trial app stopped, cold backup first, the stack stops a few seconds). Both before any app code that requires the key. M9 is frozen once applied to a stack: a later change ships as a new migration. | Trial only: the key becomes optional in the client and the UI hides the date when absent (a second code path, no real-stack proof). A new sandbox: the current one is removed (inventory and go first). | | D4 | RR-13's written scope | The TODO text says « admin lists: rights and official sheets »; still separate, still not implemented | A new item for the sheet list; the `shortcut:` cites it. | | D5 | Search, sort and filters kept when coming back from a sheet | Not in R3: it is the app-wide follow-up of BDR-079, and the address was rejected for view state in BDR-116 | Its own small lot after R3, on the editor's browser-history mechanism, typed text kept local. | | D6 | The card's link below two sheets (« Voir les {n} fiches » has no singular) | The existing validated label « Toutes les fiches officielles » for zero or one sheet, « Voir les {n} fiches » from two | Another wording: a label change to validate. | | D7 | The R3 browser test removes its own fixtures (soft deletion of the three sheets each run created, ids captured one by one) | Yes: it is the run-scoped cleanup the owner asked to be proposed (TODO, « e2e fixture accumulation ») | The three sheets stay listed on the sandbox, six more per sweep. | Recorded unless the owner objects (each follows a validated document or an existing rule): - Boards as drawn: no new badge (published and draft told apart by the detail line), the four filters unchanged, the empty-result note of board 28 literally. - No magnifier in the search field (F14). - The name sort follows the shown name: French names with the French collation in the French interface, English names in the English interface (the board says « ordre alphabétique français » for the French screen). - The date is shown in Paris time, as the editor's « enregistré le … ». - Search and sort stay TypeScript, with their rows in the Rust exception table (class C, display only), as BDR-114 (A) did for the Références tab and as the ratified R3 text prescribes (`folded`, `Intl.Collator`). - The limits of F7 are written into the checkpoint document; (i) is fixed in the write the day a « supprimer le brouillon » screen exists. ## Global Constraints - Work in `/home/bchanot/Documents/faunosteo-v1-2` on `feature/v1-2-anatomical-sheets`. No merge, no deploy, no DEV. - The trial app serves this worktree live: between tasks it shows work in progress. The owner's verdict is asked only at the final stop. - Never touch: `crates/`, `supabase/migrations/*` other than the one new file, `scripts/db-trial*`, `components/editor/**`, `components/catalogue/**`, `GrantList`, `list_catalog_grants`, `admin.spec.ts` beyond the two locator rewrites of Task 7 (BLK-015 and RR-13 stay separate tracks). - M9 is frozen from its first application to any stack: never edited in place afterwards (the sandbox cannot detect an edited migration). - `scripts/db-sandbox.sh`: additions only, no line removed or changed (`scripts/db-trial-check.sh:627`). - Stack operations: `pnpm db:sandbox migrate` only after D3; `pnpm db:trial migrate` only on the owner's go at that moment; never `db:sandbox down` or `up`, a reset, `pnpm db:trial stop` or `start`, or `editor-equine.spec.ts`. `scripts/db-trial-check.sh` exits 2 while the trial is up and suggests stopping it: report the refusal, stop nothing. - Limits: 25 logic lines per function, 100 chars per line, 5 params, 5 locals, no global state; i18n through `useT()` with fr / en parity; no raw string in JSX. - Labels exactly as `REVUE.md:82-84`: « Rechercher une fiche par son nom », « Trier par », « Dernière modification », « Nom », « {n} fiches », « {n} fiches sur {total} », « Modifiée le {date} », « Aucune fiche ne contient « {q} » dans son nom. », « Effacer la recherche », « Dernières modifications », « Voir les {n} fiches »; EN « Search a sheet by name », « Sort by », « Last modified », « Name », « {n} sheets », « {n} sheets of {total} », « Modified on {date} », « No sheet name contains “{q}”. », « Clear the search », « Latest changes », « See the {n} sheets ». - Date (A8): « 9 nov. 2026, 12:06 » / « 9 Nov 2026, 12:06 », Paris time. - No section, filter or rule built on a UUID, a name, a slug or a test convention; generic for every species (D7). - R-24: every removed or rewritten assertion mapped to its replacement in the commit body. - Data guard around every sandbox session (R2 rule G3): per-id snapshot of the pre-existing rows of `anatomical_sheets` and `anatomical_sheet_versions` (full rows, `md5(content::text)`) before and after, through `pnpm db:sandbox sql`, select only; a changed or missing row stops the run. New rows are counted per run. - Commits: Conventional Commits; one coherent change each. - Gates per task in this order: `pnpm typecheck`, `pnpm lint`, `pnpm test:run`, then what the task names. ## Review Focus 1. A stack without the migration: the client answers the closed `unavailable` failure, never a list with a missing or invented date. Test in Task 3. 2. Two sheets with the same date, or the same name: the order does not depend on the server's order and does not flip between renders. Test in Task 4 (ties by name, then id). 3. Search text with replacement patterns (`$&`), brackets, or 100 characters without a space: shown as plain text, no exception, no sideways scroll at 375 px. Tests in Task 5 (unit) and Task 7 (browser). 4. A sheet named differently in each language (« Cheval » / « Horse »): found by either name in either interface language; the name sort follows the shown language. Test in Task 4. 5. The species read fails while the sheet list answers: the home card still shows, with the species id in place of its name. Test in Task 6. --- ## File Structure | File | Responsibility | |---|---| | Create `supabase/migrations/20261116100000_sheet_admin_last_modified.sql` | M9: the summary with `last_modified_at` | | Modify `supabase/tests/022_sheet_admin_reads.sql` | 8 new assertions, plan 60 → 68 | | Modify `scripts/db-sandbox.sh` | `migrate` command (additions only) | | Modify `packages/supabase/src/sheets.ts`, `tests/sheets-admin.test.ts` | the summary schema requires the key | | Modify `apps/app/src/catalogue/useCatalogue.ts` | `matchesSheetName` exported, `filterCatalog` calls it (one search rule for both screens) | | Create `apps/app/src/admin/sheetList.ts` | `searchSheets`, `sortSheets`, `SHEET_SORTS` (pure) | | Modify `apps/app/src/admin/labels.ts` | `formatShortParisDateTime`, `sheetDetails` (moved from the route) | | Create `apps/app/src/components/admin/ModifiedDate.tsx` | « Modifiée le … » as a `