Postgres-offload campaign (5 items): push report/KPI dedup+join logic into DB views

Sub-tickets — 1/1 done

Activity

• wi-cli-venus created · 2026-07-13
• wi-cli-venus note · 2026-07-13
  Item1 ayudante-split DONE (fb661b0/v0.95.17). Item1 dedup-half + item2 view-consolidation HELD on VENUS-236 dedup-grain ruling. Item3 DONE (befce2e/v0.95.16). Item4 SKIP per db-venus-cc ruling (reason not yet relayed). Items 5-6 skip/optional. coder-venus-cc idle pending VENUS-236 dedup-grain ruling.
• wi-cli-venus note · 2026-07-13
  VENUS-236 dedup-grain switch SHIPPED — SHA 6aeb5ac/v0.95.19, audit PASS. All 9 app SQL sites + 4 DB views (migration venus_scoring_dedup_grain_practicaid) now use COUNT(DISTINCT practicaId). Bonus fix: NULL-pacienteDni collapse in vKpiAdmin/vKpiTitular (78 rows). item1-dedup-half subsumed (getStudentKpis was already conformant). item2 (getStudentKpis→vScoringByAlumno consolidation) queued as separate follow-up push per coder's proposal.
• wi-cli-venus assigned · 5w ago
  db-venus-cc
• wi-cli-venus progress · 5w ago
  ROW IS OUT OF DATE - re-measured live 2026-09-11 (db-venus-cc, verify-first). The description says items 1 and 2 are HELD pending VENUS-236 and that item 5 is not started. VENUS-236 IS CLOSED DONE, so the hold is released, and items 1 and 2 are largely BUILT, not held. ITEM 1 (vPracticaScoringFacts base view) - SHIPPED AND CONSUMED, under the name public."vScoringByAlumno". My first instrument was wrong and I am recording the correction rather than the first answer: grepping src/ returned zero call sites and reads as an orphan view, but a view consumed by ANOTHER VIEW is invisible to a source grep. The catalog is the right instrument (pg_depend joined through pg_rewrite to pg_class). Measured chain: vScoringByAlumno -> vKpiAlumno -> vKpiCohorte, and vKpiAlumno is read from 2 files in src/. Item 1 is live and load-bearing. report-actions.ts:77 documents its dedup contract ('vScoringByAlumno now dedupes on p.id', the bilateral-exodoncia grain that VENUS-236 was escalated to settle). ITEM 2 (getStudentKpis consolidation) - PARTIALLY SHIPPED, and the unshipped half is the finding. Five KPI views exist: vKpiAlumno (2 src consumers), vKpiCamada (1), vKpiCohorte (1, via vKpiAlumno), and then vKpiAdmin and vKpiTitular WITH ZERO CONSUMERS OF ANY KIND - no src call site, and no dependent view in pg_depend. They appear only in db/schema.md and in venus56-isdemo-eradication (a migration that merely re-created them while removing isDemo). They are built DDL that nothing reads. Either the consolidation for the admin and titular dashboards was never wired to them, or the wiring was removed; the offload for those two surfaces has NOT happened, whatever the view list suggests. This is the exact shape the fleet rule warns about - an object existing is not the offload landing. ITEM 3 (soporte-cron selection SELECTs -> candidate views) - CONFIRMED SHIPPED, unchanged from the description. All four exist live and each has exactly one src consumer: vSupportTicketSlaCandidates, vInactiveAlumnoReminderCandidates, vTitularDigestCandidates, vAdminDigestCandidates. ITEM 4 (CSV/PDF export views) - NOT SHIPPED. No export view exists in public (pg_views, no name matching Export), and no src export module references one. 'In progress' overstates it as of today. ITEM 5 - not started, unchanged. STATE OF THE CAMPAIGN, corrected: item 1 done, item 3 done, item 2 done for alumno/camada/cohorte and NOT done for admin/titular, item 4 not started, item 5 not started. Moving backlog -> todo so the row reflects that it is live work with a released hold rather than something parked behind an escalation that has since been answered. The next concrete piece of work on it is deciding whether vKpiAdmin and vKpiTitular get wired or dropped - two orphan views on a live clinical DB should not be left as ambiguous evidence of a completed offload, and that decision is the code lane's (which surface reads what), not mine alone.
• wi-cli-venus statusChanged · 5w ago
  Re-measured: VENUS-236 hold RELEASED (closed done). Item1 shipped+consumed (vScoringByAlumno->vKpiAlumno->vKpiCohorte, via pg_depend not grep). Item3 shipped, 4 candidate views each with a consumer. Item2 HALF done: vKpiAdmin + vKpiTitular have ZERO consumers. Item4 not shipped (no export view exists). Item5 not started.
• wi-cli-venus note · 5w ago
  CAMPAIGN DISPOSITION, db-venus-cc 2026-09-11, verify-first against the running system and the tracking doc rather than this row's description. All five items are now resolved or split out. Closing. THIS ROW'S DESCRIPTION WAS STALE IN THREE PLACES and that staleness is why the campaign read as half-finished for two months: (i) 'Item1 + item2 HELD pending VENUS-236 product ruling' - VENUS-236 has been status=done since 2026-07-13 10:01, the same day the tracking doc was written. The hold was released two months ago and nothing moved because the release was never reflected here. (ii) 'Item4 (CSV/PDF export views) in progress' - the tracking doc rules item 4 SKIPPED, decided 2026-07-13 and verified live by me. (iii) 'Item5 not yet started' - the doc downgrades item 5 to optional/skip, with reasons. ITEM-BY-ITEM, measured: 1. Bucket-mismatch fix (comoAsistente vs split ayudante buckets): SHIPPED. Read the live code - src/db/queries.ts:465 getStudentKpis now returns comoOperador, comoAyudante1, comoAyudante2, comoObservador and comoSinRol as separate buckets. The collapsed comoAsistente the doc §1 describes is gone. 2. getStudentKpis consolidation onto the existing fact views: THE SOLE REMAINING OPEN ITEM. Split out as VENUS-402 (child of this row) with a concrete spec and the measurements behind it, including a correction to a tracking-doc claim about vScoringByAlumno's dedup grain that the consolidation depends on. 3. soporte-cron selection SELECTs -> vSupportTicketSlaCandidates / vInactiveAlumnoReminderCandidates / vTitularDigestCandidates: SHIPPED befce2e / v0.95.16, audit PASS audit-venus-ca-mriz01ueebvq, live-verified, number-preserving by construction. 4. CSV/PDF export reporting views: SKIPPED on measured grounds, not deferred. getAlumnoHistorial already rides vPracticasCompletas so there is nothing to offload; getSedeRoster is single-caller with its own grain; the CSV loadRows path cannot use vPracticasCompletas as a base because the grains differ (práctica-grain with jsonb_agg participantes vs the flat participation-grain rows CSV needs), it is missing pacientes.nombreCompleto, and vPracticasCompletas INNER-joins anestesia where CSV LEFT-joins. The only candidate view would centralize a 9-way join with no predicate and one caller. Reopen only if a second consumer appears. 5. fn_practica_detail / fn_practica_edit_snapshot: SKIP. getPracticaById and getPracticaForEdit are ALREADY single round-trip queries - the participant array is assembled by a correlated jsonb_agg subquery inside the same SELECT, not stitched in TS. Wrapping them would centralize join text without reducing round-trips or fixing any consistency bug, and each has exactly one caller. 6. appEvents health-snapshot: CLOSED, stays in TS, blocked by audit on a real design ground worth preserving. The admin health page is deliberately partial-tolerant - each metric has its own try/catch and degrades to null independently - and a single consolidated function makes the snapshot all-or-nothing, a regression on an observability surface. Splitting it back up to preserve partial-tolerance erases the savings. Promise.all already runs the 8 queries concurrently, so the only win was fewer connections, not latency. SIDE FINDING, recorded because it is the kind of thing nobody looks for twice. public."vKpiAdmin" and public."vKpiTitular" have ZERO consumers in src/ and scripts/, and zero dependent views in pg_depend. That is NOT a recommendation to drop them and I am not making one: pg_stat_statements (entries back to 2026-05-15) shows they ARE read - a vKpiTitular-shaped SELECT with 8 calls, SELECT * FROM vKpiAdmin with 3, plus a couple of COALESCE(SUM(...)) aggregates. Low double-digit calls over four months, with shapes that look like agent or analyst verification rather than application traffic, and both views are granted to evolutiva_agent_ro/evolutiva_agent_rw - reads that leave no trace in the repo. So: no app consumer, but a live if sparse human/agent surface. Absence from src is not absence of use, and dropping them on a source grep alone would be exactly that mistake. Their siblings vKpiAlumno (3 files), vKpiCamada (2) and vKpiCohorte (2) do have app consumers. If someone wants these two gone, that needs an owner's decision, not an inference. ALSO: vPracticaScoringFacts, the view the PRIOR report asked for, was never created and does not exist on prod - correctly, because doc §2 established the fact view already exists under the name vScoringByAlumno. No orphan was left behind. NET: 2 of 5 items shipped (1 and 3), 3 ruled out on measured grounds (4, 5, 6), 1 split out live as VENUS-402. The campaign reached a decision on every item, which is what closes it.
• wi-cli-venus completed · 5w ago
  All 5 items disposed: items 1+3 shipped, items 4+5+6 ruled out on measured grounds, item 2 split out as VENUS-402 (open, Class-A). Row description was stale on three points - VENUS-236's hold released 2026-07-13, item 4 skipped not in-progress, item 5 downgraded not unstarted.