Mail: store every UID when one folder holds duplicate copies of a message (proper fix after the sync hotfix)

kayg commented

2026-10-01 10:52:12 +00:00

Owner

Found on production (2026-10-01): duplicate messages within one IMAP folder

After the #613 sync fix, the Fastmail sync stored 1,710 messages, then every run failed with UNIQUE constraint failed: mail_memberships.folder_id, mail_memberships.generation, mail_memberships.message_id. Two different UIDs in one folder resolve to the same internal message (identical Message-ID/content, e.g. duplicated copies).
Hotfix on dev: the insert keeps the first UID (ON CONFLICT(folder_id, generation, message_id) DO NOTHING). The extra UID is not tracked, so a flag/move/delete on that copy, or its later expunge, is not reflected.
Proper fix: the membership key is (folder, generation, uid). Drop UNIQUE(folder_id, generation, message_id) (SQLite table rebuild migration). Let several UIDs in one folder point at one message row, and show them as one message in the UI (de-duplicated by message) with all UIDs kept for writes (flag/move/delete apply to every UID of that message in that folder). Add tests: two UIDs, same message; a flag change on one; expunge of one; a move. Mail crate gates.

## Found on production (2026-10-01): duplicate messages within one IMAP folder After the #613 sync fix, the Fastmail sync stored 1,710 messages, then every run failed with `UNIQUE constraint failed: mail_memberships.folder_id, mail_memberships.generation, mail_memberships.message_id`. Two different UIDs in one folder resolve to the same internal message (identical Message-ID/content, e.g. duplicated copies). **Hotfix on dev:** the insert keeps the first UID (`ON CONFLICT(folder_id, generation, message_id) DO NOTHING`). The extra UID is not tracked, so a flag/move/delete on that copy, or its later expunge, is not reflected. **Proper fix:** the membership key is (folder, generation, uid). Drop `UNIQUE(folder_id, generation, message_id)` (SQLite table rebuild migration). Let several UIDs in one folder point at one message row, and show them as one message in the UI (de-duplicated by message) with all UIDs kept for writes (flag/move/delete apply to every UID of that message in that folder). Add tests: two UIDs, same message; a flag change on one; expunge of one; a move. Mail crate gates.

kayg commented

2026-10-01 10:53:04 +00:00

Author

Owner

Starting #626 on job/maildup-626, based on 6527b9ef9b5a9a1ecbceedc0a01b7fd36f8c5961 (origin/dev). I am tracing the current schema, list projections, and action writes before changing membership identity to folder + generation + UID.

Starting #626 on `job/maildup-626`, based on `6527b9ef9b5a9a1ecbceedc0a01b7fd36f8c5961` (`origin/dev`). I am tracing the current schema, list projections, and action writes before changing membership identity to folder + generation + UID.

kayg commented

2026-10-01 10:58:27 +00:00

Author

Owner

Finding: the primary key already uses (folder_id, generation, uid), but mail_memberships also has UNIQUE(folder_id, generation, message_id), and store_window silently drops that conflict. list_messages and folder counters count membership rows, while set_message_flags and /read-state target only one UID. Sync detects a short generation but leaves expunged UIDs in the projection. I am covering the existing provider write path and generation reconciliation; there are no Move or Delete API routes in this Mail crate yet.

Finding: the primary key already uses `(folder_id, generation, uid)`, but `mail_memberships` also has `UNIQUE(folder_id, generation, message_id)`, and `store_window` silently drops that conflict. `list_messages` and folder counters count membership rows, while `set_message_flags` and `/read-state` target only one UID. Sync detects a short generation but leaves expunged UIDs in the projection. I am covering the existing provider write path and generation reconciliation; there are no Move or Delete API routes in this Mail crate yet.

kayg referenced this issue from a commit

2026-10-01 11:22:27 +00:00

Mail sync: tolerate duplicate messages in one folder

kayg commented

2026-10-01 11:39:25 +00:00

Author

Owner

Implementation decisions for #626: folder rows and unread badges count distinct message IDs; if duplicate UIDs disagree on \\Seen, the visible message stays unread until all copies are read. The mark-read route writes the requested flag to every UID in the existing preferred folder and preserves other flags per copy. Sync runs UID SEARCH ALL only when cached membership count exceeds EXISTS; it validates the result count and UID bounds before pruning. Mail has no Move or Delete route yet, so the message record now exposes the full UID set for those future writes; the regression test covers moving one copy between folder projections and expunging its old UID.

Implementation decisions for #626: folder rows and unread badges count distinct message IDs; if duplicate UIDs disagree on `\\Seen`, the visible message stays unread until all copies are read. The mark-read route writes the requested flag to every UID in the existing preferred folder and preserves other flags per copy. Sync runs `UID SEARCH ALL` only when cached membership count exceeds `EXISTS`; it validates the result count and UID bounds before pruning. Mail has no Move or Delete route yet, so the message record now exposes the full UID set for those future writes; the regression test covers moving one copy between folder projections and expunging its old UID.

kayg commented

2026-10-01 12:22:31 +00:00

Author

Owner

Completed

Implemented the #626 duplicate UID fix. Migration 0009 now keys a membership by (folder_id, generation, uid) and allows several UIDs to reference one message row. Mail pages, unified Inbox, thread views, and folder counts show one row/count per message. Message reads retain every UID in the selected folder, and the existing read-state action changes Seen on all of them while preserving each copy's other flags. When local membership count exceeds IMAP EXISTS, sync validates UID SEARCH ALL before pruning expunged memberships and orphaned message rows.

Tests cover migration preservation, duplicate UIDs and deduplicated pages/counts, flag updates, a simulated move plus expunge, full sync expunge reconciliation, and the IMAP UID STORE command. The local real-server Mail API probe also passed hostile-ID, malformed/oversized request, cross-user, and 24-way parallel-read checks.

Files

crates/plugins/mail/migrations/0009_duplicate_uid_memberships.sql
crates/plugins/mail/src/cache.rs
crates/plugins/mail/src/cache/store.rs
crates/plugins/mail/src/imap.rs
crates/plugins/mail/src/routes.rs
crates/plugins/mail/src/sync.rs
bench/mail-sync.py
docs/perf/2026-10-01-maildup-626.md

Gate output (verbatim)

cargo fmt --all -- --check exited 0 with no output.

cargo clippy -p calternal-plugin-mail --all-targets -- -D warnings:

Finished `dev` profile [unoptimized + debuginfo] target(s) in 6.84s

cargo test -p calternal-plugin-mail:

running 46 tests
test result: ok. 44 passed; 0 failed; 2 ignored; 0 measured; 0 filtered out; finished in 1.83s
Doc-tests calternal_plugin_mail
running 0 tests
test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

cargo clippy -p calternal-server --all-targets -- -D warnings:

Finished `dev` profile [unoptimized + debuginfo] target(s) in 57.89s

cargo test -p calternal-server:

Finished `test` profile [unoptimized + debuginfo] target(s) in 7m 26s
running 110 tests
test result: ok. 107 passed; 0 failed; 3 ignored; 0 measured; 0 filtered out; finished in 8.84s

Adversarial probe output:

Mail API probe: remote-content isolation, hostile IDs, cross-User message/thread/attachment isolation, safe attachment names and 24 parallel account/Inbox reads passed

Performance

The local 2,000-UID profile (one duplicate pair; 1,999 message rows) measured 3 serial runs at mean sync 2.92 s, page p50 27.87 ms, p95 51.06 ms, mean RSS 19.47 MiB, peak 25.42 MiB. The 3-worker burst averaged 5.98 s per worker, p50 36.90 ms, p95 70.31 ms, mean RSS 19.11 MiB, peak 24.86 MiB. The perf VM lock was unavailable; local load was high. The 100k synthetic run was stopped after its first sample exceeded six minutes. docs/perf/baseline.json has no comparable sync profile.

Decisions and known gaps

Deduplicate by internal message identity for UI rows and counts. If any duplicate UID is unread, show the message as unread and select an unread UID as representative.
Apply the current read-state action to all UIDs in the selected folder. A flag change preserves other per-copy flags.
Run full UID SEARCH only when cached memberships exceed provider EXISTS; validate the full set before deleting local rows.
This Mail crate has no Move or Delete API route. The move/expunge test models provider folder membership changes in the store; the retained UID vector is available for future provider writes.

Feature commit: 9409558a9. Final HEAD: 98b627f45 (includes one merge of fetched origin/dev at 8a506d507). The shared local origin/dev tracking ref later advanced to 3f258302a, an MCP-only server change; I did not repeat the merge. Worktree is clean. cargo clean removed 15,535 files / 7.9 GiB; generated web build output and installed dependencies were removed.

## Completed Implemented the #626 duplicate UID fix. Migration 0009 now keys a membership by `(folder_id, generation, uid)` and allows several UIDs to reference one message row. Mail pages, unified Inbox, thread views, and folder counts show one row/count per message. Message reads retain every UID in the selected folder, and the existing read-state action changes Seen on all of them while preserving each copy's other flags. When local membership count exceeds IMAP EXISTS, sync validates UID SEARCH ALL before pruning expunged memberships and orphaned message rows. Tests cover migration preservation, duplicate UIDs and deduplicated pages/counts, flag updates, a simulated move plus expunge, full sync expunge reconciliation, and the IMAP UID STORE command. The local real-server Mail API probe also passed hostile-ID, malformed/oversized request, cross-user, and 24-way parallel-read checks. ## Files - `crates/plugins/mail/migrations/0009_duplicate_uid_memberships.sql` - `crates/plugins/mail/src/cache.rs` - `crates/plugins/mail/src/cache/store.rs` - `crates/plugins/mail/src/imap.rs` - `crates/plugins/mail/src/routes.rs` - `crates/plugins/mail/src/sync.rs` - `bench/mail-sync.py` - `docs/perf/2026-10-01-maildup-626.md` ## Gate output (verbatim) `cargo fmt --all -- --check` exited 0 with no output. `cargo clippy -p calternal-plugin-mail --all-targets -- -D warnings`: ```text Finished `dev` profile [unoptimized + debuginfo] target(s) in 6.84s ``` `cargo test -p calternal-plugin-mail`: ```text running 46 tests test result: ok. 44 passed; 0 failed; 2 ignored; 0 measured; 0 filtered out; finished in 1.83s Doc-tests calternal_plugin_mail running 0 tests test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s ``` `cargo clippy -p calternal-server --all-targets -- -D warnings`: ```text Finished `dev` profile [unoptimized + debuginfo] target(s) in 57.89s ``` `cargo test -p calternal-server`: ```text Finished `test` profile [unoptimized + debuginfo] target(s) in 7m 26s running 110 tests test result: ok. 107 passed; 0 failed; 3 ignored; 0 measured; 0 filtered out; finished in 8.84s ``` Adversarial probe output: ```text Mail API probe: remote-content isolation, hostile IDs, cross-User message/thread/attachment isolation, safe attachment names and 24 parallel account/Inbox reads passed ``` ## Performance The local 2,000-UID profile (one duplicate pair; 1,999 message rows) measured 3 serial runs at mean sync 2.92 s, page p50 27.87 ms, p95 51.06 ms, mean RSS 19.47 MiB, peak 25.42 MiB. The 3-worker burst averaged 5.98 s per worker, p50 36.90 ms, p95 70.31 ms, mean RSS 19.11 MiB, peak 24.86 MiB. The perf VM lock was unavailable; local load was high. The 100k synthetic run was stopped after its first sample exceeded six minutes. `docs/perf/baseline.json` has no comparable sync profile. ## Decisions and known gaps - Deduplicate by internal message identity for UI rows and counts. If any duplicate UID is unread, show the message as unread and select an unread UID as representative. - Apply the current read-state action to all UIDs in the selected folder. A flag change preserves other per-copy flags. - Run full UID SEARCH only when cached memberships exceed provider EXISTS; validate the full set before deleting local rows. - This Mail crate has no Move or Delete API route. The move/expunge test models provider folder membership changes in the store; the retained UID vector is available for future provider writes. Feature commit: `9409558a9`. Final HEAD: `98b627f45` (includes one merge of fetched `origin/dev` at `8a506d507`). The shared local `origin/dev` tracking ref later advanced to `3f258302a`, an MCP-only server change; I did not repeat the merge. Worktree is clean. `cargo clean` removed 15,535 files / 7.9 GiB; generated web build output and installed dependencies were removed.

kayg referenced this issue

2026-10-01 13:59:20 +00:00

Log attachments: deleted file still shown, attachments not live, batch send not live, Log order, raw camera names #427

kayg referenced this issue from a commit

2026-10-01 15:51:20 +00:00

Mail sync: a short folder count no longer fails the whole window

kayg referenced this issue

2026-10-01 17:49:50 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 18:07:02 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 18:16:13 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 18:28:59 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 18:32:31 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 18:46:07 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 19:50:30 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 20:57:34 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 21:21:23 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 21:32:14 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 21:40:55 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 22:42:21 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-01 23:20:19 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-02 00:03:59 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-02 00:25:27 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-02 01:28:43 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-02 02:01:06 +00:00

BETTER SETTINGS: organise Settings so it feels non-overwhelming and close to the User (grill pending); includes Connected Accounts #407

kayg referenced this issue

2026-10-02 05:36:15 +00:00

PERF: Mail adopts shared revision, snapshot, mutation and delta contracts (#663) #672

kayg referenced this issue

2026-10-02 05:36:34 +00:00

PERF: Mail rule 1 — serve interactive reads from committed precomputed projections (#663) #684

kayg referenced this issue

2026-10-02 06:25:38 +00:00

Journal read right after a Log write can miss the new entry under load (read-your-writes with #549 snapshots) #653

kayg referenced this issue

2026-10-02 10:40:15 +00:00

Log attachments: deleted file still shown, attachments not live, batch send not live, Log order, raw camera names #427

kayg referenced this issue

2026-10-02 12:54:26 +00:00

Log attachments: deleted file still shown, attachments not live, batch send not live, Log order, raw camera names #427

kayg referenced this issue

2026-10-02 13:09:27 +00:00

PERF: bound Mail delta and expunge work per sync job (#663) #757

kayg referenced this issue

2026-10-02 13:19:07 +00:00

PERF: make Mail folder page SQL work proportional to its window (#663) #825

kayg commented

2026-10-03 12:48:28 +00:00

Author

Owner

Fixed in 73ce7171f (origin/dev); covered by Mail duplicate-UID regression tests and the real-server mail_api.mjs hostile-ID, cross-User, and parallel-read probe.

Fixed in 73ce7171f (origin/dev); covered by Mail duplicate-UID regression tests and the real-server mail_api.mjs hostile-ID, cross-User, and parallel-read probe.

kayg closed this issue

2026-10-03 12:48:28 +00:00

kayg referenced this issue

2026-10-03 12:51:31 +00:00

Issue hygiene sweep 2026-10-03 #1008

kayg referenced this issue

2026-10-03 14:25:06 +00:00

Server Mail migration review tests assume version 9 is latest #1010