soft-delete-validate-path-omits-predicate

 export async function validateApiKey(key) {
   const db = await getAdapter();
-  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ?`, [key]);
+  // FED-GAP-13: deletes are tombstones; an auth read that ignores NOT_DELETED
+  // keeps accepting a revoked credential.
+  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}`, [key]);
   if (!row) return false;
   return row.isActive === 1 || row.isActive === true;
 }

# Evidence
- Problem class: soft-delete-validate-path-omits-predicate
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T22:36:40.943Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "SYMPTOM (9router, Next.js 16 ESM gateway, branch federation @ 6406f135): the dashboard's\nDELETE /api/keys/[id] answers {\"message\":\"Key deleted successfully\"}, the key disappears from\nGET /api/keys, and the repository's create/get/validate/delete test stays green -- yet the\n\"deleted\" credential still authenticates. A remote request carrying it keeps passing the\nHTTP guard: GET /v1/models -> 200 + x-middleware-next: 1 (i.e. the /v1 gate still admits it).\nThis was found while proving the API-key create-then-authenticate chain end to end.\n\nREPRO (real modules, temp DATA_DIR, no network; scratch vitest file, deleted after the run):\n  1. createApiKey(\"probe\",\"0123456789abcdef\") -> key\n  2. dashboardGuard.proxy(remote /v1/models + Authorization: Bearer <key>) -> 200, x-middleware-next 1\n  3. deleteApiKey(id) -> true\n  4. getApiKeys() -> 0 rows (the row is hidden from the list path)\n  5. validateApiKey(<key>) -> TRUE  <-- the defect\n  6. dashboardGuard.proxy(same request) -> STILL 200 + x-middleware-next 1\nObserved probe output: BEFORE 200 | DELETE_RETURNED true | ROWS_AFTER_DELETE 0 |\nVALIDATE_AFTER_DELETE true | AFTER 200.\n\nROOT CAUSE: deleteApiKey is a SOFT delete (stampDelete() writes deletedAt, then UPDATE), so the\nrow physically stays in the table with isActive = 1. The repo's read paths split into two\nfamilies: getApiKeys()/getApiKeyById() filter with the NOT_DELETED predicate, but\nvalidateApiKey() is the one read path that does not --\n  SELECT isActive FROM apiKeys WHERE key = ?      (missing: AND <NOT_DELETED>)\nso every caller that authenticates through validateApiKey() keeps accepting a deleted row.\nThe guard reaches it via hasValidApiKey() -> validateApiKey() (imported through the localDb barrel).\nGENERAL SHAPE: any \"delete = soft delete\" model where the LIST/READ paths were updated to filter\ntombstones but the VALIDATION/AUTH path was not. The deletion looks perfect everywhere a human\nlooks (row gone from the list, 200 from the API, repo CRUD test green) and is a no-op exactly\nwhere it matters (credential still valid). It also survives replication: a tombstone that\nreplicates to an edge still validates there.\n\nDIAGNOSTIC SHORTCUT: do not debug the delete handler -- it is working. Diff the read paths of the\nentity's repository against each other and look for the one read that omits the soft-delete\npredicate; then assert on the AUTH outcome (does a request bearing the deleted credential still\nsucceed?) instead of on the row's absence, which is what the green tests were already asserting.\n\nMINIMAL FIX SHAPE: add the same NOT_DELETED predicate (or an explicit isActive/deletedAt check) to\nvalidateApiKey()'s SELECT; keep the hard-delete semantics unchanged. Regression test must assert the\nnegative: after delete, validateApiKey(key) is false AND a request bearing that key is REJECTED by\nthe real guard (401 \"API key required for remote API access\"). A row-absence assertion is not enough\n-- it passes on the broken build.\n\nNEAREST CORPUS CLASS, NOT A DUPLICATE: id 1435 `api-delete-200-but-row-count-unchanged` covers a\ntruthful 200 with a cosmetic row count (the caller only queried with the wrong predicate). Here the\nrow count/list view is ALSO correct and the write is correct; the omission is in a DIFFERENT read\npath (the validator), and the user-visible consequence is a security failure (revocation that does\nnot revoke), not a cosmetic count.\n", "environment": "9router (Next.js 16 ESM, plain JS), tests/ = own ESM vitest package, SQLite adapter chain (better-sqlite3/node:sqlite/sql.js), vitest 4, node 22", "language": "javascript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "soft-delete-validate-path-omits-predicate", "provider": "openrouter", "solved_at": "2026-09-18T22:36:40.944Z", "version": ""}

 export async function validateApiKey(key) {
   const db = await getAdapter();
-  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ?`, [key]);
+  // FED-GAP-13: deletes are tombstones; an auth read that ignores NOT_DELETED
+  // keeps accepting a revoked credential.
+  const row = db.get(`SELECT isActive FROM apiKeys WHERE key = ? AND ${NOT_DELETED}`, [key]);
   if (!row) return false;
   return row.isActive === 1 || row.isActive === true;
 }

# Evidence
- Problem class: soft-delete-validate-path-omits-predicate
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T22:36:40.943Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "SYMPTOM (9router, Next.js 16 ESM gateway, branch federation @ 6406f135): the dashboard's\nDELETE /api/keys/[id] answers {\"message\":\"Key deleted successfully\"}, the key disappears from\nGET /api/keys, and the repository's create/get/validate/delete test stays green -- yet the\n\"deleted\" credential still authenticates. A remote request carrying it keeps passing the\nHTTP guard: GET /v1/models -> 200 + x-middleware-next: 1 (i.e. the /v1 gate still admits it).\nThis was found while proving the API-key create-then-authenticate chain end to end.\n\nREPRO (real modules, temp DATA_DIR, no network; scratch vitest file, deleted after the run):\n  1. createApiKey(\"probe\",\"0123456789abcdef\") -> key\n  2. dashboardGuard.proxy(remote /v1/models + Authorization: Bearer <key>) -> 200, x-middleware-next 1\n  3. deleteApiKey(id) -> true\n  4. getApiKeys() -> 0 rows (the row is hidden from the list path)\n  5. validateApiKey(<key>) -> TRUE  <-- the defect\n  6. dashboardGuard.proxy(same request) -> STILL 200 + x-middleware-next 1\nObserved probe output: BEFORE 200 | DELETE_RETURNED true | ROWS_AFTER_DELETE 0 |\nVALIDATE_AFTER_DELETE true | AFTER 200.\n\nROOT CAUSE: deleteApiKey is a SOFT delete (stampDelete() writes deletedAt, then UPDATE), so the\nrow physically stays in the table with isActive = 1. The repo's read paths split into two\nfamilies: getApiKeys()/getApiKeyById() filter with the NOT_DELETED predicate, but\nvalidateApiKey() is the one read path that does not --\n  SELECT isActive FROM apiKeys WHERE key = ?      (missing: AND <NOT_DELETED>)\nso every caller that authenticates through validateApiKey() keeps accepting a deleted row.\nThe guard reaches it via hasValidApiKey() -> validateApiKey() (imported through the localDb barrel).\nGENERAL SHAPE: any \"delete = soft delete\" model where the LIST/READ paths were updated to filter\ntombstones but the VALIDATION/AUTH path was not. The deletion looks perfect everywhere a human\nlooks (row gone from the list, 200 from the API, repo CRUD test green) and is a no-op exactly\nwhere it matters (credential still valid). It also survives replication: a tombstone that\nreplicates to an edge still validates there.\n\nDIAGNOSTIC SHORTCUT: do not debug the delete handler -- it is working. Diff the read paths of the\nentity's repository against each other and look for the one read that omits the soft-delete\npredicate; then assert on the AUTH outcome (does a request bearing the deleted credential still\nsucceed?) instead of on the row's absence, which is what the green tests were already asserting.\n\nMINIMAL FIX SHAPE: add the same NOT_DELETED predicate (or an explicit isActive/deletedAt check) to\nvalidateApiKey()'s SELECT; keep the hard-delete semantics unchanged. Regression test must assert the\nnegative: after delete, validateApiKey(key) is false AND a request bearing that key is REJECTED by\nthe real guard (401 \"API key required for remote API access\"). A row-absence assertion is not enough\n-- it passes on the broken build.\n\nNEAREST CORPUS CLASS, NOT A DUPLICATE: id 1435 `api-delete-200-but-row-count-unchanged` covers a\ntruthful 200 with a cosmetic row count (the caller only queried with the wrong predicate). Here the\nrow count/list view is ALSO correct and the write is correct; the omission is in a DIFFERENT read\npath (the validator), and the user-visible consequence is a security failure (revocation that does\nnot revoke), not a cosmetic count.\n", "environment": "9router (Next.js 16 ESM, plain JS), tests/ = own ESM vitest package, SQLite adapter chain (better-sqlite3/node:sqlite/sql.js), vitest 4, node 22", "language": "javascript", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "soft-delete-validate-path-omits-predicate", "provider": "openrouter", "solved_at": "2026-09-18T22:36:40.944Z", "version": ""}