Memoize getUserProgramas + collapse per-request lookup fetches (CPU/round-trips)

Sub-tickets

Activity

• wi-cli-venus created · 7w ago
• wi-cli-venus titleChanged · 7w ago
  Memoize getUserProgramas + collapse per-request lookup fetches (CPU/round-trips)
• wi-cli-venus note · 7w ago
  SCOPE REWRITTEN on measurement. My original description was wrong about the driver and I am recording that rather than quietly editing it. WRONG: it blamed getLookupOptions and ranked periodos above lookupOptions. periodos has 3 live rows and reads 1.55 tuples/scan — 10.2M tuples total vs lookupOptions' 830M, i.e. 1.2%. I ranked by seq_scan COUNT; the cost is tuples read. periodos is dropped from scope entirely. ALSO WRONG: the lookup helper family is ~69k calls / 124s of exec time over the 72-day pg_stat_statements window (getLookupOptionId 37,463 / getLookupOptions 24,040 / getMultipleLookupCategories 5,219 / getLookupOptionById 2,317). Scaled to the 192-day table window that is ~184k calls against 4,675,463 seq scans — about 4 percent. The other ~96 percent is JOIN lookupOptions inside unrelated queries. Fixing the helper layer could not have moved the counter I cited. ACTUAL SCOPE, both measured: 1. getUserProgramas (src/lib/user-programa-scope.ts) — 21,566 calls, 19.0ms mean, 409.7s total, no cache() wrapper despite 6 call sites, and it JOINs BOTH lookupOptions and periodos. Largest single app-side item. Watch the key: it takes RoleValue[], and React cache() keys on argument identity, so a fresh array literal per call site never hits — the same latent bug already in getMultipleLookupCategories. 2. Collapse the per-category fetches into one un-argumented getAllLookupOptions() under cache() (all 194 rows = 120kB) and slice by category in JS. Turns the 12 uncached getLookupOptionId/ById sites into in-memory lookups. Deliberately NOT unstable_cache: it persists across deploys, so a db-mars-cc lookup seed would silently not appear. NOT DOING: no index on lookupOptions.category. At 194 rows / 120kB the planner will keep choosing a seq scan, correctly; the table already serves 13.5M idx_scans for its id lookups. An index here is dead code. Realistic saving ~450s of DB time per 72 days. Real, worth doing, and an order of magnitude below the connection-churn item now split out to its own WI.
• wi-cli-venus completed · 7w ago
  PASS:77a3243 deep from audit-mars-ca (depth verified independently: deep/CHECK=2c/SAMPLED=yes, so full deep review regardless). PTD confirmed: live /api/app-version = 2.74.26, dpl_2uWPXbTp3XRWMo7Aptw6PyLPPtri, matched against the REMOTE package.json on main read at check time (also 2.74.26), never the local checkout. Shipped: getUserProgramas memoized on userId ALONE with the fullAccess short-circuit kept outside the cached unit (caching a permission decision under a data key would let one call's roles decide a later call's answer for the same user); all lookup reads collapsed onto one unfiltered cached fetch of the 194-row vocabulary, sliced in memory, turning the 12 getLookupOptionId/ById sites into zero-round-trip lookups. Not unstable_cache: it survives deploys, so a db-mars-cc lookup seed would silently not appear. Audit confirmed active-view filtering, ordering, category slicing, empty-category [], id lookup and duplicate-category behaviour all preserved, and that no caller mutates the shared arrays. DISCLOSED LIMIT, carried forward rather than closed over: the round-trip reduction is NOT covered by a call count. react cache() only dedupes inside a request scope and vitest provides none, so a count assertion would have measured the harness. The 8 tests assert SQL shape instead (one unfiltered read, no category filter, no bound params), which does fail against the pre-fix code; audit ruled that valid unit-level evidence. The saving itself is therefore argued from the query shape, not measured in prod — re-read pg_stat_statements after a few days if the number matters. Latent bug found by reading, not by test: getMultipleLookupCategories was cache()d on a string[] for its whole life and never hit once, since every call site passes a fresh array literal. No test could have found it.