Implement metric snapshot persistence and cache
epic-bufdir-data-aggregation-data-layer-task-007 — Implement the write and cache operations in the Bufdir Metrics Repository: saveMetricSnapshot (persist computed aggregation results to Supabase for audit trail and caching), getMetricSnapshot (retrieve previously computed snapshot by org and period, enabling instant re-display of past results), and deleteMetricSnapshot (cleanup). Include cache-hit logic so the repository returns the stored snapshot when available instead of triggering a new expensive RPC run.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 5 - 253 tasks
Can start after Tier 4 completes
Implementation Notes
Use Supabase's `.upsert()` with `onConflict: 'org_id,period_start,period_end'` for idempotent saves. Store the snapshot as a JSONB column so Supabase can index into it if needed later. Define a MetricSnapshot Dart class with fromJson/toJson using json_serializable. The cache-hit check in getMetricSnapshot should be a simple database lookup — do not introduce an in-memory LRU cache at this stage unless the codebase already has one, as that adds complexity without clear benefit given Supabase's speed for indexed single-row lookups.
For the migration, add the bufdir_metric_snapshots table creation alongside the RLS policies in the same migration file as other Bufdir tables to keep schema changes atomic.
Testing Requirements
Unit tests (flutter_test + mockito): (1) saveMetricSnapshot calls Supabase upsert with correct columns and conflict target, (2) getMetricSnapshot returns Right(Some(snapshot)) on cache hit, (3) getMetricSnapshot returns Right(None) on cache miss (empty result), (4) deleteMetricSnapshot returns Right(unit) on success and Left(QueryFailure) on missing record, (5) cache-hit path invokes zero calls to AggregationQueryBuilder mock, (6) PostgrestException from save/delete maps to Left(QueryFailure). Integration test against Supabase staging: verify upsert idempotency, RLS blocks cross-org reads, and snapshot survives repository re-instantiation.
Supabase RPC functions return JSON with PostgreSQL numeric types (bigint, numeric) that do not map cleanly to Dart int/double. Silent truncation or JSON parsing errors could corrupt participant counts in the final Bufdir submission without any runtime exception.
Mitigation & Contingency
Mitigation: Define explicit Dart fromJson factories for all RPC result models with type-safe parsing and assertion checks. Add a contract test that compares raw RPC JSON output against expected Dart model values using a known seed dataset.
Contingency: If type mismatches are found in production metrics, expose a validation endpoint in BufdirMetricsRepository that re-fetches and compares raw RPC output against the persisted snapshot, flagging any discrepancies before export proceeds.
Persisted metric snapshots can become stale if additional activities are registered after the snapshot is saved but before the export is finalized. Coordinators might unknowingly export data that does not reflect the latest activity registrations.
Mitigation & Contingency
Mitigation: Store a snapshot_generated_at timestamp and a record_count_at_generation field in the snapshot. When the coordinator views cached results, compare the current activity count for the period against the snapshot value and display a 'Data updated since last aggregation — re-run?' warning if counts differ.
Contingency: Add a mandatory staleness check before the export confirmation dialog can proceed: if the snapshot is more than 24 hours old or the record count has changed, require the coordinator to re-run aggregation before the export button is enabled.