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//! Times are stored as Unix milliseconds; sub-millisecond precision is
33//! dropped, as the [`EffectStore`] contract allows.
34
35use std::path::Path;
36use std::str::FromStr;
37use std::time::{Duration, SystemTime, UNIX_EPOCH};
38
39use agent_effects_store::{
40    EffectEvent, EffectId, EffectKey, EffectKind, EffectName, EffectRecord, EffectStatus,
41    EffectStore, ErrorRecord, InsertOutcome, Lease, ListQuery, LogicalKey, NewEffect, PruneQuery,
42    StoreError, Transition, TransitionRequest, WorkerId,
43};
44use serde::Serialize;
45use serde::de::DeserializeOwned;
46use sqlx::sqlite::{
47    SqliteConnectOptions, SqliteJournalMode, SqlitePool, SqlitePoolOptions, SqliteRow,
48    SqliteSynchronous,
49};
50use sqlx::{AssertSqlSafe, Row, Sqlite, Transaction};
51use uuid::Uuid;
52
53static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");
54
55const COLUMNS: &str = "id, effect_name, logical_key, kind, status, input, input_fingerprint, \
56     output, last_error, created_by, attempt_count, may_have_applied, \
57     compensation_attempts, approved, next_attempt_at, \
58     attempt_started_at, attempt_ended_at, \
59     lease_owner, lease_epoch, lease_expires_at, version, created_at, updated_at, committed_at";
60
61/// An [`EffectStore`] in a SQLite database.
62///
63/// Cheap to clone; clones share the connection pool.
64#[derive(Clone, Debug)]
65pub struct SqliteStore {
66    pool: SqlitePool,
67}
68
69impl SqliteStore {
70    /// Opens (creating if needed) the database at `path`, configures it for
71    /// durability (see the [crate docs](crate)) and applies migrations.
72    ///
73    /// # Errors
74    ///
75    /// [`StoreError::Backend`] if the file cannot be opened or migrated.
76    pub async fn open(path: impl AsRef<Path>) -> Result<Self, StoreError> {
77        let options = SqliteConnectOptions::new()
78            .filename(path)
79            .create_if_missing(true)
80            .journal_mode(SqliteJournalMode::Wal)
81            .synchronous(SqliteSynchronous::Full)
82            .busy_timeout(Duration::from_secs(5))
83            .foreign_keys(true);
84        let pool = SqlitePoolOptions::new()
85            .max_connections(8)
86            .connect_with(options)
87            .await
88            .map_err(StoreError::backend)?;
89        Self::from_pool(pool).await
90    }
91
92    /// Uses an existing pool and applies migrations. The pool's connection
93    /// options are the caller's responsibility; see the
94    /// [crate docs](crate) for what [`Self::open`] sets and why.
95    ///
96    /// # Errors
97    ///
98    /// [`StoreError::Backend`] if migrating fails.
99    pub async fn from_pool(pool: SqlitePool) -> Result<Self, StoreError> {
100        MIGRATOR.run(&pool).await.map_err(StoreError::backend)?;
101        Ok(Self { pool })
102    }
103
104    /// The underlying pool.
105    pub fn pool(&self) -> &SqlitePool {
106        &self.pool
107    }
108
109    async fn begin(&self) -> Result<Transaction<'static, Sqlite>, StoreError> {
110        self.pool
111            .begin_with("BEGIN IMMEDIATE")
112            .await
113            .map_err(StoreError::backend)
114    }
115
116    /// Loads `id` under a write transaction, applies `change`, and saves the
117    /// record. Returns what `change` returned and the saved record, with the
118    /// transaction still open for further writes.
119    async fn modify<T>(
120        &self,
121        id: EffectId,
122        change: impl FnOnce(&mut EffectRecord) -> Result<T, StoreError>,
123    ) -> Result<(T, EffectRecord, Transaction<'static, Sqlite>), StoreError> {
124        let mut tx = self.begin().await?;
125        let row = sqlx::query(AssertSqlSafe(format!(
126            "SELECT {COLUMNS} FROM effects WHERE id = ?"
127        )))
128        .bind(id.to_string())
129        .fetch_optional(&mut *tx)
130        .await
131        .map_err(StoreError::backend)?;
132        let mut record = decode_record(&row.ok_or(StoreError::NotFound(id))?)?;
133        let read_version = record.version;
134        let result = change(&mut record)?;
135        normalize(&mut record);
136        save(&mut tx, &record, read_version).await?;
137        Ok((result, record, tx))
138    }
139}
140
141impl EffectStore for SqliteStore {
142    async fn insert_or_get(&self, new: NewEffect) -> Result<InsertOutcome, StoreError> {
143        let mut record = EffectRecord::new(new);
144        normalize(&mut record);
145        let inserted = bind_record(
146            sqlx::query(AssertSqlSafe(format!(
147                "INSERT INTO effects ({COLUMNS}) \
148                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \
149                 ON CONFLICT (effect_name, logical_key) DO NOTHING"
150            ))),
151            &record,
152        )?
153        .execute(&self.pool)
154        .await
155        .map_err(StoreError::backend)?
156        .rows_affected()
157            == 1;
158        if inserted {
159            return Ok(InsertOutcome {
160                record,
161                inserted: true,
162            });
163        }
164        let existing = self
165            .get_by_key(&record.key)
166            .await?
167            .ok_or_else(|| StoreError::backend("conflicting insert, but no existing record"))?;
168        Ok(InsertOutcome {
169            record: existing,
170            inserted: false,
171        })
172    }
173
174    async fn get(&self, id: EffectId) -> Result<Option<EffectRecord>, StoreError> {
175        sqlx::query(AssertSqlSafe(format!(
176            "SELECT {COLUMNS} FROM effects WHERE id = ?"
177        )))
178        .bind(id.to_string())
179        .fetch_optional(&self.pool)
180        .await
181        .map_err(StoreError::backend)?
182        .as_ref()
183        .map(decode_record)
184        .transpose()
185    }
186
187    async fn get_by_key(&self, key: &EffectKey) -> Result<Option<EffectRecord>, StoreError> {
188        sqlx::query(AssertSqlSafe(format!(
189            "SELECT {COLUMNS} FROM effects WHERE effect_name = ? AND logical_key = ?"
190        )))
191        .bind(key.name.as_str())
192        .bind(key.key.as_str())
193        .fetch_optional(&self.pool)
194        .await
195        .map_err(StoreError::backend)?
196        .as_ref()
197        .map(decode_record)
198        .transpose()
199    }
200
201    async fn acquire_lease(
202        &self,
203        id: EffectId,
204        owner: &WorkerId,
205        now: SystemTime,
206        ttl: Duration,
207    ) -> Result<Lease, StoreError> {
208        let (mut lease, _, tx) = self
209            .modify(id, |record| record.acquire_lease(owner, now, ttl))
210            .await?;
211        tx.commit().await.map_err(StoreError::backend)?;
212        lease.expires_at = round_trip(lease.expires_at);
213        Ok(lease)
214    }
215
216    async fn renew_lease(
217        &self,
218        lease: &Lease,
219        now: SystemTime,
220        ttl: Duration,
221    ) -> Result<Lease, StoreError> {
222        let (mut renewed, _, tx) = self
223            .modify(lease.effect_id, |record| {
224                record.renew_lease(lease, now, ttl)
225            })
226            .await?;
227        tx.commit().await.map_err(StoreError::backend)?;
228        renewed.expires_at = round_trip(renewed.expires_at);
229        Ok(renewed)
230    }
231
232    async fn release_lease(&self, lease: &Lease) -> Result<(), StoreError> {
233        let (released, _, tx) = self
234            .modify(lease.effect_id, |record| Ok(record.release_lease(lease)))
235            .await?;
236        if released {
237            tx.commit().await.map_err(StoreError::backend)?;
238        } else {
239            tx.rollback().await.map_err(StoreError::backend)?;
240        }
241        Ok(())
242    }
243
244    async fn transition(&self, request: TransitionRequest) -> Result<EffectRecord, StoreError> {
245        let id = request.id;
246        let (mut event, record, mut tx) = self.modify(id, |record| record.apply(request)).await?;
247        event.at = round_trip(event.at);
248        sqlx::query(
249            "INSERT INTO effect_events \
250             (effect_id, sequence, transition, from_status, to_status, attempt, actor, payload, at) \
251             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
252        )
253        .bind(event.effect_id.to_string())
254        .bind(to_i64(event.sequence)?)
255        .bind(event.transition.as_str())
256        .bind(event.from.as_str())
257        .bind(event.to.as_str())
258        .bind(i64::from(event.attempt))
259        .bind(event.actor.as_deref())
260        .bind(json(event.payload.as_ref())?)
261        .bind(to_ms(event.at))
262        .execute(&mut *tx)
263        .await
264        .map_err(StoreError::backend)?;
265        tx.commit().await.map_err(StoreError::backend)?;
266        Ok(record)
267    }
268
269    async fn list(&self, query: ListQuery) -> Result<Vec<EffectRecord>, StoreError> {
270        let mut sql = format!("SELECT {COLUMNS} FROM effects WHERE 1 = 1");
271        if !query.statuses.is_empty() {
272            let marks = vec!["?"; query.statuses.len()].join(", ");
273            sql.push_str(" AND status IN (");
274            sql.push_str(&marks);
275            sql.push(')');
276        }
277        if query.lease_expired_at.is_some() {
278            sql.push_str(
279                " AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?)",
280            );
281        }
282        if query.after.is_some() {
283            sql.push_str(" AND id > ?");
284        }
285        sql.push_str(" ORDER BY id LIMIT ?");
286
287        // Only placeholders are interpolated; every value is bound.
288        let mut statement = sqlx::query(AssertSqlSafe(sql));
289        for status in &query.statuses {
290            statement = statement.bind(status.as_str());
291        }
292        if let Some(now) = query.lease_expired_at {
293            statement = statement.bind(to_ms(now));
294        }
295        if let Some(after) = query.after {
296            statement = statement.bind(after.to_string());
297        }
298        statement = statement.bind(i64::try_from(query.limit).unwrap_or(i64::MAX));
299        statement
300            .fetch_all(&self.pool)
301            .await
302            .map_err(StoreError::backend)?
303            .iter()
304            .map(decode_record)
305            .collect()
306    }
307
308    async fn events(&self, id: EffectId) -> Result<Vec<EffectEvent>, StoreError> {
309        if self.get(id).await?.is_none() {
310            return Err(StoreError::NotFound(id));
311        }
312        sqlx::query(
313            "SELECT effect_id, sequence, transition, from_status, to_status, attempt, actor, \
314             payload, at FROM effect_events WHERE effect_id = ? ORDER BY sequence",
315        )
316        .bind(id.to_string())
317        .fetch_all(&self.pool)
318        .await
319        .map_err(StoreError::backend)?
320        .iter()
321        .map(decode_event)
322        .collect()
323    }
324
325    async fn prune(&self, query: PruneQuery) -> Result<u64, StoreError> {
326        let Some(cutoff) = query.cutoff().filter(|_| query.status.is_settled()) else {
327            return Ok(0);
328        };
329        // Selected and deleted under one write lock: nothing can lease or
330        // change a selected record in between.
331        let mut tx = self.begin().await?;
332        let ids: Vec<String> = sqlx::query_scalar(
333            "SELECT id FROM effects WHERE status = ? AND updated_at <= ? \
334             AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?) \
335             ORDER BY id LIMIT ?",
336        )
337        .bind(query.status.as_str())
338        .bind(to_ms(cutoff))
339        .bind(to_ms(query.now))
340        .bind(i64::try_from(query.limit).unwrap_or(i64::MAX))
341        .fetch_all(&mut *tx)
342        .await
343        .map_err(StoreError::backend)?;
344        for chunk in ids.chunks(500) {
345            let marks = vec!["?"; chunk.len()].join(", ");
346            for (table, column) in [("effect_events", "effect_id"), ("effects", "id")] {
347                // Only placeholders are interpolated; every value is bound.
348                let mut statement = sqlx::query(AssertSqlSafe(format!(
349                    "DELETE FROM {table} WHERE {column} IN ({marks})"
350                )));
351                for id in chunk {
352                    statement = statement.bind(id.as_str());
353                }
354                statement
355                    .execute(&mut *tx)
356                    .await
357                    .map_err(StoreError::backend)?;
358            }
359        }
360        tx.commit().await.map_err(StoreError::backend)?;
361        Ok(ids.len() as u64)
362    }
363}
364
365/// Writes `record` over the row last read at `read_version`.
366async fn save(
367    tx: &mut Transaction<'static, Sqlite>,
368    record: &EffectRecord,
369    read_version: u64,
370) -> Result<(), StoreError> {
371    let updated = sqlx::query(
372        "UPDATE effects SET status = ?, output = ?, last_error = ?, attempt_count = ?, \
373         may_have_applied = ?, compensation_attempts = ?, approved = ?, \
374         next_attempt_at = ?, attempt_started_at = ?, attempt_ended_at = ?, \
375         lease_owner = ?, lease_epoch = ?, \
376         lease_expires_at = ?, version = ?, updated_at = ?, committed_at = ? \
377         WHERE id = ? AND version = ?",
378    )
379    .bind(record.status.as_str())
380    .bind(json(record.output.as_ref())?)
381    .bind(json(record.last_error.as_ref())?)
382    .bind(i64::from(record.attempt_count))
383    .bind(record.may_have_applied)
384    .bind(i64::from(record.compensation_attempts))
385    .bind(record.approved)
386    .bind(record.next_attempt_at.map(to_ms))
387    .bind(record.attempt_started_at.map(to_ms))
388    .bind(record.attempt_ended_at.map(to_ms))
389    .bind(record.lease_owner.as_ref().map(WorkerId::as_str))
390    .bind(to_i64(record.lease_epoch)?)
391    .bind(record.lease_expires_at.map(to_ms))
392    .bind(to_i64(record.version)?)
393    .bind(to_ms(record.updated_at))
394    .bind(record.committed_at.map(to_ms))
395    .bind(record.id.to_string())
396    .bind(to_i64(read_version)?)
397    .execute(&mut **tx)
398    .await
399    .map_err(StoreError::backend)?
400    .rows_affected();
401    if updated == 1 {
402        Ok(())
403    } else {
404        // Unreachable under BEGIN IMMEDIATE; kept as a second guard.
405        Err(StoreError::backend(format!(
406            "effect {} changed under a write lock",
407            record.id
408        )))
409    }
410}
411
412type Query<'q> = sqlx::query::Query<'q, Sqlite, sqlx::sqlite::SqliteArguments>;
413
414fn bind_record<'q>(query: Query<'q>, r: &EffectRecord) -> Result<Query<'q>, StoreError> {
415    Ok(query
416        .bind(r.id.to_string())
417        .bind(r.key.name.as_str().to_owned())
418        .bind(r.key.key.as_str().to_owned())
419        .bind(r.kind.as_str())
420        .bind(r.status.as_str())
421        .bind(json(r.input.as_ref())?)
422        .bind(r.input_fingerprint.clone())
423        .bind(json(r.output.as_ref())?)
424        .bind(json(r.last_error.as_ref())?)
425        .bind(r.created_by.clone())
426        .bind(i64::from(r.attempt_count))
427        .bind(r.may_have_applied)
428        .bind(i64::from(r.compensation_attempts))
429        .bind(r.approved)
430        .bind(r.next_attempt_at.map(to_ms))
431        .bind(r.attempt_started_at.map(to_ms))
432        .bind(r.attempt_ended_at.map(to_ms))
433        .bind(r.lease_owner.as_ref().map(|w| w.as_str().to_owned()))
434        .bind(to_i64(r.lease_epoch)?)
435        .bind(r.lease_expires_at.map(to_ms))
436        .bind(to_i64(r.version)?)
437        .bind(to_ms(r.created_at))
438        .bind(to_ms(r.updated_at))
439        .bind(r.committed_at.map(to_ms)))
440}
441
442fn decode_record(row: &SqliteRow) -> Result<EffectRecord, StoreError> {
443    let id: String = get(row, "id")?;
444    let kind: String = get(row, "kind")?;
445    let status: String = get(row, "status")?;
446    Ok(EffectRecord {
447        id: parse_id(&id)?,
448        key: EffectKey::new(
449            EffectName::new(get::<String>(row, "effect_name")?).map_err(StoreError::backend)?,
450            LogicalKey::new(get::<String>(row, "logical_key")?).map_err(StoreError::backend)?,
451        ),
452        kind: EffectKind::parse(&kind).ok_or_else(|| corrupt("kind", &kind))?,
453        status: EffectStatus::parse(&status).ok_or_else(|| corrupt("status", &status))?,
454        input: from_json(get(row, "input")?)?,
455        input_fingerprint: get(row, "input_fingerprint")?,
456        output: from_json(get(row, "output")?)?,
457        last_error: from_json::<ErrorRecord>(get(row, "last_error")?)?,
458        created_by: get(row, "created_by")?,
459        attempt_count: u32::try_from(get::<i64>(row, "attempt_count")?)
460            .map_err(StoreError::backend)?,
461        may_have_applied: get(row, "may_have_applied")?,
462        compensation_attempts: u32::try_from(get::<i64>(row, "compensation_attempts")?)
463            .map_err(StoreError::backend)?,
464        approved: get(row, "approved")?,
465        next_attempt_at: get::<Option<i64>>(row, "next_attempt_at")?.map(from_ms),
466        attempt_started_at: get::<Option<i64>>(row, "attempt_started_at")?.map(from_ms),
467        attempt_ended_at: get::<Option<i64>>(row, "attempt_ended_at")?.map(from_ms),
468        lease_owner: get::<Option<String>>(row, "lease_owner")?.map(WorkerId::new),
469        lease_epoch: to_u64(get(row, "lease_epoch")?)?,
470        lease_expires_at: get::<Option<i64>>(row, "lease_expires_at")?.map(from_ms),
471        version: to_u64(get(row, "version")?)?,
472        created_at: from_ms(get(row, "created_at")?),
473        updated_at: from_ms(get(row, "updated_at")?),
474        committed_at: get::<Option<i64>>(row, "committed_at")?.map(from_ms),
475    })
476}
477
478fn decode_event(row: &SqliteRow) -> Result<EffectEvent, StoreError> {
479    let id: String = get(row, "effect_id")?;
480    let transition: String = get(row, "transition")?;
481    let from: String = get(row, "from_status")?;
482    let to: String = get(row, "to_status")?;
483    Ok(EffectEvent {
484        effect_id: parse_id(&id)?,
485        sequence: to_u64(get(row, "sequence")?)?,
486        transition: Transition::parse(&transition)
487            .ok_or_else(|| corrupt("transition", &transition))?,
488        from: EffectStatus::parse(&from).ok_or_else(|| corrupt("from_status", &from))?,
489        to: EffectStatus::parse(&to).ok_or_else(|| corrupt("to_status", &to))?,
490        attempt: u32::try_from(get::<i64>(row, "attempt")?).map_err(StoreError::backend)?,
491        actor: get(row, "actor")?,
492        payload: from_json(get(row, "payload")?)?,
493        at: from_ms(get(row, "at")?),
494    })
495}
496
497fn get<'r, T>(row: &'r SqliteRow, column: &str) -> Result<T, StoreError>
498where
499    T: sqlx::Decode<'r, Sqlite> + sqlx::Type<Sqlite>,
500{
501    row.try_get(column).map_err(StoreError::backend)
502}
503
504fn parse_id(id: &str) -> Result<EffectId, StoreError> {
505    Uuid::from_str(id)
506        .map(EffectId::from_uuid)
507        .map_err(|_| corrupt("id", id))
508}
509
510fn corrupt(column: &str, value: &str) -> StoreError {
511    StoreError::backend(format!(
512        "unreadable {column} in effects database: {value:?}"
513    ))
514}
515
516fn json<T: Serialize>(value: Option<&T>) -> Result<Option<String>, StoreError> {
517    value
518        .map(serde_json::to_string)
519        .transpose()
520        .map_err(StoreError::backend)
521}
522
523fn from_json<T: DeserializeOwned>(text: Option<String>) -> Result<Option<T>, StoreError> {
524    text.map(|t| serde_json::from_str(&t))
525        .transpose()
526        .map_err(StoreError::backend)
527}
528
529fn to_i64(value: u64) -> Result<i64, StoreError> {
530    i64::try_from(value).map_err(StoreError::backend)
531}
532
533fn to_u64(value: i64) -> Result<u64, StoreError> {
534    u64::try_from(value).map_err(StoreError::backend)
535}
536
537fn to_ms(time: SystemTime) -> i64 {
538    match time.duration_since(UNIX_EPOCH) {
539        Ok(after) => i64::try_from(after.as_millis()).unwrap_or(i64::MAX),
540        Err(before) => -i64::try_from(before.duration().as_millis()).unwrap_or(i64::MAX),
541    }
542}
543
544fn from_ms(ms: i64) -> SystemTime {
545    if ms >= 0 {
546        UNIX_EPOCH + Duration::from_millis(ms.unsigned_abs())
547    } else {
548        UNIX_EPOCH - Duration::from_millis(ms.unsigned_abs())
549    }
550}
551
552/// `time` at the precision it is stored with.
553fn round_trip(time: SystemTime) -> SystemTime {
554    from_ms(to_ms(time))
555}
556
557/// Gives every time in `record` its stored precision, so returned records
558/// equal what a later read returns.
559fn normalize(record: &mut EffectRecord) {
560    for time in [
561        &mut record.next_attempt_at,
562        &mut record.attempt_started_at,
563        &mut record.attempt_ended_at,
564        &mut record.lease_expires_at,
565        &mut record.committed_at,
566    ]
567    .into_iter()
568    .flatten()
569    {
570        *time = round_trip(*time);
571    }
572    record.created_at = round_trip(record.created_at);
573    record.updated_at = round_trip(record.updated_at);
574}
575
576#[cfg(test)]
577mod tests {
578    use super::*;
579
580    #[test]
581    fn times_round_trip_at_millisecond_precision() {
582        let t = UNIX_EPOCH + Duration::new(1_700_000_000, 123_456_789);
583        assert_eq!(
584            round_trip(t),
585            UNIX_EPOCH + Duration::new(1_700_000_000, 123_000_000)
586        );
587        let before = UNIX_EPOCH - Duration::from_millis(1500);
588        assert_eq!(round_trip(before), before);
589    }
590}