Integration tests for aggregation accuracy and org isolation

epic-periodic-summaries-foundation-task-011 — Write integration tests using a local Supabase test instance that seed two organisations with overlapping activity data, then invoke the ActivityAggregationRepository and assert that each organisation's aggregation results contain only their own data. Verify session counts and total hours match the seeded values exactly. Test offline fallback by disabling network and confirming SummaryCacheRepository returns stale cached data instead of throwing.

critical priority medium complexity testing pending testing specialist Tier 5

Acceptance Criteria

Two organisations (orgA, orgB) are seeded with overlapping date ranges but distinct session data before each test run

Calling `ActivityAggregationRepository.getAggregation(orgId: orgA, period: ...)` returns results whose session_count and total_hours match exactly the seeded values for orgA only

orgB's sessions are never present in orgA's aggregation results — verified by asserting on session IDs or unique identifying fields

Calling the same method with orgB's ID returns orgB's data without contamination from orgA

Passing an orgId that matches neither seeded org returns an empty/zero aggregation, not an exception

Offline fallback test: after a successful aggregation call (which populates the cache), network access is simulated as unavailable; a second call returns the cached data with `is_stale = false`

After calling `invalidateCache(orgId)`, a subsequent offline call returns data with `is_stale = true` (stale indicator present, not an exception)

All assertions on numeric totals use exact equality — no floating-point tolerance (hours stored as integer minutes or fixed-point decimal)

Tests are isolated — each test case seeds fresh data and cleans up after itself (tearDown)

Technical Requirements

frameworks

flutter_test

Flutter

Supabase local emulator or test project

drift or sqflite (in-memory for cache tests)

apis

Supabase REST API (activities table)

Supabase Auth (test user sessions for orgA and orgB)

data models

ActivitySession

ActivityAggregation

SummaryPeriodModel

periodic_summaries_cache

performance requirements

Each integration test must complete in under 10 seconds against the local Supabase emulator

Seed and teardown operations must complete in under 2 seconds

security requirements

Test credentials must never be hardcoded in source — use environment variables or a `.env.test` file excluded from version control

Test Supabase instance must use Row Level Security (RLS) policies identical to production — the tests must prove RLS enforcement, not bypass it

Execution Context

Execution Tier

Tier 5

Tier 5 - 253 tasks

Can start after Tier 4 completes

View Full Execution Plan

Implementation Notes

Use a dedicated Supabase test project (not production) — configure via environment variables `SUPABASE_TEST_URL` and `SUPABASE_TEST_ANON_KEY`. To simulate offline, override the HTTP client used by the Supabase client with a stub that throws `SocketException` — inject this via a test-only constructor parameter on the repository. Do NOT disable WiFi on the device — that would make the test environment-dependent. For seeding, use Supabase's service role key (server-side) to bypass RLS during setup, then test with the anon key (client-side) to verify RLS enforcement.

Tag these tests with `@Tags(['integration'])` so they can be skipped in fast unit test runs. Ensure the CI workflow has a `supabase start` step before the test step. Document the required Docker/Supabase version in the repo's CI readme.

Testing Requirements

Use `flutter_test` integration test runner. Set up a local Supabase instance (Docker or `supabase start`) for CI. In `setUp`: create two test users assigned to orgA and orgB respectively, insert 10 activity sessions for orgA and 8 for orgB with a 3-session date overlap in the target period. In `tearDown`: delete all seeded rows by test-run ID.

Test cases: (1) orgA aggregation returns exactly 10 sessions and correct total hours; (2) orgB aggregation returns exactly 8 sessions; (3) no session from orgB appears in orgA results; (4) unknown orgId returns zero aggregation; (5) cache populated after successful fetch; (6) offline mode returns cached data; (7) invalidated cache returns stale flag. Run with `flutter test integration_test/` in CI pipeline.

Component

Activity Aggregation Repository

data medium

Dependencies (3)

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. epic-periodic-summaries-foundation-task-008 Implement the ActivityAggregationRepository class in Dart using the Supabase client. It must call the aggregation RPCs defined in task-004, deserialise results into typed AggregationResult models, surface organisation_id in every request to enforce isolation, and propagate Supabase errors as domain exceptions. Integrate with Riverpod as an auto-dispose provider. epic-periodic-summaries-foundation-task-005 Implement the OrgDataIsolationGuard Dart class that acts as a cross-cutting validation layer. It must verify that any repository method call carries a non-null, non-empty organisation_id matching the authenticated user's active organisation before the call proceeds. Provide a wrapCall<T>(orgId, Future<T> Function() fn) utility that throws an OrganisationMismatchException if isolation is violated. Register as a singleton in Riverpod. epic-periodic-summaries-foundation-task-009

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.

Quick Links

All Tasks Execution Plan