{"record":{"id":"43e21f16f55d6677","repo":"agalwood/Motrix","slug":"foreign-key-violation","errorCode":"foreign_key_violation","errorMessage":"The versioned database schema is incompatible with this build: the canonical schema contains foreign-key violations. This unpublished build updates the canonical v1 schema directly, so an existing local development database can have a valid version marker while its substantive tables are stale.\n\nAction: delete the database file and restart the app to recreate it on the current v1 schema.\n  rm '${dbPath}'","messagePattern":"The versioned database schema is incompatible with this build: the canonical schema contains foreign-key violations\\. This unpublished build updates the canonical v1 schema directly, so an existing local development database can have a valid version marker while its substantive tables are stale\\.\n\nAction: delete the database file and restart the app to recreate it on the current v1 schema\\.\n  rm '(.+?)'","errorType":"exception","errorClass":"StaleSchemaError","httpStatus":null,"severity":"critical","filePath":"src/core/session/migrations/index.ts","lineNumber":269,"sourceCode":"    ).filter((index) => index.origin === 'c')\n  )\n  const unexpectedTriggers = db\n    .prepare(\n      `SELECT name FROM sqlite_master\n       WHERE type = 'trigger'\n         AND tbl_name IN (\n           'task_inspector_activity',\n           'task_history_events',\n           'task_transfer_samples'\n         )`\n    )\n    .all()\n  if (unexpectedIndexes.length > 0 || unexpectedTriggers.length > 0) {\n    throw new StaleSchemaError('inspector_activity_schema_missing', dbPath)\n  }\n\n  if ((db.pragma('foreign_key_check') as unknown[]).length > 0) {\n    throw new StaleSchemaError('foreign_key_violation', dbPath)\n  }\n}\n\nexport function migrate(db: Database.Database): void {\n  db.exec(`\n    CREATE TABLE IF NOT EXISTS schema_version (\n      version INTEGER PRIMARY KEY,\n      applied_at INTEGER NOT NULL\n    )\n  `)\n  assertCanonicalSchemaVersion(db)\n  const row = db\n    .prepare('SELECT MAX(version) AS v FROM schema_version')\n    .get() as { v: number | null } | undefined\n  const current = row?.v ?? 0\n\n  // Guard A (Codex finding #7): schema_version newer than this build\n  // knows about. The default migration loop would skip everything,","sourceCodeStart":251,"sourceCodeEnd":287,"githubUrl":"https://github.com/agalwood/Motrix/blob/1a708ee57746c434e2c67a44bbf0906a976afea4/src/core/session/migrations/index.ts#L251-L287","documentation":"StaleSchemaError reason 'foreign_key_violation', thrown at the end of validateCanonicalV3() when PRAGMA foreign_key_check returns one or more rows. Despite all DDL checks passing, the canonical schema contains referential integrity violations — orphaned child rows pointing at non-existent parents. This is the most serious StaleSchemaError because it implies data corruption, not just DDL drift.","triggerScenarios":"validateCanonicalV3 line 268: (db.pragma('foreign_key_check') as unknown[]).length > 0. Fires only after DDL/index/trigger checks pass, so all canonical tables exist with correct shape but data is inconsistent.","commonSituations":"Foreign keys were disabled (PRAGMA foreign_keys=OFF) during a migration or data import that left orphans; an older build that predates FK enforcement; a partial/failed DELETE cascade; manual row deletion that violated FKs; restoring a DB dump that was taken without FK enforcement.","solutions":["Delete the DB and restart — this is the documented recovery; corrupted data should not be salvaged blindly.","If data preservation is essential, run PRAGMA foreign_key_check to list violations and reconcile or delete orphaned rows manually, then re-run migrate().","Ensure PRAGMA foreign_keys=ON is set on every connection that writes."],"exampleFix":"# before: DB contains FK orphans\n# after (data loss accepted — recommended):\n  rm '${dbPath}'\n# alternatively, list and reconcile:\n#   PRAGMA foreign_key_check;  -- inspect, then DELETE orphans","handlingStrategy":"try-catch","validationCode":"// Defensive pre-check — list FK violations before migrate()\nfunction listForeignKeyViolations(db: import('better-sqlite3').Database): Array<{ table: string; rowid: number; parent: string; fkid: number }> {\n  return db.pragma('foreign_key_check') as any;\n}","typeGuard":"function isStaleSchemaError(e: unknown): e is StaleSchemaError { return e instanceof StaleSchemaError; }","tryCatchPattern":"try {\n  migrate(db);\n} catch (e) {\n  if (e instanceof StaleSchemaError && e.reason === 'foreign_key_violation') {\n    const violations = db.pragma('foreign_key_check');\n    // decide: reset DB (recommended) or DELETE orphaned rows then re-run migrate()\n  } else throw e;\n}","preventionTips":["Always open better-sqlite3 connections with PRAGMA foreign_keys=ON.","Never restore from a dump taken with FK enforcement disabled.","In CI, assert PRAGMA foreign_key_check is empty after every migration test."],"tags":["database","sqlite","migration","foreign-keys","data-integrity","stale-schema"],"backgroundTag":null,"analyzedSha":"1a708ee57746c434e2c67a44bbf0906a976afea4","analyzedAt":"2026-08-12T16:18:09.346Z","schemaVersion":2},"datasetVersion":"2026-08-12T23:17:12.415Z"}