Repository Public
1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: Packages/LocalChatConversations/Sources/Schema.swift3// Description: SQLite schema, migrations and JSON coding helpers of the conversation store (entities are stored as JSON next to indexed columns).4import Foundation5import GRDB6import LocalChatCore78enum DBCoding {9 /// Lossless local coding: dates stay exact Doubles.10 static func encode<T: Encodable>(_ value: T) throws -> String {11 let e = JSONEncoder(); e.outputFormatting = [.sortedKeys]12 return String(decoding: try e.encode(value), as: UTF8.self)13 }14 static func decode<T: Decodable>(_ type: T.Type, _ text: String) throws -> T {15 try JSONDecoder().decode(type, from: Data(text.utf8))16 }17}1819enum SyncCoding {20 static func encode<T: Encodable>(_ value: T) throws -> Data { try CoreCoding.encoder.encode(value) }21 /// Accepts ISO-8601 with or without fractional seconds.22 static func decode<T: Decodable>(_ type: T.Type, _ data: Data) throws -> T {23 let d = JSONDecoder()24 d.dateDecodingStrategy = .custom { decoder in25 let c = try decoder.singleValueContainer()26 if let n = try? c.decode(Double.self) { return Date(timeIntervalSinceReferenceDate: n) }27 let s = try c.decode(String.self)28 if let date = try? Date.ISO8601FormatStyle(includingFractionalSeconds: true).parse(s) { return date }29 if let date = try? Date.ISO8601FormatStyle().parse(s) { return date }30 throw DecodingError.dataCorruptedError(in: c, debugDescription: "Invalid date: \(s)")31 }32 return try d.decode(type, from: data)33 }34}3536extension Date {37 var dbValue: Double { timeIntervalSince1970 }38 init(dbValue: Double) { self.init(timeIntervalSince1970: dbValue) }39}4041enum Schema {42 static func migrator() -> DatabaseMigrator {43 var m = DatabaseMigrator()44 m.registerMigration("v1") { db in45 try db.execute(sql: """46 CREATE TABLE conversation(47 id TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL, folderID TEXT, isFavorite INTEGER NOT NULL, isArchived INTEGER NOT NULL,48 createdAt REAL NOT NULL, updatedAt REAL NOT NULL, deletedAt REAL, json TEXT NOT NULL);49 CREATE INDEX conversation_updated ON conversation(updatedAt);50 CREATE INDEX conversation_folder ON conversation(folderID);51 CREATE TABLE message(52 id TEXT PRIMARY KEY NOT NULL, conversationID TEXT NOT NULL, parentID TEXT, role TEXT NOT NULL,53 createdAt REAL NOT NULL, updatedAt REAL NOT NULL, deletedAt REAL, json TEXT NOT NULL);54 CREATE INDEX message_conversation ON message(conversationID, createdAt);55 CREATE INDEX message_parent ON message(parentID);56 CREATE INDEX message_updated ON message(updatedAt);57 CREATE TABLE entity(58 kind TEXT NOT NULL, id TEXT NOT NULL, updatedAt REAL NOT NULL, deletedAt REAL, json TEXT NOT NULL,59 PRIMARY KEY(kind, id));60 CREATE INDEX entity_updated ON entity(updatedAt);61 CREATE TABLE meta(key TEXT PRIMARY KEY NOT NULL, value TEXT NOT NULL);62 CREATE VIRTUAL TABLE search USING fts5(63 content, kind UNINDEXED, conversationID UNINDEXED, messageID UNINDEXED, role UNINDEXED,64 tokenize = "unicode61 remove_diacritics 2", prefix = '2 3');65 """)66 }67 return m68 }69}7071/// Entities kept in the generic `entity` table. All are Core types with `updatedAt` / `deletedAt`.72public protocol StoredEntity: Codable, Sendable {73 static var entityKind: SyncRecord.Kind { get }74 var id: UUID { get }75 var updatedAt: Date { get set }76 var deletedAt: Date? { get set }77}7879extension ChatFolder: StoredEntity { public static var entityKind: SyncRecord.Kind { .folder } }80extension PromptTemplate: StoredEntity { public static var entityKind: SyncRecord.Kind { .template } }81extension ModelWorkspace: StoredEntity { public static var entityKind: SyncRecord.Kind { .workspace } }82extension AgentConfiguration: StoredEntity { public static var entityKind: SyncRecord.Kind { .agent } }83extension KnowledgeCollection: StoredEntity { public static var entityKind: SyncRecord.Kind { .collection } }84