375 lines
10 KiB
Go
375 lines
10 KiB
Go
|
package db
|
||
|
|
|
||
|
|
// migration is a single named schema change with driver-specific SQL variants.
|
||
|
|
// SQLite is the reference dialect (required); postgres/mysql variants are
|
||
|
|
// filled in as those build-tagged drivers are added — until then the sqlite
|
||
|
|
// SQL is close enough to run in most cases (TEXT/BLOB/DATETIME map cleanly).
|
||
|
|
type migration struct {
|
||
|
|
name string
|
||
|
|
sql map[string]string
|
||
|
|
}
|
||
|
|
|
||
|
|
var migrations = []migration{
|
||
|
|
{
|
||
|
|
name: "0001_tenants_domains",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE tenants (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
name TEXT UNIQUE NOT NULL,
|
||
|
|
display_name TEXT,
|
||
|
|
digest_interval_mins INTEGER NOT NULL DEFAULT 60,
|
||
|
|
max_accounts INTEGER NOT NULL DEFAULT 0,
|
||
|
|
quota_mb_per_user INTEGER NOT NULL DEFAULT 2048,
|
||
|
|
settings_json TEXT NOT NULL DEFAULT '{}',
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE domains (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
domain TEXT UNIQUE NOT NULL,
|
||
|
|
active INTEGER NOT NULL DEFAULT 1,
|
||
|
|
dkim_selector TEXT,
|
||
|
|
dkim_private_key_enc BLOB,
|
||
|
|
accept_all INTEGER NOT NULL DEFAULT 1,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_domains_tenant ON domains(tenant_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0002_users_auth",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE users (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
domain_id TEXT NOT NULL REFERENCES domains(id) ON DELETE CASCADE,
|
||
|
|
email TEXT UNIQUE NOT NULL,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
display_name TEXT,
|
||
|
|
role TEXT NOT NULL DEFAULT 'user',
|
||
|
|
active INTEGER NOT NULL DEFAULT 1,
|
||
|
|
mfa_enabled INTEGER NOT NULL DEFAULT 0,
|
||
|
|
totp_secret_enc BLOB,
|
||
|
|
passkey_credentials_json TEXT NOT NULL DEFAULT '[]',
|
||
|
|
quota_mb INTEGER NOT NULL DEFAULT 2048,
|
||
|
|
used_bytes INTEGER NOT NULL DEFAULT 0,
|
||
|
|
digest_enabled INTEGER NOT NULL DEFAULT 1,
|
||
|
|
digest_interval_mins INTEGER NOT NULL DEFAULT 0,
|
||
|
|
last_digest_at DATETIME,
|
||
|
|
last_login_at DATETIME,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_users_tenant ON users(tenant_id);
|
||
|
|
CREATE INDEX idx_users_domain ON users(domain_id);
|
||
|
|
|
||
|
|
CREATE TABLE app_passwords (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
label TEXT NOT NULL,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
scopes TEXT NOT NULL DEFAULT 'smtp,imap',
|
||
|
|
last_used_at DATETIME,
|
||
|
|
expires_at DATETIME,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_app_passwords_user ON app_passwords(user_id);
|
||
|
|
|
||
|
|
CREATE TABLE sessions (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
jti TEXT UNIQUE NOT NULL,
|
||
|
|
user_agent TEXT,
|
||
|
|
ip TEXT,
|
||
|
|
expires_at DATETIME NOT NULL,
|
||
|
|
revoked_at DATETIME
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_sessions_user ON sessions(user_id);
|
||
|
|
CREATE INDEX idx_sessions_jti ON sessions(jti);
|
||
|
|
|
||
|
|
CREATE TABLE aliases (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
from_address TEXT UNIQUE NOT NULL,
|
||
|
|
to_user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
to_external TEXT,
|
||
|
|
active INTEGER NOT NULL DEFAULT 1
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_aliases_tenant ON aliases(tenant_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0003_list_rules",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE list_rules (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
list_type TEXT NOT NULL,
|
||
|
|
match_type TEXT NOT NULL DEFAULT 'email',
|
||
|
|
value TEXT NOT NULL,
|
||
|
|
note TEXT,
|
||
|
|
active INTEGER NOT NULL DEFAULT 1,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_list_rules_tenant ON list_rules(tenant_id);
|
||
|
|
CREATE INDEX idx_list_rules_value ON list_rules(value);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0004_messages_mailbox",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE messages (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
tenant_id TEXT REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
from_address TEXT NOT NULL,
|
||
|
|
to_address TEXT NOT NULL,
|
||
|
|
subject TEXT,
|
||
|
|
message_id_hdr TEXT,
|
||
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
||
|
|
verdict TEXT NOT NULL DEFAULT 'clean',
|
||
|
|
total_score REAL NOT NULL DEFAULT 0,
|
||
|
|
sender_ip TEXT,
|
||
|
|
relayed_at DATETIME,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_messages_tenant ON messages(tenant_id);
|
||
|
|
CREATE INDEX idx_messages_to ON messages(to_address);
|
||
|
|
CREATE INDEX idx_messages_created ON messages(created_at);
|
||
|
|
|
||
|
|
CREATE TABLE message_checks (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
||
|
|
stage TEXT NOT NULL,
|
||
|
|
result TEXT NOT NULL,
|
||
|
|
score REAL NOT NULL DEFAULT 0,
|
||
|
|
detail TEXT,
|
||
|
|
duration_ms INTEGER NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_message_checks_message ON message_checks(message_id);
|
||
|
|
|
||
|
|
CREATE TABLE mailbox_index (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
mailbox TEXT NOT NULL DEFAULT 'INBOX',
|
||
|
|
uid INTEGER NOT NULL,
|
||
|
|
eml_path TEXT NOT NULL,
|
||
|
|
flags TEXT NOT NULL DEFAULT '',
|
||
|
|
size_bytes INTEGER NOT NULL DEFAULT 0,
|
||
|
|
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
internal_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(user_id, mailbox, uid)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_mailbox_index_user ON mailbox_index(user_id, mailbox);
|
||
|
|
|
||
|
|
CREATE TABLE mailbox_uid_counters (
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
mailbox TEXT NOT NULL,
|
||
|
|
next_uid INTEGER NOT NULL DEFAULT 1,
|
||
|
|
PRIMARY KEY (user_id, mailbox)
|
||
|
|
);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0005_outbound_queue",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE outbound_queue (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
from_address TEXT NOT NULL,
|
||
|
|
to_address TEXT NOT NULL,
|
||
|
|
eml_path TEXT NOT NULL,
|
||
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
||
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
||
|
|
last_error TEXT,
|
||
|
|
next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_outbound_queue_next ON outbound_queue(next_attempt_at);
|
||
|
|
CREATE INDEX idx_outbound_queue_user ON outbound_queue(user_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0006_quarantine",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE quarantine (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
||
|
|
eml_path TEXT NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT 'held',
|
||
|
|
reason TEXT,
|
||
|
|
released_by TEXT,
|
||
|
|
released_at DATETIME,
|
||
|
|
expires_at DATETIME NOT NULL,
|
||
|
|
notified_at DATETIME,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_quarantine_status ON quarantine(status);
|
||
|
|
CREATE INDEX idx_quarantine_message ON quarantine(message_id);
|
||
|
|
|
||
|
|
CREATE TABLE release_tokens (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
quarantine_id TEXT NOT NULL REFERENCES quarantine(id) ON DELETE CASCADE,
|
||
|
|
token TEXT UNIQUE NOT NULL,
|
||
|
|
email TEXT,
|
||
|
|
used_at DATETIME,
|
||
|
|
expires_at DATETIME NOT NULL,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_release_tokens_token ON release_tokens(token);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0007_linked_accounts",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE linked_accounts (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
provider TEXT NOT NULL,
|
||
|
|
display_name TEXT,
|
||
|
|
email_address TEXT NOT NULL,
|
||
|
|
auth_type TEXT NOT NULL,
|
||
|
|
imap_host TEXT,
|
||
|
|
imap_port INTEGER,
|
||
|
|
imap_tls TEXT,
|
||
|
|
smtp_host TEXT,
|
||
|
|
smtp_port INTEGER,
|
||
|
|
smtp_tls TEXT,
|
||
|
|
credential_enc BLOB,
|
||
|
|
oauth_expires_at DATETIME,
|
||
|
|
sync_state TEXT,
|
||
|
|
cache_retention_days INTEGER,
|
||
|
|
last_sync_at DATETIME,
|
||
|
|
last_sync_error TEXT,
|
||
|
|
active INTEGER NOT NULL DEFAULT 1,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_linked_accounts_user ON linked_accounts(user_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0008_dav_contacts_calendars",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE addressbooks (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
owner_type TEXT NOT NULL,
|
||
|
|
owner_id TEXT NOT NULL,
|
||
|
|
display_name TEXT,
|
||
|
|
description TEXT,
|
||
|
|
sync_token TEXT NOT NULL DEFAULT '1',
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_addressbooks_owner ON addressbooks(owner_type, owner_id);
|
||
|
|
|
||
|
|
CREATE TABLE contacts (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
addressbook_id TEXT NOT NULL REFERENCES addressbooks(id) ON DELETE CASCADE,
|
||
|
|
uid TEXT NOT NULL,
|
||
|
|
vcard_enc BLOB NOT NULL,
|
||
|
|
etag TEXT NOT NULL,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(addressbook_id, uid)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_contacts_addressbook ON contacts(addressbook_id);
|
||
|
|
|
||
|
|
CREATE TABLE calendars (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
owner_type TEXT NOT NULL,
|
||
|
|
owner_id TEXT NOT NULL,
|
||
|
|
display_name TEXT,
|
||
|
|
description TEXT,
|
||
|
|
color TEXT,
|
||
|
|
timezone TEXT NOT NULL DEFAULT 'UTC',
|
||
|
|
sync_token TEXT NOT NULL DEFAULT '1',
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_calendars_owner ON calendars(owner_type, owner_id);
|
||
|
|
|
||
|
|
CREATE TABLE calendar_objects (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
calendar_id TEXT NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
|
||
|
|
uid TEXT NOT NULL,
|
||
|
|
ical_enc BLOB NOT NULL,
|
||
|
|
component_type TEXT,
|
||
|
|
summary TEXT,
|
||
|
|
dtstart DATETIME,
|
||
|
|
dtend DATETIME,
|
||
|
|
etag TEXT NOT NULL,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(calendar_id, uid)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_calendar_objects_calendar ON calendar_objects(calendar_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0009_sieve_scripts",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE sieve_scripts (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
script_text TEXT NOT NULL,
|
||
|
|
active INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(user_id, name)
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_sieve_scripts_user ON sieve_scripts(user_id);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0010_tls_certs",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE tls_certs (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
domain TEXT UNIQUE NOT NULL,
|
||
|
|
cert_pem_enc BLOB,
|
||
|
|
key_pem_enc BLOB,
|
||
|
|
expires_at DATETIME,
|
||
|
|
acme_account_key_enc BLOB,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_tls_certs_domain ON tls_certs(domain);
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
{
|
||
|
|
name: "0011_mfa_and_recovery",
|
||
|
|
sql: map[string]string{
|
||
|
|
"sqlite": `
|
||
|
|
CREATE TABLE mfa_backup_codes (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
code_hash TEXT NOT NULL,
|
||
|
|
used_at DATETIME,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX idx_mfa_backup_codes_user ON mfa_backup_codes(user_id);
|
||
|
|
|
||
|
|
ALTER TABLE users ADD COLUMN recovery_email TEXT;
|
||
|
|
`,
|
||
|
|
},
|
||
|
|
},
|
||
|
|
}
|