Define Supabase schema for mileage_claims and org_configuration tables
epic-mileage-reimbursement-entry-infrastructure-task-001 — Create the Supabase database schema for the mileage_claims and org_configuration tables, including all required columns, data types, constraints, and Row Level Security policies scoped for peer mentor and coordinator roles. Define indexes for query performance.
Acceptance Criteria
Technical Requirements
Implementation Notes
Write the schema as a Supabase migration file (supabase/migrations/YYYYMMDDHHMMSS_mileage_schema.sql). Use gen_random_uuid() not uuid_generate_v4() as the former is available without extensions in modern Postgres. For the coordinator RLS policy, join against the org membership table (e.g. organization_members or user_roles) rather than embedding org logic in the policy expression directly — this keeps policies readable and maintainable.
Define the updated_at trigger using the standard Supabase moddatetime extension pattern. The auto_approval_threshold_km default of 50 and receipt_required_threshold_nok default of 100 match the HLF requirements from the product documentation — confirm with the team before finalizing these defaults as they may differ per organization.
Testing Requirements
Write SQL-level tests (pgTAP or Supabase test helpers) covering: (1) peer mentor cannot SELECT claims belonging to another peer mentor; (2) peer mentor cannot INSERT a claim with a different peer_mentor_id; (3) coordinator can SELECT claims within their organization but not outside it; (4) unauthenticated request returns zero rows (not an error); (5) org_configuration INSERT by a non-coordinator role is rejected with permission denied; (6) CHECK constraints reject distance_km <= 0 and rate_per_km <= 0; (7) UNIQUE constraint on org_configuration(organization_id) rejects duplicate inserts. Run migration script twice and assert no error on second run (idempotency test).
Supabase Row Level Security policies for mileage_claims may require complex join conditions to distinguish peer mentor (own claims only) from coordinator (chapter-scoped claims) access. If the RLS policy is misconfigured, coordinators could see claims outside their chapter scope or peer mentors could read other users' data, causing a data privacy incident.
Mitigation & Contingency
Mitigation: Write RLS policy SQL as part of this epic with explicit test cases for each role. Use Supabase's built-in policy testing tools and add integration tests that assert cross-user data isolation before merging.
Contingency: If RLS configuration proves too complex to test reliably within the epic, add an application-layer guard in the adapter that filters query results by authenticated user ID as a defence-in-depth measure while the policy is corrected.
Norwegian tax authority reimbursement rounding rules may change or may not be publicly documented in machine-readable form. Using the wrong rounding convention could cause systematic over- or under-payment, leading to compliance issues for the organisation.
Mitigation & Contingency
Mitigation: Source the exact rounding specification from HLF's finance team before implementing MileageCalculationService. Document the rule as a comment in the source code and link to the authoritative reference.
Contingency: If the rule is ambiguous, implement both truncation and half-up rounding behind a configuration flag so the organisation can switch without a code release.
SharedPreferences reads and writes are asynchronous. If the distance prefill service (built in the next epic) calls LocalDistanceCache concurrently with a post-submission write, a race condition could result in the cache returning a stale or partially written value, causing the next form load to show an incorrect default.
Mitigation & Contingency
Mitigation: Wrap all SharedPreferences access in LocalDistanceCache with sequential async operations and document the non-concurrent usage contract in the class API.
Contingency: If race conditions are observed in testing, introduce a simple mutex pattern using a Completer to serialise cache operations.