First create at 255dd67 done; post-create checks interrupted at check 3 (TR_PW in the db container log, written by the image's init). Contract records the owner's arbitrations of 2026-11-11; TODO lists the follow-up lots.
282 lines
20 KiB
Markdown
282 lines
20 KiB
Markdown
# CONTRACT — db-trial-stack
|
|
- date: 2026-11-09 | flow: ship-feature | branch: feature/v1-2-anatomical-sheets
|
|
- status: active
|
|
- worktree: /home/bchanot/Documents/faunosteo-v1-2 (every command from this absolute path, LRN-110; never the main
|
|
checkout /home/bchanot/Documents/faunosteo, whose old scripts reach DEV). Commit in place on the working branch,
|
|
push each commit; no sub-branch, no merge, no `gitflow.sh finish`.
|
|
- base: b215b89
|
|
- untracked files kept: `faunosteo-v1-2-6-revue-ux-chatgpt.md` (this worktree),
|
|
`/home/bchanot/Documents/faunosteo/faunosteo-v1-2-4-revue-chatgpt.md` (main checkout).
|
|
- design reference: `docs/development/TRIAL_STACK.md` (arbitrations A11-A15 settled, BDR-107 to BDR-110),
|
|
`docs/development/V1-2_ANATOMICAL_SHEETS.md` §5.7.1 (first owner bootstrap order).
|
|
|
|
## REQUEST (verbatim — IMMUTABLE)
|
|
|
|
# FAUNOSTEO — Après R0 — Implémentation technique de la pile d'essai dédiée
|
|
|
|
Le propriétaire prend acte de la clôture graphique de R0, du verdict indépendant CONFORME et de BDR-110.
|
|
|
|
Les maquettes R0 sont définitivement validées. Ne pas rouvrir leur conception.
|
|
|
|
## 1. Autorisation
|
|
|
|
Autorisation ciblée pour implémenter les scripts, configurations et tests nécessaires à la pile d'essai dédiée, selon docs/development/TRIAL_STACK.md et les arbitrages A11 à A15.
|
|
|
|
Cette autorisation ne couvre PAS le démarrage effectif de la pile ni la création de données.
|
|
|
|
## 2. Isolation
|
|
|
|
Créer une pile distincte de la sandbox automatisée et de Supabase DEV :
|
|
|
|
- Préfixe des conteneurs : faunosteo_trial_.
|
|
- Commandes : pnpm db:trial.
|
|
- Ports séparés, selon le plan validé.
|
|
- Base de données, stockage, secrets, réseaux et volumes distincts.
|
|
- Volumes persistants et sauvegardes contrôlées.
|
|
- Limites mémoire adaptées aux ressources de l'hôte.
|
|
- Aucune réutilisation des volumes, secrets ou noms de conteneurs de DEV ou de la sandbox.
|
|
|
|
Conserver les commandes db:sandbox trial* existantes, sans redirection implicite.
|
|
|
|
## 3. Sécurité
|
|
|
|
Mettre en place des garde-fous empêchant les suites E2E, Vitest ou les outils de réinitialisation de la sandbox d'utiliser la pile d'essai.
|
|
|
|
Privilégier des vérifications bloquantes : tout environnement ambigu doit provoquer un refus d'exécution.
|
|
|
|
Ne pas exposer les ports internes de PostgreSQL, Auth ou Storage au réseau local.
|
|
|
|
Prévoir la rotation des secrets sans perte de données.
|
|
|
|
Ne jamais écrire de secrets dans Git, les journaux ou les rapports.
|
|
|
|
## 4. Comptes d'essai
|
|
|
|
Prévoir deux comptes distincts :
|
|
|
|
- Owner/admin, pour les essais administratifs.
|
|
- Praticien d'essai avec rôle admin technique et habilitation sheet_editor, sans owner, reviewer ou publisher.
|
|
|
|
Ne pas modifier la règle O1.
|
|
|
|
Documenter clairement que le second compte ne représente pas encore les autorisations d'un praticien de production.
|
|
|
|
Préserver les exigences MFA.
|
|
|
|
## 5. Cheval historique
|
|
|
|
Préparer une méthode reproductible pour charger la fiche équine de référence et ses illustrations.
|
|
|
|
Ne pas copier silencieusement des données de la sandbox actuelle.
|
|
|
|
Vérifier les empreintes, la présence des illustrations et l'intégrité du document importé.
|
|
|
|
Éviter les doublons en cas de relancement.
|
|
|
|
Ne pas modifier la fiche équine historique ni les données de DEV.
|
|
|
|
## 6. Tests
|
|
|
|
Exécuter uniquement les contrôles ne nécessitant pas de création ou de destruction d'environnement.
|
|
|
|
Vérifier :
|
|
- Le ciblage exclusif des ressources trial.
|
|
- Les collisions de ports et de noms.
|
|
- Les garde-fous contre les tests automatisés.
|
|
- La persistance prévue.
|
|
- La sécurité des secrets.
|
|
- Les chemins de sauvegarde et de restauration.
|
|
- La compatibilité avec les commandes sandbox existantes.
|
|
|
|
Effectuer une revue indépendante du code et une revue de sécurité.
|
|
|
|
Commiter et pousser chaque lot validé.
|
|
|
|
## 7. Checkpoint avant création
|
|
|
|
Avant de lancer pnpm db:trial up ou une commande équivalente, fournir :
|
|
|
|
1. La liste exacte des ressources Docker qui seraient créées.
|
|
2. Les ports réellement disponibles et les éventuels conflits.
|
|
3. L'emplacement des volumes et sauvegardes.
|
|
4. Les limites mémoire prévues.
|
|
5. Les commandes de démarrage, arrêt, sauvegarde et rotation.
|
|
6. La procédure de chargement de l'équin et des illustrations.
|
|
7. Les résultats des tests et revues.
|
|
8. La preuve que les commandes de test automatiques ne peuvent pas cibler cette pile.
|
|
|
|
S'arrêter et attendre l'accord explicite du propriétaire avant toute création effective.
|
|
|
|
## 8. Restrictions
|
|
|
|
Ne pas démarrer, réinitialiser ou supprimer de conteneur.
|
|
|
|
Ne pas toucher à la sandbox existante ni à ses 219 fiches.
|
|
|
|
Ne pas intervenir sur Supabase DEV.
|
|
|
|
Aucune migration SQL applicative, modification Rust, worker ou format documentaire.
|
|
|
|
Ne pas commencer R1 à R4, U2, U2b ou V1-2.7.
|
|
|
|
Aucune fusion ni déploiement.
|
|
|
|
Préserver les fichiers non suivis, notamment la revue ChatGPT.
|
|
|
|
S'arrêter au checkpoint de création de la pile d'essai.
|
|
|
|
## CLARIFICATIONS
|
|
Q: backup format and restore? / A: cold physical copy (stack stopped a few seconds, both volumes archived through a
|
|
`--rm` helper container, secrets copy, restart) + new command `pnpm db:trial restore <backup>` with typed
|
|
confirmation and an automatic backup first; same Postgres image required. Supersedes TRIAL_STACK.md §3.4 `pg_dump -Fc`.
|
|
[gated 2026-11-09]
|
|
Q: read-only dry run for the checkpoint? / A: new command `pnpm db:trial plan`: exact containers, volumes, network,
|
|
each port free or taken, volume and backup locations, memory limits; no docker call that creates or changes anything.
|
|
[gated 2026-11-09]
|
|
Q: how far to change `scripts/db-sandbox.sh`? / A: guard only (refuse a trial state folder); `db-trial.sh` self-contained
|
|
with its own image pins; no shared helper extraction (TRIAL_STACK.md §4 sentence superseded). [gated 2026-11-09]
|
|
Q: public sign-up on the trial stack? / A: closed (`GOTRUE_DISABLE_SIGNUP=true`); accounts only through
|
|
`pnpm db:trial account`. [gated 2026-11-09]
|
|
Q: plan approval? / A: owner 2026-11-10 « Décision sur le plan db:trial »: architecture, lots A, B1, B2, B3, C and
|
|
closure approved, conditional on R13 fixed in the plan (exact target identification, no ambiguous destination, no
|
|
fallback, db:sandbox trial* preserved with non-regression tests) and an independent targeted counter-check; validated:
|
|
exact interactive confirmations, piped or automated confirmations refused, harness outside check:all, grant without
|
|
implicit admin, FAUNOSTEO_TRIAL_DIR checked on its canonical path incl. symlinks, no secrets.prev given a restorable
|
|
protected prior backup, app address recomputed at each launch, ss checked explicitly. Authorisation covers writing and
|
|
static or simulated checks only: no real create/start/stop/migration/restore/destroy/rotation, no DEV or sandbox, no
|
|
merge or deploy; a script's interactive confirmation is never the owner's prior authorisation. [gated 2026-11-10]
|
|
|
|
Q: owner arbitrations at the creation checkpoint (HEAD 255dd67) / A: (1) shellcheck: the 15 directives are allowed
|
|
for demonstrated false positives only, with minimal scope and a readable reason, global shellcheck clean, no other
|
|
diagnostic masked (the file-wide SC2016 at the top of db-trial-check.sh does not qualify: to narrow). (2) LAN
|
|
Playwright: before any LAN test the environment check proves the expected target, the container or service really
|
|
used, the key issuer and that DEV cannot be reached; a port is no identity; 5193 vs 5194 explained and checked; any
|
|
ambiguity refuses before connecting. (3) rotate-secrets: existing sessions invalidated, users sign in again:
|
|
refresh tokens actually revoked and old access JWTs handled (revoking a session is not invalidating a JWT); real
|
|
behaviour tested before the command is enabled, else it refuses and the limit is documented; DEV accounts and
|
|
secrets untouched. (4) destroy: refused by default for keepable data without a backup verified by an isolated
|
|
restore, PostgreSQL and Storage both; a backup file is never proof of restore; an explicit controlled procedure for
|
|
an instance proved empty; no destroy in this step. (5) GO for the first create of faunosteo_trial only, after
|
|
worktree/HEAD, Docker context/daemon, no overwrite, no concurrent run, static checks on the final code and proof
|
|
that create depends on neither rotate-secrets, destroy nor the LAN test; then immediate checks 1-10; an exposed
|
|
secret, a DEV target or a failed critical check interrupts, no optional step, real state reported, no secret shown.
|
|
No merge, deploy, DEV change or DEV secret, no rotate-secrets before its validation, no destroy, no new network
|
|
exposure, no DEV worker. [gated 2026-11-11]
|
|
|
|
## ACCEPTANCE CRITERIA
|
|
1. `pnpm db:trial` (root `package.json`) runs `scripts/db-trial.sh` with the commands of TRIAL_STACK.md §4 (`create`,
|
|
`start`, `stop`, `status`, `migrate`, `backup`, `rotate-keys`, `rotate-secrets`, `app`, `account <email>`,
|
|
`grant <email> <right>`, `assets`, `destroy`); `pnpm db:sandbox` keeps every existing command, `trial`,
|
|
`trial-account` and `trial-grant` included, with unchanged behaviour (no redirection to the trial stack).
|
|
2. Docker resources of the trial stack are literal names only: containers `faunosteo_trial_db`, `_auth`, `_rest`,
|
|
`_storage`, `_mail`, `_gateway`, network `faunosteo_trial_net`, volumes `faunosteo_trial_db` and
|
|
`faunosteo_trial_storage`; every docker call of `db-trial.sh` that starts, stops, recreates or removes names one of
|
|
them (helper containers excepted, prefixed `faunosteo_trial_` and `--rm`), never a filter or a pattern; no name,
|
|
port, volume, network or secret is shared with DEV (`supabase_*_faunosteo`, 54321-54324) or the sandbox
|
|
(`faunosteo_dbsandbox_*`, 57xxx, 5193).
|
|
3. Ports: Postgres 127.0.0.1:58432, GoTrue :58999, PostgREST :58300, Storage :58500, Mailpit :58025, gateway :58801,
|
|
all published on 127.0.0.1 only; the app on the validated trial host :5194 (`--strictPort`); every start refuses a
|
|
taken port before any docker call and checks after start that each container publishes on 127.0.0.1 only.
|
|
4. Persistence: Postgres data in volume `faunosteo_trial_db` at `PGDATA`, Storage files in `faunosteo_trial_storage`
|
|
at `/mnt`; restart policy `no`; `start`/`stop`/rotation keep both volumes; a volume is removed only by `destroy`
|
|
(and by the restore path if the gate adds one), after a typed confirmation.
|
|
5. Resources: per container `--memory`, `--cpus 1`, `--pids-limit` as TRIAL_STACK.md §3.7 (Postgres 1 GiB with
|
|
`shared_buffers` 128 MB, PostgREST 384 MiB, Storage 256 MiB, GoTrue 128 MiB, Mailpit and gateway 64 MiB); the app
|
|
runs with `NODE_OPTIONS=--max-old-space-size=768`; `create` refuses when the host lacks the memory budget.
|
|
6. Secrets: state folder `~/.cache/faunosteo-trial` (override `FAUNOSTEO_TRIAL_DIR`), mode 700, refused inside the
|
|
repository and refused when it is (or holds) the sandbox state; files 600; values reach containers and tools through
|
|
env files or stdin, never argv, script output, logs or Git; keys signed with the trial's own JWT secret, issuer
|
|
distinct from `db-sandbox`, valid one year; `rotate-keys` re-signs both keys, `rotate-secrets` renews JWT secret,
|
|
keys and role passwords without data loss, account passwords untouched; neither prints a secret.
|
|
7. Isolation guards, blocking and fail-closed: `db-trial.sh` refuses to run when any `SANDBOX_*` variable is set; the
|
|
sandbox suites (`packages/supabase/tests/sandbox/stack.ts` and every importer, the shell and LAN Playwright
|
|
configs) refuse any target that is not the sandbox's exact container, loopback ports and key issuer before their
|
|
first request; `db-sandbox.sh` refuses a state folder that belongs to the trial stack; `e2e:avatar` and
|
|
`e2e:sheet-assets` still refuse anything but the sandbox.
|
|
8. Accounts: `account <email>` creates a confirmed account through GoTrue's admin API, password at a hidden prompt
|
|
(twice, 12 characters min.), never printed; `grant` assigns platform roles `owner` / `admin` only through
|
|
`private.assign_platform_role(null, …)` from a JWT-less session after the operator check of §5.7.1 (verified TOTP
|
|
factors present) and catalogue rights as the sandbox does; O1 and every migration unchanged; no MFA factor, aal or
|
|
session is written by the script; docs state that the practitioner account (technical `admin` + `sheet_editor`) is
|
|
not a production practitioner profile.
|
|
9. Equine sheet: `create` applies the branch's migrations and `equine_draft.sql` only (never `seed.sql`), after
|
|
checking the seed folder's hashes; it then checks in the database that the historical sheet is the only official
|
|
sheet and that its draft content equals `document.json`; `assets` uploads the drawings with the existing
|
|
`upload-sheet-assets` tool through the trial gateway (no overwrite, rerun = nothing to do) and checks every asset
|
|
row uploaded; no data copied from the sandbox; seed files untouched.
|
|
10. `migrate` takes a backup first (A15), refuses when a migration already applied differs from its file, applies the
|
|
new ones with `--db-url` on 58432 only, and reloads PostgREST's schema.
|
|
11. `backup` writes into `<state>/backups/` (700/600, never pruned by the script) what a restore needs, secrets copy
|
|
included; the restore path follows the gate's decision.
|
|
12. Environment-free checks only (no container, volume or network created, started, stopped or removed during the
|
|
run): `shellcheck` clean on the new and changed scripts; a stub harness proves criteria 2-7, 10-11 on the docker
|
|
and curl calls the script would make, with no daemon reachable; the guard's unit tests run in `pnpm test:run`;
|
|
`pnpm typecheck`, `pnpm lint`, `pnpm test:run` green.
|
|
13. Untouched: `supabase/migrations/`, `supabase/seed/`, `crates/`, document and archive formats, DEV, the sandbox's
|
|
containers and data, the untracked review files.
|
|
14. Docs: `SUPABASE.md` (trial stack section, rotation procedure), `V1-2-6_OWNER_TRIAL.md`, `TRIAL_STACK.md` status,
|
|
`CLAUDE.md` commands, `.claude/tasks/TODO.md`.
|
|
15. Stop at the creation checkpoint with the 8 items of §7 of the request; no `create` run.
|
|
16. [gated 2026-11-10] R13: the trial dev server (`apps/app/vite/trial.config.ts`, shared with `db:sandbox trial`)
|
|
serves exactly one of two literal targets (`apps/app/vite/trial-targets.ts`): sandbox = app 5193, gateway
|
|
`http://127.0.0.1:57801`, key issuer `db-sandbox`; trial = app 5194, gateway `http://127.0.0.1:58801`, issuer
|
|
`faunosteo-trial`, gateway identity `faunosteo_trial`; any other gateway, port, app URL, issuer or role is refused,
|
|
no default, no fallback; the exact values `db-sandbox.sh` passes today stay accepted (non-regression tests).
|
|
`db-trial.sh` checks before each operation the target container's exact loopback publication, the trial gateway's
|
|
identity, and every key it hands over (issuer, role, HMAC with the trial secret, values never in argv). The
|
|
independent R13 counter-check returned CONFORME (or every finding it raised is closed in the plan) before lot A2.
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm --filter @faunosteo/app exec vitest run tests/unit/vite.trialTargets.test.ts tests/unit/vite.trialConfig.test.ts && pnpm --filter @faunosteo/app exec tsc -p tsconfig.node.json --noEmit && pnpm --filter @faunosteo/supabase exec vitest run tests/sandbox-target.test.ts && echo GUARD-TESTS-OK
|
|
EXPECT: GUARD-TESTS-OK
|
|
EVIDENCE: MET exit=0 marker-found :: RUN v3.2.7 /home/bchanot/Documents/faunosteo-v1-2/apps/app stderr | tests/unit/vite.trialConfig.test.ts > shared trial dev server config, as…
|
|
17. [gated 2026-11-10] The stub harness passes in full with simulated services only (docker, curl, ss, node and env
|
|
wrapped; no daemon reachable), including: typed confirmations of `destroy` (`faunosteo_trial`), `restore`
|
|
(`restore <name>`) and `grant` (`grant <role>`) through a terminal only, piped phrases refused; `grant` never adds
|
|
`admin` implicitly; `FAUNOSTEO_TRIAL_DIR` refused on its canonical path (symlink into the repository or onto the
|
|
sandbox state, any git work tree); `ss` missing or failing refuses (never read as a free port); `rotate-keys` and
|
|
`rotate-secrets` take a verified backup first (700/600, checksums, previous `secrets.env` inside); no secret in
|
|
any logged argv. bash -n and shellcheck are never offered as proof of real behaviour.
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && bash scripts/db-trial-check.sh
|
|
EXPECT: db-trial-check: ALL PASS
|
|
EVIDENCE: MET exit=0 marker-found :: -- static PASS shellcheck: db-trial.sh and this harness PASS shellcheck: db-sandbox.sh (pre-existing SC1090, SC1091, SC2016 excluded) PASS d…
|
|
18. [gated 2026-11-10] Static checks: shellcheck clean on `db-trial.sh`, the harness and `db-sandbox.sh`.
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && shellcheck -x scripts/db-trial.sh scripts/db-trial-check.sh && shellcheck -e SC1090,SC1091,SC2016 scripts/db-sandbox.sh && echo SHELLCHECK-OK
|
|
EXPECT: SHELLCHECK-OK
|
|
EVIDENCE: MET exit=0 marker-found :: SHELLCHECK-OK
|
|
19. [gated 2026-11-10] Project suites green.
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm typecheck && pnpm lint && pnpm test:run && echo SUITES-GREEN
|
|
EXPECT: SUITES-GREEN
|
|
EVIDENCE: MET exit=0 marker-found :: > faunosteo@0.1.0 typecheck /home/bchanot/Documents/faunosteo-v1-2 > pnpm -r typecheck Scope: 3 of 4 workspace projects packages/core-wasm t…
|
|
20. [gated 2026-11-10] `scripts/db-sandbox.sh` gains lines and loses none since b215b89 (its commands unchanged).
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && test "$(git diff --numstat b215b89 -- scripts/db-sandbox.sh | awk '{print ($1>0 && $2==0) ? "ok" : "bad"}')" = ok && echo SANDBOX-ADDITIONS-ONLY
|
|
EXPECT: SANDBOX-ADDITIONS-ONLY
|
|
EVIDENCE: MET exit=0 marker-found :: SANDBOX-ADDITIONS-ONLY
|
|
21. [gated 2026-11-10] Untouched: migrations, seed and crates (committed and working tree).
|
|
CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && git diff --quiet b215b89 -- supabase/migrations supabase/seed crates && test -z "$(git status --porcelain -- supabase/migrations supabase/seed crates)" && echo UNTOUCHED
|
|
EXPECT: UNTOUCHED
|
|
EVIDENCE: MET exit=0 marker-found :: UNTOUCHED
|
|
22. [gated 2026-11-10] Nothing real created: no `faunosteo_trial*` container, volume or network, no state folder
|
|
(the pattern is first proven on a fixture line, the listings must succeed).
|
|
CHECK: printf 'faunosteo_trial_db\n' | grep -q '^faunosteo_trial' && c=$(docker ps -a --format '{{.Names}}') && v=$(docker volume ls --format '{{.Name}}') && n=$(docker network ls --format '{{.Name}}') && ! printf '%s\n%s\n%s\n' "$c" "$v" "$n" | grep -q '^faunosteo_trial' && test ! -e "$HOME/.cache/faunosteo-trial" && echo NOTHING-CREATED
|
|
EXPECT: NOTHING-CREATED
|
|
EVIDENCE: MET exit=0 marker-found :: NOTHING-CREATED
|
|
23. [gated 2026-11-10] The untracked review files are kept.
|
|
CHECK: test -f /home/bchanot/Documents/faunosteo-v1-2/faunosteo-v1-2-6-revue-ux-chatgpt.md && test -f /home/bchanot/Documents/faunosteo/faunosteo-v1-2-4-revue-chatgpt.md && echo REVIEWS-KEPT
|
|
EXPECT: REVIEWS-KEPT
|
|
EVIDENCE: MET exit=0 marker-found :: REVIEWS-KEPT
|
|
24. [gated 2026-11-10] Each lot (A, A2, B1, B2, B3, C) tested, reviewed, committed and pushed before the next; no
|
|
merge, no deploy, no real create/start/stop/migrate/restore/destroy/rotation, DEV and the sandbox untouched; the
|
|
closure report gives the exact R13 counter-check verdict, test results, security review, the 8 checkpoint items
|
|
and the read-only `pnpm db:trial plan` output, then stops for the owner's explicit go.
|
|
|
|
## FILE SCOPE
|
|
- new: `scripts/db-trial.sh`, the stub harness under `scripts/`, the guard module and its unit test under
|
|
`packages/supabase/tests/`
|
|
- changed: `package.json` (one script), `scripts/db-sandbox.sh` (state-folder guard),
|
|
`packages/supabase/tests/sandbox/stack.ts`, `apps/app/e2e/playwright.shell.config.ts`,
|
|
`apps/app/e2e/playwright.lan.config.ts`, `apps/app/vite/trial.config.ts` (target table checks, R13 [gated
|
|
2026-11-10]); new `apps/app/vite/trial-targets.ts`, `apps/app/tests/unit/vite.trialTargets.test.ts` and
|
|
`apps/app/tests/unit/vite.trialConfig.test.ts`
|
|
- docs: `docs/development/{SUPABASE,V1-2-6_OWNER_TRIAL,TRIAL_STACK}.md`, `CLAUDE.md`, `.claude/tasks/TODO.md`
|
|
- orchestrator artifacts (named after GATE 1, 2026-11-10; not a criterion change): this contract and the plan
|
|
`.claude/tasks/plans/2026-11-09-db-trial-stack.md` (review record of every lot and of the R13 counter-check)
|