--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/messages.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,52 @@
+--
+-- Messages.
+--
+
+CREATE SEQUENCE next_rawmsgid;
+
+-- all incoming mail in original form, for reference
+-- (should be pruned periodically)
+CREATE TABLE rawmail (
+	id bigint		primary key default nextval('next_rawmsgid'),
+	data text		not null
+)
+WITHOUT OIDS;
+
+CREATE SEQUENCE next_msgid;
+
+-- comments
+CREATE TABLE messages (
+	id bigint		primary key default nextval('next_msgid'),
+	pr bigint		not null references prs (id),
+	who bigint		not null references users (id),
+	parent_id bigint	null references messages (id),
+	when date		not null,
+	body text		not null,
+
+	-- we don't keep these directly, they go into an admin log entry
+	--from_address text	not null,
+	--mail_subject text	not null,
+	--message_id text	not null,
+	rawid bigint		null references rawmail (id)
+
+	check (parent_id != commentid)
+)
+WITHOUT OIDS;
+
+-- for patches and mime-attachments
+-- if msgid is null, attachment came with original PR
+CREATE TABLE attachments (
+	pr bigint		not null references PRs (id),
+	msgid bigint		null references messages (id),
+	mimetype text		not null,
+	body text		not null
+)
+WITHOUT OIDS;
+
+-- intended constraint:
+--   SELECT messages.pr, attachments.pr
+--   FROM messages, attachments
+--   WHERE messages.id = attachments.msgid AND messages.pr <> attachments.pr
+-- should always be empty.
+-- (XXX this is gross.)
+