| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S41a workflow runners + per-runner registration tokens. |
| 4 | -- |
| 5 | -- workflow_runners — one row per registered runner (operator runs |
| 6 | -- `shithubd admin runner register --name foo --labels self-hosted,linux`). |
| 7 | -- The labels[] array is what `runs-on:` matches against. last_heartbeat_at |
| 8 | -- is updated by the runner's heartbeat endpoint (S41c); rows where |
| 9 | -- last_heartbeat_at is older than `runner.heartbeat_timeout` (config, |
| 10 | -- default 60s) are considered offline. |
| 11 | -- |
| 12 | -- runner_tokens — registration tokens minted at admin-register time. |
| 13 | -- The plaintext token is shown to the operator once and never persisted; |
| 14 | -- token_hash is what we store + compare on heartbeat. expires_at NULL |
| 15 | -- means the registration token doesn't expire (long-lived); when set, |
| 16 | -- the token is rejected after that point. revoked_at is the operator's |
| 17 | -- revoke knob (`shithubd admin runner revoke --id N`). |
| 18 | -- |
| 19 | -- The per-job JWT (15-min, single-use, scoped to one workflow_jobs.id) |
| 20 | -- is a separate construct minted in-memory and validated against |
| 21 | -- runner_jwt_used (S41c migration); it does NOT live in this table. |
| 22 | |
| 23 | -- +goose Up |
| 24 | |
| 25 | CREATE TYPE workflow_runner_status AS ENUM ('idle', 'busy', 'offline'); |
| 26 | |
| 27 | CREATE TABLE workflow_runners ( |
| 28 | id bigserial PRIMARY KEY, |
| 29 | name text NOT NULL, |
| 30 | labels text[] NOT NULL DEFAULT ARRAY[]::text[], |
| 31 | capacity integer NOT NULL DEFAULT 1, |
| 32 | status workflow_runner_status NOT NULL DEFAULT 'offline', |
| 33 | last_heartbeat_at timestamptz, |
| 34 | registered_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 35 | created_at timestamptz NOT NULL DEFAULT now(), |
| 36 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 37 | |
| 38 | CONSTRAINT workflow_runners_name_length CHECK (char_length(name) BETWEEN 1 AND 100), |
| 39 | CONSTRAINT workflow_runners_name_format CHECK (name ~ '^[A-Za-z0-9_-]+$'), |
| 40 | CONSTRAINT workflow_runners_capacity_range CHECK (capacity BETWEEN 1 AND 64) |
| 41 | ); |
| 42 | |
| 43 | CREATE UNIQUE INDEX workflow_runners_name_idx ON workflow_runners (name); |
| 44 | CREATE INDEX workflow_runners_status_idx ON workflow_runners (status); |
| 45 | CREATE INDEX workflow_runners_labels_idx ON workflow_runners USING GIN (labels); |
| 46 | |
| 47 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON workflow_runners |
| 48 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 49 | |
| 50 | |
| 51 | CREATE TABLE runner_tokens ( |
| 52 | id bigserial PRIMARY KEY, |
| 53 | runner_id bigint NOT NULL REFERENCES workflow_runners(id) ON DELETE CASCADE, |
| 54 | token_hash bytea NOT NULL, |
| 55 | expires_at timestamptz, |
| 56 | revoked_at timestamptz, |
| 57 | created_at timestamptz NOT NULL DEFAULT now(), |
| 58 | |
| 59 | CONSTRAINT runner_tokens_token_hash_length CHECK (octet_length(token_hash) = 32) |
| 60 | ); |
| 61 | |
| 62 | CREATE UNIQUE INDEX runner_tokens_hash_idx ON runner_tokens (token_hash); |
| 63 | CREATE INDEX runner_tokens_runner_idx ON runner_tokens (runner_id); |
| 64 | |
| 65 | |
| 66 | -- The forward reference from workflow_jobs.runner_id to workflow_runners.id |
| 67 | -- couldn't be expressed in 0043 because that table didn't exist yet. |
| 68 | -- Add the FK now that both tables are in place. |
| 69 | ALTER TABLE workflow_jobs |
| 70 | ADD CONSTRAINT workflow_jobs_runner_id_fkey |
| 71 | FOREIGN KEY (runner_id) REFERENCES workflow_runners(id) ON DELETE SET NULL; |
| 72 | |
| 73 | |
| 74 | -- +goose Down |
| 75 | ALTER TABLE workflow_jobs |
| 76 | DROP CONSTRAINT IF EXISTS workflow_jobs_runner_id_fkey; |
| 77 | DROP TABLE IF EXISTS runner_tokens; |
| 78 | DROP TABLE IF EXISTS workflow_runners; |
| 79 | DROP TYPE IF EXISTS workflow_runner_status; |
| 80 |