Delete older Emails from a Sugar Instance

Deleting older emails is a common approach to reduce a Sugar instance storage footprint.
In this post, I want to cover the following options available to delete emails prior to a given date.
1 - Mass Update 
2 - REST Endpoints 
3 - Direct DB queries

1 – Mass Update

The mass update is arguably the easier approach and can be performed via GUI.
Navigate to the list view, and add the threshold date to a filter.
Press Select all records and then Delete.

When the amount of emails is larger than the max_record_fetch_size setting, the process needs to be repeated until all the emails are deleted.
This will soft delete the emails and remove the related attachments from the filesystem.
If the goal is to free up DB space, pruning these records from the DB can be performed after.

2 – Deleting via REST endpoints.

Deleting using REST endpoints offers more flexibility when handling larger amounts of records.
We can filter and delete emails using the endpoints below:

1 – Authenticate
2 – Filter Emails
3 – Delete Emails

To illustrate the usage of these endpoints I have attached a POSTMAN collection.
Mind that this was created as a test example and should be adapted and tested to meet any production requirements.
To use it against your test instance follow the steps below:

1 - Import the Collection and the Environment:

2 - Add the instance details and the threshold date:

3 - Add the postman_script platform in Sugar

4 - Run the Collection selecting the target environment

This will soft delete the emails and remove the related attachments.

To free up the DB space the records should then be deleted from the DB.

*** Pruning the deleted=1 records 

After soft deleting the emails we can remove them from the DB using the following queries:

DELETE et FROM emails_text et 
INNER JOIN emails e ON e.id = et.email_id 
WHERE e.date_entered < 'YYYY-MM-DD' 
AND et.deleted=1; 

DELETE eb FROM emails_beans eb
INNER JOIN emails e 
ON e.id = eb.email_id
WHERE e.date_entered < 'YYYY-MM-DD' 
AND eb.deleted=1;

DELETE eear FROM emails_email_addr_rel
eear INNER JOIN emails e 
ON e.id = eear.email_id 
WHERE e.date_entered < 'YYYY-MM-DD' 
AND e.deleted=1;

DELETE FROM emails 
WHERE date_entered < 'YYYY-MM-DD' AND deleted=1;

3 – Deleting via direct DB queries.

If DB access is available deleting Emails via DB queries is also an option, this might be useful when the email related tables are large.
There are some caveats to consider to avoid leaving orphaned rows and files in the filesystem.

3.1 – Deleting Email Attachments

When an email is archived to Sugar the attachments are stored using the Notes module.
The file is renamed to match the Note id and the email_id in the Note links it to the parent email.
Before deleting emails we need to store all the Note id’s to remove with the following query:

SELECT n.id FROM notes n
INNER JOIN emails e 
ON e.id = n.email_id 
WHERE e.date_entered < 'YYYY-MM-DD';

3.2 – Removing files via filesystem

Store the result of the query above in a files_to_delete.txt file containing only the note id's:

Having that we can delete the email attachments with the following commands:

# transfer the files_to_delete.txt to the upload/ folder

$mv {filelocation}/files_to_delete.txt {sugarinstancelocation}/upload/ 

# Navigate to the upload/ folder and create a subfolder /upload/files_to_delete 

$cd upload 
$mkdir files_to_delete 

# Transfer all the files in the files_to_delete.txt to the files_to_delete subfolder 

$while read file; do mv "$file" files_to_delete; done < files_to_delete.txt 

#remove the /upload/files_to_delete subfolder 

$rm -rf upload/files_to_delete

3.3 – Deleting the Note records

After removing the files the notes can be deleted.

DELETE notes n 
INNER JOIN emails e
ON e.id = n.email_id 
WHERE e.date_entered < 'YYYY-MM-DD';

3.4 - Deleting the Emails

With the Notes that are storing the attachments deleted, we can perform the deletion of emails with the following queries.

DELETE et FROM emails_text et 
INNER JOIN emails e ON e.id = et.email_id
WHERE e.date_entered < 'YYYY-MM-DD';

DELETE eb FROM emails_beans eb 
INNER JOIN emails e 
ON e.id = eb.email_id 
WHERE e.date_entered < 'YYYY-MM-DD'; 

DELETE eear FROM emails_email_addr_rel eear 
INNER JOIN emails e 
ON e.id = eear.email_id
WHERE e.date_entered < 'YYYY-MM-DD';

DELETE FROM emails WHERE date_entered < 'YYYY-MM-DD';