Four DB functions set app.user_id but never app.archive_reason, so archiveReason falls through to the caller or NULL
Measured 2026-08-17 by db-pluto-cc against prod pg_proc.prosrc (fleet GUC audit, EVO-88 tail). Twelve distinct DB functions are second writers of the archive-context GUC pair - they call set_config themselves, invisible to anyone auditing who sets archive context from src/ alone. Eight set BOTH names: fn_addUserRole, fn_applyParticipantChange, fn_bulkAssignRole, fn_insertSupportTicket (both the 10-arg and 11-arg overloads), fn_setPersonaRoles (5-arg), fn_setPrimaryRole, fn_submitAccessRequest, fn_updateSupportTicketTriage. FOUR set app.user_id only and never app.archive_reason: fn_approveEmailChangesBatch, fn_bulkImportUsers, fn_completeStudentAssignment, fn_setPersonaRoles (3-arg emergency-restore variant). Consequence: any archive row those four cause via their triggers gets archivedBy attributed correctly while archiveReason falls through to whatever SET LOCAL the caller happened to do outside the function, or NULL. That breaks the 6-prefix archiveReason policy in shared/md/pluto-commons.md for exactly the writes whose provenance matters most - bulk user import, batch email-change approval, role restore. It also degrades archiveReason as a write-signature instrument, which is the sanctioned way to attribute a write since updatedAt cannot be used for it. Fix: set both GUCs inside each of the four with an explicit prefix, or establish that the caller must and assert it. Measure first - query the archive tables for rows from these paths with a NULL or unexpected archiveReason to size real damage before changing anything. Related: PLUTO-704 (schema.md does not export function bodies, which is why this class was invisible).
Questions
Activity
-
Pre-fix damage measurement, db-pluto-cc, read-only against prod 2026-08-17. archiveReason NULL count is ZERO across all four archive tables the defective functions touch: usersArchive 1273 rows, userRolesArchive 113, supportTicketsArchive 65, studentAssignmentsArchive 105. Also checked the non-NULL-but-wrong failure mode a NULL count cannot see: full distinct archiveReason distribution read value-by-value on usersArchive (52 distinct) and userRolesArchive (20 distinct); every value is specific and attributable to a real action, nothing generic or leftover from another code path. SEVERITY RESTATED: dormant risk, not observed damage - schedule as a defect, do not escalate as an active incident. OPEN CAVEAT, not yet measured: a zero-damage result is also what an unexercised path returns. Nothing here confirms any archive row was actually produced BY one of the four functions, so absence of bad rows may mean the gap has never fired rather than that it fires harmlessly. Needs a positive exercise count per function before the zero is read as evidence. Second bound: the 12-second-writer enumeration selected prosrc on the literal GUC names, so it is exhaustive only for functions naming them literally - a dynamically built GUC name would not appear, untested on pluto or venus.
-
CORRECTION 2026-08-17, db-pluto-cc: fn_completeStudentAssignment is NOT one of the affected functions - false positive from the original prosrc grep. Its body (migration 089 ~line 165) sets BOTH GUCs; the archive_reason value is format()-built but the KEY is the plain literal, so the original literal-name search should have matched and the miss was a verification gap, not the dynamic-GUC-name bound flagged separately. Confirmed against data: studentAssignmentsArchive holds one row with archiveReason=user:completarAsignacion:user:<uuid>:comision:<uuid>:jtp:<uuid>, an exact match to that format string, and one row is the full prod history of that flow. Affected set shrinks from four to THREE: fn_approveEmailChangesBatch, fn_bulkImportUsers, fn_setPersonaRoles(3-arg emergency-restore, no live TS caller). All three being re-verified against prosrc directly rather than trusted from the grep. STRUCTURAL FINDING, independent of the correction and the more important half: fn_archive_studentAssignments RAISES on missing/empty app.user_id (archive trigger on % requires app.user_id GUC) but has NO corresponding check on app.archive_reason - NULLIF(current_setting(...,true),) falls through to NULL silently. So shared/md/pluto-commons.md:87 "Archive triggers fail loudly if actor context is absent" is accurate for user_id and OVERSTATED for archive_reason. A migration or function author relying on that sentence would expect a missing reason to be caught; it is not. Open: whether that asymmetry holds across all 23 archive trigger functions or is specific to this one.
-
SECOND CORRECTION 2026-08-17, larger than the first: PLUTO-705 currently has ZERO confirmed members. db-pluto-cc read full prosrc for all three remaining candidates and every one sets BOTH GUCs, adjacent and unconditional - fn_approveEmailChangesBatch (reason admin:accessRequestApprovedWithEmailChange:..., inside item_loop), fn_bulkImportUsers (bulk:csvImport:batch:%s, after the capability check), fn_setPersonaRoles 3-arg (admin:personaRolesUpdated:user:%s, after validation/lock). Combined with the fn_completeStudentAssignment correction, the original "4 write only user_id" result was wrong for all four members. Root cause, same mistake twice in one pass: the enumeration query matched set_config on app.user_id without checking whether a set_config on app.archive_reason existed elsewhere in the same body, and no member was verified against full prosrc before being reported. I amplified it - filed this WI and broadcast the finding to coder-mars-cc and coder-venus-cc as measured fact. The original enumeration is being rerun with full-prosrc reads per hit. HOLDING this WI open pending that result rather than cancelling, because the underlying question (are there half-writing second writers) is not yet answered either way - only the specific four are cleared. WHAT SURVIVES BOTH CORRECTIONS, and is the finding worth keeping: fn_archive_studentAssignments RAISES on missing app.user_id but has no check at all on app.archive_reason, which falls through to NULL silently. archivedBy is enforced by the database; archiveReason is enforced by convention alone. See the separate ask on whether that asymmetry holds across all 23 archive triggers.
-
NO DEFECT FOUND - closing on a full re-derivation, not on a second opinion. db-pluto-cc regex-matched pg_proc.prosrc for every public function naming app.user_id or app.archive_reason as a set_config first-arg literal and read each body: 13 rows, 12 distinct signatures, ALL 13 write BOTH GUCs, zero write user_id only. Full set all TRUE/TRUE: fn_addUserRole(4), fn_applyParticipantChange(4), fn_approveEmailChangesBatch(3), fn_bulkAssignRole(4), fn_bulkImportUsers(3), fn_completeStudentAssignment(4), fn_insertSupportTicket(10), fn_insertSupportTicket(11), fn_setPersonaRoles(3), fn_setPersonaRoles(5), fn_setPrimaryRole(4), fn_submitAccessRequest(2), fn_updateSupportTicketTriage(5). HOW THIS WI CAME TO EXIST, recorded because the mechanism will recur: an AGGREGATE result (13 rows / 12 distinct names, 4 of them user_id-only) was read as a PER-FUNCTION claim and reported without confirming any individual function against its body. It then survived two rounds of scrutiny because each round questioned the scope of the claim rather than its members - I asked for attachment, then for branch reachability, both of which returned clean answers ABOUT A SET THAT WAS NEVER VERIFIED TO CONTAIN WHAT IT SAID. It dissolved only when a body read was forced. I amplified it: filed this WI at p2 and broadcast it to coder-mars-cc and coder-venus-cc as measured fact, which prompted both to re-measure their own projects against a defect that did not exist. Cost was real but bounded - both found their own method gaps in the process. NOT cancelled: the trigger asymmetry this WI surfaced, which is real and now tracked separately.