Message bodies stored in MySQL and attachments stored on my servers

[ktwalrus]

I'm very new to RC (just discovered today) and I am trying to decide if RC is appropriate for my needs.

All messages that will be sent to the users' mailboxes will be sent only by RC (my smtp servers will enforce this) and the recipients will be restricted by allowing only vetted email addresses from the contacts list (I need to control the list of addresses in each user's contacts list).  Also, the messages will have an average of 100 to 200 recipients.

The users mailboxes will be on various different servers, but I control all servers.

Since the messages go to a large number of users, I want to store the HTML message bodies that are sent in my own MySQL databases that are separate from the mail servers.  I also want to store the attachments on my servers as many messages will attach large files (mostly videos).

So, I'm thinking I will need to "intercept" the mail as it is sent from RC, store the attachments and HTML message bodies as I wish and "forward" the message on to the appropriate mail server with the message body only containing information that will allow me to reconstruct the message body (HTML and the attachments).  I want to "embed" the videos into RC message view page and not have RC show the attachments as attachments (but as HTML, like embedding a YouTube video in HTML5).

So, my questions are:

• Can RC upload attachments to a separate server and not include the attachment in MIME format in the messages that it sends, but send a link to the attachment?  Or, do I need to code this feature myself?
• Can I filter the messages as they are sent from RC so that I can store the message body in MySQL and change the message body to a link that will retrieve the message body from MySQL?
• Can I filter messages as the message body is retrieved from the IMAP connection?  That is, can I change the body of all messages as they are retrieved to change the link to the original HTML that was stored in MySQL and the attachments turned into embedded HTML?
• If RC doesn't have support for the above built-in, will it be straight-forward for me to code a plug-in?

I can code, but I'm looking for advice from others as to what the best approach is for me to pursue.

[ktwalrus]

I've been reading more and it looks like I can do what I need by writing a plug in.

[SKaero]

I'm not sure what your doing on some of the finer points but you should be able to do the message changing and storing easily with the plugin api.

[ktwalrus]

I think it is turning out even easier for me.  I'm planning on configuring sendmail on the server that RC is running on to deliver all email to a php script (via Pipe).  This script will store the sent messages in a database.  A cron job will run periodically to read the sent messages in the database, and pre-process the messages so the attachments are stored permanently in cloud storage and HTML thumbnail links replace the attachments in the emails.  Any validating of recipients I need to do can be done at this time.  Only when I'm satisfied, will I forward the email on to the real IMAP mailboxes.

This way, I can control delivery and content without changing RC at all.

The only plugin I might have to write is to pre-process the messages as they are retrieved through IMAP to replace the HTML bodies with the real bodies (as stored in my database).  I only need to do this preprocessing if I decide to send only a link to retrieve the real body in the messages which is a storage optimization.

So, I think I am all set with my plan to use RC.

[SKaero]

Well good luck with the build.

[ktwalrus]

Thanks.

I've since discovered that I can have a plugin that can alter the message before it is sent.  I haven't implemented it yet, but I think this plugin could just store the message in the database and send it to an archive email account.  I can then have a cron job run to upload the message html and attachments to my Amazon S3 bucket, change the HTML body to have links to the attachments (and thumbnails) in the S3 bucket and forward the message on to the IMAP mailboxes.  This also gives me the opportunity to search the message body for any embed links (like to videos or external images) and replace them with HTTPS served thumbnails (using embed.ly).

This is turning out to be rather simple...  I may not even bother with replacing the HTML message body with a link to the S3 copy since I was mainly worried about replicating 10MB attachments into 100 or 200 mailboxes.  Just getting the attachments into the cloud will be a big help.  I can do virus scanning and other security checks on the attachments before I forward the emails on to the real recipients. 

[ktwalrus]

It just got even simpler for me.  I noticed that Dovecot (my IMAP server) has the option to do Single Instance Storage for Attachments.  So, now I don't even need to fake this by replacing attachments with links to Amazon S3 thumbnails with download link.

The only thing I'm going to do now is to implement a plugin to save the messages in the db and send them to an archive address.  I will then have a cron job to scrub the messages and forward them to the real recipients at a time of my choosing (I'm thinking of delaying all mail forwarding by a set number of hours maybe even overnight as the emails are never "urgent").