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";
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 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 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 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 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
365async 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 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
552fn round_trip(time: SystemTime) -> SystemTime {
554 from_ms(to_ms(time))
555}
556
557fn 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}