Merge release/v3.8.51 (with #13211) into fix/batches-delete-completed-authz

# Conflicts:
#	src/app/api/v1/batches/delete-completed/route.ts
#	src/lib/db/batches.ts

changelog.d/fixes/12969-batches-delete-completed-ownership.md

@@ -0,0 +1 @@
+- **fix(api):** `DELETE /v1/batches/delete-completed` now sweeps only the calling API key's own completed batches (batches with no owner stay out of a key-scoped sweep on purpose), with an explicit instance-wide mode reserved for authenticated dashboard sessions, a 401 for a presented key that is unknown, revoked, deactivated, banned or expired (never falling through to the session branch), audit logging of every sweep, a sanitized 500 on failure, an owner-scoped file half (a key-scoped sweep never nulls a file another tenant owns) and an atomic sweep — chunked in 200-batch transactions in instance mode — so a mid-way error never leaves a batch pointing at a nulled file (GHSA-wvxc-jp3v-5mg5) ([#12969](https://github.com/diegosouzapw/OmniRoute/pull/12969))

src/app/api/v1/batches/delete-completed/route.ts

@@ -1,7 +1,12 @@
 import { CORS_HEADERS, handleCorsOptions } from "@/shared/utils/cors";
-import { deleteCompletedBatches } from "@/lib/db/batches";
+import { deleteCompletedBatches, type DeleteCompletedBatchesScope } from "@/lib/db/batches";
+import { validateApiKey } from "@/lib/db/apiKeys";
 import { NextResponse } from "next/server";
 import { getApiKeyRequestScope } from "@/app/api/v1/_helpers/apiKeyScope";
+import { buildErrorBody } from "@omniroute/open-sse/utils/error";
+import * as log from "@/sse/utils/logger";
+
+const LOG_ROUTE = "batches/delete-completed";
 
 export async function OPTIONS() {
   return handleCorsOptions();
@@ -11,18 +16,83 @@ export async function DELETE(request: Request) {
   const scope = await getApiKeyRequestScope(request);
   if (scope.rejection) return scope.rejection;
 
-  // Allow session-authenticated (dashboard) requests; for API-key requests, require a key
-  if (!scope.isSessionAuth && !scope.apiKeyId) {
-    return NextResponse.json(
-      { error: { message: "Authentication required", type: "invalid_request_error" } },
-      { status: 401, headers: CORS_HEADERS }
-    );
+  // Fail closed on an unresolvable OR invalid credential. `getApiKeyRequestScope`
+  // resolves the key by row EXISTENCE (so the list/count siblings can still
+  // attribute reads); existence is not authorization for a destructive sweep:
+  // a revoked, deactivated, banned or expired key still has a row and would
+  // otherwise run the sweep (CWE-613). `validateApiKey` is the one lifecycle
+  // gate (is_active, revoked_at, is_banned, expires_at) — and neither case may
+  // fall through to the session branch and widen the sweep to the whole instance.
+  if (scope.apiKey && (!scope.apiKeyId || !(await validateApiKey(scope.apiKey)))) {
+    log.warn("BATCHES", "delete-completed: presented API key rejected", {
+      route: LOG_ROUTE,
+      reason: scope.apiKeyId ? "invalid" : "unresolved",
+      apiKeyId: scope.apiKeyId,
+      isSessionAuth: scope.isSessionAuth,
+    });
+    return NextResponse.json(buildErrorBody(401, "Invalid API key"), {
+      status: 401,
+      headers: CORS_HEADERS,
+    });
   }
 
-  // Scope the sweep to the caller. Only the operator's own dashboard (session
-  // auth) may clear the whole instance; an API key clears only its own
-  // completed batches (GHSA-wvxc-jp3v-5mg5).
-  const result = deleteCompletedBatches(scope.isSessionAuth ? undefined : scope.apiKeyId);
+  // A presented API key always scopes the sweep to that key — even when the
+  // request also carries a dashboard session cookie — exactly like the
+  // list/count siblings (`apiKeyId || undefined`), so a leaked or over-shared
+  // key can never widen a destructive sweep. Only a dashboard session WITHOUT a
+  // key sweeps the whole instance; otherwise an ordinary key would delete every
+  // tenant's completed batches and null out their file contents
+  // (GHSA-wvxc-jp3v-5mg5). A caller that is neither gets 401; there is no
+  // fallback that silently widens the sweep.
+  let sweepScope: DeleteCompletedBatchesScope;
+  let mode: "instance" | "api_key";
+  if (scope.apiKeyId) {
+    sweepScope = { apiKeyId: scope.apiKeyId };
+    mode = "api_key";
+  } else if (scope.isSessionAuth) {
+    sweepScope = { allTenants: true };
+    mode = "instance";
+  } else {
+    return NextResponse.json(buildErrorBody(401, "Authentication required"), {
+      status: 401,
+      headers: CORS_HEADERS,
+    });
+  }
+
+  let result: ReturnType<typeof deleteCompletedBatches>;
+  try {
+    result = deleteCompletedBatches(sweepScope);
+  } catch (err) {
+    log.error("BATCHES", "delete-completed sweep failed", {
+      route: LOG_ROUTE,
+      mode,
+      apiKeyId: scope.apiKeyId,
+      error: err instanceof Error ? { message: err.message, stack: err.stack } : String(err),
+    });
+    return NextResponse.json(buildErrorBody(500, "Failed to delete completed batches"), {
+      status: 500,
+      headers: CORS_HEADERS,
+    });
+  }
+
+  const audit = {
+    route: LOG_ROUTE,
+    mode,
+    apiKeyId: scope.apiKeyId,
+    deletedBatches: result.deletedBatches,
+    deletedFiles: result.deletedFiles,
+  };
+  // A bulk delete is an audit event, not routine chatter: an instance-wide sweep
+  // and any key-scoped sweep that actually removed rows log at warn so the trail
+  // survives APP_LOG_LEVEL=warn; a no-op key-scoped sweep stays at info so a
+  // caller looping on the endpoint cannot flood the warn log.
+  if (mode === "instance") {
+    log.warn("BATCHES", "instance-wide completed-batch sweep", audit);
+  } else if (result.deletedBatches > 0) {
+    log.warn("BATCHES", "completed-batch sweep", audit);
+  } else {
+    log.info("BATCHES", "completed-batch sweep (no-op)", audit);
+  }
 
   return NextResponse.json(
     { deleted: true, deletedBatches: result.deletedBatches, deletedFiles: result.deletedFiles },

src/lib/db/batches.ts

@@ -1,6 +1,9 @@
 import { getDbInstance, rowToCamel, objToSnake } from "./core";
-import { deleteFile } from "./files";
+import { deleteFile, deleteFileOwnedBy } from "./files";
 import { v4 as uuidv4 } from "uuid";
+import { logger } from "../../../open-sse/utils/logger.ts";
+
+const log = logger("DB_BATCHES");
 
 function parseBatchRow(row: any): BatchRecord {
   const camel = rowToCamel(row) as any;
@@ -412,71 +415,155 @@ export function deleteBatch(id: string): boolean {
 }
 
 /**
- * Bulk-delete completed batches and the files they reference.
- *
- * `apiKeyId` scopes EVERY statement to that owner. Omitting it keeps the
- * instance-wide sweep, which is legitimate for the operator's own dashboard
- * (session auth) and for nothing else: without the predicate, an ordinary
- * inference key could wipe every tenant's completed batches and null out their
- * file contents (GHSA-wvxc-jp3v-5mg5). Same ownership shape as `listBatches`
- * and `countBatches` above.
+ * Scope of a `deleteCompletedBatches` sweep. The intent is explicit on purpose:
+ * a caller either names the API key whose batches it may sweep, or states
+ * `allTenants: true` — there is no default that widens to the whole instance.
  */
-export function deleteCompletedBatches(apiKeyId?: string | null): {
+export type DeleteCompletedBatchesScope = { apiKeyId: string } | { allTenants: true };
+
+/** Instance-wide sweeps commit in chunks of this many batches (SEC-D). */
+export const INSTANCE_SWEEP_CHUNK = 200;
+
+/**
+ * Delete completed batches and the files they reference.
+ *
+ * `{ apiKeyId }` scopes the sweep to that key's own batches, exactly like
+ * `listBatches`/`countBatches`. `{ allTenants: true }` sweeps the whole instance
+ * and is reserved for an authenticated dashboard session — an ordinary inference
+ * key that reached this without its own id would otherwise delete every tenant's
+ * completed batches and null out their file contents (GHSA-wvxc-jp3v-5mg5). A
+ * missing/empty `apiKeyId` without `allTenants` throws instead of silently
+ * widening the sweep, and a scope carrying BOTH `apiKeyId` and `allTenants` is
+ * rejected rather than widened.
+ *
+ * Batches whose `api_key_id` IS NULL are intentionally OUT of a key-scoped sweep.
+ * This diverges from `scopeCheck` in `src/app/api/v1/batches/[id]/route.ts`,
+ * which lets any key read/delete a single unowned batch by id: a bulk destructive
+ * sweep must never reach records the key does not own, so unowned batches are
+ * only swept by `{ allTenants: true }`.
+ *
+ * In key mode the file half is owner-scoped too: only files whose api_key_id is
+ * the caller's are soft-deleted; a referenced file another tenant owns (or an
+ * unowned one) is left intact and is not counted in deletedFiles.
+ *
+ * The file soft-deletes, the checkpoint DELETE and the batches DELETE for a set
+ * of batch ids run in one transaction, so a mid-sweep failure rolls that set back
+ * — within a chunk, no batch row is left pointing at a file whose content was
+ * already nulled. Both modes walk the key's/instance's completed batches in
+ * chunks of `INSTANCE_SWEEP_CHUNK` ids and run that unit once per chunk.
+ * Key mode wraps the whole chunk loop in ONE outer transaction (a nested
+ * transaction call is a savepoint on every adapter), so the key sweep stays
+ * all-or-nothing: a failure in any chunk rolls every earlier chunk back too.
+ * Instance mode (`allTenants`) commits per chunk (SEC-D): a large sweep never
+ * holds one write lock over the whole table, each chunk stays atomic, and a
+ * failure inside chunk N leaves chunks < N committed, chunk N fully rolled
+ * back, and rethrows. Inherent to per-chunk commits: a file shared by batches
+ * in two different chunks can be nulled by chunk 1 before chunk 2 fails; the
+ * surviving batch row is swept by the next run. The returned totals sum the
+ * chunks.
+ *
+ * The ids of a unit are bound as `IN (?, …)` placeholders. No statement ever
+ * binds more than `INSTANCE_SWEEP_CHUNK` ids in either mode, so a tenant with
+ * tens of thousands of completed batches never hits SQLite's default
+ * SQLITE_MAX_VARIABLE_NUMBER (32766 since 3.32).
+ */
+export function deleteCompletedBatches(scope: DeleteCompletedBatchesScope): {
   deletedBatches: number;
   deletedFiles: number;
 } {
+  const scopeObj = scope && typeof scope === "object" ? scope : {};
+  const allTenants = "allTenants" in scopeObj && scopeObj.allTenants === true;
+  const apiKeyId = "apiKeyId" in scopeObj ? scopeObj.apiKeyId : undefined;
+  if (!allTenants && (typeof apiKeyId !== "string" || apiKeyId.trim() === "")) {
+    throw new Error("deleteCompletedBatches: apiKeyId required unless allTenants");
+  }
+  if (allTenants && apiKeyId) {
+    throw new Error("deleteCompletedBatches: apiKeyId and allTenants are mutually exclusive");
+  }
+
   const db = getDbInstance();
-  const scoped = typeof apiKeyId === "string" && apiKeyId.length > 0;
 
-  // Collect unique file IDs from the completed batches in scope
-  const rows = (
-    scoped
-      ? db
-          .prepare(
-            "SELECT input_file_id, output_file_id, error_file_id FROM batches WHERE status = 'completed' AND api_key_id = ?"
-          )
-          .all(apiKeyId)
-      : db
-          .prepare(
-            "SELECT input_file_id, output_file_id, error_file_id FROM batches WHERE status = 'completed'"
-          )
-          .all()
-  ) as Array<{
-    input_file_id: string | null;
-    output_file_id: string | null;
-    error_file_id: string | null;
-  }>;
+  // One consistent unit: file soft-deletes → checkpoints → batch rows for a
+  // given set of batch ids. Both modes run it per chunk of INSTANCE_SWEEP_CHUNK
+  // ids; key mode nests the chunks in one outer transaction, instance mode
+  // commits each chunk so a large sweep never holds one write-lock for the
+  // whole table (SEC-D) while each chunk stays atomic.
+  const sweepIds = db.transaction((ids: string[]) => {
+    if (ids.length === 0) return { deletedBatches: 0, deletedFiles: 0 };
+    const marks = ids.map(() => "?").join(",");
+    const rows = db
+      .prepare(
+        `SELECT input_file_id, output_file_id, error_file_id FROM batches WHERE id IN (${marks})`
+      )
+      .all(...ids) as Array<{
+      input_file_id: string | null;
+      output_file_id: string | null;
+      error_file_id: string | null;
+    }>;
 
-  const fileIds = new Set<string>();
-  for (const row of rows) {
-    if (row.input_file_id) fileIds.add(row.input_file_id);
-    if (row.output_file_id) fileIds.add(row.output_file_id);
-    if (row.error_file_id) fileIds.add(row.error_file_id);
-  }
-
-  let deletedFiles = 0;
-  for (const fid of fileIds) {
-    try {
-      if (deleteFile(fid)) deletedFiles++;
-    } catch {
-      /* ignore */
+    const fileIds = new Set<string>();
+    for (const row of rows) {
+      if (row.input_file_id) fileIds.add(row.input_file_id);
+      if (row.output_file_id) fileIds.add(row.output_file_id);
+      if (row.error_file_id) fileIds.add(row.error_file_id);
     }
-  }
 
-  if (scoped) {
-    db.prepare(
-      "DELETE FROM batch_item_checkpoints WHERE batch_id IN (SELECT id FROM batches WHERE status = 'completed' AND api_key_id = ?)"
-    ).run(apiKeyId);
-    const result = db
-      .prepare("DELETE FROM batches WHERE status = 'completed' AND api_key_id = ?")
-      .run(apiKeyId);
+    let deletedFiles = 0;
+    for (const fid of fileIds) {
+      try {
+        // Key mode: only the key's OWN files. A batch may reference a file
+        // another tenant (or nobody) owns; a bulk destructive sweep must not
+        // reach it (SEC-C). Instance mode keeps the unconditional soft delete.
+        const removed = allTenants ? deleteFile(fid) : deleteFileOwnedBy(fid, apiKeyId as string);
+        if (removed) deletedFiles++;
+      } catch (err) {
+        log.warn("deleteCompletedBatches: file soft-delete failed", {
+          fid,
+          err: err instanceof Error ? err.message : String(err),
+        });
+      }
+    }
+
+    db.prepare(`DELETE FROM batch_item_checkpoints WHERE batch_id IN (${marks})`).run(...ids);
+    const result = db.prepare(`DELETE FROM batches WHERE id IN (${marks})`).run(...ids);
     return { deletedBatches: result.changes, deletedFiles };
+  });
+
+  if (!allTenants) {
+    // One outer transaction so the key sweep stays all-or-nothing; inside it,
+    // the same 200-id unit as instance mode (nested transaction calls become
+    // savepoints on every adapter), so no statement ever binds more than
+    // INSTANCE_SWEEP_CHUNK ids — a tenant with tens of thousands of completed
+    // batches must not hit SQLite's 32766 bound-parameter ceiling.
+    const keyChunk = db.prepare(
+      "SELECT id FROM batches WHERE status = 'completed' AND api_key_id = ? ORDER BY rowid LIMIT ?"
+    );
+    const sweepKey = db.transaction(() => {
+      const totals = { deletedBatches: 0, deletedFiles: 0 };
+      for (;;) {
+        const ids = (keyChunk.all(apiKeyId, INSTANCE_SWEEP_CHUNK) as Array<{ id: string }>).map(
+          (r) => r.id
+        );
+        if (ids.length === 0) break;
+        const part = sweepIds(ids);
+        totals.deletedBatches += part.deletedBatches;
+        totals.deletedFiles += part.deletedFiles;
+      }
+      return totals;
+    });
+    return sweepKey();
   }
 
-  db.prepare(
-    "DELETE FROM batch_item_checkpoints WHERE batch_id IN (SELECT id FROM batches WHERE status = 'completed')"
-  ).run();
-
-  const result = db.prepare("DELETE FROM batches WHERE status = 'completed'").run();
-  return { deletedBatches: result.changes, deletedFiles };
+  const totals = { deletedBatches: 0, deletedFiles: 0 };
+  const nextChunk = db.prepare(
+    "SELECT id FROM batches WHERE status = 'completed' ORDER BY rowid LIMIT ?"
+  );
+  for (;;) {
+    const ids = (nextChunk.all(INSTANCE_SWEEP_CHUNK) as Array<{ id: string }>).map((r) => r.id);
+    if (ids.length === 0) break;
+    const part = sweepIds(ids);
+    totals.deletedBatches += part.deletedBatches;
+    totals.deletedFiles += part.deletedFiles;
+  }
+  return totals;
 }

src/lib/db/files.ts

@@ -168,3 +168,22 @@ export function deleteFile(id: string): boolean {
     .run(Math.floor(Date.now() / 1000), id);
   return result.changes > 0;
 }
+
+/**
+ * Owner-scoped soft delete: same effect as `deleteFile`, but only when the
+ * file belongs to `apiKeyId`. Used by the key-scoped completed-batch sweep so
+ * a batch that references another tenant's (or an unowned) file never nulls
+ * that file's content (GHSA-wvxc-jp3v-5mg5, SEC-C). Returns false when the
+ * row is not the caller's; throws on an empty owner so a caller cannot widen
+ * the delete by passing a blank id.
+ */
+export function deleteFileOwnedBy(id: string, apiKeyId: string): boolean {
+  if (typeof apiKeyId !== "string" || apiKeyId.trim() === "") {
+    throw new Error("deleteFileOwnedBy: apiKeyId is required");
+  }
+  const db = getDbInstance();
+  const result = db
+    .prepare("UPDATE files SET deleted_at = ?, content = NULL WHERE id = ? AND api_key_id = ?")
+    .run(Math.floor(Date.now() / 1000), id, apiKeyId);
+  return result.changes > 0;
+}

tests/unit/batch-delete-completed-ownership-wvxc.test.ts

@@ -29,11 +29,15 @@ const KEY_A = "key-wvxc-aaaa";
 const KEY_B = "key-wvxc-bbbb";
 
 function seedCompletedBatch(apiKeyId: string | null, tag: string) {
+  // The file carries the batch's owner, as an upload through that key does in production.
+  // #13374 (SEC-C) scopes the file half of a key sweep to files the caller owns, so an
+  // unowned file referenced by the caller's batch is deliberately left intact.
   const file = createFile({
     bytes: 10,
     filename: `wvxc-${tag}.jsonl`,
     purpose: "batch",
     content: Buffer.from("{}"),
+    apiKeyId,
   });
   const batch = createBatch({
     endpoint: "/v1/chat/completions",
@@ -45,12 +49,15 @@ function seedCompletedBatch(apiKeyId: string | null, tag: string) {
   return { file, batch };
 }
 
+// The helper now takes an explicit scope (#12969): `{ apiKeyId }` or `{ allTenants: true }`.
+// A bare string or an omitted argument throws instead of widening the sweep, so every call
+// below states its scope. The assertions are the original #13211 ones, unchanged.
 describe("deleteCompletedBatches — ownership scoping (GHSA-wvxc-jp3v-5mg5)", () => {
   it("scoped to one key deletes ONLY that key's completed batches", () => {
     const a = seedCompletedBatch(KEY_A, "a1");
     const b = seedCompletedBatch(KEY_B, "b1");
 
-    const result = deleteCompletedBatches(KEY_A);
+    const result = deleteCompletedBatches({ apiKeyId: KEY_A });
 
     assert.equal(getBatch(a.batch.id), null, "the caller's own batch should be gone");
     assert.ok(getBatch(b.batch.id), "another key's batch must survive");
@@ -61,7 +68,7 @@ describe("deleteCompletedBatches — ownership scoping (GHSA-wvxc-jp3v-5mg5)", (
     const a = seedCompletedBatch(KEY_A, "a2");
     const b = seedCompletedBatch(KEY_B, "b2");
 
-    deleteCompletedBatches(KEY_A);
+    deleteCompletedBatches({ apiKeyId: KEY_A });
 
     assert.equal(getFile(a.file.id), null, "the caller's own file should be gone");
     assert.ok(getFile(b.file.id), "another key's file must survive with its content intact");
@@ -70,7 +77,7 @@ describe("deleteCompletedBatches — ownership scoping (GHSA-wvxc-jp3v-5mg5)", (
   it("a key with no completed batches deletes nothing at all", () => {
     const b = seedCompletedBatch(KEY_B, "b3");
 
-    const result = deleteCompletedBatches("key-wvxc-with-nothing");
+    const result = deleteCompletedBatches({ apiKeyId: "key-wvxc-with-nothing" });
 
     assert.equal(result.deletedBatches, 0);
     assert.equal(result.deletedFiles, 0);
@@ -79,11 +86,11 @@ describe("deleteCompletedBatches — ownership scoping (GHSA-wvxc-jp3v-5mg5)", (
 
   it("unscoped (dashboard session) still clears the whole instance", () => {
     // The operator's own dashboard legitimately cleans up everything; that is
-    // the ONLY caller allowed to omit the key. Preserved deliberately.
+    // the ONLY caller allowed to ask for allTenants. Preserved deliberately.
     seedCompletedBatch(KEY_A, "a4");
     seedCompletedBatch(KEY_B, "b4");
 
-    const result = deleteCompletedBatches();
+    const result = deleteCompletedBatches({ allTenants: true });
 
     assert.ok(
       result.deletedBatches >= 2,
@@ -107,7 +114,8 @@ describe("the route passes the caller's key through", () => {
       "the route still calls deleteCompletedBatches() with no owner — every tenant's batches go"
     );
     assert.ok(
-      /deleteCompletedBatches\(\s*scope\./.test(src),
+      /deleteCompletedBatches\(\s*sweepScope\s*\)/.test(src) &&
+        /sweepScope = \{ apiKeyId: scope\.apiKeyId \}/.test(src),
       "the route must pass the caller's scope into the helper"
     );
   });

tests/unit/batch-deletion.test.ts

@@ -180,7 +180,7 @@ describe("deleteCompletedBatches", () => {
     assert.ok(getFile(liveInput.id));
 
     // Delete all completed (may include pre-existing ones from other tests)
-    const result = deleteCompletedBatches();
+    const result = deleteCompletedBatches({ allTenants: true });
     assert.ok(result.deletedBatches >= 3, `expected >=3, got ${result.deletedBatches}`);
     assert.ok(result.deletedFiles >= 3, `expected >=3, got ${result.deletedFiles}`);
 
@@ -194,7 +194,7 @@ describe("deleteCompletedBatches", () => {
   });
 
   it("should return zero counts when no completed batches exist", () => {
-    const result = deleteCompletedBatches();
+    const result = deleteCompletedBatches({ allTenants: true });
     assert.strictEqual(result.deletedBatches, 0);
     assert.strictEqual(result.deletedFiles, 0);
   });
@@ -224,7 +224,7 @@ describe("deleteCompletedBatches", () => {
     assert.ok(getBatch(batchB.id));
     assert.ok(getFile(sharedFile.id));
 
-    const result = deleteCompletedBatches();
+    const result = deleteCompletedBatches({ allTenants: true });
     assert.ok(result.deletedBatches >= 2);
     assert.ok(result.deletedFiles >= 1, "shared file should be counted once");
 

tests/unit/batches-delete-completed-ownership-wvxc.test.ts

@@ -0,0 +1,407 @@
+/**
+ * GHSA-wvxc-jp3v-5mg5 — DELETE /api/v1/batches/delete-completed dropped the
+ * ownership predicate that every sibling batch operation keeps.
+ *
+ * `listBatches(apiKeyId?)` / `countBatches(apiKeyId?)` take the caller's key and
+ * scope the SQL to `api_key_id = ?`, falling back to instance-wide only when the
+ * caller is an authenticated dashboard session. `deleteCompletedBatches()` took
+ * no such argument, so any valid inference key — including one with
+ * `scopes: []` — deleted every completed batch on the instance and nulled the
+ * content of the files those batches referenced.
+ *
+ * These tests pin the DB-level contract:
+ *   - `{ apiKeyId }` sweeps only that key's completed batches;
+ *   - `{ allTenants: true }` is the ONLY way to sweep the whole instance — an
+ *     empty/missing apiKeyId throws instead of silently widening the sweep;
+ *   - batches with `api_key_id IS NULL` are deliberately OUT of a key-scoped
+ *     sweep (strict — diverges from the single-batch `scopeCheck` on purpose);
+ *   - the sweep is atomic: a failure after the file soft-deletes rolls the file
+ *     content back, so no batch row is left pointing at a nulled file;
+ *   - a file soft-delete failure is logged, not swallowed, and the batch rows
+ *     are still swept.
+ *
+ * Self-isolating: DATA_DIR is pointed at a fresh temp dir BEFORE any `@/lib/db/*`
+ * module loads (dynamic imports below), so this file never touches ~/.omniroute
+ * even when run without tests/_setup/isolateDataDir.ts.
+ */
+import { describe, it, after, mock } from "node:test";
+import assert from "node:assert";
+import fs from "node:fs";
+import os from "node:os";
+import path from "node:path";
+
+const TEST_DATA_DIR = fs.mkdtempSync(path.join(os.tmpdir(), "wvxc-"));
+process.env.DATA_DIR = TEST_DATA_DIR;
+// The soft-delete-failure test observes `log.warn`; pin the level so the assertion
+// does not depend on the ambient APP_LOG_LEVEL (a documented setting like `error` would hide it).
+process.env.APP_LOG_LEVEL = "warn";
+
+const { createFile, getFile, getFileContent } = await import("../../src/lib/db/files.ts");
+const { createBatch, getBatch, deleteCompletedBatches, INSTANCE_SWEEP_CHUNK } =
+  await import("../../src/lib/db/batches.ts");
+const { getDbInstance, resetDbInstance } = await import("../../src/lib/db/core.ts");
+
+/** One completed batch owned by `apiKeyId`, with its input file. */
+function seedCompletedBatch(apiKeyId: string | null, label: string) {
+  const file = createFile({
+    bytes: 8,
+    filename: `${label}.jsonl`,
+    purpose: "batch",
+    content: Buffer.from(label),
+    apiKeyId,
+  });
+  const batch = createBatch({
+    endpoint: "/v1/chat/completions",
+    completionWindow: "24h",
+    inputFileId: file.id,
+    status: "completed",
+    apiKeyId,
+  });
+  return { file, batch };
+}
+
+describe("deleteCompletedBatches — ownership boundary (GHSA-wvxc-jp3v-5mg5)", () => {
+  after(() => {
+    resetDbInstance();
+    fs.rmSync(TEST_DATA_DIR, { recursive: true, force: true, maxRetries: 5, retryDelay: 100 });
+  });
+
+  it("deletes only the caller's completed batches, never another key's", () => {
+    const attacker = seedCompletedBatch("key_attacker_wvxc", "wvxc-attacker");
+    const victim = seedCompletedBatch("key_victim_wvxc", "wvxc-victim");
+
+    const result = deleteCompletedBatches({ apiKeyId: "key_attacker_wvxc" });
+
+    assert.strictEqual(
+      getBatch(attacker.batch.id),
+      null,
+      "the caller's own completed batch should be deleted"
+    );
+    assert.ok(
+      getBatch(victim.batch.id),
+      "another key's completed batch must survive — this is the vulnerability"
+    );
+    assert.ok(
+      getFile(victim.file.id),
+      "another key's file content must not be cleared by a foreign caller"
+    );
+    assert.strictEqual(result.deletedBatches, 1, "only one batch belonged to the caller");
+  });
+
+  it("leaves a batch that is not completed alone, even when the caller owns it", () => {
+    const own = seedCompletedBatch("key_owner_wvxc", "wvxc-owner");
+    const inProgressFile = createFile({
+      bytes: 8,
+      filename: "wvxc-inprogress.jsonl",
+      purpose: "batch",
+      content: Buffer.from("running"),
+      apiKeyId: "key_owner_wvxc",
+    });
+    const inProgress = createBatch({
+      endpoint: "/v1/chat/completions",
+      completionWindow: "24h",
+      inputFileId: inProgressFile.id,
+      status: "in_progress",
+      apiKeyId: "key_owner_wvxc",
+    });
+
+    deleteCompletedBatches({ apiKeyId: "key_owner_wvxc" });
+
+    assert.strictEqual(getBatch(own.batch.id), null, "completed batch of the caller goes");
+    assert.ok(getBatch(inProgress.id), "an in-progress batch is never swept");
+  });
+
+  it("keeps the instance-wide sweep only for the explicit allTenants scope", () => {
+    const a = seedCompletedBatch("key_a_wvxc_global", "wvxc-global-a");
+    const b = seedCompletedBatch("key_b_wvxc_global", "wvxc-global-b");
+
+    deleteCompletedBatches({ allTenants: true });
+
+    assert.strictEqual(getBatch(a.batch.id), null, "session sweep clears every key");
+    assert.strictEqual(getBatch(b.batch.id), null, "session sweep clears every key");
+  });
+
+  it("throws on a missing/empty apiKeyId instead of silently sweeping the instance", () => {
+    const survivor = seedCompletedBatch("key_survivor_wvxc", "wvxc-survivor");
+
+    assert.throws(
+      () => deleteCompletedBatches({ apiKeyId: "" }),
+      /apiKeyId required unless allTenants/
+    );
+    assert.throws(
+      // A caller that forgot the scope entirely (JS caller / `any` cast) must not
+      // fall through to an instance-wide sweep either.
+      () => (deleteCompletedBatches as unknown as (s?: unknown) => unknown)(),
+      /apiKeyId required unless allTenants/
+    );
+    assert.throws(
+      () =>
+        (deleteCompletedBatches as unknown as (s: unknown) => unknown)({
+          apiKeyId: null,
+          allTenants: false,
+        }),
+      /apiKeyId required unless allTenants/
+    );
+    assert.ok(getBatch(survivor.batch.id), "a rejected call must not delete anything");
+    assert.strictEqual(
+      getFileContent(survivor.file.id)?.toString(),
+      "wvxc-survivor",
+      "a rejected call must not null file content"
+    );
+
+    deleteCompletedBatches({ apiKeyId: "key_survivor_wvxc" });
+  });
+
+  it("rejects a scope carrying both apiKeyId and allTenants instead of silently widening", () => {
+    const survivor = seedCompletedBatch("key_mixed_wvxc", "wvxc-mixed");
+    assert.throws(
+      () =>
+        (deleteCompletedBatches as unknown as (s: unknown) => unknown)({
+          apiKeyId: "key_mixed_wvxc",
+          allTenants: true,
+        }),
+      /mutually exclusive/
+    );
+    assert.ok(getBatch(survivor.batch.id), "a rejected mixed scope must not delete anything");
+    deleteCompletedBatches({ apiKeyId: "key_mixed_wvxc" });
+  });
+
+  it("STRICT: a batch with api_key_id NULL stays out of a key-scoped sweep (diverges from scopeCheck on purpose)", () => {
+    const unowned = seedCompletedBatch(null, "wvxc-unowned");
+    const own = seedCompletedBatch("key_strict_wvxc", "wvxc-strict-own");
+
+    const scoped = deleteCompletedBatches({ apiKeyId: "key_strict_wvxc" });
+
+    assert.strictEqual(scoped.deletedBatches, 1, "only the key's own batch is counted");
+    assert.strictEqual(getBatch(own.batch.id), null, "the key's own batch goes");
+    assert.ok(getBatch(unowned.batch.id), "the NULL-owned batch survives a key-scoped sweep");
+    assert.ok(getFile(unowned.file.id), "the NULL-owned batch's file survives");
+    assert.strictEqual(
+      getFileContent(unowned.file.id)?.toString(),
+      "wvxc-unowned",
+      "the NULL-owned batch's file content is intact"
+    );
+
+    const instanceWide = deleteCompletedBatches({ allTenants: true });
+
+    assert.ok(instanceWide.deletedBatches >= 1, "the allTenants sweep reaches unowned batches");
+    assert.strictEqual(getBatch(unowned.batch.id), null, "allTenants removes the NULL-owned batch");
+    assert.strictEqual(getFile(unowned.file.id), null, "allTenants soft-deletes its file too");
+  });
+
+  it("SEC-C: a key-scoped sweep never nulls a file another key owns, even when its own batch references it", () => {
+    const foreignFile = createFile({
+      bytes: 7,
+      filename: "foreign.jsonl",
+      purpose: "batch",
+      content: Buffer.from("foreign"),
+      apiKeyId: "key-other",
+    });
+    const unownedFile = createFile({
+      bytes: 7,
+      filename: "unowned.jsonl",
+      purpose: "batch",
+      content: Buffer.from("unowned"),
+      apiKeyId: null,
+    });
+    const own = seedCompletedBatch("key-secc", "secc-own");
+    const cross = createBatch({
+      endpoint: "/v1/chat/completions",
+      completionWindow: "24h",
+      inputFileId: foreignFile.id,
+      outputFileId: unownedFile.id,
+      status: "completed",
+      apiKeyId: "key-secc",
+    });
+
+    const result = deleteCompletedBatches({ apiKeyId: "key-secc" });
+
+    assert.strictEqual(result.deletedBatches, 2, "both of the key's completed batches are swept");
+    assert.strictEqual(result.deletedFiles, 1, "only the key's OWN file is soft-deleted");
+    assert.strictEqual(getBatch(own.batch.id), null);
+    assert.strictEqual(getBatch(cross.id), null);
+    assert.strictEqual(getFileContent(own.file.id), null, "own file content nulled");
+    assert.strictEqual(
+      getFileContent(foreignFile.id)?.toString(),
+      "foreign",
+      "another key's file intact"
+    );
+    assert.strictEqual(
+      getFileContent(unownedFile.id)?.toString(),
+      "unowned",
+      "unowned file intact"
+    );
+  });
+
+  it("ATOMIC: a failure after the file soft-deletes rolls the file content back", () => {
+    const db = getDbInstance();
+    const own = seedCompletedBatch("key_atomic_wvxc", "wvxc-atomic");
+
+    // Test-side failure injection: make the final `DELETE FROM batches` abort. The
+    // file soft-deletes and the checkpoint DELETE run before it, so without a
+    // transaction the batch row would survive pointing at a nulled file.
+    db.exec(
+      "CREATE TRIGGER wvxc_abort_batch_delete BEFORE DELETE ON batches BEGIN SELECT RAISE(ABORT, 'wvxc injected failure'); END"
+    );
+    try {
+      assert.throws(
+        () => deleteCompletedBatches({ apiKeyId: "key_atomic_wvxc" }),
+        /wvxc injected failure/
+      );
+    } finally {
+      db.exec("DROP TRIGGER IF EXISTS wvxc_abort_batch_delete");
+    }
+
+    const batch = getBatch(own.batch.id);
+    assert.ok(batch, "the batch row is still there after the failed sweep");
+    assert.ok(
+      getFile(own.file.id),
+      "the referenced file is not soft-deleted after the failed sweep"
+    );
+    assert.strictEqual(
+      getFileContent(own.file.id)?.toString(),
+      "wvxc-atomic",
+      "the file content was rolled back — no batch row points at a nulled file"
+    );
+
+    // Sanity: without the injected failure the sweep completes normally.
+    const result = deleteCompletedBatches({ apiKeyId: "key_atomic_wvxc" });
+    assert.strictEqual(result.deletedBatches, 1);
+    assert.strictEqual(result.deletedFiles, 1);
+    assert.strictEqual(getFile(own.file.id), null);
+  });
+
+  it("logs (does not swallow) a file soft-delete failure and still sweeps the batch rows", () => {
+    const db = getDbInstance();
+    const own = seedCompletedBatch("key_filefail_wvxc", "wvxc-filefail");
+    const warn = mock.method(console, "warn", () => {});
+
+    db.exec(
+      "CREATE TRIGGER wvxc_abort_file_update BEFORE UPDATE ON files BEGIN SELECT RAISE(ABORT, 'wvxc file failure'); END"
+    );
+    let result: { deletedBatches: number; deletedFiles: number };
+    try {
+      result = deleteCompletedBatches({ apiKeyId: "key_filefail_wvxc" });
+    } finally {
+      db.exec("DROP TRIGGER IF EXISTS wvxc_abort_file_update");
+      warn.mock.restore();
+    }
+
+    assert.strictEqual(result.deletedBatches, 1, "batch rows are swept even when a file fails");
+    assert.strictEqual(result.deletedFiles, 0, "a failed soft-delete is not counted");
+    assert.strictEqual(getBatch(own.batch.id), null);
+    const logged = warn.mock.calls
+      .map((c) => c.arguments.map((a) => String(a)).join(" "))
+      .find((line) => line.includes("deleteCompletedBatches: file soft-delete failed"));
+    assert.ok(logged, "the failure is logged at warn level");
+    assert.ok(logged!.includes(own.file.id), "the log line names the file id");
+  });
+  it("SEC-D: the instance sweep runs in chunks of INSTANCE_SWEEP_CHUNK and still deletes everything", () => {
+    const total = INSTANCE_SWEEP_CHUNK * 2 + 50; // 3 chunks: 200 + 200 + 50
+    const ids: string[] = [];
+    for (let i = 0; i < total; i++)
+      ids.push(seedCompletedBatch(i % 2 ? "key-chunk-a" : null, `chunk-${i}`).batch.id);
+    // `db.transaction(fn)` is called ONCE to build the unit; what must happen per
+    // chunk is the INVOCATION of the unit — count those.
+    const db = getDbInstance();
+    let runs = 0;
+    const origTx = db.transaction.bind(db);
+    const txSpy = mock.method(db, "transaction", (fn: (...a: unknown[]) => unknown) => {
+      const tx = origTx(fn);
+      return (...args: unknown[]) => {
+        runs++;
+        return tx(...args);
+      };
+    });
+
+    let result: ReturnType<typeof deleteCompletedBatches>;
+    try {
+      result = deleteCompletedBatches({ allTenants: true });
+    } finally {
+      txSpy.mock.restore();
+    }
+
+    assert.strictEqual(result.deletedBatches, total);
+    assert.strictEqual(result.deletedFiles, total);
+    assert.strictEqual(runs, 3, "one transaction per chunk (200 + 200 + 50)");
+    for (const id of ids) assert.strictEqual(getBatch(id), null);
+  });
+
+  it("SEC-D: a failure in chunk 2 keeps chunk 1 done and rolls chunk 2 back entirely", () => {
+    const first = Array.from({ length: INSTANCE_SWEEP_CHUNK }, (_, i) =>
+      seedCompletedBatch(null, `c1-${i}`)
+    );
+    const second = Array.from({ length: 10 }, (_, i) => seedCompletedBatch(null, `c2-${i}`));
+    const poison = second[5].batch.id;
+    const db = getDbInstance();
+    // DDL cannot take bound parameters in SQLite; the value is createBatch's generated id.
+    db.exec(
+      `CREATE TRIGGER wvxc_chunk_poison BEFORE DELETE ON batches WHEN OLD.id = '${poison}' BEGIN SELECT RAISE(ABORT, 'poison'); END`
+    );
+    try {
+      assert.throws(() => deleteCompletedBatches({ allTenants: true }), /poison/);
+    } finally {
+      db.exec("DROP TRIGGER IF EXISTS wvxc_chunk_poison");
+    }
+    for (const s of first) assert.strictEqual(getBatch(s.batch.id), null, "chunk 1 committed");
+    for (const s of second) {
+      assert.ok(getBatch(s.batch.id), "chunk 2 rolled back as a unit");
+      assert.strictEqual(
+        getFileContent(s.file.id)?.toString(),
+        s.file.filename.replace(".jsonl", ""),
+        "chunk 2 file content restored"
+      );
+    }
+  });
+
+  it("SEC-D: a key with more than INSTANCE_SWEEP_CHUNK completed batches is swept in one call, atomically", () => {
+    const total = INSTANCE_SWEEP_CHUNK + 1;
+    const own = Array.from({ length: total }, (_, i) => seedCompletedBatch("key-big", `big-${i}`));
+    const other = seedCompletedBatch("key-small", "big-other");
+    const db = getDbInstance();
+    let runs = 0;
+    const origTx = db.transaction.bind(db);
+    const txSpy = mock.method(db, "transaction", (fn: (...a: unknown[]) => unknown) => {
+      const tx = origTx(fn);
+      return (...args: unknown[]) => {
+        runs++;
+        return tx(...args);
+      };
+    });
+    let result: ReturnType<typeof deleteCompletedBatches>;
+    try {
+      result = deleteCompletedBatches({ apiKeyId: "key-big" });
+    } finally {
+      txSpy.mock.restore();
+    }
+    assert.strictEqual(result.deletedBatches, total);
+    assert.strictEqual(result.deletedFiles, total);
+    assert.ok(runs >= 3, `outer transaction + 2 chunk units expected, got ${runs}`);
+    for (const s of own) assert.strictEqual(getBatch(s.batch.id), null);
+    assert.ok(getBatch(other.batch.id), "another key's batch survives");
+  });
+
+  it("SEC-D: a failure in the key sweep's second chunk rolls the WHOLE key sweep back (single atomic transaction)", () => {
+    const own = Array.from({ length: INSTANCE_SWEEP_CHUNK + 5 }, (_, i) =>
+      seedCompletedBatch("key-atomic", `atomic-${i}`)
+    );
+    const poison = own[INSTANCE_SWEEP_CHUNK + 2].batch.id;
+    const db = getDbInstance();
+    // DDL cannot take bound parameters in SQLite; the value is createBatch's generated id.
+    db.exec(
+      `CREATE TRIGGER wvxc_key_poison BEFORE DELETE ON batches WHEN OLD.id = '${poison}' BEGIN SELECT RAISE(ABORT, 'key poison'); END`
+    );
+    try {
+      assert.throws(() => deleteCompletedBatches({ apiKeyId: "key-atomic" }), /key poison/);
+    } finally {
+      db.exec("DROP TRIGGER IF EXISTS wvxc_key_poison");
+    }
+    for (const s of own) {
+      assert.ok(getBatch(s.batch.id), "key sweep is all-or-nothing: chunk 1 rolled back too");
+      assert.strictEqual(
+        getFileContent(s.file.id)?.toString(),
+        s.file.filename.replace(".jsonl", "")
+      );
+    }
+  });
+});

tests/unit/batches-delete-completed-route-scope.test.ts

@@ -0,0 +1,294 @@
+/**
+ * GHSA-wvxc-jp3v-5mg5 — route-level regression guard for
+ * `DELETE /api/v1/batches/delete-completed`.
+ *
+ * The DB-level contract lives in batches-delete-completed-ownership-wvxc.test.ts.
+ * This file drives the REAL route handler with REAL credentials (API keys created
+ * through `createApiKey`, a dashboard session via a signed `auth_token` cookie) so
+ * it fails whenever the route stops translating the caller's scope into the
+ * matching `deleteCompletedBatches` scope:
+ *
+ *   - an inference key must only sweep its own completed batches and the response
+ *     must report 0 deletions when it owns none — proven against an un-scoped
+ *     `{ allTenants: true }` regression (the "flip" recorded in the PR notes);
+ *   - a presented API key always scopes the sweep to that key, even alongside a
+ *     dashboard session cookie (the key wins, like GET /v1/batches); only a
+ *     session WITHOUT a key sweeps the whole instance;
+ *   - a presented key that does not resolve (deleted/rotated/mistyped) is rejected
+ *     with 401 even when a session cookie is also present (fail closed);
+ *   - a presented key that resolves but is no longer VALID (revoked, deactivated,
+ *     banned or expired) is rejected with 401 too — existence of the row is not
+ *     authorization (CWE-613); the 401 body is the `buildErrorBody()` shape;
+ *   - no credentials at all → 401;
+ *   - a sweep that throws → sanitized 500 (no stack trace, no raw SQLite message)
+ *     and nothing deleted (the sweep is atomic).
+ *
+ * Self-isolating: DATA_DIR points at a fresh temp dir BEFORE any `@/lib/db/*`
+ * module loads (dynamic imports below), so this file never touches ~/.omniroute.
+ */
+import { describe, it, after } from "node:test";
+import assert from "node:assert";
+import fs from "node:fs";
+import os from "node:os";
+import path from "node:path";
+import { SignJWT } from "jose";
+
+const TEST_DATA_DIR = fs.mkdtempSync(path.join(os.tmpdir(), "wvxc-route-"));
+process.env.DATA_DIR = TEST_DATA_DIR;
+process.env.API_KEY_SECRET = process.env.API_KEY_SECRET || "wvxc-route-api-secret";
+process.env.JWT_SECRET = "wvxc-route-jwt-secret";
+
+const { getDbInstance, resetDbInstance } = await import("../../src/lib/db/core.ts");
+const { createApiKey, revokeApiKey, updateApiKeyPermissions, setApiKeyExpiry } =
+  await import("../../src/lib/db/apiKeys.ts");
+const { createFile, getFile, getFileContent } = await import("../../src/lib/db/files.ts");
+const { createBatch, getBatch } = await import("../../src/lib/db/batches.ts");
+const { DELETE } = await import("../../src/app/api/v1/batches/delete-completed/route.ts");
+
+const ROUTE_URL = "http://localhost/api/v1/batches/delete-completed";
+
+async function sessionCookie(): Promise<string> {
+  const secret = new TextEncoder().encode(process.env.JWT_SECRET);
+  const jwt = await new SignJWT({ authenticated: true, sub: "admin" })
+    .setProtectedHeader({ alg: "HS256" })
+    .setExpirationTime("1h")
+    .sign(secret);
+  return `auth_token=${jwt}`;
+}
+
+function seedCompletedBatch(apiKeyId: string | null, label: string) {
+  const file = createFile({
+    bytes: 8,
+    filename: `${label}.jsonl`,
+    purpose: "batch",
+    content: Buffer.from(label),
+    apiKeyId,
+  });
+  const batch = createBatch({
+    endpoint: "/v1/chat/completions",
+    completionWindow: "24h",
+    inputFileId: file.id,
+    status: "completed",
+    apiKeyId,
+  });
+  return { file, batch };
+}
+
+async function callDelete(headers: Record<string, string>) {
+  const res = await DELETE(new Request(ROUTE_URL, { method: "DELETE", headers }));
+  const body = (await res.json()) as {
+    deleted?: boolean;
+    deletedBatches?: number;
+    deletedFiles?: number;
+    error?: { message: string; type?: string; code?: string };
+  };
+  return { res, body };
+}
+
+describe("DELETE /api/v1/batches/delete-completed — caller scope (GHSA-wvxc-jp3v-5mg5)", () => {
+  after(() => {
+    resetDbInstance();
+    fs.rmSync(TEST_DATA_DIR, { recursive: true, force: true, maxRetries: 5, retryDelay: 100 });
+  });
+
+  it("an inference key sweeps only its own batches: another key's batch survives and the response reports 0", async () => {
+    const keyA = await createApiKey("wvxc-route-key-a", "machine-wvxc-a", []);
+    const keyB = await createApiKey("wvxc-route-key-b", "machine-wvxc-b", []);
+    const victim = seedCompletedBatch(keyB.id, "wvxc-route-victim");
+
+    const { res, body } = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+
+    assert.strictEqual(res.status, 200);
+    assert.strictEqual(body.deleted, true);
+    assert.strictEqual(body.deletedBatches, 0, "key A owns no completed batch — nothing to sweep");
+    assert.strictEqual(body.deletedFiles, 0);
+    assert.ok(getBatch(victim.batch.id), "key B's completed batch must survive key A's sweep");
+    assert.strictEqual(
+      getFileContent(victim.file.id)?.toString(),
+      "wvxc-route-victim",
+      "key B's file content must not be nulled by key A"
+    );
+
+    // And key A's own batch IS swept by key A.
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-own");
+    const second = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+    assert.strictEqual(second.res.status, 200);
+    assert.strictEqual(second.body.deletedBatches, 1);
+    assert.strictEqual(second.body.deletedFiles, 1);
+    assert.strictEqual(getBatch(own.batch.id), null, "key A's own completed batch is swept");
+    assert.ok(getBatch(victim.batch.id), "key B's batch still survives");
+  });
+
+  it("a dashboard session WITHOUT a key sweeps the whole instance", async () => {
+    const keyB = await createApiKey("wvxc-route-session-b", "machine-wvxc-sb", []);
+    const other = seedCompletedBatch(keyB.id, "wvxc-route-session-other");
+    const unowned = seedCompletedBatch(null, "wvxc-route-session-unowned");
+
+    const { res, body } = await callDelete({ cookie: await sessionCookie() });
+
+    assert.strictEqual(res.status, 200);
+    assert.ok(
+      body.deletedBatches! >= 2,
+      `session sweep must reach every tenant, got ${body.deletedBatches}`
+    );
+    assert.strictEqual(getBatch(other.batch.id), null, "session sweep removes another key's batch");
+    assert.strictEqual(getBatch(unowned.batch.id), null, "session sweep removes the unowned batch");
+    assert.strictEqual(getFile(other.file.id), null, "session sweep soft-deletes the files too");
+  });
+
+  it("a request carrying BOTH a session cookie and an API key is scoped to the key (the key wins, like GET /v1/batches)", async () => {
+    const keyA = await createApiKey("wvxc-route-both-a", "machine-wvxc-ba", []);
+    const keyB = await createApiKey("wvxc-route-both-b", "machine-wvxc-bb", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-both-own");
+    const other = seedCompletedBatch(keyB.id, "wvxc-route-both-other");
+    const unowned = seedCompletedBatch(null, "wvxc-route-both-unowned");
+
+    const { res, body } = await callDelete({
+      Authorization: `Bearer ${keyA.key}`,
+      cookie: await sessionCookie(),
+    });
+
+    assert.strictEqual(res.status, 200);
+    assert.strictEqual(body.deletedBatches, 1, "only key A's own completed batch is swept");
+    assert.strictEqual(getBatch(own.batch.id), null, "key A's own batch is swept");
+    assert.ok(
+      getBatch(other.batch.id),
+      "key B's batch survives — a presented key never widens the sweep"
+    );
+    assert.ok(getBatch(unowned.batch.id), "the unowned batch survives a key-scoped sweep");
+    assert.strictEqual(
+      getFileContent(other.file.id)?.toString(),
+      "wvxc-route-both-other",
+      "key B's file content is intact"
+    );
+  });
+
+  it("rejects a presented API key that does not resolve with 401 — even alongside a session cookie — and deletes nothing", async () => {
+    const keyB = await createApiKey("wvxc-route-unknown-b", "machine-wvxc-ub", []);
+    const other = seedCompletedBatch(keyB.id, "wvxc-route-unknown-other");
+
+    const { res, body } = await callDelete({
+      Authorization: "Bearer sk-omni-this-key-was-rotated-away-wvxc",
+      cookie: await sessionCookie(),
+    });
+
+    assert.strictEqual(
+      res.status,
+      401,
+      "an unresolvable key must fail closed, not fall through to the session"
+    );
+    assert.match(body.error?.message ?? "", /Invalid API key/);
+    assert.ok(getBatch(other.batch.id), "nothing is swept on a rejected credential");
+    assert.strictEqual(
+      getFileContent(other.file.id)?.toString(),
+      "wvxc-route-unknown-other",
+      "file content is intact on a rejected credential"
+    );
+  });
+
+  it("rejects a REVOKED key with 401 — the row still exists but is no longer valid — and deletes nothing", async () => {
+    const keyA = await createApiKey("wvxc-route-revoked-a", "machine-wvxc-ra", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-revoked-own");
+    assert.strictEqual(await revokeApiKey(keyA.id), true);
+
+    const { res, body } = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+
+    assert.strictEqual(res.status, 401, "a revoked key must not run the sweep");
+    assert.strictEqual(body.error?.message, "Invalid API key");
+    assert.strictEqual(body.error?.type, "authentication_error");
+    assert.strictEqual(body.error?.code, "invalid_api_key");
+    assert.ok(getBatch(own.batch.id), "nothing is swept with a revoked key");
+    assert.strictEqual(
+      getFileContent(own.file.id)?.toString(),
+      "wvxc-route-revoked-own",
+      "file content is intact with a revoked key"
+    );
+  });
+
+  it("rejects a DEACTIVATED key (is_active = 0) with 401 and deletes nothing", async () => {
+    const keyA = await createApiKey("wvxc-route-inactive-a", "machine-wvxc-ia", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-inactive-own");
+    await updateApiKeyPermissions(keyA.id, { isActive: false });
+
+    const { res, body } = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+
+    assert.strictEqual(res.status, 401, "a deactivated key must not run the sweep");
+    assert.strictEqual(body.error?.message, "Invalid API key");
+    assert.ok(getBatch(own.batch.id), "nothing is swept with a deactivated key");
+  });
+
+  it("rejects a BANNED key with 401 and deletes nothing", async () => {
+    const keyA = await createApiKey("wvxc-route-banned-a", "machine-wvxc-ba2", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-banned-own");
+    await updateApiKeyPermissions(keyA.id, { isBanned: true });
+
+    const { res, body } = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+
+    assert.strictEqual(res.status, 401, "a banned key must not run the sweep");
+    assert.strictEqual(body.error?.message, "Invalid API key");
+    assert.ok(getBatch(own.batch.id), "nothing is swept with a banned key");
+  });
+
+  it("rejects an EXPIRED key with 401 — even alongside a session cookie — and deletes nothing", async () => {
+    const keyA = await createApiKey("wvxc-route-expired-a", "machine-wvxc-ea", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-expired-own");
+    const unowned = seedCompletedBatch(null, "wvxc-route-expired-unowned");
+    await setApiKeyExpiry(keyA.id, new Date(Date.now() - 60_000).toISOString());
+
+    const { res, body } = await callDelete({
+      Authorization: `Bearer ${keyA.key}`,
+      cookie: await sessionCookie(),
+    });
+
+    assert.strictEqual(
+      res.status,
+      401,
+      "an expired key must fail closed, not fall through to the session"
+    );
+    assert.strictEqual(body.error?.message, "Invalid API key");
+    assert.ok(getBatch(own.batch.id), "nothing is swept with an expired key");
+    assert.ok(getBatch(unowned.batch.id), "the session branch is never reached");
+  });
+
+  it("rejects an unauthenticated request with 401 and deletes nothing", async () => {
+    const keyB = await createApiKey("wvxc-route-401-b", "machine-wvxc-401", []);
+    const seeded = seedCompletedBatch(keyB.id, "wvxc-route-401");
+
+    const { res, body } = await callDelete({});
+
+    assert.strictEqual(res.status, 401);
+    assert.strictEqual(body.error?.message, "Authentication required");
+    assert.strictEqual(body.error?.type, "authentication_error", "401 body uses buildErrorBody()");
+    assert.strictEqual(body.error?.code, "invalid_api_key");
+    assert.ok(getBatch(seeded.batch.id), "nothing is swept without credentials");
+  });
+
+  it("returns a sanitized 500 (no stack trace, no raw SQLite message) when the sweep throws, and deletes nothing", async () => {
+    const keyA = await createApiKey("wvxc-route-500-a", "machine-wvxc-500", []);
+    const own = seedCompletedBatch(keyA.id, "wvxc-route-500");
+    const db = getDbInstance();
+
+    db.exec(
+      "CREATE TRIGGER wvxc_route_abort_batch_delete BEFORE DELETE ON batches BEGIN SELECT RAISE(ABORT, 'wvxc route injected failure at /secret/path.ts:1'); END"
+    );
+    let outcome: Awaited<ReturnType<typeof callDelete>>;
+    try {
+      outcome = await callDelete({ Authorization: `Bearer ${keyA.key}` });
+    } finally {
+      db.exec("DROP TRIGGER IF EXISTS wvxc_route_abort_batch_delete");
+    }
+
+    assert.strictEqual(outcome.res.status, 500);
+    const message = outcome.body.error?.message ?? "";
+    assert.ok(message.length > 0, "a 500 still carries a human-readable message");
+    assert.ok(!message.includes("at /"), `stack trace leaked: ${message}`);
+    assert.ok(!message.includes("wvxc route injected failure"), `raw DB error leaked: ${message}`);
+    assert.ok(!message.includes("SQLITE"), `raw SQLite code leaked: ${message}`);
+    assert.ok(getBatch(own.batch.id), "a failed sweep leaves the batch row in place");
+    assert.strictEqual(
+      getFileContent(own.file.id)?.toString(),
+      "wvxc-route-500",
+      "a failed sweep rolls the file content back"
+    );
+  });
+});

tests/unit/files-delete-owned-by.test.ts

@@ -0,0 +1,56 @@
+// tests/unit/files-delete-owned-by.test.ts
+import { describe, it, after } from "node:test";
+import assert from "node:assert";
+import fs from "node:fs";
+import os from "node:os";
+import path from "node:path";
+
+const TEST_DATA_DIR = fs.mkdtempSync(path.join(os.tmpdir(), "files-owned-"));
+process.env.DATA_DIR = TEST_DATA_DIR;
+
+const { resetDbInstance } = await import("../../src/lib/db/core.ts");
+const { createFile, getFile, getFileContent, deleteFileOwnedBy } =
+  await import("../../src/lib/db/files.ts");
+
+const seed = (apiKeyId: string | null, label: string) =>
+  createFile({
+    bytes: label.length,
+    filename: `${label}.jsonl`,
+    purpose: "batch",
+    content: Buffer.from(label),
+    apiKeyId,
+  });
+
+describe("deleteFileOwnedBy — owner-scoped soft delete", () => {
+  after(() => {
+    resetDbInstance();
+    fs.rmSync(TEST_DATA_DIR, { recursive: true, force: true, maxRetries: 5, retryDelay: 100 });
+  });
+
+  it("soft-deletes the owner's own file and nulls its content", () => {
+    const own = seed("key-A", "own");
+    assert.strictEqual(deleteFileOwnedBy(own.id, "key-A"), true);
+    assert.strictEqual(getFile(own.id), null, "metadata read hides a soft-deleted file");
+    assert.strictEqual(getFileContent(own.id), null);
+  });
+
+  it("returns false and leaves another key's file intact", () => {
+    const other = seed("key-B", "other");
+    assert.strictEqual(deleteFileOwnedBy(other.id, "key-A"), false);
+    assert.ok(getFile(other.id));
+    assert.strictEqual(getFileContent(other.id)?.toString(), "other");
+  });
+
+  it("returns false for an unowned file (api_key_id NULL) — only the instance sweep reaches those", () => {
+    const unowned = seed(null, "unowned");
+    assert.strictEqual(deleteFileOwnedBy(unowned.id, "key-A"), false);
+    assert.strictEqual(getFileContent(unowned.id)?.toString(), "unowned");
+  });
+
+  it("throws on an empty apiKeyId instead of widening", () => {
+    const own = seed("key-A", "guard");
+    assert.throws(() => deleteFileOwnedBy(own.id, ""), /apiKeyId/);
+    assert.throws(() => deleteFileOwnedBy(own.id, undefined as unknown as string), /apiKeyId/);
+    assert.strictEqual(getFileContent(own.id)?.toString(), "guard");
+  });
+});