▣ wi

Pluto P3: 7 of 21 fn_archive* functions use explicit VALUES (not to_jsonb) — MARS-78/88 dropped-column-in-trigger latent class; convert to to_jsonb, prioritize fn_archivePracticaDocenteNotaChange (8 domain cols)

Ref

PLUTO-34 (#898)

Project

pluto

Status

done

Priority

low

Type

task

Assigned

—

Created by

wi-cli-venus

Created

2026-06-10T07:53:51.326Z

Updated

2026-06-12T07:23:11.180Z

Closed

2026-06-12T07:23:11.180Z

Sub-items

Questions

Event log

• 2026-06-10T07:53:51.326Z · created · wi-cli-venus
• 2026-06-10T07:54:01.726Z · note · wi-cli-venus
  Found in Mars round-2 delta-re-audit (db lane) at 3e1df2c — round-1 misclassification, NOT introduced by migs 011-015. 7 of Pluto's 21 fn_archive* functions still use explicit VALUES column lists instead of to_jsonb(NEW/OLD). Same latent class as MARS-78/88 / PLUTO-18: if a domain column is ever dropped/renamed, the explicit-VALUES trigger breaks (the getAuditLog-class failure). Currently LATENT — none of these 7 fns' tables were touched by 011-015, so no active break. Worst: fn_archivePracticaDocenteNotaChange (8 domain cols) — convert first. Fix = same to_jsonb(NEW)/to_jsonb(OLD) pattern PLUTO-18 applied to practicasArchive + accessRequestsArchive triggers. db lane. Does NOT gate go-live (Mars verdict). Report: /gdrive/projects/evolutiva/pluto/hardening/round-2-db.md. Joins P3 backlog (25/27-31); drain timing is Elazar's call.
• 2026-06-12T06:54:42.880Z · note · wi-cli-venus
  CONFIRMED LIVE 2026-06-12 (db-pluto-cc): 7 of 21 fn_archive* use explicit VALUES column lists (brittle, MARS-78 class): fn_archivePracticaDocenteNotaChange (8 domain cols, convert first), fn_archive_practicaAnestesias, _practicaAntecedentesPersonales, _practicaCaracteristicasPieza, _practicaComplicacionesIntraoperatorias, _practicaDiagnosticosRadiograficos, _practicaTecnicasQuirurgicas — all practica-detail family. Other 14 already use to_jsonb (safe). Cross-app context: venus verified CLEAN (row_to_json full-row), Mars swept all 21 in MARS-88. Pluto is the only one of the three still carrying the time-bomb. PM recommendation: ELEVATE from P3/backlog given (a) Elazar's 'i want it fixed' directive this session and (b) Pluto actively does column migrations on trigger-archived tables (the appEvents reshape this session is exactly the class of change that detonates these). Fix = convert all 7 to to_jsonb(NEW)/to_jsonb(OLD) into rowBefore/rowAfter, same pattern PLUTO-18 used. db lane; non-destructive fn replacement, audit pre-review since it touches archive triggers on fraud-adjacent practica data.
• 2026-06-12T07:13:25.859Z · note · wi-cli-venus
  SCOPE GREW + de-risked to EXPAND/CONTRACT (2026-06-12). db found the 7 brittle archive tables have FLAT column schemas (not rowBefore/rowAfter jsonb), so defusing the bomb needs a schema change, and the original single-shot 016 included an IRREVERSIBLE flat-column DROP on fraud-adjacent audit history. Re-cut: - PLUTO-34 (expand, this WI): ADD rowId/rowBefore/rowAfter to the 7 tables + backfill the 4-61 historical rows/table (jsonb_build_object from flat cols) + CREATE OR REPLACE the 7 fns to to_jsonb(OLD/NEW). Non-destructive, reversible (backup + flat cols retained). Fully defuses the time-bomb (triggers become column-agnostic). Migration 016 re-cut expand-only; audit pre-apply review focuses on backfill capturing EVERY flat col (zero data loss) + faithful trigger rewrite + backup restorable. - PLUTO-65 (contract, deferred): DROP the flat columns — irreversible — applied only AFTER the expand's jsonb archiving is verified live (a real archive write lands in rowBefore/rowAfter + backfill confirmed). No rush; dead flat cols are harmless until then. Elazar looped on the scope growth + offered the full-one-shot alternative; proceeding expand-first unless he redirects. Shared-tree: db's push coordinates with coder's PLUTO-64 sweep (pathspec-stash if coder's tree is dirty at push time).
• 2026-06-12T07:17:42.607Z · note · wi-cli-venus
  INCIDENT + recovery (2026-06-12, expand apply): db applied the expand-only 016 to prod at 04:14 under audit's stage-1 PASS (which had reviewed the prior WITH-DROP version). The re-cut KEPT the flat columns, leaving flat id NOT NULL (no default) on all 7 tables while the new to_jsonb triggers stopped writing flat id → next real archive write would 500 on a NOT NULL violation, breaking every práctica save touching the 7 child tables. Audit caught it post-apply (BLOCK:016) within ~1min. db immediately patched live: ALTER COLUMN id DROP NOT NULL on all 7 (via Supabase MCP, DDL-only, no txn), verified nullable via information_schema, migration file updated to match live. Exposure window (~1min) appEvents = ZERO errors — no user was active, no harm. PROCESS LESSON: a re-cut/re-scoped migration is NOT covered by a prior audit PASS even when it looks like a strict subset — keeping the flat cols changed the end-state (constraint surface). PM (me) wrongly authorized apply 'under the old PASS'; correct flow = re-cut re-enters audit PRE-APPLY before apply. Net: expand is live + correct; remaining = db:export + audit live==file confirm + push + PTD; flat-col DROP deferred to PLUTO-65.
• 2026-06-12T07:23:11.180Z · completed · wi-cli-venus
  Archive-fn brittleness fixed via expand/contract. 7 fn_archive* rewritten to to_jsonb(OLD/NEW) column-agnostic pattern; rowId/rowBefore/rowAfter added + backfilled on 7 archive tables; flat id NOT NULL relaxed. Migration 016 applied live + repo-committed a2084d7. Audit PASS:016-patched (exposure-window clean) + PASS:a2084d7 (deploy READY, live v1.67.29 deploymentId match, zero runtime errors, schema.md staleness closed). Contract DROP deferred to PLUTO-65.