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

Search should search in notes, too #488

New Issue
Closed
opened 2026-06-06 15:27:17 +00:00 by guettli · 6 comments
guettli commented 2026-06-06 15:27:17 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

The search should search in notes of mails, too.

The search should search in notes of mails, too.
guettlibot commented 2026-06-06 15:35:14 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

Now I have enough context to write the implementation plan.

Implementation Plan: Include Notes in Email Search

Background

The current searchEmailsGlobal() in lib/data/repositories/email_repository_impl.dart (lines 2916–2938) queries only the email_fts FTS5 virtual table, which indexes subject, preview, and from_json from the emails table. Notes live in the separate EmailNotes table (note_text column), linked to emails via message_id + account_id. They are not indexed by FTS5.

What to change

1. lib/data/repositories/email_repository_impl.dart — searchEmailsGlobal()

Extend the method to run a UNION of two queries and return deduplicated results:

SELECT DISTINCT e.*
FROM emails e
WHERE e.rowid IN (
  -- existing FTS5 path (subject, preview, from)
  SELECT e2.rowid
  FROM email_fts f
  JOIN emails e2 ON e2.rowid = f.rowid
  WHERE email_fts MATCH ?            -- fts query
    AND (? IS NULL OR e2.account_id = ?)

  UNION

  -- new: notes path
  SELECT e3.rowid
  FROM emails e3
  JOIN email_notes n
    ON n.message_id = e3.message_id
   AND (? IS NULL OR n.account_id = ?)
  WHERE n.note_text LIKE ?           -- '%query%'
)
AND (? IS NULL OR e.account_id = ?)
ORDER BY e.date DESC
LIMIT 50

Pass the FTS prefix query (output of _toFtsQuery()) for the first branch and a LIKE '%raw_query%' pattern for the notes branch.

No schema migration is needed — EmailNotes already exists (since schema v39).

2. No other files need to change

  • The EmailRepository interface, the search screen (search_screen.dart), and the search history logic are all unaffected — they receive the same List<Email> return type.

Scope / risks

  • The LIKE '%…%' on note_text is a full-scan, but the EmailNotes table is expected to be small (user-authored notes only), so performance impact is negligible.
  • The UNION deduplicates by rowid, so an email matching both FTS and a note appears only once.
  • The existing 50-result LIMIT and optional accountId filter are preserved.
Now I have enough context to write the implementation plan. **Implementation Plan: Include Notes in Email Search** **Background** The current `searchEmailsGlobal()` in `lib/data/repositories/email_repository_impl.dart` (lines 2916–2938) queries only the `email_fts` FTS5 virtual table, which indexes `subject`, `preview`, and `from_json` from the `emails` table. Notes live in the separate `EmailNotes` table (`note_text` column), linked to emails via `message_id` + `account_id`. They are not indexed by FTS5. **What to change** **1. `lib/data/repositories/email_repository_impl.dart` — `searchEmailsGlobal()`** Extend the method to run a `UNION` of two queries and return deduplicated results: ```sql SELECT DISTINCT e.* FROM emails e WHERE e.rowid IN ( -- existing FTS5 path (subject, preview, from) SELECT e2.rowid FROM email_fts f JOIN emails e2 ON e2.rowid = f.rowid WHERE email_fts MATCH ? -- fts query AND (? IS NULL OR e2.account_id = ?) UNION -- new: notes path SELECT e3.rowid FROM emails e3 JOIN email_notes n ON n.message_id = e3.message_id AND (? IS NULL OR n.account_id = ?) WHERE n.note_text LIKE ? -- '%query%' ) AND (? IS NULL OR e.account_id = ?) ORDER BY e.date DESC LIMIT 50 ``` Pass the FTS prefix query (output of `_toFtsQuery()`) for the first branch and a `LIKE '%raw_query%'` pattern for the notes branch. No schema migration is needed — `EmailNotes` already exists (since schema v39). **2. No other files need to change** - The `EmailRepository` interface, the search screen (`search_screen.dart`), and the search history logic are all unaffected — they receive the same `List<Email>` return type. **Scope / risks** - The `LIKE '%…%'` on `note_text` is a full-scan, but the `EmailNotes` table is expected to be small (user-authored notes only), so performance impact is negligible. - The `UNION` deduplicates by `rowid`, so an email matching both FTS and a note appears only once. - The existing 50-result `LIMIT` and optional `accountId` filter are preserved.
guettlibot commented 2026-06-06 23:58:51 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

Implemented in PR #512: https://codeberg.org/guettli/sharedinbox/pulls/512

What changed:

The search now also searches in email notes. When a user searches for a keyword, the results include emails whose associated notes (in email_notes.note_text) contain that keyword — not just emails matched by subject, preview, or sender.

How it works:

  • Added a private _searchEmailsByNotes helper in EmailRepositoryImpl that JOINs emails with email_notes on message_id + account_id, filtering by LIKE on each search word
  • Both searchEmailsGlobal (cross-mailbox) and searchEmails (mailbox-specific) now call this helper and merge note-matched results with the existing FTS5 results, deduplicating by email ID

Tests added:

  • searchEmailsGlobal includes emails matched by note text
  • searchEmails includes emails matched by note text in mailbox (verifies mailbox filtering is respected)
Implemented in PR #512: https://codeberg.org/guettli/sharedinbox/pulls/512 **What changed:** The search now also searches in email notes. When a user searches for a keyword, the results include emails whose associated notes (in `email_notes.note_text`) contain that keyword — not just emails matched by subject, preview, or sender. **How it works:** - Added a private `_searchEmailsByNotes` helper in `EmailRepositoryImpl` that JOINs `emails` with `email_notes` on `message_id` + `account_id`, filtering by `LIKE` on each search word - Both `searchEmailsGlobal` (cross-mailbox) and `searchEmails` (mailbox-specific) now call this helper and merge note-matched results with the existing FTS5 results, deduplicating by email ID **Tests added:** - `searchEmailsGlobal includes emails matched by note text` - `searchEmails includes emails matched by note text in mailbox` (verifies mailbox filtering is respected)
guettlibot commented 2026-06-07 00:00:42 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 1/3).

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 1/3).
guettlibot commented 2026-06-07 00:05:50 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 2/3).

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 2/3).
guettlibot commented 2026-06-07 00:10:57 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 3/3).

agentloop: the issue was updated while the agent was working; resuming acpx session to adapt (attempt 3/3).
guettlibot commented 2026-06-07 00:16:04 +00:00 (Migrated from codeberg.org)
Copy Link
Copy Source

Still just agentloop housekeeping — no change to requirements. Previous answer stands.

Implemented in PR #512: https://codeberg.org/guettli/sharedinbox/pulls/512

What changed:

The search now also searches in email notes. When a user searches for a keyword, the results include emails whose associated notes (in email_notes.note_text) contain that keyword — not just emails matched by subject, preview, or sender.

How it works:

  • Added a private _searchEmailsByNotes helper in EmailRepositoryImpl that JOINs emails with email_notes on message_id + account_id, filtering by LIKE on each search word
  • Both searchEmailsGlobal (cross-mailbox) and searchEmails (mailbox-specific) now call this helper and merge note-matched results with the existing FTS5 results, deduplicating by email ID

Tests added:

  • searchEmailsGlobal includes emails matched by note text
  • searchEmails includes emails matched by note text in mailbox (verifies mailbox filtering is respected)
Still just agentloop housekeeping — no change to requirements. Previous answer stands. Implemented in PR #512: https://codeberg.org/guettli/sharedinbox/pulls/512 **What changed:** The search now also searches in email notes. When a user searches for a keyword, the results include emails whose associated notes (in `email_notes.note_text`) contain that keyword — not just emails matched by subject, preview, or sender. **How it works:** - Added a private `_searchEmailsByNotes` helper in `EmailRepositoryImpl` that JOINs `emails` with `email_notes` on `message_id` + `account_id`, filtering by `LIKE` on each search word - Both `searchEmailsGlobal` (cross-mailbox) and `searchEmails` (mailbox-specific) now call this helper and merge note-matched results with the existing FTS5 results, deduplicating by email ID **Tests added:** - `searchEmailsGlobal includes emails matched by note text` - `searchEmails includes emails matched by note text in mailbox` (verifies mailbox filtering is respected)
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/code-done
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#488