OXYGENHUB
# 19bb31ff1f5c oxygenhub / crates / hub / src / db / accounts.rs
20.1 KiB · Rust · last changed Milestone 0.2: accounts and access 1 month ago source blame history raw
19bb31ff Andy McCall ↶ 1 
//! Accounts, organisations, teams, collaborators, identities, tokens,
2 
//! invitations, settings and the audit log (hub spec §5).
3
4 
use super::{now_ms, Pool};
5 
use sqlx::FromRow;
6
7 
#[derive(Clone, Debug, FromRow)]
8 
pub struct Account {
9 
    pub id: i64,
10 
    pub username: String,
11 
    pub display_name: String,
12 
    pub email: String,
13 
    pub email_verified: bool,
14 
    pub password_hash: String,
15 
    pub session_salt: String,
16 
    pub totp_secret: Option<String>,
17 
    pub recovery_codes: String,
18 
    pub is_admin: bool,
19 
    pub banned: bool,
20 
    pub created_ms: i64,
21 
    pub last_seen_ms: i64,
22 
}
23
24 
impl Account {
25 
    pub fn name(&self) -> &str {
26 
        if self.display_name.is_empty() {
27 
            &self.username
28 
        } else {
29 
            &self.display_name
30 
        }
31 
    }
32
33 
    pub fn totp_enabled(&self) -> bool {
34 
        self.totp_secret.is_some()
35 
    }
36 
}
37
38 
#[derive(Clone, Debug, FromRow)]
39 
pub struct Organisation {
40 
    pub id: i64,
41 
    pub name: String,
42 
    pub display_name: String,
43 
    pub description: String,
44 
    pub default_role: String,
45 
    pub created_ms: i64,
46 
}
47
48 
#[derive(Clone, Debug, FromRow)]
49 
pub struct Team {
50 
    pub id: i64,
51 
    pub org_id: i64,
52 
    pub name: String,
53 
}
54
55 
#[derive(Clone, Debug, FromRow)]
56 
pub struct Token {
57 
    pub id: i64,
58 
    pub account_id: i64,
59 
    pub name: String,
60 
    pub hash: String,
61 
    pub scopes: String,
62 
    pub repos: String,
63 
    pub created_ms: i64,
64 
    pub expires_ms: Option<i64>,
65 
    pub last_used_ms: Option<i64>,
66 
}
67
68 
impl Token {
69 
    pub fn scope_list(&self) -> Vec<&str> {
70 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
71 
    }
72
73 
    pub fn has_scope(&self, scope: &str) -> bool {
74 
        self.scope_list().contains(&scope)
75 
    }
76
77 
    /// Whether the token may touch `owner/name`: every repository when the
78 
    /// list is empty.
79 
    pub fn covers(&self, slug: &str) -> bool {
80 
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
81 
    }
82
83 
    pub fn expired(&self, now: i64) -> bool {
84 
        self.expires_ms.is_some_and(|e| e < now)
85 
    }
86 
}
87
88 
#[derive(Clone, Debug, FromRow)]
89 
pub struct LinkedIdentity {
90 
    pub key: String,
91 
    pub account_id: i64,
92 
    pub name: String,
93 
    pub email: String,
94 
    pub object: Vec<u8>,
95 
    pub linked_ms: i64,
96 
}
97
98 
#[derive(Clone, Debug, FromRow)]
99 
pub struct AuditRow {
100 
    pub id: i64,
101 
    pub at_ms: i64,
102 
    pub actor: String,
103 
    pub action: String,
104 
    pub target: String,
105 
    pub detail: String,
106 
}
107
108 
// ---------------------------------------------------------------- accounts
109
110 
pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
111 
    sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
112 
}
113
114 
pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
115 
    sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
116 
}
117
118 
pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
119 
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
120 
}
121
122 
pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
123 
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
124 
}
125
126 
pub async fn create_account(
127 
    pool: &Pool,
128 
    username: &str,
129 
    email: &str,
130 
    password_hash: &str,
131 
    session_salt: &str,
132 
    is_admin: bool,
133 
) -> sqlx::Result<Account> {
134 
    sqlx::query_as(
135 
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
136 
    )
137 
    .bind(username)
138 
    .bind(email)
139 
    .bind(password_hash)
140 
    .bind(session_salt)
141 
    .bind(is_admin)
142 
    .bind(now_ms())
143 
    .fetch_one(pool)
144 
    .await
145 
}
146
147 
pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
148 
    sqlx::query("UPDATE accounts SET display_name = ?, email = ? WHERE id = ?")
149 
        .bind(display_name)
150 
        .bind(email)
151 
        .bind(id)
152 
        .execute(pool)
153 
        .await?;
154 
    Ok(())
155 
}
156
157 
pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
158 
    sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
159 
        .bind(password_hash)
160 
        .bind(session_salt)
161 
        .bind(id)
162 
        .execute(pool)
163 
        .await?;
164 
    Ok(())
165 
}
166
167 
pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
168 
    sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
169 
    Ok(())
170 
}
171
172 
pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
173 
    sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
174 
        .bind(secret)
175 
        .bind(recovery_codes)
176 
        .bind(id)
177 
        .execute(pool)
178 
        .await?;
179 
    Ok(())
180 
}
181
182 
pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
183 
    sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
184 
    Ok(())
185 
}
186
187 
pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
188 
    sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
189 
    Ok(())
190 
}
191
192 
pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
193 
    sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
194 
    Ok(())
195 
}
196
197 
// ----------------------------------------------------------- organisations
198
199 
pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
200 
    sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
201 
}
202
203 
pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
204 
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
205 
}
206
207 
pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
208 
    let rows: Vec<(i64, String, String, String, String, i64, String)> = sqlx::query_as(
209 
        "SELECT o.id, o.name, o.display_name, o.description, o.default_role, o.created_ms, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ? ORDER BY o.name",
210 
    )
211 
    .bind(account_id)
212 
    .fetch_all(pool)
213 
    .await?;
214 
    Ok(rows
215 
        .into_iter()
216 
        .map(|(id, name, display_name, description, default_role, created_ms, role)| {
217 
            (Organisation { id, name, display_name, description, default_role, created_ms }, role)
218 
        })
219 
        .collect())
220 
}
221
222 
pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
223 
    let mut tx = pool.begin().await?;
224 
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
225 
        .bind(name)
226 
        .bind(display_name)
227 
        .bind(now_ms())
228 
        .fetch_one(&mut *tx)
229 
        .await?;
230 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
231 
        .bind(org.id)
232 
        .bind(owner)
233 
        .execute(&mut *tx)
234 
        .await?;
235 
    tx.commit().await?;
236 
    Ok(org)
237 
}
238
239 
pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
240 
    sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
241 
        .bind(display_name)
242 
        .bind(description)
243 
        .bind(default_role)
244 
        .bind(id)
245 
        .execute(pool)
246 
        .await?;
247 
    Ok(())
248 
}
249
250 
/// `(account, role)` for every member.
251 
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
252 
    let rows: Vec<(i64, String)> =
253 
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
254 
    let mut out = Vec::new();
255 
    for (id, role) in rows {
256 
        if let Some(a) = account_by_id(pool, id).await? {
257 
            out.push((a, role));
258 
        }
259 
    }
260 
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
261 
    Ok(out)
262 
}
263
264 
pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
265 
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
266 
        .bind(org_id)
267 
        .bind(account_id)
268 
        .fetch_optional(pool)
269 
        .await
270 
}
271
272 
pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
273 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
274 
        .bind(org_id)
275 
        .bind(account_id)
276 
        .bind(role)
277 
        .execute(pool)
278 
        .await?;
279 
    Ok(())
280 
}
281
282 
pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
283 
    sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
284 
    Ok(())
285 
}
286
287 
pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
288 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
289 
}
290
291 
pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
292 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
293 
}
294
295 
pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
296 
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
297 
}
298
299 
pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
300 
    sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
301 
    Ok(())
302 
}
303
304 
pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
305 
    sqlx::query_as("SELECT a.* FROM accounts a JOIN team_members t ON t.account_id = a.id WHERE t.team_id = ? ORDER BY a.username")
306 
        .bind(team_id)
307 
        .fetch_all(pool)
308 
        .await
309 
}
310
311 
pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
312 
    sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
313 
        .bind(team_id)
314 
        .bind(account_id)
315 
        .execute(pool)
316 
        .await?;
317 
    Ok(())
318 
}
319
320 
pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
321 
    sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
322 
    Ok(())
323 
}
324
325 
// ----------------------------------------------------------- collaborators
326
327 
#[derive(Clone, Debug)]
328 
pub struct Collaborator {
329 
    pub account: Option<Account>,
330 
    pub team: Option<Team>,
331 
    pub role: String,
332 
}
333
334 
pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
335 
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
336 
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
337 
    let mut out = Vec::new();
338 
    for (account_id, team_id, role) in rows {
339 
        let account = match account_id {
340 
            Some(id) => account_by_id(pool, id).await?,
341 
            None => None,
342 
        };
343 
        let team = match team_id {
344 
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
345 
            None => None,
346 
        };
347 
        out.push(Collaborator { account, team, role });
348 
    }
349 
    Ok(out)
350 
}
351
352 
pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
353 
    if let Some(a) = account_id {
354 
        sqlx::query("INSERT INTO collaborators (repo_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (repo_id, account_id) WHERE account_id IS NOT NULL DO UPDATE SET role = excluded.role")
355 
            .bind(repo_id)
356 
            .bind(a)
357 
            .bind(role)
358 
            .execute(pool)
359 
            .await?;
360 
    } else if let Some(t) = team_id {
361 
        sqlx::query("INSERT INTO collaborators (repo_id, team_id, role) VALUES (?, ?, ?) ON CONFLICT (repo_id, team_id) WHERE team_id IS NOT NULL DO UPDATE SET role = excluded.role")
362 
            .bind(repo_id)
363 
            .bind(t)
364 
            .bind(role)
365 
            .execute(pool)
366 
            .await?;
367 
    }
368 
    Ok(())
369 
}
370
371 
pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
372 
    sqlx::query("DELETE FROM collaborators WHERE repo_id = ? AND ((account_id IS NOT NULL AND account_id = ?) OR (team_id IS NOT NULL AND team_id = ?))")
373 
        .bind(repo_id)
374 
        .bind(account_id)
375 
        .bind(team_id)
376 
        .execute(pool)
377 
        .await?;
378 
    Ok(())
379 
}
380
381 
/// The direct or team role an account holds on a repository, the highest.
382 
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
383 
    let rows: Vec<String> = sqlx::query_scalar(
384 
        "SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
385 
    )
386 
    .bind(repo_id)
387 
    .bind(account_id)
388 
    .bind(account_id)
389 
    .fetch_all(pool)
390 
    .await?;
391 
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
392 
}
393
394 
// ------------------------------------------------------------------ tokens
395
396 
pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
397 
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
398 
}
399
400 
pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
401 
    sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
402 
}
403
404 
pub async fn create_token(
405 
    pool: &Pool,
406 
    account_id: i64,
407 
    name: &str,
408 
    hash: &str,
409 
    scopes: &str,
410 
    repos: &str,
411 
    expires_ms: Option<i64>,
412 
) -> sqlx::Result<Token> {
413 
    sqlx::query_as(
414 
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
415 
    )
416 
    .bind(account_id)
417 
    .bind(name)
418 
    .bind(hash)
419 
    .bind(scopes)
420 
    .bind(repos)
421 
    .bind(now_ms())
422 
    .bind(expires_ms)
423 
    .fetch_one(pool)
424 
    .await
425 
}
426
427 
pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
428 
    sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
429 
    Ok(())
430 
}
431
432 
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
433 
    sqlx::query("UPDATE tokens SET last_used_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
434 
    Ok(())
435 
}
436
437 
// -------------------------------------------------------------- identities
438
439 
pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
440 
    sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
441 
}
442
443 
pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
444 
    sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
445 
}
446
447 
/// Every linked key with its account's username, for attribution.
448 
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
449 
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
450 
}
451
452 
pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
453 
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
454 
        .bind(key)
455 
        .bind(account_id)
456 
        .bind(name)
457 
        .bind(email)
458 
        .bind(object)
459 
        .bind(now_ms())
460 
        .execute(pool)
461 
        .await?;
462 
    Ok(())
463 
}
464
465 
pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
466 
    sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
467 
    Ok(())
468 
}
469
470 
pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
471 
    sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
472 
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
473 
        .bind(account_id)
474 
        .bind(bytes_hex)
475 
        .bind(now_ms())
476 
        .fetch_one(pool)
477 
        .await?;
478 
    Ok(id)
479 
}
480
481 
/// Take (and delete) a pending challenge of this account.
482 
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
483 
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
484 
        .bind(id)
485 
        .bind(account_id)
486 
        .bind(now_ms() - 10 * 60 * 1000)
487 
        .fetch_optional(pool)
488 
        .await?;
489 
    sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
490 
    Ok(bytes)
491 
}
492
493 
// ------------------------------------------------------------- invitations
494
495 
pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
496 
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
497 
        .bind(code)
498 
        .bind(created_by)
499 
        .bind(now_ms())
500 
        .execute(pool)
501 
        .await?;
502 
    Ok(())
503 
}
504
505 
pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
506 
    let n: i64 =
507 
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
508 
    Ok(n > 0)
509 
}
510
511 
pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
512 
    sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
513 
    Ok(())
514 
}
515
516 
pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
517 
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
518 
}
519
520 
// ---------------------------------------------------------------- settings
521
522 
pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
523 
    sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
524 
}
525
526 
pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
527 
    sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
528 
        .bind(key)
529 
        .bind(value)
530 
        .execute(pool)
531 
        .await?;
532 
    Ok(())
533 
}
534
535 
// ------------------------------------------------------------------- audit
536
537 
pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
538 
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
539 
        .bind(now_ms())
540 
        .bind(actor.map(|a| a.id))
541 
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
542 
        .bind(action)
543 
        .bind(target)
544 
        .bind(detail)
545 
        .execute(pool)
546 
        .await?;
547 
    Ok(())
548 
}
549
550 
pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
551 
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
552 
        .bind(limit)
553 
        .fetch_all(pool)
554 
        .await
555 
}
556
557 
/// The repositories an account can see beyond the public ones: its own,
558 
/// its organisations', and those it collaborates on.
559 
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
560 
    sqlx::query_scalar(
561 
        "SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
562 
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
563 
         OR r.id IN (SELECT repo_id FROM collaborators WHERE account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
564 
    )
565 
    .bind(account_id)
566 
    .bind(account_id)
567 
    .bind(account_id)
568 
    .bind(account_id)
569 
    .fetch_all(pool)
570 
    .await
571 
}