critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

PromptHistoryRepository abstract interface defined with concrete SupabasePromptHistoryRepository implementation
recordPromptSent(activityId, ruleId, sentAt) inserts a row into scenario_prompt_history and returns the persisted PromptHistoryRecord with server-generated id
recordPromptSent is idempotent when called with the same (activityId, ruleId) pair — second call returns the existing record without inserting a duplicate
fetchRecentPrompts(mentorId, windowHours) returns all PromptHistoryRecord rows for the given mentor where sentAt > now() - windowHours, ordered by sentAt descending
hasPromptBeenSent(activityId, ruleId) returns true if a row exists in scenario_prompt_history for that (activityId, ruleId) pair, false otherwise
hasPromptBeenSent completes within 200ms for normal Supabase latency (uses a COUNT or EXISTS query, not a full fetch)
All three methods throw PromptHistoryRepositoryException with a typed error code on Supabase errors
Riverpod provider promptHistoryRepositoryProvider exposed and injectable for testing
PromptHistoryRecord domain model defined with fields: id, activityId, ruleId, mentorId, sentAt
Unit tests cover all three methods with mocked Supabase client

Technical Requirements

frameworks
Flutter
Dart
Riverpod
Supabase
apis
Supabase PostgREST: POST /scenario_prompt_history (insert with ON CONFLICT DO NOTHING on activity_id + rule_id)
Supabase PostgREST: GET /scenario_prompt_history?mentor_id=eq.{id}&sent_at=gte.{windowStart}&order=sent_at.desc
Supabase PostgREST: HEAD /scenario_prompt_history?activity_id=eq.{id}&rule_id=eq.{id} with Prefer: count=exact for hasPromptBeenSent
data models
PromptHistoryRecord
ScenarioRule
performance requirements
hasPromptBeenSent must use a server-side EXISTS/COUNT check — must not fetch the full row set and filter in Dart
fetchRecentPrompts for a 72-hour window with up to 200 records must return within 600ms
scenario_prompt_history table must have a composite unique index on (activity_id, rule_id) and a separate index on (mentor_id, sent_at DESC)
security requirements
Supabase RLS on scenario_prompt_history must restrict reads to the authenticated mentor's own records (mentor_id = auth.uid())
recordPromptSent must only be callable server-side or by the authenticated mentor themselves — prevent one mentor from recording prompts on behalf of another
Cooldown enforcement is a data-integrity concern: the ON CONFLICT DO NOTHING constraint is the last line of defense against duplicate prompts reaching users

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

The idempotency of recordPromptSent is critical — the Rule Engine may be triggered multiple times for the same activity (e.g., app restarted, push notification retry). Implement this using a Supabase upsert with onConflict: 'activity_id,rule_id' and ignoreDuplicates: true, or a raw INSERT ... ON CONFLICT DO NOTHING. For hasPromptBeenSent, use a HEAD request with Prefer: count=exact to get just the count without fetching row data — this is the most efficient pattern for an existence check in Supabase PostgREST.

The mentorId parameter in fetchRecentPrompts should always be the currently authenticated user's UID (auth.currentUser.id) — do not accept arbitrary mentorIds from the UI layer, as this would bypass RLS intent. The windowHours parameter maps directly to the ScenarioRule.delayMaxHours: the Rule Engine queries prompt history within the maximum possible delay window to determine whether a prompt has already been sent during any valid delay slot.

Testing Requirements

Write unit tests in test/features/scenario_prompts/repositories/prompt_history_repository_test.dart using a mocked Supabase client. Cover: (1) recordPromptSent inserts and returns correct PromptHistoryRecord, (2) recordPromptSent called twice with same activityId+ruleId returns existing record (idempotency), (3) fetchRecentPrompts returns only records within the window (test with records inside and outside the window), (4) fetchRecentPrompts returns empty list when no recent prompts exist, (5) hasPromptBeenSent returns true when a matching record exists, (6) hasPromptBeenSent returns false when no matching record exists, (7) Supabase error maps to PromptHistoryRepositoryException. Write one integration test against local Supabase verifying the unique constraint and RLS policies.

Component
Scenario Deep Link Handler
service medium
Epic Risks (2)
high impact medium prob scope

The Rule Engine must support a flexible JSON rule schema that can express compound conditions (e.g., contact_type AND wellbeing_flag AND delay_days). Underestimating schema expressiveness may require breaking changes to the rule format after coordinators have already configured rules.

Mitigation & Contingency

Mitigation: Define and freeze the rule JSON schema (trigger_type enum, metadata_conditions structure, delay logic) before any implementation begins; validate schema against all known HLF scenarios documented in the feature spec.

Contingency: If schema changes are needed after deployment, implement a schema version field and a migration utility that upgrades stored rules to the new format without coordinator intervention.

medium impact medium prob technical

Deep-link navigation to the activity wizard with pre-filled arguments may fail if the user's session has expired or if the wizard route is not yet mounted in the navigator stack, causing unhandled navigation exceptions.

Mitigation & Contingency

Mitigation: Implement session state check before navigation; if session is expired, redirect to biometric/login screen and store the pending deep-link URI for post-auth redirect using go_router's redirect mechanism.

Contingency: If post-auth redirect proves unreliable, fall back to navigating to the home screen with a visible action banner that re-triggers the wizard with pre-filled arguments.