Skip to main content

nidus_jobs_sqlx/
lib.rs

1#![forbid(unsafe_code)]
2#![deny(missing_docs)]
3
4//! First-party SQLx persistence for [`nidus_jobs`] durable workers.
5//!
6//! The store exposes its native [`sqlx::AnyPool`] and keeps execution semantics
7//! at least once. Atomic conditional updates make lease ownership safe across
8//! workers, but handlers remain responsible for idempotent side effects.
9
10use std::{fmt, time::Instant};
11
12use async_trait::async_trait;
13use nidus_core::{Container, LifecycleHook, NidusError};
14use nidus_integrations::{IntegrationEvent, IntegrationStatus, IntegrationTelemetry};
15use nidus_jobs::{
16    DurableJobError, DurableJobRecord, DurableJobStore, DurableResult, EnqueueResult,
17    JobDisposition, JobStatus, LeaseRequest, NewJob, StoreStats,
18};
19use sqlx::{AnyPool, Row, any::AnyPoolOptions};
20
21const TABLE: &str = "nidus_jobs";
22const MAX_QUERY_LIMIT: usize = 1_024;
23
24/// SQL dialect used by the durable job schema and parameter renderer.
25#[derive(Clone, Copy, Debug, Eq, PartialEq)]
26pub enum SqlDialect {
27    /// SQLite 3.
28    Sqlite,
29    /// PostgreSQL.
30    Postgres,
31    /// CockroachDB through its PostgreSQL wire protocol.
32    Cockroach,
33    /// MySQL.
34    MySql,
35}
36
37impl SqlDialect {
38    fn placeholder(self, index: usize) -> String {
39        match self {
40            Self::Postgres | Self::Cockroach => format!("${index}"),
41            Self::Sqlite | Self::MySql => "?".to_owned(),
42        }
43    }
44}
45
46/// Redaction-safe SQLx durable store configuration.
47#[derive(Clone, Eq, PartialEq)]
48pub struct SqlxJobStoreConfig {
49    database_url: String,
50    dialect: SqlDialect,
51    max_connections: u32,
52    allow_insecure_local: bool,
53}
54
55impl SqlxJobStoreConfig {
56    /// Creates explicit configuration for a SQL dialect.
57    pub fn new(database_url: impl Into<String>, dialect: SqlDialect) -> Self {
58        Self {
59            database_url: database_url.into(),
60            dialect,
61            max_connections: 10,
62            allow_insecure_local: false,
63        }
64    }
65
66    /// Creates SQLite configuration.
67    pub fn sqlite(database_url: impl Into<String>) -> Self {
68        Self::new(database_url, SqlDialect::Sqlite)
69    }
70
71    /// Creates PostgreSQL configuration.
72    pub fn postgres(database_url: impl Into<String>) -> Self {
73        Self::new(database_url, SqlDialect::Postgres)
74    }
75
76    /// Creates CockroachDB configuration requiring `sslmode=verify-full`.
77    pub fn cockroach(database_url: impl Into<String>) -> Self {
78        Self::new(database_url, SqlDialect::Cockroach)
79    }
80
81    /// Creates MySQL configuration.
82    pub fn mysql(database_url: impl Into<String>) -> Self {
83        Self::new(database_url, SqlDialect::MySql)
84    }
85
86    /// Sets the connection pool bound.
87    pub fn with_max_connections(mut self, max_connections: u32) -> DurableResult<Self> {
88        if max_connections == 0 {
89            return Err(DurableJobError::Configuration(
90                "SQLx job pool max_connections must be greater than zero".to_owned(),
91            ));
92        }
93        self.max_connections = max_connections;
94        Ok(self)
95    }
96
97    /// Allows a plaintext CockroachDB URL only when it targets the loopback host.
98    ///
99    /// This escape hatch is intended for hermetic local tests, never production.
100    pub fn allow_insecure_local_cockroach(mut self) -> DurableResult<Self> {
101        if self.dialect != SqlDialect::Cockroach
102            || !has_dialect_scheme(self.dialect, &self.database_url)
103            || !is_loopback_url(&self.database_url)
104        {
105            return Err(DurableJobError::Configuration(
106                "insecure CockroachDB is restricted to explicit loopback URLs".to_owned(),
107            ));
108        }
109        self.allow_insecure_local = true;
110        Ok(self)
111    }
112
113    /// Allows a plaintext PostgreSQL URL only when it targets the loopback host.
114    pub fn allow_insecure_local_postgres(mut self) -> DurableResult<Self> {
115        if self.dialect != SqlDialect::Postgres
116            || !has_dialect_scheme(self.dialect, &self.database_url)
117            || !is_loopback_url(&self.database_url)
118        {
119            return Err(DurableJobError::Configuration(
120                "insecure PostgreSQL is restricted to explicit loopback URLs".to_owned(),
121            ));
122        }
123        self.allow_insecure_local = true;
124        Ok(self)
125    }
126
127    /// Allows a plaintext MySQL URL only when it targets the loopback host.
128    pub fn allow_insecure_local_mysql(mut self) -> DurableResult<Self> {
129        if self.dialect != SqlDialect::MySql
130            || !has_dialect_scheme(self.dialect, &self.database_url)
131            || !is_loopback_url(&self.database_url)
132        {
133            return Err(DurableJobError::Configuration(
134                "insecure MySQL is restricted to explicit loopback URLs".to_owned(),
135            ));
136        }
137        self.allow_insecure_local = true;
138        Ok(self)
139    }
140
141    /// Returns the selected SQL dialect.
142    pub const fn dialect(&self) -> SqlDialect {
143        self.dialect
144    }
145
146    /// Returns the maximum pool connection count.
147    pub const fn max_connections(&self) -> u32 {
148        self.max_connections
149    }
150
151    fn validate(&self) -> DurableResult<()> {
152        if self.database_url.trim().is_empty() {
153            return Err(DurableJobError::Configuration(
154                "SQLx job database URL must not be empty".to_owned(),
155            ));
156        }
157        if !has_dialect_scheme(self.dialect, &self.database_url) {
158            return Err(DurableJobError::Configuration(
159                "SQLx job database URL scheme does not match the selected dialect".to_owned(),
160            ));
161        }
162        if matches!(self.dialect, SqlDialect::Postgres | SqlDialect::Cockroach)
163            && !has_verify_full(&self.database_url)
164            && !self.allow_insecure_local
165        {
166            return Err(DurableJobError::Configuration(
167                "PostgreSQL and CockroachDB require TLS hostname verification with sslmode=verify-full"
168                    .to_owned(),
169            ));
170        }
171        if self.dialect == SqlDialect::MySql
172            && !has_mysql_verify_identity(&self.database_url)
173            && !self.allow_insecure_local
174        {
175            return Err(DurableJobError::Configuration(
176                "MySQL requires ssl-mode=VERIFY_IDENTITY except for explicit loopback development"
177                    .to_owned(),
178            ));
179        }
180        Ok(())
181    }
182}
183
184impl fmt::Debug for SqlxJobStoreConfig {
185    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
186        formatter
187            .debug_struct("SqlxJobStoreConfig")
188            .field("database_url", &"<redacted>")
189            .field("dialect", &self.dialect)
190            .field("max_connections", &self.max_connections)
191            .field("allow_insecure_local", &self.allow_insecure_local)
192            .finish()
193    }
194}
195
196/// SQLx-backed durable job store with native pool access.
197#[derive(Clone)]
198pub struct SqlxJobStore {
199    pool: AnyPool,
200    dialect: SqlDialect,
201    telemetry: IntegrationTelemetry,
202}
203
204impl SqlxJobStore {
205    /// Connects a bounded native SQLx pool after validating secure defaults.
206    pub async fn connect(config: SqlxJobStoreConfig) -> DurableResult<Self> {
207        config.validate()?;
208        sqlx::any::install_default_drivers();
209        let started = Instant::now();
210        let result = AnyPoolOptions::new()
211            .max_connections(config.max_connections)
212            .connect(&config.database_url)
213            .await;
214        let success = result.is_ok();
215        let store = Self {
216            pool: result.map_err(|error| DurableJobError::store("connect", error))?,
217            dialect: config.dialect,
218            telemetry: IntegrationTelemetry::new(),
219        };
220        store.record("connect", success, started).await;
221        Ok(store)
222    }
223
224    /// Wraps an application-owned native SQLx pool.
225    ///
226    /// Callers selecting [`SqlDialect::Cockroach`] are responsible for building
227    /// the pool with certificate and hostname verification enabled.
228    pub fn from_pool(pool: AnyPool, dialect: SqlDialect) -> Self {
229        Self {
230            pool,
231            dialect,
232            telemetry: IntegrationTelemetry::new(),
233        }
234    }
235
236    /// Adds shared tracing, metrics, or dashboard observers.
237    pub fn with_telemetry(mut self, telemetry: IntegrationTelemetry) -> Self {
238        self.telemetry = telemetry;
239        self
240    }
241
242    /// Returns the native SQLx pool for backend-specific operations.
243    pub const fn pool(&self) -> &AnyPool {
244        &self.pool
245    }
246
247    /// Returns the selected SQL dialect.
248    pub const fn dialect(&self) -> SqlDialect {
249        self.dialect
250    }
251
252    /// Registers this store as a typed singleton dependency.
253    pub fn register(&self, container: &mut Container) -> nidus_core::Result<()> {
254        container.register_singleton(self.clone())
255    }
256
257    /// Runs a readiness query and returns dashboard-friendly state counts.
258    pub async fn health(&self) -> DurableResult<StoreStats> {
259        sqlx::query("SELECT 1")
260            .execute(&self.pool)
261            .await
262            .map_err(|error| DurableJobError::store("health query", error))?;
263        self.stats().await
264    }
265
266    /// Executes the native readiness probe and returns a redaction-safe status.
267    #[cfg(feature = "health")]
268    pub async fn health_status(&self) -> nidus_http::health::HealthStatus {
269        if self.health().await.is_ok() {
270            nidus_http::health::HealthStatus::up()
271        } else {
272            nidus_http::health::HealthStatus::down("durable job store readiness check failed")
273        }
274    }
275
276    /// Adds this durable store as a readiness check.
277    #[cfg(feature = "health")]
278    pub fn register_ready_check(
279        self: std::sync::Arc<Self>,
280        registry: nidus_http::health::HealthRegistry,
281        name: impl Into<String>,
282    ) -> nidus_http::health::HealthRegistry {
283        registry.ready_check(name, move || {
284            let store = std::sync::Arc::clone(&self);
285            async move { store.health_status().await }
286        })
287    }
288
289    /// Gracefully closes the native pool.
290    pub async fn close(&self) {
291        self.pool.close().await;
292    }
293
294    async fn record(&self, operation: &'static str, success: bool, started: Instant) {
295        self.telemetry
296            .record(&IntegrationEvent::new(
297                "nidus-jobs-sqlx",
298                operation,
299                if success {
300                    IntegrationStatus::Success
301                } else {
302                    IntegrationStatus::Failure
303                },
304                started.elapsed(),
305            ))
306            .await;
307    }
308
309    async fn fetch_by_id(&self, id: &str) -> DurableResult<Option<DurableJobRecord>> {
310        let sql = format!(
311            "SELECT {COLUMNS} FROM {TABLE} WHERE id = {}",
312            self.dialect.placeholder(1)
313        );
314        sqlx::query(&sql)
315            .bind(id)
316            .fetch_optional(&self.pool)
317            .await
318            .map_err(|error| DurableJobError::store("fetch job by id", error))?
319            .map(record_from_row)
320            .transpose()
321    }
322
323    async fn fetch_by_idempotency_key(
324        &self,
325        name: &str,
326        key: &str,
327    ) -> DurableResult<Option<DurableJobRecord>> {
328        let sql = format!(
329            "SELECT {COLUMNS} FROM {TABLE} WHERE name = {} AND idempotency_key = {}",
330            self.dialect.placeholder(1),
331            self.dialect.placeholder(2)
332        );
333        sqlx::query(&sql)
334            .bind(name)
335            .bind(key)
336            .fetch_optional(&self.pool)
337            .await
338            .map_err(|error| DurableJobError::store("fetch idempotent job", error))?
339            .map(record_from_row)
340            .transpose()
341    }
342
343    async fn enqueue_inner(&self, job: NewJob) -> DurableResult<EnqueueResult> {
344        if let Some(key) = job.idempotency_key()
345            && let Some(existing) = self.fetch_by_idempotency_key(job.name(), key).await?
346        {
347            return Ok(EnqueueResult::Duplicate(existing));
348        }
349
350        let payload_json = serde_json::to_string(job.payload())?;
351        let now_ms = unix_timestamp_ms();
352        let values = (1..=9)
353            .map(|index| self.dialect.placeholder(index))
354            .collect::<Vec<_>>();
355        let sql = format!(
356            "INSERT INTO {TABLE} (id, name, payload_json, status, attempts, max_attempts, \
357             available_at_ms, lease_owner, lease_until_ms, idempotency_key, correlation_id, \
358             last_error, created_at_ms, updated_at_ms) VALUES \
359             ({}, {}, {}, 'pending', 0, {}, {}, NULL, NULL, {}, {}, NULL, {}, {})",
360            values[0],
361            values[1],
362            values[2],
363            values[3],
364            values[4],
365            values[5],
366            values[6],
367            values[7],
368            values[8]
369        );
370        let insert = sqlx::query(&sql)
371            .bind(job.id())
372            .bind(job.name())
373            .bind(payload_json)
374            .bind(i64::from(job.max_attempts()))
375            .bind(job.available_at_ms())
376            .bind(job.idempotency_key())
377            .bind(job.correlation_id())
378            .bind(now_ms)
379            .bind(now_ms)
380            .execute(&self.pool)
381            .await;
382
383        if let Err(error) = insert {
384            if let Some(key) = job.idempotency_key()
385                && let Some(existing) = self.fetch_by_idempotency_key(job.name(), key).await?
386            {
387                return Ok(EnqueueResult::Duplicate(existing));
388            }
389            return Err(DurableJobError::store("insert durable job", error));
390        }
391
392        self.fetch_by_id(job.id())
393            .await?
394            .map(EnqueueResult::Enqueued)
395            .ok_or_else(|| DurableJobError::store_message("inserted durable job was not found"))
396    }
397
398    async fn lease_inner(&self, request: LeaseRequest) -> DurableResult<Vec<DurableJobRecord>> {
399        let limit = request.limit().min(MAX_QUERY_LIMIT);
400        let candidates_sql = format!(
401            "SELECT id FROM {TABLE} WHERE status = 'pending' AND available_at_ms <= {} \
402             ORDER BY available_at_ms ASC, created_at_ms ASC LIMIT {limit}",
403            self.dialect.placeholder(1)
404        );
405        let candidates = sqlx::query(&candidates_sql)
406            .bind(request.now_ms())
407            .fetch_all(&self.pool)
408            .await
409            .map_err(|error| DurableJobError::store("select lease candidates", error))?;
410
411        let update_sql = format!(
412            "UPDATE {TABLE} SET status = 'running', lease_owner = {}, lease_until_ms = {}, \
413             attempts = attempts + 1, updated_at_ms = {} WHERE id = {} AND status = 'pending' \
414             AND available_at_ms <= {}",
415            self.dialect.placeholder(1),
416            self.dialect.placeholder(2),
417            self.dialect.placeholder(3),
418            self.dialect.placeholder(4),
419            self.dialect.placeholder(5)
420        );
421        let mut leased = Vec::with_capacity(candidates.len());
422        for row in candidates {
423            let id: String = row
424                .try_get("id")
425                .map_err(|error| DurableJobError::store("decode lease candidate", error))?;
426            let updated = sqlx::query(&update_sql)
427                .bind(request.worker_id())
428                .bind(request.lease_until_ms())
429                .bind(request.now_ms())
430                .bind(&id)
431                .bind(request.now_ms())
432                .execute(&self.pool)
433                .await
434                .map_err(|error| DurableJobError::store("claim job lease", error))?;
435            if updated.rows_affected() == 1
436                && let Some(record) = self.fetch_by_id(&id).await?
437            {
438                leased.push(record);
439            }
440        }
441        Ok(leased)
442    }
443
444    async fn acknowledge_inner(
445        &self,
446        job_id: &str,
447        worker_id: &str,
448        attempt: u32,
449    ) -> DurableResult<bool> {
450        let sql = format!(
451            "UPDATE {TABLE} SET status = 'succeeded', lease_owner = NULL, lease_until_ms = NULL, \
452             updated_at_ms = {} WHERE id = {} AND status = 'running' AND lease_owner = {} \
453             AND attempts = {}",
454            self.dialect.placeholder(1),
455            self.dialect.placeholder(2),
456            self.dialect.placeholder(3),
457            self.dialect.placeholder(4)
458        );
459        let result = sqlx::query(&sql)
460            .bind(unix_timestamp_ms())
461            .bind(job_id)
462            .bind(worker_id)
463            .bind(i64::from(attempt))
464            .execute(&self.pool)
465            .await
466            .map_err(|error| DurableJobError::store("acknowledge job", error))?;
467        Ok(result.rows_affected() == 1)
468    }
469
470    async fn fail_inner(
471        &self,
472        job_id: &str,
473        worker_id: &str,
474        attempt: u32,
475        safe_error: &str,
476        disposition: JobDisposition,
477    ) -> DurableResult<bool> {
478        let (status, available_at_ms) = match disposition {
479            JobDisposition::RetryAt(timestamp) => (JobStatus::Pending, timestamp),
480            JobDisposition::DeadLetter => (JobStatus::DeadLettered, unix_timestamp_ms()),
481        };
482        let sql = format!(
483            "UPDATE {TABLE} SET status = {}, available_at_ms = {}, last_error = {}, \
484             lease_owner = NULL, lease_until_ms = NULL, updated_at_ms = {} WHERE id = {} \
485             AND status = 'running' AND lease_owner = {} AND attempts = {}",
486            self.dialect.placeholder(1),
487            self.dialect.placeholder(2),
488            self.dialect.placeholder(3),
489            self.dialect.placeholder(4),
490            self.dialect.placeholder(5),
491            self.dialect.placeholder(6),
492            self.dialect.placeholder(7)
493        );
494        let result = sqlx::query(&sql)
495            .bind(status.as_str())
496            .bind(available_at_ms)
497            .bind(safe_error)
498            .bind(unix_timestamp_ms())
499            .bind(job_id)
500            .bind(worker_id)
501            .bind(i64::from(attempt))
502            .execute(&self.pool)
503            .await
504            .map_err(|error| DurableJobError::store("persist job failure", error))?;
505        Ok(result.rows_affected() == 1)
506    }
507
508    async fn extend_lease_inner(
509        &self,
510        job_id: &str,
511        worker_id: &str,
512        attempt: u32,
513        lease_until_ms: i64,
514    ) -> DurableResult<bool> {
515        let sql = format!(
516            "UPDATE {TABLE} SET lease_until_ms = {}, updated_at_ms = {} WHERE id = {} \
517             AND status = 'running' AND lease_owner = {} AND attempts = {}",
518            self.dialect.placeholder(1),
519            self.dialect.placeholder(2),
520            self.dialect.placeholder(3),
521            self.dialect.placeholder(4),
522            self.dialect.placeholder(5)
523        );
524        let result = sqlx::query(&sql)
525            .bind(lease_until_ms)
526            .bind(unix_timestamp_ms())
527            .bind(job_id)
528            .bind(worker_id)
529            .bind(i64::from(attempt))
530            .execute(&self.pool)
531            .await
532            .map_err(|error| DurableJobError::store("extend job lease", error))?;
533        Ok(result.rows_affected() == 1)
534    }
535
536    async fn recover_inner(&self, now_ms: i64) -> DurableResult<u64> {
537        let sql = format!(
538            "UPDATE {TABLE} SET status = 'pending', available_at_ms = {}, lease_owner = NULL, \
539             lease_until_ms = NULL, updated_at_ms = {} WHERE status = 'running' \
540             AND lease_until_ms < {}",
541            self.dialect.placeholder(1),
542            self.dialect.placeholder(2),
543            self.dialect.placeholder(3)
544        );
545        sqlx::query(&sql)
546            .bind(now_ms)
547            .bind(now_ms)
548            .bind(now_ms)
549            .execute(&self.pool)
550            .await
551            .map(|result| result.rows_affected())
552            .map_err(|error| DurableJobError::store("recover expired leases", error))
553    }
554
555    async fn cancel_inner(&self, job_id: &str) -> DurableResult<bool> {
556        let sql = format!(
557            "UPDATE {TABLE} SET status = 'cancelled', lease_owner = NULL, lease_until_ms = NULL, \
558             updated_at_ms = {} WHERE id = {} AND status IN ('pending', 'running')",
559            self.dialect.placeholder(1),
560            self.dialect.placeholder(2)
561        );
562        let result = sqlx::query(&sql)
563            .bind(unix_timestamp_ms())
564            .bind(job_id)
565            .execute(&self.pool)
566            .await
567            .map_err(|error| DurableJobError::store("cancel job", error))?;
568        Ok(result.rows_affected() == 1)
569    }
570
571    async fn stats_inner(&self) -> DurableResult<StoreStats> {
572        let rows = sqlx::query(&format!(
573            "SELECT status, COUNT(*) AS job_count FROM {TABLE} GROUP BY status"
574        ))
575        .fetch_all(&self.pool)
576        .await
577        .map_err(|error| DurableJobError::store("query job statistics", error))?;
578        let mut stats = StoreStats::default();
579        for row in rows {
580            let status: String = row
581                .try_get("status")
582                .map_err(|error| DurableJobError::store("decode job status", error))?;
583            let count: i64 = row
584                .try_get("job_count")
585                .map_err(|error| DurableJobError::store("decode job count", error))?;
586            let count = u64::try_from(count)
587                .map_err(|_| DurableJobError::store_message("durable job count was negative"))?;
588            match JobStatus::from_storage(&status)? {
589                JobStatus::Pending => stats.pending = count,
590                JobStatus::Running => stats.running = count,
591                JobStatus::Succeeded => stats.succeeded = count,
592                JobStatus::DeadLettered => stats.dead_lettered = count,
593                JobStatus::Cancelled => stats.cancelled = count,
594            }
595        }
596        Ok(stats)
597    }
598
599    async fn dead_letters_inner(&self, limit: usize) -> DurableResult<Vec<DurableJobRecord>> {
600        let limit = limit.min(MAX_QUERY_LIMIT);
601        if limit == 0 {
602            return Ok(Vec::new());
603        }
604        let sql = format!(
605            "SELECT {COLUMNS} FROM {TABLE} WHERE status = 'dead_lettered' \
606             ORDER BY updated_at_ms DESC LIMIT {limit}"
607        );
608        sqlx::query(&sql)
609            .fetch_all(&self.pool)
610            .await
611            .map_err(|error| DurableJobError::store("query dead letters", error))?
612            .into_iter()
613            .map(record_from_row)
614            .collect()
615    }
616}
617
618impl fmt::Debug for SqlxJobStore {
619    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
620        formatter
621            .debug_struct("SqlxJobStore")
622            .field("dialect", &self.dialect)
623            .field("pool", &"AnyPool")
624            .field("telemetry", &self.telemetry)
625            .finish()
626    }
627}
628
629#[async_trait]
630impl DurableJobStore for SqlxJobStore {
631    async fn migrate(&self) -> DurableResult<()> {
632        let started = Instant::now();
633        let result = async {
634            let schema = if self.dialect == SqlDialect::MySql {
635                MYSQL_SCHEMA
636            } else {
637                SCHEMA
638            };
639            sqlx::query(schema)
640                .execute(&self.pool)
641                .await
642                .map_err(|error| DurableJobError::store("migrate durable jobs", error))?;
643            if self.dialect != SqlDialect::MySql {
644                for index in INDEXES {
645                    sqlx::query(index)
646                        .execute(&self.pool)
647                        .await
648                        .map_err(|error| {
649                            DurableJobError::store("migrate durable job index", error)
650                        })?;
651                }
652            }
653            Ok(())
654        }
655        .await;
656        self.record("migrate", result.is_ok(), started).await;
657        result
658    }
659
660    async fn enqueue(&self, job: NewJob) -> DurableResult<EnqueueResult> {
661        let started = Instant::now();
662        let result = self.enqueue_inner(job).await;
663        self.record("enqueue", result.is_ok(), started).await;
664        result
665    }
666
667    async fn lease(&self, request: LeaseRequest) -> DurableResult<Vec<DurableJobRecord>> {
668        let started = Instant::now();
669        let result = self.lease_inner(request).await;
670        self.record("lease", result.is_ok(), started).await;
671        result
672    }
673
674    async fn acknowledge(
675        &self,
676        job_id: &str,
677        worker_id: &str,
678        attempt: u32,
679    ) -> DurableResult<bool> {
680        let started = Instant::now();
681        let result = self.acknowledge_inner(job_id, worker_id, attempt).await;
682        self.record("acknowledge", result.is_ok(), started).await;
683        result
684    }
685
686    async fn fail(
687        &self,
688        job_id: &str,
689        worker_id: &str,
690        attempt: u32,
691        safe_error: &str,
692        disposition: JobDisposition,
693    ) -> DurableResult<bool> {
694        let started = Instant::now();
695        let result = self
696            .fail_inner(job_id, worker_id, attempt, safe_error, disposition)
697            .await;
698        self.record("fail", result.is_ok(), started).await;
699        result
700    }
701
702    async fn extend_lease(
703        &self,
704        job_id: &str,
705        worker_id: &str,
706        attempt: u32,
707        lease_until_ms: i64,
708    ) -> DurableResult<bool> {
709        let started = Instant::now();
710        let result = self
711            .extend_lease_inner(job_id, worker_id, attempt, lease_until_ms)
712            .await;
713        self.record("extend_lease", result.is_ok(), started).await;
714        result
715    }
716
717    async fn recover_expired_leases(&self, now_ms: i64) -> DurableResult<u64> {
718        let started = Instant::now();
719        let result = self.recover_inner(now_ms).await;
720        self.record("recover_expired_leases", result.is_ok(), started)
721            .await;
722        result
723    }
724
725    async fn cancel(&self, job_id: &str) -> DurableResult<bool> {
726        let started = Instant::now();
727        let result = self.cancel_inner(job_id).await;
728        self.record("cancel", result.is_ok(), started).await;
729        result
730    }
731
732    async fn stats(&self) -> DurableResult<StoreStats> {
733        let started = Instant::now();
734        let result = self.stats_inner().await;
735        self.record("stats", result.is_ok(), started).await;
736        result
737    }
738
739    async fn dead_letters(&self, limit: usize) -> DurableResult<Vec<DurableJobRecord>> {
740        let started = Instant::now();
741        let result = self.dead_letters_inner(limit).await;
742        self.record("dead_letters", result.is_ok(), started).await;
743        result
744    }
745}
746
747#[async_trait]
748impl LifecycleHook for SqlxJobStore {
749    async fn on_startup(&self) -> nidus_core::Result<()> {
750        self.migrate()
751            .await
752            .map_err(|_| NidusError::ApplicationBuild {
753                message: "SQLx durable job migration failed".to_owned(),
754            })
755    }
756
757    async fn on_shutdown(&self) -> nidus_core::Result<()> {
758        self.close().await;
759        Ok(())
760    }
761}
762
763const COLUMNS: &str = "id, name, payload_json, status, attempts, max_attempts, available_at_ms, \
764    lease_owner, lease_until_ms, idempotency_key, correlation_id, last_error, created_at_ms, \
765    updated_at_ms";
766
767const SCHEMA: &str = "CREATE TABLE IF NOT EXISTS nidus_jobs (\
768    id VARCHAR(255) PRIMARY KEY,\
769    name VARCHAR(255) NOT NULL,\
770    payload_json TEXT NOT NULL,\
771    status VARCHAR(32) NOT NULL,\
772    attempts BIGINT NOT NULL,\
773    max_attempts BIGINT NOT NULL,\
774    available_at_ms BIGINT NOT NULL,\
775    lease_owner VARCHAR(255) NULL,\
776    lease_until_ms BIGINT NULL,\
777    idempotency_key VARCHAR(255) NULL,\
778    correlation_id VARCHAR(255) NULL,\
779    last_error TEXT NULL,\
780    created_at_ms BIGINT NOT NULL,\
781    updated_at_ms BIGINT NOT NULL,\
782    UNIQUE (name, idempotency_key)\
783)";
784
785const MYSQL_SCHEMA: &str = "CREATE TABLE IF NOT EXISTS nidus_jobs (\
786    id VARCHAR(255) PRIMARY KEY,\
787    name VARCHAR(255) NOT NULL,\
788    payload_json TEXT NOT NULL,\
789    status VARCHAR(32) NOT NULL,\
790    attempts BIGINT NOT NULL,\
791    max_attempts BIGINT NOT NULL,\
792    available_at_ms BIGINT NOT NULL,\
793    lease_owner VARCHAR(255) NULL,\
794    lease_until_ms BIGINT NULL,\
795    idempotency_key VARCHAR(255) NULL,\
796    correlation_id VARCHAR(255) NULL,\
797    last_error TEXT NULL,\
798    created_at_ms BIGINT NOT NULL,\
799    updated_at_ms BIGINT NOT NULL,\
800    UNIQUE (name, idempotency_key),\
801    INDEX nidus_jobs_ready_idx (status, available_at_ms, created_at_ms),\
802    INDEX nidus_jobs_recovery_idx (status, lease_until_ms),\
803    INDEX nidus_jobs_dead_letter_idx (status, updated_at_ms)\
804)";
805
806const INDEXES: [&str; 3] = [
807    "CREATE INDEX IF NOT EXISTS nidus_jobs_ready_idx \
808     ON nidus_jobs (status, available_at_ms, created_at_ms)",
809    "CREATE INDEX IF NOT EXISTS nidus_jobs_recovery_idx \
810     ON nidus_jobs (status, lease_until_ms)",
811    "CREATE INDEX IF NOT EXISTS nidus_jobs_dead_letter_idx \
812     ON nidus_jobs (status, updated_at_ms)",
813];
814
815fn record_from_row(row: sqlx::any::AnyRow) -> DurableResult<DurableJobRecord> {
816    let payload_json = decode_text(&row, "payload_json")?;
817    let attempts: i64 = decode(&row, "attempts")?;
818    let max_attempts: i64 = decode(&row, "max_attempts")?;
819    Ok(DurableJobRecord {
820        id: decode(&row, "id")?,
821        name: decode(&row, "name")?,
822        payload: serde_json::from_str(&payload_json)?,
823        status: JobStatus::from_storage(&decode::<String>(&row, "status")?)?,
824        attempts: u32::try_from(attempts).map_err(|_| {
825            DurableJobError::store_message("persisted attempt count was out of range")
826        })?,
827        max_attempts: u32::try_from(max_attempts).map_err(|_| {
828            DurableJobError::store_message("persisted max attempt count was out of range")
829        })?,
830        available_at_ms: decode(&row, "available_at_ms")?,
831        lease_owner: decode(&row, "lease_owner")?,
832        lease_until_ms: decode(&row, "lease_until_ms")?,
833        idempotency_key: decode(&row, "idempotency_key")?,
834        correlation_id: decode(&row, "correlation_id")?,
835        last_error: decode_optional_text(&row, "last_error")?,
836        created_at_ms: decode(&row, "created_at_ms")?,
837        updated_at_ms: decode(&row, "updated_at_ms")?,
838    })
839}
840
841fn decode_text(row: &sqlx::any::AnyRow, column: &'static str) -> DurableResult<String> {
842    match row.try_get::<String, _>(column) {
843        Ok(value) => Ok(value),
844        Err(_) => {
845            let value: Vec<u8> = row
846                .try_get(column)
847                .map_err(|error| DurableJobError::store("decode durable job text", error))?;
848            String::from_utf8(value)
849                .map_err(|error| DurableJobError::store("decode durable job UTF-8", error))
850        }
851    }
852}
853
854fn decode_optional_text(
855    row: &sqlx::any::AnyRow,
856    column: &'static str,
857) -> DurableResult<Option<String>> {
858    match row.try_get::<Option<String>, _>(column) {
859        Ok(value) => Ok(value),
860        Err(_) => {
861            let value: Option<Vec<u8>> = row.try_get(column).map_err(|error| {
862                DurableJobError::store("decode optional durable job text", error)
863            })?;
864            value
865                .map(String::from_utf8)
866                .transpose()
867                .map_err(|error| DurableJobError::store("decode durable job UTF-8", error))
868        }
869    }
870}
871
872fn decode<'row, T>(row: &'row sqlx::any::AnyRow, column: &'static str) -> DurableResult<T>
873where
874    T: sqlx::Decode<'row, sqlx::Any> + sqlx::Type<sqlx::Any>,
875{
876    row.try_get(column)
877        .map_err(|error| DurableJobError::store("decode durable job row", error))
878}
879
880fn unix_timestamp_ms() -> i64 {
881    let millis = std::time::SystemTime::now()
882        .duration_since(std::time::UNIX_EPOCH)
883        .unwrap_or_default()
884        .as_millis();
885    i64::try_from(millis).unwrap_or(i64::MAX)
886}
887
888fn has_verify_full(url: &str) -> bool {
889    let Ok(url) = url::Url::parse(url) else {
890        return false;
891    };
892    if !matches!(url.scheme(), "postgres" | "postgresql") {
893        return false;
894    }
895    let values = url
896        .query_pairs()
897        .filter(|(key, _)| key.eq_ignore_ascii_case("sslmode"))
898        .map(|(_, value)| value.into_owned())
899        .collect::<Vec<_>>();
900    values.len() == 1 && values[0].eq_ignore_ascii_case("verify-full")
901}
902
903fn has_mysql_verify_identity(url: &str) -> bool {
904    let Ok(url) = url::Url::parse(url) else {
905        return false;
906    };
907    if url.scheme() != "mysql" {
908        return false;
909    }
910    let values = url
911        .query_pairs()
912        .filter(|(key, _)| key.eq_ignore_ascii_case("ssl-mode"))
913        .map(|(_, value)| value.into_owned())
914        .collect::<Vec<_>>();
915    values.len() == 1
916        && (values[0].eq_ignore_ascii_case("verify_identity")
917            || values[0].eq_ignore_ascii_case("verify-identity"))
918}
919
920fn is_loopback_url(url: &str) -> bool {
921    url::Url::parse(url)
922        .ok()
923        .is_some_and(|url| match url.host() {
924            Some(url::Host::Domain(host)) => {
925                host.eq_ignore_ascii_case("localhost")
926                    || host
927                        .parse::<std::net::IpAddr>()
928                        .is_ok_and(|address| address.is_loopback())
929            }
930            Some(url::Host::Ipv4(host)) => host.is_loopback(),
931            Some(url::Host::Ipv6(host)) => host.is_loopback(),
932            None => false,
933        })
934}
935
936fn has_dialect_scheme(dialect: SqlDialect, value: &str) -> bool {
937    let Ok(url) = url::Url::parse(value) else {
938        return false;
939    };
940    match dialect {
941        SqlDialect::Sqlite => url.scheme() == "sqlite",
942        SqlDialect::Postgres | SqlDialect::Cockroach => {
943            matches!(url.scheme(), "postgres" | "postgresql")
944        }
945        SqlDialect::MySql => url.scheme() == "mysql",
946    }
947}