Reporting and activity

Most views prefixed vm_ are materialised. If a report looks stale, check the refresh schedule for its view before suspecting the route. The two exceptions are vm_main_campaign_reports and vm_main_daily_agent_performance_report, which keep the vm_ name for compatibility but are now plain views over incrementally maintained rollup tables.

The daily call stats and daily agent performance reports are backed by rollup tables rather than materialized views:

A full REFRESH MATERIALIZED VIEW re-aggregates every call ever made, so its cost grows with history. At roughly 10–13k calls a day that became untenable: both refresh jobs began failing on the 120 s statement_timeout and the campaign report silently froze for a month.

Instead, refresh_call_daily_stats(p_from, p_to) and refresh_agent_daily_performance_stats(p_from, p_to) recompute one inclusive America/New_York date range, deleting and reinserting those days in a single transaction so readers never see a partial window. pg_cron calls them every 10 minutes for the trailing 3 days, plus a nightly 30-day re-sync. Both windows are fixed, so refresh cost does not grow with history.

A 3-day hot window is safe because a Call row stops being updated within about 21 hours of creation. Anything later is caught by the nightly re-sync.

Both refreshes depend on idx_call_created_at. Without an index on "createdAt" alone the planner falls back to a composite index where createdAt is the third column and scans all of it, which makes a windowed aggregate slower than scanning the whole table.

refreshed_at on each table records when a day was last recomputed. To rebuild history — after changing an aggregate expression, for example — call the refresh function directly over the range, chunked to stay inside the statement timeout.

The definitions below are the contract these two reports actually implement. They are recorded here because several are not what the column name suggests, and a reader comparing two reports — or reconciling a number against Call by hand — will otherwise conclude the data is wrong.

One row per date plus a totals object, both validated by DailyStatSchema in lib/schema/reports.ts.

Volume: unique_calls, total_duration_m, billed_minutes, calls_picked_up.

Per-disposition counts: qualified_lead, call_back, not_qualified, not_a_seller, not_interested, wrong_num, voice_mail, ai_screening, busy, hangup_early, ai_issue, do_not_call, ivr, gatekeeper, dial_failed, phone_provider_unavailable, dial_no_answer, user_declined, invalid_destination, dial_busy, user_hangup, error_retell, tel_provider_perm_denied, no_disposition.

Returning a totals object alongside the daily rows means the UI never re-sums the page and therefore cannot disagree with the database.

How each of these is computed — including the per-call rounding behind billed_minutes and what calls_picked_up excludes — is in How the daily metrics are derived.

Four stages, returned both daily and aggregated:

main_sankey_chart_view produces the node/link graph of how calls flow from total volume through conversation validity and CRM status into individual dispositions. The response has a daily array and an aggregated object, each { nodes: [{ name }], links: [{ source, target, value }] } where source and target are node indices.

SANKEY_NODE_COLOR_MAP in lib/constants.ts assigns a stable colour per node name, grouped by meaning: blue for entry, green for positive outcomes, red shades for negative, indigo and purple for call-status problems, teal for AI-related, grey for technical failures, amber for follow-up. Keeping the map in constants rather than the chart component means the same disposition is the same colour in every visualisation.

Per date and per AI agent — the report that makes prompt versions comparable:

total_calls, total_analysis, avg_score, total_score, correct_dispositions, correct_disposition_pct, avg_qa_ai_agent_score, total_qa_ai_agent_score, total_critical_issues, hangup_calls, hangup_disposition_rate_pct.

Accepts date, or dateFrom and dateTo.

See How the daily metrics are derived for how each field is computed, and why this report’s call total does not match the daily call stats report.

Per-number pickup rate, filterable by state and provider. This is the input to keep, rest or release decisions — see Phone numbers.

The Calls total comes from vm_main_campaign_reports (GET /api/reports/daily-stats), the same Outreach call log as the dashboard Daily Calls card. Connect rate is calls longer than 0 seconds that are not voicemail or AI screening, divided by that Calls total. Conversation rate is calls longer than 10 seconds that are also not a hang-up (Hangup_Early or user_hangup), a gatekeeper, or a wrong number, divided by the same total. HubSpot does not receive every AI call. Outcome stages still come from BigQuery hubspot4.v_calls_latest where ownerid is 86817215. The Outcome funnel Conversations bar is distinct people with a mapped conversation disposition, shown as a share of contacts reached. It does not use the call-level Conversation rate rules. Deals come from hubspot4.v_deals_latest, which keeps one row per Stitch-synced deal. The query does not read the raw hubspot4.deals table. Funded status comes from PayabilitySheets.v_supplier_summary_extended.first_purchase_date.

The app applies dateFrom / dateTo (YYYY-MM-DD) with a 366-day cap. Missing dates default to the last 30 Eastern days. A deal counts only when it was created on or after that contact’s first AI call in the range. Lost reasons group those lost deals by lost_reason, falling back to closed_lost_reason, with a blank reason shown as No reason. Deal won is the best deal stage closedwon (Active (Won)) or 1045206663 (Closed Won). Funded is a first purchase on or after that first AI call. Amazon qualified is the contact’s current property_ai_call_outcome of Amazon_Seller, not the status at call time.

The warm-leads list (/reports/ai-calling/warm-leads) shows, per contact, human calls (HubSpot v_calls_latest with an owner other than 86817215) and emails (hubspot4.v_emails_latest, any owner). Both are counted from the contact’s first Warm Lead call in the range up to today; contacts listed only for a won deal use their first AI call instead. Engagements are credited to the first associated contact only. Each row links to the contact in HubSpot.

UI: /reports/ai-calling, linked from /reports. Hook: useAiCallingReport.

Source is the BigQuery view outreach_app.v_re_engagement_daily_call_summary (documented in lib/bigquery/v_re_engagement_daily_call_summary.sql). The view is global — there is no subscriptionId column — so the API authenticates the session but does not tenant-filter the query.

The app applies dateFrom / dateTo (YYYY-MM-DD, list dates) with a 90-day cap. Missing dates default to the last 14 Eastern days. The service sums daily into by_segment (including per- disposition call counts) and a funnel (total_contacts, called_contacts, uncalled_contacts, never_called_contacts, connected_contacts, positive_calls_contacts). The segment chart stacks those HubSpot disposition counts. Funnel stage 3 is distinct listed contacts with at least one mapped conversation disposition. positive_calls_contacts is Warm Lead, Follow up scheduled, API Connected Confirmed, or Account Created. The header and daily chart count re-engagement entries (one list date each). contact_funnel counts each person once: people, dialled, conversations, positive outcomes, and attributed wins (first purchase on or after their first list date), plus not dialled and never called. The contact list at /reports/reengagement-contacts shows those people and their list history.

UI: /reports/reengagement-daily, linked from /reports and the Call summary button on /reengagement. Hook: useReengagementDailyReport.

Source is the BigQuery view outreach_app.v_re_engagement_results_summary (documented in lib/bigquery/outreach_app/v_re_engagement_results_summary.sql). One view row is one HubSpot contact that appeared on a daily list, joined to the latest supplier statuses. The view is global — there is no subscriptionId column — so the API authenticates the session but does not tenant-filter the query.

The app does not pull contact-grain rows. queryReengagementResultsSummary() runs a single COUNT(*) / COUNTIF over the view (all-time; no date filter). Status matching is LOWER(TRIM(…​)). API needed includes both needed and required. Payability includes suspended as a first-class bucket. Each group also returns other and null. no_supplier is supplierkey IS NULL. First-purchase wins: with_first_purchase is first_purchase_date IS NOT NULL; attributed_wins is DATE(first_purchase_date) >= DATE(first_listdate) (purchase on or after the contact’s first daily-list appearance).

UI: /reports/reengagement-results, linked from /reports and the Results button on /reengagement. Hook: useReengagementResultsReport.

Activity is the unified interaction log. ActivityType covers sms, email, call, task, meeting, note, reminder.

externalId is unique per (subscriptionId, externalId), which makes webhook-driven activity upserts idempotent.

GET /api/activity merges Activity rows with Call rows into one chronological feed, filterable by type or types, search, campaignId, dateFrom, dateTo, with standard pagination. lib/utils/map-activity-feed.ts normalises both sources into one shape, and lib/utils/resolve-activity-campaign-names.ts resolves campaign names in bulk rather than per row.

Calls are merged from their own table rather than duplicated into Activity because Call carries far more structure (transcript, recording, analysis) than an activity row could hold.

Hooks: useActivity(params?) in lib/hooks/use-activity.ts. Repository: lib/repositories/activity-repository.ts.

Chart components live in components/reports/. Hooks: use-daily-stats, use-yearly-stats, use-daily-calls, use-recent-calls, use-daily-agent-performance-chart, use-email-campaign-target-summary, use-reengagement-daily-report, use-ai-calling-report, use-reengagement-results-report.