SummaryCacheRepository Dart implementation
epic-periodic-summaries-foundation-task-008 — Implement the SummaryCacheRepository class in Dart that manages read and write operations against the local SQLite cache. Expose methods: getCachedSummary(orgId, periodType), cacheSummaries(List<SummaryPeriodModel>), invalidateCache(orgId), and isCacheStale(orgId, maxAgeMinutes). This repository is the offline fallback used by the digest card when no network connection is available.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Implementation Notes
Implement as a plain Dart class (not a Flutter widget) registered via `riverpod` `Provider` or `AsyncNotifierProvider`. Accept the database accessor as a constructor parameter (dependency injection) so tests can pass an in-memory instance. Use a single database transaction in `cacheSummaries` — wrap the entire list upsert in `db.transaction(() async { ... })`.
For `isCacheStale`, query `MAX(last_synced_at)` rather than iterating rows — this is O(1) with the composite index. The `invalidateCache` method should issue a single `UPDATE SET is_stale = 1 WHERE organisation_id = ?` — do not load rows into Dart objects first. Avoid storing sensitive PII in the cache; store only aggregated numeric fields and metadata. Keep the class under 150 lines — if it grows larger, extract query helpers to a separate `SummaryCacheDao`.
Testing Requirements
Write flutter_test unit tests using an in-memory database (drift test helper or sqflite in-memory): (1) getCachedSummary returns null on empty cache; (2) cacheSummaries writes models and getCachedSummary retrieves the correct one; (3) isCacheStale returns true when last_synced_at is older than maxAgeMinutes; (4) isCacheStale returns false immediately after cacheSummaries; (5) invalidateCache sets is_stale=true and subsequent isCacheStale returns true; (6) cacheSummaries rolls back on simulated write failure; (7) all methods throw on null/empty orgId. Target 100% branch coverage of the repository class.
Supabase RLS policies for aggregation views are more complex than single-table policies. A misconfigured policy could silently allow a coordinator in one organisation to see data from another, causing a data breach and breaking trust with participating organisations.
Mitigation & Contingency
Mitigation: Write automated RLS integration tests that create two separate organisations with distinct data, then assert that queries authenticated as org-A users return only org-A rows. Run these tests in CI on every PR touching the database layer.
Contingency: If an RLS bypass is discovered post-deployment, immediately disable the periodic summaries feature flag, revoke affected sessions, audit access logs, notify affected organisations, and patch the policy before re-enabling.
Activity records may span multiple sessions types, proxy registrations, and coordinator bulk entries. Incorrect JOIN logic or missing filters in the aggregation query could double-count sessions or omit activity types, producing inaccurate summaries that erode user trust.
Mitigation & Contingency
Mitigation: Build a fixture dataset covering all activity registration paths (direct, proxy, bulk) and assert expected aggregated counts in integration tests before any UI consumes the repository.
Contingency: If inaccurate counts are reported post-launch, mark affected summaries as invalidated in the database and re-trigger generation once the query is corrected. Communicate transparently to affected users via an in-app banner.
The local cache must be invalidated when a new summary arrives via push notification. If the push token is stale or the FCM/APNs delivery is delayed, the device may show an outdated summary for an extended period, confusing users who see different numbers online versus offline.
Mitigation & Contingency
Mitigation: Implement a TTL on cached summaries (max 48 hours) so stale data is auto-cleared even without a push notification. Also trigger cache refresh on app foreground if the current period's summary is older than 24 hours.
Contingency: Provide a manual pull-to-refresh on the summary card that bypasses the cache and fetches directly from Supabase when a network connection is available.