mirror of
https://github.com/lingble/chatwoot.git
synced 2025-11-02 20:18:08 +00:00
ffa124d0d5
While investigating a customer-reported issue, I found that some emails
were appearing late in Chatwoot. The root cause was query timeouts.
It only happened for emails with an in_reply_to header. In these cases,
Chatwoot first checks if a message exists with message_id = in_reply_to.
If not, it falls back to checking conversations where
additional_attributes->>'in_reply_to' = ?.
We were using:
```rb
@inbox.conversations.where("additional_attributes->>'in_reply_to' = ?", in_reply_to).first
```
This looked harmless, but .first caused timeouts. Without .first, the
query ran fine. The issue was the generated SQL:
```sql
SELECT *
FROM conversations
WHERE inbox_id = $1
AND (additional_attributes->>'in_reply_to' = '<in-reply-to-id>')
ORDER BY id ASC
LIMIT $2;
```
The ORDER BY id forced a full scan, even with <10k records.
The fix was to replace .first with .find_by:
```rb
@inbox.conversations.find_by("additional_attributes->>'in_reply_to' = ?", in_reply_to)
```
This generates:
```sql
SELECT *
FROM conversations
WHERE inbox_id = $1
AND (additional_attributes->>'in_reply_to' = '<in-reply-to-id>')
LIMIT $2;
```
This avoids the scan and runs quickly without needing an index.
By the way, Cursor and Claude failed
[here](https://github.com/chatwoot/chatwoot/pull/12401), it just kept on
adding the index without figuring out the root cause.
Co-authored-by: Muhsin Keloth <muhsinkeramam@gmail.com>
110 lines
2.8 KiB
Ruby
110 lines
2.8 KiB
Ruby
class Imap::ImapMailbox
|
|
include MailboxHelper
|
|
include IncomingEmailValidityHelper
|
|
attr_accessor :channel, :account, :inbox, :conversation, :processed_mail
|
|
|
|
def process(mail, channel)
|
|
@inbound_mail = mail
|
|
@channel = channel
|
|
load_account
|
|
load_inbox
|
|
decorate_mail
|
|
|
|
Rails.logger.info("Processing Email from: #{@processed_mail.original_sender} : inbox #{@inbox.id} : message_id #{@processed_mail.message_id}")
|
|
|
|
# Skip processing email if it belongs to any of the edge cases
|
|
return unless incoming_email_from_valid_email?
|
|
|
|
ActiveRecord::Base.transaction do
|
|
find_or_create_contact
|
|
find_or_create_conversation
|
|
create_message
|
|
add_attachments_to_message
|
|
end
|
|
end
|
|
|
|
private
|
|
|
|
def load_account
|
|
@account = @channel.account
|
|
end
|
|
|
|
def load_inbox
|
|
@inbox = @channel.inbox
|
|
end
|
|
|
|
def decorate_mail
|
|
@processed_mail = MailPresenter.new(@inbound_mail, @account)
|
|
end
|
|
|
|
def find_conversation_by_in_reply_to
|
|
return if in_reply_to.blank?
|
|
|
|
message = @inbox.messages.find_by(source_id: in_reply_to)
|
|
if message.nil?
|
|
@inbox.conversations.find_by("additional_attributes->>'in_reply_to' = ?", in_reply_to)
|
|
else
|
|
@inbox.conversations.find(message.conversation_id)
|
|
end
|
|
end
|
|
|
|
def find_conversation_by_reference_ids
|
|
return if @inbound_mail.references.blank? && in_reply_to.present?
|
|
|
|
message = find_message_by_references
|
|
|
|
return if message.nil?
|
|
|
|
@inbox.conversations.find(message.conversation_id)
|
|
end
|
|
|
|
def in_reply_to
|
|
@processed_mail.in_reply_to
|
|
end
|
|
|
|
def find_message_by_references
|
|
message_to_return = nil
|
|
|
|
references = Array.wrap(@inbound_mail.references)
|
|
|
|
references.each do |message_id|
|
|
message = @inbox.messages.find_by(source_id: message_id)
|
|
message_to_return = message if message.present?
|
|
end
|
|
message_to_return
|
|
end
|
|
|
|
def find_or_create_conversation
|
|
@conversation = find_conversation_by_in_reply_to || find_conversation_by_reference_ids || ::Conversation.create!(
|
|
{
|
|
account_id: @account.id,
|
|
inbox_id: @inbox.id,
|
|
contact_id: @contact.id,
|
|
contact_inbox_id: @contact_inbox.id,
|
|
additional_attributes: {
|
|
source: 'email',
|
|
in_reply_to: in_reply_to,
|
|
auto_reply: @processed_mail.auto_reply?,
|
|
mail_subject: @processed_mail.subject,
|
|
initiated_at: {
|
|
timestamp: Time.now.utc
|
|
}
|
|
}
|
|
}
|
|
)
|
|
end
|
|
|
|
def find_or_create_contact
|
|
@contact = @inbox.contacts.from_email(@processed_mail.original_sender)
|
|
if @contact.present?
|
|
@contact_inbox = ContactInbox.find_by(inbox: @inbox, contact: @contact)
|
|
else
|
|
create_contact
|
|
end
|
|
end
|
|
|
|
def identify_contact_name
|
|
processed_mail.sender_name || processed_mail.from.first.split('@').first
|
|
end
|
|
end
|