OmniRoute/tests/unit/db-core-init.test.ts

// ENVIRONMENT NOTE (sandbox better-sqlite3 / glibc limitation, not a code defect):
// This test constructs or exercises a real better-sqlite3-backed SQLite database.
// better-sqlite3 is a native addon; production and CI load it normally, but some
// sandboxes/dev boxes ship a system glibc older than the prebuilt binary requires
// ("GLIBC_2.29 not found"), so the native module fails to dlopen and any test that
// reaches better-sqlite3 directly (or asserts stdout that the load-failure warning
// would pollute) fails HERE while passing in CI. This is a known environment
// limitation, not a defect in the code under test: the OmniRoute runtime itself
// cascades to node:sqlite/sql.js when better-sqlite3 is unavailable. See
// tests/unit/_helpers/betterSqlite3Availability.ts for a guard helper.
import test from "node:test";
import assert from "node:assert/strict";
import fs from "node:fs";
import os from "node:os";
import path from "node:path";
import { pathToFileURL } from "node:url";
import Database from "better-sqlite3";

const serial = { concurrency: false };
const originalEnv = {
  DATA_DIR: process.env.DATA_DIR,
  NEXT_PHASE: process.env.NEXT_PHASE,
  HOME: process.env.HOME,
  XDG_CONFIG_HOME: process.env.XDG_CONFIG_HOME,
  APPDATA: process.env.APPDATA,
};

function restoreEnv() {
  for (const [key, value] of Object.entries(originalEnv)) {
    if (value === undefined) {
      delete process.env[key];
    } else {
      process.env[key] = value;
    }
  }
}

function cleanupGlobalDb() {
  try {
    if (globalThis.__omnirouteDb?.open) {
      globalThis.__omnirouteDb.close();
    }
  } catch {}

  delete globalThis.__omnirouteDb;
}

function makeTempDir(prefix) {
  return fs.mkdtempSync(path.join(os.tmpdir(), prefix));
}

function removePath(targetPath) {
  fs.rmSync(targetPath, { recursive: true, force: true, maxRetries: 5, retryDelay: 100 });
}

async function importFresh(modulePath) {
  const url = pathToFileURL(path.resolve(modulePath)).href;
  return import(`${url}?test=${Date.now()}-${Math.random().toString(16).slice(2)}`);
}

async function withEnv(overrides, fn) {
  const snapshot = {};
  for (const key of Object.keys(overrides)) {
    snapshot[key] = process.env[key];
    const value = overrides[key];
    if (value === undefined) {
      delete process.env[key];
    } else {
      process.env[key] = value;
    }
  }

  try {
    return await fn();
  } finally {
    for (const [key, value] of Object.entries(snapshot)) {
      if (value === undefined) {
        delete process.env[key];
      } else {
        process.env[key] = value as string;
      }
    }
  }
}

function createLegacySchemaDb(sqliteFile, { withData = false } = {}) {
  const seedDb = new Database(sqliteFile);
  seedDb.exec(`
    CREATE TABLE schema_migrations (version TEXT);
    CREATE TABLE provider_connections (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL,
      auth_type TEXT,
      name TEXT,
      email TEXT,
      priority INTEGER DEFAULT 0,
      is_active INTEGER DEFAULT 1,
      access_token TEXT,
      refresh_token TEXT,
      expires_at TEXT,
      token_expires_at TEXT,
      scope TEXT,
      project_id TEXT,
      test_status TEXT,
      error_code TEXT,
      last_error TEXT,
      last_error_at TEXT,
      last_error_type TEXT,
      last_error_source TEXT,
      backoff_level INTEGER DEFAULT 0,
      rate_limited_until TEXT,
      health_check_interval INTEGER,
      last_health_check_at TEXT,
      last_tested TEXT,
      api_key TEXT,
      id_token TEXT,
      provider_specific_data TEXT,
      expires_in INTEGER,
      display_name TEXT,
      global_priority INTEGER,
      default_model TEXT,
      token_type TEXT,
      consecutive_use_count INTEGER DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX idx_pc_provider ON provider_connections(provider);
    CREATE INDEX idx_pc_active ON provider_connections(is_active);
    CREATE INDEX idx_pc_priority ON provider_connections(provider, priority);
  `);

  if (withData) {
    const now = new Date().toISOString();
    seedDb
      .prepare(
        "INSERT INTO provider_connections (id, provider, auth_type, name, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
      )
      .run("legacy-openai", "openai", "apikey", "Legacy", 1, now, now);
  }

  seedDb.close();
}

function createLegacyCallLogsDb(sqliteFile) {
  const seedDb = new Database(sqliteFile);
  seedDb.exec(`
    CREATE TABLE provider_connections (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL,
      auth_type TEXT,
      name TEXT,
      email TEXT,
      priority INTEGER DEFAULT 0,
      is_active INTEGER DEFAULT 1,
      access_token TEXT,
      refresh_token TEXT,
      expires_at TEXT,
      token_expires_at TEXT,
      scope TEXT,
      project_id TEXT,
      test_status TEXT,
      error_code TEXT,
      last_error TEXT,
      last_error_at TEXT,
      last_error_type TEXT,
      last_error_source TEXT,
      backoff_level INTEGER DEFAULT 0,
      rate_limited_until TEXT,
      health_check_interval INTEGER,
      last_health_check_at TEXT,
      last_tested TEXT,
      api_key TEXT,
      id_token TEXT,
      provider_specific_data TEXT,
      expires_in INTEGER,
      display_name TEXT,
      global_priority INTEGER,
      default_model TEXT,
      token_type TEXT,
      consecutive_use_count INTEGER DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX idx_pc_provider ON provider_connections(provider);
    CREATE INDEX idx_pc_active ON provider_connections(is_active);
    CREATE INDEX idx_pc_priority ON provider_connections(provider, priority);

    CREATE TABLE call_logs (
      id TEXT PRIMARY KEY,
      timestamp TEXT NOT NULL,
      method TEXT,
      path TEXT,
      status INTEGER,
      model TEXT,
      provider TEXT,
      account TEXT,
      connection_id TEXT,
      duration INTEGER DEFAULT 0,
      tokens_in INTEGER DEFAULT 0,
      tokens_out INTEGER DEFAULT 0,
      source_format TEXT,
      target_format TEXT,
      api_key_id TEXT,
      api_key_name TEXT,
      combo_name TEXT,
      request_body TEXT,
      response_body TEXT,
      error TEXT
    );
    CREATE INDEX idx_cl_timestamp ON call_logs(timestamp);
    CREATE INDEX idx_cl_status ON call_logs(status);
  `);
  seedDb.close();
}

function createRecoverableDb(sqliteFile) {
  const seedDb = new Database(sqliteFile);
  const now = new Date().toISOString();
  seedDb.exec(`
    CREATE TABLE provider_connections (
      id TEXT PRIMARY KEY,
      provider TEXT NOT NULL,
      auth_type TEXT,
      name TEXT,
      is_active INTEGER DEFAULT 1,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE provider_nodes (
      id TEXT PRIMARY KEY,
      type TEXT NOT NULL,
      name TEXT NOT NULL,
      prefix TEXT,
      api_type TEXT,
      base_url TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE key_value (
      namespace TEXT NOT NULL,
      key TEXT NOT NULL,
      value TEXT NOT NULL,
      PRIMARY KEY (namespace, key)
    );

    CREATE TABLE combos (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL UNIQUE,
      data TEXT NOT NULL,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE api_keys (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      key TEXT NOT NULL UNIQUE,
      machine_id TEXT,
      allowed_models TEXT DEFAULT '[]',
      no_log INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL
    );
  `);

  seedDb
    .prepare(
      "INSERT INTO provider_connections (id, provider, auth_type, name, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
    )
    .run("recover-openai", "openai", "apikey", "Recover Me", 1, now, now);
  seedDb
    .prepare(
      "INSERT INTO provider_nodes (id, type, name, prefix, api_type, base_url, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    )
    .run(
      "recover-node",
      "custom",
      "Recover Node",
      "recover",
      "openai",
      "https://example.com",
      now,
      now
    );
  seedDb
    .prepare("INSERT INTO key_value (namespace, key, value) VALUES (?, ?, ?)")
    .run("settings", "globalFallbackModel", JSON.stringify("openai/gpt-4o-mini"));
  seedDb
    .prepare("INSERT INTO key_value (namespace, key, value) VALUES (?, ?, ?)")
    .run("modelAliases", "fast-default", JSON.stringify("openai/gpt-4o-mini"));
  seedDb
    .prepare(
      "INSERT INTO combos (id, name, data, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)"
    )
    .run(
      "recover-combo",
      "Recover Combo",
      JSON.stringify({
        id: "recover-combo",
        name: "Recover Combo",
        models: ["openai/gpt-4o-mini"],
      }),
      1,
      now,
      now
    );
  seedDb
    .prepare(
      "INSERT INTO api_keys (id, name, key, machine_id, allowed_models, no_log, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
    )
    .run(
      "recover-key",
      "Recover Key",
      "sk-recover-key",
      "machine-recover",
      JSON.stringify(["openai/gpt-4o-mini"]),
      1,
      now
    );
  seedDb.close();
}

function createLegacySchemaDbWithName(sqliteFile, name) {
  createLegacySchemaDb(sqliteFile, { withData: true });
  const db = new Database(sqliteFile);
  db.prepare("UPDATE provider_connections SET name = ? WHERE id = ?").run(name, "legacy-openai");
  db.close();
}

function listProbeFailedBackups(sqliteFile) {
  const directory = path.dirname(sqliteFile);
  const prefix = `${path.basename(sqliteFile)}.probe-failed-`;
  return fs
    .readdirSync(directory)
    .filter((name) => name.startsWith(prefix))
    .map((name) => path.join(directory, name))
    .sort();
}

test.beforeEach(() => {
  restoreEnv();
  cleanupGlobalDb();
});

test.afterEach(() => {
  cleanupGlobalDb();
  restoreEnv();
});

test.after(() => {
  cleanupGlobalDb();
  restoreEnv();
});

test("getDbInstance creates sqlite schema, metadata and applies migrations", serial, async () => {
  const dataDir = makeTempDir("omniroute-db-core-");

  try {
    await withEnv({ DATA_DIR: dataDir, NEXT_PHASE: undefined }, async () => {
      const core = await importFresh("src/lib/db/core.ts");
      const db = core.getDbInstance();

      assert.equal(fs.existsSync(core.SQLITE_FILE), true);
      assert.ok(
        db
          .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
          .get("provider_connections")
      );
      assert.deepEqual(db.prepare("SELECT value FROM db_meta WHERE key = 'schema_version'").get(), {
        value: "1",
      });

      const versions = db
        .prepare("SELECT version FROM _omniroute_migrations ORDER BY version")
        .all()
        .map((row) => row.version);

      assert.equal(versions[0], "001");
      assert.ok(versions.includes("017"));
      assert.ok(
        db
          .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
          .get("version_manager")
      );

      core.resetDbInstance();
    });
  } finally {
    removePath(dataDir);
  }
});

test("getDbInstance reuses the singleton and closeDbInstance resets it", serial, async () => {
  const dataDir = makeTempDir("omniroute-db-core-");

  try {
    await withEnv({ DATA_DIR: dataDir, NEXT_PHASE: undefined }, async () => {
      const core = await importFresh("src/lib/db/core.ts");
      const firstDb = core.getDbInstance();
      const secondDb = core.getDbInstance();

      assert.strictEqual(secondDb, firstDb);
      assert.equal(core.closeDbInstance(), true);
      assert.equal(firstDb.open, false);
      assert.equal(core.closeDbInstance(), false);

      const reopenedDb = core.getDbInstance();
      assert.notStrictEqual(reopenedDb, firstDb);

      core.resetDbInstance();
    });
  } finally {
    removePath(dataDir);
  }
});

test("local sqlite configuration enables WAL and sane pragmas", serial, async () => {
  const dataDir = makeTempDir("omniroute-db-core-");

  try {
    await withEnv({ DATA_DIR: dataDir, NEXT_PHASE: undefined }, async () => {
      const core = await importFresh("src/lib/db/core.ts");
      const db = core.getDbInstance();

      assert.equal(db.pragma("journal_mode", { simple: true }), "wal");
      // v3.8.32 intentionally capped busy_timeout at 2s (was 5s) so a contended
      // synchronous write cannot park the Node event loop past the host watchdog's
      // 6s liveness probe — see src/lib/db/core.ts.
      assert.equal(db.pragma("busy_timeout", { simple: true }), 2000);
      assert.equal(db.pragma("synchronous", { simple: true }), 1);
      // cache_size/mmap_size are settings-driven (migration 046 seeds cacheSize=16384 KiB;
      // mmap falls back to 256MiB) — operators with RAM to spare raise them via the
      // database settings, the default stays conservative for small-VPS installs
      // (owner decision 2026-08-05 on #9467; see also #9471).
      assert.equal(db.pragma("cache_size", { simple: true }), -16384);
      assert.equal(db.pragma("mmap_size", { simple: true }), 268435456);
      assert.equal(db.pragma("temp_store", { simple: true }), 2);
      assert.equal(core.closeDbInstance({ checkpointMode: null }), true);
    });
  } finally {
    removePath(dataDir);
  }
});

test("module exports honor DATA_DIR from the environment", serial, async () => {
  const dataDir = makeTempDir("omniroute-db-core-env-");

  try {
    await withEnv({ DATA_DIR: dataDir }, async () => {
      const core = await importFresh("src/lib/db/core.ts");

      assert.equal(core.DATA_DIR, path.resolve(dataDir));
      assert.equal(core.SQLITE_FILE, path.join(path.resolve(dataDir), "storage.sqlite"));
      assert.equal(core.DB_BACKUPS_DIR, path.join(path.resolve(dataDir), "db_backups"));
    });
  } finally {
    removePath(dataDir);
  }
});

test(
  "module falls back to the default home data directory when DATA_DIR is absent",
  serial,
  async () => {
    const fakeHome = makeTempDir("omniroute-home-");

    try {
      await withEnv(
        {
          DATA_DIR: undefined,
          XDG_CONFIG_HOME: undefined,
          HOME: fakeHome,
          USERPROFILE: fakeHome,
          APPDATA: undefined,
          // #10428: this pins the SERVER fallback (home data dir). The test-context guard
          // would otherwise redirect this DATA_DIR-less process to a temp dir — correct for
          // real test runs, but it would turn this assertion into a test of the guard rather
          // than of the home-dir fallback. `fakeHome` already keeps the real DB out of reach.
          OMNIROUTE_ALLOW_DEFAULT_DATA_DIR: "1",
        },
        async () => {
          const core = await importFresh("src/lib/db/core.ts");
          const expectedDir =
            process.platform === "win32"
              ? path.join(fakeHome, "AppData", "Roaming", "omniroute")
              : path.join(fakeHome, ".omniroute");

          assert.equal(core.DATA_DIR, expectedDir);
          assert.equal(core.SQLITE_FILE, path.join(expectedDir, "storage.sqlite"));
        }
      );
    } finally {
      removePath(fakeHome);
    }
  }
);

test("build phase returns the no-op stub without creating sqlite files", serial, async () => {
  // Contract changed by #10060 (via #10952): the build phase no longer opens a
  // real in-memory SQLite with migrations — loading the native better-sqlite3
  // addon aborts the Next.js build worker on exit (node::
  // RemoveEnvironmentCleanupHook). getDbInstance() now returns a no-op stub
  // (pinned by tests/unit/build/10060-build-sqlite-stub.test.ts); queries are
  // harmless no-ops and no file is touched.
  const dataDir = makeTempDir("omniroute-db-build-");

  try {
    await withEnv(
      {
        DATA_DIR: dataDir,
        NEXT_PHASE: "phase-production-build",
      },
      async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.notEqual(db.driver, "better-sqlite3");
        assert.equal(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
            .get("provider_connections"),
          undefined,
          "the build stub must answer queries with no-ops, never a real table scan"
        );
        assert.equal(fs.existsSync(path.join(dataDir, "storage.sqlite")), false);

        core.resetDbInstance();
      }
    );
  } finally {
    removePath(dataDir);
  }
});

test("invalid DATA_DIR (a file where a dir is expected) surfaces as a startup failure", serial, async () => {
  const sandboxDir = makeTempDir("omniroute-db-bad-path-");
  const fileAsDir = path.join(sandboxDir, "not-a-directory");
  fs.writeFileSync(fileAsDir, "blocked");

  try {
    // Since #4767, db/core.ts resolves a writable data dir at module load via
    // resolveWritableDataDir() → mkdirSync(recursive). Pointing DATA_DIR at a
    // regular file is a non-permission misconfiguration (EEXIST/ENOTDIR), which
    // resolveWritableDataDir rethrows by design (only EACCES/EPERM fall back), so
    // the failure now surfaces at import time, not lazily from getDbInstance().
    let caught: unknown;
    await withEnv({ DATA_DIR: fileAsDir }, () => importFresh("src/lib/db/core.ts")).then(
      () => {
        throw new Error("expected importing db/core with an invalid DATA_DIR to reject");
      },
      (err) => {
        caught = err;
      }
    );
    assert.ok(caught instanceof Error, "an invalid DATA_DIR must surface as a thrown Error");
    assert.match(
      String((caught as Error).message),
      /unable to open database file|ENOTDIR|EEXIST|not a directory|file already exists/i
    );
  } finally {
    removePath(sandboxDir);
  }
});

test(
  "legacy empty schema databases are renamed before a fresh sqlite database is created",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-legacy-empty-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    createLegacySchemaDb(sqliteFile);

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.equal(fs.existsSync(`${sqliteFile}.old-schema`), true);
        assert.ok(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
            .get("_omniroute_migrations")
        );
        assert.equal(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
            .get("schema_migrations"),
          undefined
        );

        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);

test(
  "legacy databases with data preserve rows while removing the old migration table",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-legacy-data-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    createLegacySchemaDb(sqliteFile, { withData: true });

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.deepEqual(
          db
            .prepare("SELECT id, provider FROM provider_connections WHERE id = ?")
            .get("legacy-openai"),
          { id: "legacy-openai", provider: "openai" }
        );
        assert.equal(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
            .get("schema_migrations"),
          undefined
        );
        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('provider_connections') WHERE name = ?")
            .get("rate_limit_protection")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('provider_connections') WHERE name = ?")
            .get("last_used_at")
        );

        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);

test(
  "provider connection max_concurrent column is healed even if migration 029 was already recorded",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-missing-max-concurrent-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    const seedDb = new Database(sqliteFile);
    const now = new Date().toISOString();

    seedDb.exec(`
      CREATE TABLE provider_connections (
        id TEXT PRIMARY KEY,
        provider TEXT NOT NULL,
        auth_type TEXT,
        name TEXT,
        priority INTEGER DEFAULT 0,
        is_active INTEGER DEFAULT 1,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );

      CREATE TABLE _omniroute_migrations (
        version TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        applied_at TEXT NOT NULL DEFAULT (datetime('now'))
      );

      INSERT INTO _omniroute_migrations (version, name) VALUES ('001', 'initial_schema');
      INSERT INTO _omniroute_migrations (version, name) VALUES ('029', 'webhooks_templates');
    `);
    seedDb
      .prepare(
        "INSERT INTO provider_connections (id, provider, auth_type, name, priority, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
      )
      .run("missing-max-openai", "openai", "apikey", "Missing max", 0, 1, now, now);
    seedDb.close();

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('provider_connections') WHERE name = ?")
            .get("max_concurrent")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'index' AND name = ?")
            .get("idx_pc_max_concurrent")
        );

        db.prepare(
          "INSERT INTO provider_connections (id, provider, auth_type, name, priority, is_active, max_concurrent, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        ).run("healed-openai", "openai", "apikey", "Healed", 0, 1, 2, now, now);

        assert.deepEqual(
          db
            .prepare(
              "SELECT max_concurrent AS maxConcurrent FROM provider_connections WHERE id = ?"
            )
            .get("healed-openai"),
          { maxConcurrent: 2 }
        );

        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);

test(
  "legacy call_logs schemas are upgraded before combo target indexes are created",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-legacy-call-logs-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    createLegacyCallLogsDb(sqliteFile);

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('call_logs') WHERE name = ?")
            .get("requested_model")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('call_logs') WHERE name = ?")
            .get("request_type")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('call_logs') WHERE name = ?")
            .get("combo_step_id")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM pragma_table_info('call_logs') WHERE name = ?")
            .get("combo_execution_key")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'index' AND name = ?")
            .get("idx_call_logs_requested_model")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'index' AND name = ?")
            .get("idx_call_logs_request_type")
        );
        assert.ok(
          db
            .prepare("SELECT name FROM sqlite_master WHERE type = 'index' AND name = ?")
            .get("idx_cl_combo_target")
        );

        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);

test(
  "probe failures restore preserved critical state instead of booting with an empty database",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-probe-recover-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    createRecoverableDb(sqliteFile);

    const originalPrepare = Database.prototype.prepare;

    try {
      Database.prototype.prepare = function patchedPrepare(sql, ...args) {
        if (String(sql).includes("schema_migrations")) {
          throw new Error("forced probe failure");
        }
        return originalPrepare.call(this, sql, ...args);
      };

      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.deepEqual(
          db
            .prepare("SELECT id, provider, name FROM provider_connections WHERE id = ?")
            .get("recover-openai"),
          { id: "recover-openai", provider: "openai", name: "Recover Me" }
        );
        assert.deepEqual(
          db.prepare("SELECT id, name FROM provider_nodes WHERE id = ?").get("recover-node"),
          { id: "recover-node", name: "Recover Node" }
        );
        assert.deepEqual(
          db.prepare("SELECT id, name FROM combos WHERE id = ?").get("recover-combo"),
          { id: "recover-combo", name: "Recover Combo" }
        );
        assert.deepEqual(
          db.prepare("SELECT id, name, no_log FROM api_keys WHERE id = ?").get("recover-key"),
          { id: "recover-key", name: "Recover Key", no_log: 1 }
        );
        assert.deepEqual(
          db
            .prepare("SELECT value FROM key_value WHERE namespace = 'settings' AND key = ?")
            .get("globalFallbackModel"),
          { value: JSON.stringify("openai/gpt-4o-mini") }
        );
        assert.equal(listProbeFailedBackups(sqliteFile).length >= 1, true);

        core.resetDbInstance();
      });
    } finally {
      Database.prototype.prepare = originalPrepare;
      removePath(dataDir);
    }
  }
);

test(
  "auto-restore picks latest probe-failed timestamp instead of latest mtime",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-probe-latest-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    const olderBackup = `${sqliteFile}.probe-failed-1000`;
    const newerBackup = `${sqliteFile}.probe-failed-2000`;

    createLegacySchemaDbWithName(olderBackup, "Older Backup");
    createLegacySchemaDbWithName(newerBackup, "Newer Backup");
    fs.utimesSync(
      olderBackup,
      new Date("2030-01-01T00:00:00.000Z"),
      new Date("2030-01-01T00:00:00.000Z")
    );
    fs.utimesSync(
      newerBackup,
      new Date("2020-01-01T00:00:00.000Z"),
      new Date("2020-01-01T00:00:00.000Z")
    );

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");
        const db = core.getDbInstance();

        assert.deepEqual(
          db
            .prepare("SELECT id, provider, name FROM provider_connections WHERE id = ?")
            .get("legacy-openai"),
          { id: "legacy-openai", provider: "openai", name: "Newer Backup" }
        );
        assert.equal(fs.existsSync(sqliteFile), true);
        assert.equal(fs.existsSync(newerBackup), false);
        assert.equal(fs.existsSync(olderBackup), true);

        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);

test(
  "probe failures without a safe snapshot abort startup and keep manual recovery explicit",
  serial,
  async () => {
    const dataDir = makeTempDir("omniroute-db-probe-abort-");
    const sqliteFile = path.join(dataDir, "storage.sqlite");
    fs.writeFileSync(sqliteFile, "not-a-valid-sqlite-database");

    try {
      await withEnv({ DATA_DIR: dataDir }, async () => {
        const core = await importFresh("src/lib/db/core.ts");

        assert.throws(() => core.getDbInstance(), /Manual recovery required after probe failure/i);
        assert.equal(fs.existsSync(sqliteFile), false);
        assert.equal(listProbeFailedBackups(sqliteFile).length >= 1, true);

        const restartedCore = await importFresh("src/lib/db/core.ts");
        assert.throws(
          () => restartedCore.getDbInstance(),
          /Manual recovery required after probe failure/i
        );
        assert.equal(fs.existsSync(sqliteFile), false);
        core.resetDbInstance();
      });
    } finally {
      removePath(dataDir);
    }
  }
);