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: When a tool starts work it calls with a title and its own .
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 . 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 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 was.
Fix attempt one, which does not work The obvious move: This is wrong, and it is wrong in a way that survives a casual test. is nullable.
In SQL, is never equal to , and that includes comparisons inside a unique index.
The constraint stops constraining the moment the column is NULL: 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 rows: sessions created through the library API without a tool set.
The fix Index an expression instead of the raw column: NULL collapses to the empty string, which does compare equal to itself: 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: 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 .
The inline on is part of the table definition, so removing it means rebuilding the table: 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 before the migration check.
On an existing database that still carries the legacy inline , 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 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: 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: What I took from this A UNIQUE constraint is an identity claim. 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 if you want to look at the code, or run to point it at your own AI sessions.