Harden INNER JOINs to lookupOptions that drop a live row if a status/programa lookup is ever deleted (soporte + alumnos)

Sub-tickets

Questions

Activity

• wi-cli-venus created · 2026-06-21
• wi-cli-venus note · 2026-06-21
  ORIGIN: triage of benign 404 event 626cd9b2 (admin soporte detail, hard-nav, non-reproducible). Latent fragility, NOT an active bug — current data has all lookups live, so nothing drops today. File-only hardening. PROBLEM: an INNER JOIN "lookupOptions" lo ON lo.id = <table>.<someOptionId> silently DROPS a row whose lookup target is ever soft/hard-deleted. Where that JOIN feeds a notFound() coupling, a LIVE record 404s; where it feeds a list+count, the row vanishes from the list AND the pager undercounts. INVENTORY (verified line refs, src/app/(protected)/): 1. administrar/soporte/[id]/page.tsx:63 — detail, statusOptionId → notFound() at :73. (the triage origin) 2. administrar/soporte/page.tsx:61 (LIST) + :73 (COUNT) — statusOptionId; deleted status drops ticket from list + undercounts pager. 3. administrar/soporte/actions.ts:134 — statusOptionId in a server-action query; same drop risk on whatever it returns. 4. soporte/[id]/page.tsx:55 (STUDENT-FACING) → notFound() at :61 — same shape, but a STUDENTs own ticket 404s if status-lookup deleted. (not in coders original note; found in sweep) 5. alumnos/[id]/page.tsx:274 — INNER JOIN on programaOptionId → notFound() at :281; a live student 404s if their programa lookup is deleted. (different lookup category, same shape) SAFE (swept, no action): - administrar/solicitudes/[id] + list — read vAccessRequestsForReview; the VIEW owns the join. - mi-cursada/[id] — notFound() is access/existence-based, not a lookupOptions-join drop. FIX SHAPE: LEFT JOIN + COALESCE(lo.label, lo.value, —) for the status/programa label, OR enforce lookupOptions rows backing live FKs as non-deletable (guard the soft-delete). One WI covers all soporte sites so the venus soporte clone inherits the hardened version; mirror the alumnos/programa fix too. WATCH-TRIGGER (escalate p1→ship now): if a 404 recurs for a NON-Elazar user on a LIVE ticket/record. Until then this is file-only hardening.
• wi-cli-venus note · 2026-06-21
  PRIORITY HEAD (pm-mars steer): the two USER-FACING sites have wider blast radius than the admin-only ones — fix these FIRST / elevate-first if any non-Elazar 404 ever reproduces on a live record: • soporte/[id]/page.tsx:55→notFound:61 (student-facing ticket detail) • alumnos/[id]/page.tsx:274→notFound:281 (student programaOption) Admin-only sites (admin soporte detail/list/count + actions.ts) are lower blast radius — harden in the same pass but they are not the elevate trigger.
• wi-cli-venus completed · 6w ago
  Closed as already-hardened: silent-drop vector structurally closed (FK NOT NULL + soft-delete keeps row + hard-delete FK-blocked); shipped one-line schema-invariant comment as future-FK-nullable regression guard. No authz predicate rode the joins. SHA 0072591, PASS.