critical priority medium complexity infrastructure pending infrastructure specialist Tier 1

Acceptance Criteria

The 'bufdir-exports' bucket is created as private (public: false) via Supabase dashboard or migration
A Storage INSERT policy allows authenticated users to upload only to paths matching '{org_id}/*' where org_id is their JWT claim
A Storage SELECT policy allows authenticated users to read only files under their own '{org_id}/*' prefix
A Storage DELETE policy allows only super_admin or the uploading user to delete files under their own org prefix
A user from org A attempting to read or write a path prefixed with org B's ID receives a 400/403 error from Supabase Storage
Signed URL generation produces a URL with an expiry of exactly the configured TTL (default 900 seconds / 15 minutes)
Signed URLs expire after TTL — a request made after TTL returns 400 'Object not found or access denied'
Signed URL generation is only callable by users with access to the file's org prefix (no signing of cross-org paths)
All bucket policies are defined in a Supabase migration or storage config file for reproducibility
Path construction follows the exact pattern: '{org_id}/{export_id}.{extension}' with no additional nesting

Technical Requirements

frameworks
Supabase Storage (S3-compatible)
Supabase Storage RLS policies (storage.objects table)
apis
Supabase Storage API: createSignedUrl(path, expiresIn)
Supabase Storage API: upload(path, data, options)
Supabase Auth JWT claims for org_id
data models
storage.objects (bucket_id, name, owner, metadata)
Path schema: {org_id}/{export_id}.{csv|xlsx|json}
performance requirements
Signed URL generation must complete within 500ms (network latency excluded)
Upload of files up to 50MB must complete without timeout — configure Supabase client upload timeout accordingly
security requirements
Bucket must be private — never set to public
Signed URLs must not be logged server-side in plain text — log only the file path and expiry timestamp
TTL must be configurable via app environment variable (BUFDIR_EXPORT_SIGNED_URL_TTL_SECONDS), defaulting to 900
Path construction must sanitise org_id and export_id — reject any input containing '..', '/', or non-UUID characters
CORS policy on the bucket must restrict allowed origins to the app domain only

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Supabase Storage policies are defined on the storage.objects table using SQL expressions referencing (storage.foldername(name))[1] to extract the first path segment (org_id). Policy expression for INSERT: (auth.jwt() ->> 'org_id') = (storage.foldername(name))[1]. Same expression applies to SELECT and DELETE policies. Create policies via Supabase migration SQL or the dashboard Storage > Policies tab — choose migration SQL for reproducibility.

The signed URL TTL should be passed as a Duration in the Dart adapter (task-009) and converted to integer seconds before calling createSignedUrl. Test the short-TTL edge case by creating a signed URL with expiresIn=1, sleeping 2 seconds, then asserting the URL is rejected. Ensure the bucket MIME type restrictions are configured to allow only text/csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, and application/json to prevent arbitrary file uploads.

Testing Requirements

Integration tests against Supabase test instance or local supabase CLI. Test cases: (1) upload a file to '{org_id}/{export_id}.csv' with matching org JWT — expect 200; (2) upload to '{other_org_id}/{export_id}.csv' with mismatched org JWT — expect 400/403; (3) generate signed URL for own file — expect valid URL with expiresIn matching TTL; (4) attempt to generate signed URL for a file in another org's prefix — expect error; (5) access signed URL before expiry — expect file bytes returned; (6) access signed URL after TTL has elapsed (mock clock or use very short TTL in test) — expect 400; (7) upload a file with a path containing '..' — expect rejection at path validation layer before reaching storage. Use flutter_test with real Supabase test credentials injected via environment variables.

Component
Export File Storage Adapter
infrastructure low
Epic Risks (2)
high impact medium prob security

RLS policies for the audit log and schema config tables must correctly handle multi-chapter membership hierarchies (up to 1,400 local chapters for NHF). Incorrect policies could either over-expose data across organisations or prevent legitimate coordinator access, both of which are serious compliance failures.

Mitigation & Contingency

Mitigation: Design RLS policies using the existing org hierarchy resolver pattern. Write integration tests that verify cross-organisation isolation with representative fixture data covering NHF's multi-level hierarchy before merging.

Contingency: If RLS policies prove too complex to express safely in Postgres, implement a Supabase Edge Function as a data access proxy that enforces isolation in application code, with RLS serving as a secondary defence layer.

medium impact medium prob scope

Bufdir's column schema is expected to evolve as Norse Digital Products negotiates a simplified digital reporting format. If the schema config versioning model is too rigid, applying Bufdir schema updates without a code deployment could fail, forcing emergency releases.

Mitigation & Contingency

Mitigation: Design the schema config table to store the full JSON column mapping as a JSONB field with a version number. Provide an admin API to upsert new versions without any schema migration required.

Contingency: If the versioning model is insufficient for a Bufdir schema change, fall back to a code deployment with the updated default schema, using the database config only for org-specific overrides.