Implement GeneratedReportsRepository class
epic-bufdir-report-export-foundation-task-002 — Build the Dart repository class that wraps all CRUD operations for the generated_reports table. Implement methods: createAuditRecord(ExportRequest), updateStatus(reportId, status, errorMessage), getReportsByOrg(orgId, dateRange), getReportById(reportId), and deleteOldReports(retentionDays). Use Supabase client with typed response models. Repository must insert an audit record before any export attempt begins so failures are also captured.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Follow the existing repository pattern in the project (check other repository classes for naming conventions and Supabase client usage). Use `supabase.from('generated_reports').insert(data).select().single()` for createAuditRecord to get the server-generated UUID back in one round trip. For updateStatus, use `.update(data).eq('report_id', reportId).select().single()` — do not do a separate fetch after update. The `deleteOldReports` method is only called by a background maintenance process — it should use the service role client (passed as a parameter or injected via a separate provider) not the user-auth client, because RLS blocks DELETE for user roles.
Model fromJson/toJson should handle ISO 8601 date strings from Supabase, converting to Dart `DateTime` with `DateTime.parse()`. Register as a Riverpod `Provider
Testing Requirements
Unit tests using flutter_test with a mocked SupabaseClient. Test each method: (1) createAuditRecord — verify correct INSERT payload is sent and returned model is correctly deserialized, (2) updateStatus — verify only specified fields are included in the UPDATE, (3) getReportsByOrg — verify query includes correct org_id filter, ordering, and pagination params, (4) getReportById — verify query filters by report_id and handles null return, (5) deleteOldReports — verify DELETE filters by date threshold and status IN ('completed', 'failed'). Test error paths: simulate PostgrestException and assert RepositoryException is thrown with a meaningful message. Place tests in `test/data/repositories/generated_reports_repository_test.dart`.
NHF's three-level hierarchy (national / region / chapter) with 1,400 chapters may have edge cases such as chapters belonging to multiple regions, orphaned nodes, or missing parent links in the database. Incorrect scope expansion would silently under- or over-report activities, which could invalidate a Bufdir submission.
Mitigation & Contingency
Mitigation: Obtain a full hierarchy fixture export from NHF before implementation begins. Write exhaustive unit tests covering boundary cases: single chapter, full national roll-up, chapters with no activities, and chapters assigned to multiple regions. Validate resolver output against a known-good manual count.
Contingency: If hierarchy data quality is too poor for automated resolution at launch, implement a manual scope override in the coordinator UI that allows the coordinator to explicitly select org units from a tree picker, bypassing the resolver.
The activity_type_configuration table may not cover all activity types currently in use, leaving a subset unmapped at launch. Bufdir submissions with unmapped categories will be incomplete and may be rejected by Bufdir.
Mitigation & Contingency
Mitigation: Run a query against production activity data before implementation to enumerate all distinct activity type IDs. Cross-reference with Bufdir's published category schema (request from Norse Digital Products). Flag every gap as a known issue and build the warning surface into the preview panel.
Contingency: Implement a fallback 'Other' category bucket for unmapped types and surface a prominent warning in the export preview requiring coordinator acknowledgement before proceeding. Log unmapped types for post-launch cleanup.
Supabase RLS policies on generated_reports and the storage bucket must enforce strict org isolation. A misconfigured policy could allow a coordinator from one organisation to read another organisation's export files, creating a serious data breach with GDPR implications.
Mitigation & Contingency
Mitigation: Write RLS integration tests that attempt cross-org reads with explicitly different JWT tokens and assert that all attempts return empty sets or 403 errors. Include RLS policy review in the pull request checklist. Use Supabase's built-in policy tester during development.
Contingency: If a policy gap is discovered post-deployment, immediately revoke all signed URLs for affected exports, audit the access log for unauthorised reads, and issue a coordinated disclosure to affected organisations per GDPR breach notification requirements.