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

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, 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.

// 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 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 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.

// 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.

// 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. A typical policy extracts the organization_id from the JWT claims and compares it against the row's organization_id column:

-- 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 (line 103), the code filters messages by the event's organization ID:

// 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, 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

The test suite in 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 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 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 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.

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