🏠 Overview › ✅ Implementation Tasks ›✅ epic-travel-expense-registration-foundation-task-001

Create Supabase expense type catalogue schema

epic-travel-expense-registration-foundation-task-001 — Design and migrate the Supabase database schema for the expense type catalogue, including tables for expense types, exclusive group definitions, and org-specific catalogue overrides. Define RLS policies to enforce organisation-level isolation so HLF and Blindeforbundet see only their own catalogue entries.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file creates the expense_types table with columns: id (uuid PK default gen_random_uuid()), organization_id (uuid FK → organizations), name (text not null), description (text), is_travel_expense_eligible (boolean not null default true), requires_receipt_above_nok (integer), created_at (timestamptz default now()), updated_at (timestamptz default now())
Migration creates the expense_exclusive_groups table: id (uuid PK), organization_id (uuid FK), name (text not null) — represents a named mutual-exclusion group (e.g. 'transport-mode')
Migration creates the expense_type_exclusive_group_members table: expense_type_id (uuid FK → expense_types), exclusive_group_id (uuid FK → expense_exclusive_groups), PRIMARY KEY (expense_type_id, exclusive_group_id) — allows an expense type to belong to one or more exclusive groups
RLS enabled on all three tables with policies: authenticated users can SELECT only rows where organization_id matches their JWT organization_id claim; no INSERT/UPDATE/DELETE from mobile clients (service role only)
Seed data migration populates HLF's catalogue: at minimum 'km-godtgjørelse', 'bompenger', 'parkering', 'kollektiv', 'drosje' expense types, with 'km-godtgjørelse' and 'kollektiv' in the same exclusive group
Migration is idempotent: running it twice does not produce errors (use CREATE TABLE IF NOT EXISTS and INSERT ... ON CONFLICT DO NOTHING)
All foreign keys have ON DELETE CASCADE where appropriate (deleting an organization cascades to its expense types and groups)
Migration file is located at supabase/migrations/ and named with a timestamp prefix following existing conventions in the project
Applying the migration to a fresh Supabase project succeeds without errors via supabase db push

Technical Requirements

frameworks
Supabase PostgreSQL 15
Dart
apis
Supabase PostgreSQL 15 (DDL migrations)
Supabase Auth (JWT organization_id claim for RLS)
data models
activity_type
performance requirements
expense_types table has a composite index on (organization_id, is_travel_expense_eligible) for the primary query pattern
expense_type_exclusive_group_members has index on exclusive_group_id for reverse lookup
security requirements
RLS policies use (auth.jwt() -> 'organization_id')::uuid for the organization_id comparison — never a client-supplied parameter
No mobile client has INSERT/UPDATE/DELETE access to expense_types — catalogue is managed via service role (admin portal or seed data only)
Row-level isolation ensures HLF seed data is invisible to Blindeforbundet and vice versa at the database level

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Design the exclusive group concept as a many-to-many join table rather than a foreign key on expense_types so that future requirements can place a type in multiple groups if needed. Do not add a 'display_order' column yet — sort alphabetically or by created_at until UX requires explicit ordering. The requires_receipt_above_nok column stores the threshold as a nullable integer (null means no receipt ever required for this type) — this avoids a separate boolean and keeps the receipt logic in one place. For the RLS policy, use: CREATE POLICY select_own_org ON expense_types FOR SELECT TO authenticated USING ((auth.jwt()->>'organization_id')::uuid = organization_id).

Keep the seed data in a separate seed migration file (suffixed _seed) so it can be skipped in production if catalogue is managed via admin portal.

Testing Requirements

Write a migration test script (bash or SQL) that: (1) applies the migration to a test database, (2) inserts two organizations with different expense type catalogues using the service role, (3) authenticates as a user from org A and verifies SELECT returns only org A's expense types, (4) attempts SELECT as org B user and verifies org A's rows are not returned, (5) attempts INSERT as an authenticated (non-service-role) user and verifies it is blocked by RLS. This script should be runnable via supabase db test or a custom npm test:db script. Additionally, verify the mutual exclusion seed data: query expense_type_exclusive_group_members for HLF's 'transport-mode' group and confirm both 'km-godtgjørelse' and 'kollektiv' are present.

Component
Expense Type Catalogue Repository
data low
Epic Risks (3)
high impact medium prob security

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.

medium impact medium prob technical

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.

medium impact low prob scope

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.

Quick Links
All Tasks Execution Plan