Skip to main content

khive_runtime/curation/
outbound_health.rs

1use chrono::{DateTime, Utc};
2use khive_storage::{SqlRow, SqlStatement, SqlValue};
3
4use super::{KhiveRuntime, NamespaceToken, RuntimeError, RuntimeResult};
5
6/// Uncapped metadata observation of one channel's eligible undelivered rows.
7/// The delivery page is a separate read and keeps its own bounded row count.
8#[derive(Clone, Debug, PartialEq, Eq)]
9pub struct OutboundHealthSummary {
10    pub observed_at: DateTime<Utc>,
11    pub undelivered_rows: u64,
12    pub backoff_rows: u64,
13    pub oldest_undelivered_created_at: Option<DateTime<Utc>>,
14    pub oldest_backoff_created_at: Option<DateTime<Utc>>,
15    pub earliest_backoff_next_attempt_at: Option<DateTime<Utc>>,
16    pub max_backoff_delivery_attempts: Option<u64>,
17}
18
19impl KhiveRuntime {
20    /// Read backlog metadata on the runtime that owns comm's notes.
21    /// Missing-slug rows belong here only when the caller has one adapter of this kind.
22    pub async fn outbound_health_for_channel(
23        &self,
24        token: &NamespaceToken,
25        to_prefix: &str,
26        channel_slug: &str,
27        include_legacy: bool,
28    ) -> RuntimeResult<OutboundHealthSummary> {
29        self.outbound_health_for_channel_at(
30            token,
31            to_prefix,
32            channel_slug,
33            include_legacy,
34            Utc::now(),
35        )
36        .await
37    }
38
39    async fn outbound_health_for_channel_at(
40        &self,
41        token: &NamespaceToken,
42        to_prefix: &str,
43        channel_slug: &str,
44        include_legacy: bool,
45        observed_at: DateTime<Utc>,
46    ) -> RuntimeResult<OutboundHealthSummary> {
47        if !to_prefix
48            .strip_suffix(':')
49            .is_some_and(|head| !head.is_empty() && !head.contains(':'))
50        {
51            return Err(RuntimeError::InvalidInput(
52                "outbox health requires a complete channel prefix ending in one colon".into(),
53            ));
54        }
55        let _ = self.notes(token)?;
56        // Match delivery's microsecond comparison, including the rest of this microsecond.
57        let due_through = DateTime::from_timestamp_micros(observed_at.timestamp_micros())
58            .and_then(|at| at.checked_add_signed(chrono::Duration::nanoseconds(999)))
59            .ok_or_else(|| invalid_summary("observation timestamp"))?;
60        let row = self
61            .sql()
62            .reader()
63            .await?
64            .query_row(SqlStatement {
65                sql: crate::sql!("outbound_channel_health_summary").into(),
66                params: vec![
67                    SqlValue::Text(token.namespace().as_str().into()),
68                    SqlValue::Text(to_prefix.into()),
69                    SqlValue::Text(channel_slug.into()),
70                    SqlValue::Integer(i64::from(include_legacy)),
71                    SqlValue::Text(due_through.to_rfc3339()),
72                ],
73                label: Some("outbound-channel-health".into()),
74            })
75            .await?
76            .ok_or_else(|| invalid_summary("aggregate row"))?;
77        let max_backoff_delivery_attempts = match row.get("max_backoff_delivery_attempts") {
78            Some(SqlValue::Null) => None,
79            Some(SqlValue::Text(raw)) => Some(
80                raw.parse::<u64>()
81                    .map_err(|_| invalid_summary("max_backoff_delivery_attempts"))?,
82            ),
83            _ => return Err(invalid_summary("max_backoff_delivery_attempts")),
84        };
85        let earliest_backoff_next_attempt_at = match row.get("earliest_backoff_next_attempt_at") {
86            Some(SqlValue::Null) => None,
87            Some(SqlValue::Text(raw)) => Some(
88                DateTime::parse_from_rfc3339(raw)
89                    .map_err(|_| invalid_summary("earliest_backoff_next_attempt_at"))?
90                    .with_timezone(&Utc),
91            ),
92            _ => return Err(invalid_summary("earliest_backoff_next_attempt_at")),
93        };
94        Ok(OutboundHealthSummary {
95            observed_at,
96            undelivered_rows: count(&row, "undelivered_rows")?,
97            backoff_rows: count(&row, "backoff_rows")?,
98            oldest_undelivered_created_at: timestamp(&row, "oldest_undelivered_created_at")?,
99            oldest_backoff_created_at: timestamp(&row, "oldest_backoff_created_at")?,
100            earliest_backoff_next_attempt_at,
101            max_backoff_delivery_attempts,
102        })
103    }
104}
105
106fn invalid_summary(field: &str) -> RuntimeError {
107    RuntimeError::Internal(format!("outbox health: invalid {field}"))
108}
109
110fn count(row: &SqlRow, field: &str) -> RuntimeResult<u64> {
111    match row.get(field) {
112        Some(SqlValue::Integer(value)) => u64::try_from(*value).map_err(|_| invalid_summary(field)),
113        _ => Err(invalid_summary(field)),
114    }
115}
116
117fn timestamp(row: &SqlRow, field: &str) -> RuntimeResult<Option<DateTime<Utc>>> {
118    match row.get(field) {
119        Some(SqlValue::Null) => Ok(None),
120        Some(SqlValue::Integer(value)) => DateTime::from_timestamp_micros(*value)
121            .map(Some)
122            .ok_or_else(|| invalid_summary(field)),
123        _ => Err(invalid_summary(field)),
124    }
125}
126
127#[cfg(test)]
128mod tests {
129    use super::*;
130    use crate::Namespace;
131    use khive_storage::Note;
132    use serde_json::{json, Value};
133
134    fn at(raw: &str) -> DateTime<Utc> {
135        DateTime::parse_from_rfc3339(raw)
136            .unwrap()
137            .with_timezone(&Utc)
138    }
139
140    fn fixture() -> (KhiveRuntime, NamespaceToken) {
141        let runtime = KhiveRuntime::memory().unwrap();
142        runtime.install_pack_owned_note_kinds(vec!["message".into()]);
143        let token = runtime.authorize(Namespace::local()).unwrap();
144        (runtime, token)
145    }
146
147    fn pending() -> Value {
148        json!({
149            "direction": "outbound", "to_actor": "email:recipient@example.com",
150            "channel_slug": "sender@example.com", "next_attempt_at": "2035-01-01T00:00:00Z",
151            "delivery_attempts": 9
152        })
153    }
154
155    async fn seed(runtime: &KhiveRuntime, namespace: &str, properties: Value) -> Note {
156        let mut note = Note::new(namespace, "message", "body is not needed by the summary");
157        note.created_at = at("2020-01-01T00:00:00Z").timestamp_micros();
158        note.properties = Some(properties);
159        runtime
160            .backend()
161            .notes_for_namespace(namespace)
162            .unwrap()
163            .upsert_note(note.clone())
164            .await
165            .unwrap();
166        note
167    }
168
169    async fn summary(
170        runtime: &KhiveRuntime,
171        token: &NamespaceToken,
172        legacy: bool,
173    ) -> OutboundHealthSummary {
174        runtime
175            .outbound_health_for_channel_at(
176                token,
177                "email:",
178                "sender@example.com",
179                legacy,
180                at("2030-01-01T00:00:00Z"),
181            )
182            .await
183            .unwrap()
184    }
185
186    #[tokio::test]
187    async fn backlog_preserves_channel_namespace_slug_and_delivery_eligibility() {
188        let (runtime, token) = fixture();
189        seed(&runtime, "local", pending()).await;
190        let mut legacy = pending();
191        legacy.as_object_mut().unwrap().remove("channel_slug");
192        seed(&runtime, "local", legacy).await;
193        let mut due = pending();
194        due["next_attempt_at"] = json!("1990-01-01T00:00:00Z");
195        let due_id = seed(&runtime, "local", due).await.id;
196        let mut invalid_deadline = pending();
197        let relaxed_only_deadline = "2035-01-01T00:00:00+0000";
198        assert!(DateTime::parse_from_rfc3339(relaxed_only_deadline).is_err());
199        assert!(relaxed_only_deadline.parse::<DateTime<Utc>>().is_ok());
200        invalid_deadline["next_attempt_at"] = json!(relaxed_only_deadline);
201        let invalid_deadline_id = seed(&runtime, "local", invalid_deadline).await.id;
202        let mut nullable = pending();
203        nullable["delivered_at"] = Value::Null;
204        nullable["delivery_hold"] = Value::Null;
205        seed(&runtime, "local", nullable).await;
206        for (field, value) in [
207            ("channel_slug", json!("another@example.com")),
208            ("channel_slug", Value::Null),
209            ("channel_slug", json!("")),
210            ("direction", json!("inbound")),
211            ("to_actor", json!("lambda:recipient")),
212            ("to_actor", json!("emailx:recipient")),
213            ("to_actor", json!("xemail:recipient")),
214            ("delivery", json!("failed")),
215            ("delivery", json!("delivered")),
216            ("delivery_hold", json!(false)),
217            ("delivered_at", json!("")),
218        ] {
219            let mut properties = pending();
220            properties[field] = value;
221            seed(&runtime, "local", properties).await;
222        }
223        seed(&runtime, "tenant-other", pending()).await;
224        let mut deleted = seed(&runtime, "local", pending()).await;
225        deleted.deleted_at = Some(deleted.created_at);
226        runtime
227            .backend()
228            .notes_for_namespace("local")
229            .unwrap()
230            .upsert_note(deleted)
231            .await
232            .unwrap();
233        let mut other_kind = seed(&runtime, "local", pending()).await;
234        other_kind.kind = "observation".into();
235        runtime
236            .backend()
237            .notes_for_namespace("local")
238            .unwrap()
239            .upsert_note(other_kind)
240            .await
241            .unwrap();
242
243        let exact = summary(&runtime, &token, false).await;
244        assert_eq!((exact.undelivered_rows, exact.backoff_rows), (4, 2));
245        let with_legacy = summary(&runtime, &token, true).await;
246        assert_eq!(
247            (with_legacy.undelivered_rows, with_legacy.backoff_rows),
248            (5, 3)
249        );
250        assert_eq!(with_legacy.max_backoff_delivery_attempts, Some(9));
251        // All accepted due examples are also due at the real delivery query's current clock.
252        let due = runtime
253            .list_undelivered_outbound_messages_for_channel(
254                &token,
255                "email:",
256                "sender@example.com",
257                true,
258                200,
259            )
260            .await
261            .unwrap();
262        assert_eq!(due.len(), 2);
263        assert_eq!(
264            due.iter()
265                .map(|note| note.id)
266                .collect::<std::collections::HashSet<_>>(),
267            std::collections::HashSet::from([due_id, invalid_deadline_id]),
268            "delivery must include the overdue and malformed-deadline rows only"
269        );
270        assert!(runtime
271            .outbound_health_for_channel(&token, "email", "sender@example.com", true)
272            .await
273            .is_err());
274    }
275
276    #[tokio::test]
277    async fn backlog_uses_strict_instant_order_and_delivery_microsecond_boundary() {
278        let (runtime, token) = fixture();
279        let observed = at("2030-01-01T00:00:00.123456100Z");
280        for deadline in [
281            "2030-01-01T00:00:00.123456999Z",
282            "2030-01-01T00:00:00.123457000Z",
283            // Lexically earlier than the preceding value but two hours later as an instant.
284            "2029-12-31T23:00:00-03:00",
285        ] {
286            let mut properties = pending();
287            properties["next_attempt_at"] = json!(deadline);
288            seed(&runtime, "local", properties).await;
289        }
290        let mut stale_key = seed(&runtime, "local", pending()).await;
291        stale_key.properties.as_mut().unwrap()["next_attempt_at"] =
292            json!("2030-01-01T00:00:00.123456999Z");
293        runtime
294            .sql()
295            .writer()
296            .await
297            .unwrap()
298            .execute(SqlStatement {
299                sql: "UPDATE notes SET properties=?1 WHERE id=?2".into(),
300                params: vec![
301                    SqlValue::Text(stale_key.properties.unwrap().to_string()),
302                    SqlValue::Text(stale_key.id.to_string()),
303                ],
304                label: None,
305            })
306            .await
307            .unwrap();
308        let summary = runtime
309            .outbound_health_for_channel_at(&token, "email:", "sender@example.com", false, observed)
310            .await
311            .unwrap();
312        assert_eq!((summary.undelivered_rows, summary.backoff_rows), (4, 2));
313        assert_eq!(
314            summary.earliest_backoff_next_attempt_at,
315            Some(at("2030-01-01T00:00:00.123457Z"))
316        );
317        assert_eq!(summary.observed_at, observed);
318    }
319
320    #[tokio::test]
321    async fn backlog_attempts_preserve_u64_values_and_ignore_other_json_types() {
322        let (runtime, token) = fixture();
323        let mut note = seed(&runtime, "local", pending()).await;
324        for (value, expected) in [
325            (json!(9), 9),
326            (json!(10), 10),
327            (json!(i64::MAX as u64 + 1), i64::MAX as u64 + 1),
328            (json!(u64::MAX), u64::MAX),
329            (json!(-1), 0),
330            (json!(1.0), 0),
331            (json!("42"), 0),
332            (Value::Null, 0),
333            (json!(true), 0),
334            (json!([]), 0),
335        ] {
336            note.properties.as_mut().unwrap()["delivery_attempts"] = value.clone();
337            runtime
338                .backend()
339                .notes_for_namespace("local")
340                .unwrap()
341                .upsert_note(note.clone())
342                .await
343                .unwrap();
344            assert_eq!(
345                summary(&runtime, &token, false)
346                    .await
347                    .max_backoff_delivery_attempts,
348                Some(expected),
349                "{value}"
350            );
351        }
352        let mut nine = pending();
353        nine["delivery_attempts"] = json!(9);
354        seed(&runtime, "local", nine).await;
355        let mut ten = pending();
356        ten["delivery_attempts"] = json!(10);
357        seed(&runtime, "local", ten).await;
358        assert_eq!(
359            summary(&runtime, &token, false)
360                .await
361                .max_backoff_delivery_attempts,
362            Some(10)
363        );
364        note.properties.as_mut().unwrap()["delivery_attempts"] = json!(u64::MAX);
365        runtime
366            .backend()
367            .notes_for_namespace("local")
368            .unwrap()
369            .upsert_note(note)
370            .await
371            .unwrap();
372        assert_eq!(
373            summary(&runtime, &token, false)
374                .await
375                .max_backoff_delivery_attempts,
376            Some(u64::MAX)
377        );
378    }
379
380    #[tokio::test]
381    async fn backlog_is_not_capped_by_either_delivery_page_limit() {
382        let (runtime, token) = fixture();
383        let template = seed(&runtime, "local", pending()).await;
384        runtime.sql().writer().await.unwrap().execute(SqlStatement {
385            sql: "WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<10001)
386                  INSERT INTO notes(id,namespace,kind,status,content,properties,created_at,updated_at,version)
387                  SELECT printf('00000000-0000-4000-8000-%012d',i), namespace,kind,status,content,properties,created_at,updated_at,version
388                  FROM n CROSS JOIN notes WHERE id=?1".into(),
389            params: vec![SqlValue::Text(template.id.to_string())], label: None,
390        }).await.unwrap();
391        let backlog = summary(&runtime, &token, false).await;
392        assert_eq!(
393            (backlog.undelivered_rows, backlog.backoff_rows),
394            (10002, 10002)
395        );
396        assert!(runtime
397            .list_undelivered_outbound_messages_for_channel(
398                &token,
399                "email:",
400                "sender@example.com",
401                false,
402                200
403            )
404            .await
405            .unwrap()
406            .is_empty());
407        assert_eq!(
408            backlog.oldest_backoff_created_at,
409            Some(at("2020-01-01T00:00:00Z"))
410        );
411    }
412
413    #[tokio::test]
414    async fn backlog_reads_the_comm_backend_instead_of_its_core_backend() {
415        let core_backend = std::sync::Arc::new(crate::StorageBackend::memory().unwrap());
416        core_backend.prepare_core_schema().unwrap();
417        let core = KhiveRuntime::from_backend(
418            core_backend.clone(),
419            crate::RuntimeConfig {
420                db_path: None,
421                ..crate::RuntimeConfig::no_embeddings()
422            },
423        );
424        let comm_backend = std::sync::Arc::new(crate::StorageBackend::memory().unwrap());
425        comm_backend.prepare_core_schema().unwrap();
426        let runtime = KhiveRuntime::from_backend(
427            comm_backend,
428            crate::RuntimeConfig {
429                db_path: None,
430                backend_id: crate::BackendId::parse("comm").unwrap(),
431                ..crate::RuntimeConfig::no_embeddings()
432            },
433        )
434        .with_core_backend(core_backend);
435        runtime.install_pack_owned_note_kinds(vec!["message".into()]);
436        let token = runtime.authorize(Namespace::local()).unwrap();
437        seed(&runtime, "local", pending()).await;
438        for _ in 0..3 {
439            let mut properties = pending();
440            properties["delivery_attempts"] = json!(500);
441            seed(&core, "local", properties).await;
442        }
443        let observed = summary(&runtime, &token, false).await;
444        assert_eq!((observed.undelivered_rows, observed.backoff_rows), (1, 1));
445        assert_eq!(observed.max_backoff_delivery_attempts, Some(9));
446    }
447
448    fn backlog_rows(runtime: &KhiveRuntime) -> Vec<Vec<rusqlite::types::Value>> {
449        runtime
450            .backend()
451            .pool()
452            .writer()
453            .unwrap()
454            .autocommit_sqlite(|conn| {
455                conn.prepare("SELECT * FROM notes ORDER BY id")?
456                    .query_map([], |row| {
457                        (0..row.as_ref().column_count())
458                            .map(|i| row.get(i))
459                            .collect()
460                    })?
461                    .collect()
462            })
463            .unwrap()
464    }
465
466    fn measure_backlog(
467        runtime: &KhiveRuntime,
468        legacy: bool,
469    ) -> (Vec<rusqlite::types::Value>, i32, Vec<String>) {
470        runtime
471            .backend()
472            .pool()
473            .writer()
474            .unwrap()
475            .autocommit_sqlite(|conn| {
476                let sql = crate::sql!("outbound_channel_health_summary");
477                let legacy = i64::from(legacy);
478                let params = rusqlite::params![
479                    "local",
480                    "email:",
481                    "sender@example.com",
482                    legacy,
483                    "2030-01-01T00:00:00.000000999Z"
484                ];
485                let plan = conn
486                    .prepare(&format!("EXPLAIN QUERY PLAN {sql}"))?
487                    .query_map(params, |row| row.get(3))?
488                    .collect::<rusqlite::Result<Vec<_>>>()?;
489                let mut statement = conn.prepare(sql)?;
490                let mut result = statement.query(params)?;
491                let row = result.next()?.expect("aggregate row");
492                let values = (0..6)
493                    .map(|i| row.get(i))
494                    .collect::<rusqlite::Result<Vec<_>>>()?;
495                assert!(result.next()?.is_none(), "exactly one aggregate row");
496                drop(result);
497                Ok((
498                    values,
499                    statement.get_status(rusqlite::StatementStatus::VmStep),
500                    plan,
501                ))
502            })
503            .unwrap()
504    }
505
506    async fn file_backlog_fixture(
507        history: usize,
508    ) -> (tempfile::TempDir, KhiveRuntime, NamespaceToken) {
509        let dir = tempfile::tempdir().unwrap();
510        let path = dir.path().join("outbox.db");
511        let backend = std::sync::Arc::new(
512            crate::StorageBackend::sqlite_for_test_with_journal_mode_in(
513                &path,
514                true,
515                std::time::Duration::from_secs(5),
516                dir.path().join("locks"),
517            )
518            .unwrap(),
519        );
520        backend.prepare_core_schema().unwrap();
521        let runtime = KhiveRuntime::from_backend(
522            backend,
523            crate::RuntimeConfig {
524                db_path: Some(path),
525                ..crate::RuntimeConfig::no_embeddings()
526            },
527        );
528        runtime.install_pack_owned_note_kinds(vec!["message".into()]);
529        let token = runtime.authorize(Namespace::local()).unwrap();
530        for i in 0..25 {
531            let mut note = Note::new("local", "message", "retained pending message");
532            note.id = uuid::Uuid::from_u128(i + 1);
533            note.created_at = at("2020-01-01T00:00:00Z").timestamp_micros()
534                + i64::try_from(i / 2).unwrap() * 1_000_000;
535            note.updated_at = note.created_at;
536            let mut props = pending();
537            props["next_attempt_at"] = json!(if i < 3 || (15..17).contains(&i) {
538                "1990-01-01T00:00:00Z"
539            } else {
540                "2999-01-01T00:00:00Z"
541            });
542            props["delivery_attempts"] = json!(if i == 14 {
543                u64::MAX - 1
544            } else if i == 24 {
545                u64::MAX
546            } else {
547                9
548            });
549            if i >= 15 {
550                props.as_object_mut().unwrap().remove("channel_slug");
551            }
552            note.properties = Some(props);
553            // Seed retained transport metadata through the privileged fixture store.
554            runtime
555                .backend()
556                .notes_for_namespace(token.namespace().as_str())
557                .unwrap()
558                .upsert_note(note)
559                .await
560                .unwrap();
561        }
562        runtime.backend().pool().writer().unwrap().transaction(|conn| {
563            {
564                let mut insert = conn.prepare(
565                    "INSERT INTO notes(id,namespace,kind,content,properties,created_at,updated_at)
566                     VALUES (?1,'local','message','retained history',?2,1,1)",
567                )?;
568                for i in 0..history {
569                    insert.execute(rusqlite::params![
570                        format!("10000000-0000-4000-8000-{i:012}"),
571                        json!({"direction":"outbound","to_actor":"email:recipient@example.com",
572                            "channel_slug":"sender@example.com"}).to_string(),
573                    ])?;
574                }
575                for i in 0..1000 {
576                    for (prefix, direction) in [("20000000", "outbound"), ("30000000", "inbound")] {
577                        insert.execute(rusqlite::params![
578                            format!("{prefix}-0000-4000-8000-{i:012}"),
579                            json!({"direction":direction,"to_actor":"other:recipient"}).to_string(),
580                        ])?;
581                    }
582                }
583            }
584            assert_eq!(conn.execute(
585                "UPDATE notes SET properties=json_set(properties,'$.delivered_at','2020-01-01T00:00:00Z')
586                 WHERE id >= '10000000' AND id < '20000000'", [],
587            )?, history);
588            let mode: String = conn.query_row("PRAGMA journal_mode", [], |row| row.get(0))?;
589            assert_eq!(mode, "wal");
590            Ok(())
591        }).unwrap();
592        (dir, runtime, token)
593    }
594
595    #[tokio::test]
596    async fn outbound_backlog_work_is_independent_of_delivered_history() {
597        use rusqlite::types::Value as SqliteValue;
598        let (_small_dir, small, small_token) = file_backlog_fixture(1000).await;
599        let (_large_dir, large, large_token) = file_backlog_fixture(10_000).await;
600        for stage in ["fresh", "analyzed"] {
601            if stage == "analyzed" {
602                for runtime in [&small, &large] {
603                    runtime
604                        .backend()
605                        .pool()
606                        .writer()
607                        .unwrap()
608                        .autocommit_sqlite(|conn| conn.execute_batch("ANALYZE"))
609                        .unwrap();
610                }
611            }
612            let small_before = backlog_rows(&small);
613            let large_before = backlog_rows(&large);
614            assert_eq!(small_before.len(), 3025);
615            assert_eq!(large_before.len(), 12_025);
616            for legacy in [false, true] {
617                let maximum = if legacy { u64::MAX } else { u64::MAX - 1 };
618                let expected = OutboundHealthSummary {
619                    observed_at: at("2030-01-01T00:00:00Z"),
620                    undelivered_rows: if legacy { 25 } else { 15 },
621                    backoff_rows: if legacy { 20 } else { 12 },
622                    oldest_undelivered_created_at: Some(at("2020-01-01T00:00:00Z")),
623                    oldest_backoff_created_at: Some(at("2020-01-01T00:00:01Z")),
624                    earliest_backoff_next_attempt_at: Some(at("2999-01-01T00:00:00Z")),
625                    max_backoff_delivery_attempts: Some(maximum),
626                };
627                let expected_row = vec![
628                    SqliteValue::Integer(i64::try_from(expected.undelivered_rows).unwrap()),
629                    SqliteValue::Integer(i64::try_from(expected.backoff_rows).unwrap()),
630                    SqliteValue::Integer(
631                        expected
632                            .oldest_undelivered_created_at
633                            .unwrap()
634                            .timestamp_micros(),
635                    ),
636                    SqliteValue::Integer(
637                        expected
638                            .oldest_backoff_created_at
639                            .unwrap()
640                            .timestamp_micros(),
641                    ),
642                    SqliteValue::Text(format!("{maximum:020}")),
643                    SqliteValue::Text("2999-01-01T00:00:00Z".into()),
644                ];
645                let (small_row, small_steps, small_plan) = measure_backlog(&small, legacy);
646                let (large_row, large_steps, large_plan) = measure_backlog(&large, legacy);
647                assert_eq!(small_row, expected_row, "{stage}, legacy={legacy}");
648                assert_eq!(large_row, expected_row);
649                for plan in [&small_plan, &large_plan] {
650                    assert!(
651                        plan.iter()
652                            .any(|line| line.contains("idx_comm_message_outbound_due")
653                                && line
654                                    .contains("namespace=? AND kind=? AND <expr>=? AND <expr>=?")),
655                        "{stage}, legacy={legacy}: aggregate lost the bucket seek: {plan:?}"
656                    );
657                }
658                assert!(large_steps <= small_steps + 128,
659                    "{stage}, legacy={legacy}: delivered history added aggregate work: {small_steps} -> {large_steps}");
660                for (runtime, token) in [(&small, &small_token), (&large, &large_token)] {
661                    assert_eq!(summary(runtime, token, legacy).await, expected);
662                    let expected_ids: Vec<_> = if legacy {
663                        vec![17, 16, 3, 1, 2]
664                    } else {
665                        vec![3, 1, 2]
666                    }
667                    .into_iter()
668                    .map(uuid::Uuid::from_u128)
669                    .collect();
670                    let due = runtime
671                        .list_undelivered_outbound_messages_for_channel(
672                            token,
673                            "email:",
674                            "sender@example.com",
675                            legacy,
676                            200,
677                        )
678                        .await
679                        .unwrap();
680                    assert_eq!(
681                        due.iter().map(|note| note.id).collect::<Vec<_>>(),
682                        expected_ids
683                    );
684                }
685            }
686            assert_eq!(backlog_rows(&small), small_before);
687            assert_eq!(backlog_rows(&large), large_before);
688        }
689    }
690
691    #[tokio::test]
692    async fn outbound_delivery_and_raw_marker_changes_preserve_index_membership() {
693        let (runtime, token) = fixture();
694        let mut props = pending();
695        props["next_attempt_at"] = json!("1990-01-01T00:00:00Z");
696        let note = seed(&runtime, "local", props.clone()).await;
697        let control = seed(&runtime, "local", pending()).await;
698        let original = backlog_rows(&runtime);
699        let before = summary(&runtime, &token, false).await;
700        assert_eq!((before.undelivered_rows, before.backoff_rows), (2, 1));
701        let due = runtime
702            .list_undelivered_outbound_messages_for_channel(
703                &token,
704                "email:",
705                "sender@example.com",
706                false,
707                200,
708            )
709            .await
710            .unwrap();
711        assert_eq!(due.iter().map(|row| row.id).collect::<Vec<_>>(), [note.id]);
712        assert_eq!(backlog_rows(&runtime), original);
713        let delivered = runtime
714            .mark_outbound_message_delivered(
715                &token,
716                note.id,
717                "2020-01-02T00:00:00Z".into(),
718                Some("transport-id".into()),
719            )
720            .await
721            .unwrap();
722        assert_eq!(
723            runtime
724                .notes(&token)
725                .unwrap()
726                .get_note(note.id)
727                .await
728                .unwrap(),
729            Some(delivered.clone())
730        );
731        assert_eq!((delivered.id, &delivered.content), (note.id, &note.content));
732        assert_eq!(
733            delivered.properties.as_ref().unwrap()["delivery"],
734            json!("delivered")
735        );
736        assert_eq!(
737            delivered.properties.as_ref().unwrap()["delivered_at"],
738            json!("2020-01-02T00:00:00Z")
739        );
740        assert!(delivered
741            .properties
742            .as_ref()
743            .unwrap()
744            .get("next_attempt_at")
745            .is_none());
746        let stamped = backlog_rows(&runtime);
747        assert_eq!(stamped.len(), original.len());
748        assert_eq!(summary(&runtime, &token, false).await.undelivered_rows, 1);
749        assert!(runtime
750            .list_undelivered_outbound_messages_for_channel(
751                &token,
752                "email:",
753                "sender@example.com",
754                false,
755                200,
756            )
757            .await
758            .unwrap()
759            .is_empty());
760        assert_eq!(backlog_rows(&runtime), stamped);
761        for marker in [
762            None,
763            Some(Value::Null),
764            Some(json!("")),
765            Some(json!("2020-01-01T00:00:00Z")),
766            Some(json!("invalid")),
767            Some(json!(0)),
768            Some(json!(42)),
769            Some(json!(false)),
770            Some(json!(true)),
771            Some(json!([])),
772            Some(json!({})),
773            None,
774            Some(Value::Null),
775        ] {
776            let eligible = marker.as_ref().is_none_or(Value::is_null);
777            let mut edited = props.clone();
778            if let Some(value) = marker {
779                edited["delivered_at"] = value;
780            }
781            runtime
782                .backend()
783                .pool()
784                .writer()
785                .unwrap()
786                .autocommit_sqlite(|conn| {
787                    conn.execute(
788                        "UPDATE notes SET properties=?1 WHERE id=?2",
789                        rusqlite::params![edited.to_string(), note.id.to_string()],
790                    )
791                })
792                .unwrap();
793            let baseline = backlog_rows(&runtime);
794            let observed = summary(&runtime, &token, false).await;
795            assert_eq!(
796                (observed.undelivered_rows, observed.backoff_rows),
797                (if eligible { 2 } else { 1 }, 1),
798                "properties={edited}"
799            );
800            let due = runtime
801                .list_undelivered_outbound_messages_for_channel(
802                    &token,
803                    "email:",
804                    "sender@example.com",
805                    false,
806                    200,
807                )
808                .await
809                .unwrap();
810            assert_eq!(
811                due.iter().map(|row| row.id).collect::<Vec<_>>(),
812                if eligible { vec![note.id] } else { vec![] },
813                "properties={edited}"
814            );
815            assert_eq!(backlog_rows(&runtime), baseline);
816        }
817        runtime
818            .backend()
819            .pool()
820            .writer()
821            .unwrap()
822            .autocommit_sqlite(|conn| {
823                conn.execute(
824                    "UPDATE notes SET properties=NULL WHERE id=?1",
825                    [note.id.to_string()],
826                )
827            })
828            .unwrap();
829        let baseline = backlog_rows(&runtime);
830        assert_eq!(summary(&runtime, &token, false).await.undelivered_rows, 1);
831        assert!(runtime
832            .list_undelivered_outbound_messages_for_channel(
833                &token,
834                "email:",
835                "sender@example.com",
836                false,
837                200,
838            )
839            .await
840            .unwrap()
841            .is_empty());
842        assert_eq!(
843            runtime
844                .notes(&token)
845                .unwrap()
846                .get_note(control.id)
847                .await
848                .unwrap(),
849            Some(control)
850        );
851        assert_eq!(backlog_rows(&runtime), baseline);
852    }
853}