teamcraft/services/
audit.rs

1//! Append-only audit trail of every change to product data and users (only
2//! the optional retention, [`purge_older_than`], removes old entries – and
3//! records that it did).
4
5use chrono::{DateTime, Utc};
6use serde_json::Value;
7use sqlx::{PgConnection, PgPool};
8use uuid::Uuid;
9
10use crate::{
11    auth::Actor,
12    error::AppResult,
13    models::AuditEntry,
14    services::{access, Page},
15};
16
17/// Writes an audit entry. Call it inside the transaction of the change it
18/// documents, so that both are committed (or rolled back) together.
19pub async fn record(
20    conn: &mut PgConnection,
21    actor: Option<&Actor>,
22    action: &str,
23    entity_type: &str,
24    entity_id: Option<Uuid>,
25    summary: impl Into<String>,
26    details: Value,
27) -> AppResult<()> {
28    insert(
29        conn,
30        actor,
31        action,
32        entity_type,
33        entity_id,
34        None,
35        &Mentions::default(),
36        summary.into(),
37        details,
38    )
39    .await
40}
41
42/// Items and change orders the text of an entry (or notification) names
43/// besides its subject. A reader must be allowed to see all of them;
44/// otherwise the entry is redacted for that reader.
45#[derive(Clone, Debug, Default, PartialEq, Eq)]
46pub struct Mentions {
47    pub items: Vec<Uuid>,
48    pub changes: Vec<Uuid>,
49}
50
51impl Mentions {
52    pub fn items(items: impl IntoIterator<Item = Uuid>) -> Mentions {
53        Mentions {
54            items: items.into_iter().collect(),
55            changes: Vec::new(),
56        }
57    }
58
59    pub fn change(change_id: Uuid) -> Mentions {
60        Mentions {
61            items: Vec::new(),
62            changes: vec![change_id],
63        }
64    }
65
66    pub fn with_change(mut self, change_id: Uuid) -> Mentions {
67        self.changes.push(change_id);
68        self
69    }
70
71    pub fn is_empty(&self) -> bool {
72        self.items.is_empty() && self.changes.is_empty()
73    }
74}
75
76/// Like [`record`] for an entry whose summary or details name other items
77/// or change orders (labels of a change's revisions, flagged dependents, a
78/// linked change): redacted for readers who may not see all of them.
79#[allow(clippy::too_many_arguments)]
80pub async fn record_mentioning(
81    conn: &mut PgConnection,
82    actor: Option<&Actor>,
83    action: &str,
84    entity_type: &str,
85    entity_id: Option<Uuid>,
86    mentions: &Mentions,
87    summary: impl Into<String>,
88    details: Value,
89) -> AppResult<()> {
90    insert(
91        conn,
92        actor,
93        action,
94        entity_type,
95        entity_id,
96        None,
97        mentions,
98        summary.into(),
99        details,
100    )
101    .await
102}
103
104/// The item ids of the revisions in a change (for [`Mentions`]).
105pub(crate) async fn change_item_ids(
106    conn: &mut PgConnection,
107    change_id: Uuid,
108) -> AppResult<Vec<Uuid>> {
109    Ok(sqlx::query_scalar::<_, Uuid>(
110        "SELECT DISTINCT r.item_id FROM change_items ci JOIN revisions r ON r.id = ci.revision_id
111         WHERE ci.change_id = $1",
112    )
113    .bind(change_id)
114    .fetch_all(&mut *conn)
115    .await?)
116}
117
118/// Like [`record`] for an entry that also talks about a second item (BOM
119/// child, reference target, basis item, item added to a change, …): the
120/// entry is redacted for readers who may not see that item.
121#[allow(clippy::too_many_arguments)]
122pub async fn record_related(
123    conn: &mut PgConnection,
124    actor: Option<&Actor>,
125    action: &str,
126    entity_type: &str,
127    entity_id: Option<Uuid>,
128    related_item_id: Option<Uuid>,
129    summary: impl Into<String>,
130    details: Value,
131) -> AppResult<()> {
132    insert(
133        conn,
134        actor,
135        action,
136        entity_type,
137        entity_id,
138        related_item_id,
139        &Mentions::default(),
140        summary.into(),
141        details,
142    )
143    .await
144}
145
146#[allow(clippy::too_many_arguments)]
147async fn insert(
148    conn: &mut PgConnection,
149    actor: Option<&Actor>,
150    action: &str,
151    entity_type: &str,
152    entity_id: Option<Uuid>,
153    related_item_id: Option<Uuid>,
154    mentions: &Mentions,
155    summary: String,
156    details: Value,
157) -> AppResult<()> {
158    sqlx::query(
159        // mentioned_projects: the groups of the named items now, so the entry
160        // stays redacted after such an item is deleted
161        "INSERT INTO audit_log (user_id, username, action, entity_type, entity_id, summary, details,
162                                related_item_id, mentioned_items, mentioned_changes,
163                                mentioned_projects)
164         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
165                 ARRAY(SELECT DISTINCT pi.project_id FROM items pi
166                       WHERE pi.project_id IS NOT NULL
167                         AND (pi.id = $8 OR pi.id = ANY($9))))",
168    )
169    // the system actor (the command line) has no user row: no user id, its
170    // name "system" is kept
171    .bind(actor.map(|a| a.id).filter(|id| !id.is_nil()))
172    .bind(actor.map(|a| a.username.as_str()).unwrap_or("system"))
173    .bind(action)
174    .bind(entity_type)
175    .bind(entity_id)
176    .bind(&summary)
177    .bind(details)
178    .bind(related_item_id)
179    .bind(&mentions.items)
180    .bind(&mentions.changes)
181    .execute(&mut *conn)
182    .await?;
183    tracing::info!(
184        actor = actor.map(|a| a.username.as_str()).unwrap_or("system"),
185        action,
186        entity_type,
187        ?entity_id,
188        "{summary}"
189    );
190    Ok(())
191}
192
193/// Removes the audit entries older than `retention_days` (housekeeping,
194/// `TEAMCRAFT_AUDIT_RETENTION_DAYS`; the caller skips it for 0 = keep for
195/// ever) and records the removal as one `audit.purge` entry with the count
196/// and the time span removed – in one transaction, so entries never vanish
197/// without that record. Returns the number of entries removed.
198///
199/// # Invariants
200///
201/// * **Permission:** none – the server's housekeeping, no actor ("system").
202/// * **Data:** only entries older than the cut-off, except the entries separation
203///   of duties still reads: logged edits (`reviewers::EDIT_ACTIONS`) of revisions
204///   that are still in work or review and preparing actions
205///   (`changes::PREPARE_ACTIONS`) of changes that are not yet released or
206///   cancelled – otherwise an editor could drop out of the four-eyes check; a retention of 0 days is
207///   refused (400).
208/// * **Audit:** one `audit.purge` entry when something was removed (only
209///   administrators see it, like every entry without an item, change or
210///   group).
211/// * **Atomic:** one transaction.
212pub async fn purge_older_than(pool: &PgPool, retention_days: u32) -> AppResult<u64> {
213    if retention_days == 0 {
214        return Err(crate::error::AppError::BadRequest(
215            "An audit log retention of 0 days means keeping the entries.".into(),
216        ));
217    }
218    let mut tx = pool.begin().await?;
219    let (removed, oldest, newest): (i64, Option<DateTime<Utc>>, Option<DateTime<Utc>>) =
220        sqlx::query_as(
221            "WITH gone AS (
222                 DELETE FROM audit_log a
223                 WHERE a.at < now() - make_interval(days => $1)
224                   AND NOT (a.entity_type = 'revision' AND a.action = ANY($2)
225                            AND EXISTS (SELECT 1 FROM revisions r
226                                        WHERE r.id = a.entity_id
227                                          AND r.state IN ('in_work', 'in_review')))
228                   AND NOT (a.entity_type = 'change' AND a.action = ANY($3)
229                            AND EXISTS (SELECT 1 FROM changes c
230                                        WHERE c.id = a.entity_id
231                                          AND c.status IN ('draft', 'in_review', 'rejected')))
232                 RETURNING a.at
233             )
234             SELECT COUNT(*), MIN(at), MAX(at) FROM gone",
235        )
236        .bind(i32::try_from(retention_days).unwrap_or(i32::MAX))
237        .bind(crate::services::reviewers::EDIT_ACTIONS)
238        .bind(crate::services::changes::PREPARE_ACTIONS)
239        .fetch_one(&mut *tx)
240        .await?;
241    if removed > 0 {
242        record(
243            &mut tx,
244            None,
245            "audit.purge",
246            "audit",
247            None,
248            format!(
249                "Removed {removed} audit entries older than {retention_days} days \
250                 (TEAMCRAFT_AUDIT_RETENTION_DAYS)"
251            ),
252            serde_json::json!({
253                "removed": removed,
254                "retention_days": retention_days,
255                "oldest": oldest,
256                "newest": newest,
257            }),
258        )
259        .await?;
260    }
261    tx.commit().await?;
262    Ok(u64::try_from(removed).unwrap_or_default())
263}
264
265#[derive(Clone, Debug, Default)]
266pub struct AuditFilter {
267    pub entity_type: Option<String>,
268    pub username: Option<String>,
269    pub action: Option<String>,
270}
271
272/// Lists audit entries, newest first. Returns the page and the total count.
273/// Administrators only (`/admin/audit`): every entry except those about
274/// items, changes, requests or groups the administrator's need-to-know
275/// policy hides (see `visible_clause`); entries that name such items are
276/// redacted.
277pub async fn list(
278    pool: &PgPool,
279    actor: &Actor,
280    filter: &AuditFilter,
281    page: Page,
282) -> AppResult<(Vec<AuditEntry>, i64)> {
283    // $1..$3 filter, $4 limit, $5 offset, $6 hidden groups, $7 admin, $8 actor
284    let filter_sql = "($1::text IS NULL OR a.entity_type = $1)
285                      AND ($2::text IS NULL OR a.username = $2)
286                      AND ($3::text IS NULL OR a.action = $3)";
287    let joins = "LEFT JOIN revisions sr ON a.entity_type = 'revision' AND sr.id = a.entity_id
288         LEFT JOIN items si ON si.id = CASE a.entity_type WHEN 'item' THEN a.entity_id
289                                                          WHEN 'revision' THEN sr.item_id END";
290    let visible = visible_clause(6, 7, 8);
291    // the count has no limit and offset: hidden groups, admin and actor are $4..$6
292    let count_visible = visible_clause(4, 5, 6);
293    let entries = sqlx::query_as::<_, AuditEntry>(&format!(
294        "SELECT {cols} FROM audit_log a {joins}
295         WHERE {filter_sql} AND ({visible})
296         ORDER BY a.at DESC, a.id DESC LIMIT $4 OFFSET $5",
297        cols = columns(6, 8),
298    ))
299    .bind(filter.entity_type.as_deref())
300    .bind(filter.username.as_deref())
301    .bind(filter.action.as_deref())
302    .bind(page.limit)
303    .bind(page.offset)
304    .bind(&actor.hidden_projects)
305    .bind(actor.is_admin())
306    .bind(actor.id)
307    .fetch_all(pool)
308    .await?;
309    let total = sqlx::query_scalar::<_, i64>(&format!(
310        "SELECT COUNT(*) FROM audit_log a {joins} WHERE {filter_sql} AND ({count_visible})"
311    ))
312    .bind(filter.entity_type.as_deref())
313    .bind(filter.username.as_deref())
314    .bind(filter.action.as_deref())
315    .bind(&actor.hidden_projects)
316    .bind(actor.is_admin())
317    .bind(actor.id)
318    .fetch_one(pool)
319    .await?;
320    Ok((redact(entries), total))
321}
322
323/// Most entries in one export ([`export`]); narrow the filter for more.
324pub const EXPORT_MAX: i64 = 100_000;
325
326/// The audit log as an export (#152 step 8): the entries of [`list`] with
327/// the same filter, visibility and redaction, newest first, at most
328/// [`EXPORT_MAX`]. Returns the entries and whether more matched.
329///
330/// # Invariants
331///
332/// * **Permission:** administrators only (403).
333/// * **Audit:** `audit.export` with the filter and the number of entries –
334///   exporting the log is itself on record.
335pub async fn export(
336    pool: &PgPool,
337    actor: &Actor,
338    filter: &AuditFilter,
339) -> AppResult<(Vec<AuditEntry>, bool)> {
340    actor.require(crate::domain::Role::Admin)?;
341    let (entries, total) = list(
342        pool,
343        actor,
344        filter,
345        Page {
346            limit: EXPORT_MAX,
347            offset: 0,
348        },
349    )
350    .await?;
351    let mut tx = pool.begin().await?;
352    record(
353        &mut tx,
354        Some(actor),
355        "audit.export",
356        "user",
357        Some(actor.id),
358        format!("Exported {} audit log entries", entries.len()),
359        serde_json::json!({
360            "entity_type": filter.entity_type,
361            "username": filter.username,
362            "action": filter.action,
363            "entries": entries.len(),
364            "matching": total,
365        }),
366    )
367    .await?;
368    tx.commit().await?;
369    let truncated = total > entries.len() as i64;
370    Ok((entries, truncated))
371}
372
373/// An export as CSV: `id,at,username,action,entity_type,entity_id,summary,details`
374/// (details as JSON).
375pub fn to_csv(entries: &[AuditEntry]) -> String {
376    let mut out = String::from("id,at,username,action,entity_type,entity_id,summary,details\n");
377    for e in entries {
378        let fields = [
379            e.id.to_string(),
380            e.at.to_rfc3339(),
381            e.username.clone(),
382            e.action.clone(),
383            e.entity_type.clone(),
384            e.entity_id.map(|i| i.to_string()).unwrap_or_default(),
385            e.summary.clone(),
386            e.details.to_string(),
387        ];
388        let row: Vec<String> = fields.iter().map(|f| crate::util::csv_field(f)).collect();
389        out.push_str(&row.join(","));
390        out.push('\n');
391    }
392    out
393}
394
395/// The row filter of the audit feeds; expects the joins `sr` (revision) and
396/// `si` (item of an item or revision entry) on `audit_log a`. `hidden`,
397/// `admin` and `actor` are the parameter numbers of the actor's hidden
398/// groups, its administrator flag and its id.
399///
400/// * administrators see every entry except those about an item, revision,
401///   change, request or group they may not read (a deleted subject stays
402///   visible, as without a policy);
403/// * everybody else sees the entries about subjects they may read, plus
404///   their own user, webhook and assistant entries.
405fn visible_clause(hidden: usize, admin: usize, actor: usize) -> String {
406    format!(
407        "(${admin} AND NOT (
408               (a.entity_type IN ('item', 'revision') AND si.id IS NOT NULL AND NOT {item})
409            OR (a.entity_type = 'change' AND EXISTS (
410                   SELECT 1 FROM changes c WHERE c.id = a.entity_id AND NOT {change}))
411            OR (a.entity_type = 'change_request' AND EXISTS (
412                   SELECT 1 FROM change_requests cr WHERE cr.id = a.entity_id AND NOT {request}))
413            OR (a.entity_type = 'project' AND NOT {project})))
414         OR (NOT ${admin} AND (
415               (a.entity_type IN ('item', 'revision') AND si.id IS NOT NULL AND {item})
416            OR (a.entity_type = 'change' AND EXISTS (
417                   SELECT 1 FROM changes c WHERE c.id = a.entity_id AND {change}))
418            OR (a.entity_type = 'change_request' AND EXISTS (
419                   SELECT 1 FROM change_requests cr WHERE cr.id = a.entity_id AND {request}))
420            OR (a.entity_type = 'project' AND {project})
421            OR (a.entity_type IN ('user', 'webhook', 'assistant') AND a.user_id = ${actor})))",
422        item = access::item_visible("si", hidden),
423        change = access::change_visible("c", actor, hidden),
424        request = access::request_visible("cr", actor, hidden),
425        project = access::project_visible("a.entity_id", hidden),
426    )
427}
428
429/// Columns of a readable entry; `$n` = the actor's hidden groups, `$actor`
430/// = the actor's id.
431fn columns(n: usize, actor: usize) -> String {
432    format!(
433        "a.id, a.at, a.user_id, a.username, a.action, a.entity_type, a.entity_id, a.summary,
434         a.details,
435         (a.related_item_id IS NOT NULL AND EXISTS (
436             SELECT 1 FROM items ri WHERE ri.id = a.related_item_id AND NOT {related}))
437           AS related_hidden,
438         (EXISTS (SELECT 1 FROM items mi WHERE mi.id = ANY(a.mentioned_items) AND NOT {item})
439          OR EXISTS (SELECT 1 FROM changes mc WHERE mc.id = ANY(a.mentioned_changes)
440                     AND NOT {change})
441          -- the groups when written: also for items deleted since (fail closed)
442          OR a.mentioned_projects && ${n})
443           AS mentions_hidden",
444        related = access::item_visible("ri", n),
445        item = access::item_visible("mi", n),
446        change = access::change_visible("mc", actor, n),
447    )
448}
449
450/// Replaces summary and details of entries that name a hidden item or a
451/// change order the reader may not see.
452fn redact(entries: Vec<AuditEntry>) -> Vec<AuditEntry> {
453    entries
454        .into_iter()
455        .map(|mut e| {
456            if e.related_hidden {
457                e.summary = format!("{} changed a link to a restricted item", e.username);
458                e.details = serde_json::json!({});
459            } else if e.mentions_hidden {
460                e.summary = format!(
461                    "{} – {} (the details name restricted items)",
462                    e.username, e.action
463                );
464                e.details = serde_json::json!({});
465            }
466            e
467        })
468        .collect()
469}
470
471/// History of a set of entities (e.g. an item and all of its revisions) the
472/// caller already checked. Entries that name an item the actor may not see
473/// are redacted.
474pub async fn for_entities(
475    pool: &PgPool,
476    actor: &Actor,
477    ids: &[Uuid],
478    limit: i64,
479) -> AppResult<Vec<AuditEntry>> {
480    let entries = sqlx::query_as::<_, AuditEntry>(&format!(
481        "SELECT {} FROM audit_log a WHERE a.entity_id = ANY($1)
482         ORDER BY a.at DESC, a.id DESC LIMIT $2",
483        columns(3, 4)
484    ))
485    .bind(ids)
486    .bind(limit)
487    .bind(&actor.hidden_projects)
488    .bind(actor.id)
489    .fetch_all(pool)
490    .await?;
491    Ok(redact(entries))
492}
493
494/// Most recent activity the actor may see: entries about items, revisions,
495/// changes, requests and groups it may read; user, webhook and assistant
496/// entries only for admins (and the user's own).
497pub async fn recent(pool: &PgPool, actor: &Actor, limit: i64) -> AppResult<Vec<AuditEntry>> {
498    let entries = sqlx::query_as::<_, AuditEntry>(&format!(
499        "SELECT {cols}
500         FROM audit_log a
501         LEFT JOIN revisions sr ON a.entity_type = 'revision' AND sr.id = a.entity_id
502         LEFT JOIN items si ON si.id = CASE a.entity_type WHEN 'item' THEN a.entity_id
503                                                          WHEN 'revision' THEN sr.item_id END
504         WHERE {visible}
505         ORDER BY a.at DESC, a.id DESC LIMIT $1",
506        cols = columns(2, 4),
507        visible = visible_clause(2, 3, 4),
508    ))
509    .bind(limit)
510    .bind(&actor.hidden_projects)
511    .bind(actor.is_admin())
512    .bind(actor.id)
513    .fetch_all(pool)
514    .await?;
515    Ok(redact(entries))
516}
517
518#[cfg(test)]
519mod tests {
520    use super::*;
521
522    #[test]
523    fn the_csv_export_escapes_text_formulas_and_details() {
524        let entry = AuditEntry {
525            id: 7,
526            at: "2026-11-10T08:00:00Z".parse().unwrap(),
527            user_id: None,
528            username: "erika".into(),
529            action: "change.approve".into(),
530            entity_type: "change".into(),
531            entity_id: Some(Uuid::nil()),
532            summary: "Approved ECO-1, \"as is\"".into(),
533            details: serde_json::json!({ "comment": "=SUM(A1)" }),
534            related_hidden: false,
535            mentions_hidden: false,
536        };
537        let csv = to_csv(&[entry]);
538        let mut lines = csv.lines();
539        assert_eq!(
540            lines.next(),
541            Some("id,at,username,action,entity_type,entity_id,summary,details")
542        );
543        let row = lines.next().unwrap();
544        assert!(
545            row.starts_with("7,2026-11-10T08:00:00+00:00,erika,change.approve,change,"),
546            "{row}"
547        );
548        assert!(row.contains(r#""Approved ECO-1, ""as is""""#), "{row}");
549        // the details are JSON in one quoted field
550        assert!(row.ends_with(r#""{""comment"":""=SUM(A1)""}""#), "{row}");
551        assert_eq!(lines.next(), None);
552    }
553}
teamcraft/services/audit.rs

teamcraft/services/
audit.rs
