🏠 Overview › ✅ Implementation Tasks ›✅ epic-contact-list-management-foundation-task-003

Build validated ILIKE search predicate constructor

epic-contact-list-management-foundation-task-003 — Add the search predicate construction method to ContactRLSQueryBuilder that generates ILIKE patterns for the name and notes columns. Validate the input string (trim, escape special characters, enforce minimum length) before constructing the predicate to prevent accidental full-table scans or injection-style patterns.

high priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

withSearch(query) method exists on ContactRLSQueryBuilder and is chainable (returns self/builder)
Input string is trimmed before validation — leading/trailing whitespace does not count toward minimum length
Minimum length of 2 non-whitespace characters is enforced; strings below this threshold cause the search predicate to be omitted entirely (no error, no full-table scan)
Special ILIKE metacharacters (%, _) are escaped in the input string before pattern construction so user-typed '%' does not produce a wildcard match
Backslash characters are escaped to prevent escape sequence injection
The generated ILIKE pattern follows the form '%<escaped_input>%' for substring matching on both name and notes columns
The predicate applies to both columns using an .or() join: ilike('name', pattern).or(ilike('notes', pattern))
Whitespace-only strings (after trim) are treated identically to empty strings — search predicate is omitted
The method is safe to call with null input in debug mode (logs warning, omits predicate); in release mode a non-nullable parameter type is preferred
flutter analyze reports no errors on the modified file

Technical Requirements

frameworks
Flutter
Dart
apis
Supabase PostgREST (.ilike, .or)
data models
Contact
performance requirements
Search predicate is only appended when the validated input meets minimum length — prevents expensive full-table ILIKE scans on Supabase for empty or trivial queries
The .or() combining name and notes ILIKE predicates must be expressed as a single Supabase .or() call, not two chained .ilike() calls, to allow the database to use a combined index if available
security requirements
ILIKE metacharacter escaping (%, _, \) must be applied before the pattern is sent to Supabase to prevent unintended wildcard expansion
Input length cap: truncate strings over 100 characters before constructing the pattern to prevent excessively long ILIKE patterns from degrading Supabase query performance
Never log the raw search query in release builds as it may contain PII (contact names)

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Implement the escaping logic as a private static method _escapeIlikePattern(String input) that handles %, _, and \ in a single pass using String.replaceAllMapped() or a regex. The minimum length check should be applied AFTER trimming and BEFORE escaping so the length count reflects actual content. For the Supabase .or() call, check the current version of the supabase_flutter package for the correct syntax — the API changed between v1 and v2 (v2 uses .or('name.ilike.%x%,notes.ilike.%x%') as a string filter). Pin to the project's existing supabase_flutter version.

Add a brief inline comment explaining why the minimum length guard exists (performance, not security) to help future maintainers understand the intent.

Testing Requirements

Unit tests (consolidated in task-004) must cover: (1) normal input 'hansen' → pattern '%hansen%' on both columns; (2) input with % character → escaped to '\%'; (3) input with _ character → escaped to '\_'; (4) whitespace-only input → no search predicate appended; (5) single character input → no predicate appended; (6) exactly 2 character input → predicate appended; (7) input over 100 characters → truncated to 100 before pattern construction; (8) combined builder call (forOrganisation + withRole + withSearch) → all three predicates present in correct order. Use flutter_test with mock Supabase builder.

Component
Contact RLS Query Builder
infrastructure low
Dependencies (1)
Within ContactRLSQueryBuilder, implement the organization_id scoping logic that appends an .eq('organization_id', orgId) filter to every outbound Supabase query. Ensure the scope is applied before any additional predicates so it cannot be bypassed. Cover the multi-chapter NHF case where a user may belong to multiple organization units. epic-contact-list-management-foundation-task-002
Epic Risks (2)
high impact medium prob security

Existing Supabase RLS policies for the contacts and peer_mentors tables may not align with the application-level UserRole model, causing ContactRLSQueryBuilder to construct filter expressions that are redundant, conflicting, or that allow over-fetching. In a multi-chapter context (NHF), this could expose contacts belonging to other chapters.

Mitigation & Contingency

Mitigation: Audit and document the existing RLS policies against the UserRole enum before writing a single line of query builder code. Write integration tests asserting cross-organization data isolation using separate test user tokens for each role.

Contingency: If RLS policies are misaligned at runtime, add an explicit application-level organization_id equality check in ContactRepository as a secondary guard while the database policies are corrected in a coordinated migration.

medium impact low prob integration

The Supabase schema for contacts and peer_mentors tables may differ from the expected typed models — missing columns, renamed fields, or type mismatches — causing deserialization failures that surface only at runtime during integration testing.

Mitigation & Contingency

Mitigation: Document expected schema fields upfront and validate against the live Supabase schema at sprint start. Use freezed and json_serializable for compile-time-safe deserialization with explicit required/optional field declarations.

Contingency: Introduce nullable fields with safe defaults for any schema mismatches discovered in testing; log deserialization errors to the monitoring service so schema drift is caught before production deployment.

Quick Links
All Tasks Execution Plan