Driver honorarium service: DB schema and core persistence

epic-travel-expense-registration-org-extensions-task-001 — Create the database schema for honorarium records, including the honorarium_records table with fields for amount, org-specific rate band, declaration_acknowledged_at timestamp, parent expense ID foreign key, and activity ID. Implement the DriverHonorariumService repository layer with create, read, and update operations. Ensure row-level security policies restrict access by organisation and coordinator role.

high priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file `supabase/migrations/{timestamp}_create_honorarium_records.sql` is created and applies cleanly against a fresh local emulator with `supabase db reset`

honorarium_records table contains columns: id (uuid PK default gen_random_uuid()), org_id (uuid FK → organisations.id NOT NULL), expense_id (uuid FK → expense_claims.id NOT NULL), activity_id (uuid FK → activities.id NULLABLE), rate_band (text NOT NULL), amount (numeric(10,2) NOT NULL CHECK amount > 0), declaration_acknowledged_at (timestamptz NULLABLE), created_at (timestamptz NOT NULL default now()), updated_at (timestamptz NOT NULL default now())

RLS policy 'honorarium_select_own_org' allows SELECT only when auth.jwt()->'org_id' matches the row's org_id

RLS policy 'honorarium_insert_coordinator' allows INSERT only for users with role = 'coordinator' or 'admin' in their JWT claims

RLS policy 'honorarium_update_coordinator' allows UPDATE of amount, rate_band, declaration_acknowledged_at only for coordinator/admin role

DELETE is not permitted via RLS (soft-delete pattern is not required at this stage but no hard deletes should be possible from the client)

An index exists on (org_id, expense_id) for fast lookup by parent claim

DriverHonorariumService Dart class exposes: createHonorarium(CreateHonorariumParams) → Future<HonorariumRecord>, getHonorariumByExpenseId(String expenseId) → Future<HonorariumRecord?>, updateHonorarium(String id, UpdateHonorariumParams) → Future<HonorariumRecord>

Each repository method returns typed domain objects (HonorariumRecord), not raw Supabase maps

Network errors, constraint violations, and RLS denials are mapped to typed exceptions (HonorariumException subtypes)

Unit tests verify that the Dart model correctly serialises/deserialises from the expected Supabase JSON shape

Migration is reviewed and approved by the team before deployment

Technical Requirements

frameworks

Flutter

Supabase (PostgreSQL + RLS)

Dart

apis

Supabase REST API (via Dart supabase_flutter client)

data models

honorarium_records (new)

expense_claims (FK reference)

activities (FK reference)

organisations (FK reference)

performance requirements

Composite index on (org_id, expense_id) required

getHonorariumByExpenseId must complete in under 200 ms on a warm connection

Avoid N+1 queries: if future screens need list + rate band, use a single select with join

security requirements

RLS must be enabled on honorarium_records before any data is inserted

org_id must be written server-side from the JWT claim (not from the client payload) to prevent cross-org writes — use a BEFORE INSERT trigger or a Postgres function with SECURITY DEFINER

declaration_acknowledged_at can only be set to now() — clients must not be able to supply an arbitrary timestamp; enforce via a CHECK constraint or trigger

rate_band values should be validated against a lookup table or CHECK constraint to prevent free-text injection

Audit trail: add an honorarium_audit_log trigger or use Supabase Vault audit if available in the project

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Place migration in `supabase/migrations/` with a timestamp prefix. Place the Dart repository in `lib/features/expense/data/honorarium_repository.dart` and the domain model in `lib/features/expense/domain/models/honorarium_record.dart`. The `rate_band` field maps to Blindeforbundet's tiered honorarium system (standard rate vs. elevated rate at 3 and 15 assignments) — define the allowed band values as constants in a `HonorariumRateBand` enum in Dart and as a CHECK constraint in SQL to keep them in sync.

Use `Equatable` on `HonorariumRecord` for value equality in tests. The feature flag check (Blindeforbundet-only) is implemented in task-002, not here — the schema and repository are generic. Do not add any org-specific business logic in this task; keep the persistence layer clean.

Testing Requirements

Unit tests (flutter_test): HonorariumRecord.fromJson() with all fields present; fromJson() with nullable fields null; toJson() round-trip. Integration tests (against local emulator): createHonorarium inserts a row and returns it; getHonorariumByExpenseId returns null when no row exists; update changes the correct fields; RLS test — a user from a different org cannot SELECT the row (expect RLS-denied response); a non-coordinator role cannot INSERT (expect 403/RLS denied). Migration test: run `supabase db reset` and verify the table and indexes exist via information_schema queries.

Component

Driver Honorarium Service

service medium

Epic Risks (3)

high impact high prob integration

The Dynamics portal (HLF) and Xledger (Blindeforbundet) APIs have organisation-managed API contracts that may change without notice. Field mapping requirements, authentication flows, and export formats are not fully documented and may only be clarified during integration testing.

Mitigation & Contingency

Mitigation: Engage HLF and Blindeforbundet technical contacts early to obtain API documentation, sandbox credentials, and example payloads before implementation starts. Design the accounting integration client as a thin adapter layer with organisation-specific mappers so that field mapping changes require only mapper updates, not core client changes.

Contingency: If API documentation is unavailable or the API is unstable during Phase 3, implement a CSV/JSON file export as an interim deliverable. Coordinators can manually upload the file to their respective accounting systems until the live API integration is completed.

high impact medium prob scope

The confidentiality declaration for Blindeforbundet drivers may have specific legal requirements around content, format, wording, and record-keeping that are not yet specified. Implementing the wrong declaration flow could expose Blindeforbundet to compliance risk.

Mitigation & Contingency

Mitigation: Treat the declaration content and acknowledgement flow as a Blindeforbundet-controlled configuration, not hardcoded text. Implement the declaration as a templated document fetched from Supabase and reviewed by Blindeforbundet before any production deployment. Obtain written sign-off on the declaration text and acknowledgement mechanism before the epic is considered complete.

Contingency: If legal requirements cannot be confirmed in time for the sprint, deliver the driver honorarium form without the confidentiality declaration and gate the entire driver feature behind its feature flag. The declaration can be added in a follow-up sprint once requirements are confirmed, without blocking other feature delivery.

high impact medium prob integration

If the accounting export can be triggered multiple times for the same approved claims batch, duplicate records may be created in Dynamics or Xledger, causing accounting reconciliation problems that are difficult to reverse.

Mitigation & Contingency

Mitigation: Implement idempotent export runs: each export batch is assigned a unique run ID stored in the database. The accounting integration client checks for an existing successful export run for the same claim IDs before submitting. Approved claims that have been exported are marked with exported_at timestamp to prevent re-export.

Contingency: If duplicate exports occur despite idempotency checks (e.g. network failure after API success but before local confirmation), provide coordinators with an export history panel showing run IDs and timestamps. Implement a reconciliation endpoint that can query the accounting system for existing records before re-submitting flagged claims.

Quick Links

All Tasks Execution Plan