Add addresses

1 files changed, 14 insertions, 8 deletions

diff --git a/schema.sql b/schema.sql
index 5faeb74..26f366c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,20 +1,26 @@
 CREATE TABLE accounts (
 	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-	username TEXT,
-	password TEXT
+	username TEXT NOT NULL,
+	password TEXT -- NULL for locked accounts
 );
 
 CREATE TABLE mailboxes (
 	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-	account INTEGER NOT NULL,
-	FOREIGN KEY(account) REFERENCES accounts(id),
-	name TEXT,
-	UNIQUE(account, name)
+	account_id INTEGER NOT NULL,
+	FOREIGN KEY(account_id) REFERENCES accounts(id),
+	name TEXT NOT NULL,
+	UNIQUE(account_id, name)
+);
+
+CREATE TABLE addresses (
+	address TEXT PRIMARY KEY NOT NULL,
+	mailbox_id INTEGER NOT NULL,
+	FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id)
 );
 
 CREATE TABLE mail (
 	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-	mailbox INTEGER NOT NULL,
-	FOREIGN KEY(mailbox) REFERENCES mailboxes(id),
+	mailbox_id INTEGER NOT NULL,
+	FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id),
 	data bytea
 );