mirror of
https://github.com/Crazyco-xyz/48hr.email.git
synced 2026-01-08 10:49:35 +01:00
82 lines
3 KiB
SQL
82 lines
3 KiB
SQL
-- User Registration System Schema
|
|
-- SQLite database for user accounts and associated features
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
instance_id TEXT NOT NULL,
|
|
username TEXT NOT NULL COLLATE NOCASE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
last_login INTEGER,
|
|
CHECK (length(username) >= 3 AND length(username) <= 20),
|
|
UNIQUE(instance_id, username)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
|
|
|
|
-- User verified forwarding emails
|
|
CREATE TABLE IF NOT EXISTS user_forward_emails (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
instance_id TEXT NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
email TEXT NOT NULL COLLATE NOCASE,
|
|
verified_at INTEGER NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(instance_id, user_id, email)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_forward_emails_user_id ON user_forward_emails(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_forward_emails_email ON user_forward_emails(email);
|
|
|
|
-- User locked inboxes
|
|
CREATE TABLE IF NOT EXISTS user_locked_inboxes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
instance_id TEXT NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
inbox_address TEXT NOT NULL COLLATE NOCASE,
|
|
password_hash TEXT NOT NULL,
|
|
locked_at INTEGER NOT NULL,
|
|
last_accessed INTEGER NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(instance_id, user_id, inbox_address)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_locked_inboxes_user_id ON user_locked_inboxes(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_locked_inboxes_address ON user_locked_inboxes(inbox_address);
|
|
CREATE INDEX IF NOT EXISTS idx_locked_inboxes_last_accessed ON user_locked_inboxes(last_accessed);
|
|
|
|
-- API tokens (one per user for programmatic access)
|
|
CREATE TABLE IF NOT EXISTS api_tokens (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
instance_id TEXT NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
token TEXT NOT NULL UNIQUE,
|
|
created_at INTEGER NOT NULL,
|
|
last_used INTEGER,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(instance_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_api_tokens_token ON api_tokens(token);
|
|
CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id);
|
|
|
|
-- Statistics storage for persistence across restarts
|
|
CREATE TABLE IF NOT EXISTS statistics (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
instance_id TEXT NOT NULL,
|
|
largest_uid INTEGER NOT NULL DEFAULT 0,
|
|
hourly_data TEXT, -- JSON array of 24h rolling data
|
|
last_updated INTEGER NOT NULL,
|
|
imap_hash TEXT NULL
|
|
);
|
|
|
|
-- Trigger to enforce max 5 locked inboxes per user
|
|
CREATE TRIGGER IF NOT EXISTS check_locked_inbox_limit
|
|
BEFORE INSERT ON user_locked_inboxes
|
|
BEGIN
|
|
SELECT RAISE(ABORT, 'User already has maximum number of locked inboxes')
|
|
WHERE (SELECT COUNT(*) FROM user_locked_inboxes WHERE user_id = NEW.user_id) >= 5;
|
|
END;
|