high priority low complexity backend pending backend specialist Tier 3

Acceptance Criteria

`getCachedSummary(orgId, periodType)` returns the most recently synced `SummaryPeriodModel` for the given org and period type, or null if no cache entry exists
`cacheSummaries(List<SummaryPeriodModel>)` upserts all provided models, setting `last_synced_at` to the current timestamp and `is_stale = false`
`invalidateCache(orgId)` sets `is_stale = true` for all rows matching the given orgId and returns the count of rows invalidated
`isCacheStale(orgId, maxAgeMinutes)` returns true if the most recent `last_synced_at` for the org is older than `maxAgeMinutes` minutes, or if no cache entry exists
All methods throw an `OrganisationMismatchException` (or equivalent) if orgId is null or empty
The repository is registered as a Riverpod provider and can be injected into dependent providers without a Flutter widget tree
`cacheSummaries` is atomic — if any upsert fails, no partial writes are committed (use a database transaction)
A unit test with a mocked/in-memory database verifies each method's contract independently

Technical Requirements

frameworks
Flutter
Riverpod
drift or sqflite
flutter_test
data models
SummaryPeriodModel
periodic_summaries_cache (local SQLite table)
performance requirements
cacheSummaries for a list of 50 models must complete in under 200 ms
getCachedSummary must return within 20 ms for any org with up to 1 000 cached rows
security requirements
orgId parameter must be validated as non-null and non-empty before any database operation
No orgId may be inferred from context — callers must always supply it explicitly

Execution Context

Execution Tier
Tier 3

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.

Component
Summary Cache Repository
data low
Epic Risks (3)
high impact medium prob security

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.

medium impact medium prob technical

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.

low impact low prob integration

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.