MARS-68 Audit Next.js/Node compute that should be pushed to Postgres (aggregations/filters/joins done in JS that belong in SQL or a view)

MARS-68 · mars

Audit Next.js/Node compute that should be pushed to Postgres (aggregations/filters/joins done in JS that belong in SQL or a view)

Ref: MARS-68 (#819)
Project: mars
Status: inProgress
Priority: normal
Type: task
Assigned: — db
Created by: wi-cli-venus
Created: 2026-06-09T03:00:37.627Z
Updated: 2026-06-12T05:56:39.110Z

Sub-items (2/2 done · 100%)

ref	title	status	priority	assignee
MARS-73	Rewrite teacher-practicas CTE: kill seq-scan on practicas (18.4% DB time) — LATERAL/direct JOIN from studentAssignments, est. 60-70% wall-time cut. Sites: weekly-kpi.ts + reporting.ts. See db/pg-stat-statements-report.md	done	normal	—
MARS-74	Cache unread-notifications count (5.1% DB time, 41k calls): SELECT count(*) FROM notifications WHERE userId AND readAt IS NULL fires every page load — counter column on users or 30s sessionStorage TTL. See db/pg-stat-statements-report.md	done	normal	—

+ Add sub-item

Questions

No questions.

Event log

2026-06-09T03:00:37.627Z · created · wi-cli-venus
2026-06-12T05:56:35.224Z · commented · wi-cli-venus

Audit complete (db02-mars-cc, 2026-06-12). No correctness bugs. 8 findings ranked by impact: HIGH: #1 N+1: resolveCanonicalTcmTeachers per import row (administrar-actions.ts:1305) — 200-student import fires 200 queries instead of 3. Fix: batch all unique comisionIds before the loop. Coder-only change. #2 N+1: sequential logEvent per audit row (administrar-actions.ts:1328-1355) — 200+ sequential INSERTs after import. Fix: new logEvents() batch helper. Coder + DB review. MEDIUM: #3 Roster role-split in JS (comisiones/[id]/page.tsx:143-147) — 4x scan over already-fetched roster. Fix: structured return from getComisionRoster. Coder-only. #4 Linear .find() for periodoId in import loop (administrar-actions.ts:1294) — trivial, fix with comisionById Map. Coder-only, bundle with #1. #5 Orphan-student Set dedup (comisiones/[id]/page.tsx:153-158) — stems from vComisionStudentsByJtp UNION emitting a NULL-jtp row for ayudante-only students. View fix: add AND (sa.assignedTeacherUserId IS NOT NULL OR sa.ayudanteUserId IS NULL) to Branch 1 WHERE clause. DB-lane pre-fix (no app code change needed; JS dedup becomes a no-op but stays harmless). Confirmed safe. LOW: #6 TCM allCount JS reduce (student-assignment-tcm.ts:96) — 2-row dataset, trivial. #7 weekly-kpi.ts .find()/.filter()/.reduce() on pre-aggregated audience — small dataset, no action urgency. #8 alumnos-table.tsx client-side comision dedup for dropdown — appropriate for client-filtered table. Dispatch order: #1+#4 (coder, one PR, wait for tree to free) → #2 (coder + new logEvents helper) → #5 (db lane view fix, greenlit by PM, can run now) → #3 → #6-8 backlog. Findings file: mars/db/mars-68-compute-pushdown-findings.md
2026-06-12T05:56:39.110Z · statusChanged · wi-cli-venus

Audit done — #5 view fix greenlit (db lane); #1+#4 coder dispatch pending tree free
2026-06-12T06:02:39.331Z · commented · wi-cli-venus

#5 (a) DB view fix applied and verified (vComisionStudentsByJtp Branch-1 NULL-jtp suppression for ayudante-only students). Schema docs re-exported, riding coder02's next push. #5 (b) remaining: remove now-no-op JS orphan-dedup Set in comisiones/[id]/page.tsx:153-158 — deferred as low-pri cleanup, no correctness impact (Set is a no-op post view fix).