Create driver_assignments Supabase table with RLS policies
epic-driver-and-confidentiality-management-foundation-task-003 — Create the driver_assignments table in Supabase with org-scoped RLS policies. Include columns for driver identity, assignee contact reference, assignment date, status, and soft-delete fields for audit preservation. Add migration scripts and index on org_id, driver_id, and status.
Acceptance Criteria
Technical Requirements
Implementation Notes
The status CHECK constraint values must exactly match the constants used in the Dart DriverAssignment model and DriverAssignmentService — define them together and keep them in sync. Consider adding a database trigger to auto-update updated_at on every UPDATE. The fee_amount column uses numeric(10,2) to avoid float precision issues — this is important for financial fee routing logic downstream. The (org_id, status) composite index is more selective than individual indexes for the most common query pattern (list pending assignments for my org).
Add a partial index on (org_id, driver_id) WHERE deleted_at IS NULL for the common 'active assignments for driver' query pattern.
Testing Requirements
SQL or Supabase integration tests verifying: (1) INSERT by coordinator in correct org succeeds, (2) INSERT by coordinator in wrong org is rejected by RLS, (3) SELECT returns only own-org rows, (4) UPDATE status within org succeeds, (5) UPDATE status across orgs rejected, (6) DELETE attempt rejected, (7) Invalid status value rejected by CHECK constraint, (8) Migration runs cleanly on fresh Supabase project, (9) Migration is idempotent on second run.
Row-level security policies for driver assignments and declarations must correctly scope data to the coordinator's chapter without leaking records across organizations. An incorrect RLS predicate could silently return empty result sets or, worse, expose cross-org data, both of which are difficult to detect in unit tests.
Mitigation & Contingency
Mitigation: Write dedicated RLS integration test scenarios with multiple org fixtures asserting both data isolation and correct data visibility. Use Supabase's built-in policy testing utilities and review policies with a second developer.
Contingency: If RLS policies prove too complex to get right quickly, implement application-layer org scoping as a temporary guard while RLS is fixed in a follow-up, with an explicit security review gate before production deployment.
The declaration audit logger must produce tamper-evident records. If the database allows updates or deletes on audit rows, the compliance guarantee is broken. Supabase does not natively prevent row deletion by default.
Mitigation & Contingency
Mitigation: Implement an insert-only RLS policy on the audit table that denies UPDATE and DELETE for all roles including the service role. Add a database trigger that rejects mutation attempts and logs the attempt itself.
Contingency: If immutability cannot be enforced at the database level within the sprint, store audit entries in an append-only Supabase Edge Function log stream as a temporary alternative, with a migration plan to the proper table once constraints are implemented.
The org-feature-flag-service caches flag values to avoid repeated database reads. If the cache is not invalidated promptly after an admin toggles the flag, coordinators may see stale UI state — either seeing driver features when they should not, or not seeing them when they should.
Mitigation & Contingency
Mitigation: Use a Supabase Realtime subscription to listen for changes on the driver_feature_flag_config table and invalidate the in-memory cache immediately on change. Set a short TTL (60 seconds) as a safety net.
Contingency: If Realtime subscription proves unreliable, expose a manual cache-bust endpoint accessible from the admin toggle action, ensuring the cache is cleared synchronously on every flag change.