mars
MARS-64
Archive trigger captures NEW not OLD on UPDATE — verify forensic recovery coverage across audited tables (db)
In progress normal
dmdb03-mars-ca
Activity
-
db-mars-cc / db
-
Live-catalog triage 2026-09-11. Real and confirmed against the live catalog; capture side is PER-TABLE, not one fleet-wide answer, so the WI's framing as a single behaviour is wrong. Swept prosrc for all 10 fn_archive*Change trigger functions: only fn_archiveUsersChange and fn_archiveStudentExamScoresChange have an explicit TG_OP='UPDATE' branch referencing both OLD and NEW - and those two are the rowBefore/rowAfter-shaped archive tables, which is the correct pattern. The other eight (fn_archiveComisionChange, fn_archivePeriodoChange, fn_archivePracticaChange, fn_archiveStudentAssignmentChange, fn_archiveTeacherMembershipChange, fn_archive_practica_images, fn_archive_practica_teeth, fn_archive_practica_tooth_conductos) write flat column-per-field archive rows with no distinct UPDATE branch, and fn_archivePracticaChange is already known to capture NEW - meaning the pre-update value is unrecoverable from the archive for those tables and pg_dump is the only pre-update recovery path. NEXT CONCRETE STEP: produce the per-table OLD-vs-NEW capture matrix for those eight, then decide per table whether to switch to OLD or move it to the rowBefore/rowAfter shape - do not apply a blanket change, since flipping to OLD silently breaks any consumer reading the archive as a current-value mirror.
-
Per-table OLD-vs-NEW capture matrix delivered (the NEXT CONCRETE STEP from the 2026-09-11 triage). Measured live 2026-09-12 against pg_proc.prosrc, pg_trigger and the archive tables themselves. TWO CORRECTIONS to the triage note's premises, both of which change the disposition. CORRECTION 1 - "the other eight ... have no distinct UPDATE branch" is FALSE for two of them. fn_archiveStudentAssignmentChange and fn_archiveTeacherMembershipChange both carry an explicit IF TG_OP IN ('UPDATE','DELETE') branch whose INSERT reads OLD."..." on every column. They capture the PRE-UPDATE value and are already correct. The triage's prosrc sweep looked for a branch referencing BOTH OLD and NEW and these have one INSERT branch per side, so they read as unbranched. Counting OLD./NEW. occurrences (12/12 on both) is what surfaced them; reading the bodies is what settled it. THE MATRIX - ten audited tables, four shapes. (A) rowBefore/rowAfter shape, correct by construction - users (fn_archiveUsersChange), studentExamScores (fn_archiveStudentExamScoresChange). (B) Explicit UPDATE+DELETE branch reading OLD, correct - studentAssignments (fn_archiveStudentAssignmentChange), teacherComisionMemberships (fn_archiveTeacherMembershipChange). (C) DELETE branch reads OLD, fall-through branch handles INSERT *and* UPDATE reading NEW - comisiones (fn_archiveComisionChange), periodos (fn_archivePeriodoChange). UPDATE captures NEW. (D) rec := OLD on DELETE else NEW, single INSERT - practicas (fn_archivePracticaChange), practicaImages, practicaTeeth, practicaToothConductos. UPDATE captures NEW. So SIX tables capture NEW on UPDATE, not eight: comisiones, periodos, practicas, practicaImages, practicaTeeth, practicaToothConductos. CORRECTION 2 - "the pre-update value is unrecoverable from the archive for those tables, and pg_dump is the only pre-update recovery path" is TOO STRONG, and the weaker true statement changes what to build. These triggers fire on INSERT, UPDATE and DELETE (tgtype IDU on all ten, all BEFORE). A NEW-capturing trigger that also archives the INSERT writes a complete forward chain: the pre-update state of any UPDATE is the PRECEDING archive row for that id. The value is recoverable by CHAINING, not from the row itself - which is exactly the method already recorded in [[reference_archive_chain_transition_proof]], and why [[reference_changelog_insert_diff_baseline]] insists archiveOp=INSERT rows are retained as the diff baseline. The population that is genuinely unrecoverable is the set of rows whose chain has NO INSERT baseline (row predates trigger installation, or the chain has a hole). MEASURED, that population is 15 rows, all config/reference, zero clinical: practicas 0 of ~3989 lack an INSERT baseline (archive: 3989 I / 7487 U / 1 D) practicaImages 0 (16347 I / 1500 U / 243 D) practicaTeeth 0 (4242 I / 1212 U / 92 D) practicaToothConductos 0 (5226 I / 1406 U / 96 D) comisiones 13 of 20 (7 I / 15 U) periodos 2 of 3 (1 I / 9 U) The entire clinical chain - práctica, its images, its teeth, its conductos - is reconstructible to creation TODAY. The 15 exposed rows are comisiones/periodos rows that existed before their trigger was installed. THE REAL RISK IS LATENT, AND IT IS A DIFFERENT DEFECT. A NEW-capture archive is only as good as chain COMPLETENESS, whereas an OLD-capture archive is self-contained per row: one missing link costs you one transition under OLD, but under NEW it costs you the pre-state of the NEXT update too. And the chain is currently complete only because the triggers have never been bypassed. tgenabled is 'O' (origin) on NINE of the ten - only trg_practicas_archive is 'A' (ENABLE ALWAYS). Per [[reference_enable_always_survives_replica_role]] (MARS-597), an 'O' trigger is silently skipped under session_replication_role=replica, which is precisely how a restore, a bulk repair or a logical-replication apply would punch the hole. MARS-597's hardening reached exactly one trigger; the other nine are still bypassable. DISPOSITION - do NOT flip any trigger to OLD, and the reason is stronger than the triage note's. The note warned it would break consumers reading the archive as a current-value mirror; additionally, flipping to OLD on a shape-(C)/(D) trigger makes the INSERT-op row incoherent (there is no OLD on INSERT) and destroys the diff baseline the chain method depends on. The NEW-capture design is not wrong, it is a forward changelog - it was only ever mis-described as a per-row snapshot. RECOMMENDED NEXT ACTIONS, in order, none applied yet: 1. ENABLE ALWAYS on the nine 'O' archive triggers. This is the actual forensic-coverage fix: it protects chain completeness, which is what the recoverability argument rests on. MARS-597 already established the pattern and verified it safe on Mars for trg_practicas_archive. Own migration, own review - it is prod DDL on nine triggers. 2. COMMENT ON FUNCTION for the six shape-(C)/(D) functions stating the capture side and that pre-update recovery is by chaining to the preceding archive row. The semantics being undocumented is what produced this WI's original premise. 3. Leave the 15 comisiones/periodos baseline-less rows alone and state them as residue; they are pre-trigger config rows and no write can retroactively create their baseline. Coverage statement: capture side determined by reading prosrc for all ten functions (not inferred from the OLD./NEW. counts, which were only used to find candidates); chain completeness measured by anti-join against each archive table for six tables; trigger events and tgenabled read from pg_trigger. NOT exercised: no restore or replica-role apply was performed, so the 'O' bypass is argued from MARS-597's prior measurement rather than re-measured here; and studentAssignments/teacherComisionMemberships chain completeness was not measured because their capture side makes it irrelevant.
-
RECOMMENDED ACTION 1 APPLIED. Migration 159 is live on prod and committed as handoff b55ba16 (Local-Author: db-mars-cc, one file, parses). All ten archive triggers are now tgenabled='A'. MARS-597's hardening had reached exactly one of them. THE HOLE WAS FORCED TO FAIL BEFORE THE FIX, not asserted from the catalog. Pre-fix, in a rolled-back transaction: SET LOCAL session_replication_role = replica; UPDATE on periodos returned UPDATE 1 while periodosArchive stayed 10 -> 10 - the write landed and the archive gained nothing. The identical UPDATE at origin gave 10 -> 11, which is the control that proves the probe was capable of detecting a row. Post-fix, the same replica-role UPDATE gives 10 -> 11. Residue 10 after every leg. So this is a control that has been seen to fail against the prior behaviour, not a green line. SEVERITY IS HIGHER THAN THE TRIAGE ASSUMED, and this is the finding worth carrying: the bypass is reachable FROM THE DB LANE, not only by supabase_admin. The connecting postgres role is rolsuper=f but rolreplication=t, SET LOCAL session_replication_role = replica returns SET and current_setting reads back 'replica', and pg_parameter_acl has no row for the GUC. Anyone holding the DSN this lane holds could have punched the chain. That makes 159 a live-hole closure rather than defence-in-depth. PREMISE RE-MEASURED, NOT INHERITED. Migration 033 (MARS-435) is the ENABLE ALWAYS precedent on Mars - not 094/MARS-597 - and it justified itself with "confirmed safe: zero replication slots/subscriptions live", true on 2026-07-13. Re-run 2026-09-12: 0 replication slots, 0 subscriptions, 1 publication (supabase_realtime, puballtables=f) containing ZERO tables, i.e. inert. Nothing replicates off this instance, so promoting to ALWAYS cannot double-apply at a subscriber. POST-CHECK SHAPE. Asserts the END STATE (10 of 10 are 'A'), because ENABLE ALWAYS on an already-'A' trigger is a no-op and the migration must stay replayable - "nine changed" would be false on replay. It asserts in BOTH directions: the (schema, table, trigger) triple must resolve to exactly ten live triggers, so a renamed or dropped trigger cannot make the ALWAYS count read correct by shrinking the denominator. Identifying by triple rather than tgname alone is the pg_trigger analogue of the pg_proc-filtered-on-proname-without-pronargs trap from MARS-723; this is the first migration written under that corrected template. NO schema.md RE-EXPORT, and it was measured rather than assumed: the export prints trigger events (TRIGGER: name (BEFORE UPDATE)) but carries no enable-state at all - zero ALWAYS occurrences in the file - so tgenabled is invisible to the mirror and 033's own change was never reflected there either. SCOPE NOTE: trg_sync_archive_assigned_teacher_user_id was deliberately excluded. It matches a '%archive%' name sweep but is a sync trigger ON studentAssignmentsArchive, not one of the ten archive-capture triggers. OPEN HALF - DO NOT READ "ten of ten" AS DONE. Nothing detects reversion. Plain ALTER TABLE ... ENABLE TRIGGER silently downgrades 'A' back to 'O' with no error, and DISABLE is not the reverse (it turns archiving off entirely). So an ordinary DISABLE -> maintenance -> ENABLE sequence would lose this hardening quietly, which is exactly how it will be lost if it is lost. ENABLE ALWAYS is therefore a SETTING today, not a CONTROL. The instrument that would catch it already exists and is already scheduled: practicas_integrity_check() carries a catalog-drift arm asserting ENABLE ALWAYS, but it covers practicas ONLY. Extending it to all ten (table, trigger) pairs is the half that makes this durable, and it is a separate migration against the LIVE function body - which is from migration 152, not 033, so read prosrc before editing. REMAINING ACTIONS 2 and 3 from the matrix are unchanged: COMMENT ON FUNCTION for the six shape-(C)/(D) functions stating capture side and that pre-update recovery is by chaining; and the 15 baseline-less comisiones/periodos rows stay as stated residue (pre-trigger config, zero clinical, no write can retroactively create their baseline). COVERAGE STATEMENT: the replica bypass and its fix were exercised on periodos only, one trigger of the nine, in a rolled-back transaction. The other eight are asserted from pg_trigger.tgenabled, not individually exercised - the GUC is session-wide and not per-trigger, so one demonstration establishes the mechanism, but no write was made against the other eight tables. Not exercised at all: no restore, no pg_dump reload, no logical-replication apply.
-
SELF-REPORTED DEFECTS IN 159, found on my own re-read after handoff. Neither changes the outcome and neither is being amended - the file is applied and b55ba16 is already in coder02-mars-cc's hands, so a rewrite would make it review a phantom. Both are recorded here and will be folded into my reply to its verdict rather than spending a thread round. (1) THE POST-CHECK'S FIRST ASSERTION IS DEAD CODE. v_total is count(*) over 'FROM expected e LEFT JOIN pg_class ... LEFT JOIN pg_trigger', which is always 10: a LEFT JOIN preserves every expected row whether or not a trigger matches, and fan-out is impossible since tgname is unique per table. So 'v_total <> v_expected' can never fire. It should have been count(t.oid). The dropped-or-renamed case IS still caught, but by the SECOND assertion, via COALESCE(t.tgenabled::text,'ABSENT') - the missing pair makes v_always < 10 and names itself in v_missing. So the both-directions property the header claims is real, but the comment misattributes which line delivers it. The general trap worth keeping: an assertion written over a LEFT JOIN's row count measures the EXPECTED list, not the OBSERVED catalog, and reads as a presence check while checking nothing. (2) THE MECHANISM SENTENCE IN 159'S HEADER IS WRONG, inherited from MARS-597 rather than measured. Both say rolreplication=t is what lets postgres set session_replication_role. Stock Postgres does not work that way - it is a SUSET GUC and rolreplication does not confer it. Measured: current_setting('supautils.privileged_role_allowed_configs') lists session_replication_role explicitly, alongside auto_explain.*, deadlock_timeout, pgaudit.*, safeupdate.enabled and the log_* family. So the capability comes from a SUPABASE PLATFORM ALLOWLIST, not a role attribute. This matters beyond pedantry: the allowlist can change under us on a platform upgrade and is invisible to pg_roles, so anyone re-checking this later must read the allowlist, not rolreplication. The severity claim is unaffected - the GUC is settable either way, which was verified directly. SCOPE CORRECTION WORTH STATING PLAINLY: 159 closed the FORENSIC-CHAIN hole, not the replica BYPASS. The GUC is still settable and every other 'O' trigger on Mars is still skippable - periodos alone carries four non-archive 'O' guards (trg_periodos_trainingActivity_parent, trg_periodos_materiaOption_valid, trg_periodos_endo1ClinicalShape_parent, trg_periodos_aa_enforce_isactive). No census of 'O' guard triggers outside the archive set has ever been done on Mars. Reading 'hole closed' as 'bypass closed' is exactly the failure class this WI has already produced once.
-
PASS:b55ba16 deep (CHECK=2a known-deep path, SAMPLED=no) - coder02-mars-cc, review lane. PUBLISHED as 97160d6 v2.79.70, live 2.79.70 == remote package.json on main, dpl_Gx1aqKgqeGwCSJyUHMec4G8Q5iPt. END STATE CONFIRMED INDEPENDENTLY, not read off the handoff message: running the migration's own expected-pairs list live as the review lane returns expected_rows 10, always_now 10, unresolved 0 - comisiones, periodos, practicas, practicaImages, practicaTeeth, practicaToothConductos, studentAssignments, studentExamScores, teacherComisionMemberships, users, every one tgenabled='A'. THE SHAPE OF THE FIX IS RIGHT AND THE REJECTED ALTERNATIVE MATTERS MORE THAN THE FIX. Six of the ten triggers capture NEW on UPDATE and that is not the defect: because they also archive the INSERT they write a forward changelog, so the pre-update state of any UPDATE is the preceding archive row for that id. Recoverability therefore rests on chain COMPLETENESS, and flipping a NEW-capture trigger to OLD would make its INSERT-op row incoherent and destroy the diff baseline recovery depends on. So the control is "the trigger is never skipped", which is exactly what ENABLE ALWAYS buys. P3 - THE POST-CHECK'S FIRST ASSERTION CANNOT FIRE, and it is again a correct control with a wrong description. v_total is count(*) over a LEFT JOIN from a literal VALUES list, so it is structurally 10 for every input: a renamed or dropped trigger leaves its expected row present with NULLs, and both joins (pg_class on relname+namespace, pg_trigger on tgrelid+tgname) match at most one row, so the count can neither shrink nor grow. The denominator-shrink hazard the comment describes does not exist in this shape. The rename/drop case IS caught - by v_always plus the ABSENT branch of v_missing - so the migration is SAFE and only the first IF is inert. The cost is the comment crediting protection to the check that cannot provide it, which is how the next author writes the same dead assertion deliberately. Cheapest fix in the template going forward: count the RESOLVED pairs (count(t.oid)) rather than count(*), which makes the first assertion do the work its comment claims. COVERAGE STATEMENT THAT WIDENS THE ROW'S OWN OPEN HALF, measured live rather than inferred: public carries 432 INTERNAL constraint triggers across 38 tables and every one is tgenabled='O'. session_replication_role=replica suppresses those too, so after this migration the archive chain is protected while every foreign-key check in the schema remains skippable by the same one-line SET from the same credential. "The chain is replica-bypass resistant" is true and must not be read as "the GUC is harmless now". Flipping internal constraint triggers requires superuser, which the DB lane does not hold, so the durable remedy is narrowing WHO MAY SET that GUC - an Elazar/PM call, not a lane call - alongside the catalog-drift arm db-mars-cc already named as this row's open half. ON THE OPEN HALF, the second opinion db-mars-cc asked for: agreed, ENABLE ALWAYS is a SETTING and not yet a CONTROL, and the asymmetry is the dangerous part - plain ENABLE silently downgrades 'A' to 'O' with no error while DISABLE is not the reverse at all, so an ordinary DISABLE -> maintenance -> ENABLE window loses this hardening with nothing raised. practicas_integrity_check()'s existing drift arm covering practicas ONLY is a control bounding a narrower axis than the question, which is the failure shape that reads clean indefinitely. Extending it to all ten pairs as its own migration against the live body (from 152, not 033) is the right sequencing, and leaving it stated rather than shipped blind into a function that had not been re-read was the right call. NOT EXERCISED, from db-mars-cc and accepted as the honest bound: the bypass and the fix were demonstrated on periodos only; the other eight are asserted from pg_trigger.tgenabled, which establishes the mechanism (the GUC is session-wide, not per-trigger) but is not a write against those tables. No restore, no dump reload, no logical-replication apply was exercised. The safety premise was re-measured rather than inherited from migration 033 - zero replication slots, zero subscriptions, one publication with puballtables=f and zero publication_tables, so the publication is inert. PUBLICATION SHAPE: 97160d6's only content is the version bump, because 159 was already committed by the DB lane and the staged set was empty. That deploy therefore changes deploymentId with app code unchanged - correct for a catalog-only migration already applied in prod, and the window where the version endpoint is an inert instrument rather than a check. Both read and agreeing anyway.
-
CORRECTION TO MY OWN VERDICT'S MECHANISM, and to 97160d6's commit message which is immutable. db-mars-cc caught it and I verified it live rather than adopting it. WRONG: that the bypass is reachable because postgres is rolreplication=t. rolreplication does NOT confer a PGC_SUSET GUC in stock Postgres. That premise is in MARS-597, was inherited by migration 159's header, and I repeated it in my PASS:b55ba16 verdict and in the publishing commit message - four copies of one unread sentence, which is the retraction-reaches-the-sentence-not-its-copies shape evolutiva-commons names. RIGHT, read live as the review lane: the lever is supautils. current_setting('supautils.privileged_role_allowed_configs') on this ref lists session_replication_role explicitly, alongside auto_explain.*, deadlock_timeout, pgaudit.*, pgrst.*, plan_filter.*, safeupdate.enabled, wal_compression, track_* and the log_* family. Confirmed alongside it: is_superuser=off, pg_roles gives rolsuper=false / rolreplication=true, and pg_parameter_acl has ZERO rows for the parameter. So both facts I stated are true individually - rolsuper=f, rolreplication=t, no parameter ACL - and the CAUSAL claim joining them was wrong. WHAT THIS CHANGES FOR THE REMEDY, which is the reason it matters rather than being bookkeeping: 'narrow who may SET that GUC' is concretely 'remove session_replication_role from supautils.privileged_role_allowed_configs' - a PLATFORM-CONFIG change over the Supabase Management API, not DDL and not a role grant. Nothing in pg_roles or pg_parameter_acl would show it, and it can change under us on a Supabase upgrade invisibly. Anyone picking up the open half should start from the allowlist, not from rolreplication, and should re-read the allowlist at that time rather than citing this event. Unchanged by the correction: the 432 internal constraint triggers across 38 public tables all at tgenabled='O', so replica mode still suppresses every foreign-key check; migration 159's end state (10 of 10 archive triggers 'A', confirmed independently); and PASS:b55ba16 deep. The P3 on the post-check's inert first assertion was found independently by db-mars-cc before my message landed, same diagnosis, and the template is corrected to count(t.oid).
-
PARKED - fleet-wide STOP (relayed, pmmaster-evolutiva-cc, 2026-09-11 23:57 ART, Elazar direct). Not mid-push, no uncommitted work, not broken. db-lane scoped status clean across db/migrations/applied, db/schema.md, db/reference-data.tsv. DONE. Recommended action 1 is complete and PUBLISHED: migration 159 applied to prod, handoff b55ba16, published by coder02-mars-cc as 97160d6 v2.79.70 (live version == remote package.json). All ten archive triggers are tgenabled='A'. Verdict PASS:b55ba16 deep, CHECK=2a, SAMPLED=no, end state verified independently by the reviewer against my own expected-pairs list (expected 10, always 10, unresolved 0). The hole was forced to fail before the fix and the fix re-proven with the same probe, all legs rolled back, residue 0. NOT DONE, and stated so 'ten of ten' does not read as closure. (a) The drift arm. ENABLE ALWAYS is a SETTING, not a CONTROL: plain ALTER TABLE ... ENABLE TRIGGER silently downgrades 'A' to 'O' with no error, DISABLE is not the reverse, and nothing detects the reversion. practicas_integrity_check() has a scheduled catalog-drift arm asserting ENABLE ALWAYS but covers practicas ONLY. (b) COMMENT ON FUNCTION on the six shape-(C)/(D) archive functions stating capture side and that pre-update recovery is by chaining. (c) The 15 baseline-less comisiones/periodos rows stay as stated residue - pre-trigger config, zero clinical, no write can retroactively create their baseline. NEXT ACTION when resumed (needs Elazar's word, not pmmaster's): item (b) first - six COMMENT ON FUNCTION, trivially reversible, no behaviour change, cheapest thing on the row. Item (a) second, and it requires reading the LIVE prosrc of practicas_integrity_check() before editing, because the live body is from migration 152, NOT 033 - do not write it against 033's text. Both carry the corrected post-check template: identify the object precisely (pronargs / the full triple) and count the CATALOG side, never count(*) over a LEFT JOIN from the expected list. CARRIED FORWARD, NOT MINE TO ACT ON - the finding that widens this WI beyond its title. coder02-mars-cc measured that public holds 432 INTERNAL CONSTRAINT TRIGGERS across 38 tables, all tgenabled='O'. session_replication_role=replica suppresses those too, so after 159 the forensic chain is protected while every foreign-key check in the schema remains skippable by the same one-line SET from the same credential. 159's NOTICE ('chain is replica-bypass resistant') is true and must not be read as 'the GUC is harmless now'. Flipping internal constraint triggers needs superuser, which this lane does not hold, so the durable remedy is narrowing WHO MAY SET the GUC. Concrete lever, measured: the capability is NOT rolreplication=t (MARS-597 asserts that and 159's header inherited it - both wrong, rolreplication does not confer a SUSET GUC); it is supautils, and current_setting('supautils.privileged_role_allowed_configs') lists session_replication_role explicitly. So the remedy is removing it from that allowlist - a platform-config change over the Management API, not DDL, and it can change under us on a Supabase upgrade invisibly to pg_roles. ELAZAR/PM CALL. This lane reports and stops. KNOWN DEFECT IN 159, self-reported and independently confirmed by the reviewer, deliberately NOT amended (applied + published = immutable): the post-check's first assertion is dead code - count(*) over a LEFT JOIN from a literal VALUES list is structurally 10, so it can never fire. The rename/drop case IS caught, by the second assertion via COALESCE(...,'ABSENT'). Migration is safe; the comment misattributes which line protects it. Template corrected to count(t.oid) for future post-checks.
-
Status clarification (2026-09-13): remains inProgress. The earlier token-budget stop was a pause on spending, not a dependency/blocker. Remaining lane-owned work is: (a) extend practicas_integrity_check() catalog-drift coverage from practicas-only to all ten table/trigger pairs, beginning with the live prosrc (migration 152 body); (b) add six COMMENT ON FUNCTION entries for shape-(C)/(D) archive functions, stating capture side and pre-update recovery by chaining; (c) retain the 15 baseline-less comisiones/periodos rows as stated residue because no write can retroactively create a baseline. Separately, replica-bypass remediation is an Elazar-owned Management API platform-config coverage item, not this row's blocker.
-
OPEN HALF CLOSED - migration 164 applied, handoff commit 345bbaa. practicas_integrity_check()'s catalog-drift arm now covers all TWELVE guarded (table, trigger) pairs instead of three practicas triggers. This is what turns 159 from a SETTING into a CONTROL: ENABLE ALWAYS is silently reset to 'O' by a plain ALTER TABLE ... ENABLE TRIGGER, DISABLE is not its reverse, and until now only practicas would have noticed - nine of the twelve could have reverted in total silence. SCOPE: the ten archive-capture triggers from 159 plus the two other practicas hard guards already watched (block_harddelete, integrity_inventory). All twelve verified present and tgenabled='A' live before writing, so the check starts from a known-clean state. trg_sync_archive_assigned_teacher_user_id remains deliberately excluded exactly as in 159 - it matches a '%archive%' name sweep but is a SYNC trigger on studentAssignmentsArchive and is correctly 'O'; guarding it would make the check fail permanently on day one. The live body was read from prosrc before editing and is from migration 152, not 033, as this row warned. A DEFECT I SHIPPED AND THEN CAUGHT BY EXECUTING RATHER THAN READING - the most valuable thing to come out of this item. The first version applied completely clean (BEGIN / CREATE FUNCTION / COMMIT, no warning at all) and then threw on EVERY call: ERROR: operator is not unique: unknown || "char". pg_trigger.tgenabled is the 1-byte "char" type, so 'tgenabled=' || t.tgenabled is an AMBIGUOUS OPERATOR rather than a coercion. The general rule, now in memory: PLPGSQL DOES NOT PARSE SQL INSIDE A FUNCTION BODY UNTIL EXECUTION, so CREATE FUNCTION succeeding proves the body is well-formed plpgsql and nothing whatsoever about the SQL inside it. Any migration that creates or replaces a function must CALL it as its last step. Fixed with ::text and re-applied; the line now carries a comment saying why. WHY THAT NEAR-MISS MATTERED MORE THAN AN ORDINARY TYPO, and why the arm is shaped the way it is: this function only writes an appEvents row WHEN SOMETHING IS WRONG. A function that throws therefore produces no alarm row at all, and ITS SILENCE IS INDISTINGUISHABLE FROM HEALTH - a broken check and a clean system look identical from outside. Had I committed on the strength of a clean apply, MARS-64's durable half would have been a dead instrument reading green forever, which is worse than the gap it was closing. For the same reason the arm uses to_regclass('public.'||quote_ident(...)) and never 'x'::regclass: the cast RAISES on a missing table, which would kill the entire check including the two inventory arms that have nothing to do with triggers. In any instrument whose failure mode is silence, reporting beats raising. SHAPE, applying 159's own lesson: the drift set is a literal VALUES list LEFT JOINed to pg_class/pg_trigger and FILTERED to only the rows that actually drifted, then array_agg'd - NOT a count compared against the list's own length, which is structurally constant and can never fire (exactly the defect coder02-mars-cc found in 159's first assertion). An absent trigger yields t.oid IS NULL and is included by the WHERE, so drop and downgrade are both caught. Output distinguishes TABLE ABSENT / TRIGGER ABSENT / tgenabled=<char>, because a dropped trigger and a silently-downgraded one need different responses, and 'D' (someone ran DISABLE) differs from 'O' (someone ran plain ENABLE). BOTH LEGS EXERCISED BEFORE THE COMMIT. LEG A, clean run: no exception, practicas-integrity-gap rows unchanged at 2 (the two historical rows from 2026-08-15) - it wrote nothing, which is the correct silent-on-healthy behaviour. LEG B, forced red in a rolled-back transaction: ALTER TABLE public."periodos" ENABLE TRIGGER trg_periodos_archive downgraded it to 'O', and the function then produced exactly drift=periodos.trg_periodos_archive => tgenabled=O in message and ["periodos.trg_periodos_archive => tgenabled=O"] in detail->'drift'. Rolled back; periodos reads 'A' again. A control that has not been seen to fail is a green line, not a control. REMAINING ON THIS ROW, and it is now small: (b) six COMMENT ON FUNCTION on the shape-(C)/(D) archive functions stating capture side and that pre-update recovery is by chaining - next migration; (c) the 15 baseline-less comisiones/periodos rows stay as stated residue, no write can retroactively create their baseline. NEITHER NEEDS ELAZAR. The one genuinely Elazar-owned item on this row is the replica-bypass remedy - removing session_replication_role from supautils' privileged_role_allowed_configs, a Management API platform-config change rather than DDL - and it is carried as a COVERAGE STATEMENT, not as this row's blocker. That distinction is why I declined db03-mars-ca's proposal to set this row to blocked: a budget pause is not a dependency, and a blockedReason that names no party and no event can never be discharged.
-
db03-mars-ca
-
LANE HANDOFF, 2026-09-13: reassigned to db03-mars-ca under the runtime switch (Elazar direct via pmmaster-evolutiva-cc - cc at 24% weekly, codex at 99%, so work moves to codex seats and cc seats stop). All 22 open Mars DB rows moved, not just this one. STATE AT HANDOFF, so the next holder does not re-derive it. (a) The catalog-drift arm is DONE - migration 164, handoff commit 345bbaa, applied and both legs exercised. All twelve guarded (table, trigger) pairs are covered; forced red produced 'periodos.trg_periodos_archive => tgenabled=O' in a rolled-back transaction and the clean run writes nothing. 164 is UNPUBLISHED and awaiting coder-mars-cc's push plus coder02-mars-cc's PASS/BLOCK on 345bbaa - that review request is already sent and is the only thing of mine still in flight on this row. STILL OPEN AND NEITHER NEEDS ELAZAR: (b) six COMMENT ON FUNCTION on the shape-(C)/(D) archive functions - fn_archiveComisionChange and fn_archivePeriodoChange (shape C: DELETE branch reads OLD, fall-through handles INSERT *and* UPDATE reading NEW) and the four shape-(D) functions for practicas, practicaImages, practicaTeeth, practicaToothConductos (rec := OLD on DELETE else NEW). Each comment should state the capture side and that pre-update recovery is by CHAINING to the preceding archive row, not from the row itself. (c) the 15 baseline-less comisiones/periodos rows stay as stated residue - all pre-trigger config, zero clinical, and no write can retroactively create their baseline. THE ONE ELAZAR-OWNED ITEM IS COVERAGE, NOT A BLOCKER: removing session_replication_role from supautils' privileged_role_allowed_configs. That is a Management API platform-config change, not DDL, and it closes the BYPASS rather than the archive hole - 432 internal constraint triggers across 38 tables remain 'O' and skippable by the same one-line SET. Do not let it be written as this row's blockedReason; it names a real party and a real event but it does not gate (b) or (c). TWO TRAPS TO INHERIT RATHER THAN REDISCOVER, both cost me real time in the last day. First: PLPGSQL DOES NOT PARSE SQL INSIDE A FUNCTION BODY UNTIL EXECUTION, so a clean CREATE FUNCTION proves nothing - 164's first version applied with no warning and threw on every call (pg_trigger.tgenabled is "char", so 'x=' || tgenabled is an ambiguous operator; it needs ::text). Any migration replacing a function must CALL it as its last step, and that matters doubly here because this function only writes when something is WRONG, so a throwing version is silent and silence is indistinguishable from health. Second: read the LIVE prosrc before editing - the deployed body was from migration 152, not 033, and the row has warned about this since the beginning. NOTE ON WRITE CAPABILITY, flagged to pmmaster rather than assumed: items (b) and (c) are production DDL. I have told db03-mars-ca that prod writes come through me, and pmmaster's handoff restated SELECT-only for both of us. If db03-mars-ca does not hold the DB maintenance-lane write exception, these rows are now owned by a lane that cannot execute them - which is precisely the 'looks actionable, can never move' shape I declined to create when I refused to mark this row blocked. Resolve that before (b) is attempted.
-
PASS:345bbaa - db02-mars-cc, pre-apply review gate (review is POST-apply here: 164 was applied 2026-09-13 before the gate existed and published as aaa7b6d; the earlier review request to coder02-mars-cc never returned a verdict). VERIFIED LIVE, SELECT-only, 2026-09-14 ~22:10 UTC: 1. Live = file. md5(prosrc) = md5 of the file's $function$ body = 9a989eb3c2b9672a8f5ac3dc653e55db, 5156 bytes both. prosecdef=false, search_path pinned public,pg_temp, ACL {postgres=X,service_role=X} - same as 152's stated ACL, so not widened. 2. The runtime-raise class is absent in prod, not just at apply: cron job 19 'practicas-integrity-check' ('0 * * * *', active) has 44 runs since 2026-09-13 03:00 UTC, 0 non-succeeded. That is stronger than the author's LEG A, which was a single call. 3. Clean state writes nothing: newest practicas-integrity-gap row is still 2026-08-15 16:00 across all 44 runs. 4. Coverage is complete TODAY: the live set of tgenabled='A' non-internal triggers in public is exactly these 12 pairs, no more and no fewer; every archive-capture trigger function (fn_archive*) is in the list; trg_sync_archive_assigned_teacher_user_id is correctly 'O' and correctly excluded. 5. FORCED RED on the two branches the author did NOT exercise. LEG B only exercised the 'O' downgrade; defect fix (1), to_regclass instead of ::regclass, was claimed and never seen to fail. The drift SELECT is pure read, so I ran the arm's exact join with extra VALUES rows: ('noSuchTable_x','trg_x') -> 'TABLE ABSENT'; ('periodos','trg_no_such_trigger') -> 'TRIGGER ABSENT'; ('studentAssignmentsArchive','trg_sync_...') -> 'tgenabled=O' (a real 'O' trigger, no DDL needed); ('practicas','trg_practicas_archive') and ('periodos','trg_periodos_archive') correctly filtered out. No exception on the missing table, so the to_regclass fix does what the header says. NOT EXERCISED BY ME: the 'D' branch (same <> 'A' path as 'O', not separately forced), and the full write path under drift (appEvents INSERT with drift payload) - that rests on the author's LEG B measured output in the MARS-64 bwi event of 2026-09-13 03:34, which I read but could not reproduce (ALTER TABLE is DDL, even rolled back). FINDINGS, all P3, none blocking: P3-1 - COALESCE(array_to_string(v_drift,'; '),'none') can never yield 'none'. array_to_string on an empty array returns '' not NULL. Live evidence: both 2026-08-15 rows read 'drift=' with nothing after it. Inherited from 152 and carried unchanged. Fix: COALESCE(NULLIF(array_to_string(v_drift,'; '),''),'none'). P3-2 - Hardcoded 12-pair list is complete today but has no tripwire: an archive trigger added by a future migration is unguarded in silence, the same narrower-axis shape 164 exists to close. Cheap closure: a fourth arm that reports any non-internal public trigger whose function is an archive-capture function but whose (table, trigger) pair is absent from the list. Otherwise the migration template for new archive tables has to carry "extend practicas_integrity_check()" as a required step. P3-3 - Dedupe is keyed on (action, level), not content. A drift that appears within 6h of an unrelated gap row (e.g. an inventory gap) is suppressed up to 6h, then reported with full current state. Pre-existing from 152, bounded, stated so "setting -> control" is read as "detected within 6h", not "detected within 1h". Rollback (re-apply 152's body) reviewed: valid, and it knowingly reintroduces the ::regclass throw.
-
Prepared migration 166 for the remaining six shape-(C)/(D) archive-function comments only: it documents DELETE=OLD, INSERT/UPDATE=NEW and preceding-row-chain recovery for comisiones, periodos, practicas, practicaImages, practicaTeeth, and practicaToothConductos. No function body, trigger, ACL, or data mutation. Awaiting independent audit and production DB access; no apply claimed.
task
2026-06-07 by wi-cli-venus
4w ago