I maintain Chron, an MCP server that writes an audit log of AI coding sessions to a local SQLite database. Every message gets a timestamp, and every session records which tool produced it: Claude, Cursor, Codex. A while back I opened the history and found Claude messages and Cursor messages interleaved inside a single session. Two different tools, two different terminals, one session record. For a tool whose entire job is attribution, that is about as bad as a bug gets. The setup The sessions table looked like this: CREATE TABLE sessions ( id TEXT PRIMARY KEY, title TEXT NOT NULL UNIQUE, ai_tool TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); When a tool starts work it calls init_session with a title and its own ai_tool. If a session with that title already exists, resume it. Otherwise create a new one. Both Claude and Cursor generate a short descriptive title from the task. Working in the same repo on the same task, they generate the same title. Something like General assistance session. UNIQUE(title) then guarantees only one row can exist for that title. So the second tool does not get its own session. It resumes the first one. Claude's messages and Cursor's messages land under the same session id, and ai_tool on that session reports whichever tool happened to start first. The constraint was doing exactly what it was written to do. It was just the wrong constraint. A title was never the identity of a session. The pair (title, tool) was. Fix attempt one, which does not work The obvious move: CREATE UNIQUE INDEX idx_sessions_title_tool ON sessions(title, ai_tool); This is wrong, and it is wrong in a way that survives a casual test. ai_tool is nullable. In SQL, NULL is never equal to NULL, and that includes comparisons inside a unique index. The constraint stops constraining the moment the column is NULL: CREATE TABLE a (title TEXT NOT NULL, ai_tool TEXT); CREATE UNIQUE INDEX ia ON a(title, ai_tool); INSERT INTO a VALUES ('Session', NULL); INSERT INTO a VALUES ('Session', NULL); -- succeeds SELECT count(*) FROM a; -- 2 Two identical rows under a unique index. This is standard SQL behaviour rather than a SQLite quirk, but it is easy to forget the moment you add a nullable column to a composite key. It mattered here because Chron has legitimate NULL ai_tool rows: sessions created through the library API without a tool set. The fix Index an expression instead of the raw column: CREATE UNIQUE INDEX idx_sessions_title_tool ON sessions(title, COALESCE(ai_tool, '')); NULL collapses to the empty string, which does compare equal to itself: CREATE TABLE b (title TEXT NOT NULL, ai_tool TEXT); CREATE UNIQUE INDEX ib ON b(title, COALESCE(ai_tool, '')); INSERT INTO b VALUES ('Session', NULL); INSERT INTO b VALUES ('Session', NULL); -- UNIQUE constraint failed INSERT INTO b VALUES ('Session', 'claude'); -- ok INSERT INTO b VALUES ('Session', 'cursor'); -- ok One row per tool, duplicates within a tool still rejected. That is the property I actually wanted. The lookup code had to match, of course. Resume was searching by title alone, so even with the right constraint it would have kept finding the other tool's row: // before const existing = await db.select().from(sessions) .where(eq(sessions.title, args.title)); // after const existing = await db.select().from(sessions) .where(and( eq(sessions.title, args.title), requestedTool === null ? isNull(sessions.ai_tool) : eq(sessions.ai_tool, requestedTool), )); A constraint and the query that relies on it are one unit. Changing only one of them just moves the bug. Dropping a constraint in SQLite SQLite has no ALTER TABLE ... DROP CONSTRAINT. The inline UNIQUE on title is part of the table definition, so removing it means rebuilding the table: await client.execute('PRAGMA foreign_keys = OFF'); await client.execute('BEGIN'); try { await client.execute(`CREATE TABLE sessions_migration ( id TEXT PRIMARY KEY, title TEXT NOT NULL, -- no UNIQUE ai_tool TEXT, ... )`); await client.execute( 'INSERT INTO sessions_migration SELECT id,title,ai_tool,... FROM sessions' ); await client.execute('DROP TABLE sessions'); await client.execute('ALTER TABLE sessions_migration RENAME TO sessions'); await client.execute('COMMIT'); } catch (e) { try { await client.execute('ROLLBACK'); } catch { /* ignore */ } throw e; } finally { await client.execute('PRAGMA foreign_keys = ON'); } Standard shadow-table dance. Nothing surprising. The migration bug that was worse than the original bug Here is the part worth the post. I first wrote the migration guard as: if the composite index does not exist, migrate. It reads perfectly sensibly, and it fails on exactly the databases that need it. The schema bootstrap runs CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_title_tool ... before the migration check. On an existing database that still carries the legacy inline UNIQUE(title), that statement succeeds. It creates the composite index on the old table. The old constraint is still sitting there, untouched. So by the time the migration check runs, the index exists, the guard concludes "already migrated", and it skips. The table keeps UNIQUE(title) forever. Fresh databases were fine, because they were created correctly from the start. The test suite passed, because tests build fresh databases. Every real user upgrading from an older version would have kept the bug. The guard has to ask about the constraint, not about a side effect that usually correlates with it. The only way to see the constraint is the stored DDL: const info = await client.execute( "SELECT sql FROM sqlite_master WHERE type='table' AND name='sessions'" ); const tableSql = String(info.rows[0]?.sql ?? ''); const hasLegacyTitleUnique = /\btitle\s+TEXT\s+NOT\s+NULL\s+UNIQUE\b/i.test(tableSql); if (hasLegacyTitleUnique || compositeIdxMissing) { // rebuild } Regex against DDL is not elegant. It is, however, the thing that is actually true. And the test has to start from the old schema, not a fresh one: // build a legacy database on purpose await client.execute(`CREATE TABLE sessions ( id TEXT PRIMARY KEY, title TEXT NOT NULL UNIQUE, -- the old constraint ai_tool TEXT, ... )`); await client.execute(`INSERT INTO sessions VALUES ('old-claude', 'General assistance session', 'claude', ...)`); await initDb(dbPath); const table = await client.execute( "SELECT sql FROM sqlite_master WHERE type='table' AND name='sessions'" ); expect(String(table.rows[0].sql)).not.toMatch(/\btitle\s+TEXT\s+NOT\s+NULL\s+UNIQUE\b/i); // cursor can now hold the same title await client.execute(`INSERT INTO sessions VALUES ('new-cursor', 'General assistance session', 'cursor', ...)`); // but a duplicate within the same tool is still rejected await expect(client.execute( `INSERT INTO sessions VALUES ('dupe-claude', 'General assistance session', 'claude', ...)` )).rejects.toThrow(); What I took from this A UNIQUE constraint is an identity claim. UNIQUE(title) asserted "a title identifies a session". That was never true, and the database enforced the false claim faithfully until the day two tools showed up. Nullable columns in composite unique indexes usually do not do what you want. If the column can be NULL, index an expression. Migration guards should test for the thing you are fixing. Not for a marker that normally accompanies it. Ordering inside your own bootstrap can invalidate the marker. A migration test that starts from a fresh schema tests nothing. Construct the old schema, put a row in it, then migrate. This is the one that nearly got me: the fix was correct, the suite was green, and real upgrades would have stayed broken. That last point is the general shape of the lesson. Green tests told me the bug was fixed. They were testing a database that never had the bug. Chron is on npm as chron-mcp if you want to look at the code, or run npx chron-mcp to point it at your own AI sessions.