| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertWorkflowRun :one |
| 4 | INSERT INTO workflow_runs ( |
| 5 | repo_id, run_index, workflow_file, workflow_name, |
| 6 | head_sha, head_ref, event, event_payload, |
| 7 | actor_user_id, parent_run_id, concurrency_group, need_approval |
| 8 | ) VALUES ( |
| 9 | $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12 |
| 10 | ) |
| 11 | RETURNING id, repo_id, run_index, workflow_file, workflow_name, |
| 12 | head_sha, head_ref, event, event_payload, |
| 13 | actor_user_id, parent_run_id, concurrency_group, |
| 14 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 15 | started_at, completed_at, version, created_at, updated_at, trigger_event_id; |
| 16 | |
| 17 | -- name: EnqueueWorkflowRun :one |
| 18 | -- Idempotent insert: if a row with the same (repo_id, workflow_file, |
| 19 | -- trigger_event_id) already exists, returns no rows (pgx.ErrNoRows in |
| 20 | -- Go). The handler treats that as a successful no-op so worker |
| 21 | -- retries and admin replays of the same triggering event don't |
| 22 | -- duplicate runs. |
| 23 | -- |
| 24 | -- The ON CONFLICT predicate matches the partial unique index defined |
| 25 | -- in migration 0051; both must agree for postgres to infer the |
| 26 | -- target. |
| 27 | INSERT INTO workflow_runs ( |
| 28 | repo_id, run_index, workflow_file, workflow_name, |
| 29 | head_sha, head_ref, event, event_payload, |
| 30 | actor_user_id, parent_run_id, concurrency_group, need_approval, |
| 31 | trigger_event_id |
| 32 | ) VALUES ( |
| 33 | $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13 |
| 34 | ) |
| 35 | ON CONFLICT (repo_id, workflow_file, trigger_event_id) WHERE trigger_event_id <> '' |
| 36 | DO NOTHING |
| 37 | RETURNING id, repo_id, run_index, workflow_file, workflow_name, |
| 38 | head_sha, head_ref, event, event_payload, |
| 39 | actor_user_id, parent_run_id, concurrency_group, |
| 40 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 41 | started_at, completed_at, version, created_at, updated_at, trigger_event_id; |
| 42 | |
| 43 | -- name: LookupWorkflowRunByTriggerEvent :one |
| 44 | -- Companion to EnqueueWorkflowRun for the conflict path: when an |
| 45 | -- INSERT ... ON CONFLICT DO NOTHING returns no rows, the trigger |
| 46 | -- handler uses this to find the existing row so it can surface a |
| 47 | -- stable RunID. Matches the partial-unique index from migration 0051. |
| 48 | SELECT id, repo_id, run_index, workflow_file, workflow_name, |
| 49 | head_sha, head_ref, event, event_payload, |
| 50 | actor_user_id, parent_run_id, concurrency_group, |
| 51 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 52 | started_at, completed_at, version, created_at, updated_at, trigger_event_id |
| 53 | FROM workflow_runs |
| 54 | WHERE repo_id = $1 AND workflow_file = $2 AND trigger_event_id = $3; |
| 55 | |
| 56 | -- name: DeleteWorkflowRunByID :execrows |
| 57 | -- Cascades to workflow_jobs → workflow_steps → workflow_step_log_chunks |
| 58 | -- and workflow_artifacts via the on-delete-cascade FK chain. The |
| 59 | -- S3-side artifact blobs are NOT removed here; the handler queries |
| 60 | -- the artifact object_keys first and deletes them best-effort after |
| 61 | -- the row is gone. |
| 62 | DELETE FROM workflow_runs WHERE id = $1; |
| 63 | |
| 64 | -- name: GetWorkflowRunByID :one |
| 65 | SELECT id, repo_id, run_index, workflow_file, workflow_name, |
| 66 | head_sha, head_ref, event, event_payload, |
| 67 | actor_user_id, parent_run_id, concurrency_group, |
| 68 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 69 | started_at, completed_at, version, created_at, updated_at, trigger_event_id |
| 70 | FROM workflow_runs |
| 71 | WHERE id = $1; |
| 72 | |
| 73 | -- name: ListBlockingConcurrencyRunsForUpdate :many |
| 74 | -- Older queued/running runs with the same group block the new run while they |
| 75 | -- still have at least one queued/running job that has not already received a |
| 76 | -- cancel request. cancel-in-progress releases the slot by flipping that job |
| 77 | -- flag even if the runner is still draining the old container. |
| 78 | SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 79 | r.head_sha, r.head_ref, r.event, r.event_payload, |
| 80 | r.actor_user_id, r.parent_run_id, r.concurrency_group, |
| 81 | r.status, r.conclusion, r.pinned, r.need_approval, r.approved_by_user_id, |
| 82 | r.started_at, r.completed_at, r.version, r.created_at, r.updated_at, r.trigger_event_id |
| 83 | FROM workflow_runs r |
| 84 | JOIN workflow_runs current_run ON current_run.id = sqlc.arg(run_id)::bigint |
| 85 | WHERE r.repo_id = sqlc.arg(repo_id)::bigint |
| 86 | AND r.concurrency_group = sqlc.arg(concurrency_group)::text |
| 87 | AND r.concurrency_group <> '' |
| 88 | AND r.id <> current_run.id |
| 89 | AND r.status IN ('queued', 'running') |
| 90 | AND (r.created_at, r.id) < (current_run.created_at, current_run.id) |
| 91 | AND EXISTS ( |
| 92 | SELECT 1 |
| 93 | FROM workflow_jobs j |
| 94 | WHERE j.run_id = r.id |
| 95 | AND j.status IN ('queued', 'running') |
| 96 | AND j.cancel_requested = false |
| 97 | ) |
| 98 | ORDER BY r.created_at ASC, r.id ASC |
| 99 | FOR UPDATE OF r; |
| 100 | |
| 101 | -- name: GetWorkflowRunForRepoByIndex :one |
| 102 | SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 103 | r.head_sha, r.head_ref, r.event, r.event_payload, |
| 104 | r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username, |
| 105 | r.parent_run_id, r.concurrency_group, |
| 106 | r.status, r.conclusion, r.pinned, r.need_approval, r.approved_by_user_id, |
| 107 | r.started_at, r.completed_at, r.version, r.created_at, r.updated_at, r.trigger_event_id |
| 108 | FROM workflow_runs r |
| 109 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 110 | WHERE r.repo_id = $1 AND r.run_index = $2; |
| 111 | |
| 112 | -- name: MarkWorkflowRunRunning :exec |
| 113 | UPDATE workflow_runs |
| 114 | SET status = 'running', |
| 115 | started_at = COALESCE(started_at, now()), |
| 116 | version = version + 1, |
| 117 | updated_at = now() |
| 118 | WHERE id = $1 AND status = 'queued'; |
| 119 | |
| 120 | -- name: StartWorkflowRun :one |
| 121 | UPDATE workflow_runs |
| 122 | SET status = 'running', |
| 123 | started_at = COALESCE(started_at, now()), |
| 124 | version = version + 1, |
| 125 | updated_at = now() |
| 126 | WHERE id = $1 AND status = 'queued' |
| 127 | AND (need_approval = false OR approved_by_user_id IS NOT NULL) |
| 128 | RETURNING id, repo_id, run_index, workflow_file, workflow_name, |
| 129 | head_sha, head_ref, event, event_payload, |
| 130 | actor_user_id, parent_run_id, concurrency_group, |
| 131 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 132 | started_at, completed_at, version, created_at, updated_at, trigger_event_id; |
| 133 | |
| 134 | -- name: CompleteWorkflowRun :one |
| 135 | UPDATE workflow_runs |
| 136 | SET status = 'completed', |
| 137 | conclusion = sqlc.arg(conclusion)::check_conclusion, |
| 138 | started_at = COALESCE(started_at, now()), |
| 139 | completed_at = COALESCE(completed_at, now()), |
| 140 | version = version + 1, |
| 141 | updated_at = now() |
| 142 | WHERE id = $1 |
| 143 | RETURNING id, repo_id, run_index, workflow_file, workflow_name, |
| 144 | head_sha, head_ref, event, event_payload, |
| 145 | actor_user_id, parent_run_id, concurrency_group, |
| 146 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 147 | started_at, completed_at, version, created_at, updated_at, trigger_event_id; |
| 148 | |
| 149 | -- name: NextRunIndexForRepo :one |
| 150 | -- Atomic next-index emitter: take the max + 1 for this repo. Pairs |
| 151 | -- with the (repo_id, run_index) UNIQUE so concurrent inserts that |
| 152 | -- race here will catch a unique-violation and the caller retries. |
| 153 | -- Cast to bigint so sqlc generates int64 (the column type) rather |
| 154 | -- than int32 (the type the +1 literal would default to). |
| 155 | SELECT (COALESCE(MAX(run_index), 0) + 1)::bigint AS next_index |
| 156 | FROM workflow_runs |
| 157 | WHERE repo_id = $1; |
| 158 | |
| 159 | -- name: ListWorkflowRunsForRepo :many |
| 160 | SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 161 | r.head_sha, r.head_ref, r.event, r.status, r.conclusion, |
| 162 | r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username, |
| 163 | r.started_at, r.completed_at, r.created_at, r.updated_at |
| 164 | FROM workflow_runs r |
| 165 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 166 | WHERE r.repo_id = sqlc.arg(repo_id)::bigint |
| 167 | AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text) |
| 168 | AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text) |
| 169 | AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event) |
| 170 | AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status) |
| 171 | AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion) |
| 172 | AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext) |
| 173 | ORDER BY r.created_at DESC, r.id DESC |
| 174 | LIMIT sqlc.arg(page_limit) OFFSET sqlc.arg(page_offset); |
| 175 | |
| 176 | -- name: CountWorkflowRunsForRepo :one |
| 177 | SELECT COUNT(*)::bigint |
| 178 | FROM workflow_runs r |
| 179 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 180 | WHERE r.repo_id = sqlc.arg(repo_id)::bigint |
| 181 | AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text) |
| 182 | AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text) |
| 183 | AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event) |
| 184 | AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status) |
| 185 | AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion) |
| 186 | AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext); |
| 187 | |
| 188 | -- name: ListActiveWorkflowRunsForAdmin :many |
| 189 | SELECT id, repo_id, run_index, workflow_file, workflow_name, |
| 190 | head_sha, head_ref, event, event_payload, |
| 191 | actor_user_id, parent_run_id, concurrency_group, |
| 192 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 193 | started_at, completed_at, version, created_at, updated_at, trigger_event_id |
| 194 | FROM workflow_runs |
| 195 | WHERE status IN ('queued', 'running') |
| 196 | AND (sqlc.arg(repo_id)::bigint = 0 OR repo_id = sqlc.arg(repo_id)::bigint) |
| 197 | ORDER BY created_at ASC, id ASC |
| 198 | LIMIT sqlc.arg(limit_count)::int; |
| 199 | |
| 200 | -- name: ListWorkflowRunWorkflowsForRepo :many |
| 201 | WITH ranked AS ( |
| 202 | SELECT workflow_file, |
| 203 | workflow_name, |
| 204 | (COUNT(*) OVER (PARTITION BY workflow_file))::bigint AS run_count, |
| 205 | ROW_NUMBER() OVER (PARTITION BY workflow_file ORDER BY created_at DESC, id DESC) AS rn |
| 206 | FROM workflow_runs |
| 207 | WHERE repo_id = $1 |
| 208 | ) |
| 209 | SELECT workflow_file, |
| 210 | COALESCE(NULLIF(workflow_name, ''), workflow_file) AS workflow_name, |
| 211 | run_count |
| 212 | FROM ranked |
| 213 | WHERE rn = 1 |
| 214 | ORDER BY lower(COALESCE(NULLIF(workflow_name, ''), workflow_file)), workflow_file; |
| 215 | |
| 216 | -- name: DeleteOldWorkflowRunsForCleanup :execrows |
| 217 | DELETE FROM workflow_runs |
| 218 | WHERE pinned = false |
| 219 | AND status IN ('completed', 'cancelled') |
| 220 | AND completed_at IS NOT NULL |
| 221 | AND completed_at < $1; |
| 222 |