Row-Level Security — Multi-Tenant Data Isolation

HIPAA-07 — Available from v0.1.155

Postgres Row-Level Security (RLS) is enforced at the database level on all user-scoped tables. Even if an application query accidentally omits a userId WHERE clause, the database engine will prevent rows belonging to another tenant from being returned.

This is a defence-in-depth layer that sits beneath the existing Drizzle application-layer predicates.

---

How it works

Request arrives → auth() resolves userId
    ↓
withUserContext(userId, async (txDb) => {
  // Postgres sees: app.current_user_id = 'user-abc'  (transaction-local)
  // RLS policy: USING (user_id = current_app_user_id())
  // → only rows owned by 'user-abc' are visible
  return txDb.select().from(people);
})

1. The application resolves the authenticated userId from the session.
2. withUserContext(userId, fn) opens a real pg transaction and runs set_config('app.current_user_id', userId, TRUE) — the TRUE flag makes this setting transaction-local.
3. Inside fn, all queries to RLS-protected tables are automatically filtered by the Postgres policy. Rows not owned by userId are invisible — they cannot be selected, updated, or deleted.
4. When the transaction commits or rolls back the session variable is cleared automatically.

---

Tables protected by RLS

Table	Policy type
people	Direct user_id comparison
matches	Sub-select via people.user_id
match_reviews	Sub-select via matches → people.user_id or reviewer_id
user_settings	Direct user_id comparison
api_keys	Direct user_id comparison
notification_preferences	Direct user_id comparison
adverse_media_screenings	Direct user_id comparison
credit_accounts	Direct user_id comparison
credit_transactions	Direct user_id comparison
monitoring_billing_cycles	Direct user_id comparison
monitoring_charges	Direct user_id comparison
sms_log	Direct user_id comparison
feedback	Direct user_id comparison (nullable — NULL user_id rows visible to all)

---

Application helpers

withUserContext(userId, fn)

Use in user-facing API routes and tRPC procedures where you have a resolved user ID.

import { withUserContext } from "@/lib/db/rls";

const results = await withUserContext(session.userId, async (txDb) => {
  return txDb.select().from(people);
});

• Sets app.current_user_id = userId for the duration of fn().
• Automatically commits on success and rolls back on error.
• Strips null bytes from userId as a safety measure.

withSystemContext(fn)

Use in Inngest background jobs and any system-level operation that legitimately needs cross-tenant access (e.g. nightly OFSI sync, billing jobs).

import { withSystemContext } from "@/lib/db/rls";

await withSystemContext(async (txDb) => {
  // All RLS policies evaluate to TRUE — full table access
  return txDb.select().from(people);
});

• Sets app.bypass_rls = 'on' for the duration of fn().
• All RLS policies short-circuit via is_system_context() and permit full access.

isRlsAvailable()

Health-check helper that returns true when the database connection supports RLS (i.e. DATABASE_URL is a real Postgres connection, not a CI dummy).

import { isRlsAvailable } from "@/lib/db/rls";

if (await isRlsAvailable()) {
  // RLS is active
}

---

Background jobs and migrations

The migration (0026_rls_user_isolation.sql) and scripts/migrate.js runner run without a user context. The is_system_context() guard causes RLS policies to evaluate to TRUE when app.bypass_rls = 'on' is set, and NULL-safe current_setting(..., TRUE) calls prevent errors when neither setting is present — rows are simply not returned.

For Inngest functions, always wrap database calls in withSystemContext() to ensure full table access.

---

Technical notes

Why a separate pg.Pool?

The primary db client uses the Neon HTTP driver, which is stateless — each query is an independent HTTP request. SET LOCAL session variables have no effect across separate HTTP requests. The RLS helpers use a standard pg.Pool (5-connection cap) to maintain a real Postgres connection where SET LOCAL semantics work correctly within a transaction.

Graceful CI fallback

When DATABASE_URL is a dummy/CI value (e.g. postgresql://dummy:dummy@localhost/dummy), the pool is not initialised and the helpers pass through without setting session variables. The application-layer userId predicates in Drizzle queries remain the primary isolation guard in this case.

Idempotent migration

The migration is tracked in the _drizzle_migrations table and is safe to apply to an existing database. CREATE OR REPLACE FUNCTION and CREATE POLICY are used where appropriate.

---

Related

• Changelog — v0.1.155
• Migration file: src/db/migrations/0026_rls_user_isolation.sql
• Helper module: src/lib/db/rls.ts