Database migration: last_contact_date on assignments
epic-assignment-follow-up-reminders-foundation-task-002 — Add the last_contact_date timestamp column to the assignments table via a migration script. Include an index on (org_id, last_contact_date) for efficient overdue-query performance. Apply RLS so peer mentors only read their own assignment rows and coordinators read all rows within their org.
Acceptance Criteria
Technical Requirements
Implementation Notes
Since assignments is an existing table, use `ALTER TABLE assignments ADD COLUMN IF NOT EXISTS last_contact_date TIMESTAMPTZ;` — never add a NOT NULL column without a DEFAULT on an existing table with data, as it causes a full table rewrite in older Postgres versions (Supabase runs Postgres 15+, so it's safe but adds lock time). Create the index CONCURRENTLY if the assignments table is expected to have significant existing data: `CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_assignments_org_contact ON assignments(org_id, last_contact_date);` — note that CONCURRENTLY cannot run inside a transaction, so it must be a separate migration step. For RLS, check whether assignments table already has policies; if so, ADD policies using CREATE POLICY with unique names rather than replacing existing ones. The overdue query in AssignmentContactTrackingRepository (task-006) will use `last_contact_date < NOW() - INTERVAL '${thresholdDays} days'` — NULLs (never-contacted) should be treated as overdue by also including `OR last_contact_date IS NULL` in the query logic.
Testing Requirements
SQL integration tests: (1) Schema test: assert last_contact_date column exists with correct type using information_schema.columns; (2) Null default test: insert a new assignment row and assert last_contact_date IS NULL; (3) RLS peer mentor isolation: create two peer mentors in same org, assert mentor A cannot SELECT mentor B's assignment; (4) RLS coordinator access: assert coordinator can SELECT all assignments within org but not outside org; (5) UPDATE authorization: assert peer mentor can update last_contact_date on own row, assert 0 rows updated when targeting another mentor's row; (6) Performance test: EXPLAIN ANALYZE the overdue query and assert Index Scan (not Seq Scan) on the compound index; (7) Idempotency: run migration twice without error.
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.