--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/database/schema/users.sql	Sun Feb 19 19:54:48 2012 -0500
@@ -0,0 +1,75 @@
+--
+-- Users.
+--
+-- Rather than trying to have different kinds of users (as in the last
+-- rev of this stuff) I think we will just have user permissions.
+-- Otherwise we end up in various kinds of trouble if users change
+-- type. This means we cannot use foreign key constraints to e.g.
+-- ensure that non-developers are not responsible for bugs; however,
+-- I guess we can cope.
+--
+-- The permissions are:
+--    mailto		we can mail to the user's e-mail address on file
+--    oldresponsible	allowed to be responsible for closed PRs
+--    responsible	allowed to be responsible for PRs
+--    editpr		can do edit-pr things
+--    admin		can do destructive things
+--
+-- The following types of users should have permissions as follows:
+--    peon              none or mailto
+--    submitter         mailto
+--    role              mailto|oldresponsible|responsible
+--    developer         mailto|oldresponsible|responsible|editpr
+--    administrator     mailto|oldresponsible|responsible|editpr|admin
+--    retired devel     mailto|oldresponsible
+--    deceased devel           oldresponsible
+--
+-- However, this may vary; e.g. users whose mail address bounces might
+-- have their mailto permission revoked.
+--
+-- The "webpassword" is for use logging in over unsecured HTTP to do
+-- things submitters can do. It is assumed that developers (and
+-- administrators, who should all be developers) authenticate some
+-- other way.
+-- 
+
+CREATE SEQUENCE next_user;
+
+CREATE TABLE users (
+	id bigint		primary key default nextval('next_user'),
+	username text		,
+	realname text		,
+
+	-- permissions --
+	mailto boolean		not null,
+	oldresponsible boolean	not null,
+	responsible boolean	not null,
+	editpr boolean		not null,
+	admin boolean		not null
+
+	-- responsible implies oldresponsible
+	check NOT (responsible AND NOT oldresponsible)
+)
+WITHOUT OIDS;
+
+CREATE TABLE mailaddresses (
+	id bigint		not null references users (id),
+	selected boolean	not null,
+	email text		not null,
+	organization text	,
+	webpassword text	,
+	lastheardfrom date
+)
+WITHOUT OIDS;
+
+CREATE VIEW usermail AS
+   SELECT id, username, realname, email,
+          mailto, responsible, editpr, admin
+   FROM users, mailaddresses
+   WHERE users.id = mailaddresses.id AND mailaddresses.selected
+;
+
+-- Intended constraint:
+-- (oldresponsible OR responsible OR editpr OR admin)
+-- implies username not null.
+