Implement Supabase query layer for organization fetching

epic-organization-selection-and-onboarding-data-layer-task-002 — Implement the concrete OrganizationRepository backed by the Supabase client. Write the query to fetch all organizations the authenticated user is a member of, applying RLS policies automatically. Map raw Supabase JSON rows to typed Organization domain models. Handle network errors with typed exceptions and implement retry logic for transient failures.

critical priority medium complexity integration pending integration specialist Tier 1

Acceptance Criteria

SupabaseOrganizationRepository implements OrganizationRepository and is registered in the DI container (Riverpod provider)

fetchAllActive() queries the organizations table with a filter for is_active = true and returns only orgs the authenticated user is a member of (RLS enforces membership automatically)

fetchById(orgId) queries a single organization row and throws OrgNotFoundException (typed) if the row is not found or RLS blocks access

watchAll() subscribes to Supabase Realtime on the organizations table and emits updated lists on INSERT, UPDATE, and DELETE events

All raw JSON rows are mapped via Organization.fromJson — no dynamic casting outside the fromJson factory

Network errors (timeouts, connection failures) are caught and rethrown as OrgNetworkException with the original error wrapped

Transient failures are retried up to 3 times with exponential backoff (500ms, 1s, 2s) before throwing

watchAll() stream is cancelled and Realtime subscription is unsubscribed when the provider is disposed

fetchAllActive() returns an empty list (not null) when the user has no active organization memberships

The implementation does NOT use the Supabase service role key — only the anon/user-scoped client

Code compiles cleanly with zero `flutter analyze` warnings

Technical Requirements

frameworks

Flutter

Dart (latest)

Riverpod (for provider registration and lifecycle management)

Supabase Flutter SDK

apis

Supabase PostgreSQL 15 (PostgREST: .from('organizations').select().eq('is_active', true))

Supabase Realtime (websocket subscription on 'organizations' table)

Supabase Auth (authenticated client — anon key with user JWT)

data models

assignment (JOIN to resolve user's org memberships if RLS uses assignment table)

contact (authenticated user cross-reference for RLS)

performance requirements

fetchAllActive() must complete within 3 seconds on a standard mobile connection

Select only required columns (id, name, logo_url, is_active, branding_config, label_overrides, feature_flags) — not SELECT *

Realtime subscription must reconnect automatically on WebSocket disconnect without manual intervention

security requirements

Use only the user-scoped Supabase client (anon key + user JWT) — never the service role key in mobile app

RLS policies on the organizations table enforce that users only receive rows they are members of — do not add client-side membership filtering as a substitute for RLS

All PII in organization records (if any) subject to encryption at rest per Supabase RLS security policy

Realtime subscription validated via JWT on every channel subscription per Supabase Realtime security policy

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Place the implementation in `lib/data/repositories/supabase_organization_repository.dart`. Register it as a Riverpod `Provider` (not a StateNotifier) since it is a pure data layer object with no UI state. For retry logic, implement a simple `_withRetry()` helper method using a loop with `await Future.delayed(backoffDuration)` — do not introduce a new retry package unless one is already in pubspec.yaml. For watchAll(), use Supabase Realtime's `postgres_changes` event on the organizations table.

Wrap the stream in a StreamController and add a finalizer/dispose callback in the Riverpod provider's `ref.onDispose` to cancel the Realtime subscription — memory leaks from undisposed Realtime channels are a common Flutter/Supabase bug. For error mapping, create a private `_mapSupabaseError(Object e)` method that converts PostgrestException, SocketException, and TimeoutException to the appropriate typed domain exceptions — this keeps the query methods clean.

Testing Requirements

Unit tests using mocktail to mock the Supabase client and query builder chain. Test: fetchAllActive() calls the correct query with is_active filter; fetchById() throws OrgNotFoundException on empty result; retry logic fires 3 times before throwing OrgNetworkException; empty result returns empty list not null. Integration test (optional, CI-gated): against a local Supabase instance via Docker, verify RLS correctly filters organizations by authenticated user. Use flutter_test for all tests.

Mock the Supabase Realtime channel for watchAll() tests — verify subscribe() and unsubscribe() are called at correct lifecycle points. Minimum 85% branch coverage on the repository implementation.

Component

Organization Repository

data medium

Dependencies (1)

Create the typed Organization domain model class mapping all Supabase row fields to strongly-typed Dart properties. Define the abstract OrganizationRepository interface with methods for fetching all active organizations, fetching a single org by ID, and exposing a reactive Stream<List<Organization>>. Establish the contract that all higher-level services will depend on. epic-organization-selection-and-onboarding-data-layer-task-001

Epic Risks (2)

high impact low prob technical

TenantSessionStore must write to both SecureStorageAdapter and Supabase session synchronously. If the Supabase write succeeds but the local secure storage write fails (or vice versa), the system ends up in an inconsistent state: local app thinks org A is selected but Supabase queries are scoped to org B (or unscoped), causing silent data leakage or empty result sets.

Mitigation & Contingency

Mitigation: Implement the dual-write with compensating rollback: if the second write fails, undo the first write and surface a typed DualWriteFailureError to callers. Add a startup integrity check in restoreSession() that validates local storage and Supabase session agree on the current org_id.

Contingency: If integrity check fails on startup, clear both stores and redirect the user to the org selection screen rather than proceeding with potentially mismatched state.

medium impact low prob integration

An organization could be deactivated in Supabase between the time the org list is cached and the time the user taps to select it. If the repository serves stale cached data the org-selection-service will attempt to seed a context for an inactive org, potentially causing RLS scope issues or confusing error states.

Mitigation & Contingency

Mitigation: The OrganizationRepository.getOrganizationById() path used during selection validation always performs a fresh network fetch (bypassing cache) to confirm the org is still active before the TenantSessionStore writes anything.

Contingency: If a freshness check finds the org is inactive, surface a localized error message on the selection screen ('This organization is no longer available') and refresh the org list to show only currently active partners.

Quick Links

All Tasks Execution Plan