Create scenario_rules database migration

epic-scenario-based-follow-up-prompts-foundation-task-001 — Write and apply a Supabase database migration that creates the scenario_rules table. The table must store JSON-configured rule definitions per chapter, including columns for rule_id, chapter_id, scenario_type, trigger_conditions (JSONB), prompt_content (JSONB), active flag, and timestamps. Include appropriate indexes on chapter_id and scenario_type for efficient lookups.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file exists in supabase/migrations/ with a timestamped filename (e.g., 20260330000000_create_scenario_rules.sql)

scenario_rules table is created with columns: rule_id (UUID, primary key, default gen_random_uuid()), chapter_id (UUID, not null), scenario_type (TEXT, not null), trigger_conditions (JSONB, not null), prompt_content (JSONB, not null), active (BOOLEAN, not null, default true), created_at (TIMESTAMPTZ, not null, default now()), updated_at (TIMESTAMPTZ, not null, default now())

An index exists on chapter_id (idx_scenario_rules_chapter_id)

An index exists on scenario_type (idx_scenario_rules_scenario_type)

A composite index exists on (chapter_id, active) for the common query pattern of fetching active rules per chapter

RLS is enabled on the table with at least one policy allowing authenticated users to SELECT their own chapter's rules

A trigger or policy ensures updated_at is automatically set to now() on UPDATE

Migration applies cleanly on a fresh Supabase project without errors

A corresponding rollback migration (down migration) exists or the migration is annotated with DROP TABLE instructions for reversal

scenario_type column has a CHECK constraint limiting values to the defined enum set (e.g., post_initial_contact, post_long_session, wellbeing_flag_raised, no_contact_after_10_days, post_group_activity)

Technical Requirements

frameworks

Supabase

PostgreSQL

apis

Supabase Migrations CLI (supabase db push)

Supabase Dashboard

data models

scenario_rules

ScenarioRule

performance requirements

Index on chapter_id must support queries returning < 50ms for chapters with up to 100 rules

JSONB columns (trigger_conditions, prompt_content) should have GIN indexes if JSON field queries will be used in WHERE clauses

Table must handle up to 10,000 rules across all chapters without performance degradation

security requirements

RLS must be enabled — no public access without authentication

SELECT policy must scope results to the authenticated user's accessible chapters (join against a chapters or memberships table)

INSERT/UPDATE/DELETE policies must restrict to coordinator or admin roles

rule_id must use UUID (not sequential integer) to prevent enumeration attacks

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Use supabase/migrations/ directory structure with Supabase CLI conventions. The updated_at auto-update can be implemented with a PostgreSQL trigger function (moddatetime extension if available, or a custom trigger). Define the scenario_type CHECK constraint as an exhaustive list matching the EvaluationResult enum cases — this creates a contract between the database and Dart code. For JSONB columns, document the expected schema in a SQL comment on the column (COMMENT ON COLUMN scenario_rules.trigger_conditions IS '...') rather than enforcing full JSON schema validation in PostgreSQL (too complex).

The RLS policy pattern should follow the existing patterns already established in the project for other tables (check existing migrations for the chapter access control approach). If the project uses a chapters table with a members junction, the RLS SELECT policy should be: USING (chapter_id IN (SELECT chapter_id FROM chapter_members WHERE user_id = auth.uid())).

Testing Requirements

Write a migration smoke test that: (1) applies the migration to the Supabase test instance; (2) inserts one valid row for each scenario_type enum value and asserts INSERT succeeds; (3) attempts to insert a row with an invalid scenario_type and asserts CHECK constraint violation; (4) queries by chapter_id and asserts the correct rows are returned; (5) asserts EXPLAIN ANALYZE for a chapter_id lookup uses the index (not a seq scan). Run these via the Supabase test project in CI. Additionally verify RLS by attempting a SELECT as an unauthenticated user and asserting zero rows returned.

Component

Scenario Rule Repository

data medium

Epic Risks (2)

high impact medium prob security

Supabase RLS policies for chapter-scoped rule access may interact unexpectedly with service-role keys used by the Edge Function, potentially blocking backend reads or leaking cross-chapter data.

Mitigation & Contingency

Mitigation: Write and review RLS policies in isolation with automated policy tests before merging; define a dedicated service-role bypass policy scoped to the edge function's Postgres role.

Contingency: If RLS blocks the edge function, temporarily use a bypass policy with audit logging while a permanent fix is implemented; escalate to a Supabase security review.

medium impact high prob integration

FCM device tokens become invalid when users reinstall the app or revoke permissions; stale tokens cause silent delivery failures that are hard to detect without explicit error handling.

Mitigation & Contingency

Mitigation: Implement token invalidation handling in PushNotificationDispatcher that removes stale tokens from the database on FCM 404/410 responses; log all delivery failures with structured output.

Contingency: If token hygiene proves unreliable, add a periodic token refresh job that re-registers all active users' tokens via the FCM registration endpoint.

Quick Links

All Tasks Execution Plan