🏠 Overview › ✅ Implementation Tasks ›✅ epic-activity-type-configuration-foundation-task-002

Configure RLS policies for activity_types

epic-activity-type-configuration-foundation-task-002 — Define and apply Row Level Security policies on the activity_types table scoped by org_id. Ensure coordinators and peer mentors can read only their organisation's activity types, and that only org admins can insert, update, or delete records.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS is confirmed enabled on activity_types (ALTER TABLE activity_types ENABLE ROW LEVEL SECURITY was applied in the previous migration)
Policy 'activity_types_select_org_member' allows SELECT for authenticated users whose JWT claim org_id matches the row's org_id
Policy 'activity_types_insert_org_admin' allows INSERT only for users whose JWT claim role equals 'org_admin' and whose org_id matches
Policy 'activity_types_update_org_admin' allows UPDATE only for org_admin role matching org_id
Policy 'activity_types_delete_org_admin' allows DELETE only for org_admin role matching org_id
A coordinator JWT can SELECT rows for their org but receives empty result for another org's rows (no error, just no data)
A peer mentor JWT can SELECT rows for their org
A coordinator or peer mentor JWT receives a permission denied error when attempting INSERT, UPDATE, or DELETE
An org_admin JWT can INSERT, UPDATE, and DELETE rows scoped to their own org_id
An org_admin JWT cannot INSERT or modify rows for a different org_id
Policies are defined in a dedicated migration file (not mixed with table creation)
All policies use (auth.jwt() ->> 'org_id')::uuid for org matching and (auth.jwt() ->> 'role') for role matching

Technical Requirements

frameworks
Supabase
apis
Supabase Auth API
Supabase Database API
data models
ActivityType
Organization
UserRole
performance requirements
RLS policy expressions must use indexed columns (org_id) to avoid full table scans — verified with EXPLAIN ANALYZE showing index usage even with RLS applied
security requirements
No policy must use USING (true) or WITH CHECK (true) without scope restriction
JWT claims must be read from auth.jwt() — never from a user-supplied parameter
Policies must be tested with Supabase's built-in RLS testing tools or pgTAP
Service role key bypasses RLS by design — document that server-side operations using service role must independently validate org_id

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

JWT claims structure must be agreed with the auth configuration. Confirm that the Supabase JWT includes org_id and role as top-level custom claims — check the auth.users and auth hook configuration. If org_id is nested (e.g., under app_metadata), update the policy expression accordingly: (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid. Use separate CREATE POLICY statements for each operation rather than combining them, as this makes auditing and modification easier.

Name policies consistently using the pattern

__. Store policies in supabase/migrations/20250226120001_activity_types_rls.sql. Coordinate with the team's auth setup to confirm the exact JWT claim path used for org_id and role across the project — reuse the same pattern used by other tables' RLS policies for consistency.

Testing Requirements

Write SQL tests using Supabase's RLS testing approach: use SET LOCAL role = authenticated and SET LOCAL request.jwt.claims = '...' to simulate JWT contexts within a transaction, then verify SELECT/INSERT/UPDATE/DELETE outcomes. Test matrix: (coordinator, own org, SELECT) → success; (coordinator, other org, SELECT) → 0 rows; (coordinator, own org, INSERT) → error; (peer_mentor, own org, SELECT) → success; (org_admin, own org, INSERT) → success; (org_admin, other org, INSERT) → error. All tests must run in a transaction that is rolled back to leave the database clean. Include these tests in the supabase/tests/ directory.

Component
Supabase Client
infrastructure low
Dependencies (1)
Write and apply a Supabase database migration that creates the activity_types table with all required columns: id, org_id, name, is_active, metadata (JSONB), created_at, updated_at. Include appropriate indexes on org_id and is_active for query performance. epic-activity-type-configuration-foundation-task-001
Epic Risks (3)
high impact medium prob technical

The JSONB metadata column has no enforced schema at the database level. If the Dart model and the stored JSON diverge (e.g., a field is renamed or a new required flag is added without a migration), the metadata resolver will silently return null or throw at parse time, breaking conditional wizard logic for all organisations.

Mitigation & Contingency

Mitigation: Define a versioned Dart Freezed model for ActivityTypeMetadata and add a Supabase check constraint or trigger that validates the JSONB structure on write. Document the canonical metadata schema in a shared constants file and require schema review for any metadata field additions.

Contingency: Implement a lenient parse path in ActivityTypeMetadataResolver that returns safe defaults for missing fields and logs a structured warning to Supabase edge logs, allowing the app to degrade gracefully rather than crash.

high impact low prob security

If RLS policies on the activity_types table are misconfigured, a coordinator from one organisation could read or mutate activity types belonging to another organisation, violating data isolation guarantees required by all three client organisations.

Mitigation & Contingency

Mitigation: Write integration tests against the Supabase local emulator that explicitly assert cross-org isolation: a token scoped to org A must receive zero rows when querying org B activity types, and upsert attempts must return permission-denied errors.

Contingency: Apply an emergency RLS policy patch via Supabase dashboard without a code deploy. Audit all activity_type rows for cross-org contamination and restore from backup if any data leakage is confirmed.

medium impact medium prob integration

If the cache invalidation call in ActivityTypeService is not reliably triggered after an admin creates, edits, or archives an activity type, peer mentors on the same device will see stale data in the registration wizard until the next app restart, leading to confusion and potential misregistrations.

Mitigation & Contingency

Mitigation: Enforce a strict pattern: ActivityTypeService always calls cacheProvider.invalidate() inside the same try block as the successful Supabase mutation, before returning to the caller. Write a widget test that verifies the cache notifier emits an updated list after a service mutation.

Contingency: Add a background Supabase Realtime subscription on the activity_types table that triggers cache invalidation automatically, providing an independent safety net independent of the service call path.

Quick Links
All Tasks Execution Plan

Generated by Eircodex v1.0.0