| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S41a workflow runs — top-level row per triggered workflow. |
| 4 | -- |
| 5 | -- Each push / pull_request / schedule / workflow_dispatch event matched |
| 6 | -- against a parsed `.shithub/workflows/*.yml` produces one row here. |
| 7 | -- Child rows live in workflow_jobs (0043) and workflow_steps (0044). |
| 8 | -- Status moves queued → running → completed | cancelled. conclusion is |
| 9 | -- set on completion via the same enum values check_runs uses (S24); |
| 10 | -- one workflow_jobs row maps to one check_runs row (S41b creates the |
| 11 | -- mapping). |
| 12 | -- |
| 13 | -- Per-repo run_index gives stable URLs (/owner/repo/actions/runs/42) |
| 14 | -- without leaking global IDs across repos. Pattern cribbed from |
| 15 | -- Forgejo's actions_run.index — see .refs/forgejo/models/actions/run.go. |
| 16 | -- |
| 17 | -- Optimistic-lock `version` column lets the runner + the cancel path |
| 18 | -- update status concurrently without overwriting each other (Forgejo |
| 19 | -- pattern, same file). |
| 20 | -- |
| 21 | -- concurrency_group is parsed in S41a but only honored from S41g; we |
| 22 | -- carry the column from day one so retroactive backfill isn't needed. |
| 23 | -- Fork-PR approval flow (need_approval, approved_by) is parked for |
| 24 | -- v2 but the columns exist so the schema doesn't churn later. |
| 25 | |
| 26 | -- +goose Up |
| 27 | |
| 28 | CREATE TYPE workflow_run_status AS ENUM ( |
| 29 | 'queued', 'running', 'completed', 'cancelled' |
| 30 | ); |
| 31 | |
| 32 | CREATE TYPE workflow_run_event AS ENUM ( |
| 33 | 'push', 'pull_request', 'schedule', 'workflow_dispatch' |
| 34 | ); |
| 35 | |
| 36 | CREATE TABLE workflow_runs ( |
| 37 | id bigserial PRIMARY KEY, |
| 38 | repo_id bigint NOT NULL REFERENCES repos(id) ON DELETE CASCADE, |
| 39 | run_index bigint NOT NULL, |
| 40 | workflow_file text NOT NULL, |
| 41 | workflow_name text NOT NULL DEFAULT '', |
| 42 | head_sha text NOT NULL, |
| 43 | head_ref text NOT NULL DEFAULT '', |
| 44 | event workflow_run_event NOT NULL, |
| 45 | event_payload jsonb NOT NULL DEFAULT '{}'::jsonb, |
| 46 | actor_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 47 | parent_run_id bigint REFERENCES workflow_runs(id) ON DELETE SET NULL, |
| 48 | concurrency_group text NOT NULL DEFAULT '', |
| 49 | status workflow_run_status NOT NULL DEFAULT 'queued', |
| 50 | conclusion check_conclusion, |
| 51 | pinned boolean NOT NULL DEFAULT false, |
| 52 | need_approval boolean NOT NULL DEFAULT false, |
| 53 | approved_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 54 | started_at timestamptz, |
| 55 | completed_at timestamptz, |
| 56 | version integer NOT NULL DEFAULT 0, |
| 57 | created_at timestamptz NOT NULL DEFAULT now(), |
| 58 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 59 | |
| 60 | UNIQUE (repo_id, run_index), |
| 61 | |
| 62 | CONSTRAINT workflow_runs_workflow_file_length CHECK (char_length(workflow_file) BETWEEN 1 AND 256), |
| 63 | CONSTRAINT workflow_runs_workflow_name_length CHECK (char_length(workflow_name) <= 256), |
| 64 | CONSTRAINT workflow_runs_head_sha_format CHECK (char_length(head_sha) BETWEEN 7 AND 64), |
| 65 | CONSTRAINT workflow_runs_head_ref_length CHECK (char_length(head_ref) <= 256), |
| 66 | CONSTRAINT workflow_runs_concurrency_length CHECK (char_length(concurrency_group) <= 256), |
| 67 | CONSTRAINT workflow_runs_completed_has_conclusion CHECK ( |
| 68 | status <> 'completed' OR conclusion IS NOT NULL |
| 69 | ), |
| 70 | CONSTRAINT workflow_runs_started_when_running CHECK ( |
| 71 | status NOT IN ('running', 'completed', 'cancelled') OR started_at IS NOT NULL |
| 72 | ), |
| 73 | CONSTRAINT workflow_runs_completed_when_done CHECK ( |
| 74 | status NOT IN ('completed', 'cancelled') OR completed_at IS NOT NULL |
| 75 | ) |
| 76 | ); |
| 77 | |
| 78 | CREATE INDEX workflow_runs_repo_head_idx ON workflow_runs (repo_id, head_sha); |
| 79 | CREATE INDEX workflow_runs_repo_status_idx ON workflow_runs (repo_id, status, created_at DESC); |
| 80 | CREATE INDEX workflow_runs_actor_idx ON workflow_runs (actor_user_id, created_at DESC); |
| 81 | CREATE INDEX workflow_runs_concurrency_idx ON workflow_runs (repo_id, concurrency_group, status) |
| 82 | WHERE concurrency_group <> ''; |
| 83 | CREATE INDEX workflow_runs_event_idx ON workflow_runs (repo_id, event, created_at DESC); |
| 84 | CREATE INDEX workflow_runs_parent_idx ON workflow_runs (parent_run_id) |
| 85 | WHERE parent_run_id IS NOT NULL; |
| 86 | |
| 87 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON workflow_runs |
| 88 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 89 | |
| 90 | |
| 91 | -- +goose Down |
| 92 | DROP TABLE IF EXISTS workflow_runs; |
| 93 | DROP TYPE IF EXISTS workflow_run_event; |
| 94 | DROP TYPE IF EXISTS workflow_run_status; |
| 95 |