high priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

fetchOpenAssignments(mentorId) returns only assignments where status == AssignmentStatus.open
fetchAssignmentsByStatus(mentorId, AssignmentStatus.closed) returns only closed assignments
fetchAssignmentsByStatus(mentorId, AssignmentStatus.open) is functionally equivalent to fetchOpenAssignments
Each returned AssignedContact includes: due_date (nullable DateTime), days_remaining (int, nullable), overdue_flag (bool), contact_name, and contact_id
overdue_flag is true when due_date is non-null and due_date.isBefore(DateTime.now())
days_remaining is null when due_date is null; otherwise computed as due_date.difference(DateTime.now()).inDays (negative when overdue)
Results from fetchOpenAssignments are ordered by due_date ascending (most urgent first); null due_dates appear last
Both methods apply the same org-branching guard from task-008: non-Blindeforbundet orgs are out of scope — these methods may assume they are called only in Blindeforbundet context, or they may delegate to fetchAssignmentHistory with a status filter
The Supabase query for status filtering uses .eq('status', status.name) (or equivalent string mapping)
No N+1 queries: contact reference data (name, id) is fetched in the same query via a Supabase join or embedded select, not in a separate call

Technical Requirements

frameworks
Flutter
Dart
supabase_flutter
apis
Supabase PostgREST — assignment_records table with embedded contact select
data models
AssignedContact
AssignmentStatus enum
DeadlineMetadata value object
performance requirements
Supabase query must use .select('*, contacts(id, name)') or equivalent to avoid N+1 contact lookups
fetchOpenAssignments must return within 400 ms for up to 50 open assignments
security requirements
Status filter must be applied server-side (in Supabase query), not client-side, to avoid fetching all records and filtering in Dart
Supabase RLS ensures mentor can only access their own assignments — no additional client-side mentor_id cross-check needed beyond what task-008 established

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

Implement fetchOpenAssignments as a convenience wrapper: return fetchAssignmentsByStatus(mentorId, AssignmentStatus.open) — do not duplicate query logic. For the deadline ordering, use Dart's List.sort with a comparator that handles null due_dates by placing them after non-null ones. Inject a Clock or DateTime provider into the repository (or the mapper) so unit tests can fix 'now' to a known value — avoids flaky tests that depend on wall time. The AssignedContact model should already include all fields from task-008; this task only adds the filter-by-status query methods.

If the Supabase table uses a string column for status (e.g., 'open', 'closed'), map to/from AssignmentStatus enum in the fromJson factory, not in the repository method.

Testing Requirements

Unit tests with flutter_test and mockito. Test cases: (1) fetchOpenAssignments returns only open-status records; (2) fetchAssignmentsByStatus with open status matches fetchOpenAssignments results; (3) fetchAssignmentsByStatus with closed status returns only closed records; (4) overdue_flag is true when due_date is in the past; (5) overdue_flag is false when due_date is in the future; (6) days_remaining is null when due_date is null; (7) days_remaining is negative for overdue assignments; (8) result ordering puts most imminent deadlines first, null deadlines last; (9) empty result returns empty list. Use DateTime.now() injection or a clock abstraction to make date-dependent assertions deterministic in tests.

Component
Assignment History Repository
data low
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for peer mentor data may block coordinator queries if the RLS rules are written for peer-mentor-self access only, requiring policy updates that affect other features sharing the same tables.

Mitigation & Contingency

Mitigation: Review existing RLS policies on peer_mentors, certification_records, and activity_log tables before writing repository queries. Coordinate with the database team to add coordinator-role predicates without weakening existing mentor-self policies.

Contingency: If policy changes are blocked, implement a Supabase Edge Function as a secure query proxy that enforces authorization server-side, avoiding direct RLS policy modification.

medium impact medium prob technical

The activity log table schema may not have a mentor_id foreign key column or may require a JOIN through an intermediate table, making the aggregation query significantly more complex than anticipated.

Mitigation & Contingency

Mitigation: Inspect the actual Supabase activity_log table schema before starting the MentorActivityLogRepository implementation. Document the exact JOIN path needed and validate it returns correct results for a known mentor.

Contingency: If schema requires complex multi-table aggregation, implement a Supabase database function (RPC) and expose it via the repository's fetchSummary method to keep Dart code clean.

high impact low prob dependency

The Blindeforbundet assignment table may not yet exist in the shared Supabase schema or may have a different structure than assumed, blocking the AssignmentHistoryRepository implementation.

Mitigation & Contingency

Mitigation: Verify the assignments table exists and confirm its column structure with the Contact Detail & Edit Screen team which also depends on assignment data (assignment-repository in that feature).

Contingency: If the assignments table is not yet available, implement the AssignmentHistoryRepository with a stub returning empty list and a TODO marker, unblocking the aggregation service while the schema is finalized.