How we built agent analytics without melting Postgres

Early SaaS dashboards often cheat: fetch every row, aggregate in JavaScript, hope the customer doesn’t have more than a few thousand messages yet. That works until someone selects “All time” on a account with four years of traffic and their laptop fan sounds like a jet engine.

Pengo’s analytics page is intentionally boring in the best way - the browser requests summaries, not raw tables.

The summary endpoint

GET /api/analytics/summary accepts timeRange (1d, 7d, 30d, 90d, all) and an optional propertyId. The handler:

1. Resolves which properties the authenticated user owns or belongs to - never trusting a client-supplied UUID blindly.
2. Pulls chats in-range with assigned_to for agent attribution.
3. Pulls messages for those chats in one query (still bounded by the date filter).
4. Computes response times by walking each chat’s message timeline visitor→agent pairs.
5. Builds daily chart buckets with DATE_TRUNC semantics in application code for portability.
6. Joins agent profiles and CSAT ratings for per-agent breakdowns.

Everything returns in one JSON payload: stats, chart series, top properties, and agentStats.

Agent productivity, not vanity metrics

The agent table answers questions managers actually ask:

Column	Meaning
Conversations handled	Distinct chats where the agent sent at least one message
Resolved	Chats in resolved status assigned to that agent
Messages sent	Raw agent message count (excludes system events)
Avg msgs/chat	Quick read on thoroughness vs. one-line closes
Avg first response	Time from first visitor message to first agent reply
CSAT	Average rating where the agent resolved the chat

We sort by conversations handled descending - the people doing the work float to the top.

Charts with Recharts

We replaced hand-rolled div bars with Recharts BarChart inside ResponsiveContainer. Why? Accessibility tooltips, consistent axes, and less custom CSS when date ranges change from 7 to 90 days.

The chart data is pre-aggregated server-side as { date, conversations, messages }[]. The client only formats dates for display (MMM d).

CSV export two ways

Product teams always ask for export. We ship both:

1. Server endpoint - GET /api/analytics/export reuses the summary API internally and streams text/csv with a Content-Disposition attachment header. Good for scheduled reports and large datasets.
2. Dashboard button - calls the export endpoint and downloads the blob. Same columns as the on-screen table.

Columns include agent productivity fields so finance and ops don’t need to re-merge spreadsheets.

CSAT tab stays separate

Customer satisfaction gets its own tab with distribution bars, satisfaction rate (% rated 4–5), trend sparkline, and per-agent CSAT. It pulls from /api/analytics/csat so heavy rating joins don’t slow the overview page.

Performance guardrails

• Property ID filter always intersects with the user’s accessible set.
• Previous-period comparisons use parallel count queries with head: true where possible.
• Agent stats derive from in-memory maps over the already-fetched message set - no N+1 query per agent.

We’re not at warehouse scale yet. When we get there, the summary route is the seam where we’d drop in pre-computed rollups without changing the dashboard contract.

What you should expect as a customer

Open Analytics, pick a property, switch between 7 and 30 days - numbers and charts update together. Hit Export CSV - you get the same agent productivity table you’d see if you screenshot the page, but usable in Excel.

No “upgrade to Enterprise for basic reporting.” Metrics belong on every plan because you can’t improve what you can’t measure.