Summary
EnsureColumn() (line 905-928) checks if a column exists via PRAGMA table_info(), then issues ALTER TABLE ADD COLUMN if missing. A second process checking between the PRAGMA and ALTER will both see the column as missing and attempt simultaneous ALTER operations, causing silent failure or schema corruption on some SQLite versions. The TOCTOU (time-of-check–time-of-use) window exists even though a "duplicate column" exception handler is in place—the handler assumes a single missed detection, not concurrent updates.
Where
src/CodeIndex/Database/DbContext.cs:905src/CodeIndex/Database/DbContext.cs:919
Suggested approach
- Wrap EnsureColumn in an exclusive lock (BEGIN IMMEDIATE or similar serialized mode) per table to prevent concurrent ALTER attempts
- Re-check column existence after acquiring the lock and before executing ALTER, accounting for a sibling process having already done the work
- Catch SQLITE_LOCKED and SQLITE_BUSY on ALTER, then retry the existence check after backoff (in case the other process just completed)
- Move EnsureColumn calls into a dedicated schema-migration transaction with SERIALIZABLE isolation if SQLite supports it
- Add a test case simulating concurrent DbContext construction on legacy DBs to verify no race corruption
- Log every EnsureColumn attempt (column name, action taken) at DEBUG level to trace migration in production
Summary
EnsureColumn() (line 905-928) checks if a column exists via
PRAGMA table_info(), then issuesALTER TABLE ADD COLUMNif missing. A second process checking between the PRAGMA and ALTER will both see the column as missing and attempt simultaneous ALTER operations, causing silent failure or schema corruption on some SQLite versions. The TOCTOU (time-of-check–time-of-use) window exists even though a "duplicate column" exception handler is in place—the handler assumes a single missed detection, not concurrent updates.Where
src/CodeIndex/Database/DbContext.cs:905src/CodeIndex/Database/DbContext.cs:919Suggested approach