Skip to main content

qail_workflow_postgres/
store.rs

1use std::sync::Arc;
2use std::time::Duration;
3
4use async_trait::async_trait;
5use chrono::{DateTime, Utc};
6use qail_core::ast::{Operator, Qail, Value as QailValue};
7use qail_pg::{PgDriver, PgPool, PgRow, PooledConnection};
8use qail_workflow::{
9    WorkflowContext, WorkflowError, WorkflowLease, WorkflowOperation, WorkflowOperationStatus,
10    WorkflowSideEffect, WorkflowSideEffectStatus,
11};
12use tokio::sync::Mutex;
13
14use crate::tables::PgWorkflowTables;
15use crate::util::{
16    STATUS_COMPLETED, STATUS_FAILED, STATUS_STARTED, deadline_after, deadline_from_duration,
17    excluded, finish_pool_connection, finish_pool_tx, finish_tx, is_duplicate, is_stale_timestamp,
18    json_error, missing_started_row, operation_kind_text, option_string, optional_json, pg_error,
19    required_string, timestamp,
20};
21
22#[derive(Clone)]
23enum PgWorkflowBackend {
24    Driver(Arc<Mutex<PgDriver>>),
25    Pool(PgPool),
26}
27
28// async_trait marks each boxed future #[must_use]; clippy 1.99 already
29// counts the boxed future as must_use and denies the pair.
30#[allow(clippy::double_must_use)]
31#[async_trait]
32trait PgWorkflowConnection: Send {
33    async fn execute_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<u64>;
34    async fn fetch_all_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<Vec<PgRow>>;
35}
36
37#[async_trait]
38impl PgWorkflowConnection for PgDriver {
39    async fn execute_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<u64> {
40        self.execute(cmd).await
41    }
42
43    async fn fetch_all_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<Vec<PgRow>> {
44        self.fetch_all(cmd).await
45    }
46}
47
48#[async_trait]
49impl PgWorkflowConnection for PooledConnection {
50    async fn execute_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<u64> {
51        self.execute(cmd).await
52    }
53
54    async fn fetch_all_ast(&mut self, cmd: &Qail) -> qail_pg::PgResult<Vec<PgRow>> {
55        self.fetch_all_uncached(cmd).await
56    }
57}
58
59/// PostgreSQL workflow storage using either a single `PgDriver` or a `PgPool`.
60#[derive(Clone)]
61pub struct PgWorkflowStore {
62    backend: PgWorkflowBackend,
63    tables: PgWorkflowTables,
64    timeout_claim_ttl: Duration,
65    in_progress_ttl: Duration,
66}
67
68impl PgWorkflowStore {
69    /// Create a store around an existing driver.
70    pub fn new(driver: PgDriver) -> Self {
71        Self::from_shared(Arc::new(Mutex::new(driver)))
72    }
73
74    /// Create a store around a shared driver handle.
75    pub fn from_shared(driver: Arc<Mutex<PgDriver>>) -> Self {
76        Self {
77            backend: PgWorkflowBackend::Driver(driver),
78            tables: PgWorkflowTables::default(),
79            timeout_claim_ttl: Duration::from_secs(60),
80            in_progress_ttl: Duration::from_secs(3600),
81        }
82    }
83
84    /// Create a store around an existing PostgreSQL connection pool.
85    ///
86    /// The store uses raw pooled connections because workflow ledger tables are
87    /// internal runtime state, not tenant-scoped application data.
88    pub fn from_pool(pool: PgPool) -> Self {
89        Self {
90            backend: PgWorkflowBackend::Pool(pool),
91            tables: PgWorkflowTables::default(),
92            timeout_claim_ttl: Duration::from_secs(60),
93            in_progress_ttl: Duration::from_secs(3600),
94        }
95    }
96
97    /// Connect a store from a PostgreSQL URL.
98    pub async fn connect_url(url: &str) -> Result<Self, WorkflowError> {
99        let driver = PgDriver::connect_url(url).await.map_err(pg_error)?;
100        Ok(Self::new(driver))
101    }
102
103    /// Override storage table names.
104    pub fn with_tables(mut self, tables: PgWorkflowTables) -> Self {
105        self.tables = tables;
106        self
107    }
108
109    /// Override how long a timeout drain claim should be held.
110    pub fn with_timeout_claim_ttl(mut self, ttl: Duration) -> Self {
111        self.timeout_claim_ttl = ttl;
112        self
113    }
114
115    /// Override how long a started operation or side effect is considered in progress.
116    pub fn with_in_progress_ttl(mut self, ttl: Duration) -> Self {
117        self.in_progress_ttl = ttl;
118        self
119    }
120
121    /// Return the storage table names.
122    pub fn tables(&self) -> &PgWorkflowTables {
123        &self.tables
124    }
125
126    /// Return QAIL AST schema commands for this store.
127    pub fn schema_commands(&self) -> Vec<Qail> {
128        self.tables.schema_commands()
129    }
130
131    async fn execute_command(&self, cmd: &Qail) -> Result<u64, WorkflowError> {
132        match &self.backend {
133            PgWorkflowBackend::Driver(driver) => {
134                let mut driver = driver.lock().await;
135                driver.execute_ast(cmd).await.map_err(pg_error)
136            }
137            PgWorkflowBackend::Pool(pool) => {
138                // SAFETY: workflow storage owns internal ledger tables and every
139                // command is generated as QAIL AST, so no tenant app data is read.
140                let mut conn = pool.acquire_raw().await.map_err(pg_error)?;
141                let result = conn.execute_ast(cmd).await.map_err(pg_error);
142                finish_pool_connection(conn, result).await
143            }
144        }
145    }
146
147    async fn fetch_all_command(&self, cmd: &Qail) -> Result<Vec<PgRow>, WorkflowError> {
148        match &self.backend {
149            PgWorkflowBackend::Driver(driver) => {
150                let mut driver = driver.lock().await;
151                driver.fetch_all_ast(cmd).await.map_err(pg_error)
152            }
153            PgWorkflowBackend::Pool(pool) => {
154                // SAFETY: workflow storage owns internal ledger tables and every
155                // command is generated as QAIL AST, so no tenant app data is read.
156                let mut conn = pool.acquire_raw().await.map_err(pg_error)?;
157                let result = conn.fetch_all_ast(cmd).await.map_err(pg_error);
158                finish_pool_connection(conn, result).await
159            }
160        }
161    }
162
163    async fn begin_pool_transaction(pool: &PgPool) -> Result<PooledConnection, WorkflowError> {
164        // SAFETY: workflow storage owns internal ledger tables and every command
165        // is generated as QAIL AST, so no tenant app data is read.
166        let mut conn = pool.acquire_raw().await.map_err(pg_error)?;
167        if let Err(err) = conn.begin().await.map_err(pg_error) {
168            let _ = conn.rollback_and_release().await;
169            return Err(err);
170        }
171        Ok(conn)
172    }
173
174    /// Execute the schema commands through the AST driver path.
175    pub async fn install_schema(&self) -> Result<(), WorkflowError> {
176        for cmd in self.schema_commands() {
177            self.execute_command(&cmd).await?;
178        }
179        Ok(())
180    }
181
182    pub(crate) async fn save_state(&self, ctx: &WorkflowContext) -> Result<(), WorkflowError> {
183        let context_json = serde_json::to_string(ctx).map_err(json_error)?;
184        let wait = ctx.cursor.as_ref().and_then(|cursor| cursor.wait.as_ref());
185        let wait_event = wait.map(|wait| wait.event.clone());
186        let wait_deadline = wait.map(|wait| timestamp(wait.deadline_at));
187        let now = timestamp(Utc::now());
188
189        let cmd = Qail::add(&self.tables.states)
190            .columns([
191                "workflow_id",
192                "definition_name",
193                "definition_version",
194                "current_state",
195                "context",
196                "wait_event",
197                "wait_deadline_at",
198                "timeout_claimed_until",
199                "created_at",
200                "updated_at",
201            ])
202            .values([
203                QailValue::String(ctx.workflow_id.clone()),
204                option_string(ctx.definition_name.clone()),
205                option_string(ctx.definition_version.clone()),
206                QailValue::String(ctx.current_state.clone()),
207                QailValue::Json(context_json),
208                option_string(wait_event),
209                option_string(wait_deadline),
210                QailValue::Null,
211                QailValue::String(timestamp(ctx.created_at)),
212                QailValue::String(now),
213            ])
214            .on_conflict_update(
215                &["workflow_id"],
216                &[
217                    ("definition_name", excluded("definition_name")),
218                    ("definition_version", excluded("definition_version")),
219                    ("current_state", excluded("current_state")),
220                    ("context", excluded("context")),
221                    ("wait_event", excluded("wait_event")),
222                    ("wait_deadline_at", excluded("wait_deadline_at")),
223                    ("timeout_claimed_until", excluded("timeout_claimed_until")),
224                    ("updated_at", excluded("updated_at")),
225                ],
226            );
227
228        self.execute_command(&cmd).await?;
229        Ok(())
230    }
231
232    pub(crate) async fn load_state(
233        &self,
234        workflow_id: &str,
235    ) -> Result<Option<WorkflowContext>, WorkflowError> {
236        let cmd = Qail::get(&self.tables.states)
237            .columns(["context"])
238            .eq("workflow_id", workflow_id)
239            .limit(1);
240
241        let rows = self.fetch_all_command(&cmd).await?;
242        let Some(row) = rows.first() else {
243            return Ok(None);
244        };
245        let context_json = required_string(row, 0, "context")?;
246        let ctx = serde_json::from_str(&context_json).map_err(json_error)?;
247        Ok(Some(ctx))
248    }
249
250    pub(crate) async fn acquire_workflow_lease(
251        &self,
252        lease: &WorkflowLease,
253    ) -> Result<bool, WorkflowError> {
254        match &self.backend {
255            PgWorkflowBackend::Driver(driver) => {
256                let mut driver = driver.lock().await;
257                driver.begin().await.map_err(pg_error)?;
258
259                let result = self.acquire_workflow_lease_tx(&mut *driver, lease).await;
260                finish_tx(&mut driver, result).await
261            }
262            PgWorkflowBackend::Pool(pool) => {
263                let mut conn = Self::begin_pool_transaction(pool).await?;
264                let result = self.acquire_workflow_lease_tx(&mut conn, lease).await;
265                finish_pool_tx(conn, result).await
266            }
267        }
268    }
269
270    async fn acquire_workflow_lease_tx<C>(
271        &self,
272        conn: &mut C,
273        lease: &WorkflowLease,
274    ) -> Result<bool, WorkflowError>
275    where
276        C: PgWorkflowConnection,
277    {
278        let now = timestamp(Utc::now());
279        let expires_at = timestamp(deadline_from_duration(lease.ttl)?);
280        let existing = Qail::get(&self.tables.leases)
281            .columns(["owner", "expires_at"])
282            .eq("workflow_id", lease.workflow_id.as_str())
283            .for_update()
284            .limit(1);
285
286        let rows = conn.fetch_all_ast(&existing).await.map_err(pg_error)?;
287        if let Some(row) = rows.first() {
288            let expires = required_string(row, 1, "expires_at")?;
289            if expires > now {
290                return Ok(false);
291            }
292
293            let update = Qail::set(&self.tables.leases)
294                .set_value("owner", lease.owner.clone())
295                .set_value("expires_at", expires_at)
296                .set_value("updated_at", now)
297                .eq("workflow_id", lease.workflow_id.as_str());
298            conn.execute_ast(&update).await.map_err(pg_error)?;
299            return Ok(true);
300        }
301
302        let insert = Qail::add(&self.tables.leases)
303            .columns(["workflow_id", "owner", "expires_at", "updated_at"])
304            .values([
305                QailValue::String(lease.workflow_id.clone()),
306                QailValue::String(lease.owner.clone()),
307                QailValue::String(expires_at),
308                QailValue::String(now),
309            ]);
310
311        match conn.execute_ast(&insert).await {
312            Ok(_) => Ok(true),
313            Err(err) if is_duplicate(&err) => Ok(false),
314            Err(err) => Err(pg_error(err)),
315        }
316    }
317
318    pub(crate) async fn release_workflow_lease(
319        &self,
320        lease: &WorkflowLease,
321    ) -> Result<(), WorkflowError> {
322        let cmd = Qail::del(&self.tables.leases)
323            .eq("workflow_id", lease.workflow_id.as_str())
324            .eq("owner", lease.owner.as_str());
325        self.execute_command(&cmd).await?;
326        Ok(())
327    }
328
329    pub(crate) async fn begin_workflow_operation(
330        &self,
331        operation: &WorkflowOperation,
332    ) -> Result<WorkflowOperationStatus, WorkflowError> {
333        self.ensure_in_progress_ttl()?;
334        match &self.backend {
335            PgWorkflowBackend::Driver(driver) => {
336                let mut driver = driver.lock().await;
337                driver.begin().await.map_err(pg_error)?;
338
339                let result = self
340                    .begin_workflow_operation_tx(&mut *driver, operation)
341                    .await;
342                finish_tx(&mut driver, result).await
343            }
344            PgWorkflowBackend::Pool(pool) => {
345                let mut conn = Self::begin_pool_transaction(pool).await?;
346                let result = self.begin_workflow_operation_tx(&mut conn, operation).await;
347                finish_pool_tx(conn, result).await
348            }
349        }
350    }
351
352    async fn begin_workflow_operation_tx<C>(
353        &self,
354        conn: &mut C,
355        operation: &WorkflowOperation,
356    ) -> Result<WorkflowOperationStatus, WorkflowError>
357    where
358        C: PgWorkflowConnection,
359    {
360        let operation_kind = operation_kind_text(&operation.kind);
361        let now = Utc::now();
362        let existing = Qail::get(&self.tables.operations)
363            .columns(["status", "state", "kind", "workflow_name", "updated_at"])
364            .eq("workflow_id", operation.workflow_id.as_str())
365            .eq("idempotency_key", operation.idempotency_key.as_str())
366            .for_update()
367            .limit(1);
368        let rows = conn.fetch_all_ast(&existing).await.map_err(pg_error)?;
369
370        if let Some(row) = rows.first() {
371            let status = required_string(row, 0, "status")?;
372            let stored_kind = required_string(row, 2, "kind")?;
373            if stored_kind != operation_kind {
374                return Err(WorkflowError::Other(format!(
375                    "Workflow operation idempotency key '{}' was previously used for kind {}, not {}",
376                    operation.idempotency_key, stored_kind, operation_kind
377                )));
378            }
379            let stored_workflow_name = required_string(row, 3, "workflow_name")?;
380            if stored_workflow_name != operation.workflow_name {
381                return Err(WorkflowError::Other(format!(
382                    "Workflow operation idempotency key '{}' was previously used for workflow '{}', not '{}'",
383                    operation.idempotency_key, stored_workflow_name, operation.workflow_name
384                )));
385            }
386
387            return match status.as_str() {
388                STATUS_STARTED => {
389                    let updated_at = required_string(row, 4, "updated_at")?;
390                    if is_stale_timestamp(
391                        &updated_at,
392                        now,
393                        self.in_progress_ttl,
394                        "workflow operation updated_at",
395                    )? {
396                        self.mark_started_workflow_operation_started_tx(conn, operation)
397                            .await?;
398                        Ok(WorkflowOperationStatus::Started)
399                    } else {
400                        Ok(WorkflowOperationStatus::InProgress)
401                    }
402                }
403                STATUS_COMPLETED => {
404                    let state = required_string(row, 1, "state")?;
405                    Ok(WorkflowOperationStatus::Completed { state })
406                }
407                STATUS_FAILED => {
408                    self.mark_workflow_operation_started_tx(conn, operation)
409                        .await?;
410                    Ok(WorkflowOperationStatus::Started)
411                }
412                other => Err(WorkflowError::Other(format!(
413                    "Unknown workflow operation status '{other}'"
414                ))),
415            };
416        }
417
418        let now = timestamp(Utc::now());
419        let insert = Qail::add(&self.tables.operations)
420            .columns([
421                "workflow_name",
422                "workflow_id",
423                "idempotency_key",
424                "kind",
425                "status",
426                "state",
427                "error",
428                "created_at",
429                "updated_at",
430            ])
431            .values([
432                QailValue::String(operation.workflow_name.clone()),
433                QailValue::String(operation.workflow_id.clone()),
434                QailValue::String(operation.idempotency_key.clone()),
435                QailValue::String(operation_kind),
436                QailValue::String(STATUS_STARTED.to_string()),
437                QailValue::Null,
438                QailValue::Null,
439                QailValue::String(now.clone()),
440                QailValue::String(now),
441            ]);
442
443        match conn.execute_ast(&insert).await {
444            Ok(_) => Ok(WorkflowOperationStatus::Started),
445            Err(err) if is_duplicate(&err) => Ok(WorkflowOperationStatus::InProgress),
446            Err(err) => Err(pg_error(err)),
447        }
448    }
449
450    fn ensure_in_progress_ttl(&self) -> Result<(), WorkflowError> {
451        if self.in_progress_ttl.is_zero() {
452            return Err(WorkflowError::Other(
453                "Workflow in-progress TTL must be greater than zero".to_string(),
454            ));
455        }
456        Ok(())
457    }
458
459    async fn mark_started_workflow_operation_started_tx<C>(
460        &self,
461        conn: &mut C,
462        operation: &WorkflowOperation,
463    ) -> Result<(), WorkflowError>
464    where
465        C: PgWorkflowConnection,
466    {
467        self.refresh_workflow_operation_started_tx(conn, operation, STATUS_STARTED)
468            .await
469    }
470
471    async fn mark_workflow_operation_started_tx<C>(
472        &self,
473        conn: &mut C,
474        operation: &WorkflowOperation,
475    ) -> Result<(), WorkflowError>
476    where
477        C: PgWorkflowConnection,
478    {
479        self.refresh_workflow_operation_started_tx(conn, operation, STATUS_FAILED)
480            .await
481    }
482
483    async fn refresh_workflow_operation_started_tx<C>(
484        &self,
485        conn: &mut C,
486        operation: &WorkflowOperation,
487        expected_status: &str,
488    ) -> Result<(), WorkflowError>
489    where
490        C: PgWorkflowConnection,
491    {
492        let operation_kind = operation_kind_text(&operation.kind);
493        let cmd = Qail::set(&self.tables.operations)
494            .set_value("status", STATUS_STARTED)
495            .set_value("state", QailValue::Null)
496            .set_value("error", QailValue::Null)
497            .set_value("updated_at", timestamp(Utc::now()))
498            .eq("workflow_name", operation.workflow_name.as_str())
499            .eq("workflow_id", operation.workflow_id.as_str())
500            .eq("idempotency_key", operation.idempotency_key.as_str())
501            .eq("kind", operation_kind.as_str())
502            .eq("status", expected_status);
503        let affected = conn.execute_ast(&cmd).await.map_err(pg_error)?;
504        if affected == 0 {
505            return Err(WorkflowError::Other(format!(
506                "Workflow operation '{}' was not found in {expected_status} state",
507                operation.idempotency_key,
508            )));
509        }
510        Ok(())
511    }
512
513    pub(crate) async fn complete_workflow_operation(
514        &self,
515        operation: &WorkflowOperation,
516        state: &str,
517    ) -> Result<(), WorkflowError> {
518        let operation_kind = operation_kind_text(&operation.kind);
519        let cmd = Qail::set(&self.tables.operations)
520            .set_value("status", STATUS_COMPLETED)
521            .set_value("state", state)
522            .set_value("error", QailValue::Null)
523            .set_value("updated_at", timestamp(Utc::now()))
524            .eq("workflow_name", operation.workflow_name.as_str())
525            .eq("workflow_id", operation.workflow_id.as_str())
526            .eq("idempotency_key", operation.idempotency_key.as_str())
527            .eq("kind", operation_kind.as_str())
528            .eq("status", STATUS_STARTED);
529        let affected = self.execute_command(&cmd).await?;
530        if affected == 0 {
531            return Err(missing_started_row(
532                "operation",
533                operation.idempotency_key.as_str(),
534            ));
535        }
536        Ok(())
537    }
538
539    pub(crate) async fn fail_workflow_operation(
540        &self,
541        operation: &WorkflowOperation,
542        error: &str,
543    ) -> Result<(), WorkflowError> {
544        let operation_kind = operation_kind_text(&operation.kind);
545        let cmd = Qail::set(&self.tables.operations)
546            .set_value("status", STATUS_FAILED)
547            .set_value("error", error)
548            .set_value("updated_at", timestamp(Utc::now()))
549            .eq("workflow_name", operation.workflow_name.as_str())
550            .eq("workflow_id", operation.workflow_id.as_str())
551            .eq("idempotency_key", operation.idempotency_key.as_str())
552            .eq("kind", operation_kind.as_str())
553            .eq("status", STATUS_STARTED);
554        let affected = self.execute_command(&cmd).await?;
555        if affected == 0 {
556            return Err(missing_started_row(
557                "operation",
558                operation.idempotency_key.as_str(),
559            ));
560        }
561        Ok(())
562    }
563
564    pub(crate) async fn load_due_workflow_timeouts(
565        &self,
566        workflow_name: &str,
567        now: DateTime<Utc>,
568        limit: usize,
569    ) -> Result<Vec<String>, WorkflowError> {
570        if limit == 0 {
571            return Ok(Vec::new());
572        }
573        if self.timeout_claim_ttl.is_zero() {
574            return Err(WorkflowError::Other(
575                "Workflow timeout claim TTL must be greater than zero".to_string(),
576            ));
577        }
578
579        match &self.backend {
580            PgWorkflowBackend::Driver(driver) => {
581                let mut driver = driver.lock().await;
582                driver.begin().await.map_err(pg_error)?;
583
584                let result = self
585                    .load_due_workflow_timeouts_tx(&mut *driver, workflow_name, now, limit)
586                    .await;
587                finish_tx(&mut driver, result).await
588            }
589            PgWorkflowBackend::Pool(pool) => {
590                let mut conn = Self::begin_pool_transaction(pool).await?;
591                let result = self
592                    .load_due_workflow_timeouts_tx(&mut conn, workflow_name, now, limit)
593                    .await;
594                finish_pool_tx(conn, result).await
595            }
596        }
597    }
598
599    async fn load_due_workflow_timeouts_tx<C>(
600        &self,
601        conn: &mut C,
602        workflow_name: &str,
603        now: DateTime<Utc>,
604        limit: usize,
605    ) -> Result<Vec<String>, WorkflowError>
606    where
607        C: PgWorkflowConnection,
608    {
609        let claim_until = timestamp(deadline_after(now, self.timeout_claim_ttl)?);
610        let now = timestamp(now);
611        let limit = i64::try_from(limit).unwrap_or(i64::MAX);
612        let due = timeout_due_query(&self.tables, workflow_name, now.as_str(), limit);
613
614        let rows = conn.fetch_all_ast(&due).await.map_err(pg_error)?;
615        let mut ids = Vec::with_capacity(rows.len());
616        for row in rows {
617            let workflow_id = required_string(&row, 0, "workflow_id")?;
618            let claim = Qail::set(&self.tables.states)
619                .set_value("timeout_claimed_until", claim_until.clone())
620                .set_value("updated_at", timestamp(Utc::now()))
621                .eq("workflow_id", workflow_id.as_str());
622            conn.execute_ast(&claim).await.map_err(pg_error)?;
623            ids.push(workflow_id);
624        }
625        Ok(ids)
626    }
627
628    pub(crate) async fn begin_workflow_side_effect(
629        &self,
630        operation: &WorkflowSideEffect,
631    ) -> Result<WorkflowSideEffectStatus, WorkflowError> {
632        self.ensure_in_progress_ttl()?;
633        match &self.backend {
634            PgWorkflowBackend::Driver(driver) => {
635                let mut driver = driver.lock().await;
636                driver.begin().await.map_err(pg_error)?;
637
638                let result = self
639                    .begin_workflow_side_effect_tx(&mut *driver, operation)
640                    .await;
641                finish_tx(&mut driver, result).await
642            }
643            PgWorkflowBackend::Pool(pool) => {
644                let mut conn = Self::begin_pool_transaction(pool).await?;
645                let result = self
646                    .begin_workflow_side_effect_tx(&mut conn, operation)
647                    .await;
648                finish_pool_tx(conn, result).await
649            }
650        }
651    }
652
653    async fn begin_workflow_side_effect_tx<C>(
654        &self,
655        conn: &mut C,
656        operation: &WorkflowSideEffect,
657    ) -> Result<WorkflowSideEffectStatus, WorkflowError>
658    where
659        C: PgWorkflowConnection,
660    {
661        let existing = Qail::get(&self.tables.side_effects)
662            .columns([
663                "status",
664                "result",
665                "workflow_id",
666                "state",
667                "step_path",
668                "kind",
669                "updated_at",
670            ])
671            .eq("operation_id", operation.operation_id.as_str())
672            .for_update()
673            .limit(1);
674        let rows = conn.fetch_all_ast(&existing).await.map_err(pg_error)?;
675
676        if let Some(row) = rows.first() {
677            let status = required_string(row, 0, "status")?;
678            self.ensure_side_effect_identity(row, operation)?;
679            return match status.as_str() {
680                STATUS_STARTED => {
681                    let updated_at = required_string(row, 6, "updated_at")?;
682                    if is_stale_timestamp(
683                        &updated_at,
684                        Utc::now(),
685                        self.in_progress_ttl,
686                        "workflow side effect updated_at",
687                    )? {
688                        self.mark_started_workflow_side_effect_started_tx(conn, operation)
689                            .await?;
690                        Ok(WorkflowSideEffectStatus::Execute)
691                    } else {
692                        Err(WorkflowError::Other(format!(
693                            "Workflow side effect '{}' is already in progress",
694                            operation.operation_id
695                        )))
696                    }
697                }
698                STATUS_COMPLETED => Ok(WorkflowSideEffectStatus::AlreadyCompleted {
699                    result: optional_json(row, 1)?,
700                }),
701                STATUS_FAILED => {
702                    self.mark_workflow_side_effect_started_tx(conn, operation)
703                        .await?;
704                    Ok(WorkflowSideEffectStatus::Execute)
705                }
706                other => Err(WorkflowError::Other(format!(
707                    "Unknown workflow side effect status '{other}'"
708                ))),
709            };
710        }
711
712        let now = timestamp(Utc::now());
713        let insert = Qail::add(&self.tables.side_effects)
714            .columns([
715                "operation_id",
716                "workflow_id",
717                "state",
718                "step_path",
719                "kind",
720                "status",
721                "result",
722                "error",
723                "created_at",
724                "updated_at",
725            ])
726            .values([
727                QailValue::String(operation.operation_id.clone()),
728                QailValue::String(operation.workflow_id.clone()),
729                QailValue::String(operation.state.clone()),
730                QailValue::String(operation.step_path.clone()),
731                QailValue::String(operation.kind.as_str().to_string()),
732                QailValue::String(STATUS_STARTED.to_string()),
733                QailValue::Null,
734                QailValue::Null,
735                QailValue::String(now.clone()),
736                QailValue::String(now),
737            ]);
738
739        match conn.execute_ast(&insert).await {
740            Ok(_) => Ok(WorkflowSideEffectStatus::Execute),
741            Err(err) if is_duplicate(&err) => Err(WorkflowError::Other(format!(
742                "Workflow side effect '{}' is already in progress",
743                operation.operation_id
744            ))),
745            Err(err) => Err(pg_error(err)),
746        }
747    }
748
749    fn ensure_side_effect_identity(
750        &self,
751        row: &qail_pg::PgRow,
752        operation: &WorkflowSideEffect,
753    ) -> Result<(), WorkflowError> {
754        let stored_workflow_id = required_string(row, 2, "workflow_id")?;
755        let stored_state = required_string(row, 3, "state")?;
756        let stored_step_path = required_string(row, 4, "step_path")?;
757        let stored_kind = required_string(row, 5, "kind")?;
758        let expected_kind = operation.kind.as_str();
759        if stored_workflow_id != operation.workflow_id
760            || stored_state != operation.state
761            || stored_step_path != operation.step_path
762            || stored_kind != expected_kind
763        {
764            return Err(WorkflowError::Other(format!(
765                "Workflow side effect operation id '{}' was previously used for {}/{}/{}/{}, not {}/{}/{}/{}",
766                operation.operation_id,
767                stored_workflow_id,
768                stored_state,
769                stored_step_path,
770                stored_kind,
771                operation.workflow_id,
772                operation.state,
773                operation.step_path,
774                expected_kind
775            )));
776        }
777        Ok(())
778    }
779
780    async fn mark_workflow_side_effect_started_tx<C>(
781        &self,
782        conn: &mut C,
783        operation: &WorkflowSideEffect,
784    ) -> Result<(), WorkflowError>
785    where
786        C: PgWorkflowConnection,
787    {
788        self.refresh_workflow_side_effect_started_tx(conn, operation, STATUS_FAILED)
789            .await
790    }
791
792    async fn mark_started_workflow_side_effect_started_tx<C>(
793        &self,
794        conn: &mut C,
795        operation: &WorkflowSideEffect,
796    ) -> Result<(), WorkflowError>
797    where
798        C: PgWorkflowConnection,
799    {
800        self.refresh_workflow_side_effect_started_tx(conn, operation, STATUS_STARTED)
801            .await
802    }
803
804    async fn refresh_workflow_side_effect_started_tx<C>(
805        &self,
806        conn: &mut C,
807        operation: &WorkflowSideEffect,
808        expected_status: &str,
809    ) -> Result<(), WorkflowError>
810    where
811        C: PgWorkflowConnection,
812    {
813        let cmd = Qail::set(&self.tables.side_effects)
814            .set_value("status", STATUS_STARTED)
815            .set_value("result", QailValue::Null)
816            .set_value("error", QailValue::Null)
817            .set_value("updated_at", timestamp(Utc::now()))
818            .eq("operation_id", operation.operation_id.as_str())
819            .eq("workflow_id", operation.workflow_id.as_str())
820            .eq("state", operation.state.as_str())
821            .eq("step_path", operation.step_path.as_str())
822            .eq("kind", operation.kind.as_str())
823            .eq("status", expected_status);
824        let affected = conn.execute_ast(&cmd).await.map_err(pg_error)?;
825        if affected == 0 {
826            return Err(WorkflowError::Other(format!(
827                "Workflow side effect '{}' was not found in {expected_status} state",
828                operation.operation_id,
829            )));
830        }
831        Ok(())
832    }
833
834    pub(crate) async fn complete_workflow_side_effect(
835        &self,
836        operation: &WorkflowSideEffect,
837        result: Option<&serde_json::Value>,
838    ) -> Result<(), WorkflowError> {
839        let result = match result {
840            Some(value) => QailValue::Json(serde_json::to_string(value).map_err(json_error)?),
841            None => QailValue::Null,
842        };
843        let cmd = Qail::set(&self.tables.side_effects)
844            .set_value("status", STATUS_COMPLETED)
845            .set_value("result", result)
846            .set_value("error", QailValue::Null)
847            .set_value("updated_at", timestamp(Utc::now()))
848            .eq("operation_id", operation.operation_id.as_str())
849            .eq("workflow_id", operation.workflow_id.as_str())
850            .eq("state", operation.state.as_str())
851            .eq("step_path", operation.step_path.as_str())
852            .eq("kind", operation.kind.as_str())
853            .eq("status", STATUS_STARTED);
854        let affected = self.execute_command(&cmd).await?;
855        if affected == 0 {
856            return Err(missing_started_row(
857                "side effect",
858                operation.operation_id.as_str(),
859            ));
860        }
861        Ok(())
862    }
863
864    pub(crate) async fn fail_workflow_side_effect(
865        &self,
866        operation: &WorkflowSideEffect,
867        error: &str,
868    ) -> Result<(), WorkflowError> {
869        let cmd = Qail::set(&self.tables.side_effects)
870            .set_value("status", STATUS_FAILED)
871            .set_value("error", error)
872            .set_value("updated_at", timestamp(Utc::now()))
873            .eq("operation_id", operation.operation_id.as_str())
874            .eq("workflow_id", operation.workflow_id.as_str())
875            .eq("state", operation.state.as_str())
876            .eq("step_path", operation.step_path.as_str())
877            .eq("kind", operation.kind.as_str())
878            .eq("status", STATUS_STARTED);
879        let affected = self.execute_command(&cmd).await?;
880        if affected == 0 {
881            return Err(missing_started_row(
882                "side effect",
883                operation.operation_id.as_str(),
884            ));
885        }
886        Ok(())
887    }
888}
889
890fn timeout_due_query(
891    tables: &PgWorkflowTables,
892    workflow_name: &str,
893    now: &str,
894    limit: i64,
895) -> Qail {
896    Qail::get(&tables.states)
897        .columns(["workflow_id"])
898        .eq("definition_name", workflow_name)
899        .lte("wait_deadline_at", now)
900        .is_not_null("wait_deadline_at")
901        .or_filter("timeout_claimed_until", Operator::IsNull, QailValue::Null)
902        .or_filter("timeout_claimed_until", Operator::Lt, now)
903        .for_update_skip_locked()
904        .limit(limit)
905}
906
907#[cfg(test)]
908mod tests {
909    use super::*;
910
911    #[test]
912    fn due_timeout_query_uses_row_locking() {
913        let tables = PgWorkflowTables::default();
914        let cmd = timeout_due_query(&tables, "booking", "2026-01-01T00:00:00.000000Z", 10);
915
916        assert!(cmd.lock_mode.is_some());
917        assert!(cmd.skip_locked);
918    }
919}