tCMS/schema/auth.schema

CREATE TABLE IF NOT EXISTS user (
    name TEXT NOT NULL UNIQUE,
    salt TEXT NOT NULL,
    hash TEXT NOT NULL,
    totp_secret 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
);