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.
9.8 KiB
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.otherrefused 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_righton 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 sha2560ea5202f…08541equals the sha256 referenced by the DEV metadata (the asset row7f767f74…of the historical sheet, seeded fromequine_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.objectsrow (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).