DB-health sweep follow-ups (from PLUTO-138, non-urgent): (1) verify practicas.idx_practicas_editlocked query path — drop if genuinely cold; (2) confirm userAuditLogLegacy table is retired → archival candidate (table+idx); (3) FK-usage audit: of 64 advisor-flagged unindexed FKs, identify which appear in live WHERE/JOIN (hot) vs audit/write-path (createdBy/deletedBy/archivedBy — leave cold), index only the hot ones via migration w/ archive-context. Don't index/drop blindly — query-path verify first.

Sub-tickets

Activity

• wi-cli-venus created · 2026-06-16
• wi-cli-venus progress · 7w ago
  App-side half done; DB-side instruments requested from db-pluto-cc (read-only, no DDL). LEG 1 idx_practicas_editlocked - COLD BY CONSTRUCTION, not merely unused-so-far. Partial index on practicas(editLockedAt) WHERE NOT NULL from migration 006. Every app read of lock state keys on practicas.id (the PK) and only PROJECTS editLockedAt - claimStudentEditLock, the detail fetch, the save path. No query in src/ or db/ uses editLockedAt as a driving predicate, and there is no stale-lock sweeper: the lock expires by comparison against now() - interval 2 hours at read time, it is never swept. So no future query shape is waiting for this index either. Awaiting idx_scan + the stats-reset timestamp to state it as a measurement over a named window rather than an argument from code shape. LEG 2 userAuditLogLegacy - the WI premise is WRONG and should not be acted on as written. It expects a retired table and therefore an archival candidate. It is frozen for WRITES only (trg_userAuditLogLegacy_forbidWrite, ENABLE ALWAYS since migration 101; UPDATE/DELETE/TRUNCATE revoked in 100) but LIVE FOR READS: migration 094 admin-user-detail bundle fn UNIONs it into the user history panel, and admin-user-detail.tsx says so in the card description. 486 rows. Archiving or dropping it silently empties the historical half of every user audit panel - invisible until someone needs it in a dispute, which is the worst time to find out. Recommend closing leg 2 negative and keeping the table. Two confirmations pending from the db lane: that no later migration dropped the legacy UNION from that fn (prosrc is authoritative; repo grep cannot answer installed state), and the index list + idx_scan on the table - if it carries a non-PK index nothing reads, THAT is the archival candidate here, not the table. LEG 3 the 64 unindexed FKs - blocked on the enumeration. PLUTO-138 close says "mostly audit/write-path" but the list is not in the WI. Once it lands I classify each app-side into hot (live WHERE/JOIN) vs cold (createdBy/deletedBy/archivedBy attribution) and hand back only the hot set as a proposed migration for the db lane.
• wi-cli-venus progress · 7w ago
  DB-side numbers in from db-pluto-cc; legs 1 and 2 answered, leg 3 classified on the app plane. LEG 1 - CLOSED NEGATIVE, drop candidate. idx_practicas_editlocked idx_scan = 0 over a 6.3-month window (stats reset 2026-02-12 23:53:40 UTC). Corroborates the code reading: no query uses editLockedAt as a driving predicate and no stale-lock sweeper exists, so it is cold by construction, not merely unused-so-far. Two independent planes agree; drop is safe whenever the db lane wants to write it. LEG 2 - CLOSED NEGATIVE, the WI premise was wrong. userAuditLogLegacy is NOT retired. fn_getAdminUserDetail is the live function and its prosrc still unions the legacy table (checked in prosrc, not the migration file - repo grep cannot answer installed state). 486 rows feeding the historical half of every user audit panel. KEEP the table. What IS an archival candidate is one index on it: of its four, idx_userAuditLog_targetUserId_createdAt (1394 scans), idx_userAuditLog_actorUserId_createdAt (848) and the PK are live, while idx_userAuditLog_relatedAccessRequestId (partial) has idx_scan 0. So the WI was pointing at a table when the dead object was a single partial index inside it - to be filed as its own small cleanup. LEG 3 - app-plane classification done, DB plane requested. Method: extracted the 211 SQL literals in src/, split each into keyword regions, resolved table aliases, and counted each FK column only in FILTER regions (WHERE/ON/HAVING, AND/OR inheriting their ancestor), never in SET/VALUES/INSERT lists. That separation is load-bearing - a naive grep counts UPDATE ... SET deletedBy = $2 and would promote every attribution column to hot. RESULT, the WI hypothesis measured rather than assumed: every attribution FK is filter-count ZERO with nonzero writes - createdBy 0/5, deletedBy 0/10, restoredBy 0/4, updatedBy 0/2, archivedBy 0/0, reviewedBy 0/1, resolvedBy, requestedBy, resolvedByUserId, editLockedByUserId all 0/0. Leave cold. PROPOSED HOT SET, and it is one coherent thing - the who-teaches-where scope joins behind resolveDataAccessScope/accessibleComisionIds: comisionAdjuntos.adjuntoId 9, comisionJtps.jtpId 9, jtpAyudantes.jtpId 3, studentAssignments.studentId 3, jtpAyudantes.ayudanteId 2, studentAssignments.jtpId 2, and academicHierarchy.adjuntoId 2 as the weakest (small table, would want a plan first). Useful negative that validates the flagged set: userRoles.userId is the most-filtered column in the whole app (11) and is NOT flagged, i.e. already covered - so the flagged 77 really is the uncovered half, not noise. STATED LIMIT: this covers the APP plane only. Pluto shapes its heavy reads in views/functions, and the app literals reference vActiveRoster / vActiveComisionJtps / vActiveComisionAdjuntos / vUserRolesActive / vPracticaParticipantFacts by name, never their base tables - so the count structurally UNDERSTATES join load on exactly those six columns. Asked db-pluto-cc to run the same predicate over pg_get_viewdef + prosrc. A column cold in both planes is a real negative; an attribution column hot in a view would falsify my leave-cold verdict, and I want that before any migration is written. TWO OPEN RECONCILIATIONS, flagged not resolved: (1) 64 vs 77 unindexed FKs. PLUTO-138 close says 64; a fresh hand-written predicate today says 77. Likely different questions (advisor output vs pg_index predicate) rather than drift, but the WI should record which is authoritative instead of carrying two counts. (2) pg_stat_statements. db-pluto-cc reports the extension is NOT enabled on this instance; PLUTO-138 close reports a 107-day pg_stat_statements window. Both cannot be true of the same database. This reaches past 139 - the PLUTO-700 queryid proof named in eq#44 assumes the extension exists. Settle before anything else leans on it.
• wi-cli-venus progress · 7w ago
  Both planes now measured on leg 3, and the attribution-cold verdict survived a real challenge - but it is SCOPED, not universal. RECONCILIATIONS SETTLED. (1) pg_stat_statements IS installed (extversion 1.11, re-queried against pg_extension). db-pluto-cc retracted its own earlier "not enabled" claim within minutes; PLUTO-138 "107d window, NOT reset" is the correct reading and nothing gated on the extension is blocked. Recording the shape as much as the fact: a lane recollecting its own instrument is not a measurement of it - the retraction came from re-querying, not from remembering harder. (2) 64 vs 77 unindexed FKs: NOT adjudicable from either lane, because the advisor predicate behind 138s 64 is not inspectable. Both numbers stay in the record with their source (advisor=64, hand pg_index predicate=77). Naming one authoritative would be a false settlement. DB-PLANE PASS CONFIRMS THE HOT SET, 6 of 7. comisionAdjuntos.adjuntoId and studentAssignments.studentId/jtpId appear in vActiveRoster ON-clauses; comisionJtps.jtpId and both jtpAyudantes columns appear in fn_resolveDataAccessScope (comisionJtps.jtpId also in fn_forbidAdjuntoJtpOverlap). academicHierarchy.adjuntoId gets NO corroboration either way - the scope functions do not reference that table at all - so it stays the deferred seventh with no plan on either side. THE CHALLENGE, AND WHAT IT COST THE VERDICT. A coarse prosrc regex surfaced 3 candidate filter-region uses of createdBy/deletedBy, which would have falsified "leave every attribution FK cold". Snippets pulled rather than assumed. Two were false positives of exactly the kind the app-plane method was built to exclude: fn_bulkImportUsers had createdBy in an INSERT column list, fn_applyParticipantChange had deletedBy as a SET target with the WHERE keyed on practicaId. The third is REAL: isUserRowSafeToSupersede contains NOT EXISTS (SELECT 1 FROM practicas p WHERE u.id IN (p.assignmentAdjuntoId, p.asistenteId, p.operadorId, p.assignmentJtpId, p.createdBy, p.complementadoPor)) - practicas.createdBy genuinely inside a WHERE. That is a methodology gap, not a stray: the app plane can NEVER see it, because the app never queries practicas by participant or attribution column. So the WI records the scoped claim - attribution FKs measured cold on the app plane; cold on the DB plane across the five named views and the scope-resolution functions; ONE exception, practicas.createdBy in isUserRowSafeToSupersede. THE EXCEPTION IS PROBABLY MOOT, WHICH IS THE NEXT MEASUREMENT. isUserRowSafeToSupersede has ZERO callers in src/ - repo-wide grep returns only migration files (062 dropped its legacy-audit check, 029 and 099 pinned its search_path, 054 noted this very IN-list as NULL-safe) and the schema listing. Asked the db lane for prosrc callers plus pg_stat_user_functions call count. Zero callers and zero calls would make it dead code, and indexing for a caller that does not exist is precisely the blind indexing this WI forbids - the finding would then be a retired safety check that has been carried through three hardening migrations, i.e. a cleanup item rather than an index item. Costing note for whoever writes the migration if it IS live: u.id IN (six columns of the same row) is an OR of equalities, so it needs a BitmapOr across six separate indexes to be used at all. Six single-column indexes on a clinical-volume table is not obviously a net win - that costing is the migration content, not the column list. MIGRATION HELD until the caller question comes back.
• wi-cli-venus completed · 7w ago
  All three legs answered on two independent planes. No index was written blind, which was the WI own condition. Execution filed as PLUTO-724 for the db lane (4 items, all reversible, wants a pre-apply audit review). LEG 1 - idx_practicas_editlocked is COLD, drop it. idx_scan 0 over a 6.3-month window (stats reset 2026-02-12 23:53:40 UTC). More than unused-so-far: cold BY CONSTRUCTION. Every app read of lock state keys on practicas.id and merely projects editLockedAt; no query anywhere uses editLockedAt as a driving predicate; and there is no stale-lock sweeper, because the lock expires by comparison against now() - interval 2 hours at read time rather than being swept. So no future query shape is waiting for it either. LEG 2 - the WI PREMISE WAS WRONG and acting on it as written would have caused data loss in the UI. userAuditLogLegacy is not retired. It is frozen for WRITES only (forbidWrite trigger ENABLE ALWAYS since migration 101, UPDATE/DELETE/TRUNCATE revoked in 100) and LIVE FOR READS - fn_getAdminUserDetail prosrc still unions it into the user history panel, 486 rows, and the UI card says so out loud. Archiving or dropping it silently empties the historical half of every user audit panel, invisible until someone needs it in a dispute. The table stays. The actually-dead object is one index inside it: idx_userAuditLog_relatedAccessRequestId, idx_scan 0, while the other three serve 1394 / 848 / 1. A WI can be right that there is dead weight and wrong about what the weight is. LEG 3 - hot/cold classified, and the method is the deliverable as much as the list. App plane: extracted the 211 SQL literals in src/, split each into keyword regions, resolved table aliases, counted each FK column ONLY in filter regions (WHERE/ON/HAVING, AND/OR inheriting their nearest ancestor), never in SET/VALUES/INSERT lists. That separation is load-bearing - a naive grep matches UPDATE ... SET deletedBy = $2 and promotes every attribution column to hot, which is the exact error the exercise exists to avoid. Result: every attribution FK measured filter-0 / write-nonzero (createdBy 0/5, deletedBy 0/10, restoredBy 0/4, updatedBy 0/2, reviewedBy 0/1, archivedBy/resolvedBy/requestedBy/resolvedByUserId/editLockedByUserId all 0/0). HOT SET, 6 columns, one coherent thing - the who-teaches-where scope joins behind resolveDataAccessScope/accessibleComisionIds: comisionAdjuntos.adjuntoId, comisionJtps.jtpId, jtpAyudantes.jtpId, jtpAyudantes.ayudanteId, studentAssignments.studentId, studentAssignments.jtpId. academicHierarchy.adjuntoId deferred as a weak seventh. Validating negative: userRoles.userId is the most-filtered column in the whole app and is NOT on the unindexed list, so the flagged set really is the uncovered half rather than noise. THE PART WORTH REMEMBERING - the app plane alone would have been unsound, and we found out by testing it rather than by reasoning about it. Pluto shapes its heavy reads in views/functions; the app literals name vActiveRoster, vActiveComisionJtps, vActiveComisionAdjuntos, vUserRolesActive, vPracticaParticipantFacts and never their base tables, so an app-only count is a lower bound by construction. Running the same predicate over pg_get_viewdef + prosrc confirmed all 6 hot columns independently (vActiveRoster ON-clauses; fn_resolveDataAccessScope; fn_forbidAdjuntoJtpOverlap) and, more importantly, CHALLENGED the cold verdict: a coarse prosrc regex surfaced 3 candidate filter uses of createdBy/deletedBy. Snippets were pulled rather than assumed. Two were false positives of exactly the kind the method excludes (fn_bulkImportUsers: INSERT column list; fn_applyParticipantChange: SET target, WHERE keyed on practicaId). The third was REAL - isUserRowSafeToSupersede has practicas.createdBy inside a WHERE IN-list - and the app plane can NEVER see it, because the app never queries practicas that way. That exception then dissolved on measurement instead of on argument: isUserRowSafeToSupersede has ZERO callers in src/ AND zero in prosrc (every fn_* body and trigger def swept). It is structurally unreachable dead code, carried through three hardening migrations nobody needed. So it is not an index candidate, it is a cleanup item, and the cold verdict stands clean on both planes with no exception. TWO INSTRUMENT FACTS FOR THE RECORD. pg_stat_statements IS installed (extversion 1.11, re-queried against pg_extension). An earlier claim in this thread that it was not enabled was retracted by its own author within minutes; PLUTO-138 "107d window, NOT reset" is correct, and nothing gated on the extension is blocked. The shape is worth as much as the fact: a lane recollecting its own instrument is not a measurement of it. The unindexed-FK count is 64 (PLUTO-138 advisor output) vs 77 (hand-written pg_index predicate, 2026-08-22). NOT adjudicable from either lane - the advisor predicate is not inspectable - so both numbers stay in the record with their source. Naming one authoritative would be a false settlement, and they are probably different questions rather than drift. COVERAGE STATEMENT. This is a static two-plane classification: app SQL literals plus view/function bodies. NOT exercised: EXPLAIN plans for any of the 6 proposed indexes (nobody has proved the planner would use them, only that the columns are filtered), write-cost measurement on the affected tables, and per-query timing. PLUTO-724 should not treat the hot list as a performance claim - it is a candidacy claim.