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

9.8 KiB
Raw Permalink Blame History

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).