AkurAI-Build

Latest change 2c724a1e055243fecad97b3231c6e7f51379e50d - pr: persisted PR CRUD, SHA-bound review, stale-approval invalidation, merge queue core by Ólafur Búi Ólafsson

-- Persisted pull requests and SHA-bound independent reviews.
-- A review is only valid against the exact head_sha it was cast on; any push
-- that changes the PR's head_sha immediately makes prior reviews stale for
-- merge purposes (enforced in application code via reviews.head_sha vs
-- pull_requests.head_sha, never by deleting review history).
CREATE TABLE pull_requests (
    id INTEGER PRIMARY KEY,
    repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    number INTEGER NOT NULL CHECK (number > 0),
    title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 256),
    body TEXT NOT NULL CHECK (length(body) <= 65536),
    source_ref TEXT NOT NULL CHECK (length(source_ref) BETWEEN 1 AND 200),
    target_ref TEXT NOT NULL CHECK (length(target_ref) BETWEEN 1 AND 200),
    head_sha TEXT NOT NULL CHECK (length(head_sha) IN (40, 64)),
    base_sha TEXT NOT NULL CHECK (length(base_sha) IN (40, 64)),
    state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed', 'merged', 'queued')),
    author_sub TEXT NOT NULL CHECK (length(author_sub) BETWEEN 1 AND 256),
    author_display TEXT NOT NULL CHECK (length(author_display) BETWEEN 1 AND 256),
    merge_commit TEXT CHECK (merge_commit IS NULL OR length(merge_commit) IN (40, 64)),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    closed_at INTEGER,
    merged_at INTEGER,
    UNIQUE(repository_id, number),
    UNIQUE(repository_id, source_ref, target_ref, state)
) STRICT;

CREATE TABLE pull_request_reviews (
    id INTEGER PRIMARY KEY,
    pull_request_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
    reviewer_sub TEXT NOT NULL CHECK (length(reviewer_sub) BETWEEN 1 AND 256),
    reviewer_display TEXT NOT NULL CHECK (length(reviewer_display) BETWEEN 1 AND 256),
    state TEXT NOT NULL CHECK (state IN ('approved', 'changes_requested', 'commented')),
    head_sha TEXT NOT NULL CHECK (length(head_sha) IN (40, 64)),
    body TEXT NOT NULL DEFAULT '' CHECK (length(body) <= 65536),
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

-- Durable, per-repository serialized merge queue. One row per queue entry;
-- a repository advances entries strictly in position order and a crashed
-- worker leaves a `running` row that recovery resets to `queued`.
CREATE TABLE merge_queue_entries (
    id INTEGER PRIMARY KEY,
    repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    pull_request_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
    position INTEGER NOT NULL CHECK (position > 0),
    base_sha TEXT NOT NULL CHECK (length(base_sha) IN (40, 64)),
    head_sha TEXT NOT NULL CHECK (length(head_sha) IN (40, 64)),
    status TEXT NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'canceled')),
    run_id INTEGER REFERENCES runs(id) ON DELETE SET NULL,
    error TEXT,
    enqueued_by TEXT NOT NULL CHECK (length(enqueued_by) BETWEEN 1 AND 256),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    started_at INTEGER,
    finished_at INTEGER,
    UNIQUE(repository_id, pull_request_id, status)
) STRICT;

CREATE INDEX pull_requests_repository_state ON pull_requests(repository_id, state, updated_at DESC);
CREATE INDEX pull_requests_repository_number ON pull_requests(repository_id, number);
CREATE INDEX pull_request_reviews_pr ON pull_request_reviews(pull_request_id, created_at, id);
CREATE INDEX merge_queue_repository_position ON merge_queue_entries(repository_id, position);
CREATE INDEX merge_queue_repository_status ON merge_queue_entries(repository_id, status, position);

PRAGMA user_version = 11;