Audit: SQLite batch migrations ran with foreign_keys=ON via init_database — cascade deletes and dropped FTS triggers

[CST-100]

Found during the PR #42 adversarial review; the systemic fix landed there (init_database now runs alembic on a plain engine, FK off, per alembic's documented batch-mode requirement), and the risk migration rebuilds the full FTS schema, healing dropped triggers for anyone upgrading through it. This issue tracks the historical exposure:

1. Any past upgrade through opal serve/opal init/the launcher (rather than opal migrate upgrade) ran batch migrations with PRAGMA foreign_keys=ON. SQLite's batch table-recreate (copy/DROP/RENAME) makes the DROP fire ON DELETE CASCADE on referencing tables. Each batch migration before the PR Risk module: scenarios, dispositions, acceptance (issue #40) #42 fix should be audited for which CASCADE children it could have silently emptied — e.g. ccf93c36170a (part identity) batch-recreated part; anything referencing part with CASCADE at that revision was at risk on that path.
2. Dropped FTS triggers: a batch recreate always drops the table's triggers regardless of the FK pragma. ccf93c36170a dropped the part_fts_* triggers and ecccad46c669 (issue overhaul, if batch) likely dropped issue_fts_* — deployments that upgraded through those revisions have stale search indexes for those entities until they pass through a7b9c1d3e5f7 (which rebuilds everything) or run a manual rebuild.

Suggested actions:

• Enumerate batch migrations and their CASCADE children per revision; decide if any deployment-facing data-loss advisory is needed (for us: check the live instance's part_fts trigger presence and reference-table row counts before/after its next upgrade).
• Consider a startup integrity check: warn when sqlite_master is missing expected FTS triggers (cheap, catches this class permanently).
• Consider a migration-chain smoke test in CI that upgrades a seeded fixture DB through the full chain on a pragma-ON engine and asserts row counts + trigger presence (the unit suite uses create_all and structurally cannot catch this).

🤖 Generated with Claude Code