Problem
An outbound-sales reporting dashboard took about 15 seconds to load. The useful question was not simply how to make one SQL statement faster, but how much reporting work needed to happen each time someone opened the dashboard.
Context and constraints
The dashboard used Node.js, Prisma and GraphQL over PostgreSQL. Changes had to account for existing software dependencies and the distinction between active and historical campaigns. The final refresh cadence and a complete correctness-validation report are not retained in the available case notes.
Existing architecture
Dashboard requests depended on queries that combined campaign and lead data and calculated reporting aggregates. Repeating that work in the request path made the shape of the data and queries central to the investigation.
Investigation
I examined the data and query structure, including campaign, lead, manager and industry metrics. The retained notes compare historical, active and all-record queries before and after the changes. They informed the optimization, but are not a controlled benchmark across identical snapshots.
Root cause
The reporting workload was doing aggregation and combining data at query time. The practical bottleneck was how reporting data was prepared and queried, rather than a need to rewrite the whole backend.
Solution
I introduced materialized views and precomputed campaign and lead metrics, then revised the dashboard queries to use those results. Active and historical campaigns received filtered aggregation paths.
Architecture and implementation
CampaignMetricCombined and ActiveClient materialized views supported the new reporting structure. Manager and industry aggregation moved into prepared data, allowing the API to retrieve results with less repeated computation.
Before · work on every request
- Dashboard API
- Joins and aggregation
- Raw reporting tables
- About 15s dashboard load
After · prepare once, read efficiently
- Dashboard API
- Revised queries
- Materialized / pre-aggregated data
- About 2s dashboard load
Result
Dashboard load time fell from about 15 seconds to about 2 seconds, according to the project account. Retained query notes show historical queries at 2,204 ms versus 288 ms, active queries at 2,527 ms versus 285 ms, and all-record queries at 1,326 ms versus 384 ms. These are separate measurements; differing result counts mean they should not be treated as equivalent-snapshot correctness evidence.
Tradeoffs
Pre-aggregation exchanges work at read time for work preparing and refreshing data. Freshness requirements, refresh cost and consistency need an explicit agreement with the product team. A fast result is useful only if its meaning is preserved.
What I would carry forward
For a similar project, I would retain an equivalent-snapshot comparison, expected result counts and a documented refresh policy alongside the latency measurements. That makes both performance and correctness easier to review.
CLIENT EVIDENCE
“Completed task to perfection, understood the inner workings of our software dependencies (and willing to learn where necessary) and provided a perfect solution to speed up our software's performance times. Easy to work with and kept in contact with issues and queries.”