The database tables

Here's where you can find all the detail on how Archiveopteryx stores its mail — what's in each table, what each row is, what each column is. In other words, this is not the simple introduction.

The main tables are mailboxes, mailbox_messages and messages. Messages holds messages, mailboxes holds mailboxes and mailbox_messages records which messages are in which mailboxes. (If a message in in two mailboxes, there will be two mailbox_messages rows but only one messages row.)

Connecting, logging in and reading data is governed by the users and permissions tables. Users holds users' own data, and permissions records what access users have granted to other users.

The messages table doesn't actually contain all the mail. Instead it has a variety of helper tables: address_fields for From, To and friends, header_fields for Subject etc., date_fields for Date, bodyparts for message bodies and the helper table part_numbers to tie messages to bodyparts.

The addresses table deserves mention. It's used to hold every email address in the system, and is used all the time.

The retention_policies table determines how long mail must be retained before users are allowed to delete it, as well as when aox vacuum deletes it forcibly.

Three tables are used to hold user-specified information about messages and mailboxes: flags record whether a message is read or unread, etc., annotations is practically unused (see IMAP ANNOTATE for more) and subscriptions records which mailboxes each user is particularly interested in (used by some people, not all).

There are also some other small peripheral helpers: field_names, flag_names and annotation_names.

Outgoing mail delivery uses two special tables, namely deliveries and delivery_recipients.

The mailstore table contains the schema version number (88 at present). The command aox upgrade schema upgrades the schema automatically when we've extended it.

Finally there are some semi-internal tables. The list below includes everything.

In case of questions, please write to info@aox.org.

Relevant links

About this page

Last modified: 2010-11-19
Location: aox.org/db/