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