Persist previously-unlocked milestones for new-unlock detection
epic-annual-impact-summary-core-services-task-009 — Implement the storage layer that persists which milestone IDs a user has previously unlocked, so the MilestoneDetectionService can correctly determine newly-unlocked vs already-held milestones on subsequent Wrapped views. Use the local SQLite cache with a user_milestones table (user_id, milestone_id, first_unlocked_at). Write an upsert method called after each successful detection run.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 5 - 253 tasks
Can start after Tier 4 completes
Implementation Notes
Use sqflite's ConflictAlgorithm.ignore in the insert call inside a batch to achieve INSERT OR IGNORE semantics efficiently. Migration version must be incremented in the existing DatabaseHelper (do not create a new database file). Store first_unlocked_at as DateTime.now().toUtc().toIso8601String() — always UTC. Keep the repository interface behind an abstract class so it can be mocked in BLoC tests.
Do not expose the raw Database object outside the repository. If the project already uses drift/moor, create the table using drift's @DataClassName annotation pattern to stay consistent — check the existing codebase pattern before choosing sqflite directly.
Testing Requirements
Integration tests using flutter_test with sqflite in-memory database: (1) upsert a list of milestone IDs and assert getUnlockedMilestoneIds returns the same set; (2) upsert the same milestone IDs twice and assert no duplicate rows and first_unlocked_at unchanged; (3) upsert for user A and assert user B query returns empty set; (4) upsert empty list and assert no exception and row count unchanged; (5) assert transaction rollback on simulated write failure leaves no partial rows. Unit test: assert upsertUnlockedMilestones calls db.transaction(). Coverage target: all public repository methods.
Activity records may contain duplicate entries (as evidenced by the duplicate-detection feature dependency) or proxy-registered activities that should be attributed differently. Including duplicates or mis-attributed records would produce inflated stats, undermining trust in the summary.
Mitigation & Contingency
Mitigation: Implement the aggregation query to join against the deduplication-reviewed-flag on activity records and filter out unresolved duplicates. Coordinate with the duplicate-detection feature team to confirm the authoritative flag field before implementing the RPC. Include a data-quality warning in the summary when unresolved duplicates are detected.
Contingency: If deduplication state is unreliable at release time, add a prominent disclaimer in the summary UI noting that figures reflect all registered activities and may include duplicates pending review. Track a follow-up task to re-aggregate after deduplication runs.
Each organisation wants to define their own milestone thresholds (e.g., NHF's counting model differs from HLF's certification model). Implementing configurable thresholds may expand scope significantly if the configuration UI is expected in this epic.
Mitigation & Contingency
Mitigation: Scope this epic strictly to the evaluation engine and a hardcoded default threshold set. Define the MilestoneDefinition interface with an organisation_id discriminator so per-org configs can be loaded from the database in a later sprint. Build the admin configuration UI as a separate follow-on task outside this epic.
Contingency: If stakeholders require per-org milestone configuration before launch, deliver a JSON-based configuration file per org as an interim solution, loaded from Supabase storage, until a full admin UI is built.
Android 13+ restricts access to media collections and requires READ_MEDIA_IMAGES permission for gallery saves, while older Android versions use WRITE_EXTERNAL_STORAGE. Handling both permission models correctly across the device matrix is error-prone.
Mitigation & Contingency
Mitigation: Use the permission_handler Flutter package with version-aware permission requests abstracted behind the summary-share-service interface. Write platform-specific unit tests for both Android API levels in the test harness. Test on a minimum of three Android versions (API 29, 32, 34) in CI.
Contingency: If gallery save is broken on specific Android versions at launch, disable the 'Save to gallery' option on affected API levels and surface only clipboard and system share sheet, which require no media permissions.