zeph_memory/sqlite/
skills.rs

1// SPDX-FileCopyrightText: 2026 Andrei G <bug-ops>
2// SPDX-License-Identifier: MIT OR Apache-2.0
3
4use super::SqliteStore;
5use crate::error::MemoryError;
6
7#[derive(Debug)]
8pub struct SkillUsageRow {
9    pub skill_name: String,
10    pub invocation_count: i64,
11    pub last_used_at: String,
12}
13
14#[derive(Debug)]
15pub struct SkillMetricsRow {
16    pub skill_name: String,
17    pub version_id: Option<i64>,
18    pub total: i64,
19    pub successes: i64,
20    pub failures: i64,
21}
22
23#[derive(Debug)]
24pub struct SkillVersionRow {
25    pub id: i64,
26    pub skill_name: String,
27    pub version: i64,
28    pub body: String,
29    pub description: String,
30    pub source: String,
31    pub is_active: bool,
32    pub success_count: i64,
33    pub failure_count: i64,
34    pub created_at: String,
35}
36
37type SkillVersionTuple = (
38    i64,
39    String,
40    i64,
41    String,
42    String,
43    String,
44    i64,
45    i64,
46    i64,
47    String,
48);
49
50fn skill_version_from_tuple(t: SkillVersionTuple) -> SkillVersionRow {
51    SkillVersionRow {
52        id: t.0,
53        skill_name: t.1,
54        version: t.2,
55        body: t.3,
56        description: t.4,
57        source: t.5,
58        is_active: t.6 != 0,
59        success_count: t.7,
60        failure_count: t.8,
61        created_at: t.9,
62    }
63}
64
65impl SqliteStore {
66    /// Record usage of skills (UPSERT: increment count and update timestamp).
67    ///
68    /// # Errors
69    ///
70    /// Returns an error if the database operation fails.
71    pub async fn record_skill_usage(&self, skill_names: &[&str]) -> Result<(), MemoryError> {
72        for name in skill_names {
73            sqlx::query(
74                "INSERT INTO skill_usage (skill_name, invocation_count, last_used_at) \
75                 VALUES (?, 1, datetime('now')) \
76                 ON CONFLICT(skill_name) DO UPDATE SET \
77                 invocation_count = invocation_count + 1, \
78                 last_used_at = datetime('now')",
79            )
80            .bind(name)
81            .execute(&self.pool)
82            .await?;
83        }
84        Ok(())
85    }
86
87    /// Load all skill usage statistics.
88    ///
89    /// # Errors
90    ///
91    /// Returns an error if the query fails.
92    pub async fn load_skill_usage(&self) -> Result<Vec<SkillUsageRow>, MemoryError> {
93        let rows: Vec<(String, i64, String)> = sqlx::query_as(
94            "SELECT skill_name, invocation_count, last_used_at \
95             FROM skill_usage ORDER BY invocation_count DESC",
96        )
97        .fetch_all(&self.pool)
98        .await?;
99
100        Ok(rows
101            .into_iter()
102            .map(
103                |(skill_name, invocation_count, last_used_at)| SkillUsageRow {
104                    skill_name,
105                    invocation_count,
106                    last_used_at,
107                },
108            )
109            .collect())
110    }
111
112    /// Record a skill outcome event.
113    ///
114    /// # Errors
115    ///
116    /// Returns an error if the insert fails.
117    pub async fn record_skill_outcome(
118        &self,
119        skill_name: &str,
120        version_id: Option<i64>,
121        conversation_id: Option<crate::types::ConversationId>,
122        outcome: &str,
123        error_context: Option<&str>,
124        outcome_detail: Option<&str>,
125    ) -> Result<(), MemoryError> {
126        sqlx::query(
127            "INSERT INTO skill_outcomes \
128             (skill_name, version_id, conversation_id, outcome, error_context, outcome_detail) \
129             VALUES (?, ?, ?, ?, ?, ?)",
130        )
131        .bind(skill_name)
132        .bind(version_id)
133        .bind(conversation_id)
134        .bind(outcome)
135        .bind(error_context)
136        .bind(outcome_detail)
137        .execute(&self.pool)
138        .await?;
139        Ok(())
140    }
141
142    /// Record outcomes for multiple skills in a single transaction.
143    ///
144    /// # Errors
145    ///
146    /// Returns an error if any insert fails (whole batch is rolled back).
147    pub async fn record_skill_outcomes_batch(
148        &self,
149        skill_names: &[String],
150        conversation_id: Option<crate::types::ConversationId>,
151        outcome: &str,
152        error_context: Option<&str>,
153        outcome_detail: Option<&str>,
154    ) -> Result<(), MemoryError> {
155        // Acquire the write lock up front to avoid DEFERRED read->write upgrades
156        // failing with SQLITE_BUSY_SNAPSHOT under concurrent WAL writers.
157        let mut tx = self.pool.begin_with("BEGIN IMMEDIATE").await?;
158
159        let mut version_map: std::collections::HashMap<String, Option<i64>> =
160            std::collections::HashMap::new();
161        for name in skill_names {
162            let vid: Option<(i64,)> = sqlx::query_as(
163                "SELECT id FROM skill_versions WHERE skill_name = ? AND is_active = 1",
164            )
165            .bind(name)
166            .fetch_optional(&mut *tx)
167            .await?;
168            version_map.insert(name.clone(), vid.map(|r| r.0));
169        }
170
171        for name in skill_names {
172            let version_id = version_map.get(name.as_str()).copied().flatten();
173            sqlx::query(
174                "INSERT INTO skill_outcomes \
175                 (skill_name, version_id, conversation_id, outcome, error_context, outcome_detail) \
176                 VALUES (?, ?, ?, ?, ?, ?)",
177            )
178            .bind(name)
179            .bind(version_id)
180            .bind(conversation_id)
181            .bind(outcome)
182            .bind(error_context)
183            .bind(outcome_detail)
184            .execute(&mut *tx)
185            .await?;
186        }
187        tx.commit().await?;
188        Ok(())
189    }
190
191    /// Load metrics for a skill (latest version group).
192    ///
193    /// # Errors
194    ///
195    /// Returns an error if the query fails.
196    pub async fn skill_metrics(
197        &self,
198        skill_name: &str,
199    ) -> Result<Option<SkillMetricsRow>, MemoryError> {
200        let row: Option<(String, Option<i64>, i64, i64, i64)> = sqlx::query_as(
201            "SELECT skill_name, version_id, \
202             COUNT(*) as total, \
203             SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) as successes, \
204             COUNT(*) - SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) as failures \
205             FROM skill_outcomes WHERE skill_name = ? \
206             AND outcome NOT IN ('user_approval', 'user_rejection') \
207             GROUP BY skill_name, version_id \
208             ORDER BY version_id DESC LIMIT 1",
209        )
210        .bind(skill_name)
211        .fetch_optional(&self.pool)
212        .await?;
213
214        Ok(row.map(
215            |(skill_name, version_id, total, successes, failures)| SkillMetricsRow {
216                skill_name,
217                version_id,
218                total,
219                successes,
220                failures,
221            },
222        ))
223    }
224
225    /// Load all skill outcome stats grouped by skill name.
226    ///
227    /// # Errors
228    ///
229    /// Returns an error if the query fails.
230    pub async fn load_skill_outcome_stats(&self) -> Result<Vec<SkillMetricsRow>, MemoryError> {
231        let rows: Vec<(String, Option<i64>, i64, i64, i64)> = sqlx::query_as(
232            "SELECT skill_name, version_id, \
233             COUNT(*) as total, \
234             SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) as successes, \
235             COUNT(*) - SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) as failures \
236             FROM skill_outcomes \
237             GROUP BY skill_name \
238             ORDER BY total DESC",
239        )
240        .fetch_all(&self.pool)
241        .await?;
242
243        Ok(rows
244            .into_iter()
245            .map(
246                |(skill_name, version_id, total, successes, failures)| SkillMetricsRow {
247                    skill_name,
248                    version_id,
249                    total,
250                    successes,
251                    failures,
252                },
253            )
254            .collect())
255    }
256
257    /// Save a new skill version and return its ID.
258    ///
259    /// # Errors
260    ///
261    /// Returns an error if the insert fails.
262    #[allow(clippy::too_many_arguments)]
263    pub async fn save_skill_version(
264        &self,
265        skill_name: &str,
266        version: i64,
267        body: &str,
268        description: &str,
269        source: &str,
270        error_context: Option<&str>,
271        predecessor_id: Option<i64>,
272    ) -> Result<i64, MemoryError> {
273        let row: (i64,) = sqlx::query_as(
274            "INSERT INTO skill_versions \
275             (skill_name, version, body, description, source, error_context, predecessor_id) \
276             VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING id",
277        )
278        .bind(skill_name)
279        .bind(version)
280        .bind(body)
281        .bind(description)
282        .bind(source)
283        .bind(error_context)
284        .bind(predecessor_id)
285        .fetch_one(&self.pool)
286        .await?;
287        Ok(row.0)
288    }
289
290    /// Load the active version for a skill.
291    ///
292    /// # Errors
293    ///
294    /// Returns an error if the query fails.
295    pub async fn active_skill_version(
296        &self,
297        skill_name: &str,
298    ) -> Result<Option<SkillVersionRow>, MemoryError> {
299        let row: Option<SkillVersionTuple> = sqlx::query_as(
300            "SELECT id, skill_name, version, body, description, source, \
301                 is_active, success_count, failure_count, created_at \
302                 FROM skill_versions WHERE skill_name = ? AND is_active = 1 LIMIT 1",
303        )
304        .bind(skill_name)
305        .fetch_optional(&self.pool)
306        .await?;
307
308        Ok(row.map(skill_version_from_tuple))
309    }
310
311    /// Activate a specific version (deactivates others for the same skill).
312    ///
313    /// # Errors
314    ///
315    /// Returns an error if the update fails.
316    pub async fn activate_skill_version(
317        &self,
318        skill_name: &str,
319        version_id: i64,
320    ) -> Result<(), MemoryError> {
321        let mut tx = self.pool.begin().await?;
322
323        sqlx::query(
324            "UPDATE skill_versions SET is_active = 0 WHERE skill_name = ? AND is_active = 1",
325        )
326        .bind(skill_name)
327        .execute(&mut *tx)
328        .await?;
329
330        sqlx::query("UPDATE skill_versions SET is_active = 1 WHERE id = ?")
331            .bind(version_id)
332            .execute(&mut *tx)
333            .await?;
334
335        tx.commit().await?;
336        Ok(())
337    }
338
339    /// Get the next version number for a skill.
340    ///
341    /// # Errors
342    ///
343    /// Returns an error if the query fails.
344    pub async fn next_skill_version(&self, skill_name: &str) -> Result<i64, MemoryError> {
345        let row: (i64,) = sqlx::query_as(
346            "SELECT COALESCE(MAX(version), 0) + 1 FROM skill_versions WHERE skill_name = ?",
347        )
348        .bind(skill_name)
349        .fetch_one(&self.pool)
350        .await?;
351        Ok(row.0)
352    }
353
354    /// Get the latest auto-generated version's `created_at` for cooldown check.
355    ///
356    /// # Errors
357    ///
358    /// Returns an error if the query fails.
359    pub async fn last_improvement_time(
360        &self,
361        skill_name: &str,
362    ) -> Result<Option<String>, MemoryError> {
363        let row: Option<(String,)> = sqlx::query_as(
364            "SELECT created_at FROM skill_versions \
365             WHERE skill_name = ? AND source = 'auto' \
366             ORDER BY id DESC LIMIT 1",
367        )
368        .bind(skill_name)
369        .fetch_optional(&self.pool)
370        .await?;
371        Ok(row.map(|r| r.0))
372    }
373
374    /// Ensure a base (v1 manual) version exists for a skill. Idempotent.
375    ///
376    /// # Errors
377    ///
378    /// Returns an error if the DB operation fails.
379    pub async fn ensure_skill_version_exists(
380        &self,
381        skill_name: &str,
382        body: &str,
383        description: &str,
384    ) -> Result<(), MemoryError> {
385        let existing: Option<(i64,)> =
386            sqlx::query_as("SELECT id FROM skill_versions WHERE skill_name = ? LIMIT 1")
387                .bind(skill_name)
388                .fetch_optional(&self.pool)
389                .await?;
390
391        if existing.is_none() {
392            let id = self
393                .save_skill_version(skill_name, 1, body, description, "manual", None, None)
394                .await?;
395            self.activate_skill_version(skill_name, id).await?;
396        }
397        Ok(())
398    }
399
400    /// Load all versions for a skill, ordered by version number.
401    ///
402    /// # Errors
403    ///
404    /// Returns an error if the query fails.
405    pub async fn load_skill_versions(
406        &self,
407        skill_name: &str,
408    ) -> Result<Vec<SkillVersionRow>, MemoryError> {
409        let rows: Vec<SkillVersionTuple> = sqlx::query_as(
410            "SELECT id, skill_name, version, body, description, source, \
411                 is_active, success_count, failure_count, created_at \
412                 FROM skill_versions WHERE skill_name = ? ORDER BY version ASC",
413        )
414        .bind(skill_name)
415        .fetch_all(&self.pool)
416        .await?;
417
418        Ok(rows.into_iter().map(skill_version_from_tuple).collect())
419    }
420
421    /// Count auto-generated versions for a skill.
422    ///
423    /// # Errors
424    ///
425    /// Returns an error if the query fails.
426    pub async fn count_auto_versions(&self, skill_name: &str) -> Result<i64, MemoryError> {
427        let row: (i64,) = sqlx::query_as(
428            "SELECT COUNT(*) FROM skill_versions WHERE skill_name = ? AND source = 'auto'",
429        )
430        .bind(skill_name)
431        .fetch_one(&self.pool)
432        .await?;
433        Ok(row.0)
434    }
435
436    /// Delete oldest non-active auto versions exceeding max limit.
437    /// Returns the number of pruned versions.
438    ///
439    /// # Errors
440    ///
441    /// Returns an error if the delete fails.
442    pub async fn prune_skill_versions(
443        &self,
444        skill_name: &str,
445        max_versions: u32,
446    ) -> Result<u32, MemoryError> {
447        let result = sqlx::query(
448            "DELETE FROM skill_versions WHERE id IN (\
449                SELECT id FROM skill_versions \
450                WHERE skill_name = ? AND source = 'auto' AND is_active = 0 \
451                ORDER BY id ASC \
452                LIMIT max(0, (SELECT COUNT(*) FROM skill_versions \
453                    WHERE skill_name = ? AND source = 'auto') - ?)\
454            )",
455        )
456        .bind(skill_name)
457        .bind(skill_name)
458        .bind(max_versions)
459        .execute(&self.pool)
460        .await?;
461        Ok(u32::try_from(result.rows_affected()).unwrap_or(0))
462    }
463
464    /// Get the predecessor version for rollback.
465    ///
466    /// # Errors
467    ///
468    /// Returns an error if the query fails.
469    pub async fn predecessor_version(
470        &self,
471        version_id: i64,
472    ) -> Result<Option<SkillVersionRow>, MemoryError> {
473        let pred_id: Option<(Option<i64>,)> =
474            sqlx::query_as("SELECT predecessor_id FROM skill_versions WHERE id = ?")
475                .bind(version_id)
476                .fetch_optional(&self.pool)
477                .await?;
478
479        let Some((Some(pid),)) = pred_id else {
480            return Ok(None);
481        };
482
483        let row: Option<SkillVersionTuple> = sqlx::query_as(
484            "SELECT id, skill_name, version, body, description, source, \
485                 is_active, success_count, failure_count, created_at \
486                 FROM skill_versions WHERE id = ?",
487        )
488        .bind(pid)
489        .fetch_optional(&self.pool)
490        .await?;
491
492        Ok(row.map(skill_version_from_tuple))
493    }
494
495    /// Return the skill names for all currently active auto-generated versions.
496    ///
497    /// Used to check rollback eligibility at the start of each agent turn.
498    ///
499    /// # Errors
500    /// Returns [`MemoryError`] on `SQLite` query failure.
501    pub async fn list_active_auto_versions(&self) -> Result<Vec<String>, MemoryError> {
502        let rows: Vec<(String,)> = sqlx::query_as(
503            "SELECT skill_name FROM skill_versions WHERE is_active = 1 AND source = 'auto'",
504        )
505        .fetch_all(&self.pool)
506        .await?;
507        Ok(rows.into_iter().map(|(name,)| name).collect())
508    }
509}
510
511#[cfg(test)]
512mod tests {
513    use std::time::Duration;
514
515    use sqlx::Connection;
516    use tokio::time::sleep;
517
518    use super::*;
519
520    async fn test_store() -> SqliteStore {
521        SqliteStore::new(":memory:").await.unwrap()
522    }
523
524    #[tokio::test]
525    async fn record_skill_usage_increments() {
526        let store = test_store().await;
527
528        store.record_skill_usage(&["git"]).await.unwrap();
529        store.record_skill_usage(&["git"]).await.unwrap();
530
531        let usage = store.load_skill_usage().await.unwrap();
532        assert_eq!(usage.len(), 1);
533        assert_eq!(usage[0].skill_name, "git");
534        assert_eq!(usage[0].invocation_count, 2);
535    }
536
537    #[tokio::test]
538    async fn load_skill_usage_returns_all() {
539        let store = test_store().await;
540
541        store.record_skill_usage(&["git", "docker"]).await.unwrap();
542        store.record_skill_usage(&["git"]).await.unwrap();
543
544        let usage = store.load_skill_usage().await.unwrap();
545        assert_eq!(usage.len(), 2);
546        assert_eq!(usage[0].skill_name, "git");
547        assert_eq!(usage[0].invocation_count, 2);
548        assert_eq!(usage[1].skill_name, "docker");
549        assert_eq!(usage[1].invocation_count, 1);
550    }
551
552    #[tokio::test]
553    async fn migration_005_creates_tables() {
554        let store = test_store().await;
555        let pool = store.pool();
556
557        let versions: (i64,) = sqlx::query_as(
558            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='skill_versions'",
559        )
560        .fetch_one(pool)
561        .await
562        .unwrap();
563        assert_eq!(versions.0, 1);
564
565        let outcomes: (i64,) = sqlx::query_as(
566            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='skill_outcomes'",
567        )
568        .fetch_one(pool)
569        .await
570        .unwrap();
571        assert_eq!(outcomes.0, 1);
572    }
573
574    #[tokio::test]
575    async fn record_skill_outcome_inserts() {
576        let store = test_store().await;
577
578        store
579            .record_skill_outcome(
580                "git",
581                None,
582                Some(crate::types::ConversationId(1)),
583                "success",
584                None,
585                None,
586            )
587            .await
588            .unwrap();
589        store
590            .record_skill_outcome(
591                "git",
592                None,
593                Some(crate::types::ConversationId(1)),
594                "tool_failure",
595                Some("exit code 1"),
596                None,
597            )
598            .await
599            .unwrap();
600
601        let metrics = store.skill_metrics("git").await.unwrap().unwrap();
602        assert_eq!(metrics.total, 2);
603        assert_eq!(metrics.successes, 1);
604        assert_eq!(metrics.failures, 1);
605    }
606
607    #[tokio::test]
608    async fn skill_metrics_none_for_unknown() {
609        let store = test_store().await;
610        let m = store.skill_metrics("nonexistent").await.unwrap();
611        assert!(m.is_none());
612    }
613
614    #[tokio::test]
615    async fn load_skill_outcome_stats_grouped() {
616        let store = test_store().await;
617
618        store
619            .record_skill_outcome("git", None, None, "success", None, None)
620            .await
621            .unwrap();
622        store
623            .record_skill_outcome("git", None, None, "tool_failure", None, None)
624            .await
625            .unwrap();
626        store
627            .record_skill_outcome("docker", None, None, "success", None, None)
628            .await
629            .unwrap();
630
631        let stats = store.load_skill_outcome_stats().await.unwrap();
632        assert_eq!(stats.len(), 2);
633        assert_eq!(stats[0].skill_name, "git");
634        assert_eq!(stats[0].total, 2);
635        assert_eq!(stats[1].skill_name, "docker");
636        assert_eq!(stats[1].total, 1);
637    }
638
639    #[tokio::test]
640    async fn save_and_load_skill_version() {
641        let store = test_store().await;
642
643        let id = store
644            .save_skill_version("git", 1, "body v1", "Git helper", "manual", None, None)
645            .await
646            .unwrap();
647        assert!(id > 0);
648
649        store.activate_skill_version("git", id).await.unwrap();
650
651        let active = store.active_skill_version("git").await.unwrap().unwrap();
652        assert_eq!(active.version, 1);
653        assert_eq!(active.body, "body v1");
654        assert!(active.is_active);
655    }
656
657    #[tokio::test]
658    async fn activate_deactivates_previous() {
659        let store = test_store().await;
660
661        let v1 = store
662            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
663            .await
664            .unwrap();
665        store.activate_skill_version("git", v1).await.unwrap();
666
667        let v2 = store
668            .save_skill_version("git", 2, "v2", "desc", "auto", None, Some(v1))
669            .await
670            .unwrap();
671        store.activate_skill_version("git", v2).await.unwrap();
672
673        let versions = store.load_skill_versions("git").await.unwrap();
674        assert_eq!(versions.len(), 2);
675        assert!(!versions[0].is_active);
676        assert!(versions[1].is_active);
677    }
678
679    #[tokio::test]
680    async fn next_skill_version_increments() {
681        let store = test_store().await;
682
683        let next = store.next_skill_version("git").await.unwrap();
684        assert_eq!(next, 1);
685
686        store
687            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
688            .await
689            .unwrap();
690        let next = store.next_skill_version("git").await.unwrap();
691        assert_eq!(next, 2);
692    }
693
694    #[tokio::test]
695    async fn last_improvement_time_returns_auto_only() {
696        let store = test_store().await;
697
698        store
699            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
700            .await
701            .unwrap();
702
703        let t = store.last_improvement_time("git").await.unwrap();
704        assert!(t.is_none());
705
706        store
707            .save_skill_version("git", 2, "v2", "desc", "auto", None, None)
708            .await
709            .unwrap();
710
711        let t = store.last_improvement_time("git").await.unwrap();
712        assert!(t.is_some());
713    }
714
715    #[tokio::test]
716    async fn ensure_skill_version_exists_idempotent() {
717        let store = test_store().await;
718
719        store
720            .ensure_skill_version_exists("git", "body", "Git helper")
721            .await
722            .unwrap();
723        store
724            .ensure_skill_version_exists("git", "body2", "Git helper 2")
725            .await
726            .unwrap();
727
728        let versions = store.load_skill_versions("git").await.unwrap();
729        assert_eq!(versions.len(), 1);
730        assert_eq!(versions[0].body, "body");
731    }
732
733    #[tokio::test]
734    async fn load_skill_versions_ordered() {
735        let store = test_store().await;
736
737        let v1 = store
738            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
739            .await
740            .unwrap();
741        store
742            .save_skill_version("git", 2, "v2", "desc", "auto", None, Some(v1))
743            .await
744            .unwrap();
745
746        let versions = store.load_skill_versions("git").await.unwrap();
747        assert_eq!(versions.len(), 2);
748        assert_eq!(versions[0].version, 1);
749        assert_eq!(versions[1].version, 2);
750    }
751
752    #[tokio::test]
753    async fn count_auto_versions_only() {
754        let store = test_store().await;
755
756        store
757            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
758            .await
759            .unwrap();
760        store
761            .save_skill_version("git", 2, "v2", "desc", "auto", None, None)
762            .await
763            .unwrap();
764        store
765            .save_skill_version("git", 3, "v3", "desc", "auto", None, None)
766            .await
767            .unwrap();
768
769        let count = store.count_auto_versions("git").await.unwrap();
770        assert_eq!(count, 2);
771    }
772
773    #[tokio::test]
774    async fn prune_preserves_manual_and_active() {
775        let store = test_store().await;
776
777        let v1 = store
778            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
779            .await
780            .unwrap();
781        store.activate_skill_version("git", v1).await.unwrap();
782
783        for i in 2..=5 {
784            store
785                .save_skill_version("git", i, &format!("v{i}"), "desc", "auto", None, None)
786                .await
787                .unwrap();
788        }
789
790        let pruned = store.prune_skill_versions("git", 2).await.unwrap();
791        assert_eq!(pruned, 2);
792
793        let versions = store.load_skill_versions("git").await.unwrap();
794        assert!(versions.iter().any(|v| v.source == "manual"));
795        let auto_count = versions.iter().filter(|v| v.source == "auto").count();
796        assert_eq!(auto_count, 2);
797    }
798
799    #[tokio::test]
800    async fn predecessor_version_returns_parent() {
801        let store = test_store().await;
802
803        let v1 = store
804            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
805            .await
806            .unwrap();
807        let v2 = store
808            .save_skill_version("git", 2, "v2", "desc", "auto", None, Some(v1))
809            .await
810            .unwrap();
811
812        let pred = store.predecessor_version(v2).await.unwrap().unwrap();
813        assert_eq!(pred.id, v1);
814        assert_eq!(pred.version, 1);
815    }
816
817    #[tokio::test]
818    async fn predecessor_version_none_for_root() {
819        let store = test_store().await;
820
821        let v1 = store
822            .save_skill_version("git", 1, "v1", "desc", "manual", None, None)
823            .await
824            .unwrap();
825
826        let pred = store.predecessor_version(v1).await.unwrap();
827        assert!(pred.is_none());
828    }
829
830    #[tokio::test]
831    async fn active_skill_version_none_for_unknown() {
832        let store = test_store().await;
833        let active = store.active_skill_version("nonexistent").await.unwrap();
834        assert!(active.is_none());
835    }
836
837    #[tokio::test]
838    async fn load_skill_outcome_stats_empty() {
839        let store = test_store().await;
840        let stats = store.load_skill_outcome_stats().await.unwrap();
841        assert!(stats.is_empty());
842    }
843
844    #[tokio::test]
845    async fn load_skill_versions_empty() {
846        let store = test_store().await;
847        let versions = store.load_skill_versions("nonexistent").await.unwrap();
848        assert!(versions.is_empty());
849    }
850
851    #[tokio::test]
852    async fn count_auto_versions_zero_for_unknown() {
853        let store = test_store().await;
854        let count = store.count_auto_versions("nonexistent").await.unwrap();
855        assert_eq!(count, 0);
856    }
857
858    #[tokio::test]
859    async fn prune_nothing_when_below_limit() {
860        let store = test_store().await;
861
862        store
863            .save_skill_version("git", 1, "v1", "desc", "auto", None, None)
864            .await
865            .unwrap();
866
867        let pruned = store.prune_skill_versions("git", 5).await.unwrap();
868        assert_eq!(pruned, 0);
869    }
870
871    #[tokio::test]
872    async fn record_skill_outcome_with_error_context() {
873        let store = test_store().await;
874
875        store
876            .record_skill_outcome(
877                "docker",
878                None,
879                Some(crate::types::ConversationId(1)),
880                "tool_failure",
881                Some("container not found"),
882                None,
883            )
884            .await
885            .unwrap();
886
887        let metrics = store.skill_metrics("docker").await.unwrap().unwrap();
888        assert_eq!(metrics.total, 1);
889        assert_eq!(metrics.failures, 1);
890    }
891
892    #[tokio::test]
893    async fn save_skill_version_with_error_context() {
894        let store = test_store().await;
895
896        let id = store
897            .save_skill_version(
898                "git",
899                1,
900                "improved body",
901                "Git helper",
902                "auto",
903                Some("exit code 128"),
904                None,
905            )
906            .await
907            .unwrap();
908        assert!(id > 0);
909    }
910
911    #[tokio::test]
912    async fn record_skill_outcomes_batch_resolves_version_id() {
913        let store = test_store().await;
914
915        let vid = store
916            .save_skill_version("git", 1, "body", "desc", "manual", None, None)
917            .await
918            .unwrap();
919        store.activate_skill_version("git", vid).await.unwrap();
920
921        store
922            .record_skill_outcomes_batch(
923                &["git".to_string()],
924                None,
925                "tool_failure",
926                Some("exit code 1"),
927                Some("exit_nonzero"),
928            )
929            .await
930            .unwrap();
931
932        let pool = store.pool();
933        let row: (Option<i64>, Option<String>) =
934            sqlx::query_as("SELECT version_id, outcome_detail FROM skill_outcomes WHERE skill_name = 'git' LIMIT 1")
935                .fetch_one(pool)
936                .await
937                .unwrap();
938        assert_eq!(
939            row.0,
940            Some(vid),
941            "version_id should be resolved to active version"
942        );
943        assert_eq!(row.1.as_deref(), Some("exit_nonzero"));
944    }
945
946    #[tokio::test]
947    async fn record_skill_outcome_stores_outcome_detail() {
948        let store = test_store().await;
949
950        store
951            .record_skill_outcome("docker", None, None, "tool_failure", None, Some("timeout"))
952            .await
953            .unwrap();
954
955        let pool = store.pool();
956        let row: (Option<String>,) = sqlx::query_as(
957            "SELECT outcome_detail FROM skill_outcomes WHERE skill_name = 'docker' LIMIT 1",
958        )
959        .fetch_one(pool)
960        .await
961        .unwrap();
962        assert_eq!(row.0.as_deref(), Some("timeout"));
963    }
964
965    #[tokio::test]
966    async fn record_skill_outcomes_batch_waits_for_active_writer() {
967        let file = tempfile::NamedTempFile::new().expect("tempfile");
968        let path = file.path().to_str().expect("valid path").to_owned();
969        let store = SqliteStore::with_pool_size(&path, 2)
970            .await
971            .expect("with_pool_size");
972
973        let vid = store
974            .save_skill_version("git", 1, "body", "desc", "manual", None, None)
975            .await
976            .unwrap();
977        store.activate_skill_version("git", vid).await.unwrap();
978
979        let mut writer = store.pool().acquire().await.expect("acquire writer");
980        let mut writer_tx = writer
981            .begin_with("BEGIN IMMEDIATE")
982            .await
983            .expect("begin immediate");
984        sqlx::query("INSERT INTO conversations DEFAULT VALUES")
985            .execute(&mut *writer_tx)
986            .await
987            .expect("hold write lock");
988
989        let batch_store = store.clone();
990        let batch = tokio::spawn(async move {
991            batch_store
992                .record_skill_outcomes_batch(
993                    &["git".to_string()],
994                    None,
995                    "success",
996                    None,
997                    Some("waited_for_writer"),
998                )
999                .await
1000        });
1001
1002        sleep(Duration::from_millis(100)).await;
1003        writer_tx.commit().await.expect("commit writer");
1004
1005        batch
1006            .await
1007            .expect("join batch task")
1008            .expect("record outcomes");
1009
1010        let count: i64 =
1011            sqlx::query_scalar("SELECT COUNT(*) FROM skill_outcomes WHERE skill_name = 'git'")
1012                .fetch_one(store.pool())
1013                .await
1014                .unwrap();
1015        assert_eq!(
1016            count, 1,
1017            "expected batch insert to succeed after writer commits"
1018        );
1019    }
1020}
zeph_memory/sqlite/skills.rs

zeph_memory/sqlite/
skills.rs
