Supabase schema migration: proxy attribution columns
epic-proxy-activity-registration-foundation-task-001 — Write and apply a Supabase migration that adds `registered_by` (uuid, NOT NULL, references auth.users) and `attributed_to` (uuid, NOT NULL, references auth.users) columns to the activities table. Both columns must be immutable after insert. Include appropriate indexes and update existing RLS policies to remain compatible.
Acceptance Criteria
Technical Requirements
Implementation Notes
Implement immutability using a PostgreSQL trigger function: `BEFORE UPDATE ON activities FOR EACH ROW EXECUTE FUNCTION prevent_attribution_update()` where the function raises an exception if NEW.registered_by != OLD.registered_by or NEW.attributed_to != OLD.attributed_to. This is more portable than CHECK constraints for immutability. For existing rows: if the table already has data, add the columns as nullable first, backfill with a sensible default (e.g., attributed_to = peer_mentor_id, registered_by = peer_mentor_id), then add the NOT NULL constraint in a second migration step to avoid locking issues on large tables. Document the two-step approach in migration comments.
Testing Requirements
Database migration tests: (1) apply migration to a clean schema and verify columns exist with correct types and constraints via information_schema queries, (2) attempt to UPDATE registered_by on an inserted row and verify it is rejected with a constraint violation, (3) attempt to UPDATE attributed_to and verify the same rejection, (4) insert a row and verify both columns accept valid auth.users UUIDs, (5) run EXPLAIN ANALYZE on a duplicate-detection query pattern to confirm index is used. These tests can be run via Supabase CLI with a local Docker instance.
The activities table migration adding registered_by and attributed_to columns may conflict with existing RLS policies or FK constraints if the user profile table structure differs from assumptions, blocking all subsequent epics.
Mitigation & Contingency
Mitigation: Review existing activities table schema and RLS policies before writing the migration. Run the migration against a staging database clone first. Write rollback scripts alongside the migration.
Contingency: If migration fails in staging, isolate the conflict with a targeted schema audit, adjust FK references or RLS policy scope, and re-run before touching production.
The RLS policy must filter proxy inserts to the coordinator's chapter scope. If the chapter-scope resolver pattern differs between organisations (multi-chapter coordinators in NHF vs single-chapter in HLF), the policy may be too broad or too restrictive.
Mitigation & Contingency
Mitigation: Design the RLS policy to accept a coordinator's full set of assigned chapter IDs (array) rather than a single chapter_id. Validate the policy against NHF multi-chapter test fixtures during the integration test phase.
Contingency: If the policy is found to be incorrect after deployment, introduce a server-side validation edge function as a safety net while the RLS policy is corrected.
The bulk_register_activities RPC function may time out or cause lock contention when inserting large participant batches (e.g. 40+ peer mentors in a single group session), degrading the user experience.
Mitigation & Contingency
Mitigation: Benchmark the RPC function with 50-participant batches during development. Use unnest-based bulk insert rather than row-by-row PL/pgSQL loops. Set a reasonable statement_timeout.
Contingency: If performance is insufficient, split the client-side submission into chunks of 20 participants with progress feedback, rather than a single RPC call.