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