Travel Expense Report
Data Entity
Description
Parent entity for a peer mentor's travel reimbursement submission, grouping individual expense items into a single report with overall status, total amount, and submission metadata. Reports flow through auto-approval (under threshold) or manual coordinator attestation.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key generated via gen_random_uuid() on insert | PKrequiredunique |
user_id |
uuid |
FK to users.id — identifies the peer mentor who owns this report. Immutable after creation; enforced by RLS so a user can only access their own reports unless they hold a coordinator or admin role. | required |
organization_id |
uuid |
FK to organizations.id — scopes the report to a specific member organization for approval routing, threshold configuration lookup, and downstream accounting sync (Xledger or Dynamics). Immutable after creation. | required |
status |
enum |
Current lifecycle state of the report. Drives approval routing logic: 'draft' while the peer mentor is composing the wizard; 'submitted' on confirmation; 'auto_approved' when below configured thresholds; 'pending_attestation' when above threshold and awaiting coordinator action; 'approved' after coordinator attestation; 'rejected' after coordinator rejection with mandatory reason. | required |
total_amount |
decimal |
Computed sum of all expense_items.amount values linked to this report, stored in Norwegian Krone (NOK) with two decimal places. Recalculated on each expense item insert or update. Used by the auto-approval threshold check and displayed in approval queue. | required |
total_distance_km |
decimal |
Aggregate kilometre distance across all mileage-type expense items in this report. Stored alongside total_amount because the auto-approval rule for HLF is threshold-based on distance (under 50 km triggers auto-approval), separate from the monetary threshold. Precision: 8,2. | - |
auto_approved |
boolean |
Set to true when the Auto-Approval Service determines the report meets the configured thresholds (distance and/or total amount). Persisted separately from status to allow downstream auditing to distinguish auto-approved records from manually attested ones, as required for Bufdir grant compliance. | required |
submitted_at |
datetime |
UTC timestamp of the moment the peer mentor confirmed final submission of the wizard. Null while in draft state. Used to calculate processing SLAs, filter reports by submission period, and populate Bufdir export date ranges. | - |
reporting_period |
string |
Human-readable reporting period label in YYYY-MM format (e.g. '2025-03') derived from submitted_at. Stored explicitly to enable fast period-scoped queries for Bufdir report generation and coordinator dashboards without date truncation in queries. | - |
submission_notes |
text |
Optional free-text notes entered by the peer mentor at submission time explaining context (e.g. unusual route, special circumstance). Not used in approval threshold logic but displayed to coordinator in the attestation detail screen. | - |
rejection_reason |
text |
Mandatory coordinator-provided reason when status transitions to 'rejected'. Captured by the Attestation Service and stored here for the peer mentor's notification and audit trail. Null for all non-rejected states. | - |
accounting_sync_status |
enum |
Tracks whether the approved report has been successfully pushed to the organization's accounting system (Xledger or Dynamics). Set to 'not_synced' on approval, updated by the Accounting Sync Orchestrator as it processes the record. | required |
accounting_synced_at |
datetime |
UTC timestamp of the last successful sync to the external accounting system. Null until the first successful sync. Used by the Integration Status Screen to show last sync time and by the Accounting Sync Orchestrator to detect duplicate sync attempts. | - |
has_receipts |
boolean |
Denormalized flag indicating whether any expense items in this report have attached receipts. Computed and stored on each expense item save to enable fast filtering in the approval queue without joining to expense_items and receipts tables. | required |
expense_item_count |
integer |
Denormalized count of expense items linked to this report. Incremented/decremented via database trigger on expense_items insert/delete. Used in approval queue list rendering to show report complexity at a glance without a COUNT join. | required |
created_at |
datetime |
UTC timestamp of record creation. Set by default to now() on insert. Immutable. Used for audit trails and to calculate how long a report has been in draft before submission. | required |
updated_at |
datetime |
UTC timestamp of the most recent modification to any field on this record. Maintained by a Supabase database trigger (set_updated_at). Used by the Accounting Sync Orchestrator to detect stale sync states. | required |
Database Indexes
idx_travel_expense_reports_user_id
Columns: user_id
idx_travel_expense_reports_organization_id
Columns: organization_id
idx_travel_expense_reports_status
Columns: status
idx_travel_expense_reports_user_org
Columns: user_id, organization_id
idx_travel_expense_reports_org_status
Columns: organization_id, status
idx_travel_expense_reports_submitted_at
Columns: submitted_at
idx_travel_expense_reports_reporting_period
Columns: organization_id, reporting_period
idx_travel_expense_reports_accounting_sync
Columns: accounting_sync_status, organization_id
Validation Rules
submitted_at_must_not_be_future
error
Validation failed
total_amount_non_negative
error
Validation failed
total_distance_non_negative
error
Validation failed
reporting_period_format
error
Validation failed
status_transition_sequence
error
Validation failed
rejection_reason_max_length
error
Validation failed
organization_id_matches_user_membership
error
Validation failed
expense_item_count_consistency
error
Validation failed
Business Rules
single_draft_per_user_per_org
A peer mentor may only have one report in 'draft' status per organization at any given time. Attempting to open a new registration wizard when a draft already exists must resume the existing draft rather than creating a new record. Prevents orphaned drafts accumulating in the database.
immutable_after_submission
Once status transitions out of 'draft' to 'submitted', the user_id, organization_id, and all expense items are locked. Further edits to the report body are only permitted via the coordinator correction workflow (coordinator-activity-approval feature), not by the submitting peer mentor.
auto_approval_threshold_evaluation
Immediately upon status transition to 'submitted', the Auto-Approval Service evaluates organization-specific thresholds fetched from the integration_configs table. If total_distance_km is under the configured distance threshold AND total_amount is under the configured amount threshold, status is set to 'auto_approved' and auto_approved is set to true. Otherwise, status transitions to 'pending_attestation'.
rejection_requires_reason
A coordinator may only transition status to 'rejected' if a non-empty rejection_reason is provided. This is a Bufdir audit compliance requirement — rejected claims must carry documented justification for grant reporting purposes.
accounting_sync_on_approval
When status transitions to 'approved' or 'auto_approved', the Accounting Sync Orchestrator is triggered to route the record to the correct connector (Xledger for Blindeforbundet, Dynamics for HLF). accounting_sync_status is set to 'syncing' at trigger time and updated to 'synced' or 'failed' after the sync attempt resolves.
at_least_one_expense_item_before_submission
Status cannot transition from 'draft' to 'submitted' unless expense_item_count >= 1. The wizard submit action is gated on this check client-side by the BLoC and validated server-side by the Expense Repository before executing the status update.
total_amount_sync_with_items
total_amount and total_distance_km must always equal the sum of their respective fields across all linked expense_items records. A Supabase database trigger recalculates and updates these denormalized fields on every expense_items insert, update, or delete. Direct writes to total_amount by application code are prohibited.
user_must_be_active_peer_mentor_in_org
Only users with an active peer_mentor role in the specified organization_id may create a travel_expense_report for that organization. The Supabase RLS policy enforces this at the database level using auth.uid() and a join to user_roles and user_organization_memberships.
coordinator_scope_for_approval_actions
Only coordinators whose organizational scope includes the report's organization_id may approve, reject, or correct a pending report. The Attestation Service validates scope before executing any RPC call; the Supabase RLS policy provides a second enforcement layer.
CRUD Operations
Storage Configuration
Entity Relationships
Each expense report contains one or more individual expense line items of different types
Expense reports are scoped to an organization for approval routing and accounting system sync
Each expense report has exactly one approval record tracking its auto-approval or manual attestation lifecycle
A user submits multiple travel expense reports over their tenure as a peer mentor