schema.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE TABLE accounts (
	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	username TEXT NOT NULL,
	password TEXT -- NULL for locked accounts
);

CREATE TABLE mailboxes (
	id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	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_id INTEGER NOT NULL,
	FOREIGN KEY(mailbox_id) REFERENCES mailboxes(id),
	data bytea
);