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 (88); -- 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, ldapdn 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 ); -- When aoximport or others create /users/foo/bar, bar needs to own -- the mailbox, so ensure that that happens. create function set_mailbox_owner() returns trigger as $$ begin if new.owner is null then select into new.owner u.id from users u join namespaces n on (u.parentspace=n.id) where new.name like n.name||'/'||u.login||'/%' or new.name = n.name||'/'||u.login limit 1; end if; return new; end; $$ language 'plpgsql'; create trigger mailbox_owner_trigger before insert on mailboxes for each row execute procedure set_mailbox_owner(); -- Ensure that mailboxes cannot be deleted while something relies on -- their existence create function check_mailbox_update() returns trigger as $$ declare address text; begin notify mailboxes_updated; if new.deleted='t' and old.deleted='f' then perform * from mailbox_messages where mailbox=new.id; if found then raise exception '% is not empty', new.name; end if; select a.localpart||'@'||a.domain into address from addresses a join aliases al on (a.id=al.address) where al.mailbox=new.id; if address is not null then raise exception '% used by alias %', new.name, address; end if; perform * from fileinto_targets where mailbox=new.id; if found then raise exception '% is used by sieve fileinto', new.name; end if; end if; return new; end; $$ language 'plpgsql'; create trigger mailbox_update_trigger before update on mailboxes for each row execute procedure check_mailbox_update(); -- 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); -- 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, idate integer not null, 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), modseq bigint not null, seen boolean not null default false, deleted boolean not null default false, 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) ); -- One entry for each message that a poor misguided Exchange/Outlook -- contributes to its idea of a thread. Used to turn E/O threading -- into what the rest of the universe uses. create table thread_indexes ( -- Grant: select, insert message integer not null references messages(id) on delete cascade, thread_index text ); create index ti_outlook_hack on thread_indexes(thread_index); -- One row for each explicit retention policy defined by the -- administrator. create table retention_policies ( id serial primary key, action text not null, mailbox integer references mailboxes(id), duration integer not null, selector text, constraint rp_action check (action in ('retain','delete')) ); create or replace function notify_retention_policies() returns trigger as $$ begin notify retention_policies_updated; return NULL; end;$$ language 'plpgsql'; create trigger retention_policies_trigger after insert or update or delete on retention_policies for each statement execute procedure notify_retention_policies(); -- Schema downgrade functions. create or replace function downgrade_to_84() returns int as $$ begin return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_83() returns int as $$ begin drop table retention_policies; return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_82() returns int as $$ begin insert into flags (mailbox, uid, flag) select mailbox, uid, (select id from flag_names where name=E'\\Seen') from mailbox_messages where seen; insert into flags (mailbox, uid, flag) select mailbox, uid, (select id from flag_names where name=E'\\Deleted') from mailbox_messages where deleted; alter table mailbox_messages drop seen, drop deleted; return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_81() returns int as $$ begin drop trigger mailbox_update_trigger on mailboxes; drop function check_mailbox_update(); return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_80() returns int as $$ begin drop trigger mailbox_owner_trigger on mailboxes; drop function set_mailbox_owner(); return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_85() returns int as $$ begin drop trigger retention_policies_trigger on retention_policies; drop function notify_retention_policies(); return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_86() returns int as $$ begin return 0; end;$$ language 'plpgsql'; create or replace function downgrade_to_87() returns int as $$ begin alter table users alter alias set not null; return 0; end;$$ language 'plpgsql';

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@aox.org.

Relevant links

About this page

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