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