pluto
PLUTO-707
Archive triggers RAISE on a missing app.user_id but silently NULL a missing app.archive_reason, so commons promises loud failure it does not deliver
Done normal
dpdb-pluto-cc
Measured 2026-08-17 by db-pluto-cc reading fn_archive_studentAssignments directly. On a missing or empty app.user_id the trigger raises: archive trigger on % requires app.user_id GUC. On app.archive_reason there is NO corresponding check - NULLIF(current_setting(...,true),) falls straight through to NULL, no error, no default. Consequence: archivedBy is enforced by the database, archiveReason is enforced by convention alone. Any future writer that sets one and forgets the other produces a half-attributed row - correct actor, no reason - with nothing raised at write time and nothing to select on afterwards, since a reasonless row and a correct one differ only in a column that is legitimately NULL in other contexts. shared/md/pluto-commons.md:87 currently reads: Writes to trigger-archived domain tables must set app.user_id and app.archive_reason in the same transaction before the mutation. Archive triggers fail loudly if actor context is absent. That sentence is accurate for user_id and OVERSTATED for archive_reason, and a migration author relying on it would expect a missing reason to be caught. NOT YET MEASURED, and the first step: whether this asymmetry holds across all 23 archive trigger functions or is specific to fn_archive_studentAssignments. Per-function yes/no is enough. If uniform, the fix worth discussing is adding the reason check to the trigger rather than patching call sites - one enforcement point instead of N. That is a design call with real blast radius: it converts a currently-silent path into a hard failure, so every existing writer must be verified first, and migrations that legitimately omit a reason would start failing. Measure before implementing. Correct commons:87 in the same pass as PLUTO-706, which is rewriting this area anyway. Context: surfaced during PLUTO-705, which was itself cancelled as no-defect-found - this finding is what survived it and is independent of whether any function currently exercises the gap. All 13 second-writer functions currently set both GUCs.
Activity
-
Asymmetry measured per-function across all archive triggers, db-pluto-cc, prosrc read 2026-08-17. UNIFORM: every one of the 23 accepts a silent NULL app.archive_reason whenever app.user_id is present. Two shapes. 20 use the plain form - RAISE EXCEPTION only if app.user_id is NULL or empty, no independent check on archive_reason: fn_archivePracticaChange, fn_archivePracticaDocenteNotaChange, fn_archivePracticaReviewChange, fn_archive_academicHierarchy, fn_archive_comisionAdjuntos, fn_archive_comisionJtps, fn_archive_comisiones, fn_archive_jtpAyudantes, fn_archive_lookupOptions, fn_archive_periodos, fn_archive_practicaAnestesias, fn_archive_practicaAntecedentesPersonales, fn_archive_practicaCaracteristicasPieza, fn_archive_practicaComplicacionesIntraoperatorias, fn_archive_practicaDiagnosticosRadiograficos, fn_archive_practicaTecnicasQuirurgicas, fn_archive_studentAssignments, fn_archive_systemSettings, fn_archive_userRoles, fn_archive_users. The other 3 - fn_archive_accessRequests, fn_archive_supportTickets, fn_archive_supportticketmessages, byte-identical to each other - use IF v_actor IS NULL AND (v_reason IS NULL OR v_reason NOT LIKE public:%) THEN RAISE. That is an OR-relaxation admitting anonymous public flows (accessRequests submission, support intake), NOT a stricter reason check: when v_actor IS NOT NULL, which is every authenticated path, the condition is false regardless of v_reason, so reason can still be silently NULL. A first regex pass read these three as raises-on-reason=true; that was a false positive - the RAISE text mentions archive_reason without enforcing it on the authenticated path. Reading the gate is not the same as reading the string it contains. COUNT CORRECTION: there are exactly 23 archive triggers, not 24. fn_detectAdminAccountEdit was swept in by the original literal-name grep but INSERTs into no *Archive table and has no RAISE at all - it is not an archive trigger. This is now the only finding left standing from the PLUTO-705 thread, and the single-enforcement-point argument holds: one trigger-side check would cover all 23 call paths.
-
Cross-project comparison, measured per project by its own lane 2026-08-17. THREE DIFFERENT ENFORCEMENT SHAPES, no symmetry. PLUTO: asymmetric - all 23 archive triggers RAISE on a missing app.user_id, none checks app.archive_reason. Doc PROMISED loud failure for both; commons:87 corrected in a39cab5. Zero NULL archiveReason in prod. VENUS: identical asymmetry, replicates 2/2, same RAISE text about calling setArchiveContext. db-venus-role.md:24 carries the same overstated wording, coder-venus-cc routed the fix to db-venus-cc. 7385 archive rows across two tables, zero NULL reasons, every value 6-prefixed - the convention has held 100 percent while unenforced, which coder-venus-cc correctly called the worst shape for a latent defect: nothing in the data will ever warn you and the first NULL arrives from a new call site. MARS: does not reproduce - uniformly FAIL-SOFT on BOTH GUCs. All 10 trigger functions use current_setting(name, TRUE) inside NULLIF plus an EXCEPTION WHEN OTHERS handler setting NULL, so archivedBy and archiveReason both land NULL silently and neither raises. No doc defect there: mars-commons already states that a missed setArchiveContext lands NULL and names it a silent forensic blind spot. Mars has no database-level enforcement of either field, only a push-time lint on the reason prefix, documented rather than promised away. THE POINT FOR PLUTO: our defect was never the trigger behaviour alone - it was the gap between what commons:87 promised and what the trigger does. Mars has weaker enforcement than us and no defect, because its doc tells the truth. METHOD NOTE from the mars measurement, worth reusing: mars found 1180 NULL rows across three archive tables and did NOT report a 20 percent failure rate. Broken out by month, usersArchive runs 88/106 April, 528/686 May, then 3/1032 June, 0/799 July, 2/527 August - the bulk is pre-adoption history and the mechanism has worked since June. A raw NULL count would have been wrong in the alarming direction. Any pluto NULL count on this axis must be time-distributed before it is read as a rate.
-
archiveReason VALUE-axis measured by db-pluto-cc (read-only), the axis nobody had checked. Prior work only tested archiveReason IS NULL; venus made the same mistake, saw colon-shaped values and called them 6-prefix compliant, then found 1612/3408 (47%) out of policy on re-test. Pluto membership test against the closed set {admin,public,system,demo,bulk,user} across all 23 archive tables: 31 of 9140 rows out of policy = 0.34%. No bulk-migration contamination, no db:-style prefix, no concentration in any single month. Two clusters: free-text reasons from 2026-04 predating the convention (docente membership add, alumno assignment edit, CR 64 data patch, authId linked on first SSO login), and a Jul/Aug scatter of manual/test/support that matches ad hoc psql/SOP writes. Note support and test are colon-shaped but NOT in the closed set - exactly the trap venus fell into. Premise correction: the WI said the convention has held 100% while unenforced. Materially true at 99.66%, literally false. The 31 exceptions are the mechanism 707 already names - the trigger checks nothing, so any direct GUC write bypasses the TS-side ARCHIVE_PREFIXES guard, which only covers the archivePractica server action. Gives the open enforcement question a real small number instead of an assumed zero. Nothing changed.
-
CORRECTION to the VENUS paragraph of the 2026-08-17T21:31 cross-project note on this WI. That note says venus has "7385 archive rows across two tables, zero NULL reasons, every value 6-prefixed - the convention has held 100 percent while unenforced". The final clause is FALSE and coder-venus-cc retracted it 2026-08-18. Measured against the closed set {admin,public,system,demo,bulk,user}: practicasArchive is 1612 of 3408 rows OUT of policy (47%) - "db:" 1610 rows all from the venus155 migration dated 2026-07-19, "support:" 1, and one row with no colon at all ("support correction SOP-VENUS-17"). practicaParticipationsArchive is clean at 3977 rows, all user:/admin:. HOW THE WRONG FIGURE WAS PRODUCED, which is the portable part: venus tested archiveReason IS NULL (0/7385, genuinely clean) and then eyeballed a top-8 value list. Colon-prefixed is a SHAPE check; in-policy is a MEMBERSHIP check. The NULL axis was clean and the VALUE axis was 47% broken, and the clean axis was reported as if it settled the question. When a policy names a closed set, test membership in that set. Pluto fell into the adjacent version of the same trap - "support" and "test" are colon-shaped and NOT in the set - which is why the pluto figure is 31/9140 (0.34%) rather than zero. WHAT DOES NOT CHANGE FOR PLUTO: the pluto numbers in the 14:11 verified event were measured independently by db-pluto-cc against the closed set and are unaffected. The mechanism this WI names is also unaffected and is now confirmed on a second project - venus triggers enforce app.user_id by RAISE, archiveReason NULLs through unchecked, and the TS-side prefix guard only covers the archivePractica server action, so any migration or manual psql write setting the GUC directly writes an arbitrary string. That is exactly how "db:" and the bare-string row got in. VENUS ENFORCEMENT DECISION, recorded so pluto does not inherit it as precedent: db-venus-cc ruled wording-only, NOT a trigger-side RAISE, because that is a live behaviour change on real clinical data and 1610 of the 1612 exceptions are one historical migration rather than an ongoing rate. Pluto has not decided its own equivalent and should not read the venus ruling as settling it - the pluto shape (0.34%, two clusters, no single-migration concentration) is a different distribution and argues its own way. DO NOT GENERALISE ACROSS PROJECTS: all three gave different answers on every axis measured so far - enforcement shape, NULL rate, and now value compliance. Every cross-project sentence in this WI needs its own per-project measurement behind it.
-
db-pluto-cc
-
Pluto's own enforcement ruling (independent of venus, per the WI's own warning not to inherit it): wording-only, no trigger-side RAISE on missing app.archive_reason. Basis: 0.34% out-of-policy (31/9140), two old pre-convention clusters (2026-04 free-text predating the lint, Jul/Aug ad hoc psql/SOP scatter), no bulk-migration contamination, no ongoing rate - same shape venus found when it ruled wording-only to avoid a live behavior change on real clinical data. commons.md:87 wording already corrected (a39cab5) to state only app.user_id is enforced. A trigger RAISE would convert a currently-silent path to a hard failure for every existing + future writer, for a defect class that isn't accruing. Revisit only if the rate changes.
bug
2026-08-17 by wi-cli-venus
1w ago
2026-10-04 15:29