Postgres, step 3: the hub builds and passes its tests on PostgreSQL 3b1bc27fe805

-- The hub's schema on Postgres: one migration equal to SQLite's 0001-0072.

-- Generated from the migrated SQLite schema, then reviewed; later changes

-- are written for both engines. citext is made by db::open, in public.

CREATE TABLE repositories (

    id               BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    owner            TEXT NOT NULL,

    name             TEXT NOT NULL,

    description      TEXT NOT NULL DEFAULT '',

    default_bookmark TEXT NOT NULL DEFAULT 'main',

    visibility       TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'internal', 'private')),

    created_ms       BIGINT NOT NULL,

    updated_ms       BIGINT NOT NULL,

    required_approvals BIGINT NOT NULL DEFAULT 0,

    require_owners BOOLEAN NOT NULL DEFAULT FALSE,

    allow_conflicts BOOLEAN NOT NULL DEFAULT FALSE,

    required_checks TEXT NOT NULL DEFAULT '',

    git_mirror BOOLEAN NOT NULL DEFAULT FALSE,

    git_mirror_state TEXT NOT NULL DEFAULT '',

    forked_from BIGINT REFERENCES repositories(id) ON DELETE SET NULL,

    topics TEXT NOT NULL DEFAULT '',

    flow TEXT NOT NULL DEFAULT '',

    archived BOOLEAN NOT NULL DEFAULT FALSE,

    archived_reason TEXT NOT NULL DEFAULT '',

    diffusion TEXT NOT NULL DEFAULT '',

    diffusion_land TEXT NOT NULL DEFAULT '',

    backup TEXT NOT NULL DEFAULT '',

    UNIQUE (owner, name)

);

CREATE TABLE accounts (

    id             BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    username       CITEXT NOT NULL UNIQUE,

    display_name   TEXT NOT NULL DEFAULT '',

    email          TEXT NOT NULL DEFAULT '',

    email_verified BOOLEAN NOT NULL DEFAULT FALSE,

    password_hash  TEXT NOT NULL,

    session_salt   TEXT NOT NULL,

    totp_secret    TEXT,

    recovery_codes TEXT NOT NULL DEFAULT '',

    is_admin       BOOLEAN NOT NULL DEFAULT FALSE,

    banned         BOOLEAN NOT NULL DEFAULT FALSE,

    created_ms     BIGINT NOT NULL,

    last_seen_ms   BIGINT NOT NULL DEFAULT 0,

    email_notifications BOOLEAN NOT NULL DEFAULT FALSE,

    unsubscribe_key TEXT NOT NULL DEFAULT '',

    bio TEXT NOT NULL DEFAULT '',

    website TEXT NOT NULL DEFAULT '',

    location TEXT NOT NULL DEFAULT '',

    avatar TEXT NOT NULL DEFAULT '',

    disabled BOOLEAN NOT NULL DEFAULT FALSE,

    bluesky TEXT NOT NULL DEFAULT '',

    mastodon TEXT NOT NULL DEFAULT '',

    github TEXT NOT NULL DEFAULT ''

);

CREATE TABLE pushes (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id    BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    at_ms      BIGINT NOT NULL,

    reference  TEXT NOT NULL,

    ok         BOOLEAN NOT NULL,

    reason     TEXT NOT NULL DEFAULT '',

    old_state TEXT NOT NULL DEFAULT '',

    new_state TEXT NOT NULL DEFAULT '',

    heads BIGINT NOT NULL DEFAULT 0,

    changes BIGINT NOT NULL DEFAULT 0,

    account_id BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    pusher TEXT NOT NULL DEFAULT '',

    via TEXT NOT NULL DEFAULT ''

);

CREATE TABLE organisations (

    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    name         CITEXT NOT NULL UNIQUE,

    display_name TEXT NOT NULL DEFAULT '',

    description  TEXT NOT NULL DEFAULT '',

    default_role TEXT NOT NULL DEFAULT 'read' CHECK (default_role IN ('none', 'read', 'triage', 'write', 'maintain', 'admin')),

    created_ms   BIGINT NOT NULL,

    avatar TEXT NOT NULL DEFAULT ''

);

CREATE TABLE org_members (

    org_id     BIGINT NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    role       TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member')),

    PRIMARY KEY (org_id, account_id)

);

CREATE TABLE teams (

    id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    org_id BIGINT NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,

    name   CITEXT NOT NULL,

    UNIQUE (org_id, name)

);

CREATE TABLE team_members (

    team_id    BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    PRIMARY KEY (team_id, account_id)

);

CREATE TABLE collaborators (

    repo_id    BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    account_id BIGINT REFERENCES accounts(id) ON DELETE CASCADE,

    team_id    BIGINT REFERENCES teams(id) ON DELETE CASCADE,

    role       TEXT NOT NULL CHECK (role IN ('read', 'triage', 'write', 'maintain', 'admin')),

    CHECK ((account_id IS NULL) <> (team_id IS NULL))

);

CREATE TABLE tokens (

    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    account_id   BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    name         TEXT NOT NULL,

    hash         TEXT NOT NULL UNIQUE,

    scopes       TEXT NOT NULL,

    repos        TEXT NOT NULL DEFAULT '',

    created_ms   BIGINT NOT NULL,

    expires_ms   BIGINT,

    last_used_ms BIGINT,

    scopes_note TEXT NOT NULL DEFAULT '',

    renew_ms BIGINT,

    agent BOOLEAN NOT NULL DEFAULT FALSE

);

CREATE TABLE identities (

    key        TEXT PRIMARY KEY,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    email      TEXT NOT NULL,

    object     BYTEA NOT NULL,

    linked_ms  BIGINT NOT NULL

);

CREATE TABLE challenges (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    bytes      TEXT NOT NULL,

    object     BYTEA NOT NULL DEFAULT ''::bytea,

    created_ms BIGINT NOT NULL

);

CREATE TABLE invitations (

    code       TEXT PRIMARY KEY,

    created_by BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    used_by    BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    created_ms BIGINT NOT NULL

);

CREATE TABLE audit (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    at_ms      BIGINT NOT NULL,

    actor_id   BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    actor      TEXT NOT NULL DEFAULT '',

    action     TEXT NOT NULL,

    target     TEXT NOT NULL DEFAULT '',

    detail     TEXT NOT NULL DEFAULT ''

);

CREATE TABLE settings (

    key   TEXT PRIMARY KEY,

    value TEXT NOT NULL

);

CREATE TABLE numbers (

    repo_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    next    BIGINT NOT NULL DEFAULT 1

);

CREATE TABLE proposals (

    id               BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id          BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    number           BIGINT NOT NULL,

    title            TEXT NOT NULL,

    body             TEXT NOT NULL DEFAULT '',

    author_id        BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author           TEXT NOT NULL DEFAULT '',

    state            TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('draft', 'open', 'landed', 'closed')),

    target           TEXT NOT NULL,

    source_kind      TEXT NOT NULL CHECK (source_kind IN ('bookmark', 'fork', 'changes')),

    source_repo_id   BIGINT REFERENCES repositories(id) ON DELETE SET NULL,

    source_bookmark  TEXT NOT NULL DEFAULT '',

    source_changes   TEXT NOT NULL DEFAULT '',

    projected_state  TEXT NOT NULL DEFAULT '',

    projected_heads  TEXT NOT NULL DEFAULT '',

    projected_target TEXT NOT NULL DEFAULT '',

    projected_changes TEXT NOT NULL DEFAULT '',

    conflicted       BOOLEAN NOT NULL DEFAULT FALSE,

    landed_state     TEXT NOT NULL DEFAULT '',

    created_ms       BIGINT NOT NULL,

    updated_ms       BIGINT NOT NULL,

    landed_by TEXT NOT NULL DEFAULT '',

    landed_via TEXT NOT NULL DEFAULT '',

    landed_ms BIGINT NOT NULL DEFAULT 0,

    UNIQUE (repo_id, number)

);

CREATE TABLE proposal_comments (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    proposal_id BIGINT NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    body        TEXT NOT NULL,

    path        TEXT NOT NULL DEFAULT '',

    anchor_change TEXT NOT NULL DEFAULT '',

    anchor_index  BIGINT NOT NULL DEFAULT 0,

    resolved    BOOLEAN NOT NULL DEFAULT FALSE,

    created_ms  BIGINT NOT NULL,

    via TEXT NOT NULL DEFAULT ''

);

CREATE TABLE proposal_events (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    proposal_id BIGINT NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    at_ms       BIGINT NOT NULL,

    actor       TEXT NOT NULL DEFAULT '',

    event       TEXT NOT NULL,

    detail      TEXT NOT NULL DEFAULT ''

);

CREATE TABLE milestones (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    title       TEXT NOT NULL,

    description TEXT NOT NULL DEFAULT '',

    due_ms      BIGINT,

    state       TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),

    UNIQUE (repo_id, title)

);

CREATE TABLE issues (

    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id      BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    number       BIGINT NOT NULL,

    title        TEXT NOT NULL,

    body         TEXT NOT NULL DEFAULT '',

    author_id    BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author       TEXT NOT NULL DEFAULT '',

    state        TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),

    reason       TEXT NOT NULL DEFAULT '' CHECK (reason IN ('', 'completed', 'not_planned')),

    locked       BOOLEAN NOT NULL DEFAULT FALSE,

    pinned       BOOLEAN NOT NULL DEFAULT FALSE,

    milestone_id BIGINT REFERENCES milestones(id) ON DELETE SET NULL,

    created_ms   BIGINT NOT NULL,

    updated_ms   BIGINT NOT NULL,

    closed_ms    BIGINT,

    UNIQUE (repo_id, number)

);

CREATE TABLE issue_comments (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    author_id  BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author     TEXT NOT NULL DEFAULT '',

    body       TEXT NOT NULL,

    created_ms BIGINT NOT NULL,

    edited_ms  BIGINT

);

CREATE TABLE issue_events (

    id       BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    issue_id BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    at_ms    BIGINT NOT NULL,

    actor    TEXT NOT NULL DEFAULT '',

    event    TEXT NOT NULL,

    detail   TEXT NOT NULL DEFAULT ''

);

CREATE TABLE labels (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    name        CITEXT NOT NULL,

    colour      TEXT NOT NULL DEFAULT '#0072ff',

    description TEXT NOT NULL DEFAULT '',

    UNIQUE (repo_id, name)

);

CREATE TABLE issue_labels (

    issue_id BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    label_id BIGINT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,

    PRIMARY KEY (issue_id, label_id)

);

CREATE TABLE issue_assignees (

    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    PRIMARY KEY (issue_id, account_id)

);

CREATE TABLE proposal_labels (

    proposal_id BIGINT NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    label_id    BIGINT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,

    PRIMARY KEY (proposal_id, label_id)

);

CREATE TABLE notifications (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    at_ms      BIGINT NOT NULL,

    kind       TEXT NOT NULL,

    title      TEXT NOT NULL,

    url        TEXT NOT NULL,

    actor      TEXT NOT NULL DEFAULT '',

    read       BOOLEAN NOT NULL DEFAULT FALSE,

    repo_id BIGINT REFERENCES repositories(id) ON DELETE CASCADE,

    thread TEXT NOT NULL DEFAULT '',

    reason TEXT NOT NULL DEFAULT 'participating' CHECK (reason IN ('participating', 'watching')),

    done BOOLEAN NOT NULL DEFAULT FALSE

);

CREATE TABLE watches (

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    repo_id    BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    muted BOOLEAN NOT NULL DEFAULT FALSE,

    PRIMARY KEY (account_id, repo_id)

);

CREATE TABLE subscriptions (

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal')),

    target_id  BIGINT NOT NULL,

    PRIMARY KEY (account_id, kind, target_id)

);

CREATE TABLE hooks (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id    BIGINT REFERENCES repositories(id) ON DELETE CASCADE,

    org_id     BIGINT REFERENCES organisations(id) ON DELETE CASCADE,

    url        TEXT NOT NULL,

    secret     TEXT NOT NULL DEFAULT '',

    events     TEXT NOT NULL DEFAULT '*',

    active     BOOLEAN NOT NULL DEFAULT TRUE,

    created_ms BIGINT NOT NULL,

    format TEXT NOT NULL DEFAULT 'oxygenhub' CHECK (format IN ('oxygenhub', 'discord', 'slack')),

    CHECK ((repo_id IS NULL) <> (org_id IS NULL))

);

CREATE TABLE deliveries (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    hook_id     BIGINT NOT NULL REFERENCES hooks(id) ON DELETE CASCADE,

    delivery    TEXT NOT NULL,

    event       TEXT NOT NULL,

    payload     TEXT NOT NULL,

    created_ms  BIGINT NOT NULL,

    attempts    BIGINT NOT NULL DEFAULT 0,

    next_ms     BIGINT NOT NULL,

    status      BIGINT,

    response    TEXT NOT NULL DEFAULT '',

    delivered_ms BIGINT

);

CREATE TABLE checks (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    state       TEXT NOT NULL,

    name        TEXT NOT NULL,

    status      TEXT NOT NULL CHECK (status IN ('queued', 'running', 'passed', 'failed', 'cancelled', 'skipped')),

    summary     TEXT NOT NULL DEFAULT '',

    url         TEXT NOT NULL DEFAULT '',

    reporter_id BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    reporter    TEXT NOT NULL DEFAULT '',

    seal        TEXT NOT NULL DEFAULT '',

    started_ms  BIGINT NOT NULL,

    finished_ms BIGINT,

    UNIQUE (repo_id, state, name)

);

CREATE TABLE releases (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    tag         TEXT NOT NULL,

    title       TEXT NOT NULL,

    notes       TEXT NOT NULL DEFAULT '',

    prerelease  BOOLEAN NOT NULL DEFAULT FALSE,

    author_id   BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    created_ms  BIGINT NOT NULL,

    UNIQUE (repo_id, tag)

);

CREATE TABLE release_assets (

    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    release_id   BIGINT NOT NULL REFERENCES releases(id) ON DELETE CASCADE,

    name         TEXT NOT NULL,

    size         BIGINT NOT NULL,

    hash         TEXT NOT NULL,

    content_type TEXT NOT NULL DEFAULT 'application/octet-stream',

    downloads    BIGINT NOT NULL DEFAULT 0,

    created_ms   BIGINT NOT NULL,

    UNIQUE (release_id, name)

);

CREATE TABLE stars (

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    repo_id    BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    at_ms      BIGINT NOT NULL,

    PRIMARY KEY (account_id, repo_id)

);

CREATE TABLE flow_runs (

    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id       BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    bookmark      TEXT NOT NULL,

    state         TEXT NOT NULL,

    heads         TEXT NOT NULL,

    pipeline_hash TEXT NOT NULL DEFAULT '',

    status        TEXT NOT NULL CHECK (status IN ('queued', 'running', 'passed', 'failed', 'error', 'cancelled')),

    error         TEXT NOT NULL DEFAULT '',

    actor         TEXT NOT NULL DEFAULT '',

    created_ms    BIGINT NOT NULL,

    finished_ms   BIGINT,

    number        BIGINT NOT NULL DEFAULT 0,

    trigger       TEXT NOT NULL DEFAULT 'push'

);

CREATE TABLE flow_jobs (

    id             BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    run_id         BIGINT NOT NULL REFERENCES flow_runs(id) ON DELETE CASCADE,

    name           TEXT NOT NULL,

    depth          BIGINT NOT NULL,

    depends        TEXT NOT NULL,

    spec           TEXT NOT NULL,

    sandbox        TEXT NOT NULL,

    runtime        TEXT NOT NULL,

    status         TEXT NOT NULL CHECK (status IN ('queued', 'running', 'passed', 'failed', 'skipped', 'error', 'cancelled')),

    runner         TEXT NOT NULL DEFAULT '',

    token_hash     TEXT NOT NULL DEFAULT '',

    started_ms     BIGINT,

    finished_ms    BIGINT,

    exit_code      BIGINT,

    error          TEXT NOT NULL DEFAULT '',

    receipt        TEXT NOT NULL DEFAULT '',

    receipt_hash   TEXT NOT NULL DEFAULT '',

    log_count      BIGINT NOT NULL DEFAULT 0,

    also_read      TEXT NOT NULL DEFAULT '',

    receipt_attest TEXT NOT NULL DEFAULT '',

    receipt_signer TEXT NOT NULL DEFAULT '',

    receipt_note   TEXT NOT NULL DEFAULT '',

    runner_id      BIGINT,

    timeout_s      BIGINT NOT NULL DEFAULT 0,

    waiting TEXT NOT NULL DEFAULT '',

    peak_disk_mib BIGINT,

    peak_memory_mib BIGINT,

    need_disk_mib BIGINT,

    placed TEXT NOT NULL DEFAULT '',

    approval TEXT NOT NULL DEFAULT '',

    approved_by TEXT NOT NULL DEFAULT '',

    approved_ms BIGINT,

    environment TEXT NOT NULL DEFAULT '',

    approval_seal TEXT NOT NULL DEFAULT '',

    UNIQUE (run_id, name)

);

CREATE TABLE flow_logs (

    job_id BIGINT NOT NULL REFERENCES flow_jobs(id) ON DELETE CASCADE,

    seq    BIGINT NOT NULL,

    t      BIGINT NOT NULL,

    stream TEXT NOT NULL,

    text   TEXT NOT NULL,

    PRIMARY KEY (job_id, seq)

);

CREATE TABLE account_links (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    kind       TEXT NOT NULL CHECK (kind IN ('reset', 'verify')),

    hash       TEXT NOT NULL UNIQUE,

    email      TEXT NOT NULL DEFAULT '',

    created_by BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    created_ms BIGINT NOT NULL,

    expires_ms BIGINT NOT NULL,

    used_ms    BIGINT

);

CREATE TABLE projects (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    owner       TEXT NOT NULL,

    number      BIGINT NOT NULL,

    title       TEXT NOT NULL,

    description TEXT NOT NULL DEFAULT '',

    public      BOOLEAN NOT NULL DEFAULT TRUE,

    closed      BOOLEAN NOT NULL DEFAULT FALSE,

    created_ms  BIGINT NOT NULL,

    updated_ms  BIGINT NOT NULL,

    UNIQUE (owner, number)

);

CREATE TABLE project_columns (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    position   BIGINT NOT NULL

);

CREATE TABLE project_cards (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    column_id  BIGINT NOT NULL REFERENCES project_columns(id) ON DELETE CASCADE,

    position   BIGINT NOT NULL,

    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal', 'note')),

    repo_id    BIGINT REFERENCES repositories(id) ON DELETE CASCADE,

    number     BIGINT,

    note       TEXT NOT NULL DEFAULT '',

    added_by   TEXT NOT NULL DEFAULT '',

    created_ms BIGINT NOT NULL

);

CREATE TABLE flow_runner_keys (

    token_hash TEXT PRIMARY KEY,

    key TEXT NOT NULL,

    name TEXT NOT NULL,

    first_ms BIGINT NOT NULL

);

CREATE TABLE flow_artifacts (

    job_id BIGINT NOT NULL REFERENCES flow_jobs(id) ON DELETE CASCADE,

    name TEXT NOT NULL,

    path TEXT NOT NULL,

    blob TEXT NOT NULL,

    size BIGINT NOT NULL,

    content TEXT NOT NULL,

    created_ms BIGINT NOT NULL,

    PRIMARY KEY (job_id, name)

);

CREATE TABLE flow_runners (

    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    name TEXT NOT NULL,

    token_hash TEXT NOT NULL UNIQUE,

    source TEXT NOT NULL,

    key TEXT NOT NULL DEFAULT '',

    key_name TEXT NOT NULL DEFAULT '',

    arch TEXT NOT NULL DEFAULT '',

    os TEXT NOT NULL DEFAULT '',

    version TEXT NOT NULL DEFAULT '',

    labels TEXT NOT NULL DEFAULT '',

    max_jobs BIGINT NOT NULL DEFAULT 1,

    memory_mib BIGINT,

    cpus BIGINT,

    state TEXT NOT NULL DEFAULT 'active',

    created_ms BIGINT NOT NULL,

    created_by TEXT NOT NULL DEFAULT '',

    last_seen_ms BIGINT,

    scope_owner TEXT NOT NULL DEFAULT '',

    scope_repo_id BIGINT,

    admin_labels TEXT NOT NULL DEFAULT '',

    timeout_minutes BIGINT,

    tiers TEXT NOT NULL DEFAULT '',

    images TEXT NOT NULL DEFAULT '',

    profile TEXT NOT NULL DEFAULT '',

    fetches_images BOOLEAN NOT NULL DEFAULT FALSE,

    free_disk_mib BIGINT,

    load_x100 BIGINT,

    speed BIGINT,

    speed_class BIGINT,

    kernel TEXT NOT NULL DEFAULT '',

    state_ms BIGINT NOT NULL DEFAULT 0

);

CREATE TABLE flow_runner_registrations (

    token_hash TEXT PRIMARY KEY,

    created_by TEXT NOT NULL,

    created_ms BIGINT NOT NULL,

    expires_ms BIGINT NOT NULL,

    used_ms BIGINT,

    runner_id BIGINT,

    scope_owner TEXT NOT NULL DEFAULT '',

    scope_repo_id BIGINT

);

CREATE TABLE device_codes (

    id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    device_hash     TEXT NOT NULL UNIQUE,

    user_code       TEXT NOT NULL UNIQUE,

    client          TEXT NOT NULL,

    scopes          TEXT NOT NULL,

    created_ms      BIGINT NOT NULL,

    expires_ms      BIGINT NOT NULL,

    last_poll_ms    BIGINT NOT NULL DEFAULT 0,

    account_id      BIGINT REFERENCES accounts(id) ON DELETE CASCADE,

    approved_scopes TEXT NOT NULL DEFAULT '',

    denied          BOOLEAN NOT NULL DEFAULT FALSE,

    requester TEXT NOT NULL DEFAULT ''

);

CREATE TABLE proposal_review_requests (

    proposal_id  BIGINT NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    account_id   BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    requested_by TEXT NOT NULL,

    requested_ms BIGINT NOT NULL,

    PRIMARY KEY (proposal_id, account_id)

);

CREATE TABLE retired_usernames (

    username   CITEXT PRIMARY KEY,

    retired_ms BIGINT NOT NULL

);

CREATE TABLE proposal_reviews (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    proposal_id BIGINT NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    author_id   BIGINT REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    verdict     TEXT NOT NULL CHECK (verdict IN ('comment', 'changes', 'approve')),

    body        TEXT NOT NULL DEFAULT '',

    created_ms  BIGINT NOT NULL,

    via TEXT NOT NULL DEFAULT ''

);

CREATE TABLE assistants (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    BIGINT NOT NULL DEFAULT 0,

    name        CITEXT NOT NULL,

    endpoint    TEXT NOT NULL,

    api         TEXT NOT NULL,

    model       TEXT NOT NULL,

    headers     TEXT NOT NULL DEFAULT '',

    token       BYTEA NOT NULL,

    enabled     BOOLEAN NOT NULL DEFAULT TRUE,

    budget      BIGINT NOT NULL DEFAULT 120000,

    created_ms  BIGINT NOT NULL,

    updated_ms  BIGINT NOT NULL,

    UNIQUE (scope, scope_id, name)

);

CREATE TABLE assistant_prompts (

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    BIGINT NOT NULL DEFAULT 0,

    prompt      TEXT NOT NULL,

    updated_ms  BIGINT NOT NULL,

    PRIMARY KEY (scope, scope_id)

);

CREATE TABLE flow_variables (

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    BIGINT NOT NULL DEFAULT 0,

    name        TEXT NOT NULL,

    value       TEXT NOT NULL,

    updated_ms  BIGINT NOT NULL,

    PRIMARY KEY (scope, scope_id, name)

);

CREATE TABLE flow_secrets (

    scope       TEXT NOT NULL CHECK (scope IN ('site', 'org', 'repo')),

    scope_id    BIGINT NOT NULL DEFAULT 0,

    name        TEXT NOT NULL,

    value       BYTEA NOT NULL,

    updated_ms  BIGINT NOT NULL,

    PRIMARY KEY (scope, scope_id, name)

);

CREATE TABLE repo_redirects (

    old_owner   TEXT NOT NULL,

    old_name    TEXT NOT NULL,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    moved_ms    BIGINT NOT NULL,

    PRIMARY KEY (old_owner, old_name)

);

CREATE TABLE account_logins (

    provider    TEXT NOT NULL,

    subject     TEXT NOT NULL,

    account_id  BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    login       TEXT NOT NULL DEFAULT '',

    linked_ms   BIGINT NOT NULL,

    PRIMARY KEY (provider, subject)

);

CREATE TABLE flow_images (

    hash     TEXT PRIMARY KEY,

    arch     TEXT NOT NULL,

    seen_ms  BIGINT NOT NULL

);

CREATE TABLE flow_declines (

    job_id     BIGINT NOT NULL REFERENCES flow_jobs(id) ON DELETE CASCADE,

    runner_id  BIGINT NOT NULL,

    runner     TEXT NOT NULL,

    profile    TEXT NOT NULL,

    reason     TEXT NOT NULL,

    ms         BIGINT NOT NULL,

    PRIMARY KEY (job_id, runner_id)

);

CREATE TABLE repo_storage (

    repo_id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    url          TEXT NOT NULL,

    credentials  BYTEA NOT NULL,

    created_ms   BIGINT NOT NULL,

    checked_ms   BIGINT,

    check_result TEXT NOT NULL DEFAULT ''

);

CREATE TABLE org_storage_policy (

    org_id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY REFERENCES organisations(id) ON DELETE CASCADE,

    require_bucket BOOLEAN NOT NULL DEFAULT FALSE,

    allowed        TEXT NOT NULL DEFAULT ''

);

CREATE TABLE coverage_settings (

    repo_id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,

    min       DOUBLE PRECISION,

    max_drop  DOUBLE PRECISION

);

CREATE TABLE test_reports (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    run_id      BIGINT NOT NULL REFERENCES flow_runs(id) ON DELETE CASCADE,

    job_id      BIGINT NOT NULL UNIQUE,

    job         TEXT NOT NULL,

    run_number  BIGINT NOT NULL,

    bookmark    TEXT NOT NULL,

    state       TEXT NOT NULL,

    blob        TEXT NOT NULL,

    tests       BIGINT NOT NULL,

    passed      BIGINT NOT NULL,

    failed      BIGINT NOT NULL,

    errors      BIGINT NOT NULL,

    skipped     BIGINT NOT NULL,

    time_ms     BIGINT NOT NULL,

    created_ms  BIGINT NOT NULL

);

CREATE TABLE test_cases (

    report_id   BIGINT NOT NULL REFERENCES test_reports(id) ON DELETE CASCADE,

    idx         BIGINT NOT NULL,

    suite       TEXT NOT NULL,

    classname   TEXT NOT NULL,

    name        TEXT NOT NULL,

    status      TEXT NOT NULL,

    time_ms     BIGINT NOT NULL,

    message     TEXT NOT NULL,

    output      TEXT NOT NULL,

    PRIMARY KEY (report_id, idx)

);

CREATE TABLE project_redirects (

    owner      TEXT NOT NULL,

    number     BIGINT NOT NULL,

    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    PRIMARY KEY (owner, number)

);

CREATE TABLE reports (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id     BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    name        TEXT NOT NULL,

    kind        TEXT NOT NULL,

    title       TEXT NOT NULL,

    summary     TEXT NOT NULL,

    context     TEXT NOT NULL,

    run_id      BIGINT REFERENCES flow_runs(id) ON DELETE CASCADE,

    job_id      BIGINT,

    run_number  BIGINT,

    job         TEXT NOT NULL,

    bookmark    TEXT NOT NULL,

    state       TEXT NOT NULL,

    manifest    TEXT,

    files       BIGINT NOT NULL,

    bytes       BIGINT NOT NULL,

    created_ms  BIGINT NOT NULL,

    files_expired_ms BIGINT

);

CREATE TABLE report_metrics (

    report_id   BIGINT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,

    key         TEXT NOT NULL,

    value       DOUBLE PRECISION NOT NULL,

    unit        TEXT NOT NULL,

    better      TEXT NOT NULL,

    fail_above  DOUBLE PRECISION,

    fail_below  DOUBLE PRECISION,

    PRIMARY KEY (report_id, key)

);

CREATE TABLE report_files (

    report_id   BIGINT NOT NULL REFERENCES reports(id) ON DELETE CASCADE,

    path        TEXT NOT NULL,

    blob        TEXT NOT NULL,

    size        BIGINT NOT NULL,

    PRIMARY KEY (report_id, path)

);

CREATE TABLE jet_images (

    hash        TEXT PRIMARY KEY,

    size        BIGINT NOT NULL,

    arch        TEXT NOT NULL,

    chunks      BIGINT NOT NULL,

    uploader    TEXT NOT NULL,

    created_ms  BIGINT NOT NULL

);

CREATE TABLE coverage_reports (

    id               BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    repo_id          BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    run_id           BIGINT REFERENCES flow_runs(id) ON DELETE CASCADE,

    job_id           BIGINT UNIQUE,

    job              TEXT NOT NULL,

    run_number       BIGINT NOT NULL,

    bookmark         TEXT NOT NULL,

    state            TEXT NOT NULL,

    heads            TEXT NOT NULL,

    blob             TEXT NOT NULL,

    format           TEXT NOT NULL,

    lines_found      BIGINT NOT NULL,

    lines_hit        BIGINT NOT NULL,

    functions_found  BIGINT NOT NULL,

    functions_hit    BIGINT NOT NULL,

    branches_found   BIGINT NOT NULL,

    branches_hit     BIGINT NOT NULL,

    hits             BIGINT NOT NULL DEFAULT 0,

    files            BIGINT NOT NULL,

    dropped          BIGINT NOT NULL,

    created_ms       BIGINT NOT NULL,

    uploader         TEXT NOT NULL DEFAULT ''

);

CREATE TABLE coverage_files (

    report_id        BIGINT NOT NULL REFERENCES "coverage_reports"(id) ON DELETE CASCADE,

    path             TEXT NOT NULL,

    lines_found      BIGINT NOT NULL,

    lines_hit        BIGINT NOT NULL,

    functions_found  BIGINT NOT NULL,

    functions_hit    BIGINT NOT NULL,

    branches_found   BIGINT NOT NULL,

    branches_hit     BIGINT NOT NULL,

    PRIMARY KEY (report_id, path)

);

CREATE TABLE diffusion_pins (

    consumer_id  BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    bookmark     TEXT NOT NULL,

    path         TEXT NOT NULL,

    upstream     TEXT NOT NULL,

    heads        TEXT NOT NULL,

    updated_ms   BIGINT NOT NULL,

    upstream_repo_id BIGINT,

    PRIMARY KEY (consumer_id, bookmark, path)

);

CREATE TABLE backup_settings (

    owner       TEXT PRIMARY KEY,

    kind        TEXT NOT NULL,

    every       TEXT NOT NULL DEFAULT 'off',

    destination TEXT NOT NULL DEFAULT 'dir',

    target      TEXT NOT NULL DEFAULT '',

    prefix      TEXT NOT NULL DEFAULT '',

    provider    TEXT NOT NULL DEFAULT '',

    keep_days   BIGINT NOT NULL DEFAULT 30,

    updated_ms  BIGINT NOT NULL,

    region TEXT NOT NULL DEFAULT ''

);

CREATE TABLE backup_runs (

    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    owner       TEXT NOT NULL,

    started_ms  BIGINT NOT NULL,

    finished_ms BIGINT,

    status      TEXT NOT NULL,

    at          TEXT NOT NULL DEFAULT '',

    repos       BIGINT NOT NULL DEFAULT 0,

    files       BIGINT NOT NULL DEFAULT 0,

    sent        BIGINT NOT NULL DEFAULT 0,

    bytes       BIGINT NOT NULL DEFAULT 0,

    detail      TEXT NOT NULL DEFAULT '',

    trigger     TEXT NOT NULL DEFAULT 'schedule'

);

CREATE TABLE sessions (

    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    hash TEXT NOT NULL UNIQUE,

    salt TEXT NOT NULL,

    created_ms BIGINT NOT NULL,

    seen_ms BIGINT NOT NULL,

    address TEXT NOT NULL DEFAULT '',

    agent TEXT NOT NULL DEFAULT ''

);

CREATE TABLE diffusion_indexed (

    consumer_id BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    bookmark TEXT NOT NULL,

    heads TEXT NOT NULL,

    updated_ms BIGINT NOT NULL,

    PRIMARY KEY (consumer_id, bookmark)

);

CREATE INDEX account_links_by_account ON account_links (account_id, kind, created_ms DESC);

CREATE INDEX account_logins_by_account ON account_logins (account_id);

CREATE INDEX audit_by_time ON audit (at_ms DESC);

CREATE INDEX backup_runs_by_owner ON backup_runs (owner, id DESC);

CREATE INDEX checks_by_state ON checks (repo_id, state);

CREATE UNIQUE INDEX collaborators_account ON collaborators (repo_id, account_id) WHERE account_id IS NOT NULL;

CREATE UNIQUE INDEX collaborators_team ON collaborators (repo_id, team_id) WHERE team_id IS NOT NULL;

CREATE INDEX coverage_reports_bookmark ON coverage_reports (repo_id, bookmark, created_ms);

CREATE INDEX deliveries_by_hook ON deliveries (hook_id, created_ms DESC);

CREATE INDEX deliveries_due ON deliveries (next_ms) WHERE delivered_ms IS NULL;

CREATE INDEX diffusion_pins_by_upstream ON diffusion_pins (upstream);

CREATE INDEX flow_artifacts_content ON flow_artifacts(content);

CREATE INDEX flow_declines_runner ON flow_declines (runner_id, ms);

CREATE INDEX flow_jobs_environment ON flow_jobs(environment) WHERE environment != '';

CREATE INDEX flow_jobs_queued ON flow_jobs (status, id);

CREATE INDEX flow_jobs_runner ON flow_jobs (runner_id);

CREATE INDEX flow_jobs_token ON flow_jobs (token_hash) WHERE token_hash <> '';

CREATE INDEX flow_runs_by_repo ON flow_runs (repo_id, id DESC);

CREATE UNIQUE INDEX flow_runs_number ON flow_runs (repo_id, number);

CREATE INDEX issue_comments_by_issue ON issue_comments (issue_id, created_ms);

CREATE INDEX notifications_by_account ON notifications (account_id, read, at_ms DESC);

CREATE INDEX notifications_by_thread ON notifications (account_id, done, thread, at_ms DESC);

CREATE INDEX project_cards_by_column ON project_cards (column_id, position);

CREATE UNIQUE INDEX project_cards_once ON project_cards (project_id, repo_id, number) WHERE kind <> 'note';

CREATE INDEX project_columns_by_project ON project_columns (project_id, position);

CREATE INDEX proposal_comments_by_proposal ON proposal_comments (proposal_id, created_ms);

CREATE INDEX proposal_reviews_by_proposal ON proposal_reviews (proposal_id, created_ms);

CREATE INDEX pushes_by_repo ON pushes (repo_id, at_ms DESC);

CREATE INDEX report_files_blob ON report_files (blob);

CREATE INDEX reports_history ON reports (repo_id, name, bookmark, created_ms);

CREATE UNIQUE INDEX reports_job ON reports (job_id, name);

CREATE INDEX repositories_forks ON repositories (forked_from) WHERE forked_from IS NOT NULL;

CREATE INDEX sessions_account ON sessions(account_id);

CREATE INDEX stars_by_repo ON stars (repo_id);

CREATE INDEX test_cases_name ON test_cases (classname, name);

CREATE INDEX test_reports_bookmark ON test_reports (repo_id, bookmark, created_ms);

-- Full-text search (SQLite: an FTS5 table with the porter tokenizer):

-- English stemming, titles weighted above bodies.

CREATE TABLE search (

    kind      TEXT NOT NULL,

    repo_id   BIGINT NOT NULL,

    target_id BIGINT NOT NULL,

    number    BIGINT NOT NULL,

    title     TEXT NOT NULL,

    body      TEXT NOT NULL,

    doc       tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) STORED

);

CREATE INDEX search_doc ON search USING GIN (doc);

CREATE INDEX search_target ON search (kind, target_id);

//! The two schemas agree (#211): every table and column SQLite's

//! migrations make, `migrations-pg` makes too, so a change written for one

//! engine and not the other fails here. Postgres builds only, as the

//! Postgres tests run (`OXYHUB_DATABASE_URL`, `OXYHUB_DATABASE_SCHEMA=*`).

#![cfg(feature = "postgres")]

use oxygenhub::config::Config;

use std::collections::{BTreeMap, BTreeSet};

type Tables = BTreeMap<String, BTreeSet<String>>;

/// The full-text index is each engine's own (FTS5's shadow tables, a

/// generated tsvector), and the migrations' ledger is sqlx's.

fn compared(table: &str) -> bool {

    !table.starts_with("search") && !table.starts_with("_sqlx")

}

async fn sqlite() -> Tables {

    use sqlx::Connection;

    let mut conn = sqlx::SqliteConnection::connect("sqlite::memory:").await.unwrap();

    sqlx::migrate!("./migrations").run(&mut conn).await.unwrap();

    let names: Vec<String> =

        sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'").fetch_all(&mut conn).await.unwrap();

    let mut out = Tables::new();

    for t in names.into_iter().filter(|t| compared(t)) {

        let cols: Vec<String> =

            sqlx::query_scalar(&format!("SELECT name FROM pragma_table_info('{t}')")).fetch_all(&mut conn).await.unwrap();

        out.insert(t, cols.into_iter().collect());

    }

    out

}

async fn postgres() -> Tables {

    let dir = tempfile::tempdir().unwrap();

    let config = Config::load(dir.path()).unwrap();

    let pool = oxygenhub::db::open(&config).await.unwrap();

    let rows: Vec<(String, String)> = sqlx::query_as(

        "SELECT CAST(t.relname AS TEXT), CAST(a.attname AS TEXT) FROM pg_attribute a JOIN pg_class t ON t.oid = a.attrelid \

         WHERE t.relkind = 'r' AND t.relnamespace = CAST(current_schema() AS regnamespace) AND a.attnum > 0 AND NOT a.attisdropped",

    )

    .fetch_all(&pool)

    .await

    .unwrap();

    let mut out = Tables::new();

    for (t, c) in rows.into_iter().filter(|(t, _)| compared(t)) {

        out.entry(t).or_default().insert(c);

    }

    out

}

#[tokio::test]

async fn the_postgres_schema_has_every_table_and_column_sqlite_has() {

    let (lite, pg) = (sqlite().await, postgres().await);

    let only = |a: &Tables, b: &Tables| -> Vec<String> {

        a.iter()

            .flat_map(|(t, cols)| match b.get(t) {

                None => vec![format!("table {t}")],

                Some(other) => cols.difference(other).map(|c| format!("{t}.{c}")).collect(),

            })

            .collect()

    };

    assert_eq!(only(&lite, &pg), Vec::<String>::new(), "in SQLite's migrations but not migrations-pg");

    assert_eq!(only(&pg, &lite), Vec::<String>::new(), "in migrations-pg but not SQLite's migrations");

}

# OxygenHub on PostgreSQL

A hub keeps its records in SQLite (`<data>/hub.db`) unless it is built with

the `postgres` feature, when it keeps them in PostgreSQL 16 or later

(oxygenhub#211). The engine is chosen when the hub is built, not when it

starts: one binary speaks one engine.

## Build and configure

```sh

cargo build --release -p oxygenhub --features postgres

```

```toml

# <data>/hub.toml

[database]

url = "postgres://[email protected]/hub"   # the password from PGPASSWORD or the URL

schema = ""                             # empty: public

```

`OXYHUB_DATABASE_URL` and `OXYHUB_DATABASE_SCHEMA` override the file. The

hub makes the `citext` extension (in `public`) and its schema if they are

missing, then applies `crates/hub/migrations-pg`. Repositories, keys and

uploads stay in the data directory as before.

`schema = "*"` gives each data directory a schema of its own (`hub_<dir>`),

so many hubs can share one database. The tests use it.

## Two schemas, one set of queries

The queries are the same on both engines: `$n` placeholders, `ON CONFLICT`,

`TRUE`/`FALSE`, `RETURNING`, `CASE` for counting flags, window functions,

`lower()` for comparing names. Postgres is stricter than SQLite, so:

- A flag column is `BOOLEAN` on Postgres and `INTEGER` on SQLite. Bind and

  read it as `bool`, and compare it with `TRUE`/`FALSE`, never `1`/`0`.

- `SUM` of a `BIGINT` is `NUMERIC` on Postgres: `CAST(SUM(…) AS BIGINT)`.

- Every column a `GROUP BY` query returns is grouped or aggregated.

- `HAVING` and `WHERE` cannot name a `SELECT` alias: repeat the expression or

  select from a subquery.

The few places that differ choose by `cfg(feature = "postgres")`:

connecting and migrating (`db::open`), the write lock (`db::begin_write`:

`BEGIN IMMEDIATE`, or an advisory lock), full-text search (FTS5, or a

`tsvector` with `ts_headline`), JSON (`db::json_text`, `json_each`), and the

catalog backups read (`backups::catalog`). A backup taken on either engine

restores into either: flags are written as 0 or 1, blobs as hex.

## Changing the schema

`migrations-pg/0001_schema.sql` is SQLite's 0001–0072 as one. Each later

change is two files with the same number, one in `migrations` (SQLite) and

one in `migrations-pg`. `tests/schema.rs` fails when the two disagree about a

table or column.

## Testing on Postgres

```sh

docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \

  -p 127.0.0.1:55432:5432 postgres:16-alpine

OXYHUB_DATABASE_URL=postgres://hub:[email protected]:55432/hub OXYHUB_DATABASE_SCHEMA='*' \

  cargo test -p oxygenhub --features postgres

```

Each test makes its own schema; drop the `hub_%` schemas between runs.

 "sqlx-postgres",

[features]

# PostgreSQL instead of SQLite (#211): `[database] url` in hub.toml.

postgres = ["sqlx/postgres"]

    let mut t = catalog::tables(pool).await?;

    Ok(table_cols(pool, table).await?.into_iter().map(|c| c.name).collect())

/// left out, blobs as hex and flags as 0 or 1, whichever engine wrote them,

/// so a run from either restores into either.

    let cols: Vec<Col> = table_cols(pool, table).await?.into_iter().filter(|c| !private_column(&c.name)).collect();

    if cols.is_empty() {

    let text: String = sqlx::query_scalar(&catalog::rows_sql(table, &cols, cond)).fetch_one(pool).await?;

/// What backups ask of the database's own catalog, per engine. Column

/// types are given in SQLite's words (INTEGER, TEXT, BLOB, REAL) plus

/// BOOLEAN, which only Postgres has.

#[cfg(not(feature = "postgres"))]

mod catalog {

    use super::{Col, Pool, Result};

    pub async fn tables(pool: &Pool) -> Result<Vec<String>> {

        Ok(sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'").fetch_all(pool).await?)

    }

    pub async fn cols(pool: &Pool, table: &str) -> Result<Vec<Col>> {

        let rows: Vec<(String, String, i64, Option<String>, i64)> =

            sqlx::query_as(&format!("SELECT name, type, \"notnull\", dflt_value, pk FROM pragma_table_info('{table}')"))

                .fetch_all(pool)

                .await?;

        Ok(rows.into_iter().map(|(name, ty, notnull, default, pk)| Col { name, ty, notnull: notnull != 0, default, pk }).collect())

    }

    /// `(parent table, column, parent column)` for each reference.

    pub async fn fks(pool: &Pool, table: &str) -> Result<Vec<(String, String, String)>> {

        Ok(sqlx::query_as(&format!("SELECT \"table\", \"from\", \"to\" FROM pragma_foreign_key_list('{table}')")).fetch_all(pool).await?)

    }

    pub fn rows_sql(table: &str, cols: &[Col], cond: &str) -> String {

        let fields: Vec<String> =

            cols.iter().map(|c| format!("'{0}', CASE WHEN typeof(\"{0}\") = 'blob' THEN hex(\"{0}\") ELSE \"{0}\" END", c.name)).collect();

        format!("SELECT json_group_array(json_object({})) FROM \"{table}\" WHERE {cond}", fields.join(", "))

    }

    /// How a restored row goes in when its key is taken: `INSERT …` then this.

    pub fn insert(verb: super::Verb, _cols: &[String]) -> (&'static str, String) {

        match verb {

            super::Verb::Replace => ("INSERT OR REPLACE", String::new()),

            super::Verb::New => ("INSERT", String::new()),

            super::Verb::Ignore => ("INSERT OR IGNORE", String::new()),

        }

    }

}

#[cfg(feature = "postgres")]

mod catalog {

    use super::{Col, Pool, Result};

    pub async fn tables(pool: &Pool) -> Result<Vec<String>> {

        Ok(sqlx::query_scalar(

            "SELECT CAST(relname AS TEXT) FROM pg_class WHERE relkind = 'r' AND relnamespace = CAST(current_schema() AS regnamespace)",

        )

        .fetch_all(pool)

        .await?)

    }

    /// Generated columns (the search index's) are left out: they are made,

    /// never restored.

    pub async fn cols(pool: &Pool, table: &str) -> Result<Vec<Col>> {

        // pg_catalog, not information_schema: many times faster.

        let rows: Vec<(String, String, bool, Option<String>, i64)> = sqlx::query_as(

            "SELECT CAST(a.attname AS TEXT), format_type(a.atttypid, a.atttypmod), a.attnotnull, pg_get_expr(d.adbin, d.adrelid), \

               CAST(COALESCE((SELECT array_position(k.conkey, a.attnum) FROM pg_constraint k WHERE k.conrelid = t.oid AND k.contype = 'p'), 0) AS BIGINT) \

             FROM pg_attribute a JOIN pg_class t ON t.oid = a.attrelid \

             LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum \

             WHERE t.relname = $1 AND t.relnamespace = CAST(current_schema() AS regnamespace) \

               AND a.attnum > 0 AND NOT a.attisdropped AND a.attgenerated = '' ORDER BY a.attnum",

        )

        .bind(table)

        .fetch_all(pool)

        .await?;

        Ok(rows

            .into_iter()

            .map(|(name, ty, notnull, default, pk)| {

                let ty = match ty.as_str() {

                    "bigint" | "integer" | "smallint" => "INTEGER",

                    "bytea" => "BLOB",

                    "boolean" => "BOOLEAN",

                    "double precision" | "real" => "REAL",

                    _ => "TEXT",

                };

                // An identity column's default is the sequence, as SQLite's

                // rowid: none to give.

                let default = default.filter(|d| !d.starts_with("nextval("));

                Col { name, ty: ty.to_string(), notnull, default, pk }

            })

            .collect())

    }

    /// `(parent table, column, parent column)` for each reference.

    pub async fn fks(pool: &Pool, table: &str) -> Result<Vec<(String, String, String)>> {

        Ok(sqlx::query_as(

            "SELECT CAST(p.relname AS TEXT), CAST(a.attname AS TEXT), CAST(pa.attname AS TEXT) FROM pg_constraint k \

             JOIN pg_class t ON t.oid = k.conrelid JOIN pg_class p ON p.oid = k.confrelid \

             JOIN pg_attribute a ON a.attrelid = k.conrelid AND a.attnum = k.conkey[1] \

             JOIN pg_attribute pa ON pa.attrelid = k.confrelid AND pa.attnum = k.confkey[1] \

             WHERE k.contype = 'f' AND t.relname = $1 AND t.relnamespace = CAST(current_schema() AS regnamespace)",

        )

        .bind(table)

        .fetch_all(pool)

        .await?)

    }

    pub fn rows_sql(table: &str, cols: &[Col], cond: &str) -> String {

        let fields: Vec<String> = cols

            .iter()

            .map(|c| match c.ty.as_str() {

                "BLOB" => format!("upper(encode(\"{0}\", 'hex')) AS \"{0}\"", c.name),

                "BOOLEAN" => format!("CASE WHEN \"{0}\" THEN 1 ELSE 0 END AS \"{0}\"", c.name),

                _ => format!("\"{0}\"", c.name),

            })

            .collect();

        format!("SELECT CAST(COALESCE(json_agg(r), '[]') AS TEXT) FROM (SELECT {} FROM \"{table}\" WHERE {cond}) r", fields.join(", "))

    }

    /// How a restored row goes in when its key is taken: `INSERT …` then this.

    pub fn insert(verb: super::Verb, cols: &[String]) -> (&'static str, String) {

        match verb {

            super::Verb::Replace => {

                let set: Vec<String> = cols.iter().filter(|c| *c != "repo_id").map(|c| format!("\"{c}\" = excluded.\"{c}\"")).collect();

                let tail = if set.is_empty() {

                    " ON CONFLICT (repo_id) DO NOTHING".to_string()

                } else {

                    format!(" ON CONFLICT (repo_id) DO UPDATE SET {}", set.join(", "))

                };

                ("INSERT", tail)

            }

            super::Verb::New => ("INSERT", String::new()),

            super::Verb::Ignore => ("INSERT", " ON CONFLICT DO NOTHING".to_string()),

        }

    }

}

/// How restored rows meet the rows already there.

#[derive(Clone, Copy)]

enum Verb {

    /// One row per repository: the restored one wins.

    Replace,

    /// A row with an id of its own, given a new one.

    New,

    /// Kept as it is when already there.

    Ignore,

}

            let fks = catalog::fks(pool, t).await?;

    catalog::fks(pool, table).await

    catalog::cols(pool, table).await

/// Bind one value of a backed-up row to a column of type `ty`: blobs were

/// written as hex, flags as 0 or 1 (a Postgres BOOLEAN takes them as such).

    q: sqlx::query::Query<'q, crate::db::Db, <crate::db::Db as sqlx::Database>::Arguments<'q>>,

    ty: &str,

) -> sqlx::query::Query<'q, crate::db::Db, <crate::db::Db as sqlx::Database>::Arguments<'q>> {

    let blob = ty.eq_ignore_ascii_case("BLOB");

    if ty == "BOOLEAN" {

        return match v {

            serde_json::Value::Null => q.bind(None::<bool>),

            serde_json::Value::Bool(b) => q.bind(b),

            serde_json::Value::Number(n) => q.bind(n.as_f64().unwrap_or(0.0) != 0.0),

            other => q.bind(matches!(other.as_str(), Some("1" | "true"))),

        };

    }

            Verb::Replace

            Verb::New

            Verb::Ignore

                        values.push((v, c.ty.as_str()));

                        let blank = if c.ty.to_ascii_uppercase().contains("INT") || c.ty == "BOOLEAN" {

                            serde_json::json!(0)

                        } else {

                            serde_json::json!("")

                        };