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

# de505df5cf8d oxygenhub / crates / hub / src / db / accounts.rs

20.1 KiB · Rust · last changed Milestone 0.2: accounts and access 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,
    pub created_ms: i64,
    pub last_seen_ms: i64,
}

impl Account {
    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,
}

#[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>,
}

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<()> {
    sqlx::query("UPDATE accounts SET display_name = ?, email = ? WHERE id = ?")
        .bind(display_name)
        .bind(email)
        .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 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
}

pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
    let rows: Vec<(i64, String, String, String, String, i64, String)> = sqlx::query_as(
        "SELECT o.id, o.name, o.display_name, o.description, o.default_role, o.created_ms, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ? ORDER BY o.name",
    )
    .bind(account_id)
    .fetch_all(pool)
    .await?;
    Ok(rows
        .into_iter()
        .map(|(id, name, display_name, description, default_role, created_ms, role)| {
            (Organisation { id, name, display_name, description, default_role, created_ms }, role)
        })
        .collect())
}

pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
    let mut tx = pool.begin().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

#[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(())
}

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

// -------------------------------------------------------------- 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(())
}

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
}

`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet

1	//! Accounts, organisations, teams, collaborators, identities, tokens,
2	//! invitations, settings and the audit log (hub spec §5).
3
4	use super::{now_ms, Pool};
5	use sqlx::FromRow;
6
7	#[derive(Clone, Debug, FromRow)]
8	pub struct Account {
9	pub id: i64,
10	pub username: String,
11	pub display_name: String,
12	pub email: String,
13	pub email_verified: bool,
14	pub password_hash: String,
15	pub session_salt: String,
16	pub totp_secret: Option<String>,
17	pub recovery_codes: String,
18	pub is_admin: bool,
19	pub banned: bool,
20	pub created_ms: i64,
21	pub last_seen_ms: i64,
22	}
23
24	impl Account {
25	pub fn name(&self) -> &str {
26	if self.display_name.is_empty() {
27	&self.username
28	} else {
29	&self.display_name
30	}
31	}
32
33	pub fn totp_enabled(&self) -> bool {
34	self.totp_secret.is_some()
35	}
36	}
37
38	#[derive(Clone, Debug, FromRow)]
39	pub struct Organisation {
40	pub id: i64,
41	pub name: String,
42	pub display_name: String,
43	pub description: String,
44	pub default_role: String,
45	pub created_ms: i64,
46	}
47
48	#[derive(Clone, Debug, FromRow)]
49	pub struct Team {
50	pub id: i64,
51	pub org_id: i64,
52	pub name: String,
53	}
54
55	#[derive(Clone, Debug, FromRow)]
56	pub struct Token {
57	pub id: i64,
58	pub account_id: i64,
59	pub name: String,
60	pub hash: String,
61	pub scopes: String,
62	pub repos: String,
63	pub created_ms: i64,
64	pub expires_ms: Option<i64>,
65	pub last_used_ms: Option<i64>,
66	}
67
68	impl Token {
69	pub fn scope_list(&self) -> Vec<&str> {
70	self.scopes.split(',').filter(\|s\| !s.is_empty()).collect()
71	}
72
73	pub fn has_scope(&self, scope: &str) -> bool {
74	self.scope_list().contains(&scope)
75	}
76
77	/// Whether the token may touch `owner/name`: every repository when the
78	/// list is empty.
79	pub fn covers(&self, slug: &str) -> bool {
80	self.repos.is_empty() \|\| self.repos.split(',').any(\|r\| r.trim().eq_ignore_ascii_case(slug))
81	}
82
83	pub fn expired(&self, now: i64) -> bool {
84	self.expires_ms.is_some_and(\|e\| e < now)
85	}
86	}
87
88	#[derive(Clone, Debug, FromRow)]
89	pub struct LinkedIdentity {
90	pub key: String,
91	pub account_id: i64,
92	pub name: String,
93	pub email: String,
94	pub object: Vec<u8>,
95	pub linked_ms: i64,
96	}
97
98	#[derive(Clone, Debug, FromRow)]
99	pub struct AuditRow {
100	pub id: i64,
101	pub at_ms: i64,
102	pub actor: String,
103	pub action: String,
104	pub target: String,
105	pub detail: String,
106	}
107
108	// ---------------------------------------------------------------- accounts
109
110	pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
111	sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
112	}
113
114	pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
115	sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
116	}
117
118	pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
119	sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
120	}
121
122	pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
123	sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
124	}
125
126	pub async fn create_account(
127	pool: &Pool,
128	username: &str,
129	email: &str,
130	password_hash: &str,
131	session_salt: &str,
132	is_admin: bool,
133	) -> sqlx::Result<Account> {
134	sqlx::query_as(
135	"INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
136	)
137	.bind(username)
138	.bind(email)
139	.bind(password_hash)
140	.bind(session_salt)
141	.bind(is_admin)
142	.bind(now_ms())
143	.fetch_one(pool)
144	.await
145	}
146
147	pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
148	sqlx::query("UPDATE accounts SET display_name = ?, email = ? WHERE id = ?")
149	.bind(display_name)
150	.bind(email)
151	.bind(id)
152	.execute(pool)
153	.await?;
154	Ok(())
155	}
156
157	pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
158	sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
159	.bind(password_hash)
160	.bind(session_salt)
161	.bind(id)
162	.execute(pool)
163	.await?;
164	Ok(())
165	}
166
167	pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
168	sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
169	Ok(())
170	}
171
172	pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
173	sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
174	.bind(secret)
175	.bind(recovery_codes)
176	.bind(id)
177	.execute(pool)
178	.await?;
179	Ok(())
180	}
181
182	pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
183	sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
184	Ok(())
185	}
186
187	pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
188	sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
189	Ok(())
190	}
191
192	pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
193	sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
194	Ok(())
195	}
196
197	// ----------------------------------------------------------- organisations
198
199	pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
200	sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
201	}
202
203	pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
204	sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
205	}
206
207	pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
208	let rows: Vec<(i64, String, String, String, String, i64, String)> = sqlx::query_as(
209	"SELECT o.id, o.name, o.display_name, o.description, o.default_role, o.created_ms, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ? ORDER BY o.name",
210	)
211	.bind(account_id)
212	.fetch_all(pool)
213	.await?;
214	Ok(rows
215	.into_iter()
216	.map(\|(id, name, display_name, description, default_role, created_ms, role)\| {
217	(Organisation { id, name, display_name, description, default_role, created_ms }, role)
218	})
219	.collect())
220	}
221
222	pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
223	let mut tx = pool.begin().await?;
224	let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
225	.bind(name)
226	.bind(display_name)
227	.bind(now_ms())
228	.fetch_one(&mut *tx)
229	.await?;
230	sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
231	.bind(org.id)
232	.bind(owner)
233	.execute(&mut *tx)
234	.await?;
235	tx.commit().await?;
236	Ok(org)
237	}
238
239	pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
240	sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
241	.bind(display_name)
242	.bind(description)
243	.bind(default_role)
244	.bind(id)
245	.execute(pool)
246	.await?;
247	Ok(())
248	}
249
250	/// `(account, role)` for every member.
251	pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
252	let rows: Vec<(i64, String)> =
253	sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
254	let mut out = Vec::new();
255	for (id, role) in rows {
256	if let Some(a) = account_by_id(pool, id).await? {
257	out.push((a, role));
258	}
259	}
260	out.sort_by(\|a, b\| a.0.username.cmp(&b.0.username));
261	Ok(out)
262	}
263
264	pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
265	sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
266	.bind(org_id)
267	.bind(account_id)
268	.fetch_optional(pool)
269	.await
270	}
271
272	pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
273	sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
274	.bind(org_id)
275	.bind(account_id)
276	.bind(role)
277	.execute(pool)
278	.await?;
279	Ok(())
280	}
281
282	pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
283	sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
284	Ok(())
285	}
286
287	pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
288	sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
289	}
290
291	pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
292	sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
293	}
294
295	pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
296	sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
297	}
298
299	pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
300	sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
301	Ok(())
302	}
303
304	pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
305	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")
306	.bind(team_id)
307	.fetch_all(pool)
308	.await
309	}
310
311	pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
312	sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
313	.bind(team_id)
314	.bind(account_id)
315	.execute(pool)
316	.await?;
317	Ok(())
318	}
319
320	pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
321	sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
322	Ok(())
323	}
324
325	// ----------------------------------------------------------- collaborators
326
327	#[derive(Clone, Debug)]
328	pub struct Collaborator {
329	pub account: Option<Account>,
330	pub team: Option<Team>,
331	pub role: String,
332	}
333
334	pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
335	let rows: Vec<(Option<i64>, Option<i64>, String)> =
336	sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
337	let mut out = Vec::new();
338	for (account_id, team_id, role) in rows {
339	let account = match account_id {
340	Some(id) => account_by_id(pool, id).await?,
341	None => None,
342	};
343	let team = match team_id {
344	Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
345	None => None,
346	};
347	out.push(Collaborator { account, team, role });
348	}
349	Ok(out)
350	}
351
352	pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
353	if let Some(a) = account_id {
354	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")
355	.bind(repo_id)
356	.bind(a)
357	.bind(role)
358	.execute(pool)
359	.await?;
360	} else if let Some(t) = team_id {
361	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")
362	.bind(repo_id)
363	.bind(t)
364	.bind(role)
365	.execute(pool)
366	.await?;
367	}
368	Ok(())
369	}
370
371	pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
372	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 = ?))")
373	.bind(repo_id)
374	.bind(account_id)
375	.bind(team_id)
376	.execute(pool)
377	.await?;
378	Ok(())
379	}
380
381	/// The direct or team role an account holds on a repository, the highest.
382	pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
383	let rows: Vec<String> = sqlx::query_scalar(
384	"SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
385	)
386	.bind(repo_id)
387	.bind(account_id)
388	.bind(account_id)
389	.fetch_all(pool)
390	.await?;
391	Ok(rows.into_iter().max_by_key(\|r\| crate::auth::Role::parse(r).map(\|r\| r as u8).unwrap_or(0)))
392	}
393
394	// ------------------------------------------------------------------ tokens
395
396	pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
397	sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
398	}
399
400	pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
401	sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
402	}
403
404	pub async fn create_token(
405	pool: &Pool,
406	account_id: i64,
407	name: &str,
408	hash: &str,
409	scopes: &str,
410	repos: &str,
411	expires_ms: Option<i64>,
412	) -> sqlx::Result<Token> {
413	sqlx::query_as(
414	"INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
415	)
416	.bind(account_id)
417	.bind(name)
418	.bind(hash)
419	.bind(scopes)
420	.bind(repos)
421	.bind(now_ms())
422	.bind(expires_ms)
423	.fetch_one(pool)
424	.await
425	}
426
427	pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
428	sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
429	Ok(())
430	}
431
432	pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
433	sqlx::query("UPDATE tokens SET last_used_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
434	Ok(())
435	}
436
437	// -------------------------------------------------------------- identities
438
439	pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
440	sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
441	}
442
443	pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
444	sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
445	}
446
447	/// Every linked key with its account's username, for attribution.
448	pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
449	sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
450	}
451
452	pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
453	sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
454	.bind(key)
455	.bind(account_id)
456	.bind(name)
457	.bind(email)
458	.bind(object)
459	.bind(now_ms())
460	.execute(pool)
461	.await?;
462	Ok(())
463	}
464
465	pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
466	sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
467	Ok(())
468	}
469
470	pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
471	sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
472	let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
473	.bind(account_id)
474	.bind(bytes_hex)
475	.bind(now_ms())
476	.fetch_one(pool)
477	.await?;
478	Ok(id)
479	}
480
481	/// Take (and delete) a pending challenge of this account.
482	pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
483	let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
484	.bind(id)
485	.bind(account_id)
486	.bind(now_ms() - 10 * 60 * 1000)
487	.fetch_optional(pool)
488	.await?;
489	sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
490	Ok(bytes)
491	}
492
493	// ------------------------------------------------------------- invitations
494
495	pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
496	sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
497	.bind(code)
498	.bind(created_by)
499	.bind(now_ms())
500	.execute(pool)
501	.await?;
502	Ok(())
503	}
504
505	pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
506	let n: i64 =
507	sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
508	Ok(n > 0)
509	}
510
511	pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
512	sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
513	Ok(())
514	}
515
516	pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
517	sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
518	}
519
520	// ---------------------------------------------------------------- settings
521
522	pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
523	sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
524	}
525
526	pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
527	sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
528	.bind(key)
529	.bind(value)
530	.execute(pool)
531	.await?;
532	Ok(())
533	}
534
535	// ------------------------------------------------------------------- audit
536
537	pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
538	sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
539	.bind(now_ms())
540	.bind(actor.map(\|a\| a.id))
541	.bind(actor.map(\|a\| a.username.as_str()).unwrap_or(""))
542	.bind(action)
543	.bind(target)
544	.bind(detail)
545	.execute(pool)
546	.await?;
547	Ok(())
548	}
549
550	pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
551	sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
552	.bind(limit)
553	.fetch_all(pool)
554	.await
555	}
556
557	/// The repositories an account can see beyond the public ones: its own,
558	/// its organisations', and those it collaborates on.
559	pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
560	sqlx::query_scalar(
561	"SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
562	OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
563	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 = ?))",
564	)
565	.bind(account_id)
566	.bind(account_id)
567	.bind(account_id)
568	.bind(account_id)
569	.fetch_all(pool)
570	.await
571	}

Keyboard