OXYGENHUB
◆ v0.5.0 oxygenhub / crates / hub / src / db / accounts.rs
33.5 KiB · Rust · last changed Notifications that reach you, and 2FA without the password twice 22 days 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,
6a178055 Andy McCall ↶ 20 
    /// Disabled by an admin: refused like a ban, and enabled again (0031).
21 
    #[sqlx(default)]
22 
    pub disabled: bool,
19bb31ff Andy McCall ↶ 23 
    pub created_ms: i64,
24 
    pub last_seen_ms: i64,
18e381ea Andy McCall ↶ 25 
    pub email_notifications: bool,
26 
    pub unsubscribe_key: String,
10174045 Andy McCall ↶ 27 
    /// Shown on the profile page (migration 0008).
28 
    #[sqlx(default)]
29 
    pub bio: String,
30 
    #[sqlx(default)]
31 
    pub website: String,
32 
    #[sqlx(default)]
33 
    pub location: String,
a160b98f Andy McCall ↶ 34 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
35 
    pub avatar: String,
89e89836 Andy McCall ↶ 36 
    /// Accounts elsewhere (migration 0039, #105): a Bluesky handle, a
37 
    /// Mastodon `user@instance`, a GitHub username; empty when not set.
38 
    #[sqlx(default)]
39 
    pub bluesky: String,
40 
    #[sqlx(default)]
41 
    pub mastodon: String,
42 
    #[sqlx(default)]
43 
    pub github: String,
19bb31ff Andy McCall ↶ 44 
}
45
46 
impl Account {
6a178055 Andy McCall ↶ 47 
    /// Whether the account may not sign in or act: banned or disabled.
48 
    pub fn blocked(&self) -> bool {
49 
        self.banned || self.disabled
50 
    }
51
19bb31ff Andy McCall ↶ 52 
    pub fn name(&self) -> &str {
53 
        if self.display_name.is_empty() {
54 
            &self.username
55 
        } else {
56 
            &self.display_name
57 
        }
58 
    }
59
60 
    pub fn totp_enabled(&self) -> bool {
61 
        self.totp_secret.is_some()
62 
    }
63 
}
64
65 
#[derive(Clone, Debug, FromRow)]
66 
pub struct Organisation {
67 
    pub id: i64,
68 
    pub name: String,
69 
    pub display_name: String,
70 
    pub description: String,
71 
    pub default_role: String,
72 
    pub created_ms: i64,
a160b98f Andy McCall ↶ 73 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
74 
    pub avatar: String,
19bb31ff Andy McCall ↶ 75 
}
76
77 
#[derive(Clone, Debug, FromRow)]
78 
pub struct Team {
79 
    pub id: i64,
80 
    pub org_id: i64,
81 
    pub name: String,
82 
}
83
84 
#[derive(Clone, Debug, FromRow)]
85 
pub struct Token {
86 
    pub id: i64,
87 
    pub account_id: i64,
88 
    pub name: String,
89 
    pub hash: String,
90 
    pub scopes: String,
91 
    pub repos: String,
92 
    pub created_ms: i64,
93 
    pub expires_ms: Option<i64>,
94 
    pub last_used_ms: Option<i64>,
60f11b71 Andy McCall ↶ 95 
    /// A renewing token's lifetime: each use extends it to now plus this.
96 
    pub renew_ms: Option<i64>,
bff9106e Andy McCall ↶ 97 
    /// An agent uses it: what is done with it is told to its owner
98 
    /// (migration 0065, oxygenhub#183).
99 
    #[sqlx(default)]
100 
    pub agent: bool,
19bb31ff Andy McCall ↶ 101 
}
102
103 
impl Token {
104 
    pub fn scope_list(&self) -> Vec<&str> {
105 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
106 
    }
107
108 
    pub fn has_scope(&self, scope: &str) -> bool {
109 
        self.scope_list().contains(&scope)
110 
    }
111
112 
    /// Whether the token may touch `owner/name`: every repository when the
113 
    /// list is empty.
114 
    pub fn covers(&self, slug: &str) -> bool {
115 
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
116 
    }
117
118 
    pub fn expired(&self, now: i64) -> bool {
119 
        self.expires_ms.is_some_and(|e| e < now)
120 
    }
121 
}
122
123 
#[derive(Clone, Debug, FromRow)]
124 
pub struct LinkedIdentity {
125 
    pub key: String,
126 
    pub account_id: i64,
127 
    pub name: String,
128 
    pub email: String,
129 
    pub object: Vec<u8>,
130 
    pub linked_ms: i64,
131 
}
132
133 
#[derive(Clone, Debug, FromRow)]
134 
pub struct AuditRow {
135 
    pub id: i64,
136 
    pub at_ms: i64,
137 
    pub actor: String,
138 
    pub action: String,
139 
    pub target: String,
140 
    pub detail: String,
141 
}
142
143 
// ---------------------------------------------------------------- accounts
144
145 
pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
146 
    sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
147 
}
148
149 
pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
150 
    sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
151 
}
152
153 
pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
154 
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
155 
}
156
157 
pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
158 
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
159 
}
160
161 
pub async fn create_account(
162 
    pool: &Pool,
163 
    username: &str,
164 
    email: &str,
165 
    password_hash: &str,
166 
    session_salt: &str,
167 
    is_admin: bool,
168 
) -> sqlx::Result<Account> {
169 
    sqlx::query_as(
170 
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
171 
    )
172 
    .bind(username)
173 
    .bind(email)
174 
    .bind(password_hash)
175 
    .bind(session_salt)
176 
    .bind(is_admin)
177 
    .bind(now_ms())
178 
    .fetch_one(pool)
179 
    .await
180 
}
181
182 
pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
34beb1a7 Andy McCall ↶ 183 
    // A changed address is unverified until its own link is used.
184 
    sqlx::query(
185 
        "UPDATE accounts SET display_name = ?, email_verified = CASE WHEN email = ? THEN email_verified ELSE 0 END, email = ? WHERE id = ?",
186 
    )
187 
    .bind(display_name)
188 
    .bind(email)
189 
    .bind(email)
190 
    .bind(id)
191 
    .execute(pool)
192 
    .await?;
19bb31ff Andy McCall ↶ 193 
    Ok(())
194 
}
195
10174045 Andy McCall ↶ 196 
/// The public profile fields.
89e89836 Andy McCall ↶ 197 
/// The accounts elsewhere a profile shows (#105), already normalised.
198 
pub async fn set_links(pool: &Pool, id: i64, bluesky: &str, mastodon: &str, github: &str) -> sqlx::Result<()> {
199 
    sqlx::query("UPDATE accounts SET bluesky = ?, mastodon = ?, github = ? WHERE id = ?")
200 
        .bind(bluesky)
201 
        .bind(mastodon)
202 
        .bind(github)
203 
        .bind(id)
204 
        .execute(pool)
205 
        .await?;
206 
    Ok(())
207 
}
208
10174045 Andy McCall ↶ 209 
pub async fn set_bio(pool: &Pool, id: i64, bio: &str, website: &str, location: &str) -> sqlx::Result<()> {
210 
    sqlx::query("UPDATE accounts SET bio = ?, website = ?, location = ? WHERE id = ?")
211 
        .bind(bio)
212 
        .bind(website)
213 
        .bind(location)
214 
        .bind(id)
215 
        .execute(pool)
216 
        .await?;
217 
    Ok(())
218 
}
219
19bb31ff Andy McCall ↶ 220 
pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
221 
    sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
222 
        .bind(password_hash)
223 
        .bind(session_salt)
224 
        .bind(id)
225 
        .execute(pool)
226 
        .await?;
227 
    Ok(())
228 
}
229
230 
pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
231 
    sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
232 
    Ok(())
233 
}
234
235 
pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
236 
    sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
237 
        .bind(secret)
238 
        .bind(recovery_codes)
239 
        .bind(id)
240 
        .execute(pool)
241 
        .await?;
242 
    Ok(())
243 
}
244
245 
pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
246 
    sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
247 
    Ok(())
248 
}
249
250 
pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
251 
    sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
6a178055 Andy McCall ↶ 252 
    Ok(())
253 
}
254
255 
pub async fn set_disabled(pool: &Pool, id: i64, disabled: bool) -> sqlx::Result<()> {
256 
    sqlx::query("UPDATE accounts SET disabled = ? WHERE id = ?").bind(disabled).bind(id).execute(pool).await?;
19bb31ff Andy McCall ↶ 257 
    Ok(())
6a178055 Andy McCall ↶ 258 
}
259
260 
/// Delete an account (oxygenhub#81): its row goes, and with it (by the
261 
/// schema's cascades) its tokens, keys, sessions' salt, memberships, stars
262 
/// and notifications; what it wrote keeps its author's name. The username
263 
/// is retired so nobody can register it and pass for the account.
264 
pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> {
265 
    let mut tx = super::begin_write(pool).await?;
266 
    sqlx::query("INSERT OR IGNORE INTO retired_usernames (username, retired_ms) VALUES (?, ?)")
267 
        .bind(username)
268 
        .bind(now_ms())
269 
        .execute(&mut *tx)
270 
        .await?;
271 
    sqlx::query("DELETE FROM accounts WHERE id = ?").bind(id).execute(&mut *tx).await?;
272 
    tx.commit().await
273 
}
274
275 
/// The organisations whose only owner is this account: deleting it would
276 
/// leave them with nobody to manage them.
277 
pub async fn orgs_owned_only_by(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<String>> {
278 
    sqlx::query_scalar(
279 
        "SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id \
280 
         WHERE m.account_id = ? AND m.role = 'owner' \
281 
           AND (SELECT COUNT(*) FROM org_members x WHERE x.org_id = o.id AND x.role = 'owner') = 1 \
282 
         ORDER BY o.name",
283 
    )
284 
    .bind(account_id)
285 
    .fetch_all(pool)
286 
    .await
287 
}
288
289 
/// Whether `name` belonged to a deleted account.
290 
pub async fn username_retired(pool: &Pool, name: &str) -> sqlx::Result<bool> {
291 
    Ok(sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM retired_usernames WHERE username = ?").bind(name).fetch_one(pool).await? > 0)
19bb31ff Andy McCall ↶ 292 
}
293
294 
pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
295 
    sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
296 
    Ok(())
297 
}
298
299 
// ----------------------------------------------------------- organisations
300
301 
pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
302 
    sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
303 
}
304
305 
pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
306 
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
307 
}
308
a160b98f Andy McCall ↶ 309 
/// An organisation with the account's role in it.
310 
#[derive(FromRow)]
311 
struct Membership {
312 
    #[sqlx(flatten)]
313 
    org: Organisation,
314 
    role: String,
315 
}
316
19bb31ff Andy McCall ↶ 317 
pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
a160b98f Andy McCall ↶ 318 
    let rows: Vec<Membership> = sqlx::query_as(
319 
        "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 ↶ 320 
    )
321 
    .bind(account_id)
322 
    .fetch_all(pool)
323 
    .await?;
a160b98f Andy McCall ↶ 324 
    Ok(rows.into_iter().map(|m| (m.org, m.role)).collect())
19bb31ff Andy McCall ↶ 325 
}
326
327 
pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
941abdd6 Andy McCall ↶ 328 
    let mut tx = super::begin_write(pool).await?;
19bb31ff Andy McCall ↶ 329 
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
330 
        .bind(name)
331 
        .bind(display_name)
332 
        .bind(now_ms())
333 
        .fetch_one(&mut *tx)
334 
        .await?;
335 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
336 
        .bind(org.id)
337 
        .bind(owner)
338 
        .execute(&mut *tx)
339 
        .await?;
340 
    tx.commit().await?;
341 
    Ok(org)
342 
}
343
344 
pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
345 
    sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
346 
        .bind(display_name)
347 
        .bind(description)
348 
        .bind(default_role)
349 
        .bind(id)
350 
        .execute(pool)
351 
        .await?;
352 
    Ok(())
353 
}
354
355 
/// `(account, role)` for every member.
356 
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
357 
    let rows: Vec<(i64, String)> =
358 
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
359 
    let mut out = Vec::new();
360 
    for (id, role) in rows {
361 
        if let Some(a) = account_by_id(pool, id).await? {
362 
            out.push((a, role));
363 
        }
364 
    }
365 
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
366 
    Ok(out)
367 
}
368
369 
pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
370 
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
371 
        .bind(org_id)
372 
        .bind(account_id)
373 
        .fetch_optional(pool)
374 
        .await
375 
}
376
377 
pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
378 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
379 
        .bind(org_id)
380 
        .bind(account_id)
381 
        .bind(role)
382 
        .execute(pool)
383 
        .await?;
384 
    Ok(())
385 
}
386
387 
pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
388 
    sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
389 
    Ok(())
390 
}
391
392 
pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
393 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
394 
}
395
396 
pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
397 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
398 
}
399
400 
pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
401 
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
402 
}
403
404 
pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
405 
    sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
406 
    Ok(())
407 
}
408
409 
pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
410 
    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")
411 
        .bind(team_id)
412 
        .fetch_all(pool)
413 
        .await
414 
}
415
416 
pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
417 
    sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
418 
        .bind(team_id)
419 
        .bind(account_id)
420 
        .execute(pool)
421 
        .await?;
422 
    Ok(())
423 
}
424
425 
pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
426 
    sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
427 
    Ok(())
428 
}
429
430 
// ----------------------------------------------------------- collaborators
431
cb71e18e Andy McCall ↶ 432 
/// Who an issue of this repository can be assigned to, by username: its
433 
/// owner (an account), its collaborators (directly or through a team) and,
434 
/// when an organisation owns it, the organisation's members. Blocked
435 
/// accounts are left out.
436 
pub async fn assignable(pool: &Pool, repo_id: i64, owner: &str) -> sqlx::Result<Vec<String>> {
437 
    sqlx::query_scalar(
438 
        "SELECT username FROM accounts WHERE banned = 0 AND disabled = 0 AND id IN (\
439 
           SELECT id FROM accounts WHERE username = ?1 \
440 
           UNION SELECT account_id FROM collaborators WHERE repo_id = ?2 AND account_id IS NOT NULL \
441 
           UNION SELECT tm.account_id FROM team_members tm JOIN collaborators c ON c.team_id = tm.team_id WHERE c.repo_id = ?2 \
442 
           UNION SELECT m.account_id FROM org_members m JOIN organisations o ON o.id = m.org_id WHERE o.name = ?1) \
443 
         ORDER BY username COLLATE NOCASE",
444 
    )
445 
    .bind(owner)
446 
    .bind(repo_id)
447 
    .fetch_all(pool)
448 
    .await
449 
}
450
19bb31ff Andy McCall ↶ 451 
#[derive(Clone, Debug)]
452 
pub struct Collaborator {
453 
    pub account: Option<Account>,
454 
    pub team: Option<Team>,
455 
    pub role: String,
456 
}
457
458 
pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
459 
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
460 
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
461 
    let mut out = Vec::new();
462 
    for (account_id, team_id, role) in rows {
463 
        let account = match account_id {
464 
            Some(id) => account_by_id(pool, id).await?,
465 
            None => None,
466 
        };
467 
        let team = match team_id {
468 
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
469 
            None => None,
470 
        };
471 
        out.push(Collaborator { account, team, role });
472 
    }
473 
    Ok(out)
474 
}
475
476 
pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
477 
    if let Some(a) = account_id {
478 
        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")
479 
            .bind(repo_id)
480 
            .bind(a)
481 
            .bind(role)
482 
            .execute(pool)
483 
            .await?;
484 
    } else if let Some(t) = team_id {
485 
        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")
486 
            .bind(repo_id)
487 
            .bind(t)
488 
            .bind(role)
489 
            .execute(pool)
490 
            .await?;
491 
    }
492 
    Ok(())
493 
}
494
495 
pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
496 
    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 = ?))")
497 
        .bind(repo_id)
498 
        .bind(account_id)
499 
        .bind(team_id)
500 
        .execute(pool)
501 
        .await?;
502 
    Ok(())
503 
}
504
505 
/// The direct or team role an account holds on a repository, the highest.
506 
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
507 
    let rows: Vec<String> = sqlx::query_scalar(
508 
        "SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
509 
    )
510 
    .bind(repo_id)
511 
    .bind(account_id)
512 
    .bind(account_id)
513 
    .fetch_all(pool)
514 
    .await?;
515 
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
516 
}
517
518 
// ------------------------------------------------------------------ tokens
519
520 
pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
521 
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
bff9106e Andy McCall ↶ 522 
}
523
524 
/// Mark one of `account_id`'s tokens as an agent's, or not.
525 
pub async fn set_token_agent(pool: &Pool, account_id: i64, id: i64, agent: bool) -> sqlx::Result<bool> {
526 
    let r = sqlx::query("UPDATE tokens SET agent = ? WHERE id = ? AND account_id = ?")
527 
        .bind(agent)
528 
        .bind(id)
529 
        .bind(account_id)
530 
        .execute(pool)
531 
        .await?;
532 
    Ok(r.rows_affected() > 0)
19bb31ff Andy McCall ↶ 533 
}
534
535 
pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
536 
    sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
537 
}
538
539 
pub async fn create_token(
540 
    pool: &Pool,
541 
    account_id: i64,
542 
    name: &str,
543 
    hash: &str,
544 
    scopes: &str,
545 
    repos: &str,
546 
    expires_ms: Option<i64>,
547 
) -> sqlx::Result<Token> {
548 
    sqlx::query_as(
549 
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
550 
    )
551 
    .bind(account_id)
552 
    .bind(name)
553 
    .bind(hash)
554 
    .bind(scopes)
555 
    .bind(repos)
556 
    .bind(now_ms())
557 
    .bind(expires_ms)
558 
    .fetch_one(pool)
559 
    .await
560 
}
561
562 
pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
563 
    sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
564 
    Ok(())
565 
}
566
60f11b71 Andy McCall ↶ 567 
/// Record a use; a renewing token's expiry moves to now plus its lifetime.
19bb31ff Andy McCall ↶ 568 
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
60f11b71 Andy McCall ↶ 569 
    sqlx::query(
570 
        "UPDATE tokens SET last_used_ms = ?1, \
571 
         expires_ms = CASE WHEN renew_ms IS NULL THEN expires_ms ELSE MAX(COALESCE(expires_ms, 0), ?1 + renew_ms) END \
572 
         WHERE id = ?2",
573 
    )
574 
    .bind(now_ms())
575 
    .bind(id)
576 
    .execute(pool)
577 
    .await?;
578 
    Ok(())
579 
}
580
581 
/// Make a token renewing: it lasts `renew_ms` past its last use.
582 
pub async fn set_token_renewal(pool: &Pool, id: i64, renew_ms: i64) -> sqlx::Result<()> {
583 
    sqlx::query("UPDATE tokens SET renew_ms = ? WHERE id = ?").bind(renew_ms).bind(id).execute(pool).await?;
19bb31ff Andy McCall ↶ 584 
    Ok(())
585 
}
586
df71c789 Andy McCall ↶ 587 
// ------------------------------------------------------- device sign-in
588
589 
/// A device code an app asked for (oxygenhub#67): pending until the user
590 
/// approves or denies it on the web.
591 
#[derive(Clone, Debug, FromRow)]
592 
pub struct DeviceCode {
593 
    pub id: i64,
594 
    pub device_hash: String,
595 
    pub user_code: String,
596 
    pub client: String,
597 
    pub scopes: String,
598 
    pub created_ms: i64,
599 
    pub expires_ms: i64,
600 
    pub last_poll_ms: i64,
601 
    pub account_id: Option<i64>,
602 
    pub approved_scopes: String,
603 
    pub denied: bool,
60f11b71 Andy McCall ↶ 604 
    /// The address that asked for the code, as the hub saw it.
605 
    pub requester: String,
df71c789 Andy McCall ↶ 606 
}
607
608 
impl DeviceCode {
609 
    pub fn scope_list(&self) -> Vec<&str> {
610 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
611 
    }
612 
}
613
614 
pub async fn create_device_code(
615 
    pool: &Pool,
616 
    device_hash: &str,
617 
    user_code: &str,
618 
    client: &str,
619 
    scopes: &str,
620 
    expires_ms: i64,
60f11b71 Andy McCall ↶ 621 
    requester: &str,
df71c789 Andy McCall ↶ 622 
) -> sqlx::Result<DeviceCode> {
623 
    // Expired codes go whenever a new one is made.
624 
    sqlx::query("DELETE FROM device_codes WHERE expires_ms < ?").bind(now_ms()).execute(pool).await?;
625 
    sqlx::query_as(
60f11b71 Andy McCall ↶ 626 
        "INSERT INTO device_codes (device_hash, user_code, client, scopes, created_ms, expires_ms, requester) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
df71c789 Andy McCall ↶ 627 
    )
628 
    .bind(device_hash)
629 
    .bind(user_code)
630 
    .bind(client)
631 
    .bind(scopes)
632 
    .bind(now_ms())
633 
    .bind(expires_ms)
60f11b71 Andy McCall ↶ 634 
    .bind(requester)
df71c789 Andy McCall ↶ 635 
    .fetch_one(pool)
636 
    .await
637 
}
638
639 
pub async fn device_by_user_code(pool: &Pool, user_code: &str) -> sqlx::Result<Option<DeviceCode>> {
640 
    sqlx::query_as("SELECT * FROM device_codes WHERE user_code = ?").bind(user_code).fetch_optional(pool).await
641 
}
642
643 
pub async fn device_by_hash(pool: &Pool, device_hash: &str) -> sqlx::Result<Option<DeviceCode>> {
644 
    sqlx::query_as("SELECT * FROM device_codes WHERE device_hash = ?").bind(device_hash).fetch_optional(pool).await
645 
}
646
647 
/// Approve a pending code for an account with these scopes; false when it
648 
/// was no longer pending.
649 
pub async fn approve_device(pool: &Pool, id: i64, account_id: i64, scopes: &str) -> sqlx::Result<bool> {
650 
    let r = sqlx::query(
651 
        "UPDATE device_codes SET account_id = ?, approved_scopes = ? WHERE id = ? AND account_id IS NULL AND denied = 0 AND expires_ms >= ?",
652 
    )
653 
    .bind(account_id)
654 
    .bind(scopes)
655 
    .bind(id)
656 
    .bind(now_ms())
657 
    .execute(pool)
658 
    .await?;
659 
    Ok(r.rows_affected() == 1)
660 
}
661
662 
pub async fn deny_device(pool: &Pool, id: i64) -> sqlx::Result<()> {
663 
    sqlx::query("UPDATE device_codes SET denied = 1 WHERE id = ? AND account_id IS NULL").bind(id).execute(pool).await?;
664 
    Ok(())
665 
}
666
667 
pub async fn device_polled(pool: &Pool, id: i64) -> sqlx::Result<()> {
668 
    sqlx::query("UPDATE device_codes SET last_poll_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
669 
    Ok(())
670 
}
671
672 
/// Remove a code; true when this call removed it (so one poll gets the
673 
/// token, however many race).
674 
pub async fn delete_device(pool: &Pool, id: i64) -> sqlx::Result<bool> {
675 
    let r = sqlx::query("DELETE FROM device_codes WHERE id = ?").bind(id).execute(pool).await?;
676 
    Ok(r.rows_affected() == 1)
677 
}
678
19bb31ff Andy McCall ↶ 679 
// -------------------------------------------------------------- identities
680
681 
pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
682 
    sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
683 
}
684
685 
pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
686 
    sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
687 
}
688
689 
/// Every linked key with its account's username, for attribution.
690 
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
691 
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
692 
}
693
694 
pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
695 
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
696 
        .bind(key)
697 
        .bind(account_id)
698 
        .bind(name)
699 
        .bind(email)
700 
        .bind(object)
701 
        .bind(now_ms())
702 
        .execute(pool)
703 
        .await?;
704 
    Ok(())
705 
}
706
707 
pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
708 
    sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
709 
    Ok(())
710 
}
711
712 
pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
713 
    sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
714 
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
715 
        .bind(account_id)
716 
        .bind(bytes_hex)
717 
        .bind(now_ms())
718 
        .fetch_one(pool)
719 
        .await?;
720 
    Ok(id)
721 
}
722
723 
/// Take (and delete) a pending challenge of this account.
724 
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
725 
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
726 
        .bind(id)
727 
        .bind(account_id)
728 
        .bind(now_ms() - 10 * 60 * 1000)
729 
        .fetch_optional(pool)
730 
        .await?;
731 
    sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
732 
    Ok(bytes)
733 
}
734
735 
// ------------------------------------------------------------- invitations
736
737 
pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
738 
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
739 
        .bind(code)
740 
        .bind(created_by)
741 
        .bind(now_ms())
742 
        .execute(pool)
743 
        .await?;
744 
    Ok(())
745 
}
746
747 
pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
748 
    let n: i64 =
749 
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
750 
    Ok(n > 0)
751 
}
752
753 
pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
754 
    sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
755 
    Ok(())
756 
}
757
758 
pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
759 
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
760 
}
761
762 
// ---------------------------------------------------------------- settings
763
764 
pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
765 
    sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
766 
}
767
768 
pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
769 
    sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
770 
        .bind(key)
771 
        .bind(value)
772 
        .execute(pool)
773 
        .await?;
774 
    Ok(())
775 
}
776
777 
// ------------------------------------------------------------------- audit
778
779 
pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
780 
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
781 
        .bind(now_ms())
782 
        .bind(actor.map(|a| a.id))
783 
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
784 
        .bind(action)
785 
        .bind(target)
786 
        .bind(detail)
787 
        .execute(pool)
788 
        .await?;
789 
    Ok(())
790 
}
791
e8e7158b Andy McCall ↶ 792 
/// A page of the audit log, newest first, older than `before` (an id) when given.
793 
pub async fn audit_page(pool: &Pool, before: Option<i64>, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
794 
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit WHERE (? IS NULL OR id < ?) ORDER BY id DESC LIMIT ?")
795 
        .bind(before)
796 
        .bind(before)
797 
        .bind(limit)
798 
        .fetch_all(pool)
799 
        .await
800 
}
801
19bb31ff Andy McCall ↶ 802 
pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
803 
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
804 
        .bind(limit)
805 
        .fetch_all(pool)
806 
        .await
807 
}
808
809 
/// The repositories an account can see beyond the public ones: its own,
810 
/// its organisations', and those it collaborates on.
811 
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
812 
    sqlx::query_scalar(
813 
        "SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
814 
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
815 
         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 = ?))",
816 
    )
817 
    .bind(account_id)
818 
    .bind(account_id)
819 
    .bind(account_id)
820 
    .bind(account_id)
821 
    .fetch_all(pool)
822 
    .await
34beb1a7 Andy McCall ↶ 823 
}
824
825 
// ------------------------------------------------------ account links
826
827 
/// A verification or reset link (hub spec §5.1.1).
828 
#[derive(Clone, Debug, FromRow)]
829 
pub struct AccountLink {
830 
    pub id: i64,
831 
    pub account_id: i64,
832 
    pub kind: String,
833 
    pub hash: String,
834 
    pub email: String,
835 
    pub created_by: Option<i64>,
836 
    pub created_ms: i64,
837 
    pub expires_ms: i64,
838 
    pub used_ms: Option<i64>,
839 
}
840
841 
/// Make a link; returns its secret (shown or sent once, stored as a hash).
842 
pub async fn create_link(
843 
    pool: &Pool,
844 
    account_id: i64,
845 
    kind: &str,
846 
    email: &str,
847 
    created_by: Option<i64>,
848 
    ttl_ms: i64,
849 
) -> sqlx::Result<String> {
850 
    let secret = crate::auth::random_hex(32);
851 
    let now = now_ms();
852 
    sqlx::query(
853 
        "INSERT INTO account_links (account_id, kind, hash, email, created_by, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?)",
854 
    )
855 
    .bind(account_id)
856 
    .bind(kind)
857 
    .bind(crate::auth::token_hash(&secret))
858 
    .bind(email)
859 
    .bind(created_by)
860 
    .bind(now)
861 
    .bind(now + ttl_ms)
862 
    .execute(pool)
863 
    .await?;
864 
    Ok(secret)
865 
}
866
867 
/// An unused, unexpired link of this kind with this secret.
868 
pub async fn live_link(pool: &Pool, kind: &str, secret: &str) -> sqlx::Result<Option<AccountLink>> {
869 
    sqlx::query_as("SELECT * FROM account_links WHERE kind = ? AND hash = ? AND used_ms IS NULL AND expires_ms > ?")
870 
        .bind(kind)
871 
        .bind(crate::auth::token_hash(secret))
872 
        .bind(now_ms())
873 
        .fetch_optional(pool)
874 
        .await
875 
}
876
877 
/// Consume a link; false when it was already used (a race lost).
878 
pub async fn use_link(pool: &Pool, id: i64) -> sqlx::Result<bool> {
879 
    let r =
880 
        sqlx::query("UPDATE account_links SET used_ms = ? WHERE id = ? AND used_ms IS NULL").bind(now_ms()).bind(id).execute(pool).await?;
881 
    Ok(r.rows_affected() == 1)
882 
}
883
884 
/// Void every unused link of a kind for an account.
885 
pub async fn void_links(pool: &Pool, account_id: i64, kind: &str) -> sqlx::Result<()> {
886 
    sqlx::query("UPDATE account_links SET used_ms = ? WHERE account_id = ? AND kind = ? AND used_ms IS NULL")
887 
        .bind(now_ms())
888 
        .bind(account_id)
889 
        .bind(kind)
890 
        .execute(pool)
891 
        .await?;
892 
    Ok(())
893 
}
894
895 
/// When the account's newest link of a kind (for this address, when given) was made, for rate limits.
896 
pub async fn last_link_ms(pool: &Pool, account_id: i64, kind: &str, email: Option<&str>) -> sqlx::Result<Option<i64>> {
897 
    match email {
898 
        Some(e) => {
899 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ? AND email = ?")
900 
                .bind(account_id)
901 
                .bind(kind)
902 
                .bind(e)
903 
                .fetch_one(pool)
904 
                .await
905 
        }
906 
        None => {
907 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ?")
908 
                .bind(account_id)
909 
                .bind(kind)
910 
                .fetch_one(pool)
911 
                .await
912 
        }
913 
    }
914 
}
915
916 
/// How many links of a kind the account got since `since_ms`.
917 
pub async fn links_since(pool: &Pool, account_id: i64, kind: &str, since_ms: i64) -> sqlx::Result<i64> {
918 
    sqlx::query_scalar("SELECT COUNT(*) FROM account_links WHERE account_id = ? AND kind = ? AND created_ms >= ?")
919 
        .bind(account_id)
920 
        .bind(kind)
921 
        .bind(since_ms)
922 
        .fetch_one(pool)
923 
        .await
924 
}
925
926 
pub async fn set_email_verified(pool: &Pool, id: i64, verified: bool) -> sqlx::Result<()> {
927 
    sqlx::query("UPDATE accounts SET email_verified = ? WHERE id = ?").bind(verified).bind(id).execute(pool).await?;
928 
    Ok(())
929 
}
930
931 
/// The account with this verified email (case-insensitive), if exactly one.
932 
pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {
933 
    let found: Vec<Account> = sqlx::query_as("SELECT * FROM accounts WHERE email_verified = 1 AND lower(email) = lower(?) LIMIT 2")
934 
        .bind(email)
935 
        .fetch_all(pool)
936 
        .await?;
937 
    Ok(if found.len() == 1 { found.into_iter().next() } else { None })
a160b98f Andy McCall ↶ 938 
}
939
940 
/// Set or clear the picture of an account (`account`) or organisation (`org`).
941 
pub async fn set_avatar(pool: &Pool, kind: &str, id: i64, value: &str) -> sqlx::Result<()> {
942 
    let table = if kind == "org" { "organisations" } else { "accounts" };
943 
    sqlx::query(&format!("UPDATE {table} SET avatar = ? WHERE id = ?")).bind(value).bind(id).execute(pool).await?;
944 
    Ok(())
19bb31ff Andy McCall ↶ 945 
}