path: root/schema.sql

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
);