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 (
    -- Grant: select, update
    revision    integer not null primary key
);
insert into mailstore (revision) values (76);

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

create table addresses (
    -- Grant: select, insert
    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 group of users.

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 (
    -- Grant: select, insert, update
    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 (
    -- Grant: select, update
    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 (
    -- Grant: select, insert, delete, update
    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 (
    -- Grant: select, insert
    id          serial primary key,
    rfc822size  integer
);

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

create table mailbox_messages (
    -- Grant: select, insert, update
    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 (
    -- Grant: select, insert
    id          integer default nextval('bodypart_ids') primary key,
    bytes       integer not null,
    hash        text not null,
    text        text,
    data        bytea
);
create index b_h on bodyparts(hash);

-- One entry for each bodypart in a message.

create table part_numbers (
    -- Grant: select, insert
    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 (
    -- Grant: select, insert
    id          serial primary key,
    name        text unique
);

-- One entry for each header field in a message, except address fields.

create table header_fields (
    -- Grant: select, insert
    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;

-- One entry for each address associated with a message. Address
-- fields are stored as one or more row here.

create table address_fields (
    -- Grant: select, insert
    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 (
    -- Grant: select, insert
    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 (
    -- Grant: select, insert, update
    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 (
    -- Grant: select, insert
    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 (
    -- Grant: select, insert
    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 (
    -- Grant: select, insert, delete
    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 (
    -- Grant: select, insert, delete
    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 (
    -- Grant: select, insert
    id          serial primary key,
    name        text unique
);

-- One entry per annotation.

create table annotations (
    -- Grant: select, insert, delete, update
    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 (
    -- Grant: select, insert, delete, update
    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 (
    -- Grant: select, insert, delete, update
    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. A row here 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 (
    -- Grant: select, insert
    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_mm on deleted_messages(mailbox,modseq);
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 (
    -- Grant: select, insert, delete, update
    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
);

-- One entry for each recipient of pending outgoing mail.

create table delivery_recipients (
    -- Grant: select, insert, update
    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 (
    -- Grant: select, insert, delete
    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 (
    -- Grant: insert
    bodypart    integer not null references bodyparts(id)
                on delete cascade,
    primary key(bodypart)
);

-- One entry for every autoresponse we send.

create table autoresponses (
    -- Grant: select, insert
    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 (
    -- Grant: insert
    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 (
    -- Grant: select, insert, delete
    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-10-14
Location: aox.org/db/schema