My AI audit tool was merging Claude and Cursor sessions. The bug was one UNIQUE constraint.

A developer fixed a bug in Chron, an MCP server that logs AI coding sessions, where Claude and Cursor sessions were incorrectly merged due to a UNIQUE constraint on the session title. The issue was that the title alone was not unique across tools, and the fix involved using a composite unique index on (title, COALESCE(ai_tool, '')) to handle NULL values correctly. The developer also updated the lookup query to filter by both title and tool.

I maintain Chron https://www.npmjs.com/package/chron-mcp , 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 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. 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. 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: js // 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. 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. 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: js 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 ; 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.