Skip to main content

agent_effects_sqlite/
lib.rs

1//! SQLite store for [`agent-effects`](https://crates.io/crates/agent-effects).
2//!
3//! For CLI agents, desktop apps and single-node services. Several processes
4//! may share one database file.
5//!
6//! ```no_run
7//! # async fn demo() -> Result<(), agent_effects_store::StoreError> {
8//! let store = agent_effects_sqlite::SqliteStore::open("effects.db").await?;
9//! # Ok(())
10//! # }
11//! ```
12//!
13//! # Durability and concurrency
14//!
15//! [`SqliteStore::open`] configures:
16//!
17//! - **WAL** journal, so readers do not block the writer.
18//! - **`synchronous = FULL`**: a commit is on disk before it returns. The
19//!   runtime persists an attempt *before* calling the remote system. Losing
20//!   that write in a power cut would leave a `Pending` record for an effect
21//!   that may have run, and the next call would run it again. `NORMAL` would
22//!   allow exactly that.
23//! - **`busy_timeout`** of 5 seconds, so concurrent writers wait instead of
24//!   failing.
25//!
26//! Every change runs as "load, apply the pure `EffectRecord` operation, save"
27//! inside a `BEGIN IMMEDIATE` transaction. That takes the write lock up
28//! front, so two writers, even in different processes, cannot both read a
29//! record and then both write it. The record update also checks the version
30//! it read, as a second guard.
31//!
32//! SQLite admits one writer at a time, and its busy handler is not fair:
33//! under contention a waiting writer can lose the race for the lock again
34//! and again until its busy timeout runs out. So writers in one process
35//! (every clone of one store) queue for their turn, first come, first
36//! served, before asking SQLite for the lock; only writers in other
37//! processes meet the busy timeout. Every commit is an `fsync`, so write
38//! throughput is bounded by the disk: tens of commits per second on some
39//! encrypted or copy-on-write filesystems, thousands on a fast SSD.
40//!
41//! Times are stored as Unix milliseconds; sub-millisecond precision is
42//! dropped, as the [`EffectStore`] contract allows.
43
44use std::ops::{Deref, DerefMut};
45use std::path::Path;
46use std::str::FromStr;
47use std::sync::Arc;
48use std::time::{Duration, SystemTime, UNIX_EPOCH};
49
50use agent_effects_store::{
51    EffectEvent, EffectId, EffectKey, EffectKind, EffectName, EffectRecord, EffectStatus,
52    EffectStore, ErrorRecord, InsertOutcome, Lease, ListQuery, LogicalKey, NewEffect, PruneQuery,
53    StoreError, Transition, TransitionRequest, WorkerId,
54};
55use serde::Serialize;
56use serde::de::DeserializeOwned;
57use sqlx::sqlite::{
58    SqliteConnectOptions, SqliteJournalMode, SqlitePool, SqlitePoolOptions, SqliteRow,
59    SqliteSynchronous,
60};
61use sqlx::{AssertSqlSafe, Row, Sqlite, SqliteConnection, Transaction};
62use tokio::sync::{Mutex, OwnedMutexGuard};
63use uuid::Uuid;
64
65static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");
66
67const COLUMNS: &str = "id, effect_name, logical_key, kind, status, input, input_fingerprint, \
68     output, last_error, created_by, attempt_count, may_have_applied, \
69     compensation_attempts, approved, next_attempt_at, \
70     attempt_started_at, attempt_ended_at, \
71     lease_owner, lease_epoch, lease_expires_at, version, created_at, updated_at, committed_at, execution_contract, input_schema_version, output_schema_version";
72
73/// An [`EffectStore`] in a SQLite database.
74///
75/// Cheap to clone; clones share the connection pool and take turns to
76/// write (see [the crate docs](crate#durability-and-concurrency)).
77#[derive(Clone, Debug)]
78pub struct SqliteStore {
79    pool: SqlitePool,
80    /// This process's turn to write: held for every write, in order.
81    writes: Arc<Mutex<()>>,
82}
83
84/// A write transaction, with this process's turn to write.
85struct Write {
86    tx: Transaction<'static, Sqlite>,
87    _turn: OwnedMutexGuard<()>,
88}
89
90impl Write {
91    async fn commit(self) -> Result<(), StoreError> {
92        self.tx.commit().await.map_err(StoreError::backend)
93    }
94
95    async fn rollback(self) -> Result<(), StoreError> {
96        self.tx.rollback().await.map_err(StoreError::backend)
97    }
98}
99
100impl Deref for Write {
101    type Target = SqliteConnection;
102    fn deref(&self) -> &SqliteConnection {
103        &self.tx
104    }
105}
106
107impl DerefMut for Write {
108    fn deref_mut(&mut self) -> &mut SqliteConnection {
109        &mut self.tx
110    }
111}
112
113impl SqliteStore {
114    /// Opens (creating if needed) the database at `path`, configures it for
115    /// durability (see the [crate docs](crate)) and applies migrations.
116    ///
117    /// # Errors
118    ///
119    /// [`StoreError::Backend`] if the file cannot be opened or migrated.
120    pub async fn open(path: impl AsRef<Path>) -> Result<Self, StoreError> {
121        let options = SqliteConnectOptions::new()
122            .filename(path)
123            .create_if_missing(true)
124            .journal_mode(SqliteJournalMode::Wal)
125            .synchronous(SqliteSynchronous::Full)
126            .busy_timeout(Duration::from_secs(5))
127            .foreign_keys(true);
128        let pool = SqlitePoolOptions::new()
129            .max_connections(8)
130            .connect_with(options)
131            .await
132            .map_err(StoreError::backend)?;
133        Self::from_pool(pool).await
134    }
135
136    /// Uses an existing pool and applies migrations. The pool's connection
137    /// options are the caller's responsibility; see the
138    /// [crate docs](crate) for what [`Self::open`] sets and why.
139    ///
140    /// # Errors
141    ///
142    /// [`StoreError::Backend`] if migrating fails.
143    pub async fn from_pool(pool: SqlitePool) -> Result<Self, StoreError> {
144        MIGRATOR.run(&pool).await.map_err(StoreError::backend)?;
145        Ok(Self::with_pool(pool))
146    }
147
148    fn with_pool(pool: SqlitePool) -> Self {
149        Self {
150            pool,
151            writes: Arc::new(Mutex::new(())),
152        }
153    }
154
155    /// Uses an existing pool without migrating, after checking that every
156    /// migration is applied. For tools that must never change the schema,
157    /// such as an operator CLI, or a pool opened read-only
158    /// (`?mode=ro`), where reads work and every write fails.
159    ///
160    /// # Errors
161    ///
162    /// [`StoreError::Backend`] if the database has no migration history, or
163    /// lacks or differs from one of this crate's migrations.
164    pub async fn attach(pool: SqlitePool) -> Result<Self, StoreError> {
165        let applied: Vec<(i64, Vec<u8>)> =
166            sqlx::query_as("SELECT version, checksum FROM _sqlx_migrations WHERE success")
167                .fetch_all(&pool)
168                .await
169                .map_err(|e| {
170                    StoreError::backend(format!(
171                        "no agent-effects migration history ({e}): open the database with \
172                         SqliteStore::open first"
173                    ))
174                })?;
175        for migration in MIGRATOR.iter() {
176            if migration.migration_type.is_down_migration() {
177                continue;
178            }
179            match applied
180                .iter()
181                .find(|(version, _)| *version == migration.version)
182            {
183                None => {
184                    return Err(StoreError::backend(format!(
185                        "migration {} ({}) is not applied: open the database with \
186                         SqliteStore::open first",
187                        migration.version, migration.description
188                    )));
189                }
190                Some((_, checksum)) if checksum.as_slice() != &*migration.checksum => {
191                    return Err(StoreError::backend(format!(
192                        "migration {} differs from agent-effects' own",
193                        migration.version
194                    )));
195                }
196                Some(_) => {}
197            }
198        }
199        Ok(Self::with_pool(pool))
200    }
201
202    /// The underlying pool.
203    pub fn pool(&self) -> &SqlitePool {
204        &self.pool
205    }
206
207    /// Waits for this process's turn to write, then starts a write
208    /// transaction. The turn comes first, so no connection is held while
209    /// waiting for it.
210    async fn begin(&self) -> Result<Write, StoreError> {
211        let turn = Arc::clone(&self.writes).lock_owned().await;
212        let tx = self
213            .pool
214            .begin_with("BEGIN IMMEDIATE")
215            .await
216            .map_err(StoreError::backend)?;
217        Ok(Write { tx, _turn: turn })
218    }
219
220    /// Loads `id` under a write transaction, applies `change`, and saves the
221    /// record. Returns what `change` returned and the saved record, with the
222    /// transaction still open for further writes.
223    async fn modify<T>(
224        &self,
225        id: EffectId,
226        change: impl FnOnce(&mut EffectRecord) -> Result<T, StoreError>,
227    ) -> Result<(T, EffectRecord, Write), StoreError> {
228        let mut tx = self.begin().await?;
229        let row = sqlx::query(AssertSqlSafe(format!(
230            "SELECT {COLUMNS} FROM effects WHERE id = ?"
231        )))
232        .bind(id.to_string())
233        .fetch_optional(&mut *tx)
234        .await
235        .map_err(StoreError::backend)?;
236        let mut record = decode_record(&row.ok_or(StoreError::NotFound(id))?)?;
237        let read_version = record.version;
238        let result = change(&mut record)?;
239        normalize(&mut record);
240        save(&mut tx, &record, read_version).await?;
241        Ok((result, record, tx))
242    }
243}
244
245impl EffectStore for SqliteStore {
246    async fn append_late_observation(
247        &self,
248        observation: agent_effects_store::LateObservation,
249    ) -> Result<(), StoreError> {
250        let _turn = self.writes.lock().await;
251        sqlx::query("INSERT INTO late_observations (id, effect_id, observation) VALUES (?, ?, ?) ON CONFLICT (id) DO NOTHING")
252            .bind(observation.id.to_string()).bind(observation.effect_id.to_string())
253            .bind(serde_json::to_string(&observation).map_err(StoreError::backend)?)
254            .execute(&self.pool).await.map_err(StoreError::backend)?;
255        Ok(())
256    }
257
258    async fn late_observations(
259        &self,
260        id: EffectId,
261        after: Option<EffectId>,
262        limit: usize,
263    ) -> Result<Vec<agent_effects_store::LateObservation>, StoreError> {
264        if self.get(id).await?.is_none() {
265            return Err(StoreError::NotFound(id));
266        }
267        let rows = sqlx::query("SELECT observation FROM late_observations WHERE effect_id = ? AND (? IS NULL OR id > ?) ORDER BY id LIMIT ?")
268            .bind(id.to_string()).bind(after.map(|id| id.to_string()))
269            .bind(after.map(|id| id.to_string()))
270            .bind(i64::try_from(limit).unwrap_or(i64::MAX))
271            .fetch_all(&self.pool).await.map_err(StoreError::backend)?;
272        rows.iter()
273            .map(|row| {
274                let json: String = get(row, "observation")?;
275                serde_json::from_str(&json).map_err(|_| corrupt("late observation JSON", ""))
276            })
277            .collect()
278    }
279
280    async fn insert_or_get(&self, new: NewEffect) -> Result<InsertOutcome, StoreError> {
281        let mut record = EffectRecord::new(new);
282        normalize(&mut record);
283        let turn = self.writes.lock().await;
284        let inserted = bind_record(
285            sqlx::query(AssertSqlSafe(format!(
286                "INSERT INTO effects ({COLUMNS}) \
287                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \
288                 ON CONFLICT (effect_name, logical_key) DO NOTHING"
289            ))),
290            &record,
291        )?
292        .execute(&self.pool)
293        .await
294        .map_err(StoreError::backend)?
295        .rows_affected()
296            == 1;
297        drop(turn);
298        if inserted {
299            return Ok(InsertOutcome {
300                record,
301                inserted: true,
302            });
303        }
304        let existing = self
305            .get_by_key(&record.key)
306            .await?
307            .ok_or_else(|| StoreError::backend("conflicting insert, but no existing record"))?;
308        Ok(InsertOutcome {
309            record: existing,
310            inserted: false,
311        })
312    }
313
314    async fn get(&self, id: EffectId) -> Result<Option<EffectRecord>, StoreError> {
315        sqlx::query(AssertSqlSafe(format!(
316            "SELECT {COLUMNS} FROM effects WHERE id = ?"
317        )))
318        .bind(id.to_string())
319        .fetch_optional(&self.pool)
320        .await
321        .map_err(StoreError::backend)?
322        .as_ref()
323        .map(decode_record)
324        .transpose()
325    }
326
327    async fn get_by_key(&self, key: &EffectKey) -> Result<Option<EffectRecord>, StoreError> {
328        sqlx::query(AssertSqlSafe(format!(
329            "SELECT {COLUMNS} FROM effects WHERE effect_name = ? AND logical_key = ?"
330        )))
331        .bind(key.name.as_str())
332        .bind(key.key.as_str())
333        .fetch_optional(&self.pool)
334        .await
335        .map_err(StoreError::backend)?
336        .as_ref()
337        .map(decode_record)
338        .transpose()
339    }
340
341    async fn acquire_lease(
342        &self,
343        id: EffectId,
344        owner: &WorkerId,
345        now: SystemTime,
346        ttl: Duration,
347    ) -> Result<Lease, StoreError> {
348        let (mut lease, _, tx) = self
349            .modify(id, |record| record.acquire_lease(owner, now, ttl))
350            .await?;
351        tx.commit().await?;
352        lease.expires_at = round_trip(lease.expires_at);
353        Ok(lease)
354    }
355
356    async fn renew_lease(
357        &self,
358        lease: &Lease,
359        now: SystemTime,
360        ttl: Duration,
361    ) -> Result<Lease, StoreError> {
362        let (mut renewed, _, tx) = self
363            .modify(lease.effect_id, |record| {
364                record.renew_lease(lease, now, ttl)
365            })
366            .await?;
367        tx.commit().await?;
368        renewed.expires_at = round_trip(renewed.expires_at);
369        Ok(renewed)
370    }
371
372    async fn release_lease(&self, lease: &Lease) -> Result<(), StoreError> {
373        let (released, _, tx) = self
374            .modify(lease.effect_id, |record| Ok(record.release_lease(lease)))
375            .await?;
376        if released {
377            tx.commit().await?;
378        } else {
379            tx.rollback().await?;
380        }
381        Ok(())
382    }
383
384    async fn transition(&self, request: TransitionRequest) -> Result<EffectRecord, StoreError> {
385        let id = request.id;
386        let (mut event, record, mut tx) = self.modify(id, |record| record.apply(request)).await?;
387        event.at = round_trip(event.at);
388        sqlx::query(
389            "INSERT INTO effect_events \
390             (effect_id, sequence, transition, from_status, to_status, attempt, actor, payload, at) \
391             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
392        )
393        .bind(event.effect_id.to_string())
394        .bind(to_i64(event.sequence)?)
395        .bind(event.transition.as_str())
396        .bind(event.from.as_str())
397        .bind(event.to.as_str())
398        .bind(i64::from(event.attempt))
399        .bind(event.actor.as_deref())
400        .bind(json(event.payload.as_ref())?)
401        .bind(to_ms(event.at))
402        .execute(&mut *tx)
403        .await
404        .map_err(StoreError::backend)?;
405        tx.commit().await?;
406        Ok(record)
407    }
408
409    async fn list(&self, query: ListQuery) -> Result<Vec<EffectRecord>, StoreError> {
410        let mut sql = format!("SELECT {COLUMNS} FROM effects WHERE 1 = 1");
411        if !query.statuses.is_empty() {
412            let marks = vec!["?"; query.statuses.len()].join(", ");
413            sql.push_str(" AND status IN (");
414            sql.push_str(&marks);
415            sql.push(')');
416        }
417        if query.lease_expired_at.is_some() {
418            sql.push_str(
419                " AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?)",
420            );
421        }
422        if query.after.is_some() {
423            sql.push_str(" AND id > ?");
424        }
425        sql.push_str(" ORDER BY id LIMIT ?");
426
427        // Only placeholders are interpolated; every value is bound.
428        let mut statement = sqlx::query(AssertSqlSafe(sql));
429        for status in &query.statuses {
430            statement = statement.bind(status.as_str());
431        }
432        if let Some(now) = query.lease_expired_at {
433            statement = statement.bind(to_ms(now));
434        }
435        if let Some(after) = query.after {
436            statement = statement.bind(after.to_string());
437        }
438        statement = statement.bind(i64::try_from(query.limit).unwrap_or(i64::MAX));
439        statement
440            .fetch_all(&self.pool)
441            .await
442            .map_err(StoreError::backend)?
443            .iter()
444            .map(decode_record)
445            .collect()
446    }
447
448    async fn events(&self, id: EffectId) -> Result<Vec<EffectEvent>, StoreError> {
449        if self.get(id).await?.is_none() {
450            return Err(StoreError::NotFound(id));
451        }
452        sqlx::query(
453            "SELECT effect_id, sequence, transition, from_status, to_status, attempt, actor, \
454             payload, at FROM effect_events WHERE effect_id = ? ORDER BY sequence",
455        )
456        .bind(id.to_string())
457        .fetch_all(&self.pool)
458        .await
459        .map_err(StoreError::backend)?
460        .iter()
461        .map(decode_event)
462        .collect()
463    }
464
465    async fn prune(&self, query: PruneQuery) -> Result<u64, StoreError> {
466        let Some(cutoff) = query.cutoff().filter(|_| query.status.is_settled()) else {
467            return Ok(0);
468        };
469        // Selected and deleted under one write lock: nothing can lease or
470        // change a selected record in between.
471        let mut tx = self.begin().await?;
472        let ids: Vec<String> = sqlx::query_scalar(
473            "SELECT id FROM effects WHERE status = ? AND updated_at <= ? \
474             AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?) \
475             ORDER BY id LIMIT ?",
476        )
477        .bind(query.status.as_str())
478        .bind(to_ms(cutoff))
479        .bind(to_ms(query.now))
480        .bind(i64::try_from(query.limit).unwrap_or(i64::MAX))
481        .fetch_all(&mut *tx)
482        .await
483        .map_err(StoreError::backend)?;
484        for chunk in ids.chunks(500) {
485            let marks = vec!["?"; chunk.len()].join(", ");
486            for (table, column) in [("effect_events", "effect_id"), ("effects", "id")] {
487                // Only placeholders are interpolated; every value is bound.
488                let mut statement = sqlx::query(AssertSqlSafe(format!(
489                    "DELETE FROM {table} WHERE {column} IN ({marks})"
490                )));
491                for id in chunk {
492                    statement = statement.bind(id.as_str());
493                }
494                statement
495                    .execute(&mut *tx)
496                    .await
497                    .map_err(StoreError::backend)?;
498            }
499        }
500        tx.commit().await?;
501        Ok(ids.len() as u64)
502    }
503}
504
505/// Writes `record` over the row last read at `read_version`.
506async fn save(
507    tx: &mut SqliteConnection,
508    record: &EffectRecord,
509    read_version: u64,
510) -> Result<(), StoreError> {
511    let updated = sqlx::query(
512        "UPDATE effects SET status = ?, output = ?, last_error = ?, attempt_count = ?, \
513         may_have_applied = ?, compensation_attempts = ?, approved = ?, \
514         next_attempt_at = ?, attempt_started_at = ?, attempt_ended_at = ?, \
515         lease_owner = ?, lease_epoch = ?, \
516         lease_expires_at = ?, version = ?, updated_at = ?, committed_at = ?, output_schema_version = ? \
517         WHERE id = ? AND version = ?",
518    )
519    .bind(record.status.as_str())
520    .bind(json(record.output.as_ref())?)
521    .bind(json(record.last_error.as_ref())?)
522    .bind(i64::from(record.attempt_count))
523    .bind(record.may_have_applied)
524    .bind(i64::from(record.compensation_attempts))
525    .bind(record.approved)
526    .bind(record.next_attempt_at.map(to_ms))
527    .bind(record.attempt_started_at.map(to_ms))
528    .bind(record.attempt_ended_at.map(to_ms))
529    .bind(record.lease_owner.as_ref().map(WorkerId::as_str))
530    .bind(to_i64(record.lease_epoch)?)
531    .bind(record.lease_expires_at.map(to_ms))
532    .bind(to_i64(record.version)?)
533    .bind(to_ms(record.updated_at))
534    .bind(record.committed_at.map(to_ms))
535    .bind(i64::from(record.output_schema_version))
536    .bind(record.id.to_string())
537    .bind(to_i64(read_version)?)
538    .execute(&mut *tx)
539    .await
540    .map_err(StoreError::backend)?
541    .rows_affected();
542    if updated == 1 {
543        Ok(())
544    } else {
545        // Unreachable under BEGIN IMMEDIATE; kept as a second guard.
546        Err(StoreError::backend(format!(
547            "effect {} changed under a write lock",
548            record.id
549        )))
550    }
551}
552
553type Query<'q> = sqlx::query::Query<'q, Sqlite, sqlx::sqlite::SqliteArguments>;
554
555fn bind_record<'q>(query: Query<'q>, r: &EffectRecord) -> Result<Query<'q>, StoreError> {
556    Ok(query
557        .bind(r.id.to_string())
558        .bind(r.key.name.as_str().to_owned())
559        .bind(r.key.key.as_str().to_owned())
560        .bind(r.kind.as_str())
561        .bind(r.status.as_str())
562        .bind(json(r.input.as_ref())?)
563        .bind(r.input_fingerprint.clone())
564        .bind(json(r.output.as_ref())?)
565        .bind(json(r.last_error.as_ref())?)
566        .bind(r.created_by.clone())
567        .bind(i64::from(r.attempt_count))
568        .bind(r.may_have_applied)
569        .bind(i64::from(r.compensation_attempts))
570        .bind(r.approved)
571        .bind(r.next_attempt_at.map(to_ms))
572        .bind(r.attempt_started_at.map(to_ms))
573        .bind(r.attempt_ended_at.map(to_ms))
574        .bind(r.lease_owner.as_ref().map(|w| w.as_str().to_owned()))
575        .bind(to_i64(r.lease_epoch)?)
576        .bind(r.lease_expires_at.map(to_ms))
577        .bind(to_i64(r.version)?)
578        .bind(to_ms(r.created_at))
579        .bind(to_ms(r.updated_at))
580        .bind(r.committed_at.map(to_ms))
581        .bind(json(r.execution_contract.as_ref())?)
582        .bind(i64::from(r.input_schema_version))
583        .bind(i64::from(r.output_schema_version)))
584}
585
586fn decode_record(row: &SqliteRow) -> Result<EffectRecord, StoreError> {
587    let id: String = get(row, "id")?;
588    let kind: String = get(row, "kind")?;
589    let status: String = get(row, "status")?;
590    Ok(EffectRecord {
591        id: parse_id(&id)?,
592        key: EffectKey::new(
593            EffectName::new(get::<String>(row, "effect_name")?).map_err(StoreError::backend)?,
594            LogicalKey::new(get::<String>(row, "logical_key")?).map_err(StoreError::backend)?,
595        ),
596        kind: EffectKind::parse(&kind).ok_or_else(|| corrupt("kind", &kind))?,
597        status: EffectStatus::parse(&status).ok_or_else(|| corrupt("status", &status))?,
598        execution_contract: from_json(get(row, "execution_contract")?)?,
599        input_schema_version: u32::try_from(get::<i64>(row, "input_schema_version")?)
600            .map_err(StoreError::backend)?,
601        output_schema_version: u32::try_from(get::<i64>(row, "output_schema_version")?)
602            .map_err(StoreError::backend)?,
603        input: from_json(get(row, "input")?)?,
604        input_fingerprint: get(row, "input_fingerprint")?,
605        output: from_json(get(row, "output")?)?,
606        last_error: from_json::<ErrorRecord>(get(row, "last_error")?)?,
607        created_by: get(row, "created_by")?,
608        attempt_count: u32::try_from(get::<i64>(row, "attempt_count")?)
609            .map_err(StoreError::backend)?,
610        may_have_applied: get(row, "may_have_applied")?,
611        compensation_attempts: u32::try_from(get::<i64>(row, "compensation_attempts")?)
612            .map_err(StoreError::backend)?,
613        approved: get(row, "approved")?,
614        next_attempt_at: get::<Option<i64>>(row, "next_attempt_at")?.map(from_ms),
615        attempt_started_at: get::<Option<i64>>(row, "attempt_started_at")?.map(from_ms),
616        attempt_ended_at: get::<Option<i64>>(row, "attempt_ended_at")?.map(from_ms),
617        lease_owner: get::<Option<String>>(row, "lease_owner")?.map(WorkerId::new),
618        lease_epoch: to_u64(get(row, "lease_epoch")?)?,
619        lease_expires_at: get::<Option<i64>>(row, "lease_expires_at")?.map(from_ms),
620        version: to_u64(get(row, "version")?)?,
621        created_at: from_ms(get(row, "created_at")?),
622        updated_at: from_ms(get(row, "updated_at")?),
623        committed_at: get::<Option<i64>>(row, "committed_at")?.map(from_ms),
624    })
625}
626
627fn decode_event(row: &SqliteRow) -> Result<EffectEvent, StoreError> {
628    let id: String = get(row, "effect_id")?;
629    let transition: String = get(row, "transition")?;
630    let from: String = get(row, "from_status")?;
631    let to: String = get(row, "to_status")?;
632    Ok(EffectEvent {
633        effect_id: parse_id(&id)?,
634        sequence: to_u64(get(row, "sequence")?)?,
635        transition: Transition::parse(&transition)
636            .ok_or_else(|| corrupt("transition", &transition))?,
637        from: EffectStatus::parse(&from).ok_or_else(|| corrupt("from_status", &from))?,
638        to: EffectStatus::parse(&to).ok_or_else(|| corrupt("to_status", &to))?,
639        attempt: u32::try_from(get::<i64>(row, "attempt")?).map_err(StoreError::backend)?,
640        actor: get(row, "actor")?,
641        payload: from_json(get(row, "payload")?)?,
642        at: from_ms(get(row, "at")?),
643    })
644}
645
646fn get<'r, T>(row: &'r SqliteRow, column: &str) -> Result<T, StoreError>
647where
648    T: sqlx::Decode<'r, Sqlite> + sqlx::Type<Sqlite>,
649{
650    row.try_get(column).map_err(StoreError::backend)
651}
652
653fn parse_id(id: &str) -> Result<EffectId, StoreError> {
654    Uuid::from_str(id)
655        .map(EffectId::from_uuid)
656        .map_err(|_| corrupt("id", id))
657}
658
659fn corrupt(column: &str, _value: &str) -> StoreError {
660    StoreError::backend(format!("unreadable {column} in effects database"))
661}
662
663fn json<T: Serialize>(value: Option<&T>) -> Result<Option<String>, StoreError> {
664    value
665        .map(serde_json::to_string)
666        .transpose()
667        .map_err(StoreError::backend)
668}
669
670fn from_json<T: DeserializeOwned>(text: Option<String>) -> Result<Option<T>, StoreError> {
671    text.map(|t| serde_json::from_str(&t))
672        .transpose()
673        .map_err(|_| corrupt("JSON", ""))
674}
675
676fn to_i64(value: u64) -> Result<i64, StoreError> {
677    i64::try_from(value).map_err(StoreError::backend)
678}
679
680fn to_u64(value: i64) -> Result<u64, StoreError> {
681    u64::try_from(value).map_err(StoreError::backend)
682}
683
684fn to_ms(time: SystemTime) -> i64 {
685    match time.duration_since(UNIX_EPOCH) {
686        Ok(after) => i64::try_from(after.as_millis()).unwrap_or(i64::MAX),
687        Err(before) => -i64::try_from(before.duration().as_millis()).unwrap_or(i64::MAX),
688    }
689}
690
691fn from_ms(ms: i64) -> SystemTime {
692    if ms >= 0 {
693        UNIX_EPOCH + Duration::from_millis(ms.unsigned_abs())
694    } else {
695        UNIX_EPOCH - Duration::from_millis(ms.unsigned_abs())
696    }
697}
698
699/// `time` at the precision it is stored with.
700fn round_trip(time: SystemTime) -> SystemTime {
701    from_ms(to_ms(time))
702}
703
704/// Gives every time in `record` its stored precision, so returned records
705/// equal what a later read returns.
706fn normalize(record: &mut EffectRecord) {
707    for time in [
708        &mut record.next_attempt_at,
709        &mut record.attempt_started_at,
710        &mut record.attempt_ended_at,
711        &mut record.lease_expires_at,
712        &mut record.committed_at,
713    ]
714    .into_iter()
715    .flatten()
716    {
717        *time = round_trip(*time);
718    }
719    record.created_at = round_trip(record.created_at);
720    record.updated_at = round_trip(record.updated_at);
721}
722
723#[cfg(test)]
724mod tests {
725    use super::*;
726
727    #[test]
728    fn times_round_trip_at_millisecond_precision() {
729        let t = UNIX_EPOCH + Duration::new(1_700_000_000, 123_456_789);
730        assert_eq!(
731            round_trip(t),
732            UNIX_EPOCH + Duration::new(1_700_000_000, 123_000_000)
733        );
734        let before = UNIX_EPOCH - Duration::from_millis(1500);
735        assert_eq!(round_trip(before), before);
736    }
737}