max / goingson

1.0 KB · 27 lines History Blame Raw

1	-- Email account configuration (SQLite)
2	CREATE TABLE email_accounts (
3	id TEXT PRIMARY KEY NOT NULL,
4	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
5	account_name TEXT NOT NULL,
6	email_address TEXT NOT NULL,
7	imap_server TEXT NOT NULL,
8	imap_port INTEGER NOT NULL DEFAULT 993,
9	smtp_server TEXT NOT NULL,
10	smtp_port INTEGER NOT NULL DEFAULT 587,
11	username TEXT NOT NULL,
12	password TEXT NOT NULL,
13	use_tls INTEGER NOT NULL DEFAULT 1,
14	last_sync_at TEXT,
15	created_at TEXT NOT NULL DEFAULT (datetime('now')),
16	UNIQUE(user_id, email_address)
17	);
18
19	-- Add columns to emails table for IMAP tracking
20	ALTER TABLE emails ADD COLUMN message_id TEXT;
21	ALTER TABLE emails ADD COLUMN email_account_id TEXT REFERENCES email_accounts(id) ON DELETE SET NULL;
22	ALTER TABLE emails ADD COLUMN is_outgoing INTEGER NOT NULL DEFAULT 0;
23
24	CREATE INDEX idx_email_accounts_user ON email_accounts(user_id);
25	CREATE INDEX idx_emails_message_id ON emails(message_id);
26	CREATE INDEX idx_emails_account ON emails(email_account_id);
27