Create scenario notification repository and DB schema
epic-scenario-push-engagement-core-engine-task-002 — Design and implement the database table and repository for scenario notifications, tracking which notifications have been sent to which users, with timestamps, scenario type, and delivery status. Include indexes for efficient cooldown lookups by user_id and scenario_type.
Acceptance Criteria
Technical Requirements
Implementation Notes
Define `scenario_type` as a PostgreSQL CHECK constraint against a fixed set of values ('inactivity', 'milestone', 'certification_expiry', 'welcome_back') — do not use a separate lookup table to keep the schema lean. The `milestone_value` column stores the integer threshold that was crossed (e.g. 50) so the evaluator can later query 'has milestone=50 already been sent?' without parsing metadata. Use `timestamptz` (not `timestamp`) for all time fields so cooldown math is timezone-correct for Norwegian users.
In the Dart repository, wrap all Supabase calls in try/catch and rethrow as a typed `ScenarioRepositoryException` — never let raw PostgrestException propagate to callers. Consider adding a partial index on `(user_id, scenario_type)` WHERE `delivery_status = 'sent'` to speed up the most common cooldown query pattern.
Testing Requirements
Unit tests using flutter_test and a mock SupabaseClient: verify `getLastSentAt` returns null when no prior records exist; verify it returns the most recent `triggered_at` when multiple rows exist for the same (user_id, scenario_type); verify `hasBeenSentSinceThreshold` returns true when a record exists within the window and false when outside. Integration test against a local Supabase instance: insert a row, query it back, verify RLS blocks cross-user reads using a second auth token. SQL migration tested in CI by running the migration twice (idempotency check). All tests must run without network access to production Supabase.
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.