teamcraft/services/
projects.rs

1//! Project groups (e.g. a product line, a machine, a customer project or a
2//! rover). CAD editors may only
3//! modify items of the project groups they are members of.
4//!
5//! Groups are self-service: every CAD editor can create one and becomes its
6//! manager; managers (and administrators) edit the group and its members.
7//! Linking a group to an identity-provider group (`oidc_group`, synchronised
8//! at every SSO login) stays with the administrators, because it grants
9//! membership to people outside TeamCraft's control.
10
11use chrono::Utc;
12use serde::Serialize;
13use serde_json::json;
14use sqlx::{PgConnection, PgPool};
15use uuid::Uuid;
16
17use crate::{
18    auth::Actor,
19    domain::{self, ItemType, ProjectVisibility, Role},
20    error::{AppError, AppResult},
21    models::{Project, ProjectMember},
22    services::{access, audit, users},
23};
24
25// There is no project delete. `items.project_id` is `ON DELETE SET NULL`, so
26// deleting a group would declassify all its items (items without a group are
27// readable by everyone): any future delete must refuse restricted groups that
28// still have items.
29const COLUMNS: &str = "id, code, name, description, active, created_at, oidc_group, created_by,
30    number_scheme, visibility,
31    (SELECT COUNT(*) FROM project_members pm WHERE pm.project_id = projects.id) AS member_count";
32
33/// The project groups the actor may see: restricted groups only for their
34/// members and administrators.
35pub async fn list(pool: &PgPool, actor: &Actor, only_active: bool) -> AppResult<Vec<Project>> {
36    Ok(sqlx::query_as::<_, Project>(&format!(
37        "SELECT {COLUMNS} FROM projects WHERE (active OR NOT $1) AND {}
38         ORDER BY active DESC, name",
39        access::project_visible("projects.id", 2)
40    ))
41    .bind(only_active)
42    .bind(&actor.hidden_projects)
43    .fetch_all(pool)
44    .await?)
45}
46
47/// Every project group, for the project overview of an administrator who
48/// manages groups they may not read (need-to-know policy): names, codes and
49/// member counts are management data, not content.
50pub async fn list_managed(pool: &PgPool, actor: &Actor) -> AppResult<Vec<Project>> {
51    if !actor.is_admin() {
52        return list(pool, actor, false).await;
53    }
54    Ok(sqlx::query_as::<_, Project>(&format!(
55        "SELECT {COLUMNS} FROM projects ORDER BY active DESC, name"
56    ))
57    .fetch_all(pool)
58    .await?)
59}
60
61/// The actor's own memberships whose group is restricted.
62pub async fn restricted_memberships(pool: &PgPool, actor: &Actor) -> AppResult<Vec<Uuid>> {
63    Ok(sqlx::query_scalar::<_, Uuid>(
64        "SELECT id FROM projects WHERE visibility = 'restricted' AND id = ANY($1) ORDER BY code",
65    )
66    .bind(&actor.projects)
67    .fetch_all(pool)
68    .await?)
69}
70
71/// Active projects in which the actor may create and modify items.
72pub async fn editable_by(pool: &PgPool, actor: &Actor) -> AppResult<Vec<Project>> {
73    Ok(list(pool, actor, true)
74        .await?
75        .into_iter()
76        .filter(|p| actor.can_edit_project(Some(p.id)))
77        .collect())
78}
79
80/// Projects a user is a member of.
81pub async fn memberships_of(pool: &PgPool, user_id: Uuid) -> AppResult<Vec<Project>> {
82    Ok(sqlx::query_as::<_, Project>(&format!(
83        "SELECT {COLUMNS} FROM projects
84         WHERE id IN (SELECT project_id FROM project_members WHERE user_id = $1)
85         ORDER BY name"
86    ))
87    .bind(user_id)
88    .fetch_all(pool)
89    .await?)
90}
91
92pub(crate) async fn get_unchecked(pool: &PgPool, id: Uuid) -> AppResult<Project> {
93    sqlx::query_as::<_, Project>(&format!("SELECT {COLUMNS} FROM projects WHERE id = $1"))
94        .bind(id)
95        .fetch_optional(pool)
96        .await?
97        .ok_or_else(access::not_found_project)
98}
99
100/// A project group the actor may see (restricted groups answer "not found"
101/// to non-members).
102pub async fn get(pool: &PgPool, actor: &Actor, id: Uuid) -> AppResult<Project> {
103    let project = get_unchecked(pool, id).await?;
104    access::ensure_visible(actor, Some(project.id), access::not_found_project())?;
105    Ok(project)
106}
107
108/// A project group for its maintenance (members, policy, settings): like
109/// [`get`], but an administrator also reaches a group their need-to-know
110/// policy hides. The record holds no content of the group.
111pub async fn get_for_management(pool: &PgPool, actor: &Actor, id: Uuid) -> AppResult<Project> {
112    if actor.is_admin() {
113        return get_unchecked(pool, id).await;
114    }
115    get(pool, actor, id).await
116}
117
118/// [`get_for_management`] by code.
119pub async fn get_by_code_for_management(
120    pool: &PgPool,
121    actor: &Actor,
122    code: &str,
123) -> AppResult<Project> {
124    if actor.is_admin() {
125        return get_by_code_unchecked(pool, code).await;
126    }
127    get_by_code(pool, actor, code).await
128}
129
130pub(crate) async fn get_by_code_unchecked(pool: &PgPool, code: &str) -> AppResult<Project> {
131    let code = domain::normalize_project_code(code)?;
132    sqlx::query_as::<_, Project>(&format!("SELECT {COLUMNS} FROM projects WHERE code = $1"))
133        .bind(&code)
134        .fetch_optional(pool)
135        .await?
136        .ok_or_else(|| AppError::not_found(format!("Project '{code}' not found.")))
137}
138
139/// A project group by code, if the actor may see it; a hidden group gives
140/// the message of an unknown code.
141pub async fn get_by_code(pool: &PgPool, actor: &Actor, code: &str) -> AppResult<Project> {
142    let project = get_by_code_unchecked(pool, code).await?;
143    access::ensure_visible(
144        actor,
145        Some(project.id),
146        AppError::not_found(format!("Project '{}' not found.", project.code)),
147    )?;
148    Ok(project)
149}
150
151/// Restricts a group to its members or opens it to all users again
152/// (managers of the group and administrators).
153///
154/// # Invariants
155///
156/// * **Permission:** the group is visible to the actor ("not found"
157///   otherwise) and the actor manages it (or is an administrator).
158/// * **Locks:** the group row (`FOR UPDATE`); item creation holds it
159///   `FOR SHARE`, so no number is drawn while the counters move.
160/// * **Data:** opening the group merges its private number counters back into
161///   the shared ones; the same visibility again changes nothing.
162/// * **Audit:** `project.visibility`.
163/// * **Notifications / webhooks:** none.
164/// * **Atomic:** one transaction (`tests/atomic_processes.rs`).
165pub async fn set_visibility(
166    pool: &PgPool,
167    actor: &Actor,
168    id: Uuid,
169    visibility: ProjectVisibility,
170) -> AppResult<Project> {
171    let mut tx = pool.begin().await?;
172    let current = sqlx::query_as::<_, Project>(&format!(
173        "SELECT {COLUMNS} FROM projects WHERE id = $1 FOR UPDATE"
174    ))
175    .bind(id)
176    .fetch_optional(&mut *tx)
177    .await?
178    .ok_or_else(access::not_found_project)?;
179    require_manager(&mut tx, actor, id).await?;
180    if current.visibility == visibility.as_str() {
181        tx.rollback().await?;
182        return Ok(current);
183    }
184    if visibility == ProjectVisibility::Internal {
185        super::project_policy::require_member_manager_under_policy(
186            &mut tx,
187            actor,
188            id,
189            "Opening the group to all users",
190        )
191        .await?;
192    }
193    let project = sqlx::query_as::<_, Project>(&format!(
194        "UPDATE projects SET visibility = $2 WHERE id = $1 RETURNING {COLUMNS}"
195    ))
196    .bind(id)
197    .bind(visibility.as_str())
198    .fetch_one(&mut *tx)
199    .await?;
200    if visibility == ProjectVisibility::Internal {
201        // the private counters go back into the shared ones (see
202        // `number_counter`); restricting starts them from the shared ones
203        let prefix = private_prefix(id);
204        sqlx::query(
205            "INSERT INTO counters (name, value)
206             SELECT 'num:' || substr(name, length($1) + 1), value FROM counters
207             WHERE left(name, length($1)) = $1
208             ON CONFLICT (name) DO UPDATE SET value = GREATEST(counters.value, EXCLUDED.value)",
209        )
210        .bind(&prefix)
211        .execute(&mut *tx)
212        .await?;
213        sqlx::query("DELETE FROM counters WHERE left(name, length($1)) = $1")
214            .bind(&prefix)
215            .execute(&mut *tx)
216            .await?;
217    }
218    let summary = match visibility {
219        ProjectVisibility::Restricted => format!("Restricted group {}", project.code),
220        ProjectVisibility::Internal => format!("Opened group {} to all users", project.code),
221    };
222    audit::record(
223        &mut tx,
224        Some(actor),
225        "project.visibility",
226        "project",
227        Some(id),
228        summary,
229        json!({ "visibility": visibility.as_str() }),
230    )
231    .await?;
232    tx.commit().await?;
233    Ok(project)
234}
235
236fn clean_group(group: Option<&str>) -> AppResult<Option<String>> {
237    match group.map(str::trim).filter(|g| !g.is_empty()) {
238        None => Ok(None),
239        Some(g) => Ok(Some(domain::limit_text("IdP group", g, 200)?)),
240    }
241}
242
243/// Whether the actor maintains the group: administrators always, otherwise
244/// the members marked as manager.
245pub async fn can_manage(
246    conn: &mut PgConnection,
247    actor: &Actor,
248    project_id: Uuid,
249) -> AppResult<bool> {
250    if actor.is_admin() {
251        return Ok(true);
252    }
253    if !actor.can_edit() {
254        return Ok(false);
255    }
256    Ok(sqlx::query_scalar::<_, bool>(
257        "SELECT EXISTS (SELECT 1 FROM project_members
258                        WHERE project_id = $1 AND user_id = $2 AND manager)",
259    )
260    .bind(project_id)
261    .bind(actor.id)
262    .fetch_one(&mut *conn)
263    .await?)
264}
265
266/// For pages: whether the actor may maintain the group.
267pub async fn is_manager(pool: &PgPool, actor: &Actor, project_id: Uuid) -> AppResult<bool> {
268    let mut conn = pool.acquire().await?;
269    can_manage(&mut conn, actor, project_id).await
270}
271
272pub(crate) async fn require_manager(
273    conn: &mut PgConnection,
274    actor: &Actor,
275    project_id: Uuid,
276) -> AppResult<()> {
277    // A hidden group answers like a missing one, never "only managers".
278    // Administrators maintain every group, also one whose need-to-know
279    // policy keeps them from reading it.
280    if !actor.is_admin() {
281        access::ensure_visible(actor, Some(project_id), access::not_found_project())?;
282    }
283    if can_manage(conn, actor, project_id).await? {
284        Ok(())
285    } else {
286        Err(AppError::Forbidden(
287            "Only the managers of this project group (and administrators) can change it.".into(),
288        ))
289    }
290}
291
292/// Creates a project group. CAD editors and administrators may do so; a CAD
293/// editor becomes member and manager of the new group.
294///
295/// # Invariants
296///
297/// * **Permission:** editor role or higher (403 otherwise); linking an
298///   identity-provider group is for administrators only (403).
299/// * **Locks:** none; a duplicate code is refused by the unique index (409).
300/// * **Data:** a non-administrator creator is added as a manual member and
301///   manager; administrators manage every group and are not added.
302/// * **Audit:** `project.create`.
303/// * **Notifications / webhooks:** none.
304/// * **Atomic:** one transaction.
305pub async fn create(
306    pool: &PgPool,
307    actor: &Actor,
308    code: &str,
309    name: &str,
310    description: &str,
311    oidc_group: Option<&str>,
312    visibility: ProjectVisibility,
313) -> AppResult<Project> {
314    actor.require(Role::Editor)?;
315    let code = domain::normalize_project_code(code)?;
316    let name = domain::require_text("Project name", name, 200)?;
317    let description = domain::limit_text("Description", description, 4000)?;
318    let oidc_group = clean_group(oidc_group)?;
319    if oidc_group.is_some() && !actor.is_admin() {
320        return Err(AppError::Forbidden(
321            "Only administrators link project groups to groups of the identity provider.".into(),
322        ));
323    }
324    let mut tx = pool.begin().await?;
325    let id = Uuid::new_v4();
326    sqlx::query(
327        "INSERT INTO projects (id, code, name, description, oidc_group, created_by, visibility)
328         VALUES ($1, $2, $3, $4, $5, $6, $7)",
329    )
330    .bind(id)
331    .bind(&code)
332    .bind(&name)
333    .bind(&description)
334    .bind(&oidc_group)
335    .bind(actor.id)
336    .bind(visibility.as_str())
337    .execute(&mut *tx)
338    .await
339    .map_err(|e| {
340        AppError::unique_violation(e, format!("A project with code '{code}' already exists."))
341    })?;
342    // the creator works in the group and maintains it (admins manage every
343    // group anyway and are not added)
344    if !actor.is_admin() {
345        sqlx::query(
346            "INSERT INTO project_members (project_id, user_id, source, manager)
347             VALUES ($1, $2, 'manual', true)",
348        )
349        .bind(id)
350        .bind(actor.id)
351        .execute(&mut *tx)
352        .await?;
353    }
354    audit::record(
355        &mut tx,
356        Some(actor),
357        "project.create",
358        "project",
359        Some(id),
360        format!("Created project {code} – {name}"),
361        json!({ "oidc_group": oidc_group, "visibility": visibility.as_str() }),
362    )
363    .await?;
364    let project =
365        sqlx::query_as::<_, Project>(&format!("SELECT {COLUMNS} FROM projects WHERE id = $1"))
366            .bind(id)
367            .fetch_one(&mut *tx)
368            .await?;
369    tx.commit().await?;
370    Ok(project)
371}
372
373pub struct ProjectUpdate<'a> {
374    pub name: &'a str,
375    pub description: &'a str,
376    pub active: bool,
377    /// Only administrators change it; `None` from others keeps the value.
378    pub oidc_group: Option<&'a str>,
379}
380
381/// Changes name, description, active flag and (administrators only) the
382/// linked identity-provider group of a project group.
383///
384/// # Invariants
385///
386/// * **Permission:** the group is visible to the actor ("not found"
387///   otherwise) and the actor manages it (or is an administrator; 403).
388/// * **Locks:** the group row (`FOR UPDATE`).
389/// * **Data:** for non-administrators the IdP group stays as it is.
390/// * **Audit:** `project.update`.
391/// * **Notifications / webhooks:** none.
392/// * **Atomic:** one transaction.
393pub async fn update(
394    pool: &PgPool,
395    actor: &Actor,
396    id: Uuid,
397    upd: ProjectUpdate<'_>,
398) -> AppResult<Project> {
399    let name = domain::require_text("Project name", upd.name, 200)?;
400    let description = domain::limit_text("Description", upd.description, 4000)?;
401    let mut tx = pool.begin().await?;
402    let current = sqlx::query_as::<_, Project>(&format!(
403        "SELECT {COLUMNS} FROM projects WHERE id = $1 FOR UPDATE"
404    ))
405    .bind(id)
406    .fetch_optional(&mut *tx)
407    .await?
408    .ok_or_else(|| AppError::not_found("Project not found."))?;
409    require_manager(&mut tx, actor, id).await?;
410    let oidc_group = if actor.is_admin() {
411        let wanted = clean_group(upd.oidc_group)?;
412        if wanted != current.oidc_group {
413            super::project_policy::require_member_manager_under_policy(
414                &mut tx,
415                actor,
416                id,
417                "Linking another identity-provider group",
418            )
419            .await?;
420        }
421        wanted
422    } else {
423        current.oidc_group.clone()
424    };
425    let project = sqlx::query_as::<_, Project>(&format!(
426        "UPDATE projects SET name = $2, description = $3, active = $4, oidc_group = $5
427         WHERE id = $1 RETURNING {COLUMNS}"
428    ))
429    .bind(id)
430    .bind(&name)
431    .bind(&description)
432    .bind(upd.active)
433    .bind(&oidc_group)
434    .fetch_one(&mut *tx)
435    .await?;
436    audit::record(
437        &mut tx,
438        Some(actor),
439        "project.update",
440        "project",
441        Some(id),
442        format!("Updated project {}", project.code),
443        json!({ "active": upd.active, "oidc_group": oidc_group }),
444    )
445    .await?;
446    tx.commit().await?;
447    Ok(project)
448}
449
450/// Sets the numbering scheme of a project group (managers and admins); an
451/// empty scheme returns to the built-in `{TYPE}-{SEQ:6}`. Existing item
452/// numbers never change.
453///
454/// # Invariants
455///
456/// * **Permission:** the group is visible to the actor ("not found"
457///   otherwise) and the actor manages it (or is an administrator; 403).
458/// * **Locks:** the group row (`FOR UPDATE`).
459/// * **Audit:** `project.numbering` (scheme before and after).
460/// * **Notifications / webhooks:** none.
461/// * **Atomic:** one transaction.
462pub async fn set_number_scheme(
463    pool: &PgPool,
464    actor: &Actor,
465    id: Uuid,
466    raw: &str,
467) -> AppResult<Project> {
468    let scheme = match raw.trim() {
469        "" => None,
470        s => Some(domain::NumberScheme::parse(s)?.as_string()),
471    };
472    let mut tx = pool.begin().await?;
473    let current = sqlx::query_as::<_, Project>(&format!(
474        "SELECT {COLUMNS} FROM projects WHERE id = $1 FOR UPDATE"
475    ))
476    .bind(id)
477    .fetch_optional(&mut *tx)
478    .await?
479    .ok_or_else(|| AppError::not_found("Project not found."))?;
480    require_manager(&mut tx, actor, id).await?;
481    let project = sqlx::query_as::<_, Project>(&format!(
482        "UPDATE projects SET number_scheme = $2 WHERE id = $1 RETURNING {COLUMNS}"
483    ))
484    .bind(id)
485    .bind(&scheme)
486    .fetch_one(&mut *tx)
487    .await?;
488    audit::record(
489        &mut tx,
490        Some(actor),
491        "project.numbering",
492        "project",
493        Some(id),
494        format!(
495            "Numbering scheme of project {}: {}",
496            project.code,
497            scheme.as_deref().unwrap_or(domain::DEFAULT_NUMBER_SCHEME)
498        ),
499        json!({ "before": current.number_scheme, "after": scheme }),
500    )
501    .await?;
502    tx.commit().await?;
503    Ok(project)
504}
505
506/// What a numbering scheme gives in a project group.
507#[derive(Clone, Debug, Serialize)]
508pub struct NumberingPreview {
509    /// The scheme in canonical spelling.
510    pub scheme: String,
511    /// Whether it is the built-in scheme.
512    pub is_default: bool,
513    /// The next generated number per item type.
514    pub next: Vec<NextNumber>,
515}
516
517#[derive(Clone, Debug, Serialize)]
518pub struct NextNumber {
519    pub item_type: ItemType,
520    pub label: &'static str,
521    pub number: String,
522}
523
524/// Prefix of the private counters of a restricted group.
525fn private_prefix(project_id: Uuid) -> String {
526    format!("num@{project_id}:")
527}
528
529/// The counter that numbers `ctx` with `scheme` in a project group, and the
530/// shared counter it starts from (`None` for a shared counter).
531///
532/// Counters are named after the expanded scheme, so two groups whose schemes
533/// expand alike share one. A restricted group with a scheme of its own counts
534/// in a private counter instead (`num@<group id>:…`), so no other group can
535/// read or use up its numbers – whatever scheme the other group saves, and
536/// without any check that would tell it about the restricted group. The
537/// private counter starts at the shared one's value when it is first used
538/// (numbers counted while the group was internal), and is merged back into
539/// it when the group is opened to all users again. A restricted group with
540/// the built-in scheme shares the instance-wide counter (documented).
541pub(crate) fn number_counter(
542    project_id: Uuid,
543    visibility: &str,
544    scheme: &domain::NumberScheme,
545    ctx: &domain::NumberContext<'_>,
546) -> (String, Option<String>) {
547    let shared = scheme.counter_key(ctx);
548    if visibility == ProjectVisibility::Restricted.as_str()
549        && *scheme != domain::NumberScheme::default_scheme()
550    {
551        let rest = shared.strip_prefix("num:").unwrap_or(&shared);
552        (
553            format!("{}{rest}", private_prefix(project_id)),
554            Some(shared),
555        )
556    } else {
557        (shared, None)
558    }
559}
560
561/// The next value of a number counter (see [`number_counter`]).
562pub(crate) async fn next_number(
563    conn: &mut PgConnection,
564    counter: &str,
565    seed: Option<&str>,
566) -> AppResult<i64> {
567    if let Some(seed) = seed {
568        sqlx::query(
569            "INSERT INTO counters (name, value)
570             VALUES ($1, COALESCE((SELECT value FROM counters WHERE name = $2), 0))
571             ON CONFLICT (name) DO NOTHING",
572        )
573        .bind(counter)
574        .bind(seed)
575        .execute(&mut *conn)
576        .await?;
577    }
578    crate::services::next_counter(conn, counter).await
579}
580
581/// The numbers the next new items would get with `scheme` (the project's own
582/// scheme when `None`), without using up any. Manually entered numbers that
583/// are already taken are skipped when an item is created, so the real number
584/// can be higher.
585///
586/// A scheme typed in reads the counter the group would use with it; the
587/// counters of restricted groups with a scheme of their own are private
588/// (see `number_counter`), so no scheme reads them from another group.
589pub async fn numbering_preview(
590    pool: &PgPool,
591    project: &Project,
592    scheme: Option<&str>,
593) -> AppResult<NumberingPreview> {
594    let scheme = match scheme.map(str::trim).filter(|s| !s.is_empty()) {
595        Some(s) => domain::NumberScheme::parse(s)?,
596        None => domain::NumberScheme::of_project(project.number_scheme.as_deref())?,
597    };
598    let now = Utc::now();
599    let mut next = Vec::new();
600    for item_type in ItemType::ALL {
601        let ctx = domain::number_context(&project.code, item_type, now);
602        let (counter, seed) = number_counter(project.id, &project.visibility, &scheme, &ctx);
603        let used = sqlx::query_scalar::<_, Option<i64>>(
604            "SELECT COALESCE((SELECT value FROM counters WHERE name = $1),
605                             (SELECT value FROM counters WHERE name = $2))",
606        )
607        .bind(&counter)
608        .bind(&seed)
609        .fetch_one(pool)
610        .await?
611        .unwrap_or(0);
612        next.push(NextNumber {
613            item_type,
614            label: item_type.label(),
615            number: scheme.format(&ctx, used + 1),
616        });
617    }
618    Ok(NumberingPreview {
619        is_default: scheme == domain::NumberScheme::default_scheme(),
620        scheme: scheme.as_string(),
621        next,
622    })
623}
624
625pub async fn members(pool: &PgPool, project_id: Uuid) -> AppResult<Vec<ProjectMember>> {
626    Ok(sqlx::query_as::<_, ProjectMember>(
627        "SELECT u.id AS user_id, u.username, u.display_name, u.role, u.active, pm.source,
628                pm.added_at, pm.manager,
629                (SELECT COUNT(*) FROM revisions r JOIN items i ON i.id = r.item_id
630                 WHERE i.project_id = pm.project_id AND r.checked_out_by = u.id) AS checkouts
631         FROM project_members pm JOIN users u ON u.id = pm.user_id
632         WHERE pm.project_id = $1 ORDER BY pm.manager DESC, u.display_name",
633    )
634    .bind(project_id)
635    .fetch_all(pool)
636    .await?)
637}
638
639/// Somebody who still holds check-outs in a group without being a member of
640/// it any more (removed, or never one before the group was restricted).
641#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
642pub struct FormerHolder {
643    pub user_id: Uuid,
644    pub username: String,
645    pub display_name: String,
646    pub active: bool,
647    pub checkouts: i64,
648}
649
650/// The non-members holding check-outs in a group, for the managers' member
651/// list: their locks are orphaned and a manager may release them
652/// (`checkouts::release_held`). Bounded to 200 people.
653pub async fn former_holders(
654    pool: &PgPool,
655    actor: &Actor,
656    project_id: Uuid,
657) -> AppResult<Vec<FormerHolder>> {
658    let mut conn = pool.acquire().await?;
659    require_manager(&mut conn, actor, project_id).await?;
660    Ok(sqlx::query_as::<_, FormerHolder>(
661        "SELECT u.id AS user_id, u.username, u.display_name, u.active, COUNT(*) AS checkouts
662         FROM revisions r JOIN items i ON i.id = r.item_id JOIN users u ON u.id = r.checked_out_by
663         WHERE i.project_id = $1
664           AND NOT EXISTS (SELECT 1 FROM project_members pm
665                           WHERE pm.project_id = $1 AND pm.user_id = u.id)
666         GROUP BY u.id, u.username, u.display_name, u.active
667         ORDER BY u.display_name LIMIT 200",
668    )
669    .bind(project_id)
670    .fetch_all(&mut *conn)
671    .await?)
672}
673
674/// Active managers of the group other than `besides`: the "at least one
675/// manager" rule counts only these, since a deactivated manager cannot
676/// maintain the group any more (issue #173).
677async fn other_active_managers(
678    conn: &mut PgConnection,
679    project_id: Uuid,
680    besides: Uuid,
681) -> AppResult<i64> {
682    Ok(sqlx::query_scalar::<_, i64>(
683        "SELECT COUNT(*) FROM project_members pm JOIN users u ON u.id = pm.user_id
684         WHERE pm.project_id = $1 AND pm.manager AND u.active AND pm.user_id <> $2",
685    )
686    .bind(project_id)
687    .bind(besides)
688    .fetch_one(&mut *conn)
689    .await?)
690}
691
692/// Adds a user to a project group (managers and admins); `manager` makes
693/// them a manager as well (an existing member is updated).
694///
695/// # Invariants
696///
697/// * **Permission:** the group is visible to the actor ("not found"
698///   otherwise) and the actor manages it (or is an administrator; 403); an
699///   unknown username is refused (400).
700/// * **Locks:** the group row (`FOR UPDATE`) before the manager check, so a manager removed
701///   at the same moment is refused and membership changes of one group are serialised.
702/// * **Policy:** an administrator who is no member cannot add themselves (or make themselves
703///   manager) to a group whose need-to-know policy is in force (403).
704/// * **Data:** an existing membership keeps its source (an IdP-synchronised
705///   one stays one) and is only ever raised to manager here; lowering is
706///   [`demote_manager`].
707/// * **Audit:** `project.add_member`.
708/// * **Notifications / webhooks:** none.
709/// * **Atomic:** one transaction.
710pub async fn add_member(
711    pool: &PgPool,
712    actor: &Actor,
713    project_id: Uuid,
714    username: &str,
715    manager: bool,
716) -> AppResult<ProjectMember> {
717    let project = get_for_management(pool, actor, project_id).await?;
718    let user = users::find_by_username(pool, username)
719        .await?
720        .ok_or_else(|| AppError::BadRequest(format!("Unknown user '{}'.", username.trim())))?;
721    let mut tx = pool.begin().await?;
722    // serialise membership changes of this group (as `remove_member` does)
723    sqlx::query("SELECT id FROM projects WHERE id = $1 FOR UPDATE")
724        .bind(project_id)
725        .execute(&mut *tx)
726        .await?;
727    require_manager(&mut tx, actor, project_id).await?;
728    if user.id == actor.id {
729        super::project_policy::forbid_admin_self_add(&mut tx, actor, project_id, manager).await?;
730    }
731    // An existing membership keeps its source: one synchronised from the
732    // identity provider stays one (also when its holder is made a manager),
733    // so it still ends when the IdP drops the user from the group.
734    let changed = sqlx::query(
735        "INSERT INTO project_members (project_id, user_id, source, manager) VALUES ($1, $2, 'manual', $3)
736         ON CONFLICT (project_id, user_id)
737         DO UPDATE SET manager = project_members.manager OR EXCLUDED.manager",
738    )
739    .bind(project_id)
740    .bind(user.id)
741    .bind(manager)
742    .execute(&mut *tx)
743    .await?
744    .rows_affected();
745    if changed > 0 {
746        audit::record(
747            &mut tx,
748            Some(actor),
749            "project.add_member",
750            "project",
751            Some(project_id),
752            format!(
753                "Added {} to {}{}",
754                user.username,
755                project.code,
756                if manager { " as manager" } else { "" }
757            ),
758            json!({ "user_id": user.id, "manager": manager }),
759        )
760        .await?;
761        if user.id == actor.id {
762            super::project_policy::note_admin_self_add(&mut tx, actor, &project).await?;
763        }
764    }
765    tx.commit().await?;
766    members(pool, project_id)
767        .await?
768        .into_iter()
769        .find(|m| m.user_id == user.id)
770        .ok_or_else(|| AppError::not_found("Membership not found."))
771}
772
773/// Removes a member (managers and admins; everybody may leave a group).
774/// A manager cannot leave while no other active manager remains – an admin can still remove them.
775/// Returns how many check-outs the user still holds in the group.
776///
777/// # Invariants
778///
779/// * **Permission:** the group is visible to the actor ("not found"
780///   otherwise); removing someone else needs a manager (or an administrator;
781///   403), leaving needs nothing more.
782/// * **Locks:** the group row (`FOR UPDATE`), so membership changes of one
783///   group are serialised.
784/// * **Data:** a manager cannot be removed (nor leave) when no other
785///   *active* manager remains, except by an administrator (409); a
786///   membership synchronised from the identity provider cannot be removed
787///   here while its user is active (409) – a deactivated user's can, since
788///   SSO refuses them and it cannot come back; a non-member is "not found".
789///   Check-outs of the removed user are kept and only counted.
790/// * **Audit:** `project.remove_member`.
791/// * **Notifications / webhooks:** none.
792/// * **Atomic:** one transaction.
793pub async fn remove_member(
794    pool: &PgPool,
795    actor: &Actor,
796    project_id: Uuid,
797    user_id: Uuid,
798) -> AppResult<i64> {
799    let project = get_for_management(pool, actor, project_id).await?;
800    let mut tx = pool.begin().await?;
801    // serialise membership changes of this group
802    sqlx::query("SELECT id FROM projects WHERE id = $1 FOR UPDATE")
803        .bind(project_id)
804        .execute(&mut *tx)
805        .await?;
806    if user_id != actor.id {
807        require_manager(&mut tx, actor, project_id).await?;
808    }
809    let (is_manager, from_idp, active) = sqlx::query_as::<_, (bool, bool, bool)>(
810        "SELECT pm.manager, pm.source = 'oidc' AND p.oidc_group IS NOT NULL, u.active
811         FROM project_members pm JOIN projects p ON p.id = pm.project_id
812         JOIN users u ON u.id = pm.user_id
813         WHERE pm.project_id = $1 AND pm.user_id = $2",
814    )
815    .bind(project_id)
816    .bind(user_id)
817    .fetch_optional(&mut *tx)
818    .await?
819    .ok_or_else(|| AppError::not_found("This user is not a member of the project group."))?;
820    if !actor.is_admin()
821        && is_manager
822        && other_active_managers(&mut tx, project_id, user_id).await? == 0
823    {
824        return Err(AppError::Conflict(format!(
825            "{} needs at least one active manager – make someone else a manager first.",
826            project.code
827        )));
828    }
829    // A membership from the identity provider would come back at the user's
830    // next SSO login: removing it here would only look like revoking access.
831    // A deactivated user is refused at SSO login, so theirs cannot return.
832    if from_idp && active {
833        return Err(AppError::Conflict(format!(
834            "This membership comes from the identity-provider group {} and would return at the next SSO login. \
835             Remove the user from that group in the identity provider, or ask an administrator to unlink \
836             the group or deactivate the user.",
837            project.oidc_group.as_deref().unwrap_or("")
838        )));
839    }
840    let username = sqlx::query_scalar::<_, String>(
841        "DELETE FROM project_members pm USING users u
842         WHERE pm.project_id = $1 AND pm.user_id = $2 AND u.id = pm.user_id
843         RETURNING u.username",
844    )
845    .bind(project_id)
846    .bind(user_id)
847    .fetch_optional(&mut *tx)
848    .await?
849    .ok_or_else(|| AppError::not_found("This user is not a member of the project group."))?;
850    // Locks are not released automatically; say so, so a manager can cancel
851    // them (revisions::cancel_checkout allows that for former members).
852    let held = held_checkouts(&mut tx, project_id, user_id).await?;
853    let still = if held > 0 {
854        format!(
855            " ({username} still holds {held} check-outs in {})",
856            project.code
857        )
858    } else {
859        String::new()
860    };
861    audit::record(
862        &mut tx,
863        Some(actor),
864        "project.remove_member",
865        "project",
866        Some(project_id),
867        format!("Removed {username} from {}{still}", project.code),
868        json!({ "user_id": user_id, "checkouts": held }),
869    )
870    .await?;
871    tx.commit().await?;
872    Ok(held)
873}
874
875/// Lowers a manager to a plain member (managers and admins): the member keeps
876/// the membership and its source, so this works for IdP-synchronised
877/// memberships too, which cannot be removed while their user is active.
878///
879/// # Invariants
880///
881/// * **Permission:** the group is visible to the actor ("not found"
882///   otherwise) and the actor manages it (or is an administrator; 403).
883///   Managers may demote themselves.
884/// * **Locks:** the group row (`FOR UPDATE`), so membership changes of one
885///   group are serialised (as `add_member` / `remove_member`).
886/// * **Data:** the user is a manager of the group ("not found" for a
887///   non-member, 409 for a plain member); at least one other *active*
888///   manager remains, except when an administrator demotes (409).
889/// * **Audit:** `project.demote_manager`.
890/// * **Notifications / webhooks:** none.
891/// * **Atomic:** one transaction.
892pub async fn demote_manager(
893    pool: &PgPool,
894    actor: &Actor,
895    project_id: Uuid,
896    user_id: Uuid,
897) -> AppResult<()> {
898    let project = get_for_management(pool, actor, project_id).await?;
899    let mut tx = pool.begin().await?;
900    sqlx::query("SELECT id FROM projects WHERE id = $1 FOR UPDATE")
901        .bind(project_id)
902        .execute(&mut *tx)
903        .await?;
904    require_manager(&mut tx, actor, project_id).await?;
905    let (manager, username) = sqlx::query_as::<_, (bool, String)>(
906        "SELECT pm.manager, u.username FROM project_members pm JOIN users u ON u.id = pm.user_id
907         WHERE pm.project_id = $1 AND pm.user_id = $2",
908    )
909    .bind(project_id)
910    .bind(user_id)
911    .fetch_optional(&mut *tx)
912    .await?
913    .ok_or_else(|| AppError::not_found("This user is not a member of the project group."))?;
914    if !manager {
915        return Err(AppError::Conflict(format!(
916            "{username} is not a manager of {}.",
917            project.code
918        )));
919    }
920    if !actor.is_admin() && other_active_managers(&mut tx, project_id, user_id).await? == 0 {
921        return Err(AppError::Conflict(format!(
922            "{} needs at least one active manager – make someone else a manager first.",
923            project.code
924        )));
925    }
926    sqlx::query(
927        "UPDATE project_members SET manager = false WHERE project_id = $1 AND user_id = $2",
928    )
929    .bind(project_id)
930    .bind(user_id)
931    .execute(&mut *tx)
932    .await?;
933    audit::record(
934        &mut tx,
935        Some(actor),
936        "project.demote_manager",
937        "project",
938        Some(project_id),
939        format!("{username} is no longer a manager of {}", project.code),
940        json!({ "user_id": user_id }),
941    )
942    .await?;
943    tx.commit().await?;
944    Ok(())
945}
946
947/// Sets or clears when a membership is meant to end ("member until", issue
948/// #173 step 8). Only a flag on the season page: nothing removes the member
949/// when the date has passed.
950///
951/// # Invariants
952///
953/// * **Permission:** the group is visible to the actor ("not found"
954///   otherwise) and the actor manages it (or is an administrator; 403).
955/// * **Locks:** the group row (`FOR UPDATE`), as the other membership changes.
956/// * **Data:** the user is a member ("not found" otherwise).
957/// * **Audit:** `project.member_expiry`.
958/// * **Notifications / webhooks:** none.
959/// * **Atomic:** one transaction.
960pub async fn set_member_expiry(
961    pool: &PgPool,
962    actor: &Actor,
963    project_id: Uuid,
964    user_id: Uuid,
965    until: Option<chrono::NaiveDate>,
966) -> AppResult<()> {
967    let project = get_for_management(pool, actor, project_id).await?;
968    let mut tx = pool.begin().await?;
969    sqlx::query("SELECT id FROM projects WHERE id = $1 FOR UPDATE")
970        .bind(project_id)
971        .execute(&mut *tx)
972        .await?;
973    require_manager(&mut tx, actor, project_id).await?;
974    let username = sqlx::query_scalar::<_, String>(
975        "UPDATE project_members pm SET expires_at = $3 FROM users u
976         WHERE pm.project_id = $1 AND pm.user_id = $2 AND u.id = pm.user_id
977         RETURNING u.username",
978    )
979    .bind(project_id)
980    .bind(user_id)
981    .bind(until)
982    .fetch_optional(&mut *tx)
983    .await?
984    .ok_or_else(|| AppError::not_found("This user is not a member of the project group."))?;
985    audit::record(
986        &mut tx,
987        Some(actor),
988        "project.member_expiry",
989        "project",
990        Some(project_id),
991        match until {
992            Some(d) => format!("{username} is a member of {} until {d}", project.code),
993            None => format!("{username} is a member of {} without an end", project.code),
994        },
995        json!({ "user_id": user_id, "expires_at": until }),
996    )
997    .await?;
998    tx.commit().await?;
999    Ok(())
1000}
1001
1002/// Revisions of the group checked out by a user.
1003pub(crate) async fn held_checkouts(
1004    conn: &mut PgConnection,
1005    project_id: Uuid,
1006    user_id: Uuid,
1007) -> AppResult<i64> {
1008    Ok(sqlx::query_scalar::<_, i64>(
1009        "SELECT COUNT(*) FROM revisions r JOIN items i ON i.id = r.item_id
1010         WHERE i.project_id = $1 AND r.checked_out_by = $2",
1011    )
1012    .bind(project_id)
1013    .bind(user_id)
1014    .fetch_one(&mut *conn)
1015    .await?)
1016}
teamcraft/services/projects.rs

teamcraft/services/
projects.rs
