Skip to main content

journio_sqlite/
lib_impl.rs

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                // Go appends "%" for a prefix match (`addWhereLikeAny`).
1388                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
1685/// Append a comma-separated, bind-parameter list of `values` to `qb`, with no
1686/// surrounding parentheses — the caller writes the `IN (` / `)`.
1687fn 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
1694/// Breadth-first gather of every descendant workflow id of `roots` (ported
1695/// from `getWorkflowChildren` in `system_database.go`). Includes the roots
1696/// themselves in the returned set.
1697async 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}