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/AstraStore.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 AstraCore9192/// Tables a view can watch with `AstraStore.changes(in:)`.93public enum AstraTable: String, Sendable, Hashable, CaseIterable {94 case conversation, message, attachment, arenaSession, arenaEntry, agentProfile, agentRun, agentStep, promptTemplate,95 promptTemplateVersion, preset, usage, diagnostic, memory, document, chunk, endpoint, setting, benchmark9697 public static let conversations: Set<AstraTable> = [.conversation]98 public static let messages: Set<AstraTable> = [.message, .attachment]99 public static let arena: Set<AstraTable> = [.arenaSession, .arenaEntry]100 public static let agents: Set<AstraTable> = [.agentProfile, .agentRun, .agentStep]101 public static let promptStudio: Set<AstraTable> = [.promptTemplate, .promptTemplateVersion, .preset]102 public static let knowledge: Set<AstraTable> = [.document, .chunk]103 public static let all = Set(AstraTable.allCases)104}105106/// The app's local-first store: one SQLite database (WAL, foreign keys on) plus a directory of attachment files.107/// Every method is `async` and runs off the caller's actor; nothing here ever touches the network or holds a secret.108public final class AstraStore: Sendable {109 public struct Options: Sendable, Hashable {110 /// Largest attachment accepted, in bytes.111 public var maxAttachmentBytes: Int112 /// Number of request diagnostics kept (ring buffer).113 public var diagnosticsCapacity: Int114 public init(maxAttachmentBytes: Int = 25 * 1024 * 1024, diagnosticsCapacity: Int = 500) {115 self.maxAttachmentBytes = max(0, maxAttachmentBytes); self.diagnosticsCapacity = max(1, diagnosticsCapacity)116 }117 }118119 let writer: any DatabaseWriter120 let blobs: BlobDirectory121 public let options: Options122 /// Nil for an in-memory store.123 public let databaseURL: URL?124 public var attachmentsDirectory: URL { blobs.url }125 private let ownsAttachmentsDirectory: Bool126127 /// Opens (creating and migrating as needed) the database at `url`. Attachments default to an `Attachments` directory next to it.128 /// Opening does file I/O: call it off the main actor, or use `open(url:)`.129 public init(url: URL, attachmentsDirectory: URL? = nil, options: Options = Options()) throws {130 let directory = url.deletingLastPathComponent()131 try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)132 Self.protect(directory)133 writer = try DatabasePool(path: url.path, configuration: Self.configuration())134 blobs = BlobDirectory(url: attachmentsDirectory ?? directory.appendingPathComponent("Attachments", isDirectory: true),135 maxBytes: options.maxAttachmentBytes)136 self.options = options; databaseURL = url; ownsAttachmentsDirectory = false137 try blobs.prepare()138 try AstraSchema.migrator().migrate(writer)139 for suffix in ["", "-wal", "-shm"] { Self.protect(URL(fileURLWithPath: url.path + suffix)) }140 }141142 /// An in-memory database for tests and previews. Attachments go to a private temporary directory removed with the store.143 public init(inMemory options: Options, attachmentsDirectory: URL? = nil) throws {144 writer = try DatabaseQueue(configuration: Self.configuration())145 let directory = attachmentsDirectory146 ?? FileManager.default.temporaryDirectory.appendingPathComponent("AstraStore-" + UUID().uuidString, isDirectory: true)147 blobs = BlobDirectory(url: directory, maxBytes: options.maxAttachmentBytes)148 self.options = options; databaseURL = nil; ownsAttachmentsDirectory = attachmentsDirectory == nil149 try blobs.prepare()150 try AstraSchema.migrator().migrate(writer)151 }152153 deinit {154 if ownsAttachmentsDirectory { try? FileManager.default.removeItem(at: blobs.url) }155 }156157 public static func open(url: URL, attachmentsDirectory: URL? = nil, options: Options = Options()) async throws -> AstraStore {158 try AstraStore(url: url, attachmentsDirectory: attachmentsDirectory, options: options)159 }160161 private static func configuration() -> Configuration {162 var configuration = Configuration()163 configuration.foreignKeysEnabled = true164 configuration.busyMode = .timeout(5)165 configuration.label = "ai.spboucher.orix.astra.store"166 return configuration167 }168169 /// Readable after the first unlock, so background work (finishing a stream, a download) keeps working while the device is locked.170 static func protect(_ url: URL) {171 #if !os(macOS)172 try? FileManager.default.setAttributes([.protectionKey: FileProtectionType.completeUntilFirstUserAuthentication], ofItemAtPath: url.path)173 #endif174 }175176 func read<T: Sendable>(_ body: @Sendable (Database) throws -> T) async throws -> T { try await writer.read(body) }177 func write<T: Sendable>(_ body: @Sendable (Database) throws -> T) async throws -> T { try await writer.write(body) }178179 // MARK: Schema180181 /// Number of migrations applied to this database.182 public func schemaVersion() async throws -> Int { try await appliedMigrations().count }183184 public func appliedMigrations() async throws -> [String] {185 try await read { db in186 let applied = try AstraSchema.migrator().appliedMigrations(db)187 return AstraSchema.migrationNames.filter(applied.contains)188 }189 }190191 /// Runs SQLite's integrity check (tables, indexes, full-text indexes) and the foreign key check. Empty means healthy.192 /// It runs on the writer connection on purpose: FTS5's integrity method does not refresh the index structure a pooled193 /// reader cached during an earlier search, so the same check on a reader can report a mismatch that does not exist.194 public func integrityCheck() async throws -> [String] {195 try await writer.writeWithoutTransaction { db in196 var problems = try String.fetchAll(db, sql: "PRAGMA integrity_check").filter { $0 != "ok" }197 let violations = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM pragma_foreign_key_check") ?? 0198 if violations > 0 { problems.append("\(violations) foreign key violation(s)") }199 return problems200 }201 }202203 // MARK: Observation204205 /// Signals that something changed in one of `tables`. The stream carries no payload on purpose: SQLite reports changes after206 /// the fact and several can coalesce, so a consumer re-reads what it shows. One signal is sent at once so the first read207 /// happens in the same loop: `for await _ in store.changes(in: AstraTable.conversations) { reload() }`.208 /// Persisting streaming text signals `.message` at the rate `updateStreaming` is called.209 public func changes(in tables: Set<AstraTable>) -> AsyncStream<Void> {210 let observation = DatabaseRegionObservation(tracking: tables.map { Table($0.rawValue) })211 let writer = self.writer212 return AsyncStream(bufferingPolicy: .bufferingNewest(1)) { continuation in213 continuation.yield()214 let cancellable = observation.start(in: writer, onError: { _ in continuation.finish() }, onChange: { _ in continuation.yield() })215 continuation.onTermination = { _ in cancellable.cancel() }216 }217 }218219 // MARK: Settings220221 public func setting<T: Decodable & Sendable>(_ key: String, as type: T.Type = T.self) async throws -> T? {222 try await read { db in223 guard let text = try String.fetchOne(db, sql: "SELECT value FROM setting WHERE key = ?", arguments: [key]) else { return nil }224 return try? DBJSON.decode(T.self, text)225 }226 }227228 public func setting<T: Decodable & Sendable>(_ key: String, default fallback: T) async throws -> T { try await setting(key, as: T.self) ?? fallback }229230 /// Stores any `Codable` value as JSON; nil removes the key.231 public func setSetting<T: Encodable & Sendable>(_ key: String, _ value: T?) async throws {232 try await write { db in233 if let value {234 try db.execute(sql: "INSERT INTO setting(key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",235 arguments: [key, try DBJSON.encode(value)])236 } else {237 try db.execute(sql: "DELETE FROM setting WHERE key = ?", arguments: [key])238 }239 }240 }241242 public func removeSetting(_ key: String) async throws {243 try await write { db in try db.execute(sql: "DELETE FROM setting WHERE key = ?", arguments: [key]) }244 }245246 public func settingKeys() async throws -> [String] { try await read { db in try String.fetchAll(db, sql: "SELECT key FROM setting ORDER BY key") } }247248 static func allSettings(_ db: Database) throws -> [String: JSONValue] {249 var out: [String: JSONValue] = [:]250 for row in try Row.fetchAll(db, sql: "SELECT key, value FROM setting") {251 let value: String = row[1]252 out[row[0]] = JSONValue.parse(value) ?? .string(value)253 }254 return out255 }256257 // MARK: Endpoint profiles258259 /// Saves a custom endpoint after validating its URL and headers. The API key is not part of the profile: it lives in the Keychain.260 public func saveEndpoint(_ profile: EndpointProfile) async throws {261 _ = try profile.validated()262 try await write { db in263 try db.execute(sql: """264 INSERT INTO endpoint(id, name, createdAt, json) VALUES (?, ?, ?, ?)265 ON CONFLICT(id) DO UPDATE SET name = excluded.name, json = excluded.json266 """, arguments: [profile.id, profile.name, profile.createdAt.dbValue, try DBJSON.encode(profile)])267 }268 }269270 public func endpoints() async throws -> [EndpointProfile] { try await read { db in try Self.endpoints(db) } }271272 public func endpoint(id: String) async throws -> EndpointProfile? {273 try await read { db in274 DBJSON.decodeIfPresent(EndpointProfile.self, try String.fetchOne(db, sql: "SELECT json FROM endpoint WHERE id = ?", arguments: [id]))275 }276 }277278 public func deleteEndpoint(id: String) async throws {279 try await write { db in try db.execute(sql: "DELETE FROM endpoint WHERE id = ?", arguments: [id]) }280 }281282 static func endpoints(_ db: Database) throws -> [EndpointProfile] {283 try String.fetchAll(db, sql: "SELECT json FROM endpoint ORDER BY name COLLATE NOCASE, id").compactMap { try? DBJSON.decode(EndpointProfile.self, $0) }284 }285286 // MARK: Diagnostics287288 /// Appends one sanitized request record and drops the oldest beyond `options.diagnosticsCapacity`.289 public func recordDiagnostic(_ diagnostic: RequestDiagnostic) async throws {290 let capacity = options.diagnosticsCapacity291 var clean = diagnostic292 clean.errorTitle = diagnostic.errorTitle.map(Redactor.redact)293 clean.requestID = diagnostic.requestID.map(Redactor.redact)294 let record = clean295 try await write { db in296 try db.execute(sql: "INSERT OR REPLACE INTO diagnostic(id, startedAt, json) VALUES (?, ?, ?)",297 arguments: [record.id.uuidString, record.startedAt.dbValue, try DBJSON.encode(record)])298 // Everything older than the Nth newest row goes (a replaced id leaves a gap in `seq`, so counting rows is the only safe way).299 try db.execute(sql: "DELETE FROM diagnostic WHERE seq < (SELECT seq FROM diagnostic ORDER BY seq DESC LIMIT 1 OFFSET ?)", arguments: [capacity - 1])300 }301 }302303 /// Most recent first.304 public func diagnostics(limit: Int = 500) async throws -> [RequestDiagnostic] { try await read { db in try Self.diagnostics(db, limit: limit) } }305306 static func diagnostics(_ db: Database, limit: Int) throws -> [RequestDiagnostic] {307 try String.fetchAll(db, sql: "SELECT json FROM diagnostic ORDER BY seq DESC LIMIT ?", arguments: [limit])308 .compactMap { try? DBJSON.decode(RequestDiagnostic.self, $0) }309 }310311 // MARK: Memory312313 @discardableResult314 public func addMemory(text: String, sourceConversationID: String? = nil, enabled: Bool = true, at date: Date = Date()) async throws -> MemoryRecord {315 let record = MemoryRecord(id: AstraID.new(), text: text, createdAt: date, updatedAt: date, enabled: enabled, sourceConversationID: sourceConversationID)316 try await write { db in try record.insert(db) }317 return record318 }319320 public func updateMemory(id: String, text: String? = nil, enabled: Bool? = nil, at date: Date = Date()) async throws {321 try await write { db in322 var record = try MemoryRecord.require(db, id: id)323 if let text { record.text = text }324 if let enabled { record.enabled = enabled }325 record.updatedAt = date326 try record.updateByID(db)327 }328 }329330 public func deleteMemory(id: String) async throws { try await write { db in _ = try MemoryRecord.remove(db, id: id) } }331332 /// Newest first. `enabledOnly` returns what may be given to a model.333 public func memories(enabledOnly: Bool = false) async throws -> [MemoryRecord] {334 try await read { db in335 try MemoryRecord.fetchAll(db, sql: "SELECT * FROM memory \(enabledOnly ? "WHERE enabled = 1" : "") ORDER BY createdAt DESC, id")336 }337 }338339 // MARK: Benchmarks340341 public func saveBenchmark(_ record: BenchmarkRecord) async throws { try await write { db in try record.save(db) } }342343 public func benchmarks(model: ModelRef? = nil) async throws -> [BenchmarkRecord] {344 try await read { db in345 let all = try BenchmarkRecord.fetchAll(db, sql: "SELECT * FROM benchmark ORDER BY createdAt DESC, id")346 return model.map { wanted in all.filter { $0.model == wanted } } ?? all347 }348 }349350 public func deleteBenchmark(id: String) async throws { try await write { db in _ = try BenchmarkRecord.remove(db, id: id) } }351352 public func deleteAllBenchmarks() async throws -> Int { try await write { db in try BenchmarkRecord.deleteAll(db) } }353354 public func exportBenchmarks(_ format: ExportFormat) async throws -> String {355 let records = try await benchmarks()356 return try Self.render(benchmarks: records, format)357 }358359 static func render(benchmarks records: [BenchmarkRecord], _ format: ExportFormat) throws -> String {360 let header = ["date", "provider", "model", "model_revision", "device", "os", "start", "ttft_s", "tokens_per_second", "prompt_tokens",361 "output_tokens", "peak_memory_bytes", "thermal_state", "notes"]362 let rows = records.map { r in363 [ExportCoding.iso(r.createdAt), r.model.provider.rawValue, r.model.modelID, r.modelRevision ?? "", r.deviceModel, r.osVersion,364 r.isColdStart ? "cold" : "warm", CSVWriter.number(r.timeToFirstToken), CSVWriter.number(r.tokensPerSecond),365 CSVWriter.number(r.promptTokens), CSVWriter.number(r.outputTokens), CSVWriter.number(r.peakMemoryBytes), r.thermalState, r.notes]366 }367 switch format {368 case .json: return String(decoding: try ExportCoding.encoder.encode(records), as: UTF8.self)369 case .csv: return CSVWriter.document(header: header, rows: rows)370 case .markdown: return Markdown.table(header: header, rows: rows)371 }372 }373}374375enum Markdown {376 static func cell(_ text: String) -> String {377 text.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "|", with: "\\|").replacingOccurrences(of: "\r\n", with: " ")378 .replacingOccurrences(of: "\n", with: " ")379 }380 static func table(header: [String], rows: [[String]]) -> String {381 var lines = ["| " + header.map(cell).joined(separator: " | ") + " |", "|" + header.map { _ in " --- |" }.joined()]382 lines += rows.map { "| " + $0.map(cell).joined(separator: " | ") + " |" }383 return lines.joined(separator: "\n") + "\n"384 }385 /// A fence longer than any run of backticks in the text, so content can never close it early.386 static func fenced(_ text: String, language: String = "") -> String {387 var longest = 0, run = 0388 for character in text { if character == "`" { run += 1; longest = max(longest, run) } else { run = 0 } }389 let fence = String(repeating: "`", count: max(3, longest + 1))390 return fence + language + "\n" + text + (text.hasSuffix("\n") ? "" : "\n") + fence391 }392}393