--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/subscription.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,44 @@
+--
+-- subscription lists
+-- 
+-- Users subscribe to PRs to be notified when the PR is modified.
+--
+-- If "batch" is set, notices and copies of messages are sent out
+-- in batches.
+--
+-- If "reporter" is set, the user is also nagged when the PR is in
+-- feedback. If "responsible" is set, the user is nagged when the PR
+-- is not in feedback.
+--
+-- The original submitter is automatically subscribed as a reporter.
+
+CREATE TABLE subscriptions (
+	pr bigint		not null references PRs (id),
+	user bigint		not null references users (id),
+	batch boolean		not null,
+	reporter boolean	not null,
+	responsible boolean	not null
+)
+WITHOUT OIDS;
+
+-- Intended constraints:
+--
+--    SELECT id, responsible FROM PRs, subscriptions, users
+--    WHERE PRs.id = subscriptions.id
+--      AND subscriptions.user = users.id
+--      AND PRs.state <> 'closed'
+--      AND subscriptions.responsible
+--      AND NOT users.responsible
+--    ;
+--
+-- and
+--
+--    SELECT id, responsible FROM PRs, subscriptions, users
+--    WHERE PRs.id = subscriptions.id
+--      AND subscriptions.user = users.id
+--      AND PRs.state = 'closed'
+--      AND subscriptions.responsible
+--      AND NOT users.oldresponsible
+--    ;
+--
+-- should always return nothing.