Skip to main content

ironbridge/
database.rs

1// Copyright (c) 2024-2026 Nervosys LLC
2// SPDX-License-Identifier: AGPL-3.0-only OR LicenseRef-IronBridge-Commercial
3//! Universal Chat Session Database
4//!
5//! A unified database schema for storing, versioning, and tracking chat sessions
6//! from multiple providers. This serves as an intermediate representation that
7//! normalizes data from various sources (VS Code, web providers, share links, etc.)
8//!
9//! Note: Many types and methods are infrastructure for future integration.
10#![allow(dead_code)]
11
12//! ## Schema Overview
13//!
14//! ```text
15//! +-----------------+     +-----------------+     +-----------------+
16//! |   Workspaces    |----<|    Sessions     |----<|    Messages     |
17//! +-----------------+     +-----------------+     +-----------------+
18//!                                |
19//!                                v
20//!                        +-----------------+
21//!                        |   Checkpoints   |
22//!                        +-----------------+
23//!                                |
24//!                                v
25//!                        +-----------------+
26//!                        |   ShareLinks    |
27//!                        +-----------------+
28//! ```
29
30use anyhow::{Context, Result};
31use chrono::Utc;
32use rusqlite::{params, Connection, OptionalExtension};
33use serde::{Deserialize, Serialize};
34use std::path::Path;
35
36/// Database schema version
37pub const SCHEMA_VERSION: &str = "3.0";
38
39// =============================================================================
40// Database Models
41// =============================================================================
42
43/// A workspace/project that contains chat sessions
44#[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>, // JSON blob for provider-specific data
54}
55
56/// A chat session containing messages
57#[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>, // JSON blob for provider-specific data
71}
72
73/// A message within a session
74#[derive(Debug, Clone, Serialize, Deserialize)]
75pub struct Message {
76    pub id: String,
77    pub session_id: String,
78    pub role: String, // "user", "assistant", "system"
79    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>, // For branching conversations
84    pub metadata: Option<String>,
85}
86
87/// A checkpoint/snapshot of a session at a point in time
88#[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, // JSON of session state
96    pub created_at: i64,
97    pub git_commit: Option<String>,
98}
99
100/// A share link for a session
101#[derive(Debug, Clone, Serialize, Deserialize)]
102pub struct ShareLink {
103    pub id: String,
104    pub session_id: Option<String>, // Linked session after import
105    pub provider: String,
106    pub url: String,
107    pub share_id: String, // Provider-specific share ID extracted from URL
108    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/// Supported share link providers
116#[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    /// Parse a URL to determine the provider
128    pub fn from_url(url: &str) -> Option<(Self, String)> {
129        let url_lower = url.to_lowercase();
130
131        // ChatGPT: https://chat.openai.com/share/abc123 or https://chatgpt.com/share/abc123
132        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        // Claude: https://claude.ai/share/abc123
140        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        // Gemini: https://g.co/gemini/share/abc123 or https://gemini.google.com/share/abc123
147        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        // Perplexity: https://www.perplexity.ai/search/abc123
156        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        // Poe: https://poe.com/s/abc123
163        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/// Parsed share link information
185#[derive(Debug, Clone)]
186pub struct ShareLinkInfo {
187    pub provider: String,
188    pub share_id: String,
189}
190
191/// Parser for share link URLs
192pub struct ShareLinkParser;
193
194impl ShareLinkParser {
195    /// Parse a URL to extract provider and share ID
196    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
204/// Extract a path segment after a given key
205fn 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
218// =============================================================================
219// Database Operations
220// =============================================================================
221
222/// Universal Chat Database manager
223pub struct ChatDatabase {
224    pub conn: Connection,
225}
226
227impl ChatDatabase {
228    /// Open or create a database at the given path
229    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    /// Open an in-memory database (for testing)
239    #[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    /// Initialize the database schema
250    fn initialize(&self) -> Result<()> {
251        // Check if this is a harvest database (has sessions but missing 'model' column)
252        // If so, skip full schema initialization to preserve harvest data.
253        //
254        // Both checks read the schema rather than the data. Probing with
255        // `SELECT 1 FROM sessions LIMIT 1` looks equivalent but returns
256        // `QueryReturnedNoRows` on an empty table, so a harvested-but-empty
257        // database was classified as "not harvest"; the full schema then ran
258        // against harvest-shaped tables and died on
259        // `CREATE INDEX idx_sessions_model ON sessions(model)`.
260        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        // Only apply full schema if not a harvest database, or if it's a fresh database
280        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        // For harvest databases, ensure we have the tables we need for API
286        // (agents, metadata tables might be missing)
287        else {
288            // Create minimal additional tables needed for API functionality
289            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    /// Get access to the underlying connection
344    pub fn connection(&self) -> &Connection {
345        &self.conn
346    }
347
348    /// Get schema version
349    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    // -------------------------------------------------------------------------
362    // Workspace Operations
363    // -------------------------------------------------------------------------
364
365    /// Insert or update a workspace
366    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    /// Get a workspace by ID
393    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    /// List all workspaces
417    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    // -------------------------------------------------------------------------
442    // Session Operations
443    // -------------------------------------------------------------------------
444
445    /// Insert or update a session
446    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    /// Get a session by ID
484    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    /// List sessions with optional filters
513    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    /// Count sessions by provider
565    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    // -------------------------------------------------------------------------
578    // Message Operations
579    // -------------------------------------------------------------------------
580
581    /// Insert a message
582    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    /// Get messages for a session
608    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    // -------------------------------------------------------------------------
634    // Checkpoint Operations
635    // -------------------------------------------------------------------------
636
637    /// Create a checkpoint for a session
638    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    /// List checkpoints for a session
687    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    // -------------------------------------------------------------------------
712    // Share Link Operations
713    // -------------------------------------------------------------------------
714
715    /// Add a share link
716    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    /// Get share link by URL
745    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    /// List all share links
771    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    /// Mark a share link as imported
803    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    // -------------------------------------------------------------------------
813    // Statistics
814    // -------------------------------------------------------------------------
815
816    /// Get database statistics
817    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/// Database statistics
856#[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// =============================================================================
867// Tests
868// =============================================================================
869
870#[cfg(test)]
871mod tests {
872    use super::*;
873
874    #[test]
875    fn test_share_link_provider_parsing() {
876        // ChatGPT
877        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        // Claude
883        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        // Perplexity
888        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        // Invalid URL
894        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        // Create a session to link to
981        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        // Mark as imported
998        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        // Create a session first
1010        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        // Create checkpoint
1027        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        // List checkpoints
1033        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    /// A harvest database is detected by schema, not by content.
1052    ///
1053    /// Probing with `SELECT 1 FROM sessions LIMIT 1` returns no rows on an
1054    /// empty harvest table, so an empty one was classified as fresh; the full
1055    /// schema then ran against harvest-shaped tables and
1056    /// `CREATE INDEX idx_sessions_model ON sessions(model)` failed outright.
1057    #[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        // No rows inserted -- this is the case that used to fail.
1064        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    /// Every table the REST API reads must exist after opening a harvest
1078    /// database. `workspaces` was missing, so `GET /api/workspaces` answered
1079    /// 500 against every harvested install.
1080    #[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    /// The join `GET /api/workspaces` runs must actually execute.
1098    #[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    /// A genuinely fresh database still gets the full schema.
1121    #[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}