medium priority low complexity database pending database specialist Tier 3

Acceptance Criteria

generated_reports table has a bufdir_reference_number column of type TEXT, nullable (null for file-based exports that have no reference number)
A non-unique index exists on generated_reports(bufdir_reference_number) to support coordinator lookup queries
GeneratedReportsRepository.updateBufdirReferenceNumber(reportId, referenceNumber) persists the value via Supabase and returns updated record
The update is called immediately after BufdirSubmissionResult.success is received and before showing the confirmation screen
If the update fails (e.g., network drop after submission), the error is logged but does not surface as a submission failure to the user — the submission itself succeeded
Existing rows with no reference number remain valid (nullable column, no NOT NULL constraint)
RLS policy on generated_reports restricts writes to the submitting coordinator's organisation only
Coordinator export history screen displays the bufdir_reference_number when present, with a 'N/A' fallback for file-based exports
Migration script is idempotent — running it twice does not error or duplicate the column/index
Supabase migration file is versioned with a timestamp prefix and committed to the migrations directory

Technical Requirements

frameworks
Flutter
Dart
Riverpod
apis
Supabase PostgreSQL 15
Supabase Auth
data models
bufdir_export_audit_log
performance requirements
Index lookup by bufdir_reference_number must return within 10ms for tables up to 100k rows
Update operation must complete within 2 seconds under normal network conditions
security requirements
RLS policy enforces organisation-scoped write access — coordinators cannot write to another org's records
bufdir_reference_number is a non-sensitive identifier and does not require encryption at rest
Supabase service role key never used from mobile client — all writes use the user's JWT

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Integration Task

Handles integration between different epics or system components. Requires coordination across multiple development streams.

Implementation Notes

Add the column via a Supabase migration file: `ALTER TABLE generated_reports ADD COLUMN IF NOT EXISTS bufdir_reference_number TEXT; CREATE INDEX IF NOT EXISTS idx_generated_reports_bufdir_ref ON generated_reports(bufdir_reference_number);`. In GeneratedReportsRepository, add a dedicated `updateBufdirReferenceNumber` method rather than reusing a generic `update` — this makes the intent explicit and avoids accidentally overwriting other fields. Call this method in the BLoC/Cubit submission handler in a try/catch that logs failures but does not rethrow — the reference number write is best-effort after a successful submission. In the export history UI, conditionally render the reference number badge using `if (report.bufdirReferenceNumber != null)`.

Testing Requirements

Unit tests for GeneratedReportsRepository.updateBufdirReferenceNumber using a Supabase mock or in-memory fake: (1) successful update returns updated record with reference number set, (2) update on non-existent reportId returns error without crashing, (3) null reference number accepted (file-based export path). Integration test against Supabase test project: verify column exists, RLS prevents cross-org write, index present via pg_indexes query. Migration script tested by running against a clean schema and verifying idempotency on second run.

Component
Bufdir API Client
service high
Epic Risks (2)
medium impact high prob dependency

Norse Digital Products has not yet completed API negotiations with Bufdir. If negotiations stall or Bufdir's API design diverges significantly from expectations, the API client may need substantial rework, or the epic may be blocked indefinitely.

Mitigation & Contingency

Mitigation: Implement the client against a locally defined stub of the expected Bufdir API schema. Isolate all Bufdir-specific schema mapping in a single adapter class so that changes to the actual API schema require changes in only one place. Keep the epic in 'interface-ready' status until real API credentials are available for integration testing.

Contingency: If API negotiations are not completed within the planned window, defer this epic without impact on any other epic — the PDF/CSV fallback path from Epics 1–4 delivers full standalone value. Mark the epic as blocked and resurface when negotiations conclude.

high impact low prob security

Bufdir API credentials stored in the application or edge function environment could be exposed through misconfigured secrets management, log leakage, or a compromised deployment pipeline, allowing unauthorised Bufdir submissions on behalf of the organisation.

Mitigation & Contingency

Mitigation: Store all Bufdir API credentials exclusively in Supabase Vault (or the integration credential vault component), never in client-side code or environment variables accessible to the Flutter app. Transmit credentials only from within the edge function, not from the Flutter client. Implement credential rotation support from the outset.

Contingency: If a credential leak is detected, immediately revoke and rotate the affected API credentials through Bufdir's credential management portal, audit submission logs for any unauthorised calls, and notify Bufdir's technical contact per the API agreement's security incident clause.