From 3965c222ec138163346a4e9e596f3ac15b3ead60 Mon Sep 17 00:00:00 2001
From: Runxi Yu <me@runxiyu.org>
Date: Mon, 13 Jan 2025 08:48:34 +0800
Subject: Add addresses

---
 schema.sql | 22 ++++++++++++++--------
 1 file changed, 14 insertions(+), 8 deletions(-)

(limited to 'schema.sql')

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
 );
-- 
cgit v1.2.3

