Repository Public MIT
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Astra9// File : Packages/AstraPersistence/Sources/Conversations.swift10// Module : Packages11// Repository : https://git.spboucher.ai/orix-astra12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : Proprietary, all rights reserved (see license file)80// License File : LICENSE81// Maintainer : Simon-Pierre Boucher82// Technical Contact: dev@spboucher.ai83//84// =============================================================================85// END OF SP BOUCHER SOURCE HEADER86// =============================================================================8788import Foundation89import GRDB90import AstraCore9192public extension AstraStore {93 @discardableResult94 func createConversation(title: String = "", model: ModelRef, systemPrompt: String = "", parameters: GenerationParameters = .default,95 contextWindowCap: Int? = nil, truncationStrategy: TruncationStrategy = .dropOldest, memoryEnabled: Bool = true,96 presetID: String? = nil, routing: RoutingPreferences? = nil, at date: Date = Date()) async throws -> ConversationRecord {97 let record = ConversationRecord(id: AstraID.new(), title: title, createdAt: date, model: model, systemPrompt: systemPrompt,98 parameters: parameters, contextWindowCap: contextWindowCap, truncationStrategy: truncationStrategy,99 memoryEnabled: memoryEnabled, presetID: presetID, routing: routing)100 try await write { db in try record.insert(db) }101 return record102 }103104 func conversation(id: String) async throws -> ConversationRecord? { try await read { db in try ConversationRecord.find(db, id: id) } }105106 /// Atomic read-modify-write of a conversation's settings. `id`, `createdAt` and `activeLeafID` cannot be changed here.107 /// `touch` moves the conversation to the top of the list.108 @discardableResult109 func updateConversation(id: String, touch: Bool = false, at date: Date = Date(),110 _ mutate: @Sendable (inout ConversationRecord) -> Void) async throws -> ConversationRecord {111 try await write { db in112 let current = try Self.requireConversation(db, id)113 var updated = current114 mutate(&updated)115 updated.id = current.id; updated.createdAt = current.createdAt; updated.activeLeafID = current.activeLeafID116 if touch { updated.updatedAt = date }117 try updated.updateByID(db)118 return updated119 }120 }121122 func renameConversation(id: String, title: String) async throws {123 let clean = title.trimmingCharacters(in: .whitespacesAndNewlines)124 try await updateConversation(id: id) { $0.title = clean }125 }126127 func setConversationPinned(id: String, _ pinned: Bool) async throws { try await updateConversation(id: id) { $0.pinned = pinned } }128129 func setConversationArchived(id: String, _ archived: Bool) async throws {130 try await updateConversation(id: id) { $0.archived = archived; if archived { $0.pinned = false } }131 }132133 /// Pinned first, then most recently updated.134 func conversations(_ filter: ConversationFilter = .active, limit: Int? = nil) async throws -> [ConversationRecord] {135 try await read { db in136 let condition = switch filter {137 case .active: "WHERE archived = 0"138 case .archived: "WHERE archived = 1"139 case .all: ""140 }141 return try ConversationRecord.fetchAll(db, sql: """142 SELECT * FROM conversation \(condition) ORDER BY pinned DESC, updatedAt DESC, id LIMIT ?143 """, arguments: [limit ?? -1])144 }145 }146147 func conversationCount(_ filter: ConversationFilter = .all) async throws -> Int {148 try await read { db in149 switch filter {150 case .all: try ConversationRecord.fetchCount(db)151 case .active: try ConversationRecord.filter(Column("archived") == false).fetchCount(db)152 case .archived: try ConversationRecord.filter(Column("archived") == true).fetchCount(db)153 }154 }155 }156157 /// Deep copy: settings, every message of every branch (with new ids), attachments (files are shared by hash) and the active branch.158 @discardableResult159 func duplicateConversation(id: String, title: String? = nil, at date: Date = Date()) async throws -> ConversationRecord {160 try await write { db in161 let source = try Self.requireConversation(db, id)162 var copy = source163 copy.id = AstraID.new()164 copy.title = title ?? (source.title.isEmpty ? "" : source.title + " (copy)")165 copy.createdAt = date; copy.updatedAt = date; copy.pinned = false; copy.archived = false; copy.activeLeafID = nil166 try copy.insert(db)167 // Insertion order (seq) guarantees a parent is always copied before its children.168 let messages = try MessageRecord.fetch(db, "WHERE conversationID = ? ORDER BY seq", [id])169 var ids: [String: String] = [:]170 for var message in messages {171 let old = message.id172 message.id = AstraID.new(); ids[old] = message.id173 message.conversationID = copy.id174 message.parentID = message.parentID.flatMap { ids[$0] }175 if message.status == .streaming { message.status = .stopped; message.stopReason = .cancelled }176 try message.insert(db)177 }178 let attachments = try AttachmentRecord.fetchAll(db, sql: """179 SELECT a.* FROM attachment a JOIN message m ON m.id = a.messageID WHERE m.conversationID = ? ORDER BY a.createdAt, a.id180 """, arguments: [id])181 for var attachment in attachments {182 guard let messageID = ids[attachment.messageID] else { continue }183 attachment.id = AstraID.new(); attachment.messageID = messageID184 try attachment.insert(db)185 }186 copy.activeLeafID = source.activeLeafID.flatMap { ids[$0] }187 try db.execute(sql: "UPDATE conversation SET activeLeafID = ? WHERE id = ?", arguments: [copy.activeLeafID, copy.id])188 return copy189 }190 }191192 /// Deletes a conversation with its messages and attachment rows. Attachment files no other message uses are removed too.193 @discardableResult194 func deleteConversation(id: String) async throws -> DeletionResult { try await deleteConversations(ids: [id]) }195196 @discardableResult197 func deleteConversations(ids: [String]) async throws -> DeletionResult {198 let blobs = self.blobs199 return try await write { db in200 let before = try Self.usedBytes(db)201 var rows = 0202 for id in ids {203 try db.execute(sql: "DELETE FROM conversation WHERE id = ?", arguments: [id])204 rows += db.changesCount205 }206 let files = try blobs.removeOrphans(db)207 return DeletionResult(rowsDeleted: rows, bytesFreed: max(0, before - (try Self.usedBytes(db))) + files)208 }209 }210211 /// Sets the title from the first user message of the active branch when the conversation has none. Returns the new title.212 @discardableResult213 func autoTitleIfNeeded(conversationID: String) async throws -> String? {214 try await write { db in215 let conversation = try Self.requireConversation(db, conversationID)216 guard conversation.title.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty else { return nil }217 let path = try MessageRecord.path(db, conversationID: conversationID, limit: nil)218 guard let first = path.first(where: { $0.role == .user && !$0.text.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty }) else { return nil }219 let title = Self.suggestedTitle(from: first.text)220 guard !title.isEmpty else { return nil }221 try db.execute(sql: "UPDATE conversation SET title = ? WHERE id = ?", arguments: [title, conversationID])222 return title223 }224 }225226 /// First meaningful line of a message, without Markdown markers, cut at a word boundary.227 static func suggestedTitle(from text: String, maxLength: Int = 60) -> String {228 let line = text.split(whereSeparator: \.isNewline).map { $0.trimmingCharacters(in: .whitespaces) }229 .first { line in line.contains { $0.isLetter || $0.isNumber } } ?? ""230 var clean = line.trimmingCharacters(in: CharacterSet(charactersIn: "#>*-`_~ \t"))231 clean = clean.split(whereSeparator: \.isWhitespace).joined(separator: " ")232 guard clean.count > maxLength else { return clean }233 let cut = String(clean.prefix(maxLength))234 if let space = cut.lastIndex(of: " "), cut.distance(from: cut.startIndex, to: space) > maxLength / 2 {235 return cut[..<space].trimmingCharacters(in: CharacterSet(charactersIn: " ,;:.-")) + "…"236 }237 return cut + "…"238 }239240 /// Full-text search over titles and message text. Any text is accepted; nothing in it is interpreted as query syntax.241 /// Terms are matched as word prefixes; text without spaces between words (Chinese, Japanese...) or with no word at all242 /// (emoji, punctuation) is matched as a substring. One hit per conversation, title matches first, then best message match.243 func searchConversations(_ query: String, includeArchived: Bool = true, limit: Int = 50) async throws -> [ConversationSearchHit] {244 let trimmed = query.trimmingCharacters(in: .whitespacesAndNewlines)245 guard !trimmed.isEmpty else { return [] }246 return try await read { db in247 var titleIDs: [String] = []248 var messageHits: [(conversationID: String, messageID: String, snippet: String)] = []249 var usedIndex = false250 if let pattern = FTSQuery.pattern(trimmed), !FTSQuery.containsUnsegmentedScript(trimmed) {251 do {252 titleIDs = try String.fetchAll(db, sql: """253 SELECT c.id FROM conversation_fts JOIN conversation c ON c.seq = conversation_fts.rowid254 WHERE conversation_fts MATCH ? ORDER BY bm25(conversation_fts), c.updatedAt DESC LIMIT 200255 """, arguments: [pattern])256 messageHits = try Row.fetchAll(db, sql: """257 SELECT m.conversationID, m.id, snippet(message_fts, 0, '', '', '…', 16) FROM message_fts258 JOIN message m ON m.seq = message_fts.rowid259 WHERE message_fts MATCH ? ORDER BY bm25(message_fts), m.createdAt DESC LIMIT 500260 """, arguments: [pattern]).map { ($0[0], $0[1], $0[2]) }261 usedIndex = !(titleIDs.isEmpty && messageHits.isEmpty)262 } catch is DatabaseError {263 titleIDs = []; messageHits = []264 }265 }266 if !usedIndex {267 let like = FTSQuery.likePattern(trimmed)268 titleIDs = try String.fetchAll(db, sql: """269 SELECT id FROM conversation WHERE title LIKE ? ESCAPE '\\' ORDER BY updatedAt DESC LIMIT 200270 """, arguments: [like])271 messageHits = try Row.fetchAll(db, sql: """272 SELECT conversationID, id, text FROM message WHERE text LIKE ? ESCAPE '\\' AND status <> 'streaming'273 ORDER BY createdAt DESC LIMIT 500274 """, arguments: [like]).map { ($0[0], $0[1], FTSQuery.snippet(of: $0[2], around: trimmed)) }275 }276 var order: [String] = []277 var best: [String: (messageID: String?, snippet: String, title: Bool)] = [:]278 for id in titleIDs where best[id] == nil { best[id] = (nil, "", true); order.append(id) }279 for hit in messageHits {280 if let existing = best[hit.conversationID] {281 if existing.messageID == nil { best[hit.conversationID] = (hit.messageID, hit.snippet, existing.title) }282 } else {283 best[hit.conversationID] = (hit.messageID, hit.snippet, false); order.append(hit.conversationID)284 }285 }286 var out: [ConversationSearchHit] = []287 for id in order {288 guard out.count < limit, let hit = best[id], let conversation = try ConversationRecord.find(db, id: id) else { continue }289 if conversation.archived && !includeArchived { continue }290 out.append(ConversationSearchHit(conversation: conversation, messageID: hit.messageID,291 snippet: hit.messageID == nil ? conversation.title : hit.snippet, matchedTitle: hit.title))292 }293 return out294 }295 }296}297298extension AstraStore {299 static func requireConversation(_ db: Database, _ id: String) throws -> ConversationRecord {300 guard let record = try ConversationRecord.find(db, id: id) else { throw AstraStoreError.notFound("This conversation") }301 return record302 }303304 /// Bytes of the main database file holding data (free pages excluded).305 static func usedBytes(_ db: Database) throws -> Int64 {306 let pageSize = try Int64.fetchOne(db, sql: "PRAGMA page_size") ?? 0307 let pages = try Int64.fetchOne(db, sql: "PRAGMA page_count") ?? 0308 let free = try Int64.fetchOne(db, sql: "PRAGMA freelist_count") ?? 0309 return max(0, pages - free) * pageSize310 }311}312