[db] migrateExpandedTypes fails with FOREIGN KEY constraint failed on any database with relationships rows, leaves entities_new behind, and then fails every subsequent init

[devinmlowe]

Evidence

src/_core/db/schema.ts:21 sets foreign_keys = ON; schema.ts:507-533:

db.exec(`
  CREATE TABLE entities_new ( … );
  INSERT INTO entities_new (id, name, type, description, aliases, first_seen, last_seen, mention_count, created_at)
    SELECT id, name, type, description, aliases, first_seen, last_seen, mention_count, created_at FROM entities;
  DROP TABLE entities;
  ALTER TABLE entities_new RENAME TO entities;
`);

relationships (:181-182) and bridge_scores (:274) reference entities(id) with no ON DELETE CASCADE. Repro with better-sqlite3 in :memory: using these DDL shapes: DROP TABLE entities throws FOREIGN KEY constraint failed as soon as one relationship row exists (SQLite's DROP TABLE runs an implicit DELETE that immediate FK constraints reject). db.exec autocommits each statement, so entities_new (already populated) survives; the fast-path check at :489 still sees the old entities DDL, and the next initDatabase fails at CREATE TABLE entities_new ("table already exists"). The INSERT … SELECT also omits conversation_count / informativeness, so when the rebuild does run against a table that has them they reset to 0.

Why it's a bug

Precondition: a database whose entities CHECK predates 'function' (created before 79c716e, 2026-03-10) that has relationships. Every fresh database still runs this rebuild (:152 — the base CREATE lacks 'function'), but while empty, which is why it passes today; no public release predates the migration, so the population is essentially pre-March developer databases — hence P3 despite the outcome (every command fails, and the database cannot self-heal). It is also the template any future CHECK-expansion migration would copy.

Suggested direction

Wrap the rebuild in a transaction with PRAGMA foreign_keys = OFF around it (the documented SQLite 12-step ALTER procedure), DROP TABLE IF EXISTS entities_new first, copy every column, and add 'function'/'class'/'module' to the base CREATE so new databases skip the rebuild.

Related: #7B.1 (79c716e).