critical priority medium complexity testing pending testing specialist Tier 2

Acceptance Criteria

A test suite file exists (e.g., `test/database/expiry_rls_policy_test.dart`) tagged `@integration` and documented as requiring a local Supabase instance
Test: querying `peer_mentors` as `anon` role returns zero rows for a seeded expired mentor (expiry_date = yesterday)
Test: querying `peer_mentors` as `authenticated` coordinator role returns zero rows for the same expired mentor
Test: querying `peer_mentors` as `authenticated` coordinator role returns the row for a seeded active mentor (expiry_date = next year)
Test: querying `peer_mentors` via the Flutter Supabase client (using coordinator JWT) does NOT return expired mentor even when the client appends `.eq('id', expiredMentorId)` — the RLS policy wins over client-side filters
Test: querying `peer_mentors` using the service_role key returns BOTH active and expired mentors (admin bypass confirmed)
Test: a client attempting to override the RLS by appending `OR expiry_date < NOW()` to a query receives an empty result for the expired mentor (PostgREST does not allow filter injection that bypasses RLS)
All tests use a dedicated Supabase test schema or test-specific seed data that is cleaned up after each test run
Test results are interpretable in CI: pass/fail with clear error messages when a policy is missing or misconfigured

Technical Requirements

frameworks
flutter_test
Supabase Flutter SDK (for client-side query tests)
Supabase CLI (for local instance)
PostgreSQL psql or supabase-dart admin client (for service_role tests)
apis
Supabase PostgREST (anon and authenticated role queries)
Supabase Admin API (service_role key for admin bypass test)
Supabase local development environment
data models
peer_mentors
certification_expiry_tracking
user_roles (for seeding coordinator JWT)
performance requirements
Full test suite must complete within 30 seconds against a local Supabase instance
Each individual test must complete within 5 seconds
security requirements
Tests must use dedicated test credentials — never production API keys
service_role key used in tests must come from environment variables, not hardcoded
Test seed data must use synthetic (non-real) personal data

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

The most common failure mode in RLS testing is accidentally using the service_role key for the 'should-be-blocked' tests — double-check that the anon and coordinator clients are initialized without elevated keys. For seeding coordinator JWT, either use a pre-seeded test user with coordinator role or generate a JWT with the local Supabase auth helper. The filter-injection bypass test is particularly important for security assurance: PostgREST applies RLS before applying client-provided filters, so `WHERE id = ? AND expiry_date < NOW()` will still be blocked if the RLS policy says otherwise — but this must be explicitly verified, not assumed.

Consider writing a test helper `SupabaseRlsTestHelper` with methods `seedExpiredMentor()`, `seedActiveMentor()`, and `cleanupTestMentors()` to keep test bodies readable. Tag all tests in this file with `group('RLS: expiry policy', ...)` for easy filtering in CI.

Testing Requirements

These tests ARE the deliverable. Use flutter_test with a test helper that initializes a Supabase client against `http://localhost:54321` (local Supabase). Seed data setup: use setUp() to insert test mentor rows into peer_mentors and corresponding rows in certification_expiry_tracking. Use tearDown() to delete seeded rows by test-specific IDs.

For the anon role test, initialize a second Supabase client with the anon key and no session. For the service_role bypass test, use the service_role key (from env vars). For the client-side filter bypass test, attempt `.from('peer_mentors').select().eq('id', expiredId).gt('expiry_date', DateTime(2000))` — the RLS should still suppress the row. Document in the test file header that these tests require `supabase start` to be running locally.

Component
Certificate Expiry RLS Visibility Policy
infrastructure medium
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.