| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S41a workflow jobs — one row per `jobs.<id>` block in the workflow. |
| 4 | -- |
| 5 | -- Each row pairs 1:1 with a check_runs row created in S41b |
| 6 | -- (app_slug='shithub-actions'); status transitions on this table cascade |
| 7 | -- to that row via the suite-rollup logic in internal/checks/. Status |
| 8 | -- moves queued → running → completed | cancelled. conclusion uses the |
| 9 | -- existing check_conclusion enum from 0025. |
| 10 | -- |
| 11 | -- runner_id binds a claimed job to the runner that's executing it |
| 12 | -- (workflow_runners table, 0046). NULL = not yet claimed. cancel_requested |
| 13 | -- is the boolean the runner heartbeat checks (S41g); we add it from day |
| 14 | -- one so the runner protocol doesn't need a column-add later. |
| 15 | -- |
| 16 | -- needs_jobs[] mirrors GHA's `needs:` — the names (not IDs) of |
| 17 | -- prerequisite jobs in the same run. Resolved at trigger time (S41b) |
| 18 | -- against same-run job names; cycles + dangling refs caught by the |
| 19 | -- parser (S41a expr/eval). |
| 20 | -- |
| 21 | -- if_expr is the parsed-but-unevaluated `if:` expression carried as |
| 22 | -- text; the evaluator (S41a) consumes it at dispatch time. |
| 23 | -- |
| 24 | -- timeout_minutes mirrors GHA semantics; runner enforcement lands in |
| 25 | -- S41g but we carry the column now. |
| 26 | |
| 27 | -- +goose Up |
| 28 | |
| 29 | CREATE TYPE workflow_job_status AS ENUM ( |
| 30 | 'queued', 'running', 'completed', 'cancelled', 'skipped' |
| 31 | ); |
| 32 | |
| 33 | CREATE TABLE workflow_jobs ( |
| 34 | id bigserial PRIMARY KEY, |
| 35 | run_id bigint NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE, |
| 36 | job_index integer NOT NULL, |
| 37 | job_key text NOT NULL, |
| 38 | job_name text NOT NULL DEFAULT '', |
| 39 | runs_on text NOT NULL DEFAULT '', |
| 40 | runner_id bigint, |
| 41 | needs_jobs text[] NOT NULL DEFAULT ARRAY[]::text[], |
| 42 | if_expr text NOT NULL DEFAULT '', |
| 43 | timeout_minutes integer NOT NULL DEFAULT 360, |
| 44 | permissions jsonb NOT NULL DEFAULT '{}'::jsonb, |
| 45 | job_env jsonb NOT NULL DEFAULT '{}'::jsonb, |
| 46 | status workflow_job_status NOT NULL DEFAULT 'queued', |
| 47 | conclusion check_conclusion, |
| 48 | cancel_requested boolean NOT NULL DEFAULT false, |
| 49 | started_at timestamptz, |
| 50 | completed_at timestamptz, |
| 51 | version integer NOT NULL DEFAULT 0, |
| 52 | created_at timestamptz NOT NULL DEFAULT now(), |
| 53 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 54 | |
| 55 | UNIQUE (run_id, job_key), |
| 56 | |
| 57 | CONSTRAINT workflow_jobs_job_key_format CHECK ( |
| 58 | char_length(job_key) BETWEEN 1 AND 100 |
| 59 | AND job_key ~ '^[A-Za-z_][A-Za-z0-9_-]*$' |
| 60 | ), |
| 61 | CONSTRAINT workflow_jobs_job_name_length CHECK (char_length(job_name) <= 256), |
| 62 | CONSTRAINT workflow_jobs_runs_on_length CHECK (char_length(runs_on) <= 256), |
| 63 | CONSTRAINT workflow_jobs_timeout_range CHECK (timeout_minutes BETWEEN 1 AND 4320), |
| 64 | CONSTRAINT workflow_jobs_completed_has_conclusion CHECK ( |
| 65 | status NOT IN ('completed', 'skipped') OR conclusion IS NOT NULL |
| 66 | ), |
| 67 | CONSTRAINT workflow_jobs_runner_when_running CHECK ( |
| 68 | status NOT IN ('running', 'completed') OR runner_id IS NOT NULL |
| 69 | ) |
| 70 | ); |
| 71 | |
| 72 | CREATE INDEX workflow_jobs_run_idx ON workflow_jobs (run_id); |
| 73 | CREATE INDEX workflow_jobs_runner_idx ON workflow_jobs (runner_id, status) |
| 74 | WHERE runner_id IS NOT NULL; |
| 75 | CREATE INDEX workflow_jobs_status_idx ON workflow_jobs (status, created_at) |
| 76 | WHERE status IN ('queued', 'running'); |
| 77 | |
| 78 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON workflow_jobs |
| 79 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 80 | |
| 81 | |
| 82 | -- +goose Down |
| 83 | DROP TABLE IF EXISTS workflow_jobs; |
| 84 | DROP TYPE IF EXISTS workflow_job_status; |
| 85 |