Repository Public
1//2// SP Chat iOS3// File: SPChat-iOS/Core/Persistence/LocalStore.swift4// Description: Encrypted-at-rest SQLite cache (conversation list, conversation details, models) and the outbox of unsent messages.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8//910import Foundation11import SQLite31213/// A message written while the server could not be reached. It is shown as "waiting" — never as sent — and is posted14/// with its idempotency key when the connection returns.15struct OutboxItem: Identifiable, Codable, Hashable, Sendable {16 /// Also the `clientRequestId` of the POST: replaying it can never create a second message.17 var id: String18 var conversationId: String?19 var projectId: String?20 var text: String21 var attachments: [Attachment]22 var options: SendOptions23 var createdAt: Date24 var lastError: String?25}2627/// Local persistence. The server is the source of truth; this database is a read cache that makes already-downloaded28/// conversations readable offline, plus the outbox. SQLite in WAL mode behind an actor; the file is protected by iOS29/// Data Protection (encrypted with a key derived from the device passcode) and excluded from backups.30actor LocalStore {31 // only touched inside the actor; `nonisolated(unsafe)` lets deinit close the handle32 nonisolated(unsafe) private var db: OpaquePointer?33 private let url: URL?34 private static let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)3536 /// `directory == nil` opens an in-memory database (tests).37 init(directory: URL?) throws {38 if let directory {39 try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true, attributes: [.protectionKey: FileProtectionType.completeUnlessOpen])40 var dir = directory41 var values = URLResourceValues(); values.isExcludedFromBackup = true42 try? dir.setResourceValues(values)43 url = directory.appendingPathComponent("spchat.sqlite")44 } else { url = nil }45 let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX | (url == nil ? 0 : SQLITE_OPEN_FILEPROTECTION_COMPLETEUNLESSOPEN)46 var handle: OpaquePointer?47 guard sqlite3_open_v2(url?.path ?? ":memory:", &handle, flags, nil) == SQLITE_OK else {48 let message = handle.map { String(cString: sqlite3_errmsg($0)) } ?? "open"49 sqlite3_close(handle)50 throw LocalStoreError.sqlite(message)51 }52 db = handle53 sqlite3_busy_timeout(handle, 3000)54 for sql in [55 "PRAGMA journal_mode = WAL", "PRAGMA synchronous = NORMAL", "PRAGMA secure_delete = ON",56 "CREATE TABLE IF NOT EXISTS conversations (id TEXT PRIMARY KEY, json BLOB NOT NULL, sort REAL NOT NULL, archived INTEGER NOT NULL DEFAULT 0)",57 "CREATE TABLE IF NOT EXISTS details (id TEXT PRIMARY KEY, json BLOB NOT NULL, fetched REAL NOT NULL)",58 "CREATE TABLE IF NOT EXISTS outbox (id TEXT PRIMARY KEY, json BLOB NOT NULL, created REAL NOT NULL)",59 "CREATE TABLE IF NOT EXISTS kv (key TEXT PRIMARY KEY, value BLOB NOT NULL)",60 ] { sqlite3_exec(handle, sql, nil, nil, nil) }61 }6263 deinit { sqlite3_close(db) }6465 // MARK: conversation list6667 /// Replaces the cached list of one scope (active or archived) with the server's answer: rows that disappeared68 /// from the server disappear here too, together with their cached detail.69 func replaceConversations(_ list: [Conversation], archived: Bool) {70 exec("BEGIN")71 let keep = Set(list.map(\.id))72 for id in ids("SELECT id FROM conversations WHERE archived = ?", [.int(archived ? 1 : 0)]) where !keep.contains(id) {73 run("DELETE FROM conversations WHERE id = ?", [.text(id)])74 run("DELETE FROM details WHERE id = ?", [.text(id)])75 }76 for c in list { upsert(c) }77 exec("COMMIT")78 }7980 func upsertConversation(_ c: Conversation) { upsert(c) }8182 private func upsert(_ c: Conversation) {83 guard let data = try? JSONEncoder().encode(c) else { return }84 run("INSERT INTO conversations (id, json, sort, archived) VALUES (?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET json = excluded.json, sort = excluded.sort, archived = excluded.archived",85 [.text(c.id), .blob(data), .real(c.sortDate.timeIntervalSince1970), .int(c.isArchived ? 1 : 0)])86 }8788 func conversations(archived: Bool = false) -> [Conversation] {89 blobs("SELECT json FROM conversations WHERE archived = ? ORDER BY sort DESC", [.int(archived ? 1 : 0)]).compactMap { try? JSONDecoder().decode(Conversation.self, from: $0) }90 .sorted { ($0.pinned ? 1 : 0, $0.sortDate) > ($1.pinned ? 1 : 0, $1.sortDate) }91 }9293 func deleteConversation(_ id: String) {94 run("DELETE FROM conversations WHERE id = ?", [.text(id)]); run("DELETE FROM details WHERE id = ?", [.text(id)]); run("DELETE FROM outbox WHERE json_extract(json, '$.conversationId') = ?", [.text(id)])95 }9697 // MARK: conversation detail9899 func saveDetail(_ d: ConversationDetail) {100 var stored = d; stored.liveRun = nil101 guard let data = try? JSONEncoder().encode(stored) else { return }102 run("INSERT INTO details (id, json, fetched) VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET json = excluded.json, fetched = excluded.fetched", [.text(d.conversation.id), .blob(data), .real(Date().timeIntervalSince1970)])103 upsert(d.conversation)104 }105106 func detail(_ id: String) -> (detail: ConversationDetail, fetchedAt: Date)? {107 guard let stmt = prepare("SELECT json, fetched FROM details WHERE id = ?", [.text(id)]) else { return nil }108 defer { sqlite3_finalize(stmt) }109 guard sqlite3_step(stmt) == SQLITE_ROW, let data = blob(stmt, 0), let d = try? JSONDecoder().decode(ConversationDetail.self, from: data) else { return nil }110 return (d, Date(timeIntervalSince1970: sqlite3_column_double(stmt, 1)))111 }112113 func cachedDetailIDs() -> Set<String> { Set(ids("SELECT id FROM details", [])) }114115 /// Offline search over the titles and message text already on the device.116 func searchCached(_ query: String, limit: Int = 40) -> [Conversation] {117 let needle = query.trimmingCharacters(in: .whitespacesAndNewlines)118 guard needle.count >= 2 else { return [] }119 let pattern = "%" + needle.replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") + "%"120 let found = ids("SELECT c.id FROM conversations c LEFT JOIN details d ON d.id = c.id WHERE json_extract(c.json, '$.title') LIKE ? ESCAPE '\\' OR CAST(d.json AS TEXT) LIKE ? ESCAPE '\\' ORDER BY c.sort DESC LIMIT ?", [.text(pattern), .text(pattern), .int(Int64(limit))])121 let all = Dictionary(uniqueKeysWithValues: (conversations(archived: false) + conversations(archived: true)).map { ($0.id, $0) })122 return found.compactMap { all[$0] }123 }124125 // MARK: outbox126127 func enqueue(_ item: OutboxItem) {128 guard let data = try? JSONEncoder().encode(item) else { return }129 run("INSERT INTO outbox (id, json, created) VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET json = excluded.json", [.text(item.id), .blob(data), .real(item.createdAt.timeIntervalSince1970)])130 }131 func outbox() -> [OutboxItem] { blobs("SELECT json FROM outbox ORDER BY created ASC", []).compactMap { try? JSONDecoder().decode(OutboxItem.self, from: $0) } }132 func removeFromOutbox(_ id: String) { run("DELETE FROM outbox WHERE id = ?", [.text(id)]) }133134 // MARK: key-value (models, presets, last sync…)135136 func setValue<T: Encodable>(_ value: T, for key: String) {137 guard let data = try? JSONEncoder().encode(value) else { return }138 run("INSERT INTO kv (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.text(key), .blob(data)])139 }140 func value<T: Decodable>(_ type: T.Type = T.self, for key: String) -> T? {141 blobs("SELECT value FROM kv WHERE key = ?", [.text(key)]).first.flatMap { try? JSONDecoder().decode(T.self, from: $0) }142 }143144 func removeValue(for key: String) { run("DELETE FROM kv WHERE key = ?", [.text(key)]) }145146 // MARK: wipe147148 /// Removes every local trace of the account (sign-out, revoked session). `secure_delete` overwrites freed pages;149 /// VACUUM then truncates the file and the write-ahead log.150 func wipe() {151 for t in ["conversations", "details", "outbox", "kv"] { exec("DELETE FROM \(t)") }152 exec("VACUUM"); exec("PRAGMA wal_checkpoint(TRUNCATE)")153 }154155 func counts() -> (conversations: Int, details: Int, outbox: Int) {156 (ids("SELECT id FROM conversations", []).count, ids("SELECT id FROM details", []).count, ids("SELECT id FROM outbox", []).count)157 }158159 // MARK: sqlite plumbing160161 private enum Bind { case text(String), blob(Data), real(Double), int(Int64) }162163 private func exec(_ sql: String) { sqlite3_exec(db, sql, nil, nil, nil) }164165 private func prepare(_ sql: String, _ binds: [Bind]) -> OpaquePointer? {166 var stmt: OpaquePointer?167 guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else { return nil }168 for (i, b) in binds.enumerated() {169 let n = Int32(i + 1)170 switch b {171 case .text(let s): sqlite3_bind_text(stmt, n, s, -1, Self.transient)172 case .blob(let d): _ = d.withUnsafeBytes { sqlite3_bind_blob(stmt, n, $0.baseAddress, Int32(d.count), Self.transient) }173 case .real(let r): sqlite3_bind_double(stmt, n, r)174 case .int(let v): sqlite3_bind_int64(stmt, n, v)175 }176 }177 return stmt178 }179 private func run(_ sql: String, _ binds: [Bind]) {180 guard let stmt = prepare(sql, binds) else { return }181 sqlite3_step(stmt); sqlite3_finalize(stmt)182 }183 private func blob(_ stmt: OpaquePointer?, _ col: Int32) -> Data? {184 guard let p = sqlite3_column_blob(stmt, col) else { return nil }185 return Data(bytes: p, count: Int(sqlite3_column_bytes(stmt, col)))186 }187 private func blobs(_ sql: String, _ binds: [Bind]) -> [Data] {188 guard let stmt = prepare(sql, binds) else { return [] }189 defer { sqlite3_finalize(stmt) }190 var out: [Data] = []191 while sqlite3_step(stmt) == SQLITE_ROW { if let d = blob(stmt, 0) { out.append(d) } }192 return out193 }194 private func ids(_ sql: String, _ binds: [Bind]) -> [String] {195 guard let stmt = prepare(sql, binds) else { return [] }196 defer { sqlite3_finalize(stmt) }197 var out: [String] = []198 while sqlite3_step(stmt) == SQLITE_ROW { if let c = sqlite3_column_text(stmt, 0) { out.append(String(cString: c)) } }199 return out200 }201}202203enum LocalStoreError: Error { case sqlite(String) }204