Define notification preferences repository interface and schema
epic-scenario-push-engagement-core-engine-task-001 — Create the database schema and repository interface for storing per-user notification preferences, including opt-out flags per scenario type and global push opt-out. Implement CRUD operations backed by Supabase with RLS policies ensuring users can only read and write their own preferences.
Acceptance Criteria
Technical Requirements
Implementation Notes
Define a Dart abstract class NotificationPreferencesRepository with the four methods listed in acceptance criteria. Create NotificationPreferences as an immutable data class with copyWith, fromJson, and toJson. Use Supabase's upsert with onConflict: 'user_id' for both create and update operations — this ensures idempotency and avoids race conditions on first-time preference creation. The JSONB column scenario_type_opt_outs should map to a Map
For the default-return behavior, check if the Supabase response list is empty and return NotificationPreferences.defaults() without an extra DB call. Register the repository as a Riverpod provider: final notificationPrefsRepoProvider = Provider
Testing Requirements
Three levels of testing: (1) Unit tests — mock the Supabase client and verify that getPreferences returns a default object when the mock returns null/empty, and that upsertPreferences passes the correct payload including onConflict parameter. (2) Integration tests — against the Supabase test project: insert a preferences row, read it back, update global_push_opt_out, verify RLS blocks cross-user read (authenticate as user A and attempt to read user B's row, expect empty result). (3) RLS policy test — dedicated test that authenticates as two different test users and asserts isolation. Run unit tests with flutter test, integration tests with integration_test package.
Coverage target: >= 90% for the repository implementation class.
The scenario-edge-function-scheduler must evaluate all active peer mentors within the 30-second Supabase Edge Function timeout. For large organisations, a sequential evaluation loop may exceed this limit, causing partial runs and missed notifications.
Mitigation & Contingency
Mitigation: Design the trigger engine to batch mentor evaluations using database-side SQL queries (bulk inactivity check via a single query rather than per-mentor calls), and add a performance test against 500 mentors during development. Document the evaluated mentor count per scenario type in scenario-evaluation-config to allow selective scenario execution per run.
Contingency: If single-run execution is insufficient, split evaluation into per-scenario-type scheduled functions (inactivity check, milestone check, expiry check) on separate cron schedules, dividing the computational load across multiple invocations.
A race condition between concurrent scheduler invocations or retried cron triggers could cause the same scenario notification to be dispatched multiple times to a mentor, severely degrading trust in the feature.
Mitigation & Contingency
Mitigation: Implement cooldown enforcement using a database-level upsert with a unique constraint on (user_id, scenario_type, cooldown_window_start) so that a second invocation within the same window is rejected at the persistence layer rather than the application layer.
Contingency: Add an idempotency key derived from (user_id, scenario_type, evaluation_date) to the notification record insert; if a duplicate key violation is caught, log it as a warning and skip dispatch without error.
The trigger engine queries peer mentor activity history across potentially multiple organisations and chapters. RLS policies configured for app-user roles may block the Edge Function's service-role queries, or query performance may degrade on large activity tables.
Mitigation & Contingency
Mitigation: Confirm the Edge Function runs with the Supabase service role key (bypassing RLS) and add composite indexes on (user_id, activity_date) to the activity tables before implementing the inactivity detection query.
Contingency: If service-role access is restricted by organisational policy, implement a dedicated database function (SECURITY DEFINER) that performs the inactivity aggregation and is callable by the Edge Function with limited scope.