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;