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/Messages.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 AstraCore9192extension MessageRecord {93 static let columns = ["id", "conversationID", "parentID", "role", "text", "reasoning", "toolCalls", "toolCallID", "toolName", "toolIsError",94 "status", "error", "model", "reportedModel", "servedBy", "usage", "stopReason", "cost", "ttft", "totalTime",95 "createdAt", "updatedAt", "bookmarked"]96 static let selection = columns.map { "message." + $0 }.joined(separator: ", ")9798 /// Positional decoding in the order of `columns`: this is the hot path of opening a conversation.99 init(row: Row) throws {100 id = row[0]; conversationID = row[1]; parentID = row[2]101 guard let role = PromptMessage.Role(rawValue: row[3]) else { throw AstraStoreError.invalidOperation("Unknown message role.") }102 self.role = role103 text = row[4]; reasoningSummary = row[5]104 toolCalls = DBJSON.decodeIfPresent([ToolCall].self, row[6]) ?? []105 toolCallID = row[7]; toolName = row[8]; toolIsError = row[9]106 status = MessageStatus(rawValue: row[10]) ?? .complete107 error = DBJSON.decodeIfPresent(AstraError.self, row[11])108 model = (row[12] as String?).flatMap(ModelRef.init(id:))109 reportedModel = row[13]; servedBy = row[14]110 usage = DBJSON.decodeIfPresent(TokenUsage.self, row[15])111 stopReason = (row[16] as String?).flatMap(StopReason.init(rawValue:))112 cost = DBJSON.decodeIfPresent(CostEstimate.self, row[17])113 timeToFirstToken = row[18]; totalTime = row[19]114 createdAt = Date(dbValue: row[20]); updatedAt = Date(dbValue: row[21]); bookmarked = row[22]115 }116117 private func values() throws -> [(any DatabaseValueConvertible)?] {118 [id, conversationID, parentID, role.rawValue, text, reasoningSummary, toolCalls.isEmpty ? nil : try DBJSON.encode(toolCalls),119 toolCallID, toolName, toolIsError, status.rawValue, try error.map(DBJSON.encode), model?.id, reportedModel, servedBy,120 try usage.map(DBJSON.encode), stopReason?.rawValue, try cost.map(DBJSON.encode), timeToFirstToken, totalTime,121 createdAt.dbValue, updatedAt.dbValue, bookmarked]122 }123124 func insert(_ db: Database) throws {125 let statement = try db.cachedStatement(sql: """126 INSERT INTO message(\(Self.columns.joined(separator: ", "))) VALUES (\(Self.columns.map { _ in "?" }.joined(separator: ", ")))127 """)128 try statement.execute(arguments: StatementArguments(try values()))129 }130131 func update(_ db: Database) throws {132 let assignments = Self.columns.dropFirst().map { "\($0) = ?" }.joined(separator: ", ")133 try db.execute(sql: "UPDATE message SET \(assignments) WHERE id = ?", arguments: StatementArguments(Array(try values().dropFirst()) + [id]))134 }135136 static func fetch(_ db: Database, _ tail: String, _ arguments: StatementArguments = []) throws -> [MessageRecord] {137 let statement = try db.cachedStatement(sql: "SELECT \(selection) FROM message \(tail)")138 return try Array(Row.fetchCursor(statement, arguments: arguments).map { try MessageRecord(row: $0) })139 }140141 static func find(_ db: Database, _ id: String) throws -> MessageRecord? { try fetch(db, "WHERE id = ?", [id]).first }142143 static func require(_ db: Database, _ id: String) throws -> MessageRecord {144 guard let message = try find(db, id) else { throw AstraStoreError.notFound("This message") }145 return message146 }147148 /// Root → leaf. One recursive walk up the `parentID` chain, each step an index lookup.149 static func path(db: Database, toMessage leafID: String, limit: Int?) throws -> [MessageRecord] {150 let statement = try db.cachedStatement(sql: """151 WITH RECURSIVE path(id, depth) AS (152 SELECT ?, 0153 UNION ALL154 SELECT m.parentID, path.depth + 1 FROM path JOIN message m ON m.id = path.id155 WHERE m.parentID IS NOT NULL AND path.depth + 1 < ?)156 SELECT \(selection) FROM path JOIN message ON message.id = path.id ORDER BY path.depth DESC157 """)158 return try Array(Row.fetchCursor(statement, arguments: [leafID, limit ?? 1_000_000]).map { try MessageRecord(row: $0) })159 }160161 static func path(_ db: Database, conversationID: String, limit: Int?) throws -> [MessageRecord] {162 guard let leaf = try String.fetchOne(db, sql: "SELECT activeLeafID FROM conversation WHERE id = ?", arguments: [conversationID]) else { return [] }163 return try path(db: db, toMessage: leaf, limit: limit)164 }165166 /// Children of `parentID` (or the conversation's root messages), oldest first, ties broken by id.167 static func children(_ db: Database, conversationID: String, parentID: String?) throws -> [MessageRecord] {168 if let parentID { return try fetch(db, "WHERE parentID = ? ORDER BY createdAt, id", [parentID]) }169 return try fetch(db, "WHERE conversationID = ? AND parentID IS NULL ORDER BY createdAt, id", [conversationID])170 }171172 /// Follows the most recent child at every level down to a leaf.173 static func deepestLeaf(_ db: Database, from id: String) throws -> String {174 let statement = try db.cachedStatement(sql: "SELECT id FROM message WHERE parentID = ? ORDER BY createdAt DESC, id DESC LIMIT 1")175 var current = id176 while let next = try String.fetchOne(statement, arguments: [current]) { current = next }177 return current178 }179}180181public extension AstraStore {182 // MARK: Reading183184 /// The branch being shown, root → leaf. `last` returns only the most recent N messages of it.185 func activePath(conversationID: String, last: Int? = nil) async throws -> [MessageRecord] {186 try await read { db in try MessageRecord.path(db, conversationID: conversationID, limit: last) }187 }188189 /// Root → `messageID`, whatever the active branch is.190 func path(toMessage messageID: String) async throws -> [MessageRecord] {191 try await read { db in try MessageRecord.path(db: db, toMessage: messageID, limit: nil) }192 }193194 func message(id: String) async throws -> MessageRecord? { try await read { db in try MessageRecord.find(db, id) } }195196 /// Every message of every branch, in insertion order (a parent always precedes its children).197 func allMessages(conversationID: String) async throws -> [MessageRecord] {198 try await read { db in try MessageRecord.fetch(db, "WHERE conversationID = ? ORDER BY seq", [conversationID]) }199 }200201 func messageCount(conversationID: String) async throws -> Int {202 try await read { db in try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM message WHERE conversationID = ?", arguments: [conversationID]) ?? 0 }203 }204205 /// The alternatives at this position of the tree, including the message itself, oldest first (ties broken by id).206 func siblings(of messageID: String) async throws -> [MessageRecord] {207 try await read { db in208 let message = try MessageRecord.require(db, messageID)209 return try MessageRecord.children(db, conversationID: message.conversationID, parentID: message.parentID)210 }211 }212213 func children(of messageID: String) async throws -> [MessageRecord] {214 try await read { db in215 let message = try MessageRecord.require(db, messageID)216 return try MessageRecord.children(db, conversationID: message.conversationID, parentID: message.id)217 }218 }219220 /// Number of alternatives at each position of the active path that has more than one, keyed by message id: `(index, count)`.221 func branchPositions(conversationID: String) async throws -> [String: BranchPosition] {222 try await read { db in try Self.branchPositions(db, conversationID: conversationID) }223 }224225 /// Bookmarked messages, most recent first, in one conversation or everywhere.226 func bookmarks(conversationID: String? = nil) async throws -> [MessageRecord] {227 try await read { db in228 if let conversationID {229 return try MessageRecord.fetch(db, "WHERE bookmarked = 1 AND conversationID = ? ORDER BY createdAt DESC, id DESC", [conversationID])230 }231 return try MessageRecord.fetch(db, "WHERE bookmarked = 1 ORDER BY createdAt DESC, id DESC")232 }233 }234235 // MARK: Writing236237 /// Adds a user turn after the active leaf (or after `parentID`) and makes it the active leaf.238 @discardableResult239 func appendUserMessage(conversationID: String, text: String, attachments: [AttachmentDraft] = [], parentID: String? = nil,240 at date: Date = Date()) async throws -> MessageRecord {241 let blobs = self.blobs242 return try await write { db in243 let conversation = try Self.requireConversation(db, conversationID)244 let parent = try Self.resolveParent(db, conversation, parentID)245 let message = MessageRecord(id: AstraID.new(), conversationID: conversationID, parentID: parent, role: .user, text: text, createdAt: date)246 try Self.add(db, message, attachments: attachments, blobs: blobs)247 return message248 }249 }250251 /// Adds the result of a tool call after the active leaf and makes it the active leaf.252 @discardableResult253 func appendToolMessage(conversationID: String, text: String, toolCallID: String, toolName: String, isError: Bool = false,254 at date: Date = Date()) async throws -> MessageRecord {255 let blobs = self.blobs256 return try await write { db in257 let conversation = try Self.requireConversation(db, conversationID)258 let message = MessageRecord(id: AstraID.new(), conversationID: conversationID, parentID: conversation.activeLeafID, role: .tool,259 text: text, toolCallID: toolCallID, toolName: toolName, toolIsError: isError, createdAt: date)260 try Self.add(db, message, attachments: [], blobs: blobs)261 return message262 }263 }264265 /// Inserts an empty assistant turn in `streaming` state after the active leaf (or `parentID`) and makes it the active leaf.266 @discardableResult267 func beginAssistantMessage(conversationID: String, model: ModelRef, parentID: String? = nil, at date: Date = Date()) async throws -> MessageRecord {268 let blobs = self.blobs269 return try await write { db in270 let conversation = try Self.requireConversation(db, conversationID)271 let parent = try Self.resolveParent(db, conversation, parentID)272 let message = MessageRecord(id: AstraID.new(), conversationID: conversationID, parentID: parent, role: .assistant, status: .streaming,273 model: model, createdAt: date)274 try Self.add(db, message, attachments: [], blobs: blobs)275 return message276 }277 }278279 /// Persists the text received so far. One indexed `UPDATE` that skips the full-text index; meant to be called a few times a280 /// second so a crash or a kill loses at most the last fraction of a second. Ignored once the message is no longer streaming.281 func updateStreaming(id: String, text: String, reasoning: String = "", at date: Date = Date()) async throws {282 try await write { db in283 let statement = try db.cachedStatement(sql: "UPDATE message SET text = ?, reasoning = ?, updatedAt = ? WHERE id = ? AND status = 'streaming'")284 try statement.execute(arguments: [text, reasoning, date.dbValue, id])285 }286 }287288 /// Ends a generation: final text, identity, usage, cost and timings. With `recordUsage`, one usage ledger row is written in289 /// the same transaction.290 @discardableResult291 func finalizeAssistantMessage(id: String, _ completion: MessageCompletion, recordUsage: Bool = true, at date: Date = Date()) async throws -> MessageRecord {292 try await write { db in293 var message = try MessageRecord.require(db, id)294 guard message.role == .assistant else { throw AstraStoreError.invalidOperation("Only an assistant message can be finalized.") }295 message.text = completion.text; message.reasoningSummary = completion.reasoningSummary; message.toolCalls = completion.toolCalls296 message.status = completion.status == .streaming ? .complete : completion.status297 message.error = completion.error; message.reportedModel = completion.reportedModel; message.servedBy = completion.servedBy298 message.usage = completion.usage; message.stopReason = completion.stopReason; message.cost = completion.cost299 message.timeToFirstToken = completion.timeToFirstToken; message.totalTime = completion.totalTime; message.updatedAt = date300 try message.update(db)301 try db.execute(sql: "UPDATE conversation SET updatedAt = MAX(updatedAt, ?) WHERE id = ?", arguments: [date.dbValue, message.conversationID])302 if recordUsage, let model = message.model, completion.usage != nil || completion.cost != nil {303 try UsageRecord(id: AstraID.new(), date: date, model: model, usage: completion.usage, cost: completion.cost,304 origin: .conversation, originID: message.conversationID).insert(db)305 }306 return message307 }308 }309310 /// Partial response recovery, to run once at launch: any message still `streaming` belongs to a generation that died with the311 /// previous process. Its saved partial text is kept and it becomes `stopped`. Returns how many were recovered.312 @discardableResult313 func recoverInterruptedMessages(at date: Date = Date()) async throws -> Int {314 try await write { db in315 try db.execute(sql: "UPDATE message SET status = 'stopped', stopReason = 'cancelled', updatedAt = ? WHERE status = 'streaming'",316 arguments: [date.dbValue])317 return db.changesCount318 }319 }320321 /// Edit and resend: a new user message next to `messageID` (same parent) becomes the active leaf. The original branch is kept.322 @discardableResult323 func editAndResend(messageID: String, newText: String, attachments: [AttachmentDraft] = [], keepAttachments: Bool = true,324 at date: Date = Date()) async throws -> MessageRecord {325 let blobs = self.blobs326 return try await write { db in327 let original = try MessageRecord.require(db, messageID)328 guard original.role == .user else { throw AstraStoreError.invalidOperation("Only a user message can be edited and resent.") }329 let message = MessageRecord(id: AstraID.new(), conversationID: original.conversationID, parentID: original.parentID, role: .user,330 text: newText, createdAt: date)331 try Self.add(db, message, attachments: attachments, blobs: blobs)332 if keepAttachments {333 for var attachment in try AttachmentRecord.filter(Column("messageID") == messageID).order(Column("createdAt"), Column("id")).fetchAll(db) {334 attachment.id = AstraID.new(); attachment.messageID = message.id335 try attachment.insert(db)336 }337 }338 return message339 }340 }341342 /// Regenerate: a new `streaming` assistant message next to `assistantMessageID` (same parent) becomes the active leaf.343 @discardableResult344 func regenerate(assistantMessageID: String, model: ModelRef? = nil, at date: Date = Date()) async throws -> MessageRecord {345 let blobs = self.blobs346 return try await write { db in347 let original = try MessageRecord.require(db, assistantMessageID)348 guard original.role == .assistant else { throw AstraStoreError.invalidOperation("Only an assistant message can be regenerated.") }349 let conversation = try Self.requireConversation(db, original.conversationID)350 let message = MessageRecord(id: AstraID.new(), conversationID: original.conversationID, parentID: original.parentID, role: .assistant,351 status: .streaming, model: model ?? original.model ?? conversation.model, createdAt: date)352 try Self.add(db, message, attachments: [], blobs: blobs)353 return message354 }355 }356357 /// Shows the branch that goes through `messageID`: follows the most recent child at every level below it and makes that leaf358 /// active. Returns the new active leaf id.359 @discardableResult360 func selectBranch(messageID: String) async throws -> String {361 try await write { db in362 let message = try MessageRecord.require(db, messageID)363 let leaf = try MessageRecord.deepestLeaf(db, from: message.id)364 try db.execute(sql: "UPDATE conversation SET activeLeafID = ? WHERE id = ?", arguments: [leaf, message.conversationID])365 return leaf366 }367 }368369 /// Returns the new state.370 @discardableResult371 func toggleBookmark(messageID: String) async throws -> Bool {372 try await write { db in373 try db.execute(sql: "UPDATE message SET bookmarked = 1 - bookmarked WHERE id = ?", arguments: [messageID])374 guard let state = try Bool.fetchOne(db, sql: "SELECT bookmarked FROM message WHERE id = ?", arguments: [messageID]) else {375 throw AstraStoreError.notFound("This message")376 }377 return state378 }379 }380381 /// Changes the text of a settled message in place (no new branch), for example to fix a typo in a saved answer.382 func setMessageText(id: String, text: String, at date: Date = Date()) async throws {383 try await write { db in384 try db.execute(sql: "UPDATE message SET text = ?, updatedAt = ? WHERE id = ? AND status <> 'streaming'", arguments: [text, date.dbValue, id])385 }386 }387388 /// Deletes a message and everything below it. When the active branch went through it, the active leaf moves to the most recent389 /// remaining branch at the same position (or to the parent).390 @discardableResult391 func deleteMessageSubtree(messageID: String) async throws -> DeletionResult {392 let blobs = self.blobs393 return try await write { db in394 let message = try MessageRecord.require(db, messageID)395 let before = try Self.usedBytes(db)396 let subtree = "WITH RECURSIVE sub(id) AS (SELECT ? UNION ALL SELECT m.id FROM message m JOIN sub ON m.parentID = sub.id)"397 let activeLeaf = try String.fetchOne(db, sql: "SELECT activeLeafID FROM conversation WHERE id = ?", arguments: [message.conversationID])398 let wasActive = try activeLeaf.map { leaf in399 try Bool.fetchOne(db, sql: "\(subtree) SELECT EXISTS(SELECT 1 FROM sub WHERE id = ?)", arguments: [messageID, leaf]) ?? false400 } ?? false401 try db.execute(sql: "\(subtree) DELETE FROM message WHERE id IN (SELECT id FROM sub)", arguments: [messageID])402 let rows = db.changesCount403 if wasActive {404 var leaf: String?405 if let parent = message.parentID {406 leaf = try MessageRecord.deepestLeaf(db, from: parent)407 } else if let root = try String.fetchOne(db, sql: """408 SELECT id FROM message WHERE conversationID = ? AND parentID IS NULL ORDER BY createdAt DESC, id DESC LIMIT 1409 """, arguments: [message.conversationID]) {410 leaf = try MessageRecord.deepestLeaf(db, from: root)411 }412 try db.execute(sql: "UPDATE conversation SET activeLeafID = ? WHERE id = ?", arguments: [leaf, message.conversationID])413 }414 let files = try blobs.removeOrphans(db)415 return DeletionResult(rowsDeleted: rows, bytesFreed: max(0, before - (try Self.usedBytes(db))) + files)416 }417 }418}419420/// Position of a message among its alternatives: `index` is zero-based.421public struct BranchPosition: Sendable, Codable, Hashable {422 public var index: Int423 public var count: Int424 public var siblingIDs: [String]425}426427extension AstraStore {428 static func resolveParent(_ db: Database, _ conversation: ConversationRecord, _ parentID: String?) throws -> String? {429 guard let parentID else { return conversation.activeLeafID }430 let parent = try MessageRecord.require(db, parentID)431 guard parent.conversationID == conversation.id else {432 throw AstraStoreError.invalidOperation("A message can only follow a message of the same conversation.")433 }434 return parentID435 }436437 /// Inserts a message, makes it the active leaf and moves the conversation to the top of the list.438 static func add(_ db: Database, _ message: MessageRecord, attachments: [AttachmentDraft], blobs: BlobDirectory) throws {439 try message.insert(db)440 for draft in attachments { _ = try blobs.attach(db, draft, messageID: message.id, at: message.createdAt) }441 try db.execute(sql: "UPDATE conversation SET activeLeafID = ?, updatedAt = MAX(updatedAt, ?) WHERE id = ?",442 arguments: [message.id, message.createdAt.dbValue, message.conversationID])443 }444445 static func branchPositions(_ db: Database, conversationID: String) throws -> [String: BranchPosition] {446 var out: [String: BranchPosition] = [:]447 // Positions with alternatives are those whose parent has several children; the roots are handled apart.448 let rows = try Row.fetchAll(db, sql: """449 SELECT id, parentID FROM message WHERE conversationID = ?1 AND (450 (parentID IS NULL AND (SELECT COUNT(*) FROM message r WHERE r.conversationID = ?1 AND r.parentID IS NULL) > 1)451 OR parentID IN (SELECT parentID FROM message WHERE conversationID = ?1 AND parentID IS NOT NULL GROUP BY parentID HAVING COUNT(*) > 1))452 ORDER BY createdAt, id453 """, arguments: [conversationID])454 var groups: [String: [String]] = [:]455 for row in rows { groups[(row[1] as String?) ?? "", default: []].append(row[0]) }456 for ids in groups.values {457 for (index, id) in ids.enumerated() { out[id] = BranchPosition(index: index, count: ids.count, siblingIDs: ids) }458 }459 return out460 }461}462