PLUTO-97 ·
plutoCollapse /informes 24-query fan-out into one set-based Postgres query (view/RPC)
- Ref
PLUTO-97(#1015)- Project
pluto- Status
- done
- Priority
- normal
- Type
- task
- Assigned
- — db
- Created by
- wi-cli-venus
- Created
- 2026-06-14T02:48:15.397Z
- Updated
- 2026-06-14T04:47:01.631Z
- Closed
- 2026-06-14T04:47:01.630Z
Questions
No questions.
Event log
-
Follow-up to PLUTO-93. PLUTO-93 fixed the /informes 19s timeout by bounding the Node-side fan-out to 6 concurrent (p-limit, 5f13d10) + a practicaReviews FK index (b92a8b3). That stopped the timeout but the root inefficiency remains: the page fires 24 separate DB queries from Node against a 2-conn pool. Elazar (2026-06-13): 'work that should be done in postgres being done in nodejs' — the canonical push-compute-to-Postgres principle (evolutiva-db.md). REAL FIX: collapse the 24-query fan-out into ONE set-based Postgres query — a view or RPC with GROUP BY/aggregates returning the full informe payload in a single round-trip. db to scope: enumerate the 24 queries in src/app/(protected)/informes/page.tsx:219-346, identify the aggregation shape, design the single query/view. The N=6 p-limit stays as a safety cap underneath. Acceptance: /informes renders from <=2 DB round-trips, audit-PASS, live-version verified.
-
SHAPE LOCKED (db scope + audit sign-off, 2026-06-13). fn_getInformesBundle: plpgsql, STABLE, SECURITY INVOKER, returns jsonb. 24 queries -> 3 round-trips (bundle RPC + paginated getStudentsForTeacherPage + getAnomalyReport kept separate for single-fn risk). p_skip_catedra + p_skip_docente_productivity => IF blocks, NO compute for non-admin/non-titular (fail-safe: never materialize admin-only aggregates for unprivileged caller, not run-all-NULL). YoY prev-periodo JOIN inside RPC approved. Every CTE carries deletedAt IS NULL + isDemo=false explicit. plpgsql wrapper for plan caching. TWO HARD APPLY-TIME GATES (audit-owned): (A) CTE-by-CTE filter-parity pre-apply review of the .sql; (B) result-parity diff on real data (admin/scoped-teacher/non-admin) before cutover. N=6 p-limit stays as safety cap. DDL deferred until P0 cross-audit batch clears; db pings audit with .sql for gate-A when ready.
-
GATE-0 CATCH (audit, pre-DDL, 2026-06-13): db's proposed jtpGates predicate C used flat ANY/ANY over (jtpId, comisión), which flattens the pairs = the exact PLUTO-77 cross-comisión leak (a teacher would see other comisiones' rows for any jtpId held anywhere) AND wipes global-scope admin to zero rows. Fix: lift pairScopePredicate verbatim (zipped-unnest of the (jtpId,comisión) pairs, never flattened) + scoped-base-CTE factoring so filter-parity is provable at gate-A. db rewrites predicate C BEFORE writing the fn. Validates the pre-implementation review gate — real authz leak caught with zero SQL written. mem:pluto-pair-aware-scope-gating.
-
/informes 24-query Node fan-out collapsed to Postgres set-based fn_getInformesBundle (Elazar 'work that should be in Postgres'). 3 round-trips (bundle RPC + getStudentsForTeacherPage + getAnomalyReport). Two-gate validation: gate-A CTE filter-parity pre-DDL (caught PLUTO-77 cross-comisión leak in predicate C), gate-B fn-vs-original-20-query real-data parity diff (caught inactiveStudents wrong-count from LIMIT-before-filter + proved teacher-leak boundary zipped<flat). App rewire e017499/v1.69.31: skipCatedra/skipDocenteProductivity privilege gating at call site, PLUTO-77 zipped pair scope (not flat ANY/ANY), N=6 limiter kept as safety cap. audit PASS:e017499 live (deploy READY, version match, no runtime errors, teacher-leak verified live). fn committed 01b723a.