## PR2: Report builder refactor — DataSource abstraction
The existing report builders (timeseries + summary) had their SQL
queries inlined — each builder constructed its own scopes, groupings,
and aggregations directly. This made it hard to swap the underlying data
source without duplicating builder logic.
This PR extracts all raw-event querying into a `Reports::RawDataSource`
behind a `Reports::DataSource` factory. Builders now call
`data_source.timeseries`, `.aggregate`, or `.summary` instead of
constructing queries themselves. Behavior is identical —
`DataSource.for(...)` returns `RawDataSource` in all cases today.
The timeseries path had two separate builders (`CountReportBuilder`,
`AverageReportBuilder`) that were selected via a metric-name case
statement in `Conversations::BaseReportBuilder`. These are replaced by a
single `ReportBuilder` that delegates to the data source. The metric
type (count vs average) is now decided inside the data source, not the
builder.
Summary builders similarly moved their inline SQL into
`RawDataSource#summary`, which returns a unified hash keyed by dimension
ID.
the rollup read path.
## Flow
### Before
```
ReportsController ──▶ case metric ──▶ AverageReportBuilder ──▶ inline SQL ──▶ DB
└──▶ CountReportBuilder ──▶ inline SQL ──▶ DB
SummaryController ──▶ AgentSummaryBuilder ──▶ inline SQL ──▶ DB
└──▶ InboxSummaryBuilder ──▶ inline SQL ──▶ DB
└──▶ TeamSummaryBuilder ──▶ inline SQL ──▶ DB
```
### After
```
ReportsController ──▶ ReportBuilder ──┐
├──▶ DataSource.for ──▶ RawDataSource ──▶ DB
SummaryController ──▶ SummaryBuilder ──┘
```
### Expected (after rollup read path)
```
ReportsController ──▶ ReportBuilder ──┐
├──▶ DataSource.for ──▶ RawDataSource ──▶ reporting_events
SummaryController ──▶ SummaryBuilder ──┘ └──▶ RollupDataSource ──▶ reporting_events_rollups
```
### What changed
- `Reports::DataSource` factory + `Reports::RawDataSource`
- `TimezoneHelper#timezone_name_from_params` — prefers IANA name, falls
back to offset
- Unified `Timeseries::ReportBuilder` replaces `CountReportBuilder` +
`AverageReportBuilder`
- Summary builders delegate to `DataSource` instead of querying directly
### How to test
This is a pure refactor — all existing report pages (Overview, Agent,
Inbox, Label, Team) should produce identical numbers. No feature flag or
new config needed.
---------
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
Co-authored-by: Tanmay Deep Sharma <tanmaydeepsharma21@gmail.com>
Co-authored-by: Tanmay Deep Sharma <32020192+tds-1@users.noreply.github.com>
This PR collapses multiple queries fetching stats from a single table to
a single query
```sql
SELECT
user_id as user_id,
COUNT(CASE WHEN name = 'conversation_resolved' THEN 1 END) as resolved_count,
AVG(CASE WHEN name = 'conversation_resolved' THEN value END) as avg_resolution_time,
AVG(CASE WHEN name = 'first_response' THEN value END) as avg_first_response_time,
AVG(CASE WHEN name = 'reply_time' THEN value END) as avg_reply_time
FROM "reporting_events"
WHERE
"reporting_events"."account_id" = <account_id> AND
"reporting_events"."created_at" >= '2025-09-14 18:30:00' AND
"reporting_events"."created_at" < '2025-10-14 18:29:59'
GROUP BY "reporting_events"."user_id";
```
### Why this works?
Here's why this optimization is faster based on PostgreSQL internals:
- Single Table Scan vs Multiple Scans: Earlier we did 4 sequential scans
(or 4 index scans) of the same data, with the same where clause, now in
a single scan all 4 `CASE` expressions are evaluated in a single pass.
- Shared Buffer Cache Efficiency: PostgreSQL's shared buffer cache
stores recently accessed pages, with this, pages are loaded once and
re-used for all aggregation, earlier with separate queries we were
forced to re-read all from the disk each time
- Reduced planning and network overhead (4 vs 1 query)
### How is it tested
1. The specs all pass without making any changes
2. Verified the reports side by side after generating from report seeder
#### How to test
Generate seed data using the following command
```bash
ACCOUNT_ID=1 ENABLE_ACCOUNT_SEEDING=true bundle exec rake db:seed:reports_data
```
Once done download the reports, checkout to this branch and download the
reports again and compare them
Following https://github.com/chatwoot/chatwoot/pull/10604, this PR
introduces similar reporting features for Agents and Teams.
Updates in this PR:
- Added additional methods to the base class to avoid repetition.
- Improve reporting for Teams and Agents to include resolution count.
The Inbox Overview section is being updated to offer a more detailed
report, showing an overall view of the account grouped by inboxes. To
view detailed reports and access specific graphs for individual inboxes,
click on the inbox name to navigate to its dedicated report page.
---------
Co-authored-by: Sojan Jose <sojan@pepalo.com>
