SQLite per-connection state (foreign keys, sqlite-vec extension) is registered per-store on caller-supplied engines #1568

SQLite requires per-connection state for two things this codebase depends on: PRAGMA foreign_keys=ON (FK enforcement, including ON DELETE CASCADE) and loadable extensions (sqlite-vec). Several stores register that state via a connect listener in their own __init__, on an engine supplied through their params. That placement has two structural faults, independent of the store:

1. Connections pooled before the store exists never receive the state. A connect listener fires only for new DBAPI connections; anything the (possibly shared) engine pooled earlier -- e.g. a validation SELECT 1 -- serves later statements with foreign keys off or the extension unloaded. For FK, that means cascade deletes silently leave orphaned child rows; nothing errors.
2. Registering listeners on a live pool races its event dispatch. SQLAlchemy dispatches events by iterating a plain deque; a concurrent event.listen (a second store constructed while the engine serves traffic) mutates it mid-iteration and raises RuntimeError: deque mutated during iteration. Reproduced deterministically in Overhaul segment store: shared tables with incarnation-scoped tenant keys (fixes #1544, #1546, #1549) #1545's concurrency tests.

#1545 hit exactly this in the segment store (a live bug there: the shared relational engine is validated -- and therefore pooled -- before the store is constructed) and established the fix convention:

• per-connection state is registered at engine creation, before anything is pooled: enable_sqlite_foreign_keys(engine) in common/resource_manager/database_manager.py, applied to the engines the DatabaseManager creates;
• the store verifies instead of mutates: startup() checks PRAGMA foreign_keys and refuses an unenforced engine with a directive naming the helper.

Remaining instances of the old pattern, out of #1545's scope:

• common/vector_store/sqlite_vector_store.py (~line 691): SQLiteVectorStore.__init__ registers the FK pragma on its caller-supplied params.sqlalchemy_engine (the second decorator, on the sync engine it creates itself two lines above, is fine). Latent rather than live today: the DatabaseManager creates that engine immediately before constructing the store, so nothing is pooled ahead of registration on the production path -- but the params API accepts any engine, and the dispatch race applies whenever a store is constructed while the engine serves traffic.
• common/vector_store/sqlite_vec_vector_store.py (~line 474): SQLiteVecVectorStore.__init__ registers extension loading (sqlite_vec.loadable_path()) on its caller-supplied params.engine. Same shape, same latent status; a pre-pooled connection would serve vec queries without the extension.

Suggested fix, mirroring #1545: have the DatabaseManager apply the creation-time registration for both engines (the FK helper already exists; extension loading needs an equivalent registered at the same point), drop the __init__-time listeners on caller-supplied engines, and verify in each store's startup() (PRAGMA foreign_keys for FK; e.g. SELECT vec_version() for the extension), refusing with a directive. Test engine fixtures register the same helpers at creation.

Investigated and written by Claude (Claude Code), filed from the account of the user who commissioned the investigation.