AkurAI-Build / migrations/006_access_control.sql

Latest change f1c5c4135fbe517df4f870f0b12485ace485d229 - OLI-271 (groundwork): collaborator roles, deploy keys and scoped tokens module, not yet wired by Óli

CREATE TABLE repository_collaborators (
    repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    subject TEXT NOT NULL CHECK (length(subject) BETWEEN 1 AND 256),
    role TEXT NOT NULL CHECK (role IN ('read', 'write', 'admin')),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    PRIMARY KEY (repository_id, subject)
) STRICT;

CREATE TABLE access_tokens (
    id INTEGER PRIMARY KEY,
    kind TEXT NOT NULL CHECK (kind IN ('pat', 'deploy_key')),
    name TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 128),
    subject TEXT CHECK (subject IS NULL OR length(subject) BETWEEN 1 AND 256),
    repository_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE,
    role TEXT NOT NULL CHECK (role IN ('read', 'write', 'admin')),
    token_hash TEXT NOT NULL UNIQUE CHECK (length(token_hash) = 64),
    expires_at INTEGER,
    revoked_at INTEGER,
    last_used_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    CHECK (kind = 'pat' AND subject IS NOT NULL
        OR kind = 'deploy_key' AND repository_id IS NOT NULL AND role IN ('read', 'write'))
) STRICT;

CREATE INDEX access_tokens_subject ON access_tokens(subject);
CREATE INDEX access_tokens_repository ON access_tokens(repository_id);