Architecting Multi-Tenant Flutter Apps with Supabase and PostgreSQL RLS

1. The Vulnerability of Client-Side Authorization

In multi-tenant SaaS products—such as clinical hospital systems with multiple branch locations or enterprise retail suites—tenant isolation is a non-negotiable security requirement.

A novice architecture pattern is adding 'tenant_id' as a query parameter in Flutter client code: supabase.from('patients').select().eq('tenant_id', currentTenantId). This is fundamentally insecure. Any user with a modified APK or reverse-engineered JWT can modify the client-side query and read records belonging to rival tenants.

True multi-tenancy requires database-enforced security: PostgreSQL Row-Level Security (RLS). When RLS is active, the database engine itself rejects unauthorized queries, regardless of what the Flutter client requests.

2. Structuring PostgreSQL RLS Policies for Supabase

Supabase passes authenticated JWTs directly to PostgreSQL session variables. By injecting the tenant ID and user role into the app_metadata or user_metadata claims of the auth JWT, PostgreSQL policies can inspect them via auth.jwt().

Here is the exact production policy pattern we utilize in multi-branch enterprise platforms:

-- 1. Enable Row-Level Security on the sensitive table
ALTER TABLE hospital_consultations ENABLE ROW LEVEL SECURITY;

-- 2. Create helper function to extract tenant ID from the active Supabase JWT
CREATE OR REPLACE FUNCTION current_user_tenant_id()
RETURNS UUID AS $$
  SELECT NULLIF(current_setting('request.jwt.claims', true)::jsonb -> 'app_metadata' ->> 'tenant_id', '')::UUID;
$$ LANGUAGE SQL STABLE;

-- 3. Read policy: Users can ONLY view rows matching their tenant_id
CREATE POLICY tenant_isolation_select_policy ON hospital_consultations
  FOR SELECT
  USING (
    tenant_id = current_user_tenant_id()
  );

-- 4. Role-based mutation policy: Only Doctors and Branch Admins can create records
CREATE POLICY role_based_insert_policy ON hospital_consultations
  FOR INSERT
  WITH CHECK (
    tenant_id = current_user_tenant_id()
    AND (current_setting('request.jwt.claims', true)::jsonb -> 'app_metadata' ->> 'role') IN ('doctor', 'branch_admin')
  );

3. Clean Flutter Client Integration

With RLS active on the backend, the Flutter client code becomes dramatically simpler, faster, and more secure.

The repository does not need complex condition checks before fetching records. The client simply invokes .select()—PostgreSQL automatically applies the active policies and returns only records belonging to the authenticated tenant.

class SupabaseConsultationRepository implements ConsultationRepository {
  final SupabaseClient supabase;

  SupabaseConsultationRepository(this.supabase);

  @override
  Future<List<ConsultationModel>> fetchActiveQueue() async {
    // No manual tenant filtering required!
    // PostgreSQL RLS automatically scopes this query to the authenticated tenant.
    final response = await supabase
        .from('hospital_consultations')
        .select('id, patient_name, queue_number, status, created_at')
        .eq('status', 'waiting')
        .order('queue_number', ascending: true);

    return (response as List)
        .map((json) => ConsultationModel.fromJson(json))
        .toList();
  }
}

4. Handling Offline Sync with RLS

In enterprise retail or hospital shop floors, devices frequently experience internet drops. When queuing local writes to SQLite or Hive and syncing them back to Supabase upon reconnect, always preserve the original actor ID and tenant ID.

When syncing batched offline operations, write a PostgreSQL stored procedure (RPC) with transactional rollbacks. If one record in an offline batch violates an RLS policy, the entire transaction should roll back safely with an explicit audit log.