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);

-- What a user asked us to remember for them, sealed with the installation's
-- master key.  See Trog::Vault: the key is not in this file and not in this
-- database, so these rows are ciphertext to anybody who only has the db.
CREATE TABLE IF NOT EXISTS user_secret (
    username   TEXT    NOT NULL REFERENCES user(name) ON DELETE CASCADE,
    name       TEXT    NOT NULL,
    key_id     TEXT    NOT NULL,
    salt       BLOB    NOT NULL,
    ciphertext BLOB    NOT NULL,
    tag        BLOB    NOT NULL,
    created    INTEGER NOT NULL,
    last_used  INTEGER DEFAULT NULL,
    PRIMARY KEY (username, name)
);

-- The last TOTP timestep this user spent.  A code authorizes things now, so it
-- is worth exactly one thing rather than everything you can do inside its
-- window.
CREATE TABLE IF NOT EXISTS totp_spent (
    username TEXT    NOT NULL UNIQUE REFERENCES user(name) ON DELETE CASCADE,
    step     INTEGER NOT NULL
);