crates/hub/src/db/accounts.rs at 26d0d091deb7 · andymccall/oxygenhub

# 26d0d091deb7 oxygenhub / crates / hub / src / db / accounts.rs
33.0 KiB · Rust · last changed Administration is a sectioned page with a left-hand menu 1 month ago source blame history raw
//! Accounts, organisations, teams, collaborators, identities, tokens,
//! invitations, settings and the audit log (hub spec §5).

use super::{now_ms, Pool};
use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]
pub struct Account {
    pub id: i64,
    pub username: String,
    pub display_name: String,
    pub email: String,
    pub email_verified: bool,
    pub password_hash: String,
    pub session_salt: String,
    pub totp_secret: Option<String>,
    pub recovery_codes: String,
    pub is_admin: bool,
    pub banned: bool,
    /// Disabled by an admin: refused like a ban, and enabled again (0031).
    #[sqlx(default)]
    pub disabled: bool,
    pub created_ms: i64,
    pub last_seen_ms: i64,
    pub email_notifications: bool,
    pub unsubscribe_key: String,
    /// Shown on the profile page (migration 0008).
    #[sqlx(default)]
    pub bio: String,
    #[sqlx(default)]
    pub website: String,
    #[sqlx(default)]
    pub location: String,
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
    pub avatar: String,
    /// Accounts elsewhere (migration 0039, #105): a Bluesky handle, a
    /// Mastodon `user@instance`, a GitHub username; empty when not set.
    #[sqlx(default)]
    pub bluesky: String,
    #[sqlx(default)]
    pub mastodon: String,
    #[sqlx(default)]
    pub github: String,
}

impl Account {
    /// Whether the account may not sign in or act: banned or disabled.
    pub fn blocked(&self) -> bool {
        self.banned || self.disabled
    }

    pub fn name(&self) -> &str {
        if self.display_name.is_empty() {
            &self.username
        } else {
            &self.display_name
        }
    }

    pub fn totp_enabled(&self) -> bool {
        self.totp_secret.is_some()
    }
}

#[derive(Clone, Debug, FromRow)]
pub struct Organisation {
    pub id: i64,
    pub name: String,
    pub display_name: String,
    pub description: String,
    pub default_role: String,
    pub created_ms: i64,
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
    pub avatar: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct Team {
    pub id: i64,
    pub org_id: i64,
    pub name: String,
}

#[derive(Clone, Debug, FromRow)]
pub struct Token {
    pub id: i64,
    pub account_id: i64,
    pub name: String,
    pub hash: String,
    pub scopes: String,
    pub repos: String,
    pub created_ms: i64,
    pub expires_ms: Option<i64>,
    pub last_used_ms: Option<i64>,
    /// A renewing token's lifetime: each use extends it to now plus this.
    pub renew_ms: Option<i64>,
}

impl Token {
    pub fn scope_list(&self) -> Vec<&str> {
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
    }

    pub fn has_scope(&self, scope: &str) -> bool {
        self.scope_list().contains(&scope)
    }

    /// Whether the token may touch `owner/name`: every repository when the
    /// list is empty.
    pub fn covers(&self, slug: &str) -> bool {
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
    }

    pub fn expired(&self, now: i64) -> bool {
        self.expires_ms.is_some_and(|e| e < now)
    }
}

#[derive(Clone, Debug, FromRow)]
pub struct LinkedIdentity {
    pub key: String,
    pub account_id: i64,
    pub name: String,
    pub email: String,
    pub object: Vec<u8>,
    pub linked_ms: i64,
}

#[derive(Clone, Debug, FromRow)]
pub struct AuditRow {
    pub id: i64,
    pub at_ms: i64,
    pub actor: String,
    pub action: String,
    pub target: String,
    pub detail: String,
}

// ---------------------------------------------------------------- accounts

pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
    sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
}

pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
    sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
}

pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
}

pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
}

pub async fn create_account(
    pool: &Pool,
    username: &str,
    email: &str,
    password_hash: &str,
    session_salt: &str,
    is_admin: bool,
) -> sqlx::Result<Account> {
    sqlx::query_as(
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(username)
    .bind(email)
    .bind(password_hash)
    .bind(session_salt)
    .bind(is_admin)
    .bind(now_ms())
    .fetch_one(pool)
    .await
}

pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
    // A changed address is unverified until its own link is used.
    sqlx::query(
        "UPDATE accounts SET display_name = ?, email_verified = CASE WHEN email = ? THEN email_verified ELSE 0 END, email = ? WHERE id = ?",
    )
    .bind(display_name)
    .bind(email)
    .bind(email)
    .bind(id)
    .execute(pool)
    .await?;
    Ok(())
}

/// The public profile fields.
/// The accounts elsewhere a profile shows (#105), already normalised.
pub async fn set_links(pool: &Pool, id: i64, bluesky: &str, mastodon: &str, github: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET bluesky = ?, mastodon = ?, github = ? WHERE id = ?")
        .bind(bluesky)
        .bind(mastodon)
        .bind(github)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_bio(pool: &Pool, id: i64, bio: &str, website: &str, location: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET bio = ?, website = ?, location = ? WHERE id = ?")
        .bind(bio)
        .bind(website)
        .bind(location)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
        .bind(password_hash)
        .bind(session_salt)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
        .bind(secret)
        .bind(recovery_codes)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn set_disabled(pool: &Pool, id: i64, disabled: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET disabled = ? WHERE id = ?").bind(disabled).bind(id).execute(pool).await?;
    Ok(())
}

/// Delete an account (oxygenhub#81): its row goes, and with it (by the
/// schema's cascades) its tokens, keys, sessions' salt, memberships, stars
/// and notifications; what it wrote keeps its author's name. The username
/// is retired so nobody can register it and pass for the account.
pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> {
    let mut tx = super::begin_write(pool).await?;
    sqlx::query("INSERT OR IGNORE INTO retired_usernames (username, retired_ms) VALUES (?, ?)")
        .bind(username)
        .bind(now_ms())
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM accounts WHERE id = ?").bind(id).execute(&mut *tx).await?;
    tx.commit().await
}

/// The organisations whose only owner is this account: deleting it would
/// leave them with nobody to manage them.
pub async fn orgs_owned_only_by(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar(
        "SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id \
         WHERE m.account_id = ? AND m.role = 'owner' \
           AND (SELECT COUNT(*) FROM org_members x WHERE x.org_id = o.id AND x.role = 'owner') = 1 \
         ORDER BY o.name",
    )
    .bind(account_id)
    .fetch_all(pool)
    .await
}

/// Whether `name` belonged to a deleted account.
pub async fn username_retired(pool: &Pool, name: &str) -> sqlx::Result<bool> {
    Ok(sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM retired_usernames WHERE username = ?").bind(name).fetch_one(pool).await? > 0)
}

pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(())
}

// ----------------------------------------------------------- organisations

pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
    sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
}

pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
}

/// An organisation with the account's role in it.
#[derive(FromRow)]
struct Membership {
    #[sqlx(flatten)]
    org: Organisation,
    role: String,
}

pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
    let rows: Vec<Membership> = sqlx::query_as(
        "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",
    )
    .bind(account_id)
    .fetch_all(pool)
    .await?;
    Ok(rows.into_iter().map(|m| (m.org, m.role)).collect())
}

pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
    let mut tx = super::begin_write(pool).await?;
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
        .bind(name)
        .bind(display_name)
        .bind(now_ms())
        .fetch_one(&mut *tx)
        .await?;
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
        .bind(org.id)
        .bind(owner)
        .execute(&mut *tx)
        .await?;
    tx.commit().await?;
    Ok(org)
}

pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
        .bind(display_name)
        .bind(description)
        .bind(default_role)
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

/// `(account, role)` for every member.
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
    let rows: Vec<(i64, String)> =
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
    let mut out = Vec::new();
    for (id, role) in rows {
        if let Some(a) = account_by_id(pool, id).await? {
            out.push((a, role));
        }
    }
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
    Ok(out)
}

pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
        .bind(org_id)
        .bind(account_id)
        .fetch_optional(pool)
        .await
}

pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
        .bind(org_id)
        .bind(account_id)
        .bind(role)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
    Ok(())
}

pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
}

pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
}

pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
}

pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
    Ok(())
}

pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
    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")
        .bind(team_id)
        .fetch_all(pool)
        .await
}

pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
    sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
        .bind(team_id)
        .bind(account_id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
    Ok(())
}

// ----------------------------------------------------------- collaborators

/// Who an issue of this repository can be assigned to, by username: its
/// owner (an account), its collaborators (directly or through a team) and,
/// when an organisation owns it, the organisation's members. Blocked
/// accounts are left out.
pub async fn assignable(pool: &Pool, repo_id: i64, owner: &str) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar(
        "SELECT username FROM accounts WHERE banned = 0 AND disabled = 0 AND id IN (\
           SELECT id FROM accounts WHERE username = ?1 \
           UNION SELECT account_id FROM collaborators WHERE repo_id = ?2 AND account_id IS NOT NULL \
           UNION SELECT tm.account_id FROM team_members tm JOIN collaborators c ON c.team_id = tm.team_id WHERE c.repo_id = ?2 \
           UNION SELECT m.account_id FROM org_members m JOIN organisations o ON o.id = m.org_id WHERE o.name = ?1) \
         ORDER BY username COLLATE NOCASE",
    )
    .bind(owner)
    .bind(repo_id)
    .fetch_all(pool)
    .await
}

#[derive(Clone, Debug)]
pub struct Collaborator {
    pub account: Option<Account>,
    pub team: Option<Team>,
    pub role: String,
}

pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
    let mut out = Vec::new();
    for (account_id, team_id, role) in rows {
        let account = match account_id {
            Some(id) => account_by_id(pool, id).await?,
            None => None,
        };
        let team = match team_id {
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
            None => None,
        };
        out.push(Collaborator { account, team, role });
    }
    Ok(out)
}

pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
    if let Some(a) = account_id {
        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")
            .bind(repo_id)
            .bind(a)
            .bind(role)
            .execute(pool)
            .await?;
    } else if let Some(t) = team_id {
        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")
            .bind(repo_id)
            .bind(t)
            .bind(role)
            .execute(pool)
            .await?;
    }
    Ok(())
}

pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
    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 = ?))")
        .bind(repo_id)
        .bind(account_id)
        .bind(team_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// The direct or team role an account holds on a repository, the highest.
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
    let rows: Vec<String> = sqlx::query_scalar(
        "SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
    )
    .bind(repo_id)
    .bind(account_id)
    .bind(account_id)
    .fetch_all(pool)
    .await?;
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
}

// ------------------------------------------------------------------ tokens

pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
}

pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
    sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
}

pub async fn create_token(
    pool: &Pool,
    account_id: i64,
    name: &str,
    hash: &str,
    scopes: &str,
    repos: &str,
    expires_ms: Option<i64>,
) -> sqlx::Result<Token> {
    sqlx::query_as(
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(account_id)
    .bind(name)
    .bind(hash)
    .bind(scopes)
    .bind(repos)
    .bind(now_ms())
    .bind(expires_ms)
    .fetch_one(pool)
    .await
}

pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
    Ok(())
}

/// Record a use; a renewing token's expiry moves to now plus its lifetime.
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query(
        "UPDATE tokens SET last_used_ms = ?1, \
         expires_ms = CASE WHEN renew_ms IS NULL THEN expires_ms ELSE MAX(COALESCE(expires_ms, 0), ?1 + renew_ms) END \
         WHERE id = ?2",
    )
    .bind(now_ms())
    .bind(id)
    .execute(pool)
    .await?;
    Ok(())
}

/// Make a token renewing: it lasts `renew_ms` past its last use.
pub async fn set_token_renewal(pool: &Pool, id: i64, renew_ms: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE tokens SET renew_ms = ? WHERE id = ?").bind(renew_ms).bind(id).execute(pool).await?;
    Ok(())
}

// ------------------------------------------------------- device sign-in

/// A device code an app asked for (oxygenhub#67): pending until the user
/// approves or denies it on the web.
#[derive(Clone, Debug, FromRow)]
pub struct DeviceCode {
    pub id: i64,
    pub device_hash: String,
    pub user_code: String,
    pub client: String,
    pub scopes: String,
    pub created_ms: i64,
    pub expires_ms: i64,
    pub last_poll_ms: i64,
    pub account_id: Option<i64>,
    pub approved_scopes: String,
    pub denied: bool,
    /// The address that asked for the code, as the hub saw it.
    pub requester: String,
}

impl DeviceCode {
    pub fn scope_list(&self) -> Vec<&str> {
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
    }
}

pub async fn create_device_code(
    pool: &Pool,
    device_hash: &str,
    user_code: &str,
    client: &str,
    scopes: &str,
    expires_ms: i64,
    requester: &str,
) -> sqlx::Result<DeviceCode> {
    // Expired codes go whenever a new one is made.
    sqlx::query("DELETE FROM device_codes WHERE expires_ms < ?").bind(now_ms()).execute(pool).await?;
    sqlx::query_as(
        "INSERT INTO device_codes (device_hash, user_code, client, scopes, created_ms, expires_ms, requester) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
    )
    .bind(device_hash)
    .bind(user_code)
    .bind(client)
    .bind(scopes)
    .bind(now_ms())
    .bind(expires_ms)
    .bind(requester)
    .fetch_one(pool)
    .await
}

pub async fn device_by_user_code(pool: &Pool, user_code: &str) -> sqlx::Result<Option<DeviceCode>> {
    sqlx::query_as("SELECT * FROM device_codes WHERE user_code = ?").bind(user_code).fetch_optional(pool).await
}

pub async fn device_by_hash(pool: &Pool, device_hash: &str) -> sqlx::Result<Option<DeviceCode>> {
    sqlx::query_as("SELECT * FROM device_codes WHERE device_hash = ?").bind(device_hash).fetch_optional(pool).await
}

/// Approve a pending code for an account with these scopes; false when it
/// was no longer pending.
pub async fn approve_device(pool: &Pool, id: i64, account_id: i64, scopes: &str) -> sqlx::Result<bool> {
    let r = sqlx::query(
        "UPDATE device_codes SET account_id = ?, approved_scopes = ? WHERE id = ? AND account_id IS NULL AND denied = 0 AND expires_ms >= ?",
    )
    .bind(account_id)
    .bind(scopes)
    .bind(id)
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(r.rows_affected() == 1)
}

pub async fn deny_device(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE device_codes SET denied = 1 WHERE id = ? AND account_id IS NULL").bind(id).execute(pool).await?;
    Ok(())
}

pub async fn device_polled(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE device_codes SET last_poll_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(())
}

/// Remove a code; true when this call removed it (so one poll gets the
/// token, however many race).
pub async fn delete_device(pool: &Pool, id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("DELETE FROM device_codes WHERE id = ?").bind(id).execute(pool).await?;
    Ok(r.rows_affected() == 1)
}

// -------------------------------------------------------------- identities

pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
    sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
}

pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
    sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
}

/// Every linked key with its account's username, for attribution.
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
}

pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
        .bind(key)
        .bind(account_id)
        .bind(name)
        .bind(email)
        .bind(object)
        .bind(now_ms())
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
    Ok(())
}

pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
    sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
        .bind(account_id)
        .bind(bytes_hex)
        .bind(now_ms())
        .fetch_one(pool)
        .await?;
    Ok(id)
}

/// Take (and delete) a pending challenge of this account.
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
        .bind(id)
        .bind(account_id)
        .bind(now_ms() - 10 * 60 * 1000)
        .fetch_optional(pool)
        .await?;
    sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
    Ok(bytes)
}

// ------------------------------------------------------------- invitations

pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
        .bind(code)
        .bind(created_by)
        .bind(now_ms())
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
    let n: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
    Ok(n > 0)
}

pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
    Ok(())
}

pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
}

// ---------------------------------------------------------------- settings

pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
}

pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
        .bind(key)
        .bind(value)
        .execute(pool)
        .await?;
    Ok(())
}

// ------------------------------------------------------------------- audit

pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
        .bind(now_ms())
        .bind(actor.map(|a| a.id))
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
        .bind(action)
        .bind(target)
        .bind(detail)
        .execute(pool)
        .await?;
    Ok(())
}

/// A page of the audit log, newest first, older than `before` (an id) when given.
pub async fn audit_page(pool: &Pool, before: Option<i64>, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit WHERE (? IS NULL OR id < ?) ORDER BY id DESC LIMIT ?")
        .bind(before)
        .bind(before)
        .bind(limit)
        .fetch_all(pool)
        .await
}

pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
        .bind(limit)
        .fetch_all(pool)
        .await
}

/// The repositories an account can see beyond the public ones: its own,
/// its organisations', and those it collaborates on.
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar(
        "SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
         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 = ?))",
    )
    .bind(account_id)
    .bind(account_id)
    .bind(account_id)
    .bind(account_id)
    .fetch_all(pool)
    .await
}

// ------------------------------------------------------ account links

/// A verification or reset link (hub spec §5.1.1).
#[derive(Clone, Debug, FromRow)]
pub struct AccountLink {
    pub id: i64,
    pub account_id: i64,
    pub kind: String,
    pub hash: String,
    pub email: String,
    pub created_by: Option<i64>,
    pub created_ms: i64,
    pub expires_ms: i64,
    pub used_ms: Option<i64>,
}

/// Make a link; returns its secret (shown or sent once, stored as a hash).
pub async fn create_link(
    pool: &Pool,
    account_id: i64,
    kind: &str,
    email: &str,
    created_by: Option<i64>,
    ttl_ms: i64,
) -> sqlx::Result<String> {
    let secret = crate::auth::random_hex(32);
    let now = now_ms();
    sqlx::query(
        "INSERT INTO account_links (account_id, kind, hash, email, created_by, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?)",
    )
    .bind(account_id)
    .bind(kind)
    .bind(crate::auth::token_hash(&secret))
    .bind(email)
    .bind(created_by)
    .bind(now)
    .bind(now + ttl_ms)
    .execute(pool)
    .await?;
    Ok(secret)
}

/// An unused, unexpired link of this kind with this secret.
pub async fn live_link(pool: &Pool, kind: &str, secret: &str) -> sqlx::Result<Option<AccountLink>> {
    sqlx::query_as("SELECT * FROM account_links WHERE kind = ? AND hash = ? AND used_ms IS NULL AND expires_ms > ?")
        .bind(kind)
        .bind(crate::auth::token_hash(secret))
        .bind(now_ms())
        .fetch_optional(pool)
        .await
}

/// Consume a link; false when it was already used (a race lost).
pub async fn use_link(pool: &Pool, id: i64) -> sqlx::Result<bool> {
    let r =
        sqlx::query("UPDATE account_links SET used_ms = ? WHERE id = ? AND used_ms IS NULL").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(r.rows_affected() == 1)
}

/// Void every unused link of a kind for an account.
pub async fn void_links(pool: &Pool, account_id: i64, kind: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE account_links SET used_ms = ? WHERE account_id = ? AND kind = ? AND used_ms IS NULL")
        .bind(now_ms())
        .bind(account_id)
        .bind(kind)
        .execute(pool)
        .await?;
    Ok(())
}

/// When the account's newest link of a kind (for this address, when given) was made, for rate limits.
pub async fn last_link_ms(pool: &Pool, account_id: i64, kind: &str, email: Option<&str>) -> sqlx::Result<Option<i64>> {
    match email {
        Some(e) => {
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ? AND email = ?")
                .bind(account_id)
                .bind(kind)
                .bind(e)
                .fetch_one(pool)
                .await
        }
        None => {
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ?")
                .bind(account_id)
                .bind(kind)
                .fetch_one(pool)
                .await
        }
    }
}

/// How many links of a kind the account got since `since_ms`.
pub async fn links_since(pool: &Pool, account_id: i64, kind: &str, since_ms: i64) -> sqlx::Result<i64> {
    sqlx::query_scalar("SELECT COUNT(*) FROM account_links WHERE account_id = ? AND kind = ? AND created_ms >= ?")
        .bind(account_id)
        .bind(kind)
        .bind(since_ms)
        .fetch_one(pool)
        .await
}

pub async fn set_email_verified(pool: &Pool, id: i64, verified: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE accounts SET email_verified = ? WHERE id = ?").bind(verified).bind(id).execute(pool).await?;
    Ok(())
}

/// The account with this verified email (case-insensitive), if exactly one.
pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {
    let found: Vec<Account> = sqlx::query_as("SELECT * FROM accounts WHERE email_verified = 1 AND lower(email) = lower(?) LIMIT 2")
        .bind(email)
        .fetch_all(pool)
        .await?;
    Ok(if found.len() == 1 { found.into_iter().next() } else { None })
}

/// Set or clear the picture of an account (`account`) or organisation (`org`).
pub async fn set_avatar(pool: &Pool, kind: &str, id: i64, value: &str) -> sqlx::Result<()> {
    let table = if kind == "org" { "organisations" } else { "accounts" };
    sqlx::query(&format!("UPDATE {table} SET avatar = ? WHERE id = ?")).bind(value).bind(id).execute(pool).await?;
    Ok(())
}
`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet
Keyboard
