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/Schema.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 GRDB9091/// Versioned schema. A shipped migration is never edited: every change is a new `vN_…` migration appended to `migrations`.92/// `SchemaTests` pins a hash of the resulting schema so an accidental edit fails the build's tests.93public enum AstraSchema {94 public static let migrationNames: [String] = migrations.map(\.name)95 public static var currentVersion: Int { migrations.count }9697 static let migrations: [(name: String, body: @Sendable (Database) throws -> Void)] = [98 ("v1_initial", v1),99 ]100101 /// `through` limits the migrator to the first N migrations (tests build an old database, then upgrade it).102 static func migrator(through count: Int? = nil) -> DatabaseMigrator {103 var migrator = DatabaseMigrator()104 for migration in migrations.prefix(count ?? migrations.count) {105 migrator.registerMigration(migration.name, migrate: migration.body)106 }107 return migrator108 }109110 private static func fts(_ table: String, source: String, column: String, indexWhen condition: String? = nil) -> String {111 let insertWhen = condition.map { " WHEN \($0)" } ?? ""112 return """113 CREATE VIRTUAL TABLE \(table) USING fts5(\(column), tokenize = "unicode61 remove_diacritics 2");114 CREATE TRIGGER \(table)_ai AFTER INSERT ON \(source)\(insertWhen) BEGIN115 INSERT INTO \(table)(rowid, \(column)) VALUES (new.seq, new.\(column));116 END;117 CREATE TRIGGER \(table)_ad AFTER DELETE ON \(source) BEGIN118 DELETE FROM \(table) WHERE rowid = old.seq;119 END;120 """121 }122123 private static let v1: @Sendable (Database) throws -> Void = { db in124 try db.execute(sql: """125 CREATE TABLE preset(126 id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, system TEXT NOT NULL, parameters TEXT NOT NULL,127 createdAt REAL NOT NULL, updatedAt REAL NOT NULL);128129 CREATE TABLE conversation(130 seq INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE, title TEXT NOT NULL,131 createdAt REAL NOT NULL, updatedAt REAL NOT NULL, pinned INTEGER NOT NULL DEFAULT 0, archived INTEGER NOT NULL DEFAULT 0,132 model TEXT NOT NULL, systemPrompt TEXT NOT NULL DEFAULT '', parameters TEXT NOT NULL,133 contextWindowCap INTEGER, truncationStrategy TEXT NOT NULL, memoryEnabled INTEGER NOT NULL DEFAULT 1,134 presetID TEXT REFERENCES preset(id) ON DELETE SET NULL, routing TEXT, activeLeafID TEXT);135 CREATE INDEX conversation_list ON conversation(archived, pinned, updatedAt);136 CREATE INDEX conversation_preset ON conversation(presetID);137138 CREATE TABLE message(139 seq INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE,140 conversationID TEXT NOT NULL REFERENCES conversation(id) ON DELETE CASCADE,141 parentID TEXT REFERENCES message(id),142 role TEXT NOT NULL, text TEXT NOT NULL DEFAULT '', reasoning TEXT NOT NULL DEFAULT '', toolCalls TEXT,143 toolCallID TEXT, toolName TEXT, toolIsError INTEGER NOT NULL DEFAULT 0,144 status TEXT NOT NULL, error TEXT, model TEXT, reportedModel TEXT, servedBy TEXT, usage TEXT, stopReason TEXT, cost TEXT,145 ttft REAL, totalTime REAL, createdAt REAL NOT NULL, updatedAt REAL NOT NULL, bookmarked INTEGER NOT NULL DEFAULT 0);146 CREATE INDEX message_parent ON message(parentID, createdAt, id);147 CREATE INDEX message_conversation ON message(conversationID, createdAt, id);148 CREATE INDEX message_bookmarked ON message(createdAt) WHERE bookmarked = 1;149 CREATE INDEX message_streaming ON message(status) WHERE status = 'streaming';150151 CREATE TABLE attachment(152 id TEXT PRIMARY KEY NOT NULL, messageID TEXT NOT NULL REFERENCES message(id) ON DELETE CASCADE,153 kind TEXT NOT NULL, filename TEXT NOT NULL, mimeType TEXT NOT NULL, byteSize INTEGER NOT NULL, sha256 TEXT NOT NULL,154 extractedText TEXT, createdAt REAL NOT NULL);155 CREATE INDEX attachment_message ON attachment(messageID);156 CREATE INDEX attachment_sha ON attachment(sha256);157158 CREATE TABLE arenaSession(159 id TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL DEFAULT '', prompt TEXT NOT NULL, system TEXT NOT NULL DEFAULT '',160 parameters TEXT NOT NULL, blind INTEGER NOT NULL DEFAULT 0, createdAt REAL NOT NULL, templateID TEXT);161 CREATE INDEX arenaSession_created ON arenaSession(createdAt);162 CREATE TABLE arenaEntry(163 id TEXT PRIMARY KEY NOT NULL, sessionID TEXT NOT NULL REFERENCES arenaSession(id) ON DELETE CASCADE,164 ordinal INTEGER NOT NULL, model TEXT NOT NULL, reportedModel TEXT, servedBy TEXT, text TEXT NOT NULL DEFAULT '',165 status TEXT NOT NULL, usage TEXT, timeToFirstToken REAL, totalTime REAL, tokensPerSecond REAL, cost TEXT, error TEXT,166 stopReason TEXT, isWinner INTEGER NOT NULL DEFAULT 0, revealOrder INTEGER NOT NULL, createdAt REAL NOT NULL);167 CREATE INDEX arenaEntry_session ON arenaEntry(sessionID, ordinal);168169 CREATE TABLE agentProfile(170 id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, instructions TEXT NOT NULL, model TEXT NOT NULL, allowedTools TEXT NOT NULL,171 maxSteps INTEGER NOT NULL, maxToolCalls INTEGER NOT NULL, maxOutputTokens INTEGER NOT NULL, maxCostUSD REAL,172 memoryPolicy TEXT NOT NULL, createdAt REAL NOT NULL, updatedAt REAL NOT NULL);173 CREATE TABLE agentRun(174 id TEXT PRIMARY KEY NOT NULL, profileID TEXT REFERENCES agentProfile(id) ON DELETE SET NULL, profileName TEXT NOT NULL,175 model TEXT, goal TEXT NOT NULL, status TEXT NOT NULL, startedAt REAL NOT NULL, finishedAt REAL,176 stepCount INTEGER NOT NULL DEFAULT 0, toolCallCount INTEGER NOT NULL DEFAULT 0, usage TEXT NOT NULL, costUSD REAL,177 result TEXT, error TEXT);178 CREATE INDEX agentRun_started ON agentRun(startedAt);179 CREATE INDEX agentRun_profile ON agentRun(profileID);180 CREATE TABLE agentStep(181 id TEXT PRIMARY KEY NOT NULL, runID TEXT NOT NULL REFERENCES agentRun(id) ON DELETE CASCADE, ordinal INTEGER NOT NULL,182 kind TEXT NOT NULL, toolName TEXT, text TEXT NOT NULL DEFAULT '', inputJSON TEXT, outputJSON TEXT, approval TEXT,183 createdAt REAL NOT NULL, finishedAt REAL, usage TEXT, costUSD REAL, UNIQUE(runID, ordinal));184185 CREATE TABLE promptTemplate(186 id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, system TEXT NOT NULL, userTemplate TEXT NOT NULL, parameters TEXT NOT NULL,187 currentVersion INTEGER NOT NULL, createdAt REAL NOT NULL, updatedAt REAL NOT NULL);188 CREATE TABLE promptTemplateVersion(189 id TEXT PRIMARY KEY NOT NULL, templateID TEXT NOT NULL REFERENCES promptTemplate(id) ON DELETE CASCADE, version INTEGER NOT NULL,190 name TEXT NOT NULL, system TEXT NOT NULL, userTemplate TEXT NOT NULL, parameters TEXT NOT NULL, note TEXT,191 createdAt REAL NOT NULL, UNIQUE(templateID, version));192193 CREATE TABLE usage(194 id TEXT PRIMARY KEY NOT NULL, date REAL NOT NULL, provider TEXT NOT NULL, modelID TEXT NOT NULL, endpointID TEXT,195 inputTokens INTEGER NOT NULL DEFAULT 0, outputTokens INTEGER NOT NULL DEFAULT 0, cachedInputTokens INTEGER NOT NULL DEFAULT 0,196 reasoningTokens INTEGER NOT NULL DEFAULT 0, costUSD REAL, costBasis TEXT NOT NULL, origin TEXT NOT NULL, originID TEXT);197 CREATE INDEX usage_date ON usage(date);198199 CREATE TABLE diagnostic(seq INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE, startedAt REAL NOT NULL, json TEXT NOT NULL);200201 CREATE TABLE memory(202 id TEXT PRIMARY KEY NOT NULL, text TEXT NOT NULL, createdAt REAL NOT NULL, updatedAt REAL NOT NULL,203 enabled INTEGER NOT NULL DEFAULT 1, sourceConversationID TEXT REFERENCES conversation(id) ON DELETE SET NULL);204 CREATE INDEX memory_source ON memory(sourceConversationID);205206 CREATE TABLE document(207 id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, kind TEXT NOT NULL, byteSize INTEGER NOT NULL, sha256 TEXT NOT NULL,208 createdAt REAL NOT NULL);209 CREATE TABLE chunk(210 seq INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT NOT NULL UNIQUE,211 documentID TEXT NOT NULL REFERENCES document(id) ON DELETE CASCADE, ordinal INTEGER NOT NULL, text TEXT NOT NULL, embedding BLOB);212 CREATE INDEX chunk_document ON chunk(documentID, ordinal);213214 CREATE TABLE endpoint(id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, createdAt REAL NOT NULL, json TEXT NOT NULL);215 CREATE TABLE setting(key TEXT PRIMARY KEY NOT NULL, value TEXT NOT NULL);216 CREATE TABLE benchmark(217 id TEXT PRIMARY KEY NOT NULL, model TEXT NOT NULL, deviceModel TEXT NOT NULL, osVersion TEXT NOT NULL, modelRevision TEXT,218 isColdStart INTEGER NOT NULL, timeToFirstToken REAL, tokensPerSecond REAL, promptTokens INTEGER, outputTokens INTEGER,219 peakMemoryBytes INTEGER, thermalState TEXT NOT NULL, createdAt REAL NOT NULL, notes TEXT NOT NULL DEFAULT '');220 CREATE INDEX benchmark_created ON benchmark(createdAt);221 """)222223 // Full-text indexes keyed by the stable integer `seq` of their source row. Streaming messages are indexed once they224 // settle, so persisting partial text several times a second never touches the index.225 try db.execute(sql: fts("conversation_fts", source: "conversation", column: "title"))226 try db.execute(sql: """227 CREATE TRIGGER conversation_fts_au AFTER UPDATE OF title ON conversation BEGIN228 DELETE FROM conversation_fts WHERE rowid = old.seq;229 INSERT INTO conversation_fts(rowid, title) VALUES (new.seq, new.title);230 END;231 """)232 try db.execute(sql: fts("message_fts", source: "message", column: "text", indexWhen: "new.status <> 'streaming'"))233 try db.execute(sql: """234 CREATE TRIGGER message_fts_au AFTER UPDATE OF text, status ON message WHEN new.status <> 'streaming' BEGIN235 DELETE FROM message_fts WHERE rowid = old.seq;236 INSERT INTO message_fts(rowid, text) VALUES (new.seq, new.text);237 END;238 """)239 try db.execute(sql: fts("chunk_fts", source: "chunk", column: "text"))240 }241}242