Row-Level Security

Purpose

Row-Level Security (RLS) provides a defense-in-depth safety net for tenant data isolation. The primary isolation mechanism is application-level (withTenantFilter), but RLS prevents data leaks from bugs where a developer forgets to filter by project_id.

How It Works

Transaction-scoped GUCs

The withTenantSession() helper wraps database work in a transaction that sets PostgreSQL GUCs (Grand Unified Configuration) before executing queries:

SET LOCAL app.project_id = '<uuid>';
SET LOCAL app.is_operator = '<boolean>';
SET LOCAL app.organization_id = '<uuid>';

These SET LOCAL values only persist within the current transaction.

RLS Policies

Every tenant-scoped table has RLS enabled with a policy like:

ALTER TABLE units ENABLE ROW LEVEL SECURITY;

CREATE POLICY units_isolation ON units
  USING (
    project_id = current_setting('app.project_id', true)::uuid
    OR current_setting('app.is_operator', true)::boolean = true
  );

Operators bypass RLS when app.is_operator = true.

Migrations

Migration	RLS Related
0010_tenant_rls.sql	Enable RLS on all tenant-scoped tables, create policies
0011_force_tenant_rls.sql	FORCE RLS on critical tables
0014_tenant_constraints_pages_rls.sql	Additional RLS constraints on pages

Important Limitation

The Neon HTTP driver is stateless — every query is a separate HTTP round-trip. SET LOCAL only works within a single transaction. Therefore, all tenant-scoped DB work MUST go through withTenantSession(), which bundles the SET LOCAL statements and queries into one Neon HTTP transaction:

const result = await withTenantSession(ctx, (db) => [
  db.select().from(units).where(eq(units.id, id)),
]);

Design Decisions

CRM routes bypass RLS by design

CRM API routes (src/pages/api/admin/**) intentionally pass isOperator: true to withTenantSession, which sets app.is_operator = 'true' and disables RLS for that transaction. This is not a bug.

Tenant isolation for CRM routes is enforced at the application layer only, via:

• validateAdminSession() → resolves admin_sessions.active_project_id
• requireAdminApiAuth() → returns the active project ID to the handler
• Every CRM query filters by that project ID explicitly (eq(units.projectId, activeProjectId))

The RLS safety net applies to the buyer portal only — buyer routes resolve locals.tenant from the Host header and pass isOperator: false, so RLS gates every buyer query as a second layer of defense.

Implications:

• The Phase 7 exit criterion (“at least one buyer-portal route relies solely on RLS for isolation”) is buyer-side only by design.
• A bug in CRM activeProjectId resolution has no DB-side guard — application-level filters are the sole defense. Extra scrutiny on validateAdminSession, project-switching, and cross-project lookups in CRM handlers is warranted.
• New CRM routes should pass isOperator: true consistently with the rest of the surface. Do not “fix” this by flipping it to false without auditing every callsite — CRM routes touching agents rely on the operator bypass because the agents RLS policy keys on app.organization_id (which CRM handlers do not currently thread through).

Buyer isOperator caveat: The isOperator bypass is used only by CRM routes, never for buyer portal authentication. If a future buyer route inadvertently sets isOperator: true, it would bypass all RLS. Buyer middleware resolves locals.tenant from the Host header via resolveHost() and the isOperator field is always false (or undefined) for buyer requests. The operator token fallback in the buyer middleware is gated behind OPERATOR_PORTAL_TOKEN (a secret bearer token), not buyer OTP credentials.

Database driver: Neon HTTP (production)

src/db/index.ts uses the Neon HTTP driver (neon() + drizzle-orm/neon-http) for routine queries when DATABASE_URL points at Neon (.neon.tech). CI and local dev use node-postgres when the URL is a plain Postgres instance.

withTenantSession applies RLS GUCs via:

• getDb().batch() for sync array builders (one HTTP transaction, multiple statements), or
• getTransactionDb().transaction() for async builders and single-query thenables — uses the Neon WebSocket pool (same package) or node-postgres, because Neon HTTP cannot run interactive transactions.

This satisfies Locked Decision 6 and is a prerequisite for OPS2 (non-owner app role in production).

Tenant Scoping Flow

HTTP Request
    │
    v
middleware.ts ──> resolve tenant context → locals.tenant
    │
    v
API handler ──> withTenantSession(ctx, (db) => [
    │               SET LOCAL app.project_id = '...'
    │               SET LOCAL app.is_operator = '...'
    │               query1, query2, ...
    │           ])
    v
PostgreSQL ──> RLS evaluates against GUCs
    │
    v
Result: only rows matching tenant.project_id returned

Testing

A dedicated multi-tenant isolation test ensures RLS works:

• tests/multi-tenant-isolation.test.ts — authenticates as tenant A, attempts to read tenant B’s data, asserts empty result
• tests/tenant-invariants.test.ts — verifies tenant context is always set correctly