fix: include mailboxPath in IMAP email ID to prevent UID collisions #511

2026-06-06T21:57:19Z

guettlibot commented

2026-06-06 21:57:19 +00:00

(Migrated from codeberg.org)

Summary

Changes IMAP email ID format from accountId:uid to accountId:mailboxPath:uid in _refreshFlagsImap and _fetchAndUpsertImap
Adds schema migration v41 that re-derives IDs for existing IMAP rows, updates email_bodies.email_id FK, patches thread_id fields that pointed at the old email ID, and rebuilds threads for IMAP accounts
Bumps dbSchemaVersion from 40 to 41

Root cause

IMAP UIDs are mailbox-scoped: UID 50 in INBOX and UID 50 in Archive are different emails. The old composite key accountId:uid caused insertOnConflictUpdate to silently overwrite whichever arrived first, corrupting the local DB in ways a force-full-sync could not fix.

Migration safety

The migration uses PRAGMA defer_foreign_keys = ON so both email_bodies.email_id and emails.id can be updated sequentially within the same migration transaction without a transient FK violation. Threads are deleted and rebuilt from the corrected email rows.

Test plan

New v40→v41 migration test verifies IDs, email_bodies, thread_ids, and threads are updated correctly
New unit test verifies same-UID emails in different mailboxes get independent IDs and are both retrievable
Full unit suite: all 338 tests pass

Closes #502

## Summary - Changes IMAP email ID format from `accountId:uid` to `accountId:mailboxPath:uid` in `_refreshFlagsImap` and `_fetchAndUpsertImap` - Adds schema migration v41 that re-derives IDs for existing IMAP rows, updates `email_bodies.email_id` FK, patches `thread_id` fields that pointed at the old email ID, and rebuilds threads for IMAP accounts - Bumps `dbSchemaVersion` from 40 to 41 ## Root cause IMAP UIDs are mailbox-scoped: UID 50 in INBOX and UID 50 in Archive are different emails. The old composite key `accountId:uid` caused `insertOnConflictUpdate` to silently overwrite whichever arrived first, corrupting the local DB in ways a force-full-sync could not fix. ## Migration safety The migration uses `PRAGMA defer_foreign_keys = ON` so both `email_bodies.email_id` and `emails.id` can be updated sequentially within the same migration transaction without a transient FK violation. Threads are deleted and rebuilt from the corrected email rows. ## Test plan - New `v40→v41` migration test verifies IDs, email_bodies, thread_ids, and threads are updated correctly - New unit test verifies same-UID emails in different mailboxes get independent IDs and are both retrievable - Full unit suite: all 338 tests pass Closes #502

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: guettli/sharedinbox#511