Skip to main content

acme_proxy/sqlite/
audit.rs

1//! The `audit_log` model: append, read back, purge by age.
2//!
3//! Deliberately narrow. There is no setter of any kind and no `update`: a row
4//! is INSERTed once and thereafter only read or deleted wholesale by
5//! [`AuditEntry::cleanup`]. That is the whole reason the table has no foreign
6//! keys either — see the header comment in
7//! `migrations/20260809120000_add_audit_log.sql`.
8
9use serde_json::{Value, json};
10use sqlx::Row;
11use sqlx::sqlite::SqliteRow;
12use tracing::debug;
13
14use crate::audit::{Actor, ActorKind, AuditEvent, AuditRecord, ClientContext};
15use crate::sqlite::db::Database;
16use crate::sqlite::nonce::now_secs;
17use crate::sqlite::order::rfc3339;
18
19/// One stored audit row.
20///
21/// `event` and `actor_kind` come back as the strings they were stored as rather
22/// than as [`AuditEvent`]/[`ActorKind`]: a `CHECK` constraint written before a
23/// future variant existed is exactly the thing that would make a read of an
24/// older database fail, and a listing that renders an unrecognised event name
25/// is a better outcome than one that refuses to load. [`AuditEntry::event`]
26/// parses it for the callers that want the enum.
27#[derive(Debug, Clone)]
28pub struct AuditEntry {
29    pub id: i64,
30    pub created_at: i64,
31    pub event: String,
32    pub outcome: String,
33    pub profile: String,
34    pub actor_kind: String,
35    pub actor_id: Option<String>,
36    pub account_id: Option<String>,
37    pub order_id: Option<String>,
38    pub cert_serial: Option<String>,
39    pub identifiers: Vec<String>,
40    pub client_ip: Option<String>,
41    pub client_ptr: Option<String>,
42    pub user_agent: Option<String>,
43    pub request_id: Option<String>,
44    pub reason: Option<String>,
45    pub detail: Option<String>,
46}
47
48/// Every column, in one place: the page query, the count and the single-row
49/// read must select the same set or `from_row` fails on whichever forgot one.
50const COLUMNS: &str = "id, created_at, event, outcome, profile, actor_kind, actor_id, \
51                       account_id, order_id, cert_serial, identifiers, client_ip, client_ptr, \
52                       user_agent, request_id, reason, detail";
53
54/// The filters and page window [`AuditEntry::search`] applies.
55///
56/// Every filter is optional and an absent one imposes no constraint, so
57/// `AuditQuery { limit, offset, .. }` is "the newest page of everything".
58#[derive(Debug, Clone, Default)]
59pub struct AuditQuery {
60    pub profile: Option<String>,
61    pub account_id: Option<String>,
62    pub order_id: Option<String>,
63    pub cert_serial: Option<String>,
64    pub event: Option<String>,
65    pub outcome: Option<String>,
66    /// Lower bound on `created_at`, inclusive. The one filter that is not an
67    /// equality, which is why it is applied separately below.
68    pub since: Option<i64>,
69    pub limit: i64,
70    pub offset: i64,
71}
72
73impl AuditQuery {
74    /// Appends the `WHERE` clause shared by the page query and the count — one
75    /// function rather than two copies, for the reason
76    /// [`crate::sqlite::order::OrderQuery::push_predicates`] gives: a filter
77    /// applied to only one of them reports a total the rows contradict.
78    fn push_predicates(&self, builder: &mut sqlx::QueryBuilder<sqlx::Sqlite>) {
79        let mut separator = " WHERE ";
80        for (column, value) in [
81            ("profile = ", self.profile.as_ref()),
82            ("account_id = ", self.account_id.as_ref()),
83            ("order_id = ", self.order_id.as_ref()),
84            ("cert_serial = ", self.cert_serial.as_ref()),
85            ("event = ", self.event.as_ref()),
86            ("outcome = ", self.outcome.as_ref()),
87        ] {
88            if let Some(value) = value {
89                builder
90                    .push(separator)
91                    .push(column)
92                    .push_bind(value.clone());
93                separator = " AND ";
94            }
95        }
96        if let Some(since) = self.since {
97            builder
98                .push(separator)
99                .push("created_at >= ")
100                .push_bind(since);
101        }
102    }
103}
104
105impl AuditEntry {
106    fn from_row(row: SqliteRow) -> Result<Self, sqlx::Error> {
107        let identifiers_json: String = row.try_get("identifiers")?;
108        let identifiers: Vec<String> = serde_json::from_str(&identifiers_json)
109            .map_err(|error| sqlx::Error::Decode(Box::new(error)))?;
110
111        Ok(Self {
112            id: row.try_get("id")?,
113            created_at: row.try_get("created_at")?,
114            event: row.try_get("event")?,
115            outcome: row.try_get("outcome")?,
116            profile: row.try_get("profile")?,
117            actor_kind: row.try_get("actor_kind")?,
118            actor_id: row.try_get("actor_id")?,
119            account_id: row.try_get("account_id")?,
120            order_id: row.try_get("order_id")?,
121            cert_serial: row.try_get("cert_serial")?,
122            identifiers,
123            client_ip: row.try_get("client_ip")?,
124            client_ptr: row.try_get("client_ptr")?,
125            user_agent: row.try_get("user_agent")?,
126            request_id: row.try_get("request_id")?,
127            reason: row.try_get("reason")?,
128            detail: row.try_get("detail")?,
129        })
130    }
131
132    /// The parsed event, or `None` for a value this build does not know — see
133    /// the struct comment.
134    #[must_use]
135    pub fn event(&self) -> Option<AuditEvent> {
136        AuditEvent::parse(&self.event)
137    }
138
139    /// Appends one row, returning its assigned id.
140    ///
141    /// `outcome` is taken from [`AuditEvent::outcome`] rather than the caller,
142    /// so the two columns cannot disagree — there is one definition of which
143    /// events are failures and this is the only place it is read.
144    pub async fn insert(record: AuditRecord, database: &Database) -> Result<i64, sqlx::Error> {
145        let AuditRecord {
146            event,
147            profile,
148            actor,
149            account_id,
150            order_id,
151            cert_serial,
152            identifiers,
153            client,
154            reason,
155            detail,
156        } = record;
157        let Actor { kind, id: actor_id } = actor;
158        let ClientContext {
159            ip: client_ip,
160            ptr: client_ptr,
161            user_agent,
162            request_id,
163        } = client;
164
165        // `identifiers` is a `Vec<String>`, so serialization is infallible.
166        let identifiers_json = Value::from(identifiers).to_string();
167        let created_at = now_secs();
168
169        let id = sqlx::query(
170            "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind, actor_id, \
171             account_id, order_id, cert_serial, identifiers, client_ip, client_ptr, user_agent, \
172             request_id, reason, detail) \
173             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id;",
174        )
175        .bind(created_at)
176        .bind(event.as_str())
177        .bind(event.outcome())
178        .bind(&profile)
179        .bind(kind.as_str())
180        .bind(&actor_id)
181        .bind(&account_id)
182        .bind(&order_id)
183        .bind(&cert_serial)
184        .bind(identifiers_json)
185        .bind(&client_ip)
186        .bind(&client_ptr)
187        .bind(&user_agent)
188        .bind(&request_id)
189        .bind(&reason)
190        .bind(&detail)
191        .fetch_one(&database.pool)
192        .await?
193        .try_get::<i64, _>("id")?;
194
195        debug!(
196            event = "db_audit_row_written",
197            outcome = "success",
198            audit_id = id,
199            audit_event = event.as_str(),
200            profile = %profile,
201            actor_kind = kind.as_str(),
202        );
203        Ok(id)
204    }
205
206    /// One row by id — what `acme-proxy audit show <id>` reads.
207    pub async fn find_by_id(id: i64, database: &Database) -> Result<Option<Self>, sqlx::Error> {
208        // A `QueryBuilder` rather than `sqlx::query`, which takes only
209        // `&'static str` and so cannot be handed the shared `COLUMNS`. `id`
210        // still goes through `push_bind`, so nothing is interpolated.
211        let mut query =
212            sqlx::QueryBuilder::new(format!("SELECT {COLUMNS} FROM audit_log WHERE id = "));
213        query.push_bind(id);
214        let row = query.build().fetch_optional(&database.pool).await?;
215        row.map(Self::from_row).transpose()
216    }
217
218    /// One page of rows matching `query`, newest first, plus the total the same
219    /// predicate matches unpaged.
220    pub async fn search(
221        query: &AuditQuery,
222        database: &Database,
223    ) -> Result<(Vec<Self>, i64), sqlx::Error> {
224        debug!(
225            event = "db_audit_search_started",
226            outcome = "progress",
227            profile = ?query.profile,
228            account_id = ?query.account_id,
229            audit_event = ?query.event,
230            outcome = ?query.outcome,
231            limit = query.limit,
232            offset = query.offset,
233        );
234
235        let mut page = sqlx::QueryBuilder::new(format!("SELECT {COLUMNS} FROM audit_log"));
236        query.push_predicates(&mut page);
237        // Newest first. `id` breaks the tie rather than merely decorating the
238        // clause: `created_at` is whole seconds and this table takes several
239        // rows a second under load, so without it two rows could swap between
240        // pages and one would never be seen.
241        page.push(" ORDER BY created_at DESC, id DESC LIMIT ");
242        page.push_bind(query.limit);
243        page.push(" OFFSET ");
244        page.push_bind(query.offset);
245
246        let rows = page.build().fetch_all(&database.pool).await?;
247        let entries: Vec<Self> = rows
248            .into_iter()
249            .map(Self::from_row)
250            .collect::<Result<_, _>>()?;
251
252        let mut count = sqlx::QueryBuilder::new("SELECT COUNT(*) FROM audit_log");
253        query.push_predicates(&mut count);
254        let total: i64 = count
255            .build()
256            .fetch_one(&database.pool)
257            .await?
258            .try_get::<i64, _>(0)?;
259
260        Ok((entries, total))
261    }
262
263    /// How many rows are older than `cutoff` — what the purge prompt names
264    /// before deleting anything.
265    pub async fn count_older_than(cutoff: i64, database: &Database) -> Result<i64, sqlx::Error> {
266        sqlx::query("SELECT COUNT(*) FROM audit_log WHERE created_at < ?;")
267            .bind(cutoff)
268            .fetch_one(&database.pool)
269            .await?
270            .try_get::<i64, _>(0)
271    }
272
273    /// Deletes every row older than `cutoff`, returning how many went.
274    ///
275    /// The only statement in the crate that removes an audit row, and it takes
276    /// an absolute cutoff rather than a duration so the CLI's `--older-than`
277    /// and the `audit.retention_days` sweep run the identical delete.
278    pub async fn cleanup(cutoff: i64, database: &Database) -> Result<u64, sqlx::Error> {
279        let deleted = sqlx::query("DELETE FROM audit_log WHERE created_at < ?;")
280            .bind(cutoff)
281            .execute(&database.pool)
282            .await?
283            .rows_affected();
284        debug!(
285            event = "db_audit_cleanup",
286            outcome = "success",
287            rows_removed = deleted,
288            cutoff = cutoff
289        );
290        Ok(deleted)
291    }
292
293    /// The JSON shape the admin API serves.
294    ///
295    /// Absent members rather than `null` for the optional columns, the shape
296    /// `Account::to_json` and `Order::to_json` already use: a consumer reading
297    /// `client_ip` off a CLI-initiated revocation gets nothing, which is what
298    /// happened.
299    #[must_use]
300    pub fn to_json(&self) -> Value {
301        let mut value = json!({
302            "id": self.id,
303            "createdAt": rfc3339(self.created_at),
304            "event": self.event,
305            "outcome": self.outcome,
306            "profile": self.profile,
307            "actorKind": self.actor_kind,
308            "identifiers": self.identifiers,
309        });
310        let map = value
311            .as_object_mut()
312            .expect("the literal above is an object");
313        for (key, field) in [
314            ("actorId", self.actor_id.as_ref()),
315            ("accountId", self.account_id.as_ref()),
316            ("orderId", self.order_id.as_ref()),
317            ("certSerial", self.cert_serial.as_ref()),
318            ("clientIp", self.client_ip.as_ref()),
319            ("clientPtr", self.client_ptr.as_ref()),
320            ("userAgent", self.user_agent.as_ref()),
321            ("requestId", self.request_id.as_ref()),
322            ("reason", self.reason.as_ref()),
323            ("detail", self.detail.as_ref()),
324        ] {
325            if let Some(field) = field {
326                map.insert(key.to_string(), Value::from(field.clone()));
327            }
328        }
329        value
330    }
331}
332
333/// The `actor_kind` values, for the CLI help and the page filter. Mirrors
334/// [`crate::audit::ActorKind`]; the `CHECK` in the migration is the authority.
335#[must_use]
336pub fn actor_kinds() -> [&'static str; 4] {
337    [
338        ActorKind::Acme.as_str(),
339        ActorKind::Admin.as_str(),
340        ActorKind::Cli.as_str(),
341        ActorKind::System.as_str(),
342    ]
343}
344
345#[cfg(test)]
346mod tests {
347    use super::*;
348    use crate::audit::{Actor, AuditEvent, ClientContext};
349    use std::sync::Arc;
350
351    async fn db() -> Arc<Database> {
352        Arc::new(Database::connect_in_memory().await.unwrap())
353    }
354
355    fn client() -> ClientContext {
356        ClientContext {
357            ip: Some("203.0.113.7".to_string()),
358            ptr: Some("host.example.com".to_string()),
359            user_agent: Some("certbot/2.9.0".to_string()),
360            request_id: Some("req-1".to_string()),
361        }
362    }
363
364    fn record(event: AuditEvent, profile: &str) -> AuditRecord {
365        AuditRecord::new(event, profile, Actor::acme("acct-1"))
366    }
367
368    /// The two fields production only ever sets through `with_order`, which
369    /// needs a real `Order`. The struct's fields are public, so a test that
370    /// only cares about the column can set them directly rather than justify a
371    /// builder method with no caller outside this file.
372    fn with_subject(mut record: AuditRecord, order_id: &str, identifiers: &[&str]) -> AuditRecord {
373        record.order_id = Some(order_id.to_string());
374        record.identifiers = identifiers.iter().map(|v| (*v).to_string()).collect();
375        record
376    }
377
378    /// The round trip, and the one column no caller sets: `outcome` comes from
379    /// `AuditEvent::outcome`, so an insert cannot get it wrong.
380    #[tokio::test]
381    async fn a_row_round_trips_and_derives_its_own_outcome() {
382        let db = db().await;
383        let id = AuditEntry::insert(
384            with_subject(
385                record(AuditEvent::CertificateIssued, "le"),
386                "order-1",
387                &["a.example.com"],
388            )
389            .with_serial("0a0b")
390            .with_client(client()),
391            &db,
392        )
393        .await
394        .unwrap();
395
396        let entry = AuditEntry::find_by_id(id, &db).await.unwrap().unwrap();
397        assert_eq!(entry.event, "certificate_issued");
398        assert_eq!(entry.outcome, "success");
399        assert_eq!(entry.event(), Some(AuditEvent::CertificateIssued));
400        assert_eq!(entry.profile, "le");
401        assert_eq!(entry.actor_kind, "acme");
402        assert_eq!(entry.actor_id.as_deref(), Some("acct-1"));
403        assert_eq!(entry.order_id.as_deref(), Some("order-1"));
404        assert_eq!(entry.cert_serial.as_deref(), Some("0a0b"));
405        assert_eq!(entry.identifiers, vec!["a.example.com"]);
406        assert_eq!(entry.client_ip.as_deref(), Some("203.0.113.7"));
407        assert_eq!(entry.client_ptr.as_deref(), Some("host.example.com"));
408        assert_eq!(entry.user_agent.as_deref(), Some("certbot/2.9.0"));
409        assert_eq!(entry.request_id.as_deref(), Some("req-1"));
410
411        let failed = AuditEntry::insert(
412            record(AuditEvent::CertificateRevokeFailed, "le").with_reason("unauthorized"),
413            &db,
414        )
415        .await
416        .unwrap();
417        let entry = AuditEntry::find_by_id(failed, &db).await.unwrap().unwrap();
418        assert_eq!(entry.outcome, "failure");
419        assert!(entry.identifiers.is_empty());
420        assert_eq!(entry.client_ip, None);
421
422        assert!(AuditEntry::find_by_id(9_999, &db).await.unwrap().is_none());
423    }
424
425    /// The property the whole table is shaped around: an audit row outlives the
426    /// account and the order it names. There is no foreign key, so deleting
427    /// either must leave the row — and its frozen identifiers — untouched.
428    #[tokio::test]
429    async fn a_row_survives_the_account_and_order_it_names_being_deleted() {
430        let db = db().await;
431        let account_id = crate::testutil::account_id(&db).await;
432        let order = crate::sqlite::order::Order::new(
433            "default",
434            &account_id,
435            vec![crate::sqlite::order::Identifier::dns("a.example.com")],
436            0,
437            None,
438            None,
439        );
440        order.insert(&db.pool).await.unwrap();
441
442        let id = AuditEntry::insert(
443            AuditRecord::new(
444                AuditEvent::CertificateIssued,
445                "default",
446                Actor::acme(&account_id),
447            )
448            .with_order(&order),
449            &db,
450        )
451        .await
452        .unwrap();
453
454        // The cascade takes the order with the account; neither takes the row.
455        crate::sqlite::account::Account::delete(&account_id, &db)
456            .await
457            .unwrap();
458        assert!(
459            crate::sqlite::order::Order::find_by_id(&order.id, &db)
460                .await
461                .unwrap()
462                .is_none()
463        );
464
465        let entry = AuditEntry::find_by_id(id, &db).await.unwrap().unwrap();
466        assert_eq!(entry.account_id.as_deref(), Some(account_id.as_str()));
467        assert_eq!(entry.order_id.as_deref(), Some(order.id.as_str()));
468        assert_eq!(entry.identifiers, vec!["a.example.com"]);
469    }
470
471    /// Every filter, applied to the page *and* the count — a predicate on only
472    /// one of them reports a total the rows contradict.
473    #[tokio::test]
474    async fn every_filter_narrows_the_page_and_the_total_together() {
475        let db = db().await;
476        for (event, profile, account, serial) in [
477            (AuditEvent::CertificateIssued, "le", "acct-1", "aa"),
478            (AuditEvent::CertificateIssued, "le", "acct-2", "bb"),
479            (AuditEvent::CertificateIssueFailed, "le", "acct-1", "cc"),
480            (AuditEvent::CertificateRevoked, "internal", "acct-1", "dd"),
481        ] {
482            AuditEntry::insert(
483                AuditRecord::new(event, profile, Actor::acme(account))
484                    // `Actor::acme` fills `actor_id`; the *subject* column the
485                    // filter reads is `account_id`, and the two are not the
486                    // same field — an admin revoking somebody else's order
487                    // writes a row where they differ.
488                    .with_account(account)
489                    .with_serial(serial),
490                &db,
491            )
492            .await
493            .unwrap();
494        }
495
496        let count = async |query: AuditQuery| {
497            let (rows, total) = AuditEntry::search(&query, &db).await.unwrap();
498            assert_eq!(rows.len() as i64, total, "page and total must agree");
499            total
500        };
501        let base = AuditQuery {
502            limit: 50,
503            ..AuditQuery::default()
504        };
505
506        assert_eq!(count(base.clone()).await, 4);
507        assert_eq!(
508            count(AuditQuery {
509                profile: Some("le".to_string()),
510                ..base.clone()
511            })
512            .await,
513            3
514        );
515        assert_eq!(
516            count(AuditQuery {
517                account_id: Some("acct-1".to_string()),
518                ..base.clone()
519            })
520            .await,
521            3
522        );
523        assert_eq!(
524            count(AuditQuery {
525                cert_serial: Some("dd".to_string()),
526                ..base.clone()
527            })
528            .await,
529            1
530        );
531        assert_eq!(
532            count(AuditQuery {
533                event: Some("certificate_issued".to_string()),
534                ..base.clone()
535            })
536            .await,
537            2
538        );
539        assert_eq!(
540            count(AuditQuery {
541                outcome: Some("failure".to_string()),
542                ..base.clone()
543            })
544            .await,
545            1
546        );
547        // Combined, and an unknown value narrows to nothing rather than erroring.
548        assert_eq!(
549            count(AuditQuery {
550                profile: Some("le".to_string()),
551                outcome: Some("success".to_string()),
552                account_id: Some("acct-1".to_string()),
553                ..base.clone()
554            })
555            .await,
556            1
557        );
558        assert_eq!(
559            count(AuditQuery {
560                event: Some("certificate_renewed".to_string()),
561                ..base
562            })
563            .await,
564            0
565        );
566    }
567
568    /// Newest first, with `id` breaking the tie — every row here lands in the
569    /// same whole second, which is exactly the case the tiebreak exists for:
570    /// without it a row could swap between pages and never be seen.
571    #[tokio::test]
572    async fn paging_one_row_at_a_time_sees_every_row_exactly_once_newest_first() {
573        let db = db().await;
574        let mut inserted = Vec::new();
575        for index in 0..5 {
576            inserted.push(
577                AuditEntry::insert(
578                    record(AuditEvent::CertificateIssued, "le")
579                        .with_serial(format!("serial-{index}")),
580                    &db,
581                )
582                .await
583                .unwrap(),
584            );
585        }
586
587        let mut seen = Vec::new();
588        for offset in 0..5 {
589            let (rows, total) = AuditEntry::search(
590                &AuditQuery {
591                    limit: 1,
592                    offset,
593                    ..AuditQuery::default()
594                },
595                &db,
596            )
597            .await
598            .unwrap();
599            assert_eq!(total, 5);
600            seen.push(rows[0].id);
601        }
602
603        inserted.reverse();
604        assert_eq!(seen, inserted);
605    }
606
607    /// The purge: counted before it runs (that number words the prompt), and
608    /// bounded strictly by the cutoff so a row exactly on it survives.
609    #[tokio::test]
610    async fn cleanup_removes_only_what_is_strictly_older_than_the_cutoff() {
611        let db = db().await;
612        for _ in 0..3 {
613            AuditEntry::insert(record(AuditEvent::CertificateIssued, "le"), &db)
614                .await
615                .unwrap();
616        }
617        let now = crate::sqlite::nonce::now_secs();
618
619        // Nothing is older than "an hour ago".
620        let past = now - 3600;
621        assert_eq!(AuditEntry::count_older_than(past, &db).await.unwrap(), 0);
622        assert_eq!(AuditEntry::cleanup(past, &db).await.unwrap(), 0);
623
624        // Nor exactly at `created_at`: the predicate is `<`, not `<=`.
625        assert_eq!(AuditEntry::count_older_than(now, &db).await.unwrap(), 0);
626
627        let future = now + 3600;
628        assert_eq!(AuditEntry::count_older_than(future, &db).await.unwrap(), 3);
629        assert_eq!(AuditEntry::cleanup(future, &db).await.unwrap(), 3);
630        assert_eq!(AuditEntry::count_older_than(future, &db).await.unwrap(), 0);
631    }
632
633    /// The JSON shape: optional columns are **absent**, not `null`, matching
634    /// `Account::to_json`/`Order::to_json`.
635    #[tokio::test]
636    async fn to_json_omits_the_columns_that_have_no_value() {
637        let db = db().await;
638        let bare = AuditEntry::insert(
639            AuditRecord::new(AuditEvent::CertificateRevoked, "le", Actor::system()),
640            &db,
641        )
642        .await
643        .unwrap();
644        let json = AuditEntry::find_by_id(bare, &db)
645            .await
646            .unwrap()
647            .unwrap()
648            .to_json();
649        let object = json.as_object().unwrap();
650
651        assert_eq!(object["event"], "certificate_revoked");
652        assert_eq!(object["outcome"], "success");
653        assert_eq!(object["actorKind"], "system");
654        assert_eq!(object["identifiers"], serde_json::json!([]));
655        assert!(object["createdAt"].as_str().unwrap().contains('T'));
656        for absent in [
657            "actorId",
658            "accountId",
659            "orderId",
660            "certSerial",
661            "clientIp",
662            "clientPtr",
663            "userAgent",
664            "requestId",
665            "reason",
666            "detail",
667        ] {
668            assert!(!object.contains_key(absent), "{absent} should be absent");
669        }
670
671        let full = AuditEntry::insert(
672            record(AuditEvent::CertificateIssueFailed, "le")
673                .with_client(client())
674                .with_reason("badCSR")
675                .with_detail("nope"),
676            &db,
677        )
678        .await
679        .unwrap();
680        let json = AuditEntry::find_by_id(full, &db)
681            .await
682            .unwrap()
683            .unwrap()
684            .to_json();
685        assert_eq!(json["clientIp"], "203.0.113.7");
686        assert_eq!(json["clientPtr"], "host.example.com");
687        assert_eq!(json["reason"], "badCSR");
688        assert_eq!(json["detail"], "nope");
689    }
690
691    /// An `event` this build does not know still loads: the `CHECK` guards the
692    /// write, and a reader that refused an unrecognised value would make a
693    /// newer server's rows unreadable by an older one.
694    #[tokio::test]
695    async fn an_unknown_event_string_still_loads_and_simply_does_not_parse() {
696        let db = db().await;
697        sqlx::query(
698            "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind) \
699             VALUES (0, 'certificate_issued', 'success', 'le', 'acme');",
700        )
701        .execute(&db.pool)
702        .await
703        .unwrap();
704        let mut entry = AuditEntry::search(
705            &AuditQuery {
706                limit: 1,
707                ..AuditQuery::default()
708            },
709            &db,
710        )
711        .await
712        .unwrap()
713        .0
714        .remove(0);
715        entry.event = "certificate_renewed".to_string();
716        assert_eq!(entry.event(), None);
717    }
718
719    /// The `CHECK` constraints are the schema's own guard on the two enums.
720    #[tokio::test]
721    async fn the_schema_refuses_an_event_outcome_or_actor_it_does_not_know() {
722        let db = db().await;
723        for (event, outcome, actor) in [
724            ("certificate_renewed", "success", "acme"),
725            ("certificate_issued", "maybe", "acme"),
726            ("certificate_issued", "success", "robot"),
727        ] {
728            let error = sqlx::query(
729                "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind) \
730                 VALUES (0, ?, ?, 'le', ?);",
731            )
732            .bind(event)
733            .bind(outcome)
734            .bind(actor)
735            .execute(&db.pool)
736            .await
737            .unwrap_err();
738            assert!(
739                error.to_string().contains("CHECK constraint failed"),
740                "{event}/{outcome}/{actor} was accepted: {error}"
741            );
742        }
743    }
744
745    /// Mirrors `crate::audit::ActorKind`, and is what the CLI help and the page
746    /// filter read.
747    #[test]
748    fn the_actor_kinds_helper_lists_every_variant() {
749        assert_eq!(actor_kinds(), ["acme", "admin", "cli", "system"]);
750    }
751}