Create activity_attachments database migration
epic-document-attachments-foundation-task-001 — Write and apply a Supabase migration that creates the activity_attachments table with columns: id (uuid PK), activity_id (uuid FK), org_id (uuid FK), file_name (text), file_path (text), mime_type (text), file_size (bigint), uploaded_by (uuid FK), deleted_at (timestamptz nullable), created_at (timestamptz). Add appropriate indexes on activity_id and org_id. Ensure the table supports soft-delete semantics via deleted_at.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use `supabase migration new create_activity_attachments` to scaffold the file. Prefer `gen_random_uuid()` over `uuid_generate_v4()` as it requires no extension. Set `created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`. The `deleted_at` column should have no default (NULL means active).
FK to `auth.users` should reference `auth.users(id)` — confirm the schema name is correct for your Supabase project. Add a partial index `CREATE INDEX idx_activity_attachments_active ON activity_attachments(activity_id) WHERE deleted_at IS NULL;` for the common query pattern that only fetches active (non-deleted) attachments. This partial index will significantly outperform a full index for the primary read path.
Testing Requirements
Database migration tests only. After applying the migration: (1) verify the table exists with correct schema using `information_schema.columns`; (2) insert a valid row and confirm it is retrievable; (3) insert a row and set `deleted_at` to confirm soft-delete pattern works; (4) attempt to insert a row with a non-existent `activity_id` and verify FK constraint rejects it; (5) verify both indexes exist via `pg_indexes`. Run these checks as SQL assertions in a local Supabase instance before committing the migration.
Supabase RLS policies may not cover all query paths (e.g., service-role key usage in edge functions), potentially exposing attachment metadata or objects from another organisation to an unauthorised actor, breaching GDPR requirements.
Mitigation & Contingency
Mitigation: Add org_id scoping as an explicit WHERE clause at the Dart repository level as a second line of defence. Document which queries use the anon key versus service-role key, and audit all edge function calls that touch the storage bucket.
Contingency: If a bypass is discovered post-deployment, immediately revoke the affected signed URLs, rotate the service-role key, add the missing org_id filter, and deploy a patch. Notify affected organisations per GDPR breach protocol.
Supabase free/pro tier storage quotas may be exceeded earlier than expected if organisations upload large PDFs frequently, causing upload failures with no graceful degradation for users.
Mitigation & Contingency
Mitigation: Configure a 10 MB per-file cap enforced in the upload service (Epic 2), and add a storage usage monitoring alert at 80% of the allocated quota. Document the upgrade path in runbooks.
Contingency: If the quota is hit, temporarily disable new uploads via the org-level feature flag (attachments_enabled) and upgrade the Supabase plan. Communicate clearly to affected coordinators with an estimated restoration time.
The feature documentation specifies a migration order dependency: the activity_attachments table must be created after the activities table and before the Bufdir export join query is updated. Running migrations out of order will cause foreign-key or join failures.
Mitigation & Contingency
Mitigation: Add the migration to the numbered Supabase migration sequence immediately after the activities table migration. Add a CI check that runs migrations in order against a clean schema.
Contingency: If a deployment runs migrations out of order, roll back via the Supabase migration rollback script, reorder, and redeploy. No data loss occurs as attachments do not exist yet at that point.