guettli/sharedinbox
1
0
Fork 0
Code Issues 21 Pull Requests 6 Actions Packages Projects Releases Wiki Activity

Fix email ID collision: include mailboxPath in local ID #502

New Issue
Closed
opened 2026-06-06 18:15:33 +00:00 by guettlibot · 1 comment
guettlibot commented 2026-06-06 18:15:33 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

Problem

The local email ID is constructed as '$accountId:$uid' (sync: line 618, search: line 3085 of email_repository_impl.dart). IMAP UIDs are mailbox-scoped — UID 50 in INBOX and UID 50 in Archive are different emails, yet both get the same local ID. insertOnConflictUpdate silently overwrites whichever was stored first, corrupting the local DB. A force-full-sync cannot fix this because it repeats the same collision.

Plan

  1. Change the ID scheme to '$accountId:$mailboxPath:$uid' everywhere an ID is constructed from an IMAP UID:

    • _fetchAndUpsertImap (line 618)
    • _refreshFlagsImap (line 563)
    • searchEmails (line 3085) — moot if #501 (offline search) is implemented first; remove this call entirely.
    • Any other site that builds accountId:uid — grep for '$accountId:$' pattern.

  2. Write a schema migration (Drift MigrationStrategy):

    • Rename or clear the emails table rows whose id does not contain a mailbox segment.
    • Re-derive correct IDs from the existing (account_id, mailbox_path, uid) columns already stored on each row.
    • Update email_bodies.email_id foreign key to match.
    • Bump the database schema version.

  3. Update all code that constructs or parses an email ID by raw string manipulation (e.g. emailId.substring(emailId.indexOf(':') + 1) in the JMAP body path, line 333–334).

  4. Add a unit test that inserts two emails with the same UID in different mailboxes and asserts both are retrievable independently.

  5. Verify that after the migration existing data is intact and getEmail returns the correct record for each mailbox.

Note

If issue #501 (offline-first search) is implemented first, step 1 for searchEmails is already covered — the IMAP live search that constructed IDs from server UIDs will be gone. The remaining ID fix is still needed for sync.

## Problem The local email ID is constructed as `'$accountId:$uid'` (sync: line 618, search: line 3085 of `email_repository_impl.dart`). IMAP UIDs are mailbox-scoped — UID 50 in INBOX and UID 50 in Archive are different emails, yet both get the same local ID. `insertOnConflictUpdate` silently overwrites whichever was stored first, corrupting the local DB. A force-full-sync cannot fix this because it repeats the same collision. ## Plan 1. **Change the ID scheme** to `'$accountId:$mailboxPath:$uid'` everywhere an ID is constructed from an IMAP UID: - `_fetchAndUpsertImap` (line 618) - `_refreshFlagsImap` (line 563) - `searchEmails` (line 3085) — moot if #501 (offline search) is implemented first; remove this call entirely. - Any other site that builds `accountId:uid` — grep for `'$accountId:$' ` pattern. 2. **Write a schema migration** (Drift `MigrationStrategy`): - Rename or clear the `emails` table rows whose `id` does not contain a mailbox segment. - Re-derive correct IDs from the existing `(account_id, mailbox_path, uid)` columns already stored on each row. - Update `email_bodies.email_id` foreign key to match. - Bump the database schema version. 3. **Update all code that constructs or parses an email ID** by raw string manipulation (e.g. `emailId.substring(emailId.indexOf(':') + 1)` in the JMAP body path, line 333–334). 4. **Add a unit test** that inserts two emails with the same UID in different mailboxes and asserts both are retrievable independently. 5. **Verify** that after the migration existing data is intact and `getEmail` returns the correct record for each mailbox. ## Note If issue #501 (offline-first search) is implemented first, step 1 for `searchEmails` is already covered — the IMAP live search that constructed IDs from server UIDs will be gone. The remaining ID fix is still needed for sync.
guettlibot commented 2026-06-06 22:00:11 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

PR created: https://codeberg.org/guettli/sharedinbox/pulls/511

Here's a summary of what was done to fix issue #502:

Root cause: IMAP UIDs are mailbox-scoped, but the local email ID was accountId:uid. This meant UID 50 in INBOX and UID 50 in Archive generated the same ID, causing insertOnConflictUpdate to silently corrupt whichever email was stored first.

Changes made:

  1. lib/data/repositories/email_repository_impl.dart (lines 563, 618): Changed email ID construction from '${account.id}:$uid' to '${account.id}:$mailboxPath:$uid' in both _refreshFlagsImap and _fetchAndUpsertImap.

  2. lib/core/db_schema_version.dart: Bumped schema version 40 → 41.

  3. lib/data/db/database.dart: Added migration v41 that:

    • Uses PRAGMA defer_foreign_keys = ON to safely update both email_bodies.email_id (FK) and emails.id (PK) within the same transaction
    • Patches emails.thread_id where it was set to the email's own ID (fallback for messages without a Message-ID header)
    • Deletes and rebuilds threads for IMAP accounts from the corrected email rows

  4. test/unit/migration_test.dart: Added a v40→v41 migration test that verifies IDs, email_bodies, thread_ids, and threads are all correctly updated.

  5. test/unit/email_repository_impl_test.dart: Added a unit test asserting that two emails with the same UID in different mailboxes get distinct IDs and are independently retrievable.

All 338 unit tests pass.

PR created: https://codeberg.org/guettli/sharedinbox/pulls/511 --- Here's a summary of what was done to fix issue #502: **Root cause**: IMAP UIDs are mailbox-scoped, but the local email ID was `accountId:uid`. This meant UID 50 in INBOX and UID 50 in Archive generated the same ID, causing `insertOnConflictUpdate` to silently corrupt whichever email was stored first. **Changes made**: 1. **`lib/data/repositories/email_repository_impl.dart`** (lines 563, 618): Changed email ID construction from `'${account.id}:$uid'` to `'${account.id}:$mailboxPath:$uid'` in both `_refreshFlagsImap` and `_fetchAndUpsertImap`. 2. **`lib/core/db_schema_version.dart`**: Bumped schema version 40 → 41. 3. **`lib/data/db/database.dart`**: Added migration v41 that: - Uses `PRAGMA defer_foreign_keys = ON` to safely update both `email_bodies.email_id` (FK) and `emails.id` (PK) within the same transaction - Patches `emails.thread_id` where it was set to the email's own ID (fallback for messages without a Message-ID header) - Deletes and rebuilds threads for IMAP accounts from the corrected email rows 4. **`test/unit/migration_test.dart`**: Added a `v40→v41` migration test that verifies IDs, email_bodies, thread_ids, and threads are all correctly updated. 5. **`test/unit/email_repository_impl_test.dart`**: Added a unit test asserting that two emails with the same UID in different mailboxes get distinct IDs and are independently retrievable. All 338 unit tests pass.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
NeedSupervisor
Issue escalated to a human supervisor; agentloop will skip it until cleared.
 State/InProgress
State/Later
State/Planned
automerge
Eligible for automatic merge by CI
 ci-failure
Issue opened by agentloop to track a failing CI workflow; used for deduplication.
 do-not-merge
Plan PR — review only, do not merge.
 loop/code
Add to run the built-in "code" prompt; override at prompts/code.md.
 loop/code-ci-pending
Prompt "code" finished; waiting for the PR's CI to pass before advancing.
 loop/code-done
Prompt "code" finished successfully.
 loop/code-in-process
Agent for the "code" prompt is currently running on this issue.
 loop/merge
Managed by agentloop
 loop/merge-done
Managed by agentloop
 loop/merge-in-process
Managed by agentloop
 loop/plan
Add to run the built-in "plan" prompt; override at prompts/plan.md.
 loop/plan-done
Prompt "plan" finished successfully.
 loop/plan-in-process
Agent for the "plan" prompt is currently running on this issue.
No labels loop/merge
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
guettlibot guettli
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: guettli/sharedinbox#502