1use 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, execution_contract, input_schema_version, output_schema_version";
60
61#[derive(Clone, Debug)]
65pub struct SqliteStore {
66 pool: SqlitePool,
67}
68
69impl SqliteStore {
70 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 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 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 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 append_late_observation(
143 &self,
144 observation: agent_effects_store::LateObservation,
145 ) -> Result<(), StoreError> {
146 sqlx::query("INSERT INTO late_observations (id, effect_id, observation) VALUES (?, ?, ?) ON CONFLICT (id) DO NOTHING")
147 .bind(observation.id.to_string()).bind(observation.effect_id.to_string())
148 .bind(serde_json::to_string(&observation).map_err(StoreError::backend)?)
149 .execute(&self.pool).await.map_err(StoreError::backend)?;
150 Ok(())
151 }
152
153 async fn late_observations(
154 &self,
155 id: EffectId,
156 after: Option<EffectId>,
157 limit: usize,
158 ) -> Result<Vec<agent_effects_store::LateObservation>, StoreError> {
159 if self.get(id).await?.is_none() {
160 return Err(StoreError::NotFound(id));
161 }
162 let rows = sqlx::query("SELECT observation FROM late_observations WHERE effect_id = ? AND (? IS NULL OR id > ?) ORDER BY id LIMIT ?")
163 .bind(id.to_string()).bind(after.map(|id| id.to_string()))
164 .bind(after.map(|id| id.to_string()))
165 .bind(i64::try_from(limit).unwrap_or(i64::MAX))
166 .fetch_all(&self.pool).await.map_err(StoreError::backend)?;
167 rows.iter()
168 .map(|row| {
169 let json: String = get(row, "observation")?;
170 serde_json::from_str(&json).map_err(StoreError::backend)
171 })
172 .collect()
173 }
174
175 async fn insert_or_get(&self, new: NewEffect) -> Result<InsertOutcome, StoreError> {
176 let mut record = EffectRecord::new(new);
177 normalize(&mut record);
178 let inserted = bind_record(
179 sqlx::query(AssertSqlSafe(format!(
180 "INSERT INTO effects ({COLUMNS}) \
181 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) \
182 ON CONFLICT (effect_name, logical_key) DO NOTHING"
183 ))),
184 &record,
185 )?
186 .execute(&self.pool)
187 .await
188 .map_err(StoreError::backend)?
189 .rows_affected()
190 == 1;
191 if inserted {
192 return Ok(InsertOutcome {
193 record,
194 inserted: true,
195 });
196 }
197 let existing = self
198 .get_by_key(&record.key)
199 .await?
200 .ok_or_else(|| StoreError::backend("conflicting insert, but no existing record"))?;
201 Ok(InsertOutcome {
202 record: existing,
203 inserted: false,
204 })
205 }
206
207 async fn get(&self, id: EffectId) -> Result<Option<EffectRecord>, StoreError> {
208 sqlx::query(AssertSqlSafe(format!(
209 "SELECT {COLUMNS} FROM effects WHERE id = ?"
210 )))
211 .bind(id.to_string())
212 .fetch_optional(&self.pool)
213 .await
214 .map_err(StoreError::backend)?
215 .as_ref()
216 .map(decode_record)
217 .transpose()
218 }
219
220 async fn get_by_key(&self, key: &EffectKey) -> Result<Option<EffectRecord>, StoreError> {
221 sqlx::query(AssertSqlSafe(format!(
222 "SELECT {COLUMNS} FROM effects WHERE effect_name = ? AND logical_key = ?"
223 )))
224 .bind(key.name.as_str())
225 .bind(key.key.as_str())
226 .fetch_optional(&self.pool)
227 .await
228 .map_err(StoreError::backend)?
229 .as_ref()
230 .map(decode_record)
231 .transpose()
232 }
233
234 async fn acquire_lease(
235 &self,
236 id: EffectId,
237 owner: &WorkerId,
238 now: SystemTime,
239 ttl: Duration,
240 ) -> Result<Lease, StoreError> {
241 let (mut lease, _, tx) = self
242 .modify(id, |record| record.acquire_lease(owner, now, ttl))
243 .await?;
244 tx.commit().await.map_err(StoreError::backend)?;
245 lease.expires_at = round_trip(lease.expires_at);
246 Ok(lease)
247 }
248
249 async fn renew_lease(
250 &self,
251 lease: &Lease,
252 now: SystemTime,
253 ttl: Duration,
254 ) -> Result<Lease, StoreError> {
255 let (mut renewed, _, tx) = self
256 .modify(lease.effect_id, |record| {
257 record.renew_lease(lease, now, ttl)
258 })
259 .await?;
260 tx.commit().await.map_err(StoreError::backend)?;
261 renewed.expires_at = round_trip(renewed.expires_at);
262 Ok(renewed)
263 }
264
265 async fn release_lease(&self, lease: &Lease) -> Result<(), StoreError> {
266 let (released, _, tx) = self
267 .modify(lease.effect_id, |record| Ok(record.release_lease(lease)))
268 .await?;
269 if released {
270 tx.commit().await.map_err(StoreError::backend)?;
271 } else {
272 tx.rollback().await.map_err(StoreError::backend)?;
273 }
274 Ok(())
275 }
276
277 async fn transition(&self, request: TransitionRequest) -> Result<EffectRecord, StoreError> {
278 let id = request.id;
279 let (mut event, record, mut tx) = self.modify(id, |record| record.apply(request)).await?;
280 event.at = round_trip(event.at);
281 sqlx::query(
282 "INSERT INTO effect_events \
283 (effect_id, sequence, transition, from_status, to_status, attempt, actor, payload, at) \
284 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
285 )
286 .bind(event.effect_id.to_string())
287 .bind(to_i64(event.sequence)?)
288 .bind(event.transition.as_str())
289 .bind(event.from.as_str())
290 .bind(event.to.as_str())
291 .bind(i64::from(event.attempt))
292 .bind(event.actor.as_deref())
293 .bind(json(event.payload.as_ref())?)
294 .bind(to_ms(event.at))
295 .execute(&mut *tx)
296 .await
297 .map_err(StoreError::backend)?;
298 tx.commit().await.map_err(StoreError::backend)?;
299 Ok(record)
300 }
301
302 async fn list(&self, query: ListQuery) -> Result<Vec<EffectRecord>, StoreError> {
303 let mut sql = format!("SELECT {COLUMNS} FROM effects WHERE 1 = 1");
304 if !query.statuses.is_empty() {
305 let marks = vec!["?"; query.statuses.len()].join(", ");
306 sql.push_str(" AND status IN (");
307 sql.push_str(&marks);
308 sql.push(')');
309 }
310 if query.lease_expired_at.is_some() {
311 sql.push_str(
312 " AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?)",
313 );
314 }
315 if query.after.is_some() {
316 sql.push_str(" AND id > ?");
317 }
318 sql.push_str(" ORDER BY id LIMIT ?");
319
320 let mut statement = sqlx::query(AssertSqlSafe(sql));
322 for status in &query.statuses {
323 statement = statement.bind(status.as_str());
324 }
325 if let Some(now) = query.lease_expired_at {
326 statement = statement.bind(to_ms(now));
327 }
328 if let Some(after) = query.after {
329 statement = statement.bind(after.to_string());
330 }
331 statement = statement.bind(i64::try_from(query.limit).unwrap_or(i64::MAX));
332 statement
333 .fetch_all(&self.pool)
334 .await
335 .map_err(StoreError::backend)?
336 .iter()
337 .map(decode_record)
338 .collect()
339 }
340
341 async fn events(&self, id: EffectId) -> Result<Vec<EffectEvent>, StoreError> {
342 if self.get(id).await?.is_none() {
343 return Err(StoreError::NotFound(id));
344 }
345 sqlx::query(
346 "SELECT effect_id, sequence, transition, from_status, to_status, attempt, actor, \
347 payload, at FROM effect_events WHERE effect_id = ? ORDER BY sequence",
348 )
349 .bind(id.to_string())
350 .fetch_all(&self.pool)
351 .await
352 .map_err(StoreError::backend)?
353 .iter()
354 .map(decode_event)
355 .collect()
356 }
357
358 async fn prune(&self, query: PruneQuery) -> Result<u64, StoreError> {
359 let Some(cutoff) = query.cutoff().filter(|_| query.status.is_settled()) else {
360 return Ok(0);
361 };
362 let mut tx = self.begin().await?;
365 let ids: Vec<String> = sqlx::query_scalar(
366 "SELECT id FROM effects WHERE status = ? AND updated_at <= ? \
367 AND (lease_owner IS NULL OR lease_expires_at IS NULL OR lease_expires_at <= ?) \
368 ORDER BY id LIMIT ?",
369 )
370 .bind(query.status.as_str())
371 .bind(to_ms(cutoff))
372 .bind(to_ms(query.now))
373 .bind(i64::try_from(query.limit).unwrap_or(i64::MAX))
374 .fetch_all(&mut *tx)
375 .await
376 .map_err(StoreError::backend)?;
377 for chunk in ids.chunks(500) {
378 let marks = vec!["?"; chunk.len()].join(", ");
379 for (table, column) in [("effect_events", "effect_id"), ("effects", "id")] {
380 let mut statement = sqlx::query(AssertSqlSafe(format!(
382 "DELETE FROM {table} WHERE {column} IN ({marks})"
383 )));
384 for id in chunk {
385 statement = statement.bind(id.as_str());
386 }
387 statement
388 .execute(&mut *tx)
389 .await
390 .map_err(StoreError::backend)?;
391 }
392 }
393 tx.commit().await.map_err(StoreError::backend)?;
394 Ok(ids.len() as u64)
395 }
396}
397
398async fn save(
400 tx: &mut Transaction<'static, Sqlite>,
401 record: &EffectRecord,
402 read_version: u64,
403) -> Result<(), StoreError> {
404 let updated = sqlx::query(
405 "UPDATE effects SET status = ?, output = ?, last_error = ?, attempt_count = ?, \
406 may_have_applied = ?, compensation_attempts = ?, approved = ?, \
407 next_attempt_at = ?, attempt_started_at = ?, attempt_ended_at = ?, \
408 lease_owner = ?, lease_epoch = ?, \
409 lease_expires_at = ?, version = ?, updated_at = ?, committed_at = ?, output_schema_version = ? \
410 WHERE id = ? AND version = ?",
411 )
412 .bind(record.status.as_str())
413 .bind(json(record.output.as_ref())?)
414 .bind(json(record.last_error.as_ref())?)
415 .bind(i64::from(record.attempt_count))
416 .bind(record.may_have_applied)
417 .bind(i64::from(record.compensation_attempts))
418 .bind(record.approved)
419 .bind(record.next_attempt_at.map(to_ms))
420 .bind(record.attempt_started_at.map(to_ms))
421 .bind(record.attempt_ended_at.map(to_ms))
422 .bind(record.lease_owner.as_ref().map(WorkerId::as_str))
423 .bind(to_i64(record.lease_epoch)?)
424 .bind(record.lease_expires_at.map(to_ms))
425 .bind(to_i64(record.version)?)
426 .bind(to_ms(record.updated_at))
427 .bind(record.committed_at.map(to_ms))
428 .bind(i64::from(record.output_schema_version))
429 .bind(record.id.to_string())
430 .bind(to_i64(read_version)?)
431 .execute(&mut **tx)
432 .await
433 .map_err(StoreError::backend)?
434 .rows_affected();
435 if updated == 1 {
436 Ok(())
437 } else {
438 Err(StoreError::backend(format!(
440 "effect {} changed under a write lock",
441 record.id
442 )))
443 }
444}
445
446type Query<'q> = sqlx::query::Query<'q, Sqlite, sqlx::sqlite::SqliteArguments>;
447
448fn bind_record<'q>(query: Query<'q>, r: &EffectRecord) -> Result<Query<'q>, StoreError> {
449 Ok(query
450 .bind(r.id.to_string())
451 .bind(r.key.name.as_str().to_owned())
452 .bind(r.key.key.as_str().to_owned())
453 .bind(r.kind.as_str())
454 .bind(r.status.as_str())
455 .bind(json(r.input.as_ref())?)
456 .bind(r.input_fingerprint.clone())
457 .bind(json(r.output.as_ref())?)
458 .bind(json(r.last_error.as_ref())?)
459 .bind(r.created_by.clone())
460 .bind(i64::from(r.attempt_count))
461 .bind(r.may_have_applied)
462 .bind(i64::from(r.compensation_attempts))
463 .bind(r.approved)
464 .bind(r.next_attempt_at.map(to_ms))
465 .bind(r.attempt_started_at.map(to_ms))
466 .bind(r.attempt_ended_at.map(to_ms))
467 .bind(r.lease_owner.as_ref().map(|w| w.as_str().to_owned()))
468 .bind(to_i64(r.lease_epoch)?)
469 .bind(r.lease_expires_at.map(to_ms))
470 .bind(to_i64(r.version)?)
471 .bind(to_ms(r.created_at))
472 .bind(to_ms(r.updated_at))
473 .bind(r.committed_at.map(to_ms))
474 .bind(json(r.execution_contract.as_ref())?)
475 .bind(i64::from(r.input_schema_version))
476 .bind(i64::from(r.output_schema_version)))
477}
478
479fn decode_record(row: &SqliteRow) -> Result<EffectRecord, StoreError> {
480 let id: String = get(row, "id")?;
481 let kind: String = get(row, "kind")?;
482 let status: String = get(row, "status")?;
483 Ok(EffectRecord {
484 id: parse_id(&id)?,
485 key: EffectKey::new(
486 EffectName::new(get::<String>(row, "effect_name")?).map_err(StoreError::backend)?,
487 LogicalKey::new(get::<String>(row, "logical_key")?).map_err(StoreError::backend)?,
488 ),
489 kind: EffectKind::parse(&kind).ok_or_else(|| corrupt("kind", &kind))?,
490 status: EffectStatus::parse(&status).ok_or_else(|| corrupt("status", &status))?,
491 execution_contract: from_json(get(row, "execution_contract")?)?,
492 input_schema_version: u32::try_from(get::<i64>(row, "input_schema_version")?)
493 .map_err(StoreError::backend)?,
494 output_schema_version: u32::try_from(get::<i64>(row, "output_schema_version")?)
495 .map_err(StoreError::backend)?,
496 input: from_json(get(row, "input")?)?,
497 input_fingerprint: get(row, "input_fingerprint")?,
498 output: from_json(get(row, "output")?)?,
499 last_error: from_json::<ErrorRecord>(get(row, "last_error")?)?,
500 created_by: get(row, "created_by")?,
501 attempt_count: u32::try_from(get::<i64>(row, "attempt_count")?)
502 .map_err(StoreError::backend)?,
503 may_have_applied: get(row, "may_have_applied")?,
504 compensation_attempts: u32::try_from(get::<i64>(row, "compensation_attempts")?)
505 .map_err(StoreError::backend)?,
506 approved: get(row, "approved")?,
507 next_attempt_at: get::<Option<i64>>(row, "next_attempt_at")?.map(from_ms),
508 attempt_started_at: get::<Option<i64>>(row, "attempt_started_at")?.map(from_ms),
509 attempt_ended_at: get::<Option<i64>>(row, "attempt_ended_at")?.map(from_ms),
510 lease_owner: get::<Option<String>>(row, "lease_owner")?.map(WorkerId::new),
511 lease_epoch: to_u64(get(row, "lease_epoch")?)?,
512 lease_expires_at: get::<Option<i64>>(row, "lease_expires_at")?.map(from_ms),
513 version: to_u64(get(row, "version")?)?,
514 created_at: from_ms(get(row, "created_at")?),
515 updated_at: from_ms(get(row, "updated_at")?),
516 committed_at: get::<Option<i64>>(row, "committed_at")?.map(from_ms),
517 })
518}
519
520fn decode_event(row: &SqliteRow) -> Result<EffectEvent, StoreError> {
521 let id: String = get(row, "effect_id")?;
522 let transition: String = get(row, "transition")?;
523 let from: String = get(row, "from_status")?;
524 let to: String = get(row, "to_status")?;
525 Ok(EffectEvent {
526 effect_id: parse_id(&id)?,
527 sequence: to_u64(get(row, "sequence")?)?,
528 transition: Transition::parse(&transition)
529 .ok_or_else(|| corrupt("transition", &transition))?,
530 from: EffectStatus::parse(&from).ok_or_else(|| corrupt("from_status", &from))?,
531 to: EffectStatus::parse(&to).ok_or_else(|| corrupt("to_status", &to))?,
532 attempt: u32::try_from(get::<i64>(row, "attempt")?).map_err(StoreError::backend)?,
533 actor: get(row, "actor")?,
534 payload: from_json(get(row, "payload")?)?,
535 at: from_ms(get(row, "at")?),
536 })
537}
538
539fn get<'r, T>(row: &'r SqliteRow, column: &str) -> Result<T, StoreError>
540where
541 T: sqlx::Decode<'r, Sqlite> + sqlx::Type<Sqlite>,
542{
543 row.try_get(column).map_err(StoreError::backend)
544}
545
546fn parse_id(id: &str) -> Result<EffectId, StoreError> {
547 Uuid::from_str(id)
548 .map(EffectId::from_uuid)
549 .map_err(|_| corrupt("id", id))
550}
551
552fn corrupt(column: &str, value: &str) -> StoreError {
553 StoreError::backend(format!(
554 "unreadable {column} in effects database: {value:?}"
555 ))
556}
557
558fn json<T: Serialize>(value: Option<&T>) -> Result<Option<String>, StoreError> {
559 value
560 .map(serde_json::to_string)
561 .transpose()
562 .map_err(StoreError::backend)
563}
564
565fn from_json<T: DeserializeOwned>(text: Option<String>) -> Result<Option<T>, StoreError> {
566 text.map(|t| serde_json::from_str(&t))
567 .transpose()
568 .map_err(StoreError::backend)
569}
570
571fn to_i64(value: u64) -> Result<i64, StoreError> {
572 i64::try_from(value).map_err(StoreError::backend)
573}
574
575fn to_u64(value: i64) -> Result<u64, StoreError> {
576 u64::try_from(value).map_err(StoreError::backend)
577}
578
579fn to_ms(time: SystemTime) -> i64 {
580 match time.duration_since(UNIX_EPOCH) {
581 Ok(after) => i64::try_from(after.as_millis()).unwrap_or(i64::MAX),
582 Err(before) => -i64::try_from(before.duration().as_millis()).unwrap_or(i64::MAX),
583 }
584}
585
586fn from_ms(ms: i64) -> SystemTime {
587 if ms >= 0 {
588 UNIX_EPOCH + Duration::from_millis(ms.unsigned_abs())
589 } else {
590 UNIX_EPOCH - Duration::from_millis(ms.unsigned_abs())
591 }
592}
593
594fn round_trip(time: SystemTime) -> SystemTime {
596 from_ms(to_ms(time))
597}
598
599fn normalize(record: &mut EffectRecord) {
602 for time in [
603 &mut record.next_attempt_at,
604 &mut record.attempt_started_at,
605 &mut record.attempt_ended_at,
606 &mut record.lease_expires_at,
607 &mut record.committed_at,
608 ]
609 .into_iter()
610 .flatten()
611 {
612 *time = round_trip(*time);
613 }
614 record.created_at = round_trip(record.created_at);
615 record.updated_at = round_trip(record.updated_at);
616}
617
618#[cfg(test)]
619mod tests {
620 use super::*;
621
622 #[test]
623 fn times_round_trip_at_millisecond_precision() {
624 let t = UNIX_EPOCH + Duration::new(1_700_000_000, 123_456_789);
625 assert_eq!(
626 round_trip(t),
627 UNIX_EPOCH + Duration::new(1_700_000_000, 123_000_000)
628 );
629 let before = UNIX_EPOCH - Duration::from_millis(1500);
630 assert_eq!(round_trip(before), before);
631 }
632}