How DeskcommCRM Ensures Cross-Tenant Isolation in Its Test Suite: RLS Verification and Service-Role Guards

DeskcommCRM guarantees cross-tenant isolation by validating PostgreSQL Row-Level Security (RLS) policies through invariant tests, simulating multi-tenant queries in unit tests, and enforcing explicit organization_id filters on service-role clients that bypass RLS.

DeskcommCRM is a Supabase-based CRM that uses Row-Level Security to enforce tenant boundaries at the database level. Its test suite treats cross-tenant isolation as a critical invariant, employing a three-layer defense strategy that combines schema validation, realistic query simulation, and explicit access guards.

Invariant Tests That Verify RLS Is Enabled

The first line of defense ensures that every table containing tenant data has RLS physically enabled in PostgreSQL. The file tests/invariants/rls-isolation.test.ts (implementing the G1-02 RLS isolation invariant) queries the catalog to confirm that relrowsecurity is true for all relevant relations.

If a developer adds a new table—such as contacts, crm_leads, or organizations—but omits ALTER TABLE ... ENABLE ROW LEVEL SECURITY, this test fails immediately during CI. The invariant checks the pg_class system catalog:

// tests/invariants/rls-isolation.test.ts
await db.query(`
  SELECT relname, relrowsecurity
  FROM pg_class
  WHERE relname IN ('contacts', 'crm_leads', 'organizations')
`);

// Every tenant table must have RLS enforced
expect(row.relrowsecurity).toBe(true);

This prevents accidental data exposure caused by forgotten RLS policies before any application code reaches production.

Unit Tests That Simulate Real-World Query Isolation

Beyond schema checks, the suite validates that Supabase clients respect these policies at runtime. The test tests/unit/telas-filtram-a-organizacao-ativa.test.ts creates two distinct organizations, inserts records for each, and then executes queries using a standard (non-service-role) client.

Because the test connects with a JWT simulating a specific user, Supabase applies the RLS policies defined for that user's organization. The test asserts that the result set contains only rows where organization_id matches the authenticated tenant:

// tests/unit/telas-filtram-a-organizacao-ativa.test.ts
await supabase.from('organizations').insert([{ name: 'Org A' }, { name: 'Org B' }]);

// Query executed with Org A's authenticated client
const { data: leads } = await supabase
  .from('crm_leads')
  .select()
  .eq('organization_id', orgAId); // RLS automatically enforces tenant boundary

expect(leads.every(l => l.organization_id === orgAId)).toBe(true);

This proves that even if application logic inadvertently omits a filter, the database layer blocks access to foreign tenant data.

Safeguarding Service-Role Clients with Explicit Filters

Service-role keys bypass RLS entirely, creating a potential leak vector. DeskcommCRM mitigates this by requiring that every service-role query include an explicit, programmatic organization_id filter. This pattern is enforced in lib/supabase/admin.ts and verified by tests such as tests/unit/agent-watchdog.test.ts and tests/unit/mcp-agendamento-tools.test.ts.

As noted in the source code comments: "Service role bypassa RLS → toda query filtra organization_id programático" (Service role bypasses RLS → every query filters organization_id programmatically).

The implementation wraps the admin client in a scopedQuery helper that mandates the tenant identifier:

// lib/supabase/admin.ts
import { createClient } from '@supabase/supabase-js';

export const admin = createClient(
  process.env.SUPABASE_URL,
  process.env.SERVICE_ROLE_KEY,
  { auth: { persistSession: false } }
);

export async function scopedQuery<T>(table: string, orgId: string) {
  // Manual tenant guard because admin bypasses RLS
  return admin.from<T>(table).eq('organization_id', orgId);
}

Tests like tests/unit/agent-watchdog.test.ts deliberately fail if a service-role query is executed without the mandatory organization_id filter, ensuring that background jobs, webhooks, and MCP tools cannot accidentally span tenants.

Integration Tests for Webhooks and Background Processes

Additional invariants in tests/invariants/webhooks-rls.test.ts and tests/invariants/followup-isolamento.ts verify that asynchronous processes—such as webhook handlers and scheduled follow-up jobs—respect the same boundaries. These tests spawn events for multiple organizations and confirm that handlers process only the records belonging to the triggering tenant, even when using elevated privileges.

Summary

• Schema-level enforcement: tests/invariants/rls-isolation.test.ts verifies that relrowsecurity is enabled on every tenant table, catching forgotten RLS policies during CI.
• Runtime validation: tests/unit/telas-filtram-a-organizacao-ativa.test.ts simulates real users and confirms that the database filters data correctly based on JWT claims.
• Service-role guards: The codebase mandates explicit organization_id filters in lib/supabase/admin.ts, with tests like tests/unit/agent-watchdog.test.ts ensuring that bypassed RLS cannot lead to cross-tenant leaks.
• Comprehensive coverage: Webhook and background-job tests extend these guarantees to asynchronous workflows, maintaining isolation across all execution contexts.

Frequently Asked Questions

How does DeskcommCRM prevent service-role clients from leaking tenant data?

The repository treats service-role keys as dangerous primitives. Every query using the admin client in lib/supabase/admin.ts must pass through the scopedQuery helper, which programmatically appends an organization_id filter. Tests such as tests/unit/agent-watchdog.test.ts enforce this contract by failing any test that executes a service-role query without the explicit tenant filter, ensuring that RLS bypass does not become a data leak vector.

What happens if a developer forgets to enable RLS on a new table?

The invariant test tests/invariants/rls-isolation.test.ts queries the PostgreSQL catalog table pg_class and asserts that relrowsecurity is true for all tables storing tenant data. If a new table lacks RLS, the assertion fails immediately, blocking the CI pipeline and preventing the code from reaching production.

Does the test suite verify actual SQL RLS policies or just application logic?

The suite verifies both. The invariant tests confirm physical RLS enablement in the database schema (relrowsecurity = true), while unit tests like tests/unit/telas-filtram-a-organizacao-ativa.test.ts execute real Supabase clients against a test database to ensure that the SQL policies actually restrict rows based on the authenticated user's organization_id claim.

How are multi-tenant scenarios simulated during testing?

The test suite creates isolated organization records and generates JWTs with specific organization_id claims to simulate distinct tenants. For example, tests/unit/telas-filtram-a-organizacao-ativa.test.ts inserts data for "Org A" and "Org B," then issues queries from each tenant's perspective, asserting that the result sets are strictly partitioned and contain no cross-tenant contamination.

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