OXYGENHUB

Coverage / crates/hub/src/db/accounts.rs 91.6%

733 of 800 lines run, 174 of 388 functions · run #525 on main · run missed

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
    /// Disabled by an admin: refused like a ban, and enabled again (0031).
21
    #[sqlx(default)]
22
    pub disabled: bool,
23
    pub created_ms: i64,
24
    pub last_seen_ms: i64,
25
    pub email_notifications: bool,
26
    pub unsubscribe_key: String,
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,
34
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
35
    pub avatar: String,
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,
44
}
45
46
impl Account {
47
    /// Whether the account may not sign in or act: banned or disabled.
482656×
    pub fn blocked(&self) -> bool {
492656×
        self.banned || self.disabled
502656×
    }
51
526×
    pub fn name(&self) -> &str {
536×
        if self.display_name.is_empty() {
543×
            &self.username
55
        } else {
563×
            &self.display_name
57
        }
586×
    }
59
6033×
    pub fn totp_enabled(&self) -> bool {
6133×
        self.totp_secret.is_some()
6233×
    }
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,
73
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
74
    pub avatar: String,
75
    /// Its repositories' Flow priority (oxygenhub#244): `low`, `normal`,
76
    /// `high`, or empty to follow the site's (migration 0073).
77
    #[sqlx(default)]
78
    pub flow_priority: String,
79
}
80
810×
pub async fn set_org_flow_priority(pool: &Pool, org_id: i64, priority: &str) -> sqlx::Result<()> {
820×
    sqlx::query("UPDATE organisations SET flow_priority = $1 WHERE id = $2").bind(priority).bind(org_id).execute(pool).await?;
830×
    Ok(())
840×
}
85
86
#[derive(Clone, Debug, FromRow)]
87
pub struct Team {
88
    pub id: i64,
89
    pub org_id: i64,
90
    pub name: String,
91
}
92
93
#[derive(Clone, Debug, FromRow)]
94
pub struct Token {
95
    pub id: i64,
96
    pub account_id: i64,
97
    pub name: String,
98
    pub hash: String,
99
    pub scopes: String,
100
    pub repos: String,
101
    pub created_ms: i64,
102
    pub expires_ms: Option<i64>,
103
    pub last_used_ms: Option<i64>,
104
    /// A renewing token's lifetime: each use extends it to now plus this.
105
    pub renew_ms: Option<i64>,
106
    /// An agent uses it: what is done with it is told to its owner
107
    /// (migration 0065, oxygenhub#183).
108
    #[sqlx(default)]
109
    pub agent: bool,
110
}
111
112
impl Token {
113530×
    pub fn scope_list(&self) -> Vec<&str> {
114913×
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
115530×
    }
116
117316×
    pub fn has_scope(&self, scope: &str) -> bool {
118316×
        self.scope_list().contains(&scope)
119316×
    }
120
121
    /// Whether the token may touch `owner/name`: every repository when the
122
    /// list is empty.
123161×
    pub fn covers(&self, slug: &str) -> bool {
124161×
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
125161×
    }
126
127417×
    pub fn expired(&self, now: i64) -> bool {
128417×
        self.expires_ms.is_some_and(|e| e < now)
129417×
    }
130
}
131
132
#[derive(Clone, Debug, FromRow)]
133
pub struct LinkedIdentity {
134
    pub key: String,
135
    pub account_id: i64,
136
    pub name: String,
137
    pub email: String,
138
    pub object: Vec<u8>,
139
    pub linked_ms: i64,
140
    /// When the hub last received something this key signed (#286).
141
    #[sqlx(default)]
142
    pub last_used_ms: Option<i64>,
143
}
144
145
impl LinkedIdentity {
146
    /// When the identity itself expires, if it says.
1477×
    pub fn expires_ms(&self) -> Option<i64> {
1487×
        oxy_core::identity::Identity::decode(&self.object).ok()?.expires.map(|e| e as i64)
1497×
    }
150
}
151
152
#[derive(Clone, Debug, FromRow)]
153
pub struct AuditRow {
154
    pub id: i64,
155
    pub at_ms: i64,
156
    pub actor: String,
157
    pub action: String,
158
    pub target: String,
159
    pub detail: String,
160
}
161
162
// ---------------------------------------------------------------- accounts
163
1641147×
pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
1651147×
    sqlx::query_as("SELECT * FROM accounts WHERE username = $1").bind(username).fetch_optional(pool).await
1661147×
}
167
1681850×
pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
1691850×
    sqlx::query_as("SELECT * FROM accounts WHERE id = $1").bind(id).fetch_optional(pool).await
1701850×
}
171
172301×
pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
173301×
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
174301×
}
175
176348×
pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
177348×
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
178348×
}
179
180210×
pub async fn create_account(
181210×
    pool: &Pool,
182210×
    username: &str,
183210×
    email: &str,
184210×
    password_hash: &str,
185210×
    session_salt: &str,
186210×
    is_admin: bool,
187210×
) -> sqlx::Result<Account> {
188210×
    sqlx::query_as(
189210×
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *",
190210×
    )
191210×
    .bind(username)
192210×
    .bind(email)
193210×
    .bind(password_hash)
194210×
    .bind(session_salt)
195210×
    .bind(is_admin)
196210×
    .bind(now_ms())
197210×
    .fetch_one(pool)
198210×
    .await
199210×
}
200
2019×
pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
202
    // A changed address is unverified until its own link is used.
2039×
    sqlx::query(
2049×
        "UPDATE accounts SET display_name = $1, email_verified = CASE WHEN email = $2 THEN email_verified ELSE FALSE END, email = $3 WHERE id = $4",
2059×
    )
2069×
    .bind(display_name)
2079×
    .bind(email)
2089×
    .bind(email)
2099×
    .bind(id)
2109×
    .execute(pool)
2119×
    .await?;
2129×
    Ok(())
2139×
}
214
215
/// The public profile fields.
216
/// The accounts elsewhere a profile shows (#105), already normalised.
2176×
pub async fn set_links(pool: &Pool, id: i64, bluesky: &str, mastodon: &str, github: &str) -> sqlx::Result<()> {
2186×
    sqlx::query("UPDATE accounts SET bluesky = $1, mastodon = $2, github = $3 WHERE id = $4")
2196×
        .bind(bluesky)
2206×
        .bind(mastodon)
2216×
        .bind(github)
2226×
        .bind(id)
2236×
        .execute(pool)
2246×
        .await?;
2256×
    Ok(())
2266×
}
227
2286×
pub async fn set_bio(pool: &Pool, id: i64, bio: &str, website: &str, location: &str) -> sqlx::Result<()> {
2296×
    sqlx::query("UPDATE accounts SET bio = $1, website = $2, location = $3 WHERE id = $4")
2306×
        .bind(bio)
2316×
        .bind(website)
2326×
        .bind(location)
2336×
        .bind(id)
2346×
        .execute(pool)
2356×
        .await?;
2366×
    Ok(())
2376×
}
238
2392×
pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
2402×
    sqlx::query("UPDATE accounts SET password_hash = $1, session_salt = $2 WHERE id = $3")
2412×
        .bind(password_hash)
2422×
        .bind(session_salt)
2432×
        .bind(id)
2442×
        .execute(pool)
2452×
        .await?;
2462×
    Ok(())
2472×
}
248
2491×
pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
2501×
    sqlx::query("UPDATE accounts SET session_salt = $1 WHERE id = $2").bind(session_salt).bind(id).execute(pool).await?;
2511×
    Ok(())
2521×
}
253
2541×
pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
2551×
    sqlx::query("UPDATE accounts SET totp_secret = $1, recovery_codes = $2 WHERE id = $3")
2561×
        .bind(secret)
2571×
        .bind(recovery_codes)
2581×
        .bind(id)
2591×
        .execute(pool)
2601×
        .await?;
2611×
    Ok(())
2621×
}
263
2647×
pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
2657×
    sqlx::query("UPDATE accounts SET is_admin = $1 WHERE id = $2").bind(is_admin).bind(id).execute(pool).await?;
2667×
    Ok(())
2677×
}
268
2690×
pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
2700×
    sqlx::query("UPDATE accounts SET banned = $1 WHERE id = $2").bind(banned).bind(id).execute(pool).await?;
2710×
    Ok(())
2720×
}
273
2742×
pub async fn set_disabled(pool: &Pool, id: i64, disabled: bool) -> sqlx::Result<()> {
2752×
    sqlx::query("UPDATE accounts SET disabled = $1 WHERE id = $2").bind(disabled).bind(id).execute(pool).await?;
2762×
    Ok(())
2772×
}
278
279
/// Delete an account (oxygenhub#81): its row goes, and with it (by the
280
/// schema's cascades) its tokens, keys, sessions' salt, memberships, stars
281
/// and notifications; what it wrote keeps its author's name. The username
282
/// is retired so nobody can register it and pass for the account.
2831×
pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> {
2841×
    let mut tx = super::begin_write(pool).await?;
2851×
    sqlx::query("INSERT INTO retired_usernames (username, retired_ms) VALUES ($1, $2) ON CONFLICT DO NOTHING")
2861×
        .bind(username)
2871×
        .bind(now_ms())
2881×
        .execute(&mut *tx)
2891×
        .await?;
2901×
    sqlx::query("DELETE FROM accounts WHERE id = $1").bind(id).execute(&mut *tx).await?;
2911×
    tx.commit().await
2921×
}
293
294
/// The organisations whose only owner is this account: deleting it would
295
/// leave them with nobody to manage them.
2962×
pub async fn orgs_owned_only_by(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<String>> {
2972×
    sqlx::query_scalar(
2982×
        "SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id \
2992×
         WHERE m.account_id = $1 AND m.role = 'owner' \
3002×
           AND (SELECT COUNT(*) FROM org_members x WHERE x.org_id = o.id AND x.role = 'owner') = 1 \
3012×
         ORDER BY o.name",
3022×
    )
3032×
    .bind(account_id)
3042×
    .fetch_all(pool)
3052×
    .await
3062×
}
307
308
/// Whether `name` belonged to a deleted account.
309139×
pub async fn username_retired(pool: &Pool, name: &str) -> sqlx::Result<bool> {
310139×
    Ok(sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM retired_usernames WHERE username = $1").bind(name).fetch_one(pool).await? > 0)
311139×
}
312
3131113×
pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
3141113×
    sqlx::query("UPDATE accounts SET last_seen_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
3151113×
    Ok(())
3161113×
}
317
318
// ----------------------------------------------------------- organisations
319
3202633×
pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
3212633×
    sqlx::query_as("SELECT * FROM organisations WHERE name = $1").bind(name).fetch_optional(pool).await
3222633×
}
323
32496×
pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
32596×
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
32696×
}
327
328
/// An organisation with the account's role in it.
329
#[derive(FromRow)]
330
struct Membership {
331
    #[sqlx(flatten)]
332
    org: Organisation,
333
    role: String,
334
}
335
336130×
pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
337130×
    let rows: Vec<Membership> = sqlx::query_as(
338130×
        "SELECT o.*, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = $1 ORDER BY o.name",
339130×
    )
340130×
    .bind(account_id)
341130×
    .fetch_all(pool)
342130×
    .await?;
343130×
    Ok(rows.into_iter().map(|m| (m.org, m.role)).collect())
344130×
}
345
34614×
pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
34714×
    let mut tx = super::begin_write(pool).await?;
34814×
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES ($1, $2, $3) RETURNING *")
34914×
        .bind(name)
35014×
        .bind(display_name)
35114×
        .bind(now_ms())
35214×
        .fetch_one(&mut *tx)
35314×
        .await?;
35414×
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES ($1, $2, 'owner')")
35514×
        .bind(org.id)
35614×
        .bind(owner)
35714×
        .execute(&mut *tx)
35814×
        .await?;
35914×
    tx.commit().await?;
36014×
    Ok(org)
36114×
}
362
3630×
pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
3640×
    sqlx::query("UPDATE organisations SET display_name = $1, description = $2, default_role = $3 WHERE id = $4")
3650×
        .bind(display_name)
3660×
        .bind(description)
3670×
        .bind(default_role)
3680×
        .bind(id)
3690×
        .execute(pool)
3700×
        .await?;
3710×
    Ok(())
3720×
}
373
374
/// `(account, role)` for every member.
37537×
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
37637×
    let rows: Vec<(i64, String)> =
37737×
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = $1").bind(org_id).fetch_all(pool).await?;
37837×
    let mut out = Vec::new();
37947×
    for (id, role) in rows {
38047×
        if let Some(a) = account_by_id(pool, id).await? {
38147×
            out.push((a, role));
38247×
        }
383
    }
38437×
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
38537×
    Ok(out)
38637×
}
387
38814×
pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
38914×
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = $1 AND account_id = $2")
39014×
        .bind(org_id)
39114×
        .bind(account_id)
39214×
        .fetch_optional(pool)
39314×
        .await
39414×
}
395
3963×
pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
3973×
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES ($1, $2, $3) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
3983×
        .bind(org_id)
3993×
        .bind(account_id)
4003×
        .bind(role)
4013×
        .execute(pool)
4023×
        .await?;
4033×
    Ok(())
4043×
}
405
4060×
pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
4070×
    sqlx::query("DELETE FROM org_members WHERE org_id = $1 AND account_id = $2").bind(org_id).bind(account_id).execute(pool).await?;
4080×
    Ok(())
4090×
}
410
41143×
pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
41243×
    sqlx::query_as("SELECT * FROM teams WHERE org_id = $1 ORDER BY name").bind(org_id).fetch_all(pool).await
41343×
}
414
4150×
pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
4160×
    sqlx::query_as("SELECT * FROM teams WHERE org_id = $1 AND name = $2").bind(org_id).bind(name).fetch_optional(pool).await
4170×
}
418
4191×
pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
4201×
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES ($1, $2) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
4211×
}
422
4230×
pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
4240×
    sqlx::query("DELETE FROM teams WHERE id = $1").bind(id).execute(pool).await?;
4250×
    Ok(())
4260×
}
427
4282×
pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
4292×
    sqlx::query_as("SELECT a.* FROM accounts a JOIN team_members t ON t.account_id = a.id WHERE t.team_id = $1 ORDER BY a.username")
4302×
        .bind(team_id)
4312×
        .fetch_all(pool)
4322×
        .await
4332×
}
434
4351×
pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
4361×
    sqlx::query("INSERT INTO team_members (team_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
4371×
        .bind(team_id)
4381×
        .bind(account_id)
4391×
        .execute(pool)
4401×
        .await?;
4411×
    Ok(())
4421×
}
443
4440×
pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
4450×
    sqlx::query("DELETE FROM team_members WHERE team_id = $1 AND account_id = $2").bind(team_id).bind(account_id).execute(pool).await?;
4460×
    Ok(())
4470×
}
448
449
// ----------------------------------------------------------- collaborators
450
451
/// Who an issue of this repository can be assigned to, by username: its
452
/// owner (an account), its collaborators (directly or through a team) and,
453
/// when an organisation owns it, the organisation's members. Blocked
454
/// accounts are left out.
45578×
pub async fn assignable(pool: &Pool, repo_id: i64, owner: &str) -> sqlx::Result<Vec<String>> {
45678×
    sqlx::query_scalar(
45778×
        "SELECT username FROM accounts WHERE banned = FALSE AND disabled = FALSE AND id IN (\
45878×
           SELECT id FROM accounts WHERE username = $1 \
45978×
           UNION SELECT account_id FROM collaborators WHERE repo_id = $2 AND account_id IS NOT NULL \
46078×
           UNION SELECT tm.account_id FROM team_members tm JOIN collaborators c ON c.team_id = tm.team_id WHERE c.repo_id = $2 \
46178×
           UNION SELECT m.account_id FROM org_members m JOIN organisations o ON o.id = m.org_id WHERE o.name = $1) \
46278×
         ORDER BY lower(username), username",
46378×
    )
46478×
    .bind(owner)
46578×
    .bind(repo_id)
46678×
    .fetch_all(pool)
46778×
    .await
46878×
}
469
470
#[derive(Clone, Debug)]
471
pub struct Collaborator {
472
    pub account: Option<Account>,
473
    pub team: Option<Team>,
474
    pub role: String,
475
}
476
477133×
pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
478133×
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
479133×
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;
480133×
    let mut out = Vec::new();
481133×
    for (account_id, team_id, role) in rows {
482122×
        let account = match account_id {
483122×
            Some(id) => account_by_id(pool, id).await?,
4840×
            None => None,
485
        };
486122×
        let team = match team_id {
4870×
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = $1").bind(id).fetch_optional(pool).await?,
488122×
            None => None,
489
        };
490122×
        out.push(Collaborator { account, team, role });
491
    }
492133×
    Ok(out)
493133×
}
494
4957×
pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
4967×
    if let Some(a) = account_id {
4977×
        sqlx::query("INSERT INTO collaborators (repo_id, account_id, role) VALUES ($1, $2, $3) ON CONFLICT (repo_id, account_id) WHERE account_id IS NOT NULL DO UPDATE SET role = excluded.role")
4987×
            .bind(repo_id)
4997×
            .bind(a)
5007×
            .bind(role)
5017×
            .execute(pool)
5027×
            .await?;
5030×
    } else if let Some(t) = team_id {
5040×
        sqlx::query("INSERT INTO collaborators (repo_id, team_id, role) VALUES ($1, $2, $3) ON CONFLICT (repo_id, team_id) WHERE team_id IS NOT NULL DO UPDATE SET role = excluded.role")
5050×
            .bind(repo_id)
5060×
            .bind(t)
5070×
            .bind(role)
5080×
            .execute(pool)
5090×
            .await?;
5100×
    }
5117×
    Ok(())
5127×
}
513
5140×
pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
5150×
    sqlx::query("DELETE FROM collaborators WHERE repo_id = $1 AND ((account_id IS NOT NULL AND account_id = $2) OR (team_id IS NOT NULL AND team_id = $3))")
5160×
        .bind(repo_id)
5170×
        .bind(account_id)
5180×
        .bind(team_id)
5190×
        .execute(pool)
5200×
        .await?;
5210×
    Ok(())
5220×
}
523
524
/// The direct or team role an account holds on a repository, the highest.
525182×
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
526182×
    let rows: Vec<String> = sqlx::query_scalar(
527182×
        "SELECT role FROM collaborators WHERE repo_id = $1 AND (account_id = $2 OR team_id IN (SELECT team_id FROM team_members WHERE account_id = $3))",
528182×
    )
529182×
    .bind(repo_id)
530182×
    .bind(account_id)
531182×
    .bind(account_id)
532182×
    .fetch_all(pool)
533182×
    .await?;
534182×
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
535182×
}
536
537
// ------------------------------------------------------------------ tokens
538
53939×
pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
54039×
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = $1 ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
54139×
}
542
543
/// Mark one of `account_id`'s tokens as an agent's, or not.
5443×
pub async fn set_token_agent(pool: &Pool, account_id: i64, id: i64, agent: bool) -> sqlx::Result<bool> {
5453×
    let r = sqlx::query("UPDATE tokens SET agent = $1 WHERE id = $2 AND account_id = $3")
5463×
        .bind(agent)
5473×
        .bind(id)
5483×
        .bind(account_id)
5493×
        .execute(pool)
5503×
        .await?;
5513×
    Ok(r.rows_affected() > 0)
5523×
}
553
5542202×
pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
5552202×
    sqlx::query_as("SELECT * FROM tokens WHERE hash = $1").bind(hash).fetch_optional(pool).await
5562202×
}
557
55848×
pub async fn create_token(
55948×
    pool: &Pool,
56048×
    account_id: i64,
56148×
    name: &str,
56248×
    hash: &str,
56348×
    scopes: &str,
56448×
    repos: &str,
56548×
    expires_ms: Option<i64>,
56648×
) -> sqlx::Result<Token> {
56748×
    sqlx::query_as(
56848×
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
56948×
    )
57048×
    .bind(account_id)
57148×
    .bind(name)
57248×
    .bind(hash)
57348×
    .bind(scopes)
57448×
    .bind(repos)
57548×
    .bind(now_ms())
57648×
    .bind(expires_ms)
57748×
    .fetch_one(pool)
57848×
    .await
57948×
}
580
5812×
pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
5822×
    sqlx::query("DELETE FROM tokens WHERE id = $1 AND account_id = $2").bind(id).bind(account_id).execute(pool).await?;
5832×
    Ok(())
5842×
}
585
586
/// Record a use; a renewing token's expiry moves to now plus its lifetime.
587390×
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
588390×
    sqlx::query(
589390×
        "UPDATE tokens SET last_used_ms = $1, \
590390×
         expires_ms = CASE WHEN renew_ms IS NULL THEN expires_ms WHEN COALESCE(expires_ms, 0) > $1 + renew_ms THEN expires_ms ELSE $1 + renew_ms END \
591390×
         WHERE id = $2",
592390×
    )
593390×
    .bind(now_ms())
594390×
    .bind(id)
595390×
    .execute(pool)
596390×
    .await?;
597390×
    Ok(())
598390×
}
599
600
/// Make a token renewing: it lasts `renew_ms` past its last use.
6011×
pub async fn set_token_renewal(pool: &Pool, id: i64, renew_ms: i64) -> sqlx::Result<()> {
6021×
    sqlx::query("UPDATE tokens SET renew_ms = $1 WHERE id = $2").bind(renew_ms).bind(id).execute(pool).await?;
6031×
    Ok(())
6041×
}
605
606
// ------------------------------------------------------- device sign-in
607
608
/// A device code an app asked for (oxygenhub#67): pending until the user
609
/// approves or denies it on the web.
610
#[derive(Clone, Debug, FromRow)]
611
pub struct DeviceCode {
612
    pub id: i64,
613
    pub device_hash: String,
614
    pub user_code: String,
615
    pub client: String,
616
    pub scopes: String,
617
    pub created_ms: i64,
618
    pub expires_ms: i64,
619
    pub last_poll_ms: i64,
620
    pub account_id: Option<i64>,
621
    pub approved_scopes: String,
622
    pub denied: bool,
623
    /// The address that asked for the code, as the hub saw it.
624
    pub requester: String,
625
}
626
627
impl DeviceCode {
62817×
    pub fn scope_list(&self) -> Vec<&str> {
62934×
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
63017×
    }
631
}
632
6333×
pub async fn create_device_code(
6343×
    pool: &Pool,
6353×
    device_hash: &str,
6363×
    user_code: &str,
6373×
    client: &str,
6383×
    scopes: &str,
6393×
    expires_ms: i64,
6403×
    requester: &str,
6413×
) -> sqlx::Result<DeviceCode> {
642
    // Expired codes go whenever a new one is made.
6433×
    sqlx::query("DELETE FROM device_codes WHERE expires_ms < $1").bind(now_ms()).execute(pool).await?;
6443×
    sqlx::query_as(
6453×
        "INSERT INTO device_codes (device_hash, user_code, client, scopes, created_ms, expires_ms, requester) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
6463×
    )
6473×
    .bind(device_hash)
6483×
    .bind(user_code)
6493×
    .bind(client)
6503×
    .bind(scopes)
6513×
    .bind(now_ms())
6523×
    .bind(expires_ms)
6533×
    .bind(requester)
6543×
    .fetch_one(pool)
6553×
    .await
6563×
}
657
6586×
pub async fn device_by_user_code(pool: &Pool, user_code: &str) -> sqlx::Result<Option<DeviceCode>> {
6596×
    sqlx::query_as("SELECT * FROM device_codes WHERE user_code = $1").bind(user_code).fetch_optional(pool).await
6606×
}
661
6628×
pub async fn device_by_hash(pool: &Pool, device_hash: &str) -> sqlx::Result<Option<DeviceCode>> {
6638×
    sqlx::query_as("SELECT * FROM device_codes WHERE device_hash = $1").bind(device_hash).fetch_optional(pool).await
6648×
}
665
666
/// Approve a pending code for an account with these scopes; false when it
667
/// was no longer pending.
6681×
pub async fn approve_device(pool: &Pool, id: i64, account_id: i64, scopes: &str) -> sqlx::Result<bool> {
6691×
    let r = sqlx::query(
6701×
        "UPDATE device_codes SET account_id = $1, approved_scopes = $2 WHERE id = $3 AND account_id IS NULL AND denied = FALSE AND expires_ms >= $4",
6711×
    )
6721×
    .bind(account_id)
6731×
    .bind(scopes)
6741×
    .bind(id)
6751×
    .bind(now_ms())
6761×
    .execute(pool)
6771×
    .await?;
6781×
    Ok(r.rows_affected() == 1)
6791×
}
680
6811×
pub async fn deny_device(pool: &Pool, id: i64) -> sqlx::Result<()> {
6821×
    sqlx::query("UPDATE device_codes SET denied = TRUE WHERE id = $1 AND account_id IS NULL").bind(id).execute(pool).await?;
6831×
    Ok(())
6841×
}
685
6862×
pub async fn device_polled(pool: &Pool, id: i64) -> sqlx::Result<()> {
6872×
    sqlx::query("UPDATE device_codes SET last_poll_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
6882×
    Ok(())
6892×
}
690
691
/// Remove a code; true when this call removed it (so one poll gets the
692
/// token, however many race).
6933×
pub async fn delete_device(pool: &Pool, id: i64) -> sqlx::Result<bool> {
6943×
    let r = sqlx::query("DELETE FROM device_codes WHERE id = $1").bind(id).execute(pool).await?;
6953×
    Ok(r.rows_affected() == 1)
6963×
}
697
698
// -------------------------------------------------------------- identities
699
700189×
pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
701189×
    sqlx::query_as("SELECT * FROM identities WHERE account_id = $1 ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
702189×
}
703
704
/// Note that the hub received something signed by each of `keys` (#286):
705
/// linked keys only, at most once a minute each.
70681×
pub async fn identities_used(pool: &Pool, keys: &[String]) -> sqlx::Result<()> {
70781×
    let now = now_ms();
70882×
    for k in keys {
70982×
        sqlx::query("UPDATE identities SET last_used_ms = $1 WHERE key = $2 AND (last_used_ms IS NULL OR last_used_ms < $3)")
71082×
            .bind(now)
71182×
            .bind(k)
71282×
            .bind(now - 60_000)
71382×
            .execute(pool)
71482×
            .await?;
715
    }
71681×
    Ok(())
71781×
}
718
71916×
pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
72016×
    sqlx::query_as("SELECT * FROM identities WHERE key = $1").bind(key).fetch_optional(pool).await
72116×
}
722
723
/// Every linked key with its account's username, for attribution.
724333×
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
725333×
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
726333×
}
727
72818×
pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
72918×
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES ($1, $2, $3, $4, $5, $6)")
73018×
        .bind(key)
73118×
        .bind(account_id)
73218×
        .bind(name)
73318×
        .bind(email)
73418×
        .bind(object)
73518×
        .bind(now_ms())
73618×
        .execute(pool)
73718×
        .await?;
73818×
    Ok(())
73918×
}
740
7410×
pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
7420×
    sqlx::query("DELETE FROM identities WHERE key = $1 AND account_id = $2").bind(key).bind(account_id).execute(pool).await?;
7430×
    Ok(())
7440×
}
745
7462×
pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
7472×
    sqlx::query("DELETE FROM challenges WHERE created_ms < $1").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
7482×
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES ($1, $2, $3) RETURNING id")
7492×
        .bind(account_id)
7502×
        .bind(bytes_hex)
7512×
        .bind(now_ms())
7522×
        .fetch_one(pool)
7532×
        .await?;
7542×
    Ok(id)
7552×
}
756
757
/// Take (and delete) a pending challenge of this account.
7583×
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
7593×
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = $1 AND account_id = $2 AND created_ms >= $3")
7603×
        .bind(id)
7613×
        .bind(account_id)
7623×
        .bind(now_ms() - 10 * 60 * 1000)
7633×
        .fetch_optional(pool)
7643×
        .await?;
7653×
    sqlx::query("DELETE FROM challenges WHERE id = $1").bind(id).execute(pool).await?;
7663×
    Ok(bytes)
7673×
}
768
769
// ------------------------------------------------------------- invitations
770
77110×
pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
77210×
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES ($1, $2, $3)")
77310×
        .bind(code)
77410×
        .bind(created_by)
77510×
        .bind(now_ms())
77610×
        .execute(pool)
77710×
        .await?;
77810×
    Ok(())
77910×
}
780
78115×
pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
78215×
    let n: i64 =
78315×
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = $1 AND used_by IS NULL").bind(code).fetch_one(pool).await?;
78415×
    Ok(n > 0)
78515×
}
786
787
/// Use an invitation up if it is still unused; whether it was.
7881×
pub async fn claim_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<bool> {
7891×
    let r = sqlx::query("UPDATE invitations SET used_by = $1 WHERE code = $2 AND used_by IS NULL")
7901×
        .bind(account_id)
7911×
        .bind(code)
7921×
        .execute(pool)
7931×
        .await?;
7941×
    Ok(r.rows_affected() > 0)
7951×
}
796
7978×
pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
7988×
    sqlx::query("UPDATE invitations SET used_by = $1 WHERE code = $2").bind(account_id).bind(code).execute(pool).await?;
7998×
    Ok(())
8008×
}
801
80296×
pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
80396×
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
80496×
}
805
806
// ---------------------------------------------------------------- settings
807
8082974×
pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
8092974×
    sqlx::query_scalar("SELECT value FROM settings WHERE key = $1").bind(key).fetch_optional(pool).await
8102974×
}
811
81262×
pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
81362×
    sqlx::query("INSERT INTO settings (key, value) VALUES ($1, $2) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
81462×
        .bind(key)
81562×
        .bind(value)
81662×
        .execute(pool)
81762×
        .await?;
81862×
    Ok(())
81962×
}
820
821
// ------------------------------------------------------------------- audit
822
823637×
pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
824637×
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES ($1, $2, $3, $4, $5, $6)")
825637×
        .bind(now_ms())
826637×
        .bind(actor.map(|a| a.id))
827637×
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
828637×
        .bind(action)
829637×
        .bind(target)
830637×
        .bind(detail)
831637×
        .execute(pool)
832637×
        .await?;
833637×
    Ok(())
834637×
}
835
836
/// A page of the audit log, newest first, older than `before` (an id) when given.
83796×
pub async fn audit_page(pool: &Pool, before: Option<i64>, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
83896×
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit WHERE ($1 IS NULL OR id < $2) ORDER BY id DESC LIMIT $3")
83996×
        .bind(before)
84096×
        .bind(before)
84196×
        .bind(limit)
84296×
        .fetch_all(pool)
84396×
        .await
84496×
}
845
84616×
pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
84716×
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT $1")
84816×
        .bind(limit)
84916×
        .fetch_all(pool)
85016×
        .await
85116×
}
852
853
/// The repositories an account can see beyond the public ones: its own,
854
/// its organisations', and those it collaborates on. Drops are not
855
/// repositories here (#279).
85642×
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
85742×
    sqlx::query_scalar(
85842×
        "SELECT r.id FROM repositories r WHERE r.kind = 'repo' AND (r.owner IN (SELECT username FROM accounts WHERE id = $1) \
85942×
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = $2) \
86042×
         OR r.id IN (SELECT repo_id FROM collaborators WHERE account_id = $3 OR team_id IN (SELECT team_id FROM team_members WHERE account_id = $4)))",
86142×
    )
86242×
    .bind(account_id)
86342×
    .bind(account_id)
86442×
    .bind(account_id)
86542×
    .bind(account_id)
86642×
    .fetch_all(pool)
86742×
    .await
86842×
}
869
870
// ------------------------------------------------------ account links
871
872
/// A verification or reset link (hub spec §5.1.1).
873
#[derive(Clone, Debug, FromRow)]
874
pub struct AccountLink {
875
    pub id: i64,
876
    pub account_id: i64,
877
    pub kind: String,
878
    pub hash: String,
879
    pub email: String,
880
    pub created_by: Option<i64>,
881
    pub created_ms: i64,
882
    pub expires_ms: i64,
883
    pub used_ms: Option<i64>,
884
}
885
886
/// Make a link; returns its secret (shown or sent once, stored as a hash).
8876×
pub async fn create_link(
8886×
    pool: &Pool,
8896×
    account_id: i64,
8906×
    kind: &str,
8916×
    email: &str,
8926×
    created_by: Option<i64>,
8936×
    ttl_ms: i64,
8946×
) -> sqlx::Result<String> {
8956×
    let secret = crate::auth::random_hex(32);
8966×
    let now = now_ms();
8976×
    sqlx::query(
8986×
        "INSERT INTO account_links (account_id, kind, hash, email, created_by, created_ms, expires_ms) VALUES ($1, $2, $3, $4, $5, $6, $7)",
8996×
    )
9006×
    .bind(account_id)
9016×
    .bind(kind)
9026×
    .bind(crate::auth::token_hash(&secret))
9036×
    .bind(email)
9046×
    .bind(created_by)
9056×
    .bind(now)
9066×
    .bind(now + ttl_ms)
9076×
    .execute(pool)
9086×
    .await?;
9096×
    Ok(secret)
9106×
}
911
912
/// An unused, unexpired link of this kind with this secret.
91311×
pub async fn live_link(pool: &Pool, kind: &str, secret: &str) -> sqlx::Result<Option<AccountLink>> {
91411×
    sqlx::query_as("SELECT * FROM account_links WHERE kind = $1 AND hash = $2 AND used_ms IS NULL AND expires_ms > $3")
91511×
        .bind(kind)
91611×
        .bind(crate::auth::token_hash(secret))
91711×
        .bind(now_ms())
91811×
        .fetch_optional(pool)
91911×
        .await
92011×
}
921
922
/// Consume a link; false when it was already used (a race lost).
9234×
pub async fn use_link(pool: &Pool, id: i64) -> sqlx::Result<bool> {
9244×
    let r = sqlx::query("UPDATE account_links SET used_ms = $1 WHERE id = $2 AND used_ms IS NULL")
9254×
        .bind(now_ms())
9264×
        .bind(id)
9274×
        .execute(pool)
9284×
        .await?;
9294×
    Ok(r.rows_affected() == 1)
9304×
}
931
932
/// Void every unused link of a kind for an account.
9332×
pub async fn void_links(pool: &Pool, account_id: i64, kind: &str) -> sqlx::Result<()> {
9342×
    sqlx::query("UPDATE account_links SET used_ms = $1 WHERE account_id = $2 AND kind = $3 AND used_ms IS NULL")
9352×
        .bind(now_ms())
9362×
        .bind(account_id)
9372×
        .bind(kind)
9382×
        .execute(pool)
9392×
        .await?;
9402×
    Ok(())
9412×
}
942
943
/// When the account's newest link of a kind (for this address, when given) was made, for rate limits.
9447×
pub async fn last_link_ms(pool: &Pool, account_id: i64, kind: &str, email: Option<&str>) -> sqlx::Result<Option<i64>> {
9457×
    match email {
9465×
        Some(e) => {
9475×
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = $1 AND kind = $2 AND email = $3")
9485×
                .bind(account_id)
9495×
                .bind(kind)
9505×
                .bind(e)
9515×
                .fetch_one(pool)
9525×
                .await
953
        }
954
        None => {
9552×
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = $1 AND kind = $2")
9562×
                .bind(account_id)
9572×
                .bind(kind)
9582×
                .fetch_one(pool)
9592×
                .await
960
        }
961
    }
9627×
}
963
964
/// How many links of a kind the account got since `since_ms`.
9654×
pub async fn links_since(pool: &Pool, account_id: i64, kind: &str, since_ms: i64) -> sqlx::Result<i64> {
9664×
    sqlx::query_scalar("SELECT COUNT(*) FROM account_links WHERE account_id = $1 AND kind = $2 AND created_ms >= $3")
9674×
        .bind(account_id)
9684×
        .bind(kind)
9694×
        .bind(since_ms)
9704×
        .fetch_one(pool)
9714×
        .await
9724×
}
973
9749×
pub async fn set_email_verified(pool: &Pool, id: i64, verified: bool) -> sqlx::Result<()> {
9759×
    sqlx::query("UPDATE accounts SET email_verified = $1 WHERE id = $2").bind(verified).bind(id).execute(pool).await?;
9769×
    Ok(())
9779×
}
978
979
/// The account with this verified email (case-insensitive), if exactly one.
9807×
pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {
9817×
    let found: Vec<Account> = sqlx::query_as("SELECT * FROM accounts WHERE email_verified = TRUE AND lower(email) = lower($1) LIMIT 2")
9827×
        .bind(email)
9837×
        .fetch_all(pool)
9847×
        .await?;
9857×
    Ok(if found.len() == 1 { found.into_iter().next() } else { None })
9867×
}
987
988
/// Set or clear the picture of an account (`account`) or organisation (`org`).
9893×
pub async fn set_avatar(pool: &Pool, kind: &str, id: i64, value: &str) -> sqlx::Result<()> {
9903×
    let table = if kind == "org" { "organisations" } else { "accounts" };
9913×
    sqlx::query(&format!("UPDATE {table} SET avatar = $1 WHERE id = $2")).bind(value).bind(id).execute(pool).await?;
9923×
    Ok(())
9933×
}
994
995
// ------------------------------------------------------------ agent grants
996
997
/// An agent an account lets act for it (oxygenhub#241, migration 0075).
998
#[derive(Clone, Debug, FromRow)]
999
pub struct AgentGrant {
1000
    pub id: i64,
1001
    pub account_id: i64,
1002
    pub agent: String,
1003
    pub subject: String,
1004
    /// The owner's KeyId (hex) that signed the grant.
1005
    pub grantor: String,
1006
    pub scope: String,
1007
    /// The signed Oxygen grant, as certificates carry it.
1008
    pub signed: Vec<u8>,
1009
    pub token_hash: String,
1010
    pub created_ms: i64,
1011
    pub expires_ms: i64,
1012
    pub revoked_ms: Option<i64>,
1013
    pub last_used_ms: Option<i64>,
1014
}
1015
1016
impl AgentGrant {
1017
    /// Whether certificates may still be issued under it at `now`.
101844×
    pub fn live(&self, now: i64) -> bool {
101944×
        self.revoked_ms.is_none() && now < self.expires_ms
102044×
    }
1021
}
1022
1023
pub struct NewAgentGrant<'a> {
1024
    pub account_id: i64,
1025
    pub agent: &'a str,
1026
    pub subject: &'a str,
1027
    pub grantor: &'a str,
1028
    pub scope: &'a str,
1029
    pub signed: &'a [u8],
1030
    pub token_hash: &'a str,
1031
    pub expires_ms: i64,
1032
}
1033
10341×
pub async fn create_agent_grant(pool: &Pool, g: NewAgentGrant<'_>) -> sqlx::Result<i64> {
10351×
    sqlx::query_scalar(
10361×
        "INSERT INTO agent_grants (account_id, agent, subject, grantor, scope, signed, token_hash, created_ms, expires_ms) \
10371×
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id",
10381×
    )
10391×
    .bind(g.account_id)
10401×
    .bind(g.agent)
10411×
    .bind(g.subject)
10421×
    .bind(g.grantor)
10431×
    .bind(g.scope)
10441×
    .bind(g.signed)
10451×
    .bind(g.token_hash)
10461×
    .bind(now_ms())
10471×
    .bind(g.expires_ms)
10481×
    .fetch_one(pool)
10491×
    .await
10501×
}
1051
10521×
pub async fn agent_grants_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<AgentGrant>> {
10531×
    sqlx::query_as("SELECT * FROM agent_grants WHERE account_id = $1 ORDER BY created_ms DESC, id DESC")
10541×
        .bind(account_id)
10551×
        .fetch_all(pool)
10561×
        .await
10571×
}
1058
105944×
pub async fn agent_grant_by_token(pool: &Pool, token_hash: &str) -> sqlx::Result<Option<AgentGrant>> {
106044×
    sqlx::query_as("SELECT * FROM agent_grants WHERE token_hash = $1").bind(token_hash).fetch_optional(pool).await
106144×
}
1062
106323×
pub async fn touch_agent_grant(pool: &Pool, id: i64) -> sqlx::Result<()> {
106423×
    sqlx::query("UPDATE agent_grants SET last_used_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
106523×
    Ok(())
106623×
}
1067
1068
/// Revoke one of the account's grants; true when this call revoked it.
10691×
pub async fn revoke_agent_grant(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<bool> {
10701×
    let r = sqlx::query("UPDATE agent_grants SET revoked_ms = $1 WHERE id = $2 AND account_id = $3 AND revoked_ms IS NULL")
10711×
        .bind(now_ms())
10721×
        .bind(id)
10731×
        .bind(account_id)
10741×
        .execute(pool)
10751×
        .await?;
10761×
    Ok(r.rows_affected() == 1)
10771×
}