teamcraft/services/
offboarding.rs

1//! What a user still holds (issue #173, step 1): the report an administrator
2//! or a group manager reads before somebody leaves – check-outs, change
3//! orders they wrote that are still open, pending reviews, change requests
4//! assigned to or raised by them, items they own, their memberships (and the
5//! groups in which they are the only active manager) and their API tokens.
6//!
7//! The report is filtered by the **viewer's** rights, never the leaver's: a
8//! lock in a group the leaver was removed from is still listed, which is the
9//! lock nobody else would find. Administrators see every group they may
10//! read; for a group that hides its content from them (need-to-know policy)
11//! only the number of check-outs per group. A manager who is no
12//! administrator sees what lies in the groups they manage, and no tokens.
13//!
14//! Read-only; every list is bounded by [`MAX_LIST`] with the full count next
15//! to it.
16
17use chrono::{DateTime, Utc};
18use serde::Serialize;
19use sqlx::PgPool;
20use uuid::Uuid;
21
22use crate::{
23    auth::Actor,
24    error::{AppError, AppResult},
25    models::ApiToken,
26    services::{access, users},
27};
28
29/// Most entries of one kind in a report.
30pub const MAX_LIST: i64 = 200;
31
32/// A bounded list with the number of entries there are in all.
33#[derive(Clone, Debug, Serialize)]
34pub struct Listed<T> {
35    pub total: i64,
36    pub shown: Vec<T>,
37}
38
39impl<T> Listed<T> {
40    pub fn is_empty(&self) -> bool {
41        self.total == 0
42    }
43}
44
45/// Single sign-on users without activity for this many days are reported by
46/// [`idle_sso_users`] (the stale threshold of the access review).
47pub const IDLE_SSO_DAYS: i64 = crate::services::access_review::STALE_DAYS;
48
49/// An active single sign-on user who stopped signing in but still holds
50/// something (issue #173, step 9): typically removed at the identity provider
51/// and never offboarded in TeamCraft.
52#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
53pub struct IdleSsoUser {
54    pub user_id: Uuid,
55    pub username: String,
56    pub display_name: String,
57    pub last_login_at: Option<DateTime<Utc>>,
58    /// Latest sign-in, token use or the account's creation.
59    pub last_active_at: Option<DateTime<Utc>>,
60    /// When the identity provider last logged them out (back channel).
61    pub idp_logout_at: Option<DateTime<Utc>>,
62    pub memberships: i64,
63    pub tokens: i64,
64    pub checkouts: i64,
65}
66
67/// Active single sign-on users without a sign-in or token use for
68/// [`IDLE_SSO_DAYS`] who still hold memberships, tokens or check-outs –
69/// candidates for *Offboard and deactivate*. Bounded by [`MAX_LIST`].
70///
71/// # Invariants
72///
73/// * **Permission:** administrators (403 otherwise).
74/// * **Data:** read-only; counts over every group (management data, also of
75///   need-to-know groups); the clock of the database decides what is idle.
76pub async fn idle_sso_users(pool: &PgPool, actor: &Actor) -> AppResult<Vec<IdleSsoUser>> {
77    if !actor.is_admin() {
78        return Err(AppError::Forbidden(
79            "Only administrators see the idle single sign-on users.".into(),
80        ));
81    }
82    Ok(sqlx::query_as::<_, IdleSsoUser>(
83        "SELECT * FROM (
84           SELECT u.id AS user_id, u.username, u.display_name, u.last_login_at, u.idp_logout_at,
85                  GREATEST(u.last_login_at, u.created_at,
86                           (SELECT MAX(t.last_used_at) FROM api_tokens t WHERE t.user_id = u.id))
87                      AS last_active_at,
88                  (SELECT COUNT(*) FROM project_members pm WHERE pm.user_id = u.id) AS memberships,
89                  (SELECT COUNT(*) FROM api_tokens t
90                   WHERE t.user_id = u.id AND (t.expires_at IS NULL OR t.expires_at > now()))
91                      AS tokens,
92                  (SELECT COUNT(*) FROM revisions r WHERE r.checked_out_by = u.id) AS checkouts
93           FROM users u
94           WHERE u.auth_source = 'oidc' AND u.active
95         ) idle
96         WHERE last_active_at < now() - make_interval(days => $1)
97           AND (memberships > 0 OR tokens > 0 OR checkouts > 0)
98         ORDER BY last_active_at, username
99         LIMIT $2",
100    )
101    .bind(IDLE_SSO_DAYS as i32)
102    .bind(MAX_LIST)
103    .fetch_all(pool)
104    .await?)
105}
106
107/// A revision the user has checked out.
108#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
109pub struct HeldRevision {
110    pub revision_id: Uuid,
111    pub item_number: String,
112    pub code: String,
113    pub name: String,
114    pub project_code: Option<String>,
115    pub checked_out_at: Option<DateTime<Utc>>,
116    #[serde(skip)]
117    total: i64,
118}
119
120/// A change order (authored and still open, or waiting for the user's review).
121#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
122pub struct HeldChange {
123    pub id: Uuid,
124    pub number: String,
125    pub title: String,
126    pub status: String,
127    #[serde(skip)]
128    total: i64,
129}
130
131/// A change request assigned to or raised by the user.
132#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
133pub struct HeldRequest {
134    pub id: Uuid,
135    pub number: String,
136    pub title: String,
137    pub status: String,
138    #[serde(skip)]
139    total: i64,
140}
141
142/// An item the user owns.
143#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
144pub struct HeldItem {
145    pub id: Uuid,
146    pub number: String,
147    /// Name of the newest revision.
148    pub name: String,
149    pub project_code: Option<String>,
150    #[serde(skip)]
151    total: i64,
152}
153
154/// Check-outs in a group whose content the viewer may not read: the count.
155#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
156pub struct GroupCount {
157    pub project_code: String,
158    pub count: i64,
159}
160
161/// A membership of the user.
162#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
163pub struct HeldMembership {
164    pub project_id: Uuid,
165    pub project_code: String,
166    pub project_name: String,
167    /// `manual` or `oidc`.
168    pub source: String,
169    pub manager: bool,
170    /// The user is a manager and no other active user manages the group.
171    pub only_active_manager: bool,
172}
173
174/// Everything the viewer may know about what a user still holds.
175#[derive(Clone, Debug, Serialize)]
176pub struct Holdings {
177    pub user_id: Uuid,
178    pub username: String,
179    pub display_name: String,
180    pub active: bool,
181    pub last_login_at: Option<DateTime<Utc>>,
182    /// `all` for administrators, `managed` for a manager (only the groups
183    /// they manage).
184    pub scope: &'static str,
185    pub checkouts: Listed<HeldRevision>,
186    /// Administrators only: check-outs in groups that hide their content
187    /// from them, as a count per group.
188    pub hidden_checkouts: Vec<GroupCount>,
189    /// Change orders the user wrote: draft, in review or rejected.
190    pub authored_changes: Listed<HeldChange>,
191    /// Change orders that wait for the user's review.
192    pub pending_reviews: Listed<HeldChange>,
193    /// Open change requests assigned to the user.
194    pub assigned_requests: Listed<HeldRequest>,
195    /// Open change requests the user raised.
196    pub raised_requests: Listed<HeldRequest>,
197    pub owned_items: Listed<HeldItem>,
198    pub memberships: Vec<HeldMembership>,
199    /// Administrators only (never the secret, see `models::ApiToken`).
200    pub tokens: Option<Vec<ApiToken>>,
201}
202
203impl Holdings {
204    /// Nothing listed: the user holds nothing the viewer may know of
205    /// (memberships and tokens aside, which do not block work).
206    pub fn blocks_nothing(&self) -> bool {
207        self.checkouts.is_empty()
208            && self.hidden_checkouts.is_empty()
209            && self.authored_changes.is_empty()
210            && self.pending_reviews.is_empty()
211            && self.assigned_requests.is_empty()
212            && self.raised_requests.is_empty()
213            && self.owned_items.is_empty()
214            && !self.memberships.iter().any(|m| m.only_active_manager)
215    }
216}
217
218/// SQL: the change `c` touches a group of the scope bound at `n`
219/// (`uuid[]`, NULL = no limit).
220fn change_in_scope(n: usize) -> String {
221    format!(
222        "(${n}::uuid[] IS NULL OR EXISTS (
223            SELECT 1 FROM change_items sci
224            JOIN revisions sr ON sr.id = sci.revision_id
225            JOIN items si ON si.id = sr.item_id
226            WHERE sci.change_id = c.id AND si.project_id = ANY(${n})))"
227    )
228}
229
230/// SQL: the request `r` names an item of a group of the scope bound at `n`.
231fn request_in_scope(n: usize) -> String {
232    format!(
233        "(${n}::uuid[] IS NULL OR EXISTS (
234            SELECT 1 FROM change_request_items sri
235            JOIN items si ON si.id = sri.item_id
236            WHERE sri.request_id = r.id AND si.project_id = ANY(${n})))"
237    )
238}
239
240/// The open change requests whose `column` (`assignee` or `requested_by`,
241/// never input) is the user, as the actor may see them.
242async fn open_requests(
243    pool: &PgPool,
244    actor: &Actor,
245    user_id: Uuid,
246    column: &'static str,
247    scope: Option<&[Uuid]>,
248) -> AppResult<Vec<HeldRequest>> {
249    Ok(sqlx::query_as::<_, HeldRequest>(&format!(
250        "SELECT r.id, r.number, r.title, r.status, COUNT(*) OVER () AS total
251         FROM change_requests r
252         WHERE r.{column} = $1 AND r.status IN ('new', 'in_triage', 'accepted', 'deferred')
253           AND {} AND {}
254         ORDER BY r.number LIMIT $5",
255        access::request_visible("r", 2, 3),
256        request_in_scope(4)
257    ))
258    .bind(user_id)
259    .bind(actor.id)
260    .bind(&actor.hidden_projects)
261    .bind(scope)
262    .bind(MAX_LIST)
263    .fetch_all(pool)
264    .await?)
265}
266
267/// The holdings of a user as `actor` may see them.
268///
269/// # Invariants
270///
271/// * **Permission:** administrators, and managers of at least one project
272///   group (403 otherwise); an unknown user is "not found".
273/// * **Visibility:** every list is filtered with the guards of
274///   `services::access` for the **actor**; a manager additionally sees only
275///   what lies in the groups they manage (items without a group are for
276///   administrators). Check-outs in groups hidden from an administrator are
277///   a count per group, without item numbers. Tokens are for administrators.
278/// * **Data:** read-only; at most [`MAX_LIST`] entries per list.
279pub async fn holdings(pool: &PgPool, actor: &Actor, user_id: Uuid) -> AppResult<Holdings> {
280    // the groups a non-administrator may report on: managed and readable
281    let scope: Option<Vec<Uuid>> = if actor.is_admin() {
282        None
283    } else {
284        let managed: Vec<Uuid> = if actor.can_edit() {
285            sqlx::query_scalar(
286                "SELECT project_id FROM project_members WHERE user_id = $1 AND manager",
287            )
288            .bind(actor.id)
289            .fetch_all(pool)
290            .await?
291        } else {
292            Vec::new()
293        };
294        let managed: Vec<Uuid> = managed
295            .into_iter()
296            .filter(|p| actor.can_see_project(Some(*p)))
297            .collect();
298        if managed.is_empty() {
299            return Err(AppError::Forbidden(
300                "Only administrators and managers of a project group can see what a user holds."
301                    .into(),
302            ));
303        }
304        Some(managed)
305    };
306    let user = users::get(pool, user_id).await?;
307
308    let checkouts = sqlx::query_as::<_, HeldRevision>(&format!(
309        "SELECT r.id AS revision_id, i.number AS item_number, r.code, r.name,
310                p.code AS project_code, r.checked_out_at, COUNT(*) OVER () AS total
311         FROM revisions r JOIN items i ON i.id = r.item_id
312         LEFT JOIN projects p ON p.id = i.project_id
313         WHERE r.checked_out_by = $1 AND {}
314           AND ($3::uuid[] IS NULL OR i.project_id = ANY($3))
315         ORDER BY r.checked_out_at NULLS LAST, i.number, r.code LIMIT $4",
316        access::item_visible("i", 2)
317    ))
318    .bind(user_id)
319    .bind(&actor.hidden_projects)
320    .bind(scope.as_deref())
321    .bind(MAX_LIST)
322    .fetch_all(pool)
323    .await?;
324
325    let hidden_checkouts = if actor.is_admin() && !actor.hidden_projects.is_empty() {
326        sqlx::query_as::<_, GroupCount>(
327            "SELECT p.code AS project_code, COUNT(*) AS count
328             FROM revisions r JOIN items i ON i.id = r.item_id
329             JOIN projects p ON p.id = i.project_id
330             WHERE r.checked_out_by = $1 AND i.project_id = ANY($2)
331             GROUP BY p.code ORDER BY p.code",
332        )
333        .bind(user_id)
334        .bind(&actor.hidden_projects)
335        .fetch_all(pool)
336        .await?
337    } else {
338        Vec::new()
339    };
340
341    let authored_changes = sqlx::query_as::<_, HeldChange>(&format!(
342        "SELECT c.id, c.number, c.title, c.status, COUNT(*) OVER () AS total
343         FROM changes c
344         WHERE c.created_by = $1 AND c.status IN ('draft', 'in_review', 'rejected')
345           AND {} AND {}
346         ORDER BY c.number LIMIT $5",
347        access::change_visible("c", 2, 3),
348        change_in_scope(4)
349    ))
350    .bind(user_id)
351    .bind(actor.id)
352    .bind(&actor.hidden_projects)
353    .bind(scope.as_deref())
354    .bind(MAX_LIST)
355    .fetch_all(pool)
356    .await?;
357
358    let pending_reviews = sqlx::query_as::<_, HeldChange>(&format!(
359        "SELECT c.id, c.number, c.title, c.status, COUNT(*) OVER () AS total
360         FROM change_reviews rv JOIN changes c ON c.id = rv.change_id
361         WHERE rv.reviewer_id = $1 AND rv.decision = 'pending'
362           AND c.status IN ('draft', 'in_review')
363           AND {} AND {}
364         ORDER BY c.number LIMIT $5",
365        access::change_visible("c", 2, 3),
366        change_in_scope(4)
367    ))
368    .bind(user_id)
369    .bind(actor.id)
370    .bind(&actor.hidden_projects)
371    .bind(scope.as_deref())
372    .bind(MAX_LIST)
373    .fetch_all(pool)
374    .await?;
375
376    let assigned_requests =
377        open_requests(pool, actor, user_id, "assignee", scope.as_deref()).await?;
378    let raised_requests =
379        open_requests(pool, actor, user_id, "requested_by", scope.as_deref()).await?;
380
381    let owned_items = sqlx::query_as::<_, HeldItem>(&format!(
382        "SELECT i.id, i.number,
383                COALESCE((SELECT lr.name FROM revisions lr WHERE lr.item_id = i.id
384                          ORDER BY lr.seq DESC LIMIT 1), '') AS name,
385                p.code AS project_code, COUNT(*) OVER () AS total
386         FROM items i LEFT JOIN projects p ON p.id = i.project_id
387         WHERE i.owner_id = $1 AND {}
388           AND ($3::uuid[] IS NULL OR i.project_id = ANY($3))
389         ORDER BY i.number LIMIT $4",
390        access::item_visible("i", 2)
391    ))
392    .bind(user_id)
393    .bind(&actor.hidden_projects)
394    .bind(scope.as_deref())
395    .bind(MAX_LIST)
396    .fetch_all(pool)
397    .await?;
398
399    let memberships = sqlx::query_as::<_, HeldMembership>(
400        "SELECT p.id AS project_id, p.code AS project_code, p.name AS project_name,
401                pm.source, pm.manager,
402                (pm.manager AND NOT EXISTS (
403                    SELECT 1 FROM project_members o JOIN users ou ON ou.id = o.user_id
404                    WHERE o.project_id = p.id AND o.manager AND ou.active
405                      AND o.user_id <> pm.user_id)) AS only_active_manager
406         FROM project_members pm JOIN projects p ON p.id = pm.project_id
407         WHERE pm.user_id = $1 AND ($2::uuid[] IS NULL OR p.id = ANY($2))
408         ORDER BY p.code",
409    )
410    .bind(user_id)
411    .bind(scope.as_deref())
412    .fetch_all(pool)
413    .await?;
414
415    let tokens = if actor.is_admin() {
416        Some(users::list_tokens(pool, user_id).await?)
417    } else {
418        None
419    };
420
421    Ok(Holdings {
422        user_id,
423        username: user.username,
424        display_name: user.display_name,
425        active: user.active,
426        last_login_at: user.last_login_at,
427        scope: if scope.is_none() { "all" } else { "managed" },
428        checkouts: Listed {
429            total: checkouts.first().map_or(0, |r| r.total),
430            shown: checkouts,
431        },
432        hidden_checkouts,
433        authored_changes: Listed {
434            total: authored_changes.first().map_or(0, |r| r.total),
435            shown: authored_changes,
436        },
437        pending_reviews: Listed {
438            total: pending_reviews.first().map_or(0, |r| r.total),
439            shown: pending_reviews,
440        },
441        assigned_requests: Listed {
442            total: assigned_requests.first().map_or(0, |r| r.total),
443            shown: assigned_requests,
444        },
445        raised_requests: Listed {
446            total: raised_requests.first().map_or(0, |r| r.total),
447            shown: raised_requests,
448        },
449        owned_items: Listed {
450            total: owned_items.first().map_or(0, |r| r.total),
451            shown: owned_items,
452        },
453        memberships,
454        tokens,
455    })
456}
457
458/// The report as plain text lines (the `teamcraft user holdings` command).
459pub fn to_text(h: &Holdings) -> String {
460    fn section<T>(out: &mut String, title: &str, list: &Listed<T>, line: impl Fn(&T) -> String) {
461        out.push_str(&format!("{title}: {}\n", list.total));
462        for entry in &list.shown {
463            out.push_str(&format!("  {}\n", line(entry)));
464        }
465        if list.total > list.shown.len() as i64 {
466            out.push_str(&format!(
467                "  … and {} more\n",
468                list.total - list.shown.len() as i64
469            ));
470        }
471    }
472    let group = |code: &Option<String>| code.clone().unwrap_or_else(|| "-".into());
473    let mut out = format!(
474        "{} ({}), {}, last sign-in: {}\n",
475        h.display_name,
476        h.username,
477        if h.active { "active" } else { "deactivated" },
478        h.last_login_at
479            .map_or_else(|| "never".to_string(), |t| t.to_rfc3339())
480    );
481    section(&mut out, "Check-outs", &h.checkouts, |r| {
482        format!(
483            "{}/{}  {}  [{}]  since {}",
484            r.item_number,
485            r.code,
486            r.name,
487            group(&r.project_code),
488            r.checked_out_at
489                .map_or_else(|| "?".to_string(), |t| t.to_rfc3339())
490        )
491    });
492    for g in &h.hidden_checkouts {
493        out.push_str(&format!(
494            "  {} check-outs in {} (content hidden from administrators)\n",
495            g.count, g.project_code
496        ));
497    }
498    let change = |c: &HeldChange| format!("{}  {}  ({})", c.number, c.title, c.status);
499    section(
500        &mut out,
501        "Open change orders they wrote",
502        &h.authored_changes,
503        change,
504    );
505    section(&mut out, "Pending reviews", &h.pending_reviews, change);
506    let request = |r: &HeldRequest| format!("{}  {}  ({})", r.number, r.title, r.status);
507    section(
508        &mut out,
509        "Open change requests assigned to them",
510        &h.assigned_requests,
511        request,
512    );
513    section(
514        &mut out,
515        "Open change requests they raised",
516        &h.raised_requests,
517        request,
518    );
519    section(&mut out, "Items they own", &h.owned_items, |i| {
520        format!("{}  {}  [{}]", i.number, i.name, group(&i.project_code))
521    });
522    out.push_str(&format!("Memberships: {}\n", h.memberships.len()));
523    for m in &h.memberships {
524        out.push_str(&format!(
525            "  {}  {}  {}{}{}\n",
526            m.project_code,
527            m.project_name,
528            if m.source == "oidc" {
529                "identity provider"
530            } else {
531                "manual"
532            },
533            if m.manager { ", manager" } else { "" },
534            if m.only_active_manager {
535                " – the ONLY active manager"
536            } else {
537                ""
538            }
539        ));
540    }
541    if let Some(tokens) = &h.tokens {
542        out.push_str(&format!("API tokens: {}\n", tokens.len()));
543        for t in tokens {
544            out.push_str(&format!(
545                "  {}  {}  last used: {}  expires: {}\n",
546                t.name,
547                t.scope,
548                t.last_used_at
549                    .map_or_else(|| "never".to_string(), |x| x.to_rfc3339()),
550                t.expires_at
551                    .map_or_else(|| "never".to_string(), |x| x.to_rfc3339())
552            ));
553        }
554    }
555    out
556}
557
558#[cfg(test)]
559mod tests {
560    use super::*;
561
562    fn empty<T>() -> Listed<T> {
563        Listed {
564            total: 0,
565            shown: Vec::new(),
566        }
567    }
568
569    fn report() -> Holdings {
570        Holdings {
571            user_id: Uuid::nil(),
572            username: "lena".into(),
573            display_name: "Lena".into(),
574            active: false,
575            last_login_at: None,
576            scope: "all",
577            checkouts: Listed {
578                total: 3,
579                shown: vec![HeldRevision {
580                    revision_id: Uuid::nil(),
581                    item_number: "PRT-000001".into(),
582                    code: "B".into(),
583                    name: "Upright".into(),
584                    project_code: Some("CHASSIS".into()),
585                    checked_out_at: None,
586                    total: 3,
587                }],
588            },
589            hidden_checkouts: vec![GroupCount {
590                project_code: "NTK".into(),
591                count: 2,
592            }],
593            authored_changes: empty(),
594            pending_reviews: empty(),
595            assigned_requests: empty(),
596            raised_requests: empty(),
597            owned_items: empty(),
598            memberships: vec![HeldMembership {
599                project_id: Uuid::nil(),
600                project_code: "CHASSIS".into(),
601                project_name: "Chassis".into(),
602                source: "oidc".into(),
603                manager: true,
604                only_active_manager: true,
605            }],
606            tokens: Some(Vec::new()),
607        }
608    }
609
610    #[test]
611    fn the_text_report_counts_lists_and_flags() {
612        let text = to_text(&report());
613        assert!(text.starts_with("Lena (lena), deactivated, last sign-in: never\n"));
614        assert!(text.contains("Check-outs: 3\n  PRT-000001/B  Upright  [CHASSIS]"));
615        assert!(text.contains("… and 2 more"), "the list is cut");
616        assert!(text.contains("2 check-outs in NTK (content hidden"));
617        assert!(text.contains("Pending reviews: 0\n"));
618        assert!(text.contains("identity provider, manager – the ONLY active manager"));
619        assert!(text.contains("API tokens: 0"));
620    }
621
622    #[test]
623    fn memberships_and_tokens_alone_block_nothing() {
624        let mut h = report();
625        assert!(!h.blocks_nothing());
626        h.checkouts = empty();
627        h.hidden_checkouts.clear();
628        assert!(!h.blocks_nothing(), "still the only active manager");
629        h.memberships[0].only_active_manager = false;
630        assert!(h.blocks_nothing());
631    }
632
633    #[test]
634    fn the_scope_fragments_bind_one_position() {
635        assert!(change_in_scope(4).contains("$4::uuid[] IS NULL"));
636        assert!(change_in_scope(4).contains("ANY($4)"));
637        assert!(request_in_scope(7).contains("ANY($7)"));
638    }
639}
teamcraft/services/offboarding.rs

teamcraft/services/
offboarding.rs
