SummaryPeriodRepository Dart implementation

epic-periodic-summaries-foundation-task-006 — Implement the SummaryPeriodRepository class in Dart that reads from and writes to the periodic_summaries Supabase table via the service-role client. Expose methods: getSummariesForOrg(orgId, periodType), getLatestSummary(orgId, periodType), upsertSummary(SummaryPeriodModel), and deleteSummariesOlderThan(date). Ensure all queries include explicit organisation_id filters as a defence-in-depth measure beyond RLS.

critical priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

Class `SummaryPeriodRepository` exists in `lib/repositories/summary_period_repository.dart`

`getSummariesForOrg(String orgId, PeriodType periodType)` returns `Future<List<SummaryPeriodModel>>` ordered by `period_start DESC`; always includes `.eq('organisation_id', orgId)` filter

`getLatestSummary(String orgId, PeriodType periodType)` returns `Future<SummaryPeriodModel?>` (nullable) — the single most recent summary for the given org and period type; returns null (not exception) if no rows exist

`upsertSummary(SummaryPeriodModel model)` performs a Supabase upsert on conflict `(organisation_id, period_type, period_start, period_end)` — updates `session_count`, `total_hours`, `generated_at`, and `payload` on conflict

`deleteSummariesOlderThan(DateTime cutoff)` deletes rows where `generated_at < cutoff` AND `organisation_id = [current org]` — never deletes cross-organisation without an explicit org filter

`SummaryPeriodModel` is a Dart class with all table columns typed: `id` (String/UUID), `organisationId` (String), `periodType` (PeriodType), `periodStart` (DateTime UTC), `periodEnd` (DateTime UTC), `sessionCount` (int), `totalHours` (double), `generatedAt` (DateTime UTC), `payload` (Map<String, dynamic>)

`SummaryPeriodModel.fromJson()` and `toJson()` factory/method handle all column mappings including snake_case → camelCase conversion

Repository uses the service-role Supabase client (injected via constructor) for write operations, and the authenticated client for read operations — or a single service-role client with explicit org filters as stated in task description

All `PostgrestException` errors are caught and rethrown as typed `SummaryPeriodException`

Riverpod `Provider<SummaryPeriodRepository>` (not autoDispose — repository is long-lived) registered in `lib/providers/repository_providers.dart`

Technical Requirements

frameworks

Flutter

Riverpod

Supabase Flutter SDK

apis

supabase.from('periodic_summaries').select()

supabase.from('periodic_summaries').upsert()

supabase.from('periodic_summaries').delete()

data models

SummaryPeriodModel

PeriodType

periodic_summaries table

performance requirements

getSummariesForOrg must apply `.limit(100)` by default to prevent unbounded result sets — add optional `limit` parameter

getLatestSummary must use `.limit(1)` and `.single()` equivalent — do not fetch all rows and take first in Dart

upsertSummary must be idempotent and complete in <200ms for a single row

security requirements

Service-role client secret must never be embedded in the Flutter app binary — it must be accessed only in server-side Edge Functions or backend services. If the Flutter app calls this repository, use authenticated client + RLS for reads; writes must go through an Edge Function

All delete operations must include explicit organisation_id equality filter to prevent accidental cross-org deletion

payload JSONB field must be sanitised before storage — validate it is a Map<String, dynamic> with no deeply nested structures exceeding reasonable depth

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

The service-role client concern is important: in a Flutter mobile app, you cannot safely embed the service-role key. The architecture should use the authenticated client for reads (RLS provides org scoping), and route writes (`upsertSummary`, `deleteSummariesOlderThan`) through a Supabase Edge Function that uses the service-role key server-side. The repository interface in Dart can still define these methods — the implementation calls the Edge Function via `supabase.functions.invoke('upsert-summary', body: model.toJson())` for writes. For MVP, if Edge Functions are not ready, use the authenticated client for writes too and rely on RLS; document this as a security debt to resolve before production.

`SummaryPeriodModel.fromJson` must use `DateTime.parse(json['period_start'] as String)` and ensure UTC: `dt.isUtc ? dt : dt.toUtc()`.

Testing Requirements

Unit tests in `test/repositories/summary_period_repository_test.dart` using `flutter_test` and `mocktail`. Mock the `SupabaseClient` query builders. Test cases: (1) `getSummariesForOrg` correctly maps response list to `List` with correct types, (2) `getLatestSummary` returns null when response is empty (not exception), (3) `upsertSummary` calls upsert with correct conflict target columns, (4) `deleteSummariesOlderThan` includes both `generated_at < cutoff` and `organisation_id` filters, (5) `PostgrestException` from any method is caught and rethrown as `SummaryPeriodException`, (6) `SummaryPeriodModel.fromJson` correctly parses all field types including JSONB payload as `Map`. Integration test: verify upsert idempotency by calling `upsertSummary` twice with the same conflict key and asserting one row in the table.

Component

Summary Period Repository

data low

Dependencies (2)

Create and apply a PostgreSQL migration that adds the periodic_summaries table with columns for organisation_id, period_type (weekly/monthly/quarterly), period_start, period_end, session_count, total_hours, generated_at, and a JSONB payload column for flexible aggregation data. Include appropriate indexes on organisation_id and period boundaries for query performance. epic-periodic-summaries-foundation-task-001 Define and apply Supabase Row Level Security policies on the periodic_summaries table ensuring that peer mentors can only SELECT rows where organisation_id matches their active organisation context, coordinators can SELECT all rows within their organisation, and no role can INSERT or UPDATE directly (writes are service-role only). Verify policies block cross-organisation reads completely. epic-periodic-summaries-foundation-task-002

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