Create Supabase expense claims database schema
epic-travel-expense-registration-foundation-task-003 — Write and apply Supabase migration scripts for the expense_claims and expense_line_items tables, covering all required fields (amount, currency, expense_type_id, status, org_id, claimant_id, receipt_urls, auto_approved flag, threshold snapshot). Add foreign key constraints, indexes for common queries, and RLS policies that restrict row visibility to the owning organisation and authorised attestors.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Use Supabase CLI `supabase migration new expense_claims_schema` to create the migration file. Store threshold_snapshot as jsonb rather than normalized columns — this avoids JOIN complexity when rendering historical claims and protects against org config changes invalidating past records. For the RLS coordinator policy, join against an org_members or user_roles table to verify coordinator role rather than using a hardcoded role claim in JWT — this allows role changes to take effect without token re-issue. Define the claim status enum as a Postgres TYPE so Dart code generation (via supabase_flutter) can map it to a sealed class.
Ensure the migration file is committed to version control under supabase/migrations/. Do not use Supabase Studio UI to create tables — all schema must be in migration files for reproducibility.
Testing Requirements
Write PostgreSQL unit tests using pgTAP or Supabase's built-in test runner. Test coverage must include: (1) RLS policy tests — verify claimant A cannot read claimant B's claims in same org, verify coordinator can read all org claims, verify coordinator cannot modify claim amounts; (2) FK constraint tests — verify cascade delete on expense_claim deletion removes all line items; (3) Enum transition tests — verify invalid status transitions are blocked at DB level if triggers enforce state machine; (4) Index usage verification using EXPLAIN ANALYZE on the three defined query patterns; (5) Migration rollback test — apply migration, roll back, re-apply cleanly. Run migration against a local Supabase instance in CI before merging.
Row-level security policies for expense claims must correctly scope data to organisation, role (peer mentor sees own claims only, coordinator sees org-wide queue), and claim status. Incorrect RLS can expose claims cross-organisation or prevent coordinators from accessing the attestation queue.
Mitigation & Contingency
Mitigation: Define RLS policies in code-reviewed migration files. Write integration tests that attempt cross-org reads with different JWT roles and assert access denial. Review with a second engineer before merging migrations.
Contingency: If RLS is misconfigured post-deployment, disable the affected policy temporarily and apply a hotfix migration within the same release window. No claim data is exposed publicly due to Supabase project-level auth requirement.
The auto-approval Edge Function is triggered server-side on expense insert. Cold-start latency or Edge Function failures can block the submission response and degrade UX, especially on mobile networks.
Mitigation & Contingency
Mitigation: Implement the auto-approval Edge Function client with a timeout and graceful fallback: if no result is received within 5 seconds, treat the claim as 'pending' and poll for the status update via Supabase Realtime. Keep the Edge Function warm with a periodic ping.
Contingency: If Edge Function reliability is unacceptable, move auto-approval evaluation to a database trigger or Postgres function as an interim measure, accepting that threshold configuration changes require a migration rather than a settings update.
The expense type catalogue and threshold configuration are cached locally for offline use. If an organisation updates their catalogue exclusion rules or thresholds while a peer mentor is offline, the local cache may allow submissions that violate the new policy.
Mitigation & Contingency
Mitigation: Cache entries include a TTL (24 hours). On connectivity restore, refresh cache before allowing new submissions. Server-side validation in the Edge Function and save functions provides a second enforcement layer.
Contingency: If a stale-cache submission passes client validation but fails server validation, surface a clear error message explaining that the expense type rules have been updated and prompt the user to review their selection with the refreshed catalogue.