Files
faunosteo/.claude/audits/2026-10-28-v1-2-1-corrections-evidence.md
bchanot 532c61e087 docs(docs): V1-2.1 measured equine size, e2e:sheet-assets citers, logs
The M5 row gives the measured content::text size; §15 and §16.4 point the
e2e:sheet-assets rerun to the owner; the walk log carries its script and
the HTTP timing lines are committed.
2026-10-28 21:15:08 +01:00

162 lines
9.8 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# V1-2.1 corrections after the independent audit: evidence
Owner brief 2026-10-28 « V1-2.1 : corrections après audit indépendant » (verdict CHANGES
REQUIRED on 3914eaa). Every run below used the isolated `pnpm db:sandbox` stack only
(containers `faunosteo_dbsandbox_*`, ports 57xxx); DEV was not read or written.
## 1. Defects F1-F19 on the old schema (before M4)
Script `2026-10-28-v1-2-1-repro-old-schema.sql` (one rolled-back transaction) on a sandbox of
the 12 base migrations of develop, run during the first V1-2.1 pass. Output:
```
F1 plain account created a personal PUBLIC sheet for species other
F5 an account with an UNCONFIRMED email created a sheet
F6 set_sheet_visibility changes visibility with no gate
F15 another plain account reads the public personal sheet: 1 row
F9 editor rewrote draft content / renamed the sheet by direct UPDATE
F4 version published with a drawing never confirmed
F2 the draft of an ARCHIVED sheet was published; create_sheet_draft opened a draft on it
F8 after archiving, another account reads 0 published versions
F13 a DEPRECATED version was deleted physically
F3 copied asset rows not confirmed
F14 a platform admin at aal1 published an official sheet
F19 grant_platform_role(owner) accepted at aal1; the new owner holds 0 verified factors
F7 upload confirmed with no object in Storage
F12 service_role published a draft by direct UPDATE
F17 service_role inserted an owner row directly into profile_roles
F16 sha256 of an asset used by a published version changed
```
Re-run on 2026-10-28 for the final decisions pass, raw psql output committed as
`2026-10-28-v1-2-1-repro-old-schema.out` (sandbox `up fresh` from a copy holding only the 12
migrations before M4; the same NOTICE lines, `repro exit 0`, the transaction rolled back).
F18 (last-owner race) was reproduced with two sessions; its test is
`packages/supabase/tests/sandbox/sheets.sandbox.ts`.
## 2. service_role effective table privileges (§16.2 prerequisite)
Measured on the M7 rehearsal stack (DEV path), before and after M4+M5:
- before: ALL (DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE) on
`species`, `anatomical_sheets`, `anatomical_sheet_versions`, `anatomical_sheet_assets`,
`profiles`, `profile_roles`;
- after: none on those six tables (pgTAP 005 and 018 assert it, with the EXECUTE census).
## 3. New tests red on 3914eaa, green on the corrected HEAD
Stack built from the base migrations plus the M4/M5 of 3914eaa, then the new and changed
files of this pass:
| File | On 3914eaa | On the corrected HEAD |
|---|---|---|
| 006 | 2 not ok: another editor uploads a pending key it did not register (L5) | 16/16 |
| 016 | 1 not ok: a draft from a deprecated version takes it as base (M2) | 50/50 |
| 017 | M1 approval on an archived sheet succeeds; M2 base from a deprecated version; the file aborts there | 85/85 (111/111 after the final decisions pass, §4) |
| 018 | 93 not ok of 337: capability holders at aal1 get `rights.capability` instead of `rights.mfa` (§6.7); `delete_sheet_asset` answers a raw code | 337/337 |
| 019 | 38/38 (lost coverage of behaviour that was already correct) | 38/38 |
| sheets-concurrency | 5 failed of 15: publish against withdraw (no version lock), two deprecations (no sheet lock), archive against approve (no sheet lock) | 15/15 |
## 4. Real-stack results on the corrected HEAD
Final decisions pass (owner brief « V1-2.1 : décisions finales du propriétaire »), code at
`2379d28` (M6 option A, text size measure with the bounded, linear number walk, pgTAP
015/017/020; only `.claude/` differs at the final HEAD). Earlier figures (1 333 pgTAP) were the `2af61e7`
state; intermediate passes at `7eca441` (1 352) and `5620399` (1 807) preceded the review findings below.
| Run | Fresh stack | Upgraded stack (M3 + Git fixture, then M4+M5) |
|---|---|---|
| pgTAP (`test-db`, 18 files) | 1 808/1 808 | 1 808/1 808 |
| sandbox suite, run 1 | 87/87 | 87/87 |
| sandbox suite, run 2 (same stack) | 87/87 | 87/87 |
| of which two-session concurrency (`sheets-concurrency`) | 16/16 each run | 16/16 each run |
| pgTAP again after both suite runs | 1 808/1 808 | 1 808/1 808 |
| `types --check` | types match | types match |
PGlite: 128/128 (packages/supabase: the 18 pgTAP files and the closed-code census).
New in this pass:
- 017 M6: owner self-grants `sheet_reviewer`; owner-reviewer acknowledges and approves a version
written by E, E publishes; owner refused on a round it authored (`review.separation`);
publication after an author's approval refused (`publish.approval.invalid`).
- 017 size: cap − 1 and cap saved and measured exactly; cap + 1 refused; a 2 MiB document of
3-byte characters saved; cap + 1 in 4-byte characters refused; a dense document within the cap
as text and over it as binary jsonb saved whole; creation over the cap refused; numbers that
JavaScript prints with an exponent stored and measured in plain form; a document under the cap
in compact JavaScript form but over it as measured refused; 9 000 numbers of 131 072 digits
refused with the closed code; 600 000 small numbers within the cap saved (the number walk is
linear); the table CHECK definition. Every refusal answers
`document.too_large` (23514), never a raw error.
- 017 `publish.species.other` refused alone.
- 020 (449): the AR-1 B matrix of §15 in full (6 caller classes × aal1/aal2 × grant/revoke × 4
roles × self/other, each refusal with « nothing written »), the regression chain on the real
owner and admin at aal1, and the rights RPCs (`grant_catalog_right` on itself and on another,
`revoke_catalog_right`, `lookup_catalog_account`, `list_catalog_grants`) refused to every
non-owner class at aal2 (`rights.owner`) and at aal1 (`rights.mfa`).
Number walk, linear (security gate on `7c4bf12`: `jsonb_path_query` row by row is quadratic on
PostgreSQL 17). Raw psql timings on the sandbox, committed as
`2026-10-28-v1-2-1-number-walk.out` (one transaction ending in `rollback`; script in its header line below): row by row 138 / 506 / 2 172 ms for 50k /
100k / 200k numbers; the shipped walk (one `jsonb_path_query_array` call) 69 / 135 / 267 / 421 ms
for 100k / 200k / 400k / 600k. Over
HTTP on both stacks, each run (lines extracted in `2026-10-28-v1-2-1-sandbox-timings.out`): 600 000 numbers (1 801 760 bytes of text) saved in 677-695 ms;
1 000 000 numbers answered `document.too_large` in 1 019-1 078 ms (budget 2 000 ms).
Number bomb on the real image (sandbox, re-run on `2379d28`): the shipped function answers
`document.too_large`; the same payload under the previous body (`octet_length(p_doc::text)`
alone, rolled back) fails with `out of memory` after a 1 GiB buffer.
## 5. Timing at the caps (§15, budget 2 000 ms for save, submit, publish)
Synthetic document: 64 views, 1 024 annotations, 8 192 finding nodes (depth 8), 1 089 review
groups, 4 drawings of 2 000 000 bytes. The labels are calibrated on the contract's text measure
alone: text 2 076 707 bytes (cap 2 097 152), binary jsonb 2 346 648 bytes, over the cap and
accepted, as the measure requires. HTTP wall-clock through PostgREST on the development machine,
code `2379d28`, milliseconds (min / median / max):
| Stack, run | save (7) | submit (5) | approve (3) | publish (3) |
|---|---|---|---|---|
| fresh, 1 | 127 / 137 / 155 | 4 / 5 / 6 | 190 / 285 / 292 | 195 / 288 / 303 |
| fresh, 2 | 130 / 142 / 155 | 4 / 7 / 13 | 196 / 284 / 297 | 205 / 302 / 306 |
| upgraded, 1 | 135 / 139 / 148 | 5 / 6 / 6 | 192 / 282 / 294 | 196 / 292 / 296 |
| upgraded, 2 | 127 / 137 / 151 | 5 / 5 / 7 | 199 / 286 / 295 | 220 / 292 / 299 |
The core's at-caps fixture is V1-2.2 work; the contract fixes the budgets at the V1-2.3
checkpoint and measures verification (120 s) there.
## 6. M7: upgrade rehearsal with a Git fixture of the drawing object
Accepted by the owner for V1-2.1 (brief « V1-2.1 : décisions finales du propriétaire »,
2026-10-28; contract §12.3, §17.3). What this rehearsal is, and is not:
- It uses a representative Git fixture, not a copy of the DEV object: the committed file
`supabase/seed/anatomical-sheets/equine/draft/assets/horse.svg` (132 997 bytes), whose sha256
`0ea5202f…08541` equals the sha256 referenced by the DEV metadata (the asset row
`7f767f74…` of the historical sheet, seeded from `equine_draft.sql`).
- Its bytes, its Storage metadata and its relations (asset row, historical sheet, version) are
compared before and after the migration.
- No file was read from the real Storage of DEV, and nothing on DEV was read or written in
V1-2.1. The current integrity of the live DEV object is therefore **not demonstrated**; it is
checked when the owner applies M4 to M6 on DEV (contract §16.11).
DEV path: `up upgrade 20261028100000` from a copy whose migrations stop at M3, the fixture
placed as the operator tool placed the drawing on DEV (service-key upload, pre-M4 confirmation
RPC), then M4+M5 by `supabase migration up`.
- `storage.objects` row (id, owner, metadata, version, timestamps), the asset row, the
historical sheet row and its version (content md5, updated_at): identical before and after.
- Changed, as M4/M5 intend: bucket limit 20 971 520 → 2 097 152; service_role privileges
ALL → none.
- Bytes through the Storage API before and after: identical to the Git file.
- After M4+M5, through the Storage API with real tokens: an editor at aal2 reads the 132 997
bytes (sha256 equal to the key); the same editor at aal1, a catalogue reader and an anonymous
caller are refused (the historical sheet has no published version).
The rehearsal is reproducible from the repository, re-run on `7eca441`, `5620399` and `2379d28` (final decisions pass)
with the same results, the only diff lines being the bucket limit and the service_role
privileges: `bash .claude/audits/2026-10-28-v1-2-1-m7/m7-rehearsal.sh [work dir]` (sandbox only; it applies
M4+M5 with `supabase migration up --db-url` on the sandbox port, a step `scripts/db-sandbox.sh`
has no subcommand for) and `m7-read.mjs` (the reads with real tokens).