🏠 Overview › ✅ Implementation Tasks ›✅ epic-in-app-notification-centre-services-task-002

Build Supabase query predicates for each role scope

epic-in-app-notification-centre-services-task-002 — Implement the Supabase query predicate builders inside the Role-Aware Notification Filter Service for each role scope: peer_mentor (own notifications only), coordinator (own + assigned peer mentor notifications), and org_admin (all notifications within the organisation). Predicates must produce valid PostgREST filter strings consumable by the notification repository query layer.

critical priority medium complexity backend pending backend specialist Tier 1

Acceptance Criteria

peer_mentor predicate returns only notifications where recipient_id equals the authenticated user's ID
coordinator predicate returns notifications where recipient_id equals coordinator's ID OR where recipient_id is any peer mentor assigned to that coordinator
org_admin predicate returns all notifications scoped to the organisation (org_id match), not crossing organisation boundaries
All predicates produce valid PostgREST filter query strings (e.g., 'recipient_id=eq.{userId}' or 'or(recipient_id.eq.{id},recipient_id.in.(...))')
Predicate builder functions are pure — given the same role + user context they always return the same string
Each predicate builder is tested with a mock Supabase client verifying the generated filter string matches expected PostgREST syntax
No predicate leaks cross-organisation data — org_id scoping is always applied as an AND condition
Invalid or unknown role input throws an ArgumentError with a descriptive message
Predicate builder handles empty assigned peer mentor list (coordinator with no reports) without query syntax errors

Technical Requirements

frameworks
Flutter
BLoC
supabase_flutter
apis
Supabase PostgREST REST API
Supabase RLS policies
data models
Notification
UserRole
CoordinatorAssignment
Organisation
performance requirements
Predicate string construction must complete in under 1ms (pure in-memory operation)
Coordinator predicate with up to 50 assigned peer mentors must produce a valid in(...) clause without pagination issues
Avoid N+1 queries — coordinator peer mentor IDs fetched once and embedded in predicate
security requirements
Supabase RLS policies must be the authoritative enforcement layer; predicates are a defence-in-depth measure only
User IDs embedded in predicates must come from the authenticated session, never from client-supplied parameters
org_id must always be included in every predicate to prevent cross-tenant data access
Predicate strings must not be constructed via raw string interpolation of user-controlled values — use parameterised helpers

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Model each predicate builder as a static method or top-level function: `buildPeerMentorPredicate(String userId, String orgId)`, `buildCoordinatorPredicate(String userId, List menteeIds, String orgId)`, `buildOrgAdminPredicate(String orgId)`. Use Supabase's `.filter()` or `.or()` query builder methods rather than raw string concatenation to avoid syntax errors. For coordinator, fetch the list of assigned peer mentor IDs from a dedicated repository method before calling the predicate builder — keep the builder itself stateless. Wrap the role dispatch in a factory method `buildPredicateForRole(UserRole role, NotificationQueryContext ctx)` so the BLoC only calls one entry point.

Be mindful that PostgREST `in.(...)` clauses have a practical limit around 100 values; document this and add a guard that logs a warning if exceeded.

Testing Requirements

Unit tests (flutter_test) for each of the three predicate builder functions covering: (1) happy path with realistic user context, (2) coordinator with zero assigned mentors, (3) coordinator with maximum expected mentors (50), (4) org_admin with large org, (5) unknown role throws ArgumentError. Integration test verifying the generated predicate string is accepted by a local Supabase test instance or supabase_flutter mock without syntax errors. Test that org_id scoping is always present in the output regardless of role. 90%+ branch coverage on the predicate builder module.

Component
Role-Aware Notification Filter Service
service medium
Dependencies (1)
Define the Dart abstract interfaces and data models for the Role-Aware Notification Filter Service. Establish the FilterPredicate sealed class hierarchy covering peer_mentor, coordinator, and org_admin scopes, and define the NotificationFilterResult type that wraps the filtered list with metadata (role used, applied rules). epic-in-app-notification-centre-services-task-001
Epic Risks (3)
medium impact medium prob technical

A Realtime INSERT event arriving during an in-flight mark-all-read operation can cause the new notification to be incorrectly marked read in the optimistic state update, silently hiding it from the user.

Mitigation & Contingency

Mitigation: Process Realtime events sequentially in the BLoC event queue using bloc_concurrency's sequential transformer. The mark-all-read event should only affect notifications whose IDs were fetched before the operation started.

Contingency: Add a reconciliation step after mark-all-read that re-fetches the unread count from the repository and corrects the BLoC state if it diverges from the server value.

medium impact medium prob technical

A coordinator assigned to many peer mentors may trigger a query returning hundreds or thousands of notifications. Without pagination and query optimisation, the initial load will be slow and memory-heavy.

Mitigation & Contingency

Mitigation: Enforce server-side pagination (50 items per page) in the Role-Aware Filter's query predicates. Add a composite index on (org_id, user_id, created_at DESC) and profile query plans before shipping.

Contingency: If query performance is insufficient for large coordinator scopes, introduce a server-side RPC function that pre-aggregates visible notification IDs and returns only the first page, deferring full scope resolution to lazy-load.

medium impact low prob scope

If the read-state optimistic update rolls back frequently due to intermittent connectivity, users will observe notifications toggling between read and unread, creating confusion and distrust of the feature.

Mitigation & Contingency

Mitigation: Queue failed mutations in a local retry store and re-attempt on next connectivity event using a connectivity-aware retry service. Show a non-intrusive banner if offline rather than applying optimistic updates.

Contingency: Disable optimistic updates for mark-as-read in low-connectivity scenarios detected by the connectivity provider, instead showing a loading indicator until server confirmation.

Quick Links
All Tasks Execution Plan