🏠 Overview › ✅ Implementation Tasks ›✅ epic-contact-search-data-layer-task-003

Implement Supabase RLS-scoped ilike search queries

epic-contact-search-data-layer-task-003 — Implement the SupabaseSearchRepository class against the contacts and notes Supabase tables. Write parameterised ilike queries scoped by the authenticated user's organisation via RLS. Support search by contact name, organisation affiliation, and notes keywords. Return a unified ContactSearchResult list sorted by relevance (name match first).

critical priority medium complexity database pending backend specialist Tier 1

Acceptance Criteria

SupabaseSearchRepository implements IContactSearchRepository from task-001
searchByName(query) executes an ilike query on the contacts table name column: .ilike('name', '%$query%') and returns a List<ContactSearchResult> with matchedField set to SearchMatchedField.name
searchByOrganisation(query) executes an ilike query on the contacts table organisation column and returns results with matchedField set to SearchMatchedField.organisation
searchByNotes(keyword) executes an ilike query on the notes table content column, joins to the contacts table on contact_id, and returns results with matchedField set to SearchMatchedField.notes
The unified search() method calls all three search methods in parallel (Future.wait), deduplicates results by contact id (preferring the highest-priority matchedField), and returns the merged list sorted: name matches first, then organisation matches, then notes matches
All queries rely on Supabase Row Level Security to scope results to the authenticated user's organisation — no client-side organisation filter is applied in the Dart code
The class throws a SearchRepositoryException (custom exception) wrapping the original PostgrestException on any Supabase error, never leaking raw Supabase types to the caller
Integration verified: a manual test against a Supabase staging environment returns scoped results matching only the logged-in user's organisation

Technical Requirements

frameworks
Flutter
supabase_flutter
apis
Supabase PostgREST API (contacts table, notes table)
data models
ContactSearchResult
Contact
Note
performance requirements
Each ilike query must respond within 800ms under normal network conditions
The unified search() parallel call must complete within 1200ms end-to-end
Queries must use indexed columns — confirm that Supabase contacts.name and contacts.organisation have pg_trgm GIN indexes or standard btree indexes enabling ilike performance at scale
security requirements
Never pass the organisation_id as a query parameter from the client — rely exclusively on Supabase RLS policies tied to auth.uid()
Validate that query string is non-empty and trimmed before issuing the request to prevent empty ilike queries returning all rows
Log queries only in debug mode — never log query strings in production to avoid leaking PII search terms

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Place the class in lib/features/contact_search/data/remote/supabase_search_repository.dart. Inject the SupabaseClient via constructor for testability. For the notes join, use Supabase's nested select syntax: supabase.from('notes').select('id, content, contact_id, contacts(id, name, organisation, chapter_affiliation, role)').ilike('content', '%$keyword%'). Map the nested contacts object in the JSON response to ContactSearchResult.

For deduplication in search(), use a Map keyed by contact id, iterating results in priority order (name → organisation → notes) so the first (highest priority) match wins. Define SearchMatchedField as an enum with values name, organisation, notes and include it in the domain layer alongside the interface.

Testing Requirements

Write unit tests using a mocked SupabaseClient (Mockito or Mocktail). Test: (1) searchByName with a valid query calls .from('contacts').select().ilike('name', ...) and maps the response to ContactSearchResult list correctly, (2) searchByNotes calls the notes table and joins contact data, (3) search() deduplicates a contact that appears in both name and notes results — the deduplicated result keeps matchedField.name, (4) a PostgrestException from the mock client causes a SearchRepositoryException to be thrown. Do NOT write integration tests against real Supabase in this task — those belong in a separate integration test suite. Use flutter_test for all tests.

Component
Supabase Contact Search Repository
data low
Dependencies (1)
Create abstract repository interfaces (IContactSearchRepository, IOfflineSearchRepository, ICacheSyncRepository) in Dart that define the contracts the service layer will use. Include method signatures for name search, organisation search, notes keyword search, and cache operations so online and offline implementations are interchangeable. epic-contact-search-data-layer-task-001
Epic Risks (3)
high impact medium prob security

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.

medium impact medium prob integration

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.

medium impact medium prob scope

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).

Quick Links
All Tasks Execution Plan