2 Commits

Author	SHA1	Message	Date
Shivam Mishra	379e28df1f	fix: prevent bot metrics double-counting when handoff and resolution coexist [CW-6210] (#14032) ... The bot metrics dashboard can show `handoff_rate + resolution_rate > 100%`. A single conversation can accumulate both `conversation_bot_handoff` and `conversation_bot_resolved` events, and the rate queries count them independently against a shared denominator. ## How it happens ``` Customer messages bot inbox │ ▼ ┌──────────┐ │ pending │ (bot handling) └────┬─────┘ │ bot can't help ▼ ┌──────────┐ │ open │ (handed off → conversation_bot_handoff event created) └────┬─────┘ │ agent clicks "Resolve" WITHOUT sending a message ▼ ┌──────────┐ │ resolved │ conversation_resolved fires └──────────┘ │ ▼ create_bot_resolved_event guard checks: ✅ inbox.active_bot? ✅ no outgoing messages with sender_type: 'User' ← agent never messaged! │ ▼ conversation_bot_resolved event ALSO created ← BUG │ ▼ Same conversation counted in BOTH rates → sum exceeds 100% ``` ## Why fix at the read path, not the write path An earlier attempt added guards in the listener to make the two events mutually exclusive per conversation — deleting `bot_resolved` when a handoff fires, suppressing resolutions when a handoff exists. This was rejected because conversations can be reopened across multiple cycles (bot resolves on day 1, customer returns on day 5, bot hands off). Deleting the day-1 resolution corrupts historical reports, and the async event dispatcher makes listener-level guards vulnerable to race conditions. ## What this PR does Within a reporting window, if a conversation has both events, **handoff wins** — the conversation is excluded from the resolution count. This is applied via SQL subquery across all three read paths: ``` ┌─────────────────────────┐ │ Reporting Events DB │ │ │ │ conv_bot_handoff: [A,B] │ │ conv_bot_resolved: [A,C]│ └────────┬────────────────┘ │ ┌──────────────┼──────────────┐ ▼ ▼ ▼ BotMetricsBuilder ReportHelper CountReportBuilder (rate cards) (bot_summary) (timeseries charts) │ │ │ ▼ ▼ ▼ resolutions: resolutions: resolutions: [A,C] minus [A,B] same logic same logic = [C] only = [C] only = [C] only Result: Conversation A → handoff only Conversation B → handoff only Conversation C → resolution only ``` For wide date ranges spanning multiple lifecycles, a conversation bot-resolved in one cycle and handed off in a later cycle will only show as a handoff. This is an acceptable tradeoff — the alternative (>100% rates) is clearly worse, and narrow ranges handle this correctly since the events fall into different windows. No reporting events are modified, so historical data stays intact. ## Diagnostic tool `rake bot_metrics:diagnose` — read-only task that prompts for account ID and date range, shows a before/after rate comparison without modifying data. --------- Co-authored-by: aakashb95 <aakashbakhle@gmail.com> Co-authored-by: Aakash Bakhle <48802744+aakashb95@users.noreply.github.com>	2026-05-13 18:43:23 +05:30
Shivam Mishra	6cbddbdb67	feat(rollup): report builder abstraction [2/3] (#13798) ... ## 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>	2026-04-20 11:15:48 +05:30