Amazon S3 File Archive plugin

Owners of self-hosted LiveAgent licenses can activate Amazon S3 File Archive from Configuration->System->Plugins to save their hosting database space.

What the plugin does is that after X days from creating a ticket attachment it moves the attachment from your database to your Amazon storage so it helps to keep your database slimmer. You still have access and can download the attachments from LiveAgent as if they were in database, but it doesn't occupy space on your hosting/server.

After you enable and configure the plugin, LiveAgent will start quietly moving the data in the background by a cron task. You can keep working and using LiveAgent as usual, the application will take care of the rest.

AWS S3 configuration

in AWS S3 configuration you need to create user, create bucket, and grant the user permissions to the bucket.

When creating new user, you only enter the User name and select Access type Programmatic access.

 

Then when you get to the end of creation wizard, copy the user's Secret access key by clicking on Show. You won't be able to copy it later, you would need to create a new access key.

 

In user's summary, copy User ARN and save it - we will need this later in Policy generator.

 

Create bucket in the region geographically close to location of your server, you can leave all other options default.

and in Permissions > Bucket Policy copy the Bucket ARN and click on the link to Policy generator

In Policy generator enter User ARN to the Principal field, and Bucket ARN plus '/*' in the Amazon Resource Name field (if your bucket ARN is "arn:aws:s3::liveagentaws", this field will contain "arn:aws:s3::liveagentaws/*", which means this policy applies on all objects inside the bucket). In Actions select 2 items: GetObject and PutObject. Fill the rest of fields as in the picture below. Then just click Add statement and Generate policy.
 
 
The generated policy will be displayed in a small popup form. Copy the policy into the Bucket policy configuration section.
 
 
 

Plugin configuration

 

Access Key and Secret Key - you get these while creating a user in your Amazon console under Services->IAM->Users.

Files Bucket Name - the name of your Amazon bucket into which you want to store your files. You get the name while creating the bucket from the console in Services->S3->Create bucket. (Leave empty if files should not be moved to AWS S3.)

Mails Bucket Name - the name of your Amazon bucket into which you want to store your mails. You get the name while creating the bucket from the console in Services->S3->Create bucket. (Leave empty if mails should not be moved to AWS S3.)

Messages Bucket Name - the name of your Amazon bucket into which you want to store your message contents. You get the name while creating the bucket from the console in Services->S3->Create bucket. (Leave empty if message contents should not be moved to AWS S3.)

Client version - if you don't know what this then just leaves it as it is with latest

Region - You need to get the code of the region you selected for your bucket, to get the code you can search for your region in https://docs.aws.amazon.com/general/latest/gr/rande.html#s3_region

Time to store files locally [hours] - time for how long should the files be kept on your hosting/server database before they are moved to Amazon. Should be some time after you expect not to access the files that often anymore.

Min file size [bytes] - only files larger than defined size will be moved to Amazon.

By default, Amazon would bill you for every 128kB so even if you would set this limit to lower value than 131072B (128kB) and a smaller file would be moved to your Amazon storage you would still have to pay for it as if it was 128kB. However, it might still be significantly cheaper than storing the data on your own hosting/server.

Note: if you should activate this option after you have already tickets and attachments created in your database it won't really free up your hard drive space without running optimize task on your qu_g_file_contents database table.
Please keep in mind that when message contents are no longer in MySQL database, the only way to search in tickets is to use elasticsearch, where all tickets are indexed. Without elasticsearch the search would only work for the most recent tickets, which still have their messages in MySQL database.

If you ever decide to stop using AWS S3 storage, it requires manually planning un-archiving background tasks, which move ticket messages and file contents back to the database. Contact us if you need assistance.

S3 storage structure

File contents

The file contents are stored as simple binary files in path

{filecontents_bucket}/{database_name}/{fileid}

where {fileid} is 32 alphanumeric characters matching value of fileid column in database table qu_g_files

Mail messages

sources of received emails are stored as text files in path

{messagesources_bucket}/{database_name}/{messageid}

where {messageid} is integer identifier matching with a value in column messageid in table qu_g_mail_messages

Ticket messages

Ticket messages are most complicated of the 3 types. There have been multiple modificaitons to the way how messages are stored, but due to the amount of stored data it was too complicated to convert or migrate between these storage formats, so a modified application source code capable to handle all storage formats has been developed instead.

The oldest format is where ticket messages are stored as plain text in path

{messages_bucket}/{database_name}/conversation_{conversationid}/{message_groupid}/{messageid}

where {conversationid} {message_groupid} and {messageid} are values in corresponding columns in table qu_la_messages.

Second format is where message contents of all messages of the same message group are stored together in a JSON file. The file is stored in path 

{messages_bucket}/{database_name}/conversation_{conversationid}/{message_groupid}.json

and the structure of the JSON is

{
  "message1id"::message1content",
  "message2id":"message2content"
}

where message1id corresponds to value of messageid column in table qu_la_messages and message1content is the message content, which has been set to empty in the database column qu_la_messages.message.

The currently used format is similar to the previous in that it is also JSON encoded, but also the whole JSON file encrypted using AES256 cipher. Each JSON file is encrypted using a slightly different key to make it harder for attacker in case anyone would gain access to the S3 storage, although it is highly unlikely. The key for each JSON file is generated from multiple attributes of the message, message_group, conversation and a setting stored on your server filesystem. The path in which the encrypted JSON files are saved is also slightly different 

{messages_bucket}/{database_name}/c_{conversationid}/{message_groupid}.encoded