Approach A from the #227 examination (recommended). Restores Unicode-aware case-insensitive substring search — identical semantics to pre-#223, minus the ASCII-only LIKE limitation.
Implementation
Schema v18→v19: add a searchText column to messages (MessageEntity), populated at write time in the entity mapper as lowercase() of the searchable fields concatenated (sender + senderEmail + subject + snippet). Kotlin String.lowercase() is Unicode-aware and locale-independent, so it casefolds beyond ASCII.
Migration: add the column + backfill existing rows. SQL lower() is ASCII-only, so either (a) backfill via SQL lower() and let non-ASCII rows self-heal on their next sync/rewrite, or (b) backfill in Kotlin for immediate correctness. Export the schema and add a MigrationTest v18→v19 case.
Queries: replace the four-way LIKE in MessageDao.pagingUnifiedFolderSearchSummaries / pagingFolderSearchSummaries with a single WHERE searchText LIKE :pattern; the repo builds pattern from the lowercased query via the existing likePattern (keep \ % _ escaping + ESCAPE '\').
Keep inInbox unfiltered for search (still surfaces transient server-search hits), and keep the paged window loading + load-state-gated empty state from #223.
Tests
MailRepositoryImplTest: a Unicode case-insensitive match (e.g. query "ÄPFEL" matches stored "äpfel"), and the existing LIKE-escape assertion still holds against the lowercased pattern.
MigrationTest: v18→v19 adds the column and preserves rows.
Notes
Perf profile is unchanged (leading-% scan, folder-scoped + paged) — a correctness fix, not a perf regression; one comparison instead of four.
Rejected alternatives (ICU not compiled into the SQLCipher build; FTS changes substring→token semantics) are documented in #227.
**Approach A** from the #227 examination (recommended). Restores Unicode-aware case-insensitive **substring** search — identical semantics to pre-#223, minus the ASCII-only `LIKE` limitation.
## Implementation
- **Schema v18→v19:** add a `searchText` column to `messages` (`MessageEntity`), populated at write time in the entity mapper as `lowercase()` of the searchable fields concatenated (`sender` + `senderEmail` + `subject` + `snippet`). Kotlin `String.lowercase()` is Unicode-aware and locale-independent, so it casefolds beyond ASCII.
- **Migration:** add the column + backfill existing rows. SQL `lower()` is ASCII-only, so either (a) backfill via SQL `lower()` and let non-ASCII rows self-heal on their next sync/rewrite, or (b) backfill in Kotlin for immediate correctness. Export the schema and add a `MigrationTest` v18→v19 case.
- **Queries:** replace the four-way `LIKE` in `MessageDao.pagingUnifiedFolderSearchSummaries` / `pagingFolderSearchSummaries` with a single `WHERE searchText LIKE :pattern`; the repo builds `pattern` from the **lowercased** query via the existing `likePattern` (keep `\ % _` escaping + `ESCAPE '\'`).
- Keep `inInbox` unfiltered for search (still surfaces transient server-search hits), and keep the paged window loading + load-state-gated empty state from #223.
## Tests
- `MailRepositoryImplTest`: a Unicode case-insensitive match (e.g. query `"ÄPFEL"` matches stored `"äpfel"`), and the existing LIKE-escape assertion still holds against the lowercased pattern.
- `MigrationTest`: v18→v19 adds the column and preserves rows.
## Notes
- Perf profile is unchanged (leading-`%` scan, folder-scoped + paged) — a correctness fix, not a perf regression; one comparison instead of four.
- Rejected alternatives (ICU not compiled into the SQLCipher build; FTS changes substring→token semantics) are documented in #227.
Implementation finding — single searchText won't stay in sync; use per-field casefold columns
The searchable fields aren't written only through the entity mapper — they're maintained by partial UPDATEs that don't carry all four fields:
MessageDao.insertNew (via toEntity) sets sender/senderEmail/subject, but snippet is "" at insert (Mappers.kt:142).
MessageDao.updateBody (MessageDao.kt:179) sets the snippet later, when the body is fetched — so the snippet only exists post-insert.
MessageDao.updateHeaderContent (MessageDao.kt:162) refreshes sender/senderEmail/subject (headers rarely change, but for correctness).
A single concatenated searchText populated only in toEntity would therefore never include the snippet and would go stale on a header refresh — breaking snippet search. Recomputing a concatenated column inside a partial UPDATE would need the other fields, which those methods don't have.
Corrected design (still approach A — casefold columns)
Per-field fold columns, because each depends only on its own source field, so each write site can maintain its own:
Add senderFold, senderEmailFold, subjectFold, snippetFold (TEXT NOT NULL DEFAULT ''), each = source.lowercase() (Kotlin, Unicode-aware).
toEntity sets all four (snippetFold = ""); updateBody also sets snippetFold; updateHeaderContent also sets the three header folds.
Search: WHERE senderFold LIKE :p OR senderEmailFold LIKE :p OR subjectFold LIKE :p OR snippetFold LIKE :p, :p built from the lowercased query (keep \ % _ escaping + ESCAPE '\').
Migration v18→v19: add the columns + ASCII lower() backfill (non-ASCII rows self-heal on their next write). Export schema + MigrationTest.
Scope options
Full (recommended): all four fold columns — matches the pre-#223 behaviour, which searched the snippet too. Touches toEntity + updateBody + updateHeaderContent.
Headers-only:senderFold/senderEmailFold/subjectFold (3 columns), snippet not searchable — drops the updateBody change and one column, but you lose "find by body-preview text."
## Implementation finding — single `searchText` won't stay in sync; use per-field casefold columns
The searchable fields aren't written only through the entity mapper — they're maintained by **partial `UPDATE`s** that don't carry all four fields:
- `MessageDao.insertNew` (via `toEntity`) sets sender/senderEmail/subject, but **snippet is `""` at insert** (`Mappers.kt:142`).
- `MessageDao.updateBody` (`MessageDao.kt:179`) sets the **snippet** later, when the body is fetched — so the snippet only exists *post-insert*.
- `MessageDao.updateHeaderContent` (`MessageDao.kt:162`) refreshes sender/senderEmail/subject (headers rarely change, but for correctness).
A single concatenated `searchText` populated only in `toEntity` would therefore **never include the snippet** and would go stale on a header refresh — breaking snippet search. Recomputing a concatenated column inside a partial `UPDATE` would need the *other* fields, which those methods don't have.
### Corrected design (still approach A — casefold columns)
**Per-field fold columns**, because each depends only on its own source field, so each write site can maintain its own:
- Add `senderFold`, `senderEmailFold`, `subjectFold`, `snippetFold` (`TEXT NOT NULL DEFAULT ''`), each `= source.lowercase()` (Kotlin, Unicode-aware).
- `toEntity` sets all four (`snippetFold = ""`); `updateBody` also sets `snippetFold`; `updateHeaderContent` also sets the three header folds.
- Search: `WHERE senderFold LIKE :p OR senderEmailFold LIKE :p OR subjectFold LIKE :p OR snippetFold LIKE :p`, `:p` built from the **lowercased** query (keep `\ % _` escaping + `ESCAPE '\'`).
- Migration v18→v19: add the columns + ASCII `lower()` backfill (non-ASCII rows self-heal on their next write). Export schema + `MigrationTest`.
### Scope options
- **Full (recommended):** all four fold columns — matches the pre-#223 behaviour, which searched the snippet too. Touches `toEntity` + `updateBody` + `updateHeaderContent`.
- **Headers-only:** `senderFold`/`senderEmailFold`/`subjectFold` (3 columns), snippet **not** searchable — drops the `updateBody` change and one column, but you lose "find by body-preview text."
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Approach A from the #227 examination (recommended). Restores Unicode-aware case-insensitive substring search — identical semantics to pre-#223, minus the ASCII-only
LIKElimitation.Implementation
searchTextcolumn tomessages(MessageEntity), populated at write time in the entity mapper aslowercase()of the searchable fields concatenated (sender+senderEmail+subject+snippet). KotlinString.lowercase()is Unicode-aware and locale-independent, so it casefolds beyond ASCII.lower()is ASCII-only, so either (a) backfill via SQLlower()and let non-ASCII rows self-heal on their next sync/rewrite, or (b) backfill in Kotlin for immediate correctness. Export the schema and add aMigrationTestv18→v19 case.LIKEinMessageDao.pagingUnifiedFolderSearchSummaries/pagingFolderSearchSummarieswith a singleWHERE searchText LIKE :pattern; the repo buildspatternfrom the lowercased query via the existinglikePattern(keep\ % _escaping +ESCAPE '\').inInboxunfiltered for search (still surfaces transient server-search hits), and keep the paged window loading + load-state-gated empty state from #223.Tests
MailRepositoryImplTest: a Unicode case-insensitive match (e.g. query"ÄPFEL"matches stored"äpfel"), and the existing LIKE-escape assertion still holds against the lowercased pattern.MigrationTest: v18→v19 adds the column and preserves rows.Notes
%scan, folder-scoped + paged) — a correctness fix, not a perf regression; one comparison instead of four.Implementation finding — single
searchTextwon't stay in sync; use per-field casefold columnsThe searchable fields aren't written only through the entity mapper — they're maintained by partial
UPDATEs that don't carry all four fields:MessageDao.insertNew(viatoEntity) sets sender/senderEmail/subject, but snippet is""at insert (Mappers.kt:142).MessageDao.updateBody(MessageDao.kt:179) sets the snippet later, when the body is fetched — so the snippet only exists post-insert.MessageDao.updateHeaderContent(MessageDao.kt:162) refreshes sender/senderEmail/subject (headers rarely change, but for correctness).A single concatenated
searchTextpopulated only intoEntitywould therefore never include the snippet and would go stale on a header refresh — breaking snippet search. Recomputing a concatenated column inside a partialUPDATEwould need the other fields, which those methods don't have.Corrected design (still approach A — casefold columns)
Per-field fold columns, because each depends only on its own source field, so each write site can maintain its own:
senderFold,senderEmailFold,subjectFold,snippetFold(TEXT NOT NULL DEFAULT ''), each= source.lowercase()(Kotlin, Unicode-aware).toEntitysets all four (snippetFold = "");updateBodyalso setssnippetFold;updateHeaderContentalso sets the three header folds.WHERE senderFold LIKE :p OR senderEmailFold LIKE :p OR subjectFold LIKE :p OR snippetFold LIKE :p,:pbuilt from the lowercased query (keep\ % _escaping +ESCAPE '\').lower()backfill (non-ASCII rows self-heal on their next write). Export schema +MigrationTest.Scope options
toEntity+updateBody+updateHeaderContent.senderFold/senderEmailFold/subjectFold(3 columns), snippet not searchable — drops theupdateBodychange and one column, but you lose "find by body-preview text."