1#![allow(dead_code)]
11
12use anyhow::{Context, Result};
31use chrono::Utc;
32use rusqlite::{params, Connection, OptionalExtension};
33use serde::{Deserialize, Serialize};
34use std::path::Path;
35
36pub const SCHEMA_VERSION: &str = "3.0";
38
39#[derive(Debug, Clone, Serialize, Deserialize)]
45pub struct Workspace {
46 pub id: String,
47 pub name: String,
48 pub path: Option<String>,
49 pub provider: String,
50 pub provider_workspace_id: Option<String>,
51 pub created_at: i64,
52 pub updated_at: i64,
53 pub metadata: Option<String>, }
55
56#[derive(Debug, Clone, Serialize, Deserialize)]
58pub struct Session {
59 pub id: String,
60 pub workspace_id: Option<String>,
61 pub provider: String,
62 pub provider_session_id: Option<String>,
63 pub title: String,
64 pub model: Option<String>,
65 pub message_count: i32,
66 pub token_count: Option<i64>,
67 pub created_at: i64,
68 pub updated_at: i64,
69 pub archived: bool,
70 pub metadata: Option<String>, }
72
73#[derive(Debug, Clone, Serialize, Deserialize)]
75pub struct Message {
76 pub id: String,
77 pub session_id: String,
78 pub role: String, pub content: String,
80 pub model: Option<String>,
81 pub token_count: Option<i32>,
82 pub created_at: i64,
83 pub parent_id: Option<String>, pub metadata: Option<String>,
85}
86
87#[derive(Debug, Clone, Serialize, Deserialize)]
89pub struct Checkpoint {
90 pub id: String,
91 pub session_id: String,
92 pub name: String,
93 pub description: Option<String>,
94 pub message_count: i32,
95 pub session_snapshot: String, pub created_at: i64,
97 pub git_commit: Option<String>,
98}
99
100#[derive(Debug, Clone, Serialize, Deserialize)]
102pub struct ShareLink {
103 pub id: String,
104 pub session_id: Option<String>, pub provider: String,
106 pub url: String,
107 pub share_id: String, pub title: Option<String>,
109 pub imported: bool,
110 pub imported_at: Option<i64>,
111 pub created_at: i64,
112 pub metadata: Option<String>,
113}
114
115#[derive(Debug, Clone, PartialEq, Eq)]
117pub enum ShareLinkProvider {
118 ChatGPT,
119 Claude,
120 Gemini,
121 Perplexity,
122 Poe,
123 Other(String),
124}
125
126impl ShareLinkProvider {
127 pub fn from_url(url: &str) -> Option<(Self, String)> {
129 let url_lower = url.to_lowercase();
130
131 if url_lower.contains("chat.openai.com/share/") || url_lower.contains("chatgpt.com/share/")
133 {
134 if let Some(id) = extract_path_segment(url, "share") {
135 return Some((ShareLinkProvider::ChatGPT, id));
136 }
137 }
138
139 if url_lower.contains("claude.ai/share/") {
141 if let Some(id) = extract_path_segment(url, "share") {
142 return Some((ShareLinkProvider::Claude, id));
143 }
144 }
145
146 if url_lower.contains("g.co/gemini/share/")
148 || url_lower.contains("gemini.google.com/share/")
149 {
150 if let Some(id) = extract_path_segment(url, "share") {
151 return Some((ShareLinkProvider::Gemini, id));
152 }
153 }
154
155 if url_lower.contains("perplexity.ai/search/") {
157 if let Some(id) = extract_path_segment(url, "search") {
158 return Some((ShareLinkProvider::Perplexity, id));
159 }
160 }
161
162 if url_lower.contains("poe.com/s/") {
164 if let Some(id) = extract_path_segment(url, "s") {
165 return Some((ShareLinkProvider::Poe, id));
166 }
167 }
168
169 None
170 }
171
172 pub fn name(&self) -> &str {
173 match self {
174 ShareLinkProvider::ChatGPT => "ChatGPT",
175 ShareLinkProvider::Claude => "Claude",
176 ShareLinkProvider::Gemini => "Gemini",
177 ShareLinkProvider::Perplexity => "Perplexity",
178 ShareLinkProvider::Poe => "Poe",
179 ShareLinkProvider::Other(name) => name,
180 }
181 }
182}
183
184#[derive(Debug, Clone)]
186pub struct ShareLinkInfo {
187 pub provider: String,
188 pub share_id: String,
189}
190
191pub struct ShareLinkParser;
193
194impl ShareLinkParser {
195 pub fn parse(url: &str) -> Option<ShareLinkInfo> {
197 ShareLinkProvider::from_url(url).map(|(provider, share_id)| ShareLinkInfo {
198 provider: provider.name().to_string(),
199 share_id,
200 })
201 }
202}
203
204fn extract_path_segment(url: &str, key: &str) -> Option<String> {
206 let parts: Vec<&str> = url.split('/').collect();
207 for (i, part) in parts.iter().enumerate() {
208 if *part == key && i + 1 < parts.len() {
209 let id = parts[i + 1].split('?').next().unwrap_or(parts[i + 1]);
210 if !id.is_empty() {
211 return Some(id.to_string());
212 }
213 }
214 }
215 None
216}
217
218pub struct ChatDatabase {
224 pub conn: Connection,
225}
226
227impl ChatDatabase {
228 pub fn open(path: &Path) -> Result<Self> {
230 let conn = Connection::open(path).context("Failed to open database")?;
231
232 let db = ChatDatabase { conn };
233 db.initialize()?;
234
235 Ok(db)
236 }
237
238 #[allow(dead_code)]
240 pub fn open_in_memory() -> Result<Self> {
241 let conn = Connection::open_in_memory().context("Failed to create in-memory database")?;
242
243 let db = ChatDatabase { conn };
244 db.initialize()?;
245
246 Ok(db)
247 }
248
249 fn initialize(&self) -> Result<()> {
251 let sessions_table_exists = self
261 .conn
262 .query_row(
263 "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='sessions'",
264 [],
265 |row| row.get::<_, i64>(0),
266 )
267 .map(|n| n > 0)
268 .unwrap_or(false);
269
270 let has_model_column = sessions_table_exists
271 && self
272 .conn
273 .prepare("SELECT * FROM pragma_table_info('sessions') WHERE name = 'model'")
274 .and_then(|mut stmt| stmt.exists([]))
275 .unwrap_or(false);
276
277 let is_harvest_db = sessions_table_exists;
278
279 if !is_harvest_db || has_model_column {
281 self.conn
282 .execute_batch(include_str!("sql/schema.sql"))
283 .context("Failed to initialize database schema")?;
284 }
285 else {
288 self.conn
290 .execute_batch(
291 r#"
292 -- Metadata table for version tracking
293 CREATE TABLE IF NOT EXISTS metadata (
294 key TEXT PRIMARY KEY,
295 value TEXT NOT NULL,
296 updated_at INTEGER DEFAULT (strftime('%s', 'now'))
297 );
298 INSERT OR IGNORE INTO metadata (key, value) VALUES ('schema_version', 'harvest');
299
300 -- Agents table for agent management
301 CREATE TABLE IF NOT EXISTS agents (
302 id TEXT PRIMARY KEY,
303 name TEXT NOT NULL UNIQUE,
304 description TEXT,
305 instruction TEXT NOT NULL,
306 role TEXT DEFAULT 'assistant',
307 model TEXT,
308 provider TEXT,
309 temperature REAL DEFAULT 0.7,
310 max_tokens INTEGER,
311 tools TEXT,
312 sub_agents TEXT,
313 is_active INTEGER DEFAULT 1,
314 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
315 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
316 metadata TEXT
317 );
318
319 -- Workspaces. The harvest schema has no such table, so
320 -- `GET /api/workspaces` answered 500 against every harvest
321 -- database: its query joins `workspaces` and the table simply
322 -- was not there. Columns match sql/schema.sql so the two
323 -- schemas stay compatible.
324 CREATE TABLE IF NOT EXISTS workspaces (
325 id TEXT PRIMARY KEY,
326 name TEXT NOT NULL,
327 path TEXT,
328 provider TEXT,
329 provider_workspace_id TEXT,
330 git_repo TEXT,
331 git_branch TEXT,
332 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
333 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
334 metadata TEXT
335 );
336 "#,
337 )
338 .context("Failed to initialize harvest-compatible schema")?;
339 }
340 Ok(())
341 }
342
343 pub fn connection(&self) -> &Connection {
345 &self.conn
346 }
347
348 pub fn get_version(&self) -> Result<String> {
350 let version: String = self
351 .conn
352 .query_row(
353 "SELECT value FROM metadata WHERE key = 'schema_version'",
354 [],
355 |row| row.get(0),
356 )
357 .unwrap_or_else(|_| "unknown".to_string());
358 Ok(version)
359 }
360
361 pub fn upsert_workspace(&self, workspace: &Workspace) -> Result<()> {
367 self.conn.execute(
368 r#"
369 INSERT INTO workspaces (id, name, path, provider, provider_workspace_id,
370 created_at, updated_at, metadata)
371 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
372 ON CONFLICT(id) DO UPDATE SET
373 name = excluded.name,
374 path = excluded.path,
375 updated_at = excluded.updated_at,
376 metadata = excluded.metadata
377 "#,
378 params![
379 workspace.id,
380 workspace.name,
381 workspace.path,
382 workspace.provider,
383 workspace.provider_workspace_id,
384 workspace.created_at,
385 workspace.updated_at,
386 workspace.metadata,
387 ],
388 )?;
389 Ok(())
390 }
391
392 pub fn get_workspace(&self, id: &str) -> Result<Option<Workspace>> {
394 self.conn
395 .query_row(
396 "SELECT id, name, path, provider, provider_workspace_id, created_at, updated_at, metadata
397 FROM workspaces WHERE id = ?",
398 [id],
399 |row| {
400 Ok(Workspace {
401 id: row.get(0)?,
402 name: row.get(1)?,
403 path: row.get(2)?,
404 provider: row.get(3)?,
405 provider_workspace_id: row.get(4)?,
406 created_at: row.get(5)?,
407 updated_at: row.get(6)?,
408 metadata: row.get(7)?,
409 })
410 },
411 )
412 .optional()
413 .context("Failed to get workspace")
414 }
415
416 pub fn list_workspaces(&self) -> Result<Vec<Workspace>> {
418 let mut stmt = self.conn.prepare(
419 "SELECT id, name, path, provider, provider_workspace_id, created_at, updated_at, metadata
420 FROM workspaces ORDER BY updated_at DESC"
421 )?;
422
423 let workspaces = stmt
424 .query_map([], |row| {
425 Ok(Workspace {
426 id: row.get(0)?,
427 name: row.get(1)?,
428 path: row.get(2)?,
429 provider: row.get(3)?,
430 provider_workspace_id: row.get(4)?,
431 created_at: row.get(5)?,
432 updated_at: row.get(6)?,
433 metadata: row.get(7)?,
434 })
435 })?
436 .collect::<Result<Vec<_>, _>>()?;
437
438 Ok(workspaces)
439 }
440
441 pub fn upsert_session(&self, session: &Session) -> Result<bool> {
447 let existing = self.get_session(&session.id)?;
448
449 self.conn.execute(
450 r#"
451 INSERT INTO sessions (id, workspace_id, provider, provider_session_id, title,
452 model, message_count, token_count, created_at, updated_at,
453 archived, metadata)
454 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
455 ON CONFLICT(id) DO UPDATE SET
456 title = excluded.title,
457 model = excluded.model,
458 message_count = excluded.message_count,
459 token_count = excluded.token_count,
460 updated_at = excluded.updated_at,
461 archived = excluded.archived,
462 metadata = excluded.metadata
463 "#,
464 params![
465 session.id,
466 session.workspace_id,
467 session.provider,
468 session.provider_session_id,
469 session.title,
470 session.model,
471 session.message_count,
472 session.token_count,
473 session.created_at,
474 session.updated_at,
475 session.archived,
476 session.metadata,
477 ],
478 )?;
479
480 Ok(existing.is_some())
481 }
482
483 pub fn get_session(&self, id: &str) -> Result<Option<Session>> {
485 self.conn
486 .query_row(
487 "SELECT id, workspace_id, provider, provider_session_id, title, model,
488 message_count, token_count, created_at, updated_at, archived, metadata
489 FROM sessions WHERE id = ?",
490 [id],
491 |row| {
492 Ok(Session {
493 id: row.get(0)?,
494 workspace_id: row.get(1)?,
495 provider: row.get(2)?,
496 provider_session_id: row.get(3)?,
497 title: row.get(4)?,
498 model: row.get(5)?,
499 message_count: row.get(6)?,
500 token_count: row.get(7)?,
501 created_at: row.get(8)?,
502 updated_at: row.get(9)?,
503 archived: row.get(10)?,
504 metadata: row.get(11)?,
505 })
506 },
507 )
508 .optional()
509 .context("Failed to get session")
510 }
511
512 pub fn list_sessions(
514 &self,
515 workspace_id: Option<&str>,
516 provider: Option<&str>,
517 limit: usize,
518 ) -> Result<Vec<Session>> {
519 let mut query = String::from(
520 "SELECT id, workspace_id, provider, provider_session_id, title, model,
521 message_count, token_count, created_at, updated_at, archived, metadata
522 FROM sessions WHERE 1=1",
523 );
524
525 if workspace_id.is_some() {
526 query.push_str(" AND workspace_id = ?1");
527 }
528 if provider.is_some() {
529 query.push_str(" AND provider = ?2");
530 }
531 query.push_str(" ORDER BY updated_at DESC LIMIT ?3");
532
533 let mut stmt = self.conn.prepare(&query)?;
534
535 let sessions = stmt
536 .query_map(
537 params![
538 workspace_id.unwrap_or(""),
539 provider.unwrap_or(""),
540 limit as i64,
541 ],
542 |row| {
543 Ok(Session {
544 id: row.get(0)?,
545 workspace_id: row.get(1)?,
546 provider: row.get(2)?,
547 provider_session_id: row.get(3)?,
548 title: row.get(4)?,
549 model: row.get(5)?,
550 message_count: row.get(6)?,
551 token_count: row.get(7)?,
552 created_at: row.get(8)?,
553 updated_at: row.get(9)?,
554 archived: row.get(10)?,
555 metadata: row.get(11)?,
556 })
557 },
558 )?
559 .collect::<Result<Vec<_>, _>>()?;
560
561 Ok(sessions)
562 }
563
564 pub fn count_sessions_by_provider(&self) -> Result<Vec<(String, i64)>> {
566 let mut stmt = self.conn.prepare(
567 "SELECT provider, COUNT(*) FROM sessions GROUP BY provider ORDER BY COUNT(*) DESC",
568 )?;
569
570 let counts = stmt
571 .query_map([], |row| Ok((row.get(0)?, row.get(1)?)))?
572 .collect::<Result<Vec<_>, _>>()?;
573
574 Ok(counts)
575 }
576
577 pub fn insert_message(&self, message: &Message) -> Result<()> {
583 self.conn.execute(
584 r#"
585 INSERT INTO messages (id, session_id, role, content, model, token_count,
586 created_at, parent_id, metadata)
587 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
588 ON CONFLICT(id) DO UPDATE SET
589 content = excluded.content,
590 metadata = excluded.metadata
591 "#,
592 params![
593 message.id,
594 message.session_id,
595 message.role,
596 message.content,
597 message.model,
598 message.token_count,
599 message.created_at,
600 message.parent_id,
601 message.metadata,
602 ],
603 )?;
604 Ok(())
605 }
606
607 pub fn get_messages(&self, session_id: &str) -> Result<Vec<Message>> {
609 let mut stmt = self.conn.prepare(
610 "SELECT id, session_id, role, content, model, token_count, created_at, parent_id, metadata
611 FROM messages WHERE session_id = ? ORDER BY created_at ASC"
612 )?;
613
614 let messages = stmt
615 .query_map([session_id], |row| {
616 Ok(Message {
617 id: row.get(0)?,
618 session_id: row.get(1)?,
619 role: row.get(2)?,
620 content: row.get(3)?,
621 model: row.get(4)?,
622 token_count: row.get(5)?,
623 created_at: row.get(6)?,
624 parent_id: row.get(7)?,
625 metadata: row.get(8)?,
626 })
627 })?
628 .collect::<Result<Vec<_>, _>>()?;
629
630 Ok(messages)
631 }
632
633 pub fn create_checkpoint(
639 &self,
640 session_id: &str,
641 name: &str,
642 description: Option<&str>,
643 git_commit: Option<&str>,
644 ) -> Result<Checkpoint> {
645 let session = self.get_session(session_id)?.context("Session not found")?;
646
647 let messages = self.get_messages(session_id)?;
648
649 let snapshot = serde_json::json!({
650 "session": session,
651 "messages": messages,
652 });
653
654 let checkpoint = Checkpoint {
655 id: uuid::Uuid::new_v4().to_string(),
656 session_id: session_id.to_string(),
657 name: name.to_string(),
658 description: description.map(String::from),
659 message_count: messages.len() as i32,
660 session_snapshot: serde_json::to_string(&snapshot)?,
661 created_at: Utc::now().timestamp_millis(),
662 git_commit: git_commit.map(String::from),
663 };
664
665 self.conn.execute(
666 r#"
667 INSERT INTO checkpoints (id, session_id, name, description, message_count,
668 session_snapshot, created_at, git_commit)
669 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
670 "#,
671 params![
672 checkpoint.id,
673 checkpoint.session_id,
674 checkpoint.name,
675 checkpoint.description,
676 checkpoint.message_count,
677 checkpoint.session_snapshot,
678 checkpoint.created_at,
679 checkpoint.git_commit,
680 ],
681 )?;
682
683 Ok(checkpoint)
684 }
685
686 pub fn list_checkpoints(&self, session_id: &str) -> Result<Vec<Checkpoint>> {
688 let mut stmt = self.conn.prepare(
689 "SELECT id, session_id, name, description, message_count, session_snapshot, created_at, git_commit
690 FROM checkpoints WHERE session_id = ? ORDER BY created_at DESC"
691 )?;
692
693 let checkpoints = stmt
694 .query_map([session_id], |row| {
695 Ok(Checkpoint {
696 id: row.get(0)?,
697 session_id: row.get(1)?,
698 name: row.get(2)?,
699 description: row.get(3)?,
700 message_count: row.get(4)?,
701 session_snapshot: row.get(5)?,
702 created_at: row.get(6)?,
703 git_commit: row.get(7)?,
704 })
705 })?
706 .collect::<Result<Vec<_>, _>>()?;
707
708 Ok(checkpoints)
709 }
710
711 pub fn add_share_link(&self, link: &ShareLink) -> Result<()> {
717 self.conn.execute(
718 r#"
719 INSERT INTO share_links (id, session_id, provider, url, share_id, title,
720 imported, imported_at, created_at, metadata)
721 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)
722 ON CONFLICT(url) DO UPDATE SET
723 session_id = COALESCE(excluded.session_id, share_links.session_id),
724 imported = excluded.imported,
725 imported_at = excluded.imported_at,
726 metadata = excluded.metadata
727 "#,
728 params![
729 link.id,
730 link.session_id,
731 link.provider,
732 link.url,
733 link.share_id,
734 link.title,
735 link.imported,
736 link.imported_at,
737 link.created_at,
738 link.metadata,
739 ],
740 )?;
741 Ok(())
742 }
743
744 pub fn get_share_link_by_url(&self, url: &str) -> Result<Option<ShareLink>> {
746 self.conn
747 .query_row(
748 "SELECT id, session_id, provider, url, share_id, title, imported, imported_at, created_at, metadata
749 FROM share_links WHERE url = ?",
750 [url],
751 |row| {
752 Ok(ShareLink {
753 id: row.get(0)?,
754 session_id: row.get(1)?,
755 provider: row.get(2)?,
756 url: row.get(3)?,
757 share_id: row.get(4)?,
758 title: row.get(5)?,
759 imported: row.get(6)?,
760 imported_at: row.get(7)?,
761 created_at: row.get(8)?,
762 metadata: row.get(9)?,
763 })
764 },
765 )
766 .optional()
767 .context("Failed to get share link")
768 }
769
770 pub fn list_share_links(&self, imported_only: bool) -> Result<Vec<ShareLink>> {
772 let query = if imported_only {
773 "SELECT id, session_id, provider, url, share_id, title, imported, imported_at, created_at, metadata
774 FROM share_links WHERE imported = 1 ORDER BY created_at DESC"
775 } else {
776 "SELECT id, session_id, provider, url, share_id, title, imported, imported_at, created_at, metadata
777 FROM share_links ORDER BY created_at DESC"
778 };
779
780 let mut stmt = self.conn.prepare(query)?;
781
782 let links = stmt
783 .query_map([], |row| {
784 Ok(ShareLink {
785 id: row.get(0)?,
786 session_id: row.get(1)?,
787 provider: row.get(2)?,
788 url: row.get(3)?,
789 share_id: row.get(4)?,
790 title: row.get(5)?,
791 imported: row.get(6)?,
792 imported_at: row.get(7)?,
793 created_at: row.get(8)?,
794 metadata: row.get(9)?,
795 })
796 })?
797 .collect::<Result<Vec<_>, _>>()?;
798
799 Ok(links)
800 }
801
802 pub fn mark_share_link_imported(&self, url: &str, session_id: &str) -> Result<()> {
804 let now = Utc::now().timestamp_millis();
805 self.conn.execute(
806 "UPDATE share_links SET imported = 1, imported_at = ?, session_id = ? WHERE url = ?",
807 params![now, session_id, url],
808 )?;
809 Ok(())
810 }
811
812 pub fn get_statistics(&self) -> Result<DatabaseStats> {
818 let workspace_count: i64 =
819 self.conn
820 .query_row("SELECT COUNT(*) FROM workspaces", [], |row| row.get(0))?;
821
822 let session_count: i64 =
823 self.conn
824 .query_row("SELECT COUNT(*) FROM sessions", [], |row| row.get(0))?;
825
826 let message_count: i64 =
827 self.conn
828 .query_row("SELECT COUNT(*) FROM messages", [], |row| row.get(0))?;
829
830 let checkpoint_count: i64 =
831 self.conn
832 .query_row("SELECT COUNT(*) FROM checkpoints", [], |row| row.get(0))?;
833
834 let share_link_count: i64 =
835 self.conn
836 .query_row("SELECT COUNT(*) FROM share_links", [], |row| row.get(0))?;
837
838 let imported_link_count: i64 = self.conn.query_row(
839 "SELECT COUNT(*) FROM share_links WHERE imported = 1",
840 [],
841 |row| row.get(0),
842 )?;
843
844 Ok(DatabaseStats {
845 workspace_count,
846 session_count,
847 message_count,
848 checkpoint_count,
849 share_link_count,
850 imported_link_count,
851 })
852 }
853}
854
855#[derive(Debug, Clone, Serialize, Deserialize)]
857pub struct DatabaseStats {
858 pub workspace_count: i64,
859 pub session_count: i64,
860 pub message_count: i64,
861 pub checkpoint_count: i64,
862 pub share_link_count: i64,
863 pub imported_link_count: i64,
864}
865
866#[cfg(test)]
871mod tests {
872 use super::*;
873
874 #[test]
875 fn test_share_link_provider_parsing() {
876 let (provider, id) =
878 ShareLinkProvider::from_url("https://chat.openai.com/share/abc123").unwrap();
879 assert_eq!(provider, ShareLinkProvider::ChatGPT);
880 assert_eq!(id, "abc123");
881
882 let (provider, id) = ShareLinkProvider::from_url("https://claude.ai/share/xyz789").unwrap();
884 assert_eq!(provider, ShareLinkProvider::Claude);
885 assert_eq!(id, "xyz789");
886
887 let (provider, id) =
889 ShareLinkProvider::from_url("https://www.perplexity.ai/search/test-query-123").unwrap();
890 assert_eq!(provider, ShareLinkProvider::Perplexity);
891 assert_eq!(id, "test-query-123");
892
893 assert!(ShareLinkProvider::from_url("https://example.com/test").is_none());
895 }
896
897 #[test]
898 fn test_database_creation() {
899 let db = ChatDatabase::open_in_memory().unwrap();
900 let version = db.get_version().unwrap();
901 assert_eq!(version, SCHEMA_VERSION);
902 }
903
904 #[test]
905 fn test_workspace_crud() {
906 let db = ChatDatabase::open_in_memory().unwrap();
907
908 let workspace = Workspace {
909 id: "ws-1".to_string(),
910 name: "Test Workspace".to_string(),
911 path: Some("/test/path".to_string()),
912 provider: "vscode".to_string(),
913 provider_workspace_id: Some("hash123".to_string()),
914 created_at: 1000,
915 updated_at: 2000,
916 metadata: None,
917 };
918
919 db.upsert_workspace(&workspace).unwrap();
920
921 let retrieved = db.get_workspace("ws-1").unwrap().unwrap();
922 assert_eq!(retrieved.name, "Test Workspace");
923
924 let workspaces = db.list_workspaces().unwrap();
925 assert_eq!(workspaces.len(), 1);
926 }
927
928 #[test]
929 fn test_session_crud() {
930 let db = ChatDatabase::open_in_memory().unwrap();
931
932 let session = Session {
933 id: "sess-1".to_string(),
934 workspace_id: None,
935 provider: "chatgpt".to_string(),
936 provider_session_id: Some("gpt-abc".to_string()),
937 title: "Test Session".to_string(),
938 model: Some("gpt-4".to_string()),
939 message_count: 5,
940 token_count: Some(1000),
941 created_at: 1000,
942 updated_at: 2000,
943 archived: false,
944 metadata: None,
945 };
946
947 let was_update = db.upsert_session(&session).unwrap();
948 assert!(!was_update);
949
950 let retrieved = db.get_session("sess-1").unwrap().unwrap();
951 assert_eq!(retrieved.title, "Test Session");
952 }
953
954 #[test]
955 fn test_share_link_operations() {
956 let db = ChatDatabase::open_in_memory().unwrap();
957
958 let link = ShareLink {
959 id: "link-1".to_string(),
960 session_id: None,
961 provider: "ChatGPT".to_string(),
962 url: "https://chat.openai.com/share/abc123".to_string(),
963 share_id: "abc123".to_string(),
964 title: Some("Shared Chat".to_string()),
965 imported: false,
966 imported_at: None,
967 created_at: 1000,
968 metadata: None,
969 };
970
971 db.add_share_link(&link).unwrap();
972
973 let retrieved = db
974 .get_share_link_by_url("https://chat.openai.com/share/abc123")
975 .unwrap()
976 .unwrap();
977 assert_eq!(retrieved.share_id, "abc123");
978 assert!(!retrieved.imported);
979
980 let session = Session {
982 id: "sess-123".to_string(),
983 workspace_id: None,
984 provider: "chatgpt".to_string(),
985 provider_session_id: None,
986 title: "Imported Session".to_string(),
987 model: None,
988 message_count: 0,
989 token_count: None,
990 created_at: 1000,
991 updated_at: 1000,
992 archived: false,
993 metadata: None,
994 };
995 db.upsert_session(&session).unwrap();
996
997 db.mark_share_link_imported(&link.url, "sess-123").unwrap();
999
1000 let updated = db.get_share_link_by_url(&link.url).unwrap().unwrap();
1001 assert!(updated.imported);
1002 assert_eq!(updated.session_id, Some("sess-123".to_string()));
1003 }
1004
1005 #[test]
1006 fn test_checkpoint_creation() {
1007 let db = ChatDatabase::open_in_memory().unwrap();
1008
1009 let session = Session {
1011 id: "sess-1".to_string(),
1012 workspace_id: None,
1013 provider: "test".to_string(),
1014 provider_session_id: None,
1015 title: "Test".to_string(),
1016 model: None,
1017 message_count: 0,
1018 token_count: None,
1019 created_at: 1000,
1020 updated_at: 1000,
1021 archived: false,
1022 metadata: None,
1023 };
1024 db.upsert_session(&session).unwrap();
1025
1026 let checkpoint = db
1028 .create_checkpoint("sess-1", "v1.0", Some("First checkpoint"), None)
1029 .unwrap();
1030 assert_eq!(checkpoint.name, "v1.0");
1031
1032 let checkpoints = db.list_checkpoints("sess-1").unwrap();
1034 assert_eq!(checkpoints.len(), 1);
1035 }
1036
1037 #[test]
1038 fn test_database_statistics() {
1039 let db = ChatDatabase::open_in_memory().unwrap();
1040
1041 let stats = db.get_statistics().unwrap();
1042 assert_eq!(stats.workspace_count, 0);
1043 assert_eq!(stats.session_count, 0);
1044 }
1045}
1046
1047#[cfg(test)]
1048mod harvest_compat_tests {
1049 use super::*;
1050
1051 #[test]
1058 fn an_empty_harvest_database_opens() {
1059 let dir = tempfile::tempdir().unwrap();
1060 let path = dir.path().join("empty-harvest.db");
1061 crate::commands::create_harvest_database(&path).unwrap();
1062
1063 let db = ChatDatabase::open(&path).expect("an empty harvest db must open");
1065
1066 let has_blob: bool = db
1067 .conn
1068 .prepare("SELECT * FROM pragma_table_info('sessions') WHERE name = 'session_json'")
1069 .and_then(|mut s| s.exists([]))
1070 .unwrap();
1071 assert!(
1072 has_blob,
1073 "opening must not replace the harvest sessions table"
1074 );
1075 }
1076
1077 #[test]
1081 fn a_harvest_database_has_the_tables_the_api_reads() {
1082 let dir = tempfile::tempdir().unwrap();
1083 let path = dir.path().join("api-tables.db");
1084 crate::commands::create_harvest_database(&path).unwrap();
1085 let db = ChatDatabase::open(&path).unwrap();
1086
1087 for table in ["sessions", "workspaces", "agents", "metadata"] {
1088 let exists: bool = db
1089 .conn
1090 .prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name = ?1")
1091 .and_then(|mut s| s.exists([table]))
1092 .unwrap();
1093 assert!(exists, "harvest database is missing `{table}`");
1094 }
1095 }
1096
1097 #[test]
1099 fn the_workspaces_join_runs_against_a_harvest_database() {
1100 let dir = tempfile::tempdir().unwrap();
1101 let path = dir.path().join("ws-join.db");
1102 crate::commands::create_harvest_database(&path).unwrap();
1103 let db = ChatDatabase::open(&path).unwrap();
1104
1105 let rows: i64 = db
1106 .conn
1107 .query_row(
1108 "SELECT COUNT(*) FROM (
1109 SELECT w.id FROM workspaces w
1110 LEFT JOIN sessions s ON w.id = s.workspace_id
1111 GROUP BY w.id
1112 )",
1113 [],
1114 |r| r.get(0),
1115 )
1116 .expect("the workspaces join must execute");
1117 assert_eq!(rows, 0);
1118 }
1119
1120 #[test]
1122 fn a_fresh_database_still_gets_the_full_schema() {
1123 let dir = tempfile::tempdir().unwrap();
1124 let path = dir.path().join("fresh.db");
1125 let db = ChatDatabase::open(&path).unwrap();
1126
1127 let has_model: bool = db
1128 .conn
1129 .prepare("SELECT * FROM pragma_table_info('sessions') WHERE name = 'model'")
1130 .and_then(|mut s| s.exists([]))
1131 .unwrap();
1132 assert!(has_model, "a fresh database should use sql/schema.sql");
1133 }
1134}