SugarCRM In-Depth: E-mail Folders

The e-mail functionality in SugarCRM is one of the more intricate areas of the application.  It utilizes multiple tables for a variety of tasks, including storing and organizing messages.

Lets take a closer look at how the latter part works in this in-depth analysis of the e-mail folders system of SugarCRM's e-mail client.

To begin, lets take a look at what the default e-mail folder configuration looks like:

Notice that the following three folders automatically appear:

• My Email
• My Drafts
• My Sent Email

These are system folders and are used to house downloaded incoming e-mail, drafts and already sent messages respectively.  They should never be deleted as doing so may result in a loss of data.


Ironically, there are occasions on which the folders will disappear through no action of the user, thus making it difficult to access the e-mail messages they contain.


Troubleshooting this problem helps one obtain a good understanding of the underlying architecture of the e-mail folders system, so lets dive in and take a look.
First and foremost, to troubleshoot this type of issue we need direct access to the database.  Folders utilize three different tables within the SugarCRM database.  


The three tables are:

• folders
• folders_rel
• folders_subscriptions

folders contains a listing of the system and user created folders for all users.  In addition, it stores information pertaining to the structure of the folder tree itself, i.e. which folders are subfolders and which is the parent folder?


folders_rel contains the relationship information pertaining to which folder houses a particular message.  It is only used for messages stored in user created folders.  If an incoming e-mail is not stored in a user created folder, it is automatically stored in the My Email folder and an entry for that message is not made within folders_rel.


folders_subscriptions ties the folders table to the currently logged in user.  It contains the relationship information pertaining to the folders that should be displayed for each user.


Here is what the relationships look like graphically:

Given that we are interested in determining what happened to our default system folders, the first table we will want to examine is: folders.  This will help us ensure that the corresponding folder record actually exists.  If it does not -- say we are missing the record for the My Email folder -- one can correct the issue by simply copying the My Email record of another user, then updating the following columns on the copy of the record to reflect the ID of the user experiencing the problem:

• created_by
• modified_by

Suppose that the My Email folder record does exist, then what?  The next logical step is to look in folders_subscriptions to verify that a relationship to tie the user to the folder exists within the table.   


If it doesn't exist, it can be easily recreated manually.  Simply retrieved the ID column value of the My Email folder record and create the entry.  Suppose that the corresponding ID value is '123456' and that the user that should be seeing that folder has an ID value of 'abcdef', this is how we could create the folders_subscriptions record to correct the problem:


MSSQL:

• INSERT INTO folders_subscriptions (newid(), '123456', 'abcdef')

MySQL:

• INSERT INTO folders_subscriptions(uuid(), '123456', 'abcdef')

The above steps would make the folders reappear, but there is still another potential problem that one might need to address and involved the folders_rel table.  


While folders may be displaying correctly, their contents may actually be incorrect, or most likely, empty.  


To associate an individual message with a specific folder, the message must have an entry in the folders_rel table.  Upon initial inspection of folders_rel, one might notice two oddly named columns: polymorphic_module and polymorphic_id.  The first is the name of the module that contains the message, i.e. 'Emails', and the latter is the ID value of the record from said module.  As a side note, the polymorphic_module value is always 'Emails.'  


Notice as well there is a column for a folder_id value.  That is extracted from the folders table and that is how SugarCRM knows which messages should be displayed in the various folder.  


Note that the folders_rel table is blank by default.  SugarCRM only uses this table for messages stored in a folder other than the default system folders (My Email, Drafts, Sent Email).