| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S41j-3 Actions policy, abuse caps, and approval decisions. |
| 4 | -- |
| 5 | -- The existing workflow_runs.need_approval / approved_by_user_id columns remain |
| 6 | -- the fast path for runner dispatch. The policy tables below hold durable |
| 7 | -- site/org/repo defaults, and workflow_run_approvals records the explicit |
| 8 | -- approval/rejection decision without overloading run status fields. |
| 9 | |
| 10 | -- +goose Up |
| 11 | |
| 12 | CREATE TYPE actions_policy_state AS ENUM ('inherit', 'enabled', 'disabled'); |
| 13 | |
| 14 | CREATE TABLE actions_site_policy ( |
| 15 | id boolean PRIMARY KEY DEFAULT true CHECK (id), |
| 16 | actions_enabled boolean NOT NULL DEFAULT true, |
| 17 | require_pr_approval boolean NOT NULL DEFAULT true, |
| 18 | max_repo_queued_runs integer NOT NULL DEFAULT 50, |
| 19 | max_repo_concurrent_jobs integer NOT NULL DEFAULT 20, |
| 20 | max_owner_concurrent_jobs integer NOT NULL DEFAULT 100, |
| 21 | actor_trigger_limit_per_hour integer NOT NULL DEFAULT 120, |
| 22 | updated_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 23 | created_at timestamptz NOT NULL DEFAULT now(), |
| 24 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 25 | |
| 26 | CONSTRAINT actions_site_policy_caps_nonnegative CHECK ( |
| 27 | max_repo_queued_runs >= 0 |
| 28 | AND max_repo_concurrent_jobs >= 0 |
| 29 | AND max_owner_concurrent_jobs >= 0 |
| 30 | AND actor_trigger_limit_per_hour >= 0 |
| 31 | ) |
| 32 | ); |
| 33 | |
| 34 | INSERT INTO actions_site_policy (id) VALUES (true); |
| 35 | |
| 36 | CREATE TABLE actions_org_policies ( |
| 37 | org_id bigint PRIMARY KEY REFERENCES orgs(id) ON DELETE CASCADE, |
| 38 | actions_enabled actions_policy_state NOT NULL DEFAULT 'inherit', |
| 39 | require_pr_approval boolean, |
| 40 | max_repo_queued_runs integer, |
| 41 | max_repo_concurrent_jobs integer, |
| 42 | max_owner_concurrent_jobs integer, |
| 43 | actor_trigger_limit_per_hour integer, |
| 44 | updated_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 45 | created_at timestamptz NOT NULL DEFAULT now(), |
| 46 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 47 | |
| 48 | CONSTRAINT actions_org_policies_caps_nonnegative CHECK ( |
| 49 | (max_repo_queued_runs IS NULL OR max_repo_queued_runs >= 0) |
| 50 | AND (max_repo_concurrent_jobs IS NULL OR max_repo_concurrent_jobs >= 0) |
| 51 | AND (max_owner_concurrent_jobs IS NULL OR max_owner_concurrent_jobs >= 0) |
| 52 | AND (actor_trigger_limit_per_hour IS NULL OR actor_trigger_limit_per_hour >= 0) |
| 53 | ) |
| 54 | ); |
| 55 | |
| 56 | CREATE TABLE actions_repo_policies ( |
| 57 | repo_id bigint PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE, |
| 58 | actions_enabled actions_policy_state NOT NULL DEFAULT 'inherit', |
| 59 | require_pr_approval boolean, |
| 60 | max_repo_queued_runs integer, |
| 61 | max_repo_concurrent_jobs integer, |
| 62 | max_owner_concurrent_jobs integer, |
| 63 | actor_trigger_limit_per_hour integer, |
| 64 | updated_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 65 | created_at timestamptz NOT NULL DEFAULT now(), |
| 66 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 67 | |
| 68 | CONSTRAINT actions_repo_policies_caps_nonnegative CHECK ( |
| 69 | (max_repo_queued_runs IS NULL OR max_repo_queued_runs >= 0) |
| 70 | AND (max_repo_concurrent_jobs IS NULL OR max_repo_concurrent_jobs >= 0) |
| 71 | AND (max_owner_concurrent_jobs IS NULL OR max_owner_concurrent_jobs >= 0) |
| 72 | AND (actor_trigger_limit_per_hour IS NULL OR actor_trigger_limit_per_hour >= 0) |
| 73 | ) |
| 74 | ); |
| 75 | |
| 76 | CREATE TABLE workflow_run_approvals ( |
| 77 | run_id bigint PRIMARY KEY REFERENCES workflow_runs(id) ON DELETE CASCADE, |
| 78 | requested_reason text NOT NULL DEFAULT '', |
| 79 | requested_at timestamptz NOT NULL DEFAULT now(), |
| 80 | approved_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 81 | approved_at timestamptz, |
| 82 | rejected_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 83 | rejected_at timestamptz, |
| 84 | created_at timestamptz NOT NULL DEFAULT now(), |
| 85 | updated_at timestamptz NOT NULL DEFAULT now(), |
| 86 | |
| 87 | CONSTRAINT workflow_run_approvals_one_terminal_decision CHECK ( |
| 88 | NOT (approved_at IS NOT NULL AND rejected_at IS NOT NULL) |
| 89 | ), |
| 90 | CONSTRAINT workflow_run_approvals_approved_actor CHECK ( |
| 91 | (approved_at IS NULL AND approved_by_user_id IS NULL) |
| 92 | OR (approved_at IS NOT NULL AND approved_by_user_id IS NOT NULL) |
| 93 | ), |
| 94 | CONSTRAINT workflow_run_approvals_rejected_actor CHECK ( |
| 95 | (rejected_at IS NULL AND rejected_by_user_id IS NULL) |
| 96 | OR (rejected_at IS NOT NULL AND rejected_by_user_id IS NOT NULL) |
| 97 | ) |
| 98 | ); |
| 99 | |
| 100 | CREATE INDEX workflow_run_approvals_pending_idx |
| 101 | ON workflow_run_approvals (requested_at) |
| 102 | WHERE approved_at IS NULL AND rejected_at IS NULL; |
| 103 | |
| 104 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON actions_site_policy |
| 105 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 106 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON actions_org_policies |
| 107 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 108 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON actions_repo_policies |
| 109 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 110 | CREATE TRIGGER set_updated_at BEFORE UPDATE ON workflow_run_approvals |
| 111 | FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at(); |
| 112 | |
| 113 | -- +goose Down |
| 114 | DROP TABLE IF EXISTS workflow_run_approvals; |
| 115 | DROP TABLE IF EXISTS actions_repo_policies; |
| 116 | DROP TABLE IF EXISTS actions_org_policies; |
| 117 | DROP TABLE IF EXISTS actions_site_policy; |
| 118 | DROP TYPE IF EXISTS actions_policy_state; |
| 119 |