Audit follow-ups: hot-path query scans (WAL root cause), semantic backfill O(N²), sender-profile index, + minor correctness

[bhekanik]

Findings from a 2026-07-11 multi-agent audit (base v0.6.5). The high-confidence correctness/security items were fixed directly (PRs #109, #112, #113, #114, #115). The items below are real but were deliberately not auto-fixed — they are either larger perf refactors that touch the write path / need a schema migration on large production DBs (warrants review before merge), or low-severity. Each is ready to execute.

P1 — Inbox/thread list queries full-scan + temp-B-tree sort (the real WAL-growth root cause)

crates/store/src/message.rs:280,661,1038,1067, crates/store/src/thread.rs (list_threads/get_threads_batch/get_thread_envelopes).
Every list query orders by ORDER BY CASE WHEN date > ? THEN 0 ELSE date END DESC, id DESC. The ORDER BY is a bind-parameterized expression, not a column, so idx_messages_account_date cannot serve the sort — SQLite filters by account_id, then materializes and sorts all matching rows in a temp B-tree on every page. On a 108k-row account this is ~full-scan-per-page → 100% CPU, and the long-lived read transaction pins WAL frames so the checkpoint can't truncate → unbounded WAL growth (same incident class as #107, but on the hottest read path; #107's index fix does not cover this).
Fix (needs review — schema migration + write-path change on multi-GB DBs): store a clamped sort_date column at write time (e.g. min(date, ingest_time), 0 for future-dated), index (account_id, sort_date DESC, id DESC), and change the ORDER BY to sort_date DESC, id DESC so the index serves the sort. Keyset (not OFFSET) pagination removes deep-page cost too. Must prove ordering is byte-identical before/after and EXPLAIN uses the index.

P2 — Semantic backfill re-scans the done prefix each batch (~O(N²))

crates/store/src/semantic.rs:261-294 (list_message_ids_missing_semantic_chunks / …_embeddings). … WHERE NOT EXISTS(chunks) ORDER BY m.date DESC LIMIT ? always restarts from newest, so each batch index-probes past the growing done-prefix. First-time embedding of a 108k mailbox ≈ N²/limit probes. Fix: persist a (date,id) high-water cursor and paginate past the last processed row.

P2 — Sender-profile queries wrap the indexed column in LOWER()

crates/store/src/sender_profile.rs:132,155,178,470. WHERE LOWER(m.from_email) = LOWER(?) can't use idx_messages_from/idx_messages_from_date → full per-account scan per profile open. Fix: store from_email normalized (lowercased) or add an expression index LOWER(from_email) and drop the column-side LOWER().

P2 — IMAP CONDSTORE-only servers never detect deletions

crates/provider-imap/src/lib.rs:882-939. The UID-diff delete-detection is gated !qresync_used && !condstore_used; on a CONDSTORE-but-not-QRESYNC server (many Dovecot deployments), CHANGEDSINCE never reports expunged UIDs (VANISHED is QRESYNC-only), so server-side deletions linger locally forever. Fix: also run the UID SEARCH ALL diff when condstore_used (gate on !qresync_used alone).

P2 — TUI compose: abnormal editor exit is treated as discard (data loss)

crates/tui/src/compose_flow.rs handle_compose_editor_status (~463-466). Any non-success editor exit deletes the temp draft; a signal-killed/crashed editor (terminal close, OOM) is indistinguishable from a deliberate :cq, so an unsaved new composition is destroyed with no recovery. Fix: only delete on a normal non-zero exit code (status.code().is_some()); on signal termination (code().is_none()) preserve the file + schedule recovery, as the launch-failure branch already does. (The edit-in-place path in #110 already preserves content on failure; this is the new-compose path.)

P3 — extract_raw_header_block dead \n\n fallback

crates/mail-parse/src/lib.rs:542-549. raw.split("\r\n\r\n").next() always returns Some, so the \n\n fallback is unreachable; an LF-only message returns the whole body as raw_headers (pollution, not a crash). Fix: raw.find("\r\n\r\n").or_else(|| raw.find("\n\n")).

P3 — Message-ID dedup vs search normalization mismatch (latent)

Dedup does raw = on message_id_header; search normalizes (trim, strip <>, lowercase — crates/search/src/index.rs:47). Dedup can miss a duplicate search considers identical. Higher blast radius (changes dedup identity; needs the index on the normalized form + data backfill) — evaluate carefully.

---

Rejected during audit (not bugs): thread queries "cross-account leak" — thread_id is already account-namespaced (Gmail v5 UUID bakes account_id; IMAP/Outlook random UUID), so no cross-account collision is possible.