--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/bugs.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,86 @@
+--
+-- PR data.
+--
+-- PRs is the primary table of bug info, with one row per problem
+-- report.
+--
+
+CREATE SEQUENCE next_PR;
+
+CREATE TABLE PRs (
+	id bigint		primary key default nextval('next_PR'),
+
+	-- basic description
+	synopsis text		not null,
+	confidential boolean	not null,
+
+	-- states
+	--
+	-- there are 64 combinations but only these 15 are valid:
+	--    (un)locked closed			("closed")
+	--    (un)locked closed analyzed	("closed")
+	--    (un)locked closed invalid		("dead")
+	--    unlocked open			("open")
+	--    unlocked open analyzed		("analyzed")
+	--    unlocked open analyzed? feedback	("feedback")
+	--    unlocked open analyzed? suspended	("suspended")
+	--    unlocked open analyzed? feedback suspended ("stuck")
+	--    unlocked open invalid feedback	("incomplete")
+	--
+	-- gnats states map to: open	inval.	anal.	feedb.	susp.
+	--   open		open
+	--   analyzed		open analyzed
+	--   feedback		open feedback
+	--   (pullups-needed)	open (*)
+	--   pending-pullups	open (*)
+	--   suspended		open analyzed suspended
+	--   (stuck)		open feedback suspended
+	--   closed		-
+	--   dead		invalid
+	--
+	-- The cases marked (*) are distinguished from open by the
+	-- branchstate in the relevance table.
+
+	locked boolean		not null,	-- deny modifications
+	open boolean		not null,	-- master switch
+
+	invalid boolean		not null,	-- report is no good
+	analyzed boolean	not null,	-- issue is believed understood
+	feedback boolean	not null,	-- feedback required
+	suspended boolean	not null,	-- work halted
+
+	-- feedback and suspended imply open
+	check NOT (NOT open AND (feedback OR suspended)),
+	-- invalid precludes analyzed and suspended
+	check NOT (invalid AND (analyzed OR suspended)),
+	-- open and invalid implies feedback
+	check NOT (NOT feedback AND open AND invalid),
+	-- locked implies not open
+	check NOT (open AND locked),
+
+	-- fixed-size history
+	arrival_schemaversion int  not null,
+	arrival_date date	not null,
+	closed_date date	,
+
+	-- original submitter
+	originator bigint	references users (id),
+
+	-- original submission
+	-- we don't keep this as such - these items go into an
+	-- entry in the admin log instead, and the submitter is
+	-- automatically subscribed to the bug.
+	-- "Submitted by joe@schmoe, Message-Id <3@schmoe>, Subject: foo"
+	--from_address text	not null,
+	--mail_subject text	not null,
+	--mail_msgid text	not null,
+
+	-- contents
+	release text		,
+	environment text	,
+	description text	,
+	how_to_repeat text	,
+	fix text		,
+	unformatted text	
+
+) without oids;