🏠 Overview › ✅ Implementation Tasks ›✅ epic-role-based-access-control-data-infrastructure-task-001

Create user_roles Supabase table schema

epic-role-based-access-control-data-infrastructure-task-001 — Design and implement the user_roles database table in Supabase, including columns for user_id, org_unit_id, role_name (peer_mentor, coordinator, org_admin), and org_id. Add appropriate indexes, foreign key constraints, and a get_my_roles RPC function that returns the calling user's role assignments scoped to their organization units.

critical priority medium complexity database pending database specialist Tier 0

Acceptance Criteria

user_roles table exists in Supabase with columns: id (uuid, PK), user_id (uuid, NOT NULL, FK → auth.users.id), org_id (uuid, NOT NULL), org_unit_id (uuid, NULLABLE), role_name (text, NOT NULL), is_active (boolean, NOT NULL, DEFAULT true), created_at (timestamptz, DEFAULT now())
role_name column has a CHECK constraint restricting values to ('peer_mentor', 'coordinator', 'org_admin')
Foreign key from user_id to auth.users(id) with ON DELETE CASCADE is applied
Composite index on (user_id, org_id) exists for query performance
Composite index on (user_id, org_unit_id) exists where org_unit_id is not null
get_my_roles() RPC function exists, is SECURITY DEFINER, and returns only rows where user_id = auth.uid()
get_my_roles() returns typed columns: id, user_id, org_id, org_unit_id, role_name, is_active
get_my_roles() filters out is_active = false rows by default
RPC function is callable from a Supabase client using supabase.rpc('get_my_roles') without parameters
Migration file is committed to the project migrations directory with a descriptive name
Table and function verified to exist in Supabase dashboard after migration runs

Technical Requirements

frameworks
Supabase
PostgreSQL
apis
Supabase RPC (get_my_roles)
Supabase Auth (auth.uid())
data models
UserRole
OrgUnit
Organization
performance requirements
get_my_roles() must return in under 100ms for users with up to 10 role assignments
Composite indexes must be present before RLS policies are enabled to avoid sequential scans
security requirements
get_my_roles() must be SECURITY DEFINER to bypass RLS and enforce its own filter on auth.uid()
Function must explicitly set search_path = public to prevent schema injection
No direct SELECT on user_roles table permitted without RLS — RLS is enabled in the next task

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Write the schema as a numbered SQL migration file (e.g., 20260001_create_user_roles.sql) so it can be applied idempotently. Use IF NOT EXISTS on CREATE TABLE and CREATE INDEX to allow re-runs. The get_my_roles function should use RETURNS TABLE(...) for typed return shape rather than RETURNS SETOF for easier Dart deserialization. Set SECURITY DEFINER and explicit search_path on the function.

Do not add RLS policies in this migration — those are handled in task-002. Keep the org_unit_id nullable to support org-level roles that are not scoped to a specific unit.

Testing Requirements

Verify migration with direct Supabase SQL queries in the dashboard. Confirm CHECK constraint rejects invalid role_name values (e.g., 'superuser'). Confirm foreign key cascade by inserting a test user, creating a role assignment, then deleting the user and verifying the role row is removed. Test get_my_roles() RPC by calling it as two different auth users and confirming each only receives their own rows.

Test that is_active = false rows are excluded from results.

Component
Supabase Role Data Provider
infrastructure low
Epic Risks (2)
medium impact medium prob technical

The get_my_roles RPC call adds a network round-trip immediately after login, potentially increasing the time before the home screen renders. If Supabase RPC is slow or the roles table lacks proper indexing, users with multiple org affiliations could experience noticeable delays.

Mitigation & Contingency

Mitigation: Index user_roles on user_id and org_unit_id. Use JWT claim extraction as the primary fast path; fall back to the RPC only when claims are absent or stale. Set a 3-second timeout with a fallback to cached roles.

Contingency: If RPC latency exceeds acceptable thresholds in production, pre-fetch and embed roles into the session JWT at login time via a Supabase Auth hook, eliminating the post-login RPC entirely.

high impact medium prob integration

Users who belong to multiple organizations (e.g., a coordinator in one NHF chapter who is also a peer mentor in another) may have conflicting role assignments. The repository layer must correctly scope roles to the active organization context set during the organization selection step, or it could return roles from the wrong org.

Mitigation & Contingency

Mitigation: Always filter role queries by the active org_unit_id stored in the tenant session. Write integration tests that simulate multi-org users and verify only the correct org's roles are returned.

Contingency: If org-scoping logic is found to be incorrect during QA, add an explicit org_unit_id parameter to get_my_roles RPC and require the client to always pass the active org context, making the scoping explicit rather than inferred.

Quick Links
All Tasks Execution Plan