Skip to main content

wabot_addon_async_sqlite/
lib.rs

1//! SQLite `JobRepository` + `CronJobRepository`.
2//!
3//! The durable counterpart to `wabot-addon-async-in-memory` for a
4//! single node. Both are [`SqliteJsonbStore`] wrappers: the entity
5//! lives in a `data` JSON column and the queries read it with
6//! `data->>'$."field"'`, so the SQL reads side by side with the
7//! Postgres addon's.
8//!
9//! ```ignore
10//! let db = SqliteDatabase::open(SqliteConfig::at("/var/lib/app/app.db"))?;
11//! register_job_repository(&c, Arc::new(SqliteJobRepository::new(db.clone())));
12//! register_cron_job_repository(&c, Arc::new(SqliteCronJobRepository::new(db)));
13//! register_async_runtime(&c);   // InProcessLocker is the right one on one node
14//! ```
15//!
16//! ## Why this exists when there is an in-memory one
17//!
18//! Because jobs that vanish on restart are only acceptable for tests.
19//! A deployment job — build this image, start this container, renew
20//! this certificate — that disappears because the process was upgraded
21//! leaves the system half-changed with nothing recording that it was.
22//! In-memory is right for a unit test and wrong for anything that
23//! survives a reboot.
24//!
25//! ## Field names are camelCase
26//!
27//! `scheduledAt`, not `scheduled_at`. That is the wabot-ts wire
28//! format, and the document is the same shape in both stacks — a rule
29//! worth keeping even where, as here, no TypeScript will ever read
30//! these rows: it is what lets one entity definition serve both.
31
32use std::sync::Arc;
33
34use async_trait::async_trait;
35use chrono::{DateTime, Utc};
36use rusqlite::types::Value as SqlValue;
37
38use wabot_feature_async::cron_job::{CronJob, CronJobData};
39use wabot_feature_async::job::{Job, JobData};
40use wabot_feature_async::repository::{CronJobRepository, JobRepository};
41use wabot_feature_async::types::AsyncError;
42use wabot_feature_sqlite::{SqliteDatabase, SqliteError, SqliteJsonbStore};
43
44/// Default table names. Match the Postgres addon's, so an application
45/// that moves between backends addresses the same tables.
46pub const JOBS_TABLE: &str = "jobs";
47pub const CRON_JOBS_TABLE: &str = "cron_jobs";
48
49/// The JSON extraction for one document field.
50///
51/// A macro rather than a `format!` at each site because these strings
52/// appear in both the queries *and* the index DDL, and an index built
53/// on a subtly different expression is one SQLite will silently never
54/// use.
55macro_rules! field {
56    ($name:literal) => {
57        concat!("\"data\"->>'$.\"", $name, "\"'")
58    };
59}
60
61// ---------- Job repository ----------------------------------------------
62
63pub struct SqliteJobRepository {
64    store: SqliteJsonbStore<JobData>,
65}
66
67impl SqliteJobRepository {
68    pub fn new(database: SqliteDatabase) -> Self {
69        Self::with_table(database, JOBS_TABLE)
70    }
71
72    pub fn with_table(database: SqliteDatabase, table: &str) -> Self {
73        let store = SqliteJsonbStore::new(database, table)
74            // The scheduler's every-tick query. Partial, so the index
75            // stays small as completed jobs pile up — which they do,
76            // and the pending set does not.
77            .with_index(concat!(
78                "CREATE INDEX IF NOT EXISTS jobs_pending_idx ON \"jobs\" (",
79                field!("scheduledAt"),
80                ") WHERE ",
81                field!("startedAt"),
82                " IS NULL AND ",
83                field!("successAt"),
84                " IS NULL AND ",
85                field!("failedAt"),
86                " IS NULL"
87            ))
88            .with_index(concat!(
89                "CREATE INDEX IF NOT EXISTS jobs_dedup_idx ON \"jobs\" (",
90                field!("commandName"),
91                ", ",
92                field!("dedupKey"),
93                ")"
94            ))
95            .with_index(concat!(
96                "CREATE INDEX IF NOT EXISTS jobs_running_idx ON \"jobs\" (",
97                field!("commandName"),
98                ") WHERE ",
99                field!("startedAt"),
100                " IS NOT NULL AND ",
101                field!("successAt"),
102                " IS NULL AND ",
103                field!("failedAt"),
104                " IS NULL"
105            ));
106        Self { store }
107    }
108
109    /// Escape hatch for callers that need a custom query against the
110    /// same table.
111    pub fn store(&self) -> &SqliteJsonbStore<JobData> {
112        &self.store
113    }
114
115    /// Create the table + indexes now instead of on first query.
116    pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
117        self.store.ensure_ready().await.map_err(to_async_error)
118    }
119}
120
121#[async_trait]
122impl JobRepository for SqliteJobRepository {
123    async fn find(&self, id: &str) -> Result<Option<Job>, AsyncError> {
124        self.store.find_by_id(id).await.map_err(to_async_error)
125    }
126
127    async fn create(&self, job: &Job) -> Result<(), AsyncError> {
128        // Upsert rather than plain insert: the runner re-saves a job it
129        // already created, and a retry after a partial failure should
130        // not trip a primary-key violation.
131        self.store.save(job).await.map_err(to_async_error)
132    }
133
134    async fn update(&self, job: &Job) -> Result<(), AsyncError> {
135        self.store.save(job).await.map_err(to_async_error)
136    }
137
138    async fn delete(&self, job: &Job) -> Result<(), AsyncError> {
139        self.store.delete(job.id()).await.map_err(to_async_error)
140    }
141
142    async fn find_pending_for_run_from(
143        &self,
144        as_of: DateTime<Utc>,
145        limit: usize,
146    ) -> Result<Vec<Job>, AsyncError> {
147        // No cast on `scheduledAt`: SQLite's `->>` returns the stored
148        // JSON number as an INTEGER, so this comparison is numeric.
149        // The Postgres version needs `::bigint` because its `->>`
150        // always yields text.
151        let sql = format!(
152            "SELECT \"data\" FROM {t} \
153              WHERE {scheduled} IS NOT NULL \
154                AND {scheduled} <= ?1 \
155                AND {started} IS NULL \
156                AND {success} IS NULL \
157                AND {failed} IS NULL \
158              ORDER BY {scheduled} ASC \
159              LIMIT ?2",
160            t = self.store.table(),
161            scheduled = field!("scheduledAt"),
162            started = field!("startedAt"),
163            success = field!("successAt"),
164            failed = field!("failedAt"),
165        );
166        self.store
167            .query(
168                &sql,
169                vec![
170                    SqlValue::Integer(as_of.timestamp_millis()),
171                    SqlValue::Integer(limit as i64),
172                ],
173            )
174            .await
175            .map_err(to_async_error)
176    }
177
178    async fn find_running(&self) -> Result<Vec<Job>, AsyncError> {
179        let sql = format!(
180            "SELECT \"data\" FROM {t} \
181              WHERE {started} IS NOT NULL \
182                AND {success} IS NULL \
183                AND {failed} IS NULL",
184            t = self.store.table(),
185            started = field!("startedAt"),
186            success = field!("successAt"),
187            failed = field!("failedAt"),
188        );
189        self.store
190            .query(&sql, Vec::new())
191            .await
192            .map_err(to_async_error)
193    }
194
195    async fn count_running_by_command(&self, command_name: &str) -> Result<usize, AsyncError> {
196        let sql = format!(
197            "SELECT COUNT(*) FROM {t} \
198              WHERE {started} IS NOT NULL \
199                AND {success} IS NULL \
200                AND {failed} IS NULL \
201                AND {command} = ?1",
202            t = self.store.table(),
203            started = field!("startedAt"),
204            success = field!("successAt"),
205            failed = field!("failedAt"),
206            command = field!("commandName"),
207        );
208        let count = self
209            .store
210            .count(&sql, vec![SqlValue::Text(command_name.to_string())])
211            .await
212            .map_err(to_async_error)?;
213        Ok(count.max(0) as usize)
214    }
215
216    async fn find_active_by_dedup_key(
217        &self,
218        command_name: &str,
219        dedup_key: &str,
220        succeeded_since_ms: i64,
221    ) -> Result<Option<Job>, AsyncError> {
222        let sql = format!(
223            "SELECT \"data\" FROM {t} \
224              WHERE {command} = ?1 \
225                AND {dedup} = ?2 \
226                AND {failed} IS NULL \
227                AND ({success} IS NULL OR {success} >= ?3) \
228              ORDER BY \"created_at\" DESC \
229              LIMIT 1",
230            t = self.store.table(),
231            command = field!("commandName"),
232            dedup = field!("dedupKey"),
233            failed = field!("failedAt"),
234            success = field!("successAt"),
235        );
236        self.store
237            .query_opt(
238                &sql,
239                vec![
240                    SqlValue::Text(command_name.to_string()),
241                    SqlValue::Text(dedup_key.to_string()),
242                    SqlValue::Integer(succeeded_since_ms),
243                ],
244            )
245            .await
246            .map_err(to_async_error)
247    }
248}
249
250impl wabot_core::injection::IsSingleton for SqliteJobRepository {
251    fn register_in(c: &wabot_core::injection::Container) {
252        c.register_singleton_factory::<SqliteJobRepository, _>(|c2| {
253            let database: Arc<SqliteDatabase> = c2.resolve();
254            Arc::new(SqliteJobRepository::new(database.as_ref().clone()))
255        });
256    }
257}
258
259// ---------- CronJob repository ------------------------------------------
260
261pub struct SqliteCronJobRepository {
262    store: SqliteJsonbStore<CronJobData>,
263}
264
265impl SqliteCronJobRepository {
266    pub fn new(database: SqliteDatabase) -> Self {
267        Self::with_table(database, CRON_JOBS_TABLE)
268    }
269
270    pub fn with_table(database: SqliteDatabase, table: &str) -> Self {
271        let store = SqliteJsonbStore::new(database, table)
272            .with_index(concat!(
273                "CREATE INDEX IF NOT EXISTS cron_jobs_next_run_idx ON \"cron_jobs\" (",
274                field!("nextRunAt"),
275                ")"
276            ))
277            // Unique, not merely indexed: reconcile looks a cron up by
278            // name and creates it when missing. One node cannot race
279            // itself, but a restart mid-reconcile can, and two rows for
280            // one schedule means it fires twice.
281            .with_index(concat!(
282                "CREATE UNIQUE INDEX IF NOT EXISTS cron_jobs_name_idx ON \"cron_jobs\" (",
283                field!("name"),
284                ")"
285            ));
286        Self { store }
287    }
288
289    pub fn store(&self) -> &SqliteJsonbStore<CronJobData> {
290        &self.store
291    }
292
293    pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
294        self.store.ensure_ready().await.map_err(to_async_error)
295    }
296}
297
298#[async_trait]
299impl CronJobRepository for SqliteCronJobRepository {
300    async fn find(&self, id: &str) -> Result<Option<CronJob>, AsyncError> {
301        self.store.find_by_id(id).await.map_err(to_async_error)
302    }
303
304    async fn find_by_name(&self, name: &str) -> Result<Option<CronJob>, AsyncError> {
305        let sql = format!(
306            "SELECT \"data\" FROM {t} WHERE {name} = ?1 LIMIT 1",
307            t = self.store.table(),
308            name = field!("name"),
309        );
310        self.store
311            .query_opt(&sql, vec![SqlValue::Text(name.to_string())])
312            .await
313            .map_err(to_async_error)
314    }
315
316    async fn create(&self, cron: &CronJob) -> Result<(), AsyncError> {
317        self.store.save(cron).await.map_err(to_async_error)
318    }
319
320    async fn update(&self, cron: &CronJob) -> Result<(), AsyncError> {
321        self.store.save(cron).await.map_err(to_async_error)
322    }
323
324    async fn find_due(&self, now: DateTime<Utc>) -> Result<Vec<CronJob>, AsyncError> {
325        // A JSON `true` comes back from `->>` as the integer 1, which
326        // is why this compares against 1 rather than casting to a
327        // boolean type SQLite does not have.
328        let sql = format!(
329            "SELECT \"data\" FROM {t} \
330              WHERE {next} IS NOT NULL \
331                AND {next} <= ?1 \
332                AND {enabled} = 1 \
333              ORDER BY {next} ASC",
334            t = self.store.table(),
335            next = field!("nextRunAt"),
336            enabled = field!("enabled"),
337        );
338        self.store
339            .query(&sql, vec![SqlValue::Integer(now.timestamp_millis())])
340            .await
341            .map_err(to_async_error)
342    }
343}
344
345impl wabot_core::injection::IsSingleton for SqliteCronJobRepository {
346    fn register_in(c: &wabot_core::injection::Container) {
347        c.register_singleton_factory::<SqliteCronJobRepository, _>(|c2| {
348            let database: Arc<SqliteDatabase> = c2.resolve();
349            Arc::new(SqliteCronJobRepository::new(database.as_ref().clone()))
350        });
351    }
352}
353
354/// Storage failures surface as `AsyncError::Storage`, which the
355/// scheduler loops already log-and-continue on — a database blip
356/// should not kill the worker.
357fn to_async_error(error: SqliteError) -> AsyncError {
358    AsyncError::Storage(error.to_string())
359}
360
361#[cfg(test)]
362mod tests {
363    use super::*;
364    use serde_json::json;
365    use wabot_core::entity::EntityData;
366    use wabot_feature_async::cron_job::{self, CronJobData};
367    use wabot_feature_async::job::new_job;
368    use wabot_feature_async::types::MisfirePolicy;
369
370    fn ts(ms: i64) -> DateTime<Utc> {
371        DateTime::<Utc>::from_timestamp_millis(ms).unwrap()
372    }
373
374    fn job(command: &str, scheduled_at: Option<i64>, dedup_key: Option<&str>) -> Job {
375        new_job(JobData {
376            base: EntityData::default(),
377            command_name: command.into(),
378            command_data: json!({}),
379            scheduled_at,
380            started_at: None,
381            success_at: None,
382            failed_at: None,
383            retry_delays_seconds: None,
384            intent_number: None,
385            error: None,
386            acceptable_running_time_seconds: None,
387            stuck_retry_attempts: None,
388            dedup_key: dedup_key.map(str::to_string),
389            actor: None,
390            request_id: None,
391        })
392    }
393
394    async fn jobs() -> SqliteJobRepository {
395        let repository = SqliteJobRepository::new(SqliteDatabase::in_memory().expect("open"));
396        repository.ensure_ready().await.expect("ready");
397        repository
398    }
399
400    #[tokio::test]
401    async fn a_job_round_trips() {
402        let repository = jobs().await;
403        let job = job("ping", Some(1_000), None);
404        repository.create(&job).await.expect("create");
405
406        let found = repository
407            .find(job.id())
408            .await
409            .expect("find")
410            .expect("present");
411        assert_eq!(found.data().command_name, "ping");
412
413        repository.delete(&job).await.expect("delete");
414        assert!(repository.find(job.id()).await.expect("find").is_none());
415    }
416
417    /// `create` upserts, because the runner re-saves a job it already
418    /// created and a retry must not trip the primary key.
419    #[tokio::test]
420    async fn creating_the_same_job_twice_is_not_an_error() {
421        let repository = jobs().await;
422        let job = job("ping", Some(1), None);
423        repository.create(&job).await.expect("create");
424        repository.create(&job).await.expect("create again");
425    }
426
427    /// The scheduler's every-tick query, including the parts that keep
428    /// finished work out of it.
429    #[tokio::test]
430    async fn pending_finds_only_unstarted_jobs_that_are_due() {
431        let repository = jobs().await;
432
433        repository
434            .create(&job("due", Some(1_000), None))
435            .await
436            .unwrap();
437        repository
438            .create(&job("later", Some(9_000), None))
439            .await
440            .unwrap();
441        repository
442            .create(&job("unscheduled", None, None))
443            .await
444            .unwrap();
445
446        let mut started = job("started", Some(1_000), None);
447        started.update(|data| data.started_at = Some(1_500));
448        repository.create(&started).await.unwrap();
449
450        let mut done = job("done", Some(1_000), None);
451        done.update(|data| data.success_at = Some(1_500));
452        repository.create(&done).await.unwrap();
453
454        let pending = repository
455            .find_pending_for_run_from(ts(2_000), 10)
456            .await
457            .expect("pending");
458        assert_eq!(
459            pending
460                .iter()
461                .map(|j| j.data().command_name.as_str())
462                .collect::<Vec<_>>(),
463            vec!["due"]
464        );
465    }
466
467    /// The claim `find_pending_for_run_from` rests on: `scheduledAt`
468    /// compares as a number. Bound as text, 9000 would sort before
469    /// 10000 and the limit would return the wrong jobs.
470    #[tokio::test]
471    async fn pending_orders_numerically_not_lexicographically() {
472        let repository = jobs().await;
473        for at in [9_000, 10_000, 800] {
474            repository
475                .create(&job(&format!("j{at}"), Some(at), None))
476                .await
477                .unwrap();
478        }
479
480        let pending = repository
481            .find_pending_for_run_from(ts(20_000), 2)
482            .await
483            .expect("pending");
484        assert_eq!(
485            pending
486                .iter()
487                .map(|j| j.data().scheduled_at.unwrap())
488                .collect::<Vec<_>>(),
489            vec![800, 9_000],
490            "lexicographic ordering would have put 10000 before 800 and 9000"
491        );
492    }
493
494    #[tokio::test]
495    async fn running_jobs_are_counted_per_command() {
496        let repository = jobs().await;
497
498        for command in ["build", "build", "deploy"] {
499            let mut running = job(command, Some(1), None);
500            running.update(|data| data.started_at = Some(2));
501            repository.create(&running).await.unwrap();
502        }
503        // Started but finished: not running.
504        let mut finished = job("build", Some(1), None);
505        finished.update(|data| {
506            data.started_at = Some(2);
507            data.success_at = Some(3);
508        });
509        repository.create(&finished).await.unwrap();
510
511        assert_eq!(repository.find_running().await.unwrap().len(), 3);
512        assert_eq!(
513            repository.count_running_by_command("build").await.unwrap(),
514            2
515        );
516        assert_eq!(
517            repository.count_running_by_command("absent").await.unwrap(),
518            0
519        );
520    }
521
522    #[tokio::test]
523    async fn dedup_finds_an_active_job_and_respects_the_success_window() {
524        let repository = jobs().await;
525
526        repository
527            .create(&job("send", Some(1), Some("key-1")))
528            .await
529            .unwrap();
530        assert!(repository
531            .find_active_by_dedup_key("send", "key-1", 0)
532            .await
533            .unwrap()
534            .is_some());
535        assert!(
536            repository
537                .find_active_by_dedup_key("send", "other", 0)
538                .await
539                .unwrap()
540                .is_none(),
541            "a different key is a different job"
542        );
543
544        // A job that succeeded long ago is outside the window.
545        let mut old = job("send", Some(1), Some("key-2"));
546        old.update(|data| data.success_at = Some(1_000));
547        repository.create(&old).await.unwrap();
548        assert!(repository
549            .find_active_by_dedup_key("send", "key-2", 5_000)
550            .await
551            .unwrap()
552            .is_none());
553        assert!(repository
554            .find_active_by_dedup_key("send", "key-2", 500)
555            .await
556            .unwrap()
557            .is_some());
558
559        // A failed job never dedups against a new one.
560        let mut failed = job("send", Some(1), Some("key-3"));
561        failed.update(|data| data.failed_at = Some(1_000));
562        repository.create(&failed).await.unwrap();
563        assert!(repository
564            .find_active_by_dedup_key("send", "key-3", 0)
565            .await
566            .unwrap()
567            .is_none());
568    }
569
570    fn cron(name: &str, next_run_at: Option<i64>, enabled: bool) -> CronJob {
571        cron_job::new_cron_job(CronJobData {
572            base: EntityData::default(),
573            name: name.into(),
574            command_name: "ping".into(),
575            command_data: None,
576            cron: "0 0 * * * *".into(),
577            enabled,
578            last_run_at: None,
579            next_run_at,
580            max_running_jobs: Some(1),
581            misfire_policy: Some(MisfirePolicy::RunOnce),
582            retry_delays_seconds: None,
583            acceptable_running_time_seconds: None,
584            stuck_retry_attempts: None,
585        })
586        .unwrap()
587    }
588
589    async fn crons() -> SqliteCronJobRepository {
590        let repository = SqliteCronJobRepository::new(SqliteDatabase::in_memory().expect("open"));
591        repository.ensure_ready().await.expect("ready");
592        repository
593    }
594
595    #[tokio::test]
596    async fn a_cron_round_trips_and_is_findable_by_name() {
597        let repository = crons().await;
598        let cron = cron("nightly", Some(1_000), true);
599        repository.create(&cron).await.expect("create");
600
601        assert_eq!(
602            repository
603                .find_by_name("nightly")
604                .await
605                .expect("find")
606                .expect("present")
607                .id(),
608            cron.id()
609        );
610        assert!(repository.find_by_name("absent").await.unwrap().is_none());
611    }
612
613    #[tokio::test]
614    async fn due_crons_exclude_disabled_and_future_ones() {
615        let repository = crons().await;
616        repository
617            .create(&cron("due", Some(1_000), true))
618            .await
619            .unwrap();
620        repository
621            .create(&cron("later", Some(9_000), true))
622            .await
623            .unwrap();
624        repository
625            .create(&cron("off", Some(1_000), false))
626            .await
627            .unwrap();
628        repository.create(&cron("never", None, true)).await.unwrap();
629
630        let due = repository.find_due(ts(2_000)).await.expect("due");
631        assert_eq!(
632            due.iter()
633                .map(|c| c.data().name.as_str())
634                .collect::<Vec<_>>(),
635            vec!["due"],
636            "a JSON `true` reads back as 1, and a disabled cron must not fire"
637        );
638    }
639
640    /// Two rows for one schedule means it fires twice, so the name
641    /// index is unique rather than merely present.
642    #[tokio::test]
643    async fn two_crons_cannot_share_a_name() {
644        let repository = crons().await;
645        repository
646            .create(&cron("nightly", Some(1), true))
647            .await
648            .unwrap();
649        assert!(
650            repository
651                .create(&cron("nightly", Some(1), true))
652                .await
653                .is_err(),
654            "the unique index refused the duplicate"
655        );
656    }
657}