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

Sub-tickets

Questions

Activity

• wi-cli-venus created · 6w ago
• wi-cli-venus note · 6w ago
  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 · 6w ago
  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 · 6w ago
  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 · 6w ago
  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 · 6w ago
  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.