OXYGENHUB
# 76fdeedccf44 oxygenhub / crates / hub / src / db / flow.rs
48.2 KiB · Rust · last changed Insights: OxygenFlow's standard charts, for the site, an owner and a repository 18 days ago source blame history raw
1 
//! OxygenFlow runs, jobs and logs (hub spec §13.2).
2
3 
use super::{now_ms, Pool};
4 
use sqlx::FromRow;
5
6 
#[derive(Clone, Debug, FromRow)]
7 
pub struct Run {
8 
    pub id: i64,
9 
    pub repo_id: i64,
10 
    /// The run's number within its repository, from 1 (migration 0019).
11 
    #[sqlx(default)]
12 
    pub number: i64,
13 
    /// `push`, `manual` or `rerun:N` (migration 0020).
14 
    #[sqlx(default)]
15 
    pub trigger: String,
16 
    pub bookmark: String,
17 
    pub state: String,
18 
    pub heads: String,
19 
    pub pipeline_hash: String,
20 
    pub status: String,
21 
    pub error: String,
22 
    pub actor: String,
23 
    pub created_ms: i64,
24 
    pub finished_ms: Option<i64>,
25 
    /// `urgent` when expedited (oxygenhub#244), else empty: the run follows
26 
    /// its repository's, organisation's or the site's level.
27 
    #[sqlx(default)]
28 
    pub priority: String,
29 
    /// Who expedited it and why.
30 
    #[sqlx(default)]
31 
    pub priority_note: String,
32 
}
33
34 
/// The priority levels, most urgent first (oxygenhub#244).
35 
pub const PRIORITIES: [&str; 4] = ["urgent", "high", "normal", "low"];
36
37 
/// SQL for the level a run's jobs get from where they are, as a rank
38 
/// (0 urgent … 3 low): the run's own (expedited), else its repository's,
39 
/// its organisation's, the site's (`settings.flow_priority`), else normal.
40 
macro_rules! level_rank {
41 
    () => {
42 
        "CASE COALESCE(NULLIF(r.priority, ''), NULLIF(rep.flow_priority, ''), \
43 
           NULLIF((SELECT o.flow_priority FROM organisations o WHERE o.name = rep.owner), ''), \
44 
           NULLIF((SELECT s.value FROM settings s WHERE s.key = 'flow_priority'), ''), 'normal') \
45 
         WHEN 'urgent' THEN 0 WHEN 'high' THEN 1 WHEN 'low' THEN 3 ELSE 2 END"
46 
    };
47 
}
48
49 
/// SQL for a queued job's level as a rank (0 urgent … 3 low): the level
50 
/// its run gets from where it is, lowered by the job's own priority from
51 
/// its pipeline (`low`, `normal`), never raised by it; an expedited run is
52 
/// urgent whatever its jobs say (oxygenhub#244). Needs `j` (the job), `r`
53 
/// (its run) and `rep` (its repository) in the query.
54 
pub const PRIORITY_RANK: &str = concat!(
55 
    "CASE WHEN r.priority <> '' THEN ",
56 
    level_rank!(),
57 
    " WHEN j.priority = 'low' THEN 3 WHEN j.priority = 'normal' AND (",
58 
    level_rank!(),
59 
    ") < 2 THEN 2 ELSE ",
60 
    level_rank!(),
61 
    " END"
62 
);
63
64 
/// SQL for why a job stands where it does, beside [`PRIORITY_RANK`]: an
65 
/// expedited run's note, else the pipeline's word for a job it lowered.
66 
pub const PRIORITY_NOTE: &str =
67 
    "CASE WHEN r.priority <> '' THEN r.priority_note WHEN j.priority <> '' THEN 'pipeline: ' || j.priority ELSE r.priority_note END";
68
69 
/// Raise run `run_id` to urgent, saying who and why; a run already ended
70 
/// is left alone. Whether it changed.
71 
pub async fn expedite(pool: &Pool, run_id: i64, note: &str) -> sqlx::Result<bool> {
72 
    let r = sqlx::query("UPDATE flow_runs SET priority = 'urgent', priority_note = $1 WHERE id = $2 AND status IN ('queued', 'running')")
73 
        .bind(note)
74 
        .bind(run_id)
75 
        .execute(pool)
76 
        .await?;
77 
    Ok(r.rows_affected() > 0)
78 
}
79
80 
impl Run {
81 
    /// How the run page says what started it.
82 
    pub fn started_by(&self) -> String {
83 
        match self.trigger.as_str() {
84 
            "manual" => format!("started by {}", self.actor),
85 
            "schedule" => "started on schedule".to_string(),
86 
            t if t.starts_with("tag:") => format!("tag {} pushed by {}", &t[4..], self.actor),
87 
            t if t.starts_with("rerun:") => format!("re-run of #{} by {}", self.rerun_of().unwrap_or(""), self.actor),
88 
            _ => format!("pushed by {}", self.actor),
89 
        }
90 
    }
91
92 
    /// The run a rerun repeats: `N` of `rerun:N` or `rerun:N:EVENT`.
93 
    pub fn rerun_of(&self) -> Option<&str> {
94 
        let rest = self.trigger.strip_prefix("rerun:")?;
95 
        Some(rest.split(':').next().unwrap_or(rest))
96 
    }
97
98 
    /// The trigger as people read it: `rerun:N`, without the event it repeats.
99 
    pub fn trigger_label(&self) -> String {
100 
        match self.rerun_of() {
101 
            Some(n) => format!("rerun:{n}"),
102 
            None => self.trigger.clone(),
103 
        }
104 
    }
105
106 
    pub fn head_list(&self) -> Vec<&str> {
107 
        self.heads.split('+').filter(|h| !h.is_empty()).collect()
108 
    }
109
110 
    pub fn live(&self) -> bool {
111 
        matches!(self.status.as_str(), "queued" | "running")
112 
    }
113 
}
114
115 
#[derive(Clone, Debug, Default, FromRow)]
116 
pub struct Job {
117 
    pub id: i64,
118 
    pub run_id: i64,
119 
    pub name: String,
120 
    pub depth: i64,
121 
    pub depends: String,
122 
    pub spec: String,
123 
    pub sandbox: String,
124 
    pub runtime: String,
125 
    pub status: String,
126 
    pub runner: String,
127 
    pub token_hash: String,
128 
    pub started_ms: Option<i64>,
129 
    pub finished_ms: Option<i64>,
130 
    pub exit_code: Option<i64>,
131 
    pub error: String,
132 
    pub receipt: String,
133 
    pub receipt_hash: String,
134 
    pub log_count: i64,
135 
    /// Comma-separated ids of other repositories the job token may read.
136 
    pub also_read: String,
137 
    /// The receipt attestation kept in the repository (Oxygen spec §14.6).
138 
    pub receipt_attest: String,
139 
    /// Who signed it: the runner's identity name.
140 
    pub receipt_signer: String,
141 
    /// Why a signed receipt was not kept.
142 
    pub receipt_note: String,
143 
    /// Why a queued job no runner can take waits (spec §11.4), from the
144 
    /// last placement pass; empty when it is only waiting its turn.
145 
    pub waiting: String,
146 
    /// What it took, as its runner reported (oxygenflow#41), in MiB.
147 
    pub peak_disk_mib: Option<i64>,
148 
    pub peak_memory_mib: Option<i64>,
149 
    /// The free disk it needs, learned from past runs of the same job.
150 
    pub need_disk_mib: Option<i64>,
151 
    /// Why it ran on its runner (oxygenflow#42).
152 
    pub placed: String,
153 
    /// `waiting` for a person, `approved`, or empty (oxygenflow#57).
154 
    pub approval: String,
155 
    /// Who approved or rejected it, and when.
156 
    pub approved_by: String,
157 
    pub approved_ms: Option<i64>,
158 
    /// The agent token it was decided through, if one (oxygenhub#229).
159 
    #[sqlx(default)]
160 
    pub approved_via: String,
161 
    /// The environment it deploys to, or empty (oxygenflow#59).
162 
    pub environment: String,
163 
    /// The approval attestation that released it, when sealed (#235).
164 
    pub approval_seal: String,
165 
    /// When it entered the queue (made, or run again), and when it became
166 
    /// ready to run: set at its claim (migration 0080, oxygenhub#257).
167 
    #[sqlx(default)]
168 
    pub queued_ms: Option<i64>,
169 
    #[sqlx(default)]
170 
    pub ready_ms: Option<i64>,
171 
}
172
173 
impl Job {
174 
    pub fn depends_on(&self) -> Vec<String> {
175 
        serde_json::from_str(&self.depends).unwrap_or_default()
176 
    }
177
178 
    pub fn done(&self) -> bool {
179 
        matches!(self.status.as_str(), "passed" | "failed" | "skipped" | "error" | "cancelled")
180 
    }
181 
}
182
183 
#[derive(Clone, Debug, FromRow)]
184 
pub struct LogLine {
185 
    pub seq: i64,
186 
    pub t: i64,
187 
    pub stream: String,
188 
    pub text: String,
189 
}
190
191 
/// A job as the trigger creates it.
192 
pub struct NewJob {
193 
    pub name: String,
194 
    pub depth: i64,
195 
    pub depends: Vec<String>,
196 
    pub spec: String,
197 
    pub sandbox: String,
198 
    pub runtime: String,
199 
    /// The free disk it needs, from [`learned_disk_need`].
200 
    pub need_disk_mib: Option<i64>,
201 
}
202
203 
/// Runs of a job whose peaks set its next need.
204 
const NEED_FROM_RUNS: i64 = 5;
205
206 
/// The free disk a new run of job `name` in repository `repo_id` needs
207 
/// (oxygenflow#41): the most its last runs took, plus a quarter; `None`
208 
/// for a job that has not run here yet.
209 
pub async fn learned_disk_need(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<i64>> {
210 
    let peak: Option<i64> = sqlx::query_scalar(
211 
        "SELECT MAX(peak_disk_mib) FROM (SELECT j.peak_disk_mib FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id \
212 
         WHERE r.repo_id = $1 AND j.name = $2 AND j.peak_disk_mib IS NOT NULL ORDER BY j.id DESC LIMIT $3)",
213 
    )
214 
    .bind(repo_id)
215 
    .bind(name)
216 
    .bind(NEED_FROM_RUNS)
217 
    .fetch_one(pool)
218 
    .await?;
219 
    Ok(peak.map(|p| p + p / 4 + 64))
220 
}
221
222 
/// Record why job `job_id` ran where it did.
223 
pub async fn set_placed(pool: &Pool, job_id: i64, placed: &str) -> sqlx::Result<()> {
224 
    sqlx::query("UPDATE flow_jobs SET placed = $1 WHERE id = $2").bind(placed).bind(job_id).execute(pool).await?;
225 
    Ok(())
226 
}
227
228 
/// Keep what a job took, as its runner reported.
229 
pub async fn set_peaks(pool: &Pool, job_id: i64, disk_mib: Option<u64>, memory_mib: Option<u64>) -> sqlx::Result<()> {
230 
    sqlx::query("UPDATE flow_jobs SET peak_disk_mib = $1, peak_memory_mib = $2 WHERE id = $3")
231 
        .bind(disk_mib.map(|v| v.min(i64::MAX as u64) as i64))
232 
        .bind(memory_mib.map(|v| v.min(i64::MAX as u64) as i64))
233 
        .bind(job_id)
234 
        .execute(pool)
235 
        .await?;
236 
    Ok(())
237 
}
238
239 
pub struct NewRun<'a> {
240 
    pub repo_id: i64,
241 
    pub bookmark: &'a str,
242 
    pub state: &'a str,
243 
    pub heads: &'a str,
244 
    pub pipeline_hash: &'a str,
245 
    pub actor: &'a str,
246 
    /// `push`, `manual` or `rerun:N`.
247 
    pub trigger: &'a str,
248 
    /// Problems when the pipeline is invalid: the run is recorded in error.
249 
    pub error: Option<&'a str>,
250 
}
251
252 
pub async fn create_run(pool: &Pool, r: NewRun<'_>, jobs: &[NewJob]) -> sqlx::Result<i64> {
253 
    let mut tx = super::begin_write(pool).await?;
254 
    let now = now_ms();
255 
    let status = if r.error.is_some() { "error" } else { "queued" };
256 
    let id: i64 = sqlx::query_scalar(
257 
        "INSERT INTO flow_runs (repo_id, number, bookmark, state, heads, pipeline_hash, status, error, actor, trigger, created_ms, finished_ms) \
258 
         VALUES ($1, (SELECT COALESCE(MAX(number), 0) + 1 FROM flow_runs WHERE repo_id = $2), $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING id",
259 
    )
260 
    .bind(r.repo_id)
261 
    .bind(r.repo_id)
262 
    .bind(r.bookmark)
263 
    .bind(r.state)
264 
    .bind(r.heads)
265 
    .bind(r.pipeline_hash)
266 
    .bind(status)
267 
    .bind(r.error.unwrap_or(""))
268 
    .bind(r.actor)
269 
    .bind(r.trigger)
270 
    .bind(now)
271 
    .bind(r.error.map(|_| now))
272 
    .fetch_one(&mut *tx)
273 
    .await?;
274 
    for j in jobs {
275 
        sqlx::query(
276 
            "INSERT INTO flow_jobs (run_id, name, depth, depends, spec, sandbox, runtime, status, need_disk_mib, approval, environment, priority, queued_ms) \
277 
             VALUES ($1, $2, $3, $4, $5, $6, $7, 'queued', $8, $9, $10, $11, $12)",
278 
        )
279 
        .bind(id)
280 
        .bind(&j.name)
281 
        .bind(j.depth)
282 
        .bind(serde_json::to_string(&j.depends).unwrap_or_else(|_| "[]".into()))
283 
        .bind(&j.spec)
284 
        .bind(&j.sandbox)
285 
        .bind(&j.runtime)
286 
        .bind(j.need_disk_mib)
287 
        .bind(if needs_approval(&j.spec) { "waiting" } else { "" })
288 
        .bind(environment_of(&j.spec))
289 
        .bind(priority_of(&j.spec))
290 
        .bind(now)
291 
        .execute(&mut *tx)
292 
        .await?;
293 
    }
294 
    tx.commit().await?;
295 
    Ok(id)
296 
}
297
298 
/// The environment a compiled job deploys to, or empty (oxygenflow#59).
299 
fn environment_of(spec: &str) -> String {
300 
    serde_json::from_str::<serde_json::Value>(spec).ok().and_then(|v| v["environment"].as_str().map(String::from)).unwrap_or_default()
301 
}
302
303 
/// A compiled job's own priority (`low`, `normal`), or empty; anything
304 
/// else is not one a pipeline may give (oxygenhub#244).
305 
fn priority_of(spec: &str) -> String {
306 
    serde_json::from_str::<serde_json::Value>(spec)
307 
        .ok()
308 
        .and_then(|v| v["priority"].as_str().map(String::from))
309 
        .filter(|p| p == "low" || p == "normal")
310 
        .unwrap_or_default()
311 
}
312
313 
/// Whether a compiled job asks for approval (oxygenflow#57).
314 
fn needs_approval(spec: &str) -> bool {
315 
    serde_json::from_str::<serde_json::Value>(spec).is_ok_and(|v| !v["approval"].is_null())
316 
}
317
318 
/// The labels a compiled job and its sandbox ask a runner for (`runs-on`).
319 
fn labels_in(spec: &str, sandbox: &str) -> Vec<String> {
320 
    let mut out: Vec<String> = [spec, sandbox]
321 
        .iter()
322 
        .filter_map(|j| serde_json::from_str::<serde_json::Value>(j).ok())
323 
        .flat_map(|v| v["runs_on"].as_array().cloned().unwrap_or_default())
324 
        .filter_map(|l| l.as_str().map(String::from))
325 
        .collect();
326 
    out.sort();
327 
    out.dedup();
328 
    out
329 
}
330
331 
impl Job {
332 
    /// The runner labels this job needs.
333 
    pub fn needs(&self) -> Vec<String> {
334 
        labels_in(&self.spec, &self.sandbox)
335 
    }
336
337 
    /// The architecture the job's sandbox asks for (OxygenFlow spec §11.3).
338 
    pub fn arch(&self) -> Option<String> {
339 
        arch_in(&self.sandbox)
340 
    }
341 
}
342
343 
/// The architecture a sandbox's JSON asks for, if any.
344 
fn arch_in(sandbox: &str) -> Option<String> {
345 
    serde_json::from_str::<serde_json::Value>(sandbox).ok()?["arch"].as_str().map(String::from)
346 
}
347
348 
/// A job waiting for or held by a runner, with where it comes from.
349 
#[derive(Clone, Debug, FromRow)]
350 
pub struct QueueEntry {
351 
    pub job_id: i64,
352 
    pub run_id: i64,
353 
    pub repo_id: i64,
354 
    pub owner: String,
355 
    pub name: String,
356 
    pub visibility: String,
357 
    pub run_number: i64,
358 
    pub job: String,
359 
    pub status: String,
360 
    /// The runner holding it, when running.
361 
    pub runner: String,
362 
    pub spec: String,
363 
    pub sandbox: String,
364 
    /// When it started running.
365 
    pub started_ms: Option<i64>,
366 
    /// Its level (0 urgent … 3 low, oxygenhub#244) and, when expedited, who
367 
    /// did it and why.
368 
    pub priority_rank: i64,
369 
    pub priority_note: String,
370 
}
371
372 
impl QueueEntry {
373 
    pub fn priority(&self) -> &'static str {
374 
        PRIORITIES.get(self.priority_rank as usize).copied().unwrap_or("normal")
375 
    }
376 
}
377
378 
impl QueueEntry {
379 
    /// The runner labels the job needs.
380 
    pub fn needs(&self) -> Vec<String> {
381 
        labels_in(&self.spec, &self.sandbox)
382 
    }
383
384 
    /// The architecture the job's sandbox asks for (OxygenFlow spec §11.3).
385 
    pub fn arch(&self) -> Option<String> {
386 
        arch_in(&self.sandbox)
387 
    }
388 
}
389
390 
/// Every job of a live run that is running or queued: running ones first,
391 
/// then queued ones in the order runners claim them (hub spec §13.2).
392 
pub async fn queue(pool: &Pool) -> sqlx::Result<Vec<QueueEntry>> {
393 
    sqlx::query_as(&format!(
394 
        "SELECT j.id AS job_id, j.run_id, r.repo_id, rep.owner, rep.name, rep.visibility, r.number AS run_number, j.name AS job, j.status, j.runner, j.spec, j.sandbox, j.started_ms, \
395 
           CAST({PRIORITY_RANK} AS BIGINT) AS priority_rank, {PRIORITY_NOTE} AS priority_note \
396 
         FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id JOIN repositories rep ON rep.id = r.repo_id \
397 
         WHERE j.status IN ('queued', 'running') AND j.approval != 'waiting' AND r.status IN ('queued', 'running') \
398 
         ORDER BY CASE j.status WHEN 'running' THEN 0 ELSE 1 END, \
399 
           CASE j.status WHEN 'running' THEN 0 ELSE {PRIORITY_RANK} END, \
400 
           CASE j.status WHEN 'running' THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY r.repo_id, j.status ORDER BY j.run_id, j.depth, j.id) \
401 
           + (SELECT COUNT(*) FROM flow_jobs x JOIN flow_runs xr ON xr.id = x.run_id WHERE x.status = 'running' AND xr.repo_id = r.repo_id) END, \
402 
           j.run_id, j.depth, j.id"
403 
    ))
404 
    .fetch_all(pool)
405 
    .await
406 
}
407
408 
/// A repository's runs, newest first, numbered below `before`.
409 
pub async fn runs_by_number(pool: &Pool, repo_id: i64, limit: i64, before: Option<i64>) -> sqlx::Result<Vec<Run>> {
410 
    sqlx::query_as("SELECT * FROM flow_runs WHERE repo_id = $1 AND number < $2 ORDER BY number DESC LIMIT $3")
411 
        .bind(repo_id)
412 
        .bind(before.unwrap_or(i64::MAX))
413 
        .bind(limit)
414 
        .fetch_all(pool)
415 
        .await
416 
}
417
418 
/// The latest run on this bookmark of this repository, for what a push
419 
/// changed since (a job's scope).
420 
/// The run a push to `bookmark` is scoped against: the newest that was not
421 
/// cancelled, nor a push run still going (a new push supersedes it), so
422 
/// that what a cancelled run would have checked is checked by the next.
423 
pub async fn latest_on(pool: &Pool, repo_id: i64, bookmark: &str) -> sqlx::Result<Option<Run>> {
424 
    sqlx::query_as(
425 
        "SELECT * FROM flow_runs WHERE repo_id = $1 AND bookmark = $2 AND heads != '' AND status != 'cancelled'
426 
         AND NOT (trigger = 'push' AND status IN ('queued', 'running')) ORDER BY id DESC LIMIT 1",
427 
    )
428 
    .bind(repo_id)
429 
    .bind(bookmark)
430 
    .fetch_optional(pool)
431 
    .await
432 
}
433
434 
pub async fn runs(pool: &Pool, repo_id: i64, limit: i64, before: Option<i64>) -> sqlx::Result<Vec<Run>> {
435 
    sqlx::query_as("SELECT * FROM flow_runs WHERE repo_id = $1 AND id < $2 ORDER BY id DESC LIMIT $3")
436 
        .bind(repo_id)
437 
        .bind(before.unwrap_or(i64::MAX))
438 
        .bind(limit)
439 
        .fetch_all(pool)
440 
        .await
441 
}
442
443 
/// A repository's run by its number there.
444 
pub async fn run(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<Run>> {
445 
    sqlx::query_as("SELECT * FROM flow_runs WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await
446 
}
447
448 
pub async fn run_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Run>> {
449 
    sqlx::query_as("SELECT * FROM flow_runs WHERE id = $1").bind(id).fetch_optional(pool).await
450 
}
451
452 
pub async fn jobs(pool: &Pool, run_id: i64) -> sqlx::Result<Vec<Job>> {
453 
    sqlx::query_as("SELECT * FROM flow_jobs WHERE run_id = $1 ORDER BY depth, name").bind(run_id).fetch_all(pool).await
454 
}
455
456 
pub async fn job(pool: &Pool, id: i64) -> sqlx::Result<Option<Job>> {
457 
    sqlx::query_as("SELECT * FROM flow_jobs WHERE id = $1").bind(id).fetch_optional(pool).await
458 
}
459
460 
/// The running job a job token belongs to, while it may still be used.
461 
pub async fn job_by_token(pool: &Pool, token_hash: &str, max_age_ms: i64) -> sqlx::Result<Option<Job>> {
462 
    sqlx::query_as("SELECT * FROM flow_jobs WHERE token_hash = $1 AND status = 'running' AND started_ms >= $2")
463 
        .bind(token_hash)
464 
        .bind(now_ms() - max_age_ms)
465 
        .fetch_optional(pool)
466 
        .await
467 
}
468
469 
/// Take the oldest queued job whose dependencies all passed and whose tier
470 
/// the runner offers, and mark it running for `runner` with a token.
471 
/// Let a job's token also read these repositories (its pinned subrepos).
472 
pub async fn set_also_read(pool: &Pool, job_id: i64, repo_ids: &[i64]) -> sqlx::Result<()> {
473 
    let list: Vec<String> = repo_ids.iter().map(|i| i.to_string()).collect();
474 
    sqlx::query("UPDATE flow_jobs SET also_read = $1 WHERE id = $2").bind(list.join(",")).bind(job_id).execute(pool).await?;
475 
    Ok(())
476 
}
477
478 
impl Job {
479 
    pub fn also_read_ids(&self) -> Vec<i64> {
480 
        self.also_read.split(',').filter_map(|x| x.trim().parse().ok()).collect()
481 
    }
482 
}
483
484 
/// What a runner can take on: free memory (MiB) and vCPUs, `None` for no
485 
/// limit, and the rootfs images it holds, `None` when it does not say.
486 
#[derive(Clone, Debug, Default)]
487 
pub struct Room {
488 
    pub memory_mib: Option<u32>,
489 
    pub cpus: Option<u32>,
490 
    pub images: Option<std::collections::HashSet<String>>,
491 
    /// The runner's architecture (`x86_64`, `riscv64`, …), empty when unknown.
492 
    pub arch: String,
493 
    /// Each image's architecture as runners have reported it (`flow_images`).
494 
    pub image_archs: std::collections::HashMap<String, String>,
495 
    /// Jobs this runner declined, not to be offered to it again (spec §11.5).
496 
    pub declined: std::collections::HashSet<i64>,
497 
    /// Images the hub holds that this runner would fetch when it lacks
498 
    /// them (oxygenflow#39); empty for a runner that does not fetch.
499 
    pub fetchable: std::collections::HashSet<String>,
500 
    /// Its free disk now, from its claim's facts (oxygenflow#40); `None`
501 
    /// when it does not say.
502 
    pub free_disk_mib: Option<u64>,
503 
}
504
505 
impl Room {
506 
    /// Whether a job needing `need` MiB of disk fits in its free disk.
507 
    pub fn disk_fits(&self, need: Option<i64>) -> bool {
508 
        match (need, self.free_disk_mib) {
509 
            (Some(n), Some(free)) => n.max(0) as u64 <= free,
510 
            _ => true,
511 
        }
512 
    }
513 
}
514
515 
impl Room {
516 
    /// Whether a job's sandbox (its stored JSON) fits: its memory and vCPUs,
517 
    /// and its rootfs image among the runner's (oxygenhub#73).
518 
    pub fn fits(&self, sandbox_json: &str) -> bool {
519 
        let v: serde_json::Value = serde_json::from_str(sandbox_json).unwrap_or_default();
520 
        let need = |k: &str| v[k].as_u64().unwrap_or(0);
521 
        let rootfs = v["rootfs"].as_str().unwrap_or("");
522 
        // The architecture: the sandbox's own ask, else the image's as read
523 
        // or learned from its passes (oxygenhub#173); one nobody has read
524 
        // or run yet matches (OxygenFlow spec §11.3).
525 
        let wanted = v["arch"].as_str().map(String::from).or_else(|| self.image_archs.get(rootfs).cloned());
526 
        let arch_ok = self.arch.is_empty() || wanted.as_deref().is_none_or(|w| w == self.arch);
527 
        arch_ok
528 
            && self.memory_mib.is_none_or(|m| need("memory_mib") <= m as u64)
529 
            && self.cpus.is_none_or(|c| need("cpus") <= c as u64)
530 
            && (rootfs.is_empty() || self.images.as_ref().is_none_or(|have| have.contains(rootfs)) || self.fetchable.contains(rootfs))
531 
    }
532 
}
533
534 
/// Remember which architecture each image is for, as a runner reports it.
535 
pub async fn record_image_archs(pool: &Pool, archs: &std::collections::BTreeMap<String, String>) -> sqlx::Result<()> {
536 
    for (hash, arch) in archs.iter().take(500) {
537 
        if hash.len() != 64 || !flow_core::arch_ok(arch) {
538 
            continue;
539 
        }
540 
        sqlx::query("INSERT INTO flow_images (hash, arch, seen_ms) VALUES ($1, $2, $3) ON CONFLICT (hash) DO UPDATE SET arch = excluded.arch, seen_ms = excluded.seen_ms")
541 
        .bind(hash.to_ascii_lowercase())
542 
        .bind(arch)
543 
        .bind(now_ms())
544 
        .execute(pool)
545 
        .await?;
546 
    }
547 
    Ok(())
548 
}
549
550 
/// Give every image nobody has read the architecture of the runners its
551 
/// jobs passed on (oxygenhub#173), for the jobs before the hub learned at
552 
/// each pass. Never overrides a known architecture.
553 
pub async fn learn_archs_from_runs(pool: &Pool) -> sqlx::Result<u64> {
554 
    let rootfs = crate::db::json_text("j.sandbox", "rootfs");
555 
    let r = sqlx::query(&format!(
556 
        "INSERT INTO flow_images (hash, arch, seen_ms)
557 
         SELECT lower({rootfs}), MIN(r.arch), $1 FROM flow_jobs j JOIN flow_runners r ON r.id = j.runner_id
558 
         WHERE j.status = 'passed' AND r.arch != '' AND length({rootfs}) = 64
559 
         GROUP BY lower({rootfs})
560 
         ON CONFLICT (hash) DO NOTHING"
561 
    ))
562 
    .bind(now_ms())
563 
    .execute(pool)
564 
    .await?;
565 
    Ok(r.rows_affected())
566 
}
567
568 
/// Every image's architecture the hub knows.
569 
pub async fn image_archs(pool: &Pool) -> sqlx::Result<std::collections::HashMap<String, String>> {
570 
    let rows: Vec<(String, String)> = sqlx::query_as("SELECT hash, arch FROM flow_images").fetch_all(pool).await?;
571 
    Ok(rows.into_iter().collect())
572 
}
573
574 
pub async fn claim(pool: &Pool, runtimes: &[String], runner: &str, token_hash: &str) -> sqlx::Result<Option<Job>> {
575 
    claim_within(pool, runtimes, runner, token_hash, Room::default(), &Default::default(), &[]).await
576 
}
577
578 
/// The first queued job a runner with these tiers, this room and this
579 
/// scope may take, now running under `runner`. Repositories take turns
580 
/// (fair share, oxygenhub#244): a job's turn is its place in its own
581 
/// repository's queue plus that repository's running jobs, lowest first,
582 
/// oldest first among equals, so one repository's burst can't hold up the
583 
/// rest. A scoped runner never sees
584 
/// another scope's jobs (oxygenhub#41).
585 
pub async fn claim_within(
586 
    pool: &Pool,
587 
    runtimes: &[String],
588 
    runner: &str,
589 
    token_hash: &str,
590 
    room: Room,
591 
    scope: &super::runners::Scope,
592 
    labels: &[String],
593 
) -> sqlx::Result<Option<Job>> {
594 
    claim_ranked(pool, runtimes, runner, token_hash, room, scope, labels, &|_| false).await
595 
}
596
597 
/// [`claim_within`], passing over a job `elsewhere` says a better runner
598 
/// should take (oxygenflow#42).
599 
#[allow(clippy::too_many_arguments)]
600 
pub async fn claim_ranked(
601 
    pool: &Pool,
602 
    runtimes: &[String],
603 
    runner: &str,
604 
    token_hash: &str,
605 
    room: Room,
606 
    scope: &super::runners::Scope,
607 
    labels: &[String],
608 
    elsewhere: &(dyn Fn(&Job) -> bool + Sync),
609 
) -> sqlx::Result<Option<Job>> {
610 
    let mut tx = super::begin_write(pool).await?;
611 
    let queued: Vec<Job> = sqlx::query_as(&format!(
612 
        "SELECT j.* FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id JOIN repositories rep ON rep.id = r.repo_id \
613 
         WHERE j.status = 'queued' AND j.approval != 'waiting' AND r.status IN ('queued', 'running') \
614 
         AND ($1 = '' OR rep.owner = $2) AND ($3 IS NULL OR r.repo_id = $4) \
615 
         AND NOT ($5 = '' AND $6 IS NULL AND rep.flow = 'own') \
616 
         ORDER BY {PRIORITY_RANK}, ROW_NUMBER() OVER (PARTITION BY r.repo_id ORDER BY j.run_id, j.depth, j.id) \
617 
           + (SELECT COUNT(*) FROM flow_jobs x JOIN flow_runs xr ON xr.id = x.run_id WHERE x.status = 'running' AND xr.repo_id = r.repo_id), \
618 
           j.run_id, j.depth, j.id LIMIT 200"
619 
    ))
620 
    .bind(&scope.owner)
621 
    .bind(&scope.owner)
622 
    .bind(scope.repo_id)
623 
    .bind(scope.repo_id)
624 
    // A repository on its own runners only (#225): the site's runners skip it.
625 
    .bind(&scope.owner)
626 
    .bind(scope.repo_id)
627 
    .fetch_all(&mut *tx)
628 
    .await?;
629 
    for j in queued {
630 
        if !runtimes.iter().any(|r| r == &j.runtime)
631 
            || !room.fits(&j.sandbox)
632 
            || !room.disk_fits(j.need_disk_mib)
633 
            || room.declined.contains(&j.id)
634 
        {
635 
            continue;
636 
        }
637 
        // Every label the job and its sandbox ask for (`runs-on`).
638 
        if !j.needs().iter().all(|l| labels.contains(l)) {
639 
            continue;
640 
        }
641 
        let deps = j.depends_on();
642 
        // When it became ready: queued, its dependencies passed, approved.
643 
        let mut ready = j.queued_ms.unwrap_or(0).max(j.approved_ms.unwrap_or(0));
644 
        if !deps.is_empty() {
645 
            let mut passed = 0i64;
646 
            for d in &deps {
647 
                let st: Option<(String, Option<i64>)> =
648 
                    sqlx::query_as("SELECT status, finished_ms FROM flow_jobs WHERE run_id = $1 AND name = $2")
649 
                        .bind(j.run_id)
650 
                        .bind(d)
651 
                        .fetch_optional(&mut *tx)
652 
                        .await?;
653 
                if let Some((status, finished)) = st {
654 
                    if status == "passed" {
655 
                        passed += 1;
656 
                        ready = ready.max(finished.unwrap_or(0));
657 
                    }
658 
                }
659 
            }
660 
            if passed != deps.len() as i64 {
661 
                continue;
662 
            }
663 
        }
664 
        if elsewhere(&j) {
665 
            continue;
666 
        }
667 
        let now = now_ms();
668 
        let done = sqlx::query(
669 
            "UPDATE flow_jobs SET status = 'running', runner = $1, token_hash = $2, started_ms = $3, waiting = '', ready_ms = $5 \
670 
             WHERE id = $4 AND status = 'queued'",
671 
        )
672 
        .bind(runner)
673 
        .bind(token_hash)
674 
        .bind(now)
675 
        .bind(j.id)
676 
        .bind(if ready > 0 { ready.min(now) } else { now })
677 
        .execute(&mut *tx)
678 
        .await?;
679 
        if done.rows_affected() == 1 {
680 
            sqlx::query("UPDATE flow_runs SET status = 'running' WHERE id = $1 AND status = 'queued'")
681 
                .bind(j.run_id)
682 
                .execute(&mut *tx)
683 
                .await?;
684 
            tx.commit().await?;
685 
            return job(pool, j.id).await;
686 
        }
687 
    }
688 
    tx.commit().await?;
689 
    Ok(None)
690 
}
691
692 
/// How long a decline keeps a job from its runner when the runner's
693 
/// profile stays the same: a reason such as memory in use passes.
694 
pub const DECLINE_HOLDS_MS: i64 = 10 * 60_000;
695
696 
/// The jobs runner `runner_id` declined with this profile, recently.
697 
pub async fn declined_by(pool: &Pool, runner_id: i64, profile: &str) -> sqlx::Result<std::collections::HashSet<i64>> {
698 
    let rows: Vec<(i64,)> = sqlx::query_as("SELECT job_id FROM flow_declines WHERE runner_id = $1 AND profile = $2 AND ms > $3")
699 
        .bind(runner_id)
700 
        .bind(profile)
701 
        .bind(now_ms() - DECLINE_HOLDS_MS)
702 
        .fetch_all(pool)
703 
        .await?;
704 
    Ok(rows.into_iter().map(|(j,)| j).collect())
705 
}
706
707 
/// A decline: one runner's reason for handing a job back.
708 
#[derive(Clone, Debug, sqlx::FromRow)]
709 
pub struct DeclineRow {
710 
    pub job_id: i64,
711 
    pub runner_id: i64,
712 
    pub runner: String,
713 
    pub reason: String,
714 
    pub ms: i64,
715 
}
716
717 
/// Runner `runner_id` hands back job `job_id`, running on it: the decline
718 
/// is kept (one per job and runner, the latest), and the job is queued
719 
/// again. Returns every decline the job has had, or `None` when the job
720 
/// was not running on that runner.
721 
pub async fn decline(
722 
    pool: &Pool,
723 
    job_id: i64,
724 
    runner_id: i64,
725 
    runner: &str,
726 
    profile: &str,
727 
    reason: &str,
728 
) -> sqlx::Result<Option<Vec<DeclineRow>>> {
729 
    let mut tx = super::begin_write(pool).await?;
730 
    let back = sqlx::query(
731 
        "UPDATE flow_jobs SET status = 'queued', runner = '', token_hash = '', started_ms = NULL, runner_id = NULL \
732 
         WHERE id = $1 AND status = 'running' AND runner_id = $2",
733 
    )
734 
    .bind(job_id)
735 
    .bind(runner_id)
736 
    .execute(&mut *tx)
737 
    .await?;
738 
    if back.rows_affected() != 1 {
739 
        tx.rollback().await?;
740 
        return Ok(None);
741 
    }
742 
    sqlx::query(
743 
        "INSERT INTO flow_declines (job_id, runner_id, runner, profile, reason, ms) VALUES ($1, $2, $3, $4, $5, $6) \
744 
         ON CONFLICT (job_id, runner_id) DO UPDATE SET runner = excluded.runner, profile = excluded.profile, reason = excluded.reason, ms = excluded.ms",
745 
    )
746 
    .bind(job_id)
747 
    .bind(runner_id)
748 
    .bind(runner)
749 
    .bind(profile)
750 
    .bind(reason)
751 
    .bind(now_ms())
752 
    .execute(&mut *tx)
753 
    .await?;
754 
    let all: Vec<DeclineRow> =
755 
        sqlx::query_as("SELECT job_id, runner_id, runner, reason, ms FROM flow_declines WHERE job_id = $1 ORDER BY ms")
756 
            .bind(job_id)
757 
            .fetch_all(&mut *tx)
758 
            .await?;
759 
    tx.commit().await?;
760 
    Ok(Some(all))
761 
}
762
763 
/// The declines of job `job_id`, oldest first.
764 
pub async fn declines_of_job(pool: &Pool, job_id: i64) -> sqlx::Result<Vec<DeclineRow>> {
765 
    sqlx::query_as("SELECT job_id, runner_id, runner, reason, ms FROM flow_declines WHERE job_id = $1 ORDER BY ms")
766 
        .bind(job_id)
767 
        .fetch_all(pool)
768 
        .await
769 
}
770
771 
/// A runner's latest declines, newest first, with where the job is.
772 
#[derive(Clone, Debug, sqlx::FromRow)]
773 
pub struct RunnerDecline {
774 
    pub job_id: i64,
775 
    pub job: String,
776 
    pub run_number: i64,
777 
    pub owner: String,
778 
    pub name: String,
779 
    pub reason: String,
780 
    pub ms: i64,
781 
}
782
783 
pub async fn declines_of_runner(pool: &Pool, runner_id: i64, limit: i64) -> sqlx::Result<Vec<RunnerDecline>> {
784 
    sqlx::query_as(
785 
        "SELECT d.job_id, j.name AS job, r.number AS run_number, rep.owner, rep.name, d.reason, d.ms \
786 
         FROM flow_declines d JOIN flow_jobs j ON j.id = d.job_id JOIN flow_runs r ON r.id = j.run_id \
787 
         JOIN repositories rep ON rep.id = r.repo_id WHERE d.runner_id = $1 ORDER BY d.ms DESC LIMIT $2",
788 
    )
789 
    .bind(runner_id)
790 
    .bind(limit)
791 
    .fetch_all(pool)
792 
    .await
793 
}
794
795 
/// Queued jobs whose dependencies have passed, in their live runs, with
796 
/// their repositories (owner, id): the ones a runner could take now.
797 
pub async fn ready_queued(pool: &Pool) -> sqlx::Result<Vec<(Job, String, i64)>> {
798 
    let queued: Vec<Job> = sqlx::query_as(
799 
        "SELECT j.* FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id \
800 
         WHERE j.status = 'queued' AND j.approval != 'waiting' AND r.status IN ('queued', 'running') ORDER BY j.run_id, j.depth, j.id LIMIT 500",
801 
    )
802 
    .fetch_all(pool)
803 
    .await?;
804 
    let mut out = Vec::new();
805 
    for j in queued {
806 
        let mut ready = true;
807 
        for d in j.depends_on() {
808 
            let st: Option<String> = sqlx::query_scalar("SELECT status FROM flow_jobs WHERE run_id = $1 AND name = $2")
809 
                .bind(j.run_id)
810 
                .bind(&d)
811 
                .fetch_optional(pool)
812 
                .await?;
813 
            if st.as_deref() != Some("passed") {
814 
                ready = false;
815 
                break;
816 
            }
817 
        }
818 
        if !ready {
819 
            continue;
820 
        }
821 
        let repo: Option<(String, i64)> =
822 
            sqlx::query_as("SELECT rep.owner, rep.id FROM flow_runs r JOIN repositories rep ON rep.id = r.repo_id WHERE r.id = $1")
823 
                .bind(j.run_id)
824 
                .fetch_optional(pool)
825 
                .await?;
826 
        if let Some((owner, id)) = repo {
827 
            out.push((j, owner, id));
828 
        }
829 
    }
830 
    Ok(out)
831 
}
832
833 
pub async fn set_waiting(pool: &Pool, job_id: i64, waiting: &str) -> sqlx::Result<()> {
834 
    sqlx::query("UPDATE flow_jobs SET waiting = $1 WHERE id = $2 AND waiting <> $3")
835 
        .bind(waiting)
836 
        .bind(job_id)
837 
        .bind(waiting)
838 
        .execute(pool)
839 
        .await?;
840 
    Ok(())
841 
}
842
843 
/// Append records; returns how many were kept (the rest exceed the cap).
844 
pub async fn append_log(pool: &Pool, job_id: i64, records: &[flow_core::api::Record], cap: i64) -> sqlx::Result<usize> {
845 
    let mut tx = super::begin_write(pool).await?;
846 
    let count: i64 = sqlx::query_scalar("SELECT log_count FROM flow_jobs WHERE id = $1").bind(job_id).fetch_one(&mut *tx).await?;
847 
    let room = (cap - count).max(0) as usize;
848 
    let keep = &records[..records.len().min(room)];
849 
    for (i, r) in keep.iter().enumerate() {
850 
        sqlx::query("INSERT INTO flow_logs (job_id, seq, t, stream, text) VALUES ($1, $2, $3, $4, $5)")
851 
            .bind(job_id)
852 
            .bind(count + i as i64 + 1)
853 
            .bind(r.t as i64)
854 
            .bind(&r.stream)
855 
            .bind(&r.text)
856 
            .execute(&mut *tx)
857 
            .await?;
858 
    }
859 
    sqlx::query("UPDATE flow_jobs SET log_count = log_count + $1 WHERE id = $2")
860 
        .bind(keep.len() as i64)
861 
        .bind(job_id)
862 
        .execute(&mut *tx)
863 
        .await?;
864 
    tx.commit().await?;
865 
    Ok(keep.len())
866 
}
867
868 
pub async fn log(pool: &Pool, job_id: i64, after: i64, limit: i64) -> sqlx::Result<Vec<LogLine>> {
869 
    sqlx::query_as("SELECT seq, t, stream, text FROM flow_logs WHERE job_id = $1 AND seq > $2 ORDER BY seq LIMIT $3")
870 
        .bind(job_id)
871 
        .bind(after)
872 
        .bind(limit)
873 
        .fetch_all(pool)
874 
        .await
875 
}
876
877 
/// Jobs running since before `ms` (the oldest first).
878 
/// Jobs still running at `now` past their own time limit (`default_s` for
879 
/// one claimed without) plus `grace_ms`.
880 
pub async fn running_past_limit(pool: &Pool, now: i64, default_s: u64, grace_ms: i64) -> sqlx::Result<Vec<Job>> {
881 
    sqlx::query_as(
882 
        "SELECT * FROM flow_jobs WHERE status = 'running' \
883 
         AND COALESCE(started_ms, 0) + (CASE WHEN timeout_s > 0 THEN timeout_s ELSE $1 END) * 1000 + $2 < $3 ORDER BY started_ms",
884 
    )
885 
    .bind(default_s as i64)
886 
    .bind(grace_ms)
887 
    .bind(now)
888 
    .fetch_all(pool)
889 
    .await
890 
}
891
892 
/// Record the limit a claimed job runs under.
893 
pub async fn set_job_timeout(pool: &Pool, job_id: i64, timeout_s: u64) -> sqlx::Result<()> {
894 
    sqlx::query("UPDATE flow_jobs SET timeout_s = $1 WHERE id = $2").bind(timeout_s as i64).bind(job_id).execute(pool).await?;
895 
    Ok(())
896 
}
897
898 
pub async fn running_since_before(pool: &Pool, ms: i64) -> sqlx::Result<Vec<Job>> {
899 
    sqlx::query_as("SELECT * FROM flow_jobs WHERE status = 'running' AND COALESCE(started_ms, 0) < $1 ORDER BY started_ms")
900 
        .bind(ms)
901 
        .fetch_all(pool)
902 
        .await
903 
}
904
905 
/// The jobs running on the runner with this id.
906 
pub async fn running_on(pool: &Pool, runner_id: i64) -> sqlx::Result<Vec<Job>> {
907 
    sqlx::query_as("SELECT * FROM flow_jobs WHERE status = 'running' AND runner_id = $1 ORDER BY id").bind(runner_id).fetch_all(pool).await
908 
}
909
910 
pub struct Outcome<'a> {
911 
    pub status: &'a str,
912 
    pub exit_code: Option<i64>,
913 
    pub error: &'a str,
914 
    pub receipt: &'a str,
915 
    pub receipt_hash: &'a str,
916 
}
917
918 
/// Record a job's end, skip the jobs that can no longer run, and settle the
919 
/// run. Returns the jobs whose status changed (for their checks).
920 
pub async fn finish(pool: &Pool, job_id: i64, o: Outcome<'_>) -> sqlx::Result<Vec<Job>> {
921 
    let mut tx = super::begin_write(pool).await?;
922 
    let now = now_ms();
923 
    let j: Job = sqlx::query_as("SELECT * FROM flow_jobs WHERE id = $1").bind(job_id).fetch_one(&mut *tx).await?;
924 
    sqlx::query("UPDATE flow_jobs SET status = $1, exit_code = $2, error = $3, receipt = $4, receipt_hash = $5, finished_ms = $6, token_hash = '' WHERE id = $7")
925 
        .bind(o.status)
926 
        .bind(o.exit_code)
927 
        .bind(o.error)
928 
        .bind(o.receipt)
929 
        .bind(o.receipt_hash)
930 
        .bind(now)
931 
        .bind(job_id)
932 
        .execute(&mut *tx)
933 
        .await?;
934 
    // A pass teaches what its image is for, when nobody has read it
935 
    // (oxygenhub#173): the architecture of the runner it passed on.
936 
    if o.status == "passed" {
937 
        let rootfs = serde_json::from_str::<serde_json::Value>(&j.sandbox)
938 
            .ok()
939 
            .and_then(|v| v["rootfs"].as_str().map(str::to_ascii_lowercase))
940 
            .unwrap_or_default();
941 
        if rootfs.len() == 64 {
942 
            sqlx::query(
943 
                "INSERT INTO flow_images (hash, arch, seen_ms) SELECT $1, r.arch, $2 FROM flow_jobs j JOIN flow_runners r ON r.id = j.runner_id \
944 
                 WHERE j.id = $3 AND r.arch != '' ON CONFLICT (hash) DO NOTHING",
945 
            )
946 
            .bind(&rootfs)
947 
            .bind(now)
948 
            .bind(job_id)
949 
            .execute(&mut *tx)
950 
            .await?;
951 
        }
952 
    }
953 
    let mut changed = vec![job_id];
954 
    // Dependents of anything that did not pass are skipped, transitively.
955 
    loop {
956 
        let all: Vec<Job> = sqlx::query_as("SELECT * FROM flow_jobs WHERE run_id = $1").bind(j.run_id).fetch_all(&mut *tx).await?;
957 
        let blocked: Vec<i64> = all
958 
            .iter()
959 
            .filter(|x| x.status == "queued")
960 
            .filter(|x| {
961 
                x.depends_on()
962 
                    .iter()
963 
                    .any(|d| all.iter().any(|y| &y.name == d && matches!(y.status.as_str(), "failed" | "skipped" | "error" | "cancelled")))
964 
            })
965 
            .map(|x| x.id)
966 
            .collect();
967 
        if blocked.is_empty() {
968 
            break;
969 
        }
970 
        for id in blocked {
971 
            sqlx::query("UPDATE flow_jobs SET status = 'skipped', finished_ms = $1 WHERE id = $2")
972 
                .bind(now)
973 
                .bind(id)
974 
                .execute(&mut *tx)
975 
                .await?;
976 
            changed.push(id);
977 
        }
978 
    }
979 
    let all: Vec<Job> = sqlx::query_as("SELECT * FROM flow_jobs WHERE run_id = $1").bind(j.run_id).fetch_all(&mut *tx).await?;
980 
    if all.iter().all(Job::done) {
981 
        let status = if all.iter().all(|x| x.status == "passed") { "passed" } else { "failed" };
982 
        sqlx::query("UPDATE flow_runs SET status = $1, finished_ms = $2 WHERE id = $3")
983 
            .bind(status)
984 
            .bind(now)
985 
            .bind(j.run_id)
986 
            .execute(&mut *tx)
987 
            .await?;
988 
    }
989 
    tx.commit().await?;
990 
    let mut out = Vec::new();
991 
    for id in changed {
992 
        if let Some(x) = job(pool, id).await? {
993 
            out.push(x);
994 
        }
995 
    }
996 
    Ok(out)
997 
}
998
999 
/// Cancel a run (oxygenflow#32): its jobs not yet done end as cancelled,
1000 
/// and so does the run. A runner learns of it when the hub next refuses
1001 
/// its log lines, and stops the job. Returns the jobs whose status changed.
1002 
pub async fn cancel_run(pool: &Pool, run_id: i64, why: &str) -> sqlx::Result<Vec<Job>> {
1003 
    let now = now_ms();
1004 
    let mut tx = pool.begin().await?;
1005 
    let ids: Vec<(i64,)> = sqlx::query_as("SELECT id FROM flow_jobs WHERE run_id = $1 AND status IN ('queued', 'running')")
1006 
        .bind(run_id)
1007 
        .fetch_all(&mut *tx)
1008 
        .await?;
1009 
    sqlx::query(
1010 
        "UPDATE flow_jobs SET status = 'cancelled', error = $1, finished_ms = $2, token_hash = ''
1011 
         WHERE run_id = $3 AND status IN ('queued', 'running')",
1012 
    )
1013 
    .bind(why)
1014 
    .bind(now)
1015 
    .bind(run_id)
1016 
    .execute(&mut *tx)
1017 
    .await?;
1018 
    sqlx::query("UPDATE flow_runs SET status = 'cancelled', finished_ms = $1 WHERE id = $2 AND status IN ('queued', 'running')")
1019 
        .bind(now)
1020 
        .bind(run_id)
1021 
        .execute(&mut *tx)
1022 
        .await?;
1023 
    tx.commit().await?;
1024 
    let mut out = Vec::new();
1025 
    for (id,) in ids {
1026 
        if let Some(x) = job(pool, id).await? {
1027 
            out.push(x);
1028 
        }
1029 
    }
1030 
    Ok(out)
1031 
}
1032
1033 
/// Run job `name` of finished run `run_id` again, with every job that
1034 
/// depends on it (oxygenflow#31): back to the queue as if new, their logs,
1035 
/// artifacts and reports gone, and the run running again. The jobs they
1036 
/// depend on keep their results. The jobs reset; none when there is no
1037 
/// such job.
1038 
pub async fn rerun_job(pool: &Pool, run_id: i64, name: &str) -> sqlx::Result<Vec<Job>> {
1039 
    let all: Vec<Job> = sqlx::query_as("SELECT * FROM flow_jobs WHERE run_id = $1").bind(run_id).fetch_all(pool).await?;
1040 
    if !all.iter().any(|j| j.name == name) {
1041 
        return Ok(Vec::new());
1042 
    }
1043 
    let mut again = vec![name.to_string()];
1044 
    loop {
1045 
        let more: Vec<String> = all
1046 
            .iter()
1047 
            .filter(|j| !again.contains(&j.name) && j.depends_on().iter().any(|d| again.contains(d)))
1048 
            .map(|j| j.name.clone())
1049 
            .collect();
1050 
        if more.is_empty() {
1051 
            break;
1052 
        }
1053 
        again.extend(more);
1054 
    }
1055 
    let ids: Vec<i64> = all.iter().filter(|j| again.contains(&j.name)).map(|j| j.id).collect();
1056 
    let mut tx = pool.begin().await?;
1057 
    for id in &ids {
1058 
        for table in ["flow_logs", "flow_artifacts", "flow_declines", "test_reports", "coverage_reports", "reports"] {
1059 
            sqlx::query(&format!("DELETE FROM {table} WHERE job_id = $1")).bind(id).execute(&mut *tx).await?;
1060 
        }
1061 
        sqlx::query(
1062 
            "UPDATE flow_jobs SET status = 'queued', runner = '', token_hash = '', started_ms = NULL, finished_ms = NULL,
1063 
             exit_code = NULL, error = '', receipt = '', receipt_hash = '', log_count = 0, also_read = '', receipt_attest = '',
1064 
             receipt_signer = '', receipt_note = '', runner_id = NULL, waiting = '', placed = '', peak_disk_mib = NULL,
1065 
             peak_memory_mib = NULL, approval = CASE WHEN approval = '' THEN '' ELSE 'waiting' END, approved_by = '',
1066 
             approved_ms = NULL, approval_seal = '', approved_via = '', queued_ms = $2, ready_ms = NULL WHERE id = $1",
1067 
        )
1068 
        .bind(id)
1069 
        .bind(now_ms())
1070 
        .execute(&mut *tx)
1071 
        .await?;
1072 
    }
1073 
    sqlx::query("UPDATE flow_runs SET status = 'running', finished_ms = NULL WHERE id = $1").bind(run_id).execute(&mut *tx).await?;
1074 
    tx.commit().await?;
1075 
    let mut out = Vec::new();
1076 
    for id in ids {
1077 
        if let Some(x) = job(pool, id).await? {
1078 
            out.push(x);
1079 
        }
1080 
    }
1081 
    Ok(out)
1082 
}
1083
1084 
/// The push runs of `bookmark` still going, older than run `number`.
1085 
pub async fn live_push_runs_before(pool: &Pool, repo_id: i64, bookmark: &str, number: i64) -> sqlx::Result<Vec<Run>> {
1086 
    sqlx::query_as(
1087 
        "SELECT * FROM flow_runs WHERE repo_id = $1 AND bookmark = $2 AND number < $3 AND trigger = 'push'
1088 
         AND status IN ('queued', 'running') ORDER BY id",
1089 
    )
1090 
    .bind(repo_id)
1091 
    .bind(bookmark)
1092 
    .bind(number)
1093 
    .fetch_all(pool)
1094 
    .await
1095 
}
1096
1097 
/// The newest run of exactly this state (heads joined with `+`, sorted).
1098 
pub async fn latest_for_heads(pool: &Pool, repo_id: i64, heads: &str) -> sqlx::Result<Option<Run>> {
1099 
    sqlx::query_as("SELECT * FROM flow_runs WHERE repo_id = $1 AND heads = $2 ORDER BY id DESC LIMIT 1")
1100 
        .bind(repo_id)
1101 
        .bind(heads)
1102 
        .fetch_optional(pool)
1103 
        .await
1104 
}
1105
1106 
/// Record what became of job `job_id`'s signed receipt.
1107 
pub async fn set_receipt(pool: &Pool, job_id: i64, attest: &str, signer: &str, note: &str) -> sqlx::Result<()> {
1108 
    sqlx::query("UPDATE flow_jobs SET receipt_attest = $1, receipt_signer = $2, receipt_note = $3 WHERE id = $4")
1109 
        .bind(attest)
1110 
        .bind(signer)
1111 
        .bind(note)
1112 
        .bind(job_id)
1113 
        .execute(pool)
1114 
        .await?;
1115 
    Ok(())
1116 
}
1117
1118 
/// A job's artifact, kept as a blob of the run's repository.
1119 
#[derive(Clone, Debug, FromRow)]
1120 
pub struct Artifact {
1121 
    pub job_id: i64,
1122 
    pub name: String,
1123 
    pub path: String,
1124 
    pub blob: String,
1125 
    pub size: i64,
1126 
    /// BLAKE3 of the bytes.
1127 
    pub content: String,
1128 
    pub created_ms: i64,
1129 
}
1130
1131 
pub async fn add_artifact(pool: &Pool, a: &Artifact) -> sqlx::Result<()> {
1132 
    sqlx::query(
1133 
        "INSERT OR REPLACE INTO flow_artifacts (job_id, name, path, blob, size, content, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7)",
1134 
    )
1135 
    .bind(a.job_id)
1136 
    .bind(&a.name)
1137 
    .bind(&a.path)
1138 
    .bind(&a.blob)
1139 
    .bind(a.size)
1140 
    .bind(&a.content)
1141 
    .bind(a.created_ms)
1142 
    .execute(pool)
1143 
    .await?;
1144 
    Ok(())
1145 
}
1146
1147 
/// Every artifact blob a repository's runs keep, for garbage collection to
1148 
/// keep too (#126): the database is the only thing that names them.
1149 
pub async fn artifact_blobs(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<String>> {
1150 
    sqlx::query_scalar(
1151 
        "SELECT DISTINCT a.blob FROM flow_artifacts a JOIN flow_jobs j ON j.id = a.job_id JOIN flow_runs r ON r.id = j.run_id WHERE r.repo_id = $1",
1152 
    )
1153 
    .bind(repo_id)
1154 
    .fetch_all(pool)
1155 
    .await
1156 
}
1157
1158 
/// Job `job_id`'s artifacts, by name.
1159 
pub async fn artifacts(pool: &Pool, job_id: i64) -> sqlx::Result<Vec<Artifact>> {
1160 
    sqlx::query_as("SELECT * FROM flow_artifacts WHERE job_id = $1 ORDER BY name").bind(job_id).fetch_all(pool).await
1161 
}
1162
1163 
/// An artifact with the run and job that made it, for a repository's list
1164 
/// of every run's outputs (oxygenhub#254).
1165 
#[derive(Clone, Debug, FromRow)]
1166 
pub struct RepoArtifact {
1167 
    pub run_number: i64,
1168 
    pub bookmark: String,
1169 
    pub job: String,
1170 
    pub name: String,
1171 
    pub path: String,
1172 
    pub size: i64,
1173 
    pub content: String,
1174 
    pub created_ms: i64,
1175 
}
1176
1177 
/// A repository's newest artifacts, at most `limit`.
1178 
pub async fn repo_artifacts(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<RepoArtifact>> {
1179 
    sqlx::query_as(
1180 
        "SELECT r.number AS run_number, r.bookmark, j.name AS job, a.name, a.path, a.size, a.content, a.created_ms
1181 
         FROM flow_artifacts a JOIN flow_jobs j ON j.id = a.job_id JOIN flow_runs r ON r.id = j.run_id
1182 
         WHERE r.repo_id = $1 ORDER BY a.created_ms DESC, r.number DESC, j.name, a.name LIMIT $2",
1183 
    )
1184 
    .bind(repo_id)
1185 
    .bind(limit)
1186 
    .fetch_all(pool)
1187 
    .await
1188 
}
1189
1190 
/// Where an artifact with given content came from: the run and job that
1191 
/// built it, and the job's signed receipt when one was kept.
1192 
#[derive(Clone, Debug, FromRow)]
1193 
pub struct Provenance {
1194 
    pub run_number: i64,
1195 
    pub job: String,
1196 
    pub name: String,
1197 
    pub receipt_attest: String,
1198 
    pub receipt_signer: String,
1199 
}
1200
1201 
/// The build of the bytes whose BLAKE3 is `content` in repository
1202 
/// `repo_id`: a passed job, one with a kept signed receipt first, newest
1203 
/// run first.
1204 
pub async fn provenance(pool: &Pool, repo_id: i64, content: &str) -> sqlx::Result<Option<Provenance>> {
1205 
    sqlx::query_as(
1206 
        "SELECT r.number AS run_number, j.name AS job, a.name AS name, j.receipt_attest, j.receipt_signer \
1207 
         FROM flow_artifacts a JOIN flow_jobs j ON j.id = a.job_id JOIN flow_runs r ON r.id = j.run_id \
1208 
         WHERE r.repo_id = $1 AND a.content = $2 AND j.status = 'passed' \
1209 
         ORDER BY (j.receipt_attest != '') DESC, r.number DESC LIMIT 1",
1210 
    )
1211 
    .bind(repo_id)
1212 
    .bind(content)
1213 
    .fetch_optional(pool)
1214 
    .await
1215 
}
1216
1217 
/// Approve job `id` if it is still waiting for a person (oxygenflow#57);
1218 
/// whether it was.
1219 
pub async fn approve_job(pool: &Pool, id: i64, who: &str, seal: &str, via: &str) -> sqlx::Result<bool> {
1220 
    let r = sqlx::query(
1221 
        "UPDATE flow_jobs SET approval = 'approved', approved_by = $1, approved_ms = $2, approval_seal = $3, approved_via = $5 \
1222 
         WHERE id = $4 AND status = 'queued' AND approval = 'waiting'",
1223 
    )
1224 
    .bind(who)
1225 
    .bind(now_ms())
1226 
    .bind(seal)
1227 
    .bind(id)
1228 
    .bind(via)
1229 
    .execute(pool)
1230 
    .await?;
1231 
    Ok(r.rows_affected() > 0)
1232 
}
1233
1234 
/// Note who rejected job `id` (it is then finished as cancelled).
1235 
pub async fn note_rejection(pool: &Pool, id: i64, who: &str, via: &str) -> sqlx::Result<()> {
1236 
    sqlx::query("UPDATE flow_jobs SET approved_by = $1, approved_ms = $2, approved_via = $4 WHERE id = $3")
1237 
        .bind(who)
1238 
        .bind(now_ms())
1239 
        .bind(id)
1240 
        .bind(via)
1241 
        .execute(pool)
1242 
        .await?;
1243 
    Ok(())
1244 
}
1245
1246 
/// Jobs waiting for a person, in runs still going.
1247 
pub async fn waiting_for_approval(pool: &Pool) -> sqlx::Result<Vec<Job>> {
1248 
    sqlx::query_as(
1249 
        "SELECT j.* FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id \
1250 
         WHERE j.status = 'queued' AND j.approval = 'waiting' AND r.status IN ('queued', 'running')",
1251 
    )
1252 
    .fetch_all(pool)
1253 
    .await
1254 
}
1255
1256 
/// One deployment: a job for an environment that passed.
1257 
#[derive(Clone, Debug, FromRow)]
1258 
pub struct Deployment {
1259 
    pub environment: String,
1260 
    pub job: String,
1261 
    pub run_number: i64,
1262 
    pub state: String,
1263 
    pub bookmark: String,
1264 
    pub actor: String,
1265 
    pub approved_by: String,
1266 
    pub finished_ms: i64,
1267 
}
1268
1269 
/// A repository's deployments, newest first (oxygenflow#59).
1270 
pub async fn deployments(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<Deployment>> {
1271 
    sqlx::query_as(
1272 
        "SELECT j.environment, j.name AS job, r.number AS run_number, r.state, r.bookmark, r.actor, j.approved_by, \
1273 
         COALESCE(j.finished_ms, 0) AS finished_ms FROM flow_jobs j JOIN flow_runs r ON r.id = j.run_id \
1274 
         WHERE r.repo_id = $1 AND j.environment != '' AND j.status = 'passed' ORDER BY j.finished_ms DESC LIMIT $2",
1275 
    )
1276 
    .bind(repo_id)
1277 
    .bind(limit)
1278 
    .fetch_all(pool)
1279 
    .await
1280 
}