Need linux milter solution to log email body into database

Question

We exchange emails with various clients and vendors. I am trying to implement a solution where all the email communications between our rep and the client or vendor is saved in our database so in our admin system we have a record of the communications. I have seen similar system for some of the project sites like Guru.com or elance.com. You communicate back and forth via email, but the system logs the conversation.

I have been searching for a couple of days to try and figure out how to implement this. In studying emails form Guru.com I see they all go to a common email address. The subject contains an encrypted ID and "From: (freelancer's name or project owner's name)" So it seem that there is a milter on that box that parsers the body of that email, saves it to the DB, and forwards the email on to the recipient (from the subject line).

I use a vendor for my email, but run my own web server (EC2, AWS Linux), so I am thinking I can just create a second MX record for say "mail2" and use that for this application. Then I could use something like "parseme@mail2.mydomain.com" and have my server process those emails. I am thinking this can be done with a sendmail milter, but am not exactly sure.

My search for assistance or instruction on this has not turned up anything. I may not know the right term to search for. I have read about many milters, but none that seem to solve this. Can anyone offer any help or point me in the right direction?

Thanks

Thom

Answer 1

The typical flow is rather one of delivery. Your question is tagged procmail so I assume a Procmail solution is acceptable.

You should not need any additional MX records or other shenanigans. Sendmail (or any modern MTA; I would recommend Postfix if you are not particularly married to Sendmail for legacy reasons) can run a script on an incoming message quite trivially.

When a message is accepted for delivery, the MDA (in your case, Sendmail) looks for any client hooks such as a .forward file. If one is found, the file is parsed, and any pipeline in the file is executed. This is how Procmail is typically invoked on legacy systems (although more recently, a canned recipe to read the user's .procmailrc and invoke Procmail if one is found is part of the standard Sendmail feature set).

Instead of Procmail, you could run a script of your own; or you could run the script from your .procmailrc (which is beneficial because Procmail takes care of a number of pesky error scenarios).

:0
| /path/to/script

Now, Procmail will open /path/to/script with the message as standard input, and assume that the script takes care of processing it (delivering it, and/or parsing and then discarding it).

Add a :c flag if you want Procmail to also save to $DEFAULT:

:0c
| /path/to/script

Maybe add the recipient address as a parameter to the script:

ADDR=`formail -rtzxTo:`
:0c
| /path/to/script "$ADDR"

Your script could be quite simple; I leave the database details to you, and show a simple Perl script to log each incoming message to a file.

#!/usr/bin/perl

use strict;
use warnings;

open (DB, ">>", "/path/to/file.db") or die "Complication: $!";
print DB "Correspondent: $ARGV[1]\n";
while (<>) {
    print DB;
}
close DB;

This is ideal for incoming email, but the simple and straightforward way to also get your outbound communications logged is simply to Bcc: the account address on outgoing messages. On the other hand, if you use a web interface for the interactions, you could do the writing from the web scripts, then generate an email and send it, and use Procmail (or something similar) only for the incoming part of the conversation. This would simplify the system somewhat, at the expense of sacrificing the possibility of using the system simply by writing emails, as would come quite naturally from this sort of system.

The above Perl script is simple enough -- and Procmail is versatile enough -- that you could do all of this from your .procmailrc, but for actual database processing, you will need a small external script in any event.