Database schema: proxy_activities table with dual-identity fields
epic-bulk-and-proxy-registration-services-task-001 — Create the database migration that extends the activities table (or creates proxy_activities) to include the recorded_by_user_id column alongside the existing peer_mentor_id column. Define RLS policies so coordinators can only read/write rows where recorded_by_user_id matches their own user ID. Add indexes on (peer_mentor_id, activity_type, date) for duplicate detection queries and on (recorded_by_user_id) for coordinator audit views.
Acceptance Criteria
Technical Requirements
Implementation Notes
Prefer extending the existing activities table with ALTER TABLE activities ADD COLUMN recorded_by_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL; over creating a separate proxy_activities table — this avoids duplicating all existing activity columns and keeps query logic unified. Use a partial index for the coordinator audit index: CREATE INDEX idx_activity_coordinator ON activities(recorded_by_user_id) WHERE recorded_by_user_id IS NOT NULL; — this skips NULL entries (legacy records) and keeps the index compact. For RLS, write two separate policies: one for the existing peer mentor access pattern and a new coordinator policy. Use USING (auth.uid() = recorded_by_user_id) with WITH CHECK (auth.uid() = recorded_by_user_id) for both read and write enforcement.
Ensure the migration wraps all DDL in a transaction (BEGIN/COMMIT) so a partial failure rolls back cleanly. Document the dual-identity semantics clearly in a SQL comment: peer_mentor_id = the mentor being represented; recorded_by_user_id = the coordinator who entered the data.
Testing Requirements
SQL-level tests using Supabase's pgTAP or a test migration script: (1) Insert a row with recorded_by_user_id = user_A, authenticate as user_A, assert SELECT returns the row. (2) Authenticate as user_B, assert SELECT returns 0 rows for user_A's proxy records. (3) Insert a row with recorded_by_user_id = NULL (legacy), assert existing peer mentor RLS policy still applies. (4) Run EXPLAIN ANALYZE on a duplicate detection query (WHERE peer_mentor_id = X AND activity_type = Y AND date = Z) and assert the composite index is used (Index Scan in plan).
(5) Run the migration on a copy of the staging database and assert no existing rows are altered. (6) Run the rollback migration and assert the column and indexes are removed. Include these as scripts in supabase/tests/ runnable via supabase test db.
The Proxy Registration Service must verify that the coordinator has a legitimate assignment relationship with the target peer mentor before creating a record. If this check is implemented only in application code and not enforced at the DB/RLS level, a compromised or buggy client could bypass it by calling the Supabase endpoint directly, creating fraudulent proxy records for arbitrary peer mentors.
Mitigation & Contingency
Mitigation: Implement permission validation at two levels: (1) application-layer check in Proxy Registration Service that queries the assignments table before constructing the payload, and (2) RLS policy on the activities table that restricts INSERT to rows where recorded_by_user_id matches the authenticated user AND peer_mentor_id is in the set of peer mentors assigned to that coordinator. The RLS policy is the authoritative guard; the service-layer check provides early user-facing feedback.
Contingency: If RLS policy implementation is blocked by Supabase plan constraints, implement a Supabase Edge Function as a proxy endpoint that enforces the permission check server-side before forwarding to the DB. Disable direct client inserts entirely for proxy activities.
For a bulk session with 30 selected peer mentors, the Proxy Duplicate Detector must query existing activities for each mentor. If implemented as 30 sequential Supabase queries, round-trip latency could make the bulk confirmation screen feel slow (>3s), degrading coordinator experience and potentially causing timeouts.
Mitigation & Contingency
Mitigation: Implement the duplicate check as a single Supabase query using an IN clause on peer_mentor_id combined with the activity_type and date filters, returning all potential duplicates for the entire batch in one network round-trip. Group results client-side by mentor ID to produce the per-mentor warning structure.
Contingency: If the single-query approach returns too much data for very large chapters, add a database index on (peer_mentor_id, activity_type, date) and profile query time. If still insufficient, accept a short loading state on the confirmation screen with a progress indicator rather than pre-loading duplicates before navigation.