Design Drift cache table schema for contacts and notes
epic-contact-search-data-layer-task-002 — Define Drift table classes for the local SQLite cache: a contacts cache table mirroring the Supabase contacts schema (id, name, organisation, chapter affiliation, role) and a notes cache table (id, contact_id, content, updated_at). Include indexes on searchable columns (name, organisation) for fast ilike-equivalent local queries.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Define Drift table classes in lib/features/contact_search/data/local/tables/. Keep the table definitions in separate files (cached_contacts_table.dart, cached_notes_table.dart) and include them in the AppDatabase part file. For the name and organisation LIKE queries, Drift's LIKE operator is available via (table.name.like('%$query%')) in a CustomExpression or via Drift's expression API. Note that SQLite LIKE is case-insensitive for ASCII characters but NOT for Unicode — for Norwegian names (æ, ø, å), use LOWER() on both sides of the comparison or consider using the unicode61 tokenizer if full-text search is added later.
The syncedAt column on CachedContactsTable enables cache invalidation: the sync service can purge rows older than a configurable TTL.
Testing Requirements
Write unit tests using an in-memory Drift database (NativeDatabase.memory()) to verify: (1) inserting a CachedContact row and querying it back returns the same data, (2) inserting a CachedNote with a valid contactId succeeds, (3) inserting a CachedNote with a non-existent contactId fails (foreign key constraint — enable PRAGMA foreign_keys = ON in the test setup), (4) a LIKE query on name returns matching rows and excludes non-matching rows. Use flutter_test for all tests. Assert that schema migration from version N to N+1 does not throw.
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).