# How DeskcommCRM Enforces Multi-Tenancy Using Supabase RLS Policies

> Learn how DeskcommCRM enforces multi-tenancy with Supabase RLS policies. Discover how organization boundaries are protected in PostgreSQL for secure data isolation.

- Repository: [Rafael Melgaço/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM)
- Tags: how-to-guide
- Published: 2026-09-12

---

**DeskcommCRM isolates tenant data by enforcing organization boundaries directly in PostgreSQL through Row-Level Security policies that validate against a Security Definer function, while requiring explicit manual filtering for service-role clients.**

DeskcommCRM is an open-source customer relationship management system that handles sensitive data for multiple organizations within a single database instance. Rather than relying on application-layer filtering, the repository implements **multi-tenancy using Supabase RLS** (Row-Level Security) policies that automatically restrict every query based on the user's organizational membership. This approach guarantees that users can only access rows belonging to organizations they are explicitly authorized to view.

## Core Architecture of Tenant Isolation

DeskcommCRM employs a three-layer defense strategy that combines database-level enforcement with application-level safeguards. The system leverages PostgreSQL's native RLS capabilities to ensure tenant isolation cannot be bypassed through SQL injection or application bugs.

### Tenant-Aware RLS Policies on Every Data Table

Every table storing tenant-specific data includes an `organization_id` column and an RLS policy that restricts access based on the user's authorized organizations. In [`supabase/migrations/20260911170000_0238_convites_de_time_persistidos.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/20260911170000_0238_convites_de_time_persistidos.sql), the policy for team invites demonstrates this pattern:

```sql
drop policy if exists team_invites_select on public.team_invites;
create policy team_invites_select on public.team_invites
    for select
    to authenticated
    using (organization_id in (select public.fn_user_org_ids()));

```

The `to authenticated` clause ensures the policy applies to all JWT-authenticated requests, while the `using` expression validates the row's `organization_id` against the set returned by `fn_user_org_ids()`. All tenant-aware tables—including `contacts`, `conversations`, and `crm_tasks`—implement identical policies generated through migration scripts in the `supabase/migrations/` directory.

### The fn_user_org_ids() Security Definer Function

The tenant resolution logic lives in `public.fn_user_org_ids()`, defined in [`supabase/migrations/20260905210000_0220_suporte_temporario_por_sessao.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/migrations/20260905210000_0220_suporte_temporario_por_sessao.sql). This function returns all organization IDs the current user belongs to, including standard memberships and active support sessions:

```sql
create or replace function public.fn_user_org_ids()
returns setof uuid
language sql stable security definer
set search_path = public as $f$
    select organization_id
    from public.user_organizations
    where user_id = auth.uid() and revoked_at is null
    union
    select (s->>'organization_id')::uuid
    from (select public.fn_support_context() s) c
    where s->>'status' = 'active';
$f$;

```

Marked as **`SECURITY DEFINER`**, the function executes with the privileges of the database owner rather than the invoking user. This guarantees consistent results regardless of the caller's role and prevents malicious clients from manipulating the tenant resolution logic. The function combines standard organization memberships from `user_organizations` with temporary support sessions when support staff require cross-tenant access.

### Service-Role Client Safeguards

Supabase's **service-role** key bypasses all RLS policies, making it essential for background jobs and workers to implement manual tenant filtering. The authentication layer in [`lib/auth/server.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/auth/server.ts) documents this requirement, enforcing that any service-role query must include an explicit `organization_id` filter:

```typescript
// Service-role client bypasses RLS - manual filtering required
const orgId = getOrganizationIdFromContext(); // Derived from JWT or support session
const { data } = await supabase
  .from('contacts')
  .select('*')
  .eq('organization_id', orgId);   // Explicit filter mandatory for service_role

```

The test suite in [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts) and [`tests/unit/telas-filtram-a-organizacao-ativa.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/telas-filtram-a-organizacao-ativa.test.ts) verifies that every service-role query contains this safeguard, catching any omissions that could lead to cross-tenant data leakage.

## Practical Implementation Patterns

DeskcommCRM distinguishes between two query contexts: standard user requests that rely on automatic RLS enforcement, and background operations that require manual tenant scoping.

### Standard Authenticated Requests

For normal application usage, client code requires no explicit organization filtering. The RLS policies automatically restrict results based on the authenticated user's JWT:

```typescript
// Authenticated client - RLS automatically filters by organization
const { data, error } = await supabase
  .from('contacts')
  .select('*');  // Only returns rows where organization_id matches fn_user_org_ids()

```

The database enforces the tenant boundary transparently, eliminating the risk of accidental data exposure through missing `where` clauses in application code.

### Background Workers and Service Roles

When using the service-role client for AI workers or cron tasks, developers must explicitly specify the organization:

```typescript
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

const orgId = 'd3f5c0a1-9b2e-4f6a-a5e7-c0a1b2c3d4e5';

const { data, error } = await supabase
  .from('crm_tasks')
  .select('*')
  .eq('organization_id', orgId);   // Mandatory manual filter

```

Omitting the `.eq('organization_id', ...)` clause would expose all tenant data, making the explicit filter a critical security requirement for service-role operations.

### Creating New Tenant-Aware Tables

When introducing a new table like `public.project_notes`, developers must follow the established migration pattern:

```sql
create table public.project_notes (
    id uuid primary key default gen_random_uuid(),
    organization_id uuid not null references public.organizations(id) on delete cascade,
    note text not null,
    created_at timestamptz not null default now()
);

alter table public.project_notes enable row level security;

drop policy if exists project_notes_select on public.project_notes;
create policy project_notes_select on public.project_notes
    for select
    to authenticated
    using (organization_id in (select public.fn_user_org_ids()));

```

This pattern ensures immediate RLS protection for standard users while alerting developers that any service-role code accessing this table requires explicit `organization_id` filtering.

## Verification and Testing Strategy

The codebase maintains rigorous invariant testing to prevent regression. The file [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts) confirms that every tenant-aware table utilizes `fn_user_org_ids()` within its RLS policies, while [`tests/unit/telas-filtram-a-organizacao-ativa.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/unit/telas-filtram-a-organizacao-ativa.test.ts) validates that screen-level queries respect the active organization scope. These tests ensure that schema changes cannot inadvertently disable tenant isolation.

## Summary

- **RLS policies** automatically filter all queries by checking if `organization_id` exists within the set returned by `fn_user_org_ids()`.
- The **`fn_user_org_ids()`** function runs as `SECURITY DEFINER` to prevent privilege escalation and includes logic for both standard memberships and temporary support sessions.
- **Service-role clients** bypass RLS entirely and must implement explicit `organization_id` filters to maintain tenant isolation.
- New tables require a strict migration pattern: add the `organization_id` column, enable row-level security, and create policies referencing `fn_user_org_ids()`.

## Frequently Asked Questions

### How does DeskcommCRM prevent users from accessing other organizations' data?

Every query executed by an authenticated user passes through RLS policies that compare the row's `organization_id` against the results of `fn_user_org_ids()`. Since this function runs as `SECURITY DEFINER`, users cannot manipulate the query to return unauthorized organizations, and the database automatically excludes rows from other tenants regardless of application logic.

### What is the difference between authenticated and service-role clients in DeskcommCRM?

Authenticated clients operate under the constraints of RLS policies defined in `supabase/migrations/`, automatically restricting data to authorized organizations. Service-role clients possess elevated privileges that bypass all RLS checks, requiring developers to manually append `.eq('organization_id', ...)` clauses to every query to maintain tenant boundaries.

### Why does fn_user_org_ids() need to be a SECURITY DEFINER function?

Without `SECURITY DEFINER`, the function would execute with the privileges of the calling user, potentially allowing privilege escalation or inconsistent results based on the caller's role. Running as the database owner ensures the function always returns accurate organization memberships based solely on the `auth.uid()` and support session context, independent of the user's database permissions.

### How do I add a new table that respects multi-tenancy in DeskcommCRM?

Create a migration that includes an `organization_id` column referencing `public.organizations(id)`, execute `alter table ... enable row level security`, and define a policy using `organization_id in (select public.fn_user_org_ids())`. Any code using the service-role client to access this table must include explicit `organization_id` filters, verified by the invariant test suite.