Votern

Problem

The Areas page renders a hierarchical rollup (region → province → city → barangay) over the full PSGC tree — 42,010 barangays, ~43,768 nodes. Every load needs population, voters, expenses, and staff counts per visible row, scoped to whatever subset of the tree the logged-in member can access.

The initial implementation computed this live on every request: a BFS walk to resolve the member's access scope, then 5 unindexed GROUP BY queries fanned out across every psgc id in scope. When users spam-refreshed the Areas page and its nested area pages, Postgres CPU spiked to ~800%, since each refresh re-triggered the same expensive tree walk + fan-out from scratch. A materialized-view rewrite didn't fix the root issue: materialized views can't be refreshed for a filtered subset, only the entire relation — one org's data change meant refreshing (or going stale) for every tenant.

Solution

Replaced the live fan-out and the materialized view with a Redis caching layer with targeted, keyed invalidation, sitting in front of a single recursive-CTE rollup query:

• •Per-metric cache keys scoped to (organizationId, psgcIds, metric), TTL 1h — instead of one global view/query shared across all tenants.
• •A separate access-scope cache (accessible-psgc:{memberId}, 1h TTL) and PSGC-descendant cache (24h TTL, since the geo tree rarely changes) avoid re-walking the tree on every request.
• •Mutations invalidate only the ancestor chain of the affected geographic node, not the whole org — cut a single-voter mutation from ~300 keys cleared down to ~1.
• •A BullMQ worker on a recurring cron schedule (hourly) proactively re-warms the cache instead of waiting for the next user request to hit a cold key — keeping the Areas page consistently fast while also acting as a backstop against any missed invalidation path.
• •A manual refresh button and post-deploy cache warming for the descendant cache cover the remaining cold-start cases.
• •Redis failures fall back to a live DB query, so the cache layer degrades gracefully rather than breaking access control.

ResultRepeated refreshes now hit warm cache instead of recomputing a 42k-node tree walk per request, eliminating the CPU spikes.