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

# 42a0ba4bac4e oxygenhub / crates / hub / src / db / accounts.rs

20.1 KiB · Rust · last changed Milestone 0.4: issues and notifications 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,
    pub email_notifications: bool,
    pub unsubscribe_key: String,
}

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

Keyboard