The deleted_messages table

The deleted_messages table stores the identity of all recently deleted messages. It is used to support the aox undelete feature, and will also be used to support our message retention policy feature (using which it will be possible to say "do not delete mail until five years have passed" etc.)

Message is what was deleted, and mailbox is where.

The uid column exists because a trigger needs it. The only way to delete from the mailbox_messages table is to insert into deleted_messags and let a trigger clean up. That trigger needs the UID.

The deleted_by, deleted_at and reason columns record who did deleted it any why. If a mailbox has to be undeleted, looking at how messages were deleted may be convenient. (In our experience, the problem usually is that people accidentally press the wrong button on their mail readers and the mail readers quickly deletes a lot of mail.)

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) );

The deleted_messages table was introduced in version 1.1.

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

Relevant links

About this page

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