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/ResilienceTests.swift10// Description : Integration tests on real files: migrations from an older11// schema, lease contention between two connections,12// corrupt-file quarantine, backup, export/import.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 ORIXPersistence9697@Suite("ORIXStore migrations and recovery (integration, real SQLite files)")98struct MigrationTests {99 @Test func migratesFromAnOlderUserVersionKeepingData() async throws {100 let tmp = try TempDirectory(); defer { tmp.remove() }101 let url = tmp.file("orix.sqlite")102 let run = makeRun("legacy", title: "From version 1", status: .succeeded)103 do {104 // A database as version 1 of the schema left it: no leases table.105 let old = try Database(path: url.path)106 try old.configure()107 try old.migrate(Schema.migrations, upTo: 1)108 #expect(try old.userVersion() == 1)109 #expect(try old.scalarInt("SELECT COUNT(*) FROM sqlite_master WHERE name = 'leases'") == 0)110 let json = String(decoding: try JSONEncoder().encode(run), as: UTF8.self)111 try old.run("""112 INSERT INTO runs(id, kind, title, trigger_kind, mode, status, queued_at, updated_at, json) VALUES(?,?,?,?,?,?,?,?,?)113 """, [.text(run.id), .text(run.kind), .text(run.title), .text(run.trigger), .text(run.mode.rawValue), .text(run.status.rawValue),114 .date(run.queuedAt), .date(Date()), .text(json)])115 try old.run("INSERT INTO workflows(id, name, current_version, created_at, updated_at) VALUES('wf','Old',1,0,0)")116 try old.run("INSERT INTO workflow_versions(workflow_id, version, source, format, created_at) VALUES('wf',1,'old source','yaml',0)")117 old.close()118 }119 let store = try ORIXStore(url: url)120 #expect(store.recoveryNote == nil)121 #expect(try await store.run(id: "legacy") == run)122 #expect(try await store.workflowSource(workflowID: "wf", version: nil) == "old source")123 // The table added by migration 2 works.124 #expect(try await store.acquireLease(name: "scheduler", owner: "me", ttlSeconds: 10))125 let check = try Database(path: url.path)126 #expect(try check.userVersion() == Schema.latestVersion)127 #expect(Schema.latestVersion >= 2)128 check.close()129 }130131 @Test func freshDatabaseIsAtLatestVersionWithWALAndForeignKeys() throws {132 let tmp = try TempDirectory(); defer { tmp.remove() }133 let db = try Database(path: tmp.file("fresh.sqlite").path)134 try db.configure()135 try db.migrate(Schema.migrations)136 try db.migrate(Schema.migrations)137 #expect(try db.userVersion() == Schema.latestVersion)138 #expect(try db.query("PRAGMA journal_mode") { $0.text(0) } == ["wal"])139 #expect(try db.scalarInt("PRAGMA foreign_keys") == 1)140 #expect(try db.scalarInt("PRAGMA busy_timeout") == 5000)141 #expect(try db.quickCheck() == nil)142 // Foreign keys are enforced: a version without its workflow is refused.143 #expect(throws: SQLiteError.self) {144 try db.run("INSERT INTO workflow_versions(workflow_id, version, source, format, created_at) VALUES('ghost',1,'s','yaml',0)")145 }146 // A failing transaction leaves nothing behind.147 #expect(throws: SQLiteError.self) {148 try db.transaction {149 try db.run("INSERT INTO leases(name, owner, acquired_at, expires_at) VALUES('a','o',0,0)")150 try db.run("INSERT INTO no_such_table VALUES(1)")151 }152 }153 #expect(try db.scalarInt("SELECT COUNT(*) FROM leases") == 0)154 }155156 @Test func aNewerSchemaIsRefusedAndLeftUntouched() throws {157 let tmp = try TempDirectory(); defer { tmp.remove() }158 let url = tmp.file("future.sqlite")159 do {160 let db = try Database(path: url.path)161 try db.configure()162 try db.migrate(Schema.migrations)163 try db.exec("PRAGMA user_version = 999")164 db.close()165 }166 #expect(throws: StoreError.newerSchema(found: 999, supported: Schema.latestVersion)) { _ = try ORIXStore(url: url) }167 let names = try FileManager.default.contentsOfDirectory(atPath: tmp.url.path)168 #expect(!names.contains { $0.contains(".corrupt-") })169 }170171 @Test func corruptFileIsQuarantinedAndAWorkingStoreIsCreated() async throws {172 let tmp = try TempDirectory(); defer { tmp.remove() }173 let url = tmp.file("orix.sqlite")174 let garbage = Data((0..<8192).map { UInt8(truncatingIfNeeded: $0 &* 31 &+ 7) })175 try garbage.write(to: url)176177 let store = try ORIXStore(url: url)178 let note = try #require(store.recoveryNote)179 #expect(note.contains("damaged") && note.contains(".corrupt-"))180 let aside = try #require(store.quarantinedFile)181 #expect(aside.lastPathComponent.hasPrefix("orix.sqlite.corrupt-"))182 // The damaged file is kept byte for byte.183 #expect(try Data(contentsOf: aside) == garbage)184 // The new store works.185 try await store.saveRun(makeRun("after", status: .running))186 await store.emit(RunEvent(runID: "after", kind: .log, text: "alive"))187 #expect(try await store.run(id: "after")?.id == "after")188 #expect(try await store.eventCount(runID: "after") == 1)189 // Opening again is a normal open: no second quarantine.190 let again = try ORIXStore(url: url)191 #expect(again.recoveryNote == nil)192 #expect(try await again.run(id: "after") != nil)193 let quarantined = try FileManager.default.contentsOfDirectory(atPath: tmp.url.path).filter { $0.contains(".corrupt-") }194 #expect(quarantined.count == 1)195 }196197 @Test func damagedPagesInsideARealDatabaseAreQuarantined() async throws {198 let tmp = try TempDirectory(); defer { tmp.remove() }199 let url = tmp.file("orix.sqlite")200 do {201 let store = try ORIXStore(url: url)202 for i in 0..<400 { try await store.saveRun(makeRun("run-\(i)", title: String(repeating: "x", count: 500), status: .succeeded)) }203 try await store.backup(to: tmp.file("compact.sqlite"))204 }205 // Use the compacted copy (no WAL) and overwrite pages in the middle of it.206 let damaged = tmp.file("damaged.sqlite")207 try FileManager.default.copyItem(at: tmp.file("compact.sqlite"), to: damaged)208 let handle = try FileHandle(forWritingTo: damaged)209 try handle.seek(toOffset: 4096 * 3)210 try handle.write(contentsOf: Data(repeating: 0xEE, count: 4096 * 20))211 try handle.close()212213 let store = try ORIXStore(url: damaged)214 #expect(store.recoveryNote != nil)215 #expect(store.quarantinedFile != nil)216 #expect(try await store.runs().isEmpty)217 try await store.saveRun(makeRun("new"))218 #expect(try await store.runs().count == 1)219 }220}221222@Suite("ORIXStore leases across two connections (integration, same SQLite file)")223struct LeaseTests {224 @Test func onlyOneContenderWinsAndTheLeaseCanBeRenewedReleasedAndStolen() async throws {225 let tmp = try TempDirectory(); defer { tmp.remove() }226 let url = tmp.file("orix.sqlite")227 let app = try ORIXStore(url: url)228 let agent = try ORIXStore(url: url)229230 // Many rounds of simultaneous attempts from both connections: exactly one winner each time.231 for round in 0..<25 {232 let name = "scheduler-\(round)"233 let winners = await withTaskGroup(of: String?.self) { group -> [String] in234 for i in 0..<8 {235 let owner = "owner-\(i)"236 let store = i % 2 == 0 ? app : agent237 group.addTask { (try? await store.acquireLease(name: name, owner: owner, ttlSeconds: 30)) == true ? owner : nil }238 }239 var out: [String] = []240 for await w in group { if let w { out.append(w) } }241 return out242 }243 #expect(winners.count == 1, "round \(round): \(winners)")244 #expect(try await agent.leaseOwner(name: name) == winners.first)245 }246247 #expect(try await app.acquireLease(name: "scheduler", owner: "app", ttlSeconds: 0.4))248 #expect(try await !agent.acquireLease(name: "scheduler", owner: "agent", ttlSeconds: 0.4))249 // Renewal by the owner, seen by the other connection.250 #expect(try await app.acquireLease(name: "scheduler", owner: "app", ttlSeconds: 0.4))251 #expect(try await agent.leaseOwner(name: "scheduler") == "app")252 // Only the owner can release.253 #expect(try await !agent.releaseLease(name: "scheduler", owner: "agent"))254 #expect(try await !agent.acquireLease(name: "scheduler", owner: "agent", ttlSeconds: 0.4))255 // After expiry the other process takes over, and the former owner is refused.256 try await Task.sleep(for: .milliseconds(500))257 #expect(try await agent.leaseOwner(name: "scheduler") == nil)258 #expect(try await agent.acquireLease(name: "scheduler", owner: "agent", ttlSeconds: 30))259 #expect(try await !app.acquireLease(name: "scheduler", owner: "app", ttlSeconds: 30))260 // Release hands it over immediately.261 #expect(try await agent.releaseLease(name: "scheduler", owner: "agent"))262 #expect(try await app.acquireLease(name: "scheduler", owner: "app", ttlSeconds: 30))263 await #expect(throws: StoreError.self) { _ = try await app.acquireLease(name: "", owner: "app", ttlSeconds: 1) }264 }265266 @Test func twoConnectionsSeeEachOthersWrites() async throws {267 let tmp = try TempDirectory(); defer { tmp.remove() }268 let url = tmp.file("orix.sqlite")269 let app = try ORIXStore(url: url)270 let agent = try ORIXStore(url: url)271 try await agent.saveRun(makeRun("bg", status: .running))272 #expect(try await app.run(id: "bg")?.status == .running)273 await withTaskGroup(of: Void.self) { group in274 for i in 0..<60 {275 group.addTask { try? await (i % 2 == 0 ? app : agent).saveRun(makeRun("run-\(i)", status: .succeeded)) }276 group.addTask { try? await (i % 2 == 0 ? agent : app).put(Note(title: "n\(i)", body: "", count: i, when: Date()), collection: "notes", id: "\(i)") }277 }278 }279 #expect(try await app.runs(limit: 500).count == 61)280 #expect(try await agent.count(collection: "notes") == 60)281 }282}283284@Suite("ORIXStore backup and export/import (integration, real files)")285struct BundleTests {286 @Test func backupIsAConsistentOpenableCopy() async throws {287 let tmp = try TempDirectory(); defer { tmp.remove() }288 let store = try ORIXStore(url: tmp.file("orix.sqlite"))289 try await store.saveRun(makeRun("r", status: .succeeded))290 await store.emit(RunEvent(runID: "r", kind: .log, text: "x"))291 try await store.saveWorkflowVersion(workflowID: "wf", name: "W", source: "s")292 #expect(try await store.databaseSizeBytes() > 0)293 let target = tmp.file("backups/copy.sqlite")294 try await store.backup(to: target)295 await #expect(throws: StoreError.fileExists(target.path)) { try await store.backup(to: target) }296 let copy = try ORIXStore(url: target)297 #expect(copy.recoveryNote == nil)298 #expect(try await copy.run(id: "r") != nil)299 #expect(try await copy.eventCount(runID: "r") == 1)300 #expect(try await copy.workflowSource(workflowID: "wf") == "s")301 #expect(try await ORIXStore.inMemory().databaseSizeBytes() > 0)302 }303304 @Test func exportImportRoundTripExcludesGrantsRunsAndSecrets() async throws {305 let tmp = try TempDirectory(); defer { tmp.remove() }306 let source = try ORIXStore(url: tmp.file("source.sqlite"))307 let literal = "orix-bundle-literal-\(UUID().uuidString)"308 Redactor.shared.register(secret: literal)309 defer { Redactor.shared.unregister(secret: literal) }310311 try await source.saveWorkflowVersion(workflowID: "wf", name: "Report", source: "steps: [one]", note: "v1")312 try await source.saveWorkflowVersion(workflowID: "wf", name: "Report", source: "steps: [one, two]\nauth: \"{{ secrets.github }}\"\ntoken: ${GITHUB_TOKEN_REFERENCE}", note: "v2")313 try await source.saveWorkflowVersion(workflowID: "wf", name: "Report", source: "steps: [one, two, three]", note: "v3")314 try await source.publish(workflowID: "wf", version: 2)315 try await source.saveWorkflowVersion(workflowID: "leaky", name: "Leaky", source: "run: curl -H 'x: \(literal)' https://example.com")316317 let profile = AgentProfile(id: "agent-1", name: "Worker", instructions: "Be careful.", model: ModelRef(provider: .anthropic, model: "m"),318 budget: Budget(maxTotalTokens: 123_456))319 let when = Date(timeIntervalSinceReferenceDate: 812_345_678.25)320 try await source.put(profile, collection: "agents", id: profile.id)321 try await source.put(Note(title: "Plain", body: "nothing special", count: 7, when: when), collection: "notes", id: "plain")322 try await source.put(Note(title: "Leak", body: "key sk-ant-REDACTME0123456789abcdefghij and \(literal)", count: 1, when: when), collection: "notes", id: "leak")323 try await source.put(Note(title: "s", body: "vault content", count: 0, when: when), collection: "secrets", id: "1")324 try await source.put(Note(title: "c", body: "cache", count: 0, when: when), collection: "cache", id: "1")325 await source.saveGrant(PermissionGrant(id: "grant-1", capability: .fileWrite(path: "/Users/x/GRANTED-PATH"), scope: .persistent, maxTier: .consequential))326 try await source.saveRun(makeRun("run-in-source", title: "RUN-TITLE-MARKER", status: .succeeded))327 await source.emit(RunEvent(runID: "run-in-source", kind: .log, text: "EVENT-TEXT-MARKER"))328329 let file = tmp.file("export/bundle.json")330 let exported = try await source.exportBundle(to: file, excludingCollections: ["cache"])331 #expect(exported.workflows == 2 && exported.workflowVersions == 4 && exported.documents == 3)332 #expect(exported.excludedCollections == ["cache", "secrets"])333 #expect(exported.redactedValues == 2)334335 let text = try String(contentsOf: file, encoding: .utf8)336 #expect(!text.contains("GRANTED-PATH") && !text.contains("grant-1"))337 #expect(!text.contains("RUN-TITLE-MARKER") && !text.contains("EVENT-TEXT-MARKER"))338 #expect(!text.contains("vault content") && !text.contains("\"cache\""))339 #expect(!text.contains(literal) && !text.contains("REDACTME"))340 #expect(text.contains("{{ secrets.github }}") && text.contains("${GITHUB_TOKEN_REFERENCE}"))341 #expect(try JSONValue.parse(text)["format"]?.stringValue == StoreBundle.formatIdentifier)342343 // Import into an empty store.344 let target = try ORIXStore(url: tmp.file("target.sqlite"))345 let imported = try await target.importBundle(from: file)346 #expect(imported.workflows == 2 && imported.workflowVersions == 4 && imported.documents == 3)347 let row = try #require(try await target.workflow(id: "wf"))348 #expect(row.name == "Report" && row.currentVersion == 3 && row.publishedVersion == 2)349 let sourceHistory = try await source.workflowVersions(workflowID: "wf")350 let history = try await target.workflowVersions(workflowID: "wf")351 #expect(history.map(\.version) == [3, 2, 1])352 #expect(history.map(\.source) == sourceHistory.map(\.source))353 #expect(history.map(\.note) == ["v3", "v2", "v1"])354 #expect(try await target.workflowSource(workflowID: "leaky")?.contains(Redactor.placeholder) == true)355 #expect(try await target.get(AgentProfile.self, collection: "agents", id: "agent-1") == profile)356 #expect(try await target.get(Note.self, collection: "notes", id: "plain") == Note(title: "Plain", body: "nothing special", count: 7, when: when))357 #expect(try await target.get(Note.self, collection: "notes", id: "leak")?.body == "key \(Redactor.placeholder) and \(Redactor.placeholder)")358 #expect(try await target.count(collection: "secrets") == 0)359 #expect(try await target.count(collection: "cache") == 0)360 #expect(await target.allGrants().isEmpty)361 #expect(try await target.runs().isEmpty)362363 // Importing the same bundle again changes nothing.364 let again = try await target.importBundle(from: file)365 #expect(again.workflowVersions == 0)366 #expect(try await target.workflowVersions(workflowID: "wf").count == 3)367 #expect(try await target.count(collection: "notes") == 2)368 }369370 @Test func importMergesWithoutDestroyingLocalHistory() async throws {371 let tmp = try TempDirectory(); defer { tmp.remove() }372 let source = try ORIXStore.inMemory()373 try await source.saveWorkflowVersion(workflowID: "wf", name: "Remote name", source: "remote v1")374 try await source.saveWorkflowVersion(workflowID: "wf", name: "Remote name", source: "remote v2")375 try await source.put(Note(title: "remote", body: "", count: 1, when: Date()), collection: "notes", id: "shared")376 let file = tmp.file("bundle.json")377 try await source.exportBundle(to: file)378379 let local = try ORIXStore.inMemory()380 try await local.saveWorkflowVersion(workflowID: "wf", name: "Local name", source: "local v1")381 try await local.publish(workflowID: "wf", version: 1)382 try await local.put(Note(title: "local", body: "", count: 2, when: Date()), collection: "notes", id: "shared")383384 let kept = try await local.importBundle(from: file, overwriteDocuments: false)385 #expect(kept.documents == 0 && kept.workflowVersions == 1)386 #expect(try await local.get(Note.self, collection: "notes", id: "shared")?.title == "local")387 #expect(try await local.workflowVersions(workflowID: "wf").map(\.source) == ["remote v2", "local v1"])388 #expect(try await local.workflow(id: "wf")?.publishedVersion == 1)389 #expect(try await local.workflow(id: "wf")?.name == "Remote name")390 _ = try await local.importBundle(from: file)391 #expect(try await local.get(Note.self, collection: "notes", id: "shared")?.title == "remote")392 }393394 @Test func invalidBundlesAreRefusedWithoutSideEffects() async throws {395 let tmp = try TempDirectory(); defer { tmp.remove() }396 let store = try ORIXStore.inMemory()397 try tmp.write("garbage.json", "this is not json")398 try tmp.write("other.json", #"{"format":"something.else","version":1,"exportedAt":0,"workflows":[],"documents":[]}"#)399 try tmp.write("future.json", #"{"format":"ai.spboucher.orix.automation.bundle","version":99,"exportedAt":0,"workflows":[],"documents":[]}"#)400 try tmp.write("sneaky.json", """401 {"format":"ai.spboucher.orix.automation.bundle","version":1,"exportedAt":0,"workflows":[],402 "documents":[{"collection":"grants","id":"g","createdAt":0,"updatedAt":0,"value":{"x":1}},403 {"collection":"notes","id":"ok","createdAt":0,"updatedAt":0,"value":{"x":1}}]}404 """)405 for name in ["garbage.json", "other.json", "future.json", "missing.json"] {406 await #expect(throws: StoreError.self) { try await store.importBundle(from: tmp.file(name)) }407 }408 #expect(try await store.collections().isEmpty)409 let sneaky = try await store.importBundle(from: tmp.file("sneaky.json"))410 #expect(sneaky.documents == 1 && sneaky.excludedCollections == ["grants"])411 #expect(try await store.collections() == ["notes"])412 #expect(await store.allGrants().isEmpty)413 }414}415