agalwood/Motrix · critical · StaleSchemaError

foreign_key_violation

foreign_key_violation

Error message

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.

Action: delete the database file and restart the app to recreate it on the current v1 schema.
  rm '${dbPath}'

What it means

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.

Source

Thrown at src/core/session/migrations/index.ts:269

    ).filter((index) => index.origin === 'c')
  )
  const unexpectedTriggers = db
    .prepare(
      `SELECT name FROM sqlite_master
       WHERE type = 'trigger'
         AND tbl_name IN (
           'task_inspector_activity',
           'task_history_events',
           'task_transfer_samples'
         )`
    )
    .all()
  if (unexpectedIndexes.length > 0 || unexpectedTriggers.length > 0) {
    throw new StaleSchemaError('inspector_activity_schema_missing', dbPath)
  }

  if ((db.pragma('foreign_key_check') as unknown[]).length > 0) {
    throw new StaleSchemaError('foreign_key_violation', dbPath)
  }
}

export function migrate(db: Database.Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_version (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL
    )
  `)
  assertCanonicalSchemaVersion(db)
  const row = db
    .prepare('SELECT MAX(version) AS v FROM schema_version')
    .get() as { v: number | null } | undefined
  const current = row?.v ?? 0

  // Guard A (Codex finding #7): schema_version newer than this build
  // knows about. The default migration loop would skip everything,

View on GitHub (pinned to 1a708ee577)

Solutions

1. Delete the DB and restart — this is the documented recovery; corrupted data should not be salvaged blindly.
2. If data preservation is essential, run PRAGMA foreign_key_check to list violations and reconcile or delete orphaned rows manually, then re-run migrate().
3. Ensure PRAGMA foreign_keys=ON is set on every connection that writes.

Example fix

# before: DB contains FK orphans
# after (data loss accepted — recommended):
  rm '${dbPath}'
# alternatively, list and reconcile:
#   PRAGMA foreign_key_check;  -- inspect, then DELETE orphans

// Defensive pre-check — list FK violations before migrate()
function listForeignKeyViolations(db: import('better-sqlite3').Database): Array<{ table: string; rowid: number; parent: string; fkid: number }> {
  return db.pragma('foreign_key_check') as any;
}

function isStaleSchemaError(e: unknown): e is StaleSchemaError { return e instanceof StaleSchemaError; }

try {
  migrate(db);
} catch (e) {
  if (e instanceof StaleSchemaError && e.reason === 'foreign_key_violation') {
    const violations = db.pragma('foreign_key_check');
    // decide: reset DB (recommended) or DELETE orphaned rows then re-run migrate()
  } else throw e;
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• inherited_schema_missing
• canonical_task_columns_missing
• inspector_activity_schema_missing
• legacy_table_present
• new_tables_missing

AI-assisted analysis of agalwood/Motrix@1a708ee577 (2026-08-12). Data as JSON: /api/errors/43e21f16f55d6677. Report an issue: GitHub.