Files
faunosteo/.claude/tasks/plans/2026-11-09-db-trial-stack.md
bchanot f2cbf6814f fix(supabase): db:trial second re-review fixes
Post-create scan covers pg_stat_statements and labels each count; log path defined; rotate-secrets 401 window documented; plan shows image state unknown without a daemon; start_containers checks the app host before recreating GoTrue; truthful restore and destroy way-out messages; harness checks read stdin from /dev/null. Harness 372/0.
2026-11-11 14:36:12 +01:00

3667 lines
222 KiB
Markdown

# db:trial stack Implementation Plan
> **For agentic workers:** REQUIRED SUB-SKILL: subagent-driven-development (ship-feature STEP 4). Lots run in order;
> each lot is one coherent change, checked, then committed and pushed by the orchestrator. Steps use checkbox
> (`- [ ]`) syntax for tracking.
**Goal:** a dedicated persistent manual trial stack, `pnpm db:trial`, isolated from DEV and from the sandbox, with
guards that keep every automated suite off it, built and tested without creating any Docker resource.
**Architecture:** one self-contained bash tool `scripts/db-trial.sh` (own image pins, literal `faunosteo_trial_*`
names, loopback ports 58xxx, named volumes, secrets in a 700 state folder outside the repository, cold backups);
a pure TypeScript guard `packages/supabase/tests/sandbox/target.ts` called by every sandbox suite entry point; an
environment-free stub harness `scripts/db-trial-check.sh` that runs both bash scripts from a throwaway copy with
docker, curl, the CLI, Vite and the Rust tools replaced by logging stubs and no reachable daemon.
**Tech Stack:** bash 5 (`set -euo pipefail`), Docker rootless 29 (only through stubs in this run), Node 24 (key
generation, JSON), TypeScript 5 + vitest 3, shellcheck 0.11.
**Spec:** contract `.claude/tasks/contracts/2026-11-09-db-trial-stack-1809.md` (REQUEST verbatim, 15 criteria,
4 gated clarifications); design `docs/development/TRIAL_STACK.md`; first-owner order
`docs/development/V1-2_ANATOMICAL_SHEETS.md` §5.7.1.
**Contract changes at the gate (owner 2026-11-10, R13 fixed, no longer deferred):** FILE SCOPE gains
`apps/app/vite/trial-targets.ts` (new) and `apps/app/tests/unit/vite.trialTargets.test.ts` (new);
`apps/app/vite/trial.config.ts` changes from "comment only" to the target table checks (lot A2); `scripts/db-sandbox.sh`
stays additions-only (lot A2 does not touch it); FILE SCOPE also gains `apps/app/tests/unit/vite.trialConfig.test.ts`
(the wiring test of the shared config, which also ties its sandbox constants to `scripts/db-sandbox.sh`). Criterion 7
gains: the trial dev server serves exactly one of the two
known stacks (gateway, app port, app URL, anon key issuer, and for db:trial the gateway's identity), and
`db-trial.sh` checks the identity of each operation's target (lot B1 table) and of every key it hands over.
## Global Constraints
- Worktree `/home/bchanot/Documents/faunosteo-v1-2`, branch `feature/v1-2-anatomical-sheets`, base b215b89. Every
shell command starts with `cd /home/bchanot/Documents/faunosteo-v1-2 &&` (LRN-110). Never read, run or edit
anything under `/home/bchanot/Documents/faunosteo` (other checkout, old scripts that reach DEV).
- NEVER run `scripts/db-trial.sh`, `pnpm db:trial …`, `pnpm db:sandbox …`, `pnpm db:*`, `pnpm supabase …`,
`pnpm e2e:*`, `pnpm check:all`, `pnpm worker:*`, or any `docker` command that is not `ps`, `inspect -f <named
non-secret fields>`, `volume ls`, `network ls`, `info`. Never a bare `docker inspect` / `container inspect`: it
prints the env-file values (database password, JWT secret, keys) into the output; the script follows the same rule
(`-f '{{.Name}}'`, `{{.State.Running}}`, `{{.State.Status}} {{.Config.Image}}` only). The trial script runs ONLY
inside `bash scripts/db-trial-check.sh`, which is run by hand (never a `check:all` stage: it probes the host's real
ports and memory). No container, volume or network is created, started, stopped or removed. The sandbox
(`faunosteo_dbsandbox_*`, 219 sheets) and DEV (`supabase_*_faunosteo`) are never touched.
- Typed confirmations (`destroy`, `restore`, `grant owner|admin`) need a terminal: `confirm` refuses piped stdin, so
no agent can feed a phrase through a pipe; the harness drives them through a python3 pty.
- No change under `supabase/migrations/`, `supabase/seed/`, `crates/`; no document or archive format change.
- Every heredoc delimiter quoted (`<<'EOF'`) unless expansion is wanted (LRN-110). No secret in argv, output, logs,
Git: containers read 600 env files, tools their environment (bash prefix assignment, never `env K=V`), psql its
stdin; `printf` is a builtin.
- Untracked files kept: `faunosteo-v1-2-6-revue-ux-chatgpt.md` (this worktree).
- Executors write code and run checks; they never commit, push, merge or run `gitflow.sh`. The orchestrator commits
each lot (Conventional Commits, scopes of CLAUDE.md) and pushes.
- Line length: bash follows `db-sandbox.sh` (long lines accepted); TypeScript per Prettier (`pnpm format` on the
changed files only).
## Review Focus
1. A state folder left half-written (secrets file truncated or edited, wrong mode, a create that died before its
first resource) must make every command refuse with a way out, never guess: lot B1 adds the malformed-secrets,
wrong-mode, daemon-down and failed-create cases; `destroy` with no resource clears the live files.
2. A port held by a process outside Docker when the stack starts, or a container published on 0.0.0.0: `start`
refuses before starting any container (all ports checked first, `ss`), and after start on a non-loopback
publication: lot B1 cases. A missing service container is recreated by `start` from stack.conf and secrets.env.
3. A migration file edited after being applied, or a new file older than the last applied one: `migrate` refuses
before any backup or stop (kept although the CLI would also refuse: the refusal comes before the cold backup's
stop/start); a migrate that stops part-way names its pre-migrate backup: lot B2 cases.
4. A backup folder tampered with, partial (no SHA256SUMS) or whose archive does not list: `restore` refuses before
any change; a failed backup leaves no folder; `restore`/`destroy` never remove a volume without a backup of it:
lot B2 cases.
5. Password probes over 127.0.0.1 inside the database container are `trust` in this image's `pg_hba.conf`: they
succeed whatever the password and never prove one. The rotation's commit is proven by the `app.trial_rotation`
marker it writes in the same transaction: lot B2 cases (committed, not committed, database silent).
6. A wrong or empty answer at a confirmation prompt, or a phrase piped without a terminal: nothing changed (lot B2
and B3 cases, through the harness's pty driver).
7. A destination that is only "some loopback port" (R13, fixed by owner decision 2026-11-10): the trial dev
server accepts exactly the sandbox's or the trial stack's gateway, with that stack's app port, app URL and anon
key issuer, and the trial gateway must answer its identity (lot A2 tests, both directions of a mix-up, plus the
unchanged db:sandbox values); `db-trial.sh` checks the container, port, identity and keys of each operation's
target (lot B1 table; lot B3 cases: identity wrong or absent, gateway or GoTrue published elsewhere, a key of
the sandbox issuer or signed with another secret).
---
### Lot A: sandbox target guard for the automated suites
**Files:**
- Create: `packages/supabase/tests/sandbox/target.ts`
- Create: `packages/supabase/tests/sandbox-target.test.ts`
- Modify: `packages/supabase/tests/sandbox/stack.ts:1-16` (docstring + guard call)
- Modify: `apps/app/e2e/playwright.shell.config.ts:9-19`
- Modify: `apps/app/e2e/playwright.lan.config.ts:1-12`
**Interfaces:**
- Produces: `SANDBOX_TARGET`, `jwtIssuer(token)`, `sandboxTargetProblems(env): string[]`,
`assertSandboxTarget(env = process.env): void`. Exact string equality on every value (house pattern of
`avatar-flow.e2e.ts` and `sheet-assets-e2e.sh`): no URL normalisation, no trailing-slash tolerance.
- [ ] **Step 1: write the failing test** `packages/supabase/tests/sandbox-target.test.ts`
```ts
import { describe, expect, it } from 'vitest'
import { assertSandboxTarget, sandboxTargetProblems } from './sandbox/target'
const b64 = (value: unknown) =>
Buffer.from(JSON.stringify(value)).toString('base64url')
const key = (claims: Record<string, unknown>) =>
`${b64({ alg: 'HS256', typ: 'JWT' })}.${b64(claims)}.signature`
/** What scripts/db-sandbox.sh exports through with_sandbox_env. */
const SANDBOX: Record<string, string | undefined> = {
SANDBOX_DB_CONTAINER: 'faunosteo_dbsandbox_db',
SANDBOX_AUTH_URL: 'http://127.0.0.1:57999',
SANDBOX_REST_URL: 'http://127.0.0.1:57300',
SANDBOX_STORAGE_URL: 'http://127.0.0.1:57500',
SANDBOX_MAIL_URL: 'http://127.0.0.1:57025',
SANDBOX_ANON_KEY: key({ role: 'anon', iss: 'db-sandbox' }),
SANDBOX_SERVICE_KEY: key({ role: 'service_role', iss: 'db-sandbox' }),
}
const TRIAL_SERVICE_KEY = key({ role: 'service_role', iss: 'faunosteo-trial' })
describe('sandbox target guard', () => {
it('accepts the environment db-sandbox.sh writes', () => {
expect(sandboxTargetProblems(SANDBOX)).toEqual([])
expect(() => assertSandboxTarget(SANDBOX)).not.toThrow()
})
// One refusal per value: the db:trial stack's own value in each variable.
it.each([
['SANDBOX_DB_CONTAINER', 'faunosteo_trial_db'],
['SANDBOX_AUTH_URL', 'http://127.0.0.1:58999'],
['SANDBOX_REST_URL', 'http://127.0.0.1:58300'],
['SANDBOX_STORAGE_URL', 'http://127.0.0.1:58500'],
['SANDBOX_MAIL_URL', 'http://127.0.0.1:58025'],
['SANDBOX_ANON_KEY', key({ role: 'anon', iss: 'faunosteo-trial' })],
['SANDBOX_SERVICE_KEY', TRIAL_SERVICE_KEY],
])('refuses the trial value of %s', (name, value) => {
const env = { ...SANDBOX, [name]: value }
expect(sandboxTargetProblems(env)).toHaveLength(1)
expect(() => assertSandboxTarget(env)).toThrow(/db:sandbox stack only/)
})
it('refuses a missing value and a key that is not a JWT', () => {
expect(
sandboxTargetProblems({ ...SANDBOX, SANDBOX_REST_URL: undefined }),
).toHaveLength(1)
expect(
sandboxTargetProblems({ ...SANDBOX, SANDBOX_ANON_KEY: 'not-a-jwt' }),
).toHaveLength(1)
})
it('names variables, never the values it refused', () => {
let message = ''
try {
assertSandboxTarget({ ...SANDBOX, SANDBOX_SERVICE_KEY: TRIAL_SERVICE_KEY })
} catch (error) {
message = (error as Error).message
}
expect(message).toMatch(/SANDBOX_SERVICE_KEY/)
expect(message).not.toContain(TRIAL_SERVICE_KEY)
})
})
```
- [ ] **Step 2: run it, expect FAIL** (module missing)
Run: `cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm --filter @faunosteo/supabase exec vitest run tests/sandbox-target.test.ts`
Expected: FAIL, `Failed to resolve import "./sandbox/target"`.
- [ ] **Step 3: create** `packages/supabase/tests/sandbox/target.ts`
```ts
/**
* The one stack the sandbox suites may reach: the throwaway stack of `pnpm db:sandbox`
* (scripts/db-sandbox.sh), whose runner passes exactly these values. Anything else, such as the
* persistent trial stack of `pnpm db:trial` (containers faunosteo_trial_*, ports 58xxx, keys
* signed for the issuer faunosteo-trial), DEV (54321) or a look-alike address, stops a suite
* before its first request (docs/development/TRIAL_STACK.md §3.6). Exact string equality, as
* avatar-flow.e2e.ts and sheet-assets-e2e.sh do. Messages name variables, never their values.
*/
/** The exact values scripts/db-sandbox.sh writes (`with_sandbox_env`, `new_secrets`). */
export const SANDBOX_TARGET = {
container: 'faunosteo_dbsandbox_db',
issuer: 'db-sandbox',
urls: {
SANDBOX_AUTH_URL: 'http://127.0.0.1:57999',
SANDBOX_REST_URL: 'http://127.0.0.1:57300',
SANDBOX_STORAGE_URL: 'http://127.0.0.1:57500',
SANDBOX_MAIL_URL: 'http://127.0.0.1:57025',
},
} as const
type Env = Readonly<Record<string, string | undefined>>
const KEY_VARIABLES = ['SANDBOX_ANON_KEY', 'SANDBOX_SERVICE_KEY'] as const
/** The `iss` claim of a JWT, or null when the value is not a readable JWT payload. */
export function jwtIssuer(token: string | undefined): string | null {
const payload = token?.split('.')[1]
if (!payload) return null
try {
const claims: unknown = JSON.parse(
Buffer.from(payload, 'base64url').toString('utf8'),
)
if (typeof claims !== 'object' || claims === null || Array.isArray(claims))
return null
const iss = (claims as { iss?: unknown }).iss
return typeof iss === 'string' ? iss : null
} catch {
return null
}
}
/** Every way `env` differs from the sandbox runner's environment; empty when it is that environment. */
export function sandboxTargetProblems(env: Env): string[] {
const problems: string[] = []
if (env.SANDBOX_DB_CONTAINER !== SANDBOX_TARGET.container)
problems.push(`SANDBOX_DB_CONTAINER is not ${SANDBOX_TARGET.container}`)
for (const [name, expected] of Object.entries(SANDBOX_TARGET.urls))
if (env[name] !== expected) problems.push(`${name} is not ${expected}`)
for (const name of KEY_VARIABLES)
if (jwtIssuer(env[name]) !== SANDBOX_TARGET.issuer)
problems.push(`${name} is not signed for the issuer ${SANDBOX_TARGET.issuer}`)
return problems
}
/** Throws before any request when the environment is not the sandbox runner's. */
export function assertSandboxTarget(env: Env = process.env): void {
const problems = sandboxTargetProblems(env)
if (problems.length > 0)
throw new Error(
`Refused: this suite runs against the db:sandbox stack only (pnpm db:sandbox ...). ${problems.join('; ')}.`,
)
}
```
Before writing the constants, confirm them against `scripts/db-sandbox.sh` (`with_sandbox_env`, the port
variables, the key issuer): `grep -n 'SANDBOX_.*_URL\|iss' scripts/db-sandbox.sh`. If any URL is exported with a
trailing slash or a path, copy it exactly as exported and report the difference.
- [ ] **Step 4: run the test, expect PASS** (same command as step 2). Expected: all tests pass.
- [ ] **Step 5: wire the guard** in `packages/supabase/tests/sandbox/stack.ts`: docstring gains one sentence, the
call runs before the first `need()`:
```ts
/**
* Access to the throwaway stack of `pnpm db:sandbox` (scripts/db-sandbox.sh):
* real GoTrue, PostgREST and PostgreSQL sessions, never the DEV stack. The
* script passes the endpoints and the generated keys through the environment;
* the database password reaches `docker exec` through the environment too,
* never through a command argument. Any other target (the db:trial stack, DEV,
* a look-alike) stops the suite here, before its first request (./target.ts).
*/
import { execFile } from 'node:child_process'
import { randomBytes, randomUUID } from 'node:crypto'
import { assertSandboxTarget } from './target'
assertSandboxTarget(process.env)
```
`apps/app/e2e/playwright.shell.config.ts`: after the imports, replace the presence check by the guard and keep a
typed anon key for the web server env:
```ts
import { defineConfig } from '@playwright/test'
import { assertSandboxTarget } from '../../../packages/supabase/tests/sandbox/target'
import type { GuardOptions } from './support/browser'
import { PROJECTS } from './support/browser'
const ORIGIN = 'http://127.0.0.1:5192'
const FAKE_SUPABASE_ORIGIN = 'http://sandbox.faunosteo.invalid'
// The sandbox's exact values or nothing starts (the db:trial stack, DEV and look-alikes are refused).
assertSandboxTarget(process.env)
const anonKey = process.env.SANDBOX_ANON_KEY
if (!anonKey)
throw new Error(
'SANDBOX_ANON_KEY missing: run pnpm e2e:shell (the sandbox must be up)',
)
```
`apps/app/e2e/playwright.lan.config.ts`: the docstring names the guard; the origin is checked:
```ts
/**
* `playwright test -c e2e/playwright.lan.config.ts` (docs/development/V1-2-6_OWNER_TRIAL.md):
* the owner's trial reached through its LAN origin, which is a non-secure context like a
* phone's browser. It needs a running `pnpm db:sandbox trial` and a trial account; the
* page may reach that origin only, and the origin must be that trial's app (port 5193): the
* persistent db:trial stack is never a test target. No trace, no report: they would hold the
* session tokens and the password. Run by hand, never part of check:all.
*/
import { defineConfig } from '@playwright/test'
import type { GuardOptions } from './support/browser'
const ORIGIN = process.env.TRIAL_ORIGIN ?? 'http://192.168.1.100:5193'
// The app of pnpm db:sandbox trial only (5193): the db:trial app (5194) is never a test target.
// An unparsable origin throws here too.
if (new URL(ORIGIN).port !== '5193')
throw new Error(
'Refused: TRIAL_ORIGIN must be the app of pnpm db:sandbox trial (port 5193)',
)
```
- [ ] **Step 6: checks**
Run: `cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm exec prettier --write packages/supabase/tests/sandbox/target.ts packages/supabase/tests/sandbox-target.test.ts packages/supabase/tests/sandbox/stack.ts apps/app/e2e/playwright.shell.config.ts apps/app/e2e/playwright.lan.config.ts && pnpm typecheck && pnpm lint && pnpm test:run`
Expected: all green (the new test file inside `pnpm test:run` of `@faunosteo/supabase`). If the core-wasm
global setup reports a stale module, run `pnpm wasm:build` first (environment-free) and say so in the report.
Also: `pnpm --filter @faunosteo/app exec tsc -p e2e --noEmit` if an `e2e/tsconfig.json` exists (else state that the
e2e configs are type-checked by Playwright only).
- [ ] **Step 7: orchestrator commits** `test(supabase): sandbox suites refuse every target but the sandbox` + push.
---
### Lot A2: R13, the trial dev server targets exactly one known stack
Owner decision 2026-11-10 (plan approval condition): R13 is fixed before any execution, no longer deferred. « Ne
pas accepter une passerelle arbitraire simplement parce qu'elle utilise 127.0.0.1 … Refuser toute destination
ambiguë. Ne pas permettre de repli automatique vers la sandbox, DEV ou un autre service local. Préserver les
commandes db:sandbox trial* existantes. »
`apps/app/vite/trial.config.ts` is shared by `pnpm db:sandbox trial` and `pnpm db:trial app`. It now names its
two allowed targets in a literal table and identifies the one it serves from four values that must all agree:
| Element | `pnpm db:sandbox trial` (unchanged, A12) | `pnpm db:trial app` |
|---|---|---|
| Destination: `FAUNOSTEO_TRIAL_GATEWAY`, exact string | `http://127.0.0.1:57801` | `http://127.0.0.1:58801` |
| App port: Vite's resolved `server.port`, `strictPort` true | 5193 | 5194 |
| Configuration: `VITE_SUPABASE_URL`, exact | `http://<FAUNOSTEO_TRIAL_HOST>:5193` | `http://<FAUNOSTEO_TRIAL_HOST>:5194` |
| Key identity: `VITE_SUPABASE_ANON_KEY` payload `role`, `iss` | `anon`, `db-sandbox` | `anon`, `faunosteo-trial` |
| Service: gateway's `GET /__faunosteo_stack` body, exact | no probe (gateway of db-sandbox.sh, untouched) | `faunosteo_trial\n` |
No default, no fallback: an unknown gateway, a trailing slash, `localhost`, another port (57800, 54321, 58802…),
a key of the other stack, a missing key or a wrong role stops the server before it listens. The Vite side reads
the key's payload only (it holds no secret); `db-trial.sh` checks the signature with the stack's JWT secret before
it hands any key over (lot B1 `key_identity`). The identity endpoint is a static nginx answer of the trial gateway,
not authentication: it tells the trial gateway apart from any other local service on that port, while the exact
port, the key issuer and the container checks of `db-trial.sh` do the rest. The values db-sandbox.sh passes
(`scripts/db-sandbox.sh:59-60, 105, 446-449`: gateway 57801, `--port 5193 --strictPort`, URL `http://<host>:5193`,
anon key `iss` `db-sandbox`) are accepted unchanged; `scripts/db-sandbox.sh` is not touched by this lot.
**Files:**
- Create: `apps/app/vite/trial-targets.ts` (pure, no Node-only API: `atob`, `fetch`, `AbortSignal.timeout` exist in
both `tsconfig.json` (DOM) and `tsconfig.node.json` (`@types/node`), since the test imports it under the app config)
- Create: `apps/app/tests/unit/vite.trialTargets.test.ts` (vitest of the app: `include: tests/**/*.test.ts`)
- Create: `apps/app/tests/unit/vite.trialConfig.test.ts` (node environment: the shared config resolved by Vite under
each stack's values, and the sandbox constants tied to `scripts/db-sandbox.sh`)
- Modify: `apps/app/vite/trial.config.ts` (docstring, gateway check replaced by `trialTarget`, port guard plugin,
async config with the identity probe)
**Interfaces:**
- Produces: `TrialTarget`, `TRIAL_TARGETS`, `jwtClaims(token)`, `trialTarget(env): TrialTarget` (throws),
`appPortProblem(target, port, strictPort): string | null`, `targetPortGuard(target)` (the Vite plugin, structural
type, tested with a fake resolved config), `checkGatewayIdentity(target): Promise<void>` (throws; tested with a
stubbed `fetch`).
- Consumed by lot B1: the trial gateway answers `location = /__faunosteo_stack` with `faunosteo_trial\n`.
- [ ] **Step 1: write the failing test** `apps/app/tests/unit/vite.trialTargets.test.ts`
```ts
import { afterEach, describe, expect, it, vi } from 'vitest'
import {
appPortProblem,
checkGatewayIdentity,
jwtClaims,
targetPortGuard,
TRIAL_TARGETS,
trialTarget,
} from '../../vite/trial-targets'
const b64 = (value: unknown) =>
btoa(JSON.stringify(value))
.replace(/\+/g, '-')
.replace(/\//g, '_')
.replace(/=+$/, '')
const key = (claims: Record<string, unknown>) =>
`${b64({ alg: 'HS256', typ: 'JWT' })}.${b64(claims)}.signature`
const HOST = '192.168.1.100'
/** Exactly what scripts/db-sandbox.sh cmd_trial passes (non-regression, A12). */
const SANDBOX = {
FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:57801',
FAUNOSTEO_TRIAL_HOST: HOST,
VITE_SUPABASE_URL: `http://${HOST}:5193`,
VITE_SUPABASE_ANON_KEY: key({ role: 'anon', iss: 'db-sandbox' }),
}
/** What scripts/db-trial.sh cmd_app passes. */
const TRIAL = {
FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:58801',
FAUNOSTEO_TRIAL_HOST: HOST,
VITE_SUPABASE_URL: `http://${HOST}:5194`,
VITE_SUPABASE_ANON_KEY: key({ role: 'anon', iss: 'faunosteo-trial' }),
}
const [sandbox, trial] = TRIAL_TARGETS
describe('trial dev server target', () => {
it('holds exactly the two stacks, sandbox first', () => {
expect(TRIAL_TARGETS.map((t) => t.name)).toEqual(['sandbox', 'trial'])
})
it('accepts the values db:sandbox trial passes, unchanged, without a probe', () => {
expect(trialTarget(SANDBOX)).toBe(sandbox)
expect(sandbox.identity).toBeUndefined()
expect(appPortProblem(sandbox, 5193, true)).toBeNull()
})
it('accepts the values db:trial app passes, with the gateway identity', () => {
expect(trialTarget(TRIAL)).toBe(trial)
expect(trial.identity).toBe('faunosteo_trial\n')
expect(appPortProblem(trial, 5194, true)).toBeNull()
})
it.each([
['the DEV gateway', { ...TRIAL, FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:54321' }],
['the sandbox\'s other gateway (avatar and sheet-assets e2e)', { ...SANDBOX, FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:57800' }],
['another loopback port', { ...TRIAL, FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:58802' }],
['a trailing slash', { ...TRIAL, FAUNOSTEO_TRIAL_GATEWAY: 'http://127.0.0.1:58801/' }],
['localhost', { ...TRIAL, FAUNOSTEO_TRIAL_GATEWAY: 'http://localhost:58801' }],
['no gateway', { ...TRIAL, FAUNOSTEO_TRIAL_GATEWAY: undefined }],
['the trial gateway with the sandbox app URL', { ...TRIAL, VITE_SUPABASE_URL: `http://${HOST}:5193` }],
['the sandbox gateway with a trial key', { ...SANDBOX, VITE_SUPABASE_ANON_KEY: TRIAL.VITE_SUPABASE_ANON_KEY }],
['the trial gateway with a sandbox key', { ...TRIAL, VITE_SUPABASE_ANON_KEY: SANDBOX.VITE_SUPABASE_ANON_KEY }],
['a missing key', { ...TRIAL, VITE_SUPABASE_ANON_KEY: undefined }],
['a service key', { ...TRIAL, VITE_SUPABASE_ANON_KEY: key({ role: 'service_role', iss: 'faunosteo-trial' }) }],
['an app URL on another host', { ...TRIAL, VITE_SUPABASE_URL: 'http://10.0.0.9:5194' }],
])('refuses %s', (_label, env) => {
expect(() => trialTarget(env)).toThrow()
})
it.each([
['the trial gateway behind the sandbox app port', trial, 5193, true],
['the sandbox gateway behind the trial app port', sandbox, 5194, true],
['a port Vite may change', trial, 5194, false],
] as const)('refuses %s', (_label, target, port, strict) => {
expect(appPortProblem(target, port, strict)).not.toBeNull()
})
it('reads a JWT payload only when it is one', () => {
expect(jwtClaims(TRIAL.VITE_SUPABASE_ANON_KEY)?.iss).toBe('faunosteo-trial')
expect(jwtClaims('not-a-jwt')).toBeNull()
expect(jwtClaims(`x.${b64(['iss'])}.y`)).toBeNull()
})
})
describe('trial gateway identity probe', () => {
afterEach(() => {
vi.unstubAllGlobals()
})
it('accepts exactly faunosteo_trial and a newline, asked without redirects', async () => {
const fetch = vi.fn(async () => new Response('faunosteo_trial\n'))
vi.stubGlobal('fetch', fetch)
await expect(checkGatewayIdentity(trial)).resolves.toBeUndefined()
expect(fetch).toHaveBeenCalledWith(
'http://127.0.0.1:58801/__faunosteo_stack',
expect.objectContaining({ redirect: 'error' }),
)
})
it.each([
['a body without its newline', async () => new Response('faunosteo_trial')],
['another service', async () => new Response('other\n')],
['a 404', async () => new Response('faunosteo_trial\n', { status: 404 })],
['a redirect', async () => new Response(null, { status: 302, headers: { location: 'http://127.0.0.1:57801/' } })],
['a refused redirect', async () => Promise.reject(new TypeError('unexpected redirect'))],
['no answer', async () => Promise.reject(new TypeError('fetch failed'))],
])('refuses %s', async (_label, answer) => {
vi.stubGlobal('fetch', vi.fn(answer))
await expect(checkGatewayIdentity(trial)).rejects.toThrow(/does not answer/)
})
it('never probes the sandbox gateway (db-sandbox.sh unchanged, A12)', async () => {
const fetch = vi.fn()
vi.stubGlobal('fetch', fetch)
await expect(checkGatewayIdentity(sandbox)).resolves.toBeUndefined()
expect(fetch).not.toHaveBeenCalled()
})
})
describe('trial dev server port guard (configResolved)', () => {
const resolved = (port: number | undefined, strictPort: boolean | undefined) => ({
server: { port, strictPort },
})
it('accepts each stack on its own port with strictPort', () => {
expect(() => targetPortGuard(trial).configResolved(resolved(5194, true))).not.toThrow()
expect(() => targetPortGuard(sandbox).configResolved(resolved(5193, true))).not.toThrow()
})
it.each([
['the trial stack on the sandbox port', trial, 5193, true],
['the sandbox on the trial port', sandbox, 5194, true],
['no strictPort', trial, 5194, false],
['no port', trial, undefined, true],
] as const)('refuses %s', (_label, target, port, strict) => {
expect(() => targetPortGuard(target).configResolved(resolved(port, strict))).toThrow(/Refused/)
})
})
```
- [ ] **Step 2: run it, expect FAIL** (module missing):
`cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm --filter @faunosteo/app exec vitest run tests/unit/vite.trialTargets.test.ts`
- [ ] **Step 3: create** `apps/app/vite/trial-targets.ts`
```ts
/**
* The two stacks the trial dev server (vite/trial.config.ts) may proxy to, and nothing else
* (docs/development/TRIAL_STACK.md §3.6, R13): `pnpm db:sandbox trial` (app 5193, sandbox
* gateway 57801, keys of the issuer db-sandbox) and `pnpm db:trial app` (app 5194, trial gateway
* 58801, keys of the issuer faunosteo-trial, a gateway that names itself). The exact gateway
* string picks the target; every other value it implies must match, or the server does not
* start. No default, no fallback to another stack.
*/
export interface TrialTarget {
readonly name: 'sandbox' | 'trial'
readonly appPort: number
readonly gateway: string
readonly issuer: string
/** The exact body of the gateway's GET /__faunosteo_stack; absent: no probe (the sandbox gateway, A12). */
readonly identity?: string
}
export const TRIAL_TARGETS: readonly TrialTarget[] = [
{
name: 'sandbox',
appPort: 5193,
gateway: 'http://127.0.0.1:57801',
issuer: 'db-sandbox',
},
{
name: 'trial',
appPort: 5194,
gateway: 'http://127.0.0.1:58801',
issuer: 'faunosteo-trial',
identity: 'faunosteo_trial\n',
},
]
type Env = Readonly<Record<string, string | undefined>>
/**
* The claims of a JWT payload, or null. Identity only: no secret here, so no signature check
* (scripts/db-trial.sh checks it with the stack's secret before it hands a key to Vite).
*/
export function jwtClaims(
token: string | undefined,
): Record<string, unknown> | null {
const payload = token?.split('.')[1]
if (!payload || !/^[A-Za-z0-9_-]+$/.test(payload)) return null
try {
const base64 = payload.replace(/-/g, '+').replace(/_/g, '/')
const padded = base64.padEnd(Math.ceil(base64.length / 4) * 4, '=')
const claims: unknown = JSON.parse(atob(padded))
return typeof claims === 'object' && claims !== null && !Array.isArray(claims)
? (claims as Record<string, unknown>)
: null
} catch {
return null
}
}
/** The one target `env` names, every value it implies checked; throws otherwise. */
export function trialTarget(env: Env): TrialTarget {
const target = TRIAL_TARGETS.find(
(entry) => entry.gateway === env.FAUNOSTEO_TRIAL_GATEWAY,
)
if (!target)
throw new Error(
'FAUNOSTEO_TRIAL_GATEWAY is neither the gateway of pnpm db:sandbox trial (http://127.0.0.1:57801) nor that of pnpm db:trial app (http://127.0.0.1:58801)',
)
const claims = jwtClaims(env.VITE_SUPABASE_ANON_KEY)
if (claims?.role !== 'anon' || claims.iss !== target.issuer)
throw new Error(
`VITE_SUPABASE_ANON_KEY is not an anon key of the issuer ${target.issuer} (${target.name} stack)`,
)
const host = env.FAUNOSTEO_TRIAL_HOST ?? ''
if (!host || env.VITE_SUPABASE_URL !== `http://${host}:${target.appPort}`)
throw new Error(
`VITE_SUPABASE_URL must be http://<FAUNOSTEO_TRIAL_HOST>:${target.appPort} (${target.name} stack)`,
)
return target
}
/** Why Vite's resolved port does not fit the target, or null (the CLI's --port is resolved in). */
export function appPortProblem(
target: TrialTarget,
port: number | undefined,
strictPort: boolean | undefined,
): string | null {
if (port === target.appPort && strictPort === true) return null
return `the ${target.name} trial app runs on port ${target.appPort} with --strictPort only`
}
/** The part of Vite's resolved config the guard reads (structural: no import of Vite's types here). */
export interface ResolvedServerPort {
readonly server: { readonly port?: number; readonly strictPort?: boolean }
}
/** A Vite plugin that refuses, once the CLI is resolved in, any port but the target's. */
export function targetPortGuard(target: TrialTarget): {
name: string
configResolved(config: ResolvedServerPort): void
} {
return {
name: 'faunosteo-trial-target-port',
configResolved(config) {
const problem = appPortProblem(
target,
config.server.port,
config.server.strictPort,
)
if (problem) throw new Error(`Refused: ${problem}`)
},
}
}
/** Throws unless the target's gateway answers its identity body exactly, within 3 s. */
export async function checkGatewayIdentity(target: TrialTarget): Promise<void> {
if (target.identity === undefined) return
let body = ''
try {
const res = await fetch(`${target.gateway}/__faunosteo_stack`, {
signal: AbortSignal.timeout(3000),
redirect: 'error',
})
body = res.ok ? await res.text() : ''
} catch {
body = ''
}
if (body !== target.identity)
throw new Error(
`${target.gateway} does not answer as the ${target.name} stack's gateway: refused`,
)
}
```
- [ ] **Step 4: run the test, expect PASS** (same command as step 2).
- [ ] **Step 5: rewrite the head of** `apps/app/vite/trial.config.ts` (lines 1-28) and its default export; the
`trialHostOnly` plugin and the `server` block stay as they are, except `proxy` now targets `target.gateway`:
```ts
import { fileURLToPath } from 'node:url'
import { defineConfig, mergeConfig, type Plugin } from 'vite'
import base from '../vite.config'
import {
checkGatewayIdentity,
targetPortGuard,
trialTarget,
} from './trial-targets'
/**
* Dev server of `pnpm db:sandbox trial` (docs/development/V1-2-6_OWNER_TRIAL.md) and of
* `pnpm db:trial app` (docs/development/SUPABASE.md §5.2).
* The browser talks to this origin only: the three Supabase routes are
* forwarded to the stack's gateway on loopback, so the app itself needs no CORS (the proxied
* routes keep the stack's own CORS headers).
* The server binds the one host the calling script validated (loopback or a private address of
* this machine) and refuses any other Host header. It serves exactly one of the two stacks of
* ./trial-targets.ts: gateway, app port, app URL and anon key issuer must all name the same one,
* and the db:trial gateway must answer its identity; anything else stops it before it listens.
*/
const host = process.env.FAUNOSTEO_TRIAL_HOST ?? ''
const PRIVATE_IPV4 =
/^(127\.0\.0\.1|10(\.\d{1,3}){3}|172\.(1[6-9]|2\d|3[01])(\.\d{1,3}){2}|192\.168(\.\d{1,3}){2})$/
if (!PRIVATE_IPV4.test(host))
throw new Error(
'FAUNOSTEO_TRIAL_HOST must be 127.0.0.1 or a private IPv4 address',
)
const target = trialTarget(process.env)
const SUPABASE_ROUTES = ['/auth/v1', '/rest/v1', '/storage/v1']
const REPO = fileURLToPath(new URL('../../../', import.meta.url))
// The CLI's --port and --strictPort are part of the resolved config: checked there.
const targetPortOnly = targetPortGuard(target)
```
and the export (the identity probe runs before any listener):
```ts
export default defineConfig(async (env) => {
await checkGatewayIdentity(target)
return mergeConfig(base(env), {
plugins: [trialHostOnly, targetPortOnly],
// One validated host, never 0.0.0.0: the proxy carries the trial account's session.
server: {
host,
strictPort: true,
allowedHosts: [host],
// Same origin through the proxy: Vite adds no CORS header, and no hot-reload socket
// (its upgrade skips the middlewares above) on a manual trial.
cors: false,
ws: false,
// The LAN sees the app's own sources only: never the repository's notes, seed or builds.
fs: { allow: ['apps/app', 'packages', 'node_modules'].map((dir) => REPO + dir) },
proxy: Object.fromEntries(
SUPABASE_ROUTES.map((route) => [route, { target: target.gateway }]),
),
},
})
})
```
If `plugins: [trialHostOnly, targetPortOnly]` does not type-check against Vite's `PluginOption` (the structural
type should be assignable: `ResolvedConfig` has `server.port` and `server.strictPort`), report the exact error
rather than casting. Before editing, check that `base` is a function config (`base(env)` is what the file already calls) and that
Vite 7's `defineConfig` accepts an async function (it does: `UserConfigFnPromise`); report if either differs.
- [ ] **Step 5b: the wiring test** `apps/app/tests/unit/vite.trialConfig.test.ts`: the real shared config resolved
by Vite (`resolveConfig`, command `serve`: it loads the file, runs every `config` and `configResolved` hook, and
opens no listener) under each stack's values. `mergeConfig` types the overrides loosely, so only this proves the
wiring. No package.json `type` here: Vite loads the config as CJS in memory (vite 7.3.6 `config.js:2048-2056`,
`:36005-36009`), so there is no temporary file to report or clean; each `resolveConfig` call re-bundles and clears
the require cache, so the stubbed environment is re-read.
```ts
// @vitest-environment node
import { readFileSync } from 'node:fs'
import { fileURLToPath } from 'node:url'
import { resolveConfig } from 'vite'
import { afterEach, describe, expect, it, vi } from 'vitest'
const CONFIG = fileURLToPath(new URL('../../vite/trial.config.ts', import.meta.url))
const SANDBOX_SCRIPT = fileURLToPath(new URL('../../../../scripts/db-sandbox.sh', import.meta.url))
const ROUTES = ['/auth/v1', '/rest/v1', '/storage/v1']
const HOST = '127.0.0.1'
const b64 = (value: unknown) =>
Buffer.from(JSON.stringify(value)).toString('base64url')
const key = (iss: string) =>
`${b64({ alg: 'HS256', typ: 'JWT' })}.${b64({ role: 'anon', iss })}.signature`
function stubStack(gateway: string, appPort: number, iss: string) {
vi.stubEnv('FAUNOSTEO_TRIAL_GATEWAY', gateway)
vi.stubEnv('FAUNOSTEO_TRIAL_HOST', HOST)
vi.stubEnv('VITE_SUPABASE_URL', `http://${HOST}:${appPort}`)
vi.stubEnv('VITE_SUPABASE_ANON_KEY', key(iss))
}
const resolveOn = (port: number) =>
resolveConfig(
{ configFile: CONFIG, server: { host: HOST, port, strictPort: true } },
'serve',
)
const proxyTargets = (config: Awaited<ReturnType<typeof resolveConfig>>) =>
ROUTES.map((route) => {
const entry = config.server.proxy?.[route]
return typeof entry === 'string' ? entry : entry?.target
})
afterEach(() => {
vi.unstubAllEnvs()
vi.unstubAllGlobals()
})
describe('shared trial dev server config, as Vite resolves it', () => {
it('db:sandbox trial: proxies to 57801 on 5193, never probes', async () => {
const fetch = vi.fn()
vi.stubGlobal('fetch', fetch)
stubStack('http://127.0.0.1:57801', 5193, 'db-sandbox')
const config = await resolveOn(5193)
expect(proxyTargets(config)).toEqual(Array(3).fill('http://127.0.0.1:57801'))
expect(config.server.port).toBe(5193)
expect(fetch).not.toHaveBeenCalled()
})
it('db:trial app: proxies to 58801 on 5194 once the gateway names itself', async () => {
vi.stubGlobal('fetch', vi.fn(async () => new Response('faunosteo_trial\n')))
stubStack('http://127.0.0.1:58801', 5194, 'faunosteo-trial')
const config = await resolveOn(5194)
expect(proxyTargets(config)).toEqual(Array(3).fill('http://127.0.0.1:58801'))
})
it('refuses the trial stack on the sandbox port', async () => {
vi.stubGlobal('fetch', vi.fn(async () => new Response('faunosteo_trial\n')))
stubStack('http://127.0.0.1:58801', 5194, 'faunosteo-trial')
await expect(resolveOn(5193)).rejects.toThrow(/Refused/)
})
it('refuses a trial gateway that does not name itself', async () => {
vi.stubGlobal('fetch', vi.fn(async () => new Response('other\n')))
stubStack('http://127.0.0.1:58801', 5194, 'faunosteo-trial')
await expect(resolveOn(5194)).rejects.toThrow(/does not answer/)
})
})
describe('the sandbox values above are the ones scripts/db-sandbox.sh passes', () => {
it('keeps its trial gateway, app port, key issuer and the way it passes them to Vite', () => {
const script = readFileSync(SANDBOX_SCRIPT, 'utf8')
expect(script).toContain('PORT_TRIAL_GATEWAY=57801')
expect(script).toContain('PORT_TRIAL_APP=5193')
expect(script).toContain('iss: "db-sandbox"')
expect(script).toContain(
'FAUNOSTEO_TRIAL_GATEWAY="http://127.0.0.1:$PORT_TRIAL_GATEWAY"',
)
expect(script).toContain(
'VITE_SUPABASE_URL="http://$TRIAL_HOST:$PORT_TRIAL_APP"',
)
expect(script).toContain('--port $PORT_TRIAL_APP --strictPort')
})
})
```
- [ ] **Step 6: checks**: `cd /home/bchanot/Documents/faunosteo-v1-2 && pnpm exec prettier --write
apps/app/vite/trial-targets.ts apps/app/vite/trial.config.ts apps/app/tests/unit/vite.trialTargets.test.ts
apps/app/tests/unit/vite.trialConfig.test.ts &&
pnpm typecheck && pnpm lint && pnpm test:run && pnpm --filter @faunosteo/app exec tsc -p tsconfig.node.json
--noEmit`, all green (the last one compiles `vite/*.ts` under lib ES2022 + node types; it passes on the current
tree, checked 2026-11-10). `git diff --stat b215b89 -- scripts/db-sandbox.sh`
shows nothing from this lot. The real Vite start of either command is not run (owner brief); the first
`pnpm db:trial app` after the go, and the next `pnpm db:sandbox trial`, prove the async config and the port guard.
- [ ] **Step 7: orchestrator commits** `fix(app): trial dev server serves exactly one known stack (R13)` + push.
---
### Lot B1: db-trial.sh foundation, plan, create, start, stop, status; sandbox state guard; harness
**Files:**
- Create: `scripts/db-trial.sh` (sections 1-6 below, in this order)
- Create: `scripts/db-trial-check.sh` (harness sections H1-H4)
- Modify: `scripts/db-sandbox.sh` after line 70 (guard only, additions only)
- Modify: `package.json` scripts (one line after `"db:sandbox"`): `"db:trial": "bash scripts/db-trial.sh",`
**Interfaces:**
- Produces (used by B2, B3): `die`, `say`, `CONTAINERS`, `STOP_ORDER`, `STATE`, `SECRETS`, `CONF`, `MANIFEST`,
`BACKUPS`, `MARKER`, `LIVE_FILES`, `IMAGE_*`, `TR_PW TR_JWT_SECRET TR_ANON_KEY TR_SERVICE_KEY`, `parse_secrets
<file>`, `load_secrets`, `parse_conf <file>`, `load_conf`, `write_private <file>` (stdin → `<file>.new` 600 →
`mv`), `remove_live_files`, `KEYGEN`, `new_secrets_file <file>`, `KEYCHECK`, `key_check`, `key_identity`, `client_key`,
`require_trial_keys`, `published_exactly`, `gateway_identity`, `write_env_files`, `key_expiry`,
`daemon_ok`, `container_exists|container_running|volume_exists|network_exists <name>`, `existing_resources`,
`port_of|limits_of|image_of|mount_of <name>`, `port_listening <port>`, `ports_free <container>...`,
`valid_host <addr>`, `trial_host`, `wait_for`, `db_ready <role>`, `sb_psql [psql args]`, `run_service <name>`,
`ensure_network`, `restart_gateway`, `start_containers` (starts stopped, recreates missing), `stop_containers`,
`remove_containers`, `check_loopback`, `require_state|require_created|require_running`, `refuse_while_app_runs`,
`take_lock`, `applied_versions`, `write_manifest`, `check_manifest`, `pending_migrations`, `expected_assets`,
`uploaded_count`, `inventory_counts`, `list_backups`. Harness: `new_case`, `trial` (stdin `/dev/null`),
`trial_input` (stdin from the caller), `trial_tty <input> <args>` (pty), `pass/fail/check`, `changes`, `refused`,
`refused_early`, `secrets_absent`, `values_absent_from`, `run_line`, `expect_run`, `only_trial_names`,
`hold_port`, `release_port`.
- Every SQL text the script sends starts with a tag line `-- db-trial:<tag>` (the stub answers by tag).
- Identity checked per operation (R13, owner 2026-11-10; any failure refuses, never a fallback to another stack):
| Operation | What must hold before it acts |
|---|---|
| `app` | `faunosteo_trial_gateway` running and `docker port` exactly `8000/tcp -> 127.0.0.1:58801`; `GET http://127.0.0.1:58801/__faunosteo_stack` answers exactly `faunosteo_trial`; the anon key passes `client_key … anon`; then Vite's own checks (lot A2) |
| `assets` | the same gateway checks; the service key passes `client_key … service_role` |
| `account` | `faunosteo_trial_auth` published exactly on `127.0.0.1:58999`; the service key passes `client_key … service_role` and is the one sent |
| `create` | `faunosteo_trial_db` published exactly on `127.0.0.1:58432` before `migrate_up` |
| `migrate` | `faunosteo_trial_db` published exactly on `127.0.0.1:58432`, before any backup |
| every `write_env_files` (create, start's recreation, restore, rotations) | both keys pass `key_identity` (no expiry) before any env file is written |
| `restore <backup>` | the backup's keys pass `key_check … any` with the backup's own secret, before any change |
| `rotate-keys`, `rotate-secrets` | both keys pass `require_trial_keys` before the backup stops anything (refusal names `pnpm db:trial restore <backup>`); the backup just taken passes `check_backup` (checksums, archives, keys, image) before any key or secret changes |
| every command | DOCKER_HOST unset or `unix://…`, else the current context's endpoint `unix://…` (`require_local_daemon`); `plan` and `status` print it |
| every curl | `curl -q --noproxy '*'`: no `~/.curlrc`, no proxy variable; Vite starts with every proxy variable unset (`NODE_USE_ENV_PROXY` included); the upload tool already refuses proxies (`crates/core/src/bin/upload_sheet_assets/storage.rs:52-53`, `.proxy(None)`) |
`key_identity <key> <role>`: issuer `faunosteo-trial`, the expected role, HMAC-SHA256 valid with `TR_JWT_SECRET`
(node, key and secret through its environment, never argv), expiry not checked: an expired key never blocks
`start` or `rotate-keys`. `client_key <key> <role>` (app, assets, account) adds the expiry, with a message naming
`pnpm db:trial rotate-keys`; `status` reports expired keys. `rotate-keys` needs a created stack, running or
stopped. The `account` header is built from the checked key itself (no `service-header` file).
- [ ] **Step 1: write the harness first** (H1-H4 below) and the db-sandbox.sh guard; run
`cd /home/bchanot/Documents/faunosteo-v1-2 && bash scripts/db-trial-check.sh`; expected: FAIL lines (db-trial.sh
missing), never a docker call outside the stubs.
- [ ] **Step 2: write `scripts/db-trial.sh` sections 1-6**, `chmod 755`, add the package.json line.
- [ ] **Step 3: run the harness until `db-trial-check: ALL PASS`**; fix the script, not the expectations, unless an
expectation contradicts this plan (report each such case).
- [ ] **Step 4: positive controls** (then revert each): remove `--restart no` from `base_opts` → the create checks
FAIL; change `127.0.0.1:$host` to `0.0.0.0:$host` → FAIL; delete the SANDBOX_* loop → FAIL; delete the
db-sandbox.sh guard → FAIL; drop the `daemon_ok` dispatch line → FAIL; drop the `command -v ss` dispatch line →
"ss absent from PATH" FAILS; drop `published_exactly "$DB"` from `cmd_create` → "create: Postgres published beyond
127.0.0.1" FAILS; drop `realpath -m` from the STATE line → the state-symlink-repo, state-symlink-sandbox and state-symlink-ok cases
FAIL; drop `-q --noproxy '*'` from one `wait_service` curl → "create: every curl pinned" FAILS; drop
`require_local_daemon` from the dispatch → both endpoint cases FAIL; drop `refuse_while_app_runs` from `cmd_stop` →
"stop while port 5194 is in use" FAILS;
remove the `location = /__faunosteo_stack` line → "create: gateway names itself" FAILS. Report each failure.
If the harness stops with exit 2 (`cannot run while …`), the host holds a trial port or lacks memory: report it,
never stop a container to make room.
- [ ] **Step 5: `pnpm r5:import-equine --check`** (environment-free, compiles once) → exit 0, report its last line.
- [ ] **Step 6: orchestrator commits** `feat(supabase): db:trial foundation, plan, create, start, stop, status` +
push.
#### db-sandbox.sh guard (insert after line 70, before `load_secrets`)
```bash
# The db:trial state folder (scripts/db-trial.sh) is never the sandbox's: `down` would delete
# files there while the trial volumes keep the matching passwords.
TRIAL_STATE="$(realpath -m "${FAUNOSTEO_TRIAL_DIR:-$HOME/.cache/faunosteo-trial}")"
if [ "$(realpath -m "$STATE")" = "$TRIAL_STATE" ] || [ -e "$STATE/.faunosteo-trial-state" ]; then
die "FAUNOSTEO_SANDBOX_DIR is the db:trial state folder: refused, nothing was run"
fi
```
#### Section 1: header, constants, refusals
```bash
#!/usr/bin/env bash
# Dedicated persistent manual trial stack (docs/development/TRIAL_STACK.md, SUPABASE.md §5.2):
# Postgres, GoTrue, PostgREST, Storage, Mailpit and a gateway under the literal names
# faunosteo_trial_*, published on 127.0.0.1 only, the data in two named volumes, secrets of its
# own. Nothing starts at boot. No automated suite reaches it: this script refuses a shell that
# carries SANDBOX_* variables, and the suites accept the sandbox's exact values only.
#
# pnpm db:trial plan read-only: what create would make, ports, places, limits, refusals
# pnpm db:trial create secrets, network, volumes, services, migrations, equine_draft.sql only
# pnpm db:trial start | stop the same containers, data kept
# pnpm db:trial status read-only: containers, keys, migrations, equine sheet, accounts, backups
# pnpm db:trial migrate a backup first (A15), then the branch's new migrations
# pnpm db:trial backup cold copy of both volumes and the secrets (the stack stops a few seconds)
# pnpm db:trial restore <backup> typed confirmation, a backup first, then <backup>'s volumes and secrets
# pnpm db:trial rotate-keys new anon and service keys signed with the same JWT secret, one year
# pnpm db:trial rotate-secrets a backup first, then a new JWT secret, keys and role passwords
# pnpm db:trial app the app on http://<trial host>:5194; Ctrl+C stops it, the stack stays
# pnpm db:trial account <email> a confirmed account, password at a hidden prompt (12 characters min.)
# pnpm db:trial grant <email> <owner|admin|catalog_reader|sheet_editor|sheet_reviewer|sheet_publisher>
# pnpm db:trial assets the equine drawings through the trial gateway, then checked
# pnpm db:trial destroy inventory, typed confirmation, a backup, then containers, network, volumes
#
# State: $FAUNOSTEO_TRIAL_DIR (default ~/.cache/faunosteo-trial), 700, outside every git work tree:
# secrets.env and the services' env files (600, written whole then renamed), stack.conf (images),
# migrations.sha256, backups/ (never pruned here). No secret is printed, logged, written to the
# repository or put on a command line: containers read env files, tools their environment, psql its
# standard input. Never `docker inspect` a trial container without -f: it would print the env files.
# Typed confirmations need a terminal. Every command but plan needs a reachable Docker daemon.
set -euo pipefail
cd "$(dirname "$0")/.."
# shellcheck source=scripts/dev-env.sh
. scripts/dev-env.sh
# Pinned here, apart from the sandbox: an image change on this persistent stack is a deliberate
# step. create records the images in stack.conf; every later recreation reuses the recorded ones.
IMAGE_DB=public.ecr.aws/supabase/postgres:17.11.0.004
IMAGE_AUTH=public.ecr.aws/supabase/gotrue:v2.197.0
IMAGE_REST=public.ecr.aws/supabase/postgrest:v16.4
IMAGE_STORAGE=public.ecr.aws/supabase/storage-api:v1.79.36
IMAGE_MAIL=public.ecr.aws/supabase/mailpit:v1.31.3
IMAGE_GATEWAY=nginx:1.31-alpine
NET=faunosteo_trial_net
DB=faunosteo_trial_db
MAIL=faunosteo_trial_mail
AUTH=faunosteo_trial_auth
STORAGE=faunosteo_trial_storage
REST=faunosteo_trial_rest
GATEWAY=faunosteo_trial_gateway
HELPER=faunosteo_trial_helper
VOL_DB=faunosteo_trial_db
VOL_STORAGE=faunosteo_trial_storage
CONTAINERS=("$DB" "$MAIL" "$AUTH" "$STORAGE" "$REST" "$GATEWAY")
STOP_ORDER=("$GATEWAY" "$REST" "$STORAGE" "$AUTH" "$MAIL" "$DB")
PORT_DB=58432 PORT_AUTH=58999 PORT_REST=58300 PORT_STORAGE=58500 PORT_MAIL=58025 PORT_GATEWAY=58801
PORT_APP=5194
# The six caps add up to 1 920 MiB (TRIAL_STACK.md §3.7); about 650 MiB are used in practice.
MEMORY_FLOOR_MIB=2048
SEED=supabase/seed/anatomical-sheets/equine/draft
HISTORICAL_SHEET=503dc301-a8b1-50d4-9e44-0919b2fc2916
CLI=node_modules/.bin/supabase
# Canonical from the start (symlinks resolved): the guard, every mode check and every state path
# below read the real folder, never a link (whose own mode stat would report as 777).
STATE="$(realpath -m "${FAUNOSTEO_TRIAL_DIR:-$HOME/.cache/faunosteo-trial}")"
MARKER="$STATE/.faunosteo-trial-state"
SECRETS="$STATE/secrets.env"
CONF="$STATE/stack.conf"
MANIFEST="$STATE/migrations.sha256"
BACKUPS="$STATE/backups"
# Everything in the state folder but the marker, the lock and backups/: what destroy (and a create
# that died before its first resource) removes.
LIVE_FILES=(secrets.env secrets.next.env db.env auth.env storage.env rest.env stack.conf migrations.sha256)
JWT_RE='^[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+$'
die() { echo "db-trial: $*" >&2; exit 1; }
say() { echo "== $*"; }
usage() { grep '^# pnpm db:trial' "$0" | cut -c2-; }
# Refusals that come before anything else, whatever the command (TRIAL_STACK.md §3.6).
guard_environment() {
local name repo state sandbox parent
for name in $(compgen -e); do
case "$name" in
SANDBOX_*) die "$name is set: this shell is wired to the sandbox suites; open a clean shell (nothing was run)" ;;
esac
done
repo="$(realpath "$PWD")"
state="$STATE"
sandbox="$(realpath -m "${FAUNOSTEO_SANDBOX_DIR:-$HOME/.cache/faunosteo-db-sandbox}")"
case "$state" in
"$repo" | "$repo"/*) die "FAUNOSTEO_TRIAL_DIR must be outside the repository" ;;
esac
# Any git work tree, not only this one (the other checkout, a dotfiles repository): the secrets
# and backups would sit one `git add` away from a commit.
parent="$state"
while [ ! -e "$parent" ]; do parent="$(dirname "$parent")"; done
if git -C "$parent" rev-parse --is-inside-work-tree >/dev/null 2>&1; then
die "FAUNOSTEO_TRIAL_DIR must be outside every git work tree: refused"
fi
[ "$state" != "$sandbox" ] || die "FAUNOSTEO_TRIAL_DIR is the sandbox's state folder: refused"
if [ -e "$STATE" ] && [ ! -e "$MARKER" ] && [ -n "$(ls -A "$STATE" 2>/dev/null || echo unreadable)" ]; then
die "$STATE is not empty and holds no db:trial state: refused"
fi
}
check_private() { # path mode
[ "$(stat -c %u "$1")" = "$(id -u)" ] && [ "$(stat -c %a "$1")" = "$2" ] \
|| die "$1 must belong to $(id -un) with mode $2: refused"
}
```
#### Section 2: state files (parsed, never sourced)
```bash
# KEY=value lines, read without `source`: only these keys, each value checked against its shape.
parse_secrets() { # file → TR_PW TR_JWT_SECRET TR_ANON_KEY TR_SERVICE_KEY
local key value
TR_PW='' TR_JWT_SECRET='' TR_ANON_KEY='' TR_SERVICE_KEY=''
while IFS='=' read -r key value; do
case "$key" in
TR_PW) if [[ "$value" =~ ^[0-9a-f]{48}$ ]]; then TR_PW="$value"; fi ;;
TR_JWT_SECRET) if [[ "$value" =~ ^[0-9a-f]{64}$ ]]; then TR_JWT_SECRET="$value"; fi ;;
TR_ANON_KEY) if [[ "$value" =~ $JWT_RE ]]; then TR_ANON_KEY="$value"; fi ;;
TR_SERVICE_KEY) if [[ "$value" =~ $JWT_RE ]]; then TR_SERVICE_KEY="$value"; fi ;;
esac
done < "$1"
[ -n "$TR_PW" ] && [ -n "$TR_JWT_SECRET" ] && [ -n "$TR_ANON_KEY" ] && [ -n "$TR_SERVICE_KEY" ]
}
load_secrets() {
[ -f "$SECRETS" ] || die "not created: no $SECRETS (pnpm db:trial plan, then create)"
check_private "$SECRETS" 600
parse_secrets "$SECRETS" || die "secrets.env is incomplete or malformed: nothing was run (pnpm db:trial restore <backup>)"
}
# A state file is written whole under <file>.new (600) then renamed: an interrupted write never
# leaves half a file behind the real name. Content on stdin (printf is a builtin: no argv).
write_private() { # file
( umask 077; cat > "$1.new" ) && mv -f "$1.new" "$1"
}
remove_live_files() { # never backups/, the marker or the lock
local f
for f in "${LIVE_FILES[@]}"; do rm -f -- "$STATE/$f" "$STATE/$f.new"; done
}
# The six IMAGE_* recorded at create. The app host is not recorded: `app` decides it at each run.
parse_conf() { # file
local key value seen=0
while IFS='=' read -r key value; do
case "$key" in
IMAGE_DB | IMAGE_AUTH | IMAGE_REST | IMAGE_STORAGE | IMAGE_MAIL | IMAGE_GATEWAY)
if [[ "$value" =~ ^[a-z0-9./_-]+:[A-Za-z0-9._-]+$ ]]; then printf -v "$key" '%s' "$value"; seen=$((seen + 1)); fi ;;
esac
done < "$1"
[ "$seen" -eq 6 ]
}
load_conf() {
[ -f "$CONF" ] || die "not created: no $CONF (pnpm db:trial plan, then create)"
check_private "$CONF" 600
parse_conf "$CONF" || die "stack.conf is incomplete or malformed: nothing was run (pnpm db:trial restore <backup>)"
}
write_conf() {
printf 'IMAGE_DB=%s\nIMAGE_AUTH=%s\nIMAGE_REST=%s\nIMAGE_STORAGE=%s\nIMAGE_MAIL=%s\nIMAGE_GATEWAY=%s\n' \
"$IMAGE_DB" "$IMAGE_AUTH" "$IMAGE_REST" "$IMAGE_STORAGE" "$IMAGE_MAIL" "$IMAGE_GATEWAY" | write_private "$CONF"
}
# Fresh values (`new`) or new keys for the current secret (`keys`, the secret in TR_JWT_SECRET),
# as KEY=value lines for a 600 file, never for the terminal. Keys: issuer faunosteo-trial (the
# sandbox suites refuse it), one year, a random jti so every rotation yields new keys.
KEYGEN='
const c = require("crypto");
const fresh = process.argv[1] === "new";
const secret = fresh ? c.randomBytes(32).toString("hex") : process.env.TR_JWT_SECRET;
if (!/^[0-9a-f]{64}$/.test(secret || "")) process.exit(2);
const b64 = (o) => Buffer.from(JSON.stringify(o)).toString("base64url");
const jwt = (p) => { const h = b64({ alg: "HS256", typ: "JWT" }), b = b64(p);
return h + "." + b + "." + c.createHmac("sha256", secret).update(h + "." + b).digest("base64url"); };
const iat = Math.floor(Date.now() / 1000), exp = iat + 365 * 86400;
if (fresh) console.log("TR_PW=" + c.randomBytes(24).toString("hex") + "\nTR_JWT_SECRET=" + secret);
for (const [name, role] of [["TR_ANON_KEY", "anon"], ["TR_SERVICE_KEY", "service_role"]])
console.log(name + "=" + jwt({ role, iss: "faunosteo-trial", iat, exp, jti: c.randomUUID() }));
'
new_secrets_file() { ( umask 077; node -e "$KEYGEN" new > "$1" ); }
# A key is handed over (Vite, the upload tool, GoTrue's admin API, Storage's env) only when it is
# this stack's: issuer faunosteo-trial, the role asked, not expired, signed with TR_JWT_SECRET.
# Exit 0: this stack's key; 1: not this stack's (issuer, role, signature); 3 (mode fresh only):
# this stack's but expired. Identity and expiry are apart on purpose: an expired key must never
# block start or rotate-keys (the way to renew it), only its use by a client.
KEYCHECK='
const c = require("crypto");
const [role, mode] = process.argv.slice(1);
const [h, b, s] = (process.env.KEY || "").split(".");
let p = null;
try { p = JSON.parse(Buffer.from(b, "base64url").toString("utf8")); } catch { process.exit(1); }
const mac = c.createHmac("sha256", process.env.SECRET || "").update(h + "." + b).digest("base64url");
const own = p !== null && typeof p === "object" && s === mac && p.iss === "faunosteo-trial"
&& p.role === role && typeof p.exp === "number";
if (!own) process.exit(1);
process.exit(mode === "fresh" && p.exp <= Date.now() / 1000 ? 3 : 0);
'
key_check() { # key role any|fresh [secret]: the status of KEYCHECK
KEY="$1" SECRET="${4:-$TR_JWT_SECRET}" node -e "$KEYCHECK" "$2" "$3"
}
key_identity() { # key role: this stack's key, expired or not (env files, Storage)
key_check "$1" "$2" any \
|| die "the $2 key in secrets.env is not this stack's (issuer faunosteo-trial, signed with its secret): refused, nothing was done (pnpm db:trial restore <backup>)"
}
client_key() { # key role: what a client receives (Vite, the upload tool, GoTrue's admin API) is also valid now
local status=0
key_check "$1" "$2" fresh || status=$?
case "$status" in
0) ;;
3) die "the $2 key expired: pnpm db:trial rotate-keys renews it; nothing was done" ;;
*) die "the $2 key in secrets.env is not this stack's (issuer faunosteo-trial, signed with its secret): refused, nothing was done (pnpm db:trial restore <backup>)" ;;
esac
}
require_trial_keys() {
key_identity "$TR_ANON_KEY" anon
key_identity "$TR_SERVICE_KEY" service_role
}
key_expiry() {
TR_ANON_KEY="$TR_ANON_KEY" node -e '
const p = JSON.parse(Buffer.from(process.env.TR_ANON_KEY.split(".")[1], "base64url"));
console.log(new Date(p.exp * 1000).toISOString().slice(0, 10));'
}
# The values reach the containers through 600 env files, never through argv. Always derived from
# secrets.env, so they can be rewritten at any time (start rewrites them before recreating a
# missing container). Both keys are this stack's first.
write_env_files() {
require_trial_keys
printf 'POSTGRES_PASSWORD=%s\nPGPASSWORD=%s\nJWT_SECRET=%s\n' "$TR_PW" "$TR_PW" "$TR_JWT_SECRET" \
| write_private "$STATE/db.env"
printf 'GOTRUE_DB_DATABASE_URL=postgresql://supabase_auth_admin:%s@%s:5432/postgres\nGOTRUE_JWT_SECRET=%s\n' \
"$TR_PW" "$DB" "$TR_JWT_SECRET" | write_private "$STATE/auth.env"
printf 'ANON_KEY=%s\nSERVICE_KEY=%s\nAUTH_JWT_SECRET=%s\nDATABASE_URL=postgresql://supabase_storage_admin:%s@%s:5432/postgres\n' \
"$TR_ANON_KEY" "$TR_SERVICE_KEY" "$TR_JWT_SECRET" "$TR_PW" "$DB" | write_private "$STATE/storage.env"
printf 'PGRST_DB_URI=postgresql://authenticator:%s@%s:5432/postgres\nPGRST_JWT_SECRET=%s\n' \
"$TR_PW" "$DB" "$TR_JWT_SECRET" | write_private "$STATE/rest.env"
}
```
#### Section 3: host checks, Docker helpers, services
```bash
# The daemon this script talks to is the local one: DOCKER_HOST unset or a unix socket, else the
# current context's endpoint a unix socket (a named, non-secret field of the context).
daemon_endpoint() {
if [ -n "${DOCKER_HOST:-}" ]; then printf '%s' "$DOCKER_HOST"
else docker context inspect -f '{{.Endpoints.docker.Host}}' 2>/dev/null; fi
}
require_local_daemon() {
local endpoint
endpoint="$(daemon_endpoint)" || die "the current Docker context cannot be read: refused, nothing was run"
case "$endpoint" in
unix://?*) ;;
*) die "the Docker endpoint '$endpoint' is not a local unix socket (DOCKER_HOST or the current context): refused, nothing was run" ;;
esac
}
# Checked at dispatch for every command but plan: past it, "absent" means absent, not "daemon down".
daemon_ok() { docker info --format '{{.ServerVersion}}' >/dev/null 2>&1; }
# Always -f with a named field: a bare inspect prints the containers' env-file values.
container_exists() { docker container inspect -f '{{.Name}}' "$1" >/dev/null 2>&1; }
container_running() { [ "$(docker container inspect -f '{{.State.Running}}' "$1" 2>/dev/null)" = true ]; }
volume_exists() { docker volume inspect -f '{{.Name}}' "$1" >/dev/null 2>&1; }
network_exists() { docker network inspect -f '{{.Name}}' "$1" >/dev/null 2>&1; }
existing_resources() { # the literal names of this stack that exist now, one per line
local name
for name in "${CONTAINERS[@]}"; do if container_exists "$name"; then echo "container $name"; fi; done
if network_exists "$NET"; then echo "network $NET"; fi
for name in "$VOL_DB" "$VOL_STORAGE"; do if volume_exists "$name"; then echo "volume $name"; fi; done
}
port_of() { # name → host port, container port
case "$1" in
"$DB") echo "$PORT_DB 5432" ;; "$MAIL") echo "$PORT_MAIL 8025" ;; "$AUTH") echo "$PORT_AUTH 9999" ;;
"$STORAGE") echo "$PORT_STORAGE 5000" ;; "$REST") echo "$PORT_REST 3000" ;; "$GATEWAY") echo "$PORT_GATEWAY 8000" ;;
esac
}
limits_of() { # name → memory, pids (TRIAL_STACK.md §3.7; --cpus 1 for all)
case "$1" in
"$DB") echo "1g 512" ;; "$REST") echo "384m 256" ;; "$STORAGE") echo "256m 256" ;;
"$AUTH") echo "128m 256" ;; "$MAIL" | "$GATEWAY") echo "64m 64" ;;
esac
}
image_of() {
case "$1" in
"$DB") echo "$IMAGE_DB" ;; "$MAIL") echo "$IMAGE_MAIL" ;; "$AUTH") echo "$IMAGE_AUTH" ;;
"$STORAGE") echo "$IMAGE_STORAGE" ;; "$REST") echo "$IMAGE_REST" ;; "$GATEWAY") echo "$IMAGE_GATEWAY" ;;
esac
}
mount_of() {
case "$1" in
"$DB") echo "$VOL_DB:/var/lib/postgresql/data" ;; "$STORAGE") echo "$VOL_STORAGE:/mnt" ;; *) echo - ;;
esac
}
# What every service container shares: its name, the stack network, no restart at boot, its caps
# and its one loopback port.
base_opts() { # name → one option per line
local host inner memory pids
read -r host inner <<<"$(port_of "$1")"
read -r memory pids <<<"$(limits_of "$1")"
printf '%s\n' --name "$1" --network "$NET" --restart no --memory "$memory" --cpus 1 \
--pids-limit "$pids" -p "127.0.0.1:$host:$inner"
}
# True when any local address listens on the port (rootless Docker's forwarders show in ss too,
# checked 2026-11-10). Fails closed: no ss answer, no command goes on.
port_listening() { # port
local out status=0
out="$(ss -Hltn "sport = :$1" 2>&1)" || status=$?
# Fail closed: an ss error is never read as "nothing listens".
[ "$status" -eq 0 ] || die "ss cannot list the listening ports (exit $status): nothing was done"
[ -n "$out" ]
}
ports_free() { # container...: each one's host port, all checked before any docker call
local name host inner
for name in "$@"; do
read -r host inner <<<"$(port_of "$name")"
if port_listening "$host"; then die "port 127.0.0.1:$host is taken: nothing was started or changed (pnpm db:trial plan)"; fi
done
}
available_mib() { awk '/^MemAvailable:/ { print int($2 / 1024) }' /proc/meminfo; }
memory_ok() {
local mib
mib="$(available_mib)"
[ "$mib" -ge "$MEMORY_FLOOR_MIB" ] || die "only $mib MiB of memory available, the stack's caps need $MEMORY_FLOOR_MIB: nothing was started"
}
# The trial app host: loopback or a private IPv4 of an interface of this machine, nothing else
# (same rule as db-sandbox.sh trial_host). Prints the normalised address.
valid_host() { # address
local host="$1" a b c d
[[ "$host" =~ ^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})$ ]] \
|| die "FAUNOSTEO_TRIAL_HOST must be an IPv4 address (not a name, not 0.0.0.0): $host"
a=$((10#${BASH_REMATCH[1]})) b=$((10#${BASH_REMATCH[2]}))
c=$((10#${BASH_REMATCH[3]})) d=$((10#${BASH_REMATCH[4]}))
[ "$a" -le 255 ] && [ "$b" -le 255 ] && [ "$c" -le 255 ] && [ "$d" -le 255 ] \
|| die "FAUNOSTEO_TRIAL_HOST is not an IPv4 address: $host"
[ "$host" = "$a.$b.$c.$d" ] || die "FAUNOSTEO_TRIAL_HOST: write the address without leading zeros"
if [ "$a" -eq 127 ]; then host=127.0.0.1
elif [ "$a" -eq 10 ] || { [ "$a" -eq 172 ] && [ "$b" -ge 16 ] && [ "$b" -le 31 ]; } \
|| { [ "$a" -eq 192 ] && [ "$b" -eq 168 ]; }; then :
else die "FAUNOSTEO_TRIAL_HOST must be loopback or a private address (10/8, 172.16/12, 192.168/16): $host"
fi
ip -4 -o addr | grep -qF "inet $host/" || die "FAUNOSTEO_TRIAL_HOST $host is not an address of this machine"
printf '%s' "$host"
}
# Decided at each use, never recorded: a DHCP change of the LAN address needs no state edit. Call
# it as `host="$(trial_host)"` (a plain assignment, so a refusal stops the script).
trial_host() { valid_host "${FAUNOSTEO_TRIAL_HOST:-$FAUNOSTEO_DEV_HOST}"; }
wait_for() { # what command...
local what="$1"
shift
for _ in $(seq 1 120); do
if "$@" >/dev/null 2>&1; then return 0; fi
sleep 1
done
die "timeout waiting for $what"
}
# psql inside the database container; the password reaches it through the environment.
db_ready() { # role
PGPASSWORD="$TR_PW" docker exec -e PGPASSWORD "$DB" psql -X -h 127.0.0.1 -U "$1" -d postgres -c 'select 1'
}
sb_psql() {
PGPASSWORD="$TR_PW" docker exec -i -e PGPASSWORD "$DB" \
psql -X -q -h 127.0.0.1 -U postgres -d postgres -v ON_ERROR_STOP=1 "$@"
}
# The role passwords the CLI sets at its first database start (/etc/postgresql.schema.sql, run
# once on an empty data folder), for the roles this stack uses, supabase_admin included so that
# rotate-secrets can renew every password through it. A failing ALTER would log its statement,
# password included, at the default log_min_error_statement: the session turns that off first.
# shellcheck disable=SC2016
DB_INIT='cat <<'"'"'EOF'"'"' > /etc/postgresql.schema.sql && exec docker-entrypoint.sh postgres -D /etc/postgresql -c shared_buffers=128MB
set log_min_error_statement = panic;
\set pgpass `echo "$PGPASSWORD"`
\set jwt_secret `echo "$JWT_SECRET"`
ALTER DATABASE postgres SET "app.settings.jwt_secret" TO :'"'"'jwt_secret'"'"';
ALTER DATABASE postgres SET "app.settings.jwt_exp" TO '"'"'3600'"'"';
ALTER USER supabase_admin WITH PASSWORD :'"'"'pgpass'"'"';
ALTER USER postgres WITH PASSWORD :'"'"'pgpass'"'"';
ALTER USER authenticator WITH PASSWORD :'"'"'pgpass'"'"';
ALTER USER supabase_auth_admin WITH PASSWORD :'"'"'pgpass'"'"';
ALTER USER supabase_storage_admin WITH PASSWORD :'"'"'pgpass'"'"';
EOF'
# The CLI stack's Kong routes on the trial network. GoTrue's admin API is not routed: the account
# command calls GoTrue on its loopback port, so the app's LAN proxy never reaches it. The static
# /__faunosteo_stack answer names this gateway (R13: app, assets and the trial Vite config check
# it); it carries no data, and Vite proxies only the three Supabase routes, so the LAN never sees it.
GATEWAY_CONF='server {
listen 8000;
client_max_body_size 8m;
proxy_http_version 1.1;
location = /__faunosteo_stack { default_type text/plain; return 200 "faunosteo_trial\n"; }
location /auth/v1/admin/ { return 403; }
location /auth/v1/ { proxy_pass http://'"$AUTH"':9999/; }
location /rest/v1/ { proxy_pass http://'"$REST"':3000/; }
location /storage/v1/ { proxy_pass http://'"$STORAGE"':5000/; }
location / { return 404; }
}'
# Every curl of this script: -q first (no ~/.curlrc: no proxy, connect-to or resolve from it) and
# --noproxy '*' (no http_proxy / ALL_PROXY): the destination is the literal 127.0.0.1 port, always.
wait_service() { # name
case "$1" in
"$DB")
wait_for "database roles" db_ready supabase_auth_admin
wait_for "database" db_ready postgres ;;
"$MAIL") wait_for "mailpit" curl -q --noproxy '*' -fsS -o /dev/null "http://127.0.0.1:$PORT_MAIL/" ;;
"$AUTH") wait_for "auth" curl -q --noproxy '*' -fsS -o /dev/null "http://127.0.0.1:$PORT_AUTH/health" ;;
"$STORAGE") wait_for "storage" curl -q --noproxy '*' -fsS -o /dev/null "http://127.0.0.1:$PORT_STORAGE/status" ;;
"$REST") wait_for "postgrest" curl -q --noproxy '*' -fsS -o /dev/null "http://127.0.0.1:$PORT_REST/" ;;
"$GATEWAY") wait_for "gateway" curl -q --noproxy '*' -fsS -o /dev/null "http://127.0.0.1:$PORT_GATEWAY/rest/v1/" ;;
esac
}
run_db() {
local opts
mapfile -t opts < <(base_opts "$DB")
docker run -d "${opts[@]}" -v "$(mount_of "$DB")" --env-file "$STATE/db.env" \
-e POSTGRES_HOST=/var/run/postgresql -e JWT_EXP=3600 \
-e POSTGRES_INITDB_ARGS="--allow-group-access --locale-provider=icu --encoding=UTF-8 --icu-locale=en_US.UTF-8" \
--entrypoint sh "$IMAGE_DB" -c "$DB_INIT" >/dev/null
}
run_mail() {
local opts
mapfile -t opts < <(base_opts "$MAIL")
docker run -d "${opts[@]}" "$IMAGE_MAIL" >/dev/null
}
# Sign-up closed (accounts come from `account` only); mail links lead to the app origin of the
# trial host at the time GoTrue starts (create, restore, rotate-secrets, a recreation by start),
# whose proxy forwards /auth/v1 to GoTrue.
run_auth() {
local opts host origin
host="$(trial_host)"
origin="http://$host:$PORT_APP"
mapfile -t opts < <(base_opts "$AUTH")
docker run -d "${opts[@]}" --env-file "$STATE/auth.env" \
-e GOTRUE_API_HOST=0.0.0.0 -e GOTRUE_API_PORT=9999 -e API_EXTERNAL_URL="$origin/auth/v1" \
-e GOTRUE_DB_DRIVER=postgres -e GOTRUE_DB_MIGRATIONS_PATH=/usr/local/etc/auth/migrations \
-e GOTRUE_SITE_URL="$origin" -e GOTRUE_URI_ALLOW_LIST="$origin/**" -e GOTRUE_DISABLE_SIGNUP=true \
-e GOTRUE_JWT_EXP=3600 -e GOTRUE_JWT_AUD=authenticated -e GOTRUE_JWT_DEFAULT_GROUP_NAME=authenticated \
-e GOTRUE_JWT_ADMIN_ROLES=service_role \
-e GOTRUE_EXTERNAL_EMAIL_ENABLED=true -e GOTRUE_EXTERNAL_PHONE_ENABLED=false \
-e GOTRUE_EXTERNAL_ANONYMOUS_USERS_ENABLED=false -e GOTRUE_MAILER_AUTOCONFIRM=false \
-e GOTRUE_MAILER_URLPATHS_CONFIRMATION="$origin/auth/v1/verify" \
-e GOTRUE_SMTP_HOST="$MAIL" -e GOTRUE_SMTP_PORT=1025 \
-e GOTRUE_SMTP_ADMIN_EMAIL=admin@trial.local -e GOTRUE_SMTP_SENDER_NAME=faunosteo-trial \
-e GOTRUE_PASSWORD_MIN_LENGTH=12 "$IMAGE_AUTH" >/dev/null
}
run_storage() {
local opts
mapfile -t opts < <(base_opts "$STORAGE")
docker run -d "${opts[@]}" -v "$(mount_of "$STORAGE")" --env-file "$STATE/storage.env" \
-e FILE_SIZE_LIMIT=52428800 -e STORAGE_BACKEND=file -e FILE_STORAGE_BACKEND_PATH=/mnt \
-e TENANT_ID=stub -e STORAGE_S3_REGION=local -e GLOBAL_S3_BUCKET=stub \
-e ENABLE_IMAGE_TRANSFORMATION=false -e IMAGE_TRANSFORMATION_ENABLED=false \
-e S3_PROTOCOL_ENABLED=false -e DB_MIGRATIONS_FREEZE_AT= "$IMAGE_STORAGE" >/dev/null
}
run_rest() {
local opts
mapfile -t opts < <(base_opts "$REST")
docker run -d "${opts[@]}" --env-file "$STATE/rest.env" \
-e PGRST_DB_SCHEMAS=public -e PGRST_DB_EXTRA_SEARCH_PATH=public,extensions \
-e PGRST_DB_ANON_ROLE=anon -e PGRST_DB_MAX_ROWS=1000 "$IMAGE_REST" >/dev/null
}
run_gateway() {
local opts
mapfile -t opts < <(base_opts "$GATEWAY")
# "$0" is the inner shell's, expanded in the container:
# shellcheck disable=SC2016
docker run -d "${opts[@]}" --security-opt no-new-privileges:true \
--entrypoint sh "$IMAGE_GATEWAY" -c 'printf "%s\n" "$0" > /etc/nginx/conf.d/default.conf && exec nginx -g "daemon off;"' \
"$GATEWAY_CONF" >/dev/null
}
run_service() { # name: run, then wait until it answers
case "$1" in
"$DB") run_db ;; "$MAIL") run_mail ;; "$AUTH") run_auth ;;
"$STORAGE") run_storage ;; "$REST") run_rest ;; "$GATEWAY") run_gateway ;;
esac
wait_service "$1"
}
published_exactly() { # name: its one port, on 127.0.0.1, and nothing else
local host inner
read -r host inner <<<"$(port_of "$1")"
[ "$(docker port "$1" 2>/dev/null)" = "$inner/tcp -> 127.0.0.1:$host" ] \
|| die "$1 is not published on 127.0.0.1:$host only: refused (pnpm db:trial stop, then check Docker)"
}
check_loopback() {
local name
for name in "${CONTAINERS[@]}"; do published_exactly "$name"; done
}
# The gateway a tool or the app is pointed at is this stack's container, on its port, answering
# its name; never another local service on 58801.
STACK_IDENTITY=faunosteo_trial
gateway_identity() {
local body
published_exactly "$GATEWAY"
body="$(curl -q --noproxy '*' -fsS --max-time 3 "http://127.0.0.1:$PORT_GATEWAY/__faunosteo_stack" 2>/dev/null)" || body=''
[ "$body" = "$STACK_IDENTITY" ] \
|| die "127.0.0.1:$PORT_GATEWAY does not answer as the faunosteo_trial gateway: refused, nothing was done"
}
ensure_network() {
if ! network_exists "$NET"; then docker network create --label faunosteo.stack=trial "$NET" >/dev/null; fi
}
# nginx resolves the services' names once, at its start: a service started or recreated under a
# running gateway means a gateway restart.
restart_gateway() {
docker restart -t 10 "$GATEWAY" >/dev/null
wait_service "$GATEWAY"
}
# Every port of a stopped or missing container is checked before the first start. A missing
# container is recreated from stack.conf and secrets.env (env files rewritten first), so a failed
# recreation (rotate-keys, restore, a daemon hiccup) is repaired by `start`, never by a restore.
start_containers() {
local name todo=() missing=no
for name in "${CONTAINERS[@]}"; do
if container_running "$name"; then continue; fi
todo+=("$name")
if ! container_exists "$name"; then missing=yes; fi
done
ports_free "${todo[@]}"
if [ "$missing" = yes ]; then write_env_files; ensure_network; fi
for name in "${todo[@]}"; do
if container_exists "$name"; then
docker start "$name" >/dev/null
wait_service "$name"
else
run_service "$name"
fi
done
# CONTAINERS ends with the gateway: when it was not started last, it ran over the others.
if [ "${#todo[@]}" -gt 0 ] && [ "${todo[-1]}" != "$GATEWAY" ]; then restart_gateway; fi
check_loopback
}
stop_containers() {
local name
for name in "${STOP_ORDER[@]}"; do
if container_running "$name"; then docker stop -t 60 "$name" >/dev/null; fi
done
}
remove_containers() { # stopped first by the caller; volumes untouched
local name
for name in "${CONTAINERS[@]}"; do
if container_exists "$name"; then docker rm "$name" >/dev/null; fi
done
}
require_state() {
[ -e "$MARKER" ] || die "no db:trial state in $STATE (pnpm db:trial plan, then create)"
check_private "$STATE" 700
}
# The data volume is the stack: containers can be recreated (start), the volume cannot.
require_created() {
require_state
load_conf
load_secrets
volume_exists "$VOL_DB" || die "$VOL_DB does not exist (pnpm db:trial status)"
}
require_running() {
local name
require_created
for name in "${CONTAINERS[@]}"; do
container_running "$name" || die "$name is not running (pnpm db:trial start)"
done
}
# Before the commands that change keys or data under a running app (rotate-keys, rotate-secrets,
# restore, destroy) and before stop (the app would proxy to whatever then answers 58801). A local
# listener check: no probe of a LAN address.
refuse_while_app_runs() {
if port_listening "$PORT_APP"; then
die "port $PORT_APP is in use (the trial app, or another process): stop it first; nothing was changed"
fi
}
take_lock() {
exec 9>"$STATE/.lock"
flock -n 9 || die "another pnpm db:trial command is running"
}
```
#### Section 4: migrations and the equine seed
```bash
db_url() { printf 'postgresql://postgres@127.0.0.1:%s/postgres?sslmode=disable' "$PORT_DB"; }
# The CLI's own runner, always against the trial URL; the password through PGPASSWORD only.
migrate_up() { PGPASSWORD="$TR_PW" "$CLI" --workdir . migration up --db-url "$(db_url)"; }
applied_versions() {
sb_psql -t -A -c "-- db-trial:applied
select version from supabase_migrations.schema_migrations order by version"
}
file_version() { basename "$1" | cut -d_ -f1; }
file_sum() { sha256sum < "$1" | cut -d' ' -f1; }
# One line per applied migration: version, sha256 of its file, file name. A persistent stack
# replays nothing, so an applied file edited later must be noticed (TRIAL_STACK.md §3.4).
write_manifest() {
local applied f
applied="$(applied_versions)"
( umask 077
for f in supabase/migrations/*.sql; do
if grep -qx "$(file_version "$f")" <<<"$applied"; then
printf '%s %s %s\n' "$(file_version "$f")" "$(file_sum "$f")" "$(basename "$f")"
fi
done > "$MANIFEST.new" )
[ "$(cut -d' ' -f1 "$MANIFEST.new")" = "$applied" ] \
|| die "an applied migration has no file in this branch: migrations.sha256 not written"
mv -f "$MANIFEST.new" "$MANIFEST"
}
check_manifest() {
local version sum file
[ -f "$MANIFEST" ] || die "migrations.sha256 missing: the applied migrations cannot be checked (pnpm db:trial restore <backup>)"
while read -r version sum file; do
[[ "$version" =~ ^[0-9]{14}$ && "$sum" =~ ^[0-9a-f]{64}$ && "$file" =~ ^[0-9]{14}_[A-Za-z0-9_]+\.sql$ ]] \
|| die "migrations.sha256 is malformed"
[ -f "supabase/migrations/$file" ] || die "applied migration $file is missing from this branch: refused"
[ "$(file_sum "supabase/migrations/$file")" = "$sum" ] \
|| die "applied migration $file was edited after it was applied: refused (migrations are forward-only)"
done < "$MANIFEST"
[ "$(cut -d' ' -f1 "$MANIFEST")" = "$(applied_versions)" ] \
|| die "the database's migration history differs from migrations.sha256 (a migrate stopped part-way?): refused; pnpm db:trial restore <its pre-migrate backup> (pnpm db:trial status lists them)"
}
# Kept although `migration up` would refuse too: this refusal comes before migrate's cold backup
# and its stop/start of the stack.
pending_migrations() { # the branch's files not applied yet; refuses one older than the last applied
local last f
last="$(tail -n 1 "$MANIFEST" | cut -d' ' -f1)"
for f in supabase/migrations/*.sql; do
if grep -q "^$(file_version "$f") " "$MANIFEST"; then continue; fi
[ "$(file_version "$f")" \> "$last" ] || die "$(basename "$f") is older than the last applied migration $last: refused"
basename "$f"
done
}
# The seed folder's own consistency: sheet id, every drawing's size and sha256 against
# assets.json, both declared by equine_draft.sql. Prints "sha256 size" per drawing.
SEED_CHECK='
const fs = require("fs"), path = require("path"), c = require("crypto");
const [dir, sheet] = process.argv.slice(1);
const fail = (why) => { console.error(why); process.exit(1); };
const ref = JSON.parse(fs.readFileSync(path.join(dir, "sheet-ref.json"), "utf8"));
const assets = JSON.parse(fs.readFileSync(path.join(dir, "assets.json"), "utf8"));
const seed = fs.readFileSync(path.join(dir, "equine_draft.sql"), "utf8");
if (ref.sheet_id !== sheet || !seed.includes(sheet)) fail("the seed does not name the historical sheet");
if (!Array.isArray(assets) || assets.length === 0) fail("assets.json lists no drawing");
for (const a of assets) {
if (!/^[0-9a-f]{64}$/.test(a.sha256) || !Number.isInteger(a.byte_size)) fail("assets.json entry malformed");
const bytes = fs.readFileSync(path.join(dir, "assets", path.basename(String(a.file))));
if (bytes.length !== a.byte_size || c.createHash("sha256").update(bytes).digest("hex") !== a.sha256)
fail(a.file + " differs from assets.json");
if (!seed.includes(a.sha256)) fail("equine_draft.sql does not declare " + a.file);
console.log(a.sha256 + " " + a.byte_size);
}
'
check_seed_sources() {
node -e "$SEED_CHECK" "$SEED" "$HISTORICAL_SHEET" >/dev/null || die "the equine seed folder fails its own hashes: nothing was created"
}
expected_assets() { node -e "$SEED_CHECK" "$SEED" "$HISTORICAL_SHEET" | LC_ALL=C sort; }
# The Rust pipeline regenerates the seed from the legacy sources and compares it byte for byte
# (R5.6 fingerprint convention): what is loaded is what the pipeline produces. Both checks stay:
# check_seed_sources (folder vs its own hashes, cheap, also in plan) then this one (folder vs
# pipeline); verify_equine_db then checks the database against the folder.
check_seed_pipeline() {
say "equine seed: the Rust pipeline regenerates it and compares (the first run compiles)"
( unset INIT_CWD; bash scripts/import-equine.sh --check ) \
|| die "the equine seed differs from what the pipeline produces: nothing was created"
}
seed_equine() { sb_psql --single-transaction < "$SEED/equine_draft.sql" >/dev/null; }
asset_rows() {
sb_psql -t -A -c "-- db-trial:asset-rows
select sha256 || ' ' || byte_size from public.anatomical_sheet_assets
where sheet_id = private.historical_sheet_id() order by sha256 collate \"C\""
}
uploaded_count() {
sb_psql -t -A -c "-- db-trial:assets-uploaded
select count(*) from public.anatomical_sheet_assets a
where a.sheet_id = private.historical_sheet_id() and a.uploaded_at is not null
and exists (select 1 from storage.objects o
where o.bucket_id = 'anatomical-sheet-assets' and o.name = a.sha256 || '.svg')"
}
# Right after the seed: the historical sheet is the only official one, its single version is a
# draft equal to document.json (jsonb equality, so the canonical fingerprint recorded in
# sheet-ref.json holds), its drawing rows match assets.json, and no account exists.
# A dollar-quote tag, never expanded:
# shellcheck disable=SC2016
DOC_TAG='$faunosteo_trial_doc$'
verify_equine_db() {
local got want="official=1 historical=1 versions=1 draft_equal=true accounts=0"
! grep -qF "$DOC_TAG" "$SEED/document.json" || die "document.json contains $DOC_TAG: cannot compare"
got="$( { cat <<'SQL'
-- db-trial:equine
select concat_ws(' ',
'official=' || (select count(*) from public.anatomical_sheets where is_official),
'historical=' || (select count(*) from public.anatomical_sheets s
where s.id = private.historical_sheet_id() and s.id = :'sheet'::uuid and s.is_official),
'versions=' || (select count(*) from public.anatomical_sheet_versions v
where v.sheet_id = private.historical_sheet_id()),
'draft_equal=' || coalesce((select (v.content =
SQL
printf '%s' "$DOC_TAG"
cat "$SEED/document.json"
printf '%s' "$DOC_TAG"
cat <<'SQL'
::jsonb)::text from public.anatomical_sheet_versions v
where v.sheet_id = private.historical_sheet_id() and v.status = 'draft'), 'none'),
'accounts=' || (select count(*) from auth.users));
SQL
} | sb_psql -t -A -v sheet="$HISTORICAL_SHEET")"
[ "$got" = "$want" ] || die "the equine sheet in the database is not the seed's ($got; expected $want)"
[ "$(asset_rows)" = "$(expected_assets)" ] || die "the equine drawing rows differ from assets.json"
say "equine sheet loaded: $got; drawing rows match assets.json"
}
inventory_counts() {
sb_psql -t -A -F ': ' <<'SQL'
-- db-trial:inventory
select 'accounts', count(*) from auth.users
union all select 'official sheets', count(*) from public.anatomical_sheets where is_official
union all select 'sheet versions', count(*) from public.anatomical_sheet_versions
union all select 'workspaces', count(*) from public.workspaces;
SQL
}
list_backups() { # a folder without SHA256SUMS never finished: listed, marked, never restored
local dir kind
say "backups in $BACKUPS"
[ -d "$BACKUPS" ] || return 0
for dir in "$BACKUPS"/*/; do
[ -d "$dir" ] || continue
if [ -f "$dir/SHA256SUMS" ]; then kind=complete; else kind=incomplete; fi
printf ' %s %s %s\n' "$(basename "$dir")" "$(du -sh "$dir" | cut -f1)" "$kind"
done
}
```
#### Section 5: plan, create, start, stop, status
```bash
cmd_plan() {
[ $# -eq 0 ] || die "plan takes no argument"
local refusals=() name host inner memory pids vol image root owner where mib
say "db:trial plan: read-only, nothing is created, started or changed"
echo "Docker endpoint: $(daemon_endpoint) (a local unix socket, checked at dispatch)"
if daemon_ok; then echo "Docker daemon: reachable"
else refusals+=("the Docker daemon is unreachable (every Docker answer below is unknown)"); fi
echo "Containers (network $NET, restart policy no, --cpus 1):"
for name in "${CONTAINERS[@]}"; do
read -r host inner <<<"$(port_of "$name")"
read -r memory pids <<<"$(limits_of "$name")"
printf ' %-24s %-46s 127.0.0.1:%s -> %-5s --memory %-5s --pids-limit %-4s volume %s\n' \
"$name" "$(image_of "$name")" "$host" "$inner" "$memory" "$pids" "$(mount_of "$name")"
if container_exists "$name"; then refusals+=("container $name already exists"); fi
done
echo " $HELPER (create's image check, backup, restore, destroy only): --rm, --network none, image $IMAGE_DB"
echo "Network: $NET"
if network_exists "$NET"; then refusals+=("network $NET already exists"); fi
root="$(docker info --format '{{.DockerRootDir}}' 2>/dev/null || echo '<Docker data root>')"
echo "Volumes:"
for vol in "$VOL_DB" "$VOL_STORAGE"; do
printf ' %-24s %s/volumes/%s/_data\n' "$vol" "$root" "$vol"
if volume_exists "$vol"; then refusals+=("volume $vol already exists"); fi
done
echo "Images:"
for image in "$IMAGE_DB" "$IMAGE_AUTH" "$IMAGE_REST" "$IMAGE_STORAGE" "$IMAGE_MAIL" "$IMAGE_GATEWAY"; do
if docker image inspect -f '{{.Id}}' "$image" >/dev/null 2>&1; then where=present; else where='not pulled yet (create pulls it)'; fi
printf ' %-46s %s\n' "$image" "$where"
done
echo "Ports (127.0.0.1 only):"
for name in "${CONTAINERS[@]}"; do
read -r host inner <<<"$(port_of "$name")"
if port_listening "$host"; then
owner="$(docker ps --format '{{.Names}} {{.Ports}}' 2>/dev/null | grep -F ":$host->" | cut -d' ' -f1 | head -n 1 || true)"
printf ' %-6s taken by %s\n' "$host" "${owner:-a process outside Docker}"
refusals+=("port 127.0.0.1:$host is taken")
else
printf ' %-6s free\n' "$host"
fi
done
if host="$( (trial_host) 2>&1)"; then
if port_listening "$PORT_APP"; then where=taken; else where=free; fi
echo "App (pnpm db:trial app): http://$host:$PORT_APP, $where; the host is decided again at each app run"
else
refusals+=("$host")
fi
echo "State folder: $STATE (700, secrets 600)"
if [ -f "$SECRETS" ]; then refusals+=("$STATE already holds a stack"); fi
echo "Backups: $BACKUPS (cold copies, never pruned by the script; each needs free space of 110 % of the volumes)"
where="$STATE"
while [ ! -e "$where" ]; do where="$(dirname "$where")"; done
echo "Free disk there: $(df -h --output=avail "$where" | tail -n 1 | tr -d ' ')"
mib="$(available_mib)"
echo "Memory: caps 1920 MiB in all (about 650 MiB used in practice), $mib MiB available, floor $MEMORY_FLOOR_MIB MiB; the app's Node heap is capped at 768 MiB"
if [ "$mib" -lt "$MEMORY_FLOOR_MIB" ]; then refusals+=("only $mib MiB of memory available"); fi
if (check_seed_sources) 2>/dev/null; then echo "Equine seed folder: sheet id and drawing hashes match"
else refusals+=("the equine seed folder fails its hashes"); fi
echo "At create, before anything is made: the Rust pipeline regenerates the seed and compares it (pnpm r5:import-equine --check), then a --rm helper with no network checks that $IMAGE_DB holds tar and du"
if [ "${#refusals[@]}" -eq 0 ]; then
say "create would proceed"
else
say "create would refuse:"
printf ' - %s\n' "${refusals[@]}"
fi
}
# The backups run tar and du in the database image: proven before anything is made.
check_helper_image() {
docker run --rm --name "$HELPER" --network none --entrypoint sh "$IMAGE_DB" -c 'command -v tar && command -v du' >/dev/null \
|| die "$IMAGE_DB lacks tar or du for the backups: nothing was created"
}
# Before the first Docker resource, the files this create wrote are removed (none existed: create
# refuses a folder holding secrets.env); after it, nothing is removed and the way out is named.
create_stopped() {
local status=$?
[ "$status" -ne 0 ] || return 0
if [ "$FIRST_RESOURCE" = no ]; then
remove_live_files
echo "db-trial: create stopped before any Docker resource existed; the state files it wrote are removed" >&2
return 0
fi
echo "db-trial: create stopped before the end; nothing was removed. Present now:" >&2
existing_resources | sed 's/^/ /' >&2
echo "pnpm db:trial status shows the state; pnpm db:trial destroy (after the owner's go) clears it." >&2
}
FIRST_RESOURCE=no
cmd_create() {
[ $# -eq 0 ] || die "create takes no argument"
local found name
[ ! -f "$SECRETS" ] || die "$STATE already holds a trial stack (pnpm db:trial status)"
found="$(existing_resources | tr '\n' ' ')"
[ -z "$found" ] || die "these faunosteo_trial_ resources already exist, nothing was created: $found"
ports_free "${CONTAINERS[@]}"
memory_ok
trial_host >/dev/null
check_seed_sources
check_seed_pipeline
check_helper_image
( umask 077; mkdir -p "$STATE" "$BACKUPS" )
chmod 700 "$STATE" "$BACKUPS"
( umask 077; : > "$MARKER" )
take_lock
trap create_stopped EXIT
new_secrets_file "$SECRETS"
load_secrets
write_conf
write_env_files
docker network create --label faunosteo.stack=trial "$NET" >/dev/null
FIRST_RESOURCE=yes
docker volume create --label faunosteo.stack=trial "$VOL_DB" >/dev/null
docker volume create --label faunosteo.stack=trial "$VOL_STORAGE" >/dev/null
for name in "$DB" "$MAIL" "$AUTH" "$STORAGE"; do run_service "$name"; done
published_exactly "$DB"
migrate_up
write_manifest
seed_equine
verify_equine_db
for name in "$REST" "$GATEWAY"; do run_service "$name"; done
check_loopback
trap - EXIT
say "trial stack created and running (gateway http://127.0.0.1:$PORT_GATEWAY)"
echo "Next: pnpm db:trial assets, then the accounts (SUPABASE.md §5.2), then pnpm db:trial app"
}
cmd_start() {
[ $# -eq 0 ] || die "start takes no argument"
require_created
take_lock
memory_ok
start_containers
say "trial stack running; the app: pnpm db:trial app"
}
cmd_stop() {
[ $# -eq 0 ] || die "stop takes no argument"
require_created
take_lock
# A running app would keep proxying session tokens to whatever answers 58801 next.
refuse_while_app_runs
stop_containers
say "trial stack stopped; data kept in $VOL_DB and $VOL_STORAGE"
}
status_database() {
local check pending
say "migrations: $(applied_versions | grep -c .) applied, $(find supabase/migrations -maxdepth 1 -name '*.sql' | wc -l) files in this branch"
if check="$( (check_manifest) 2>&1)"; then
# A refusal of pending_migrations is printed as it is, never fatal here.
pending="$( (pending_migrations) 2>&1 | tr '\n' ' ' || true)"
echo " applied files unchanged; new: ${pending:-none}"
else
echo " $check"
fi
say "equine sheet (versions by status)"
sb_psql -t -A -c "-- db-trial:equine-status
select status::text || ' ' || count(*) from public.anatomical_sheet_versions
where sheet_id = private.historical_sheet_id() group by status order by 1" | sed 's/^/ /'
echo " drawings uploaded and confirmed: $(uploaded_count)/$(expected_assets | grep -c .)"
say "accounts (email | platform roles | catalogue rights | verified TOTP factors)"
sb_psql -t -A -F ' | ' <<'SQL' | sed 's/^/ /'
-- db-trial:accounts
select u.email,
coalesce((select string_agg(r.role::text, ',' order by r.role::text) from public.profile_roles r
where r.user_id = u.id and r.revoked_at is null), '-'),
coalesce((select string_agg(g.grant_kind::text, ',' order by g.grant_kind::text)
from public.anatomical_catalog_grants g
where g.user_id = u.id and g.revoked_at is null
and (g.expires_at is null or g.expires_at > now())), '-'),
(select count(*) from auth.mfa_factors f
where f.user_id = u.id and f.factor_type = 'totp' and f.status = 'verified')
from auth.users u order by u.email;
SQL
say "content"
inventory_counts | sed 's/^/ /'
}
cmd_status() {
[ $# -eq 0 ] || die "status takes no argument"
local name host
if [ ! -e "$MARKER" ] || [ ! -f "$SECRETS" ]; then
say "no trial stack in $STATE (pnpm db:trial plan shows what create would make)"
existing_resources | sed 's/^/ present anyway: /'
list_backups
return 0
fi
check_private "$STATE" 700
load_conf
load_secrets
say "containers"
for name in "${CONTAINERS[@]}"; do
printf ' %-24s %s\n' "$name" "$(docker container inspect -f '{{.State.Status}} {{.Config.Image}}' "$name" 2>/dev/null || echo absent)"
done
say "Docker endpoint: $(daemon_endpoint)"
say "keys valid until $(key_expiry) (pnpm db:trial rotate-keys renews them)"
if [ "$(key_check "$TR_ANON_KEY" anon fresh >/dev/null 2>&1; echo $?)" = 3 ]; then
say "the keys have EXPIRED: app, assets and account refuse until pnpm db:trial rotate-keys"
fi
host="$( (trial_host) 2>&1 || true)"
say "app host now: $host (pnpm db:trial app serves http://<host>:$PORT_APP)"
if container_running "$DB"; then status_database; else say "database stopped: pnpm db:trial start"; fi
list_backups
}
```
#### Section 6: dispatch (B2 and B3 add their lines)
```bash
# Every command's arguments are checked before any Docker call (a refusal here logs nothing).
# The commands keep their own checks too, so each stays safe if called on its own.
check_arguments() { # action args...
local action="$1"
shift
case "$action" in
plan | create | start | stop | status)
[ $# -eq 0 ] || die "$action takes no argument" ;;
*) usage; exit 1 ;;
esac
}
guard_environment
action="${1:-}"
[ $# -eq 0 ] || shift
case "$action" in
'' | help | -h | --help) usage; exit 1 ;;
esac
check_arguments "$action" "$@"
command -v docker >/dev/null || die "docker is not installed"
command -v ss >/dev/null || die "ss (iproute2) is not installed: the ports cannot be checked, nothing was run"
require_local_daemon
# Past this line a Docker "absent" is an answer, not a dead daemon (plan reports the daemon itself).
if [ "$action" != plan ]; then
daemon_ok || die "Docker daemon unreachable: nothing was run (systemctl --user status docker)"
fi
case "$action" in
plan) cmd_plan "$@" ;;
create) cmd_create "$@" ;;
start) cmd_start "$@" ;;
stop) cmd_stop "$@" ;;
status) cmd_status "$@" ;;
*) usage; exit 1 ;;
esac
```
#### Harness H1: frame, preflight, fake repository, stubs
```bash
#!/usr/bin/env bash
# shellcheck disable=SC2016
# (file-wide: the `bash -c '…'` snippets of the checks expand their own positional parameters)
#
# `bash scripts/db-trial-check.sh`, run by hand (never a check:all stage): environment-free checks
# of scripts/db-trial.sh and of the state-folder guard of scripts/db-sandbox.sh (TRIAL_STACK.md,
# SUPABASE.md §5.2). Each case runs a script from a throwaway copy of the files it reads, under a
# temporary HOME, with docker, curl, the Supabase CLI, Vite and the Rust tools replaced by stubs
# that log their arguments and keep one file per pretend resource; ss, node and env pass through
# wrappers that log their arguments, so no secret can hide in any argv. DOCKER_HOST names a socket
# that does not exist, so no daemon is reachable even past a stub. The script reads the host's
# real listening ports (ss) and memory: the preflight refuses to run (exit 2, never a FAIL) while
# a trial port is in use or memory is short. Nothing is created, started, stopped or removed
# outside the temporary folder. Prints "db-trial-check: ALL PASS" only when every check passed.
set -uo pipefail
preflight() {
local port out mib
for port in 58432 58999 58300 58500 58025 58801 5194; do
out="$(ss -Hltn "sport = :$port")" || { echo "db-trial-check: cannot run while ss cannot list the ports: stop the trial stack/app first" >&2; exit 2; }
if [ -n "$out" ]; then
echo "db-trial-check: cannot run while port $port is in use: stop the trial stack/app first" >&2
exit 2
fi
done
mib="$(awk '/^MemAvailable:/ { print int($2 / 1024) }' /proc/meminfo)"
if [ "$mib" -lt 2048 ]; then
echo "db-trial-check: cannot run while only $mib MiB of memory are available (create needs 2048): stop the trial stack/app first" >&2
exit 2
fi
}
preflight
REPO="$(cd "$(dirname "$0")/.." && pwd -P)"
TMP_ROOT="${TMPDIR:-/tmp}"
WORK="$(mktemp -d "$TMP_ROOT/db-trial-check.XXXXXX")" || exit 1
# Every later path hangs off WORK: it must be the fresh folder mktemp just made, nothing else.
case "$WORK" in
"$TMP_ROOT"/db-trial-check.?*) [ -d "$WORK" ] || exit 1 ;;
*) echo "db-trial-check: unexpected temporary folder '$WORK': nothing was run" >&2; exit 1 ;;
esac
cleanup() { ( cd "$WORK" && rm -rf -- ./repo ./bin ./bin-no-ss ./path-no-ss ./cases ./docker-config ); rmdir -- "$WORK" 2>/dev/null || true; }
trap cleanup EXIT
PASS=0 FAIL=0
pass() { echo "PASS $1"; PASS=$((PASS + 1)); }
fail() { echo "FAIL $1"; FAIL=$((FAIL + 1)); }
check() { local name="$1"; shift; if "$@"; then pass "$name"; else fail "$name"; fi; }
SEED_REL=supabase/seed/anatomical-sheets/equine/draft
mkdir -p "$WORK/repo/scripts" "$WORK/repo/$SEED_REL/assets" "$WORK/repo/node_modules/.bin" \
"$WORK/repo/apps/app/node_modules/.bin" "$WORK/bin" "$WORK/cases" "$WORK/docker-config"
cp "$REPO/scripts/db-trial.sh" "$REPO/scripts/db-sandbox.sh" "$REPO/scripts/dev-env.sh" "$WORK/repo/scripts/"
cp "$REPO/supabase/config.toml" "$REPO/supabase/seed.sql" "$WORK/repo/supabase/"
cp -R "$REPO/supabase/migrations" "$WORK/repo/supabase/migrations"
for f in equine_draft.sql document.json sheet-ref.json assets.json; do cp "$REPO/$SEED_REL/$f" "$WORK/repo/$SEED_REL/"; done
cp "$REPO/$SEED_REL/assets/"*.svg "$WORK/repo/$SEED_REL/assets/"
cat > "$WORK/bin/docker" <<'STUB'
#!/usr/bin/env bash
# docker stand-in: one log line per call (newlines shown as \n), one file per pretend resource.
# Files under $STUB_STATE/fail/ make one answer fail: daemon, networks-create, archive, <sql tag>.
set -uo pipefail
st="$STUB_STATE"
{ printf 'docker'; for a in "$@"; do printf ' %s' "${a//$'\n'/\\n}"; done; printf '\n'; } >> "$STUB_LOG"
save_stdin() { local f; f="$st/stdin/$(date +%s%N)"; cat > "$f"; printf '%s' "$f"; }
sub="${1:-}"
[ $# -eq 0 ] || shift
case "$sub" in
info) [ ! -f "$st/fail/daemon" ] || exit 1; echo /stub/docker-root ;;
context)
[ "${1:-}" = inspect ] || exit 97
[ ! -f "$st/fail/context" ] || exit 1
if [ -f "$st/answers/context" ]; then cat "$st/answers/context"; else echo unix:///stub/docker.sock; fi ;;
ps) exit 0 ;;
image) [ "${1:-}" = inspect ] || exit 97; [ ! -f "$st/fail/image" ] || exit 1 ;;
network | volume)
kind="${sub}s" op="${1:-}"
shift
case "$op" in
inspect) if [ "${1:-}" = -f ]; then shift 2; fi; [ -f "$st/$kind/$1" ] ;;
create)
[ ! -f "$st/fail/$kind-create" ] || exit 1
name="${*: -1}"; [ ! -f "$st/$kind/$name" ] || exit 1; : > "$st/$kind/$name"; echo "$name" ;;
rm) for n in "$@"; do [ -f "$st/$kind/$n" ] || exit 1; rm -f "$st/$kind/$n"; done ;;
*) exit 97 ;;
esac ;;
container)
[ "${1:-}" = inspect ] || exit 97
shift
fmt=''
if [ "${1:-}" = -f ]; then fmt="$2"; shift 2; fi
[ -f "$st/containers/$1" ] || exit 1
case "$fmt" in
'{{.State.Running}}') if [ "$(cat "$st/containers/$1")" = running ]; then echo true; else echo false; fi ;;
'{{.Name}}') echo "/$1" ;;
'') echo "stub: a bare inspect would print the env files" >&2; exit 98 ;;
*) echo "$(cat "$st/containers/$1") stub-image" ;;
esac ;;
run)
name='' port='' remove=0 input=0 args=("$@") i=0
while [ "$i" -lt "${#args[@]}" ]; do
case "${args[$i]}" in
--name) name="${args[$((i + 1))]}"; i=$((i + 1)) ;;
-p) port="${args[$((i + 1))]}"; i=$((i + 1)) ;;
--rm) remove=1 ;;
-i) input=1 ;;
esac
i=$((i + 1))
done
if [ "$remove" -eq 1 ]; then
if [ "$input" -eq 1 ]; then save_stdin >/dev/null; fi
# Measure: 100 KiB and 2 entries ("." and "./content"), what the archive below lists.
if printf '%s\n' "$@" | grep -q -- 'du -sk /volume'; then printf '100\n2\n'; fi
if printf '%s\n' "$@" | grep -qx -- -cf; then
[ ! -f "$st/fail/archive" ] || exit 3
# A real (tiny) tar, so that the script's read-back has something to list. short-archive:
# the root entry only, exit 0 (an archive cut at a member boundary).
mkdir -p "$st/archive"; printf '%s\n' "$*" > "$st/archive/content"
if [ -f "$st/short-archive" ]; then tar -cf - -C "$st/archive" --no-recursion .
else tar -cf - -C "$st/archive" .; fi
fi
exit 0
fi
[ -n "$name" ] && [ ! -f "$st/containers/$name" ] || exit 1
echo running > "$st/containers/$name"
if [ -n "$port" ]; then
IFS=: read -r ip host inner <<<"$port"
# publish-wide-<name>: Docker publishes that container on 0.0.0.0 whatever -p said.
if [ -f "$st/publish-wide-$name" ]; then ip=0.0.0.0; fi
echo "$inner/tcp -> $ip:$host" > "$st/ports/$name"
fi
echo "stub-id-$name" ;;
start) for n in "$@"; do [ -f "$st/containers/$n" ] || exit 1; echo running > "$st/containers/$n"; done ;;
restart)
while [ "${1:-}" = -t ]; do shift 2; done
for n in "$@"; do [ -f "$st/containers/$n" ] || exit 1; echo running > "$st/containers/$n"; done ;;
stop)
while [ "${1:-}" = -t ]; do shift 2; done
for n in "$@"; do [ -f "$st/containers/$n" ] || exit 1; echo exited > "$st/containers/$n"; done ;;
rm)
if [ "${1:-}" = -f ]; then shift; fi
for n in "$@"; do [ -f "$st/containers/$n" ] || exit 1; rm -f "$st/containers/$n" "$st/ports/$n"; done ;;
port)
[ -f "$st/ports/$1" ] || exit 1
if [ $# -ge 2 ]; then sed 's/^.* -> //' "$st/ports/$1"; else cat "$st/ports/$1"; fi ;;
exec)
input=0
while [ $# -gt 0 ]; do
case "$1" in -i) input=1; shift ;; -e | -u) shift 2 ;; *) break ;; esac
done
[ "$(cat "$st/containers/$1" 2>/dev/null)" = running ] || exit 1
shift
# psql reads -c when given (stdin untouched, as the real psql does), else its stdin.
query='' prev='' has_c=0
for a in "$@"; do if [ "$prev" = -c ]; then query="$a"; has_c=1; fi; prev="$a"; done
if [ "$input" -eq 1 ] && [ "$has_c" -eq 0 ]; then query="$(head -c 65536 "$(save_stdin)")"; fi
tag="$(printf '%s\n' "$query" | sed -n 's/^-- db-trial:\([a-z-]*\)$/\1/p' | head -n 1)"
# commit-then-fail: the rotation commits (its marker becomes the digest), then the exec stream
# is lost. Without it, fail/rotate leaves the marker empty: not committed.
if [ "$tag" = rotate ] && [ -f "$st/commit-then-fail" ]; then
printf '%s\n' "${ROT_DIGEST:-}" > "$st/answers/rotation-marker"; echo "stub: stream lost" >&2; exit 3
fi
if [ -n "$tag" ] && [ -f "$st/fail/$tag" ]; then echo "stub: $tag refused" >&2; exit 3; fi
if [ -n "$tag" ] && [ -f "$st/answers/$tag" ]; then cat "$st/answers/$tag"; fi
exit 0 ;;
*) echo "stub docker: unsupported $sub" >&2; exit 97 ;;
esac
STUB
cat > "$WORK/bin/curl" <<'STUB'
#!/usr/bin/env bash
# curl stand-in. The trial gateway's identity endpoint answers faunosteo_trial, or
# answers/identity, or fails (fail/identity, curl -f's exit 22).
{ printf 'curl'; for a in "$@"; do printf ' %s' "$a"; done; printf '\n'; } >> "$STUB_LOG"
for a in "$@"; do
case "$a" in
@-) cat > "$STUB_STATE/stdin/curl-$(date +%s%N)" ;;
# A header file (a process substitution): only a digest of what was sent is kept.
@/dev/fd/*) sha256sum < "${a#@}" | cut -c1-16 > "$STUB_STATE/curl-header-sha" ;;
esac
done
case "${*: -1}" in
*/__faunosteo_stack)
[ ! -f "$STUB_STATE/fail/identity" ] || exit 22
if [ -f "$STUB_STATE/answers/identity" ]; then cat "$STUB_STATE/answers/identity"; else echo faunosteo_trial; fi ;;
esac
exit 0
STUB
# ss runs for real unless the case asks it to fail (it must then never read as "port free").
cat > "$WORK/bin/ss" <<'STUB'
#!/usr/bin/env bash
if [ -f "$STUB_STATE/fail/ss" ]; then echo "ss: cannot open netlink socket" >&2; exit 1; fi
exec "$(cat "$(dirname "$0")/real-ss")" "$@"
STUB
command -v ss > "$WORK/bin/real-ss"
# node and env run for real behind a wrapper that logs their argv (newlines as \n).
cat > "$WORK/bin/passthrough" <<'STUB'
#!/usr/bin/env bash
tool="$(basename "$0")"
{ printf '%s' "$tool"; for a in "$@"; do printf ' %s' "${a//$'\n'/\\n}"; done; printf '\n'; } >> "$STUB_LOG"
exec "$(cat "$(dirname "$0")/real-$tool")" "$@"
STUB
for tool in node env; do
command -v "$tool" > "$WORK/bin/real-$tool"
cp "$WORK/bin/passthrough" "$WORK/bin/$tool"
done
cat > "$WORK/repo/node_modules/.bin/supabase" <<'STUB'
#!/usr/bin/env bash
{ printf 'supabase'; for a in "$@"; do printf ' %s' "$a"; done; printf ' PGPASSWORD=%s\n' "${PGPASSWORD:+set}"; } >> "$STUB_LOG"
if [ "$*" = "--workdir . migration up --db-url postgresql://postgres@127.0.0.1:58432/postgres?sslmode=disable" ]; then
for f in supabase/migrations/*.sql; do basename "$f" | cut -d_ -f1; done > "$STUB_STATE/answers/applied"
if [ -f "$STUB_STATE/fail/migrate" ]; then echo "stub: a migration failed after the others applied" >&2; exit 3; fi
exit 0
fi
exit 97
STUB
cat > "$WORK/repo/apps/app/node_modules/.bin/vite" <<'STUB'
#!/usr/bin/env bash
{ printf 'vite'; for a in "$@"; do printf ' %s' "$a"; done; printf '\n'
printf 'vite-env anon-sha=%s pgpassword=%s node-options=%s gateway=%s host=%s url=%s proxies=%s\n' \
"$(printf '%s' "${VITE_SUPABASE_ANON_KEY:-}" | sha256sum | cut -c1-16)" "${PGPASSWORD:+set}" \
"${NODE_OPTIONS:-}" "${FAUNOSTEO_TRIAL_GATEWAY:-}" "${FAUNOSTEO_TRIAL_HOST:-}" "${VITE_SUPABASE_URL:-}" \
"${http_proxy:-}${HTTP_PROXY:-}${https_proxy:-}${HTTPS_PROXY:-}${all_proxy:-}${ALL_PROXY:-}${NODE_USE_ENV_PROXY:-}"; } >> "$STUB_LOG"
STUB
cat > "$WORK/repo/scripts/upload-sheet-assets.sh" <<'STUB'
#!/usr/bin/env bash
{ printf 'upload'; for a in "$@"; do printf ' %s' "$a"; done; printf '\n'
printf 'upload-env url=%s key-sha=%s dev-host=%s init-cwd=%s\n' "${SUPABASE_URL:-}" \
"$(printf '%s' "${SUPABASE_SERVICE_ROLE_KEY:-}" | sha256sum | cut -c1-16)" \
"${FAUNOSTEO_DEV_HOST:+set}" "${INIT_CWD:+set}"; } >> "$STUB_LOG"
if [ "${1:-}" = --check ]; then
echo "horse.svg 0ea5202fa3b95bac167b87c12ee969ab0272e085b60c2d34dbca6202c5f08541.svg: would object verified, row confirmed, nothing to do"
else
echo "horse.svg done"
fi
echo ok
STUB
cat > "$WORK/repo/scripts/import-equine.sh" <<'STUB'
#!/usr/bin/env bash
{ printf 'import-equine'; for a in "$@"; do printf ' %s' "$a"; done; printf '\n'; } >> "$STUB_LOG"
STUB
chmod +x "$WORK/bin/docker" "$WORK/bin/curl" "$WORK/bin/ss" "$WORK/bin/node" "$WORK/bin/env" \
"$WORK/repo/node_modules/.bin/supabase" "$WORK/repo/apps/app/node_modules/.bin/vite"
BASE_PATH="$PATH"
for tool in docker curl ss node env; do
if [ "$(PATH="$WORK/bin:$BASE_PATH" command -v "$tool")" != "$WORK/bin/$tool" ]; then
echo "db-trial-check: the stubs do not shadow $tool: nothing was run" >&2
exit 1
fi
done
# A PATH with every tool but ss: the stubs (no ss wrapper), then a link to each executable of the
# real PATH except ss. Used by the "ss is not installed" case only.
mkdir -p "$WORK/bin-no-ss" "$WORK/path-no-ss"
cp -p "$WORK/bin/docker" "$WORK/bin/curl" "$WORK/bin/node" "$WORK/bin/env" "$WORK/bin/real-node" "$WORK/bin/real-env" "$WORK/bin-no-ss/"
IFS=: read -ra PATH_DIRS <<<"$BASE_PATH"
for dir in "${PATH_DIRS[@]}"; do
for exe in "$dir"/*; do
name="${exe##*/}"
if [ "$name" != ss ] && [ -x "$exe" ] && [ ! -e "$WORK/path-no-ss/$name" ]; then ln -s "$exe" "$WORK/path-no-ss/$name"; fi
done
done
```
#### Harness H2: case machinery and assertions
```bash
ASSET_LINE="$(node -e 'for (const a of require(process.argv[1])) console.log(a.sha256 + " " + a.byte_size)' "$WORK/repo/$SEED_REL/assets.json")"
new_case() { # name
CASE="$WORK/cases/$1"
mkdir -p "$CASE/home" "$CASE/stub/containers" "$CASE/stub/ports" "$CASE/stub/volumes" "$CASE/stub/networks" \
"$CASE/stub/answers" "$CASE/stub/fail" "$CASE/stub/stdin"
: > "$CASE/log"
STATE_DIR="$CASE/home/.cache/faunosteo-trial"
echo 'official=1 historical=1 versions=1 draft_equal=true accounts=0' > "$CASE/stub/answers/equine"
printf '%s\n' "$ASSET_LINE" > "$CASE/stub/answers/asset-rows"
echo 1 > "$CASE/stub/answers/assets-uploaded"
echo 'granted false' > "$CASE/stub/answers/grant-catalog"
EXTRA=()
CASE_PATH="$WORK/bin:$BASE_PATH"
# The no-daemon barrier: a unix socket that does not exist (so the script's local-daemon guard
# accepts it and a real docker, were a stub missing, would still reach nothing), and a context
# that does not exist for the cases that clear DOCKER_HOST.
CASE_DOCKER_HOST="unix://$WORK/no-daemon.sock"
}
# Gives a script a terminal (a pty), types its stdin there, copies the output, returns the exit code.
PTY_DRIVER='
import os, pty, sys
pid, fd = pty.fork()
if pid == 0:
os.execvp(sys.argv[1], sys.argv[1:])
try:
os.write(fd, sys.stdin.buffer.read())
except OSError:
pass
while True:
try:
chunk = os.read(fd, 4096)
except OSError:
break
if not chunk:
break
sys.stdout.buffer.write(chunk)
sys.stdout.flush()
_, status = os.waitpid(pid, 0)
sys.exit(os.waitstatus_to_exitcode(status))
'
LAUNCH=()
run_script() { # script args... → RC, output in $CASE/out; stdin as the caller set it
local script="$1"
shift
( cd "$WORK/repo" && env -i HOME="$CASE/home" PATH="$CASE_PATH" LANG=C.UTF-8 \
DOCKER_HOST="$CASE_DOCKER_HOST" DOCKER_CONTEXT=faunosteo-check-no-such-context DOCKER_CONFIG="$WORK/docker-config" \
STUB_LOG="$CASE/log" STUB_STATE="$CASE/stub" FAUNOSTEO_TRIAL_HOST=127.0.0.1 "${EXTRA[@]}" \
"${LAUNCH[@]}" bash "scripts/$script" "$@" ) > "$CASE/out" 2>&1
RC=$?
}
trial() { LAUNCH=(); run_script db-trial.sh "$@" < /dev/null; }
trial_input() { LAUNCH=(); run_script db-trial.sh "$@"; } # stdin from the caller (a pipe: no terminal)
trial_tty() { # input args...: the input typed at the script's terminal
local input="$1"
shift
LAUNCH=(python3 -c "$PTY_DRIVER")
run_script db-trial.sh "$@" <<<"$input"
LAUNCH=()
}
sandbox() { LAUNCH=(); run_script db-sandbox.sh "$@" < /dev/null; }
fresh_log() { : > "$CASE/log"; }
docker_calls() { grep '^docker ' "$CASE/log" || true; }
# Calls that change something: Docker writes, the CLI, the upload tool, Vite.
changes() {
grep -E '^(docker (run|start|restart|stop|rm|volume (create|rm)|network (create|rm))|supabase |upload |vite )' "$CASE/log" || true
}
out_has() { grep -qF -- "$1" "$CASE/out"; }
log_has() { grep -qF -- "$1" "$CASE/log"; }
refused() { [ "$RC" -ne 0 ] && [ -z "$(changes)" ] && out_has "$1"; }
refused_early() { [ "$RC" -ne 0 ] && [ ! -s "$CASE/log" ] && out_has "$1"; }
mode_is() { [ "$(stat -c %a "$1")" = "$2" ]; }
values_absent_from() { # secrets file, files...
local value file
[ -f "$1" ] || return 1
while IFS='=' read -r _ value; do
[ -n "$value" ] || continue
for file in "${@:2}"; do
if [ -f "$file" ] && grep -qF -- "$value" "$file"; then return 1; fi
done
done < "$1"
}
# The log holds every argv of docker, curl, node, env, the CLI, Vite and the tools.
secrets_absent() { values_absent_from "$1" "$CASE/log" "$CASE/out"; }
stdin_files() { find "$CASE/stub/stdin" -type f; }
stdin_has() { stdin_files | xargs -r grep -lF -- "$1" | grep -q .; }
run_line() { docker_calls | grep -E "^docker run -d --name $1 " || true; }
expect_run() { # name port inner memory pids [volume]
local line
line="$(run_line "$1")"
[ "$(grep -c . <<<"$line")" -eq 1 ] || return 1
grep -qF -- "--network faunosteo_trial_net --restart no --memory $4 --cpus 1 --pids-limit $5 -p 127.0.0.1:$2:$3 " <<<"$line" || return 1
[ "$(grep -oE -- ' -p [^ ]+' <<<"$line" | wc -l)" -eq 1 ] || return 1
if [ -n "${6:-}" ]; then grep -qF -- " -v $6 " <<<"$line"; else ! grep -qE -- ' -v ' <<<"$line"; fi
}
# Resource names plus the Supabase roles and the migrations schema that SQL text in argv may name.
# faunosteo_stack and faunosteo_trial: the gateway identity path and body (/__faunosteo_stack answers faunosteo_trial), not resource names.
ALLOWED_NAMES='faunosteo_trial_(db|mail|auth|storage|rest|gateway|helper|net)|supabase_(admin|auth_admin|storage_admin|migrations)|faunosteo_stack|faunosteo_trial'
only_trial_names() { ! docker_calls | grep -oE '(faunosteo|supabase)_[A-Za-z0-9_]+' | grep -vxE "$ALLOWED_NAMES" | grep -q .; }
no_volume_removal() { ! log_has 'docker volume rm'; }
line_of() { grep -nF -- "$1" "$CASE/log" | head -n 1 | cut -d: -f1; }
before() { local a b; a="$(line_of "$1")"; b="$(line_of "$2")"; [ -n "$a" ] && [ -n "$b" ] && [ "$a" -lt "$b" ]; }
hold_port() { # port → LISTENER pid, or returns 1 when the port cannot be held
python3 -c 'import socket, sys, time
s = socket.socket(); s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
s.bind(("127.0.0.1", int(sys.argv[1]))); s.listen(1); print("up", flush=True); time.sleep(30)' "$1" > "$CASE/listener" 2>&1 &
LISTENER=$!
for _ in $(seq 1 50); do if grep -q up "$CASE/listener"; then return 0; fi; sleep 0.1; done
kill "$LISTENER" 2>/dev/null
return 1
}
release_port() { kill "$LISTENER" 2>/dev/null; wait "$LISTENER" 2>/dev/null; }
# Every logged curl starts with -q (no ~/.curlrc) and --noproxy '*' (no proxy variable).
curls_pinned() { grep -q '^curl ' "$CASE/log" && ! grep '^curl ' "$CASE/log" | grep -v "^curl -q --noproxy \* " | grep -q .; }
backup_count() { find "$STATE_DIR/backups" -mindepth 1 -maxdepth 1 -type d | wc -l; }
# Rewrites one key line of a secrets file (default the case's secrets.env) with a key of the given
# issuer and role, signed with that file's own secret or a random one, expiring in <offset> s.
forge() { # var issuer own|other role [offset] [file]
FORGE_FILE="${6:-$STATE_DIR/secrets.env}" node -e '
const fs = require("fs"), c = require("crypto");
const [name, iss, source, role, offset] = process.argv.slice(1);
const file = process.env.FORGE_FILE;
const lines = fs.readFileSync(file, "utf8").trim().split("\n");
const own = lines.find((l) => l.startsWith("TR_JWT_SECRET=")).slice(14);
const secret = source === "own" ? own : c.randomBytes(32).toString("hex");
const b64 = (o) => Buffer.from(JSON.stringify(o)).toString("base64url");
const h = b64({ alg: "HS256", typ: "JWT" }), now = Math.floor(Date.now() / 1000);
const b = b64({ role, iss, iat: now - 7200, exp: now + Number(offset) });
const jwt = h + "." + b + "." + c.createHmac("sha256", secret).update(h + "." + b).digest("base64url");
fs.writeFileSync(file, lines.map((l) => l.startsWith(name + "=") ? name + "=" + jwt : l).join("\n") + "\n", { mode: 0o600 });
' "$1" "$2" "$3" "$4" "${5:-3600}"
}
env_file_state() { # file → digest and mtime, to prove a refusal wrote nothing
printf '%s %s\n' "$(sha256sum < "$1" | cut -d' ' -f1)" "$(stat -c %Y "$1")"
}
```
#### Harness H3: static checks and B1 cases
```bash
echo "-- static"
if command -v shellcheck >/dev/null; then
check "shellcheck: db-trial.sh and this harness" bash -c 'cd "$1" && shellcheck -x scripts/db-trial.sh scripts/db-trial-check.sh' _ "$REPO"
check "shellcheck: db-sandbox.sh (pre-existing SC1090, SC1091, SC2016 excluded)" shellcheck -e SC1090,SC1091,SC2016 "$REPO/scripts/db-sandbox.sh"
else
fail "shellcheck is not installed"
fi
check "db-sandbox.sh: additions only since b215b89 (its commands unchanged)" bash -c 'cd "$1" && [ "$(git diff --numstat b215b89 -- scripts/db-sandbox.sh | cut -f2)" = 0 ]' _ "$REPO"
TRIAL_PORTS="$(grep -oE 'PORT_[A-Z_]+=[0-9]+' "$REPO/scripts/db-trial.sh" | cut -d= -f2 | sort -u)"
SANDBOX_PORTS="$(grep -oE 'PORT_[A-Z_]+=[0-9]+' "$REPO/scripts/db-sandbox.sh" | cut -d= -f2 | sort -u)"
DEV_PORTS="$(printf '%s\n' 5173 5191 5192 54321 54322 54323 54324 54325 | sort -u)"
check "trial ports are the ratified ones" [ "$TRIAL_PORTS" = "$(printf '%s\n' 5194 58025 58300 58432 58500 58801 58999 | sort -u)" ]
check "no trial port is a sandbox or DEV port" [ -z "$(comm -12 <(printf '%s\n' "$TRIAL_PORTS") <(printf '%s\n%s\n' "$SANDBOX_PORTS" "$DEV_PORTS" | sort -u))" ]
check "db-trial.sh never runs a bare inspect" bash -c '! grep -nE "docker (container |volume |network |image )?inspect [^-]" "$1"' _ "$REPO/scripts/db-trial.sh"
echo "-- refusals before anything"
new_case sandbox-env; EXTRA=(SANDBOX_AUTH_URL=http://127.0.0.1:57999); trial plan
check "a SANDBOX_* variable stops every command before any call" refused_early "SANDBOX_AUTH_URL is set"
new_case state-in-repo; EXTRA=(FAUNOSTEO_TRIAL_DIR="$WORK/repo/trial-state"); trial create
check "a state folder inside the repository is refused" refused_early "outside the repository"
new_case state-in-git; git init -q "$CASE/gitrepo"; EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/gitrepo/state"); trial create
check "a state folder inside any git work tree is refused" refused_early "outside every git work tree"
new_case state-symlink-repo; ln -s "$WORK/repo/trial-state" "$CASE/link"; EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/link"); trial create
check "a state folder that is a symlink into the repository is refused (canonical path)" refused_early "outside the repository"
new_case state-symlink-sandbox; mkdir -p "$CASE/sbstate"; ln -s "$CASE/sbstate" "$CASE/link"
EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/link" FAUNOSTEO_SANDBOX_DIR="$CASE/sbstate"); trial create
check "a state folder that is a symlink to the sandbox's state is refused" refused_early "sandbox's state folder"
new_case ss-fails; : > "$CASE/stub/fail/ss"; trial create
check "ss failing: create refuses, a port is never read as free" refused "ss cannot list the listening ports"
new_case ss-absent; CASE_PATH="$WORK/bin-no-ss:$WORK/path-no-ss"; trial create
check "ss absent from PATH: every command refuses before any call" refused_early "ss (iproute2) is not installed"
new_case state-symlink-ok; mkdir -p "$CASE/real"; ln -s "$CASE/real" "$CASE/link"; EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/link")
trial create; RC_CREATE=$RC; trial status; RC_STATUS=$RC; trial stop
check "a state folder reached through a symlink works: create, status, stop on the real folder (700)" bash -c '[ "$1" -eq 0 ] && [ "$2" -eq 0 ] && [ "$3" -eq 0 ] && [ "$(stat -c %a "$4")" = 700 ] && [ -f "$4/secrets.env" ]' _ "$RC_CREATE" "$RC_STATUS" "$RC" "$CASE/real"
new_case create-db-wide; : > "$CASE/stub/publish-wide-faunosteo_trial_db"; trial create
check "create: Postgres published beyond 127.0.0.1 stops it before any migration" bash -c '[ "$1" -ne 0 ] && grep -qF "faunosteo_trial_db is not published on 127.0.0.1:58432 only" "$2" && ! grep -q "^supabase " "$3"' _ "$RC" "$CASE/out" "$CASE/log"
new_case foreign-state; mkdir -p "$CASE/foreign"; : > "$CASE/foreign/secrets.sh"; EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/foreign"); trial create
check "a non-empty folder without the trial marker is refused" refused_early "holds no db:trial state"
new_case sandbox-state; EXTRA=(FAUNOSTEO_TRIAL_DIR="$CASE/shared" FAUNOSTEO_SANDBOX_DIR="$CASE/shared"); trial create
check "the sandbox's state folder is refused" refused_early "sandbox's state folder"
new_case usage; trial
check "no command prints the usage and runs nothing" refused_early "pnpm db:trial create"
new_case remote-docker-host; CASE_DOCKER_HOST=tcp://10.0.0.1:2375; trial status
check "a DOCKER_HOST that is not a unix socket is refused before any call" refused_early "is not a local unix socket"
new_case remote-context; CASE_DOCKER_HOST=''; echo tcp://10.0.0.1:2376 > "$CASE/stub/answers/context"; trial status
check "a current context that is not a unix socket is refused before any change" refused "is not a local unix socket"
new_case local-context; CASE_DOCKER_HOST=''; trial plan
check "no DOCKER_HOST and a unix context: plan answers and prints that endpoint" bash -c '[ "$1" -eq 0 ] && grep -qF "Docker endpoint: unix:///stub/docker.sock" "$2"' _ "$RC" "$CASE/out"
new_case unreadable-context; CASE_DOCKER_HOST=''; : > "$CASE/stub/fail/context"; trial status
check "a current context that cannot be read is refused before any change" refused "cannot be read"
new_case daemon-down; : > "$CASE/stub/fail/daemon"; trial create
check "Docker daemon down: create refuses, no state written" bash -c '[ "$1" -ne 0 ] && grep -qF "Docker daemon unreachable" "$2" && [ ! -e "$3" ]' _ "$RC" "$CASE/out" "$STATE_DIR"
trial plan
check "Docker daemon down: plan still answers and names it" bash -c '[ "$1" -eq 0 ] && grep -qF "Docker daemon is unreachable" "$2" && grep -qF "create would refuse" "$2"' _ "$RC" "$CASE/out"
new_case existing-volume; : > "$CASE/stub/volumes/faunosteo_trial_db"; trial create
check "an existing faunosteo_trial_ volume: create refuses, nothing changed" refused "already exist"
new_case port-taken
if hold_port 58801; then trial create; check "a taken port: create refuses, nothing changed" refused "58801 is taken"; release_port
else fail "could not hold 127.0.0.1:58801 for the port case"; fi
echo "-- plan"
new_case plan; trial plan
check "plan: read-only, proceeds on a clean host" bash -c '[ "$1" -eq 0 ]' _ "$RC"
check "plan: names the Docker endpoint" out_has "Docker endpoint: unix://"
check "plan: no change" [ -z "$(changes)" ]
check "plan: names every container, limit and volume place" bash -c 'grep -qF "faunosteo_trial_db" "$1" && grep -qF -- "--memory 1g" "$1" && grep -qF "/stub/docker-root/volumes/faunosteo_trial_storage/_data" "$1" && grep -qF "create would proceed" "$1"' _ "$CASE/out"
echo "-- create"
new_case create-fails-early; : > "$CASE/stub/fail/networks-create"; trial create
check "create dying before its first resource removes the state files it wrote" bash -c '[ "$1" -ne 0 ] && grep -qF "state files it wrote are removed" "$2" && [ ! -e "$3/secrets.env" ] && [ ! -e "$3/db.env" ] && [ ! -e "$3/stack.conf" ]' _ "$RC" "$CASE/out" "$STATE_DIR"
check "create dying early: nothing to undo in Docker" bash -c '! grep -qE "^docker (volume create|run -d)" "$1"' _ "$CASE/log"
new_case equine-mismatch; echo 'official=2 historical=1 versions=1 draft_equal=true accounts=0' > "$CASE/stub/answers/equine"; trial create
check "create: an equine sheet that is not the seed's stops it, resources named" bash -c '[ "$1" -ne 0 ] && grep -qF "is not the seed" "$2" && grep -qF "nothing was removed" "$2" && ! grep -q "^docker run -d --name faunosteo_trial_rest " "$3"' _ "$RC" "$CASE/out" "$CASE/log"
new_case asset-rows-mismatch; printf '%s\n' "0000 1" > "$CASE/stub/answers/asset-rows"; trial create
check "create: drawing rows that differ from assets.json stop it" bash -c '[ "$1" -ne 0 ] && grep -qF "drawing rows differ" "$2"' _ "$RC" "$CASE/out"
new_case create; trial create
check "create succeeds on an empty host" [ "$RC" -eq 0 ]
check "create: the Rust pipeline check runs before any change" before 'import-equine --check' 'docker network create'
check "create: the helper image holds tar and du, checked before any resource" before 'docker run --rm --name faunosteo_trial_helper --network none --entrypoint sh public.ecr.aws/supabase/postgres:17.11.0.004 -c command -v tar && command -v du' 'docker network create'
check "create: network and volumes under literal trial names" bash -c 'grep -qx "docker network create --label faunosteo.stack=trial faunosteo_trial_net" "$1" && grep -qx "docker volume create --label faunosteo.stack=trial faunosteo_trial_db" "$1" && grep -qx "docker volume create --label faunosteo.stack=trial faunosteo_trial_storage" "$1"' _ "$CASE/log"
check "create: db name, loopback port, caps, volume" expect_run faunosteo_trial_db 58432 5432 1g 512 faunosteo_trial_db:/var/lib/postgresql/data
check "create: mail" expect_run faunosteo_trial_mail 58025 8025 64m 64
check "create: auth" expect_run faunosteo_trial_auth 58999 9999 128m 256
check "create: storage with its volume" expect_run faunosteo_trial_storage 58500 5000 256m 256 faunosteo_trial_storage:/mnt
check "create: rest" expect_run faunosteo_trial_rest 58300 3000 384m 256
check "create: gateway" expect_run faunosteo_trial_gateway 58801 8000 64m 64
check "create: GoTrue sign-up closed" bash -c 'grep -E "^docker run -d --name faunosteo_trial_auth " "$1" | grep -qF GOTRUE_DISABLE_SIGNUP=true' _ "$CASE/log"
check "create: GoTrue mail links on the trial host's app origin" bash -c 'grep -E "^docker run -d --name faunosteo_trial_auth " "$1" | grep -qF "GOTRUE_SITE_URL=http://127.0.0.1:5194"' _ "$CASE/log"
check "create: gateway refuses GoTrue's admin API" log_has 'location /auth/v1/admin/ { return 403; }'
check "create: gateway names itself on /__faunosteo_stack" log_has 'location = /__faunosteo_stack { default_type text/plain; return 200 "faunosteo_trial\n"; }'
check "create: the database init turns statement logging on errors off first" log_has 'set log_min_error_statement = panic;'
check "create: migrations through the CLI on the trial URL only" log_has 'supabase --workdir . migration up --db-url postgresql://postgres@127.0.0.1:58432/postgres?sslmode=disable PGPASSWORD=set'
check "create: equine_draft.sql loaded, seed.sql never" bash -c 'stdin=$(find "$1" -type f); grep -lF -- "-- Official equine anatomical sheet" $stdin >/dev/null && ! grep -lF "faunosteo.local" $stdin >/dev/null' _ "$CASE/stub/stdin"
check "create: the seeded draft is compared with document.json" stdin_has '$faunosteo_trial_doc$'
check "create: only trial names (and Supabase roles) in docker calls" only_trial_names
check "create: no volume removal" no_volume_removal
check "create: every curl pinned to its destination (-q, --noproxy)" curls_pinned
check "create: every container checked on 127.0.0.1 only" log_has 'docker port faunosteo_trial_gateway'
check "create: node ran behind the argv logger" log_has 'node -e '
check "create: state folder 700, backups 700, files 600" bash -c 'for f in "$1" "$1/backups"; do [ "$(stat -c %a "$f")" = 700 ] || exit 1; done; for f in secrets.env db.env auth.env storage.env rest.env stack.conf migrations.sha256 .faunosteo-trial-state; do [ "$(stat -c %a "$1/$f")" = 600 ] || exit 1; done; ! ls "$1"/*.new >/dev/null 2>&1' _ "$STATE_DIR"
check "create: keys for the issuer faunosteo-trial, one year" node -e '
const lines = Object.fromEntries(require("fs").readFileSync(process.argv[1], "utf8").trim().split("\n").map((l) => [l.slice(0, l.indexOf("=")), l.slice(l.indexOf("=") + 1)]));
for (const [name, role] of [["TR_ANON_KEY", "anon"], ["TR_SERVICE_KEY", "service_role"]]) {
const p = JSON.parse(Buffer.from(lines[name].split(".")[1], "base64url"));
if (p.iss !== "faunosteo-trial" || p.role !== role || p.exp - p.iat !== 31536000) process.exit(1);
}' "$STATE_DIR/secrets.env"
check "create: stack.conf records the six images, no host" bash -c '! grep -q "^TRIAL_HOST=" "$1" && [ "$(grep -c "^IMAGE_" "$1")" -eq 6 ]' _ "$STATE_DIR/stack.conf"
check "create: one manifest line per migration file" [ "$(wc -l < "$STATE_DIR/migrations.sha256")" -eq "$(find "$WORK/repo/supabase/migrations" -name '*.sql' | wc -l)" ]
check "create: no secret in any argv (docker, curl, node, env, tools) or output" secrets_absent "$STATE_DIR/secrets.env"
fresh_log; trial create
check "a second create refuses, nothing changed" refused "already holds a trial stack"
echo "-- stop, start, status"
if hold_port 5194; then fresh_log; trial stop; check "stop while port 5194 is in use (the app): refused, nothing stopped" refused "5194 is in use"; release_port
else fail "could not hold 127.0.0.1:5194 for the stop case"; fi
fresh_log; trial stop
check "stop: each container stopped, reverse order, nothing else" bash -c '[ "$1" -eq 0 ] && [ "$2" = "$(printf "docker stop -t 60 %s\n" faunosteo_trial_gateway faunosteo_trial_rest faunosteo_trial_storage faunosteo_trial_auth faunosteo_trial_mail faunosteo_trial_db)" ]' _ "$RC" "$(changes)"
if hold_port 58300; then fresh_log; trial start; check "start: a taken port refuses before any container starts" refused "58300 is taken"; release_port
else fail "could not hold 127.0.0.1:58300 for the start case"; fi
cp -p "$CASE/stub/ports/faunosteo_trial_rest" "$CASE/rest.port"
echo '3000/tcp -> 0.0.0.0:58300' > "$CASE/stub/ports/faunosteo_trial_rest"
fresh_log; trial start
check "start: a container published on 0.0.0.0 is refused" bash -c '[ "$1" -ne 0 ] && grep -qF "faunosteo_trial_rest is not published on 127.0.0.1:58300 only" "$2"' _ "$RC" "$CASE/out"
cp -p "$CASE/rest.port" "$CASE/stub/ports/faunosteo_trial_rest"
fresh_log; trial stop; fresh_log; trial start
check "start: same containers in order, nothing created" bash -c '[ "$1" -eq 0 ] && [ "$2" = "$(printf "docker start %s\n" faunosteo_trial_db faunosteo_trial_mail faunosteo_trial_auth faunosteo_trial_storage faunosteo_trial_rest faunosteo_trial_gateway)" ]' _ "$RC" "$(changes)"
rm -f "$CASE/stub/containers/faunosteo_trial_rest" "$CASE/stub/ports/faunosteo_trial_rest"
fresh_log; trial start
check "start: a missing container is recreated, then the running gateway restarted" bash -c '[ "$1" -eq 0 ] && [ "$(printf "%s\n" "$2" | grep -c .)" -eq 2 ] && printf "%s\n" "$2" | head -n 1 | grep -q "^docker run -d --name faunosteo_trial_rest " && printf "%s\n" "$2" | tail -n 1 | grep -qx "docker restart -t 10 faunosteo_trial_gateway"' _ "$RC" "$(changes)"
check "start: the recreation leaves no secret in argv" secrets_absent "$STATE_DIR/secrets.env"
fresh_log; trial status
check "status: read-only, keys' expiry, no secret" bash -c '[ "$1" -eq 0 ] && [ -z "$2" ] && grep -qF "keys valid until" "$3"' _ "$RC" "$(changes)" "$CASE/out"
check "status: no secret in its output" secrets_absent "$STATE_DIR/secrets.env"
echo "-- half-written state"
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.good"
printf 'TR_PW=short\n' > "$STATE_DIR/secrets.env"
for action in start stop status; do
fresh_log; trial "$action"
check "malformed secrets: $action refuses, nothing changed" refused "malformed"
done
cp -p "$CASE/secrets.good" "$STATE_DIR/secrets.env"
chmod 644 "$STATE_DIR/secrets.env"; fresh_log; trial status
check "secrets readable by others: refused" refused "mode 600"
chmod 600 "$STATE_DIR/secrets.env"
echo "-- db:sandbox state guard"
new_case sandbox-guard; mkdir -p "$CASE/trialstate"; : > "$CASE/trialstate/.faunosteo-trial-state"
EXTRA=(FAUNOSTEO_SANDBOX_DIR="$CASE/trialstate"); sandbox down
check "db:sandbox down refuses a folder holding the trial marker, nothing run" refused_early "db:trial state folder"
EXTRA=(FAUNOSTEO_SANDBOX_DIR="$CASE/same" FAUNOSTEO_TRIAL_DIR="$CASE/same"); sandbox down
check "db:sandbox down refuses the db:trial state folder by path" refused_early "db:trial state folder"
fresh_log; EXTRA=(FAUNOSTEO_SANDBOX_DIR="$CASE/sandbox-state"); sandbox down
check "db:sandbox down on its own folder names faunosteo_dbsandbox_ resources only" bash -c '[ "$1" -eq 0 ] && grep -q "^docker rm -f faunosteo_dbsandbox_" "$2" && ! grep -oE "faunosteo_[a-z_]+" "$2" | grep -qv "^faunosteo_dbsandbox_"' _ "$RC" "$CASE/log"
```
#### Harness H4: summary (always last)
```bash
echo "-- $PASS passed, $FAIL failed"
if [ "$FAIL" -eq 0 ] && [ "$PASS" -gt 0 ]; then echo "db-trial-check: ALL PASS"; else exit 1; fi
```
#### Lot B1 review fixes (2026-11-10)
The files `scripts/db-trial.sh` and `scripts/db-trial-check.sh` intentionally differ from the B1 code blocks above:
- Harness: the equine-seed check is two checks (`stdin_has`, `stdin_lacks`); no unquoted `$stdin`, no read of the harness stdin.
- Harness: `verify_stub_shadow` runs in every `run_script` with the case's own PATH (ss: the stub, or absent for the ss-absent case); a mismatch prints the reason and exits 1 for the whole run. PATH is applied after EXTRA; a PATH in EXTRA is refused.
- `new_secrets_file`: written to `<file>.new` under umask 077, then `mv -f`; `.new` removed on failure.
- Git guard: `refuse_inside_git` refuses a `.git` entry (file or folder) in any ancestor, then asks git with GIT_DIR, GIT_WORK_TREE and GIT_CEILING_DIRECTORIES unset (a subshell `unset`, not `env -u`: the harness `env` wrapper logs argv); only "not a git repository" (exit 128) is outside, any other answer dies.
- `container_exists`, `volume_exists`, `network_exists` go through `resource_exists`: only an error matching, case-insensitively, "no such" or "not found" is absent (real wordings: `No such container: x`, `get x: no such volume`, `network x not found`), any other inspect error dies naming the resource. Docker stub: absent answers those three real wordings, `volume create` is idempotent, `fail/inspect-error`, `fail/helper` and `fail/networks-create-after` modes.
- `create_state_folder`: lock taken right after mkdir and chmod, `secrets.env` check repeated under it, marker written after.
- `FIRST_RESOURCE=yes` is set before `docker network create`; `create_stopped` then removes the state files only when no faunosteo_trial_ resource exists (existing case kept), else names what is present.
- `take_lock` opens `.lock` under umask 077 (harness asserts 600).
- `require_local_daemon` prints the endpoint without anything up to the last `@`.
- `check_helper_image` message names its two causes and the `docker rm` way out.
- `cmd_plan`, `cmd_create`, `cmd_status`, `status_database` split into helpers (`plan_*`, `create_*`, `status_*`), same output; plan sections append refusals through a nameref array.
- Harness header: `hold_port` opens real loopback listeners on 58801, 5194 and 58300 for up to 30 s, closed at cleanup.
- Kept for B2/B3: `client_key`, `gateway_identity`, `key_identity`, `restart_gateway` and the other helpers; no B2/B3 block relies on a changed behaviour (`new_secrets_file "$next" || true` stays correct).
---
### Lot B2: backup, restore, destroy, migrate, rotate-keys, rotate-secrets
**Files:**
- Modify: `scripts/db-trial.sh` (section 7 before the dispatch; dispatch lines)
- Modify: `scripts/db-trial-check.sh` (cases H5 inserted before H4)
**Interfaces:**
- Consumes: all of B1.
- Produces: `confirm <phrase>` (terminal only), `backup_now <label> <yes|no>` (sets `LAST_BACKUP`; archives every
existing trial volume, copies the state files present; refuses rather than keep a partial folder), `check_backup
<dir>`.
- [ ] **Step 1:** add the H5 cases; run the harness; expect FAIL on them only.
- [ ] **Step 2:** add section 7 and the dispatch lines.
- [ ] **Step 3:** harness to `ALL PASS`; positive controls (then revert): drop `:ro` from the archive mount → FAIL;
move `backup_now pre-migrate` after `migrate_up` → FAIL; skip `sha256sum -c` in `check_backup` → FAIL; make the
rotation SQL use `$new_pw` literally instead of `\set … printf` → FAIL; drop `[ -t 0 ]` from `confirm` → FAIL;
drop the entry-count comparison in `backup_volume` → the short-archive case FAILS; drop `published_exactly "$DB"`
from `cmd_migrate` → "migrate: Postgres published beyond 127.0.0.1" FAILS; drop the backup key check in
`check_backup` → "restore: a backup whose key is not signed" FAILS; `check_backup "$LAST_BACKUP"` in either
rotation is no longer caught by these cases (its key check is covered by the restore case), so instead drop
`require_trial_keys` from either rotation → its "keys that are not this stack's are refused before any stop" case
FAILS; drop the exit-status test in `port_listening` → "ss failing" FAILS; drop `umask 077` in `new_backup_dir` →
the backup 700 checks FAIL; drop the image check in `check_backup` → "restore: the recorded database image is
absent" FAILS. Report them.
- [ ] **Step 4: orchestrator commits** `feat(supabase): db:trial cold backups, restore, migrate, rotation` + push.
#### Section 7
```bash
# The exact phrase, typed at a terminal: piped stdin (an agent, a script) is refused before the
# prompt; anything else typed, or nothing, stops too.
confirm() { # phrase
local typed=''
[ -t 0 ] || die "a typed confirmation needs a terminal (stdin is not one): nothing was changed"
printf 'Type "%s" to go on (anything else stops): ' "$1" >&2
read -r typed || true
[ "$typed" = "$1" ] || die "not confirmed: nothing was changed"
}
# Every name a backup folder may hold: a failed backup removes exactly these, then the folder.
BACKUP_FILES=(db.tar storage.tar secrets.env stack.conf migrations.sha256 SHA256SUMS SHA256SUMS.new)
new_backup_dir() { # label → a fresh folder (700)
local base dir n=1
base="$BACKUPS/$(date -u +%Y%m%dT%H%M%SZ)-$1"
dir="$base"
while [ -e "$dir" ]; do n=$((n + 1)); dir="$base-$n"; done
( umask 077; mkdir -p "$BACKUPS"; mkdir "$dir" ) || die "cannot create $dir"
printf '%s' "$dir"
}
# Size in KiB and entry count of a volume, by the helper (read-only, no network); the entries are
# counted from the root tar archives, so the archive must list as many.
volume_measure() { # volume → "<KiB> <entries>"
docker run --rm --name "$HELPER" --network none -v "$1:/volume:ro" --entrypoint sh "$IMAGE_DB" \
-c 'du -sk /volume | cut -f1 && cd /volume && find . ! -type s | wc -l' | tr '\n' ' '
}
# The listing of an archive cut at a member boundary still exits 0: its entry count is compared.
backup_volume() { # dir volume entries
local file="$1/${2#faunosteo_trial_}.tar" listed
( umask 077
docker run --rm --name "$HELPER" --network none -v "$2:/volume:ro" --entrypoint tar "$IMAGE_DB" \
--numeric-owner -C /volume -cf - . > "$file" ) || return 1
listed="$(tar -tf "$file" | wc -l)" || return 1
[ "$listed" -eq "$3" ]
}
backup_failed() { # dir why: this run's files go by name, then the (now empty) folder
local f
for f in "${BACKUP_FILES[@]}"; do rm -f -- "$1/$f"; done
rmdir -- "$1" 2>/dev/null || true
die "backup failed ($2): nothing kept, the stack stays stopped (pnpm db:trial start)"
}
# Cold copy of every trial volume that exists, with the state files present (the volumes are the
# data, the files only follow): the stack stops, the volumes are measured and free space checked,
# each volume is archived read-only by a --rm helper with no network and its listing must hold
# as many entries as the volume, then the checksums are written last
# (a folder without SHA256SUMS never finished). The stack restarts if it ran and the caller asks.
backup_now() { # label restart(yes|no) → LAST_BACKUP
local dir name vol kib entries i free need=0 vols=() counts=() was_running=no
for name in "${CONTAINERS[@]}"; do if container_running "$name"; then was_running=yes; fi; done
for vol in "$VOL_DB" "$VOL_STORAGE"; do if volume_exists "$vol"; then vols+=("$vol"); fi; done
[ "${#vols[@]}" -gt 0 ] || die "no trial volume to back up"
dir="$(new_backup_dir "$1")"
stop_containers
for vol in "${vols[@]}"; do
read -r kib entries <<<"$(volume_measure "$vol")"
if ! [[ "$kib" =~ ^[0-9]+$ && "$entries" =~ ^[0-9]+$ ]]; then backup_failed "$dir" "measure of $vol"; fi
need=$((need + kib))
counts+=("$entries")
done
# The backups share the disk of Docker's data root (DEV, the sandbox): never fill it.
free="$(df -Pk "$dir" | awk 'NR == 2 { print $4 }')"
if ! [[ "$free" =~ ^[0-9]+$ ]] || [ "$free" -lt $((need * 11 / 10)) ]; then
backup_failed "$dir" "less free space under $BACKUPS than 110 % of the volumes"
fi
for i in "${!vols[@]}"; do
backup_volume "$dir" "${vols[$i]}" "${counts[$i]}" || backup_failed "$dir" "archive of ${vols[$i]} unreadable or incomplete"
done
for name in secrets.env stack.conf migrations.sha256; do
if [ -f "$STATE/$name" ]; then cp -p "$STATE/$name" "$dir/" || backup_failed "$dir" "copy of $name"; fi
done
( cd "$dir" && umask 077 && sha256sum -- * > SHA256SUMS.new && mv SHA256SUMS.new SHA256SUMS ) \
|| backup_failed "$dir" "checksums"
LAST_BACKUP="$dir"
if [ "$2" = yes ] && [ "$was_running" = yes ]; then start_containers; fi
say "backup $(basename "$dir") ($(du -sh "$dir" | cut -f1)) in $BACKUPS"
}
cmd_backup() {
[ $# -eq 0 ] || die "backup takes no argument"
require_created
take_lock
backup_now manual yes
}
secret_digest() { grep -E '^TR_(PW|JWT_SECRET)=' "$1" | sha256sum | cut -d' ' -f1; }
# Restorable only when the checksums hold, both archives list, the secrets and stack.conf parse
# and the database image it recorded is here: all checked before any change.
check_backup() { # dir
local name
name="$(basename "$1")"
[ -f "$1/SHA256SUMS" ] || die "backup $name is incomplete (no SHA256SUMS): nothing was changed"
( cd "$1" && sha256sum --quiet --strict -c SHA256SUMS ) || die "backup $name fails its checksums: nothing was changed"
{ tar -tf "$1/db.tar" && tar -tf "$1/storage.tar"; } >/dev/null 2>&1 \
|| die "backup $name lacks a readable archive of both volumes: nothing was changed"
( parse_secrets "$1/secrets.env" && parse_conf "$1/stack.conf" ) 2>/dev/null \
|| die "backup $name holds incomplete secrets or stack.conf: nothing was changed"
# Its keys must be signed with its own secret (expiry aside: rotate-keys renews them after).
( parse_secrets "$1/secrets.env" && key_check "$TR_ANON_KEY" anon any "$TR_JWT_SECRET" \
&& key_check "$TR_SERVICE_KEY" service_role any "$TR_JWT_SECRET" ) >/dev/null 2>&1 \
|| die "backup $name holds keys that are not signed with its own secret: nothing was changed"
( parse_conf "$1/stack.conf" && docker image inspect -f '{{.Id}}' "$IMAGE_DB" >/dev/null 2>&1 ) \
|| die "the database image recorded by backup $name is not present locally: nothing was changed (docker pull it first)"
}
# Rebuilds the stack from <backup>: volumes and secrets replaced, containers recreated from the
# images the backup recorded. Works on a destroyed stack too (backups survive destroy).
cmd_restore() {
local name="${1:-}" dir vol f
[ $# -eq 1 ] && [[ "$name" =~ ^[0-9]{8}T[0-9]{6}Z-[a-z0-9-]+$ ]] \
|| die "restore <backup>: the name of a folder in $BACKUPS (pnpm db:trial status lists them)"
require_state
take_lock
dir="$BACKUPS/$name"
[ -d "$dir" ] || die "no backup $name in $BACKUPS"
check_backup "$dir"
refuse_while_app_runs
if [ -f "$CONF" ]; then parse_conf "$CONF" || true; fi
if [ -f "$SECRETS" ] && [ "$(secret_digest "$SECRETS")" != "$(secret_digest "$dir/secrets.env")" ]; then
echo "note: backup $name holds other secrets than the current ones (taken before a rotate-secrets?): the restore brings them back"
fi
echo "restore replaces the trial stack's data and secrets by backup $name. Present now:"
existing_resources | sed 's/^/ /'
confirm "restore $name"
if volume_exists "$VOL_DB" || volume_exists "$VOL_STORAGE"; then
backup_now pre-restore no
else
echo "no trial volume: nothing to back up before the restore"
fi
stop_containers
ports_free "${CONTAINERS[@]}"
remove_containers
parse_conf "$dir/stack.conf"
for vol in "$VOL_DB" "$VOL_STORAGE"; do
if volume_exists "$vol"; then docker volume rm "$vol" >/dev/null; fi
docker volume create --label faunosteo.stack=trial "$vol" >/dev/null
docker run --rm -i --name "$HELPER" --network none -v "$vol:/volume" --entrypoint tar "$IMAGE_DB" \
--numeric-owner -xpf - -C /volume < "$dir/${vol#faunosteo_trial_}.tar"
done
remove_live_files
for f in secrets.env stack.conf migrations.sha256; do
if [ -f "$dir/$f" ]; then cp -p "$dir/$f" "$STATE/"; fi
done
load_conf
load_secrets
start_containers
say "restored $(basename "$dir")${LAST_BACKUP:+; the state before it: $(basename "$LAST_BACKUP")}"
}
cmd_destroy() {
[ $# -eq 0 ] || die "destroy takes no argument"
local found vol
require_state
take_lock
refuse_while_app_runs
if [ -f "$CONF" ]; then parse_conf "$CONF" || true; fi
found="$(existing_resources)"
if [ -z "$found" ]; then
echo "no faunosteo_trial_ resource exists: destroy removes the live state files of $STATE (backups kept)"
confirm faunosteo_trial
remove_live_files
say "live state files removed; backups kept in $BACKUPS"
return 0
fi
echo "destroy removes:"
printf '%s\n' "$found" | sed 's/^/ /'
if container_running "$DB" && ( parse_secrets "$SECRETS" ) 2>/dev/null; then
load_secrets
echo "content:"
inventory_counts | sed 's/^/ /'
fi
echo "kept: every backup in $BACKUPS, including the one taken now"
confirm faunosteo_trial
if volume_exists "$VOL_DB" || volume_exists "$VOL_STORAGE"; then backup_now pre-destroy no; fi
stop_containers
remove_containers
if network_exists "$NET"; then docker network rm "$NET" >/dev/null; fi
for vol in "$VOL_DB" "$VOL_STORAGE"; do if volume_exists "$vol"; then docker volume rm "$vol" >/dev/null; fi; done
remove_live_files
say "trial stack destroyed; backups kept in $BACKUPS"
}
migrate_stopped() {
local status=$?
[ "$status" -ne 0 ] || return 0
echo "db-trial: migrate stopped part-way: pnpm db:trial restore $(basename "$LAST_BACKUP") returns to the state before it" >&2
}
cmd_migrate() {
[ $# -eq 0 ] || die "migrate takes no argument"
local pending
require_running
take_lock
published_exactly "$DB"
check_manifest
pending="$(pending_migrations)"
[ -n "$pending" ] || { say "no new migration"; return 0; }
echo "new migrations:"
printf '%s\n' "$pending" | sed 's/^/ /'
backup_now pre-migrate yes
trap migrate_stopped EXIT
migrate_up
write_manifest
sb_psql -c "-- db-trial:reload
notify pgrst, 'reload schema'" >/dev/null
trap - EXIT
say "migrated; the backup before it: $(basename "$LAST_BACKUP")"
}
keys_stopped() {
local status=$?
[ "$status" -ne 0 ] || return 0
echo "db-trial: rotate-keys stopped part-way; secrets.env holds the new keys; pnpm db:trial start recreates a missing container and restarts the gateway" >&2
}
# A backup first (secrets.env included), then new keys signed with the same secret: the expiry moves one year on. The previous keys stay valid
# until their own expiry (rotate-secrets revokes them). Storage holds the keys in its env; the
# gateway restarts after it (nginx resolved the old container's address).
cmd_rotate_keys() {
[ $# -eq 0 ] || die "rotate-keys takes no argument"
local was_running=no
# A created stack is enough, running or stopped: the way out of expired keys must never need
# the keys to be valid.
require_created
take_lock
refuse_while_app_runs
# Keys that are not this stack's: refused before the backup stops anything.
require_trial_keys
if container_running "$DB"; then was_running=yes; fi
# The same cold, read-back backup as rotate-secrets, before any change, and proven restorable
# (checksums, archives, keys signed with its secret, image) before any key changes.
backup_now pre-keys yes
check_backup "$LAST_BACKUP"
if ! ( umask 077
printf 'TR_PW=%s\nTR_JWT_SECRET=%s\n' "$TR_PW" "$TR_JWT_SECRET" > "$SECRETS.new"
TR_JWT_SECRET="$TR_JWT_SECRET" node -e "$KEYGEN" keys >> "$SECRETS.new" ) \
|| ! ( parse_secrets "$SECRETS.new" ); then
rm -f "$SECRETS.new"
die "key generation failed: nothing was changed"
fi
trap keys_stopped EXIT
mv -f "$SECRETS.new" "$SECRETS"
load_secrets
write_env_files
if container_running "$STORAGE"; then docker stop -t 30 "$STORAGE" >/dev/null; fi
if container_exists "$STORAGE"; then docker rm "$STORAGE" >/dev/null; fi
if [ "$was_running" = yes ]; then
run_service "$STORAGE"
restart_gateway
check_loopback
fi
trap - EXIT
say "anon and service keys renewed, valid until $(key_expiry); the app takes the new anon key at its next start"
if [ "$was_running" = no ]; then echo "the stack is stopped: pnpm db:trial start recreates Storage with the new keys"; fi
}
rotation_stopped() {
local status=$?
[ "$status" -ne 0 ] || return 0
if [ "$ROTATION_COMMITTED" = yes ]; then
echo "db-trial: rotate-secrets stopped after the database took the new secrets; secrets.env holds them." >&2
echo "pnpm db:trial start recreates the missing containers from it." >&2
else
echo "db-trial: rotate-secrets stopped before the commit was known; secrets.env (previous) and secrets.next.env (new) are both kept." >&2
echo "pnpm db:trial restore $(basename "$LAST_BACKUP") returns to the state before it." >&2
fi
}
ROTATION_COMMITTED=no
# One transaction run as supabase_admin; values through psql variables read from the exec
# environment (never argv, never the SQL text); statement logging off, errors included. The
# same transaction records the digest of the new secrets file in a database setting: the only
# trustworthy proof of the commit (password probes over 127.0.0.1 are `trust` in this image's
# pg_hba.conf and succeed whatever the password).
rotate_in_database() { # new password, new JWT secret, digest of the new secrets file
PGPASSWORD="$TR_PW" NEW_PW="$1" NEW_JWT="$2" ROT_DIGEST="$3" \
docker exec -i -e PGPASSWORD -e NEW_PW -e NEW_JWT -e ROT_DIGEST "$DB" \
psql -X -q -h 127.0.0.1 -U supabase_admin -d postgres -v ON_ERROR_STOP=1 --single-transaction >/dev/null <<'SQL'
-- db-trial:rotate
set local log_statement = 'none';
set local log_min_duration_statement = -1;
set local log_min_error_statement = panic;
set local pgaudit.log = 'none';
\set newpw `printf '%s' "$NEW_PW"`
\set newjwt `printf '%s' "$NEW_JWT"`
\set digest `printf '%s' "$ROT_DIGEST"`
alter role supabase_admin with password :'newpw';
alter role postgres with password :'newpw';
alter role authenticator with password :'newpw';
alter role supabase_auth_admin with password :'newpw';
alter role supabase_storage_admin with password :'newpw';
alter database postgres set "app.settings.jwt_secret" to :'newjwt';
alter database postgres set "app.trial_rotation" to :'digest';
SQL
}
# The setting the rotation transaction wrote, read in a fresh session (a database setting applies
# to new sessions only). Empty when no rotation committed.
rotation_marker() {
sb_psql -t -A -c "-- db-trial:rotation-marker
select coalesce(current_setting('app.trial_rotation', true), '')"
}
# psql failed: did the transaction commit anyway (a dropped exec stream, a daemon hiccup)? The
# marker decides; the new values are dropped only when the database answers without it.
settle_rotation() { # next file, digest of it
local marker
marker="$(rotation_marker 2>/dev/null)" \
|| die "the database does not answer: whether the rotation committed is unknown"
if [ "$marker" = "$2" ]; then
say "the database took the new secrets although psql failed: the rotation goes on"
return 0
fi
rm -f "$1"
trap - EXIT
die "the database did not take the new secrets: nothing was changed (backup $(basename "$LAST_BACKUP"))"
}
# New JWT secret, keys and role passwords; account passwords and TOTP factors untouched. The
# new values sit in secrets.next.env (600) before the database changes, so they exist somewhere
# whatever happens next (Ctrl+C included: it exits through the EXIT trap, which keeps that file).
cmd_rotate_secrets() {
[ $# -eq 0 ] || die "rotate-secrets takes no argument"
local next="$STATE/secrets.next.env" new_pw new_jwt digest
require_running
take_lock
refuse_while_app_runs
# Keys that are not this stack's: refused before the backup stops anything.
require_trial_keys
backup_now pre-rotate yes
# The backup is the only copy of the previous secrets (no secrets.prev): proven restorable first.
check_backup "$LAST_BACKUP"
trap rotation_stopped EXIT
new_secrets_file "$next" || true
if ! ( parse_secrets "$next" ); then rm -f "$next"; trap - EXIT; die "secret generation failed: nothing was changed"; fi
new_pw="$(sed -n 's/^TR_PW=//p' "$next")"
new_jwt="$(sed -n 's/^TR_JWT_SECRET=//p' "$next")"
digest="$(sha256sum < "$next" | cut -d' ' -f1)"
if ! rotate_in_database "$new_pw" "$new_jwt" "$digest"; then settle_rotation "$next" "$digest"; fi
ROTATION_COMMITTED=yes
mv -f "$next" "$SECRETS"
load_secrets
# The containers carry the previous values in their env: removed at once, recreated by
# start_containers (which checks every port before any start).
stop_containers
remove_containers
start_containers
trap - EXIT
say "secrets renewed: sessions signed with the old secret are refused (sign in again); account passwords and TOTP factors unchanged; backup before it: $(basename "$LAST_BACKUP")"
}
```
Argument line (inside `check_arguments`' case, before `*)`):
```bash
migrate | backup | rotate-keys | rotate-secrets | destroy)
[ $# -eq 0 ] || die "$action takes no argument" ;;
restore)
[ $# -eq 1 ] && [[ "$1" =~ ^[0-9]{8}T[0-9]{6}Z-[a-z0-9-]+$ ]] \
|| die "restore <backup>: the name of a folder in $BACKUPS (pnpm db:trial status lists them)" ;;
```
Dispatch lines (inside the second `case`):
```bash
migrate) cmd_migrate "$@" ;;
backup) cmd_backup "$@" ;;
restore) cmd_restore "$@" ;;
rotate-keys) cmd_rotate_keys "$@" ;;
rotate-secrets) cmd_rotate_secrets "$@" ;;
destroy) cmd_destroy "$@" ;;
```
#### Harness H5 (insert before H4)
```bash
echo "-- backup"
new_case backups; trial create
check "backups: create" [ "$RC" -eq 0 ]
fresh_log; trial backup
BK="$(find "$STATE_DIR/backups" -mindepth 1 -maxdepth 1 -type d -name '*-manual' | head -n 1)"
check "backup: folder 700, files 600, checksums valid, both archives listed" bash -c '[ -n "$1" ] && [ "$(stat -c %a "$1")" = 700 ] && for f in db.tar storage.tar secrets.env stack.conf migrations.sha256 SHA256SUMS; do [ "$(stat -c %a "$1/$f")" = 600 ] || exit 1; done && cd "$1" && sha256sum --quiet -c SHA256SUMS && grep -q " db.tar$" SHA256SUMS && grep -q " storage.tar$" SHA256SUMS && [ ! -e SHA256SUMS.new ]' _ "$BK"
check "backup: both volumes measured by the helper, read-only, no network" bash -c 'grep -qF "docker run --rm --name faunosteo_trial_helper --network none -v faunosteo_trial_db:/volume:ro --entrypoint sh public.ecr.aws/supabase/postgres:17.11.0.004 -c du -sk /volume" "$1" && grep -qF "docker run --rm --name faunosteo_trial_helper --network none -v faunosteo_trial_storage:/volume:ro --entrypoint sh public.ecr.aws/supabase/postgres:17.11.0.004 -c du -sk /volume" "$1"' _ "$CASE/log"
check "backup: both volumes archived read-only by the helper, no network" bash -c 'grep -qF "docker run --rm --name faunosteo_trial_helper --network none -v faunosteo_trial_db:/volume:ro --entrypoint tar" "$1" && grep -qF "docker run --rm --name faunosteo_trial_helper --network none -v faunosteo_trial_storage:/volume:ro --entrypoint tar" "$1"' _ "$CASE/log"
check "backup: the stack stops before the copy and restarts after" bash -c 'stop=$(grep -n "^docker stop" "$1" | tail -n 1 | cut -d: -f1); helper=$(grep -n faunosteo_trial_helper "$1" | head -n 1 | cut -d: -f1); start=$(grep -n "^docker start" "$1" | head -n 1 | cut -d: -f1); [ -n "$stop" ] && [ -n "$helper" ] && [ -n "$start" ] && [ "$stop" -lt "$helper" ] && [ "$helper" -lt "$start" ]' _ "$CASE/log"
check "backup: no volume removal" no_volume_removal
check "backup: no secret in calls or output" secrets_absent "$STATE_DIR/secrets.env"
COUNT="$(backup_count)"; : > "$CASE/stub/fail/archive"; fresh_log; trial backup
check "a failed archive: no folder kept, the stack left stopped, the way out named" bash -c '[ "$1" -ne 0 ] && [ "$2" -eq "$3" ] && grep -qF "backup failed" "$4" && grep -qF "pnpm db:trial start" "$4"' _ "$RC" "$COUNT" "$(backup_count)" "$CASE/out"
rm -f "$CASE/stub/fail/archive"; fresh_log; trial start
check "after a failed backup, start brings the stack back" [ "$RC" -eq 0 ]
COUNT="$(backup_count)"; : > "$CASE/stub/short-archive"; fresh_log; trial backup
check "an archive that lists fewer entries than its volume (exit 0): refused, no folder kept" bash -c '[ "$1" -ne 0 ] && [ "$2" -eq "$3" ] && grep -qF "unreadable or incomplete" "$4"' _ "$RC" "$COUNT" "$(backup_count)" "$CASE/out"
rm -f "$CASE/stub/short-archive"; fresh_log; trial start
mkdir -m 700 "$STATE_DIR/backups/20000101T000000Z-partial"
fresh_log; trial status
check "status marks a folder without SHA256SUMS incomplete" bash -c 'grep -F "20000101T000000Z-partial" "$1" | grep -qF incomplete' _ "$CASE/out"
fresh_log; trial restore 20000101T000000Z-partial
check "restore refuses an incomplete backup before any change" refused "is incomplete"
rmdir "$STATE_DIR/backups/20000101T000000Z-partial"
echo "-- migrate"
cp -p "$CASE/stub/ports/faunosteo_trial_db" "$CASE/db.port"
echo '5432/tcp -> 0.0.0.0:58432' > "$CASE/stub/ports/faunosteo_trial_db"
fresh_log; trial migrate
check "migrate: Postgres published beyond 127.0.0.1 is refused before any backup" bash -c '[ "$1" -ne 0 ] && grep -qF "faunosteo_trial_db is not published on 127.0.0.1:58432 only" "$2" && ! grep -qF faunosteo_trial_helper "$3"' _ "$RC" "$CASE/out" "$CASE/log"
cp -p "$CASE/db.port" "$CASE/stub/ports/faunosteo_trial_db"
fresh_log; trial migrate
check "migrate with nothing new: no backup, no change" bash -c '[ "$1" -eq 0 ] && grep -qF "no new migration" "$2" && ! grep -qF faunosteo_trial_helper "$3"' _ "$RC" "$CASE/out" "$CASE/log"
FIRST="$(find "$WORK/repo/supabase/migrations" -name '*.sql' | sort | head -n 1)"
cp -p "$FIRST" "$CASE/first.sql"; printf -- '-- edited\n' >> "$FIRST"
fresh_log; trial migrate
check "an applied migration edited in place: refused before any backup" refused "was edited after it was applied"
cp -p "$CASE/first.sql" "$FIRST"
printf -- '-- harness\n' > "$WORK/repo/supabase/migrations/20000101000000_harness_old.sql"
fresh_log; trial migrate
check "a new migration older than the last applied: refused before any backup" refused "older than the last applied migration"
rm -f "$WORK/repo/supabase/migrations/20000101000000_harness_old.sql"
printf -- '-- harness\n' > "$WORK/repo/supabase/migrations/29991231000000_harness_new.sql"
fresh_log; trial migrate
check "migrate: succeeds" [ "$RC" -eq 0 ]
check "migrate: the backup precedes the migration" before 'faunosteo_trial_helper' 'supabase --workdir . migration up'
check "migrate: the new file joins migrations.sha256" grep -q '^29991231000000 ' "$STATE_DIR/migrations.sha256"
check "migrate: PostgREST schema reloaded" log_has "notify pgrst, 'reload schema'"
printf -- '-- harness\n' > "$WORK/repo/supabase/migrations/29991231000001_harness_newer.sql"
: > "$CASE/stub/fail/migrate"; fresh_log; trial migrate
check "a migrate that stops part-way names its pre-migrate backup" bash -c '[ "$1" -ne 0 ] && grep -qE "restore [0-9]{8}T[0-9]{6}Z-pre-migrate" "$2"' _ "$RC" "$CASE/out"
fresh_log; trial status
check "after it, status names the remedy" out_has "pre-migrate backup"
rm -f "$CASE/stub/fail/migrate" "$WORK/repo/supabase/migrations/29991231000001_harness_newer.sql"
( cd "$WORK/repo" && for f in supabase/migrations/*.sql; do basename "$f" | cut -d_ -f1; done ) > "$CASE/stub/answers/applied"
rm -f "$WORK/repo/supabase/migrations/29991231000000_harness_new.sql"
echo "-- rotate-keys"
if hold_port 5194; then fresh_log; trial rotate-keys; check "rotate-keys while port 5194 is in use (the app): refused, nothing changed" refused "5194 is in use"; release_port
else fail "could not hold 127.0.0.1:5194 for the app case"; fi
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.before-keys"
fresh_log; trial rotate-keys
check "rotate-keys: password and JWT secret kept, keys renewed" bash -c '[ "$1" -eq 0 ] && [ "$(grep -E "^TR_(PW|JWT_SECRET)=" "$2")" = "$(grep -E "^TR_(PW|JWT_SECRET)=" "$3")" ] && [ "$(grep "^TR_ANON_KEY=" "$2")" != "$(grep "^TR_ANON_KEY=" "$3")" ] && [ "$(stat -c %a "$2")" = 600 ]' _ "$RC" "$STATE_DIR/secrets.env" "$CASE/secrets.before-keys"
check "rotate-keys: a backup first, then storage recreated and the gateway restarted, nothing else" bash -c 'last=$(printf "%s\n" "$1" | tail -n 4); printf "%s\n" "$last" | head -n 1 | grep -qx "docker stop -t 30 faunosteo_trial_storage" && printf "%s\n" "$last" | grep -qx "docker rm faunosteo_trial_storage" && printf "%s\n" "$last" | grep -q "^docker run -d --name faunosteo_trial_storage " && printf "%s\n" "$last" | tail -n 1 | grep -qx "docker restart -t 10 faunosteo_trial_gateway" && printf "%s\n" "$1" | head -n 1 | grep -q "^docker stop -t 60 "' _ "$(changes)"
check "rotate-keys: its backup is restorable and protected (700, 600, checksums)" bash -c 'd=$(ls -d "$1"/*-pre-keys* | head -n 1); [ -n "$d" ] && [ "$(stat -c %a "$d")" = 700 ] && for f in db.tar storage.tar secrets.env stack.conf migrations.sha256 SHA256SUMS; do [ "$(stat -c %a "$d/$f")" = 600 ] || exit 1; done && cd "$d" && sha256sum --quiet -c SHA256SUMS && cmp -s secrets.env "$2"' _ "$STATE_DIR/backups" "$CASE/secrets.before-keys"
check "rotate-keys: no secret in calls or output" secrets_absent "$STATE_DIR/secrets.env"
echo "-- rotate-secrets"
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.before-refusal"
: > "$CASE/stub/fail/rotate"; fresh_log; trial rotate-secrets
check "rotation refused by the database: secrets unchanged, no container removed" bash -c '[ "$1" -ne 0 ] && cmp -s "$2" "$3" && ! grep -q "^docker rm " "$4" && [ ! -e "$5" ] && grep -qF "nothing was changed" "$6"' _ "$RC" "$STATE_DIR/secrets.env" "$CASE/secrets.before-refusal" "$CASE/log" "$STATE_DIR/secrets.next.env" "$CASE/out"
: > "$CASE/stub/fail/rotation-marker"; fresh_log; trial rotate-secrets
check "rotation failed and the database does not answer: both files kept, the backup named" bash -c '[ "$1" -ne 0 ] && cmp -s "$2" "$3" && [ -f "$4" ] && grep -qF "secrets.next.env (new) are both kept" "$5" && grep -qE "restore [0-9]{8}T[0-9]{6}Z-pre-rotate" "$5"' _ "$RC" "$STATE_DIR/secrets.env" "$CASE/secrets.before-refusal" "$STATE_DIR/secrets.next.env" "$CASE/out"
rm -f "$CASE/stub/fail/rotate" "$CASE/stub/fail/rotation-marker" "$STATE_DIR/secrets.next.env"
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.before-lost"
: > "$CASE/stub/commit-then-fail"; fresh_log; trial rotate-secrets
check "psql lost after the commit: the database's answer decides, the rotation completes" bash -c '[ "$1" -eq 0 ] && ! cmp -s "$2" "$3" && grep -qF "took the new secrets although psql failed" "$4" && grep -qx "docker rm faunosteo_trial_db" "$5" && [ ! -e "$6" ]' _ "$RC" "$STATE_DIR/secrets.env" "$CASE/secrets.before-lost" "$CASE/out" "$CASE/log" "$STATE_DIR/secrets.next.env"
rm -f "$CASE/stub/commit-then-fail" "$CASE/stub/answers/rotation-marker"
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.before-rotation"
fresh_log; trial rotate-secrets
check "rotate-secrets: succeeds" [ "$RC" -eq 0 ]
check "rotate-secrets: every value renewed" bash -c 'for k in TR_PW TR_JWT_SECRET TR_ANON_KEY TR_SERVICE_KEY; do [ "$(grep "^$k=" "$1")" != "$(grep "^$k=" "$2")" ] || exit 1; done' _ "$STATE_DIR/secrets.env" "$CASE/secrets.before-rotation"
check "rotate-secrets: a restorable, protected backup holding the previous secrets (no secrets.prev needed)" bash -c 'd=$(ls -d "$1"/*-pre-rotate* | tail -n 1); [ -n "$d" ] && [ "$(stat -c %a "$d")" = 700 ] && for f in db.tar storage.tar secrets.env stack.conf migrations.sha256 SHA256SUMS; do [ "$(stat -c %a "$d/$f")" = 600 ] || exit 1; done && cd "$d" && sha256sum --quiet -c SHA256SUMS && cmp -s secrets.env "$2"' _ "$STATE_DIR/backups" "$CASE/secrets.before-rotation"
check "rotate-secrets: values reach psql by environment names only" log_has 'docker exec -i -e PGPASSWORD -e NEW_PW -e NEW_JWT -e ROT_DIGEST faunosteo_trial_db psql -X -q -h 127.0.0.1 -U supabase_admin'
check "rotate-secrets: no rotation SQL text holds a secret, old or new" bash -c 'files=$(grep -lF -- "-- db-trial:rotate" $(find "$1" -type f)); [ -n "$files" ] && for f in $files; do grep -qF "set local log_min_error_statement = panic;" "$f" && grep -qF "app.trial_rotation" "$f" || exit 1; for s in "${@:2}"; do while IFS="=" read -r _ v; do [ -z "$v" ] || ! grep -qF -- "$v" "$f" || exit 1; done < "$s"; done; done' _ "$CASE/stub/stdin" "$STATE_DIR/secrets.env" "$CASE/secrets.before-rotation" "$CASE/secrets.before-lost" "$CASE/secrets.before-refusal"
check "rotate-secrets: six containers recreated, volumes kept" bash -c 'for n in db mail auth storage rest gateway; do grep -qx "docker rm faunosteo_trial_$n" "$1" && grep -q "^docker run -d --name faunosteo_trial_$n " "$1" || exit 1; done; ! grep -qF "docker volume rm" "$1"' _ "$CASE/log"
check "rotate-secrets: no leftover secrets.next.env, no secrets.prev.env" bash -c '[ ! -e "$1/secrets.prev.env" ] && [ ! -e "$1/secrets.next.env" ]' _ "$STATE_DIR"
check "rotate-secrets: new secrets absent from calls and output" secrets_absent "$STATE_DIR/secrets.env"
check "rotate-secrets: old secrets absent from calls and output" secrets_absent "$CASE/secrets.before-rotation"
echo "-- rotations refuse keys that are not this stack's"
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.unforged"
forge TR_SERVICE_KEY faunosteo-trial other service_role
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.forged"
for action in rotate-keys rotate-secrets; do
COUNT="$(backup_count)"; fresh_log; trial "$action"
check "$action: keys that are not this stack's are refused before any stop, no backup taken" bash -c '[ "$1" -ne 0 ] && grep -qF "service_role key in secrets.env is not this stack" "$2" && grep -qF "pnpm db:trial restore <backup>" "$2" && cmp -s "$3" "$4" && [ -z "$5" ] && [ "$6" -eq "$7" ] && [ ! -e "$8" ]' _ "$RC" "$CASE/out" "$STATE_DIR/secrets.env" "$CASE/secrets.forged" "$(changes)" "$COUNT" "$(backup_count)" "$STATE_DIR/secrets.next.env"
done
cp -p "$CASE/secrets.unforged" "$STATE_DIR/secrets.env"
echo "-- restore"
NAME="$(basename "$BK")"
fresh_log; trial restore ../etc
check "restore: a name that is not a backup folder is refused" refused "restore <backup>"
cp -rp "$BK" "$STATE_DIR/backups/20000101T000000Z-tampered"; printf 'x' >> "$STATE_DIR/backups/20000101T000000Z-tampered/db.tar"
fresh_log; trial_tty 'restore 20000101T000000Z-tampered' restore 20000101T000000Z-tampered
check "restore: a tampered backup is refused before any change" refused "fails its checksums"
FORGED=20000101T000000Z-forged-key
cp -rp "$BK" "$STATE_DIR/backups/$FORGED"
forge TR_SERVICE_KEY faunosteo-trial other service_role 3600 "$STATE_DIR/backups/$FORGED/secrets.env"
( cd "$STATE_DIR/backups/$FORGED" && sha256sum -- db.tar storage.tar secrets.env stack.conf migrations.sha256 > SHA256SUMS )
fresh_log; trial_tty "restore $FORGED" restore "$FORGED"
check "restore: a backup whose key is not signed with its own secret is refused before any change" refused "holds keys that are not signed with its own secret"
: > "$CASE/stub/fail/image"; fresh_log; trial_tty "restore $NAME" restore "$NAME"
check "restore: the recorded database image is absent: refused before any change" refused "is not present locally"
rm -f "$CASE/stub/fail/image"
fresh_log; trial_tty nope restore "$NAME"
check "restore: a wrong phrase changes nothing" refused "not confirmed"
fresh_log; trial_input restore "$NAME" <<<"restore $NAME"
check "restore: the phrase piped without a terminal is refused" refused "needs a terminal"
fresh_log; trial_tty "restore $NAME" restore "$NAME"
check "restore: succeeds" [ "$RC" -eq 0 ]
check "restore: says the backup brings back older secrets" out_has "holds other secrets than the current ones"
check "restore: a backup of the current state first" before 'faunosteo_trial_db:/volume:ro --entrypoint tar' 'docker volume rm faunosteo_trial_db'
check "restore: volumes replaced then filled by the helper" before 'docker volume create --label faunosteo.stack=trial faunosteo_trial_db' 'docker run --rm -i --name faunosteo_trial_helper --network none -v faunosteo_trial_db:/volume --entrypoint tar'
check "restore: the backup's secrets are back" cmp -s "$STATE_DIR/secrets.env" "$BK/secrets.env"
check "restore: six containers recreated" bash -c 'for n in db mail auth storage rest gateway; do grep -q "^docker run -d --name faunosteo_trial_$n " "$1" || exit 1; done' _ "$CASE/log"
check "restore: only trial names" only_trial_names
echo "-- destroy"
fresh_log; trial_tty '' destroy
check "destroy with an empty answer: nothing changed" refused "not confirmed"
fresh_log; trial_input destroy <<<"faunosteo_trial"
check "destroy with the phrase piped without a terminal: refused, nothing changed" refused "needs a terminal"
fresh_log; trial_tty faunosteo_trial destroy
check "destroy: succeeds" [ "$RC" -eq 0 ]
check "destroy: a backup before any removal" before 'faunosteo_trial_db:/volume:ro --entrypoint tar' 'docker rm faunosteo_trial_db'
check "destroy: every removal names a trial resource" bash -c 'grep -E "^docker (rm|volume rm|network rm) " "$1" | grep -qvE " faunosteo_trial_(db|mail|auth|storage|rest|gateway|net)$" && exit 1; grep -qx "docker volume rm faunosteo_trial_db" "$1" && grep -qx "docker volume rm faunosteo_trial_storage" "$1" && grep -qx "docker network rm faunosteo_trial_net" "$1"' _ "$CASE/log"
check "destroy: live state removed, marker and backups kept" bash -c 'for f in secrets.env secrets.next.env db.env auth.env storage.env rest.env stack.conf migrations.sha256; do [ ! -e "$1/$f" ] || exit 1; done; [ -e "$1/.faunosteo-trial-state" ] && ls -d "$1"/backups/*-pre-destroy* >/dev/null 2>&1 && [ -d "$2" ]' _ "$STATE_DIR" "$BK"
fresh_log; trial_tty "restore $NAME" restore "$NAME"
check "restore after destroy rebuilds the stack from the backup" bash -c '[ "$1" -eq 0 ] && grep -qF "no trial volume" "$2" && grep -qx "docker network create --label faunosteo.stack=trial faunosteo_trial_net" "$3"' _ "$RC" "$CASE/out" "$CASE/log"
echo "-- destroy with nothing left in Docker"
new_case leftover; trial create
rm -f "$CASE/stub/containers/"* "$CASE/stub/ports/"* "$CASE/stub/volumes/"* "$CASE/stub/networks/"*
fresh_log; trial_tty faunosteo_trial destroy
check "destroy with no resource: after the phrase, the live files go, backups and marker kept, no Docker removal" bash -c '[ "$1" -eq 0 ] && [ ! -e "$2/secrets.env" ] && [ ! -e "$2/db.env" ] && [ -d "$2/backups" ] && [ -e "$2/.faunosteo-trial-state" ] && ! grep -qE "^docker (rm|volume rm|network rm) " "$3"' _ "$RC" "$STATE_DIR" "$CASE/log"
```
#### Lot B2 review fixes (2026-11-10)
- Backup proof: `verify_backup_integrity` (no symlinked `backups/` or folder, no non-regular entry, SHA256SUMS lists exactly the folder's files, checksums, every tar lists) and `check_backup` (integrity, the four required files, parse, keys signed by its own secret, all six recorded images present). `refuse_backup` takes the caller's outcome sentence, so a check after a backup never says "nothing was changed".
- Uses: restore (`check_backup` before the confirm, `verify_backup_integrity` again right before the extraction loop); migrate, rotate-keys, rotate-secrets and destroy (`check_backup` right after their backup, before any change or removal; destroy stays stopped and names the kept backup); restore's pre-restore backup (`check_pre_restore_backup`: integrity mandatory, a note, no stop, when `check_backup` would refuse it).
- Restore: `restore_stopped` EXIT trap (set after the pre-restore backup, cleared at the end) names the pre-restore backup and the rerun, or says none was taken; state files come back through `write_private` (600).
- Restore refuses a symlinked `backups/` or backup folder; `new_backup_dir` refuses a symlinked `backups/`.
- rotate-secrets: `ROTATION_COMMITTED=yes` only after `mv -f` of secrets.next.env; the "not committed" message now says secrets.env did not take the new secrets (true in the commit-to-rename window). A leftover secrets.next.env is refused before the key check and any backup; `status_leftover_rotation` prints one line, never the content.
- Rotation SQL: explicit `begin;` ... `commit;` (no `--single-transaction`, which would commit a truncated script), session named `PGAPPNAME=db-trial-rotate`; `settle_rotation` first waits (`wait_rotation_session_gone`, `ROTATION_WAIT_POLLS` = 10, one poll a second) for that session to end, else `rotation_unknown`.
- Lock first: backup, migrate, rotate-keys, rotate-secrets call `require_state; take_lock` before `require_created` / `require_running`.
- Destroy: the content inventory cannot stop it (`(content unreadable)`). backup_now: refuses a leftover helper container before any stop; `backup_interrupted` EXIT trap (cleared on success and by `backup_failed`) names the incomplete folder; its guard on `$?` is dropped because a SIGTERM leaves 0 in it.
- Harness H5 "B2 review fixes": one case per fix plus the spec gaps; stub hooks `image-nondb`, `extract`, `term-in-archive`, `append-on-rm`, `answers/measure-kib`, `answers/<tag>.seq`; `bin-mvfail` PATH for the commit-to-rename window. Two earlier assertions changed: the psql argv now carries `-e PGAPPNAME`; the stop-then-helper-then-start order matches the helper `docker run` line (the leftover-helper inspect now logs the name earlier).
- Destroy dead end: when its pre-destroy backup cannot be made (`backup_failed` appends `BACKUP_WAY_OUT`, set by destroy only) or fails `check_backup`, destroy still refuses and removes nothing, and `destroy_way_out` ends the message with the backup kept (or none), the exact `docker rm` / `docker network rm` / `docker volume rm` lines for what exists, then `pnpm db:trial destroy`; the script never runs them. `create_stopped`'s hint says so.
- `rotation_stopped` picks its branch from the state (secrets.next.env gone means the rename happened), and its trap is armed only after secrets.next.env exists and parses; `settle_rotation` clears the trap before removing the file. `keys_stopped` is armed after the `mv -f` of secrets.env.
- Restore: `restore_stopped` armed after `ports_free` (a refusal there prints no half-filled message); a leftover helper container is refused before the confirmation.
- `destroy_stopped` EXIT trap, armed once the pre-destroy backup passed `check_backup`, cleared on success: names that backup and what may remain.
- A leftover secrets.next.env is in `BACKUP_FILES`, copied by `backup_now`, brought back by restore (`write_private`, 600; status flags it), noted by `check_pre_restore_backup`; the exact-listing rule of `verify_backup_integrity` already accepts it (it compares the folder to its own SHA256SUMS).
- `backup_interrupted` says "the stack may be stopped". `check_backup` keeps `2>/dev/null` on the two parses: `parse_secrets` and `parse_conf` print nothing, so there is no reason to show.
- Harness: one case per fix (stub PATH `bin-mvterm` for a SIGTERM on a rename). Changed assertions: the trial-port restore refusal asserts no "half-filled" message instead of the pre-restore backup name; the backup-interruption case asserts the new wording.
---
### Lot B3: account, grant, assets, app
**Files:**
- Modify: `scripts/db-trial.sh` (section 8 before the dispatch; dispatch lines)
- Modify: `scripts/db-trial-check.sh` (cases H6 before H4)
**Interfaces:** consumes B1 and B2 (`confirm`).
- [ ] **Step 1:** H6 cases, harness fails on them only. **Step 2:** section 8 + dispatch. **Step 3:** `ALL PASS`;
positive controls (then revert): in `upload_assets`, pass the service key as
`env SUPABASE_URL=… SUPABASE_SERVICE_ROLE_KEY="$TR_SERVICE_KEY" bash scripts/upload-sheet-assets.sh …` → the
`env` wrapper logs that argv and "assets: no secret in calls or output" FAILS (report the failing line); lower the
owner factor requirement to 1 → FAIL; remove `gateway_identity` from `cmd_app` → the identity and
published-elsewhere app cases FAIL; replace `client_key` by `key_identity` in `cmd_app` → "app: an expired anon key"
FAILS; remove `client_key` from `cmd_assets` → the forged and expired service key cases FAIL; remove
`published_exactly "$AUTH"` from `cmd_account` → "account: GoTrue published elsewhere" FAILS; send the header from
a file again → "the header sent carries exactly the checked service key" FAILS; remove `require_trial_keys` from
`write_env_files` → "start: a forged service key and a missing container" FAILS; put the expiry back into
`key_identity` → "start: expired keys never block" FAILS; drop the `env -u …` proxy unsets from `cmd_app` → "app:
… proxies=" FAILS. **Step 4: orchestrator commits**
`feat(supabase): db:trial accounts, grants, equine drawings, app` + push.
#### Section 8
```bash
trial_email() { # one fixed shape, nothing else reaches curl or psql
[[ "${1:-}" =~ ^[A-Za-z0-9._%+-]{1,64}@[A-Za-z0-9.-]{1,120}\.[A-Za-z]{2,24}$ ]] || die "an e-mail address is expected"
}
# The password is typed twice at a hidden prompt, kept in this process only and sent to GoTrue's
# admin API on its loopback port through stdin (JSON built by node); nothing secret is printed.
# The service key header is built from the very key client_key checked (builtin printf into a
# process substitution: never argv, never a file that could differ from it).
cmd_account() {
[ $# -eq 1 ] || die "account <email>"
trial_email "$1"
require_running
published_exactly "$AUTH"
client_key "$TR_SERVICE_KEY" service_role
local pw again
read -rsp "Password (12 characters min.): " pw || die "no password read: no account created"
echo >&2
read -rsp "Again: " again || die "no password read: no account created"
echo >&2
[ "$pw" = "$again" ] || die "the two passwords differ: no account created"
[ "${#pw}" -ge 12 ] || die "12 characters min.: no account created"
TRIAL_EMAIL="$1" TRIAL_PW="$pw" node -e '
process.stdout.write(JSON.stringify({ email: process.env.TRIAL_EMAIL,
password: process.env.TRIAL_PW, email_confirm: true }));' \
| curl -q --noproxy '*' -fsS -o /dev/null -X POST "http://127.0.0.1:$PORT_AUTH/admin/users" \
-H @<(printf 'Authorization: Bearer %s\n' "$TR_SERVICE_KEY") -H 'Content-Type: application/json' --data-binary @- \
|| die "account not created (already existing, or password refused)"
say "account $1 created and confirmed"
echo "Next: pnpm db:trial app, sign in, enrol TOTP in Mon compte > Sécurité (two factors for the owner account), then pnpm db:trial grant"
}
# Platform roles follow V1-2_ANATOMICAL_SHEETS.md §5.7.1: the account enrols its factors first, the
# operator reads them (name, creation time) and confirms, then the role is assigned last through
# the operator path (null actor, JWT-less session) and an audit row records the reason and the
# operator. Owner needs two verified TOTP factors, admin one.
grant_platform_role() { # email role
local need=1 factors count
[ "$2" != owner ] || need=2
factors="$(sb_psql -t -A -F ' | ' -v email="$1" <<'SQL'
-- db-trial:grant-factors
select coalesce(nullif(f.friendly_name, ''), '(no name)'),
to_char(f.created_at at time zone 'UTC', 'YYYY-MM-DD HH24:MI "UTC"')
from auth.mfa_factors f join auth.users u on u.id = f.user_id
where u.email = :'email' and f.factor_type = 'totp' and f.status = 'verified'
order by f.created_at;
SQL
)"
count="$(grep -c . <<<"$factors" || true)"
[ "$count" -ge "$need" ] \
|| die "$1 holds $count verified TOTP factor(s), $2 needs $need: the account enrols them in Mon compte > Sécurité first (nothing was granted)"
echo "Verified TOTP factors of $1 (check they are the ones its holder enrolled):"
printf '%s\n' "$factors" | sed 's/^/ /'
confirm "grant $2"
sb_psql --single-transaction -v email="$1" -v role="$2" -v operator="$(id -un)" >/dev/null <<'SQL' \
|| die "$2 not granted to $1 (already active, or account inactive)"
-- db-trial:grant-role
select id::text as uid from auth.users where email = :'email' \gset
select private.assign_platform_role(null, :'uid'::uuid, :'role'::public.platform_role)::text as role_id \gset
select private.log_event(null, 'platform_role.operator_bootstrap', 'profile_role', :'role_id'::uuid,
:'uid'::uuid, null, jsonb_build_object('role', :'role', 'operator', :'operator',
'reason', 'db:trial operator bootstrap (V1-2 contract 5.7.1)'));
SQL
say "$2 granted to $1: the account signs in again and passes the step-up (aal2)"
}
# Catalogue rights through the same operator SQL as db-sandbox.sh trial-grant, without its
# implicit admin role: rule O1 is reported, never worked around.
grant_catalog_right() { # email kind
local outcome
outcome="$(sb_psql -t -A --single-transaction -v email="$1" -v kind="$2" <<'SQL'
-- db-trial:grant-catalog
select id::text as uid from auth.users where email = :'email' \gset
select case when exists (select 1 from public.anatomical_catalog_grants
where user_id = :'uid'::uuid and grant_kind = :'kind'::public.catalog_grant_kind and revoked_at is null)
then 'already' else 'granted' end as outcome \gset
insert into public.anatomical_catalog_grants (user_id, grant_kind, reason, verification_note, granted_by)
select :'uid'::uuid, :'kind'::public.catalog_grant_kind, 'db:trial operator bootstrap',
case when :'kind' = 'catalog_reader' then null else 'db:trial operator bootstrap' end, :'uid'::uuid
where :'outcome' = 'granted';
select :'outcome' || ' ' || private.is_platform_admin(:'uid'::uuid)::text;
SQL
)" || die "$2 not granted to $1 (no such account?)"
case "$outcome" in
'granted '*) say "$2 granted to $1" ;;
'already '*) say "$2 already active for $1: nothing changed" ;;
*) die "unexpected answer from the database: nothing is known about $2 for $1" ;;
esac
if [[ "$2" =~ ^(sheet_editor|sheet_publisher)$ ]] && [ "${outcome##* }" != true ]; then
echo "note: rule O1 keeps $2 inactive until $1 holds owner or admin (pnpm db:trial grant $1 admin)"
fi
}
cmd_grant() {
[ $# -eq 2 ] || die "grant <email> <owner|admin|catalog_reader|sheet_editor|sheet_reviewer|sheet_publisher>"
trial_email "$1"
[[ "$2" =~ ^(owner|admin|catalog_reader|sheet_editor|sheet_reviewer|sheet_publisher)$ ]] \
|| die "right: owner | admin | catalog_reader | sheet_editor | sheet_reviewer | sheet_publisher"
require_running
take_lock
case "$2" in
owner | admin) grant_platform_role "$1" "$2" ;;
*) grant_catalog_right "$1" "$2" ;;
esac
}
# The existing R5.5 tool (no overwrite, verify after upload, rerun = nothing to do) through the
# trial gateway; the service key in its environment only; the LAN address and pnpm's INIT_CWD
# dropped so the tool accepts loopback only and resolves the seed folder from the repository.
upload_assets() { # [--check]
( unset FAUNOSTEO_DEV_HOST INIT_CWD
SUPABASE_URL="http://127.0.0.1:$PORT_GATEWAY" SUPABASE_SERVICE_ROLE_KEY="$TR_SERVICE_KEY" \
bash scripts/upload-sheet-assets.sh "$@" "$SEED" )
}
cmd_assets() {
[ $# -eq 0 ] || die "assets takes no argument"
local out expected
require_running
take_lock
gateway_identity
client_key "$TR_SERVICE_KEY" service_role
upload_assets
out="$(upload_assets --check)"
printf '%s\n' "$out"
expected="$(expected_assets | grep -c .)"
[ "$(grep -c 'nothing to do' <<<"$out")" -eq "$expected" ] || die "the tool does not report every drawing verified"
[ "$(uploaded_count)" -eq "$expected" ] || die "not every drawing row is confirmed with its object in the bucket"
say "equine drawings: $expected/$expected uploaded, verified and confirmed"
}
# No proxy variable reaches Vite (Node 24 honours NODE_USE_ENV_PROXY): its identity fetch and its
# proxy to the gateway go straight to 127.0.0.1.
# The app origin is the Vite server itself (/auth/v1, /rest/v1, /storage/v1 proxied to the trial
# gateway by apps/app/vite/trial.config.ts): same origin, no CORS. The anon key reaches Vite
# through its environment, never argv.
app_stop() { [ -z "${app_pid:-}" ] || kill "$app_pid" 2>/dev/null || true; }
cmd_app() {
[ $# -eq 0 ] || die "app takes no argument"
local host
require_running
gateway_identity
client_key "$TR_ANON_KEY" anon
host="$(trial_host)"
if port_listening "$PORT_APP"; then die "port $PORT_APP is in use: nothing was started"; fi
trap app_stop EXIT
trap 'exit 130' INT TERM
say "trial app: http://$host:$PORT_APP (Ctrl+C stops the app; the stack runs until pnpm db:trial stop)"
( cd apps/app && FAUNOSTEO_TRIAL_GATEWAY="http://127.0.0.1:$PORT_GATEWAY" FAUNOSTEO_TRIAL_HOST="$host" \
VITE_SUPABASE_URL="http://$host:$PORT_APP" VITE_SUPABASE_ANON_KEY="$TR_ANON_KEY" \
NODE_OPTIONS=--max-old-space-size=768 \
exec env -u http_proxy -u HTTP_PROXY -u https_proxy -u HTTPS_PROXY -u all_proxy -u ALL_PROXY \
-u no_proxy -u NO_PROXY -u NODE_USE_ENV_PROXY \
node_modules/.bin/vite --config vite/trial.config.ts --host "$host" --port "$PORT_APP" --strictPort ) &
app_pid=$!
wait "$app_pid"
}
```
Argument lines (inside `check_arguments`' case, before `*)`):
```bash
app | assets) [ $# -eq 0 ] || die "$action takes no argument" ;;
account) [ $# -eq 1 ] || die "account <email>"; trial_email "$1" ;;
grant)
[ $# -eq 2 ] || die "grant <email> <owner|admin|catalog_reader|sheet_editor|sheet_reviewer|sheet_publisher>"
trial_email "$1"
[[ "$2" =~ ^(owner|admin|catalog_reader|sheet_editor|sheet_reviewer|sheet_publisher)$ ]] \
|| die "right: owner | admin | catalog_reader | sheet_editor | sheet_reviewer | sheet_publisher" ;;
```
Dispatch lines:
```bash
app) cmd_app "$@" ;;
account) cmd_account "$@" ;;
grant) cmd_grant "$@" ;;
assets) cmd_assets "$@" ;;
```
#### Harness H6 (insert before H4)
```bash
echo "-- accounts, grants, drawings, app"
new_case people; trial create
check "people: create" [ "$RC" -eq 0 ]
fresh_log; trial account 'not-an-email'
check "account: a malformed address is refused before any call" refused_early "e-mail address"
fresh_log; trial_input account owner@trial.test <<<$'short\nshort'
check "account: a short password is refused, nothing sent" bash -c '[ "$1" -ne 0 ] && ! grep -q "^curl .*admin/users" "$2" && grep -qF "12 characters" "$3"' _ "$RC" "$CASE/log" "$CASE/out"
fresh_log; trial_input account owner@trial.test <<<$'correct-horse-battery\nother-horse-battery'
check "account: two different passwords are refused" bash -c '[ "$1" -ne 0 ] && grep -qF "differ" "$2"' _ "$RC" "$CASE/out"
fresh_log; trial_input account owner@trial.test <<<$'correct-horse-battery\ncorrect-horse-battery'
check "account: GoTrue admin API on loopback, header through a pipe fd, body on stdin" bash -c 'grep -qE "^curl -q --noproxy \* -fsS -o /dev/null -X POST http://127.0.0.1:58999/admin/users -H @/dev/fd/[0-9]+ -H Content-Type: application/json --data-binary @-$" "$1"' _ "$CASE/log"
check "account: the header sent carries exactly the checked service key, no header file exists" bash -c '[ "$(cat "$1")" = "$(printf "Authorization: Bearer %s\n" "$(sed -n "s/^TR_SERVICE_KEY=//p" "$2")" | sha256sum | cut -c1-16)" ] && [ ! -e "$3/service-header" ]' _ "$CASE/stub/curl-header-sha" "$STATE_DIR/secrets.env" "$STATE_DIR"
check "account: the password never appears in calls or output" bash -c '[ "$1" -eq 0 ] && ! grep -qF correct-horse-battery "$2" "$3"' _ "$RC" "$CASE/log" "$CASE/out"
check "account: the body is a confirmed account" stdin_has '"email_confirm":true'
fresh_log; trial grant owner@trial.test root
check "grant: an unknown right is refused before any call" refused_early "right:"
printf 'Phone | 2026-11-09 10:00 UTC\n' > "$CASE/stub/answers/grant-factors"
fresh_log; trial_tty 'grant owner' grant owner@trial.test owner
check "grant owner with one factor: refused (two needed), nothing assigned" bash -c '[ "$1" -ne 0 ] && grep -qF "owner needs 2" "$2" && ! grep -rlF assign_platform_role "$3" | grep -q .' _ "$RC" "$CASE/out" "$CASE/stub/stdin"
printf 'Phone | 2026-11-09 10:00 UTC\nTablet | 2026-11-09 10:05 UTC\n' > "$CASE/stub/answers/grant-factors"
fresh_log; trial_tty no grant owner@trial.test owner
check "grant owner, wrong phrase: nothing assigned" bash -c '[ "$1" -ne 0 ] && grep -qF "not confirmed" "$2" && ! grep -rlF assign_platform_role "$3" | grep -q .' _ "$RC" "$CASE/out" "$CASE/stub/stdin"
fresh_log; trial_input grant owner@trial.test owner <<<'grant owner'
check "grant owner, phrase piped without a terminal: nothing assigned" bash -c '[ "$1" -ne 0 ] && grep -qF "needs a terminal" "$2" && ! grep -rlF assign_platform_role "$3" | grep -q .' _ "$RC" "$CASE/out" "$CASE/stub/stdin"
fresh_log; trial_tty 'grant owner' grant owner@trial.test owner
check "grant owner: factors shown, operator path, audit row" bash -c '[ "$1" -eq 0 ] && grep -qF "Tablet | 2026-11-09 10:05 UTC" "$2" && grep -rlF "private.assign_platform_role(null, :'"'"'uid'"'"'::uuid" "$3" | grep -q . && grep -rlF "platform_role.operator_bootstrap" "$3" | grep -q .' _ "$RC" "$CASE/out" "$CASE/stub/stdin"
check "grant owner: email, role and operator as psql variables" log_has "-v email=owner@trial.test -v role=owner -v operator="
: > "$CASE/stub/answers/grant-factors"
fresh_log; trial_tty 'grant admin' grant practitioner@trial.test admin
check "grant admin with no factor: refused" bash -c '[ "$1" -ne 0 ] && grep -qF "admin needs 1" "$2"' _ "$RC" "$CASE/out"
fresh_log; trial grant practitioner@trial.test sheet_editor
check "grant sheet_editor: inserted, O1 reported" bash -c '[ "$1" -eq 0 ] && grep -qF "sheet_editor granted" "$2" && grep -qF "rule O1 keeps sheet_editor inactive" "$2"' _ "$RC" "$CASE/out"
echo 'already true' > "$CASE/stub/answers/grant-catalog"
fresh_log; trial grant practitioner@trial.test sheet_editor
check "grant sheet_editor again: nothing changed, no O1 note" bash -c '[ "$1" -eq 0 ] && grep -qF "already active" "$2" && ! grep -qF "rule O1" "$2"' _ "$RC" "$CASE/out"
SERVICE_SHA="$(sed -n 's/^TR_SERVICE_KEY=//p' "$STATE_DIR/secrets.env" | tr -d '\n' | sha256sum | cut -c1-16)"
ANON_SHA="$(sed -n 's/^TR_ANON_KEY=//p' "$STATE_DIR/secrets.env" | tr -d '\n' | sha256sum | cut -c1-16)"
fresh_log; EXTRA=(INIT_CWD=/elsewhere FAUNOSTEO_DEV_HOST=127.0.0.1); trial assets; EXTRA=()
check "assets: upload then check, through the trial gateway" bash -c '[ "$1" -eq 0 ] && grep -qx "upload supabase/seed/anatomical-sheets/equine/draft" "$2" && grep -qx "upload --check supabase/seed/anatomical-sheets/equine/draft" "$2" && [ "$(grep -c "^upload-env url=http://127.0.0.1:58801 " "$2")" -eq 2 ]' _ "$RC" "$CASE/log"
check "assets: the service key by environment, LAN host and INIT_CWD dropped" bash -c '[ "$(grep -c "^upload-env .*key-sha=$1 dev-host= init-cwd=$" "$2")" -eq 2 ]' _ "$SERVICE_SHA" "$CASE/log"
check "assets: every drawing confirmed" out_has "1/1 uploaded"
check "assets: no secret in calls or output" secrets_absent "$STATE_DIR/secrets.env"
echo 0 > "$CASE/stub/answers/assets-uploaded"; fresh_log; trial assets
check "assets: a drawing not confirmed in the database fails" bash -c '[ "$1" -ne 0 ] && grep -qF "not every drawing row" "$2"' _ "$RC" "$CASE/out"
EXTRA=(http_proxy=http://127.0.0.1:9 HTTPS_PROXY=http://127.0.0.1:9 ALL_PROXY=socks5://127.0.0.1:9 NODE_USE_ENV_PROXY=1)
fresh_log; trial app; EXTRA=()
check "app: Vite on the trial host and 5194, strict port" log_has 'vite --config vite/trial.config.ts --host 127.0.0.1 --port 5194 --strictPort'
check "app: anon key by environment, no PGPASSWORD, heap capped, trial gateway" bash -c 'grep -qx "vite-env anon-sha=$1 pgpassword= node-options=--max-old-space-size=768 gateway=http://127.0.0.1:58801 host=127.0.0.1 url=http://127.0.0.1:5194 proxies=" "$2"' _ "$ANON_SHA" "$CASE/log"
check "app: every curl pinned (-q, --noproxy), Vite started with no proxy variable" curls_pinned
check "app: no secret in calls or output" secrets_absent "$STATE_DIR/secrets.env"
if hold_port 5194; then fresh_log; trial app; check "app: port 5194 in use, nothing started" refused "5194 is in use"; release_port
else fail "could not hold 127.0.0.1:5194 for the app case"; fi
echo 1 > "$CASE/stub/answers/assets-uploaded"
echo "-- identity of the target (R13)"
echo other_service > "$CASE/stub/answers/identity"
for action in app assets; do
fresh_log; trial "$action"
check "$action: a gateway port answering another identity is refused" refused "does not answer as the faunosteo_trial gateway"
done
rm -f "$CASE/stub/answers/identity"; : > "$CASE/stub/fail/identity"
fresh_log; trial app
check "app: no identity answer at all is refused" refused "does not answer as the faunosteo_trial gateway"
rm -f "$CASE/stub/fail/identity"
cp -p "$CASE/stub/ports/faunosteo_trial_gateway" "$CASE/gateway.port"
echo '8000/tcp -> 127.0.0.1:58802' > "$CASE/stub/ports/faunosteo_trial_gateway"
for action in app assets; do
fresh_log; trial "$action"
check "$action: the gateway container published elsewhere is refused" refused "faunosteo_trial_gateway is not published on 127.0.0.1:58801 only"
done
cp -p "$CASE/gateway.port" "$CASE/stub/ports/faunosteo_trial_gateway"
cp -p "$CASE/stub/ports/faunosteo_trial_auth" "$CASE/auth.port"
echo '9999/tcp -> 0.0.0.0:58999' > "$CASE/stub/ports/faunosteo_trial_auth"
fresh_log; trial_input account second@trial.test <<<$'correct-horse-battery\ncorrect-horse-battery'
check "account: GoTrue published elsewhere is refused before the prompt" refused "faunosteo_trial_auth is not published on 127.0.0.1:58999 only"
cp -p "$CASE/auth.port" "$CASE/stub/ports/faunosteo_trial_auth"
# Keys that look right but are not this stack's: the sandbox issuer signed with the trial secret,
# and the trial claims signed with another secret.
cp -p "$STATE_DIR/secrets.env" "$CASE/secrets.good"
forge TR_ANON_KEY db-sandbox own anon; fresh_log; trial app
check "app: an anon key of the sandbox issuer is refused" refused "anon key in secrets.env is not this stack's"
cp -p "$CASE/secrets.good" "$STATE_DIR/secrets.env"
forge TR_ANON_KEY faunosteo-trial other anon; fresh_log; trial app
check "app: an anon key signed with another secret is refused" refused "anon key in secrets.env is not this stack's"
cp -p "$CASE/secrets.good" "$STATE_DIR/secrets.env"
forge TR_SERVICE_KEY faunosteo-trial other service_role
fresh_log; trial assets
check "assets: a service key signed with another secret is refused" refused "service_role key in secrets.env is not this stack's"
fresh_log; trial_input account third@trial.test <<<$'correct-horse-battery\ncorrect-horse-battery'
check "account: the same forged service key is refused" refused "service_role key in secrets.env is not this stack's"
DB_ENV_BEFORE="$(env_file_state "$STATE_DIR/db.env")"; STORAGE_ENV_BEFORE="$(env_file_state "$STATE_DIR/storage.env")"
rm -f "$CASE/stub/containers/faunosteo_trial_rest" "$CASE/stub/ports/faunosteo_trial_rest"
sleep 1; fresh_log; trial start
check "start: a forged service key and a missing container: refused, no env file rewritten (content, mtime)" bash -c '[ "$1" -ne 0 ] && grep -qF "service_role key in secrets.env is not this stack" "$2" && [ -z "$3" ] && [ "$4" = "$5" ] && [ "$6" = "$7" ]' _ "$RC" "$CASE/out" "$(changes)" "$DB_ENV_BEFORE" "$(env_file_state "$STATE_DIR/db.env")" "$STORAGE_ENV_BEFORE" "$(env_file_state "$STATE_DIR/storage.env")"
cp -p "$CASE/secrets.good" "$STATE_DIR/secrets.env"
fresh_log; trial start
check "start: with this stack's keys the missing container is recreated" bash -c '[ "$1" -eq 0 ] && grep -q "^docker run -d --name faunosteo_trial_rest " "$2"' _ "$RC" "$CASE/log"
echo "-- expired keys (F1)"
forge TR_ANON_KEY faunosteo-trial own anon -60; forge TR_SERVICE_KEY faunosteo-trial own service_role -60
fresh_log; trial app
check "app: an expired anon key is refused, naming rotate-keys" refused "expired: pnpm db:trial rotate-keys"
fresh_log; trial assets
check "assets: an expired service key is refused, naming rotate-keys" refused "expired: pnpm db:trial rotate-keys"
fresh_log; trial status
check "status reports expired keys" out_has "EXPIRED"
rm -f "$CASE/stub/containers/faunosteo_trial_rest" "$CASE/stub/ports/faunosteo_trial_rest"
fresh_log; trial start
check "start: expired keys never block the recreation of a missing container" bash -c '[ "$1" -eq 0 ] && grep -q "^docker run -d --name faunosteo_trial_rest " "$2"' _ "$RC" "$CASE/log"
fresh_log; trial stop; fresh_log; trial rotate-keys
check "rotate-keys on a stopped stack with expired keys: renewed, Storage removed for start to recreate" bash -c '[ "$1" -eq 0 ] && grep -qx "docker rm faunosteo_trial_storage" "$2" && ! grep -q "^docker run -d --name faunosteo_trial_storage " "$2" && grep -qF "pnpm db:trial start recreates Storage" "$3"' _ "$RC" "$CASE/log" "$CASE/out"
fresh_log; trial start
check "start after it recreates Storage with the new keys; app accepts them" bash -c '[ "$1" -eq 0 ] && grep -q "^docker run -d --name faunosteo_trial_storage " "$2"' _ "$RC" "$CASE/log"
fresh_log; trial app
check "app: the renewed anon key passes" [ "$RC" -eq 0 ]
fresh_log; trial stop; fresh_log; trial app
check "app: refused while the stack is stopped" refused "is not running"
```
#### Lot B3 review fixes (2026-11-10)
Already made by the B3 executor, kept: the lock is taken first (`require_state; take_lock` before `require_running`); `account` refuses a piped stdin; `app_stop` prints its message once; `grant` and `assets` die with a message when the factor read or the upload tool fails.
1. Factor gate: `factor_count` (SQL `count(*)` of verified TOTP factors, never output lines) and `factor_list`, whose names are sanitised in SQL (`left(regexp_replace(..., '[^[:alnum:] ._-]', '?', 'g'), 40)`): no newline, CR or escape reaches the terminal.
2. `grant_platform_role`: the grant transaction counts the factors again (`select assign_platform_role(...) where (count) >= :need \gset`, `need` 2 for owner, 1 for admin): fewer rows nothing, `\gset` fails under ON_ERROR_STOP, the unfinished transaction rolls back, the operator reads "nothing was granted".
3. Catalogue rights mirror `public.grant_catalog_right` in operator SQL (the RPC needs an owner JWT at aal2): `catalog_grant_status` answers no_account, inactive, no_totp, not_admin, already or ok (confirmed active account; verified TOTP except catalog_reader; admin for sheet_editor and sheet_publisher; no active row), each refused with its own message and nothing written. The practitioner procedure is admin first (`pnpm db:trial grant <email> admin`), then sheet_editor; the "granted but inactive under O1" note is gone. The write (insert, then `private.log_event(null, 'catalog_grant.operator_bootstrap', ...)`) is one `begin; ... commit;` that re-checks the rules; `granted_by` is the account's own id (NOT NULL column, the operator has no profile). An already active right stays exit 0, nothing written.
4. Every grant, catalogue included, asks the exact typed phrase `grant <right>` after the checks and the factor list, before any write.
5. Grant SQL (role and catalogue): explicit `begin;` ... `commit;` instead of `--single-transaction` (which commits a truncated script), ON_ERROR_STOP kept.
6. `read_new_password`: `IFS= read -rsp`, blanks kept and counted in the 12-character minimum.
7. `cmd_account` and `cmd_grant` lowercase the address (`${1,,}`, GoTrue stores it lowercased) before any use.
8. `read_new_password`: locals renamed `_rnp_typed` and `_rnp_again`, so a caller's variable cannot collide with the nameref.
9. `app_stop` finds Vite through `jobs -p` (no window between `&` and the pid assignment leaves an orphan) and `wait`s for it, so port 5194 is free on return; `cmd_app` ends with `wait $!`.
10. `require_terminal` in `check_arguments` for account, grant, restore, destroy: a non-terminal stdin is refused before any Docker call ("needs a terminal ... nothing was changed"). Moved assertions: B2 "restore/destroy with the phrase piped" now `refused_early` (no docker call); "restore refuses an incomplete backup" and the B3 lock case for `grant` now run at a terminal (`trial_tty`).
11. Known limit, no code: `app` takes no lock, so a `rotate-keys` can pass `refuse_while_app_runs` just before Vite binds; the app then keeps the previous anon key until its expiry. Holding the lock for the whole app session would block backup and migrate with a misleading message. Noted as a `shortcut:` comment in `cmd_app`.
12. Harness: one case per fix (stub hooks `answers/grant-factor-count`, `answers/txn-factors`, `vite-hold` with `vite-hold-sig`, `int-trial.sh`); the changed assertions are those of fix 10, the O1 cases (now the not_admin refusal), the grant cases that now run at a terminal, and the `-v need=` argument of the role grant.
13. Re-review (2026-11-10): the `shortcut:` above `cmd_app` now states the whole unlocked window and its upgrade path; harness comment on SIGINT corrected; the two-line factor name case now runs `factor_list` (indented lines, nothing written before the confirmation) with the SQL sanitisation kept as a named text check; the failing grant session case is named for the script's handling; the `granted_by` case asserts the bash comment; new case `fail/grant-catalog-write` (catalogue write failing after a passing check).
---
### Lot C: documentation, comments, TODO
**Files:**
- Modify: `docs/development/SUPABASE.md` (new §5.2 before `## 6. Generated types`)
- Modify: `docs/development/V1-2-6_OWNER_TRIAL.md` (short French section after the intro, before `## 1.`)
- Modify: `docs/development/TRIAL_STACK.md` (status lines 3-9; §3.3 rotation sentence; new §7)
- Modify: `CLAUDE.md` (command block after the `pnpm db:sandbox` line; scripts list in the folder tree)
- Modify: `.claude/tasks/TODO.md` (the trial stack item, line ~697)
- [ ] **Step 1: SUPABASE.md §5.2**, inserted before `## 6. Generated types`:
```markdown
### 5.2 Dedicated trial stack (`pnpm db:trial`)
A persistent stack for the owner's manual trials, apart from DEV and from the sandbox, which the
automated suites keep using (`docs/development/TRIAL_STACK.md`, arbitrations A11 to A15). The script
`scripts/db-trial.sh` and its checks exist; the stack is created only on the owner's go, one operation
at a time, and never by a test.
| Container | Image | Port (127.0.0.1 only) | Data | Caps |
|---|---|---|---|---|
| `faunosteo_trial_db` | `supabase/postgres:17.11.0.004` | 58432 | volume `faunosteo_trial_db` (`PGDATA`) | 1 GiB, `shared_buffers` 128 MB, 512 pids |
| `faunosteo_trial_auth` | `supabase/gotrue:v2.197.0` | 58999 | | 128 MiB, 256 pids |
| `faunosteo_trial_rest` | `supabase/postgrest:v16.4` | 58300 | | 384 MiB, 256 pids |
| `faunosteo_trial_storage` | `supabase/storage-api:v1.79.36` | 58500 | volume `faunosteo_trial_storage` (`/mnt`) | 256 MiB, 256 pids |
| `faunosteo_trial_mail` | `supabase/mailpit:v1.31.3` | 58025 | | 64 MiB, 64 pids |
| `faunosteo_trial_gateway` | `nginx:1.31-alpine` | 58801 | | 64 MiB, 64 pids |
Every container: network `faunosteo_trial_net`, `--cpus 1`, restart policy `no`. The app (`pnpm db:trial app`)
runs Vite on `http://<trial host>:5194` with a 768 MiB Node heap; the trial host is the LAN address by default,
`FAUNOSTEO_TRIAL_HOST=127.0.0.1` for host-only, decided again at each run (nothing to edit after a change of the LAN
address). GoTrue's mail links carry the host of the moment GoTrue was started (`create`, `restore`,
`rotate-secrets`). GoTrue's sign-up is closed and the gateway answers 403 on `/auth/v1/admin/`.
State: `~/.cache/faunosteo-trial` (or `FAUNOSTEO_TRIAL_DIR`), 700, refused inside any git work tree:
`secrets.env` and the services' env files (600, written whole then renamed), `stack.conf` (images),
`migrations.sha256` (applied files and their hashes), `backups/`. Keys are signed for the issuer `faunosteo-trial`
and valid one year. Never run a bare `docker inspect` on a trial container: it prints the env-file values (database
password, JWT secret, keys); use `docker inspect -f` with a named non-secret field. Every command but `plan` needs a
reachable Docker daemon; the typed confirmations (`destroy`, `restore`, `grant owner|admin`) need a terminal and
refuse piped input.
| Command | What it does |
|---|---|
| `plan` | read-only: the exact resources, each port free or taken, volume and backup places, memory, the refusals `create` would meet |
| `create` | after the Rust pipeline check of the equine seed (`pnpm r5:import-equine --check`): secrets, network, volumes, services, the branch's migrations, then `equine_draft.sql` only (never `seed.sql`); checks that the historical sheet is the only official one and that its draft equals `document.json` |
| `start` / `stop` | the same containers, data kept; `start` recreates a missing service container from `stack.conf` and `secrets.env` |
| `status` | containers, keys' expiry, migrations, equine sheet, accounts with roles, rights and verified factors, backups |
| `migrate` | a cold backup, then the branch's new migrations; refuses an applied file edited since, or a new file older than the last applied; a migrate that stops part-way names its backup |
| `backup` | stops the stack a few seconds, refuses when free space is below 110 % of the volumes, archives each volume read-only through a `--rm` helper with no network, then lists it back and compares its entry count with the volume's (an archive cut short is refused), copies the state files, writes checksums last, restarts; a failed backup keeps no folder (a folder without `SHA256SUMS` is listed as incomplete) |
| `restore <backup>` | checksums, archives and the recorded database image checked first, typed confirmation `restore <backup>`, a backup of the current volumes, then the backup's volumes and secrets and the containers recreated; also rebuilds a destroyed stack |
| `rotate-keys` | a backup, then new anon and service keys signed with the same secret (one more year), Storage recreated and the gateway restarted; works on a stopped stack too (Storage is then recreated by the next `start`); the previous keys stay valid until their own expiry. Expired keys never block `start` or `rotate-keys`; `app`, `assets` and `account` refuse them and name `rotate-keys`; `status` reports them |
| `rotate-secrets` | a backup, then a new JWT secret, keys and role passwords set in one transaction as `supabase_admin` (values through psql variables, statement and error-statement logging off, a digest marker proving the commit); the new values wait in `secrets.next.env` (600) until the commit is known; every container recreated; sessions signed with the old secret end, account passwords and TOTP factors stay |
| `account <email>` | a confirmed account through GoTrue's admin API on 58999, password typed twice at a hidden prompt (12 characters min.) |
| `grant <email> <right>` | `owner` / `admin`: the operator path of the V1-2 contract §5.7.1 (verified TOTP factors shown, two for owner, one for admin; typed confirmation; `private.assign_platform_role(null, …)` and an audit row); catalogue rights as `db:sandbox trial-grant`, without its implicit admin role |
| `assets` | the equine drawings with `pnpm assets:upload` through the gateway (no overwrite, rerun = nothing to do), then every row confirmed with its object |
| `app` | the app, foreground; Ctrl+C stops it, the stack keeps running |
| `destroy` | inventory, typed confirmation `faunosteo_trial`, a backup of every trial volume, then the containers, the network, the volumes and the live state files; backups kept. With no trial resource left, it removes the live state files only |
First use, after the owner's go: `plan`, `create`, `assets`, then the two accounts.
1. Owner/admin account, for the administration trials: `account <email>`, `app`, sign in, enrol two TOTP
factors in Mon compte > Sécurité, then `grant <email> owner`, `grant <email> admin`, `grant <email>
sheet_editor`, `sheet_reviewer`, `sheet_publisher`; sign in again and pass the step-up.
2. Practitioner account: `account <email>`, enrol one TOTP factor, `grant <email> admin`, `grant <email>
sheet_editor`. This is a trial account: the technical `admin` role is there only because rule O1 activates
`sheet_editor` for a platform admin. A production practitioner holds neither `admin` nor a catalogue editing
right by default.
Rotation and recovery: `rotate-keys` before the keys expire (`status` shows the date); `rotate-secrets` to revoke.
If `rotate-secrets` loses psql, the marker the transaction wrote tells whether it committed and only the matching
file stays `secrets.env` (password probes prove nothing here: loopback connections are `trust` in this image); if a
rotation stops after the commit, `pnpm db:trial start` recreates the containers; if it stops before the commit is
known, the script names the backup taken just before, and `restore <that
backup>` returns to the previous secrets and data. Every backup holds the `secrets.env` of its time: restoring a
backup taken before a `rotate-secrets` brings the old secrets back (the script says so before the confirmation). A
restore needs the same Postgres image as the backup (recorded in its `stack.conf`).
No automated suite reaches this stack: `db-trial.sh` refuses a shell holding `SANDBOX_*` variables; the sandbox
suites (`packages/supabase/tests/sandbox/target.ts`, imported by `stack.ts` and the shell Playwright config)
accept only the sandbox's container, loopback ports and key issuer `db-sandbox`; the LAN Playwright config accepts
port 5193 only; `e2e:avatar` and `e2e:sheet-assets` require the sandbox URL and issuer; `db-sandbox.sh` refuses a
state folder holding the trial marker; every secret differs from the sandbox's. `bash scripts/db-trial-check.sh`,
run by hand, proves these refusals and the script's Docker calls with stubs, no daemon reached; it is not a
`pnpm check:all` stage because it reads the host's real ports and memory, and it refuses to run (exit 2) while the
trial stack or app holds a port.
Identification of the target (owner 2026-11-10): the trial dev server (`apps/app/vite/trial.config.ts`, shared with
`pnpm db:sandbox trial`) serves exactly one of the two stacks listed in `apps/app/vite/trial-targets.ts`. The exact
gateway string picks it; the app port (with `--strictPort`), `VITE_SUPABASE_URL` and the anon key's `role` and
`iss` must then match that same stack, and the db:trial gateway must answer `GET /__faunosteo_stack` with
`faunosteo_trial`; anything else stops Vite before it listens, with no fallback to the sandbox, DEV or another
local service. `db-trial.sh` checks, before acting: for `app` and `assets`, the gateway container published exactly
on 127.0.0.1:58801 and its identity answer; for `account`, GoTrue published exactly on 127.0.0.1:58999; for
`migrate`, Postgres exactly on 127.0.0.1:58432; and every key it hands over (Vite, the upload tool, GoTrue's admin
API, Storage's env) must be signed with this stack's JWT secret for the issuer `faunosteo-trial` and the expected
role. The identity answer is a static name, not authentication: the exact port, the container checks and the key
signatures carry the rest.
Limits: one host; no verification worker on this stack, so a draft submitted for verification stays waiting; the
historical sheet allows one working copy at a time; Mailpit is reachable on the host only; backups sit on the same
disk (copy them elsewhere by hand). Proven only at the first `create`: the database init under a volume, the
`supabase_admin` password used by `rotate-secrets`, the pids caps, `tar` and `du` in the database image (checked by
`create` before anything is made), the image's default logging settings.
```
- [ ] **Step 2: V1-2-6_OWNER_TRIAL.md**, after the intro paragraph, before `## 1.`:
```markdown
> **Pile d'essai dédiée (`pnpm db:trial`).** Une pile persistante, séparée de la sandbox et de DEV, est préparée
> (scripts et contrôles, `docs/development/SUPABASE.md` §5.2). Elle n'est pas encore créée : sa création attend
> l'accord du propriétaire. Ce guide décrit l'essai sur la sandbox (`pnpm db:sandbox trial`), qui reste disponible.
> Sur la pile dédiée, le compte praticien d'essai reçoit le rôle technique `admin` et l'habilitation
> `sheet_editor` : ce n'est pas le profil d'un praticien de production.
```
- [ ] **Step 3: TRIAL_STACK.md**: replace the status paragraph (lines 3-9) by:
```markdown
Status: IMPLEMENTED 2026-11-09 (scripts and environment-free checks), NOT CREATED: the stack is created only
after the owner's explicit go (brief « Après R0 — Implémentation technique de la pile d'essai dédiée »). Design
from the briefs « V1-2.6 UX — Arbitrages du premier essai propriétaire » §5, « R0 — Décisions propriétaire et
dernière validation graphique » (BDR-108) and « R0 — Verdict de revue graphique propriétaire » (BDR-109).
Operating guide: `SUPABASE.md` §5.2. Companion of `V1-2-6_U1_CORRECTIONS.md`.
```
in §3.3, replace the sentence « services restarted in order, the previous secrets file kept as `secrets.prev.sh`
(600) until the stack answers; account passwords are untouched. » by:
```markdown
services restarted in order; the new values wait in `secrets.next.env` (600) until the database answers, and the
previous secrets stay in the backup taken just before (no `secrets.prev` file); account passwords are untouched.
```
and append:
```markdown
## 7. Implementation (2026-11-09)
`scripts/db-trial.sh`, `scripts/db-trial-check.sh`, the suites' guard `packages/supabase/tests/sandbox/target.ts`.
Owner choices made at implementation (contract `.claude/tasks/contracts/2026-11-09-db-trial-stack-1809.md`):
cold physical backups (both volumes and the secrets, the stack stopped a few seconds) and a `restore` command
instead of `pg_dump -Fc` (§3.4); a read-only `plan` command; `db-sandbox.sh` gains a state-folder guard only, no
shared helper file (§4), and `db-trial.sh` pins its own images; GoTrue's public sign-up closed. Also: the gateway
refuses `/auth/v1/admin/`; `grant owner|admin` follows §5.7.1 of the V1-2 contract (factors first, typed
confirmation, audit row). After the plan challenge (2026-11-10): typed confirmations need a terminal; the app host
is decided at each run, not recorded (§3.2); `start` recreates a missing container; backups check free space, list
each archive back and write checksums last, and `restore`/`destroy` never remove a volume without a backup of it;
the stub harness is run by hand, not by `check:all` (it reads the host's real ports). R13 (owner 2026-11-10): the
shared trial dev server serves exactly one of two listed stacks (`apps/app/vite/trial-targets.ts`), and
`db-trial.sh` checks each operation's target and every key it hands over (`SUPABASE.md` §5.2). The tests of §5 that create a
stack under a test prefix were not run (owner brief: environment-free checks only); the stub harness replaces them
until the first `create`.
```
- [ ] **Step 4: CLAUDE.md** command block, after the `pnpm db:sandbox …` line:
```bash
pnpm db:trial plan|create|start|stop|status|migrate|backup|restore <b>|rotate-keys|rotate-secrets|app|account <email>|grant <email> <right>|assets|destroy # dedicated persistent manual trial stack (SUPABASE.md §5.2): faunosteo_trial_*, 127.0.0.1:58xxx, app :5194, own secrets and volumes; created only on the owner's go; never a test target
bash scripts/db-trial-check.sh # environment-free checks of db:trial and the db:sandbox state guard (stubs, no Docker daemon); run by hand, refuses (exit 2) while the trial stack or app holds a port
```
and in the folder tree, the `scripts/{dev-env,supabase,dev}.sh` line gains a sibling line:
`├─ scripts/{db-sandbox,db-trial,db-trial-check}.sh ← throwaway sandbox (suites), persistent trial stack (manual), its stub checks`.
- [ ] **Step 5: TODO.md**: the item « Dedicated persistent manual trial stack » becomes:
`- [ ] Dedicated persistent manual trial stack (owner 2026-11-09, D8; brief « Après R0 — Implémentation technique », contract .claude/tasks/contracts/2026-11-09-db-trial-stack-1809.md): [x] lot A suites' guard · [x] lot A2 trial dev server target (R13) · [x] lot B1 foundation · [x] lot B2 backups, restore, migrate, rotation · [x] lot B3 accounts, grants, drawings, app · [x] lot C docs · [ ] reviews · [ ] creation checkpoint (owner go before any create)`
(the orchestrator ticks reviews when they pass).
- [ ] **Step 6: checks**: `bash scripts/db-trial-check.sh` (ALL PASS), `pnpm exec prettier --check
docs/development/SUPABASE.md docs/development/TRIAL_STACK.md
docs/development/V1-2-6_OWNER_TRIAL.md` (if Prettier formats markdown here; else state it), `pnpm lint`.
- [ ] **Step 7: orchestrator commits** `docs(supabase): db:trial operating guide` + push.
---
## After the lots (orchestrator)
1. Fresh verifier against the enriched contract; fresh security-auditor (`MODE: gate`, scope b215b89..HEAD); code
review (opus, xhigh). Fixes go back to an executor; never redone by hand.
2. Checkpoint report (the 8 items of the brief §7) from: `pnpm db:trial plan` run once for real (read-only:
`info`, `ps`, `inspect -f`, `image inspect -f`, `ss`, `df`), the harness output (`bash scripts/db-trial-check.sh`,
by hand, ALL PASS or its exit-2 reason), `docker ps -a` / `volume ls` / `network ls` proof that no
`faunosteo_trial_*` resource exists, `ss -ltn` for 58000-58999 and 5194. The report lists what only the first
`create` proves (database init under a volume, `supabase_admin` password, pids caps, `tar`/`du` in the database
image, default logging, the async Vite config and its port guard at the first real `app` and the next
`db:sandbox trial`). STOP; no `create`.
#### Closure code-review fixes (2026-11-11)
1. `resource_exists`: absent only on the daemon's whole sentence for that name (`absent_answer`); the stub's `fail/daemon` fails every docker call with the real dial error; `plan` skips the resource listing with the daemon down.
2. `container_running` tri-state (an inspect error dies); `backup_now` refuses when a container still runs after the stop; `status_containers` reads through `container_exists`.
3. `restore`: each fresh volume is checked empty by the helper before the extraction (`volume_is_empty`).
4. `rotate-secrets` final message: old access tokens refused, refresh tokens and sessions stay valid.
5. `DB_INIT` and the rotation SQL: `\set VERBOSITY terse`, `log_statement`, `log_min_duration_statement` and `pgaudit.log` off before the first value-carrying statement.
6. `backup` and `migrate` call `refuse_while_app_runs`.
7. `restore` and `rotate-secrets` check `trial_host` before the confirmation and the backup.
8. `start` and `stop` lock before reading conf and secrets.
9. `grant owner|admin` for an address with no account refuses with the account hint (`no_account` marker from the factor-count SQL).
10. `SUPABASE.md` §5.2: rotate-secrets wording, no avatar worker, two post-create checks, backup and migrate rows.
Harness cases added: daemon lost mid-command (create on container, network and volume inspects, destroy, restore), plan with the daemon down lists nothing, inspect failing during the stop, container still running after the stop, non-empty volume at extraction, helper emptiness check order, backup and migrate refused while the app runs, unusable app host for restore and rotate-secrets, start and stop lock first, grant with no account (owner, admin), final rotate message, DB_INIT and rotation SQL lines and order.
#### Closure re-review fixes (2026-11-11)
Measured on Docker 29.9.3: a failing `inspect -f` (absent resource or lost daemon) exits 1, writes one newline to stdout and the sentence to stderr (container `No such container: <name>`, volume `get <name>: no such volume`, network `network <name> not found`, image `No such image: <name>`; a lost daemon: `failed to connect to the docker API at <DOCKER_HOST>; ...`).
- C1: `resource_exists` and `container_running` judge stderr alone against `absent_answer` (the merged `2>&1` compare refused every absent resource); `container_running` checks existence first, then reads the state apart. Other docker calls merging `2>&1` audited, none compared with a docker sentence.
- C2: `first_absent_image` goes through `image_exists` (strict); a lost daemon refuses ("cannot tell whether its images are present") instead of "docker pull it"; `check_backup` refuses when that helper dies.
- C3: stub: every failing inspect, four kinds, writes `"\n"` to stdout and the sentence to stderr; image honours the daemon-lost hooks; new hook `daemon-lost-after-create`; header comment updated.
- I1: SUPABASE.md: signing out right after `rotate-secrets` does not end a session (auth-js 2.117.3 ignores the 401/403 of `/logout`); the app's scope is `global`; sign in again, then sign out; revocation by the script is an owner decision not taken.
- M1: refusing-while-app-runs lists now include `backup` and `migrate` (SUPABASE.md, TRIAL_STACK.md); the `cmd_app` shortcut comment rewritten.
- M2: post-create checks: `pg_authid` as `supabase_admin`, secrets scanned through `grep -cFf <(sed ...)`, the server log location read with `show logging_collector; show log_directory; show log_destination;`, what to do above 0, unproven items added.
- M3: `set pg_stat_statements.track_utility = off;` in `DB_INIT` and `set local ...` in the rotation SQL.
- M4: `backup_failed` takes the stack's fate as an optional third argument; "still runs after the stop" no longer says it stays stopped.
- M5: `inspect_refused` shows Docker's answer (quoted, 200 characters), "unexpected value" wording, hint `docker info`.
- M6: create's EXIT trap prints a hint when its listing fails; destroy's way-out line says when a listing failed.
- M7: `cmd_start` checks `trial_host` before starting or recreating a container.
- H: `lines_before_first` checks order; the rotation check asserts every silencing line; the tautological `! grep "already exists"` replaced by one that can fail; cases: stream split of the stub, plan, create, backup and status on stub-absent resources, create with the daemon lost after the first resource, restore with the daemon lost, start with no usable host.
#### Closure second re-review fixes (2026-11-11)
- I-1: SUPABASE.md §5.2 post-create scan also counts `extensions.pg_stat_statements` per key; above 0: reset it as `supabase_admin`, then `rotate-secrets`, existing backups keep the text.
- m1: `check()` runs every check with stdin from `/dev/null`; the two `grep ... $(find ...)` checks (rotation SQL, account body) get `/dev/null` as an extra file.
- m2/m3: `refuse_inspect` states a reason alone (`container_running`'s two refusals); `inspect_refused` hint adds the Docker 29.9.3 wording note; SUPABASE.md records it.
- m4: `plan_images` prints "unknown (Docker daemon unreachable)" when the daemon is down.
- m5: `start_containers` calls `trial_host` before the first start when GoTrue's container is missing.
- m6: harness cases: the quoted answer and hints of `inspect_refused`, the "listing failed" way-out lines (stub hook `lost-after-archive`).
- m7/m8: SUPABASE.md: stale access token keeps getting 401 after rotation until refresh or sign-in; log scan defines `<log_path>`, labels each count, states both log-location cases.
- m9: `restore_extracted_note` names what was extracted when a volume is not empty or unreadable (stub `volume-ls-storage`).
- m10: `destroy_stop_line` prints `docker stop <running>` before the `docker rm` way-out line.
## Review record (orchestrator)
Each reviewer was a fresh subagent; each fix round went to a fresh executor; the orchestrator re-ran the checks
before every commit.
- Plan challenge (STEP 2b, 2026-11-09): simplicity CONCERNS(3), correctness FATAL(7), robustness CONCERNS(6);
plan revised, correctness re-challenge CONCERNS(2), closed in the plan.
- R13 counter-check (owner condition, 2026-11-10, before lot A2): CONCERNS(5) (5 MAJOR, 4 MINOR) → plan revised →
CONCERNS(3) → plan revised → SOLID (MINOR findings only). Criterion 16's "CONFORME" is this SOLID verdict.
- Lot A (b682f78): spec COMPLIANT; quality CHANGES(6) (6 MINOR) → fixed → APPROVED on re-review.
- Lot A2 (9f60d84): spec COMPLIANT; quality CHANGES(4) (none CRITICAL) → fixed → APPROVED on re-review.
- Lot B1 (1a7b952): spec COMPLIANT; safety CHANGES (4 IMPORTANT, 9 MINOR) → 12 fixes → re-review CHANGES(1)
(`resource_exists`) → fixed; harness 87/0.
- Lot B2 (b85614a): spec COMPLIANT; safety CHANGES REQUIRED (14) → fixed, SIGTERM trap guard fixed → re-review: 14
closed, one IMPORTANT (destroy dead end) → 7 fixes; harness 210/0.
- Lot B3 (3b687a2): spec COMPLIANT (4 MINOR); safety CHANGES REQUIRED (3 IMPORTANT, 4 MINOR) → 11 fixes → re-review:
findings closed, the `app` no-lock comment incomplete → fixed with 5 harness tightenings; harness 323/0.
- Lot C (f7bf0c8): orchestrator read of the diff against the script; one sentence restored to A13's wording (the
practitioner's `admin` exists for O1).
- Closure GATE 0 (2026-11-10): `gates.sh run` MET (8 runnable of 24). GATE 1 verifier: ECARTS(20), 0 NOT-MET: 3
UNVERIFIABLE (15, 16, 24: records and the checkpoint report, written here and in the closure report), 2 files
outside FILE SCOPE (the plan and the contract, now named there), 15 in-file `# shellcheck disable` directives
(SC2016, SC2178 on namerefs, SC2046) that only the owner can authorise; the four in this plan are copies of the
script's.
## Self-review (writing-plans)
- Spec coverage: criteria 1 (lots B1-B3 + package.json), 2-5 (B1 create checks), 6 (B1 state + B2 rotation), 7
(lot A + A2 + B1 refusals and identity table + sandbox guard), 8 (B3), 9 (B1 seed checks + B3 assets), 10 (B2 migrate), 11 (B2 backup,
restore), 12 (harness by hand, lot A tests, shellcheck; no check:all stage, criterion 12 does not ask for one), 13
(global constraints, numstat additions-only check on db-sandbox.sh), 14 (lot C), 15 (after the lots).
- Placeholders: none left.
- Names: `backup_now`, `LAST_BACKUP`, `confirm`, `check_backup`, `parse_secrets`, `parse_conf`, `write_private`,
`remove_live_files`, `LIVE_FILES`, `BACKUP_FILES`, `run_service`, `start_containers`, `remove_containers`,
`ports_free`, `port_listening`, `trial_host`, `restart_gateway`, `ensure_network`, `daemon_ok`; harness `trial`,
`trial_input`, `trial_tty`, `backup_count`; stub switches `fail/{daemon,networks-create,archive,migrate,<tag>}`,
`commit-then-fail`, answer `rotation-marker`, `fail/{context,image}`; tags `applied`, `asset-rows`, `assets-uploaded`, `equine`, `equine-status`,
`accounts`, `inventory`, `reload`, `rotate`, `grant-factors`, `grant-role`, `grant-catalog` match between script
and stub answers.
- Review Focus: items 1-2 in B1 (half-written state, failed create, daemon down, held port and 0.0.0.0 at start,
missing container), 3-4 in B2 (migrate part-way, failed and incomplete backups), 5 in B2 and B3 (pty driver and
piped-phrase refusals), 7 in A2 and B3 (target identification, R13 fixed by owner decision 2026-11-10).
- Owner conditions 2026-11-10: FAUNOSTEO_TRIAL_DIR canonical (`realpath -m`, symlink cases into the repository and to
the sandbox state); `command -v ss` at dispatch and `port_listening` failing closed on any ss error (ss wrapper
failing mode); `secrets.prev` dropped because rotate-secrets and rotate-keys both take `backup_now` first (read-back,
entry count, 700/600, checksums: asserted in H5).
- Challenge (2026-11-10, three lenses) applied: see the gate's CHALLENGE SUMMARY; kept with justification: the
out-of-order migration refusal (before the cold backup) and both seed checks (folder vs pipeline, then DB vs
folder).