teamcraft/services/
manager_gaps.rs

1//! Project groups without an active manager (issue #173, step 7).
2//!
3//! A group whose managers have all left or been deactivated can be managed by
4//! administrators only, and nobody notices: no one releases a leaver's locks,
5//! adds the new members or confirms the access review. [`list`] shows such
6//! groups to administrators, together with the groups whose only active
7//! managers have shown no activity for [`STALE_DAYS`]; [`alert_admins`]
8//! (hourly housekeeping) tells the administrators once per gap.
9
10use chrono::{DateTime, Utc};
11use serde::Serialize;
12use serde_json::json;
13use sqlx::PgPool;
14use uuid::Uuid;
15
16use crate::{
17    auth::Actor,
18    domain::NotificationKind,
19    error::{AppError, AppResult},
20    services::{
21        access_review, audit,
22        notifications::{self, Notice, Subject},
23    },
24};
25
26/// Active managers with no sign-in and no token use for this many days do
27/// not count as present (the stale threshold of the access review).
28pub const STALE_DAYS: i64 = access_review::STALE_DAYS;
29
30/// An active project group without a present manager.
31#[derive(Clone, Debug, Serialize, sqlx::FromRow)]
32pub struct ManagerGap {
33    pub project_id: Uuid,
34    pub code: String,
35    pub name: String,
36    /// Active managers of the group (0: none left).
37    pub active_managers: i64,
38    /// Latest activity of an active manager: sign-in, use of an API or
39    /// desktop token, or the creation of the account.
40    pub last_active_at: Option<DateTime<Utc>>,
41}
42
43impl ManagerGap {
44    /// `"none"` (no active manager) or `"stale"` (managers inactive for
45    /// [`STALE_DAYS`]).
46    pub fn kind(&self) -> &'static str {
47        if self.active_managers == 0 {
48            "none"
49        } else {
50            "stale"
51        }
52    }
53}
54
55/// The gaps as JSON for pages and the API, with their kind.
56pub fn to_json(gaps: &[ManagerGap]) -> serde_json::Value {
57    json!(gaps
58        .iter()
59        .map(|g| {
60            let mut v = json!(g);
61            v["kind"] = json!(g.kind());
62            v
63        })
64        .collect::<Vec<_>>())
65}
66
67/// Active groups without a present manager; `$1` = [`STALE_DAYS`], `$2` =
68/// a group id or NULL for all.
69const GAP_SELECT: &str = "SELECT p.id AS project_id, p.code, p.name,
70                COUNT(u.id) AS active_managers,
71                MAX(GREATEST(u.last_login_at, u.created_at, t.used)) AS last_active_at
72         FROM projects p
73         LEFT JOIN project_members pm ON pm.project_id = p.id AND pm.manager
74         LEFT JOIN users u ON u.id = pm.user_id AND u.active
75         LEFT JOIN (SELECT user_id, MAX(last_used_at) AS used FROM api_tokens GROUP BY user_id) t
76                ON t.user_id = u.id
77         WHERE p.active AND ($2::uuid IS NULL OR p.id = $2)
78         GROUP BY p.id, p.code, p.name
79         HAVING COUNT(u.id) = 0
80             OR MAX(GREATEST(u.last_login_at, u.created_at, t.used))
81                < now() - make_interval(days => $1)
82         ORDER BY p.code";
83
84/// Active project groups without an active manager, or whose active managers
85/// have shown no activity for [`STALE_DAYS`].
86///
87/// # Invariants
88///
89/// * **Permission:** administrators (403 otherwise). Like the project
90///   overview of an administrator, every group is listed – codes and names
91///   are management data, also of need-to-know groups the administrator may
92///   not read.
93/// * **Data:** read-only; the clock of the database decides what is stale.
94pub async fn list(pool: &PgPool, actor: &Actor) -> AppResult<Vec<ManagerGap>> {
95    if !actor.is_admin() {
96        return Err(AppError::Forbidden(
97            "Only administrators see the groups without an active manager.".into(),
98        ));
99    }
100    Ok(sqlx::query_as::<_, ManagerGap>(GAP_SELECT)
101        .bind(STALE_DAYS as i32)
102        .bind(None::<Uuid>)
103        .fetch_all(pool)
104        .await?)
105}
106
107/// Tells the active administrators about each group that has newly lost its
108/// last present manager, once per gap: the group is marked
109/// (`projects.manager_gap_alerted_at`) and the mark is cleared when the
110/// group has a present manager again, so a later gap is reported anew.
111/// Returns the number of groups reported. Run by the hourly housekeeping.
112///
113/// # Invariants
114///
115/// * **Permission:** none – a server task.
116/// * **Locks:** the group row (`FOR UPDATE`) while it is checked again and
117///   marked, so two servers do not both report it.
118/// * **Audit:** `project.manager_gap_alert` per group reported.
119/// * **Notifications:** an `access_alert` to every active administrator (not
120///   mutable); **webhooks:** none.
121/// * **Atomic:** one transaction per group.
122pub async fn alert_admins(pool: &PgPool) -> AppResult<u64> {
123    let gaps = sqlx::query_as::<_, ManagerGap>(GAP_SELECT)
124        .bind(STALE_DAYS as i32)
125        .bind(None::<Uuid>)
126        .fetch_all(pool)
127        .await?;
128    // gaps that closed: report the next one again
129    let open: Vec<Uuid> = gaps.iter().map(|g| g.project_id).collect();
130    sqlx::query(
131        "UPDATE projects SET manager_gap_alerted_at = NULL
132         WHERE manager_gap_alerted_at IS NOT NULL AND id <> ALL($1)",
133    )
134    .bind(&open)
135    .execute(pool)
136    .await?;
137    let mut sent = 0;
138    for gap in gaps {
139        let mut tx = pool.begin().await?;
140        let unreported: Option<Uuid> = sqlx::query_scalar(
141            "SELECT id FROM projects
142             WHERE id = $1 AND manager_gap_alerted_at IS NULL FOR UPDATE",
143        )
144        .bind(gap.project_id)
145        .fetch_optional(&mut *tx)
146        .await?;
147        if unreported.is_none() {
148            tx.rollback().await?;
149            continue;
150        }
151        // checked again under the lock: a manager may have been added
152        let still = sqlx::query_as::<_, ManagerGap>(GAP_SELECT)
153            .bind(STALE_DAYS as i32)
154            .bind(gap.project_id)
155            .fetch_optional(&mut *tx)
156            .await?;
157        let Some(gap) = still else {
158            tx.rollback().await?;
159            continue;
160        };
161        let admins: Vec<Uuid> =
162            sqlx::query_scalar("SELECT id FROM users WHERE role = 'admin' AND active")
163                .fetch_all(&mut *tx)
164                .await?;
165        let code = &gap.code;
166        let (title, body) = if gap.active_managers == 0 {
167            (
168                format!("{code} has no active manager"),
169                format!(
170                    "Every manager of {code} has left the group or been deactivated. Until \
171                     somebody is made a manager, only administrators can add members, release \
172                     the check-outs of people who left or confirm the access review."
173                ),
174            )
175        } else {
176            (
177                format!("The managers of {code} have been inactive for {STALE_DAYS} days"),
178                format!(
179                    "No manager of {code} has signed in or used a token for {STALE_DAYS} days. \
180                     If they have left, make somebody else a manager of the group."
181                ),
182            )
183        };
184        let notice = Notice {
185            kind: NotificationKind::AccessAlert,
186            title,
187            body,
188            link: format!("/projects/{}", gap.project_id),
189            subject_type: "",
190            subject_ref: String::new(),
191            subject: Subject::Personal,
192            redacted: None,
193        };
194        notifications::notify(&mut tx, None, &admins, &notice).await?;
195        sqlx::query("UPDATE projects SET manager_gap_alerted_at = now() WHERE id = $1")
196            .bind(gap.project_id)
197            .execute(&mut *tx)
198            .await?;
199        audit::record(
200            &mut tx,
201            None,
202            "project.manager_gap_alert",
203            "project",
204            Some(gap.project_id),
205            format!(
206                "{code} has no present manager: told {} administrators",
207                admins.len()
208            ),
209            json!({
210                "kind": gap.kind(),
211                "active_managers": gap.active_managers,
212                "recipients": admins.len(),
213            }),
214        )
215        .await?;
216        tx.commit().await?;
217        sent += 1;
218    }
219    Ok(sent)
220}
teamcraft/services/manager_gaps.rs

teamcraft/services/
manager_gaps.rs
