Implement Drift offline ilike-equivalent search queries
epic-contact-search-data-layer-task-004 — Implement the OfflineSearchRepository class using Drift generated DAOs. Write LIKE-based SQL queries against the local cache tables for name, organisation, and notes content. Ensure results match the same ContactSearchResult model returned by the Supabase repository so the service layer receives an identical interface regardless of connectivity state.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Place the class in lib/features/contact_search/data/local/offline_search_repository.dart. Inject the AppDatabase via constructor. Define a Drift DAO (ContactSearchDao) annotated with @DriftAccessor(tables: [CachedContactsTable, CachedNotesTable]) and define the LIKE queries there. For case-insensitive Norwegian LIKE, use Drift's CustomExpression or the expression API: CustomExpression
Alternatively, store a pre-lowercased search_name column in CachedContactsTable at sync time to avoid LOWER() at query time. For the notes join, use a Drift join query: select().from(cachedNotesTable).join([innerJoin(cachedContactsTable, cachedContactsTable.id.equalsExp(cachedNotesTable.contactId))]).where(cachedNotesTable.content.like(...)). Map the TypedResult rows to ContactSearchResult using the same mapping logic as SupabaseSearchRepository to guarantee interface parity.
Testing Requirements
Write unit tests using NativeDatabase.memory() as the Drift backend to avoid filesystem dependencies. Seed the in-memory database with 5–10 CachedContact rows and 3–5 CachedNote rows covering Norwegian names with æ, ø, å characters. Test: (1) searchByName with a partial Norwegian name returns the correct contact, (2) searchByOrganisation with a partial org name returns matching contacts, (3) searchByNotes with a keyword present in one note returns the linked contact, (4) search with a query matching both a name and a notes field deduplicates to a single result with matchedField.name, (5) searchByName with a query matching zero rows returns an empty list without throwing. Run all tests with flutter_test.
Supabase RLS policies may not correctly scope ilike search results to the authenticated user's organisation and chapter, causing data leakage across organisations or empty result sets for valid queries.
Mitigation & Contingency
Mitigation: Reuse and extend existing RLS query builder patterns from the contact-list-management feature. Write integration tests against a seeded multi-organisation test database to verify cross-org isolation before merging.
Contingency: If RLS scoping is insufficient, add an explicit organisation_id filter in the Dart query builder layer as a defence-in-depth measure while the Supabase policy is corrected.
Adding new Drift tables for the contact cache may conflict with existing migrations or schema versions in the contact-list-management feature if both features cache the same contacts table, causing migration failures on user devices.
Mitigation & Contingency
Mitigation: Audit existing Drift schema versions from contact-list-management before writing new migrations. Reuse existing cache tables if the schema already covers required fields; only add missing fields via ALTER or new version.
Contingency: If schema conflict occurs, consolidate into a single shared cache table owned by contact-list-management and expose a DAO interface to the search feature, avoiding duplicated schema ownership.
The offline cache may surface significantly stale contact data if sync has not run recently, leading coordinators to act on outdated information (wrong phone numbers, changed assignments).
Mitigation & Contingency
Mitigation: Store and surface the last-sync timestamp prominently in the UI layer. Trigger a background cache refresh on app foreground when connectivity is detected.
Contingency: If staleness becomes a reported UX issue, implement a maximum-age threshold that shows a warning banner when the cache is older than a configurable limit (e.g. 24 hours).