> Reopened from #13613, now from a personal fork
(`gabrieljablonski/chatwoot`) so maintainers can push edits —
organization-owned forks don't support "Allow edits from maintainers".
The previous PR is closed in favor of this one; same commits, same diff.
## Description
This PR adds support for sending voice messages (voice notes) through
the WhatsApp Cloud API. When agents record audio in Chatwoot, it is now
transcoded in the browser from WebM/Opus to OGG/Opus and sent with the
`voice: true` flag, so it appears as a native voice note bubble on
WhatsApp — not as a file/document attachment.
Closes#13283
**Key Changes:**
- Added `webmOpusToOgg.js` — a pure JS EBML parser + OGG page builder
that remuxes browser-recorded WebM/Opus audio into OGG/Opus entirely
client-side, with no server-side dependencies.
- Updated `AudioRecorder.vue` to use an explicit `mimeType` hint, proper
resource cleanup, and an `AUDIO_EXTENSION_MAP` for correct file
extensions.
- Renamed `mp3ConversionUtils.js` → `audioConversionUtils.js` and added
OGG conversion support via the new remuxer.
- Updated `ReplyBox.vue` to request OGG format for WhatsApp channels,
pass `isVoiceMessage` per-attachment, and handle recording errors with a
user-facing alert.
- Updated `MessageBuilder` to read the `is_voice_message` param and
persist it in attachment metadata.
- Updated `WhatsappCloudService` to:
- Normalize `audio/opus` → `audio/ogg` content type on ActiveStorage
blobs (works around Marcel gem re-detection).
- Send the `voice: true` flag when the attachment is a voice message
with `audio/ogg` content type.
- Use WhatsApp Cloud API `v24.0` for the attachment endpoint.
- Added `AUDIO_CONVERSION_FAILED` i18n key.
**How it works:**
1. The browser records audio as WebM/Opus (Chrome/Firefox default).
2. `audioConversionUtils.js` remuxes it to OGG/Opus using the pure-JS
`webmOpusToOgg` remuxer — no server transcoding needed.
3. The OGG file is uploaded with `is_voice_message: true` in the form
payload.
4. `MessageBuilder` persists `is_voice_message` in the attachment's
`meta` hash.
5. `WhatsappCloudService` normalizes the blob content type if needed,
then sends the attachment with `voice: true` so WhatsApp renders it as a
voice note.
## Type of change
- [X] New feature (non-breaking change which adds functionality)
## How Has This Been Tested?
1. Record a voice message in a WhatsApp Cloud conversation.
2. Verify the audio is transcoded to OGG (check file extension in the
attachment preview).
3. Verify the message arrives on WhatsApp as a voice note bubble (not a
document/file).
4. Send an image or document attachment and verify it still works as
before (no `voice` flag).
5. Send a regular (non-voice) audio file and verify it arrives without
the voice flag.
---------
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
Co-authored-by: Muhsin <12408980+muhsin-k@users.noreply.github.com>
Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Fixes Facebook fallback and shared-post attachments so they render as
clickable links in conversations.
Closes:
- https://github.com/chatwoot/chatwoot/issues/4767
- https://github.com/chatwoot/chatwoot/issues/5327
Why:
Facebook can send shared links as `fallback` attachments with a
top-level `url`, and shared posts as `share` attachments with the URL
under `payload.url`. The current flow either misses the nested URL or
treats `share` as downloadable media, so these messages do not render
correctly.
What changed:
- Store Facebook fallback URLs from either `attachment.url` or
`attachment.payload.url`.
- Treat Facebook `share` attachments as fallback link attachments
instead of downloading them as files.
- Render fallback attachments in the next message bubble UI as clickable
links.
How to test:
1. Connect a Facebook inbox.
2. Send a shared link to the page.
3. Send/share a Facebook post to the page.
4. Open the conversation in Chatwoot.
5. Confirm both messages appear as clickable link bubbles.
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
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>
Agents with limited custom roles were receiving notifications (creation,
assignment, mentions, new messages, SLA) for conversations they couldn't
actually open. For example, an agent whose custom role only grants
`conversation_unassigned_manage` was getting notified about
conversations assigned to other agents.
Notifications now go through the same `ConversationPolicy#show?` check
that gates the conversation view itself, so an agent only gets notified
for conversations they're permitted to see. Administrators and agents
without custom roles are unaffected.
---------
Co-authored-by: Sojan Jose <sojan@pepalo.com>
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
Adds the server-side flow that turns Meta WhatsApp Cloud Calling
webhooks into Chatwoot Calls, conversations, voice_call message bubbles,
and ActionCable broadcasts. Stacked on top of #14312 (PR-2 — provider
methods); intentionally does not include the HTTP controller, routes, or
frontend (those land in PR-4 and PR-9).
## Closes
- Part of the WhatsApp Cloud Calling rollout. Linear: TBD
## What changed
**Webhook routing**
- `app/jobs/webhooks/whatsapp_events_job.rb` — append
`prepend_mod_with('Webhooks::WhatsappEventsJob')` so EE can extend it
without forking.
- `enterprise/app/jobs/enterprise/webhooks/whatsapp_events_job.rb` (new)
— overlay that prepends `handle_message_events` to intercept `field:
'calls'` payloads (route to `Whatsapp::IncomingCallService`) and
`interactive.call_permission_reply` messages (route to
`Whatsapp::CallPermissionReplyService`); falls through with `super` for
regular messages.
**Services**
- `enterprise/app/services/whatsapp/incoming_call_service.rb` (new) —
gated on `provider_config['calling_enabled']`; processes `connect`
(creates inbound call via `Voice::InboundCallBuilder` or transitions an
existing outbound call to `in_progress`) and `terminate` events; updates
conversation `additional_attributes` and broadcasts
`voice_call.incoming`/`voice_call.outbound_connected`/`voice_call.ended`.
- `enterprise/app/services/whatsapp/call_permission_reply_service.rb`
(new) — handles WhatsApp interactive `call_permission_reply` replies;
clears the conversation's `call_permission_requested_at` flag and
broadcasts `voice_call.permission_granted` so the agent UI can re-enable
the call button.
**Builder/model adjustments**
- `enterprise/app/services/voice/inbound_call_builder.rb` —
provider-agnostic; accepts `provider:` and `extra_meta:` kwargs, drops
`account:` (now derived from `inbox.account` to keep the param count
under rubocop's ceiling without disabling cops), uses digits-only
`source_id` for WhatsApp ContactInbox (validation requires
`^\d{1,15}\z`), skips Twilio-only `conference_sid` for non-Twilio
providers.
- `enterprise/app/services/voice/call_message_builder.rb` — adds
`create!`/`update_status!` API and `CALL_TO_VOICE_STATUS` map; uses
direct `Message.create!` (bypasses `Messages::MessageBuilder`'s
incoming-on-non-Api-inbox guard, which would otherwise reject the system
bubble); content is `'WhatsApp Call'` for WhatsApp and `'Voice Call'`
for Twilio. Backwards-compatible `perform!` retained for the existing
Twilio call sites.
- `enterprise/app/models/call.rb` — adds `default_ice_servers` (driven
by `VOICE_CALL_STUN_URLS` env), `direction_label` alias for the
`inbound`/`outbound` strings the FE expects, and
`ringing?`/`in_progress?`/`terminal?` predicates used throughout the
pipeline.
**Outgoing-channel guard**
- `app/services/base/send_on_channel_service.rb` — extends
`invalid_message?` to skip messages with `content_type == 'voice_call'`.
Without this, agent-initiated outbound calls (PR-4) would deliver
\"WhatsApp Call\" as a text message to the contact every time.
**Twilio call-site update**
- `enterprise/app/controllers/twilio/voice_controller.rb` — drops the
now-redundant `account: current_account` kwarg from the
`Voice::InboundCallBuilder.perform!` call.
**Tests**
- New: `spec/enterprise/services/whatsapp/incoming_call_service_spec.rb`
(5 examples — calling-disabled, inbound connect, outbound connect,
terminate completed, terminate no-answer, unknown event).
- New:
`spec/enterprise/services/whatsapp/call_permission_reply_service_spec.rb`
(3 examples — accept, reject, calling-disabled).
- Updated: `spec/enterprise/services/voice/inbound_call_builder_spec.rb`
and `spec/enterprise/controllers/twilio/voice_controller_spec.rb` to
drop the `account:` kwarg from call expectations.
## How to test
In `rails console` against an account with a WhatsApp inbox where
`provider_config['calling_enabled']` is true:
```ruby
inbox = Inbox.find(<id>)
params = { calls: [{ id: 'wacid_test', from: '15550001111', event: 'connect',
session: { sdp: 'v=0...', sdp_type: 'offer' } }] }
Whatsapp::IncomingCallService.new(inbox: inbox, params: params).perform
# => Conversation + Call (status: 'ringing', provider: 'whatsapp') + voice_call message bubble
# => ActionCable broadcasts `voice_call.incoming` to the assignee or account-wide
# Then terminate it:
Whatsapp::IncomingCallService.new(inbox: inbox,
params: { calls: [{ id: 'wacid_test', event: 'terminate', duration: 0, terminate_reason: 'no_answer' }] }
).perform
# => Call status flips to 'no_answer', message bubble updates, `voice_call.ended` broadcast fires
```
End-to-end browser flow (Meta → cable → UI) requires the controller from
PR-4 and the frontend from PR-9.
---------
Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
With this change, the form will start displaying a required field. While
validation is already enforced in APIs and other areas, the super_admin
console—being autogenerated—will throw an error since this requirement
isn’t explicitly defined in the model.
<img width="670" alt="Screenshot 2025-01-30 at 2 12 43 PM"
src="https://github.com/user-attachments/assets/e0ab3ace-3649-4ef2-bc94-8d4d80453dd1"
/>
Fixes https://github.com/chatwoot/chatwoot/issues/10754
---------
Co-authored-by: Shivam Mishra <scm.mymail@gmail.com>
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
Co-authored-by: Sony Mathew <ynos1234@gmail.com>
Co-authored-by: Sony Mathew <2040199+sony-mathew@users.noreply.github.com>
Co-authored-by: Sony Mathew <sony@chatwoot.com>
## Description
When an inbox name or business name contains parentheses — e.g. `Giro
Crédito - Soporte (Email` — the resulting From header becomes
unparseable by SMTP servers. The `(` is interpreted as an RFC 5322
comment start, swallowing the actual email address and causing a `553
Invalid email address` rejection.
Closes [CW-6323](https://linear.app/chatwoot/issue/CW-6323)
## Type of change
- [ ] Bug fix (non-breaking change which fixes an issue)
## How to reproduce?
1. Set an inbox's name or business name to include a parenthesis, e.g.
`Support (Email`
2. Send an outgoing email reply from that inbox
3. Observe `Net::SMTPFatalError: 553 ... Invalid email address`
## Checklist:
- [ ] My code follows the style guidelines of this project
- [ ] I have performed a self-review of my code
- [ ] I have commented on my code, particularly in hard-to-understand
areas
- [ ] I have made corresponding changes to the documentation
- [ ] My changes generate no new warnings
- [ ] I have added tests that prove my fix is effective or that my
feature works
- [ ] New and existing unit tests pass locally with my changes
- [ ] Any dependent changes have been merged and published in downstream
modules
## 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>
## Description
ConversationReplyMailer#parse_email calls
Mail::Address.new(email_string).address without error handling. When an
account's support_email contains a non-email string (e.g., "Smith
Smith"), the mail gem raises Mail::Field::IncompleteParseError, crashing
conversation transcript emails.
This has caused 1,056 errors on Sentry (EXTERNAL-CHATINC-JX) since Feb
25, all from a single account that has a name stored in the
support_email field instead of a valid email address.
Closes
https://linear.app/chatwoot/issue/CW-6687/mailfieldincompleteparseerror-mailaddresslist-can-not-parse-orsmith
## Type of change
Please delete options that are not relevant.
- [ ] Bug fix (non-breaking change which fixes an issue)
## Checklist:
- [ ] My code follows the style guidelines of this project
- [ ] I have performed a self-review of my code
- [ ] I have commented on my code, particularly in hard-to-understand
areas
- [ ] I have made corresponding changes to the documentation
- [ ] My changes generate no new warnings
- [ ] I have added tests that prove my fix is effective or that my
feature works
- [ ] New and existing unit tests pass locally with my changes
- [ ] Any dependent changes have been merged and published in downstream
modules
---------
Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Co-authored-by: Vishnu Narayanan <iamwishnu@gmail.com>
Fixes https://linear.app/chatwoot/issue/PLA-96/argumenterror-reel-is-not-a-valid-file-type-argumenterror
Fixes a crash in `when a user shares a Facebook reel via Messenger.
Facebook sends these attachments with `type: "reel"`, which isn't a
valid `file_type` enum value on the Attachment model (only `ig_reel`
exists, matching Instagram's format).
---------
Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
Co-authored-by: Sojan Jose <sojan@pepalo.com>
When agents send replies from the native Facebook Messenger app (not
Chatwoot), echo events were created without external_echo metadata and
could be misrepresented in the UI. This change updates Messenger echo
message creation to:
- set content_attributes.external_echo = true for outgoing_echo messages
- set echo message status to delivered
- keep sender as nil for echo messages (existing behavior)
<img width="2614" height="1264" alt="CleanShot 2026-02-26 at 16 32
04@2x"
src="https://github.com/user-attachments/assets/ba61c941-465d-4893-814e-855e6b6c79e8"
/>
This PR adds a new standalone `GET
/api/v2/accounts/:id/reports/outgoing_messages_count` endpoint that
returns outgoing message counts grouped by agent, team, inbox, or label.
When businesses use WhatsApp Business App (co-existence mode) or
Instagram App or TikTok alongside Chatwoot, messages sent from the
native apps were not synced properly back to Chatwoot. This left agents
with an incomplete conversation history and no visibility into responses
sent outside the dashboard. Additionally, if these echo messages did
arrive, they appeared as "Sent by: Bot" in the UI since they had no
sender, making it confusing for agents.
This PR subscribes to WhatsApp `smb_message_echoes` webhook events and
routes them through the existing service with an `outgoing_echo` flag,
mirroring how Instagram already handles echoes. On the Instagram side,
echo messages now also carry the `external_echo` content attribute and
`delivered` status.
On the frontend, messages with `externalEcho` are distinguished from bot
messages showing a "Native app" avatar and an advisory note encouraging
agents to reply from Chatwoot to maintain the service window.
<img width="1518" height="524" alt="CleanShot 2026-01-29 at 13 37 57@2x"
src="https://github.com/user-attachments/assets/5aa0b552-6382-441f-96aa-9a62ca716e4a"
/>
Fixes
https://linear.app/chatwoot/issue/CW-4204/display-messages-not-sent-from-chatwoot-in-case-of-outgoing-echo
Fixes
https://linear.app/chatwoot/issue/PLA-33/incoming-from-me-messages-from-whatsapp-business-app-are-not-falling
The index is already added in production.
Adds a new reporting API that returns conversation counts grouped by
channel type and first response time buckets (0-1h, 1-4h, 4-8h, 8-24h,
24h+).
- GET /api/v2/accounts/:id/reports/first_response_time_distribution
- Uses SQL aggregation to handle large datasets efficiently
- Adds composite index on reporting_events for query performance
Tested on production workload.
Request: GET
`/api/v2/accounts/1/reports/first_response_time_distribution?since=<since>&until=<until>`
Response payload:
```
{
"Channel::WebWidget": {
"0-1h": 120,
"1-4h": 85,
"4-8h": 32,
"8-24h": 12,
"24h+": 3
},
"Channel::Email": {
"0-1h": 12,
"1-4h": 28,
"4-8h": 45,
"8-24h": 35,
"24h+": 10
},
"Channel::FacebookPage": {
"0-1h": 50,
"1-4h": 30,
"4-8h": 15,
"8-24h": 8,
"24h+": 2
}
}
```
---------
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
This PR added new API endpoint GET
/api/v2/accounts/:account_id/reports/inbox_label_matrix that returns
conversation counts grouped by inbox and label in a matrix format.
Supports optional filtering by date range, inbox_ids, and label_ids.
---------
Co-authored-by: Pranav <pranav@chatwoot.com>
This API gives you how many conversations exist per channel, broken down
by status in a given time period. The max time period is capped to 6
months for now.
**Input Params:**
- **since:** Unix timestamp (seconds) - start of date range
- **until:** Unix timestamp (seconds) - end of date range
**Response Payload:**
```json
{
"Channel::Sms": {
"resolved": 85,
"snoozed": 10,
"open": 5,
"pending": 5,
"total": 100
},
"Channel::Email": {
"resolved": 72,
"snoozed": 15,
"open": 13,
"pending": 13,
"total": 100
},
"Channel::WebWidget": {
"resolved": 90,
"snoozed": 7,
"open": 3,
"pending": 3,
"total": 100
}
}
```
**Definitons:**
resolved = Number of conversations created within the selected time
period that are currently marked as resolved.
snoozed = Number of conversations created within the selected time
period that are currently marked as snoozed.
pending = Number of conversations created within the selected time
period that are currently marked as pending.
open = Number of conversations created within the selected time period
that are currently open.
total = Total number of conversations created within the selected time
period, across all statuses.
Fixes
https://linear.app/chatwoot/issue/CW-6070/argumenterror-ephemeral-is-not-a-valid-file-type-argumenterror
**Problem**
The instagram webhooks containing ephemeral (disappearing) message were
causing ArgumentError exceptions because this attachment type is not
supported and was not in the enum validation.
**Solution**
- Added ephemeral to the unsupported_file_type? filter
- Ephemeral attachments are now silently filtered out before processing,
following the same pattern as existing unsupported types (template,
unsupported_type)
- Enables outbound voice calls in voice channel . We are only caring
about wiring the logic to trigger outgoing calls to the call button
introduced in previous PRs. We will connect it to call component in
subsequent PRs
ref: #11602
## Screens
<img width="2304" height="1202" alt="image"
src="https://github.com/user-attachments/assets/b91543a8-8d4e-4229-bd80-9727b42c7b0f"
/>
<img width="2304" height="1200" alt="image"
src="https://github.com/user-attachments/assets/1a1dad2a-8cb2-4aa2-9702-c062416556a7"
/>
---------
Co-authored-by: Sivin Varghese <64252451+iamsivin@users.noreply.github.com>
Co-authored-by: iamsivin <iamsivin@gmail.com>
Co-authored-by: Vishnu Narayanan <vishnu@chatwoot.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
We first added conversation continuity for the live chat widget, and
then carried the same logic over to email channels.
The problem was that this added a reply+conversationUUID@domain.com as
the reply-to for emails, which was unnecessary. For email channels, the
reply-to can just be the channel’s own email address.
That extra layer made things more complex than it needed to be. In this
PR, I’ve cleaned up the config so it’s simpler. The table below shows
how it’ll work going forward.
---
| Type | From Email | Reply To Email |
| -- | -- | -- |
| Standard IMAP, SMTP email channel | channel.email | channel.email |
| Google OAuth Email channel | channel.email | channel.email |
| Microsoft OAuth Email channel | channel.email | channel.email |
| Email forwarded to Chatwoot, brought their own SMTP | channel.email |
channel.email |
| Imap to fetch email, Use Chatwoot's SMTP | channel.email if verified
with Chatwoot's SMTP provider. Otherwise account support email |
channel.email |
| Email forwarded to Chatwoot, Use Chatwoot's SMTP | channel.email if
verified with Chatwoot's SMTP provider. Otherwise account support email
| channel.email |
| -- | -- | -- |
| Website Live Chat - Conversation Continuity Inbound Emails enabled|
Account Support Email | reply+{conversation-uuid}@{account_domain} |
| Website Live Chat - Conversation Continuity Inbound Emails disabled|
Account Support Email | Account Support Email |
Fixes https://github.com/chatwoot/chatwoot/issues/10614
Fixes https://github.com/chatwoot/chatwoot/issues/10521
Fixes https://github.com/chatwoot/chatwoot/issues/10300
Fixes https://github.com/chatwoot/chatwoot/issues/10091
Fixes https://github.com/chatwoot/chatwoot/issues/4890
Fixes https://github.com/chatwoot/chatwoot/issues/10676
Fixes https://github.com/chatwoot/chatwoot/issues/10756
Fixes https://github.com/chatwoot/chatwoot/issues/11515
Fixes https://github.com/chatwoot/chatwoot/issues/9471
---------
Co-authored-by: Sojan Jose <sojan@pepalo.com>
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
There was a fundamental difference in how resolution counts were
calculated between the agent summary and timeseries reports, causing
confusion for users when the numbers didn't match.
The agent summary report counted all `conversation_resolved` events
within a time period by querying the `reporting_events` table directly.
However, the timeseries report had an additional constraint that
required the conversation to currently be in resolved status
(`conversations.status = 1`). This meant that if an agent resolved a
conversation that was later reopened, the resolution action would be
counted in the summary but not in the timeseries.
This fix aligns both reports to count resolution events rather than
conversations in resolved state. When an agent resolves a conversation,
they should receive credit for that action regardless of what happens to
the conversation afterward. The same logic now applies to bot
resolutions as well.
The change removes the `conversations: { status: :resolved }` condition
from both `scope_for_resolutions_count` and
`scope_for_bot_resolutions_count` methods in CountReportBuilder, and
updates the corresponding test expectations to reflect that all
resolution events are counted.
## About timezone
When a timezone is specified via `timezone_offset` parameter, the
reporting system:
1. Converts timestamps to the target timezone before grouping
2. Groups data by local day/week/month boundaries in that timezone, but
the primary boundaries are sent by the frontend and used as-is
3. Returns timestamps representing midnight in the target timezone
This means the same events can appear in different day buckets depending
on the timezone used. For summary reports, it works fine, since the user
only needs the total count between two timestamps and the frontend sends
the timestamps adjusted for timezone.
## Testing Locally
Run the following command, this will erase all data for that account and
put in 1000 conversations over last 3 months, parameters of this can be
tweaked in `Seeders::Reports::ReportDataSeeder`
I'd suggest updating the values to generate data over 30 days, with
10000 conversations, it will take it's sweet time to run but then the
data will be really rich, great for testing.
```
ACCOUNT_ID=2 ENABLE_ACCOUNT_SEEDING=true bundle exec rake db:seed:reports_data
```
Pro Tip: Don't run the app when the seeder is active, we manually create
the reporting events anyway. So once done just use `redis-cli FLUSHALL`
to clear all sidekiq jobs. Will be easier on the system
Use the following scripts to test it
- https://gist.github.com/scmmishra/1263a922f5efd24df8e448a816a06257
- https://gist.github.com/scmmishra/ca0b861fa0139e2cccdb72526ea844b2
- https://gist.github.com/scmmishra/5fe73d1f48f35422fd1fd142ea3498f3
- https://gist.github.com/scmmishra/3b7b1f9e2ff149007170e5c329432f45
- https://gist.github.com/scmmishra/f245fa2f44cd973e5d60aac64f979162
---------
Co-authored-by: Sivin Varghese <64252451+iamsivin@users.noreply.github.com>
Co-authored-by: Pranav <pranav@chatwoot.com>
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
- Refactor email validation logic to be a service
- Use the service for both email/pass signup and Google SSO
- fix account email validation during signup
- Use `blocked_domain` setting for both email/pass signup and Google
Sign In [`BLOCKED_DOMAIN` via GlobalConfig]
- add specs for `account_builder`
- add specs for the new service
---------
Co-authored-by: Sojan Jose <sojan@pepalo.com>
## Description
Fixed a typo in the `CampaignConversationBuilder` class.
## Type of change
- [x] Bug fix (non-breaking change which fixes an issue)
## How Has This Been Tested?
Verified that the typo fix does not affect functionality by running the
existing test suite.
## Checklist:
- [x] My code follows the style guidelines of this project
- [x] I have performed a self-review of my code
- [x] My changes generate no new warnings
- [x] New and existing unit tests pass locally with my changes
Co-authored-by: Shivam Mishra <scm.mymail@gmail.com>
This PR introduces basic minimum version of **Instagram Business
Login**, making Instagram inbox setup more straightforward by removing
the Facebook Page dependency. This update enhances user experience and
aligns with Meta’s recommended best practices.
Fixes
https://linear.app/chatwoot/issue/CW-3728/instagram-login-how-to-implement-the-changes
## Why Introduce Instagram as a Separate Inbox?
Currently, our Instagram integration requires linking an Instagram
account to a Facebook Page, making setup complex. To simplify this
process, Instagram now offers **Instagram Business Login**, which allows
users to authenticate directly with their Instagram credentials.
The **Instagram API with Instagram Login** enables businesses and
creators to send and receive messages without needing a Facebook Page
connection. While an Instagram Business or Creator account is still
required, this approach provides a more straightforward integration
process.
| **Existing Approach (Facebook Login for Business)** | **New Approach
(Instagram Business Login)** |
| --- | --- |
| Requires linking Instagram to a Facebook Page | No Facebook Page
required |
| Users log in via Facebook credentials | Users log in via Instagram
credentials |
| Configuration is more complex | Simpler setup |
Meta recommends using **Instagram Business Login** as the preferred
authentication method due to its easier configuration and improved
developer experience.
---
## Implementation Plan
The core messaging functionality is already in place, but the transition
to **Instagram Business Login** requires adjustments.
### Changes & Considerations
- **API Adjustments**: The Instagram API uses `graph.instagram`, whereas
Koala (our existing library) interacts with `graph.facebook`. We may
need to modify API calls accordingly.
- **Three Main Modules**:
1. **Instagram Business Login** – Handle authentication flow.
2. **Permissions & Features** – Ensure necessary API scopes are granted.
3. **Webhooks** – Enable real-time message retrieval.
---
## Instagram Login Flow
1. User clicks **"Create Inbox"** for Instagram.
2. App redirects to the [Instagram Authorization
URL](https://developers.facebook.com/docs/instagram-platform/instagram-api-with-instagram-login/business-login#embed-the-business-login-url).
3. After authentication, Instagram returns an authorization code.
5. The app exchanges the code for a **long-lived token** (valid for 60
days).
6. Tokens are refreshed periodically to maintain access.
7. Once completed, the app creates an inbox and redirects to the
Chatwoot dashboard.
---
## How to Test the Instagram Inbox
1. Create a new app on [Meta's Developer
Portal](https://developers.facebook.com/apps/).
2. Select **Business** as the app type and configure it.
3. Add the Instagram product and connect a business account.
4. Copy Instagram app ID and Instagram app secret
5. Add the Instagram app ID and Instagram app secret to your app config
via `{Chatwoot installation
url}/super_admin/app_config?config=instagram`
6. Configure Webhooks:
- Callback URL: `{your_chatwoot_url}/webhooks/instagram`
- Verify Token: `INSTAGRAM_VERIFY_TOKEN`
- Subscribe to `messages`, `messaging_seen`, and `message_reactions`
events.
7. Set up **Instagram Business Login**:
- Redirect URL: `{your_chatwoot_url}/instagram/callback`
8. Test inbox creation via the Chatwoot dashboard.
## Troubleshooting & Common Errors
### Insufficient Developer Role Error
- Ensure the Instagram user is added as a developer:
- **Meta Dashboard → App Roles → Roles → Add People → Enter Instagram
ID**
### API Access Deactivated
- Ensure the **Privacy Policy URL** is valid and correctly set.
### Invalid request: Request parameters are invalid: Invalid
redirect_uri
- Please configure the Frontend URL. The Frontend URL does not match the
authorization URL.
---
## To-Do List
- [x] Basic integration setup completed.
- [x] Enable sending messages via [Messaging
API](https://developers.facebook.com/docs/instagram-platform/instagram-api-with-instagram-login/messaging-api).
- [x] Implement automatic webhook subscriptions on inbox creation.
- [x] Handle **canceled authorization errors**.
- [x] Handle all the errors
https://developers.facebook.com/docs/instagram-platform/instagram-graph-api/reference/error-codes
- [x] Dynamically fetch **account IDs** instead of hardcoding them.
- [x] Prevent duplicate Instagram channel creation for the same account.
- [x] Use **Global Config** instead of environment variables.
- [x] Explore **Human Agent feature** for message handling.
- [x] Write and refine **test cases** for all scenarios.
- [x] Implement **token refresh mechanism** (tokens expire after 60
days).
Fixes https://github.com/chatwoot/chatwoot/issues/10440
---------
Co-authored-by: Sivin Varghese <64252451+iamsivin@users.noreply.github.com>
Co-authored-by: Copilot Autofix powered by AI <62310815+github-advanced-security[bot]@users.noreply.github.com>
Co-authored-by: Shivam Mishra <scm.mymail@gmail.com>
- Add dynamic importing for routes.
- Added caching for `campaign`, `articles` and `inbox_members` API end
points.
---------
Co-authored-by: Pranav <pranavrajs@gmail.com>
In this PR https://github.com/chatwoot/chatwoot/pull/11139, if there is
an attempt to create a duplication session for the contact in the same
inbox, we will anonymize the old session.
This PR would prevent sending messages to the older sessions. The
support agents will have to create a new conversation to continue
messages with customer.
This PR addresses a race condition in the contact inbox model caused by
duplicate `source_id` values linked to different contacts.
The issue typically occurs when an agent updates a contact’s email or
phone number or when two contacts are merged. In these scenarios, the
`source_id`, which is intended to uniquely identify the contact in a
session, may still be associated with the old contact inbox.
To solve this, we check if there’s already a ContactInbox with the same
source_id but linked to another contact. If we find one, we update that
old record by changing its source_id to a random value. This breaks the
wrong connection and prevents issues, while still keeping the old data
safe.
However, this is only a temporary fix. The main issue is with the way
the contact inbox model is designed. Right now, it’s being used to track
sessions, but that may not be necessary for non-live chat channels. In
the long run, we should consider redesigning this part of the system to
avoid such problems.
This PR updates the report pages for agents, inboxes, and teams by
replacing charts with aggregate values (under a feature flag). Users can
click on any item to view more details if needed. Most users seem to
prefer aggregate values, so this change will likely stay.
The PR also includes a few fixes:
- The summary reports now use the same logic for both the front-end and
CSV exports.
- Fixed an issue where a single quote was being added to values with
hyphens in CSV files. Now, ‘n/a’ is used when no value is available.
- Fixed a bug where the average value was calculated incorrectly when
multiple accounts were present.
These changes should make reports easier to use and more consistent.
### Agents:
<img width="1438" alt="Screenshot 2025-01-26 at 10 47 18 AM"
src="https://github.com/user-attachments/assets/bf2fcebc-6207-4701-9703-5c2110b7b8a0"
/>
### Inboxes
<img width="1438" alt="Screenshot 2025-01-26 at 10 47 10 AM"
src="https://github.com/user-attachments/assets/b83e1cf2-fd14-4e8e-8dcd-9033404a9f22"
/>
### Teams:
<img width="1436" alt="Screenshot 2025-01-26 at 10 47 01 AM"
src="https://github.com/user-attachments/assets/96b1ce07-f557-42ca-8143-546a111d6458"
/>
---------
Co-authored-by: Sivin Varghese <64252451+iamsivin@users.noreply.github.com>
Co-authored-by: Shivam Mishra <scm.mymail@gmail.com>
- Prevents notifications from being created for conversations or actions
involving blocked contacts.
- The exception is the conversation_mention notification type, which
will still be created when applicable.
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>
