Implement ScenarioNotificationRepository for record and cooldown persistence

epic-scenario-push-engagement-foundation-task-004 — Implement ScenarioNotificationRepository in Dart with methods: recordNotificationSent(record), updateDeliveryStatus(id, status), getCooldownTimestamp(userId, scenarioType), upsertCooldown(userId, scenarioType, timestamp), and getPendingRetries(). All methods must use the Supabase client and handle network errors with typed exceptions.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

ScenarioNotificationRepository is a Dart class with a Riverpod Provider for DI

recordNotificationSent(ScenarioNotificationRecord record) inserts a row into scenario_notification_records and returns the saved record with server-generated id and sent_at

updateDeliveryStatus(String id, DeliveryStatus status) updates the delivery_status column for the given record id and returns the updated model

getCooldownTimestamp(String userId, String scenarioType) returns a DateTime? — null if no cooldown row exists, DateTime if found

upsertCooldown(String userId, String scenarioType, DateTime timestamp) performs ON CONFLICT DO UPDATE on scenario_notification_cooldowns

getPendingRetries() returns List<ScenarioNotificationRecord> where delivery_status = 'pending' or 'failed' and retry_count < max retry limit (configurable constant)

All methods wrap SupabaseException and network errors in a typed ScenarioNotificationException with an errorCode field

Repository is stateless — no internal caches or mutable fields

All 5 public methods are covered by unit tests with a mocked Supabase client

Technical Requirements

frameworks

Dart

Riverpod

Supabase Dart client

apis

Supabase REST (from(), insert(), update(), upsert(), select(), filter())

Supabase service role (for Edge Function context) or authenticated client (for client-side reads)

data models

ScenarioNotificationRecord (id, userId, scenarioType, sentAt, deliveryStatus, payloadJson, retryCount)

ScenarioNotificationCooldown (userId, scenarioType, lastTriggeredAt)

scenario_notification_records Supabase table

scenario_notification_cooldowns Supabase table

performance requirements

getPendingRetries() query must use the (user_id, scenario_type) composite index — verify with EXPLAIN

getCooldownTimestamp() must perform a single SELECT by PK — no full table scans

upsertCooldown() must complete in under 300ms

security requirements

recordNotificationSent() and updateDeliveryStatus() are service-role-only operations — they must only be called from Edge Function context, never from Flutter client code

getPendingRetries() and getCooldownTimestamp() may be called with authenticated client for read-only display

payloadJson must not include user PII — enforce in the model's toJson serializer

retry_count increment must be atomic — use Supabase RPC or optimistic concurrency if needed

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Define DeliveryStatus as a Dart enum with values pending, delivered, failed, retrying and a toDbString() extension to map to the Supabase CHECK constraint values. Define a MAX_RETRY_COUNT constant (suggested: 3) in a shared constants file — do not hardcode in the query. For upsertCooldown(), use supabase.from('scenario_notification_cooldowns').upsert({'user_id': userId, 'scenario_type': scenarioType, 'last_triggered_at': timestamp.toIso8601String()}, onConflict: 'user_id,scenario_type') — the onConflict parameter must match the composite PK columns exactly. For recordNotificationSent(), always request .select() after insert to get the server-generated id and sent_at back rather than assuming client-side values.

Register the Riverpod provider adjacent to the NotificationPreferencesRepository provider in the same providers.dart file for discoverability.

Testing Requirements

Unit tests with mocked SupabaseClient covering: (1) recordNotificationSent calls insert with correct payload and maps response to model. (2) updateDeliveryStatus calls update with correct id filter and status value. (3) getCooldownTimestamp returns null when Supabase returns empty list. (4) getCooldownTimestamp returns correct DateTime when row exists.

(5) upsertCooldown calls upsert with ON CONFLICT option and correct columns. (6) getPendingRetries filters by delivery_status in ('pending', 'failed') and retry_count < max. (7) Any method wraps SupabaseException into ScenarioNotificationException. Minimum 7 unit tests.

Use a ScenarioNotificationException matcher to assert typed error wrapping.

Component

Scenario Notification Repository

data medium

Dependencies (1)

Create scenario_notification_records table (id, user_id, scenario_type, sent_at, delivery_status, payload_json, retry_count) and scenario_notification_cooldowns table (user_id, scenario_type, last_triggered_at) in Supabase Postgres. Apply RLS policies restricting access to the owning user and service role. Include indexes on user_id and scenario_type for query performance. epic-scenario-push-engagement-foundation-task-002

Epic Risks (3)

high impact medium prob dependency

FCM service account key and APNs certificate configuration may be missing or misconfigured in the Supabase Edge Function secrets store, blocking end-to-end push delivery testing until resolved by the infrastructure owner.

Mitigation & Contingency

Mitigation: Raise a credentials-setup task in the project board at epic start; document the exact secret names required in scenario-evaluation-config so the infrastructure owner can provision them independently of development work.

Contingency: Implement a mock push-notification-dispatcher stub that records payloads to the database for local testing, allowing the rest of the feature to proceed while credentials are obtained.

high impact low prob security

Incorrect RLS policies on the scenario_notifications or notification_preferences tables could allow one user to read or modify another user's notification records, constituting a data privacy breach.

Mitigation & Contingency

Mitigation: Write dedicated RLS policy tests using Supabase's built-in test framework before any application code touches the tables; require a peer security review of all policy definitions before merging.

Contingency: If a policy gap is discovered post-merge, immediately disable the affected table's read policy, notify the security lead, and deploy a hotfix with corrected policies before re-enabling access.

medium impact medium prob dependency

Norwegian Bokmål ARB localisation strings for all scenario message templates may not be available at implementation time, causing content-builder tests to fail and delaying integration.

Mitigation & Contingency

Mitigation: Define all required ARB message keys in a tracked document shared with the content owner at epic kickoff; use English placeholder strings that follow the final format so template injection logic can be tested independently.

Contingency: Ship with English-only strings in the first release and gate Norwegian strings behind a feature flag that is enabled once translations are reviewed and approved.

Quick Links

All Tasks Execution Plan