SPB Git

Repository Public MIT

Schema.swift 13.5 KB 242 lines swift

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
Files main
Menu

Theme