1#![forbid(unsafe_code)]
2#![deny(missing_docs)]
3
4use 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#[derive(Clone, Copy, Debug, Eq, PartialEq)]
26pub enum SqlDialect {
27 Sqlite,
29 Postgres,
31 Cockroach,
33 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#[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 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 pub fn sqlite(database_url: impl Into<String>) -> Self {
68 Self::new(database_url, SqlDialect::Sqlite)
69 }
70
71 pub fn postgres(database_url: impl Into<String>) -> Self {
73 Self::new(database_url, SqlDialect::Postgres)
74 }
75
76 pub fn cockroach(database_url: impl Into<String>) -> Self {
78 Self::new(database_url, SqlDialect::Cockroach)
79 }
80
81 pub fn mysql(database_url: impl Into<String>) -> Self {
83 Self::new(database_url, SqlDialect::MySql)
84 }
85
86 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 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 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 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 pub const fn dialect(&self) -> SqlDialect {
143 self.dialect
144 }
145
146 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#[derive(Clone)]
198pub struct SqlxJobStore {
199 pool: AnyPool,
200 dialect: SqlDialect,
201 telemetry: IntegrationTelemetry,
202}
203
204impl SqlxJobStore {
205 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 pub fn from_pool(pool: AnyPool, dialect: SqlDialect) -> Self {
229 Self {
230 pool,
231 dialect,
232 telemetry: IntegrationTelemetry::new(),
233 }
234 }
235
236 pub fn with_telemetry(mut self, telemetry: IntegrationTelemetry) -> Self {
238 self.telemetry = telemetry;
239 self
240 }
241
242 pub const fn pool(&self) -> &AnyPool {
244 &self.pool
245 }
246
247 pub const fn dialect(&self) -> SqlDialect {
249 self.dialect
250 }
251
252 pub fn register(&self, container: &mut Container) -> nidus_core::Result<()> {
254 container.register_singleton(self.clone())
255 }
256
257 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 #[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 #[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 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}