🏠 Overview › ✅ Implementation Tasks ›✅ epic-driver-and-confidentiality-management-core-services-task-007

Build assignment history retrieval with org scope

epic-driver-and-confidentiality-management-core-services-task-007 — Add the assignment history retrieval method to the driver assignment service. The method must query the driver assignment repository for all assignments belonging to a given driver within the calling org, return them sorted by assignment date descending, and enforce org-scoped RLS so coordinators from other orgs cannot access the data.

medium priority medium complexity backend pending backend specialist Tier 2

Acceptance Criteria

getAssignmentHistory(driverId, orgId) returns a List<DriverAssignment> sorted by assignment_date descending (most recent first)
Only assignments where org_id matches the calling user's authenticated org are returned — cross-org leakage is impossible at the database level via RLS
If the driver has no assignments in the calling org, an empty list is returned — not an exception
If driverId does not belong to the calling org, an empty list is returned (RLS filters it out) — not an authorization exception that leaks existence
Pagination is supported: the method accepts optional offset and limit parameters (default limit = 50)
Each returned DriverAssignment includes: id, driver_id, org_id, assignment_date, fee_amount_ore, status, and coordinator_id
The method does not return soft-deleted assignment records (deleted_at IS NOT NULL records are excluded)
The Supabase query uses an index on (org_id, driver_id, assignment_date DESC) — confirmed via EXPLAIN output in integration test
Method response time is under 300 ms for up to 500 records in the test dataset
RLS policy test: executing the query with a JWT from a different org returns zero rows even when the driver has assignments in the target org

Technical Requirements

frameworks
Flutter
BLoC
Riverpod
flutter_test
apis
Supabase PostgREST REST API (driver_assignments table select with filter and order)
data models
DriverAssignment (id, driver_id, org_id, assignment_date, fee_amount_ore, status, coordinator_id, deleted_at)
AssignmentHistoryQuery (driverId, orgId, offset, limit)
performance requirements
Composite index on (org_id, driver_id, assignment_date DESC) must exist in the Supabase migration
Default page size of 50 records — never fetch unbounded result sets
Use Supabase .select() with explicit column list, not wildcard, to reduce payload size
security requirements
RLS policy: SELECT on driver_assignments requires auth.jwt() -> org_id = row.org_id
driverId parameter must be a UUID validated client-side before sending to Supabase to prevent query injection via malformed IDs
org_id must be extracted from the authenticated JWT — never accepted as a client-provided parameter in the service method signature

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

The service method signature should be: Future> getAssignmentHistory({required String driverId, int offset = 0, int limit = 50}). The org_id is resolved internally from the authenticated Supabase session — never passed by the caller. In the SupabaseDriverAssignmentRepository, build the query as: supabase.from('driver_assignments').select('id,driver_id,org_id,assignment_date,fee_amount_ore,status,coordinator_id').eq('driver_id', driverId).is_('deleted_at', null).order('assignment_date', ascending: false).range(offset, offset + limit - 1). The org_id filter is enforced by RLS, not an explicit .eq() — but add .eq('org_id', orgId) as a belt-and-suspenders defense-in-depth measure.

Map the raw JSON response to DriverAssignment domain objects in the repository layer, not in the service layer.

Testing Requirements

Unit tests with mocked DriverAssignmentRepository: empty result, single record, multiple records (verify sort order), pagination (offset/limit), soft-deleted records excluded. Integration tests against Supabase test project: seed assignments for two different orgs with the same driver_id, authenticate as org-1 coordinator, verify only org-1 assignments returned. Verify index usage with EXPLAIN via a Supabase RPC call in the integration test suite. Performance test: seed 500 rows, measure query latency.

Component
Driver Assignment Service
service medium
Dependencies (1)
Within the driver assignment service, implement the routing logic that hands off to the expense approval workflow when fee validation triggers the high-value path. The service must create a pending expense claim record linked to the assignment, invoke the expense attestation service, and block assignment confirmation until the claim is approved or rejected. epic-driver-and-confidentiality-management-core-services-task-006
Epic Risks (4)
high impact medium prob security

Org-scoped encryption key management is complex. If keys are not correctly isolated per organization, a breach in one org's key could expose another org's declarations. Additionally, key rotation is not specified but may be needed for compliance, and the current implementation may not support it.

Mitigation & Contingency

Mitigation: Use Supabase Vault or a dedicated secrets management approach for org-scoped key storage. Define the key derivation strategy (per-org master key) in a security design document reviewed before implementation begins. Include key isolation tests in the test suite.

Contingency: If a full per-org key management system cannot be safely implemented within the sprint, fall back to a single platform-level encryption key with strict RLS isolation as a temporary measure, flagging the key rotation gap as a security debt item with a defined resolution milestone.

medium impact medium prob integration

Push notification delivery to drivers depends on FCM token availability and device connectivity. If a driver has not granted notification permissions or has an expired FCM token, the declaration delivery notification will silently fail, leaving the coordinator unaware and the declaration unacknowledged.

Mitigation & Contingency

Mitigation: Implement delivery status tracking in declaration-notification-service. Fall back to in-app notification and SMS (if configured) when push delivery fails. Expose delivery failure status in the declaration status badge so coordinators can identify and manually follow up.

Contingency: If push delivery proves unreliable, implement a polling-based in-app notification fallback where drivers see pending declarations on next app open, ensuring the workflow can complete even without push notifications.

medium impact medium prob technical

The acknowledgement service is meant to validate that the driver has fully scrolled through the declaration before confirming. Implementing reliable scroll completion detection in Flutter across different screen sizes and font sizes is technically non-trivial and could be bypassed.

Mitigation & Contingency

Mitigation: Implement scroll position tracking using ScrollController with a threshold (e.g., 95% of content height reached) and record the validated state server-side before allowing acknowledgement submission. Document the approach in the legal sign-off checkpoint noted in the feature documentation.

Contingency: If reliable scroll detection cannot be implemented within the sprint, add a mandatory reading delay timer (e.g., estimated reading time based on word count) as an alternative validation mechanism, pending legal review of the approach.

medium impact low prob dependency

The driver assignment service must coordinate with the threshold-based expense approval workflow for fees above configured thresholds. If the expense approval workflow interface changes or is not yet stable, the integration point could break or produce incorrect routing behavior.

Mitigation & Contingency

Mitigation: Define a clear interface contract between driver-assignment-service and the expense approval workflow before implementation. Use dependency injection so the expense workflow client can be mocked in tests. Monitor the expense approval feature for interface changes.

Contingency: If the expense approval workflow interface is not stable, implement a direct database insert to the expense records table as a temporary bypass, with a flag indicating manual review is needed, until the stable interface is available.

Quick Links
All Tasks Execution Plan