Email

Overview

Outlines the relationships between emails, email addresses, and bean records.

Email Tables

Table Name	Description
email_addresses	Each record in this table represents an email address in the system. Note that the `invalid_email` column and `opt_out` column are stored on this table, meaning that they are treated as properties of the email address itself, not a relationship attribute between a given email address and related record. This means if a Lead opts-out of a campaign, this email address will be considered opted-out in all contexts, not just with further correspondence with that specific Lead.
email_addr_bean_rel	The `email_addr_bean_rel` table maintains the relationship between the email address and its parent module record (Contacts, Accounts, Leads, Users, etc) to determine which record of the given module the email address belongs to. Note that this relationship table also has the `primary_address` and `reply_to_address` columns to indicate whether a given email address for a given record is the primary email address, the reply to email address, or some other address for the contact. A contact can have one address flagged as primary, and one flagged as "Reply To". This can be the same email address or two different email addresses.
emails_email_addr_rel	The `emails_email_addr_rel` table maintains the relationships between the email address and email records. Note that this relationship table also has the `address_type` column to indicate if the email address is related to the email as a "To", "From", "CC", or "BCC" address. The valid values at the database level for this column are: from, to, cc, bcc.
emails	Each record in this table represents an email in the system. Note that emails fall outside the scope of this document, but are mentioned here for clarity, as the two modules are closely related.
emails_beans	Similar to the `email_addr_bean_rel` table, the `emails_beans` table maintains the relationship between an email record and any related records (Contacts, Accounts, Cases, etc.).
<module>	This is used to show how an email address record relates to a record in any module is set on `bean_module`. If `bean_module` is set to `Contacts`, `<module>` would be the `contacts` table.

The following diagram illustrates table relationships between email addresses and other modules, email addresses and email records, and email records and other modules.

Emails Email Addresses ERD

Helper Queries

Retrieve the Primary Email Address of a Contact

The following query will fetch the email address given a specific contacts id:

  SELECT
  email_address
FROM email_addresses
JOIN email_addr_bean_rel eabr
  ON eabr.email_address_id = email_addresses.id
WHERE eabr.bean_module = "Contacts"
AND eabr.bean_id = "<contact id>"
AND email_addresses.invalid_email = 0
AND eabr.deleted = 0
AND eabr.primary_address = 1;

Retrieve All Records Related to an Email Address

The following query will fetch the id and module name of all records related to the specified email address:

  SELECT
  bean_module,
  bean_id
FROM email_addr_bean_rel eabr
JOIN email_addresses
  ON eabr.email_address_id = email_addresses.id
WHERE email_addresses.email_address = "<email address>"
AND eabr.deleted = 0;

Retrieve All Emails Sent From An Email Address

The following query will fetch all emails sent from a specified email address:

  SELECT
  emails.name,
  emails.date_sent
FROM emails
JOIN emails_email_addr_rel eear
  ON eear.email_id = emails.id
JOIN email_addresses
  ON eear.email_address_id = email_addresses.id
WHERE email_addresses.email_address = "<email address>"
AND eear.address_type = "from"
AND eear.deleted = 0

Cleanup Duplicate Email Addresses

The following queries will remove any duplicate email addresses.

First, create a temporary table with distinct records from the email_addr_bean_rel table:

  create table email_addr_bean_rel_tmp_ful
SELECT
  *
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY email_address_id,
         bean_module,
         bean_id
ORDER BY primary_address DESC;

Next, clear out the email_addr_bean_rel table:

  truncate email_addr_bean_rel;

Move the records from the temporary table back to email_addr_bean_rel:

  INSERT INTO email_addr_bean_rel
 SELECT
 *
 FROM email_addr_bean_rel_tmp;

Validate that all of the duplicates have been removed:

  SELECT
 COUNT(*) AS repetitions,
 date_modified,
 bean_id,
 bean_module
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY bean_id,
 bean_module,
 email_address_id
HAVING repetitions > 1;

Finally, remove the temporary table:

  drop table email_addr_bean_rel_tmp;

Email Address Validation

Sugar validates emails addresses according to the RFC 5321 and RFC 5322 standards. The following sections will detail how a developer can validate email addresses both server and client side.

Server Side

To validate an email address in a server-side context, the EmailAddress::isValidEmail() static method should be used. For example:

  $emailAddress = "test@example.com";
$isValid = EmailAddress::isValidEmail($emailAddress);

The EmailAddress::isValidEmail method leverages the PHPMailer library bundled with Sugar, specifically the PHPMailer::validateAddress method, which validates the address according to the RFC 5321 and RFC 5322 standards.

Client Side

To validate an email address client-side context, the app.utils.isValidEmailAddress() function can be used.

  var emailAddress = "test@example.com";
var isValid = app.utils.isValidEmailAddress(emailAddress);

Note: This function is more permissive and does not conform exactly to the RFC standards used on the server. As such, the email address will be validated again on the server when the record is saved, which could still fail validation.