1use std::str::FromStr;
2use std::time::Duration;
3
4use async_trait::async_trait;
5use chrono::{DateTime, Utc};
6use journio_core::dialect::Dialect;
7use journio_core::error::{JournioError, JournioErrorCode, JournioResult};
8use journio_core::system_db::{ForkWorkflow, InitWorkflow, InitWorkflowResult, SystemDatabase};
9use journio_core::types::{
10 ListWorkflowsFilter, QueueConfig, ScheduleStatus, StepRecord, StreamEntry, VersionInfo,
11 WorkflowSchedule, WorkflowStatus, WorkflowStatusType,
12};
13use journio_core::value::Interchange;
14use sqlx::sqlite::{
15 SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteRow, SqliteSynchronous,
16};
17use sqlx::{Executor, QueryBuilder, Row, Sqlite, SqlitePool};
18
19use crate::dialect::SqliteDialect;
20use crate::error::{db_err, is_foreign_key_violation, is_unique_violation};
21use crate::migrations::run_migrations;
22
23const NULL_TOPIC: &str = "__null__topic__";
24const SERIALIZATION_JSON: &str = "JOURNIO_JSON";
25const STREAM_CLOSED_SENTINEL: &str = "__JOURNIO_STREAM_CLOSED__";
26const INTERNAL_QUEUE_NAME: &str = "_journio_internal_queue";
27
28pub struct SqliteSystemDatabase {
29 pool: SqlitePool,
30 dialect: SqliteDialect,
31}
32
33impl SqliteSystemDatabase {
34 pub async fn connect(database_url: &str) -> Result<Self, JournioError> {
35 let options = sqlite_connect_options(database_url)?;
36 let pool = SqlitePoolOptions::new()
37 .max_connections(8)
38 .after_connect(|conn, _| {
39 Box::pin(async move {
40 conn.execute("PRAGMA foreign_keys = ON;").await?;
41 conn.execute("PRAGMA busy_timeout = 5000;").await?;
42 Ok(())
43 })
44 })
45 .connect_with(options)
46 .await
47 .map_err(db_err)?;
48 Ok(Self {
49 pool,
50 dialect: SqliteDialect,
51 })
52 }
53
54 pub fn pool(&self) -> &SqlitePool {
55 &self.pool
56 }
57
58 fn q(&self, body: &str) -> String {
59 self.dialect.rewrite_query(body)
60 }
61}
62
63#[async_trait]
64impl SystemDatabase for SqliteSystemDatabase {
65 fn dialect(&self) -> &dyn Dialect {
66 &self.dialect
67 }
68
69 async fn migrate(&self) -> JournioResult<()> {
70 run_migrations(self.pool.clone()).await
71 }
72
73 async fn launch(&self) -> JournioResult<()> {
74 Ok(())
75 }
76
77 async fn shutdown(&self) -> JournioResult<()> {
78 self.pool.close().await;
79 Ok(())
80 }
81
82 async fn init_workflow(&self, init: InitWorkflow) -> JournioResult<InitWorkflowResult> {
83 let attempts: i32 = match init.status {
84 WorkflowStatusType::Enqueued | WorkflowStatusType::Delayed => 0,
85 _ => 1,
86 };
87 let now_ms = Utc::now().timestamp_millis();
88 let timeout_ms: Option<i64> = init.timeout.map(|d| d.as_millis() as i64);
89 let deadline_ms: Option<i64> = init.deadline.map(|t| t.timestamp_millis());
90 let delay_until_ms: Option<i64> = init.delay_until.map(|t| t.timestamp_millis());
91 let inputs_str = init.input.as_ref().map(stringify_json);
92 let roles_json =
93 serde_json::to_string(&init.authenticated_roles).unwrap_or_else(|_| "null".into());
94 let recovery_increment: i32 = i32::from(init.increment_attempts);
95
96 let query = self.q(
97 "INSERT INTO workflow_status (
98 workflow_uuid, status, name, queue_name, authenticated_user,
99 assumed_role, authenticated_roles, executor_id, application_version,
100 application_id, created_at, recovery_attempts, updated_at,
101 workflow_timeout_ms, workflow_deadline_epoch_ms, inputs,
102 deduplication_id, priority, queue_partition_key, owner_xid,
103 parent_workflow_id, class_name, config_name, serialization,
104 delay_until_epoch_ms
105 ) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25)
106 ON CONFLICT (workflow_uuid)
107 DO UPDATE SET
108 recovery_attempts = CASE
109 WHEN excluded.status NOT IN ($26, $27) THEN workflow_status.recovery_attempts + $28
110 ELSE workflow_status.recovery_attempts
111 END,
112 updated_at = excluded.updated_at,
113 executor_id = CASE
114 WHEN excluded.status IN ($26, $27) THEN workflow_status.executor_id
115 ELSE excluded.executor_id
116 END
117 RETURNING recovery_attempts, status, name, queue_name, queue_partition_key, workflow_timeout_ms, workflow_deadline_epoch_ms",
118 );
119
120 let status_str = status_to_str(init.status);
121 let enqueued_str = status_to_str(WorkflowStatusType::Enqueued);
122 let delayed_str = status_to_str(WorkflowStatusType::Delayed);
123
124 let row = sqlx::query(&query)
125 .bind(&init.workflow_id)
126 .bind(&status_str)
127 .bind(&init.name)
128 .bind(&init.queue_name)
129 .bind(&init.authenticated_user)
130 .bind(&init.assumed_role)
131 .bind(&roles_json)
132 .bind(&init.executor_id)
133 .bind(&init.application_version)
134 .bind(&init.application_id)
135 .bind(now_ms)
136 .bind(attempts)
137 .bind(now_ms)
138 .bind(timeout_ms)
139 .bind(deadline_ms)
140 .bind(&inputs_str)
141 .bind(&init.deduplication_id)
142 .bind(init.priority)
143 .bind(&init.queue_partition_key)
144 .bind(Option::<String>::None)
145 .bind(&init.parent_workflow_id)
146 .bind(&init.class_name)
147 .bind(&init.config_name)
148 .bind(&init.serialization)
149 .bind(delay_until_ms)
150 .bind(&enqueued_str)
151 .bind(&delayed_str)
152 .bind(recovery_increment)
153 .fetch_one(&self.pool)
154 .await
155 .map_err(|err| {
156 if is_unique_violation(&err) {
157 JournioError {
158 code: JournioErrorCode::QueueDeduplicated,
159 message: format!(
160 "Workflow {} was deduplicated due to an existing workflow in queue {:?} with deduplication ID {:?}",
161 init.workflow_id, init.queue_name, init.deduplication_id
162 ),
163 workflow_id: Some(init.workflow_id.clone()),
164 queue_name: init.queue_name.clone(),
165 deduplication_id: init.deduplication_id.clone(),
166 source: Some(Box::new(err)),
167 ..Default::default()
168 }
169 } else {
170 db_err(err)
171 }
172 })?;
173
174 let attempts_out: i32 = row.get(0);
175 let status_out_str: String = row.get(1);
176 let name_out: String = row.get(2);
177 let queue_name_out: Option<String> = row.get(3);
178 let queue_partition_out: Option<String> = row.get(4);
179 let timeout_out: Option<i64> = row.get(5);
180 let deadline_out: Option<i64> = row.get(6);
181
182 if !init.name.is_empty() && name_out != init.name {
183 return Err(JournioError {
184 code: JournioErrorCode::ConflictingWorkflowError,
185 message: format!(
186 "Conflicting workflow invocation with the same ID ({}): Workflow already exists with a different name: {}, but the provided name is: {}",
187 init.workflow_id, name_out, init.name
188 ),
189 workflow_id: Some(init.workflow_id),
190 ..Default::default()
191 });
192 }
193 if let Some(qn) = &init.queue_name {
194 if let Some(existing) = &queue_name_out {
195 if qn != existing {
196 return Err(JournioError {
197 code: JournioErrorCode::ConflictingWorkflowError,
198 message: format!(
199 "Conflicting workflow invocation with the same ID ({}): Workflow already exists in a different queue: {}, but the provided queue is: {}",
200 init.workflow_id, existing, qn
201 ),
202 workflow_id: Some(init.workflow_id),
203 ..Default::default()
204 });
205 }
206 }
207 }
208
209 let status_out = parse_status(&status_out_str);
210 if !matches!(
211 status_out,
212 WorkflowStatusType::Success | WorkflowStatusType::Error
213 ) && init.max_retries > 0
214 && attempts_out > init.max_retries + 1
215 {
216 let dlq = self.q(
217 "UPDATE workflow_status SET status = $1, deduplication_id = NULL, started_at_epoch_ms = NULL, queue_name = NULL WHERE workflow_uuid = $2 AND status = $3",
218 );
219 sqlx::query(&dlq)
220 .bind(status_to_str(
221 WorkflowStatusType::MaxRecoveryAttemptsExceeded,
222 ))
223 .bind(&init.workflow_id)
224 .bind(&status_str)
225 .execute(&self.pool)
226 .await
227 .map_err(db_err)?;
228 return Err(JournioError {
229 code: JournioErrorCode::DeadLetterQueueError,
230 message: format!(
231 "Workflow {} has been moved to the dead-letter queue after exceeding the maximum of {} retries",
232 init.workflow_id, init.max_retries
233 ),
234 workflow_id: Some(init.workflow_id),
235 ..Default::default()
236 });
237 }
238
239 Ok(InitWorkflowResult {
240 status: status_out,
241 attempts: attempts_out as i64,
242 name: name_out,
243 queue_name: queue_name_out,
244 queue_partition_key: queue_partition_out,
245 timeout: timeout_out
246 .filter(|&m| m > 0)
247 .map(|m| Duration::from_millis(m as u64)),
248 deadline: deadline_out.map(timestamp_ms),
249 })
250 }
251
252 async fn record_workflow_result(
253 &self,
254 workflow_id: &str,
255 status: WorkflowStatusType,
256 output: Option<&Interchange>,
257 error: Option<&str>,
258 ) -> JournioResult<()> {
259 let output_str = output.map(stringify_json);
260 let now_ms = Utc::now().timestamp_millis();
261 let query = self.q(
262 "UPDATE workflow_status \
263 SET status = $1, output = $2, error = $3, updated_at = $4, completed_at = $4, deduplication_id = NULL \
264 WHERE workflow_uuid = $5 AND NOT (status = $6 AND CAST($1 AS TEXT) IN ($7, $8))",
265 );
266 sqlx::query(&query)
267 .bind(status_to_str(status))
268 .bind(output_str)
269 .bind(error)
270 .bind(now_ms)
271 .bind(workflow_id)
272 .bind(status_to_str(WorkflowStatusType::Cancelled))
273 .bind(status_to_str(WorkflowStatusType::Success))
274 .bind(status_to_str(WorkflowStatusType::Error))
275 .execute(&self.pool)
276 .await
277 .map_err(db_err)?;
278 Ok(())
279 }
280
281 async fn get_workflow_status(
282 &self,
283 workflow_id: &str,
284 ) -> JournioResult<Option<WorkflowStatus>> {
285 let query = self.q(WORKFLOW_STATUS_BY_ID);
286 let row = sqlx::query(&query)
287 .bind(workflow_id)
288 .fetch_optional(&self.pool)
289 .await
290 .map_err(db_err)?;
291 Ok(row.map(row_to_workflow_status))
292 }
293
294 async fn list_workflows(&self, limit: i64) -> JournioResult<Vec<WorkflowStatus>> {
295 let query = format!(
296 "{} ORDER BY created_at DESC LIMIT ?1",
297 self.q(WORKFLOW_STATUS_SELECT)
298 );
299 let rows = sqlx::query(&query)
300 .bind(limit)
301 .fetch_all(&self.pool)
302 .await
303 .map_err(db_err)?;
304 Ok(rows.into_iter().map(row_to_workflow_status).collect())
305 }
306
307 async fn cancel_workflows(&self, workflow_ids: &[String]) -> JournioResult<Vec<String>> {
308 if workflow_ids.is_empty() {
309 return Ok(Vec::new());
310 }
311
312 let now_ms = Utc::now().timestamp_millis();
313 let mut tx = self.pool.begin().await.map_err(db_err)?;
314
315 let mut update = QueryBuilder::<Sqlite>::new(
316 "UPDATE workflow_status
317 SET status = ",
318 );
319 update.push_bind(status_to_str(WorkflowStatusType::Cancelled));
320 update.push(", updated_at = ");
321 update.push_bind(now_ms);
322 update.push(", completed_at = ");
323 update.push_bind(now_ms);
324 update.push(
325 ", started_at_epoch_ms = NULL, queue_name = NULL, deduplication_id = NULL
326 WHERE workflow_uuid IN (",
327 );
328 {
329 let mut separated = update.separated(", ");
330 for workflow_id in workflow_ids {
331 separated.push_bind(workflow_id);
332 }
333 }
334 update.push(") AND status NOT IN (");
335 update.push_bind(status_to_str(WorkflowStatusType::Success));
336 update.push(", ");
337 update.push_bind(status_to_str(WorkflowStatusType::Error));
338 update.push(", ");
339 update.push_bind(status_to_str(WorkflowStatusType::Cancelled));
340 update.push(")");
341 update.build().execute(&mut *tx).await.map_err(db_err)?;
342
343 let mut select = QueryBuilder::<Sqlite>::new(format!(
344 "{WORKFLOW_STATUS_ID_SELECT_PREFIX} WHERE workflow_uuid IN ("
345 ));
346 {
347 let mut separated = select.separated(", ");
348 for workflow_id in workflow_ids {
349 separated.push_bind(workflow_id);
350 }
351 }
352 select.push(")");
353 let rows = select.build().fetch_all(&mut *tx).await.map_err(db_err)?;
354 tx.commit().await.map_err(db_err)?;
355 Ok(rows.into_iter().map(|row| row.get(0)).collect())
356 }
357
358 async fn resume_workflows(
359 &self,
360 workflow_ids: &[String],
361 queue_name: Option<&str>,
362 ) -> JournioResult<Vec<String>> {
363 if workflow_ids.is_empty() {
364 return Ok(Vec::new());
365 }
366
367 let now_ms = Utc::now().timestamp_millis();
368 let queue_name = queue_name.unwrap_or(INTERNAL_QUEUE_NAME);
369 let mut tx = self.pool.begin().await.map_err(db_err)?;
370
371 let mut update = QueryBuilder::<Sqlite>::new(
372 "UPDATE workflow_status
373 SET status = ",
374 );
375 update.push_bind(status_to_str(WorkflowStatusType::Enqueued));
376 update.push(", queue_name = ");
377 update.push_bind(queue_name);
378 update.push(
379 ", recovery_attempts = 0, workflow_deadline_epoch_ms = NULL, deduplication_id = NULL,
380 started_at_epoch_ms = NULL, updated_at = ",
381 );
382 update.push_bind(now_ms);
383 update.push(
384 ", completed_at = NULL
385 WHERE workflow_uuid IN (",
386 );
387 {
388 let mut separated = update.separated(", ");
389 for workflow_id in workflow_ids {
390 separated.push_bind(workflow_id);
391 }
392 }
393 update.push(") AND status NOT IN (");
394 update.push_bind(status_to_str(WorkflowStatusType::Success));
395 update.push(", ");
396 update.push_bind(status_to_str(WorkflowStatusType::Error));
397 update.push(")");
398 update.build().execute(&mut *tx).await.map_err(db_err)?;
399
400 let mut select = QueryBuilder::<Sqlite>::new(format!(
401 "{WORKFLOW_STATUS_ID_SELECT_PREFIX} WHERE workflow_uuid IN ("
402 ));
403 {
404 let mut separated = select.separated(", ");
405 for workflow_id in workflow_ids {
406 separated.push_bind(workflow_id);
407 }
408 }
409 select.push(")");
410 let rows = select.build().fetch_all(&mut *tx).await.map_err(db_err)?;
411 tx.commit().await.map_err(db_err)?;
412 Ok(rows.into_iter().map(|row| row.get(0)).collect())
413 }
414
415 async fn get_workflow_children(&self, workflow_id: &str) -> JournioResult<Vec<WorkflowStatus>> {
416 let query = self.q(
417 "WITH RECURSIVE descendants AS (
418 SELECT workflow_uuid, status, name, authenticated_user, assumed_role, authenticated_roles,
419 output, error, executor_id, created_at, updated_at, application_version, application_id,
420 recovery_attempts, queue_name, workflow_timeout_ms, workflow_deadline_epoch_ms,
421 started_at_epoch_ms, deduplication_id, inputs, priority, queue_partition_key,
422 forked_from, was_forked_from, parent_workflow_id, completed_at, class_name, config_name,
423 serialization, delay_until_epoch_ms
424 FROM workflow_status
425 WHERE parent_workflow_id = $1
426 UNION ALL
427 SELECT ws.workflow_uuid, ws.status, ws.name, ws.authenticated_user, ws.assumed_role, ws.authenticated_roles,
428 ws.output, ws.error, ws.executor_id, ws.created_at, ws.updated_at, ws.application_version, ws.application_id,
429 ws.recovery_attempts, ws.queue_name, ws.workflow_timeout_ms, ws.workflow_deadline_epoch_ms,
430 ws.started_at_epoch_ms, ws.deduplication_id, ws.inputs, ws.priority, ws.queue_partition_key,
431 ws.forked_from, ws.was_forked_from, ws.parent_workflow_id, ws.completed_at, ws.class_name, ws.config_name,
432 ws.serialization, ws.delay_until_epoch_ms
433 FROM workflow_status ws
434 INNER JOIN descendants d ON ws.parent_workflow_id = d.workflow_uuid
435 )
436 SELECT * FROM descendants
437 ORDER BY created_at ASC",
438 );
439 let rows = sqlx::query(&query)
440 .bind(workflow_id)
441 .fetch_all(&self.pool)
442 .await
443 .map_err(db_err)?;
444 Ok(rows.into_iter().map(row_to_workflow_status).collect())
445 }
446
447 async fn fork_workflow(&self, input: ForkWorkflow) -> JournioResult<String> {
448 if input.start_step < 0 {
449 return Err(JournioError::new(
450 JournioErrorCode::InitializationError,
451 format!("startStep must be >= 0, got {}", input.start_step),
452 ));
453 }
454
455 let forked_workflow_id = input
456 .forked_workflow_id
457 .unwrap_or_else(|| uuid::Uuid::new_v4().to_string());
458 let queue_name = input
459 .queue_name
460 .unwrap_or_else(|| INTERNAL_QUEUE_NAME.to_string());
461
462 let mut tx = self.pool.begin().await.map_err(db_err)?;
463 let original_row = sqlx::query(&self.q(WORKFLOW_STATUS_BY_ID))
464 .bind(&input.original_workflow_id)
465 .fetch_optional(&mut *tx)
466 .await
467 .map_err(db_err)?;
468 let Some(original_row) = original_row else {
469 return Err(JournioError::new(
470 JournioErrorCode::NonExistentWorkflowError,
471 format!("workflow {} does not exist", input.original_workflow_id),
472 ));
473 };
474 let original = row_to_workflow_status(original_row);
475 let now_ms = Utc::now().timestamp_millis();
476 let authenticated_roles =
477 serde_json::to_string(&original.authenticated_roles).unwrap_or_else(|_| "null".into());
478 let application_version = input
479 .application_version
480 .unwrap_or_else(|| original.application_version.clone());
481 let inputs = original.input.as_ref().map(stringify_json);
482
483 let insert_query = self.q(
484 "INSERT INTO workflow_status (
485 workflow_uuid, status, name, authenticated_user, assumed_role, authenticated_roles,
486 executor_id, application_version, application_id, queue_name, queue_partition_key,
487 inputs, created_at, updated_at, recovery_attempts, forked_from, serialization,
488 class_name, config_name
489 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)",
490 );
491 sqlx::query(&insert_query)
492 .bind(&forked_workflow_id)
493 .bind(status_to_str(WorkflowStatusType::Enqueued))
494 .bind(&original.name)
495 .bind(&original.authenticated_user)
496 .bind(&original.assumed_role)
497 .bind(&authenticated_roles)
498 .bind(&original.executor_id)
499 .bind(&application_version)
500 .bind(&original.application_id)
501 .bind(&queue_name)
502 .bind(&input.queue_partition_key)
503 .bind(&inputs)
504 .bind(now_ms)
505 .bind(now_ms)
506 .bind(0_i64)
507 .bind(&input.original_workflow_id)
508 .bind(&original.serialization)
509 .bind(&original.class_name)
510 .bind(&original.config_name)
511 .execute(&mut *tx)
512 .await
513 .map_err(db_err)?;
514
515 sqlx::query(
516 &self.q("UPDATE workflow_status SET was_forked_from = 1 WHERE workflow_uuid = $1"),
517 )
518 .bind(&input.original_workflow_id)
519 .execute(&mut *tx)
520 .await
521 .map_err(db_err)?;
522
523 if input.start_step > 0 {
524 sqlx::query(&self.q(
525 "INSERT INTO operation_outputs
526 (workflow_uuid, function_id, output, error, function_name, child_workflow_id, started_at_epoch_ms, completed_at_epoch_ms, serialization)
527 SELECT $1, function_id, output, error, function_name, child_workflow_id, started_at_epoch_ms, completed_at_epoch_ms, serialization
528 FROM operation_outputs
529 WHERE workflow_uuid = $2 AND function_id < $3",
530 ))
531 .bind(&forked_workflow_id)
532 .bind(&input.original_workflow_id)
533 .bind(input.start_step)
534 .execute(&mut *tx)
535 .await
536 .map_err(db_err)?;
537
538 sqlx::query(&self.q("INSERT INTO workflow_events_history
539 (workflow_uuid, function_id, key, value, serialization)
540 SELECT $1, function_id, key, value, serialization
541 FROM workflow_events_history
542 WHERE workflow_uuid = $2 AND function_id < $3"))
543 .bind(&forked_workflow_id)
544 .bind(&input.original_workflow_id)
545 .bind(input.start_step)
546 .execute(&mut *tx)
547 .await
548 .map_err(db_err)?;
549
550 sqlx::query(&self.q(
551 "INSERT INTO workflow_events (workflow_uuid, key, value, serialization)
552 SELECT $1, h.key, h.value, h.serialization
553 FROM workflow_events_history h
554 INNER JOIN (
555 SELECT key, MAX(function_id) AS max_fid
556 FROM workflow_events_history
557 WHERE workflow_uuid = $2 AND function_id < $3
558 GROUP BY key
559 ) latest ON h.key = latest.key AND h.function_id = latest.max_fid
560 WHERE h.workflow_uuid = $2 AND h.function_id < $3",
561 ))
562 .bind(&forked_workflow_id)
563 .bind(&input.original_workflow_id)
564 .bind(input.start_step)
565 .execute(&mut *tx)
566 .await
567 .map_err(db_err)?;
568
569 sqlx::query(&self.q(
570 "INSERT INTO streams (workflow_uuid, key, value, \"offset\", function_id, serialization)
571 SELECT $1, key, value, \"offset\", function_id, serialization
572 FROM streams
573 WHERE workflow_uuid = $2 AND function_id < $3",
574 ))
575 .bind(&forked_workflow_id)
576 .bind(&input.original_workflow_id)
577 .bind(input.start_step)
578 .execute(&mut *tx)
579 .await
580 .map_err(db_err)?;
581 }
582
583 tx.commit().await.map_err(db_err)?;
584 Ok(forked_workflow_id)
585 }
586
587 async fn get_queue(&self, queue_name: &str) -> JournioResult<Option<QueueConfig>> {
588 let query = self.q(
589 "SELECT queue_id, name, concurrency, worker_concurrency, rate_limit_max, rate_limit_period_sec,
590 priority_enabled, partition_queue, polling_interval_sec
591 FROM queues WHERE name = $1",
592 );
593 let row = sqlx::query(&query)
594 .bind(queue_name)
595 .fetch_optional(&self.pool)
596 .await
597 .map_err(db_err)?;
598 Ok(row.map(row_to_queue_config))
599 }
600
601 async fn upsert_queue(&self, queue: &QueueConfig) -> JournioResult<()> {
602 let now_ms = Utc::now().timestamp_millis();
603 let query = self.q(
604 "INSERT INTO queues (
605 queue_id, name, concurrency, worker_concurrency, rate_limit_max, rate_limit_period_sec,
606 priority_enabled, partition_queue, polling_interval_sec, created_at, updated_at
607 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
608 ON CONFLICT (name)
609 DO UPDATE SET
610 concurrency = excluded.concurrency,
611 worker_concurrency = excluded.worker_concurrency,
612 rate_limit_max = excluded.rate_limit_max,
613 rate_limit_period_sec = excluded.rate_limit_period_sec,
614 priority_enabled = excluded.priority_enabled,
615 partition_queue = excluded.partition_queue,
616 polling_interval_sec = excluded.polling_interval_sec,
617 updated_at = excluded.updated_at",
618 );
619 sqlx::query(&query)
620 .bind(&queue.queue_id)
621 .bind(&queue.name)
622 .bind(queue.concurrency)
623 .bind(queue.worker_concurrency)
624 .bind(queue.rate_limit_max)
625 .bind(queue.rate_limit_period_sec)
626 .bind(queue.priority_enabled)
627 .bind(queue.partition_queue)
628 .bind(queue.polling_interval_sec)
629 .bind(now_ms)
630 .bind(now_ms)
631 .execute(&self.pool)
632 .await
633 .map_err(db_err)?;
634 Ok(())
635 }
636
637 async fn record_step_output(&self, step: &StepRecord) -> JournioResult<()> {
638 let now_ms = Utc::now().timestamp_millis();
639 let query = self.q("INSERT INTO operation_outputs \
640 (workflow_uuid, function_id, function_name, output, error, child_workflow_id, \
641 started_at_epoch_ms, completed_at_epoch_ms, serialization) \
642 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)");
643 sqlx::query(&query)
644 .bind(&step.workflow_uuid)
645 .bind(step.function_id)
646 .bind(&step.function_name)
647 .bind(&step.output)
648 .bind(&step.error)
649 .bind(&step.child_workflow_id)
650 .bind(now_ms)
651 .bind(now_ms)
652 .bind(SERIALIZATION_JSON)
653 .execute(&self.pool)
654 .await
655 .map_err(|err| {
656 if is_unique_violation(&err) {
657 JournioError {
658 code: JournioErrorCode::ConflictingIDError,
659 message: format!("Conflicting workflow ID {}", step.workflow_uuid),
660 workflow_id: Some(step.workflow_uuid.clone()),
661 source: Some(Box::new(err)),
662 ..Default::default()
663 }
664 } else {
665 db_err(err)
666 }
667 })?;
668 Ok(())
669 }
670
671 async fn get_steps(&self, workflow_id: &str) -> JournioResult<Vec<StepRecord>> {
672 let query = self.q(
673 "SELECT workflow_uuid, function_id, function_name, output, error, child_workflow_id \
674 FROM operation_outputs WHERE workflow_uuid = $1 ORDER BY function_id ASC",
675 );
676 let rows = sqlx::query(&query)
677 .bind(workflow_id)
678 .fetch_all(&self.pool)
679 .await
680 .map_err(db_err)?;
681 Ok(rows
682 .into_iter()
683 .map(|row| StepRecord {
684 workflow_uuid: row.get(0),
685 function_id: row.get(1),
686 function_name: row.get(2),
687 output: row.get(3),
688 error: row.get(4),
689 child_workflow_id: row.get(5),
690 })
691 .collect())
692 }
693
694 async fn dequeue_workflow(
695 &self,
696 queue_name: &str,
697 executor_id: &str,
698 ) -> JournioResult<Option<WorkflowStatus>> {
699 let now_ms = Utc::now().timestamp_millis();
700 let queue = self.get_queue(queue_name).await?;
701 let candidates_query = self.q("SELECT workflow_uuid, queue_partition_key
702 FROM workflow_status
703 WHERE queue_name = $1
704 AND status IN ($2, $3)
705 AND (
706 status = $2
707 OR delay_until_epoch_ms IS NULL
708 OR delay_until_epoch_ms <= $4
709 )
710 ORDER BY priority ASC, created_at ASC");
711 let candidates = sqlx::query(&candidates_query)
712 .bind(queue_name)
713 .bind(status_to_str(WorkflowStatusType::Enqueued))
714 .bind(status_to_str(WorkflowStatusType::Delayed))
715 .bind(now_ms)
716 .fetch_all(&self.pool)
717 .await
718 .map_err(db_err)?;
719
720 let mut selected_id = None;
721 for candidate in candidates {
722 let workflow_id: String = candidate.get(0);
723 let partition_key: Option<String> = candidate.get(1);
724
725 if queue
726 .as_ref()
727 .is_some_and(|cfg| cfg.partition_queue && partition_key.is_none())
728 {
729 continue;
730 }
731
732 if let Some(cfg) = queue.as_ref() {
733 if let Some(limit) = cfg.concurrency {
734 let query = if cfg.partition_queue {
735 self.q("SELECT COUNT(*) FROM workflow_status
736 WHERE queue_name = $1 AND status = $2 AND queue_partition_key = $3")
737 } else {
738 self.q("SELECT COUNT(*) FROM workflow_status
739 WHERE queue_name = $1 AND status = $2")
740 };
741 let count: i64 = if cfg.partition_queue {
742 sqlx::query_scalar(&query)
743 .bind(queue_name)
744 .bind(status_to_str(WorkflowStatusType::Pending))
745 .bind(&partition_key)
746 .fetch_one(&self.pool)
747 .await
748 .map_err(db_err)?
749 } else {
750 sqlx::query_scalar(&query)
751 .bind(queue_name)
752 .bind(status_to_str(WorkflowStatusType::Pending))
753 .fetch_one(&self.pool)
754 .await
755 .map_err(db_err)?
756 };
757 if count >= i64::from(limit) {
758 continue;
759 }
760 }
761
762 if let (Some(limit), Some(period_sec)) =
763 (cfg.rate_limit_max, cfg.rate_limit_period_sec)
764 {
765 let cutoff_ms = now_ms - (period_sec * 1000.0) as i64;
766 let query = if cfg.partition_queue {
767 self.q("SELECT COUNT(*) FROM workflow_status
768 WHERE queue_name = $1
769 AND rate_limited = 1
770 AND status NOT IN ($2, $3)
771 AND started_at_epoch_ms > $4
772 AND queue_partition_key = $5")
773 } else {
774 self.q("SELECT COUNT(*) FROM workflow_status
775 WHERE queue_name = $1
776 AND rate_limited = 1
777 AND status NOT IN ($2, $3)
778 AND started_at_epoch_ms > $4")
779 };
780 let count: i64 = if cfg.partition_queue {
781 sqlx::query_scalar(&query)
782 .bind(queue_name)
783 .bind(status_to_str(WorkflowStatusType::Enqueued))
784 .bind(status_to_str(WorkflowStatusType::Delayed))
785 .bind(cutoff_ms)
786 .bind(&partition_key)
787 .fetch_one(&self.pool)
788 .await
789 .map_err(db_err)?
790 } else {
791 sqlx::query_scalar(&query)
792 .bind(queue_name)
793 .bind(status_to_str(WorkflowStatusType::Enqueued))
794 .bind(status_to_str(WorkflowStatusType::Delayed))
795 .bind(cutoff_ms)
796 .fetch_one(&self.pool)
797 .await
798 .map_err(db_err)?
799 };
800 if count >= i64::from(limit) {
801 continue;
802 }
803 }
804 }
805
806 selected_id = Some(workflow_id);
807 break;
808 }
809
810 let Some(selected_id) = selected_id else {
811 return Ok(None);
812 };
813
814 let mut tx = self.pool.begin().await.map_err(db_err)?;
815 let query = self.q(
816 "UPDATE workflow_status
817 SET status = $1,
818 executor_id = $2,
819 started_at_epoch_ms = $3,
820 rate_limited = $4,
821 workflow_deadline_epoch_ms = CASE
822 WHEN workflow_timeout_ms IS NOT NULL AND workflow_deadline_epoch_ms IS NULL
823 THEN $3 + workflow_timeout_ms
824 ELSE workflow_deadline_epoch_ms
825 END
826 WHERE workflow_uuid = $5
827 AND status IN ($6, $7)
828 RETURNING workflow_uuid, status, name, authenticated_user, assumed_role, authenticated_roles,
829 output, error, executor_id, created_at, updated_at, application_version, application_id,
830 recovery_attempts, queue_name, workflow_timeout_ms, workflow_deadline_epoch_ms,
831 started_at_epoch_ms, deduplication_id, inputs, priority, queue_partition_key,
832 forked_from, was_forked_from, parent_workflow_id, completed_at, class_name, config_name,
833 serialization, delay_until_epoch_ms",
834 );
835 let row = sqlx::query(&query)
836 .bind(status_to_str(WorkflowStatusType::Pending))
837 .bind(executor_id)
838 .bind(now_ms)
839 .bind(
840 queue
841 .as_ref()
842 .is_some_and(|cfg| cfg.rate_limit_max.is_some()),
843 )
844 .bind(selected_id)
845 .bind(status_to_str(WorkflowStatusType::Enqueued))
846 .bind(status_to_str(WorkflowStatusType::Delayed))
847 .fetch_optional(&mut *tx)
848 .await
849 .map_err(db_err)?;
850 tx.commit().await.map_err(db_err)?;
851 Ok(row.map(row_to_workflow_status))
852 }
853
854 async fn list_runnable_queues(&self) -> JournioResult<Vec<String>> {
855 let query = self.q("SELECT DISTINCT queue_name
856 FROM workflow_status
857 WHERE queue_name IS NOT NULL
858 AND status IN ($1, $2)
859 ORDER BY queue_name ASC");
860 let rows = sqlx::query(&query)
861 .bind(status_to_str(WorkflowStatusType::Enqueued))
862 .bind(status_to_str(WorkflowStatusType::Delayed))
863 .fetch_all(&self.pool)
864 .await
865 .map_err(db_err)?;
866 Ok(rows
867 .into_iter()
868 .filter_map(|row| row.get::<Option<String>, _>(0))
869 .collect())
870 }
871
872 async fn list_queues(&self) -> JournioResult<Vec<QueueConfig>> {
873 let query = self.q(
874 "SELECT queue_id, name, concurrency, worker_concurrency, rate_limit_max,
875 rate_limit_period_sec, priority_enabled, partition_queue, polling_interval_sec
876 FROM queues
877 ORDER BY name ASC",
878 );
879 let rows = sqlx::query(&query)
880 .fetch_all(&self.pool)
881 .await
882 .map_err(db_err)?;
883 Ok(rows.into_iter().map(row_to_queue_config).collect())
884 }
885
886 async fn send(
887 &self,
888 destination_id: &str,
889 topic: &str,
890 message: &Interchange,
891 ) -> JournioResult<()> {
892 let topic = if topic.is_empty() { NULL_TOPIC } else { topic };
893 let message_str = stringify_json(message);
894 let message_uuid = uuid::Uuid::new_v4().to_string();
895 let created_ms = Utc::now().timestamp_millis();
896 let query = self.q(
897 "INSERT INTO notifications (destination_uuid, topic, message, serialization, message_uuid, created_at_epoch_ms) \
898 VALUES ($1, $2, $3, $4, $5, $6)",
899 );
900 sqlx::query(&query)
901 .bind(destination_id)
902 .bind(topic)
903 .bind(message_str)
904 .bind(SERIALIZATION_JSON)
905 .bind(message_uuid)
906 .bind(created_ms)
907 .execute(&self.pool)
908 .await
909 .map_err(|err| {
910 if is_foreign_key_violation(&err) {
911 JournioError {
912 code: JournioErrorCode::NonExistentWorkflowError,
913 message: format!("destination workflow {destination_id} does not exist"),
914 workflow_id: Some(destination_id.to_string()),
915 source: Some(Box::new(err)),
916 ..Default::default()
917 }
918 } else {
919 db_err(err)
920 }
921 })?;
922 Ok(())
923 }
924
925 async fn consume_notification(
926 &self,
927 workflow_id: &str,
928 topic: &str,
929 ) -> JournioResult<Option<journio_core::Notification>> {
930 let topic = if topic.is_empty() { NULL_TOPIC } else { topic };
931 let mut tx = self.pool.begin().await.map_err(db_err)?;
932 let select = self.q("SELECT message_uuid, message, serialization \
933 FROM notifications \
934 WHERE destination_uuid = $1 AND topic = $2 AND consumed = false \
935 ORDER BY created_at_epoch_ms ASC LIMIT 1");
936 let Some(row) = sqlx::query(&select)
937 .bind(workflow_id)
938 .bind(topic)
939 .fetch_optional(&mut *tx)
940 .await
941 .map_err(db_err)?
942 else {
943 tx.commit().await.map_err(db_err)?;
944 return Ok(None);
945 };
946
947 let message_uuid: String = row.get(0);
948 let message_str: Option<String> = row.get(1);
949 let serialization: Option<String> = row.get(2);
950
951 let update = self.q(
952 "UPDATE notifications SET consumed = true WHERE message_uuid = $1 AND consumed = false",
953 );
954 let updated = sqlx::query(&update)
955 .bind(&message_uuid)
956 .execute(&mut *tx)
957 .await
958 .map_err(db_err)?
959 .rows_affected();
960 tx.commit().await.map_err(db_err)?;
961 if updated == 0 {
962 return Ok(None);
963 }
964
965 let message = message_str
966 .and_then(|s| serde_json::from_str::<Interchange>(&s).ok())
967 .unwrap_or(Interchange::Null);
968 Ok(Some(journio_core::Notification {
969 message,
970 serialization,
971 }))
972 }
973
974 async fn set_event(
975 &self,
976 workflow_id: &str,
977 key: &str,
978 value: &Interchange,
979 function_id: i32,
980 ) -> JournioResult<()> {
981 let value_str = stringify_json(value);
982 let mut tx = self.pool.begin().await.map_err(db_err)?;
983
984 sqlx::query(&self.q(
985 "INSERT INTO workflow_events (workflow_uuid, key, value, serialization) \
986 VALUES ($1, $2, $3, $4) \
987 ON CONFLICT (workflow_uuid, key) \
988 DO UPDATE SET value = excluded.value, serialization = excluded.serialization",
989 ))
990 .bind(workflow_id)
991 .bind(key)
992 .bind(&value_str)
993 .bind(SERIALIZATION_JSON)
994 .execute(&mut *tx)
995 .await
996 .map_err(db_err)?;
997
998 sqlx::query(
999 &self.q(
1000 "INSERT INTO workflow_events_history (workflow_uuid, function_id, key, value, serialization) \
1001 VALUES ($1, $2, $3, $4, $5) \
1002 ON CONFLICT (workflow_uuid, function_id, key) \
1003 DO UPDATE SET value = excluded.value, serialization = excluded.serialization",
1004 ),
1005 )
1006 .bind(workflow_id)
1007 .bind(function_id)
1008 .bind(key)
1009 .bind(&value_str)
1010 .bind(SERIALIZATION_JSON)
1011 .execute(&mut *tx)
1012 .await
1013 .map_err(db_err)?;
1014
1015 tx.commit().await.map_err(db_err)?;
1016 Ok(())
1017 }
1018
1019 async fn get_event_value(
1020 &self,
1021 workflow_id: &str,
1022 key: &str,
1023 ) -> JournioResult<Option<Interchange>> {
1024 let query =
1025 self.q("SELECT value FROM workflow_events WHERE workflow_uuid = $1 AND key = $2");
1026 let row = sqlx::query(&query)
1027 .bind(workflow_id)
1028 .bind(key)
1029 .fetch_optional(&self.pool)
1030 .await
1031 .map_err(db_err)?;
1032 let Some(row) = row else { return Ok(None) };
1033 let value_str: Option<String> = row.get(0);
1034 Ok(value_str.and_then(|s| serde_json::from_str::<Interchange>(&s).ok()))
1035 }
1036
1037 async fn write_stream(
1038 &self,
1039 workflow_id: &str,
1040 key: &str,
1041 value: &str,
1042 function_id: i32,
1043 serialization: Option<&str>,
1044 ) -> JournioResult<()> {
1045 let check_closed = self.q("SELECT 1 FROM streams
1046 WHERE workflow_uuid = $1 AND key = $2 AND value = $3
1047 LIMIT 1");
1048 let existing = sqlx::query_scalar::<_, i64>(&check_closed)
1049 .bind(workflow_id)
1050 .bind(key)
1051 .bind(STREAM_CLOSED_SENTINEL)
1052 .fetch_optional(&self.pool)
1053 .await
1054 .map_err(db_err)?;
1055 if existing.is_some() {
1056 return Err(JournioError::new(
1057 JournioErrorCode::WorkflowExecutionError,
1058 format!("stream {key:?} is already closed"),
1059 ));
1060 }
1061
1062 let query = self.q(
1063 "INSERT INTO streams (workflow_uuid, key, value, \"offset\", function_id, serialization)
1064 SELECT $1, $2, $3,
1065 COALESCE((SELECT MAX(\"offset\") FROM streams WHERE workflow_uuid = $1 AND key = $2), -1) + 1,
1066 $4, $5",
1067 );
1068 sqlx::query(&query)
1069 .bind(workflow_id)
1070 .bind(key)
1071 .bind(value)
1072 .bind(function_id)
1073 .bind(serialization)
1074 .execute(&self.pool)
1075 .await
1076 .map_err(db_err)?;
1077 Ok(())
1078 }
1079
1080 async fn read_stream(
1081 &self,
1082 workflow_id: &str,
1083 key: &str,
1084 from_offset: i64,
1085 ) -> JournioResult<(Vec<StreamEntry>, bool)> {
1086 let query = self.q("SELECT value, \"offset\", serialization
1087 FROM streams
1088 WHERE workflow_uuid = $1 AND key = $2 AND \"offset\" >= $3
1089 ORDER BY \"offset\" ASC");
1090 let rows = sqlx::query(&query)
1091 .bind(workflow_id)
1092 .bind(key)
1093 .bind(from_offset)
1094 .fetch_all(&self.pool)
1095 .await
1096 .map_err(db_err)?;
1097
1098 let mut entries = Vec::new();
1099 let mut closed = false;
1100 for row in rows {
1101 let value: String = row.get(0);
1102 let offset: i64 = row.get(1);
1103 let serialization: Option<String> = row.get(2);
1104 if value == STREAM_CLOSED_SENTINEL {
1105 closed = true;
1106 break;
1107 }
1108 entries.push(StreamEntry {
1109 value,
1110 offset,
1111 serialization,
1112 });
1113 }
1114 Ok((entries, closed))
1115 }
1116
1117 async fn get_workflows_for_recovery(
1118 &self,
1119 executor_id: &str,
1120 ) -> JournioResult<Vec<WorkflowStatus>> {
1121 let query = format!(
1122 "{} WHERE executor_id = ?1 AND status IN ('PENDING', 'ENQUEUED', 'DELAYED') ORDER BY created_at ASC",
1123 self.q(WORKFLOW_STATUS_SELECT)
1124 );
1125 let rows = sqlx::query(&query)
1126 .bind(executor_id)
1127 .fetch_all(&self.pool)
1128 .await
1129 .map_err(db_err)?;
1130 Ok(rows.into_iter().map(row_to_workflow_status).collect())
1131 }
1132
1133 async fn delete_workflows_before(&self, before: DateTime<Utc>) -> JournioResult<u64> {
1134 let before_ms = before.timestamp_millis();
1135 let query = self.q(
1136 "DELETE FROM workflow_status WHERE status IN ('SUCCESS','ERROR','CANCELLED') AND completed_at < $1",
1137 );
1138 let rows = sqlx::query(&query)
1139 .bind(before_ms)
1140 .execute(&self.pool)
1141 .await
1142 .map_err(db_err)?
1143 .rows_affected();
1144 Ok(rows)
1145 }
1146
1147 async fn upsert_schedule(&self, schedule: &WorkflowSchedule) -> JournioResult<()> {
1148 let query = self.q(
1149 "INSERT INTO workflow_schedules
1150 (schedule_id, schedule_name, workflow_name, workflow_class_name, schedule, status, context, last_fired_at, automatic_backfill, cron_timezone, queue_name)
1151 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
1152 ON CONFLICT (schedule_name)
1153 DO UPDATE SET
1154 schedule_id = excluded.schedule_id,
1155 workflow_name = excluded.workflow_name,
1156 workflow_class_name = excluded.workflow_class_name,
1157 schedule = excluded.schedule,
1158 status = excluded.status,
1159 context = excluded.context,
1160 last_fired_at = excluded.last_fired_at,
1161 automatic_backfill = excluded.automatic_backfill,
1162 cron_timezone = excluded.cron_timezone,
1163 queue_name = excluded.queue_name",
1164 );
1165 sqlx::query(&query)
1166 .bind(&schedule.schedule_id)
1167 .bind(&schedule.schedule_name)
1168 .bind(&schedule.workflow_name)
1169 .bind(&schedule.workflow_class_name)
1170 .bind(&schedule.schedule)
1171 .bind(schedule_status_to_str(schedule.status))
1172 .bind(stringify_json(&schedule.context))
1173 .bind(schedule.last_fired_at.map(|value| value.to_rfc3339()))
1174 .bind(schedule.automatic_backfill)
1175 .bind(&schedule.cron_timezone)
1176 .bind(&schedule.queue_name)
1177 .execute(&self.pool)
1178 .await
1179 .map_err(db_err)?;
1180 Ok(())
1181 }
1182
1183 async fn list_schedules(&self) -> JournioResult<Vec<WorkflowSchedule>> {
1184 let query = self.q(
1185 "SELECT schedule_id, schedule_name, workflow_name, workflow_class_name, schedule, status, context, last_fired_at, automatic_backfill, cron_timezone, queue_name
1186 FROM workflow_schedules
1187 ORDER BY schedule_name ASC",
1188 );
1189 let rows = sqlx::query(&query)
1190 .fetch_all(&self.pool)
1191 .await
1192 .map_err(db_err)?;
1193 Ok(rows.into_iter().map(row_to_workflow_schedule).collect())
1194 }
1195
1196 async fn update_schedule_last_fired_at(
1197 &self,
1198 schedule_name: &str,
1199 fired_at: DateTime<Utc>,
1200 ) -> JournioResult<()> {
1201 let query =
1202 self.q("UPDATE workflow_schedules SET last_fired_at = $1 WHERE schedule_name = $2");
1203 sqlx::query(&query)
1204 .bind(fired_at.to_rfc3339())
1205 .bind(schedule_name)
1206 .execute(&self.pool)
1207 .await
1208 .map_err(db_err)?;
1209 Ok(())
1210 }
1211
1212 async fn get_schedule(&self, schedule_name: &str) -> JournioResult<Option<WorkflowSchedule>> {
1213 let query = self.q(
1214 "SELECT schedule_id, schedule_name, workflow_name, workflow_class_name, schedule, status, context, last_fired_at, automatic_backfill, cron_timezone, queue_name
1215 FROM workflow_schedules WHERE schedule_name = $1",
1216 );
1217 let row = sqlx::query(&query)
1218 .bind(schedule_name)
1219 .fetch_optional(&self.pool)
1220 .await
1221 .map_err(db_err)?;
1222 Ok(row.map(row_to_workflow_schedule))
1223 }
1224
1225 async fn delete_schedule(&self, schedule_name: &str) -> JournioResult<()> {
1226 let query = self.q("DELETE FROM workflow_schedules WHERE schedule_name = $1");
1227 sqlx::query(&query)
1228 .bind(schedule_name)
1229 .execute(&self.pool)
1230 .await
1231 .map_err(db_err)?;
1232 Ok(())
1233 }
1234
1235 async fn update_schedule_status(
1236 &self,
1237 schedule_name: &str,
1238 status: ScheduleStatus,
1239 ) -> JournioResult<()> {
1240 let query = self.q("UPDATE workflow_schedules SET status = $1 WHERE schedule_name = $2");
1241 sqlx::query(&query)
1242 .bind(schedule_status_to_str(status))
1243 .bind(schedule_name)
1244 .execute(&self.pool)
1245 .await
1246 .map_err(db_err)?;
1247 Ok(())
1248 }
1249
1250 async fn create_application_version(&self, version_name: &str) -> JournioResult<()> {
1251 let query = self.q(
1252 "INSERT INTO application_versions (version_id, version_name, version_timestamp, created_at)
1253 VALUES ($1, $2, $3, $4)
1254 ON CONFLICT (version_name) DO NOTHING",
1255 );
1256 let now_ms = Utc::now().timestamp_millis();
1257 sqlx::query(&query)
1258 .bind(uuid::Uuid::new_v4().to_string())
1259 .bind(version_name)
1260 .bind(now_ms)
1261 .bind(now_ms)
1262 .execute(&self.pool)
1263 .await
1264 .map_err(db_err)?;
1265 Ok(())
1266 }
1267
1268 async fn update_application_version_timestamp(
1269 &self,
1270 version_name: &str,
1271 timestamp_ms: i64,
1272 ) -> JournioResult<()> {
1273 let query = self
1274 .q("UPDATE application_versions SET version_timestamp = $1 WHERE version_name = $2");
1275 sqlx::query(&query)
1276 .bind(timestamp_ms)
1277 .bind(version_name)
1278 .execute(&self.pool)
1279 .await
1280 .map_err(db_err)?;
1281 Ok(())
1282 }
1283
1284 async fn list_application_versions(&self) -> JournioResult<Vec<VersionInfo>> {
1285 let query = self.q(
1286 "SELECT version_id, version_name, version_timestamp, created_at FROM application_versions ORDER BY version_timestamp DESC",
1287 );
1288 let rows = sqlx::query(&query)
1289 .fetch_all(&self.pool)
1290 .await
1291 .map_err(db_err)?;
1292 Ok(rows
1293 .into_iter()
1294 .map(|row| VersionInfo {
1295 version_id: row.get(0),
1296 version_name: row.get(1),
1297 version_timestamp: row.get(2),
1298 created_at: row.get(3),
1299 })
1300 .collect())
1301 }
1302
1303 async fn get_latest_application_version(&self) -> JournioResult<Option<VersionInfo>> {
1304 let query = self.q(
1305 "SELECT version_id, version_name, version_timestamp, created_at FROM application_versions ORDER BY version_timestamp DESC LIMIT 1",
1306 );
1307 let row = sqlx::query(&query)
1308 .fetch_optional(&self.pool)
1309 .await
1310 .map_err(db_err)?;
1311 Ok(row.map(|row| VersionInfo {
1312 version_id: row.get(0),
1313 version_name: row.get(1),
1314 version_timestamp: row.get(2),
1315 created_at: row.get(3),
1316 }))
1317 }
1318
1319 async fn set_workflow_delay(
1320 &self,
1321 workflow_id: &str,
1322 delay_until: DateTime<Utc>,
1323 ) -> JournioResult<()> {
1324 let query = self.q(
1325 "UPDATE workflow_status SET delay_until_epoch_ms = $1, updated_at = $2 WHERE workflow_uuid = $3 AND status = $4",
1326 );
1327 sqlx::query(&query)
1328 .bind(delay_until.timestamp_millis())
1329 .bind(Utc::now().timestamp_millis())
1330 .bind(workflow_id)
1331 .bind(status_to_str(WorkflowStatusType::Delayed))
1332 .execute(&self.pool)
1333 .await
1334 .map_err(db_err)?;
1335 Ok(())
1336 }
1337
1338 async fn delete_workflows(
1339 &self,
1340 workflow_ids: &[String],
1341 delete_children: bool,
1342 ) -> JournioResult<()> {
1343 if workflow_ids.is_empty() {
1344 return Ok(());
1345 }
1346
1347 let mut ids: Vec<String> = workflow_ids.to_vec();
1348 if delete_children {
1349 ids = gather_descendants_sqlite(&self.pool, &ids).await?;
1350 }
1351
1352 let mut delete =
1353 QueryBuilder::<Sqlite>::new("DELETE FROM workflow_status WHERE workflow_uuid IN (");
1354 {
1355 let mut separated = delete.separated(", ");
1356 for id in &ids {
1357 separated.push_bind(id);
1358 }
1359 }
1360 delete.push(")");
1361 delete.build().execute(&self.pool).await.map_err(db_err)?;
1362 Ok(())
1363 }
1364
1365 async fn list_workflows_filtered(
1366 &self,
1367 filter: &ListWorkflowsFilter,
1368 ) -> JournioResult<Vec<WorkflowStatus>> {
1369 let statuses: Vec<String> = filter
1370 .statuses
1371 .iter()
1372 .map(|status| status_to_str(*status))
1373 .collect();
1374
1375 let mut qb = QueryBuilder::<Sqlite>::new(WORKFLOW_STATUS_SELECT);
1376 qb.push(" WHERE 1=1");
1377
1378 if !filter.workflow_ids.is_empty() {
1379 qb.push(" AND workflow_uuid IN (");
1380 push_in_list(&mut qb, &filter.workflow_ids);
1381 qb.push(")");
1382 }
1383 if !filter.workflow_id_prefixes.is_empty() {
1384 qb.push(" AND (");
1385 let mut sep = qb.separated(" OR ");
1386 for prefix in &filter.workflow_id_prefixes {
1387 sep.push("workflow_uuid LIKE ")
1389 .push_bind_unseparated(format!("{prefix}%"));
1390 }
1391 qb.push(")");
1392 }
1393 if !statuses.is_empty() {
1394 qb.push(" AND status IN (");
1395 push_in_list(&mut qb, &statuses);
1396 qb.push(")");
1397 }
1398 if !filter.names.is_empty() {
1399 qb.push(" AND name IN (");
1400 push_in_list(&mut qb, &filter.names);
1401 qb.push(")");
1402 }
1403 if !filter.application_versions.is_empty() {
1404 qb.push(" AND application_version IN (");
1405 push_in_list(&mut qb, &filter.application_versions);
1406 qb.push(")");
1407 }
1408 if filter.queues_only {
1409 qb.push(" AND queue_name IS NOT NULL");
1410 } else if !filter.queue_names.is_empty() {
1411 qb.push(" AND queue_name IN (");
1412 push_in_list(&mut qb, &filter.queue_names);
1413 qb.push(")");
1414 }
1415 if !filter.authenticated_users.is_empty() {
1416 qb.push(" AND authenticated_user IN (");
1417 push_in_list(&mut qb, &filter.authenticated_users);
1418 qb.push(")");
1419 }
1420 if !filter.executor_ids.is_empty() {
1421 qb.push(" AND executor_id IN (");
1422 push_in_list(&mut qb, &filter.executor_ids);
1423 qb.push(")");
1424 }
1425 if !filter.forked_from.is_empty() {
1426 qb.push(" AND forked_from IN (");
1427 push_in_list(&mut qb, &filter.forked_from);
1428 qb.push(")");
1429 }
1430 if !filter.parent_workflow_ids.is_empty() {
1431 qb.push(" AND parent_workflow_id IN (");
1432 push_in_list(&mut qb, &filter.parent_workflow_ids);
1433 qb.push(")");
1434 }
1435 if !filter.deduplication_ids.is_empty() {
1436 qb.push(" AND deduplication_id IN (");
1437 push_in_list(&mut qb, &filter.deduplication_ids);
1438 qb.push(")");
1439 }
1440 if let Some(start) = filter.start_time {
1441 qb.push(" AND created_at >= ")
1442 .push_bind(start.timestamp_millis());
1443 }
1444 if let Some(end) = filter.end_time {
1445 qb.push(" AND created_at <= ")
1446 .push_bind(end.timestamp_millis());
1447 }
1448 if let Some(after) = filter.completed_after {
1449 qb.push(" AND completed_at >= ")
1450 .push_bind(after.timestamp_millis());
1451 }
1452 if let Some(before) = filter.completed_before {
1453 qb.push(" AND completed_at <= ")
1454 .push_bind(before.timestamp_millis());
1455 }
1456
1457 let direction = if filter.sort_desc { "DESC" } else { "ASC" };
1458 qb.push(" ORDER BY created_at ").push(direction);
1459 let limit = filter.limit.unwrap_or(100);
1460 if limit > 0 {
1461 qb.push(" LIMIT ").push_bind(limit);
1462 }
1463 if let Some(offset) = filter.offset {
1464 if offset > 0 {
1465 qb.push(" OFFSET ").push_bind(offset);
1466 }
1467 }
1468
1469 let rows = qb.build().fetch_all(&self.pool).await.map_err(db_err)?;
1470 Ok(rows.into_iter().map(row_to_workflow_status).collect())
1471 }
1472}
1473
1474const WORKFLOW_STATUS_SELECT: &str = "SELECT workflow_uuid, status, name, authenticated_user, assumed_role, authenticated_roles, \
1475 output, error, executor_id, created_at, updated_at, application_version, application_id, \
1476 recovery_attempts, queue_name, workflow_timeout_ms, workflow_deadline_epoch_ms, \
1477 started_at_epoch_ms, deduplication_id, inputs, priority, queue_partition_key, \
1478 forked_from, was_forked_from, parent_workflow_id, completed_at, class_name, config_name, \
1479 serialization, delay_until_epoch_ms \
1480 FROM workflow_status";
1481
1482const WORKFLOW_STATUS_BY_ID: &str = "SELECT workflow_uuid, status, name, authenticated_user, assumed_role, authenticated_roles, \
1483 output, error, executor_id, created_at, updated_at, application_version, application_id, \
1484 recovery_attempts, queue_name, workflow_timeout_ms, workflow_deadline_epoch_ms, \
1485 started_at_epoch_ms, deduplication_id, inputs, priority, queue_partition_key, \
1486 forked_from, was_forked_from, parent_workflow_id, completed_at, class_name, config_name, \
1487 serialization, delay_until_epoch_ms \
1488 FROM workflow_status WHERE workflow_uuid = $1";
1489
1490const WORKFLOW_STATUS_ID_SELECT_PREFIX: &str = "SELECT workflow_uuid FROM workflow_status";
1491
1492fn sqlite_connect_options(database_url: &str) -> JournioResult<SqliteConnectOptions> {
1493 let normalized = normalize_sqlite_url(database_url)?;
1494 let options = SqliteConnectOptions::from_str(&normalized).map_err(|err| {
1495 JournioError::new(
1496 JournioErrorCode::InitializationError,
1497 format!("invalid sqlite connection string {database_url}: {err}"),
1498 )
1499 })?;
1500 Ok(options
1501 .create_if_missing(true)
1502 .journal_mode(SqliteJournalMode::Wal)
1503 .synchronous(SqliteSynchronous::Normal))
1504}
1505
1506fn normalize_sqlite_url(database_url: &str) -> JournioResult<String> {
1507 let lower = database_url.to_ascii_lowercase();
1508 if lower == ":memory:" {
1509 return Ok("sqlite::memory:".to_string());
1510 }
1511
1512 let suffix = if lower.starts_with("sqlite3:") {
1513 &database_url["sqlite3:".len()..]
1514 } else if lower.starts_with("sqlite:") {
1515 &database_url["sqlite:".len()..]
1516 } else {
1517 return Ok(format!("sqlite://{database_url}"));
1518 };
1519
1520 if suffix.is_empty() {
1521 return Err(JournioError::new(
1522 JournioErrorCode::InitializationError,
1523 "invalid sqlite connection string: URL has no path",
1524 ));
1525 }
1526
1527 let normalized = if suffix.starts_with(":memory:") {
1528 "sqlite::memory:".to_string()
1529 } else if suffix.starts_with("//") {
1530 format!("sqlite:{suffix}")
1531 } else if suffix.starts_with('/') {
1532 format!("sqlite://{suffix}")
1533 } else {
1534 format!("sqlite:{suffix}")
1535 };
1536 Ok(normalized)
1537}
1538
1539fn stringify_json(value: &Interchange) -> String {
1540 serde_json::to_string(value).unwrap_or_else(|_| "null".into())
1541}
1542
1543fn status_to_str(s: WorkflowStatusType) -> String {
1544 match s {
1545 WorkflowStatusType::Pending => "PENDING",
1546 WorkflowStatusType::Enqueued => "ENQUEUED",
1547 WorkflowStatusType::Delayed => "DELAYED",
1548 WorkflowStatusType::Success => "SUCCESS",
1549 WorkflowStatusType::Error => "ERROR",
1550 WorkflowStatusType::Cancelled => "CANCELLED",
1551 WorkflowStatusType::MaxRecoveryAttemptsExceeded => "MAX_RECOVERY_ATTEMPTS_EXCEEDED",
1552 }
1553 .to_string()
1554}
1555
1556fn parse_status(s: &str) -> WorkflowStatusType {
1557 match s {
1558 "PENDING" => WorkflowStatusType::Pending,
1559 "ENQUEUED" => WorkflowStatusType::Enqueued,
1560 "DELAYED" => WorkflowStatusType::Delayed,
1561 "SUCCESS" => WorkflowStatusType::Success,
1562 "ERROR" => WorkflowStatusType::Error,
1563 "CANCELLED" => WorkflowStatusType::Cancelled,
1564 "MAX_RECOVERY_ATTEMPTS_EXCEEDED" => WorkflowStatusType::MaxRecoveryAttemptsExceeded,
1565 other => {
1566 tracing::warn!(
1567 status = other,
1568 "unknown workflow status, defaulting to PENDING"
1569 );
1570 WorkflowStatusType::Pending
1571 }
1572 }
1573}
1574
1575fn schedule_status_to_str(status: ScheduleStatus) -> &'static str {
1576 match status {
1577 ScheduleStatus::Active => "ACTIVE",
1578 ScheduleStatus::Paused => "PAUSED",
1579 }
1580}
1581
1582fn parse_schedule_status(s: &str) -> ScheduleStatus {
1583 match s {
1584 "PAUSED" => ScheduleStatus::Paused,
1585 _ => ScheduleStatus::Active,
1586 }
1587}
1588
1589fn timestamp_ms(ms: i64) -> DateTime<Utc> {
1590 DateTime::<Utc>::from_timestamp_millis(ms)
1591 .unwrap_or_else(|| DateTime::<Utc>::from_timestamp(0, 0).expect("unix epoch"))
1592}
1593
1594fn row_to_workflow_status(row: SqliteRow) -> WorkflowStatus {
1595 let roles_json: Option<String> = row.get(5);
1596 let authenticated_roles = roles_json
1597 .and_then(|s| serde_json::from_str::<Vec<String>>(&s).ok())
1598 .filter(|v| !v.is_empty());
1599
1600 let output_str: Option<String> = row.get(6);
1601 let output = output_str.and_then(|s| serde_json::from_str::<Interchange>(&s).ok());
1602
1603 let input_str: Option<String> = row.get(19);
1604 let input = input_str.and_then(|s| serde_json::from_str::<Interchange>(&s).ok());
1605
1606 let created_ms: i64 = row.get(9);
1607 let updated_ms: i64 = row.get(10);
1608 let timeout_ms: Option<i64> = row.get(15);
1609 let deadline_ms: Option<i64> = row.get(16);
1610 let started_ms: Option<i64> = row.get(17);
1611 let completed_ms: Option<i64> = row.get(25);
1612 let delay_ms: Option<i64> = row.get(29);
1613 let was_forked_from_num: Option<i64> = row.get(23);
1614
1615 WorkflowStatus {
1616 id: row.get(0),
1617 status: parse_status(&row.get::<String, _>(1)),
1618 name: row.get(2),
1619 authenticated_user: row.get(3),
1620 assumed_role: row.get(4),
1621 authenticated_roles,
1622 output,
1623 error: row.get(7),
1624 executor_id: row.get(8),
1625 created_at: timestamp_ms(created_ms),
1626 updated_at: timestamp_ms(updated_ms),
1627 application_version: row.try_get::<String, _>(11).unwrap_or_default(),
1628 application_id: row.get(12),
1629 attempts: row.get::<i64, _>(13),
1630 queue_name: row.get(14),
1631 timeout: timeout_ms
1632 .filter(|&m| m > 0)
1633 .map(|m| Duration::from_millis(m as u64)),
1634 deadline: deadline_ms.map(timestamp_ms),
1635 started_at: started_ms.map(timestamp_ms),
1636 deduplication_id: row.get(18),
1637 input,
1638 priority: row.get(20),
1639 queue_partition_key: row.get(21),
1640 forked_from: row.get(22),
1641 was_forked_from: was_forked_from_num.unwrap_or(0) != 0,
1642 parent_workflow_id: row.get(24),
1643 completed_at: completed_ms.map(timestamp_ms),
1644 class_name: row.get(26),
1645 config_name: row.get(27),
1646 serialization: row.get(28),
1647 delay_until: delay_ms.map(timestamp_ms),
1648 }
1649}
1650
1651fn row_to_workflow_schedule(row: SqliteRow) -> WorkflowSchedule {
1652 let context_str: String = row.get(6);
1653 let last_fired_at_str: Option<String> = row.get(7);
1654 WorkflowSchedule {
1655 schedule_id: row.get(0),
1656 schedule_name: row.get(1),
1657 workflow_name: row.get(2),
1658 workflow_class_name: row.get(3),
1659 schedule: row.get(4),
1660 status: parse_schedule_status(&row.get::<String, _>(5)),
1661 context: serde_json::from_str(&context_str).unwrap_or(Interchange::Null),
1662 last_fired_at: last_fired_at_str
1663 .and_then(|value| DateTime::parse_from_rfc3339(&value).ok())
1664 .map(|value| value.with_timezone(&Utc)),
1665 automatic_backfill: row.get(8),
1666 cron_timezone: row.get(9),
1667 queue_name: row.get(10),
1668 }
1669}
1670
1671fn row_to_queue_config(row: SqliteRow) -> QueueConfig {
1672 QueueConfig {
1673 queue_id: row.get(0),
1674 name: row.get(1),
1675 concurrency: row.get(2),
1676 worker_concurrency: row.get(3),
1677 rate_limit_max: row.get(4),
1678 rate_limit_period_sec: row.get(5),
1679 priority_enabled: row.get::<i64, _>(6) != 0,
1680 partition_queue: row.get::<i64, _>(7) != 0,
1681 polling_interval_sec: row.get(8),
1682 }
1683}
1684
1685fn push_in_list<'a>(qb: &mut QueryBuilder<'a, Sqlite>, values: &'a [String]) {
1688 let mut separated = qb.separated(", ");
1689 for value in values {
1690 separated.push_bind(value);
1691 }
1692}
1693
1694async fn gather_descendants_sqlite(
1698 pool: &SqlitePool,
1699 roots: &[String],
1700) -> JournioResult<Vec<String>> {
1701 let mut all: Vec<String> = roots.to_vec();
1702 let mut queue: Vec<String> = roots.to_vec();
1703 while let Some(parent) = queue.pop() {
1704 let mut qb = QueryBuilder::<Sqlite>::new(
1705 "SELECT workflow_uuid FROM workflow_status WHERE parent_workflow_id = ",
1706 );
1707 qb.push_bind(parent.clone());
1708 let rows = qb.build().fetch_all(pool).await.map_err(db_err)?;
1709 for row in rows {
1710 let id: String = row.get(0);
1711 if !all.contains(&id) {
1712 all.push(id.clone());
1713 queue.push(id);
1714 }
1715 }
1716 }
1717 Ok(all)
1718}
1719
1720#[cfg(test)]
1721mod tests {
1722 use super::normalize_sqlite_url;
1723
1724 #[test]
1725 fn normalize_sqlite_url_supports_common_forms() {
1726 let cases = [
1727 ("sqlite:/tmp/x.db", "sqlite:///tmp/x.db"),
1728 ("sqlite:///tmp/x.db", "sqlite:///tmp/x.db"),
1729 ("sqlite::memory:", "sqlite::memory:"),
1730 ("sqlite:relative.db", "sqlite:relative.db"),
1731 ("sqlite3:relative.db", "sqlite:relative.db"),
1732 ("SQLITE:/tmp/x.db", "sqlite:///tmp/x.db"),
1733 (":memory:", "sqlite::memory:"),
1734 ];
1735 for (input, expected) in cases {
1736 assert_eq!(normalize_sqlite_url(input).unwrap(), expected, "{input}");
1737 }
1738 }
1739
1740 #[test]
1741 fn normalize_sqlite_url_rejects_missing_path() {
1742 for input in ["sqlite:", "sqlite3:"] {
1743 assert!(normalize_sqlite_url(input).is_err(), "{input}");
1744 }
1745 }
1746}