Directly ingest emails by custom email in To header

Feature context

Custom email for Service Desk allows customers to use their own email address for support communication. They forward all emails to the project-specific service desk email address (generated from incoming_email) and provide their SMTP credential for the custom email address. We send all outgoing Service Desk emails using the provided credentials.

What does this MR do and why?

This MR solves Custom Email: O365 forwarding doesn't add forwa... (#496396 - closed). We usually expect the project-specific Service Desk address in for example the Delivered-To header. Some email providers don't set this header or modify the headers so the final forwarding target address is not available. These emails are dropped on ingestion because we cannot determine what to do with that email.

This MR allows direct ingestion of the custom email address in the To header. It looks for both the custom email address and the verification address and in turn should make it possible to use MS O365 with the custom email feature.

We look for mail keys (the identifier that decides what to do with the email) in (correct order)

1. Replies from custom emails (to bypass a behavior in MS O365)
2. All headers are checked for keys
3. Look for custom email (this MR!)

This change adds a single new DB query to every email that's being processed where we didn't find a key in 1) and 2).

Query

SELECT "service_desk_settings".* 
FROM "service_desk_settings"
 WHERE "service_desk_settings"."custom_email" = 'support@example.com' LIMIT 1

Query Plan

We have a unique constraint on custom_email which also adds an index, so it's an index scan.

Limit  (cost=0.29..3.31 rows=1 width=75)  
  ->  Index Scan using custom_email_unique_constraint on service_desk_settings  (cost=0.29..3.31 rows=1 width=75)  
        Index Cond: (custom_email = 'support@example.com'::text)  

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

🚫 backend only changes

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. If you haven't set up incoming_email or service_desk_email for email ingestion, please add this to your gitlab.yml file in the development: section. Please restart GDK with gdk restart:

   incoming_email:
     enabled: true
     address: "incoming+%{key}@example.com"

   This will allow you to see the Service Desk section in the settings and generate project-specific email addresses. You won't be able to ingest real emails using mail_room, but we don't need that here.

2. Select project and create custom email records

   project = Project.find(7)
   current_user = User.first

3. Add custom email records

   custom_email = 'support@example.com'
   
   setting = ServiceDeskSetting.find_or_create_by!(project_id: project.id)
   setting.update!(custom_email: custom_email)
   
   credential = ServiceDesk::CustomEmailCredential.create!(
     project_id: project.id,
     smtp_address: 'smtp.gmail.com', # we need a valid smtp address
     smtp_port: 587,
     smtp_username: custom_email,
     smtp_password: 'supersecret'
   )
   
   verification = ServiceDesk::CustomEmailVerification.new(
     project_id: project.id
   )
   verification.mark_as_started!(current_user)

4. Prepare verification email (support+verify@example.com) and ingest it via the receiver (that is usually called by the internal API endpoint that receives emails from mail_room.

   verification_address = 'support+verify@example.com'
   
   email_raw = <<~EMAIL
   From: #{custom_email}
   To: #{verification_address}
   Subject: Verification email
   
   Verification token: #{verification.token}
   EMAIL
   
   EmailReceiverWorker.new.perform(email_raw)

5. Reset verification and see email was properly ingested

   verification.reset
   
   puts <<~CHECK
   (Expected | Actual)
   Verification state:
   finished | #{verification.state}
   
   If it matches, that means email was properly ingested.
   CHECK

6. Now ingest an email to the custom email address directly. Because custom_email is set in setting we can already ingest emails with only the custom email in the To header. For simplicity, we don't enable the custom email (which is required for sending using the credentials).

   email_raw = <<~EMAIL
   From: from@example.com
   To: #{custom_email}
   Subject: Only custom email in To header
   
   If this creates an issue then it works. :tada:
   EMAIL
   
   EmailReceiverWorker.new.perform(email_raw)

7. Check whether the email created a new Service Desk issue

   i = Issue.last
   
   puts <<~CHECK
   (Expected | Actual)
   Title:
   Only custom email in To header | #{i.title}
   From:
   from@example.com | #{i.service_desk_reply_to}
   Author:
   support-bot | #{i.author.username}
   
   If all matches, it works!
   CHECK