kardo_core/db/
app_db.rs

1//! Application-level database operations.
2//!
3//! Global DB at `~/Library/Application Support/com.kardo.app/app.db`.
4
5use std::path::{Path, PathBuf};
6
7use rusqlite::{params, Connection, OptionalExtension};
8use serde::{Deserialize, Serialize};
9
10use super::{apply_pragmas, migrations, DbResult};
11
12/// A recent project entry.
13#[derive(Debug, Clone, Serialize, Deserialize)]
14pub struct RecentProject {
15    pub path: String,
16    pub name: String,
17    pub last_opened: String,
18    pub score: Option<f64>,
19    pub traffic_light: Option<String>,
20}
21
22/// Application database handle.
23pub struct AppDb {
24    pub(crate) conn: Connection,
25    #[allow(dead_code)]
26    db_path: PathBuf,
27}
28
29impl AppDb {
30    /// Open or create the app database at the given path.
31    pub fn open(db_path: &Path) -> DbResult<Self> {
32        if let Some(parent) = db_path.parent() {
33            std::fs::create_dir_all(parent)?;
34        }
35
36        let conn = Connection::open(db_path)?;
37        apply_pragmas(&conn)?;
38        migrations::run_app_migrations(&conn)?;
39
40        Ok(Self {
41            conn,
42            db_path: db_path.to_path_buf(),
43        })
44    }
45
46    /// Add or update a recent project entry.
47    pub fn add_recent_project(&self, path: &str, name: &str) -> DbResult<()> {
48        self.conn.execute(
49            "INSERT INTO recent_projects (path, name)
50             VALUES (?1, ?2)
51             ON CONFLICT(path) DO UPDATE SET
52                name = excluded.name,
53                last_opened = datetime('now')",
54            params![path, name],
55        )?;
56        Ok(())
57    }
58
59    /// List recent projects ordered by last opened (most recent first).
60    pub fn list_recent_projects(&self) -> DbResult<Vec<RecentProject>> {
61        let mut stmt = self.conn.prepare(
62            "SELECT path, name, last_opened, score, traffic_light
63             FROM recent_projects ORDER BY last_opened DESC",
64        )?;
65        let rows = stmt.query_map([], |row| {
66            Ok(RecentProject {
67                path: row.get(0)?,
68                name: row.get(1)?,
69                last_opened: row.get(2)?,
70                score: row.get(3)?,
71                traffic_light: row.get(4)?,
72            })
73        })?;
74
75        let mut projects = Vec::new();
76        for row in rows {
77            projects.push(row?);
78        }
79        Ok(projects)
80    }
81
82    /// Save or update a recent project with score.
83    pub fn save_recent_project(
84        &self,
85        path: &str,
86        name: &str,
87        score: Option<f64>,
88        traffic_light: Option<&str>,
89    ) -> DbResult<()> {
90        self.conn.execute(
91            "INSERT INTO recent_projects (path, name, score, traffic_light)
92             VALUES (?1, ?2, ?3, ?4)
93             ON CONFLICT(path) DO UPDATE SET
94               name = excluded.name,
95               last_opened = datetime('now'),
96               score = excluded.score,
97               traffic_light = excluded.traffic_light",
98            params![path, name, score, traffic_light],
99        )?;
100        Ok(())
101    }
102
103    /// Get an app setting value.
104    pub fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
105        let result = self
106            .conn
107            .query_row(
108                "SELECT value FROM app_settings WHERE key = ?1",
109                params![key],
110                |row| row.get(0),
111            )
112            .optional()?;
113        Ok(result)
114    }
115
116    /// Set an app setting value.
117    pub fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
118        self.conn.execute(
119            "INSERT INTO app_settings (key, value) VALUES (?1, ?2)
120             ON CONFLICT(key) DO UPDATE SET value = excluded.value",
121            params![key, value],
122        )?;
123        Ok(())
124    }
125
126    /// Cache an LLM response.
127    pub fn cache_llm_response(
128        &self,
129        key: &str,
130        response: &str,
131        model: Option<&str>,
132        expires_at: &str,
133    ) -> DbResult<()> {
134        self.conn.execute(
135            "INSERT INTO llm_cache (key, response, model, expires_at)
136             VALUES (?1, ?2, ?3, ?4)
137             ON CONFLICT(key) DO UPDATE SET
138                response = excluded.response,
139                model = excluded.model,
140                created_at = datetime('now'),
141                expires_at = excluded.expires_at",
142            params![key, response, model, expires_at],
143        )?;
144        Ok(())
145    }
146
147    /// Get a cached LLM response (if not expired).
148    pub fn get_cached_llm_response(&self, key: &str) -> DbResult<Option<String>> {
149        let result = self
150            .conn
151            .query_row(
152                "SELECT response FROM llm_cache
153                 WHERE key = ?1 AND expires_at > datetime('now')",
154                params![key],
155                |row| row.get(0),
156            )
157            .optional()?;
158        Ok(result)
159    }
160}
kardo_core/db/app_db.rs

kardo_core/db/
app_db.rs
