Repository Public License
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX OS Automation9// File : Packages/ORIXPersistence/Tests/ORIXPersistenceTests/StoreTests.swift10// Description : Integration tests of ORIXStore on real SQLite files: runs,11// events, receipts, documents, workflows, grants,12// aggregates, notifications.13// Module : ORIXPersistenceTests14// Repository : https://git.spboucher.ai/orix-automation15// Created : 2026-11-2116// Last Modified : 2026-11-2117//18// =============================================================================19// AUTHOR & PROFESSIONAL AFFILIATION20// =============================================================================21//22// Author : Simon-Pierre Boucher23// Position : Professor of Finance24// Institution : Université du Québec en Outaouais (UQO)25// Department : Département des sciences administratives26// Organization : SP Boucher27//28// Website : https://spboucher.ai29// Git : https://git.spboucher.ai30//31// =============================================================================32// OFFICIAL CONTACT DIRECTORY33// =============================================================================34//35// General : info@spboucher.ai36// Professional : contact@spboucher.ai37// Direct : simon@spboucher.ai38// Research : research@spboucher.ai39// Development : dev@spboucher.ai40// Support : support@spboucher.ai41// Security/Admin : admin@spboucher.ai42// Notifications : notifications@spboucher.ai43//44// Preferred Contact: support@spboucher.ai45//46// =============================================================================47// COPYRIGHT & INTELLECTUAL PROPERTY48// =============================================================================49//50// Copyright (c) 202651// Simon-Pierre Boucher52//53// All rights reserved where applicable.54//55// This file is governed by the applicable project license,56// copyright notices, institutional agreements and relevant law.57//58// Unless expressly authorized by the applicable license or59// rights holder, copying, redistribution, sublicensing,60// publication and commercial exploitation are not permitted.61//62// Existing third-party rights and notices remain unaffected.63//64// =============================================================================65// CONFIDENTIALITY & SECURITY66// =============================================================================67//68// Classification : Public69// Distribution : Public repository; use is governed by the project license70// Security Contact : admin@spboucher.ai71//72// Access to this source does not independently grant permission73// to redistribute or disclose confidential materials.74//75// Do not embed credentials, private keys, access tokens,76// certificates or confidential personal information.77//78// =============================================================================79// LICENSE & MAINTENANCE80// =============================================================================81//82// License : Proprietary, all rights reserved (see license file)83// License File : LICENSE84// Maintainer : Simon-Pierre Boucher85// Technical Contact: dev@spboucher.ai86//87// =============================================================================88// END OF SP BOUCHER SOURCE HEADER89// =============================================================================9091import Foundation92import ORIXCore93import ORIXTestingSupport94import Testing95@testable import ORIXPersistence9697func makeRun(_ id: String, title: String = "Run", status: RunStatus = .queued, workflow: String? = nil, queuedAt: Date = Date(),98 tokens: Int = 0, cost: Double = 0, error: String? = nil) -> RunRecord {99 var run = RunRecord(id: id, kind: workflow == nil ? "agent" : "workflow", title: title, workflowID: workflow, mode: .guided, status: status,100 model: ModelRef(provider: .anthropic, model: "test-model"), queuedAt: queuedAt, errorSummary: error)101 run.usage.tokens = TokenUsage(inputTokens: tokens)102 run.usage.costUSD = cost103 if status.isTerminal { run.startedAt = queuedAt; run.endedAt = queuedAt.addingTimeInterval(5) }104 return run105}106107struct Note: Codable, Sendable, Hashable {108 var title: String109 var body: String110 var count: Int111 var when: Date112}113114@Suite("ORIXStore runs, events, receipts (integration, real SQLite file)")115struct StoreRunTests {116 @Test func runRoundTripUpsertAndFilters() async throws {117 let tmp = try TempDirectory(); defer { tmp.remove() }118 let store = try ORIXStore(url: tmp.file("db/orix.sqlite"))119 #expect(store.recoveryNote == nil)120 let base = Date()121 var a = makeRun("a", title: "Weekly report", workflow: "wf-1", queuedAt: base.addingTimeInterval(-30))122 let b = makeRun("b", title: "Sort downloads", status: .failed, queuedAt: base.addingTimeInterval(-20), error: "Disk 100% full")123 let c = makeRun("c", title: "Rename_files", status: .succeeded, workflow: "wf-1", queuedAt: base.addingTimeInterval(-10))124 for run in [a, b, c] { try await store.saveRun(run) }125 #expect(try await store.run(id: "a") == a)126 #expect(try await store.run(id: "missing") == nil)127128 a.status = .running; a.startedAt = base; a.usage.turns = 3129 try await store.saveRun(a)130 #expect(try await store.run(id: "a") == a)131 #expect(try await store.runs().map(\.id) == ["c", "b", "a"])132 #expect(try await store.runs(limit: 1, offset: 1).map(\.id) == ["b"])133 #expect(try await store.runs(status: .failed).map(\.id) == ["b"])134 #expect(try await store.runs(workflowID: "wf-1").map(\.id) == ["c", "a"])135 #expect(try await store.runs(search: "REPORT").map(\.id) == ["a"])136 #expect(try await store.runs(search: "disk 100%").map(\.id) == ["b"])137 // LIKE wildcards in the query are literal.138 #expect(try await store.runs(search: "%").map(\.id) == ["b"])139 #expect(try await store.runs(search: "Rename_").map(\.id) == ["c"])140 #expect(try await store.runs(search: "Sort_").isEmpty)141 #expect(try await store.runs(status: .succeeded, workflowID: "wf-1", search: "rename").map(\.id) == ["c"])142 #expect(try await store.activeRuns().map(\.id) == ["a"])143 }144145 @Test func eventsGetMonotonicSequencePerRun() async throws {146 let tmp = try TempDirectory(); defer { tmp.remove() }147 let store = try ORIXStore(url: tmp.file("orix.sqlite"))148 for i in 1...5 {149 await store.emit(RunEvent(runID: "r1", seq: 999, kind: .log, stepID: i == 2 ? "step-a" : nil, text: "line \(i)", data: ["i": .int(i)]))150 }151 await store.emit(RunEvent(runID: "r2", kind: .statusChanged, text: "other run"))152 let events = try await store.events(runID: "r1")153 #expect(events.map(\.seq) == [1, 2, 3, 4, 5])154 #expect(events.map(\.text) == (1...5).map { "line \($0)" })155 #expect(events[1].stepID == "step-a")156 #expect(events[4].data == ["i": 5])157 #expect(try await store.events(runID: "r2").map(\.seq) == [1])158 #expect(try await store.events(runID: "r1", afterSeq: 3).map(\.seq) == [4, 5])159 #expect(try await store.events(runID: "r1", afterSeq: 1, limit: 2).map(\.seq) == [2, 3])160 #expect(try await store.eventCount(runID: "r1") == 5)161 let original = RunEvent(runID: "r1", kind: .toolFinished, text: "done", data: ["status": "succeeded"])162 let stored = try await store.append(original)163 #expect(stored.seq == 6)164 #expect(try await store.events(runID: "r1", afterSeq: 5) == [stored])165 }166167 @Test func concurrentEmitsFromTwoConnectionsNeverCollide() async throws {168 let tmp = try TempDirectory(); defer { tmp.remove() }169 let url = tmp.file("orix.sqlite")170 let a = try ORIXStore(url: url)171 let b = try ORIXStore(url: url)172 await withTaskGroup(of: Void.self) { group in173 for i in 0..<300 {174 group.addTask { await (i % 2 == 0 ? a : b).emit(RunEvent(runID: "shared", kind: .log, text: "e\(i)")) }175 if i % 3 == 0 { group.addTask { await a.emit(RunEvent(runID: "other", kind: .log, text: "o\(i)")) } }176 }177 }178 let events = try await b.events(runID: "shared", limit: 1000)179 #expect(events.count == 300)180 #expect(events.map(\.seq) == Array(1...300))181 #expect(Set(events.map(\.text)).count == 300)182 #expect(try await a.events(runID: "other", limit: 1000).map(\.seq) == Array(1...100))183 }184185 @Test func deletingARunCascadesToEventsAndReceipt() async throws {186 let tmp = try TempDirectory(); defer { tmp.remove() }187 let store = try ORIXStore(url: tmp.file("orix.sqlite"))188 for id in ["keep", "drop"] {189 try await store.saveRun(makeRun(id, status: .succeeded))190 for _ in 0..<4 { await store.emit(RunEvent(runID: id, kind: .log, text: id)) }191 try await store.saveReceipt(RunReceipt(runID: id, title: id, status: .succeeded, objective: "o", model: nil, startedAt: Date(), endedAt: Date(), usage: .init()))192 }193 #expect(try await store.deleteRun(id: "drop"))194 #expect(try await !store.deleteRun(id: "drop"))195 #expect(try await store.run(id: "drop") == nil)196 #expect(try await store.eventCount(runID: "drop") == 0)197 #expect(try await store.receipt(runID: "drop") == nil)198 #expect(try await store.eventCount(runID: "keep") == 4)199 #expect(try await store.receipt(runID: "keep") != nil)200 }201202 @Test func receiptRoundTrip() async throws {203 let store = try ORIXStore.inMemory()204 var usage = RunUsage(); usage.turns = 4; usage.costUSD = 0.0123; usage.costIsComplete = false205 let receipt = RunReceipt(runID: "r", title: "T", status: .partiallySucceeded, objective: "Do it", model: ModelRef(provider: .openai, model: "m"),206 startedAt: Date(timeIntervalSinceReferenceDate: 800_000_000.125), endedAt: Date(timeIntervalSinceReferenceDate: 800_000_100.5),207 usage: usage,208 toolCalls: [ToolCallRecord(id: "c1", toolID: "filesystem.read", arguments: ["path": "/tmp/a"], status: .succeeded, summary: "read", durationMs: 12, tier: .readOnly)],209 approvals: ["approved once"], permissionsUsed: ["file.read → /tmp"],210 artifacts: [Artifact(path: "/tmp/out.pdf", bytes: 10, sha256: "abc")],211 verifications: [VerificationResult(assertion: "exists", kind: "file_exists", passed: true, evidence: "10 bytes")],212 agentSummary: "done", limitations: ["none"], errors: ["one"])213 try await store.saveReceipt(receipt)214 #expect(try await store.receipt(runID: "r") == receipt)215 var changed = receipt; changed.status = .succeeded216 try await store.saveReceipt(changed)217 #expect(try await store.receipt(runID: "r")?.status == .succeeded)218 }219220 @Test func crashRecoveryMarksInterruptedRuns() async throws {221 let tmp = try TempDirectory(); defer { tmp.remove() }222 let url = tmp.file("orix.sqlite")223 do {224 // The "crashed" process: leaves runs in flight and never finishes them.225 let crashed = try ORIXStore(url: url)226 try await crashed.saveRun(makeRun("running", status: .running))227 try await crashed.saveRun(makeRun("queued", status: .queued))228 try await crashed.saveRun(makeRun("waiting", status: .waitingApproval, error: "needs approval"))229 try await crashed.saveRun(makeRun("done", status: .succeeded))230 try await crashed.saveRun(makeRun("failed", status: .failed))231 }232 try await Task.sleep(for: .milliseconds(20))233 let processStart = Date()234 let store = try ORIXStore(url: url)235 // A run started by the new process after it launched must not be touched.236 try await store.saveRun(makeRun("fresh", status: .running))237238 let marked = try await store.markInterruptedRuns(olderThan: processStart)239 #expect(Set(marked.map(\.id)) == ["running", "queued", "waiting"])240 #expect(marked.allSatisfy { $0.status == .recoveryRequired })241 let running = try #require(try await store.run(id: "running"))242 #expect(running.status == .recoveryRequired)243 #expect(running.errorSummary?.contains("Interrupted while RUNNING") == true)244 #expect(try await store.run(id: "waiting")?.errorSummary == "needs approval")245 #expect(try await store.run(id: "done")?.status == .succeeded)246 #expect(try await store.run(id: "failed")?.status == .failed)247 #expect(try await store.run(id: "fresh")?.status == .running)248 #expect(try await store.activeRuns().map(\.id) == ["fresh"])249 #expect(try await store.runs(status: .recoveryRequired).count == 3)250 // Idempotent: nothing left to mark.251 #expect(try await store.markInterruptedRuns(olderThan: processStart).isEmpty)252 }253254 @Test func usageAggregatesAndStatusCounts() async throws {255 let store = try ORIXStore.inMemory()256 let calendar = Calendar.current257 let noon = calendar.date(bySettingHour: 12, minute: 0, second: 0, of: Date())!258 let yesterday = calendar.date(byAdding: .day, value: -1, to: noon)!259 let old = calendar.date(byAdding: .day, value: -40, to: noon)!260 try await store.saveRun(makeRun("t1", status: .succeeded, queuedAt: noon, tokens: 1000, cost: 0.25))261 try await store.saveRun(makeRun("t2", status: .failed, queuedAt: noon, tokens: 500, cost: 0.10))262 try await store.saveRun(makeRun("t3", status: .canceled, queuedAt: noon))263 try await store.saveRun(makeRun("y1", status: .timedOut, queuedAt: yesterday, tokens: 70, cost: 0.01))264 try await store.saveRun(makeRun("o1", status: .succeeded, queuedAt: old, tokens: 9999, cost: 9))265266 let usage = try await store.usageByDay(days: 7)267 #expect(usage.count == 7)268 #expect(usage.map(\.date) == usage.map(\.date).sorted())269 let today = try #require(usage.last)270 #expect(today.date == calendar.startOfDay(for: Date()))271 #expect(today.runs == 3 && today.succeeded == 1 && today.failed == 1 && today.tokens == 1500)272 #expect(abs(today.costUSD - 0.35) < 1e-9)273 let previous = usage[5]274 #expect(previous.runs == 1 && previous.failed == 1 && previous.succeeded == 0 && previous.tokens == 70)275 #expect(usage.prefix(5).allSatisfy { $0.runs == 0 && $0.costUSD == 0 })276 #expect(today.day.count == 10)277278 #expect(try await store.statusCounts() == [.succeeded: 2, .failed: 1, .canceled: 1, .timedOut: 1])279 #expect(try await store.statusCounts(sinceDays: 7) == [.succeeded: 1, .failed: 1, .canceled: 1, .timedOut: 1])280 }281282 @Test func pruneRemovesOnlyOldFinishedRuns() async throws {283 let store = try ORIXStore.inMemory()284 let old = Date().addingTimeInterval(-40 * 86_400)285 try await store.saveRun(makeRun("old-done", status: .succeeded, queuedAt: old))286 try await store.saveRun(makeRun("old-waiting", status: .waitingApproval, queuedAt: old))287 try await store.saveRun(makeRun("new-done", status: .succeeded))288 await store.emit(RunEvent(runID: "old-done", kind: .log))289 await store.emit(RunEvent(runID: "new-done", kind: .log))290 #expect(try await store.pruneRuns(olderThanDays: 30) == 1)291 #expect(try await store.runs().map(\.id).sorted() == ["new-done", "old-waiting"])292 #expect(try await store.eventCount(runID: "old-done") == 0)293 #expect(try await store.eventCount(runID: "new-done") == 1)294 #expect(try await store.pruneRuns(olderThanDays: 30) == 0)295 }296}297298@Suite("ORIXStore documents and workflows (integration, real SQLite)")299struct StoreDocumentTests {300 @Test func documentsRoundTripOrderAndRemoval() async throws {301 let tmp = try TempDirectory(); defer { tmp.remove() }302 let store = try ORIXStore(url: tmp.file("orix.sqlite"))303 let when = Date(timeIntervalSinceReferenceDate: 812_345_678.987654)304 let first = Note(title: "First", body: "héllo \"quoted\"\nline", count: 1, when: when)305 let second = Note(title: "Second", body: "", count: 2, when: when)306 try await store.put(first, collection: "notes", id: "1")307 try await Task.sleep(for: .milliseconds(5))308 try await store.put(second, collection: "notes", id: "2")309 try await store.put(AgentProfile(id: "agent-1", name: "Worker", instructions: "Work.", model: ModelRef(provider: .anthropic, model: "m")),310 collection: "agents", id: "agent-1")311 #expect(try await store.get(Note.self, collection: "notes", id: "1") == first)312 #expect(try await store.get(Note.self, collection: "notes", id: "nope") == nil)313 #expect(try await store.get(Note.self, collection: "other", id: "1") == nil)314 #expect(try await store.all(Note.self, collection: "notes") == [second, first])315 #expect(try await store.get(AgentProfile.self, collection: "agents", id: "agent-1")?.name == "Worker")316317 // Updating moves a document to the front and keeps a single copy.318 try await Task.sleep(for: .milliseconds(5))319 var edited = first; edited.count = 10320 try await store.put(edited, collection: "notes", id: "1")321 #expect(try await store.all(Note.self, collection: "notes") == [edited, second])322 #expect(try await store.count(collection: "notes") == 2)323 #expect(try await store.collections() == ["agents", "notes"])324325 #expect(try await store.remove(collection: "notes", id: "2"))326 #expect(try await !store.remove(collection: "notes", id: "2"))327 #expect(try await store.count(collection: "notes") == 1)328 #expect(try await store.count(collection: "empty") == 0)329 await #expect(throws: StoreError.self) { try await store.put(first, collection: "", id: "x") }330 }331332 @Test func workflowVersionHistorySemantics() async throws {333 let tmp = try TempDirectory(); defer { tmp.remove() }334 let store = try ORIXStore(url: tmp.file("orix.sqlite"))335 #expect(try await store.workflows().isEmpty)336 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Report", source: "steps: []", format: "yaml", note: "first") == 1)337 // Identical source: no new version; the name still follows.338 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Weekly report", source: "steps: []", format: "yaml", note: "ignored") == 1)339 #expect(try await store.workflowVersions(workflowID: "wf").count == 1)340 #expect(try await store.workflow(id: "wf")?.name == "Weekly report")341 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Weekly report", source: "steps: [a]", format: "yaml", note: "second") == 2)342 // Same text in another format is a different version; going back to an old source appends, never rewrites.343 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Weekly report", source: "steps: [a]", format: "json") == 3)344 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Weekly report", source: "steps: []", format: "yaml", note: "revert") == 4)345346 let history = try await store.workflowVersions(workflowID: "wf")347 #expect(history.map(\.version) == [4, 3, 2, 1])348 #expect(history.map(\.note) == ["revert", "", "second", "first"])349 #expect(history.map(\.format) == ["yaml", "json", "yaml", "yaml"])350 #expect(try await store.workflowSource(workflowID: "wf", version: nil) == "steps: []")351 #expect(try await store.workflowSource(workflowID: "wf", version: 2) == "steps: [a]")352 #expect(try await store.workflowSource(workflowID: "wf", version: 9) == nil)353 #expect(try await store.workflowSource(workflowID: "none", version: nil) == nil)354355 var row = try #require(try await store.workflow(id: "wf"))356 #expect(row.currentVersion == 4 && row.publishedVersion == nil)357 try await store.publish(workflowID: "wf", version: 2)358 row = try #require(try await store.workflow(id: "wf"))359 #expect(row.publishedVersion == 2 && row.currentVersion == 4)360 await #expect(throws: StoreError.notFound("Version 12 of workflow wf")) { try await store.publish(workflowID: "wf", version: 12) }361 await #expect(throws: StoreError.notFound("Workflow ghost")) { try await store.publish(workflowID: "ghost", version: 1) }362 await #expect(throws: StoreError.self) { try await store.saveWorkflowVersion(workflowID: "wf", name: "x", source: "s", format: "xml") }363 try await store.publish(workflowID: "wf", version: nil)364 #expect(try await store.workflow(id: "wf")?.publishedVersion == nil)365366 try await Task.sleep(for: .milliseconds(5))367 try await store.saveWorkflowVersion(workflowID: "other", name: "Other", source: "{}", format: "json")368 #expect(try await store.workflows().map(\.id) == ["other", "wf"])369370 // Deleting cascades to the history through the foreign key.371 #expect(try await store.deleteWorkflow(id: "wf"))372 #expect(try await !store.deleteWorkflow(id: "wf"))373 #expect(try await store.workflowVersions(workflowID: "wf").isEmpty)374 #expect(try await store.workflowVersions(workflowID: "other").count == 1)375 // A re-created workflow starts again at version 1.376 #expect(try await store.saveWorkflowVersion(workflowID: "wf", name: "Again", source: "new") == 1)377 }378379 @Test func grantsPersistAcrossReopenWithExactDates() async throws {380 let tmp = try TempDirectory(); defer { tmp.remove() }381 let url = tmp.file("orix.sqlite")382 let created = Date()383 let grants = [384 PermissionGrant(id: "g1", capability: .fileRead(path: "/Users/x/Documents"), scope: .persistent, maxTier: .readOnly, createdAt: created),385 PermissionGrant(id: "g2", capability: .network(host: "*.example.com"), scope: .run(runID: "run-1"), maxTier: .consequential,386 createdAt: created.addingTimeInterval(1), expiresAt: created.addingTimeInterval(3600.123456), source: "workflow:wf", note: "n"),387 PermissionGrant(id: "g3", capability: .clipboardWrite, scope: .once(callID: "call-9"), maxTier: .reversibleWrite, createdAt: created.addingTimeInterval(2)),388 PermissionGrant(id: "g4", capability: .skill(id: "pdf-tools"), scope: .workflow(workflowID: "wf"), maxTier: .highRisk, createdAt: created.addingTimeInterval(3)),389 ]390 do {391 let store = try ORIXStore(url: url)392 for g in grants { await store.saveGrant(g) }393 #expect(await store.allGrants() == grants)394 }395 let store = try ORIXStore(url: url)396 #expect(await store.allGrants() == grants)397 let revokedAt = Date()398 await store.revokeGrant(id: "g1", at: revokedAt)399 await store.revokeGrant(id: "g1", at: revokedAt.addingTimeInterval(50))400 #expect(await store.allGrants().first { $0.id == "g1" }?.revokedAt == revokedAt)401 #expect(await store.allGrants().filter { $0.isActive() }.map(\.id) == ["g2", "g3", "g4"])402 await store.revokeAllGrants(at: revokedAt.addingTimeInterval(60))403 #expect(await store.allGrants().allSatisfy { !$0.isActive() })404 #expect(await store.allGrants().first { $0.id == "g1" }?.revokedAt == revokedAt)405 #expect(try await store.deleteInactiveGrants(before: Date().addingTimeInterval(120)) == 4)406 #expect(await store.allGrants().isEmpty)407 }408409 @Test func changeStreamReportsWrites() async throws {410 let store = try ORIXStore.inMemory()411 let stream = store.changes412 let collector = Task { () -> [StoreChange] in413 var seen: [StoreChange] = []414 for await change in stream { seen.append(change); if seen.count == 6 { break } }415 return seen416 }417 try await store.saveRun(makeRun("r"))418 await store.emit(RunEvent(runID: "r", kind: .log))419 try await store.saveWorkflowVersion(workflowID: "wf", name: "n", source: "s")420 try await store.put(Note(title: "t", body: "b", count: 0, when: Date()), collection: "notes", id: "1")421 await store.saveGrant(PermissionGrant(capability: .notify, scope: .persistent, maxTier: .reversibleWrite))422 try await store.deleteRun(id: "r")423 let seen = await collector.value424 #expect(seen == [.run("r"), .events("r"), .workflow("wf"), .collection("notes"), .grants, .run("r")])425 }426427 @Test func darwinNotificationFiresAfterAWriteToAFile() async throws {428 let tmp = try TempDirectory(); defer { tmp.remove() }429 let store = try ORIXStore(url: tmp.file("orix.sqlite"))430 let stream = ORIXStore.darwinChanges()431 let got = await withTaskGroup(of: Bool.self) { group in432 group.addTask { for await _ in stream { return true }; return false }433 group.addTask { try? await Task.sleep(for: .seconds(5)); return false }434 try? await store.saveRun(makeRun("r"))435 let first = await group.next() ?? false436 group.cancelAll()437 return first438 }439 #expect(got, "no Darwin notification within 5 s of a write")440 }441}442