Service-Role Handler Pattern in DeskcommCRM: Filtering organization_id and Bypassing RLS

DeskcommCRM implements a four-step pattern where service-role API handlers detect environment capabilities via isServiceRoleConfigured(), conditionally use createAdminClient() to bypass RLS, and always enforce explicit .eq("organization_id", orgId) filters from trusted sources to maintain strict tenant isolation.

Multi-tenant Supabase applications face a critical architectural challenge: privileged operations require bypassing Row-Level Security (RLS) via the service-role key, but this bypass eliminates the database's automatic tenant isolation. In the DeskcommCRM repository (melgarafael/DeskcommCRM), every route under app/api/** follows a rigid pattern to safely handle this trade-off while preventing cross-tenant data exposure.

The Four-Step Service-Role Pattern

The architecture enforces mandatory manual filtering whenever the service-role client is active. This convention is documented in the repository's AGENTS.md and implemented consistently across all API handlers.

1. Detect Service-Role Configuration

Handlers begin by checking for the presence of a valid service-role key. The helper isServiceRoleConfigured() (defined in [lib/audit/index.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/audit/index.ts#L18-L32)) inspects the SUPABASE_SERVICE_ROLE_KEY environment variable to determine if privileged operations are available.

2. Select the Appropriate Supabase Client

Based on the detection result, the code branches between two clients:

• createAdminClient() (from [lib/supabase/admin.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/supabase/admin.ts#L1-L15)): Bypasses RLS entirely, granting full table access
• createClient(): The standard user-scoped client that respects RLS policies

This conditional selection ensures RLS is only bypassed when absolutely necessary for enrichment or administrative tasks.

3. Enforce Explicit organization_id Filtering

Regardless of which client is selected, every query touching tenant-aware tables must include an explicit filter:

.eq("organization_id", orgId)

The orgId value must originate from a trusted source—such as a validated JWT/cookie, webhook secret, or secure path token—never from unvalidated user input. This manual filter compensates for the absence of RLS when using the admin client.

4. Degrade Gracefully When Service-Role Is Unavailable

In development environments where SUPABASE_SERVICE_ROLE_KEY is missing, handlers return reduced payloads (e.g., omitting user email or full names) rather than throwing errors. This ensures the application remains functional without privileged credentials while maintaining security boundaries.

Implementation Examples in app/api/

The repository demonstrates this pattern across several route handlers, each tailored to specific data access requirements.

Enriching Team Data with Service-Role

The assignable team members endpoint ([app/api/v1/team/assignable/route.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/app/api/v1/team/assignable/route.ts#L30-L40)) showcases the complete pattern. It selects the admin client only when available, then strictly filters by organization:

const client = isServiceRoleConfigured() ? createAdminClient() : await createClient();

await client.from("user_organizations")
  .select("user_id, role")
  .eq("organization_id", orgId) // <-- explicit tenant filter
  .is("revoked_at", null)
  .neq("role", "viewer")
  .order("created_at", { ascending: true });

When the service-role client is active, this handler enriches the response with additional user metadata (like full names) that RLS would normally block.

Standard Reads Without Privilege Escalation

Not all routes require the admin client. The basic team list ([app/api/v1/team/route.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/app/api/v1/team/route.ts)) uses the standard client throughout, relying on RLS for security while still explicitly filtering:

const supabase = await createClient();

await supabase.from("user_organizations")
  .select("user_id, role, …")
  .eq("organization_id", activeOrg.orgId) // explicit filter
  .order("created_at", { ascending: true });

This demonstrates that explicit organization_id filtering applies universally, even when RLS is active.

Hybrid Approach for Activity Reports

The activities report ([app/api/v1/reports/activities/route.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/app/api/v1/reports/activities/route.ts#L44-L57)) illustrates a sophisticated split: the main data fetch uses the RLS-protected client for security, while the admin client handles supplemental lookups only when necessary:

const supabase = await createClient();
const { data, error } = await supabase.rpc("fn_activity_report", { … });

if (idsDeUsuario.length > 0 && isServiceRoleConfigured()) {
  const admin = createAdminClient();
  // resolve names via admin client - enrichment only
}

This minimizes exposure while allowing privileged data resolution.

Shared Helpers for Consistent Filtering

Pipeline operations use a centralized helper ([app/api/v1/pipelines/_funis.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/app/api/v1/pipelines/_funis.ts)) that enforces the pattern across multiple routes:

await supabase
  .from("crm_pipelines")
  .select(COLUNAS)
  .eq("organization_id", orgId) // explicit filter
  .order("position", { ascending: true });

Centralizing the filter logic ensures no route accidentally omits the tenant constraint.

Critical Files in the Architecture

Understanding the service-role pattern requires familiarity with these specific files:

• lib/supabase/admin.ts – Defines createAdminClient(), which initializes the Supabase client with the service-role key and bypasses RLS. This file documents the requirement for manual organization_id filtering.
• lib/audit/index.ts – Provides isServiceRoleConfigured() to detect environment capabilities without exposing credential logic in route handlers.
• app/api/v1/team/assignable/route.ts – Canonical example of conditional client selection and mandatory tenant filtering.
• app/api/v1/reports/activities/route.ts – Demonstrates the hybrid security model where RLS handles primary filtering and service-role handles enrichment.
• app/api/v1/pipelines/_funis.ts – Shared query helper reinforcing the filtering pattern across the pipelines module.

Summary

• Always detect first: Use isServiceRoleConfigured() from lib/audit/index.ts to determine available privileges before executing database operations.
• Choose clients conditionally: Select createAdminClient() only when service-role access is required, otherwise use the standard createClient().
• Filter explicitly: Every query against tenant-aware tables must include .eq("organization_id", orgId) regardless of client type.
• Trust the source: Extract organization_id only from validated JWTs, secure cookies, or webhook secrets—never from request parameters.
• Degrade gracefully: Return partial data when service-role is unavailable rather than failing or bypassing security.

Frequently Asked Questions

How does DeskcommCRM prevent cross-tenant data leaks when bypassing RLS?

When using createAdminClient() to bypass RLS, the application loses Supabase's automatic tenant isolation. To compensate, every service-role handler enforces an explicit .eq("organization_id", orgId) clause on all queries. The orgId is sourced from cryptographically verified JWTs or secure session cookies, ensuring malicious users cannot manipulate the tenant scope even with full table access.

What happens if SUPABASE_SERVICE_ROLE_KEY is missing in the environment?

Handlers call isServiceRoleConfigured() to detect the missing key and automatically fall back to createClient(). In this mode, the application returns reduced payloads—such as user lists without email addresses or full names—rather than throwing errors. This graceful degradation ensures development environments remain functional without exposing production credentials.

Why is explicit organization_id filtering required when using the admin client?

The admin client ([lib/supabase/admin.ts](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/supabase/admin.ts)) bypasses all RLS policies, granting unrestricted access to every row in the database. Without explicit .eq("organization_id", orgId) filters, queries would return data from all tenants, causing catastrophic data breaches. The manual filter acts as a programmatic replacement for the disabled RLS policies.

Where should the organization_id value originate in service-role handlers?

The organization_id must come from trusted sources only: cryptographically signed JWT claims, validated server-side session cookies, or secure webhook secrets. Never extract this value from unvalidated request parameters, headers, or body content. In DeskcommCRM, routes typically parse this from the user's authenticated session or secure path tokens, ensuring the tenant scope is tamper-proof before executing privileged queries.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}