crates/hub/src/db/push_protection.rs at ca0c443c30cf · andymccall/oxygenhub

# ca0c443c30cf oxygenhub / crates / hub / src / db / push_protection.rs

12.2 KiB · Rust · last changed Secret scanning: scan existing history, remove an allowed fingerprint 16 days ago source blame history raw

//! Push protection's records (migration 0085, oxygenhub#268): fingerprints
//! a maintainer allowed, and the findings of recent refusals; and the
//! history scan's (migration 0086, oxygenhub#277): its findings, marked
//! resolved once the secret was revoked, and each repository's progress.

use super::{now_ms, Pool};
use sqlx::FromRow;

/// Refused pushes' findings kept per repository: the newest, for
/// maintainers to judge. The history scan's are all kept (one per value
/// and file).
const KEPT_FINDINGS: i64 = 200;

/// A finding's source: a refused push, or the scan of history.
pub const PUSH: &str = "push";
pub const HISTORY: &str = "history";

#[derive(Clone, Debug, FromRow)]
pub struct Bypass {
    pub id: i64,
    pub fingerprint: String,
    pub rule: String,
    pub reason: String,
    pub by_name: String,
    pub created_ms: i64,
}

#[derive(Clone, Debug, FromRow)]
pub struct FindingRow {
    pub id: i64,
    pub at_ms: i64,
    pub pusher: String,
    pub via: String,
    pub rule: String,
    pub description: String,
    pub path: String,
    pub line: i64,
    pub fingerprint: String,
    pub excerpt: String,
}

/// A finding of the history scan (#277): where it is, and whether a
/// maintainer marked it resolved.
#[derive(Clone, Debug, FromRow)]
pub struct HistoryRow {
    pub id: i64,
    pub at_ms: i64,
    pub rule: String,
    pub description: String,
    pub path: String,
    pub line: i64,
    pub fingerprint: String,
    pub excerpt: String,
    pub change_hash: String,
    pub bookmark: String,
    pub resolved_ms: Option<i64>,
    pub resolved_by: String,
    pub resolved_reason: String,
}

/// A finding to record.
pub struct NewFinding<'a> {
    pub rule: &'a str,
    pub description: &'a str,
    pub path: &'a str,
    pub line: i64,
    pub fingerprint: &'a str,
    pub excerpt: &'a str,
}

/// The fingerprints allowed in a repository.
pub async fn allowed(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar("SELECT fingerprint FROM secret_bypasses WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await
}

pub async fn bypasses(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Bypass>> {
    sqlx::query_as("SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 ORDER BY id DESC")
        .bind(repo_id)
        .fetch_all(pool)
        .await
}

/// Allow `fingerprint` with `reason`; allowing it again replaces the reason.
pub async fn allow(pool: &Pool, repo_id: i64, fingerprint: &str, rule: &str, reason: &str, by: Option<(i64, &str)>) -> sqlx::Result<()> {
    sqlx::query(
        "INSERT INTO secret_bypasses (repo_id, fingerprint, rule, reason, by_account, by_name, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) \
         ON CONFLICT (repo_id, fingerprint) DO UPDATE SET reason = excluded.reason, rule = excluded.rule, by_account = excluded.by_account, by_name = excluded.by_name, created_ms = excluded.created_ms",
    )
    .bind(repo_id)
    .bind(fingerprint)
    .bind(rule)
    .bind(reason)
    .bind(by.map(|b| b.0))
    .bind(by.map(|b| b.1).unwrap_or(""))
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(())
}

/// Record a refused push's findings, keeping the newest few hundred.
pub async fn record(pool: &Pool, repo_id: i64, pusher: &str, via: &str, findings: &[NewFinding<'_>]) -> sqlx::Result<()> {
    let now = now_ms();
    let mut tx = super::begin_write(pool).await?;
    for f in findings {
        sqlx::query(
            "INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
        )
        .bind(repo_id)
        .bind(now)
        .bind(pusher)
        .bind(via)
        .bind(f.rule)
        .bind(f.description)
        .bind(f.path)
        .bind(f.line)
        .bind(f.fingerprint)
        .bind(f.excerpt)
        .execute(&mut *tx)
        .await?;
    }
    sqlx::query(
        "DELETE FROM secret_findings WHERE repo_id = $1 AND source = 'push' \
         AND id NOT IN (SELECT id FROM secret_findings WHERE repo_id = $2 AND source = 'push' ORDER BY id DESC LIMIT $3)",
    )
    .bind(repo_id)
    .bind(repo_id)
    .bind(KEPT_FINDINGS)
    .execute(&mut *tx)
    .await?;
    tx.commit().await
}

/// The newest findings of refused pushes, those not since allowed.
pub async fn recent(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<FindingRow>> {
    sqlx::query_as(
        "SELECT id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt FROM secret_findings f \
         WHERE repo_id = $1 AND source = 'push' \
         AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
         ORDER BY id DESC LIMIT $2",
    )
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// The rule a fingerprint was found under in this repository, if it was.
pub async fn rule_of(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar("SELECT rule FROM secret_findings WHERE repo_id = $1 AND fingerprint = $2 ORDER BY id DESC LIMIT 1")
        .bind(repo_id)
        .bind(fingerprint)
        .fetch_optional(pool)
        .await
}

/// Whether pushes adding possible secrets are refused here.
pub async fn set_enabled(pool: &Pool, repo_id: i64, on: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE repositories SET push_protection = $1 WHERE id = $2").bind(on).bind(repo_id).execute(pool).await?;
    Ok(())
}

/// Remove an allowed fingerprint (#277): pushes adding that value are
/// refused again. The row removed, if there was one.
pub async fn remove_bypass(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<Bypass>> {
    let row: Option<Bypass> = sqlx::query_as(
        "SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 AND fingerprint = $2",
    )
    .bind(repo_id)
    .bind(fingerprint)
    .fetch_optional(pool)
    .await?;
    if let Some(b) = &row {
        sqlx::query("DELETE FROM secret_bypasses WHERE id = $1").bind(b.id).execute(pool).await?;
    }
    Ok(row)
}

/// A finding of the history scan to record.
pub struct NewHistoryFinding<'a> {
    pub finding: NewFinding<'a>,
    pub change_hash: String,
    pub bookmark: String,
}

/// Record the history scan's findings: one per value and file, so a value
/// found again (in a later change, or by a rescan) keeps its first row and
/// whether it was resolved. How many were new.
pub async fn record_history(pool: &Pool, repo_id: i64, findings: &[NewHistoryFinding<'_>]) -> sqlx::Result<usize> {
    let now = now_ms();
    let mut added = 0;
    for h in findings {
        let f = &h.finding;
        let r = sqlx::query(
            "INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt, source, change_hash, bookmark) \
             VALUES ($1, $2, '', '', $3, $4, $5, $6, $7, $8, 'history', $9, $10) \
             ON CONFLICT (repo_id, fingerprint, path) WHERE source = 'history' DO NOTHING",
        )
        .bind(repo_id)
        .bind(now)
        .bind(f.rule)
        .bind(f.description)
        .bind(f.path)
        .bind(f.line)
        .bind(f.fingerprint)
        .bind(f.excerpt)
        .bind(&h.change_hash)
        .bind(&h.bookmark)
        .execute(pool)
        .await?;
        added += r.rows_affected() as usize;
    }
    Ok(added)
}

const HISTORY_COLUMNS: &str =
    "id, at_ms, rule, description, path, line, fingerprint, excerpt, change_hash, bookmark, resolved_ms, resolved_by, resolved_reason";

/// The history scan's findings still open: not resolved, their value not
/// allowed; oldest first, as history has them.
pub async fn history(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
    sqlx::query_as(&format!(
        "SELECT {HISTORY_COLUMNS} FROM secret_findings f WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NULL \
         AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
         ORDER BY id LIMIT $2"
    ))
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// The history scan's findings marked resolved, newest mark first.
pub async fn resolved(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
    sqlx::query_as(&format!(
        "SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NOT NULL \
         ORDER BY resolved_ms DESC, id DESC LIMIT $2"
    ))
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// Mark history finding `id` resolved (the secret was revoked) with
/// `reason`; the finding, if it was an open one of this repository's.
pub async fn resolve(pool: &Pool, repo_id: i64, id: i64, by: &str, reason: &str) -> sqlx::Result<Option<HistoryRow>> {
    let done = sqlx::query(
        "UPDATE secret_findings SET resolved_ms = $1, resolved_by = $2, resolved_reason = $3 \
         WHERE id = $4 AND repo_id = $5 AND source = 'history' AND resolved_ms IS NULL",
    )
    .bind(now_ms())
    .bind(by)
    .bind(reason)
    .bind(id)
    .bind(repo_id)
    .execute(pool)
    .await?;
    if done.rows_affected() == 0 {
        return Ok(None);
    }
    sqlx::query_as(&format!("SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE id = $1")).bind(id).fetch_optional(pool).await
}

/// A repository's history scan (#277): the rules it ran with, the heads
/// whose history is scanned (`done_heads`), the heads a scan under way
/// works toward (`target_heads`, empty when none is) and how far it got
/// (`position` of `pending` changes, in the scan's order), totals, and a
/// maintainer's request for a scan now.
#[derive(Clone, Debug, Default, PartialEq, Eq, FromRow)]
pub struct ScanRow {
    pub repo_id: i64,
    pub rules_version: i64,
    pub done_heads: String,
    pub target_heads: String,
    pub position: i64,
    pub pending: i64,
    pub changes: i64,
    pub bytes: i64,
    pub requested_ms: Option<i64>,
    pub pass_ms: Option<i64>,
    pub finished_ms: Option<i64>,
    pub error: String,
}

const SCAN_COLUMNS: &str =
    "repo_id, rules_version, done_heads, target_heads, position, pending, changes, bytes, requested_ms, pass_ms, finished_ms, error";

pub async fn scan_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Option<ScanRow>> {
    sqlx::query_as(&format!("SELECT {SCAN_COLUMNS} FROM secret_scans WHERE repo_id = $1")).bind(repo_id).fetch_optional(pool).await
}

/// Save a repository's scan progress.
pub async fn save_scan(pool: &Pool, s: &ScanRow) -> sqlx::Result<()> {
    sqlx::query(&format!(
        "INSERT INTO secret_scans ({SCAN_COLUMNS}) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) \
         ON CONFLICT (repo_id) DO UPDATE SET rules_version = excluded.rules_version, done_heads = excluded.done_heads, \
         target_heads = excluded.target_heads, position = excluded.position, pending = excluded.pending, changes = excluded.changes, \
         bytes = excluded.bytes, requested_ms = excluded.requested_ms, pass_ms = excluded.pass_ms, finished_ms = excluded.finished_ms, \
         error = excluded.error"
    ))
    .bind(s.repo_id)
    .bind(s.rules_version)
    .bind(&s.done_heads)
    .bind(&s.target_heads)
    .bind(s.position)
    .bind(s.pending)
    .bind(s.changes)
    .bind(s.bytes)
    .bind(s.requested_ms)
    .bind(s.pass_ms)
    .bind(s.finished_ms)
    .bind(&s.error)
    .execute(pool)
    .await?;
    Ok(())
}

/// A maintainer asked for the whole history to be scanned again now:
/// what was scanned is forgotten (findings and their marks are kept) and
/// the repository goes first.
pub async fn request_scan(pool: &Pool, repo_id: i64) -> sqlx::Result<()> {
    let now = now_ms();
    sqlx::query(
        "INSERT INTO secret_scans (repo_id, requested_ms) VALUES ($1, $2) \
         ON CONFLICT (repo_id) DO UPDATE SET requested_ms = excluded.requested_ms, done_heads = '', target_heads = '', position = 0, pending = 0",
    )
    .bind(repo_id)
    .bind(now)
    .execute(pool)
    .await?;
    Ok(())
}

/// Repositories whose maintainers asked for a scan, oldest request first.
pub async fn requested(pool: &Pool) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar("SELECT repo_id FROM secret_scans WHERE requested_ms IS NOT NULL ORDER BY requested_ms").fetch_all(pool).await
}

`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet

1	//! Push protection's records (migration 0085, oxygenhub#268): fingerprints
2	//! a maintainer allowed, and the findings of recent refusals; and the
3	//! history scan's (migration 0086, oxygenhub#277): its findings, marked
4	//! resolved once the secret was revoked, and each repository's progress.
5
6	use super::{now_ms, Pool};
7	use sqlx::FromRow;
8
9	/// Refused pushes' findings kept per repository: the newest, for
10	/// maintainers to judge. The history scan's are all kept (one per value
11	/// and file).
12	const KEPT_FINDINGS: i64 = 200;
13
14	/// A finding's source: a refused push, or the scan of history.
15	pub const PUSH: &str = "push";
16	pub const HISTORY: &str = "history";
17
18	#[derive(Clone, Debug, FromRow)]
19	pub struct Bypass {
20	pub id: i64,
21	pub fingerprint: String,
22	pub rule: String,
23	pub reason: String,
24	pub by_name: String,
25	pub created_ms: i64,
26	}
27
28	#[derive(Clone, Debug, FromRow)]
29	pub struct FindingRow {
30	pub id: i64,
31	pub at_ms: i64,
32	pub pusher: String,
33	pub via: String,
34	pub rule: String,
35	pub description: String,
36	pub path: String,
37	pub line: i64,
38	pub fingerprint: String,
39	pub excerpt: String,
40	}
41
42	/// A finding of the history scan (#277): where it is, and whether a
43	/// maintainer marked it resolved.
44	#[derive(Clone, Debug, FromRow)]
45	pub struct HistoryRow {
46	pub id: i64,
47	pub at_ms: i64,
48	pub rule: String,
49	pub description: String,
50	pub path: String,
51	pub line: i64,
52	pub fingerprint: String,
53	pub excerpt: String,
54	pub change_hash: String,
55	pub bookmark: String,
56	pub resolved_ms: Option<i64>,
57	pub resolved_by: String,
58	pub resolved_reason: String,
59	}
60
61	/// A finding to record.
62	pub struct NewFinding<'a> {
63	pub rule: &'a str,
64	pub description: &'a str,
65	pub path: &'a str,
66	pub line: i64,
67	pub fingerprint: &'a str,
68	pub excerpt: &'a str,
69	}
70
71	/// The fingerprints allowed in a repository.
72	pub async fn allowed(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<String>> {
73	sqlx::query_scalar("SELECT fingerprint FROM secret_bypasses WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await
74	}
75
76	pub async fn bypasses(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Bypass>> {
77	sqlx::query_as("SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 ORDER BY id DESC")
78	.bind(repo_id)
79	.fetch_all(pool)
80	.await
81	}
82
83	/// Allow `fingerprint` with `reason`; allowing it again replaces the reason.
84	pub async fn allow(pool: &Pool, repo_id: i64, fingerprint: &str, rule: &str, reason: &str, by: Option<(i64, &str)>) -> sqlx::Result<()> {
85	sqlx::query(
86	"INSERT INTO secret_bypasses (repo_id, fingerprint, rule, reason, by_account, by_name, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) \
87	ON CONFLICT (repo_id, fingerprint) DO UPDATE SET reason = excluded.reason, rule = excluded.rule, by_account = excluded.by_account, by_name = excluded.by_name, created_ms = excluded.created_ms",
88	)
89	.bind(repo_id)
90	.bind(fingerprint)
91	.bind(rule)
92	.bind(reason)
93	.bind(by.map(\|b\| b.0))
94	.bind(by.map(\|b\| b.1).unwrap_or(""))
95	.bind(now_ms())
96	.execute(pool)
97	.await?;
98	Ok(())
99	}
100
101	/// Record a refused push's findings, keeping the newest few hundred.
102	pub async fn record(pool: &Pool, repo_id: i64, pusher: &str, via: &str, findings: &[NewFinding<'_>]) -> sqlx::Result<()> {
103	let now = now_ms();
104	let mut tx = super::begin_write(pool).await?;
105	for f in findings {
106	sqlx::query(
107	"INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
108	)
109	.bind(repo_id)
110	.bind(now)
111	.bind(pusher)
112	.bind(via)
113	.bind(f.rule)
114	.bind(f.description)
115	.bind(f.path)
116	.bind(f.line)
117	.bind(f.fingerprint)
118	.bind(f.excerpt)
119	.execute(&mut *tx)
120	.await?;
121	}
122	sqlx::query(
123	"DELETE FROM secret_findings WHERE repo_id = $1 AND source = 'push' \
124	AND id NOT IN (SELECT id FROM secret_findings WHERE repo_id = $2 AND source = 'push' ORDER BY id DESC LIMIT $3)",
125	)
126	.bind(repo_id)
127	.bind(repo_id)
128	.bind(KEPT_FINDINGS)
129	.execute(&mut *tx)
130	.await?;
131	tx.commit().await
132	}
133
134	/// The newest findings of refused pushes, those not since allowed.
135	pub async fn recent(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<FindingRow>> {
136	sqlx::query_as(
137	"SELECT id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt FROM secret_findings f \
138	WHERE repo_id = $1 AND source = 'push' \
139	AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
140	ORDER BY id DESC LIMIT $2",
141	)
142	.bind(repo_id)
143	.bind(limit)
144	.fetch_all(pool)
145	.await
146	}
147
148	/// The rule a fingerprint was found under in this repository, if it was.
149	pub async fn rule_of(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<String>> {
150	sqlx::query_scalar("SELECT rule FROM secret_findings WHERE repo_id = $1 AND fingerprint = $2 ORDER BY id DESC LIMIT 1")
151	.bind(repo_id)
152	.bind(fingerprint)
153	.fetch_optional(pool)
154	.await
155	}
156
157	/// Whether pushes adding possible secrets are refused here.
158	pub async fn set_enabled(pool: &Pool, repo_id: i64, on: bool) -> sqlx::Result<()> {
159	sqlx::query("UPDATE repositories SET push_protection = $1 WHERE id = $2").bind(on).bind(repo_id).execute(pool).await?;
160	Ok(())
161	}
162
163	/// Remove an allowed fingerprint (#277): pushes adding that value are
164	/// refused again. The row removed, if there was one.
165	pub async fn remove_bypass(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<Bypass>> {
166	let row: Option<Bypass> = sqlx::query_as(
167	"SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 AND fingerprint = $2",
168	)
169	.bind(repo_id)
170	.bind(fingerprint)
171	.fetch_optional(pool)
172	.await?;
173	if let Some(b) = &row {
174	sqlx::query("DELETE FROM secret_bypasses WHERE id = $1").bind(b.id).execute(pool).await?;
175	}
176	Ok(row)
177	}
178
179	/// A finding of the history scan to record.
180	pub struct NewHistoryFinding<'a> {
181	pub finding: NewFinding<'a>,
182	pub change_hash: String,
183	pub bookmark: String,
184	}
185
186	/// Record the history scan's findings: one per value and file, so a value
187	/// found again (in a later change, or by a rescan) keeps its first row and
188	/// whether it was resolved. How many were new.
189	pub async fn record_history(pool: &Pool, repo_id: i64, findings: &[NewHistoryFinding<'_>]) -> sqlx::Result<usize> {
190	let now = now_ms();
191	let mut added = 0;
192	for h in findings {
193	let f = &h.finding;
194	let r = sqlx::query(
195	"INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt, source, change_hash, bookmark) \
196	VALUES ($1, $2, '', '', $3, $4, $5, $6, $7, $8, 'history', $9, $10) \
197	ON CONFLICT (repo_id, fingerprint, path) WHERE source = 'history' DO NOTHING",
198	)
199	.bind(repo_id)
200	.bind(now)
201	.bind(f.rule)
202	.bind(f.description)
203	.bind(f.path)
204	.bind(f.line)
205	.bind(f.fingerprint)
206	.bind(f.excerpt)
207	.bind(&h.change_hash)
208	.bind(&h.bookmark)
209	.execute(pool)
210	.await?;
211	added += r.rows_affected() as usize;
212	}
213	Ok(added)
214	}
215
216	const HISTORY_COLUMNS: &str =
217	"id, at_ms, rule, description, path, line, fingerprint, excerpt, change_hash, bookmark, resolved_ms, resolved_by, resolved_reason";
218
219	/// The history scan's findings still open: not resolved, their value not
220	/// allowed; oldest first, as history has them.
221	pub async fn history(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
222	sqlx::query_as(&format!(
223	"SELECT {HISTORY_COLUMNS} FROM secret_findings f WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NULL \
224	AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
225	ORDER BY id LIMIT $2"
226	))
227	.bind(repo_id)
228	.bind(limit)
229	.fetch_all(pool)
230	.await
231	}
232
233	/// The history scan's findings marked resolved, newest mark first.
234	pub async fn resolved(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
235	sqlx::query_as(&format!(
236	"SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NOT NULL \
237	ORDER BY resolved_ms DESC, id DESC LIMIT $2"
238	))
239	.bind(repo_id)
240	.bind(limit)
241	.fetch_all(pool)
242	.await
243	}
244
245	/// Mark history finding `id` resolved (the secret was revoked) with
246	/// `reason`; the finding, if it was an open one of this repository's.
247	pub async fn resolve(pool: &Pool, repo_id: i64, id: i64, by: &str, reason: &str) -> sqlx::Result<Option<HistoryRow>> {
248	let done = sqlx::query(
249	"UPDATE secret_findings SET resolved_ms = $1, resolved_by = $2, resolved_reason = $3 \
250	WHERE id = $4 AND repo_id = $5 AND source = 'history' AND resolved_ms IS NULL",
251	)
252	.bind(now_ms())
253	.bind(by)
254	.bind(reason)
255	.bind(id)
256	.bind(repo_id)
257	.execute(pool)
258	.await?;
259	if done.rows_affected() == 0 {
260	return Ok(None);
261	}
262	sqlx::query_as(&format!("SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE id = $1")).bind(id).fetch_optional(pool).await
263	}
264
265	/// A repository's history scan (#277): the rules it ran with, the heads
266	/// whose history is scanned (`done_heads`), the heads a scan under way
267	/// works toward (`target_heads`, empty when none is) and how far it got
268	/// (`position` of `pending` changes, in the scan's order), totals, and a
269	/// maintainer's request for a scan now.
270	#[derive(Clone, Debug, Default, PartialEq, Eq, FromRow)]
271	pub struct ScanRow {
272	pub repo_id: i64,
273	pub rules_version: i64,
274	pub done_heads: String,
275	pub target_heads: String,
276	pub position: i64,
277	pub pending: i64,
278	pub changes: i64,
279	pub bytes: i64,
280	pub requested_ms: Option<i64>,
281	pub pass_ms: Option<i64>,
282	pub finished_ms: Option<i64>,
283	pub error: String,
284	}
285
286	const SCAN_COLUMNS: &str =
287	"repo_id, rules_version, done_heads, target_heads, position, pending, changes, bytes, requested_ms, pass_ms, finished_ms, error";
288
289	pub async fn scan_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Option<ScanRow>> {
290	sqlx::query_as(&format!("SELECT {SCAN_COLUMNS} FROM secret_scans WHERE repo_id = $1")).bind(repo_id).fetch_optional(pool).await
291	}
292
293	/// Save a repository's scan progress.
294	pub async fn save_scan(pool: &Pool, s: &ScanRow) -> sqlx::Result<()> {
295	sqlx::query(&format!(
296	"INSERT INTO secret_scans ({SCAN_COLUMNS}) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) \
297	ON CONFLICT (repo_id) DO UPDATE SET rules_version = excluded.rules_version, done_heads = excluded.done_heads, \
298	target_heads = excluded.target_heads, position = excluded.position, pending = excluded.pending, changes = excluded.changes, \
299	bytes = excluded.bytes, requested_ms = excluded.requested_ms, pass_ms = excluded.pass_ms, finished_ms = excluded.finished_ms, \
300	error = excluded.error"
301	))
302	.bind(s.repo_id)
303	.bind(s.rules_version)
304	.bind(&s.done_heads)
305	.bind(&s.target_heads)
306	.bind(s.position)
307	.bind(s.pending)
308	.bind(s.changes)
309	.bind(s.bytes)
310	.bind(s.requested_ms)
311	.bind(s.pass_ms)
312	.bind(s.finished_ms)
313	.bind(&s.error)
314	.execute(pool)
315	.await?;
316	Ok(())
317	}
318
319	/// A maintainer asked for the whole history to be scanned again now:
320	/// what was scanned is forgotten (findings and their marks are kept) and
321	/// the repository goes first.
322	pub async fn request_scan(pool: &Pool, repo_id: i64) -> sqlx::Result<()> {
323	let now = now_ms();
324	sqlx::query(
325	"INSERT INTO secret_scans (repo_id, requested_ms) VALUES ($1, $2) \
326	ON CONFLICT (repo_id) DO UPDATE SET requested_ms = excluded.requested_ms, done_heads = '', target_heads = '', position = 0, pending = 0",
327	)
328	.bind(repo_id)
329	.bind(now)
330	.execute(pool)
331	.await?;
332	Ok(())
333	}
334
335	/// Repositories whose maintainers asked for a scan, oldest request first.
336	pub async fn requested(pool: &Pool) -> sqlx::Result<Vec<i64>> {
337	sqlx::query_scalar("SELECT repo_id FROM secret_scans WHERE requested_ms IS NOT NULL ORDER BY requested_ms").fetch_all(pool).await
338	}

Keyboard