XFEServerManager

CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY,
    description TEXT NOT NULL,
    applied_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS accounts (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    normalized_username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('observer','helper','moderator','administrator','owner')),
    totp_secret BLOB,
    totp_enabled INTEGER NOT NULL DEFAULT 0 CHECK (totp_enabled IN (0, 1)),
    disabled INTEGER NOT NULL DEFAULT 0 CHECK (disabled IN (0, 1)),
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    last_login_at INTEGER
);

CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    token_hash BLOB NOT NULL UNIQUE,
    csrf_hash BLOB NOT NULL,
    created_at INTEGER NOT NULL,
    last_seen_at INTEGER NOT NULL,
    authenticated_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    remote_address TEXT,
    user_agent_hash BLOB,
    revoked_at INTEGER
);

CREATE INDEX IF NOT EXISTS sessions_account_idx ON sessions(account_id);
CREATE INDEX IF NOT EXISTS sessions_expiry_idx ON sessions(expires_at);

CREATE TABLE IF NOT EXISTS policy_documents (
    revision INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_revision INTEGER REFERENCES policy_documents(revision),
    schema_version INTEGER NOT NULL,
    state TEXT NOT NULL CHECK (state IN ('DRAFT','ACTIVE','ARCHIVED')),
    checksum TEXT NOT NULL,
    document_json TEXT NOT NULL,
    actor_id TEXT,
    created_at INTEGER NOT NULL,
    published_at INTEGER
);

CREATE UNIQUE INDEX IF NOT EXISTS one_active_policy
    ON policy_documents(state) WHERE state = 'ACTIVE';

CREATE TABLE IF NOT EXISTS operations (
    id TEXT PRIMARY KEY,
    request_id TEXT NOT NULL UNIQUE,
    actor_id TEXT NOT NULL,
    action TEXT NOT NULL,
    target TEXT,
    state TEXT NOT NULL,
    reason TEXT,
    submitted_at INTEGER NOT NULL,
    started_at INTEGER,
    completed_at INTEGER,
    result_json TEXT,
    error_code TEXT
);

CREATE TABLE IF NOT EXISTS audit_events (
    sequence INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id TEXT NOT NULL UNIQUE,
    occurred_at INTEGER NOT NULL,
    actor_id TEXT,
    origin TEXT NOT NULL,
    action TEXT NOT NULL,
    target TEXT,
    reason TEXT,
    before_json TEXT,
    after_json TEXT,
    policy_revision INTEGER,
    request_id TEXT,
    outcome TEXT NOT NULL,
    duration_ms INTEGER,
    metadata_json TEXT NOT NULL DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS audit_time_idx ON audit_events(occurred_at DESC);
CREATE INDEX IF NOT EXISTS audit_actor_idx ON audit_events(actor_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS audit_action_idx ON audit_events(action, occurred_at DESC);