Database migration: reminder config columns on org_settings
epic-assignment-follow-up-reminders-foundation-task-003 — Add reminder_days (integer, default 10) and escalation_days (integer, default 14) columns to the org_settings table. Include a check constraint ensuring escalation_days > reminder_days. Apply RLS so only org admins can update these columns for their own org.
Acceptance Criteria
Technical Requirements
Implementation Notes
The CHECK constraint should be defined as `CONSTRAINT chk_escalation_gt_reminder CHECK (escalation_days > reminder_days)`. Since both columns have NOT NULL defaults, existing rows will automatically receive the default values on ALTER TABLE in Postgres 15+ without a separate UPDATE statement — verify this behavior on your Supabase version. If org_settings rows are not guaranteed to exist for all orgs, the ReminderConfigRepository (task-004) must handle the case where fetchConfig returns null and apply defaults in the application layer as well. Coordinate with task-004 to agree on the exact column names and types before writing the migration, as renaming after deployment requires another migration.
Consider adding a comment on the table documenting the business rules: `COMMENT ON COLUMN org_settings.reminder_days IS 'Days since last contact before a reminder notification is triggered (default 10)';`
Testing Requirements
SQL integration tests: (1) Schema test: assert both columns exist with correct types and defaults via information_schema; (2) Default value test: insert a new org_settings row without specifying these columns and assert reminder_days=10 and escalation_days=14; (3) Constraint valid: UPDATE with reminder_days=7, escalation_days=14 — assert success; (4) Constraint violation: UPDATE with reminder_days=14, escalation_days=14 — assert CHECK violation; (5) Constraint violation: UPDATE with reminder_days=15, escalation_days=10 — assert CHECK violation; (6) RLS read: coordinator role can SELECT org_settings for own org; (7) RLS update blocked: coordinator cannot UPDATE reminder_days; (8) RLS update allowed: org_admin can UPDATE reminder_days and escalation_days for own org; (9) RLS org isolation: org_admin cannot UPDATE settings of a different org.
Adding last_contact_date to the assignments table may conflict with existing RLS policies or trigger-based logic that monitors the assignments table. If the migration is not carefully reviewed, existing assignment management features could break in production.
Mitigation & Contingency
Mitigation: Review all existing triggers, policies, and foreign key constraints on the assignments table before writing the migration. Run the migration against a staging Supabase instance with production-like data and execute the full existing test suite before merging.
Contingency: Roll back the migration using Supabase's versioned migration history. Apply the schema change as an additive-only migration (nullable column with default) to ensure zero downtime and reversibility.
The PushNotificationService wraps an existing FCM integration whose internal API contract may have changed or may not expose the payload formatting required for deep-link CTAs. Misalignment discovered late delays the dispatch service epic.
Mitigation & Contingency
Mitigation: Before implementing the wrapper, read the existing push notification integration code and confirm the method signatures, payload structure, and token management model. Agree on a stable interface contract in a shared Dart abstract class.
Contingency: If the existing service is incompatible, implement a thin adapter layer that translates reminder payloads to the existing service's format, isolating the reminder feature from upstream changes.
Incorrect RLS policies on notification_log could allow coordinators to read reminder records belonging to peer mentors in other chapters, exposing sensitive assignment information across organisational boundaries.
Mitigation & Contingency
Mitigation: Write explicit RLS policies with integration tests that assert cross-chapter queries return zero rows. Use Supabase's built-in auth.uid() and join through the org membership tables to scope all queries.
Contingency: If a policy gap is discovered post-merge, immediately disable the affected table's SELECT policy, deploy a corrected policy, and audit recent queries in Supabase logs for any cross-boundary reads.