Create generated_reports database schema and migration

epic-bufdir-report-export-foundation-task-001 — Design and implement the generated_reports audit table in Supabase with columns for report_id, org_id, generated_by, period_start, period_end, export_format, storage_path, file_size_bytes, status, error_message, and created_at. Add RLS policies ensuring coordinators can only read their own org's records. This table is the compliance backbone — every export attempt must be permanently recorded.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file exists at `supabase/migrations/{timestamp}_create_generated_reports.sql` and applies cleanly with `supabase db push` against a fresh Supabase project

Table `generated_reports` exists with columns: `report_id UUID PRIMARY KEY DEFAULT gen_random_uuid()`, `org_id UUID NOT NULL REFERENCES organisations(id)`, `generated_by UUID NOT NULL REFERENCES auth.users(id)`, `period_start DATE NOT NULL`, `period_end DATE NOT NULL`, `export_format TEXT NOT NULL CHECK (export_format IN ('csv', 'pdf'))`, `storage_path TEXT`, `file_size_bytes BIGINT`, `status TEXT NOT NULL CHECK (status IN ('pending', 'completed', 'failed')) DEFAULT 'pending'`, `error_message TEXT`, `created_at TIMESTAMPTZ NOT NULL DEFAULT now()`

RLS is enabled on the table with a policy that allows SELECT only when `org_id = (SELECT org_id FROM user_roles WHERE user_id = auth.uid())`

INSERT is restricted to the service role only (edge function uses service role key) — no user-facing INSERT policy exists

UPDATE is restricted to the service role only — coordinators cannot modify audit records

DELETE is not permitted via RLS for any role (records are permanent)

An index exists on `(org_id, created_at DESC)` to support coordinator report history queries

An index exists on `(status, created_at)` to support monitoring queries for stuck pending records

Migration is idempotent when wrapped with `IF NOT EXISTS` guards

A rollback migration file exists that drops the table and all associated policies

Technical Requirements

frameworks

Supabase CLI (supabase db push, supabase migration new)

PostgreSQL 15+ (Supabase managed)

apis

Supabase PostgREST (table exposed via RLS-secured API)

data models

generated_reports table

organisations table (foreign key reference)

auth.users table (foreign key reference for generated_by)

user_roles table (used in RLS policy)

performance requirements

Index on (org_id, created_at DESC) must support report history pagination queries returning within 200ms for orgs with up to 10,000 report records

security requirements

RLS must be enabled — table must never be accessible without row-level filtering

No user role (coordinator, admin) may INSERT, UPDATE, or DELETE records directly — only the service role (edge function) writes to this table

The audit table must never be truncated or have records deleted by application code — retention policy (if any) is handled by a separate scheduled function, not application RLS

org_id foreign key constraint prevents orphaned audit records

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Use `gen_random_uuid()` not `uuid_generate_v4()` — the latter requires the uuid-ossp extension which may not be enabled by default on all Supabase tiers. For the RLS SELECT policy, join via `user_roles` table rather than a custom claims approach for consistency with the rest of the project's access control pattern. The `storage_path` column is nullable because it is only populated after a successful upload — a `pending` record will have NULL storage_path. Similarly `file_size_bytes` and `error_message` are nullable.

Consider adding a `CHECK (period_end >= period_start)` constraint to prevent logically invalid date ranges. Name the migration file with the exact timestamp prefix format the project already uses — check existing migrations in `supabase/migrations/` before creating the file.

Testing Requirements

Write SQL-level tests using pgTAP (if available in the Supabase project) or manual verification scripts: (1) assert all columns exist with correct types and constraints, (2) verify RLS blocks a coordinator from reading another org's records, (3) verify RLS blocks a coordinator from inserting directly, (4) verify service role can insert and update, (5) verify the status CHECK constraint rejects invalid values, (6) verify export_format CHECK constraint rejects invalid values. Include these as a `supabase/tests/generated_reports_rls_test.sql` file.

Component

Generated Reports Repository

data low

Epic Risks (3)

high impact medium prob technical

NHF's three-level hierarchy (national / region / chapter) with 1,400 chapters may have edge cases such as chapters belonging to multiple regions, orphaned nodes, or missing parent links in the database. Incorrect scope expansion would silently under- or over-report activities, which could invalidate a Bufdir submission.

Mitigation & Contingency

Mitigation: Obtain a full hierarchy fixture export from NHF before implementation begins. Write exhaustive unit tests covering boundary cases: single chapter, full national roll-up, chapters with no activities, and chapters assigned to multiple regions. Validate resolver output against a known-good manual count.

Contingency: If hierarchy data quality is too poor for automated resolution at launch, implement a manual scope override in the coordinator UI that allows the coordinator to explicitly select org units from a tree picker, bypassing the resolver.

medium impact high prob dependency

The activity_type_configuration table may not cover all activity types currently in use, leaving a subset unmapped at launch. Bufdir submissions with unmapped categories will be incomplete and may be rejected by Bufdir.

Mitigation & Contingency

Mitigation: Run a query against production activity data before implementation to enumerate all distinct activity type IDs. Cross-reference with Bufdir's published category schema (request from Norse Digital Products). Flag every gap as a known issue and build the warning surface into the preview panel.

Contingency: Implement a fallback 'Other' category bucket for unmapped types and surface a prominent warning in the export preview requiring coordinator acknowledgement before proceeding. Log unmapped types for post-launch cleanup.

high impact low prob security

Supabase RLS policies on generated_reports and the storage bucket must enforce strict org isolation. A misconfigured policy could allow a coordinator from one organisation to read another organisation's export files, creating a serious data breach with GDPR implications.

Mitigation & Contingency

Mitigation: Write RLS integration tests that attempt cross-org reads with explicitly different JWT tokens and assert that all attempts return empty sets or 403 errors. Include RLS policy review in the pull request checklist. Use Supabase's built-in policy tester during development.

Contingency: If a policy gap is discovered post-deployment, immediately revoke all signed URLs for affected exports, audit the access log for unauthorised reads, and issue a coordinated disclosure to affected organisations per GDPR breach notification requirements.

Quick Links

All Tasks Execution Plan