sl_map_web/
groups.rs

1//! Database-level helpers for groups, memberships, and invitations.
2//!
3//! Higher-level permission checks live in [`crate::library`]; this module
4//! exposes the typed DB primitives those checks (and the route handlers)
5//! build on.
6
7use chrono::{DateTime, Utc};
8use serde::Serialize;
9use sqlx::SqlitePool;
10use uuid::Uuid;
11
12use crate::auth::uuid_from_bytes;
13use crate::error::Error;
14
15/// Role a user has within a group.
16#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize)]
17#[serde(rename_all = "lowercase")]
18pub enum GroupRole {
19    /// Full read/write access; can invite, remove, promote, demote, and
20    /// delete the group itself.
21    Owner,
22    /// Read-only access. Only sees finished renders in the group library.
23    Member,
24}
25
26impl GroupRole {
27    /// String form stored in the `group_memberships.role` column.
28    #[must_use]
29    pub const fn as_str(self) -> &'static str {
30        match self {
31            Self::Owner => "owner",
32            Self::Member => "member",
33        }
34    }
35
36    /// Parse a role string from the DB or a request body.
37    ///
38    /// # Errors
39    ///
40    /// Returns [`Error::BadRequest`] for any value other than `owner` or
41    /// `member`.
42    pub fn parse(raw: &str) -> Result<Self, Error> {
43        match raw {
44            "owner" => Ok(Self::Owner),
45            "member" => Ok(Self::Member),
46            other => Err(Error::BadRequest(format!("unknown role `{other}`"))),
47        }
48    }
49}
50
51/// Status of a group invitation.
52#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize)]
53#[serde(rename_all = "lowercase")]
54pub enum InvitationStatus {
55    /// Awaiting the invitee's decision.
56    Pending,
57    /// The invitee accepted; a membership row was created.
58    Accepted,
59    /// The invitee rejected the invitation.
60    Rejected,
61}
62
63impl InvitationStatus {
64    /// String form stored in the `group_invitations.status` column.
65    #[must_use]
66    pub const fn as_str(self) -> &'static str {
67        match self {
68            Self::Pending => "pending",
69            Self::Accepted => "accepted",
70            Self::Rejected => "rejected",
71        }
72    }
73}
74
75/// Public view of a group, returned by listing/get endpoints.
76#[derive(Debug, Clone, Serialize)]
77pub struct GroupView {
78    /// the group's identifier.
79    pub group_id: Uuid,
80    /// the group's display name.
81    pub name: String,
82    /// the avatar that created the group, or `None` if that account
83    /// has been deleted. The column is `ON DELETE SET NULL` so the
84    /// group survives the creator's account being removed.
85    pub created_by: Option<Uuid>,
86    /// when the group was created.
87    pub created_at: DateTime<Utc>,
88    /// when the group's metadata was last updated (e.g. renamed).
89    pub updated_at: DateTime<Utc>,
90    /// the calling user's role in this group.
91    pub my_role: GroupRole,
92}
93
94/// One member of a group with the user's display fields denormalised in.
95#[derive(Debug, Clone, Serialize)]
96pub struct GroupMemberView {
97    /// the member's UUID.
98    pub user_id: Uuid,
99    /// `firstname.lastname`.
100    pub username: String,
101    /// `Firstname Lastname`.
102    pub legacy_name: String,
103    /// the member's role in the group.
104    pub role: GroupRole,
105    /// when the membership row was created.
106    pub created_at: DateTime<Utc>,
107}
108
109/// Look up the role a given user has in a given group, or `None` if they are
110/// not a member.
111///
112/// # Errors
113///
114/// Returns [`Error::Database`] on lookup failure.
115pub async fn lookup_role(
116    db: &SqlitePool,
117    group_id: Uuid,
118    user_id: Uuid,
119) -> Result<Option<GroupRole>, Error> {
120    let row: Option<(String,)> =
121        sqlx::query_as("SELECT role FROM group_memberships WHERE group_id = ?1 AND user_id = ?2")
122            .bind(group_id.as_bytes().to_vec())
123            .bind(user_id.as_bytes().to_vec())
124            .fetch_optional(db)
125            .await
126            .map_err(|err| {
127                tracing::error!("group role lookup failed: {err}");
128                Error::Database
129            })?;
130    row.map(|(role,)| GroupRole::parse(&role)).transpose()
131}
132
133/// Atomically promote a member of a group to owner. Returns `true` if a
134/// row was updated, `false` if the user is not a member or is already an
135/// owner. The role guard lives in the WHERE clause so concurrent role
136/// changes cannot turn this into a downgrade.
137///
138/// # Errors
139///
140/// Returns [`Error::Database`] on update failure.
141pub async fn try_promote_member_to_owner(
142    db: &SqlitePool,
143    group_id: Uuid,
144    user_id: Uuid,
145) -> Result<bool, Error> {
146    let result = sqlx::query(
147        "UPDATE group_memberships SET role = 'owner' \
148         WHERE group_id = ?1 AND user_id = ?2 AND role = 'member'",
149    )
150    .bind(group_id.as_bytes().to_vec())
151    .bind(user_id.as_bytes().to_vec())
152    .execute(db)
153    .await
154    .map_err(|err| {
155        tracing::error!("promote to owner failed: {err}");
156        Error::Database
157    })?;
158    Ok(result.rows_affected() == 1)
159}
160
161/// Atomically demote the calling user from owner to member, refusing the
162/// demote if it would leave the group with zero owners. The owner-count
163/// check is folded into the WHERE clause so two concurrent self-demotes
164/// cannot both observe `owners == 2` and both succeed. Returns `true` if
165/// a row was updated.
166///
167/// # Errors
168///
169/// Returns [`Error::Database`] on update failure.
170pub async fn try_self_demote_owner(
171    db: &SqlitePool,
172    group_id: Uuid,
173    user_id: Uuid,
174) -> Result<bool, Error> {
175    let result = sqlx::query(
176        "UPDATE group_memberships SET role = 'member' \
177         WHERE group_id = ?1 AND user_id = ?2 AND role = 'owner' \
178           AND (SELECT COUNT(*) FROM group_memberships \
179                 WHERE group_id = ?1 AND role = 'owner') > 1",
180    )
181    .bind(group_id.as_bytes().to_vec())
182    .bind(user_id.as_bytes().to_vec())
183    .execute(db)
184    .await
185    .map_err(|err| {
186        tracing::error!("self-demote owner failed: {err}");
187        Error::Database
188    })?;
189    Ok(result.rows_affected() == 1)
190}
191
192/// Atomically remove a non-owner member from a group. The role guard is
193/// folded into the WHERE clause so a member promoted to owner between an
194/// upstream check and this call is never silently kicked. Returns `true`
195/// if a row was deleted.
196///
197/// # Errors
198///
199/// Returns [`Error::Database`] on delete failure.
200pub async fn try_remove_non_owner(
201    db: &SqlitePool,
202    group_id: Uuid,
203    user_id: Uuid,
204) -> Result<bool, Error> {
205    let result = sqlx::query(
206        "DELETE FROM group_memberships \
207         WHERE group_id = ?1 AND user_id = ?2 AND role = 'member'",
208    )
209    .bind(group_id.as_bytes().to_vec())
210    .bind(user_id.as_bytes().to_vec())
211    .execute(db)
212    .await
213    .map_err(|err| {
214        tracing::error!("remove non-owner member failed: {err}");
215        Error::Database
216    })?;
217    Ok(result.rows_affected() == 1)
218}
219
220/// Atomically remove the calling user from a group. If the caller is an
221/// owner the delete only fires when at least one other owner exists, so
222/// two concurrent leaves by the last two owners cannot both succeed.
223/// Returns `true` if a row was deleted.
224///
225/// # Errors
226///
227/// Returns [`Error::Database`] on delete failure.
228pub async fn try_leave(db: &SqlitePool, group_id: Uuid, user_id: Uuid) -> Result<bool, Error> {
229    let result = sqlx::query(
230        "DELETE FROM group_memberships \
231         WHERE group_id = ?1 AND user_id = ?2 \
232           AND (role <> 'owner' \
233                OR (SELECT COUNT(*) FROM group_memberships \
234                     WHERE group_id = ?1 AND role = 'owner') > 1)",
235    )
236    .bind(group_id.as_bytes().to_vec())
237    .bind(user_id.as_bytes().to_vec())
238    .execute(db)
239    .await
240    .map_err(|err| {
241        tracing::error!("leave group failed: {err}");
242        Error::Database
243    })?;
244    Ok(result.rows_affected() == 1)
245}
246
247/// Verify that the given group exists. Returns [`Error::NotFound`] if not.
248///
249/// # Errors
250///
251/// Returns [`Error::Database`] on lookup failure, [`Error::NotFound`] if the
252/// group does not exist.
253pub async fn require_exists(db: &SqlitePool, group_id: Uuid) -> Result<(), Error> {
254    let row: Option<(i64,)> = sqlx::query_as("SELECT 1 FROM groups WHERE group_id = ?1")
255        .bind(group_id.as_bytes().to_vec())
256        .fetch_optional(db)
257        .await
258        .map_err(|err| {
259            tracing::error!("group existence check failed: {err}");
260            Error::Database
261        })?;
262    if row.is_none() {
263        return Err(Error::NotFound(format!("group {group_id}")));
264    }
265    Ok(())
266}
267
268/// Insert a new group plus the creator's owner membership in one transaction.
269/// Returns the newly assigned `group_id`.
270///
271/// # Errors
272///
273/// Returns [`Error::Database`] on insert failure.
274pub async fn create_group(db: &SqlitePool, name: &str, creator: Uuid) -> Result<Uuid, Error> {
275    let group_id = Uuid::new_v4();
276    let now = Utc::now();
277    let mut tx = db.begin().await.map_err(|err| {
278        tracing::error!("begin tx for group create failed: {err}");
279        Error::Database
280    })?;
281    sqlx::query(
282        "INSERT INTO groups (group_id, name, created_by, created_at, updated_at) \
283         VALUES (?1, ?2, ?3, ?4, ?4)",
284    )
285    .bind(group_id.as_bytes().to_vec())
286    .bind(name)
287    .bind(creator.as_bytes().to_vec())
288    .bind(now)
289    .execute(&mut *tx)
290    .await
291    .map_err(|err| {
292        tracing::error!("insert groups row failed: {err}");
293        Error::Database
294    })?;
295    sqlx::query(
296        "INSERT INTO group_memberships (group_id, user_id, role, created_at) \
297         VALUES (?1, ?2, 'owner', ?3)",
298    )
299    .bind(group_id.as_bytes().to_vec())
300    .bind(creator.as_bytes().to_vec())
301    .bind(now)
302    .execute(&mut *tx)
303    .await
304    .map_err(|err| {
305        tracing::error!("insert owner membership failed: {err}");
306        Error::Database
307    })?;
308    tx.commit().await.map_err(|err| {
309        tracing::error!("commit group create failed: {err}");
310        Error::Database
311    })?;
312    Ok(group_id)
313}
314
315/// Row shape for `list_members`: `(user_id, username, legacy_name, role,
316/// created_at)`.
317type MemberRow = (Vec<u8>, String, String, String, DateTime<Utc>);
318
319/// List all members of a group, joined with the users table for display
320/// fields.
321///
322/// # Errors
323///
324/// Returns [`Error::Database`] on query failure.
325pub async fn list_members(db: &SqlitePool, group_id: Uuid) -> Result<Vec<GroupMemberView>, Error> {
326    let rows: Vec<MemberRow> = sqlx::query_as(
327        "SELECT users.user_id, users.username, users.legacy_name, \
328                group_memberships.role, group_memberships.created_at \
329         FROM group_memberships \
330         JOIN users ON users.user_id = group_memberships.user_id \
331         WHERE group_memberships.group_id = ?1 \
332         ORDER BY group_memberships.role DESC, users.username ASC",
333    )
334    .bind(group_id.as_bytes().to_vec())
335    .fetch_all(db)
336    .await
337    .map_err(|err| {
338        tracing::error!("list members failed: {err}");
339        Error::Database
340    })?;
341    let mut out = Vec::with_capacity(rows.len());
342    for (uid_bytes, username, legacy_name, role, created_at) in rows {
343        let user_id =
344            uuid_from_bytes(&uid_bytes).ok_or_else(|| Error::BadRequest("bad uuid".to_owned()))?;
345        out.push(GroupMemberView {
346            user_id,
347            username,
348            legacy_name,
349            role: GroupRole::parse(&role)?,
350            created_at,
351        });
352    }
353    Ok(out)
354}
355
356/// Row shape for `list_for_user`: `(group_id, name, created_by, created_at,
357/// updated_at, my_role)`. `created_by` is nullable because the FK is
358/// `ON DELETE SET NULL` — the group survives the creator's account
359/// being deleted.
360type GroupRow = (
361    Vec<u8>,
362    String,
363    Option<Vec<u8>>,
364    DateTime<Utc>,
365    DateTime<Utc>,
366    String,
367);
368
369/// List the groups the user belongs to, with their role in each.
370///
371/// # Errors
372///
373/// Returns [`Error::Database`] on query failure.
374pub async fn list_for_user(db: &SqlitePool, user_id: Uuid) -> Result<Vec<GroupView>, Error> {
375    let rows: Vec<GroupRow> = sqlx::query_as(
376        "SELECT groups.group_id, groups.name, groups.created_by, \
377                groups.created_at, groups.updated_at, group_memberships.role \
378         FROM groups \
379         JOIN group_memberships ON group_memberships.group_id = groups.group_id \
380         WHERE group_memberships.user_id = ?1 \
381         ORDER BY groups.name ASC",
382    )
383    .bind(user_id.as_bytes().to_vec())
384    .fetch_all(db)
385    .await
386    .map_err(|err| {
387        tracing::error!("list groups for user failed: {err}");
388        Error::Database
389    })?;
390    let mut out = Vec::with_capacity(rows.len());
391    for (gid_bytes, name, created_by_bytes, created_at, updated_at, role) in rows {
392        let group_id = uuid_from_bytes(&gid_bytes)
393            .ok_or_else(|| Error::BadRequest("bad group uuid".to_owned()))?;
394        let created_by = created_by_bytes
395            .as_deref()
396            .map(uuid_from_bytes)
397            .map(|opt| opt.ok_or_else(|| Error::BadRequest("bad creator uuid".to_owned())))
398            .transpose()?;
399        out.push(GroupView {
400            group_id,
401            name,
402            created_by,
403            created_at,
404            updated_at,
405            my_role: GroupRole::parse(&role)?,
406        });
407    }
408    Ok(out)
409}
410
411/// Fetch a single group view if the user is a member of it.
412///
413/// # Errors
414///
415/// Returns [`Error::Database`] on query failure or [`Error::NotFound`] if the
416/// group does not exist or the user is not a member.
417pub async fn get_for_user(
418    db: &SqlitePool,
419    group_id: Uuid,
420    user_id: Uuid,
421) -> Result<GroupView, Error> {
422    type GetGroupRow = (
423        String,
424        Option<Vec<u8>>,
425        DateTime<Utc>,
426        DateTime<Utc>,
427        String,
428    );
429    let row: Option<GetGroupRow> = sqlx::query_as(
430        "SELECT groups.name, groups.created_by, groups.created_at, groups.updated_at, \
431                group_memberships.role \
432         FROM groups \
433         JOIN group_memberships ON group_memberships.group_id = groups.group_id \
434         WHERE groups.group_id = ?1 AND group_memberships.user_id = ?2",
435    )
436    .bind(group_id.as_bytes().to_vec())
437    .bind(user_id.as_bytes().to_vec())
438    .fetch_optional(db)
439    .await
440    .map_err(|err| {
441        tracing::error!("get group for user failed: {err}");
442        Error::Database
443    })?;
444    let (name, created_by_bytes, created_at, updated_at, role) =
445        row.ok_or_else(|| Error::NotFound(format!("group {group_id}")))?;
446    let created_by = created_by_bytes
447        .as_deref()
448        .map(uuid_from_bytes)
449        .map(|opt| opt.ok_or_else(|| Error::BadRequest("bad creator uuid".to_owned())))
450        .transpose()?;
451    Ok(GroupView {
452        group_id,
453        name,
454        created_by,
455        created_at,
456        updated_at,
457        my_role: GroupRole::parse(&role)?,
458    })
459}
460
461/// Rename a group, bumping `updated_at`.
462///
463/// # Errors
464///
465/// Returns [`Error::Database`] on update failure.
466pub async fn rename_group(db: &SqlitePool, group_id: Uuid, name: &str) -> Result<(), Error> {
467    let now = Utc::now();
468    sqlx::query("UPDATE groups SET name = ?1, updated_at = ?2 WHERE group_id = ?3")
469        .bind(name)
470        .bind(now)
471        .bind(group_id.as_bytes().to_vec())
472        .execute(db)
473        .await
474        .map_err(|err| {
475            tracing::error!("rename group failed: {err}");
476            Error::Database
477        })?;
478    Ok(())
479}
480
481/// Delete a group. Cascades remove memberships, pending invitations and any
482/// group-owned saved notecards / renders.
483///
484/// # Errors
485///
486/// Returns [`Error::Database`] on delete failure.
487pub async fn delete_group(db: &SqlitePool, group_id: Uuid) -> Result<(), Error> {
488    sqlx::query("DELETE FROM groups WHERE group_id = ?1")
489        .bind(group_id.as_bytes().to_vec())
490        .execute(db)
491        .await
492        .map_err(|err| {
493            tracing::error!("delete group failed: {err}");
494            Error::Database
495        })?;
496    Ok(())
497}
sl_map_web/groups.rs

sl_map_web/
groups.rs
