Tahinli/forge

public

0 watching

★ 0

1.4 KiB

1	-- Outgoing mail (M1). The SMTP account lives here, not in forge.toml, so admins change it on
2	-- /admin/mail. The password is sealed with the instance key (secret::seal).
3	CREATE TABLE mail_settings (
4	id INTEGER PRIMARY KEY CHECK (id = 1),
5	host TEXT NOT NULL DEFAULT '',
6	port INTEGER NOT NULL DEFAULT 587 CHECK (port BETWEEN 1 AND 65535),
7	security TEXT NOT NULL DEFAULT 'starttls' CHECK (security IN ('starttls', 'tls', 'none')),
8	username TEXT NOT NULL DEFAULT '',
9	password_sealed BLOB,
10	from_addr TEXT NOT NULL DEFAULT '',
11	from_name TEXT NOT NULL DEFAULT '',
12	enabled INTEGER NOT NULL DEFAULT 0 CHECK (enabled IN (0, 1)),
13	updated_at TEXT
14	);
15	INSERT INTO mail_settings (id) VALUES (1);
16
17	-- Durable outbox. `idem_key` makes enqueue idempotent. A row is due when `sent_at` is NULL and
18	-- `next_attempt_at` is set; `lease_until` keeps two passes from sending the same row. A row with
19	-- no `next_attempt_at` and no `sent_at` has given up.
20	CREATE TABLE mail_outbox (
21	id TEXT PRIMARY KEY,
22	idem_key TEXT NOT NULL UNIQUE,
23	to_addr TEXT NOT NULL,
24	subject TEXT NOT NULL,
25	body_text TEXT NOT NULL,
26	created_at TEXT NOT NULL,
27	attempts INTEGER NOT NULL DEFAULT 0,
28	next_attempt_at TEXT,
29	lease_until TEXT,
30	sent_at TEXT,
31	last_error TEXT
32	);
33	CREATE INDEX mail_outbox_due ON mail_outbox (next_attempt_at);
34	CREATE INDEX mail_outbox_created ON mail_outbox (created_at);
35
36	ALTER TABLE users ADD COLUMN email_verified_at TEXT;