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