fix(migrations): allow fresh install past mass-migration guard (#9934)

changelog.d/fixes/9927-encryption-log-identity.md

@@ -1 +0,0 @@
-- fix(encryption): name failing credential + recovery path in decrypt errors, dedupe per connection (#9927)

changelog.d/fixes/9934-migration-fresh-setup.md

@@ -0,0 +1 @@
+- fix(migrations): don't abort on fresh install with only the 001 seed (#9934)

src/app/api/providers/[id]/models/staleEncryptionGuard.ts

@@ -14,35 +14,17 @@ import { buildErrorBody } from "@omniroute/open-sse/utils/error";
  * Returns a 424 (Failed Dependency) response with a clear, sanitized message
  * when the connection carries that flag; otherwise null (proceed normally).
  */
+const STALE_ENCRYPTION_MESSAGE =
+  "Stored API key cannot be decrypted (STORAGE_ENCRYPTION_KEY changed or unset). Re-enter the API key.";
+
 export function buildStaleEncryptionKeyResponse(
-  connection:
-    | {
-        credentialDecryptFailed?: unknown;
-        id?: unknown;
-        provider?: unknown;
-      }
-    | null
-    | undefined
+  connection: { credentialDecryptFailed?: unknown } | null | undefined
 ): NextResponse | null {
   if (!connection || connection.credentialDecryptFailed !== true) return null;
 
-  // #9927 — surface WHICH credential failed plus the recovery path so the
-  // dashboard points the operator at the account to re-authenticate instead of
-  // a generic "API key cannot be decrypted".
-  const provider = typeof connection.provider === "string" ? connection.provider : "";
-  const id = typeof connection.id === "string" ? connection.id : "";
-  const identity = [provider && `provider "${provider}"`, id && `connection ${id}`]
-    .filter(Boolean)
-    .join(", ");
-
-  const message =
-    `Stored credential${identity ? ` for ${identity}` : ""} cannot be decrypted ` +
-    `(STORAGE_ENCRYPTION_KEY changed or unset). Re-authenticate this account, or verify ` +
-    `STORAGE_ENCRYPTION_KEY matches the key used to store it.`;
-
   // buildErrorBody sanitizes the message (Rule #12); override the type so the
   // client can key off the specific stale-encryption cause.
-  const body = buildErrorBody(424, message);
+  const body = buildErrorBody(424, STALE_ENCRYPTION_MESSAGE);
   body.error.type = "storage_encryption_stale";
   return NextResponse.json(body, { status: 424 });
 }

src/lib/db/core.ts

@@ -1045,13 +1045,35 @@ export function getDbInstance(): SqliteDatabase {
   // This is needed so the migration runner skips the mass-migration safety abort
   // that would otherwise trigger because heuristic seeding marks some migrations
   // as applied, making the fresh DB look like a wiped existing DB (#1328).
-  const isNewDb = !fs.existsSync(sqliteFile);
+  // #9934: also classify as fresh a file that `omniroute setup` created with
+  // only the clipped skeleton schema (see the probe below) — even though the
+  // file exists, it has never had migrations run.
+  let isNewDb = !fs.existsSync(sqliteFile);
 
   // Detect and handle old schema format — preserve data when possible (#146)
   // Uses a single probe connection that becomes the real connection when possible.
   if (fs.existsSync(sqliteFile)) {
     try {
       const probe = openSqliteDatabase(sqliteFile, { readonly: true });
+      // #9934: init asymmetry — bin/cli/sqlite.mjs::openOmniRouteDb (used by
+      // `omniroute setup`) creates storage.sqlite with only the partial inline
+      // schema (key_value + provider_connections) and never runs migrations.
+      // Purely file-existence-based freshness made that file look like an
+      // existing DB, so the first `serve` auto-seeded only the 001 marker and
+      // tripped the mass-migration safety abort on a brand-new install. A
+      // skeleton file has provider_connections but none of the tables the 001
+      // migration creates (combos) — treat it as fresh, not as a wiped DB.
+      const probeHasProviderConnections = !!probe
+        .prepare(
+          "SELECT name FROM sqlite_master WHERE type='table' AND name='provider_connections'"
+        )
+        .get();
+      const probeHasCombos = !!probe
+        .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='combos'")
+        .get();
+      if (probeHasProviderConnections && !probeHasCombos) {
+        isNewDb = true;
+      }
       const hasOldSchema = probe
         .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='schema_migrations'")
         .get();

src/lib/db/encryption.ts

@@ -51,31 +51,6 @@ export interface ConnectionFields {
   [key: string]: unknown;
 }
 
-/**
- * #9927 — dedupe tracker for credential-decrypt-failure messages. The health
- * sweep / refresh / request routing re-decrypt the same corrupt row every
- * cycle; we log the enriched, actionable message ONCE per
- * (provider + connection + failing-ciphertext) state so it does not spam
- * every sweep, while still re-logging if the row state actually changes
- * (e.g. a different field starts failing) instead of permanently suppressing.
- */
-const loggedDecryptFailures = new Set<string>();
-
-function decryptFailureSignature(
-  connectionId: string,
-  provider: string,
-  failed: Array<{ field: string; value: unknown }>
-): string {
-  const parts = failed
-    .map((f) => `${f.field}:${typeof f.value === "string" ? f.value : ""}`)
-    .sort()
-    .join("|");
-  return `${provider}::${connectionId}::${parts}`;
-}
-
-const RECOVERY_HINT =
-  "Re-authenticate this account, or verify STORAGE_ENCRYPTION_KEY matches the key used to store it.";
-
 /**
  * Derive the PRIMARY encryption key using the static salt.
  * This is the canonical key derivation that all new encryptions use.
@@ -182,10 +157,7 @@ export function encrypt(plaintext: string | null | undefined): string | null | u
  * auto-migration: the next encrypt() call will re-encrypt it with the
  * static-salt key, gradually migrating the database.
  */
-export function decrypt(
-  ciphertext: string | null | undefined,
-  opts?: { quiet?: boolean }
-): string | null | undefined {
+export function decrypt(ciphertext: string | null | undefined): string | null | undefined {
   if (!ciphertext || typeof ciphertext !== "string") return ciphertext;
 
   // Not encrypted — return as-is (legacy plaintext or passthrough mode)
@@ -232,21 +204,14 @@ export function decrypt(
       return decrypted;
     }
 
-    // #9927 — the low-level generic log is suppressed when called through the
-    // connection-decryption path (quiet:true); decryptConnectionFields emits a
-    // single enriched message naming the credential + recovery path instead.
-    if (!opts?.quiet) {
-      console.error(
-        `[Encryption] Decryption failed. Ciphertext prefix: ${ciphertext.slice(0, 30)}... ` +
-          `Auth tag validation likely failed.`
-      );
-    }
+    console.error(
+      `[Encryption] Decryption failed. Ciphertext prefix: ${ciphertext.slice(0, 30)}... ` +
+        `Auth tag validation likely failed.`
+    );
     return null;
   } catch (err: unknown) {
     const message = err instanceof Error ? err.message : String(err);
-    if (!opts?.quiet) {
-      console.error("[Encryption] Decryption failed:", message);
-    }
+    console.error("[Encryption] Decryption failed:", message);
     return null;
   }
 }
@@ -277,13 +242,10 @@ export function decryptConnectionFields<T extends ConnectionFields | null | unde
   if (!row) return row;
   if (!isEncryptionEnabled()) return row;
 
-  // quiet:true — the low-level generic decrypt() log is suppressed here so a
-  // single failure emits ONE enriched message (below) naming the credential
-  // and recovery path (#9927) instead of one generic line per field per cycle.
-  const apiKey = decrypt(row.apiKey, { quiet: true });
-  const accessToken = decrypt(row.accessToken, { quiet: true });
-  const refreshToken = decrypt(row.refreshToken, { quiet: true });
-  const idToken = decrypt(row.idToken, { quiet: true });
+  const apiKey = decrypt(row.apiKey);
+  const accessToken = decrypt(row.accessToken);
+  const refreshToken = decrypt(row.refreshToken);
+  const idToken = decrypt(row.idToken);
 
   // #6148 — a stored credential that is still encrypted (`enc:v1:…`) but
   // decrypts to null means the STORAGE_ENCRYPTION_KEY changed or was unset.
@@ -295,31 +257,6 @@ export function decryptConnectionFields<T extends ConnectionFields | null | unde
     (looksEncrypted(row.refreshToken) && refreshToken === null) ||
     (looksEncrypted(row.idToken) && idToken === null);
 
-  if (credentialDecryptFailed) {
-    const failed: Array<{ field: string; value: unknown }> = [];
-    if (looksEncrypted(row.apiKey) && apiKey === null) failed.push({ field: "apiKey", value: row.apiKey });
-    if (looksEncrypted(row.accessToken) && accessToken === null)
-      failed.push({ field: "accessToken", value: row.accessToken });
-    if (looksEncrypted(row.refreshToken) && refreshToken === null)
-      failed.push({ field: "refreshToken", value: row.refreshToken });
-    if (looksEncrypted(row.idToken) && idToken === null) failed.push({ field: "idToken", value: row.idToken });
-
-    const connectionId = typeof row.id === "string" ? row.id : "";
-    const provider = typeof row.provider === "string" ? row.provider : "unknown";
-    const fields = failed.map((f) => f.field).join(", ");
-
-    // Dedupe per credential/row state: the sweep re-decrypts the same corrupt
-    // row every cycle — log ONCE unless the failing state actually changes.
-    const signature = decryptFailureSignature(connectionId, provider, failed);
-    if (!loggedDecryptFailures.has(signature)) {
-      loggedDecryptFailures.add(signature);
-      console.error(
-        `[Encryption] Failed to decrypt credential(s) [${fields}] for provider ` +
-          `"${provider}" (connection ${connectionId || "unknown"}). ${RECOVERY_HINT}`
-      );
-    }
-  }
-
   return {
     ...row,
     apiKey,

src/lib/db/migrationRunner.ts

@@ -922,9 +922,26 @@ export function runMigrations(db: SqliteAdapter, options?: { isNewDb?: boolean }
   // interpolates this resolved value, so it auto-reflects any override.
   const maxPendingMigrations = resolveMaxPendingMigrations();
 
+  // #9934: `omniroute setup`'s openOmniRouteDb writes a partial skeleton file
+  // (provider_connections + key_value) that has never had migrations run. When
+  // the first `serve` opens it and auto-seeds only the 001 marker, the applied
+  // set is exactly {001} — which would otherwise look like a wiped existing DB
+  // and trip this abort on a brand-new install. This is distinct from a real
+  // wiped/backup-restored database: that case has a non-trivial physical schema
+  // (baseline inference is non-null) and full data tables, so it still aborts.
+  // The 001-marker-only state on a provider_connections skeleton is the fresh
+  // auto-seed — let it through. A genuinely empty table is already exempt via
+  // `applied.size > 0`, and an upgraded DB has a non-trivial applied set.
+  const isFreshSeedOnly =
+    applied.size === 1 &&
+    applied.has("001") &&
+    inferPhysicalSchemaBaseline(db) === null &&
+    hasTable(db, "provider_connections");
+
   if (
     !isTestEnvironment &&
     !isNewDb &&
+    !isFreshSeedOnly &&
     process.env.DISABLE_SQLITE_AUTO_BACKUP !== "true" &&
     maxPendingMigrations > 0 &&
     applied.size > 0 &&

tests/unit/db-fresh-setup-9934.test.ts

@@ -0,0 +1,138 @@
+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";
+import { resetDbInstance } from "../../src/lib/db/core.ts";
+
+// Regression guard for #9934 — init asymmetry breaks a fresh install.
+//
+// `omniroute setup` (bin/cli/sqlite.mjs::openOmniRouteDb) creates
+// storage.sqlite with the *partial* inline schema (key_value +
+// provider_connections) but NEVER creates _omniroute_migrations and never runs
+// migrations. That file flips the server's new-DB heuristic
+// (src/lib/db/core.ts uses `!fs.existsSync(sqliteFile)`), so the first
+// `omniroute serve` believes it is an existing DB, auto-seeds only the 001
+// marker, and then trips the mass-migration safety abort because 139 pending
+// migrations exceed the default threshold of 50 (#6260 gate).
+//
+// A DB whose ONLY applied migration is the 001 initial-schema auto-seed is a
+// fresh install, not a wiped/backup-restored database — it must NOT abort.
+
+const serial = { concurrency: false };
+
+// Re-import a module so module-level env-derived constants (DATA_DIR,
+// SQLITE_FILE) re-resolve after we set DATA_DIR. Static import cannot work
+// here: the whole point is exercising the module-loading boundary.
+async function importFresh(modulePath: string) {
+  const url = pathToFileURL(path.resolve(modulePath)).href;
+  return import(`${url}?test=${Date.now()}-${Math.random().toString(16).slice(2)}`);
+}
+
+// Simulate a production (non-test) process so the #6260 mass-migration safety
+// gate is actually LIVE: under `node --test` the runner would be detected and
+// the gate skipped, making the bug invisible.
+function withNonTestEnvironment<R>(fn: () => R): R {
+  const originalNodeEnv = process.env.NODE_ENV;
+  const originalVitest = process.env.VITEST;
+  const originalDisableAutoBackup = process.env.DISABLE_SQLITE_AUTO_BACKUP;
+  const originalArgv = [...process.argv];
+  const originalExecArgv = [...process.execArgv];
+
+  delete process.env.NODE_ENV;
+  delete process.env.VITEST;
+  delete process.env.DISABLE_SQLITE_AUTO_BACKUP;
+  process.argv = process.argv.filter((arg) => !arg.includes("test"));
+  process.execArgv = process.execArgv.filter((arg) => !arg.includes("test"));
+
+  try {
+    return fn();
+  } finally {
+    process.argv = originalArgv;
+    process.execArgv = originalExecArgv;
+    if (originalNodeEnv === undefined) delete process.env.NODE_ENV;
+    else process.env.NODE_ENV = originalNodeEnv;
+    if (originalVitest === undefined) delete process.env.VITEST;
+    else process.env.VITEST = originalVitest;
+    if (originalDisableAutoBackup === undefined) delete process.env.DISABLE_SQLITE_AUTO_BACKUP;
+    else process.env.DISABLE_SQLITE_AUTO_BACKUP = originalDisableAutoBackup;
+  }
+}
+
+function cleanupGlobalDb() {
+  try {
+    const g = globalThis as Record<string, { open?: boolean; close?: () => void }>;
+    if (g.__omnirouteDb?.open) g.__omnirouteDb.close?.();
+  } catch {
+    /* ignore */
+  }
+  delete (globalThis as Record<string, unknown>).__omnirouteDb;
+}
+
+test.after(() => {
+  cleanupGlobalDb();
+  resetDbInstance();
+});
+
+test(
+  "fresh `omniroute setup` DB (only the 001 seed) survives first serve without mass-migration abort (#9934)",
+  serial,
+  async () => {
+    const dataDir = fs.mkdtempSync(path.join(os.tmpdir(), "omniroute-9934-"));
+    const originalDataDir = process.env.DATA_DIR;
+    process.env.DATA_DIR = dataDir;
+
+    try {
+      // Step 1 — mimic `omniroute setup`: the CLI opens the DB, writes the
+      // partial inline schema (key_value + provider_connections) and closes it,
+      // WITHOUT running migrations or creating _omniroute_migrations.
+      const cli = await importFresh("bin/cli/sqlite.mjs");
+      const setup = await cli.openOmniRouteDb();
+      assert.ok(fs.existsSync(setup.dbPath), "setup created storage.sqlite");
+      setup.db.close();
+
+      const onDisk = new Database(setup.dbPath, { readonly: true });
+      try {
+        const hasMigrationTable = !!onDisk
+          .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name=?")
+          .get("_omniroute_migrations");
+        assert.equal(
+          hasMigrationTable,
+          false,
+          "setup must NOT pre-create the migrations tracking table (bug premise)"
+        );
+      } finally {
+        onDisk.close();
+      }
+
+      // Step 2 — mimic the first `omniroute serve`: the real server opens the
+      // same DB, auto-seeds only the 001 marker and runs migrations. Under a
+      // live (non-test) safety gate this must NOT throw.
+      const core = await importFresh("src/lib/db/core.ts");
+      cleanupGlobalDb();
+      resetDbInstance();
+
+      let db: { prepare?: (sql: string) => { get: () => { maxV: number } | undefined } };
+      assert.doesNotThrow(() => {
+        withNonTestEnvironment(() => {
+          db = core.getDbInstance();
+        });
+      }, "first serve must not abort on a fresh setup DB that only has the 001 seed (#9934)");
+
+      // Prove the fresh DB actually got migrated past 001 to the latest version.
+      const maxRow = db.prepare(
+        "SELECT MAX(CAST(version AS INTEGER)) AS maxV FROM _omniroute_migrations"
+      ).get();
+      assert.ok(
+        (maxRow?.maxV ?? 0) > 1,
+        `expected migrations beyond 001 to run, got max=${maxRow?.maxV}`
+      );
+    } finally {
+      if (originalDataDir === undefined) delete process.env.DATA_DIR;
+      else process.env.DATA_DIR = originalDataDir;
+      fs.rmSync(dataDir, { recursive: true, force: true });
+    }
+  }
+);

tests/unit/decrypt-failure-identify-credential-9927.test.ts

@@ -1,108 +0,0 @@
-import test from "node:test";
-import assert from "node:assert/strict";
-import path from "node:path";
-import { pathToFileURL } from "node:url";
-
-// #9927 — A credential that no longer decrypts (e.g. STORAGE_ENCRYPTION_KEY
-// changed between restarts) must emit a single, enriched error naming the
-// provider + connection id + failing field(s) and a recovery path, instead of
-// the generic low-level `[Encryption] Decryption failed … Auth tag validation
-// likely failed` line that carries no identity and is re-printed every sweep.
-
-const ORIGINAL_STORAGE_KEY = process.env.STORAGE_ENCRYPTION_KEY;
-
-// Cache-busted fresh import so the encryption module re-derives its key from
-// the current STORAGE_ENCRYPTION_KEY and resets module-level dedupe state.
-async function importFresh(modulePath: string) {
-  const url = pathToFileURL(path.resolve(modulePath)).href;
-  return import(`${url}?test=${Date.now()}-${Math.random().toString(16).slice(2)}`);
-}
-
-test.after(() => {
-  if (ORIGINAL_STORAGE_KEY === undefined) {
-    delete process.env.STORAGE_ENCRYPTION_KEY;
-  } else {
-    process.env.STORAGE_ENCRYPTION_KEY = ORIGINAL_STORAGE_KEY;
-  }
-});
-
-function captureConsoleError(fn: () => void): string[] {
-  const original = console.error;
-  const logs: string[] = [];
-  console.error = (...args: unknown[]) => {
-    logs.push(args.join(" "));
-  };
-  try {
-    fn();
-  } finally {
-    console.error = original;
-  }
-  return logs;
-}
-
-test("decryptConnectionFields logs failed credential identity + recovery path (#9927)", async () => {
-  // 1. Encrypt an apiKey under key A.
-  process.env.STORAGE_ENCRYPTION_KEY = "stale-key-9927-A";
-  const encA = await importFresh("src/lib/db/encryption.ts");
-  const ciphertext = encA.encrypt("sk-real-secret-key");
-  assert.match(ciphertext, /^enc:v1:/, "expected a real enc:v1 ciphertext");
-
-  // 2. Read it back under a DIFFERENT key B (simulating a changed key).
-  process.env.STORAGE_ENCRYPTION_KEY = "stale-key-9927-B";
-  const encB = await importFresh("src/lib/db/encryption.ts");
-
-  const logs = captureConsoleError(() => {
-    encB.decryptConnectionFields({
-      id: "conn-9927",
-      provider: "openai",
-      apiKey: ciphertext,
-    });
-  });
-
-  // Must flag the failure so callers can surface the cause.
-  const decrypted = encB.decryptConnectionFields({
-    id: "conn-9927",
-    provider: "openai",
-    apiKey: ciphertext,
-  });
-  assert.equal(decrypted.credentialDecryptFailed, true);
-
-  // The generic low-level log must NOT fire (quiet:true); instead ONE enriched
-  // message names provider + connection id + recovery path.
-  assert.equal(
-    logs.some((l) => /Auth tag validation likely failed/.test(l)),
-    false,
-    "generic low-level decrypt log must be suppressed on the connection path"
-  );
-
-  const enriched = logs.find((l) => l.includes("Failed to decrypt credential(s)"));
-  assert.ok(enriched, "expected an enriched credential-decrypt-failure log");
-  assert.match(enriched, /provider "openai"/, "log must name the provider");
-  assert.match(enriched, /conn-9927/, "log must name the connection id");
-  assert.match(enriched, /apiKey/, "log must name the failing field");
-  assert.match(
-    enriched,
-    /STORAGE_ENCRYPTION_KEY matches the key used to store it/,
-    "log must include the recovery path"
-  );
-});
-
-test("credential-decrypt failure is logged once per connection (dedupe #9927)", async () => {
-  process.env.STORAGE_ENCRYPTION_KEY = "stale-key-9927-dedupe-A";
-  const encA = await importFresh("src/lib/db/encryption.ts");
-  const ciphertext = encA.encrypt("sk-dedupe-key");
-
-  process.env.STORAGE_ENCRYPTION_KEY = "stale-key-9927-dedupe-B";
-  const encB = await importFresh("src/lib/db/encryption.ts");
-
-  const row = { id: "conn-dedupe", provider: "openai", apiKey: ciphertext };
-  const logs = captureConsoleError(() => {
-    // Simulate the health sweep re-decrypting the same corrupt row repeatedly.
-    for (let i = 0; i < 5; i++) {
-      encB.decryptConnectionFields(row);
-    }
-  });
-
-  const enriched = logs.filter((l) => l.includes("Failed to decrypt credential(s)"));
-  assert.equal(enriched.length, 1, "identical failure must be logged once per connection");
-});