critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

Migration file is created in supabase/migrations/ with a timestamped filename following project conventions
activities table has a new column registered_by uuid NOT NULL references auth.users(id)
activities table has a new column attributed_to uuid NOT NULL references auth.users(id)
A CHECK constraint or trigger prevents UPDATE of registered_by after insert (immutability enforced at database level)
A CHECK constraint or trigger prevents UPDATE of attributed_to after insert (immutability enforced at database level)
Index created on (attributed_to) for efficient mentor-scoped queries
Index created on (registered_by) for efficient coordinator audit queries
Composite index created on (attributed_to, date) to support the duplicate detection query (mentorId + date + activityTypeId lookups)
All existing RLS policies on activities table reviewed and remain valid — no policy broken by the new columns
Migration runs cleanly on a fresh Supabase project with `supabase db reset`
Migration is idempotent: re-running does not error (use IF NOT EXISTS where applicable)
Existing activity rows (if any) are handled with a sensible default or the migration documents that backfill is required

Technical Requirements

frameworks
Dart
Flutter
apis
Supabase PostgreSQL 15
data models
activity
performance requirements
Composite index on (attributed_to, date, activity_type_id) must support duplicate detection query with less than 20ms on tables up to 1M rows
security requirements
Row-Level Security policies must ensure coordinators can only read activities within their own organization_id
Peer mentors must only be able to read activities where attributed_to = auth.uid()
registered_by column must not be writable via the mobile client's anon/user role — only via service role or Edge Functions
Foreign key references to auth.users ensure referential integrity and prevent orphaned attribution records

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

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.

Component
Proxy Activity Repository
data medium
Epic Risks (3)
high impact medium prob technical

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.

high impact medium prob security

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.

medium impact low prob technical

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.