| Fields | Data type | Comments |
|---|---|---|
| message_id | serial | primary key |
| email_message_id | character varying (255) | a (supposedly) unique message id |
| sender_email | character varying (255) | the from email address |
| subject | character varying (255) | |
| thread_addr | character varying (255) | thread address for dlists |
| datetime | integer | unix timestamp in UTC time |
| in_reply_to | character varying (255) | message.email_message_id |
| to | character varying (255) | to email address |
| cc | character varying (255) | cc email address |
| headers | text | all headers |
| body | text |
in_reply_to together with subject should help non-dlist lists in the grouping of messages. dlist-enabled lists would use thread_id instead. The second messageid refers to the mailman id for the message (something like 2008042321565.BAB25121@xyz.com) and not the database's primary key.
The new fields here (messageid, date, in-reply-to, and body) will be added when generating a new mailing list database. When messages are sent to the list and archiving is turned on, those messages will be inserted into the database and appended to mbox files. There should also be a script to process the current mbox files into database insert statements.