Scenario Prompt
Data Entity
Description
A generated notification instance created when a scenario rule's trigger conditions are satisfied for a specific peer mentor. Tracks whether the prompt was acted upon, dismissed, or remains pending. Prompt history is used for deduplication during daily evaluation to prevent notification fatigue from repeated identical triggers.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for this prompt instance, generated server-side on creation | PKrequiredunique |
scenario_rule_id |
uuid |
Foreign key referencing the scenario_rule that triggered this prompt. Used for deduplication and cooldown enforcement | required |
peer_mentor_id |
uuid |
Foreign key referencing the peer mentor (likeperson) this prompt targets. Combined with scenario_rule_id and activity_id to enforce deduplication | required |
activity_id |
uuid |
Nullable foreign key referencing the specific activity that triggered prompt generation. Enables deep-link navigation to the related activity and participates in the deduplication composite key | - |
chapter_id |
uuid |
Foreign key referencing the organization chapter this prompt belongs to, used for RLS scoping and coordinator visibility | required |
status |
enum |
Lifecycle state of the prompt. pending: delivered but no user action yet. acted: user tapped the CTA and was navigated to the relevant wizard. dismissed: user explicitly dismissed the prompt | required |
scenario_type |
string |
Denormalized scenario type identifier (e.g. 'inactivity_21_days', 'milestone_50_sessions', 'cert_expiry_30_days') copied from the triggering rule for fast filtering without a join | required |
notification_payload |
json |
Rendered push and in-app notification content at time of generation: {title: string, body: string, deep_link_route: string, context_data: object}. Stores the resolved message so it remains accurate even if the rule template changes later | required |
deep_link_route |
string |
Pre-resolved go_router route string used by scenario-deep-link-handler to navigate the user to the correct wizard or screen when the CTA is tapped. Mirrors the route stored inside notification_payload for direct access | - |
sent_at |
datetime |
UTC timestamp when the prompt was generated and the push notification dispatched. Used as the baseline for cooldown period calculations in subsequent evaluation runs | required |
acted_at |
datetime |
UTC timestamp when the peer mentor tapped the primary CTA and was navigated to the target screen. Null until the acted transition occurs | - |
dismissed_at |
datetime |
UTC timestamp when the peer mentor explicitly dismissed the prompt. Null until the dismissed transition occurs | - |
push_delivered |
boolean |
Whether the FCM/APNs push notification was successfully dispatched for this prompt. False if the user had no registered device token or if push delivery failed | required |
evaluation_run_id |
uuid |
Identifier of the scheduled evaluation run that produced this prompt. Supports batch-level auditing and debugging of the scenario evaluation edge function | - |
created_at |
datetime |
UTC timestamp of record insertion. Identical to sent_at for prompts created in real-time; may differ slightly due to processing lag in batch runs | required |
updated_at |
datetime |
UTC timestamp of the last status update. Automatically maintained by a Supabase trigger on any row mutation | required |
Database Indexes
idx_scenario_prompt_dedup
Columns: scenario_rule_id, peer_mentor_id, activity_id
Composite index for the deduplication query executed before each prompt creation. The scheduler checks this index to determine if a prompt for the same rule + mentor + activity combination was already sent within the cooldown window
idx_scenario_prompt_peer_mentor_status
Columns: peer_mentor_id, status
Supports getActivePromptsForUser queries in prompt-history-repository, allowing the notification centre to fetch all pending prompts for a given mentor efficiently
idx_scenario_prompt_sent_at
Columns: sent_at
Supports time-range queries when the scheduler fetches recently sent prompts to enforce per-rule cooldown periods across the look-back window
idx_scenario_prompt_chapter_status
Columns: chapter_id, status
Enables coordinator-scoped queries for active prompts within a chapter, used by admin dashboards and RLS-enforced coordinator visibility
idx_scenario_prompt_scenario_rule_sent_at
Columns: scenario_rule_id, peer_mentor_id, sent_at
Supports cooldown enforcement: hasPromptBeenSent queries filter by scenario_rule_id + peer_mentor_id and order by sent_at DESC to find the most recent prompt for that rule
idx_scenario_prompt_activity_id
Columns: activity_id
Supports reverse lookup from an activity to all prompts it triggered, used by scenario-prompt-detail-sheet when rendering activity context
Validation Rules
valid_scenario_rule_reference
error
Validation failed
valid_peer_mentor_reference
error
Validation failed
valid_activity_reference_when_provided
error
Validation failed
notification_payload_schema
error
Validation failed
sent_at_not_in_future
error
Validation failed
scenario_type_max_length
error
Validation failed
mutual_exclusion_acted_dismissed_timestamps
error
Validation failed
deep_link_route_format
warning
Validation failed
Business Rules
deduplication_before_create
Before inserting a new scenario_prompt, the scheduler must verify that no existing prompt with the same (scenario_rule_id, peer_mentor_id, activity_id) tuple was sent within the rule's configured cooldown period. If a matching prompt exists within the cooldown window, the new prompt is silently dropped to prevent notification fatigue
status_transition_immutability
Once a prompt transitions from 'pending' to either 'acted' or 'dismissed', it cannot transition to any other state. The acted and dismissed states are terminal. Attempts to update a non-pending prompt's status are rejected
acted_at_requires_acted_status
acted_at must be null when status is 'pending' or 'dismissed'. It must be populated when status transitions to 'acted'. The timestamp must be equal to or after sent_at
dismissed_at_requires_dismissed_status
dismissed_at must be null when status is 'pending' or 'acted'. It must be populated when status transitions to 'dismissed'. The timestamp must be equal to or after sent_at
only_active_rules_generate_prompts
A scenario_prompt may only be created if the referenced scenario_rule is currently enabled for the chapter_id. Prompts from disabled rules must not be created, even if trigger conditions are met
peer_mentor_must_be_active
Prompts must only be generated for peer mentors with an active lifecycle status. Paused, resigned, or expired-certification mentors must not receive scenario prompts
notification_payload_must_be_rendered_at_creation
The notification_payload JSON must be fully resolved at insert time, including the rendered title, body, and deep_link_route. Storing a template reference is not permitted — if the rule template changes later, existing prompts retain their original rendered content
chapter_rls_scope_enforcement
Row-level security policies ensure coordinators can only read scenario_prompt records belonging to their assigned chapter_id. Peer mentors can only read their own prompts. The scenario evaluation edge function uses the service role key to bypass RLS during batch writes
CRUD Operations
Storage Configuration
Entity Relationships
A peer mentor receives scenario prompts when the rule engine detects trigger conditions in their activity data
A scenario prompt may reference the specific activity that triggered its generation, enabling deep-link navigation to the related activity record
A scenario rule generates multiple prompt instances over time as its trigger conditions are met for different peer mentors