Create bufdir_report_history database table schema
epic-bufdir-report-history-foundation-task-001 — Design and implement the Supabase PostgreSQL migration for the bufdir_report_history table. Include columns for id, organization_id, report_period_start, report_period_end, submitted_by_user_id, submitted_at, status (draft/submitted/acknowledged), file_path, file_size_bytes, checksum, metadata JSONB, created_at, and updated_at. Add appropriate indexes on organization_id and submitted_at for query performance.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use Supabase CLI migration workflow: `supabase migration new create_bufdir_report_history`. Place the file in `supabase/migrations/`. Use `gen_random_uuid()` as default for id — do NOT rely on serial integers (breaks distributed inserts). Enable RLS on the table in this same migration with `ALTER TABLE bufdir_report_history ENABLE ROW LEVEL SECURITY;` but leave the policies empty (task-002 adds them).
The updated_at trigger should use a shared trigger function `moddatetime` if available in Supabase, or define a custom `set_updated_at()` PL/pgSQL function reused across tables. Avoid composite primary keys; UUID primary key + unique constraints are cleaner for Supabase's auto-generated REST API. Document the migration in `supabase/migrations/README.md` with the purpose, expected indexes, and rollback instructions.
Testing Requirements
Write SQL integration tests using pgTAP or equivalent: (1) unit test each CHECK constraint by attempting invalid status values and invalid period ranges — expect error; (2) verify all NOT NULL constraints reject null inserts; (3) insert 1000 rows for a single org and EXPLAIN ANALYZE the (organization_id, submitted_at) query — assert no sequential scan; (4) verify the updated_at trigger fires by updating a row and confirming the timestamp changed; (5) verify down migration drops all objects without errors. Run tests in a local Supabase container (supabase start) before applying to staging.
Incorrectly authored RLS policies could silently allow cross-organization data reads, exposing sensitive report history of one organization to coordinators of another in a multi-tenant environment.
Mitigation & Contingency
Mitigation: Write integration tests that explicitly authenticate as a user from organization A and assert zero rows are returned for organization B's history records. Use Supabase's built-in RLS testing utilities and review policies with a second developer.
Contingency: If a cross-tenant leak is discovered post-deployment, immediately revoke all active sessions for affected organizations, audit query logs for unauthorized access, and patch the RLS policy in a hotfix migration before re-enabling access.
The 5-year retention policy for report files may conflict with Supabase Storage's lack of native lifecycle rules, requiring a custom pg_cron job that could fail silently and either delete files prematurely or never clean up.
Mitigation & Contingency
Mitigation: Implement the retention cleanup as a documented pg_cron job with explicit logging to a separate audit_jobs table. Add a Supabase Edge Function health check that verifies the cron job ran within the last 25 hours.
Contingency: If the cron job fails, files accumulate in storage (non-critical for compliance — over-retention is safer than under-retention). Alert the ops team via monitoring and manually trigger the cleanup function once the cron issue is resolved.
Signed URL generation depends on the requesting user's Supabase session being valid at the time of the call. If sessions expire during a long screen interaction, URL generation will fail with an authorization error and confuse the coordinator.
Mitigation & Contingency
Mitigation: Wrap signed URL generation in the service layer with a session-refresh check before calling Supabase Storage. Generate URLs on demand (tap-to-download) rather than pre-generating them for all list items on screen load.
Contingency: If a URL generation fails due to session expiry, surface a clear error message prompting the coordinator to re-authenticate, then automatically retry URL generation after session refresh completes.