| 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: GetWorkflowRunByID :one |
| 57 | SELECT id, repo_id, run_index, workflow_file, workflow_name, |
| 58 | head_sha, head_ref, event, event_payload, |
| 59 | actor_user_id, parent_run_id, concurrency_group, |
| 60 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 61 | started_at, completed_at, version, created_at, updated_at, trigger_event_id |
| 62 | FROM workflow_runs |
| 63 | WHERE id = $1; |
| 64 | |
| 65 | -- name: GetWorkflowRunForRepoByIndex :one |
| 66 | SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 67 | r.head_sha, r.head_ref, r.event, r.event_payload, |
| 68 | r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username, |
| 69 | r.parent_run_id, r.concurrency_group, |
| 70 | r.status, r.conclusion, r.pinned, r.need_approval, r.approved_by_user_id, |
| 71 | r.started_at, r.completed_at, r.version, r.created_at, r.updated_at, r.trigger_event_id |
| 72 | FROM workflow_runs r |
| 73 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 74 | WHERE r.repo_id = $1 AND r.run_index = $2; |
| 75 | |
| 76 | -- name: MarkWorkflowRunRunning :exec |
| 77 | UPDATE workflow_runs |
| 78 | SET status = 'running', |
| 79 | started_at = COALESCE(started_at, now()), |
| 80 | version = version + 1, |
| 81 | updated_at = now() |
| 82 | WHERE id = $1 AND status = 'queued'; |
| 83 | |
| 84 | -- name: CompleteWorkflowRun :one |
| 85 | UPDATE workflow_runs |
| 86 | SET status = 'completed', |
| 87 | conclusion = sqlc.arg(conclusion)::check_conclusion, |
| 88 | started_at = COALESCE(started_at, now()), |
| 89 | completed_at = COALESCE(completed_at, now()), |
| 90 | version = version + 1, |
| 91 | updated_at = now() |
| 92 | WHERE id = $1 |
| 93 | RETURNING id, repo_id, run_index, workflow_file, workflow_name, |
| 94 | head_sha, head_ref, event, event_payload, |
| 95 | actor_user_id, parent_run_id, concurrency_group, |
| 96 | status, conclusion, pinned, need_approval, approved_by_user_id, |
| 97 | started_at, completed_at, version, created_at, updated_at, trigger_event_id; |
| 98 | |
| 99 | -- name: NextRunIndexForRepo :one |
| 100 | -- Atomic next-index emitter: take the max + 1 for this repo. Pairs |
| 101 | -- with the (repo_id, run_index) UNIQUE so concurrent inserts that |
| 102 | -- race here will catch a unique-violation and the caller retries. |
| 103 | -- Cast to bigint so sqlc generates int64 (the column type) rather |
| 104 | -- than int32 (the type the +1 literal would default to). |
| 105 | SELECT (COALESCE(MAX(run_index), 0) + 1)::bigint AS next_index |
| 106 | FROM workflow_runs |
| 107 | WHERE repo_id = $1; |
| 108 | |
| 109 | -- name: ListWorkflowRunsForRepo :many |
| 110 | SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 111 | r.head_sha, r.head_ref, r.event, r.status, r.conclusion, |
| 112 | r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username, |
| 113 | r.started_at, r.completed_at, r.created_at, r.updated_at |
| 114 | FROM workflow_runs r |
| 115 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 116 | WHERE r.repo_id = sqlc.arg(repo_id)::bigint |
| 117 | AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text) |
| 118 | AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text) |
| 119 | AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event) |
| 120 | AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status) |
| 121 | AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion) |
| 122 | AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext) |
| 123 | ORDER BY r.created_at DESC, r.id DESC |
| 124 | LIMIT sqlc.arg(page_limit) OFFSET sqlc.arg(page_offset); |
| 125 | |
| 126 | -- name: CountWorkflowRunsForRepo :one |
| 127 | SELECT COUNT(*)::bigint |
| 128 | FROM workflow_runs r |
| 129 | LEFT JOIN users u ON u.id = r.actor_user_id |
| 130 | WHERE r.repo_id = sqlc.arg(repo_id)::bigint |
| 131 | AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text) |
| 132 | AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text) |
| 133 | AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event) |
| 134 | AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status) |
| 135 | AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion) |
| 136 | AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext); |
| 137 | |
| 138 | -- name: ListWorkflowRunWorkflowsForRepo :many |
| 139 | WITH ranked AS ( |
| 140 | SELECT workflow_file, |
| 141 | workflow_name, |
| 142 | (COUNT(*) OVER (PARTITION BY workflow_file))::bigint AS run_count, |
| 143 | ROW_NUMBER() OVER (PARTITION BY workflow_file ORDER BY created_at DESC, id DESC) AS rn |
| 144 | FROM workflow_runs |
| 145 | WHERE repo_id = $1 |
| 146 | ) |
| 147 | SELECT workflow_file, |
| 148 | COALESCE(NULLIF(workflow_name, ''), workflow_file) AS workflow_name, |
| 149 | run_count |
| 150 | FROM ranked |
| 151 | WHERE rn = 1 |
| 152 | ORDER BY lower(COALESCE(NULLIF(workflow_name, ''), workflow_file)), workflow_file; |
| 153 |