P3c comparative-stats /informes/stats (alumno ranking + cohort agg, titular-scoped)

Sub-tickets

Activity

• wi-cli-venus created · 2026-07-02
• wi-cli-venus note · 2026-07-02
  Shape ratified 2026-07-02. 2 PG views v_stats_alumno + v_stats_camada (push-compute-to-PG). Metrics: prácticas count, roles mix, cirugías-únicas breadth, complicaciones-rate (PROVISIONAL, 89%-pending caveat until KPI-rework), antecedentes-completeness, staleness. Answers: A1 exclude isDemo+demo_seed; A2 use real cohort field or label módulo (no invented camada); A3 complicaciones provisional-in-UI. GATE: titular sede-scope = security predicate → Class-A: invariant note + audit pre-impl ping + diff review before push (cross-sede leak risk).
• wi-cli-venus note · 2026-07-02
  Audit pre-impl review: activity-scope RATIFIED over membership-gate — titular sees each alumno's numbers restricted to prácticas in the titular's OWN sedes (honest containment + no cross-sede aggregate leak). Impl: SECURITY DEFINER RPC parameterized on viewer sedes, aggregation-time filter (practicas.sedeId ∈ viewer_sedes inside SUM/COUNT); REVOKE view SELECT from app roles → RPC is sole read path (containment in the grant). Admin = no filter. Findings A(DEFINER-RPC sole path)/C(complicaciones total+resolved split, provisional footnote)/D(deletedAt on all 4 tables)/E(DDL=db-venus) accepted.
• wi-cli-venus note · 2026-07-02
  Finding-A RE-DECIDED (supersedes DEFINER-RPC/REVOKE): venus runtime = service-role → auth.uid() NULL, service_role bypasses grants, so a DB-level REVOKE guarantee is unachievable in venus's gate-based (non-RLS) model. Option 2 accepted: extend orphan vKpiTitular (activity-scope by construction) + single mandatory getStatsForViewer helper as SOLE read path; containment = single-helper discipline + audit grep-verify of orphan-status at pre-push. Conditions: server-side viewer_sedes only, header-comment invariant, titular-cohort=rollup of sede-filtered vKpiTitular (never admin vKpiCohorte), deletedAt+isDemo predicates. Tradeoff logged: true DB-level containment would need least-priv reader role+pool — rejected as overkill for one surface; revisit if stats-read surfaces multiply.
• wi-cli-venus note · 2026-07-02
  SCOPE FORK RESOLVED → OPTION B (global). Coder found activity-scope premise contradicts shipped app: canViewPractica grants titular blanket access, getReporteDataset scopes by URL-param only, titularSedes INTENTIONALLY EMPTY (WI#67 fix dropped the join to kill a zero-rows bug), specs 05-10/05-18/06-02/v2 all say titular=GLOBAL. Only the 06-03 DRAFT says sede-scoped. Decision: P3c ships GLOBAL to match every current titular surface; scoping in isolation would reintroduce WI#67 zero-rows bug. Finding-A/B security design COLLAPSED (no viewer_sedes/DEFINER/single-helper). Findings C/D retained. The 06-03 sede-scoping direction escalated to Elazar as a SEPARATE app-wide containment question (would touch /informes+/practicas+KPI+exports+titularSedes seeding), NOT a P3c blocker. /informes/stats is a redirect stub; P3c extends inline ClinicalStatsSection.
• wi-cli-venus completed · 2026-07-02
  P3c comparative-stats: 3 inline Sections in /informes ClinicalStatsSection (alumno leaderboard, cohort compare, módulo×periodo), global staff-gated, reads vKpiAlumno/Cohorte/Camada. 0cc604f/v0.69.1, audit PASS. Titular scope stayed GLOBAL matching app; 06-03 sede-scoping escalated as separate app-wide question.
• pmmaster-evolutiva-cc note · 1w ago
  INCIDENT 2026-10-06 10:29 ART: db-venus-cc ran psql -f on the migration directly; the file's own BEGIN/COMMIT committed the 10-qual ALTER POLICY rewrite to prod with no apply GO in existence. Self-caught by immediate readback, reverted in a second transaction to byte-identical bare-form quals, both transcripts preserved, no txid captured. relrowsecurity false on all 5 tables throughout, so no caller-visible effect at any point. audit-general-ca HOLD accepted (their connector reaches enamel only); independent SELECT-only readback routed to audit-venus-ca. Comment-only correction applied, file now sha256 2b5d5e38 (stale 24/14-untargeted prose matched to the executable 14-total/4-untargeted checks). No apply GO until the readback lands. PM ruling: every future dry run of a self-committing file runs under SET default_transaction_read_only=on issued BEFORE the first statement, or against a scratch DB - a wrapper BEGIN/ROLLBACK cannot survive the file's own COMMIT.
• pmmaster-evolutiva-cc note · 1w ago
  PARKED by PM 2026-10-06. relrowsecurity is FALSE on all five tables, so the initplan qual rewrite has no live benefit and there is no reason to apply it today. No apply GO sought or issued. File committed at adff953 (content digest fe14fcbc45bd7cc36ad2c73bc89e49905ce3c05c7d23a67f07edd55e86ef46b9) so the bytes are git-addressable and the next revision is a diff rather than a fresh hash. GATE TO UNPARK: RLS actually being enabled on those five tables - the apply question returns with that work and not before. Incident forensics closed as UNVERIFIABLE-BY-CONSTRUCTION: no committed export carries policy quals at any SHA (VENUS-472 p1 filed to fix that forward) and the migration was untracked until today, so no historical byte witness exists or can be made. Live state independently verified by audit-venus-ca: 14/14 policies present, 10 targeted quals back to bare fn_isadmin()/fn_istitular(), 4 ownership quals present and outside the statement set, RLS/force-RLS and metadata anomaly checks zero.
• pmmaster-evolutiva-cc blocked · 1w ago
  parked: no live benefit until RLS is enabled on the five tables; see PM ruling 2026-10-06
• pmmaster-evolutiva-cc unblocked · 1w ago
  cleared: parked: no live benefit until RLS is enabled on the five tables; see PM ruling 2026-10-06
• pmmaster-evolutiva-cc note · 1w ago
  CORRECTION, PM error 2026-10-06: the two preceding events on this row (the RLS initplan incident note and the PARKED ruling) DO NOT BELONG HERE and this row was wrongly blocked for ~1 minute, now unblocked. 'venus93' is the MIGRATION FILENAME venus93-rls-initplan-rewrite.sql, not a bwi ref; I read it as this ticket id. This row is P3c comparative-stats /informes/stats and is untouched by any of it - no RLS work, no incident, no park. Disregard both events. The RLS incident and park belong to the row that actually tracks that migration.
• coder-venus-cc completed · 1w ago
  Restored: completed 2026-07-02 (0cc604f/v0.69.1, audit PASS, /informes/stats exists in tree). Status was reset to todo only by pmmaster's 2026-10-06 mis-block/unblock (venus93 migration filename misread as this ref, per its own correction note). No work outstanding.