🏠 Overview › ✅ Implementation Tasks ›✅ epic-organization-feature-flags-foundation-task-002

Implement RLS policies for organization_configs

epic-organization-feature-flags-foundation-task-002 — Write and apply Supabase Row Level Security policies on the organization_configs table to ensure each authenticated user can only read flag rows belonging to their own organization_id. Include policies for SELECT, INSERT, UPDATE, and DELETE with proper role scoping for admin vs. read-only access.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS is enabled on the organization_configs table and cannot be bypassed by the anon or authenticated Supabase roles
A SELECT policy exists that restricts rows to those where organization_id matches the calling user's organization_id claim extracted from the JWT
An INSERT policy exists that allows only users with role 'admin' or 'org_admin' (from JWT app_metadata) to create new flag rows scoped to their own organization_id
An UPDATE policy exists with the same admin-role restriction; users cannot update flags belonging to a different organization
A DELETE policy exists restricting deletion to admin roles within the same organization
A read-only authenticated user (role: peer_mentor or coordinator) can SELECT their organization's flags but receives an empty result set or RLS error for any other organization
Service role key bypasses RLS as expected for server-side Edge Functions — documented and intentional
Policies are applied via a repeatable SQL migration file committed to source control, not applied ad hoc via the Supabase dashboard
Running the migration on a clean database produces identical policy state to the production database
All policies are verified with integration tests using two distinct test organizations to confirm cross-org isolation

Technical Requirements

frameworks
Supabase PostgreSQL 15
Supabase Auth (JWT claims)
apis
Supabase PostgreSQL RLS policy DDL
Supabase Auth JWT app_metadata for role claims
data models
organization_configs (feature flags table created in task-001)
performance requirements
RLS policy evaluation must not add more than 5ms to a standard SELECT query on the organization_configs table
Policies should use indexed columns (organization_id) to avoid full table scans during policy evaluation
security requirements
JWT claim extraction must use auth.jwt() -> 'app_metadata' -> 'organization_id' pattern — never trust user-supplied organization_id in the request body for RLS
Service role key must never be distributed to the Flutter mobile client — RLS bypass is server-side only
All RLS policies must be tested with a non-admin JWT to confirm privilege escalation is impossible
Migration script must not grant superuser or bypass-RLS permissions to the authenticated role
Row-Level Security must be enabled via ALTER TABLE ... ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Extract the organization_id from the Supabase JWT using `(auth.jwt() -> 'app_metadata' ->> 'organization_id')::uuid` — this is set server-side during user provisioning and cannot be forged by the client. Do NOT use a user-editable profile column for the organization_id comparison, as that would be exploitable. The migration file should be named with a timestamp prefix (e.g. `20260101_rls_organization_configs.sql`) and placed in `supabase/migrations/`.

Test policies locally with `supabase db reset` to apply all migrations from scratch. Be aware that Supabase's anon role also respects RLS — the mobile Flutter client uses the anon key with a user JWT attached, so policies apply as expected. Document the policy design in a comment block at the top of the migration file so future developers understand the trust model.

Testing Requirements

Integration tests are required using two isolated test organizations (org_a and org_b) with seeded organization_configs rows. Test matrix: (1) peer_mentor JWT for org_a can SELECT org_a flags and gets 0 rows for org_b; (2) coordinator JWT for org_a cannot INSERT or UPDATE; (3) org_admin JWT for org_a can INSERT/UPDATE/DELETE for org_a but not org_b; (4) anonymous (unauthenticated) request returns 401 or empty result set. Tests should run against a local Supabase instance (supabase start) to avoid polluting production. Use the supabase/tests SQL testing framework or a Dart integration test with supabase_flutter connecting to the local instance.

Minimum coverage: all 4 CRUD operations × 3 role types = 12 test cases.

Component
Feature Flag Repository
data medium
Dependencies (1)
Design and implement the Supabase database schema for the organization_configs table, including columns for organization_id, flag_key, enabled, rollout_percentage, min_app_version, activation_date, and metadata. Enforce multi-tenant isolation at the query level using organization_id as the partition key. epic-organization-feature-flags-foundation-task-001
Epic Risks (3)
high impact medium prob security

Supabase RLS policies for organization_configs may have gaps that allow cross-organization reads if the JWT claim for organization_id is absent or malformed, leading to data leakage between tenants.

Mitigation & Contingency

Mitigation: Implement RLS policies using auth.uid() joined against a memberships table to derive organization_id rather than trusting a client-supplied claim. Write integration tests that simulate a cross-org read attempt and assert it returns zero rows.

Contingency: If a gap is discovered post-launch, immediately disable the affected RLS policy, roll back the migration, and re-implement with a parameterized policy tested against all organization fixture data.

medium impact medium prob technical

Dart does not have a built-in semantic version comparison library; a naive string comparison (e.g., '2.10.0' < '2.9.0' lexicographically) would cause rollout evaluator to produce incorrect eligibility results for organizations on different app versions.

Mitigation & Contingency

Mitigation: Use the pub.dev `pub_semver` package or implement a proper three-segment integer comparison. Add parameterized unit tests covering 20+ version pairs including double-digit minor/patch segments.

Contingency: If incorrect comparison is discovered in production, push a hotfix with corrected comparison logic and temporarily disable phase-gated flags until all affected organizations have updated to the corrected version.

medium impact low prob technical

Persistent local cache written to shared_preferences or Hive could become corrupted or deserialized incorrectly after an app update changes the FeatureFlag schema, causing startup crashes or all flags defaulting to disabled.

Mitigation & Contingency

Mitigation: Wrap all cache reads in try/catch with explicit fallback to the all-disabled default map. Version the cache key (e.g., `feature_flags_v2_{orgId}`) so schema changes automatically invalidate old entries.

Contingency: If cache corruption is detected in a release, publish an app update that clears the versioned cache key on first launch and re-fetches from Supabase.

Quick Links
All Tasks Execution Plan