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.
20 KiB
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 :
- La liste exacte des ressources Docker qui seraient créées.
- Les ports réellement disponibles et les éventuels conflits.
- L'emplacement des volumes et sauvegardes.
- Les limites mémoire prévues.
- Les commandes de démarrage, arrêt, sauvegarde et rotation.
- La procédure de chargement de l'équin et des illustrations.
- Les résultats des tests et revues.
- 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
pnpm db:trial(rootpackage.json) runsscripts/db-trial.shwith 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:sandboxkeeps every existing command,trial,trial-accountandtrial-grantincluded, with unchanged behaviour (no redirection to the trial stack).- Docker resources of the trial stack are literal names only: containers
faunosteo_trial_db,_auth,_rest,_storage,_mail,_gateway, networkfaunosteo_trial_net, volumesfaunosteo_trial_dbandfaunosteo_trial_storage; every docker call ofdb-trial.shthat starts, stops, recreates or removes names one of them (helper containers excepted, prefixedfaunosteo_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). - 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. - Persistence: Postgres data in volume
faunosteo_trial_dbatPGDATA, Storage files infaunosteo_trial_storageat/mnt; restart policyno;start/stop/rotation keep both volumes; a volume is removed only bydestroy(and by the restore path if the gate adds one), after a typed confirmation. - Resources: per container
--memory,--cpus 1,--pids-limitas TRIAL_STACK.md §3.7 (Postgres 1 GiB withshared_buffers128 MB, PostgREST 384 MiB, Storage 256 MiB, GoTrue 128 MiB, Mailpit and gateway 64 MiB); the app runs withNODE_OPTIONS=--max-old-space-size=768;createrefuses when the host lacks the memory budget. - Secrets: state folder
~/.cache/faunosteo-trial(overrideFAUNOSTEO_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 fromdb-sandbox, valid one year;rotate-keysre-signs both keys,rotate-secretsrenews JWT secret, keys and role passwords without data loss, account passwords untouched; neither prints a secret. - Isolation guards, blocking and fail-closed:
db-trial.shrefuses to run when anySANDBOX_*variable is set; the sandbox suites (packages/supabase/tests/sandbox/stack.tsand 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.shrefuses a state folder that belongs to the trial stack;e2e:avatarande2e:sheet-assetsstill refuse anything but the sandbox. - Accounts:
account <email>creates a confirmed account through GoTrue's admin API, password at a hidden prompt (twice, 12 characters min.), never printed;grantassigns platform rolesowner/adminonly throughprivate.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 (technicaladmin+sheet_editor) is not a production practitioner profile. - Equine sheet:
createapplies the branch's migrations andequine_draft.sqlonly (neverseed.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 equalsdocument.json;assetsuploads the drawings with the existingupload-sheet-assetstool 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. migratetakes a backup first (A15), refuses when a migration already applied differs from its file, applies the new ones with--db-urlon 58432 only, and reloads PostgREST's schema.backupwrites 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.- Environment-free checks only (no container, volume or network created, started, stopped or removed during the
run):
shellcheckclean 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 inpnpm test:run;pnpm typecheck,pnpm lint,pnpm test:rungreen. - Untouched:
supabase/migrations/,supabase/seed/,crates/, document and archive formats, DEV, the sandbox's containers and data, the untracked review files. - Docs:
SUPABASE.md(trial stack section, rotation procedure),V1-2-6_OWNER_TRIAL.md,TRIAL_STACK.mdstatus,CLAUDE.mdcommands,.claude/tasks/TODO.md. - Stop at the creation checkpoint with the 8 items of §7 of the request; no
createrun. - [gated 2026-11-10] R13: the trial dev server (
apps/app/vite/trial.config.ts, shared withdb:sandbox trial) serves exactly one of two literal targets (apps/app/vite/trial-targets.ts): sandbox = app 5193, gatewayhttp://127.0.0.1:57801, key issuerdb-sandbox; trial = app 5194, gatewayhttp://127.0.0.1:58801, issuerfaunosteo-trial, gateway identityfaunosteo_trial; any other gateway, port, app URL, issuer or role is refused, no default, no fallback; the exact valuesdb-sandbox.shpasses today stay accepted (non-regression tests).db-trial.shchecks 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… - [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>) andgrant(grant <role>) through a terminal only, piped phrases refused;grantnever addsadminimplicitly;FAUNOSTEO_TRIAL_DIRrefused on its canonical path (symlink into the repository or onto the sandbox state, any git work tree);ssmissing or failing refuses (never read as a free port);rotate-keysandrotate-secretstake a verified backup first (700/600, checksums, previoussecrets.envinside); 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… - [gated 2026-11-10] Static checks: shellcheck clean on
db-trial.sh, the harness anddb-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 - [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…
- [gated 2026-11-10]
scripts/db-sandbox.shgains lines and loses none sinceb215b89(its commands unchanged). CHECK: cd /home/bchanot/Documents/faunosteo-v1-2 && test "$(git diff --numstatb215b89-- 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 - [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 - [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 - [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
- [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 planoutput, then stops for the owner's explicit go.
FILE SCOPE
- new:
scripts/db-trial.sh, the stub harness underscripts/, the guard module and its unit test underpackages/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]); newapps/app/vite/trial-targets.ts,apps/app/tests/unit/vite.trialTargets.test.tsandapps/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)