tCMS/schema/auth.schema

CREATE TABLE IF NOT EXISTS user (
    name TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL UNIQUE,
    salt TEXT NOT NULL,
    hash TEXT NOT NULL,
    totp_secret TEXT DEFAULT NULL,
    contact_email TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS session (
    id TEXT PRIMARY KEY UNIQUE,
    username TEXT NOT NULL UNIQUE REFERENCES user(name) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS username_idx ON user(name);

CREATE VIEW IF NOT EXISTS sess_user AS SELECT user.name AS name, session.id AS session FROM user JOIN session ON session.username=user.name;

CREATE TABLE IF NOT EXISTS user_acl (
    username TEXT NOT NULL UNIQUE REFERENCES user(name) ON DELETE CASCADE,
    acl TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS change_request (
    username TEXT NOT NULL REFERENCES user(name) ON DELETE CASCADE,
    type     TEXT NOT NULL,
    token    TEXT PRIMARY KEY UNIQUE,
    secret   TEXT,
    processed NUMERIC DEFAULT 0
);

CREATE VIEW IF NOT EXISTS change_request_full AS SELECT cr.username, u.display_name, cr.type, cr.token, cr.secret, cr.processed, u.contact_email from change_request AS cr JOIN user AS u ON u.name=cr.username;

CREATE TABLE IF NOT EXISTS audit_log (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    event_time INTEGER NOT NULL,
    username   TEXT    NOT NULL,
    session_id TEXT    DEFAULT NULL,
    ip_addr    TEXT    NOT NULL DEFAULT '',
    event_type TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS audit_log_time_idx ON audit_log(event_time);
CREATE INDEX IF NOT EXISTS audit_log_user_idx ON audit_log(username);