Change inbound email inert to create MD5 message hash based on mailbox, message id, and date sent

Idea created by jasoooon on Oct 29, 2015
    • jasoooon
    • Daniel Daniel
    • Alexander von Berg
    Currently, when inserting an email record into 7.x, Sugar runs a duplicate check against emails.message_id. The message_id field is a hash of either the delivered-to address or inbound mailbox ID, plus the message ID from the message's headers. Basically:

    md5(mailboxID + messageID)

    I think this generally works, but we have found times when automated emails sent from customers have reused the same message IDs. This resulted in the appropriate teams never receiving the update. First of all, yes, every email sent should have a unique ID, which is why the duplicate check should always work. However, the world is imperfect and messages get missed.

    I suggest basing the md5 hash off of mailbox ID, message ID, plus date sent/entered:

    md5(mailboxID + messageID + dateSent)

    I also think it would be good to always use the inbound mailbox ID and not the delivered-to for consistency when troubleshooting.

    Of course, if this were released as a new feature, there would need to be a script to update or some other mechanism to account for emails imported previously.

    Has anyone else run into this? Are others checking for missed messages?