-- OxygenFlow runs, jobs and their logs (hub spec §13.2).

CREATE TABLE flow_runs (

    id            INTEGER PRIMARY KEY,

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

    bookmark      TEXT NOT NULL,

    -- The bookmark's state hash (checks are keyed by it) and its heads, joined with '+'.

    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')),

    -- Pipeline problems, one per line, for a run in error.

    error         TEXT NOT NULL DEFAULT '',

    actor         TEXT NOT NULL DEFAULT '',

    created_ms    INTEGER NOT NULL,

    finished_ms   INTEGER

);

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

CREATE TABLE flow_jobs (

    id           INTEGER PRIMARY KEY,

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

    name         TEXT NOT NULL,

    depth        INTEGER NOT NULL,

    -- JSON: the job's dependencies, compiled form and sandbox (flow-core types).

    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')),

    runner       TEXT NOT NULL DEFAULT '',

    token_hash   TEXT NOT NULL DEFAULT '',

    started_ms   INTEGER,

    finished_ms  INTEGER,

    exit_code    INTEGER,

    error        TEXT NOT NULL DEFAULT '',

    receipt      TEXT NOT NULL DEFAULT '',

    receipt_hash TEXT NOT NULL DEFAULT '',

    log_count    INTEGER NOT NULL DEFAULT 0,

    UNIQUE (run_id, name)

);

CREATE INDEX flow_jobs_queued ON flow_jobs (status, id);

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

CREATE TABLE flow_logs (

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

    seq    INTEGER NOT NULL,

    t      INTEGER NOT NULL,

    stream TEXT NOT NULL,

    text   TEXT NOT NULL,

    PRIMARY KEY (job_id, seq)

);