DB Migration: Add recorded_by_user_id Column
epic-bulk-and-proxy-registration-foundation-task-001 — Create and apply a Supabase database migration that adds the recorded_by_user_id UUID column (nullable, foreign key to auth.users) to the activity_records table. Include index creation for query performance, update RLS policies to permit coordinator writes on behalf of peer mentors, and verify that existing rows default to NULL without data loss. This is the single most critical prerequisite for all proxy workflows and Bufdir-compliant reporting.
Acceptance Criteria
Technical Requirements
Implementation Notes
Use `supabase migration new add_recorded_by_user_id` to scaffold the file. The foreign key should reference auth.users(id) rather than a custom profiles table to avoid cross-schema FK issues. For chapter-scope enforcement in RLS, join against the coordinator_assignments (or equivalent) table using `auth.uid()`. Write the DOWN migration in the same PR.
Tag the migration with a comment referencing the Bufdir reporting requirement so future maintainers understand the compliance motivation. Coordinate with the team to ensure no other migration is in flight that touches activity_records simultaneously.
Testing Requirements
Write SQL-level unit tests (pgTAP or Supabase's built-in test framework) covering: (1) INSERT with recorded_by_user_id = NULL succeeds for any authenticated user, (2) coordinator can INSERT with recorded_by_user_id pointing to a peer mentor in their chapter, (3) coordinator cannot INSERT with recorded_by_user_id pointing to a peer mentor outside their chapter, (4) peer mentor cannot set recorded_by_user_id to another user's ID. Additionally, write a Flutter integration test that seeds the migration on a local Supabase instance and verifies the schema via a raw query.
CI must run the migration against a clean database on every PR.
Adding recorded_by_user_id to the activities table and writing correct RLS policies is error-prone: overly permissive policies would allow coordinators to record activities under arbitrary user IDs they do not manage, while overly restrictive policies would silently block valid proxy inserts. A policy defect here would either create a security vulnerability or break the entire proxy feature at runtime.
Mitigation & Contingency
Mitigation: Write RLS policies in a local Supabase emulator first. Include policy unit tests using pg_tap or supabase test helpers. Have a second reviewer check the migration SQL before merging. Explicitly test the three cases: coordinator inserting for their own mentors (should succeed), coordinator inserting for another chapter's mentors (should fail), peer mentor inserting for themselves (should succeed as before).
Contingency: If a policy defect is discovered in staging, roll back the migration with a down-migration script. Delay feature release until the policy is corrected and re-verified. Apply a feature flag to keep the proxy entry point hidden from coordinators until the fix is confirmed.
The insert_bulk_activities RPC must behave atomically — a failure on row 7 of 12 must roll back rows 1–6. If Supabase's RPC transaction handling is misconfigured or if network interruptions cause partial acknowledgements, some peer mentors could receive duplicate or missing activity records, directly corrupting Bufdir statistics for the coordinator's chapter.
Mitigation & Contingency
Mitigation: Implement the RPC as a PostgreSQL function with explicit BEGIN/EXCEPTION/END block to guarantee atomicity. Add an integration test that inserts a batch where one row violates a unique constraint and asserts zero rows are committed. Document the transaction semantics in code comments.
Contingency: If atomicity cannot be guaranteed via RPC (e.g., due to Supabase plan limitations), fall back to a sequential insert loop with a compensating DELETE in case of partial failure, and surface a clear error to the coordinator listing which mentors failed and which succeeded.