The Archiveopteryx schema

Below is the exact schema used by Archiveopteryx at the time of writing. Each link points to an explanatory page for that table. 


-- This table contains information internal to the mailstore.
-- For now, its only purpose is to coordinate schema updates.

create table mailstore (
    revision    integer not null primary key
);
insert into mailstore (revision) values (73);

-- One entry for each unique address we've encountered.

create table addresses (
    id          serial primary key,
    name        text,
    localpart   text,
    domain      text
);
create unique index addresses_nld_key on addresses(name,localpart,lower(domain));
create index ald on addresses(lower(localpart), lower(domain));

-- One entry per "other users" namespace. Personal namespaces are
-- implicitly created within these.

create table namespaces (
    id          serial primary key,
    name        text not null unique
);
insert into namespaces (name) values ('/users');

-- One entry per Archiveopteryx user. Used for authentication.

create table users (
    id          serial primary key,
    login       text,
    secret      text,
    parentspace integer not null references namespaces(id)
);
create unique index u_l on users (lower(login));

-- One entry per named group.

create table groups (
    id          serial primary key,
    name        text
);

-- One entry for each group member.

create table group_members (
    groupname   integer not null references groups(id),
    member      integer not null references users(id),
    primary key (groupname, member)
);

-- One entry per deliverable mailbox.

create table mailboxes (
    id          serial primary key,
    name        text not null unique,
    owner       integer references users(id),

    -- The UID that will be assigned to the next delivered message.
    -- Incremented after each successful delivery.
    uidnext     integer not null default 1,

    -- The next modsequence value for this mailbox.
    nextmodseq  bigint not null default 1,

    -- The UID of the first message that should be marked \Recent.
    -- Set to uidnext when each new IMAP session is created.
    first_recent integer not null default 1,

    -- The IMAP mailbox UIDVALIDITY value, which, along with a message UID,
    -- is forever guaranteed to uniquely refer to a single message.
    uidvalidity integer not null default 1,

    -- When a mailbox is deleted, its entry is marked (not removed), so
    -- that its UIDVALIDITY can be incremented if it is ever re-created.
    deleted     boolean not null default false
);

-- One entry per delivery alias: mail to the given address should be
-- accepted and delivered into the given mailbox.

create table aliases (
    id          serial primary key,
    address     integer not null unique references addresses(id),
    mailbox     integer not null references mailboxes(id)
                on delete cascade
);

alter table users add alias integer references aliases(id);
alter table users alter alias set not null;

-- One row per  entry for a mailbox.

create table permissions (
    mailbox     integer not null references mailboxes(id),
    identifier  text not null,
    rights      text not null,
    primary key (mailbox, identifier)
);

-- One entry per message stored

create table messages (
    id          serial primary key,
    rfc822size  integer
);

-- One (mailbox, uid) entry per message and mailbox.

create table mailbox_messages (
    mailbox     integer not null references mailboxes(id),
    uid         integer not null,
    message     integer not null references messages(id),
    idate       integer not null,
    modseq      bigint not null,
    primary key (mailbox, uid)
);

create index mm_m on mailbox_messages(message);

-- One entry for the text of each unique MIME body part.
-- Entries here may be shared by more than one message.

create sequence bodypart_ids;
create table bodyparts (
    id          integer default nextval('bodypart_ids') primary key,
    bytes       integer not null,
    hash        text not null unique,
    text        text,
    data        bytea
);

-- A list of bodyparts belonging to each message, with IMAP part numbers.

create table part_numbers (
    message     integer references messages(id) on delete cascade,
    part        text not null,
    bodypart    integer references bodyparts(id),
    bytes       integer,
    lines       integer,
    primary key (message, part)
);
create index pn_b on part_numbers(bodypart);

-- One entry for each field name we've seen (From, To, Subject, etc.).
-- (This table is partially populated from the field-names file.)

create table field_names (
    id          serial primary key,
    name        text unique
);

-- A list of the header fields associated with each bodypart in a message.

create table header_fields (
    id          serial primary key,
    message     integer not null,
    part        text not null,
    position    integer not null,
    field       integer not null references field_names(id),
    value       text,
    unique (message, part, position, field),
    foreign key (message, part)
                references part_numbers(message, part)
                on delete cascade
);

create index hf_msgid on header_fields(value) where field=13;

-- A list of addresses associated with each message.

create table address_fields (
    message     integer not null,
    part        text not null,
    position    integer not null,
    field       integer not null,
    number      integer,
    address     integer not null references addresses(id),
    foreign key (message, part)
                references part_numbers(message, part)
                on delete cascade
);
create index af_mp on address_fields (message, part);

-- The Date field from each message.

create table date_fields (
    message     integer not null references messages(id)
                on delete cascade,
    value       timestamp with time zone
);

create index df_m on date_fields(message);

-- One entry per unique thread.

create table threads (
    id          serial primary key,
    mailbox     integer not null references mailboxes(id),
    subject     text,
    unique (mailbox, subject)
);

-- One entry per message in each thread.

create table thread_members (
    thread      integer not null references threads(id),
    mailbox     integer not null,
    uid         integer not null,
    primary key (thread, mailbox, uid),
    foreign key (mailbox, uid)
                references mailbox_messages(mailbox, uid)
                on delete cascade
);

-- One entry per user-defined flag name to be used in flags.

create table flag_names (
    id          serial primary key,
    name        text
);
create unique index fn_uname on flag_names(lower(name));

-- One entry per user-defined IMAP message flag per message.

create table flags (
    mailbox     integer not null,
    uid         integer not null,
    flag        integer not null references flag_names(id),
    foreign key (mailbox, uid)
                references mailbox_messages(mailbox, uid)
                on delete cascade
);
create index fl_mu on flags (mailbox, uid);

-- One entry per subscribed mailbox per user.

create table subscriptions (
    id          serial primary key,
    owner       integer not null references users(id)
                on delete cascade,
    mailbox     integer not null references mailboxes(id),
    unique(owner, mailbox)
);

-- One entry per vendor- or RFC-defined name to be used in annotations

create table annotation_names (
    id          serial primary key,
    name        text unique
);

-- One entry per annotation

create table annotations (
    id          serial primary key,
    mailbox     integer not null,
    uid         integer not null,
    owner       integer references users(id) on delete cascade,
    name        integer not null references annotation_names(id),
    value       text,
    unique (mailbox, uid, owner, name),
    foreign key (mailbox, uid)
                references mailbox_messages(mailbox, uid)
                on delete cascade
);

-- One entry per view: the view is a mailbox that contains the result
-- of applying the selector to the source mailbox.

create table views (
    id          serial primary key,
    source      integer not null references mailboxes(id)
                on delete cascade,
    view        integer not null references mailboxes(id)
                on delete cascade unique,
    -- We need to keep track of how far we've searched already.
    nextmodseq  bigint not null,
    selector    text
);

-- One entry per SIEVE script owned by a user.

create table scripts (
    id          serial primary key,
    owner       integer not null references users(id)
                on delete cascade,
    name        text,
    active      boolean not null default 'f',
    script      text,
    unique (owner, name)
);

-- One entry per deleted (EXPUNGEd) message, which says "message #n used
-- to be (mailbox,uid) until it was deleted_by ... at ...". A given pair
-- of (mailbox,uid) may exist either in mailbox_messages or here, never
-- in both.

create table deleted_messages (
    mailbox     integer not null references mailboxes(id),
    uid         integer not null,
    message     integer not null references messages(id)
                on delete cascade,
    modseq      bigint not null,
    deleted_by  integer references users(id),
    deleted_at  timestamp with time zone not null
                default current_timestamp,
    reason      text,
    primary key (mailbox, uid)
);

create index dm_mud on deleted_messages(mailbox,uid,deleted_at);
create index dm_m on deleted_messages(message);

-- When an entry is inserted into deleted_messages, we delete the
-- corresponding row from mailbox_messages.

create function delete_message() returns trigger as $$
begin
    delete from mailbox_messages where mailbox=NEW.mailbox and uid=NEW.uid;
    return NULL;
end;
$$ language plpgsql security definer;

create trigger deleted_messages_trigger
after insert on deleted_messages
for each row execute procedure delete_message();

-- One entry for each pending SMTP-submitted delivery.

create table deliveries (
    id          serial primary key,
    sender      integer not null references addresses(id),
    message     integer not null references messages(id)
                on delete cascade
                unique,
    injected_at timestamp with time zone,
    expires_at  timestamp with time zone,
    tried_at    timestamp with time zone
);

-- One or more rows identifying recipients for each entry in the
-- deliveries table.

create table delivery_recipients (
    id          serial primary key,
    delivery    integer not null references deliveries(id)
                on delete cascade,
    recipient   integer not null references addresses(id),
    last_attempt timestamp with time zone,
    action      integer not null default 0,
    status      text
);

-- Each entry contains a single user's access key to a given mailbox.
-- (See URLAUTH, RFC 4467.)

create table access_keys (
    userid      integer not null references users(id)
                on delete cascade,
    mailbox     integer not null references mailboxes(id)
                on delete cascade,
    key         text not null,
    primary key (userid, mailbox)
);

-- One entry for each bodypart that represents an unparseable message
-- that was wrapped as an application/octet-stream.

create table unparsed_messages (
    bodypart    integer not null references bodyparts(id)
                on delete cascade,
    primary key(bodypart)
);

-- One entry for every autoresponse we send.

create table autoresponses (
    id          serial primary key,
    sent_from   integer not null references addresses(id),
    sent_to     integer not null references addresses(id),
    expires_at  timestamp with time zone not null
                default current_timestamp+interval '7 days',
    handle      text
);

-- One entry for every (authenticated) connection made to any of the
-- servers.

create table connections (
    id          serial primary key,
    userid      integer references users(id),
    address     inet not null,
    port        integer not null,
    mechanism   varchar not null,
    authfailures integer not null,
    syntaxerrors integer not null,
    started_at  timestamp with time zone not null,
    ended_at    timestamp with time zone not null
);

-- One entry for each mailbox used by a given script as a target for a
-- "fileinto". These mailboxes must not be deleted, since that would
-- cause the referring script to fail.

create table fileinto_targets (
    id          serial primary key,
    script      integer not null references scripts(id)
                on delete cascade,
    mailbox     integer not null references mailboxes(id),
    unique(script, mailbox)
);

Archiveopteryx also initialises the field_names table with about twenty common fields (Subject, From, etc) and the flag_names table with some message flags defined by IMAP (\seen, \answered, etc). Both tables are later extended as needed.

Finally, Archiveopteryx grants privileges on an as-needed basis. Where should we document how that's done? Each page table?

In case of questions, please write to info@oryx.com.

Relevant links

About this page

Last modified: 2008-06-25
Location: aox.org/db/schema