Note: This change was necessary for my multi-clone Dolt federation setup, where AUTO_INCREMENT collisions made bd unusable. I'm happy to maintain this as a personal fork if beads doesn't want to go this direction, but thought it might be useful for anyone else running federated Dolt deployments.

Problem

Dolt's AUTO_INCREMENT counter is per-server-instance with no cross-clone reconciliation. When multiple Dolt clones independently write to the same table and then sync via dolt_push/dolt_pull, INSERT operations fail with:

Error 1062: duplicate primary key given: [4144]

This is documented behavior:

"Dolt branches and clones do not play well with AUTO_INCREMENT primary keys."

Six tables in beads use BIGINT AUTO_INCREMENT PRIMARY KEY and are all vulnerable in any multi-clone deployment.

How it happens

1. Clone A inserts events → gets IDs 4183, 4184, 4185...
2. Sync: dolt_push from Clone A → Clone B (rows arrive on Clone B)
3. Clone B's AUTO_INCREMENT counter does NOT advance past the pushed rows
4. Client writes to Clone B → AUTO_INCREMENT assigns 4183 → COLLISION

Impact

• ALL write operations blocked: bd create, bd update, bd close fail on any node whose counter is behind
• Every sync cycle re-introduces the gap — not a one-time fix
• The only workaround was retrying failed INSERTs ~24 times to advance the counter past the gap, then repeating after every sync

Prior mitigation (insufficient)

beads#2133 added ALTER TABLE <tbl> AUTO_INCREMENT = MAX(id)+1 after every DOLT_PULL (resetAutoIncrements). This works when bd does the pull, but fails when external sync scripts use raw dolt_pull/dolt_push, when other tools write independently, or when two nodes write concurrently. Even within a single server, dolthub/dolt#7702 documents AUTO_INCREMENT race conditions.

Solution

Replace BIGINT AUTO_INCREMENT PRIMARY KEY with CHAR(36) NOT NULL PRIMARY KEY DEFAULT (UUID()) on all six affected tables, following DoltHub's official recommendation for multi-clone scenarios.

Why UUID v7

Where the application generates IDs explicitly (e.g., ImportIssueComment), we use UUID v7 (RFC 9562) — time-sorted UUIDs that preserve chronological ordering (ORDER BY id ≈ creation order).

Tables affected

Migration strategy

Dolt cannot ALTER COLUMN to change a PK's type in place. Migration 010 uses a 4-step pattern for each table:

1. Idempotency check — skip if already char(36) or table doesn't exist
2. Add column — ALTER TABLE ADD COLUMN uuid_id CHAR(36) NOT NULL DEFAULT (UUID())
3. Backfill — UPDATE SET uuid_id = UUID()
4. Drop old PK — MODIFY id BIGINT NOT NULL (remove AUTO_INCREMENT first — Dolt requires this before DROP PRIMARY KEY), DROP PRIMARY KEY, DROP COLUMN id
5. Rename & promote — RENAME COLUMN uuid_id TO id, ADD PRIMARY KEY (id)

Dolt DDL quirk

ALTER TABLE ... DROP PRIMARY KEY fails on Dolt when the column has AUTO_INCREMENT. The workaround is to MODIFY the column first to remove it. This is not documented in Dolt's migration docs.

Breaking changes

1. GetAllEventsSince signature: sinceID int64 → since time.Time (UUIDs aren't sequential, timestamps are the natural cursor)
2. Type changes: Event.ID and Comment.ID changed from int64 to string
3. resetAutoIncrements removed — root cause fixed, band-aid unnecessary
4. Schema version: 6 → 7

Testing

This change passes the existing test suite — test modifications are minimal (signature updates and int64 → string assertions):

• dolt_test.go: comment1.ID == 0 → comment1.ID == "" (string zero-value check)
• tracker_test.go: GetAllEventsSince mock updated to accept time.Time
• Migration is idempotent and was validated against 18 live databases across 3 nodes
• E2E federation test: create on Clone A → sync → write on Clone B → no Error 1062

Scope of this PR and remaining work

This PR is the minimal core change — the schema migration, type updates, and interface changes needed to make UUID primary keys work. It compiles, passes tests, and functions correctly.

However, a complete adoption would benefit from updating additional files that still reference or assume the old AUTO_INCREMENT behavior. I'm pausing here to see if this direction is desired before doing more work. The files below were updated in my full working branch but are not included in this PR to keep the diff reviewable:

Of particular note: issueops/create.go has a comment still referencing "auto-increment PK" (line 278) and PersistComments works by coincidence — it omits the id column from INSERT, so DEFAULT(UUID()) handles it. Correct behavior, but the code isn't aware of the change.

Request for review

This is a significant schema change and we want to make sure we haven't missed anything. We'd especially appreciate a second set of eyes on:

• Any callers or code paths that still assume integer event/comment IDs
• Edge cases in the migration (partial failures, large tables, concurrent access during migration)
• Whether the time.Time high-water mark in GetAllEventsSince handles all the scenarios the old int64 cursor did
• Any other tables or columns we may have overlooked that could hit the same federation collision