Certificate Expiry RLS Visibility Policy
Component Detail
Description
Supabase Row Level Security policy configuration that enforces automatic suppression of expired peer mentors from coordinator queries and public-facing data views. Implements a computed column or policy predicate that checks certification_expiry_date and suppression status at the database level.
expiry-rls-policy
Summaries
The Certificate Expiry RLS Visibility Policy is a critical data governance control that automatically prevents expired or non-compliant peer mentors from appearing in coordinator workflows and public-facing chapter listings. Without this safeguard, coordinators risk assigning mentees to unqualified mentors, exposing the organization to program integrity failures and potential liability. By enforcing compliance rules at the database level rather than relying on application-layer checks, the organization ensures that no code path — present or future — can accidentally surface invalid mentor data, providing a durable, low-maintenance compliance guarantee.
This component requires close coordination between the backend engineer and the Supabase/PostgreSQL specialist, as RLS policy authoring has significant correctness risk — a misconfigured predicate can silently over-filter or under-filter rows across all consuming queries. Development is medium complexity but testing requirements are high: the test matrix must cover active mentors, expired mentors, suppressed mentors, coordinators querying all states, and public listing queries. The dependency on `certification-expiry-repository` must be resolved before policy predicates can reference the correct columns. Budget additional QA time for regression testing since this policy affects every mentor query across the system.
Implemented as a Supabase RLS policy applied to the peer_mentor_profiles table, this component uses policy predicates that evaluate `certification_expiry_date` and a `suppression_status` flag. For public listing queries, the policy should use a USING clause that checks `certification_expiry_date > now() AND suppression_status = false`. Coordinator queries bypass suppression filtering via a role-based policy exception, enabled by checking `auth.jwt() ->> 'role' = 'coordinator'`. The `getMentorsIncludingSuppressed` interface requires a separate policy branch or a security definer function to elevate privileges.
Consider a computed column `is_active_mentor` to simplify predicate logic and improve query plan readability. `refreshSuppressionComputedColumn` should be triggered via a scheduled function or database trigger on expiry date changes.
Responsibilities
- Define RLS policy predicates that filter expired mentors from coordinator list queries
- Provide computed column or view for active mentor status based on expiry date
- Ensure suppressed mentors are excluded from public chapter website data queries
- Allow coordinators to explicitly query suppressed mentors for management purposes
Interfaces
applyActiveMentorFilter(query: PostgrestQuery) -> PostgrestQuery
isMentorActiveForPublicListing(mentorId: String) -> bool
getMentorsIncludingSuppressed(coordinatorId: String) -> List<PeerMentor>
refreshSuppressionComputedColumn()