Skip to main content

azums/backend/
postgres.rs

1use async_trait::async_trait;
2use azums_core::{
3    backend::{NotificationStream, StorageBackend, StreamBackend},
4    model::{ConsumerGroupStatus, Event, Job, JobListItem, NewEvent, NewJob},
5};
6use chrono::{DateTime, Utc};
7use sqlx::{PgPool, Postgres, Transaction};
8use uuid::Uuid;
9
10use crate::{
11    db::run_migrations,
12    jobs::{
13        attempts::AttemptsRepo, maintenance::MaintenanceRepo, repo::JobsRepo,
14        stream_repo::StreamRepo,
15    },
16};
17
18/// PostgreSQL implementation of [`StorageBackend`] using SQLx.
19/// # Examples
20///
21/// ```rust,no_run
22/// use azums::PostgresBackend;
23/// use sqlx::postgres::PgPoolOptions;
24///
25/// let pool = PgPoolOptions::new()
26///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
27/// let backend = PostgresBackend::new(pool);
28/// assert!(!backend.pool().is_closed());
29/// # Ok::<(), sqlx::Error>(())
30/// ```
31#[derive(Clone)]
32pub struct PostgresBackend {
33    pool: PgPool,
34    jobs_repo: JobsRepo,
35    attempts_repo: AttemptsRepo,
36    maintenance_repo: MaintenanceRepo,
37    stream_repo: StreamRepo,
38}
39
40/// # Examples
41///
42/// ```rust,no_run
43/// use azums::PostgresBackend;
44/// use sqlx::postgres::PgPoolOptions;
45///
46/// let pool = PgPoolOptions::new()
47///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
48/// let backend = PostgresBackend::new(pool);
49/// assert!(!backend.pool().is_closed());
50/// # Ok::<(), sqlx::Error>(())
51/// ```
52impl PostgresBackend {
53    /// Creates a new `PostgresBackend` from a SQLx connection pool.
54    /// # Examples
55    ///
56    /// ```rust,no_run
57    /// use azums::PostgresBackend;
58    /// use sqlx::postgres::PgPoolOptions;
59    ///
60    /// let pool = PgPoolOptions::new()
61    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
62    /// let backend = PostgresBackend::new(pool);
63    /// assert!(!backend.pool().is_closed());
64    /// # Ok::<(), sqlx::Error>(())
65    /// ```
66    pub fn new(pool: PgPool) -> Self {
67        Self {
68            jobs_repo: JobsRepo::new(pool.clone()),
69            attempts_repo: AttemptsRepo::new(pool.clone()),
70            maintenance_repo: MaintenanceRepo::new(pool.clone()),
71            stream_repo: StreamRepo::new(pool.clone()),
72            pool,
73        }
74    }
75
76    /// Creates a new `PostgresBackend` with a pool and database connection URL for dedicated `LISTEN` sockets.
77    /// # Examples
78    ///
79    /// ```rust,no_run
80    /// use azums::PostgresBackend;
81    /// use sqlx::postgres::PgPoolOptions;
82    ///
83    /// let pool = PgPoolOptions::new()
84    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
85    /// let backend = PostgresBackend::new(pool);
86    /// assert!(!backend.pool().is_closed());
87    /// # Ok::<(), sqlx::Error>(())
88    /// ```
89    pub fn new_with_url(pool: PgPool, database_url: impl Into<String>) -> Self {
90        let url_str = database_url.into();
91        Self {
92            jobs_repo: JobsRepo::new_with_url(pool.clone(), url_str),
93            attempts_repo: AttemptsRepo::new(pool.clone()),
94            maintenance_repo: MaintenanceRepo::new(pool.clone()),
95            stream_repo: StreamRepo::new(pool.clone()),
96            pool,
97        }
98    }
99
100    /// Returns reference to the underlying SQLx `PgPool`.
101    /// # Examples
102    ///
103    /// ```rust,no_run
104    /// use azums::PostgresBackend;
105    /// use sqlx::postgres::PgPoolOptions;
106    ///
107    /// let pool = PgPoolOptions::new()
108    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
109    /// let backend = PostgresBackend::new(pool);
110    /// assert!(!backend.pool().is_closed());
111    /// # Ok::<(), sqlx::Error>(())
112    /// ```
113    pub fn pool(&self) -> &PgPool {
114        &self.pool
115    }
116
117    /// Returns reference to the underlying `JobsRepo`.
118    /// # Examples
119    ///
120    /// ```rust,no_run
121    /// use azums::PostgresBackend;
122    /// use sqlx::postgres::PgPoolOptions;
123    ///
124    /// let pool = PgPoolOptions::new()
125    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
126    /// let backend = PostgresBackend::new(pool);
127    /// assert!(!backend.pool().is_closed());
128    /// # Ok::<(), sqlx::Error>(())
129    /// ```
130    pub fn jobs_repo(&self) -> &JobsRepo {
131        &self.jobs_repo
132    }
133
134    /// Returns reference to the underlying `AttemptsRepo`.
135    /// # Examples
136    ///
137    /// ```rust,no_run
138    /// use azums::PostgresBackend;
139    /// use sqlx::postgres::PgPoolOptions;
140    ///
141    /// let pool = PgPoolOptions::new()
142    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
143    /// let backend = PostgresBackend::new(pool);
144    /// assert!(!backend.pool().is_closed());
145    /// # Ok::<(), sqlx::Error>(())
146    /// ```
147    pub fn attempts_repo(&self) -> &AttemptsRepo {
148        &self.attempts_repo
149    }
150
151    /// Returns reference to the underlying `StreamRepo`.
152    /// # Examples
153    ///
154    /// ```rust,no_run
155    /// use azums::PostgresBackend;
156    /// use sqlx::postgres::PgPoolOptions;
157    ///
158    /// let pool = PgPoolOptions::new()
159    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
160    /// let backend = PostgresBackend::new(pool);
161    /// assert!(!backend.pool().is_closed());
162    /// # Ok::<(), sqlx::Error>(())
163    /// ```
164    pub fn stream_repo(&self) -> &StreamRepo {
165        &self.stream_repo
166    }
167
168    /// Returns reference to the underlying `MaintenanceRepo`.
169    /// # Examples
170    ///
171    /// ```rust,no_run
172    /// use azums::PostgresBackend;
173    /// use sqlx::postgres::PgPoolOptions;
174    ///
175    /// let pool = PgPoolOptions::new()
176    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
177    /// let backend = PostgresBackend::new(pool);
178    /// assert!(!backend.pool().is_closed());
179    /// # Ok::<(), sqlx::Error>(())
180    /// ```
181    pub fn maintenance_repo(&self) -> &MaintenanceRepo {
182        &self.maintenance_repo
183    }
184
185    /// Inserts a job using the caller's PostgreSQL transaction.
186    /// # Examples
187    ///
188    /// ```rust,no_run
189    /// use azums::PostgresBackend;
190    /// use sqlx::postgres::PgPoolOptions;
191    ///
192    /// let pool = PgPoolOptions::new()
193    ///     .connect_lazy("postgres://postgres:postgres@localhost/azums")?;
194    /// let backend = PostgresBackend::new(pool);
195    /// assert!(!backend.pool().is_closed());
196    /// # Ok::<(), sqlx::Error>(())
197    /// ```
198    pub async fn enqueue_in_tx(
199        &self,
200        tx: &mut Transaction<'_, Postgres>,
201        job: NewJob,
202    ) -> anyhow::Result<Uuid> {
203        self.jobs_repo.enqueue_in_tx(tx, job).await
204    }
205}
206
207#[async_trait]
208impl StorageBackend for PostgresBackend {
209    fn capabilities(&self) -> azums_core::BackendCapabilities {
210        azums_core::BackendCapabilities::postgres()
211    }
212
213    fn as_stream(&self) -> Option<&dyn StreamBackend> {
214        Some(self)
215    }
216
217    async fn run_migrations(&self) -> anyhow::Result<()> {
218        run_migrations(&self.pool).await
219    }
220
221    async fn health_check(&self) -> anyhow::Result<()> {
222        sqlx::query("SELECT 1").execute(&self.pool).await?;
223        Ok(())
224    }
225
226    async fn enqueue(&self, job: NewJob) -> anyhow::Result<Uuid> {
227        self.jobs_repo.enqueue(job).await
228    }
229
230    async fn subscribe(&self, queue: &str) -> anyhow::Result<NotificationStream> {
231        self.jobs_repo.subscribe(queue).await
232    }
233
234    async fn lease_jobs_batch(
235        &self,
236        queue: &str,
237        worker_id: &str,
238        lease_seconds: i64,
239        batch_size: i64,
240    ) -> anyhow::Result<Vec<Job>> {
241        self.jobs_repo
242            .lease_jobs_batch(queue, worker_id, lease_seconds, batch_size)
243            .await
244    }
245
246    async fn lease_jobs_batch_with_ordering(
247        &self,
248        queue: &str,
249        worker_id: &str,
250        lease_seconds: i64,
251        batch_size: i64,
252        ordering: azums_core::QueueOrdering,
253    ) -> anyhow::Result<Vec<Job>> {
254        self.jobs_repo
255            .lease_jobs_batch_with_ordering(queue, worker_id, lease_seconds, batch_size, ordering)
256            .await
257    }
258
259    async fn reap_expired_locks(&self) -> anyhow::Result<u64> {
260        let count = self.jobs_repo.reap_expired_locks().await?;
261        Ok(count as u64)
262    }
263
264    async fn start_attempts_batch(
265        &self,
266        dataset_ids: &[String],
267        job_ids: &[Uuid],
268        worker_id: &str,
269    ) -> anyhow::Result<Vec<(Uuid, Uuid, i32)>> {
270        self.attempts_repo
271            .start_attempts_batch(dataset_ids, job_ids, worker_id)
272            .await
273    }
274
275    async fn mark_succeeded(
276        &self,
277        job_id: Uuid,
278        attempt_id: Uuid,
279        worker_id: &str,
280        latency_ms: i32,
281    ) -> anyhow::Result<()> {
282        let mut tx = self.pool.begin().await?;
283
284        let attempt_res = sqlx::query(
285            r#"
286            UPDATE job_attempts
287            SET status = 'succeeded',
288                finished_at = now(),
289                latency_ms = $2
290            WHERE id = $1
291              AND job_id = $3
292              AND status = 'running'
293            "#,
294        )
295        .bind(attempt_id)
296        .bind(latency_ms)
297        .bind(job_id)
298        .execute(&mut *tx)
299        .await?;
300        if attempt_res.rows_affected() != 1 {
301            anyhow::bail!(
302                "cannot complete attempt {attempt_id}: expected running attempt for job {job_id}"
303            );
304        }
305
306        let completed_job = sqlx::query_as::<_, Job>(
307            r#"
308            UPDATE jobs
309            SET status = 'succeeded',
310                locked_at = NULL,
311                locked_by = NULL,
312                lock_expires_at = NULL,
313                updated_at = now()
314            WHERE id = $1
315              AND locked_by = $2
316              AND status = 'running'
317            RETURNING *
318            "#,
319        )
320        .bind(job_id)
321        .bind(worker_id)
322        .fetch_optional(&mut *tx)
323        .await?;
324        let Some(completed_job) = completed_job else {
325            anyhow::bail!(
326                "illegal job state transition to completed for job {job_id}: expected running lease held by {worker_id}"
327            );
328        };
329
330        if let Some(interval_seconds) = completed_job.recurring_interval_seconds {
331            let next_run_at =
332                completed_job.run_at + chrono::Duration::seconds(interval_seconds.max(1));
333            let next_deadline_at = completed_job
334                .deadline_at
335                .map(|deadline| deadline + chrono::Duration::seconds(interval_seconds.max(1)));
336            let next_dataset_id =
337                crate::jobs::JobsRepo::dataset_id_for(&completed_job.queue, next_run_at);
338            self.jobs_repo
339                .ensure_dataset_partition(&next_dataset_id)
340                .await?;
341
342            sqlx::query(
343                r#"
344                INSERT INTO jobs (
345                    dataset_id, replay_of_job_id, idempotency_key,
346                    queue, job_type, payload_json, run_at,
347                    deadline_at, timeout_seconds, recurring_interval_seconds,
348                    status, priority, max_attempts
349                )
350                VALUES (
351                    $1, $2, NULL,
352                    $3, $4, $5, $6,
353                    $7::timestamptz, $8::integer, $9::integer,
354                    'queued', $10, $11
355                )
356                "#,
357            )
358            .bind(next_dataset_id)
359            .bind(completed_job.id)
360            .bind(&completed_job.queue)
361            .bind(&completed_job.job_type)
362            .bind(&completed_job.payload)
363            .bind(next_run_at)
364            .bind(next_deadline_at)
365            .bind(completed_job.timeout_seconds)
366            .bind(completed_job.recurring_interval_seconds)
367            .bind(completed_job.priority)
368            .bind(completed_job.max_attempts)
369            .execute(&mut *tx)
370            .await?;
371        }
372
373        tx.commit().await?;
374        Ok(())
375    }
376
377    async fn mark_succeeded_batch(
378        &self,
379        dataset_id: &str,
380        updates: &[(Uuid, Uuid, i32)],
381        worker_id: &str,
382    ) -> anyhow::Result<()> {
383        if updates.is_empty() {
384            return Ok(());
385        }
386
387        let mut tx = self.pool.begin().await?;
388
389        for &(job_id, attempt_id, latency_ms) in updates {
390            let attempt_res = sqlx::query(
391                r#"
392                UPDATE job_attempts
393                SET status = 'succeeded',
394                    finished_at = now(),
395                    latency_ms = $2
396                WHERE dataset_id = $1
397                  AND id = $3
398                  AND job_id = $4
399                  AND status = 'running'
400                "#,
401            )
402            .bind(dataset_id)
403            .bind(latency_ms)
404            .bind(attempt_id)
405            .bind(job_id)
406            .execute(&mut *tx)
407            .await?;
408            if attempt_res.rows_affected() != 1 {
409                anyhow::bail!(
410                    "cannot complete attempt {attempt_id}: expected running attempt for job {job_id}"
411                );
412            }
413
414            let job_res = sqlx::query(
415                r#"
416                UPDATE jobs
417                SET status = 'succeeded',
418                    locked_at = NULL,
419                    locked_by = NULL,
420                    lock_expires_at = NULL,
421                    updated_at = now()
422                WHERE dataset_id = $1
423                  AND id = $2
424                  AND locked_by = $3
425                  AND status = 'running'
426                "#,
427            )
428            .bind(dataset_id)
429            .bind(job_id)
430            .bind(worker_id)
431            .execute(&mut *tx)
432            .await?;
433            if job_res.rows_affected() != 1 {
434                anyhow::bail!(
435                    "illegal job state transition to completed for job {job_id}: expected running lease held by {worker_id}"
436                );
437            }
438        }
439
440        tx.commit().await?;
441        Ok(())
442    }
443
444    #[allow(clippy::too_many_arguments)]
445    async fn reschedule_for_retry(
446        &self,
447        job_id: Uuid,
448        attempt_id: Uuid,
449        worker_id: &str,
450        latency_ms: i32,
451        next_run_at: DateTime<Utc>,
452        error_code: &str,
453        error_message: &str,
454        _attempt_no: i32,
455    ) -> anyhow::Result<()> {
456        let mut tx = self.pool.begin().await?;
457
458        let attempt_res = sqlx::query(
459            r#"
460            UPDATE job_attempts
461            SET status = 'failed',
462                finished_at = now(),
463                latency_ms = $2,
464                error_code = $3,
465                error_message = $4
466            WHERE id = $1
467              AND job_id = $5
468              AND status = 'running'
469            "#,
470        )
471        .bind(attempt_id)
472        .bind(latency_ms)
473        .bind(error_code)
474        .bind(error_message)
475        .bind(job_id)
476        .execute(&mut *tx)
477        .await?;
478        if attempt_res.rows_affected() != 1 {
479            anyhow::bail!(
480                "cannot fail attempt {attempt_id}: expected running attempt for job {job_id}"
481            );
482        }
483
484        let job_res = sqlx::query(
485            r#"
486            UPDATE jobs
487            SET status = 'queued',
488                run_at = $2,
489                locked_at = NULL,
490                locked_by = NULL,
491                lock_expires_at = NULL,
492                last_error_code = $4,
493                last_error_message = $5,
494                updated_at = now()
495            WHERE id = $1
496              AND locked_by = $3
497              AND status = 'running'
498            "#,
499        )
500        .bind(job_id)
501        .bind(next_run_at)
502        .bind(worker_id)
503        .bind(error_code)
504        .bind(error_message)
505        .execute(&mut *tx)
506        .await?;
507        if job_res.rows_affected() != 1 {
508            anyhow::bail!(
509                "illegal job state transition to retry_wait for job {job_id}: expected running lease held by {worker_id}"
510            );
511        }
512
513        tx.commit().await?;
514        Ok(())
515    }
516
517    #[allow(clippy::too_many_arguments)]
518    async fn mark_dlq(
519        &self,
520        job_id: Uuid,
521        attempt_id: Uuid,
522        worker_id: &str,
523        latency_ms: i32,
524        reason_code: &str,
525        error_code: &str,
526        error_message: &str,
527        _attempt_no: i32,
528    ) -> anyhow::Result<()> {
529        let mut tx = self.pool.begin().await?;
530
531        let attempt_res = sqlx::query(
532            r#"
533            UPDATE job_attempts
534            SET status = 'failed',
535                finished_at = now(),
536                latency_ms = $2,
537                error_code = $3,
538                error_message = $4
539            WHERE id = $1
540              AND job_id = $5
541              AND status = 'running'
542            "#,
543        )
544        .bind(attempt_id)
545        .bind(latency_ms)
546        .bind(error_code)
547        .bind(error_message)
548        .bind(job_id)
549        .execute(&mut *tx)
550        .await?;
551        if attempt_res.rows_affected() != 1 {
552            anyhow::bail!(
553                "cannot fail attempt {attempt_id}: expected running attempt for job {job_id}"
554            );
555        }
556
557        let job_res = sqlx::query(
558            r#"
559            UPDATE jobs
560            SET status = 'dlq',
561                dlq_reason_code = $2,
562                dlq_at = now(),
563                locked_at = NULL,
564                locked_by = NULL,
565                lock_expires_at = NULL,
566                last_error_code = $4,
567                last_error_message = $5,
568                updated_at = now()
569            WHERE id = $1
570              AND locked_by = $3
571              AND status = 'running'
572            "#,
573        )
574        .bind(job_id)
575        .bind(reason_code)
576        .bind(worker_id)
577        .bind(error_code)
578        .bind(error_message)
579        .execute(&mut *tx)
580        .await?;
581        if job_res.rows_affected() != 1 {
582            anyhow::bail!(
583                "illegal job state transition to dlq for job {job_id}: expected running lease held by {worker_id}"
584            );
585        }
586
587        tx.commit().await?;
588        Ok(())
589    }
590
591    async fn archive_succeeded_older_than(
592        &self,
593        cutoff: DateTime<Utc>,
594        limit: i64,
595    ) -> anyhow::Result<u64> {
596        let count = self
597            .maintenance_repo
598            .archive_succeeded_older_than(cutoff, limit)
599            .await?;
600        Ok(count as u64)
601    }
602
603    async fn delete_history_for_succeeded_older_than(
604        &self,
605        cutoff: DateTime<Utc>,
606        limit: i64,
607    ) -> anyhow::Result<(u64, u64)> {
608        let (attempts, decisions) = self
609            .maintenance_repo
610            .delete_history_for_succeeded_older_than(cutoff, limit)
611            .await?;
612        Ok((attempts, decisions))
613    }
614
615    async fn get_job(&self, job_id: Uuid) -> anyhow::Result<Option<Job>> {
616        self.jobs_repo.get_job(job_id).await
617    }
618
619    async fn list_jobs(
620        &self,
621        queue: Option<&str>,
622        status: Option<&str>,
623        limit: i64,
624        cursor_created_at: Option<DateTime<Utc>>,
625        cursor_id: Option<Uuid>,
626    ) -> anyhow::Result<Vec<JobListItem>> {
627        self.jobs_repo
628            .list_jobs(queue, status, limit, cursor_created_at, cursor_id)
629            .await
630    }
631
632    async fn replay_job(
633        &self,
634        job_id: Uuid,
635        override_queue: Option<&str>,
636        override_run_at: Option<DateTime<Utc>>,
637    ) -> anyhow::Result<Uuid> {
638        self.jobs_repo
639            .replay_job(job_id, override_queue, override_run_at)
640            .await
641    }
642
643    async fn perform_maintenance(&self) -> anyhow::Result<()> {
644        self.maintenance_repo.vacuum_analyze().await
645    }
646
647    async fn extend_lease(
648        &self,
649        job_id: Uuid,
650        worker_id: &str,
651        lease_seconds: i64,
652    ) -> anyhow::Result<bool> {
653        self.jobs_repo
654            .extend_lease(job_id, worker_id, lease_seconds)
655            .await
656    }
657
658    async fn cancel_job(&self, job_id: Uuid, worker_id: Option<&str>) -> anyhow::Result<()> {
659        self.jobs_repo.cancel_job(job_id, worker_id).await
660    }
661}
662
663#[async_trait]
664impl StreamBackend for PostgresBackend {
665    async fn publish(&self, stream: &str, event: NewEvent) -> anyhow::Result<i64> {
666        self.stream_repo.publish(stream, event).await
667    }
668
669    async fn subscribe_stream(
670        &self,
671        stream: &str,
672        consumer_group: &str,
673        last_seq: Option<i64>,
674    ) -> anyhow::Result<NotificationStream> {
675        self.stream_repo
676            .subscribe_stream(stream, consumer_group, last_seq)
677            .await
678    }
679
680    async fn ack(&self, stream: &str, consumer_group: &str, seq: i64) -> anyhow::Result<()> {
681        self.stream_repo.ack(stream, consumer_group, seq).await
682    }
683
684    async fn read_events(
685        &self,
686        stream: &str,
687        after_seq: i64,
688        limit: i64,
689    ) -> anyhow::Result<Vec<Event>> {
690        self.stream_repo.read_events(stream, after_seq, limit).await
691    }
692
693    async fn prune_events(&self, stream: &str, through_seq: i64) -> anyhow::Result<u64> {
694        self.stream_repo.prune_events(stream, through_seq).await
695    }
696
697    async fn consumer_group_info(&self, stream: &str) -> anyhow::Result<Vec<ConsumerGroupStatus>> {
698        self.stream_repo.consumer_group_info(stream).await
699    }
700}