# How DeskcommCRM Implements Multi-Tenant Row Level Security (RLS) Using JWT Organization ID Propagation

> Learn how DeskcommCRM ensures multi-tenant Row Level Security RLS by validating JWTs and injecting organization_id into PostgreSQL session variables for secure data isolation.

- Repository: [Rafael Melgaço/DeskcommCRM](https://github.com/melgarafael/DeskcommCRM)
- Tags: architecture
- Published: 2026-09-13

---

**DeskcommCRM enforces strict tenant isolation by validating JWTs server-side, automatically injecting the `organization_id` claim into PostgreSQL session variables, and relying on native Row Level Security policies to filter every database query at the row level.**

DeskcommCRM is a multi-tenant customer relationship management system built on Supabase and PostgreSQL. The application implements **multi-tenant Row Level Security (RLS)** by propagating the `organization_id` from validated JWTs directly into the database session context, ensuring that no query—whether from an API route or a background worker—can access data outside the requesting user's organizational boundary.

## Establishing the Security Context via JWT Validation

The authentication flow guarantees that every request carries a valid, server-verified JWT before any database interaction occurs.

### Server-Side Token Verification with `loadAuthUser`

In [`lib/auth/server.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/auth/server.ts), the `loadAuthUser()` function validates the incoming token by calling `supabase.auth.getUser()`. The implementation explicitly avoids `getSession()`, which only parses the client-side cookie without server-side verification, ensuring the JWT signature and expiration are cryptographically validated before proceeding.

```typescript
// lib/auth/server.ts
const user = await loadAuthUser();   // uses supabase.auth.getUser()

```

### PostgreSQL Session Variable Injection

Once validated, Supabase automatically sets two critical session variables for the PostgreSQL connection:

- **`request.jwt.claims`**: Contains the decoded JWT claims, including `sub` (user ID) and `org_id` (organization ID).
- **`auth.role`**: Set to `authenticated` for normal users or `service_role` for internal service clients.

These variables are transparent to the application code but are essential for the RLS policies. The test suite in [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts) demonstrates this pattern explicitly using `SELECT set_config('request.jwt.claims', …)` to simulate different tenant contexts.

## Resolving the Active Organization and Role

After authentication, the application determines which tenant context the request should execute within.

### Cookie-Validated Membership with `resolveActiveOrg`

The `requireRole()` function in [`lib/auth/require-role.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/auth/require-role.ts) serves as the canonical RBAC gate for every `/api/v1/*` route. It first resolves the active organization by calling `resolveActiveOrg(user)`, which validates the user's membership list against a server-side cookie. Optionally, the function accepts an explicit `organizationId` parameter (lines 64–78) for operations targeting a specific organization different from the user's default.

```typescript
// lib/auth/require-role.ts (lines 10-16, 64-78)
const org = await resolveActiveOrg(user);   // reads cookie-validated membership

```

### Role Verification via `fn_user_role_in_org`

To determine permissions, `requireRole()` invokes the `fn_user_role_in_org(p_org)` RPC (lines 92–100). This PostgreSQL function runs with `SECURITY DEFINER` privileges and queries the database using the tenant's `organization_id`. Because the RPC itself operates under the same RLS policies as the application, it provides a single, trustworthy source of truth for role resolution.

```typescript
// lib/auth/require-role.ts (lines 92-100)
const { data: effectiveRole } = await supabase.rpc(
  "fn_user_role_in_org",
  { p_org: org.orgId }
);

```

## Enforcing Tenant Isolation at the Database Layer

The database itself acts as the final enforcement point through PostgreSQL RLS policies defined in migration files.

### RLS Policy Definitions in Migration Files

All tables storing tenant-scoped data include policies that reference the `request.jwt.claims` session variable. These policies are defined in `supabase/migrations/*.sql` and consolidated in [`supabase/baseline.sql`](https://github.com/melgarafael/DeskcommCRM/blob/main/supabase/baseline.sql). A typical policy extracts the `organization_id` from the JWT claims and compares it against the row's `organization_id` column:

```sql
-- supabase/migrations/*.sql
CREATE POLICY tenant_isolation
  ON messages
  USING (
    auth.uid() = (current_setting('request.jwt.claims')::json->>'sub')
    AND organization_id = (current_setting('request.jwt.claims')::json->>'org_id')::uuid
  );

```

### The Security-Definer RPC Pattern

The `fn_user_role_in_org` function operates under `SECURITY DEFINER`, meaning it executes with the privileges of the function owner rather than the invoking user. This design ensures that role lookups bypass RLS restrictions for the lookup itself while still respecting the tenant boundary established by the `organization_id` parameter, preventing privilege escalation across tenants.

## Defense-in-Depth with Application-Level Filters

Even with database-level RLS, the application implements secondary safeguards to prevent data leaks.

### Explicit `organization_id` Filtering in Workers

Background workers using the service-role client—which bypasses RLS—must explicitly append `.eq("organization_id", <orgId>)` to every query. For example, in [`workers/ai-sentiment-worker.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/workers/ai-sentiment-worker.ts) (line 103), the code filters messages by the event's organization ID:

```typescript
// workers/ai-sentiment-worker.ts (line 103)
await supabase
  .from("messages")
  .select("id, body, organization_id")
  .eq("organization_id", event.organization_id);

```

This pattern appears consistently across all worker files in `workers/*.ts`, ensuring that even if an RLS policy were temporarily disabled or bypassed, the query would still only return rows for the intended tenant.

### Service-Role Client Safeguards

The service-role client, created via `createClient()` in [`lib/supabase/server.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/supabase/server.ts), possesses elevated privileges necessary for background tasks. However, the application treats this power as a fallback rather than a primary mechanism. By mandating explicit `organization_id` filters in code, DeskcommCRM maintains tenant isolation even when operating with superuser-equivalent database roles.

## Verifying RLS Invariants and Authorization Trails

The repository includes comprehensive mechanisms to verify that isolation guarantees hold and to track authorization attempts.

### Automated Testing with [`rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/rls-isolation.test.ts)

The test suite in [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts) executes invariant tests that simulate different tenant contexts by manually setting `request.jwt.claims`. These tests verify that queries only return rows belonging to the organization specified in the session variables. The CI job `invariants` runs these tests as part of the Definition of Done, preventing regressions in multi-tenant isolation.

### Audit Logging for Denied Access

Whenever `requireRole()` detects an authorization failure—whether due to insufficient role, missing MFA, or invalid organization—it emits an `authz.denied` audit event (lines 17–24 and 38–46). The audit payload includes the resolved `organizationId`, creating a traceable record of every access denial for security monitoring and compliance purposes.

## Summary

- **Server-side JWT validation** via `loadAuthUser()` in [`lib/auth/server.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/lib/auth/server.ts) ensures tokens are cryptographically verified using `supabase.auth.getUser()` rather than `getSession()`.
- **PostgreSQL session variables** (`request.jwt.claims`) automatically propagate the `organization_id` from the JWT into the database connection context.
- **RLS policies** in `supabase/migrations/*.sql` enforce row-level filtering based on the JWT claims, ensuring tenants cannot access each other's data.
- **Security-definer RPCs** like `fn_user_role_in_org` provide a trusted source of role information while respecting tenant boundaries.
- **Application-level filters** via `.eq("organization_id", …)` in workers and API routes provide defense-in-depth, particularly for service-role clients that bypass RLS.
- **Invariant tests** in [`tests/invariants/rls-isolation.test.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/tests/invariants/rls-isolation.test.ts) and audit logging guarantee that isolation mechanisms function correctly and that security events are traceable.

## Frequently Asked Questions

### How does DeskcommCRM prevent JWT session spoofing?

The application strictly uses `supabase.auth.getUser()` to validate JWTs on every request, which verifies the token signature against Supabase Auth's secret. This approach avoids `getSession()`, which only parses the client-side cookie without cryptographic validation, ensuring that forged or modified tokens are rejected before they reach the database layer.

### What happens if an RLS policy is accidentally disabled?

Even if Row Level Security were disabled on a table, the application maintains tenant isolation through explicit `.eq("organization_id", <orgId>)` filters in all service-role queries and API route handlers. This defense-in-depth strategy ensures that cross-tenant data leaks cannot occur solely due to a database configuration error.

### Can service-role clients bypass organization isolation?

While service-role clients inherently bypass RLS policies, DeskcommCRM prevents isolation breaches by mandating explicit `organization_id` filters in every worker and internal service query. For example, [`workers/ai-sentiment-worker.ts`](https://github.com/melgarafael/DeskcommCRM/blob/main/workers/ai-sentiment-worker.ts) always appends `.eq("organization_id", event.organization_id)` to its Supabase queries, ensuring that even privileged background processes cannot access data outside the target tenant.

### How is the `organization_id` claim added to the JWT?

The `organization_id` claim is embedded in the JWT during the authentication flow by Supabase Auth, based on the user's active organization membership stored in the database. When `loadAuthUser()` validates the token, Supabase automatically populates `request.jwt.claims` with this metadata, making the organization context available to both application code and PostgreSQL RLS policies without manual injection.