The suite's supabase-js clients followed redirects by default, so a 3xx from the sandbox gateway would have carried the apikey header to another origin. `noRedirectFetch` (`redirect: 'error'`) is passed as `global.fetch` of both client factories; a regression test with two loopback servers proves REST, auth and Storage calls stop at the 302. Live proof j and a second 5/5 run are in the audit log; no production client changes.
916 lines
60 KiB
Markdown
916 lines
60 KiB
Markdown
# Supabase: local stack, migrations, tests
|
|
|
|
Status: Step 3 deliverable, extended at Step 4 (2026-10-08). Covers the
|
|
foundation (profiles, platform roles, audit log) and the anatomical sheet
|
|
tables (`ANATOMICAL_SHEET_MODEL.md`). Conventions: `BACKEND_ARCHITECTURE.md` §3–§9.
|
|
|
|
## 1. Layout
|
|
|
|
```text
|
|
supabase/
|
|
├─ config.toml CLI config (public schema exposed only, PG 17, strong passwords)
|
|
├─ migrations/ forward-only SQL, <timestamp>_<name>.sql
|
|
│ ├─ 20261008100000_foundation.sql schemas private/audit, updated_at, audit log
|
|
│ ├─ 20261008100100_profiles.sql profiles + auth.users trigger + RLS
|
|
│ ├─ 20261008100200_platform_roles.sql platform_role, profile_roles, helpers, RPCs
|
|
│ ├─ 20261008110000_anatomical_sheets.sql species, sheets, immutable versions, assets, lifecycle RPCs
|
|
│ ├─ 20261008110100_private_execute_hardening.sql EXECUTE revoked from PUBLIC on the Step 3 helpers
|
|
│ ├─ 20261009100000_sheet_asset_storage.sql bucket anatomical-sheet-assets + read policy through the sheet
|
|
│ ├─ 20261010100000_sheet_document_schema_v2.sql document schema 2: references, review_status, count_range checks
|
|
│ ├─ 20261011100000_profiles_professional.sql practice_name, professional_title, siret
|
|
│ ├─ 20261013100000_files_foundation.sql assets table, seven private buckets, avatar policies through assets, purge queue, browser + worker RPCs (STORAGE.md)
|
|
│ ├─ 20261025100000_workspaces.sql V1-1.1 (M1): workspaces, workspace_members, personal workspace per account, access helpers, audit correlation cap (§3.3)
|
|
│ ├─ 20261025110000_clients.sql V1-1.2 (M2): clients, unaccent, client RPCs and search, write budget per workspace, N9 audit filter (§3.4)
|
|
│ └─ 20261026100000_animals_relations.sql V1-1.4 (M3): species rows, animals, relations with history and voiding, their RPCs and reads (§3.5)
|
|
├─ seed.sql LOCAL dev accounts + first owner (never run in prod)
|
|
├─ seed/anatomical-sheets/equine/ official equine sheet as a DRAFT, generated by `pnpm import:equine`
|
|
│ ├─ legacy-sources/ the historical Horse.svg, Horse_head.svg, pathologies.json, vertebrae.ts (importer input)
|
|
│ ├─ legacy-import/ baseline import (document, id map, SVG copies, IMPORT-REPORT.md), no SQL
|
|
│ ├─ curation/ authored changes.json + references.json
|
|
│ └─ draft/ curated working revision (document, horse.svg, sheet-ref.json, report-migration-map.json, CHANGELOG, REVIEW-REPORT, review/*.svg; bundled into the app with SHEET_BUNDLE=draft) + equine_draft.sql: idempotent, converging seed (sheet, 1 asset row, working draft), applied by db reset and by PGlite
|
|
└─ tests/ pgTAP, one file per concern, allowed AND denied paths
|
|
packages/supabase/
|
|
├─ src/client.ts createBrowserClient() (anon key, PKCE)
|
|
├─ src/database.types.ts `supabase gen types` output
|
|
├─ src/anatomical-sheets.ts publishSheetVersion(): the core's verdict (`sheet.check`) as gate before the RPC
|
|
├─ src/assets.ts avatar flow: begin / quarantine upload / confirm / remove / own state / download, closed AssetFailure kinds
|
|
├─ tests/db/ PGlite harness: runs the pgTAP files without Docker
|
|
└─ tests/e2e/ real-stack end-to-end (avatar flow with the worker container), `pnpm e2e:avatar`
|
|
crates/core/src/domain/sheet/ canonical sheet document and its integrity rules (Rust core; `packages/domain` deleted at R5.7)
|
|
```
|
|
|
|
## 2. Prerequisites
|
|
|
|
- Node 24 + pnpm via corepack (`WORKSPACE.md`).
|
|
- Docker daemon reachable by your user for the real stack. If `docker info`
|
|
says `permission denied ... docker.sock`, add yourself to the `docker`
|
|
group and log in again:
|
|
|
|
```bash
|
|
sudo usermod -aG docker "$USER"
|
|
```
|
|
|
|
- No Docker: the PGlite suite still runs (see §5), the CLI commands do not.
|
|
|
|
The Supabase CLI is a root devDependency (`pnpm supabase <cmd>`), pinned
|
|
like every other tool. Never install it globally.
|
|
|
|
## 3. Start, stop, reset
|
|
|
|
Every CLI call goes through `scripts/supabase.sh` (`pnpm db:*`, `pnpm supabase
|
|
<args>`): it exports the DEV origin that `config.toml` reads with `env()`
|
|
(`api.external_url`, `auth.site_url`, `studio.api_url`). A direct
|
|
`node_modules/.bin/supabase start` would leave the literal `env(...)` text in
|
|
GoTrue's mail links. The CLI publishes API 54321, PostgreSQL 54322, Studio
|
|
54323 and Mailpit 54324 on `0.0.0.0` (no bind option); the host firewall
|
|
closes 54322-54324 on the LAN interface and the DEV gateway serves Mailpit
|
|
behind basic auth on 54325 (`ENVIRONMENTS.md` §3).
|
|
|
|
```bash
|
|
pnpm db:start # supabase start: pulls images, applies migrations + seed, prints URLs and keys
|
|
pnpm db:stop # supabase stop (keeps the volume)
|
|
pnpm db:reset # drop + replay every migration + seed from zero
|
|
```
|
|
|
|
Local endpoints (`config.toml`):
|
|
|
|
| Service | URL |
|
|
| --------------------------------------- | --------------------------------------------------------- |
|
|
| API (PostgREST, Auth, Storage) | `http://127.0.0.1:54321` |
|
|
| PostgreSQL | `postgresql://postgres:postgres@127.0.0.1:54322/postgres` |
|
|
| Studio | `http://127.0.0.1:54323` |
|
|
| Mail catcher (confirmation / OTP mails) | `http://127.0.0.1:54324` |
|
|
|
|
Addresses on the host itself. From another device of the network: app `:5173`,
|
|
API `:54321`, Mailpit through the gateway `:54325` (basic auth); Studio by SSH
|
|
tunnel; PostgreSQL not at all (`ENVIRONMENTS.md` §3).
|
|
|
|
`pnpm db:start` prints the `anon` and `service_role` keys. Only the anon key
|
|
ever reaches the browser.
|
|
|
|
Containers, volumes and network are named `supabase_<service>_faunosteo`
|
|
(`project_id` of `config.toml`): the CLI imposes this pattern, so the
|
|
`faunosteo_` prefix convention of DEPLOY.md ("Docker resource naming")
|
|
applies as a suffix here. Several projects can run side by side as long as
|
|
their `project_id` and ports differ.
|
|
|
|
Two kinds of seed (`config.toml [db.seed] sql_paths`):
|
|
|
|
| File | Scope | Content |
|
|
| -------------- | ----------------- | ------------------------------------------------------------------------------------------------------------------------------ |
|
|
| `seed.sql` | local only | dev accounts + first owner |
|
|
| `seed/…/*.sql` | every environment | versioned reference data, idempotent (today: the official equine sheet as a draft); each file listed explicitly in `sql_paths` |
|
|
|
|
The PGlite harness applies every `.sql` under `seed/` after the migrations,
|
|
`seed.sql` never (it needs the real auth schema); a test fails when a seed
|
|
file is missing from `sql_paths`. On another environment, apply a versioned
|
|
seed with `psql -f` from a privileged session, then upload its assets (§3.1).
|
|
Never run `supabase db push --include-seed` against a shared database: the
|
|
list also contains `seed.sql` and its known local password.
|
|
|
|
### 3.1 Sheet assets (Storage)
|
|
|
|
SVG files live in the private bucket `anatomical-sheet-assets`, keyed by
|
|
content (`<sha256>.svg`). Reading goes through the sheet (policy
|
|
`anatomical_sheet_assets_read`): a published version of a usable sheet, or
|
|
any version for its editors. No API role uploads; the operator does, with
|
|
the service key, from the generated seed folder. The tool is the Rust binary
|
|
`upload-sheet-assets` (`crates/core`, feature `upload`, R5.5). It targets the
|
|
**local stack only**: there is no default destination and a host that is not
|
|
this machine (or the configured `FAUNOSTEO_DEV_HOST`, a private address) is
|
|
refused before any request. Run it by hand; the assistant does not.
|
|
|
|
```bash
|
|
pnpm db:start # the seed creates the asset rows (pending)
|
|
export SUPABASE_URL=http://127.0.0.1:54321 # http, explicit port; the DEV address needs FAUNOSTEO_DEV_HOST
|
|
read -rs SUPABASE_SERVICE_ROLE_KEY && export SUPABASE_SERVICE_ROLE_KEY # typed, not in shell history
|
|
pnpm assets:upload --check supabase/seed/anatomical-sheets/equine/draft # read-only plan
|
|
pnpm assets:upload supabase/seed/anatomical-sheets/equine/draft # upload + confirm
|
|
```
|
|
|
|
What a run does, in order, and stops at the first refusal before the first
|
|
write:
|
|
|
|
1. The folder must lie under `supabase/seed/anatomical-sheets`. `assets.json`
|
|
is read under a size cap (strict keys, plain `.svg` file names, lowercase
|
|
sha256 and uuid, no repeated entry, 64 entries at most). Each drawing is
|
|
read under a cap and must match its entry (size, sha256) and be a drawing
|
|
the product accepts (the core's SVG reader, nothing removed).
|
|
2. The destination is checked, then a handshake: the bucket exists, is
|
|
private, and the key is accepted.
|
|
3. Each entry is planned from what the destination holds: an object under the
|
|
key is read back and hashed; different bytes, a missing asset row or a row
|
|
recording another hash refuse the run (nothing is overwritten, no row is
|
|
invented; apply the seed first).
|
|
4. Missing objects are created without replacing anything (`x-upsert:
|
|
false`), read back and verified, and only then is the row confirmed
|
|
(`confirm_sheet_asset_upload`, service role only): the read policy ignores
|
|
a row whose upload was never confirmed, so a user who registers a known
|
|
hash on their own sheet opens nothing.
|
|
|
|
A rerun after any failure resumes: an object already verified is kept, a
|
|
confirmed row is left alone. A connection failure or a 5xx answer is retried
|
|
three times (four attempts); a refusal (401, 403, a state mismatch, an
|
|
oversized answer) is final. Exit codes: 0 done, 1 refused (usage, manifest,
|
|
destination, content, handshake, state, access), 2 an argument that is not
|
|
UTF-8, 3 network. The DEV address counts only if it is an address of this
|
|
machine. The key is
|
|
never printed and goes through no proxy. `pnpm e2e:sheet-assets` runs the
|
|
whole behaviour (upload, rerun, anon and wrong key, refused destinations,
|
|
unreachable port) on the throwaway sandbox only: since 2026-11-02 it refuses,
|
|
before building or any request, every target but the sandbox gateway
|
|
`http://127.0.0.1:57800` with keys naming the sandbox issuer (its runner
|
|
`pnpm db:sandbox sheet-assets-e2e` arrives with V1-2); it never reads the DEV
|
|
keys and never targets DEV. The TypeScript tool
|
|
(`packages/supabase/scripts/upload-sheet-assets.ts`) was deleted at R5.7 (RUST.md §11.23).
|
|
|
|
### 3.2 Files worker (Storage, Step 8)
|
|
|
|
User files land in private buckets and are processed by the Rust worker
|
|
(`crates/worker`, `docs/development/STORAGE.md`). Against the local stack:
|
|
|
|
```bash
|
|
pnpm worker:image # faunosteo_worker:dev
|
|
pnpm e2e:avatar # = pnpm db:sandbox avatar-e2e: the suite and the worker on the throwaway stack (§5.1) only
|
|
docker compose --profile worker up worker # the same container from docker-compose.yml, reading .env.worker
|
|
```
|
|
|
|
`.env.worker` (not editable by the assistant): `SUPABASE_URL=http://supabase_kong_faunosteo:8000`,
|
|
`SUPABASE_SERVICE_ROLE_KEY=<the service key of pnpm supabase status>`, `WORKER_ID=dev`.
|
|
The service key stays in that file and in the shell; it never reaches the
|
|
app `.env`, env.js or a commit.
|
|
|
|
Seed accounts (local only, password `LocalDev-only-password-1`):
|
|
|
|
| Email | Role |
|
|
| ----------------------- | ------------------------------------------- |
|
|
| `owner@faunosteo.local` | platform owner (bootstrapped by `seed.sql`) |
|
|
| `alice@faunosteo.local` | plain user |
|
|
| `bob@faunosteo.local` | plain user |
|
|
|
|
### 3.3 Workspaces (V1-1.1, migration M1)
|
|
|
|
Contract: `docs/development/V1-1_OWNERS_ANIMALS.md` §4, §8.1, §9. Every
|
|
account has exactly one personal workspace, created by the trigger
|
|
`profiles_create_personal_workspace` in the sign-up transaction (GoTrue →
|
|
`handle_new_auth_user` → profile → workspace + `holder` membership), through
|
|
the single write path `private.ensure_personal_workspace(uuid)` (one
|
|
statement, idempotent, race-safe). A deferred constraint trigger refuses at
|
|
commit a personal workspace without its holder or with any other member.
|
|
Reads: RLS through `private.caller_workspace_ids()` (member, active profile,
|
|
confirmed email, `auth.uid()` only). No API role writes either table; `anon`
|
|
and `service_role` hold no privilege on them. From M2 on, record tables use
|
|
the same helpers; the app passes its workspace id explicitly.
|
|
|
|
M1, M2 and M3 are written and tested on PGlite and on the isolated stack
|
|
(§5.1), none is applied to the DEV stack. Applying them there is an owner
|
|
action, never run by Claude, never with `db reset`, `db push`, `migration
|
|
repair`, a volume removal or any other destructive command. The procedure
|
|
below takes DEV from its current state (every migration up to
|
|
`20261013100000` applied, fictitious accounts present) to M3 in one run; the
|
|
upgrade path was rehearsed on the isolated stack (`pnpm db:sandbox up upgrade
|
|
20261025100000`). Steps 0 to 3 only read DEV (the SQL helpers force a
|
|
read-only session); step 3b writes on the sandbox only; step 4 is the one
|
|
write on DEV. Nothing below prints a key: the database password stays in the
|
|
containers' environment, and `supabase status` is read with `-o env`
|
|
filtered to the API URL, because its default output prints the service key
|
|
and the JWT secret.
|
|
|
|
Read-only preflight of 2026-10-26 (Claude; CLI 2.120.0, PostgreSQL 17.11,
|
|
branch at `9f585e9`): history 9 applied, M1 to M3 pending, nothing
|
|
remote-only, no linked project; none of the tables, types, functions,
|
|
trigger, constraint or extension M1 to M3 create exists; types drift =
|
|
exactly the 24 names of step 1, nothing removed; 6 accounts = 6 profiles (4
|
|
confirmed), 1 platform role, 9 asset rows (1 clean), 2 storage objects (11
|
|
files in the storage volume, leftovers of earlier resets), 13 due rows in the
|
|
purge queue, 35 audit rows, species `equine` only, vault empty; `unaccent`
|
|
and `pgtap` available, not installed. Run steps 0 to 2 again on the day: DEV
|
|
changes as it is used.
|
|
|
|
```bash
|
|
# Same shell for every step: helpers, backup folder, private file mode.
|
|
devsql() { docker exec -i supabase_db_faunosteo sh -c 'PGOPTIONS="-c default_transaction_read_only=on" \
|
|
PGPASSWORD="$POSTGRES_PASSWORD" psql -X -q -A -t -v ON_ERROR_STOP=1 -h 127.0.0.1 -U postgres -d "$1"' sh "$1"; }
|
|
boxsql() { docker exec -i faunosteo_dbsandbox_db sh -c 'PGOPTIONS="-c default_transaction_read_only=on" \
|
|
PGPASSWORD="$POSTGRES_PASSWORD" psql -X -q -A -t -v ON_ERROR_STOP=1 -h 127.0.0.1 -U postgres -d "$1"' sh "$1"; }
|
|
B=~/faunosteo-backups
|
|
umask 077
|
|
|
|
# 0. Target: the local CLI stack of this project, nothing remote.
|
|
git status -sb # the V1-1 branch, clean, equal to origin
|
|
test ! -f supabase/.temp/project-ref && echo "no linked project"
|
|
pnpm -s supabase status -o env | grep '^API_URL=' # http://<DEV host>:54321
|
|
docker port supabase_db_faunosteo 5432 # 0.0.0.0:54322: the DEV database
|
|
docker inspect -f '{{.Name}} {{.Config.Image}}' supabase_db_faunosteo faunosteo_dbsandbox_db
|
|
# the same postgres:17.11.0.004 image twice; no sandbox
|
|
# container → `pnpm db:sandbox up fresh` first (§5.1)
|
|
mkdir -p "$B" && chmod 700 "$B" # outside Git, never synced, never attached to a report
|
|
|
|
# 1. History and drift: only the V1-1 migrations may be pending.
|
|
pnpm supabase migration list --local # local = remote on every row up to 20261013100000, remote empty on
|
|
# 20261025100000 (M1), 20261025110000 (M2), 20261026100000 (M3), nothing else
|
|
pnpm -s db:types:check > "$B/types-before.diff"
|
|
grep -c '^<' "$B/types-before.diff" # 0: nothing removed
|
|
grep -oE '^> [a-z_]+:' "$B/types-before.diff" | sort -u | wc -l
|
|
# 24 names, only these: workspaces, workspace_members, workspace_kind, workspace_member_role (M1);
|
|
# clients, client_kind, create_client, update_client, set_client_archived, search_clients (M2);
|
|
# animals, animal_client_relations, animal_sex, animal_relation_role, create_animal, update_animal,
|
|
# set_animal_archived, create_/end_/update_/void_animal_client_relation, search_animals,
|
|
# list_client_animals, client_archive_preview (M3); any other name = drift, STOP
|
|
|
|
# 2. State before: counts, row fingerprints (md5 only, no row printed) and structure.
|
|
cat > "$B/counts.sql" <<'SQL'
|
|
set timezone = 'UTC';
|
|
select 'users ' || count(*) from auth.users;
|
|
select 'identities ' || count(*) from auth.identities;
|
|
select 'profiles ' || count(*) from public.profiles;
|
|
select 'profile_roles ' || count(*) from public.profile_roles;
|
|
select 'species ' || count(*) from public.species;
|
|
select 'sheets ' || count(*) from public.anatomical_sheets;
|
|
select 'sheet_versions ' || count(*) from public.anatomical_sheet_versions;
|
|
select 'assets ' || count(*) from public.assets;
|
|
select 'purge_queue ' || count(*) from private.object_purge_queue;
|
|
select 'buckets ' || count(*) from storage.buckets;
|
|
select 'objects ' || count(*) from storage.objects;
|
|
select 'audit_logs ' || count(*) from audit.audit_logs;
|
|
select 'migrations ' || string_agg(version, ',' order by version) from supabase_migrations.schema_migrations;
|
|
select 'md5 users ' || md5(coalesce(string_agg(t::text, '|' order by t::text), '')) from auth.users t;
|
|
select 'md5 profiles ' || md5(coalesce(string_agg(t::text, '|' order by t::text), '')) from public.profiles t;
|
|
select 'md5 profile_roles ' || md5(coalesce(string_agg(t::text, '|' order by t::text), '')) from public.profile_roles t;
|
|
select 'md5 assets ' || md5(coalesce(string_agg(t::text, '|' order by t::text), '')) from public.assets t;
|
|
select 'md5 sheet_versions ' || md5(coalesce(string_agg(t::text, '|' order by t::text), '')) from public.anatomical_sheet_versions t;
|
|
select 'md5 species equine ' || md5(t::text) from public.species t where t.id = 'equine';
|
|
select 'tables ' || n.nspname || ' ' || count(*) from pg_class c join pg_namespace n on n.oid = c.relnamespace
|
|
where c.relkind in ('r', 'p') and n.nspname in ('public', 'private', 'audit', 'auth', 'storage') group by n.nspname order by n.nspname;
|
|
select 'functions ' || n.nspname || ' ' || count(*) from pg_proc p join pg_namespace n on n.oid = p.pronamespace
|
|
where n.nspname in ('public', 'private') group by n.nspname order by n.nspname;
|
|
select 'policies ' || count(*) from pg_policies where schemaname in ('public', 'storage', 'audit');
|
|
select 'rls tables public ' || count(*) from pg_class c join pg_namespace n on n.oid = c.relnamespace
|
|
where n.nspname = 'public' and c.relkind = 'r' and c.relrowsecurity;
|
|
SQL
|
|
devsql postgres < "$B/counts.sql" > "$B/counts-before.txt" && cat "$B/counts-before.txt"
|
|
# users = profiles = N (6 on 2026-10-26); the other lines are the reference of steps 3b and 5
|
|
# Privileges (read only), the reference of step 3b: effective ACLs of the application schemas, grants
|
|
# of auth and storage to the API roles, policies, triggers, constraints, extensions, postgres defaults.
|
|
cat > "$B/privs.sql" <<'SQL'
|
|
select 'acl relations ' || n.nspname || ' ' || md5(string_agg(c.relname || ':' || coalesce(c.relacl, acldefault(case c.relkind when 'S' then 's'::"char" else 'r'::"char" end, c.relowner))::text || ':' || c.relrowsecurity || c.relforcerowsecurity, '|' order by c.relname))
|
|
from pg_class c join pg_namespace n on n.oid = c.relnamespace
|
|
where n.nspname in ('public', 'private', 'audit', 'supabase_migrations') and c.relkind in ('r', 'v', 'S', 'p') group by n.nspname order by n.nspname;
|
|
select 'acl functions ' || n.nspname || ' ' || md5(string_agg(p.oid::regprocedure::text || ':' || coalesce(p.proacl::text, '-') || ':' || p.prosecdef || ':' || coalesce(array_to_string(p.proconfig, ','), '-'), '|' order by p.oid::regprocedure::text))
|
|
from pg_proc p join pg_namespace n on n.oid = p.pronamespace where n.nspname in ('public', 'private', 'audit') group by n.nspname order by n.nspname;
|
|
select 'acl schemas ' || md5(string_agg(nspname || ':' || coalesce(nspacl::text, '-'), '|' order by nspname))
|
|
from pg_namespace where nspname in ('public', 'private', 'audit');
|
|
select 'grants ' || n.nspname || ' ' || md5(string_agg(c.relname || ':' || pg_get_userbyid(a.grantee) || ':' || a.privilege_type || ':' || a.is_grantable, '|'
|
|
order by c.relname, pg_get_userbyid(a.grantee), a.privilege_type))
|
|
from pg_class c join pg_namespace n on n.oid = c.relnamespace cross join lateral aclexplode(c.relacl) a
|
|
where n.nspname in ('auth', 'storage') and a.grantee not in (c.relowner, 'postgres'::regrole) group by n.nspname order by n.nspname;
|
|
select 'policies ' || md5(string_agg(schemaname || '.' || tablename || '.' || policyname || ':' || cmd || ':' || roles::text || ':' || coalesce(qual, '-') || ':' || coalesce(with_check, '-'), '|'
|
|
order by schemaname, tablename, policyname)) from pg_policies where schemaname in ('public', 'storage', 'audit');
|
|
select 'triggers ' || count(*) || ' ' || md5(string_agg(tgrelid::regclass::text || '.' || tgname || ':' || tgenabled::text || ':' || tgfoid::regprocedure::text, '|' order by tgrelid::regclass::text, tgname))
|
|
from pg_trigger t join pg_class c on c.oid = t.tgrelid join pg_namespace n on n.oid = c.relnamespace
|
|
where not tgisinternal and n.nspname in ('public', 'private', 'audit', 'auth', 'storage');
|
|
select 'constraints ' || count(*) || ' ' || md5(string_agg(conrelid::regclass::text || '.' || conname || ':' || pg_get_constraintdef(oid), '|' order by conrelid::regclass::text, conname))
|
|
from pg_constraint where connamespace::regnamespace::text in ('public', 'private', 'audit');
|
|
select 'extensions ' || string_agg(extname || '@' || extversion, ',' order by extname) from pg_extension;
|
|
select 'default acl postgres ' || count(*) || ' ' || md5(string_agg(defaclnamespace::regnamespace::text || ':' || defaclobjtype::text || ':' || defaclacl::text, '|' order by defaclnamespace::regnamespace::text, defaclobjtype::text))
|
|
from pg_default_acl where defaclrole = 'postgres'::regrole;
|
|
SQL
|
|
devsql postgres < "$B/privs.sql" > "$B/privs-before.txt" && wc -l < "$B/privs-before.txt" # 15
|
|
cat > "$B/managed.sql" <<'SQL'
|
|
select 'vault secrets ' || count(*) from vault.secrets;
|
|
select 'managed default acl ' || count(*) from pg_default_acl
|
|
where defaclrole in ('supabase_admin'::regrole, 'supabase_auth_admin'::regrole);
|
|
SQL
|
|
devsql postgres < "$B/managed.sql"
|
|
# vault secrets 0, else STOP: no backup, no M1 to M3; vault rows need a procedure of their own (keys)
|
|
# managed default acl D (21 on 2026-10-26): step 3b leaves out exactly D + 8 entries
|
|
|
|
# 3. Backup outside Git: the whole `postgres` database (auth, storage metadata, public, private, audit,
|
|
# migration history, grants) and the bytes of the Storage files (the storage container's volume).
|
|
docker exec supabase_db_faunosteo sh -c 'PGPASSWORD="$POSTGRES_PASSWORD" pg_dump -h 127.0.0.1 -U postgres -d postgres -Fc' \
|
|
> "$B/dev-before-v1-1.dump" && chmod 600 "$B/dev-before-v1-1.dump"
|
|
docker cp supabase_storage_faunosteo:/mnt - > "$B/dev-before-v1-1-storage.tar" && chmod 600 "$B/dev-before-v1-1-storage.tar"
|
|
ls -l "$B" # both files non-empty, -rw-------
|
|
docker exec -i supabase_db_faunosteo pg_restore --list < "$B/dev-before-v1-1.dump" \
|
|
| grep -cE "TABLE DATA (auth users|public profiles|storage objects|supabase_migrations schema_migrations)" # 4
|
|
tar -tvf "$B/dev-before-v1-1-storage.tar" | grep -c '^-' # the volume's file count (11 on 2026-10-26)
|
|
|
|
# 3b. The dump restores, on the isolated stack (never on the DEV server), with exit status 0: a scratch
|
|
# database of the sandbox, restored in one transaction that stops at the first error, compared with
|
|
# step 2, then dropped. `restore_check_v1_1` is the only database created or dropped here.
|
|
# Prerequisite, sandbox only: DEV owns the cluster role `supabase_functions_admin` (schema
|
|
# `supabase_functions`), a fresh sandbox does not, and the dump's ACL entries name it. Create
|
|
# it once there, NOLOGIN with no global privilege (what a no-owner replay needs); never on DEV:
|
|
docker exec -i faunosteo_dbsandbox_db sh -c 'PGPASSWORD="$POSTGRES_PASSWORD" psql -X -q -v ON_ERROR_STOP=1 -h 127.0.0.1 -U postgres -d postgres' <<'SQL'
|
|
do $$ begin
|
|
if not exists (select 1 from pg_roles where rolname = 'supabase_functions_admin') then
|
|
create role supabase_functions_admin nologin nosuperuser nocreaterole nocreatedb noreplication nobypassrls noinherit;
|
|
end if;
|
|
end $$;
|
|
SQL
|
|
# The restore leaves out the D + 8 TOC entries `postgres` may not replay; the dump file itself stays
|
|
# complete and unchanged (the list is a copy of its table of contents):
|
|
# - D « DEFAULT ACL … supabase_admin | supabase_auth_admin »: default privileges for objects those
|
|
# roles create (platform defaults; `postgres` may not change another role's defaults; no data,
|
|
# no application privilege; the sandbox image holds its own);
|
|
# - 5 « ACL vault »: secrets, decrypted_secrets, create_secret, update_secret,
|
|
# _crypto_aead_det_decrypt; with --no-owner their extension makes them postgres's own, and
|
|
# postgres cannot grant itself a grant option; the service_role grants of these entries come
|
|
# out identical from the extension (rehearsal of 2026-10-26);
|
|
# - 1 « ACL pg_catalog » pg_reload_conf, 1 « ACL extensions » pg_stat_statements_reset: owned by
|
|
# supabase_admin, postgres may not grant on them;
|
|
# - 1 « TABLE DATA vault secrets »: postgres may not write vault.secrets; nothing is lost only
|
|
# because step 2 read 0 rows. With rows there, this procedure does not apply.
|
|
docker exec -i faunosteo_dbsandbox_db pg_restore --list < "$B/dev-before-v1-1.dump" > "$B/full.list"; echo "list exit $?" # 0
|
|
grep -E '^[0-9]+; [0-9]+ [0-9]+ (DEFAULT ACL [a-z_]+ DEFAULT PRIVILEGES FOR (SEQUENCES|FUNCTIONS|TABLES) (supabase_admin|supabase_auth_admin)|ACL pg_catalog FUNCTION pg_reload_conf\(\) supabase_admin|ACL extensions FUNCTION pg_stat_statements_reset\(.*\) supabase_admin|ACL vault (FUNCTION|TABLE) .* supabase_admin|TABLE DATA vault secrets supabase_admin)$' \
|
|
"$B/full.list" > "$B/excluded.list"
|
|
cut -d' ' -f4-5 "$B/excluded.list" | sort | uniq -c
|
|
# exactly: D « DEFAULT ACL », 1 « ACL extensions », 1 « ACL pg_catalog », 5 « ACL vault », 1 « TABLE DATA »;
|
|
# any other count = STOP (never widen the pattern to make it fit)
|
|
grep -vxFf "$B/excluded.list" "$B/full.list" > "$B/restore.list"
|
|
wc -l < "$B/full.list"; wc -l < "$B/restore.list" # the second = the first minus D + 8
|
|
docker cp "$B/restore.list" faunosteo_dbsandbox_db:/tmp/restore.list # names only, no data; gone with the sandbox
|
|
docker exec faunosteo_dbsandbox_db sh -c 'PGPASSWORD="$POSTGRES_PASSWORD" createdb -h 127.0.0.1 -U postgres restore_check_v1_1'
|
|
docker exec -i faunosteo_dbsandbox_db sh -c 'PGPASSWORD="$POSTGRES_PASSWORD" pg_restore -h 127.0.0.1 -U postgres \
|
|
-d restore_check_v1_1 --no-owner --use-list=/tmp/restore.list --single-transaction --exit-on-error' \
|
|
< "$B/dev-before-v1-1.dump" 2> "$B/restore-check.log"; echo "pg_restore exit $?"
|
|
wc -c < "$B/restore-check.log" # 0: the log, rewritten by this run, holds no message
|
|
# exit 0 and an empty log, else STOP: the single transaction rolled back, the scratch database is
|
|
# empty; keep the log (a COPY error can quote a row: report only its ERROR and Command was lines),
|
|
# drop the database (last line), report
|
|
boxsql restore_check_v1_1 < "$B/counts.sql" > "$B/counts-restored.txt"
|
|
diff "$B/counts-before.txt" "$B/counts-restored.txt" && echo "rows identical" # the 28 lines
|
|
boxsql restore_check_v1_1 < "$B/privs.sql" > "$B/privs-restored.txt"
|
|
diff "$B/privs-before.txt" "$B/privs-restored.txt" && echo "privileges identical" # the 15 lines
|
|
docker exec faunosteo_dbsandbox_db sh -c 'PGPASSWORD="$POSTGRES_PASSWORD" dropdb -h 127.0.0.1 -U postgres restore_check_v1_1'
|
|
|
|
# 4. Apply without reset (M1's backfill runs inside the migration); no app session open on DEV meanwhile.
|
|
# `migration up` applies every pending migration of the checkout, in order.
|
|
pnpm supabase migration up --local; echo "migration up exit $?"
|
|
# "Applying migration 20261025100000_workspaces.sql...", then ..._clients.sql, then
|
|
# 20261026100000_animals_relations.sql; exit 0. Keep the whole output for the report.
|
|
```
|
|
|
|
Step 3b was rehearsed on the sandbox with fictitious data, never with DEV
|
|
data (2026-10-26, a 156 MB dump of the sandbox's `postgres` database at M3,
|
|
2 144 accounts, vault empty; D = 18 there, the sandbox has no
|
|
`supabase_functions` schema). Four variants, exit status read from the shell:
|
|
|
|
- `--no-owner` alone (the earlier procedure): exit 1, 26 errors, the 26
|
|
entries above (18 default privileges, 5 vault ACL, 2 ACL on supabase_admin
|
|
functions, the vault.secrets COPY); rows identical;
|
|
- `--no-acl`: exit 1 (the vault.secrets COPY), and every grant of the
|
|
application schemas lost: rejected, it removes what the check must prove;
|
|
- `--exit-on-error` on the whole list: exit 1 at the vault.secrets COPY,
|
|
before the policies: rejected;
|
|
- the filtered list with `--single-transaction --exit-on-error`: exit 0, empty
|
|
log, the 28 lines and the 15 privilege lines identical. Retained.
|
|
|
|
First run on the real DEV dump (2026-10-27): the 29 expected entries were
|
|
left out (D = 21), and the restore stopped with exit 1 on
|
|
`GRANT USAGE ON SCHEMA supabase_functions TO postgres`: role
|
|
`supabase_functions_admin` missing from the sandbox (DEV: LOGIN, CREATEROLE,
|
|
`search_path=supabase_functions`, owns the `supabase_functions` objects).
|
|
With the NOLOGIN role above and nothing else changed: exit 0, empty log, the
|
|
28 and 15 lines identical, schema `supabase_functions` restored with its 2
|
|
tables. The DEV role's login, CREATEROLE and password are not copied: no
|
|
privilege is exercised by a `--no-owner` replay.
|
|
|
|
A table whose stored ACL is the explicit owner-only one comes back with no
|
|
ACL, the same privileges (pg_dump leaves out an ACL equal to the default):
|
|
`privs.sql` therefore compares effective ACLs (`acldefault`). DEV differs
|
|
from the sandbox (read-only check, 2026-10-26): D = 21, the three more rows
|
|
being the defaults of its `supabase_functions` schema, so 29 entries left
|
|
out; its other own objects (`supabase_functions`, `_realtime`) grant
|
|
`postgres` no grant option. An entry that would still fail on the DEV dump
|
|
stops the single transaction: exit status not 0, nothing kept, STOP. This
|
|
backup is no full cluster backup. What it covers and what it does not:
|
|
|
|
- the dump holds the whole `postgres` database: auth (accounts, identities,
|
|
sessions, password hashes: the files are as sensitive as DEV itself),
|
|
profiles, roles, assets, storage metadata, audit, the migration history and
|
|
the grants. It does not hold the cluster roles (created by the image), the
|
|
`_supabase` database (services' internal metadata), nor the Storage file
|
|
bytes. It holds the vault rows, but a restore as `postgres` cannot reload
|
|
them (left out of step 3b; it only proves anything because DEV has none,
|
|
step 2, and says nothing of a restore with vault rows);
|
|
- the tar holds the Storage file bytes; restoring it is not rehearsed, and M1
|
|
to M3 touch neither Storage nor `assets`;
|
|
- a restore of this dump into the DEV stack is not rehearsed: step 3b proves
|
|
the dump is complete and readable, not that DEV can be rolled back with it
|
|
(see below).
|
|
|
|
After applying (read only, before anyone signs in):
|
|
|
|
```bash
|
|
pnpm supabase migration list --local # M1, M2, M3 now have their remote column; nothing pending
|
|
cat > "$B/post.sql" <<'SQL'
|
|
select 'workspaces ' || count(*) from public.workspaces;
|
|
select 'members ' || count(*) from public.workspace_members;
|
|
select 'profiles without exactly one personal workspace ' || count(*) from public.profiles p
|
|
where (select count(*) from public.workspaces w where w.personal_user_id = p.id) <> 1;
|
|
select 'personal workspaces without their sole holder ' || count(*) from public.workspaces w
|
|
where w.kind = 'personal'
|
|
and ((select count(*) from public.workspace_members m where m.workspace_id = w.id) <> 1
|
|
or not exists (select 1 from public.workspace_members m
|
|
where m.workspace_id = w.id and m.user_id = w.personal_user_id and m.member_role = 'holder'));
|
|
select 'duplicate memberships ' || count(*) from (
|
|
select workspace_id, user_id from public.workspace_members group by 1, 2 having count(*) > 1) d;
|
|
select 'active species ' || count(*) from public.species where active;
|
|
select 'species ' || string_agg(id, ',' order by sort_order) from public.species;
|
|
select 'clients ' || count(*) from public.clients;
|
|
select 'animals ' || count(*) from public.animals;
|
|
select 'relations ' || count(*) from public.animal_client_relations;
|
|
select 'rls off on V1-1 tables ' || count(*) from pg_class c join pg_namespace n on n.oid = c.relnamespace
|
|
where n.nspname = 'public' and not c.relrowsecurity
|
|
and c.relname in ('workspaces', 'workspace_members', 'clients', 'animals', 'animal_client_relations');
|
|
select 'anon, service_role or PUBLIC grants on V1-1 tables ' || count(*) from information_schema.role_table_grants
|
|
where table_schema = 'public' and grantee in ('anon', 'service_role', 'PUBLIC')
|
|
and table_name in ('workspaces', 'workspace_members', 'clients', 'animals', 'animal_client_relations');
|
|
select 'anon EXECUTE on V1-1 RPCs ' || count(*) from information_schema.role_routine_grants
|
|
where routine_schema = 'public' and grantee in ('anon', 'PUBLIC')
|
|
and routine_name in ('create_client', 'update_client', 'set_client_archived', 'search_clients',
|
|
'create_animal', 'update_animal', 'set_animal_archived', 'search_animals', 'list_client_animals',
|
|
'client_archive_preview', 'create_animal_client_relation', 'end_animal_client_relation',
|
|
'update_animal_client_relation', 'void_animal_client_relation');
|
|
select 'unaccent ' || count(*) from pg_extension where extname = 'unaccent';
|
|
select 'pgtap ' || count(*) from pg_extension where extname = 'pgtap';
|
|
SQL
|
|
devsql postgres < "$B/post.sql"
|
|
# workspaces N, members N, then 0, 0, 0; active species 10;
|
|
# species equine,asinine,canine,feline,bovine,caprine,ovine,porcine,leporine,other;
|
|
# clients 0, animals 0, relations 0; then 0, 0, 0; unaccent 1; pgtap 0
|
|
devsql postgres < "$B/counts.sql" > "$B/counts-after.txt"; diff "$B/counts-before.txt" "$B/counts-after.txt"
|
|
# only these lines differ (2026-10-26 values): species 1 → 10; migrations + the three V1-1 versions;
|
|
# tables private 1 → 2, public 7 → 12; functions private 41 → 54, public 26 → 40; policies 15 → 20;
|
|
# rls tables public 7 → 12. Every count and md5 of the existing rows is unchanged.
|
|
pnpm db:types:check # no diff
|
|
pnpm db:test; echo "db:test exit $?" # pgTAP 001-014, 921 assertions, exit 0 (see its effects and the 2026-10-27 run below)
|
|
echo "select 'pgtap ' || count(*) from pg_extension where extname = 'pgtap';" | devsql postgres
|
|
# 0, as before the run; 1 = left installed (interrupted run): report it, do not drop it by hand
|
|
devsql postgres < "$B/counts.sql" | diff "$B/counts-after.txt" - # no diff: the tests left no row
|
|
```
|
|
|
|
Effects of the test commands on DEV, read from the installed CLI 2.120.0 and
|
|
the scripts:
|
|
|
|
- `pnpm db:test` (`supabase test db`): runs pg_prove as `postgres`; every file
|
|
of `supabase/tests` is one `begin … rollback`, so no row stays. It creates
|
|
the `pgtap` extension before the run and drops it after when it was absent
|
|
(DEV: absent), a transient schema change; an interrupted run can leave
|
|
`pgtap` installed (`post.sql` shows it). Run it before the browser
|
|
acceptance, with no app session open and the worker stopped (010 leases
|
|
the queue rows it finds until its rollback). The files share the stack with the
|
|
data it already holds: 007 and 010 count only their own keys, and 010
|
|
first leases the base's own queued work (due purge entries, avatar jobs,
|
|
uploads begun more than 24 h ago) inside its transaction. Assumptions that
|
|
stay: the avatars bucket far below its 10 GiB ceiling; fewer than about 90
|
|
quarantine leftovers of the base (the sweep returns at most 100, oldest
|
|
first); the official equine sheet still at its seeded single draft (007
|
|
checks the seed as delivered).
|
|
- First run on DEV after M1 to M3 (2026-10-27, suites at `6c36e2f`): exit 1,
|
|
919 assertions planned, 915 run, 914 passed. 007 stopped at its storage
|
|
insert (the drawing was already uploaded and confirmed on DEV: duplicate
|
|
key, 4 assertions never run); 010 failed « nothing is due before its grace
|
|
elapsed » (13 due purge entries of DEV counted). 012 to 014 (V1-1) passed;
|
|
data, privileges and `pgtap` (absent) identical before and after. Both
|
|
files were fixed on the branch (007: 20 assertions, 010: 141) and validated
|
|
on the sandbox, clean and with those conditions reproduced (scenarios and
|
|
mutations in the journal of 2026-10-27). DEV is not validated by `db:test`
|
|
until a run there passes.
|
|
- `pnpm e2e:avatar`: not part of this procedure. Since 2026-11-02 it runs on
|
|
the sandbox only (owner decision; the DEV script `scripts/avatar-e2e.sh` is
|
|
removed). Its DEV run, as recorded on 2026-10-26, wrote on DEV beyond its own fixture: it creates a temporary confirmed
|
|
account `e2e-avatar-<time>@faunosteo.local` (and, after M1, its workspace),
|
|
deletes it at the end (a failed run leaves it), signs in as
|
|
`bob@faunosteo.local` (sessions and `last_sign_in_at` of an existing
|
|
account), and its worker drains every due purge-queue row and quarantine
|
|
leftover of the stack (13 rows on 2026-10-26). M1 to M3 touch neither
|
|
Storage nor `assets`; the avatar stays covered by the browser acceptance
|
|
below.
|
|
|
|
Stop and report instead of continuing when: `migration list` shows another
|
|
pending migration or a remote-only one; `db:types:check` before step 4 shows a
|
|
removed line or another name than the 24 of step 1; the dump check misses a
|
|
line; `vault.secrets` is not empty; the excluded entries differ from D + 8;
|
|
the restore check exits other than 0, logs anything or shows a differing
|
|
line; `migration up` applies anything else than the three V1-1 migrations
|
|
or fails; any check after it differs from the expectation.
|
|
|
|
When something fails:
|
|
|
|
- backup or restore check fails: nothing was applied; fix the cause (space,
|
|
container, sandbox) and start again from step 2. Never apply without a
|
|
restore check that passed;
|
|
- history diverges (another pending version, a remote-only version, an object
|
|
present without its history): stop; no `migration repair`, no hand-made
|
|
insert into the history. The divergence is analysed first, then resolved by
|
|
its own owner-validated procedure;
|
|
- `migration up` fails: run nothing else. Read its message, then
|
|
`pnpm supabase migration list --local` (which of the three versions are
|
|
recorded) and the object query below (which objects exist; before M1: 0
|
|
everywhere and species 1; after M3: tables 6, types 5, functions 27,
|
|
trigger 1, constraint 1, unaccent 1, species 10).
|
|
The CLI applies the files one after the other and stops at the first
|
|
failure: M1 can be recorded with M2 not. Whether a failed file leaves part
|
|
of its statements behind was not verified on this CLI version: check the
|
|
objects, not only the history. Do not assume a full rollback, and do not
|
|
run `migration up` again before the state is understood;
|
|
- profiles not caught up (a profile without exactly one workspace): stop;
|
|
`private.backfill_personal_workspaces()` is idempotent, but running it by
|
|
hand is a write on DEV, decided after the cause is known;
|
|
- an RLS policy refuses a legitimate access in the browser: note the account
|
|
(fictitious), the screen and the time; read `pnpm db:test` and `post.sql`;
|
|
the fix is a new migration on the branch, tested on the sandbox first,
|
|
never a grant typed on DEV;
|
|
- the generated types differ: compare with the committed file; a difference
|
|
means the applied schema is not the branch's: stop;
|
|
- the browser acceptance finds a regression: record it per line, device and
|
|
language; the fix goes through the branch, its gates and a fresh verifier.
|
|
|
|
Object query (read only), for a failed or doubtful step 4:
|
|
|
|
```bash
|
|
cat > "$B/objects.sql" <<'SQL'
|
|
select 'tables ' || count(*) from pg_class c join pg_namespace n on n.oid = c.relnamespace
|
|
where (n.nspname, c.relname) in (('public', 'workspaces'), ('public', 'workspace_members'), ('public', 'clients'),
|
|
('public', 'animals'), ('public', 'animal_client_relations'), ('private', 'workspace_write_budgets'));
|
|
select 'types ' || count(*) from pg_type t join pg_namespace n on n.oid = t.typnamespace where n.nspname = 'public'
|
|
and t.typname in ('workspace_kind', 'workspace_member_role', 'client_kind', 'animal_sex', 'animal_relation_role');
|
|
select 'functions ' || count(*) from pg_proc p join pg_namespace n on n.oid = p.pronamespace
|
|
where (n.nspname = 'private' and p.proname in ('backfill_personal_workspaces', 'caller_workspace_ids',
|
|
'check_personal_workspace', 'create_personal_workspace', 'ensure_personal_workspace', 'writable_workspace_ids',
|
|
'consume_write_budget', 'fold_text', 'is_clean_text', 'lock_relation_for_write', 'parse_birth_year',
|
|
'parse_record_date', 'utc_tomorrow'))
|
|
or (n.nspname = 'public' and p.proname in ('create_client', 'update_client', 'set_client_archived', 'search_clients',
|
|
'client_archive_preview', 'create_animal', 'update_animal', 'set_animal_archived', 'search_animals',
|
|
'list_client_animals', 'create_animal_client_relation', 'end_animal_client_relation',
|
|
'update_animal_client_relation', 'void_animal_client_relation'));
|
|
select 'trigger ' || count(*) from pg_trigger where tgname = 'profiles_create_personal_workspace';
|
|
select 'constraint ' || count(*) from pg_constraint where conname = 'audit_logs_correlation_id_format';
|
|
select 'unaccent ' || count(*) from pg_extension where extname = 'unaccent';
|
|
select 'species ' || count(*) from public.species;
|
|
SQL
|
|
devsql postgres < "$B/objects.sql"
|
|
```
|
|
|
|
Going back is a decision of its own, never a reflex. No down migration
|
|
exists. The preferred way is forward: a new migration that corrects. A
|
|
restore of the step 3 dump into DEV is not rehearsed: it would replace the
|
|
`postgres` database the services use, and it loses every write made after
|
|
the backup (accounts created or confirmed, password and profile changes,
|
|
sessions, which would end on every device, audit rows, clients, animals and
|
|
relations created during the acceptance, assets and purge-queue changes);
|
|
a Storage file uploaded after the backup would remain without its row, and a
|
|
file deleted after it would come back as a row without bytes. Rehearse it on
|
|
the sandbox before relying on it.
|
|
|
|
### 3.4 Clients (V1-1.2, migration M2)
|
|
|
|
Contract: `V1-1_OWNERS_ANIMALS.md` §5.1, §8.2, §9, §10. `public.clients`
|
|
holds the clients of a workspace; the browser reads them through RLS (members
|
|
of the workspace, active profile, confirmed email) and writes them only
|
|
through `create_client`, `update_client` and `set_client_archived` (security
|
|
definer); `search_clients` (invoker, POST) searches one workspace. Refusals
|
|
are closed codes: 42501 `not_found` (also for a foreign id), P0001
|
|
`invalid_input`, `archived`, `conflict`, `request_conflict`, `limit_reached`,
|
|
`rate_limited`. Writes are counted per workspace in
|
|
`private.workspace_write_budgets` (UTC hour and day). M2 creates the
|
|
extension `unaccent` in `extensions` (available on the CLI image and in
|
|
PGlite 0.3.16, loaded by the harness). An account whose workspace holds a
|
|
client can no longer be deleted (D9a); in DEV, delete its clients first from
|
|
a privileged SQL session, or reset.
|
|
|
|
### 3.5 Animals and relations (V1-1.4, migration M3)
|
|
|
|
Contract: `V1-1_OWNERS_ANIMALS.md` §5.2 to §5.4, §8.3, §9, §10. M3 adds the
|
|
species rows (`asinine` to `leporine` and `other`; `equine` unchanged; a row
|
|
never means a sheet exists), `public.animals` and
|
|
`public.animal_client_relations`. The browser reads them through RLS and
|
|
writes them only through `create_animal` (with an optional first relation),
|
|
`update_animal`, `set_animal_archived`, `create_animal_client_relation`,
|
|
`end_animal_client_relation`, `update_animal_client_relation` and
|
|
`void_animal_client_relation` (security definer); `search_animals`,
|
|
`list_client_animals` and `client_archive_preview` read (invoker, POST), and
|
|
`search_clients` now returns `current_animal_count`. A relation joins an
|
|
animal and a client of one workspace (composite keys); it is current, ended
|
|
(`ended_at`, `ended_on` when known) or voided (entered by mistake, kept,
|
|
frozen, shown nowhere); it is never deleted. New refusal codes:
|
|
`duplicate_relation`, `already_ended`. Writes share the workspace budget of
|
|
M2. An account whose workspace holds an animal can no longer be deleted
|
|
either (D9a): in DEV, delete its relations, animals and clients first from a
|
|
privileged SQL session, in that order.
|
|
|
|
The browser reaches these tables through `packages/supabase/src/{workspaces,clients,species,records}.ts`
|
|
since V1-1.5 (contract §11.7) and `{animals,relations}.ts` since V1-1.6
|
|
(§11.8). The client and animal screens run against DEV only once the owner has
|
|
applied M1 to M3 with §3.3; until then they are developed and tested on the
|
|
isolated stack, and no DEV acceptance is recorded.
|
|
|
|
#### Browser acceptance on DEV (owner, after §3.3)
|
|
|
|
A checkpoint of its own: the Playwright suites never run against DEV, so this
|
|
is the owner's check, on `pnpm dev` (DEV origin), from a computer and a phone
|
|
of the private network, with fictitious accounts only: `alice@faunosteo.local`
|
|
(account A) and `bob@faunosteo.local` (account B), password of §3.2. No real
|
|
profile is changed for a test and nothing is created outside these two
|
|
workspaces; records carry names that say they are tests (« Recette Client
|
|
1 »). Each journey is done in French and in English, on a computer and at
|
|
375 px (a phone, or the browser's responsive mode); the main actions once more
|
|
with the keyboard only (Tab, Enter, Escape in the dialogs). Results are
|
|
recorded per line, device and language; a line not done is reported as not
|
|
done.
|
|
|
|
Sign-in and workspace:
|
|
|
|
1. A signs in; the dashboard opens; Clients and Animaux open from the sidebar
|
|
(computer) and from the drawer (phone);
|
|
2. A signs out and signs in again: the records of the lines below are still
|
|
there, after a reload too;
|
|
3. signed out, `/app/clients` and a copied record URL lead to the sign-in
|
|
page, then back to that page after sign-in.
|
|
|
|
Clients:
|
|
|
|
4. create a client; find it by name, by city, then by phone digits; open its
|
|
page;
|
|
5. edit its details (address, phone), save, reload: the values are kept;
|
|
6. archive it (the confirmation lists its sole-owned animals), find it under
|
|
« Afficher les archivés », restore it.
|
|
|
|
Animals and relations:
|
|
|
|
7. create an animal with no client: « Propriétaire non renseigné »;
|
|
8. from a client's page, « Ajouter un animal »: owner role chosen, back on the
|
|
client page with the animal;
|
|
9. link an existing animal to a second client (co-ownership), then a
|
|
structure as keeper and a third client as contact: each role shows on both
|
|
pages; linking a client already linked in the same role is refused (no
|
|
second current relation);
|
|
10. end a relation with a date: « Ancien propriétaire » on that client's page,
|
|
the relation under the former ones on the animal's page;
|
|
11. correct the dates of a relation;
|
|
12. remove a relation entered by mistake (« Erreur de saisie »): gone from
|
|
both pages;
|
|
13. « Autre » with « Furet »; a birth year alone, then a full date;
|
|
14. archive the animal, find it with the archived toggle, restore it;
|
|
15. from the phone: find an animal, open it, the owner's `tel:` link opens the
|
|
dialer;
|
|
16. Mon compte: A's photo still shows (no V1-1 change; checks the Storage path
|
|
after the migrations).
|
|
|
|
Isolation:
|
|
|
|
17. A signs out and B signs in on the same browser: no client and no animal
|
|
of A in the lists or the searches, not even for an instant, nor with the
|
|
back button;
|
|
18. B opens a copied URL of A's client and of A's animal, then the same URL
|
|
with one character changed: the same « introuvable » screen each time;
|
|
19. optional: `owner@faunosteo.local` (platform owner) sees none of A's
|
|
records either (no staff access in V1-1).
|
|
|
|
Not covered: Safari iOS and Firefox unless such a device is used (the browser
|
|
suites are Chromium only until V1-5); the refusal of an unconfirmed account
|
|
(only with a fictitious unconfirmed account whose password is known, which
|
|
means a sign-up on DEV, the owner's choice); the back and forward guard
|
|
(accepted debt). This acceptance is no production security certification:
|
|
RR-1 to RR-10 stay open.
|
|
|
|
Run of 2026-10-27 (the owner's results; the detailed records stay outside
|
|
Git): the 19 lines validated in French one by one; English validated as a
|
|
whole, four lines at 375 px recorded one by one; isolation A, B and owner
|
|
checked. Two anomalies:
|
|
|
|
- RESP-01, sideways scroll at 375 px: the global scroll first seen came from
|
|
the browser zoom (the owner's measures without zoom: `/app` and
|
|
`/app/clients` at 375 and 320 px, no overflow; no other page is declared
|
|
conform by them). A real defect found apart, on the isolated stack: an
|
|
account e-mail longer than the card (from about 31 characters at 320 px)
|
|
widened the dashboard cards past a phone screen (implicit grid track); fixed, covered by
|
|
`e2e/shell/responsive.spec.ts`. Owner counter-check on the fixed code: 375 px
|
|
FR and EN, card readability, client list at 375 px, dashboard at 320 px,
|
|
console measure at 320 px (0 px overflow): six PASS. Closed, fixed.
|
|
- AVATAR-01, a new photo ends on « Le traitement prend plus de temps que
|
|
prévu »: expected on DEV without a running worker (STORAGE.md §4 step 4);
|
|
the pipeline completes once a worker runs (reproduced on the isolated
|
|
stack, never on DEV). Owner decision: class A, an operating constraint
|
|
that does not block V1-1; the DEV worker stays stopped on purpose, Alice's
|
|
waiting upload is kept as is and may be processed whenever a worker is
|
|
started later. No V1-1 regression shown. The reload gap found on the way
|
|
(STORAGE.md §4 step 4) is ticket AVATAR-02, out of V1-1.
|
|
|
|
## 4. Writing a migration
|
|
|
|
1. `pnpm supabase migration new <name>` creates `supabase/migrations/<timestamp>_<name>.sql`.
|
|
2. A migration that adds a table adds, in the same file: RLS enable, explicit
|
|
`revoke all ... from public, anon, authenticated` (and `service_role` for
|
|
V1-1 record tables, contract N7) then the exact grants,
|
|
separate SELECT / INSERT / UPDATE / DELETE policies, indexes, comments.
|
|
3. Add or extend a pgTAP file under `supabase/tests/`: the allowed path and
|
|
every denied path (other user, anon, direct write bypassing an RPC). Scope
|
|
counts to the fixture ids: on the real stack the seed accounts exist, in
|
|
PGlite they do not.
|
|
4. `pnpm --filter @faunosteo/supabase test:run` (PGlite, seconds), then
|
|
`pnpm db:reset && pnpm db:test` on the real stack.
|
|
5. `pnpm db:types` and commit `packages/supabase/src/database.types.ts`.
|
|
6. Never edit an applied migration. Corrections are new migrations.
|
|
|
|
Rules enforced by the schema-types test for every future `public` table:
|
|
RLS enabled, no `using (true)` / `with check (true)` policy for `anon` or
|
|
`authenticated`.
|
|
|
|
## 5. Tests
|
|
|
|
Two runners, one SQL source of truth (`supabase/tests/*.sql`, pgTAP):
|
|
|
|
| Command | Engine | Needs Docker | What runs |
|
|
| -------------------------------------------- | ------------------------------------------------ | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
|
|
| `pnpm --filter @faunosteo/supabase test:run` | PGlite (PostgreSQL 17 in WASM) | no | shim of the Supabase base schema (auth + storage tables) + all migrations + `seed/**` + every pgTAP file; schema ↔ types check; client factory; publish gate |
|
|
| `pnpm db:test` | `supabase test db` (pg_prove in the local stack) | yes | the same pgTAP files against the real Supabase image |
|
|
|
|
`pnpm test:run` at the root runs the PGlite suite together with the app
|
|
suite. The shim (`packages/supabase/tests/db/supabase-shim.sql`) recreates
|
|
the API roles, `auth.users`, `auth.uid()` and the permissive default
|
|
privileges of Supabase so that a forgotten `revoke` fails a test.
|
|
|
|
Two parity traps met so far: counts must be scoped to fixture ids (the real
|
|
stack carries `seed.sql` rows), and `order by` on text must pin
|
|
`collate "C"` (the real image sorts with a locale collation, PGlite with C).
|
|
|
|
### 5.1 Isolated stack (`pnpm db:sandbox`)
|
|
|
|
A throwaway Supabase stack for migration steps, so the DEV stack is never
|
|
reset or migrated by a test (owner rule since V1-1.1). `scripts/db-sandbox.sh`
|
|
starts the images the CLI stack uses (PostgreSQL 17.11.0.004, GoTrue v2.197.0,
|
|
Storage v1.79.36, PostgREST v16.4, Mailpit v1.31.3, pinned in the script) on
|
|
the network `faunosteo_dbsandbox_net`, containers `faunosteo_dbsandbox_*`,
|
|
ports on 127.0.0.1 only and distinct from DEV (db 57432, auth 57999, rest
|
|
57300, storage 57500, mail 57025). Nothing is mounted and the images declare
|
|
no volume, so `down` removes the containers and the network by their literal
|
|
names and can never reach a `supabase_*_faunosteo` volume. The password, JWT
|
|
secret and keys are generated at each `up` into a 600 file under
|
|
`~/.cache/faunosteo-db-sandbox` (`FAUNOSTEO_SANDBOX_DIR`, refused inside the
|
|
repository), with 600 env files that hand them to the containers; deleted by
|
|
`down`, never printed, never on a command line (the CLI and psql read
|
|
`PGPASSWORD` from the environment). An incomplete secrets file stops the
|
|
script before any CLI call, and a wrong `up` mode or version (fourteen
|
|
digits) is refused before any container or secret exists. Works with rootless
|
|
Docker.
|
|
|
|
| Command | What it does |
|
|
|---|---|
|
|
| `pnpm db:sandbox up fresh` | every migration through `supabase migration up --db-url`, then the seeds of `config.toml` (as `db reset`) |
|
|
| `pnpm db:sandbox up upgrade <version> [<then>]` | the migrations before `<version>`, the seeds, two GoTrue accounts (confirmed, unconfirmed), then `migration up`: the path a DEV database takes; with `<then>`, the migrations before `<then>` first (the workspaces count is printed), then the rest |
|
|
| `pnpm db:sandbox test-db` | `supabase test db --db-url`: the pgTAP files on the real image (rows unchanged afterwards) |
|
|
| `pnpm db:sandbox types [--check]` | `supabase gen types --db-url ... --schema public` through Prettier, written or diffed with the committed file |
|
|
| `pnpm db:sandbox test` | `packages/supabase/tests/sandbox/*.sandbox.ts`: real GoTrue and PostgREST sessions, concurrent SQL sessions |
|
|
| `pnpm db:sandbox e2e-shell` (= `pnpm e2e:shell`) | the authenticated `/app` shell in Chromium (`apps/app/e2e/shell`, `docs/development/BROWSER_TESTS.md`): a loopback dev server whose Supabase calls are replayed on the sandbox, fictitious accounts per test |
|
|
| `pnpm db:sandbox avatar-e2e` (= `pnpm e2e:avatar`) | the avatar flow on the sandbox alone: an nginx gateway (`nginx:1.31-alpine`; `/auth/v1/`, `/rest/v1/`, `/storage/v1/` to the sandbox containers, `return 404` elsewhere) published on `127.0.0.1:57800` only; the real worker `faunosteo_worker:dev run` (avatars and sweep, 5 s sweep, no quarantine grace, 512 MiB, read-only, no capability, `nonroot`) on the sandbox network, its service key in a 600 env file removed once started; then `packages/supabase/tests/e2e/avatar-flow.e2e.ts` through that port. The suite accepts only `http://127.0.0.1:57800`, the container `faunosteo_dbsandbox_db` and keys whose JWT payload carries `iss: db-sandbox`, else it throws before any request. The issuer check only routes the run to the sandbox: it is not a signature check and not an authentication. The isolation rests on the fixed gateway URL in the worker's env file, the worker's `max_redirects(0)` and `proxy(None)` (`crates/worker/src/http.rs`), sandbox-signed keys and upstreams reached by container name on the sandbox network. The suite's supabase-js clients refuse every HTTP redirect (`global.fetch` = `tests/e2e/noRedirectFetch.ts`, `redirect: 'error'`; regression test `tests/no-redirect-fetch.test.ts`) |
|
|
| `pnpm db:sandbox sql` / `down` | psql as postgres on stdin / remove the stack |
|
|
|
|
Every CLI call passes `--db-url` with `sslmode=disable` (the sandbox has no
|
|
TLS); none can fall back to the local DEV database. Differences from the CLI
|
|
stack: no Kong (GoTrue and PostgREST are called directly), no Studio, no
|
|
realtime or edge runtime, and the database init keeps only the role
|
|
passwords the services use.
|
|
|
|
How a test impersonates a user (same technique in both runners):
|
|
|
|
```sql
|
|
set local role authenticated;
|
|
select set_config('request.jwt.claims', '{"sub":"<user uuid>","role":"authenticated"}', true);
|
|
```
|
|
|
|
## 6. Generated types
|
|
|
|
```bash
|
|
pnpm db:types # regenerate packages/supabase/src/database.types.ts (local stack running)
|
|
pnpm db:types:check # CI: fail on any diff
|
|
```
|
|
|
|
The CLI emits unformatted TypeScript; both scripts pipe it through Prettier
|
|
(repo config) so the committed file is stable byte for byte. The schema-types
|
|
test checks tables, columns, enum labels and RPC argument names against the
|
|
migrated schema, so a stale file fails before CI.
|
|
|
|
## 7. First platform owner in a real environment
|
|
|
|
The RPCs refuse self-grants and need an existing owner or admin. The first
|
|
owner is created once, from a privileged SQL session (service connection,
|
|
`psql` on the host), never from the browser:
|
|
|
|
```sql
|
|
select private.assign_platform_role(null, '<auth.users.id of the owner>', 'owner');
|
|
```
|
|
|
|
The call is audited with a null actor. Afterwards every role change goes
|
|
through `grant_platform_role` / `revoke_platform_role`.
|
|
|
|
## 8. Manual validation (Definition of Done: "an authenticated request hits a protected table safely")
|
|
|
|
```bash
|
|
URL=http://127.0.0.1:54321
|
|
ANON=<anon key printed by pnpm db:start>
|
|
|
|
token() { # $1 = email
|
|
curl -s -X POST "$URL/auth/v1/token?grant_type=password" \
|
|
-H "apikey: $ANON" -H "Content-Type: application/json" \
|
|
-d "{\"email\":\"$1\",\"password\":\"LocalDev-only-password-1\"}" | jq -r .access_token
|
|
}
|
|
ALICE=$(token alice@faunosteo.local); OWNER=$(token owner@faunosteo.local)
|
|
ALICE_ID=10000000-0000-0000-0000-00000000000a
|
|
|
|
# 1. alice reads exactly one profile (hers)
|
|
curl -s "$URL/rest/v1/profiles?select=id,display_name" -H "apikey: $ANON" -H "Authorization: Bearer $ALICE"
|
|
|
|
# 2. anon reads nothing (permission denied)
|
|
curl -s "$URL/rest/v1/profiles" -H "apikey: $ANON"
|
|
|
|
# 3. alice cannot deactivate herself (column privilege) nor self-grant admin
|
|
curl -s -X PATCH "$URL/rest/v1/profiles?id=eq.$ALICE_ID" -H "apikey: $ANON" \
|
|
-H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -d '{"active":false}'
|
|
curl -s -X POST "$URL/rest/v1/rpc/grant_platform_role" -H "apikey: $ANON" \
|
|
-H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" \
|
|
-d "{\"target_user_id\":\"$ALICE_ID\",\"role\":\"admin\"}"
|
|
|
|
# 4. the owner grants moderator to alice; the grant is audited
|
|
curl -s -X POST "$URL/rest/v1/rpc/grant_platform_role" -H "apikey: $ANON" \
|
|
-H "Authorization: Bearer $OWNER" -H "Content-Type: application/json" \
|
|
-d "{\"target_user_id\":\"$ALICE_ID\",\"role\":\"moderator\"}"
|
|
psql postgresql://postgres:postgres@127.0.0.1:54322/postgres \
|
|
-c "select actor_user_id, action, target_user_id, metadata from audit.audit_logs order by occurred_at"
|
|
```
|
|
|
|
Expected: 1 → one row; 2 → `42501`; 3 → both `42501`; 4 → a uuid, then two
|
|
audit rows (bootstrap with null actor, grant by the owner).
|
|
|
|
## 9. Wiring the app
|
|
|
|
Development: `pnpm dev` (`scripts/dev.sh`) sets `VITE_SUPABASE_URL` to the DEV
|
|
origin (`http://<FAUNOSTEO_DEV_HOST>:54321`, the LAN address of the machine:
|
|
`ENVIRONMENTS.md` §2) and the anon key from `supabase status`; the app is
|
|
served on `http://<host>:5173` for every device of the private network.
|
|
`pnpm dev:offline` runs the local tool without backend. Docker deployments use
|
|
`.env`:
|
|
|
|
```bash
|
|
APP_SUPABASE_URL=<Supabase origin reachable by the browser>
|
|
APP_SUPABASE_ANON_KEY=<anon key> # public by design
|
|
```
|
|
|
|
Both empty keeps the app in its local-only mode:
|
|
`getSupabaseClient()` returns null, the auth screens show an "unavailable"
|
|
notice and `/app` redirects to `/login`. `APP_AUTH_PROVIDERS` /
|
|
`VITE_AUTH_PROVIDERS` (`google,apple`) lists the OAuth buttons to show.
|
|
Sign-in, sign-up, email confirmation, password recovery, the `/app` shell
|
|
and Mon compte are documented in `IDENTITY.md` (routes, session handling,
|
|
provider setup, MFA gap, manual validation).
|
|
|
|
## 10. Self-hosted parity
|
|
|
|
The local stack runs the same images as a self-hosted deployment
|
|
(`supabase/docker` compose), so migrations, policies and tests transfer
|
|
unchanged. The self-hosted runbook (VPS sizing, secrets, backups, TLS) is a
|
|
separate deliverable once the hosting question (OPEN_QUESTIONS Q2) is
|
|
settled; it reuses `pnpm supabase db push --db-url <...>` for migrations.
|