OXYGENHUB
◆ v0.1.0 oxygenhub / crates / hub / src / db / accounts.rs
25.2 KiB · Rust · last changed Write transactions begin IMMEDIATE, so a concurrent writer is waited for (busy timeout) instead of failing at once with database is locked; a test of 16 concurrent log appends fails without it 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,
10174045 Andy McCall ↶ 24 
    /// Shown on the profile page (migration 0008).
25 
    #[sqlx(default)]
26 
    pub bio: String,
27 
    #[sqlx(default)]
28 
    pub website: String,
29 
    #[sqlx(default)]
30 
    pub location: String,
a160b98f Andy McCall ↶ 31 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
32 
    pub avatar: String,
19bb31ff Andy McCall ↶ 33 
}
34
35 
impl Account {
36 
    pub fn name(&self) -> &str {
37 
        if self.display_name.is_empty() {
38 
            &self.username
39 
        } else {
40 
            &self.display_name
41 
        }
42 
    }
43
44 
    pub fn totp_enabled(&self) -> bool {
45 
        self.totp_secret.is_some()
46 
    }
47 
}
48
49 
#[derive(Clone, Debug, FromRow)]
50 
pub struct Organisation {
51 
    pub id: i64,
52 
    pub name: String,
53 
    pub display_name: String,
54 
    pub description: String,
55 
    pub default_role: String,
56 
    pub created_ms: i64,
a160b98f Andy McCall ↶ 57 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
58 
    pub avatar: String,
19bb31ff Andy McCall ↶ 59 
}
60
61 
#[derive(Clone, Debug, FromRow)]
62 
pub struct Team {
63 
    pub id: i64,
64 
    pub org_id: i64,
65 
    pub name: String,
66 
}
67
68 
#[derive(Clone, Debug, FromRow)]
69 
pub struct Token {
70 
    pub id: i64,
71 
    pub account_id: i64,
72 
    pub name: String,
73 
    pub hash: String,
74 
    pub scopes: String,
75 
    pub repos: String,
76 
    pub created_ms: i64,
77 
    pub expires_ms: Option<i64>,
78 
    pub last_used_ms: Option<i64>,
79 
}
80
81 
impl Token {
82 
    pub fn scope_list(&self) -> Vec<&str> {
83 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
84 
    }
85
86 
    pub fn has_scope(&self, scope: &str) -> bool {
87 
        self.scope_list().contains(&scope)
88 
    }
89
90 
    /// Whether the token may touch `owner/name`: every repository when the
91 
    /// list is empty.
92 
    pub fn covers(&self, slug: &str) -> bool {
93 
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
94 
    }
95
96 
    pub fn expired(&self, now: i64) -> bool {
97 
        self.expires_ms.is_some_and(|e| e < now)
98 
    }
99 
}
100
101 
#[derive(Clone, Debug, FromRow)]
102 
pub struct LinkedIdentity {
103 
    pub key: String,
104 
    pub account_id: i64,
105 
    pub name: String,
106 
    pub email: String,
107 
    pub object: Vec<u8>,
108 
    pub linked_ms: i64,
109 
}
110
111 
#[derive(Clone, Debug, FromRow)]
112 
pub struct AuditRow {
113 
    pub id: i64,
114 
    pub at_ms: i64,
115 
    pub actor: String,
116 
    pub action: String,
117 
    pub target: String,
118 
    pub detail: String,
119 
}
120
121 
// ---------------------------------------------------------------- accounts
122
123 
pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
124 
    sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
125 
}
126
127 
pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
128 
    sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
129 
}
130
131 
pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
132 
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
133 
}
134
135 
pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
136 
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
137 
}
138
139 
pub async fn create_account(
140 
    pool: &Pool,
141 
    username: &str,
142 
    email: &str,
143 
    password_hash: &str,
144 
    session_salt: &str,
145 
    is_admin: bool,
146 
) -> sqlx::Result<Account> {
147 
    sqlx::query_as(
148 
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
149 
    )
150 
    .bind(username)
151 
    .bind(email)
152 
    .bind(password_hash)
153 
    .bind(session_salt)
154 
    .bind(is_admin)
155 
    .bind(now_ms())
156 
    .fetch_one(pool)
157 
    .await
158 
}
159
160 
pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
34beb1a7 Andy McCall ↶ 161 
    // A changed address is unverified until its own link is used.
162 
    sqlx::query(
163 
        "UPDATE accounts SET display_name = ?, email_verified = CASE WHEN email = ? THEN email_verified ELSE 0 END, email = ? WHERE id = ?",
164 
    )
165 
    .bind(display_name)
166 
    .bind(email)
167 
    .bind(email)
168 
    .bind(id)
169 
    .execute(pool)
170 
    .await?;
19bb31ff Andy McCall ↶ 171 
    Ok(())
172 
}
173
10174045 Andy McCall ↶ 174 
/// The public profile fields.
175 
pub async fn set_bio(pool: &Pool, id: i64, bio: &str, website: &str, location: &str) -> sqlx::Result<()> {
176 
    sqlx::query("UPDATE accounts SET bio = ?, website = ?, location = ? WHERE id = ?")
177 
        .bind(bio)
178 
        .bind(website)
179 
        .bind(location)
180 
        .bind(id)
181 
        .execute(pool)
182 
        .await?;
183 
    Ok(())
184 
}
185
19bb31ff Andy McCall ↶ 186 
pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
187 
    sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
188 
        .bind(password_hash)
189 
        .bind(session_salt)
190 
        .bind(id)
191 
        .execute(pool)
192 
        .await?;
193 
    Ok(())
194 
}
195
196 
pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
197 
    sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
198 
    Ok(())
199 
}
200
201 
pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
202 
    sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
203 
        .bind(secret)
204 
        .bind(recovery_codes)
205 
        .bind(id)
206 
        .execute(pool)
207 
        .await?;
208 
    Ok(())
209 
}
210
211 
pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
212 
    sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
213 
    Ok(())
214 
}
215
216 
pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
217 
    sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
218 
    Ok(())
219 
}
220
221 
pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
222 
    sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
223 
    Ok(())
224 
}
225
226 
// ----------------------------------------------------------- organisations
227
228 
pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
229 
    sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
230 
}
231
232 
pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
233 
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
234 
}
235
a160b98f Andy McCall ↶ 236 
/// An organisation with the account's role in it.
237 
#[derive(FromRow)]
238 
struct Membership {
239 
    #[sqlx(flatten)]
240 
    org: Organisation,
241 
    role: String,
242 
}
243
19bb31ff Andy McCall ↶ 244 
pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
a160b98f Andy McCall ↶ 245 
    let rows: Vec<Membership> = sqlx::query_as(
246 
        "SELECT o.*, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ? ORDER BY o.name",
19bb31ff Andy McCall ↶ 247 
    )
248 
    .bind(account_id)
249 
    .fetch_all(pool)
250 
    .await?;
a160b98f Andy McCall ↶ 251 
    Ok(rows.into_iter().map(|m| (m.org, m.role)).collect())
19bb31ff Andy McCall ↶ 252 
}
253
254 
pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
941abdd6 Andy McCall ↶ 255 
    let mut tx = super::begin_write(pool).await?;
19bb31ff Andy McCall ↶ 256 
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
257 
        .bind(name)
258 
        .bind(display_name)
259 
        .bind(now_ms())
260 
        .fetch_one(&mut *tx)
261 
        .await?;
262 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
263 
        .bind(org.id)
264 
        .bind(owner)
265 
        .execute(&mut *tx)
266 
        .await?;
267 
    tx.commit().await?;
268 
    Ok(org)
269 
}
270
271 
pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
272 
    sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
273 
        .bind(display_name)
274 
        .bind(description)
275 
        .bind(default_role)
276 
        .bind(id)
277 
        .execute(pool)
278 
        .await?;
279 
    Ok(())
280 
}
281
282 
/// `(account, role)` for every member.
283 
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
284 
    let rows: Vec<(i64, String)> =
285 
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
286 
    let mut out = Vec::new();
287 
    for (id, role) in rows {
288 
        if let Some(a) = account_by_id(pool, id).await? {
289 
            out.push((a, role));
290 
        }
291 
    }
292 
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
293 
    Ok(out)
294 
}
295
296 
pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
297 
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
298 
        .bind(org_id)
299 
        .bind(account_id)
300 
        .fetch_optional(pool)
301 
        .await
302 
}
303
304 
pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
305 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
306 
        .bind(org_id)
307 
        .bind(account_id)
308 
        .bind(role)
309 
        .execute(pool)
310 
        .await?;
311 
    Ok(())
312 
}
313
314 
pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
315 
    sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
316 
    Ok(())
317 
}
318
319 
pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
320 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
321 
}
322
323 
pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
324 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
325 
}
326
327 
pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
328 
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
329 
}
330
331 
pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
332 
    sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
333 
    Ok(())
334 
}
335
336 
pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
337 
    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")
338 
        .bind(team_id)
339 
        .fetch_all(pool)
340 
        .await
341 
}
342
343 
pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
344 
    sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
345 
        .bind(team_id)
346 
        .bind(account_id)
347 
        .execute(pool)
348 
        .await?;
349 
    Ok(())
350 
}
351
352 
pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
353 
    sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
354 
    Ok(())
355 
}
356
357 
// ----------------------------------------------------------- collaborators
358
359 
#[derive(Clone, Debug)]
360 
pub struct Collaborator {
361 
    pub account: Option<Account>,
362 
    pub team: Option<Team>,
363 
    pub role: String,
364 
}
365
366 
pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
367 
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
368 
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
369 
    let mut out = Vec::new();
370 
    for (account_id, team_id, role) in rows {
371 
        let account = match account_id {
372 
            Some(id) => account_by_id(pool, id).await?,
373 
            None => None,
374 
        };
375 
        let team = match team_id {
376 
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
377 
            None => None,
378 
        };
379 
        out.push(Collaborator { account, team, role });
380 
    }
381 
    Ok(out)
382 
}
383
384 
pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
385 
    if let Some(a) = account_id {
386 
        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")
387 
            .bind(repo_id)
388 
            .bind(a)
389 
            .bind(role)
390 
            .execute(pool)
391 
            .await?;
392 
    } else if let Some(t) = team_id {
393 
        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")
394 
            .bind(repo_id)
395 
            .bind(t)
396 
            .bind(role)
397 
            .execute(pool)
398 
            .await?;
399 
    }
400 
    Ok(())
401 
}
402
403 
pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
404 
    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 = ?))")
405 
        .bind(repo_id)
406 
        .bind(account_id)
407 
        .bind(team_id)
408 
        .execute(pool)
409 
        .await?;
410 
    Ok(())
411 
}
412
413 
/// The direct or team role an account holds on a repository, the highest.
414 
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
415 
    let rows: Vec<String> = sqlx::query_scalar(
416 
        "SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
417 
    )
418 
    .bind(repo_id)
419 
    .bind(account_id)
420 
    .bind(account_id)
421 
    .fetch_all(pool)
422 
    .await?;
423 
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
424 
}
425
426 
// ------------------------------------------------------------------ tokens
427
428 
pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
429 
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
430 
}
431
432 
pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
433 
    sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
434 
}
435
436 
pub async fn create_token(
437 
    pool: &Pool,
438 
    account_id: i64,
439 
    name: &str,
440 
    hash: &str,
441 
    scopes: &str,
442 
    repos: &str,
443 
    expires_ms: Option<i64>,
444 
) -> sqlx::Result<Token> {
445 
    sqlx::query_as(
446 
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
447 
    )
448 
    .bind(account_id)
449 
    .bind(name)
450 
    .bind(hash)
451 
    .bind(scopes)
452 
    .bind(repos)
453 
    .bind(now_ms())
454 
    .bind(expires_ms)
455 
    .fetch_one(pool)
456 
    .await
457 
}
458
459 
pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
460 
    sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
461 
    Ok(())
462 
}
463
464 
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
465 
    sqlx::query("UPDATE tokens SET last_used_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
466 
    Ok(())
467 
}
468
469 
// -------------------------------------------------------------- identities
470
471 
pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
472 
    sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
473 
}
474
475 
pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
476 
    sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
477 
}
478
479 
/// Every linked key with its account's username, for attribution.
480 
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
481 
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
482 
}
483
484 
pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
485 
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
486 
        .bind(key)
487 
        .bind(account_id)
488 
        .bind(name)
489 
        .bind(email)
490 
        .bind(object)
491 
        .bind(now_ms())
492 
        .execute(pool)
493 
        .await?;
494 
    Ok(())
495 
}
496
497 
pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
498 
    sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
499 
    Ok(())
500 
}
501
502 
pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
503 
    sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
504 
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
505 
        .bind(account_id)
506 
        .bind(bytes_hex)
507 
        .bind(now_ms())
508 
        .fetch_one(pool)
509 
        .await?;
510 
    Ok(id)
511 
}
512
513 
/// Take (and delete) a pending challenge of this account.
514 
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
515 
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
516 
        .bind(id)
517 
        .bind(account_id)
518 
        .bind(now_ms() - 10 * 60 * 1000)
519 
        .fetch_optional(pool)
520 
        .await?;
521 
    sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
522 
    Ok(bytes)
523 
}
524
525 
// ------------------------------------------------------------- invitations
526
527 
pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
528 
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
529 
        .bind(code)
530 
        .bind(created_by)
531 
        .bind(now_ms())
532 
        .execute(pool)
533 
        .await?;
534 
    Ok(())
535 
}
536
537 
pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
538 
    let n: i64 =
539 
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
540 
    Ok(n > 0)
541 
}
542
543 
pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
544 
    sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
545 
    Ok(())
546 
}
547
548 
pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
549 
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
550 
}
551
552 
// ---------------------------------------------------------------- settings
553
554 
pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
555 
    sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
556 
}
557
558 
pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
559 
    sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
560 
        .bind(key)
561 
        .bind(value)
562 
        .execute(pool)
563 
        .await?;
564 
    Ok(())
565 
}
566
567 
// ------------------------------------------------------------------- audit
568
569 
pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
570 
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
571 
        .bind(now_ms())
572 
        .bind(actor.map(|a| a.id))
573 
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
574 
        .bind(action)
575 
        .bind(target)
576 
        .bind(detail)
577 
        .execute(pool)
578 
        .await?;
579 
    Ok(())
580 
}
581
582 
pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
583 
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
584 
        .bind(limit)
585 
        .fetch_all(pool)
586 
        .await
587 
}
588
589 
/// The repositories an account can see beyond the public ones: its own,
590 
/// its organisations', and those it collaborates on.
591 
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
592 
    sqlx::query_scalar(
593 
        "SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
594 
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
595 
         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 = ?))",
596 
    )
597 
    .bind(account_id)
598 
    .bind(account_id)
599 
    .bind(account_id)
600 
    .bind(account_id)
601 
    .fetch_all(pool)
602 
    .await
34beb1a7 Andy McCall ↶ 603 
}
604
605 
// ------------------------------------------------------ account links
606
607 
/// A verification or reset link (hub spec §5.1.1).
608 
#[derive(Clone, Debug, FromRow)]
609 
pub struct AccountLink {
610 
    pub id: i64,
611 
    pub account_id: i64,
612 
    pub kind: String,
613 
    pub hash: String,
614 
    pub email: String,
615 
    pub created_by: Option<i64>,
616 
    pub created_ms: i64,
617 
    pub expires_ms: i64,
618 
    pub used_ms: Option<i64>,
619 
}
620
621 
/// Make a link; returns its secret (shown or sent once, stored as a hash).
622 
pub async fn create_link(
623 
    pool: &Pool,
624 
    account_id: i64,
625 
    kind: &str,
626 
    email: &str,
627 
    created_by: Option<i64>,
628 
    ttl_ms: i64,
629 
) -> sqlx::Result<String> {
630 
    let secret = crate::auth::random_hex(32);
631 
    let now = now_ms();
632 
    sqlx::query(
633 
        "INSERT INTO account_links (account_id, kind, hash, email, created_by, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?)",
634 
    )
635 
    .bind(account_id)
636 
    .bind(kind)
637 
    .bind(crate::auth::token_hash(&secret))
638 
    .bind(email)
639 
    .bind(created_by)
640 
    .bind(now)
641 
    .bind(now + ttl_ms)
642 
    .execute(pool)
643 
    .await?;
644 
    Ok(secret)
645 
}
646
647 
/// An unused, unexpired link of this kind with this secret.
648 
pub async fn live_link(pool: &Pool, kind: &str, secret: &str) -> sqlx::Result<Option<AccountLink>> {
649 
    sqlx::query_as("SELECT * FROM account_links WHERE kind = ? AND hash = ? AND used_ms IS NULL AND expires_ms > ?")
650 
        .bind(kind)
651 
        .bind(crate::auth::token_hash(secret))
652 
        .bind(now_ms())
653 
        .fetch_optional(pool)
654 
        .await
655 
}
656
657 
/// Consume a link; false when it was already used (a race lost).
658 
pub async fn use_link(pool: &Pool, id: i64) -> sqlx::Result<bool> {
659 
    let r =
660 
        sqlx::query("UPDATE account_links SET used_ms = ? WHERE id = ? AND used_ms IS NULL").bind(now_ms()).bind(id).execute(pool).await?;
661 
    Ok(r.rows_affected() == 1)
662 
}
663
664 
/// Void every unused link of a kind for an account.
665 
pub async fn void_links(pool: &Pool, account_id: i64, kind: &str) -> sqlx::Result<()> {
666 
    sqlx::query("UPDATE account_links SET used_ms = ? WHERE account_id = ? AND kind = ? AND used_ms IS NULL")
667 
        .bind(now_ms())
668 
        .bind(account_id)
669 
        .bind(kind)
670 
        .execute(pool)
671 
        .await?;
672 
    Ok(())
673 
}
674
675 
/// When the account's newest link of a kind (for this address, when given) was made, for rate limits.
676 
pub async fn last_link_ms(pool: &Pool, account_id: i64, kind: &str, email: Option<&str>) -> sqlx::Result<Option<i64>> {
677 
    match email {
678 
        Some(e) => {
679 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ? AND email = ?")
680 
                .bind(account_id)
681 
                .bind(kind)
682 
                .bind(e)
683 
                .fetch_one(pool)
684 
                .await
685 
        }
686 
        None => {
687 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ?")
688 
                .bind(account_id)
689 
                .bind(kind)
690 
                .fetch_one(pool)
691 
                .await
692 
        }
693 
    }
694 
}
695
696 
/// How many links of a kind the account got since `since_ms`.
697 
pub async fn links_since(pool: &Pool, account_id: i64, kind: &str, since_ms: i64) -> sqlx::Result<i64> {
698 
    sqlx::query_scalar("SELECT COUNT(*) FROM account_links WHERE account_id = ? AND kind = ? AND created_ms >= ?")
699 
        .bind(account_id)
700 
        .bind(kind)
701 
        .bind(since_ms)
702 
        .fetch_one(pool)
703 
        .await
704 
}
705
706 
pub async fn set_email_verified(pool: &Pool, id: i64, verified: bool) -> sqlx::Result<()> {
707 
    sqlx::query("UPDATE accounts SET email_verified = ? WHERE id = ?").bind(verified).bind(id).execute(pool).await?;
708 
    Ok(())
709 
}
710
711 
/// The account with this verified email (case-insensitive), if exactly one.
712 
pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {
713 
    let found: Vec<Account> = sqlx::query_as("SELECT * FROM accounts WHERE email_verified = 1 AND lower(email) = lower(?) LIMIT 2")
714 
        .bind(email)
715 
        .fetch_all(pool)
716 
        .await?;
717 
    Ok(if found.len() == 1 { found.into_iter().next() } else { None })
a160b98f Andy McCall ↶ 718 
}
719
720 
/// Set or clear the picture of an account (`account`) or organisation (`org`).
721 
pub async fn set_avatar(pool: &Pool, kind: &str, id: i64, value: &str) -> sqlx::Result<()> {
722 
    let table = if kind == "org" { "organisations" } else { "accounts" };
723 
    sqlx::query(&format!("UPDATE {table} SET avatar = ? WHERE id = ?")).bind(value).bind(id).execute(pool).await?;
724 
    Ok(())
19bb31ff Andy McCall ↶ 725 
}