[performance] User directory ILIKE+exact-count per keystroke; account_activity_summary unindexed & never refreshed; query waterfalls

[TheExGenesis]

Severity: Medium (cluster)

• user-dir: fetchUsers.ts:42-43,66 runs ilike '%..%' on the user_directory view + a separate count:'exact', on every 300ms keystroke and sort change; no trigram index exists. Add trigram indexes; use count:'estimated'; consider materializing the view.
• account_activity_summary matview has zero indexes (.eq('username')/.or(...) scans, twice per profile view: user/[account_id]/page.tsx:91-95, AccountTopTweets.tsx:13-17) and is never refreshed (its refresh fn was dropped in 20250912132817_verify_noop.sql:62; process_jobs only refreshes global_activity_summary) — profile "top tweets" data is frozen. Add unique index on account_id (enables CONCURRENTLY) + username; schedule a refresh or move to on-upload updates; merge the two page queries.
• waterfalls: getLatestTweets.ts runs a 7-query sequential chain per 20 tweets (re-run every 60s by stream-monitor) — parallelize / single RPC. page.tsx:115-129 and user/[account_id]/page.tsx:72,91 serialize independent awaits — Promise.all; resolve username→account_id with an exact/lower() match instead of ilike inside .or().

---

Filed from a code-quality/security/performance/robustness review. File:line refs verified against main.