AkurAI-Build

Latest change e16ed955e37bc7f7b00de000a208ef91006cf60e - Adopt the AkurAI ID SSO contract and inject pipeline build context by AkurAI Build

-- AkurAI SSO: durable server-side OIDC login state and browser sessions.
-- Both tables live in the encrypted database so sessions survive restarts and
-- the authorization-code flow is single-use and expiry-enforced.

CREATE TABLE oauth_states (
    state TEXT PRIMARY KEY CHECK (length(state) = 64),
    nonce TEXT NOT NULL CHECK (length(nonce) = 64),
    code_verifier TEXT NOT NULL CHECK (length(code_verifier) BETWEEN 43 AND 128),
    return_to TEXT NOT NULL CHECK (length(return_to) <= 512),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    expires_at INTEGER NOT NULL CHECK (expires_at > 0)
) STRICT;

CREATE INDEX oauth_states_expires_at ON oauth_states(expires_at);

CREATE TABLE sessions (
    id TEXT PRIMARY KEY CHECK (length(id) = 64),
    sub TEXT NOT NULL CHECK (length(sub) BETWEEN 1 AND 256),
    email TEXT NOT NULL CHECK (length(email) BETWEEN 1 AND 320),
    role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
    organization_id TEXT CHECK (organization_id IS NULL OR length(organization_id) BETWEEN 1 AND 128),
    tenant_id TEXT CHECK (tenant_id IS NULL OR length(tenant_id) BETWEEN 1 AND 128),
    groups TEXT NOT NULL CHECK (length(groups) <= 4096),
    id_token TEXT NOT NULL CHECK (length(id_token) <= 8192),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    expires_at INTEGER NOT NULL CHECK (expires_at > 0)
) STRICT;

CREATE INDEX sessions_expires_at ON sessions(expires_at);

PRAGMA user_version = 5;