Create export_runs database schema and migrations
epic-accounting-system-export-foundation-task-001 — Design and implement the export_runs table in Supabase with columns for run_id, org_id, initiated_by, status, date_range_start, date_range_end, target_system (xledger|dynamics), record_count, file_url, created_at, and completed_at. Also add the exported_at column to expense_claims table with a nullable timestamp. Include RLS policies scoped to org_id and coordinator role. Write and apply the migration scripts.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase CLI migration files (supabase/migrations/YYYYMMDDHHMMSS_export_runs.sql). Define RLS policies using CREATE POLICY with USING (org_id = (SELECT org_id FROM users WHERE id = auth.uid())) — join against the users table rather than a JWT claim to ensure accuracy. For the status CHECK constraint, consider a future-proof approach: use a lookup table or domain type rather than inline CHECK if the status enum is likely to grow. The exported_at column on expense_claims is a soft-mark approach — ensure downstream query services filter on IS NULL to detect unexported claims.
Coordinate with the team to confirm the existing expense_claims table column naming convention before migration.
Testing Requirements
Write integration tests using Supabase's local emulator (supabase start). Test 1: Insert a run as a coordinator user and verify SELECT succeeds for same org, fails for different org. Test 2: Attempt INSERT as a peer mentor role and verify RLS denial. Test 3: Verify expense_claims.exported_at accepts NULL on existing rows and TIMESTAMPTZ values on update.
Test 4: Run migration twice and confirm idempotency (no errors). Test 5: Apply rollback migration and verify original schema is restored. Tests should be executable in CI via supabase db reset + migration apply.
Adding exported_at and export_run_id columns to expense_claims requires a live migration on a table shared with the approval workflow. A poorly timed migration could lock the table and block claim submissions or approvals.
Mitigation & Contingency
Mitigation: Use non-blocking ADD COLUMN with a DEFAULT of NULL (no backfill needed) executed during a low-traffic window. Test migration rollback on a staging replica before production deployment.
Contingency: If migration causes table lock contention, roll back and reschedule for a maintenance window. Use a feature flag to gate the export UI until the migration completes successfully.
Chart of accounts mapping configurations for Xledger and Dynamics may not be fully specified by stakeholders at development time, leaving the mapper with incomplete data and causing validation failures for unmapped expense categories.
Mitigation & Contingency
Mitigation: Implement the mapper to return a structured validation error (not a crash) for any unmapped field, and surface these errors clearly in the export confirmation dialog. Request full mapping tables from Blindeforbundet and HLF stakeholders as a pre-condition for this epic.
Contingency: If mappings arrive incomplete, ship the mapper with the available subset and mark unmapped categories as excluded (skipped with reason). Coordinators see which categories are skipped and can manually submit those records.
Supabase Vault configuration for storing per-org accounting credentials may require infra permissions or environment secrets not yet provisioned in staging or production, blocking development and testing of credential retrieval.
Mitigation & Contingency
Mitigation: Provision Vault secrets and environment configuration in staging as the first task of this epic. Document the exact secret naming convention and rotation procedure before implementation begins.
Contingency: If Vault is unavailable, use environment variables scoped to the Edge Function as a temporary fallback for development. Block production deployment until Vault-based storage is confirmed operational.