Skip to content
Sign in

flagon-io/g1t

public
0

g1t/services/security/src/suite_store.rs

1,473 lines56,255 bytesCodeBlame
1//! The security suite's tables in D1 (migration 0004): settings, custom
2//! patterns, secret locations and bypass requests, code scanning, the
3//! dependency graph, pull request checks, fixes and daily snapshots.
4
5use std::collections::BTreeSet;
6
7use g1t_contracts::new_id;
8use g1t_contracts::security::{AlertState, DismissReason, NewSecret, SeverityCounts};
9use g1t_contracts::security_suite::{
10 Analysis, BypassReason, BypassRequest, CodeAlert, CustomPattern, PatternSpec, RepoSecuritySettings, SecretLocation,
11 WorkspaceSecuritySettings,
12};
13use g1t_kit::now_ms;
14use serde::Deserialize;
15use worker::wasm_bindgen::JsValue;
16use worker::Result;
17
18use crate::store::{Store, now, optional};
19
20/// What a purged repository leaves in the suite's tables. `?1` its id.
21pub const PURGED: &[&str] = &[
22 "DELETE FROM secret_locations WHERE repo_id = ?1",
23 "DELETE FROM custom_patterns WHERE repo_id = ?1",
24 "DELETE FROM bypass_requests WHERE repo_id = ?1",
25 "DELETE FROM sarif_uploads WHERE repo_id = ?1",
26 "DELETE FROM analyses WHERE repo_id = ?1",
27 "DELETE FROM analysis_results WHERE repo_id = ?1",
28 "DELETE FROM code_alerts WHERE repo_id = ?1",
29 "DELETE FROM pull_checks WHERE repo_id = ?1",
30 "DELETE FROM dependencies WHERE repo_id = ?1",
31 "DELETE FROM alert_fixes WHERE repo_id = ?1",
32 "DELETE FROM snapshots WHERE repo_id = ?1",
33];
34
35fn number(value: Option<u32>) -> JsValue {
36 value.map_or(JsValue::NULL, JsValue::from)
37}
38
39#[derive(Deserialize)]
40struct NumberRow {
41 n: i64,
42}
43
44#[derive(Deserialize)]
45struct SettingsRow {
46 settings: Option<String>,
47 private: i64,
48}
49
50#[derive(Deserialize)]
51struct WorkspaceRow {
52 delegated_bypass: i64,
53 validity_checks: i64,
54}
55
56#[derive(Deserialize)]
57pub struct PatternRow {
58 pub id: String,
59 pub namespace: String,
60 pub repo_id: Option<String>,
61 pub name: String,
62 pub pattern: String,
63 pub before_text: Option<String>,
64 pub after_text: Option<String>,
65 pub test_strings: String,
66 pub state: String,
67 pub created_by: String,
68 pub created_at: String,
69 pub updated_by: String,
70 pub updated_at: String,
71 #[serde(default)]
72 pub repo_name: Option<String>,
73 #[serde(default)]
74 pub open_alerts: Option<i64>,
75}
76
77impl PatternRow {
78 pub fn spec(&self) -> PatternSpec {
79 PatternSpec {
80 id: self.id.clone(),
81 name: self.name.clone(),
82 pattern: self.pattern.clone(),
83 before: self.before_text.clone(),
84 after: self.after_text.clone(),
85 }
86 }
87
88 pub fn contract(&self) -> CustomPattern {
89 CustomPattern {
90 id: self.id.clone(),
91 scope: if self.repo_id.is_some() { "repository" } else { "workspace" }.to_owned(),
92 workspace: self.namespace.clone(),
93 repo: self.repo_name.clone(),
94 name: self.name.clone(),
95 pattern: self.pattern.clone(),
96 before: self.before_text.clone(),
97 after: self.after_text.clone(),
98 test_strings: serde_json::from_str(&self.test_strings).unwrap_or_default(),
99 state: self.state.clone(),
100 created_by: self.created_by.clone(),
101 created_at: self.created_at.clone(),
102 updated_by: self.updated_by.clone(),
103 updated_at: self.updated_at.clone(),
104 open_alerts: self.open_alerts.unwrap_or(0).max(0) as u32,
105 }
106 }
107}
108
109#[derive(Deserialize)]
110struct LocationRow {
111 path: String,
112 line: i64,
113 commit_hash: String,
114 source: String,
115 found_at: String,
116}
117
118#[derive(Deserialize)]
119pub struct RequestRow {
120 pub id: String,
121 pub repo_id: String,
122 pub namespace: String,
123 pub secret_id: String,
124 pub requester: String,
125 pub reason: String,
126 pub comment: Option<String>,
127 pub state: String,
128 pub reviewer: Option<String>,
129 pub review_comment: Option<String>,
130 pub created_at: String,
131 pub reviewed_at: Option<String>,
132 // From the secret and the repository.
133 #[serde(default)]
134 pub repo_name: Option<String>,
135 #[serde(default)]
136 pub kind: Option<String>,
137 #[serde(default)]
138 pub pattern_name: Option<String>,
139 #[serde(default)]
140 pub path: Option<String>,
141 #[serde(default)]
142 pub line: Option<i64>,
143 #[serde(default)]
144 pub preview: Option<String>,
145}
146
147impl RequestRow {
148 pub fn contract(&self) -> BypassRequest {
149 BypassRequest {
150 id: self.id.clone(),
151 repo_id: self.repo_id.clone(),
152 workspace: self.namespace.clone(),
153 repo: self.repo_name.clone().unwrap_or_default(),
154 secret_id: self.secret_id.clone(),
155 label: crate::secret_alerts::label_of(self.kind.as_deref().unwrap_or_default(), self.pattern_name.as_deref()),
156 path: self.path.clone().unwrap_or_default(),
157 line: self.line.unwrap_or(0).max(0) as u32,
158 preview: self.preview.clone().unwrap_or_default(),
159 requester: self.requester.clone(),
160 reason: BypassReason::parse(&self.reason).unwrap_or(BypassReason::WillFixLater),
161 comment: self.comment.clone(),
162 state: self.state.clone(),
163 reviewer: self.reviewer.clone(),
164 review_comment: self.review_comment.clone(),
165 created_at: self.created_at.clone(),
166 reviewed_at: self.reviewed_at.clone(),
167 }
168 }
169}
170
171const REQUEST_SELECT: &str = "SELECT b.*, r.name AS repo_name, s.kind, s.pattern_name, s.path, s.line, s.preview
172 FROM bypass_requests b LEFT JOIN repos r ON r.repo_id = b.repo_id LEFT JOIN secrets s ON s.id = b.secret_id";
173
174#[derive(Deserialize)]
175pub struct CodeRow {
176 pub id: String,
177 pub repo_id: String,
178 pub number: i64,
179 pub tool: String,
180 pub category: String,
181 pub fingerprint: String,
182 pub rule_id: String,
183 pub rule_name: Option<String>,
184 pub rule_description: Option<String>,
185 pub help: Option<String>,
186 pub help_uri: Option<String>,
187 pub tags: String,
188 pub level: String,
189 pub security_severity: Option<String>,
190 pub severity: String,
191 pub message: String,
192 pub path: Option<String>,
193 pub start_line: Option<i64>,
194 pub end_line: Option<i64>,
195 pub start_column: Option<i64>,
196 pub end_column: Option<i64>,
197 pub status: String,
198 pub first_commit: String,
199 pub last_commit: String,
200 pub created_at: String,
201 pub updated_at: String,
202 pub fixed_at: Option<String>,
203 pub dismiss_reason: Option<String>,
204 pub dismiss_comment: Option<String>,
205 pub dismissed_by: Option<String>,
206 pub dismissed_at: Option<String>,
207 pub issue: Option<i64>,
208}
209
210fn small(value: Option<i64>) -> Option<u32> {
211 value.map(|n| n.max(0) as u32)
212}
213
214impl From<CodeRow> for CodeAlert {
215 fn from(row: CodeRow) -> Self {
216 let state = AlertState::parse(&row.status).unwrap_or(AlertState::Open);
217 let dismissed = state == AlertState::Dismissed;
218 CodeAlert {
219 id: row.id,
220 number: row.number.max(0) as u32,
221 repo_id: row.repo_id,
222 tool: row.tool,
223 category: row.category,
224 rule_id: row.rule_id,
225 rule_name: row.rule_name,
226 rule_description: row.rule_description,
227 help: row.help,
228 help_uri: row.help_uri,
229 tags: serde_json::from_str(&row.tags).unwrap_or_default(),
230 level: row.level,
231 security_severity: row.security_severity,
232 severity: row.severity,
233 message: row.message,
234 path: row.path,
235 start_line: small(row.start_line),
236 end_line: small(row.end_line),
237 start_column: small(row.start_column),
238 end_column: small(row.end_column),
239 state,
240 fingerprint: row.fingerprint,
241 first_commit: row.first_commit,
242 last_commit: row.last_commit,
243 created_at: row.created_at,
244 updated_at: row.updated_at,
245 fixed_at: row.fixed_at,
246 dismissed_by: row.dismissed_by.filter(|_| dismissed),
247 dismissed_reason: row.dismiss_reason.as_deref().and_then(DismissReason::parse).filter(|_| dismissed),
248 dismissed_comment: row.dismiss_comment.filter(|_| dismissed),
249 dismissed_at: row.dismissed_at.filter(|_| dismissed),
250 issue: small(row.issue),
251 }
252 }
253}
254
255#[derive(Deserialize)]
256pub struct AnalysisRow {
257 pub id: String,
258 pub repo_id: String,
259 pub sarif_id: String,
260 pub tool: String,
261 pub tool_version: Option<String>,
262 pub category: String,
263 pub commit_sha: String,
264 pub git_ref: String,
265 pub pull: Option<i64>,
266 pub results: i64,
267 pub new_alerts: i64,
268 pub fixed_alerts: i64,
269 pub dropped: i64,
270 pub created_at: String,
271}
272
273impl From<AnalysisRow> for Analysis {
274 fn from(row: AnalysisRow) -> Self {
275 Analysis {
276 id: row.id,
277 repo_id: row.repo_id,
278 sarif_id: row.sarif_id,
279 tool: row.tool,
280 tool_version: row.tool_version,
281 category: row.category,
282 commit_sha: row.commit_sha,
283 git_ref: row.git_ref,
284 pull: small(row.pull),
285 results: row.results.max(0) as u32,
286 new_alerts: row.new_alerts.max(0) as u32,
287 fixed_alerts: row.fixed_alerts.max(0) as u32,
288 dropped: row.dropped.max(0) as u32,
289 created_at: row.created_at,
290 }
291 }
292}
293
294#[derive(Deserialize)]
295pub struct UploadRow {
296 pub id: String,
297 pub commit_sha: String,
298 pub git_ref: String,
299 pub status: String,
300 pub errors: String,
301 pub analyses: String,
302 pub created_at: String,
303}
304
305#[derive(Deserialize)]
306pub struct PullCheckRow {
307 pub commit_sha: String,
308 pub state: String,
309 pub description: String,
310 pub detail: String,
311 pub commented: String,
312}
313
314#[derive(Clone, Deserialize)]
315pub struct DependencyRow {
316 pub manifest: String,
317 pub ecosystem: String,
318 pub name: String,
319 pub version: String,
320 pub relationship: String,
321 pub development: i64,
322 pub license: Option<String>,
323}
324
325#[derive(Deserialize)]
326struct CountRow {
327 severity: String,
328 n: i64,
329}
330
331#[derive(Deserialize)]
332pub struct SnapshotRow {
333 pub day: String,
334 pub alert_type: String,
335 pub critical: i64,
336 pub high: i64,
337 pub medium: i64,
338 pub low: i64,
339 pub unknown: i64,
340}
341
342/// A new or changed result of an analysis on the default branch.
343pub struct NewCodeAlert<'a> {
344 pub tool: &'a str,
345 pub category: &'a str,
346 pub finding: &'a g1t_scan::sarif::Finding,
347 pub commit: &'a str,
348}
349
350fn counts_of(rows: Vec<CountRow>) -> SeverityCounts {
351 let mut counts = SeverityCounts::default();
352 for row in rows {
353 let n = row.n.max(0) as u32;
354 match row.severity.as_str() {
355 "critical" => counts.critical += n,
356 "high" => counts.high += n,
357 "medium" => counts.medium += n,
358 "low" => counts.low += n,
359 _ => counts.unknown += n,
360 }
361 }
362 counts
363}
364
365impl Store {
366 // --- Repository and workspace settings ----------------------------------
367
368 pub async fn set_private(&self, repo_id: &str, private: bool) -> Result<()> {
369 self.db
370 .prepare("UPDATE repos SET private = ? WHERE repo_id = ?")
371 .bind(&[i32::from(private).into(), repo_id.into()])?
372 .run()
373 .await?;
374 Ok(())
375 }
376
377 /// A repository's settings, and whether it is private.
378 pub async fn repo_settings(&self, repo_id: &str) -> Result<(RepoSecuritySettings, bool)> {
379 let row = self
380 .db
381 .prepare("SELECT settings, private FROM repos WHERE repo_id = ?")
382 .bind(&[repo_id.into()])?
383 .first::<SettingsRow>(None)
384 .await?;
385 Ok(match row {
386 Some(row) => (
387 row.settings.as_deref().and_then(|json| serde_json::from_str(json).ok()).unwrap_or_default(),
388 row.private != 0,
389 ),
390 None => (RepoSecuritySettings::default(), true),
391 })
392 }
393
394 pub async fn set_repo_settings(&self, repo_id: &str, settings: &RepoSecuritySettings) -> Result<()> {
395 self.db
396 .prepare("UPDATE repos SET settings = ? WHERE repo_id = ?")
397 .bind(&[serde_json::to_string(settings)?.into(), repo_id.into()])?
398 .run()
399 .await?;
400 Ok(())
401 }
402
403 pub async fn workspace_settings(&self, namespace: &str) -> Result<WorkspaceSecuritySettings> {
404 let row = self
405 .db
406 .prepare("SELECT delegated_bypass, validity_checks FROM workspace_settings WHERE namespace = ?")
407 .bind(&[namespace.into()])?
408 .first::<WorkspaceRow>(None)
409 .await?;
410 Ok(row.map_or_else(WorkspaceSecuritySettings::default, |row| WorkspaceSecuritySettings {
411 delegated_bypass: row.delegated_bypass != 0,
412 validity_checks: row.validity_checks != 0,
413 }))
414 }
415
416 pub async fn set_workspace_settings(&self, namespace: &str, settings: &WorkspaceSecuritySettings, by: &str) -> Result<()> {
417 self.db
418 .prepare(
419 "INSERT INTO workspace_settings (namespace, delegated_bypass, validity_checks, updated_by, updated_at)
420 VALUES (?1, ?2, ?3, ?4, ?5)
421 ON CONFLICT (namespace) DO UPDATE SET delegated_bypass = ?2, validity_checks = ?3, updated_by = ?4, updated_at = ?5",
422 )
423 .bind(&[
424 namespace.into(),
425 i32::from(settings.delegated_bypass).into(),
426 i32::from(settings.validity_checks).into(),
427 by.into(),
428 now().into(),
429 ])?
430 .run()
431 .await?;
432 Ok(())
433 }
434
435 /// Everything the suite keeps for a purged repository goes.
436 pub async fn purge_suite(&self, repo_id: &str) -> Result<()> {
437 let mut batch = Vec::with_capacity(PURGED.len());
438 for sql in PURGED {
439 batch.push(self.db.prepare(*sql).bind(&[repo_id.into()])?);
440 }
441 self.db.batch(batch).await?;
442 Ok(())
443 }
444
445 // --- Custom patterns ----------------------------------------------------
446
447 /// A repository's own patterns (with `repo_id`) and its workspace's, or
448 /// only the workspace's.
449 pub async fn patterns(&self, namespace: &str, repo_id: Option<&str>) -> Result<Vec<PatternRow>> {
450 let sql = "SELECT p.*, r.name AS repo_name,
451 (SELECT count(*) FROM secrets s WHERE s.pattern_id = p.id AND s.status IN ('open', 'blocked')) AS open_alerts
452 FROM custom_patterns p LEFT JOIN repos r ON r.repo_id = p.repo_id
453 WHERE p.namespace = ?1 AND (p.repo_id IS NULL OR p.repo_id = ?2)
454 ORDER BY p.repo_id IS NOT NULL, p.name";
455 self.db
456 .prepare(sql)
457 .bind(&[namespace.into(), optional(repo_id.or(Some("")))])?
458 .all()
459 .await?
460 .results::<PatternRow>()
461 }
462
463 pub async fn pattern(&self, id: &str) -> Result<Option<PatternRow>> {
464 self.db
465 .prepare(
466 "SELECT p.*, r.name AS repo_name, 0 AS open_alerts FROM custom_patterns p
467 LEFT JOIN repos r ON r.repo_id = p.repo_id WHERE p.id = ?",
468 )
469 .bind(&[id.into()])?
470 .first::<PatternRow>(None)
471 .await
472 }
473
474 /// Creates or changes a pattern; returns its id.
475 #[allow(clippy::too_many_arguments)]
476 pub async fn save_pattern(
477 &self,
478 id: Option<&str>,
479 namespace: &str,
480 repo_id: Option<&str>,
481 spec: &PatternSpec,
482 tests: &[String],
483 state: &str,
484 by: &str,
485 ) -> Result<String> {
486 let id = id.map_or_else(|| new_id("pat", now_ms()), str::to_owned);
487 let now = now();
488 self.db
489 .prepare(
490 "INSERT INTO custom_patterns (id, namespace, repo_id, name, pattern, before_text, after_text, test_strings,
491 state, created_by, created_at, updated_by, updated_at)
492 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?10, ?11)
493 ON CONFLICT (id) DO UPDATE SET name = ?4, pattern = ?5, before_text = ?6, after_text = ?7,
494 test_strings = ?8, state = ?9, updated_by = ?10, updated_at = ?11",
495 )
496 .bind(&[
497 id.as_str().into(),
498 namespace.into(),
499 optional(repo_id),
500 spec.name.as_str().into(),
501 spec.pattern.as_str().into(),
502 optional(spec.before.as_deref()),
503 optional(spec.after.as_deref()),
504 serde_json::to_string(tests)?.into(),
505 state.into(),
506 by.into(),
507 now.as_str().into(),
508 ])?
509 .run()
510 .await?;
511 Ok(id)
512 }
513
514 pub async fn delete_pattern(&self, id: &str) -> Result<()> {
515 self.db.prepare("DELETE FROM custom_patterns WHERE id = ?").bind(&[id.into()])?.run().await?;
516 Ok(())
517 }
518
519 /// The published patterns a repository is scanned with.
520 pub async fn published_patterns(&self, namespace: &str, repo_id: &str) -> Result<Vec<PatternSpec>> {
521 Ok(self
522 .patterns(namespace, Some(repo_id))
523 .await?
524 .into_iter()
525 .filter(|row| row.state == "published")
526 .map(|row| row.spec())
527 .collect())
528 }
529
530 /// Restarts the history scan of the repositories a published pattern
531 /// covers, so it looks for the pattern too.
532 pub async fn rescan_for_pattern(&self, namespace: &str, repo_id: Option<&str>) -> Result<()> {
533 let sql = match repo_id {
534 Some(_) => "UPDATE repos SET history = 'pending', history_cursor = NULL, history_commits = 0, history_finished_at = NULL WHERE repo_id = ?",
535 None => "UPDATE repos SET history = 'pending', history_cursor = NULL, history_commits = 0, history_finished_at = NULL WHERE namespace = ?",
536 };
537 self.db.prepare(sql).bind(&[repo_id.unwrap_or(namespace).into()])?.run().await?;
538 Ok(())
539 }
540
541 // --- Secrets: locations, patterns, bypasses, validity -------------------
542
543 /// Records where each secret was found, and which custom pattern found
544 /// it, once the secrets themselves are stored.
545 pub async fn note_found(&self, repo_id: &str, secrets: &[NewSecret], source: &str) -> Result<()> {
546 if secrets.is_empty() {
547 return Ok(());
548 }
549 let found_at = now();
550 let mut statements = Vec::new();
551 for secret in secrets {
552 statements.push(
553 self.db
554 .prepare(
555 "INSERT OR IGNORE INTO secret_locations (secret_id, repo_id, path, line, commit_hash, source, found_at)
556 SELECT id, ?1, ?2, ?3, ?4, ?5, ?6 FROM secrets WHERE repo_id = ?1 AND fingerprint = ?7",
557 )
558 .bind(&[
559 repo_id.into(),
560 secret.path.as_str().into(),
561 secret.line.into(),
562 secret.commit.as_str().into(),
563 source.into(),
564 found_at.as_str().into(),
565 secret.fingerprint.as_str().into(),
566 ])?,
567 );
568 if let Some(pattern) = &secret.pattern_id {
569 statements.push(
570 self.db
571 .prepare("UPDATE secrets SET pattern_id = ?, pattern_name = ? WHERE repo_id = ? AND fingerprint = ? AND pattern_id IS NULL")
572 .bind(&[
573 pattern.as_str().into(),
574 optional(secret.pattern_name.as_deref()),
575 repo_id.into(),
576 secret.fingerprint.as_str().into(),
577 ])?,
578 );
579 }
580 }
581 for chunk in statements.chunks(50) {
582 self.db.batch(chunk.to_vec()).await?;
583 }
584 Ok(())
585 }
586
587 pub async fn locations(&self, secret_id: &str) -> Result<Vec<SecretLocation>> {
588 Ok(self
589 .db
590 .prepare("SELECT * FROM secret_locations WHERE secret_id = ? ORDER BY found_at, path, line LIMIT 200")
591 .bind(&[secret_id.into()])?
592 .all()
593 .await?
594 .results::<LocationRow>()?
595 .into_iter()
596 .map(|row| SecretLocation {
597 path: row.path,
598 line: row.line.max(0) as u32,
599 commit: row.commit_hash,
600 source: row.source,
601 found_at: row.found_at,
602 })
603 .collect())
604 }
605
606 /// Of these fingerprints, those someone bypassed push protection for.
607 pub async fn bypassed(&self, repo_id: &str, fingerprints: &[String]) -> Result<Vec<String>> {
608 #[derive(Deserialize)]
609 struct Row {
610 fingerprint: String,
611 }
612 let mut found = Vec::new();
613 for chunk in fingerprints.chunks(90) {
614 let marks = vec!["?"; chunk.len()].join(", ");
615 let mut binds: Vec<JsValue> = vec![repo_id.into()];
616 binds.extend(chunk.iter().map(|fingerprint| JsValue::from(fingerprint.as_str())));
617 let rows = self
618 .db
619 .prepare(format!(
620 "SELECT fingerprint FROM secrets WHERE repo_id = ? AND bypassed_at IS NOT NULL AND fingerprint IN ({marks})"
621 ))
622 .bind(&binds)?
623 .all()
624 .await?
625 .results::<Row>()?;
626 found.extend(rows.into_iter().map(|row| row.fingerprint));
627 }
628 Ok(found)
629 }
630
631 /// Lets a secret through push protection: `status` is what it becomes
632 /// (`allowed` when the reason closes it, `open` for one to fix later).
633 #[allow(clippy::too_many_arguments)]
634 pub async fn bypass(
635 &self,
636 repo_id: &str,
637 id: &str,
638 reason: BypassReason,
639 comment: Option<&str>,
640 by: &str,
641 approved_by: Option<&str>,
642 ) -> Result<()> {
643 let now = now();
644 let statement = match reason.dismissal() {
645 Some(dismissal) => self
646 .db
647 .prepare(
648 "UPDATE secrets SET status = 'allowed', decided_by = ?1, reason = ?2, decided_at = ?3, dismiss_reason = ?4,
649 bypass_reason = ?5, bypass_comment = ?2, bypassed_by = ?1, bypassed_at = ?3, bypass_approved_by = ?6
650 WHERE repo_id = ?7 AND id = ?8",
651 )
652 .bind(&[
653 by.into(),
654 optional(comment),
655 now.as_str().into(),
656 dismissal.as_str().into(),
657 reason.as_str().into(),
658 optional(approved_by),
659 repo_id.into(),
660 id.into(),
661 ])?,
662 // Real, to be rotated: it stays open, and lands with the push.
663 None => self
664 .db
665 .prepare(
666 "UPDATE secrets SET status = CASE status WHEN 'blocked' THEN 'open' ELSE status END,
667 bypass_reason = ?1, bypass_comment = ?2, bypassed_by = ?3, bypassed_at = ?4, bypass_approved_by = ?5
668 WHERE repo_id = ?6 AND id = ?7",
669 )
670 .bind(&[
671 reason.as_str().into(),
672 optional(comment),
673 by.into(),
674 now.as_str().into(),
675 optional(approved_by),
676 repo_id.into(),
677 id.into(),
678 ])?,
679 };
680 statement.run().await?;
681 Ok(())
682 }
683
684 pub async fn fingerprint_of(&self, repo_id: &str, id: &str) -> Result<Option<String>> {
685 #[derive(Deserialize)]
686 struct Row {
687 fingerprint: String,
688 }
689 Ok(self
690 .db
691 .prepare("SELECT fingerprint FROM secrets WHERE repo_id = ? AND id = ?")
692 .bind(&[repo_id.into(), id.into()])?
693 .first::<Row>(None)
694 .await?
695 .map(|row| row.fingerprint))
696 }
697
698 pub async fn set_validity(&self, repo_id: &str, id: &str, validity: &str) -> Result<()> {
699 self.db
700 .prepare("UPDATE secrets SET validity = ?, validity_checked_at = ? WHERE repo_id = ? AND id = ?")
701 .bind(&[validity.into(), now().into(), repo_id.into(), id.into()])?
702 .run()
703 .await?;
704 Ok(())
705 }
706
707 /// Open secrets due a validity check: never checked, or not for a week.
708 pub async fn unchecked_secrets(&self, repo_id: &str, before: &str, limit: u32) -> Result<Vec<String>> {
709 #[derive(Deserialize)]
710 struct Row {
711 id: String,
712 }
713 Ok(self
714 .db
715 .prepare(
716 "SELECT id FROM secrets WHERE repo_id = ? AND status = 'open' AND test_value IS NULL AND kind <> 'custom_pattern'
717 AND (validity_checked_at IS NULL OR validity_checked_at < ?) AND (validity IS NULL OR validity <> 'unsupported')
718 ORDER BY found_at DESC LIMIT ?",
719 )
720 .bind(&[repo_id.into(), before.into(), limit.into()])?
721 .all()
722 .await?
723 .results::<Row>()?
724 .into_iter()
725 .map(|row| row.id)
726 .collect())
727 }
728
729 // --- Bypass requests ----------------------------------------------------
730
731 pub async fn add_request(
732 &self,
733 repo_id: &str,
734 namespace: &str,
735 secret_id: &str,
736 requester: &str,
737 reason: BypassReason,
738 comment: Option<&str>,
739 ) -> Result<String> {
740 let id = new_id("byp", now_ms());
741 self.db
742 .prepare(
743 "INSERT INTO bypass_requests (id, repo_id, namespace, secret_id, requester, reason, comment, created_at)
744 VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
745 )
746 .bind(&[
747 id.as_str().into(),
748 repo_id.into(),
749 namespace.into(),
750 secret_id.into(),
751 requester.into(),
752 reason.as_str().into(),
753 optional(comment),
754 now().into(),
755 ])?
756 .run()
757 .await?;
758 Ok(id)
759 }
760
761 pub async fn request(&self, id: &str) -> Result<Option<RequestRow>> {
762 self.db.prepare(format!("{REQUEST_SELECT} WHERE b.id = ?")).bind(&[id.into()])?.first::<RequestRow>(None).await
763 }
764
765 pub async fn pending_request(&self, secret_id: &str, requester: &str) -> Result<Option<RequestRow>> {
766 self.db
767 .prepare(format!("{REQUEST_SELECT} WHERE b.secret_id = ? AND b.requester = ? AND b.state = 'pending'"))
768 .bind(&[secret_id.into(), requester.into()])?
769 .first::<RequestRow>(None)
770 .await
771 }
772
773 pub async fn requests_for_secret(&self, secret_id: &str) -> Result<Vec<RequestRow>> {
774 self.db
775 .prepare(format!("{REQUEST_SELECT} WHERE b.secret_id = ? ORDER BY b.created_at DESC LIMIT 50"))
776 .bind(&[secret_id.into()])?
777 .all()
778 .await?
779 .results::<RequestRow>()
780 }
781
782 pub async fn requests(&self, namespace: &str, repo_id: Option<&str>, state: Option<&str>) -> Result<Vec<RequestRow>> {
783 self.db
784 .prepare(format!(
785 "{REQUEST_SELECT} WHERE b.namespace = ?1 AND (?2 IS NULL OR b.repo_id = ?2) AND (?3 IS NULL OR b.state = ?3)
786 ORDER BY b.state = 'pending' DESC, b.created_at DESC LIMIT 200"
787 ))
788 .bind(&[namespace.into(), optional(repo_id), optional(state)])?
789 .all()
790 .await?
791 .results::<RequestRow>()
792 }
793
794 pub async fn review_request(&self, id: &str, state: &str, reviewer: &str, comment: Option<&str>) -> Result<bool> {
795 let changed = self
796 .db
797 .prepare(
798 "UPDATE bypass_requests SET state = ?, reviewer = ?, review_comment = ?, reviewed_at = ?
799 WHERE id = ? AND state = 'pending' RETURNING id",
800 )
801 .bind(&[state.into(), reviewer.into(), optional(comment), now().into(), id.into()])?
802 .first::<serde_json::Value>(None)
803 .await?;
804 Ok(changed.is_some())
805 }
806
807 // --- Code scanning ------------------------------------------------------
808
809 pub async fn add_upload(&self, repo_id: &str, commit_sha: &str, git_ref: &str, by: &str) -> Result<String> {
810 let id = new_id("sar", now_ms());
811 self.db
812 .prepare(
813 "INSERT INTO sarif_uploads (id, repo_id, commit_sha, git_ref, status, created_by, created_at)
814 VALUES (?, ?, ?, ?, 'complete', ?, ?)",
815 )
816 .bind(&[id.as_str().into(), repo_id.into(), commit_sha.into(), git_ref.into(), by.into(), now().into()])?
817 .run()
818 .await?;
819 Ok(id)
820 }
821
822 pub async fn finish_upload(&self, id: &str, failed: bool, errors: &[String], analyses: &[String]) -> Result<()> {
823 self.db
824 .prepare("UPDATE sarif_uploads SET status = ?, errors = ?, analyses = ? WHERE id = ?")
825 .bind(&[
826 if failed { "failed" } else { "complete" }.into(),
827 serde_json::to_string(errors)?.into(),
828 serde_json::to_string(analyses)?.into(),
829 id.into(),
830 ])?
831 .run()
832 .await?;
833 Ok(())
834 }
835
836 pub async fn upload(&self, repo_id: &str, id: &str) -> Result<Option<UploadRow>> {
837 self.db
838 .prepare("SELECT * FROM sarif_uploads WHERE repo_id = ? AND id = ?")
839 .bind(&[repo_id.into(), id.into()])?
840 .first::<UploadRow>(None)
841 .await
842 }
843
844 #[allow(clippy::too_many_arguments)]
845 pub async fn add_analysis(
846 &self,
847 repo_id: &str,
848 sarif_id: &str,
849 run: &g1t_scan::sarif::Run,
850 commit_sha: &str,
851 git_ref: &str,
852 pull: Option<u32>,
853 new_alerts: u32,
854 fixed_alerts: u32,
855 ) -> Result<String> {
856 let id = new_id("ana", now_ms());
857 self.db
858 .prepare(
859 "INSERT INTO analyses (id, repo_id, sarif_id, tool, tool_version, category, commit_sha, git_ref, pull, results,
860 new_alerts, fixed_alerts, dropped, created_at)
861 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
862 )
863 .bind(&[
864 id.as_str().into(),
865 repo_id.into(),
866 sarif_id.into(),
867 run.tool.as_str().into(),
868 optional(run.tool_version.as_deref()),
869 run.category.as_str().into(),
870 commit_sha.into(),
871 git_ref.into(),
872 number(pull),
873 (run.findings.len() as u32).into(),
874 new_alerts.into(),
875 fixed_alerts.into(),
876 (run.dropped as u32).into(),
877 now().into(),
878 ])?
879 .run()
880 .await?;
881 // Each result, so pull requests and alerts can name them.
882 let mut statements = Vec::new();
883 for finding in &run.findings {
884 let result = crate::code_scanning::result_json(finding);
885 statements.push(
886 self.db
887 .prepare("INSERT OR IGNORE INTO analysis_results (analysis_id, repo_id, fingerprint, result) VALUES (?, ?, ?, ?)")
888 .bind(&[id.as_str().into(), repo_id.into(), finding.fingerprint.as_str().into(), result.to_string().into()])?,
889 );
890 }
891 for chunk in statements.chunks(100) {
892 self.db.batch(chunk.to_vec()).await?;
893 }
894 Ok(id)
895 }
896
897 pub async fn analyses(&self, repo_id: &str, limit: u32) -> Result<Vec<Analysis>> {
898 Ok(self
899 .db
900 .prepare("SELECT * FROM analyses WHERE repo_id = ? ORDER BY created_at DESC LIMIT ?")
901 .bind(&[repo_id.into(), limit.into()])?
902 .all()
903 .await?
904 .results::<AnalysisRow>()?
905 .into_iter()
906 .map(Analysis::from)
907 .collect())
908 }
909
910 pub async fn analyses_reporting(&self, repo_id: &str, fingerprint: &str) -> Result<Vec<Analysis>> {
911 Ok(self
912 .db
913 .prepare(
914 "SELECT a.* FROM analyses a JOIN analysis_results r ON r.analysis_id = a.id
915 WHERE r.repo_id = ? AND r.fingerprint = ? ORDER BY a.created_at DESC LIMIT 20",
916 )
917 .bind(&[repo_id.into(), fingerprint.into()])?
918 .all()
919 .await?
920 .results::<AnalysisRow>()?
921 .into_iter()
922 .map(Analysis::from)
923 .collect())
924 }
925
926 /// The fingerprints of a tool's and category's open (or dismissed)
927 /// alerts on the default branch.
928 pub async fn live_fingerprints(&self, repo_id: &str, tool: &str, category: &str) -> Result<BTreeSet<String>> {
929 #[derive(Deserialize)]
930 struct Row {
931 fingerprint: String,
932 }
933 Ok(self
934 .db
935 .prepare(
936 "SELECT fingerprint FROM code_alerts WHERE repo_id = ? AND tool = ? AND category = ? AND status IN ('open', 'dismissed')",
937 )
938 .bind(&[repo_id.into(), tool.into(), category.into()])?
939 .all()
940 .await?
941 .results::<Row>()?
942 .into_iter()
943 .map(|row| row.fingerprint)
944 .collect())
945 }
946
947 /// Every open alert's fingerprint, any tool: a pull request's result
948 /// with one of these is not its own.
949 pub async fn open_code_fingerprints(&self, repo_id: &str) -> Result<BTreeSet<String>> {
950 #[derive(Deserialize)]
951 struct Row {
952 fingerprint: String,
953 }
954 Ok(self
955 .db
956 .prepare("SELECT fingerprint FROM code_alerts WHERE repo_id = ? AND status IN ('open', 'dismissed')")
957 .bind(&[repo_id.into()])?
958 .all()
959 .await?
960 .results::<Row>()?
961 .into_iter()
962 .map(|row| row.fingerprint)
963 .collect())
964 }
965
966 /// Records what an analysis on the default branch found: new results
967 /// open alerts, results seen before refresh theirs (a fixed one opens
968 /// again; a dismissed one stays dismissed). Returns the ids of alerts
969 /// that opened, new or again.
970 pub async fn upsert_code_alerts(&self, repo_id: &str, found: &[NewCodeAlert<'_>]) -> Result<Vec<String>> {
971 let mut opened = Vec::new();
972 let now = now();
973 for item in found {
974 let finding = item.finding;
975 let location = finding.location.as_ref();
976 #[derive(Deserialize)]
977 struct Existing {
978 id: String,
979 status: String,
980 }
981 let existing = self
982 .db
983 .prepare("SELECT id, status FROM code_alerts WHERE repo_id = ? AND tool = ? AND category = ? AND fingerprint = ?")
984 .bind(&[repo_id.into(), item.tool.into(), item.category.into(), finding.fingerprint.as_str().into()])?
985 .first::<Existing>(None)
986 .await?;
987 let tags = serde_json::to_string(&finding.tags)?;
988 let security = finding.security_severity.map(|severity| severity.as_str());
989 let severity = finding.severity().as_str();
990 match existing {
991 Some(existing) => {
992 self.db
993 .prepare(
994 "UPDATE code_alerts SET status = CASE status WHEN 'fixed' THEN 'open' ELSE status END,
995 fixed_at = NULL, last_commit = ?, message = ?, level = ?, security_severity = ?, severity = ?,
996 rule_name = ?, rule_description = ?, help = ?, help_uri = ?, tags = ?, path = ?, start_line = ?,
997 end_line = ?, start_column = ?, end_column = ?, updated_at = ?
998 WHERE id = ?",
999 )
1000 .bind(&[
1001 item.commit.into(),
1002 finding.message.as_str().into(),
1003 finding.level.as_str().into(),
1004 optional(security),
1005 severity.into(),
1006 optional(finding.rule_name.as_deref()),
1007 optional(finding.rule_description.as_deref()),
1008 optional(finding.help.as_deref()),
1009 optional(finding.help_uri.as_deref()),
1010 tags.into(),
1011 optional(location.map(|l| l.path.as_str())),
1012 number(location.map(|l| l.start_line)),
1013 number(location.map(|l| l.end_line)),
1014 number(location.and_then(|l| l.start_column)),
1015 number(location.and_then(|l| l.end_column)),
1016 now.as_str().into(),
1017 existing.id.as_str().into(),
1018 ])?
1019 .run()
1020 .await?;
1021 if existing.status == "fixed" {
1022 opened.push(existing.id);
1023 }
1024 }
1025 None => {
1026 let id = new_id("cod", now_ms());
1027 // The next number; two uploads at once retry on the
1028 // unique number.
1029 for _ in 0..3 {
1030 let next = self
1031 .db
1032 .prepare("SELECT COALESCE(MAX(number), 0) + 1 AS n FROM code_alerts WHERE repo_id = ?")
1033 .bind(&[repo_id.into()])?
1034 .first::<NumberRow>(None)
1035 .await?
1036 .map_or(1, |row| row.n);
1037 let inserted = self
1038 .db
1039 .prepare(
1040 "INSERT INTO code_alerts (id, repo_id, number, tool, category, fingerprint, rule_id, rule_name,
1041 rule_description, help, help_uri, tags, level, security_severity, severity, message, path,
1042 start_line, end_line, start_column, end_column, status, first_commit, last_commit, created_at, updated_at)
1043 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'open', ?, ?, ?, ?)",
1044 )
1045 .bind(&[
1046 id.as_str().into(),
1047 repo_id.into(),
1048 JsValue::from(next as f64),
1049 item.tool.into(),
1050 item.category.into(),
1051 finding.fingerprint.as_str().into(),
1052 finding.rule_id.as_str().into(),
1053 optional(finding.rule_name.as_deref()),
1054 optional(finding.rule_description.as_deref()),
1055 optional(finding.help.as_deref()),
1056 optional(finding.help_uri.as_deref()),
1057 tags.as_str().into(),
1058 finding.level.as_str().into(),
1059 optional(security),
1060 severity.into(),
1061 finding.message.as_str().into(),
1062 optional(location.map(|l| l.path.as_str())),
1063 number(location.map(|l| l.start_line)),
1064 number(location.map(|l| l.end_line)),
1065 number(location.and_then(|l| l.start_column)),
1066 number(location.and_then(|l| l.end_column)),
1067 item.commit.into(),
1068 item.commit.into(),
1069 now.as_str().into(),
1070 now.as_str().into(),
1071 ])?
1072 .run()
1073 .await;
1074 if inserted.is_ok() {
1075 opened.push(id.clone());
1076 break;
1077 }
1078 }
1079 }
1080 }
1081 }
1082 Ok(opened)
1083 }
1084
1085 /// Marks fixed the open alerts of a tool and category whose
1086 /// fingerprints an analysis no longer reports. Returns their ids.
1087 pub async fn fix_code_alerts(&self, repo_id: &str, tool: &str, category: &str, fingerprints: &[String]) -> Result<Vec<String>> {
1088 let mut fixed = Vec::new();
1089 let now = now();
1090 for fingerprint in fingerprints {
1091 #[derive(Deserialize)]
1092 struct Row {
1093 id: String,
1094 }
1095 if let Some(row) = self
1096 .db
1097 .prepare(
1098 "UPDATE code_alerts SET status = 'fixed', fixed_at = ?, updated_at = ?
1099 WHERE repo_id = ? AND tool = ? AND category = ? AND fingerprint = ? AND status = 'open' RETURNING id",
1100 )
1101 .bind(&[now.as_str().into(), now.as_str().into(), repo_id.into(), tool.into(), category.into(), fingerprint.as_str().into()])?
1102 .first::<Row>(None)
1103 .await?
1104 {
1105 fixed.push(row.id);
1106 }
1107 }
1108 Ok(fixed)
1109 }
1110
1111 pub async fn code_alerts(&self, repo_id: &str) -> Result<Vec<CodeAlert>> {
1112 Ok(self
1113 .db
1114 .prepare(
1115 "SELECT * FROM code_alerts WHERE repo_id = ?
1116 ORDER BY CASE status WHEN 'open' THEN 0 WHEN 'dismissed' THEN 1 ELSE 2 END,
1117 CASE severity WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 ELSE 4 END,
1118 number DESC LIMIT 1000",
1119 )
1120 .bind(&[repo_id.into()])?
1121 .all()
1122 .await?
1123 .results::<CodeRow>()?
1124 .into_iter()
1125 .map(CodeAlert::from)
1126 .collect())
1127 }
1128
1129 pub async fn code_alert(&self, repo_id: &str, number: u32) -> Result<Option<CodeAlert>> {
1130 Ok(self
1131 .db
1132 .prepare("SELECT * FROM code_alerts WHERE repo_id = ? AND number = ?")
1133 .bind(&[repo_id.into(), number.into()])?
1134 .first::<CodeRow>(None)
1135 .await?
1136 .map(CodeAlert::from))
1137 }
1138
1139 pub async fn code_alert_by_id(&self, repo_id: &str, id: &str) -> Result<Option<CodeAlert>> {
1140 Ok(self
1141 .db
1142 .prepare("SELECT * FROM code_alerts WHERE repo_id = ? AND id = ?")
1143 .bind(&[repo_id.into(), id.into()])?
1144 .first::<CodeRow>(None)
1145 .await?
1146 .map(CodeAlert::from))
1147 }
1148
1149 pub async fn set_code_alert(
1150 &self,
1151 repo_id: &str,
1152 id: &str,
1153 dismissal: Option<(DismissReason, Option<&str>, &str)>,
1154 ) -> Result<()> {
1155 let now = now();
1156 match dismissal {
1157 Some((reason, comment, by)) => {
1158 self.db
1159 .prepare(
1160 "UPDATE code_alerts SET status = 'dismissed', dismiss_reason = ?, dismiss_comment = ?, dismissed_by = ?,
1161 dismissed_at = ?, updated_at = ? WHERE repo_id = ? AND id = ?",
1162 )
1163 .bind(&[reason.as_str().into(), optional(comment), by.into(), now.as_str().into(), now.as_str().into(), repo_id.into(), id.into()])?
1164 .run()
1165 .await?;
1166 }
1167 None => {
1168 self.db
1169 .prepare(
1170 "UPDATE code_alerts SET status = 'open', dismiss_reason = NULL, dismiss_comment = NULL, dismissed_by = NULL,
1171 dismissed_at = NULL, updated_at = ? WHERE repo_id = ? AND id = ?",
1172 )
1173 .bind(&[now.as_str().into(), repo_id.into(), id.into()])?
1174 .run()
1175 .await?;
1176 }
1177 }
1178 Ok(())
1179 }
1180
1181 pub async fn set_code_issue(&self, repo_id: &str, id: &str, issue: u32) -> Result<()> {
1182 self.db
1183 .prepare("UPDATE code_alerts SET issue = ? WHERE repo_id = ? AND id = ?")
1184 .bind(&[issue.into(), repo_id.into(), id.into()])?
1185 .run()
1186 .await?;
1187 Ok(())
1188 }
1189
1190 pub async fn code_counts(&self, repo_id: &str) -> Result<SeverityCounts> {
1191 let rows = self
1192 .db
1193 .prepare("SELECT severity, count(*) AS n FROM code_alerts WHERE repo_id = ? AND status = 'open' GROUP BY severity")
1194 .bind(&[repo_id.into()])?
1195 .all()
1196 .await?
1197 .results::<CountRow>()?;
1198 Ok(counts_of(rows))
1199 }
1200
1201 // --- Pull request checks ------------------------------------------------
1202
1203 pub async fn pull_check(&self, repo_id: &str, pull: u32, kind: &str) -> Result<Option<PullCheckRow>> {
1204 self.db
1205 .prepare("SELECT commit_sha, state, description, detail, commented FROM pull_checks WHERE repo_id = ? AND pull = ? AND kind = ?")
1206 .bind(&[repo_id.into(), pull.into(), kind.into()])?
1207 .first::<PullCheckRow>(None)
1208 .await
1209 }
1210
1211 #[allow(clippy::too_many_arguments)]
1212 pub async fn set_pull_check(
1213 &self,
1214 repo_id: &str,
1215 pull: u32,
1216 kind: &str,
1217 commit: &str,
1218 state: &str,
1219 description: &str,
1220 detail: &serde_json::Value,
1221 commented: &[String],
1222 ) -> Result<()> {
1223 self.db
1224 .prepare(
1225 "INSERT INTO pull_checks (repo_id, pull, kind, commit_sha, state, description, detail, commented, updated_at)
1226 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1227 ON CONFLICT (repo_id, pull, kind) DO UPDATE SET commit_sha = ?4, state = ?5, description = ?6, detail = ?7,
1228 commented = ?8, updated_at = ?9",
1229 )
1230 .bind(&[
1231 repo_id.into(),
1232 pull.into(),
1233 kind.into(),
1234 commit.into(),
1235 state.into(),
1236 description.into(),
1237 detail.to_string().into(),
1238 serde_json::to_string(commented)?.into(),
1239 now().into(),
1240 ])?
1241 .run()
1242 .await?;
1243 Ok(())
1244 }
1245
1246 // --- The dependency graph -----------------------------------------------
1247
1248 pub async fn replace_dependencies(&self, repo_id: &str, deps: &[g1t_scan::graph::Dependency]) -> Result<()> {
1249 let mut statements = vec![self.db.prepare("DELETE FROM dependencies WHERE repo_id = ?").bind(&[repo_id.into()])?];
1250 for dep in deps {
1251 statements.push(
1252 self.db
1253 .prepare(
1254 "INSERT OR IGNORE INTO dependencies (repo_id, manifest, ecosystem, name, version, relationship, development, license)
1255 VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
1256 )
1257 .bind(&[
1258 repo_id.into(),
1259 dep.manifest.as_str().into(),
1260 dep.package.ecosystem.osv().into(),
1261 dep.package.name.as_str().into(),
1262 dep.package.version.as_str().into(),
1263 dep.relationship.as_str().into(),
1264 i32::from(dep.development).into(),
1265 optional(dep.license.as_deref()),
1266 ])?,
1267 );
1268 }
1269 // D1 runs a batch as one transaction: readers never see it empty.
1270 self.db.batch(statements).await?;
1271 Ok(())
1272 }
1273
1274 /// The commit the dependencies were last read at.
1275 pub async fn deps_commit(&self, repo_id: &str) -> Result<Option<String>> {
1276 #[derive(Deserialize)]
1277 struct Row {
1278 deps_commit: Option<String>,
1279 }
1280 Ok(self
1281 .db
1282 .prepare("SELECT deps_commit FROM repos WHERE repo_id = ?")
1283 .bind(&[repo_id.into()])?
1284 .first::<Row>(None)
1285 .await?
1286 .and_then(|row| row.deps_commit))
1287 }
1288
1289 pub async fn dependencies(&self, repo_id: &str) -> Result<Vec<DependencyRow>> {
1290 self.db
1291 .prepare("SELECT * FROM dependencies WHERE repo_id = ? ORDER BY manifest, relationship <> 'direct', name, version LIMIT 20000")
1292 .bind(&[repo_id.into()])?
1293 .all()
1294 .await?
1295 .results::<DependencyRow>()
1296 }
1297
1298 // --- Fixes --------------------------------------------------------------
1299
1300 pub async fn fix_issue(&self, alert_id: &str) -> Result<Option<u32>> {
1301 Ok(self
1302 .db
1303 .prepare("SELECT issue AS n FROM alert_fixes WHERE alert_id = ?")
1304 .bind(&[alert_id.into()])?
1305 .first::<NumberRow>(None)
1306 .await?
1307 .map(|row| row.n.max(0) as u32))
1308 }
1309
1310 pub async fn add_fix(&self, repo_id: &str, alert_id: &str, issue: u32, by: &str) -> Result<()> {
1311 self.db
1312 .prepare(
1313 "INSERT INTO alert_fixes (alert_id, repo_id, issue, created_by, created_at) VALUES (?1, ?2, ?3, ?4, ?5)
1314 ON CONFLICT (alert_id) DO UPDATE SET issue = ?3, created_by = ?4, created_at = ?5",
1315 )
1316 .bind(&[alert_id.into(), repo_id.into(), issue.into(), by.into(), now().into()])?
1317 .run()
1318 .await?;
1319 Ok(())
1320 }
1321
1322 // --- Snapshots and counts -----------------------------------------------
1323
1324 /// Open secrets by severity: one that looks real and landed is
1325 /// critical; a blocked one high; a likely test value low.
1326 pub async fn secret_severity_counts(&self, repo_id: &str) -> Result<SeverityCounts> {
1327 let rows = self
1328 .db
1329 .prepare(
1330 "SELECT CASE WHEN test_value IS NOT NULL THEN 'low' WHEN status = 'blocked' THEN 'high' ELSE 'critical' END AS severity,
1331 count(*) AS n FROM secrets WHERE repo_id = ? AND status IN ('open', 'blocked') GROUP BY severity",
1332 )
1333 .bind(&[repo_id.into()])?
1334 .all()
1335 .await?
1336 .results::<CountRow>()?;
1337 Ok(counts_of(rows))
1338 }
1339
1340 pub async fn vulnerability_counts(&self, repo_id: &str) -> Result<SeverityCounts> {
1341 let rows = self
1342 .db
1343 .prepare("SELECT severity, count(*) AS n FROM vulnerabilities WHERE repo_id = ? AND status = 'open' GROUP BY severity")
1344 .bind(&[repo_id.into()])?
1345 .all()
1346 .await?
1347 .results::<CountRow>()?;
1348 Ok(counts_of(rows))
1349 }
1350
1351 /// Alerts of a type opened and closed since `since`, across `repo_ids`.
1352 pub async fn opened_and_closed(&self, alert_type: &str, repo_ids: &[String], since: &str) -> Result<(u32, u32)> {
1353 if repo_ids.is_empty() {
1354 return Ok((0, 0));
1355 }
1356 let (table, opened, closed) = match alert_type {
1357 "secret_scanning" => ("secrets", "found_at", "decided_at"),
1358 "code_scanning" => ("code_alerts", "created_at", "COALESCE(fixed_at, dismissed_at)"),
1359 _ => ("vulnerabilities", "found_at", "COALESCE(fixed_at, dismissed_at)"),
1360 };
1361 let mut totals = (0u32, 0u32);
1362 for chunk in repo_ids.chunks(80) {
1363 let marks = vec!["?"; chunk.len()].join(", ");
1364 let mut binds: Vec<JsValue> = vec![since.into(), since.into()];
1365 binds.extend(chunk.iter().map(|id| JsValue::from(id.as_str())));
1366 #[derive(Deserialize)]
1367 struct Row {
1368 opened: Option<i64>,
1369 closed: Option<i64>,
1370 }
1371 let row = self
1372 .db
1373 .prepare(format!(
1374 "SELECT sum({opened} >= ?1) AS opened, sum({closed} IS NOT NULL AND {closed} >= ?2) AS closed
1375 FROM {table} WHERE repo_id IN ({marks})"
1376 ))
1377 .bind(&binds)?
1378 .first::<Row>(None)
1379 .await?;
1380 if let Some(row) = row {
1381 totals.0 += row.opened.unwrap_or(0).max(0) as u32;
1382 totals.1 += row.closed.unwrap_or(0).max(0) as u32;
1383 }
1384 }
1385 Ok(totals)
1386 }
1387
1388 pub async fn snapshot(&self, repo_id: &str, namespace: &str, day: &str, alert_type: &str, counts: &SeverityCounts) -> Result<()> {
1389 self.db
1390 .prepare(
1391 "INSERT INTO snapshots (repo_id, namespace, day, alert_type, critical, high, medium, low, unknown)
1392 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1393 ON CONFLICT (repo_id, day, alert_type) DO UPDATE SET namespace = ?2, critical = ?5, high = ?6, medium = ?7,
1394 low = ?8, unknown = ?9",
1395 )
1396 .bind(&[
1397 repo_id.into(),
1398 namespace.into(),
1399 day.into(),
1400 alert_type.into(),
1401 counts.critical.into(),
1402 counts.high.into(),
1403 counts.medium.into(),
1404 counts.low.into(),
1405 counts.unknown.into(),
1406 ])?
1407 .run()
1408 .await?;
1409 Ok(())
1410 }
1411
1412 /// Repositories without today's snapshot.
1413 pub async fn unsnapshotted(&self, day: &str, limit: u32) -> Result<Vec<crate::store::RepoRow>> {
1414 self.db
1415 .prepare(
1416 "SELECT * FROM repos r WHERE NOT EXISTS (SELECT 1 FROM snapshots s WHERE s.repo_id = r.repo_id AND s.day = ?)
1417 ORDER BY r.repo_id LIMIT ?",
1418 )
1419 .bind(&[day.into(), limit.into()])?
1420 .all()
1421 .await?
1422 .results::<crate::store::RepoRow>()
1423 }
1424
1425 pub async fn snapshots(&self, namespace: &str, since: &str, repo_ids: &[String]) -> Result<Vec<SnapshotRow>> {
1426 let wanted: BTreeSet<&str> = repo_ids.iter().map(String::as_str).collect();
1427 #[derive(Deserialize)]
1428 struct Row {
1429 repo_id: String,
1430 #[serde(flatten)]
1431 snapshot: SnapshotRow,
1432 }
1433 Ok(self
1434 .db
1435 .prepare("SELECT * FROM snapshots WHERE namespace = ? AND day >= ? ORDER BY day")
1436 .bind(&[namespace.into(), since.into()])?
1437 .all()
1438 .await?
1439 .results::<Row>()?
1440 .into_iter()
1441 .filter(|row| wanted.contains(row.repo_id.as_str()))
1442 .map(|row| row.snapshot)
1443 .collect())
1444 }
1445
1446 /// When code scanning last reported on a repository's default branch.
1447 pub async fn last_analysis_at(&self, repo_id: &str) -> Result<Option<String>> {
1448 #[derive(Deserialize)]
1449 struct Row {
1450 at: Option<String>,
1451 }
1452 Ok(self
1453 .db
1454 .prepare("SELECT max(created_at) AS at FROM analyses WHERE repo_id = ? AND pull IS NULL")
1455 .bind(&[repo_id.into()])?
1456 .first::<Row>(None)
1457 .await?
1458 .and_then(|row| row.at))
1459 }
1460
1461 /// Published patterns covering a repository, its own and its workspace's.
1462 pub async fn pattern_count(&self, namespace: &str, repo_id: &str) -> Result<u32> {
1463 Ok(self
1464 .db
1465 .prepare(
1466 "SELECT count(*) AS n FROM custom_patterns WHERE namespace = ? AND state = 'published' AND (repo_id IS NULL OR repo_id = ?)",
1467 )
1468 .bind(&[namespace.into(), repo_id.into()])?
1469 .first::<NumberRow>(None)
1470 .await?
1471 .map_or(0, |row| row.n.max(0) as u32))
1472 }
1473}