sql/schema.sql

-- git-trace database schema (SQLite)
-- Design note: collaboration tables are laid out so that both
-- merge-request workflow and GNU-style patch-set workflow can coexist.
-- patch_sets/patch_revisions are reserved for the mailing-list milestone.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE COLLATE NOCASE,
    email         TEXT UNIQUE,
    password_hash TEXT,                 -- scrypt/argon2 digest; NULL = ssh-only account
    is_admin      INTEGER NOT NULL DEFAULT 0,
    quota_kb      INTEGER NOT NULL DEFAULT 524288,  -- 0 = unlimited (free tier = 500 MiB)
    tier          TEXT NOT NULL DEFAULT 'free',     -- free|pro|self|enterprise
    tier_expires_at INTEGER,                        -- NULL = never expires
    created_at    INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS ssh_keys (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    pubkey      TEXT NOT NULL UNIQUE,   -- full "ssh-ed25519 AAAA... comment" line
    fingerprint TEXT NOT NULL UNIQUE,
    created_at  INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

-- HTTP git-auth tokens (Basic-auth password substitutes for push/fetch over HTTPS)
CREATE TABLE IF NOT EXISTS auth_tokens (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       TEXT NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    scope      TEXT NOT NULL DEFAULT 'rw',  -- 'ro' | 'rw'
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    expires_at INTEGER
);

CREATE TABLE IF NOT EXISTS sessions (
    token      TEXT PRIMARY KEY,        -- random 256-bit hex cookie
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    expires_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS repos (
    id             INTEGER PRIMARY KEY,
    owner_id       INTEGER REFERENCES users(id) ON DELETE CASCADE,  -- NULL = orphaned/system
    name           TEXT NOT NULL,
    description    TEXT NOT NULL DEFAULT '',
    is_public      INTEGER NOT NULL DEFAULT 1,
    size_kb        INTEGER NOT NULL DEFAULT 0,   -- refreshed by the update hook
    default_branch TEXT NOT NULL DEFAULT 'master',
    created_at     INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    UNIQUE (owner_id, name)
);

-- owner_id==NULL repos are addressed as /_/<name>.git on disk
CREATE INDEX IF NOT EXISTS repos_owner ON repos(owner_id);

-- Per-repo ACL: role 'r' (pull), 'w' (push), 'a' (admin: settings/ACL/hooks)
CREATE TABLE IF NOT EXISTS repo_acl (
    repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role    TEXT NOT NULL CHECK (role IN ('r','w','a')),
    PRIMARY KEY (repo_id, user_id)
);

-- Per-branch protection rules.  pattern is a glob on the branch name
-- (no refs/heads/ prefix; '*' and '?' wildcards).  A matching rule
-- requires repo rank >= min_role to push; ff_only and no_delete add
-- the built-in default-branch semantics to any pattern.  Rules only
-- ever tighten access — they cannot relax the update hook's floor.
CREATE TABLE IF NOT EXISTS branch_rules (
    id        INTEGER PRIMARY KEY,
    repo_id   INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    pattern   TEXT NOT NULL,
    min_role  TEXT NOT NULL DEFAULT 'a' CHECK (min_role IN ('w','a')),
    ff_only   INTEGER NOT NULL DEFAULT 0,
    no_delete INTEGER NOT NULL DEFAULT 0,
    UNIQUE (repo_id, pattern)
);

CREATE TABLE IF NOT EXISTS issues (
    id         INTEGER PRIMARY KEY,
    repo_id    INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    author_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
    title      TEXT NOT NULL,
    body       TEXT NOT NULL DEFAULT '',
    state      TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open','closed')),
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS merge_requests (
    id          INTEGER PRIMARY KEY,
    repo_id     INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    author_id   INTEGER REFERENCES users(id) ON DELETE SET NULL,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL DEFAULT '',
    src_repo_id INTEGER REFERENCES repos(id) ON DELETE SET NULL, -- fork support
    src_branch  TEXT NOT NULL,
    dst_branch  TEXT NOT NULL,
    src_commit  TEXT NOT NULL,            -- sha at creation/last push
    state       TEXT NOT NULL DEFAULT 'open'
                CHECK (state IN ('open','merged','closed')),
    created_at  INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    updated_at  INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

-- Single discussion table: parent_type = 'issue' | 'mr' | 'patch_set'
CREATE TABLE IF NOT EXISTS comments (
    id          INTEGER PRIMARY KEY,
    parent_type TEXT NOT NULL CHECK (parent_type IN ('issue','mr','patch_set')),
    parent_id   INTEGER NOT NULL,
    author_id   INTEGER REFERENCES users(id) ON DELETE SET NULL,
    body        TEXT NOT NULL,
    created_at  INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS comments_parent ON comments(parent_type, parent_id);

-- ===== reserved for patch-set milestone (git format-patch / git am) =====
CREATE TABLE IF NOT EXISTS patch_sets (
    id           INTEGER PRIMARY KEY,
    repo_id      INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    author_id    INTEGER REFERENCES users(id) ON DELETE SET NULL,
    title        TEXT NOT NULL,
    cover_letter TEXT NOT NULL DEFAULT '',
    dst_branch   TEXT NOT NULL DEFAULT 'master',
    state        TEXT NOT NULL DEFAULT 'open'
                 CHECK (state IN ('open','applied','rejected')),
    created_at   INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    updated_at   INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);

CREATE TABLE IF NOT EXISTS patch_revisions (
    id           INTEGER PRIMARY KEY,
    patch_set_id INTEGER NOT NULL REFERENCES patch_sets(id) ON DELETE CASCADE,
    version      INTEGER NOT NULL,
    mbox         TEXT NOT NULL,           -- raw format-patch mbox
    tip_commit   TEXT,
    created_at   INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    UNIQUE (patch_set_id, version)
);

-- Inbound mail parts awaiting their send-email thread to complete.
-- grp is the thread root Message-ID; part 0 = cover letter.  Once
-- parts 1..total are all present the set is assembled and submitted.
CREATE TABLE IF NOT EXISTS mail_items (
    id       INTEGER PRIMARY KEY,
    repo_id  INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    grp      TEXT NOT NULL,
    msgid    TEXT NOT NULL,
    part     INTEGER NOT NULL,
    total    INTEGER NOT NULL,
    raw      TEXT NOT NULL,
    received INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    UNIQUE (repo_id, msgid)
);
CREATE INDEX IF NOT EXISTS mail_grp ON mail_items(repo_id, grp);

-- Append-only event journal: pushes, auth events, ACL changes, merges.
-- actor is denormalized on purpose: the row must survive user deletion.
-- No FK on uid/repo_id for the same reason — this is a journal, not a relation.
CREATE TABLE IF NOT EXISTS audit_log (
    id      INTEGER PRIMARY KEY,
    ts      INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    actor   TEXT,
    uid     INTEGER,
    ip      TEXT,
    action  TEXT NOT NULL,
    repo_id INTEGER,
    detail  TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS audit_ts   ON audit_log(ts);
CREATE INDEX IF NOT EXISTS audit_repo ON audit_log(repo_id);

-- Outbound webhooks: per-repo endpoints + delivery journal.
-- events is a comma list: push,mr,patch.  Deliveries are queued at
-- event time and drained asynchronously by `git-trace webhook-run`
-- (spawned detached by the post-receive hook / web enqueue path).
CREATE TABLE IF NOT EXISTS repo_webhooks (
    id      INTEGER PRIMARY KEY,
    repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    url     TEXT NOT NULL,
    secret  TEXT NOT NULL DEFAULT '',
    events  TEXT NOT NULL DEFAULT 'push',
    active  INTEGER NOT NULL DEFAULT 1,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
);
CREATE INDEX IF NOT EXISTS wh_repo ON repo_webhooks(repo_id);
CREATE TABLE IF NOT EXISTS webhook_deliveries (
    id       INTEGER PRIMARY KEY,
    hook_id  INTEGER NOT NULL REFERENCES repo_webhooks(id) ON DELETE CASCADE,
    event    TEXT NOT NULL,
    payload  TEXT NOT NULL,
    status   TEXT NOT NULL DEFAULT 'pending',  -- pending|ok|fail
    code     INTEGER NOT NULL DEFAULT 0,       -- last HTTP status
    attempts INTEGER NOT NULL DEFAULT 0,
    error    TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    done_at  INTEGER
);
CREATE INDEX IF NOT EXISTS whd_pend ON webhook_deliveries(status, id);

-- Minimal CI runner (M14): one row per build.  Script comes from
-- .git-trace-ci on the *default branch* only — writers on feature
-- branches cannot touch the pipeline.  Runs execute detached with
-- rlimits + wall-clock timeout; ci_enabled is a per-repo opt-in.
CREATE TABLE IF NOT EXISTS ci_runs (
    id       INTEGER PRIMARY KEY,
    repo_id  INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    sha      TEXT NOT NULL,
    branch   TEXT NOT NULL DEFAULT '',
    status   TEXT NOT NULL DEFAULT 'pending', -- pending|running|pass|fail|timeout|error
    exit_code INTEGER,
    log      TEXT NOT NULL DEFAULT '',
    actor    TEXT NOT NULL DEFAULT '',
    created_at  INTEGER NOT NULL DEFAULT (strftime('%s','now')),
    finished_at INTEGER
);
CREATE INDEX IF NOT EXISTS ci_repo ON ci_runs(repo_id, id);
CREATE INDEX IF NOT EXISTS ci_pend ON ci_runs(status, id);

-- Repository mirroring / lightweight federation (M15): each row pulls
-- an upstream remote into refs/mirror/* of a local repo.  Refs land in
-- their own namespace so mirrored content can never overwrite local
-- branches.  'secret' authenticates the event-driven trigger:
-- an upstream git-trace (or any webhook sender) POSTs to
-- /<o>/<r>/mirror-sync with X-Git-Trace-Signature and the follower
-- syncs — no polling daemon.
CREATE TABLE IF NOT EXISTS repo_mirrors (
    id       INTEGER PRIMARY KEY,
    repo_id  INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    url      TEXT NOT NULL,          -- remote URL (http/https/git/ssh)
    direction TEXT NOT NULL DEFAULT 'pull',  -- pull (fetch) or push
    refspec  TEXT NOT NULL DEFAULT '+refs/heads/*:refs/mirror/*',
    secret   TEXT NOT NULL DEFAULT '',   -- HMAC key for trigger endpoint
    enabled  INTEGER NOT NULL DEFAULT 1,
    last_ts  INTEGER,
    last_ok  INTEGER,
    last_err TEXT NOT NULL DEFAULT '',
    last_refs INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS mir_repo ON repo_mirrors(repo_id);