Author Supabase RLS policy suppressing expired mentor visibility

epic-certificate-expiry-notifications-data-foundation-task-009 — Write and apply the Row Level Security policy on the peer_mentors and related public-facing tables that automatically excludes any mentor whose certification has expired. The policy must apply to coordinator queries and public chapter website queries at the database level. Implement as a Supabase SQL migration with a policy predicate that joins against certification_expiry_tracking and checks expiry_date > NOW(). Document the policy logic thoroughly.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

A Supabase SQL migration file exists at `supabase/migrations/<timestamp>_expiry_rls_policy.sql` and applies cleanly via `supabase db push`

The RLS policy on `peer_mentors` is named `suppress_expired_mentors_select` and targets the SELECT operation for the `authenticated` and `anon` roles

The policy predicate joins `certification_expiry_tracking` on `peer_mentor_id = peer_mentors.id` and checks `expiry_date > NOW()` OR no expiry record exists (mentor with no cert is visible — this must be a deliberate, documented choice)

Alternatively if all active mentors must have a certification: the predicate requires a matching row with `expiry_date > NOW()` (no row = hidden). This business rule must be confirmed and documented in the migration comment.

The policy does NOT block `service_role` queries (admin bypass is preserved via Supabase's built-in service role RLS bypass)

The `postgres` role (used by Edge Functions with service_role key) can still query expired mentors for notification scheduling

The policy applies to the `anon` role so that public chapter website queries (unauthenticated) also suppress expired mentors

Migration includes a rollback section (or a separate down migration file) that drops the policy cleanly

Migration file header contains a block comment explaining: what the policy does, which roles it affects, the business rule it enforces, and the date it was authored

Technical Requirements

frameworks

Supabase (PostgreSQL RLS)

Supabase CLI for migration management

apis

Supabase SQL migration system

PostgreSQL RLS (CREATE POLICY)

Supabase PostgREST role system (anon, authenticated, service_role)

data models

peer_mentors

certification_expiry_tracking (expiry_date, peer_mentor_id, is_expired flag)

performance requirements

The RLS predicate join must use an index on certification_expiry_tracking(peer_mentor_id) to avoid sequential scans on large mentor lists

Query plan for `SELECT * FROM peer_mentors` as authenticated role must not show Seq Scan on certification_expiry_tracking — verify with EXPLAIN ANALYZE in migration comment

Policy evaluation overhead must be under 5ms per query on a table with 10,000 mentor rows

security requirements

Policy must be defined for BOTH `anon` AND `authenticated` roles — a single policy covering both or two separate policies

service_role bypass must be explicitly tested (do not assume it — verify in task-010)

The `expiry_date > NOW()` comparison must use the database server time (NOW()), never a client-supplied timestamp

Policy must be idempotent: the migration uses `DROP POLICY IF EXISTS` before `CREATE POLICY` to be re-runnable in dev environments

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Use a PostgreSQL security-barrier view as an alternative approach if the JOIN in the RLS predicate causes performance issues — a view named `active_peer_mentors` with the filter baked in can supplement the RLS policy for high-read public endpoints. For the policy definition, prefer an EXISTS subquery over a JOIN for clarity: `EXISTS (SELECT 1 FROM certification_expiry_tracking cet WHERE cet.peer_mentor_id = peer_mentors.id AND cet.expiry_date > NOW())`. This is more explicit and avoids potential duplicate rows from the join if a mentor has multiple certification records. Confirm with the team whether a mentor with NO certification_expiry_tracking row should be treated as active or inactive — this is a critical business rule that affects the predicate.

The Supabase CLI migration naming convention is `_.sql` where timestamp is `YYYYMMDDHHmmss`. Ensure the index `CREATE INDEX IF NOT EXISTS idx_cert_expiry_mentor_id ON certification_expiry_tracking(peer_mentor_id, expiry_date)` is part of the same migration.

Testing Requirements

Testing is covered by the dedicated task-010. However, this task must include a manual verification section in the migration file's comment block showing sample SQL that validates the policy works as intended: (1) a query as `anon` role that shows only non-expired mentors, (2) a query as `service_role` that shows all mentors including expired ones.

These verification queries must be documented — not necessarily automated here, but reviewable by the developer applying the migration.

Component

Certificate Expiry RLS Visibility Policy

infrastructure medium

Dependencies (1)

Design and apply the Supabase database migration that creates the certification_expiry_tracking table. Include columns for peer_mentor_id, certificate_type, expiry_date, last_notified_at, and notification_thresholds_sent (array of integers representing 60/30/7-day markers). Add appropriate indexes on expiry_date and peer_mentor_id for efficient time-windowed queries. epic-certificate-expiry-notifications-data-foundation-task-001

Epic Risks (3)

high impact medium prob technical

The RLS policy predicate that checks certification_expiry_date and suppression_status on every coordinator list query could cause full table scans at scale, degrading response time for coordinator contact list screens across all chapters.

Mitigation & Contingency

Mitigation: Add a partial index on (certification_expiry_date, suppression_status) filtered to active mentors. Benchmark the policy predicate against a representative data set (500+ mentors) during development using EXPLAIN ANALYZE on Supabase staging.

Contingency: If the index does not resolve the performance issue, introduce a computed boolean column is_publicly_visible that is updated by the mentor_visibility_suppressor service and indexed separately, shifting the predicate cost to write time rather than read time.

medium impact medium prob integration

FCM device tokens become invalid when users reinstall the app or switch devices. If the token management strategy does not handle token refresh reliably, notification delivery will silently fail for a significant portion of the user base without surfacing errors.

Mitigation & Contingency

Mitigation: Implement the FCM token refresh callback in the Flutter client to upsert the latest token to Supabase on every app launch. Store token with a last_refreshed_at timestamp. The FCM sender should handle UNREGISTERED error codes by deleting stale tokens.

Contingency: If token staleness becomes widespread, add a token health check that forces re-registration during the expiry check edge function run by querying mentors whose token was last refreshed more than 30 days ago and triggering a silent push to prompt re-registration.

medium impact low prob integration

The certification expiry and notification record tables may have column naming or constraint conflicts with existing tables in the peer mentor status and certification management features, causing migration failures in shared Supabase environments.

Mitigation & Contingency

Mitigation: Audit existing table schemas for user_roles, certifications, and notification tables before writing migrations. Prefix new columns with expiry_ to avoid collisions. Run migrations against a clean Supabase branch environment before merging.

Contingency: If a conflict is found post-merge, apply ALTER TABLE migrations to rename conflicting columns and issue a hotfix migration. Communicate schema changes to all dependent feature teams via a shared migration changelog.

Quick Links

All Tasks Execution Plan