Skip to main content

wabot_addon_async_pg/
lib.rs

1//! Postgres `JobRepository` + `CronJobRepository` — port of wabot-ts
2//! `addon/async/pg` (`JobPgQueries` / `CronJobPgQueries`).
3//!
4//! Both are [`PgJsonbStore`] wrappers: the entity lives in a `data`
5//! JSONB column and the queries read it with `data->>'field'`
6//! expressions, exactly like the TS versions, so the SQL is
7//! recognisable side by side.
8//!
9//! ```ignore
10//! let db = PgDatabase::from_env().await?;
11//! let jobs = Arc::new(PgJobRepository::new(db.pool().clone()));
12//! let crons = Arc::new(PgCronJobRepository::new(db.pool().clone()));
13//!
14//! register_job_repository(&c, jobs);
15//! register_cron_job_repository(&c, crons);
16//! register_locker(&c, PgLocker::arc(db.pool().clone())); // multi-node safe
17//! register_async_runtime(&c);
18//! ```
19//!
20//! ## Indexes
21//!
22//! Both stores declare the indexes their hot queries need, created
23//! alongside the table on first use:
24//!
25//! * jobs: a partial index over `scheduledAt` restricted to unfinished
26//!   rows — that's the scheduler's every-tick query, and the partial
27//!   predicate keeps the index small as completed jobs pile up.
28//! * jobs: `(commandName, dedupKey)` for the dedup lookup.
29//! * cron jobs: `nextRunAt` for the due scan, and a unique index on
30//!   `name` so two nodes reconciling the same cron can't create two
31//!   rows.
32
33use std::sync::Arc;
34
35use async_trait::async_trait;
36use chrono::{DateTime, Utc};
37use sqlx::PgPool;
38
39use wabot_feature_async::cron_job::{CronJob, CronJobData};
40use wabot_feature_async::job::{Job, JobData};
41use wabot_feature_async::repository::{CronJobRepository, JobRepository};
42use wabot_feature_async::types::AsyncError;
43use wabot_feature_pg::{PgError, PgJsonbStore};
44
45/// Default table names. Match the TS `@repository({ table })` values
46/// so both stacks address the same rows.
47pub const JOBS_TABLE: &str = "jobs";
48pub const CRON_JOBS_TABLE: &str = "cron_jobs";
49
50// ---------- Job repository ----------------------------------------------
51
52pub struct PgJobRepository {
53    store: PgJsonbStore<JobData>,
54}
55
56impl PgJobRepository {
57    pub fn new(pool: PgPool) -> Self {
58        Self::with_table(pool, JOBS_TABLE)
59    }
60
61    pub fn with_table(pool: PgPool, table: &str) -> Self {
62        let store = PgJsonbStore::new(pool, table)
63            .with_index(
64                "CREATE INDEX IF NOT EXISTS jobs_pending_idx ON \"public\".\"jobs\" \
65                 (((data->>'scheduledAt')::bigint)) \
66                 WHERE data->>'startedAt' IS NULL \
67                   AND data->>'successAt' IS NULL \
68                   AND data->>'failedAt' IS NULL",
69            )
70            .with_index(
71                "CREATE INDEX IF NOT EXISTS jobs_dedup_idx ON \"public\".\"jobs\" \
72                 ((data->>'commandName'), (data->>'dedupKey'))",
73            )
74            .with_index(
75                "CREATE INDEX IF NOT EXISTS jobs_running_idx ON \"public\".\"jobs\" \
76                 ((data->>'commandName')) \
77                 WHERE data->>'startedAt' IS NOT NULL \
78                   AND data->>'successAt' IS NULL \
79                   AND data->>'failedAt' IS NULL",
80            );
81        Self { store }
82    }
83
84    /// Escape hatch for callers that need a custom query against the
85    /// same table.
86    pub fn store(&self) -> &PgJsonbStore<JobData> {
87        &self.store
88    }
89
90    /// Create the table + indexes now instead of on first query.
91    pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
92        self.store.ensure_ready().await.map_err(to_async_error)
93    }
94}
95
96#[async_trait]
97impl JobRepository for PgJobRepository {
98    async fn find(&self, id: &str) -> Result<Option<Job>, AsyncError> {
99        self.store.find_by_id(id).await.map_err(to_async_error)
100    }
101
102    async fn create(&self, job: &Job) -> Result<(), AsyncError> {
103        // Upsert rather than plain insert: the runner re-saves a job
104        // it already created, and a retry after a partial failure
105        // shouldn't trip a primary-key violation.
106        self.store.save(job).await.map_err(to_async_error)
107    }
108
109    async fn update(&self, job: &Job) -> Result<(), AsyncError> {
110        self.store.save(job).await.map_err(to_async_error)
111    }
112
113    async fn delete(&self, job: &Job) -> Result<(), AsyncError> {
114        self.store.delete(job.id()).await.map_err(to_async_error)
115    }
116
117    async fn find_pending_for_run_from(
118        &self,
119        as_of: DateTime<Utc>,
120        limit: usize,
121    ) -> Result<Vec<Job>, AsyncError> {
122        let sql = format!(
123            "SELECT data FROM {t} \
124              WHERE data ? 'scheduledAt' \
125                AND (data->>'scheduledAt')::bigint <= $1 \
126                AND data->>'startedAt' IS NULL \
127                AND data->>'successAt' IS NULL \
128                AND data->>'failedAt' IS NULL \
129              ORDER BY (data->>'scheduledAt')::bigint ASC \
130              LIMIT $2",
131            t = self.store.table()
132        );
133        let ms = as_of.timestamp_millis();
134        self.store
135            .query(&sql, |q| q.bind(ms).bind(limit as i64))
136            .await
137            .map_err(to_async_error)
138    }
139
140    async fn find_running(&self) -> Result<Vec<Job>, AsyncError> {
141        let sql = format!(
142            "SELECT data FROM {t} \
143              WHERE data->>'startedAt' IS NOT NULL \
144                AND data->>'successAt' IS NULL \
145                AND data->>'failedAt' IS NULL",
146            t = self.store.table()
147        );
148        self.store.query(&sql, |q| q).await.map_err(to_async_error)
149    }
150
151    async fn count_running_by_command(&self, command_name: &str) -> Result<usize, AsyncError> {
152        let sql = format!(
153            "SELECT COUNT(*)::bigint FROM {t} \
154              WHERE data->>'startedAt' IS NOT NULL \
155                AND data->>'successAt' IS NULL \
156                AND data->>'failedAt' IS NULL \
157                AND data->>'commandName' = $1",
158            t = self.store.table()
159        );
160        let n = self
161            .store
162            .count(&sql, |q| q.bind(command_name.to_string()))
163            .await
164            .map_err(to_async_error)?;
165        Ok(n.max(0) as usize)
166    }
167
168    async fn find_active_by_dedup_key(
169        &self,
170        command_name: &str,
171        dedup_key: &str,
172        succeeded_since_ms: i64,
173    ) -> Result<Option<Job>, AsyncError> {
174        let sql = format!(
175            "SELECT data FROM {t} \
176              WHERE data->>'commandName' = $1 \
177                AND data->>'dedupKey' = $2 \
178                AND data->>'failedAt' IS NULL \
179                AND ( \
180                  data->>'successAt' IS NULL \
181                  OR (data->>'successAt')::bigint >= $3 \
182                ) \
183              ORDER BY created_at DESC \
184              LIMIT 1",
185            t = self.store.table()
186        );
187        self.store
188            .query_opt(&sql, |q| {
189                q.bind(command_name.to_string())
190                    .bind(dedup_key.to_string())
191                    .bind(succeeded_since_ms)
192            })
193            .await
194            .map_err(to_async_error)
195    }
196}
197
198impl wabot_core::injection::IsSingleton for PgJobRepository {
199    fn register_in(c: &wabot_core::injection::Container) {
200        c.register_singleton_factory::<PgJobRepository, _>(|c2| {
201            let db: Arc<wabot_feature_pg::PgDatabase> = c2.resolve();
202            Arc::new(PgJobRepository::new(db.pool().clone()))
203        });
204    }
205}
206
207// ---------- CronJob repository ------------------------------------------
208
209pub struct PgCronJobRepository {
210    store: PgJsonbStore<CronJobData>,
211}
212
213impl PgCronJobRepository {
214    pub fn new(pool: PgPool) -> Self {
215        Self::with_table(pool, CRON_JOBS_TABLE)
216    }
217
218    pub fn with_table(pool: PgPool, table: &str) -> Self {
219        let store = PgJsonbStore::new(pool, table)
220            .with_index(
221                "CREATE INDEX IF NOT EXISTS cron_jobs_next_run_idx ON \"public\".\"cron_jobs\" \
222                 (((data->>'nextRunAt')::bigint))",
223            )
224            // Unique, not just indexed: reconcile looks a cron up by
225            // name and creates it when missing, so two nodes booting
226            // together must not end up with two rows for one schedule.
227            .with_index(
228                "CREATE UNIQUE INDEX IF NOT EXISTS cron_jobs_name_idx ON \"public\".\"cron_jobs\" \
229                 ((data->>'name'))",
230            );
231        Self { store }
232    }
233
234    pub fn store(&self) -> &PgJsonbStore<CronJobData> {
235        &self.store
236    }
237
238    pub async fn ensure_ready(&self) -> Result<(), AsyncError> {
239        self.store.ensure_ready().await.map_err(to_async_error)
240    }
241}
242
243#[async_trait]
244impl CronJobRepository for PgCronJobRepository {
245    async fn find(&self, id: &str) -> Result<Option<CronJob>, AsyncError> {
246        self.store.find_by_id(id).await.map_err(to_async_error)
247    }
248
249    async fn find_by_name(&self, name: &str) -> Result<Option<CronJob>, AsyncError> {
250        let sql = format!(
251            "SELECT data FROM {t} WHERE data->>'name' = $1 LIMIT 1",
252            t = self.store.table()
253        );
254        self.store
255            .query_opt(&sql, |q| q.bind(name.to_string()))
256            .await
257            .map_err(to_async_error)
258    }
259
260    async fn create(&self, cron: &CronJob) -> Result<(), AsyncError> {
261        self.store.save(cron).await.map_err(to_async_error)
262    }
263
264    async fn update(&self, cron: &CronJob) -> Result<(), AsyncError> {
265        self.store.save(cron).await.map_err(to_async_error)
266    }
267
268    async fn find_due(&self, now: DateTime<Utc>) -> Result<Vec<CronJob>, AsyncError> {
269        let sql = format!(
270            "SELECT data FROM {t} \
271              WHERE data ? 'nextRunAt' \
272                AND (data->>'nextRunAt')::bigint <= $1 \
273                AND (data->>'enabled')::boolean \
274              ORDER BY (data->>'nextRunAt')::bigint ASC",
275            t = self.store.table()
276        );
277        self.store
278            .query(&sql, |q| q.bind(now.timestamp_millis()))
279            .await
280            .map_err(to_async_error)
281    }
282}
283
284impl wabot_core::injection::IsSingleton for PgCronJobRepository {
285    fn register_in(c: &wabot_core::injection::Container) {
286        c.register_singleton_factory::<PgCronJobRepository, _>(|c2| {
287            let db: Arc<wabot_feature_pg::PgDatabase> = c2.resolve();
288            Arc::new(PgCronJobRepository::new(db.pool().clone()))
289        });
290    }
291}
292
293/// Storage failures surface as `AsyncError::Storage`, which the
294/// scheduler loops already log-and-continue on — a database blip
295/// shouldn't kill the worker.
296fn to_async_error(e: PgError) -> AsyncError {
297    AsyncError::Storage(e.to_string())
298}