Repository Public
1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: SPChat/Core/Persistence/CacheStore.swift3// Description: Local SQLite cache (conversations, message trees, catalogues, full-text index) that makes history readable offline and startup instant.4import Foundation5import SQLite367public struct CacheError: Error, CustomStringConvertible, Sendable { public let description: String }89private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)1011/// Thin, serialised SQLite connection. All access goes through the `CacheStore` actor.12final class SQLiteDatabase {13 private var db: OpaquePointer?1415 init(path: String) throws {16 let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX17 guard sqlite3_open_v2(path, &db, flags, nil) == SQLITE_OK else {18 let msg = db.map { String(cString: sqlite3_errmsg($0)) } ?? "open failed"; sqlite3_close(db)19 throw CacheError(description: "SQLite: \(msg)")20 }21 sqlite3_busy_timeout(db, 3000)22 }23 deinit { sqlite3_close(db) }2425 enum Value { case text(String), int(Int64), double(Double), blob(Data), null }2627 func exec(_ sql: String) throws {28 var err: UnsafeMutablePointer<CChar>?29 guard sqlite3_exec(db, sql, nil, nil, &err) == SQLITE_OK else {30 let msg = err.map { String(cString: $0) } ?? "exec failed"; sqlite3_free(err)31 throw CacheError(description: "SQLite: \(msg)")32 }33 }3435 @discardableResult36 func run(_ sql: String, _ params: [Value] = []) throws -> [[Value]] {37 var stmt: OpaquePointer?38 guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else { throw CacheError(description: "SQLite: \(String(cString: sqlite3_errmsg(db)))") }39 defer { sqlite3_finalize(stmt) }40 for (i, p) in params.enumerated() {41 let idx = Int32(i + 1)42 switch p {43 case .text(let s): sqlite3_bind_text(stmt, idx, s, -1, SQLITE_TRANSIENT)44 case .int(let n): sqlite3_bind_int64(stmt, idx, n)45 case .double(let d): sqlite3_bind_double(stmt, idx, d)46 case .blob(let d): _ = d.withUnsafeBytes { sqlite3_bind_blob(stmt, idx, $0.baseAddress, Int32(d.count), SQLITE_TRANSIENT) }47 case .null: sqlite3_bind_null(stmt, idx)48 }49 }50 var rows: [[Value]] = []51 while true {52 let rc = sqlite3_step(stmt)53 if rc == SQLITE_DONE { break }54 guard rc == SQLITE_ROW else { throw CacheError(description: "SQLite: \(String(cString: sqlite3_errmsg(db)))") }55 var row: [Value] = []56 for c in 0..<sqlite3_column_count(stmt) {57 switch sqlite3_column_type(stmt, c) {58 case SQLITE_INTEGER: row.append(.int(sqlite3_column_int64(stmt, c)))59 case SQLITE_FLOAT: row.append(.double(sqlite3_column_double(stmt, c)))60 case SQLITE_TEXT: row.append(.text(String(cString: sqlite3_column_text(stmt, c))))61 case SQLITE_BLOB:62 let n = Int(sqlite3_column_bytes(stmt, c))63 row.append(.blob(n > 0 ? Data(bytes: sqlite3_column_blob(stmt, c), count: n) : Data()))64 default: row.append(.null)65 }66 }67 rows.append(row)68 }69 return rows70 }71}7273private extension SQLiteDatabase.Value {74 var string: String? { if case .text(let s) = self { return s }; return nil }75 var data: Data? { if case .blob(let d) = self { return d }; if case .text(let s) = self { return Data(s.utf8) }; return nil }76 var int: Int? { if case .int(let n) = self { return Int(n) }; return nil }77}7879/// Offline cache. The server stays the source of truth: every successful fetch overwrites the corresponding rows, and80/// signing out wipes the file's content. Stored inside the app's sandbox container.81public actor CacheStore {82 private let db: SQLiteDatabase83 private static let schemaVersion = 184 private let encoder = JSONEncoder.api()85 private let decoder = JSONDecoder.api()8687 /// `url == nil` → in-memory database (tests, previews).88 public init(url: URL?) throws {89 if let url { try FileManager.default.createDirectory(at: url.deletingLastPathComponent(), withIntermediateDirectories: true) }90 db = try SQLiteDatabase(path: url?.path ?? ":memory:")91 try db.exec("PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA foreign_keys=ON; PRAGMA secure_delete=ON;")92 let version = try db.run("PRAGMA user_version").first?.first?.int ?? 093 if version != Self.schemaVersion {94 // a cache can always be rebuilt from the server: on a schema change it is simply recreated95 try db.exec("DROP TABLE IF EXISTS conversations; DROP TABLE IF EXISTS details; DROP TABLE IF EXISTS messages; DROP TABLE IF EXISTS kv; DROP TABLE IF EXISTS message_fts;")96 try db.exec("""97 CREATE TABLE conversations (id TEXT PRIMARY KEY, json BLOB NOT NULL, title TEXT NOT NULL, pinned INTEGER NOT NULL, favorite INTEGER NOT NULL, archived INTEGER NOT NULL, project_id TEXT, sort_ts REAL NOT NULL);98 CREATE INDEX conversations_sort ON conversations (archived, pinned DESC, sort_ts DESC);99 CREATE TABLE details (conversation_id TEXT PRIMARY KEY, json BLOB NOT NULL, fetched_at REAL NOT NULL);100 CREATE TABLE messages (id TEXT PRIMARY KEY, conversation_id TEXT NOT NULL, role TEXT NOT NULL, active INTEGER NOT NULL, created_at REAL NOT NULL, text TEXT NOT NULL);101 CREATE INDEX messages_conv ON messages (conversation_id, created_at);102 CREATE VIRTUAL TABLE message_fts USING fts5(text, message_id UNINDEXED, conversation_id UNINDEXED, tokenize = 'unicode61 remove_diacritics 2');103 CREATE TABLE kv (key TEXT PRIMARY KEY, value BLOB NOT NULL, updated_at REAL NOT NULL);104 PRAGMA user_version = \(Self.schemaVersion);105 """)106 }107 }108109 // MARK: Conversations110111 /// Replaces the cached list for one filter with what the server returned (rows the server no longer lists are removed).112 public func replaceConversations(_ list: [Conversation], archived: Bool) throws {113 try db.exec("BEGIN")114 do {115 let ids = Set(list.map(\.id))116 for row in try db.run("SELECT id FROM conversations WHERE archived = ?", [.int(archived ? 1 : 0)]) {117 if let id = row[0].string, !ids.contains(id) { try removeConversation(id) }118 }119 for c in list { try upsert(c) }120 try db.exec("COMMIT")121 } catch { try? db.exec("ROLLBACK"); throw error }122 }123124 public func upsertConversation(_ c: Conversation) throws { try upsert(c) }125126 private func upsert(_ c: Conversation) throws {127 try db.run("INSERT INTO conversations (id, json, title, pinned, favorite, archived, project_id, sort_ts) VALUES (?,?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET json=excluded.json, title=excluded.title, pinned=excluded.pinned, favorite=excluded.favorite, archived=excluded.archived, project_id=excluded.project_id, sort_ts=excluded.sort_ts",128 [.text(c.id), .blob(try encoder.encode(c)), .text(c.title), .int(c.pinned ? 1 : 0), .int(c.favorite ? 1 : 0), .int(c.isArchived ? 1 : 0), c.projectId.map { .text($0) } ?? .null, .double(c.sortDate.timeIntervalSince1970)])129 }130131 public func conversations(filter: ConversationFilter = .all, query: String? = nil, limit: Int = 500) throws -> [Conversation] {132 var sql = "SELECT json FROM conversations WHERE archived = ?"; var params: [SQLiteDatabase.Value] = [.int(filter == .archived ? 1 : 0)]133 if filter == .favorites { sql += " AND favorite = 1" }134 if filter == .pinned { sql += " AND pinned = 1" }135 if let q = query?.trimmingCharacters(in: .whitespaces), !q.isEmpty { sql += " AND title LIKE ? ESCAPE '\\'"; params.append(.text("%" + Self.likeEscape(q) + "%")) }136 sql += " ORDER BY pinned DESC, sort_ts DESC LIMIT ?"; params.append(.int(Int64(limit)))137 return try db.run(sql, params).compactMap { $0[0].data.flatMap { try? decoder.decode(Conversation.self, from: $0) } }138 }139140 public func conversation(_ id: String) throws -> Conversation? {141 try db.run("SELECT json FROM conversations WHERE id = ?", [.text(id)]).first?[0].data.flatMap { try? decoder.decode(Conversation.self, from: $0) }142 }143144 public func deleteConversation(_ id: String) throws { try removeConversation(id) }145146 private func removeConversation(_ id: String) throws {147 try db.run("DELETE FROM conversations WHERE id = ?", [.text(id)])148 try db.run("DELETE FROM details WHERE conversation_id = ?", [.text(id)])149 try db.run("DELETE FROM messages WHERE conversation_id = ?", [.text(id)])150 try db.run("DELETE FROM message_fts WHERE conversation_id = ?", [.text(id)])151 }152153 // MARK: Conversation details (message tree)154155 /// Stores the full tree and refreshes the offline full-text index for the active thread.156 public func saveDetail(_ detail: ConversationDetail) throws {157 var d = detail; d.liveRun = nil158 let id = d.conversation.id159 try db.exec("BEGIN")160 do {161 try upsert(d.conversation)162 try db.run("INSERT INTO details (conversation_id, json, fetched_at) VALUES (?,?,?) ON CONFLICT(conversation_id) DO UPDATE SET json=excluded.json, fetched_at=excluded.fetched_at",163 [.text(id), .blob(try encoder.encode(d)), .double(Date().timeIntervalSince1970)])164 try db.run("DELETE FROM messages WHERE conversation_id = ?", [.text(id)])165 try db.run("DELETE FROM message_fts WHERE conversation_id = ?", [.text(id)])166 for m in d.messages {167 let text = m.visibleText168 try db.run("INSERT OR REPLACE INTO messages (id, conversation_id, role, active, created_at, text) VALUES (?,?,?,?,?,?)",169 [.text(m.id), .text(id), .text(m.role.rawValue), .int(m.active ? 1 : 0), .double(m.createdAt.timeIntervalSince1970), .text(text)])170 if m.active, !text.isEmpty { try db.run("INSERT INTO message_fts (text, message_id, conversation_id) VALUES (?,?,?)", [.text(text), .text(m.id), .text(id)]) }171 }172 try db.exec("COMMIT")173 } catch { try? db.exec("ROLLBACK"); throw error }174 }175176 public func detail(_ conversationId: String) throws -> (detail: ConversationDetail, fetchedAt: Date)? {177 guard let row = try db.run("SELECT json, fetched_at FROM details WHERE conversation_id = ?", [.text(conversationId)]).first,178 let data = row[0].data, let d = try? decoder.decode(ConversationDetail.self, from: data) else { return nil }179 var at = Date.distantPast; if case .double(let t) = row[1] { at = Date(timeIntervalSince1970: t) }180 return (d, at)181 }182183 public func cachedDetailIds() throws -> Set<String> { Set(try db.run("SELECT conversation_id FROM details").compactMap { $0[0].string }) }184185 // MARK: Offline search186187 /// Full-text search over cached conversations (titles and active messages). Used when the server cannot be reached.188 public func search(_ query: String, limit: Int = 40) throws -> [SearchHit] {189 let terms = query.split(whereSeparator: { !$0.isLetter && !$0.isNumber }).map(String.init).filter { !$0.isEmpty }190 guard !terms.isEmpty else { return [] }191 let match = terms.map { "\"\($0)\"*" }.joined(separator: " ")192 let rows = try db.run("""193 SELECT f.message_id, f.conversation_id, m.role, m.created_at, c.title, snippet(message_fts, 0, '<b>', '</b>', '…', 18)194 FROM message_fts f JOIN messages m ON m.id = f.message_id JOIN conversations c ON c.id = f.conversation_id195 WHERE message_fts MATCH ? ORDER BY rank LIMIT ?196 """, [.text(match), .int(Int64(limit))])197 var hits = rows.compactMap { r -> SearchHit? in198 guard let id = r[0].string, let cid = r[1].string else { return nil }199 var date: Date?; if case .double(let t) = r[3] { date = Date(timeIntervalSince1970: t) }200 return SearchHit(id: id, conversationId: cid, role: r[2].string ?? "assistant", title: r[4].string ?? "", snippet: r[5].string ?? "", createdAt: date)201 }202 let seen = Set(hits.map(\.conversationId))203 for c in try conversations(filter: .all, query: query, limit: 20) where !seen.contains(c.id) {204 hits.append(SearchHit(id: "title:" + c.id, conversationId: c.id, role: "title", title: c.title, snippet: c.title, createdAt: c.sortDate))205 }206 return hits207 }208209 // MARK: Key / value (catalogues: models, presets, settings…)210211 public func set<T: Encodable>(_ value: T, for key: String) throws {212 try db.run("INSERT INTO kv (key, value, updated_at) VALUES (?,?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at",213 [.text(key), .blob(try encoder.encode(value)), .double(Date().timeIntervalSince1970)])214 }215 public func get<T: Decodable>(_ type: T.Type, for key: String) throws -> T? {216 try db.run("SELECT value FROM kv WHERE key = ?", [.text(key)]).first?[0].data.flatMap { try? decoder.decode(T.self, from: $0) }217 }218 public func remove(key: String) throws { try db.run("DELETE FROM kv WHERE key = ?", [.text(key)]) }219220 // MARK: Maintenance221222 /// Removes every cached row (sign-out, or complete data deletion). The file is compacted so nothing readable remains.223 public func wipe() throws {224 try db.exec("DELETE FROM conversations; DELETE FROM details; DELETE FROM messages; DELETE FROM message_fts; DELETE FROM kv;")225 try? db.exec("VACUUM"); _ = try? db.run("PRAGMA wal_checkpoint(TRUNCATE)")226 }227228 public func stats() throws -> (conversations: Int, details: Int, messages: Int) {229 func count(_ t: String) throws -> Int { try db.run("SELECT count(*) FROM \(t)").first?[0].int ?? 0 }230 return (try count("conversations"), try count("details"), try count("messages"))231 }232233 private static func likeEscape(_ s: String) -> String { s.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") }234}235