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

Implement cache write and upsert operations in Drift

epic-contact-search-data-layer-task-005 — Add upsert methods to the Drift DAOs that write contacts and notes fetched from Supabase into the local cache. Use insertOrReplace strategy keyed on record ID. Include a bulk upsert path for initial population from the contact list feature and a single-record upsert path for incremental updates.

critical priority low complexity database pending database specialist Tier 2

Acceptance Criteria

ContactsDao exposes upsertContact(ContactsCompanion) using insertOrReplace strategy keyed on contact.id (UUID)
ContactsDao exposes upsertContacts(List<ContactsCompanion>) for bulk insertion in a single Drift transaction
NotesDao exposes upsertNote(NotesCompanion) and upsertNotes(List<NotesCompanion>) with the same insertOrReplace strategy keyed on note.id
Bulk upsert for 500 contacts and associated notes completes in under 500ms on a mid-range Android device
Re-upserting an identical record does not create duplicates; the row count remains stable
Upserting a record with changed fields (e.g. updated last_name) overwrites the previous values correctly
All upsert methods are wrapped in Drift transactions to guarantee atomicity; a partial failure rolls back the entire batch
DAO methods accept Drift companion objects (not raw Supabase maps) to enforce compile-time type safety
No Supabase SDK imports appear in DAO files — DAOs are pure Drift/SQLite layer
Unit tests for both single and bulk upsert paths pass on in-memory Drift database

Technical Requirements

frameworks
drift (moor successor) for Flutter local SQLite ORM
flutter_test for unit testing
drift/native or drift/wasm for in-memory test database
data models
contact
assignment
performance requirements
Bulk upsert of 500 contacts must complete in < 500ms
Single-record upsert must complete in < 50ms
Use Drift transaction batching — avoid issuing one SQL statement per record in loops
security requirements
DAOs must never store Supabase JWT or authentication tokens
Contact PII (names, email, phone) stored in the local Drift cache must be cleared on logout via clearCache()
No raw SQL string interpolation — use Drift's typed query builder exclusively to prevent SQL injection

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Use Drift's `into(table).insertAll(companions, mode: InsertMode.insertOrReplace)` for the bulk path — this compiles to a single `INSERT OR REPLACE` statement per row, which is far more efficient than individual inserts. Wrap the bulk call in `transaction(() async { ... })` to guarantee atomicity. For the single-record path, `into(table).insertOnConflictUpdate(companion)` is cleaner and more expressive than raw insertOrReplace.

Define ContactsCompanion and NotesCompanion as the only accepted types in DAO method signatures so the compiler enforces the data shape. Keep DAOs in the `data/local/dao/` directory following existing project conventions. The DAO layer must have zero knowledge of Supabase — mapping from Supabase response maps to Drift companions belongs in the repository layer (task-006).

Testing Requirements

Unit tests using an in-memory Drift database (NativeDatabase.memory()). Cover: (1) single upsert inserts a new row when ID is absent, (2) single upsert overwrites fields when ID already exists, (3) bulk upsert inserts N rows and row count equals N, (4) bulk upsert on pre-populated table does not increase row count for existing IDs, (5) transaction rollback on simulated failure leaves table unchanged, (6) notes upsert correctly references the parent contact FK. All tests must run without a device or emulator using flutter test.

Component
Contact Cache Sync Repository
data low
Dependencies (1)
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. epic-contact-search-data-layer-task-002
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