{"record":{"id":"ba5e3d4f32c75883","repo":"nanocoai/nanoclaw","slug":"pre-existing-fk-violations-carried-through-migrati","errorCode":null,"errorMessage":"Pre-existing FK violations carried through migration (not introduced by it)","messagePattern":"Pre-existing FK violations carried through migration \\(not introduced by it\\)","errorType":"console","errorClass":null,"httpStatus":null,"severity":"warning","filePath":"src/db/migrations/index.ts","lineNumber":218,"sourceCode":"  // no-op inside one); foreign_key_check runs INSIDE so a violating\n  // recreate rolls back atomically with nothing committed.\n  if (disableForeignKeys) raw!.pragma('foreign_keys = OFF');\n  try {\n    await db.transaction(async () => {\n      // Snapshot violations BEFORE up() runs: live DBs can carry latent\n      // FK orphans. A migration must fail only for violations it introduces.\n      const preexisting = disableForeignKeys\n        ? new Set((raw!.pragma('foreign_key_check') as FkViolation[]).map(fkIdentity))\n        : null;\n      if (override) await override.up(db);\n      else if (migration.sqliteOnly) await migration.up(raw!);\n      else await migration.up(db);\n      if (disableForeignKeys && preexisting) {\n        const violations = raw!.pragma('foreign_key_check') as FkViolation[];\n        const introduced = violations.filter((violation) => !preexisting.has(fkIdentity(violation)));\n        const carried = violations.length - introduced.length;\n        if (carried > 0) {\n          log.warn('Pre-existing FK violations carried through migration (not introduced by it)', {\n            migration: migration.name,\n            count: carried,\n          });\n        }\n        if (introduced.length > 0) {\n          throw new Error(`migration ${migration.name} left FK violations: ${JSON.stringify(introduced.slice(0, 5))}`);\n        }\n      }\n      const next = (await db.get<{ v: number }>('SELECT COALESCE(MAX(version), 0) + 1 AS v FROM schema_version'))!.v;\n      await db.run(\n        'INSERT INTO schema_version (version, name, applied) VALUES (?, ?, ?)',\n        next,\n        migration.name,\n        new Date().toISOString(),\n      );\n    });\n  } finally {\n    if (disableForeignKeys) raw!.pragma('foreign_keys = ON');","sourceCodeStart":200,"sourceCodeEnd":236,"githubUrl":"https://github.com/nanocoai/nanoclaw/blob/294ef2aee85218b23ad30eda9dfe10e590b54a8c/src/db/migrations/index.ts#L200-L236","documentation":"After running a migration with foreign keys disabled, `foreign_key_check` found violations, but all of them existed before the migration ran (matched by identity against a pre-migration snapshot). The migration itself is not at fault; the warning is informational so pre-existing corruption isn't confused with migration-introduced violations (which instead throw).","triggerScenarios":"`migrate()` runs `applyMigration` with `disableForeignKeys`; pre-existing orphan rows (e.g. sessions referencing a deleted agent_group) survive the migration and are counted.","commonSituations":"DBs corrupted by older bugs, manual deletes that bypassed FK enforcement, or restores from partial backups; surfaces during `pnpm run dev` startup or migrations after upgrading.","solutions":["Identify orphans: `pnpm exec tsx scripts/q.ts data/v2.db \"pragma foreign_key_check\"`","Delete or reparent the orphaned rows","Back up data/v2.db before manual fixes"],"exampleFix":null,"handlingStrategy":"validation","validationCode":"const v = db.pragma('foreign_key_check', { simple: true }) as unknown[];\nif (v.length > 0) { /* resolve orphans before migrating */ }","typeGuard":null,"tryCatchPattern":"try { await migrate(db); } catch (e) { if (/left FK violations/.test(String(e))) { /* migration bug */ } throw e; }","preventionTips":["Run `pragma foreign_key_check` and resolve orphans before upgrading","Never delete parent rows with FKs off","Back up data/v2.db before running migrations"],"tags":["database","migration","foreign-keys","sqlite"],"backgroundTag":"foreign-key-violation","analyzedSha":"294ef2aee85218b23ad30eda9dfe10e590b54a8c","analyzedAt":"2026-08-28T13:59:10.357Z","schemaVersion":2},"datasetVersion":"2026-08-28T16:17:29.566Z"}