Logging all site email to a mysql database.

Basic steps involved:

• Configure your MTA to deliver all mail normally, and also to your db server

• Have a procmail recipe that delivers all email to a queue directory

• Have a process that monitors that queue directory and does this for each file:

  • makes sure it's not spam or other garbage

  • use a regex to extract the To list, CC list, BCC list, From list, reply to, subject line and date

  • convert the email to html and save it

  • identify the company that this email is associated with

  • If we found a company database, log it to their email table. Otherwise log it to master.email

Database and table layout:

Every company has it's own database, plus one 'master' database. Each company has four tables: contacts, email, notes, userlist.

CREATE TABLE `contacts` (
`id` int(11) NOT NULL auto_increment,
`lastname` varchar(255) default NULL,
`firstname` varchar(255) default NULL,
`email` varchar(255) default NULL,
`email2` varchar(255) default NULL,
`email3` varchar(255) default NULL,
`title` varchar(255) default NULL,
`phone1` varchar(255) default NULL,
`phone2` varchar(255) default NULL,
`phone3` varchar(255) default NULL,
`phone4` varchar(255) default NULL,
`phone5` varchar(255) default NULL,
`notes` longtext,
PRIMARY KEY (`id`)

CREATE TABLE `email` (
`id` mediumint(9) NOT NULL auto_increment,
`emailfrom` varchar(255) default NULL,
`replyTo` varchar(255) default NULL,
`emailto` text,
`emailcc` text,
`subject` text,
`emaildatetime` datetime default NULL,
`inserteddatetime` datetime default NULL,
`message` varchar(255) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `message` (`message`)

CREATE TABLE `notes` (
`id` mediumint(9) NOT NULL auto_increment,
`inserteddatetime` datetime default NULL,
`username` varchar(255) default NULL,
`notedatetime` datetime default NULL,
`notetext` longtext,
PRIMARY KEY (`id`)

CREATE TABLE `userlist` (
`email_address` varchar(255) NOT NULL,
`phone` tinytext,
`company` tinytext,
`domain` tinytext,
`fullname` tinytext,
PRIMARY KEY (`email_address`)

The contacts table holds contacts information for employees at a company that are not necessarily users in our system. The email table holds the email (with the message field being a fullpath to the html email message, not the message itself). The notes table contains notes for each company entered in by a customer service representative. The userlist table contains contact information for employees at a company that have usernames in our system.