Tenant Scoping

Every table that stores tenant-owned data must enforce Row-Level Security (RLS). This is not optional -- it is the primary security boundary for multi-tenant isolation. Application-layer filtering is defense-in-depth; the database is the final safety net.

The Three-Part Pattern

A tenant-scoped table requires three things:

1. An organization_id column (the tenant identifier)
2. RLS policies generated by tenantPolicies()
3. The .enableRLS() call on the table definition

import { pgTable, text, integer } from "drizzle-orm/pg-core";
import { tenantColumns, tenantPolicies } from "./base";

export const myFeatureTable = pgTable(
  "my_feature_table",
  {
    id: text("id").primaryKey(),
    ...tenantColumns, // adds organizationId, createdAt, updatedAt
    score: integer("score").notNull(),
  },
  (table) => [
    ...tenantPolicies("my_feature_table", "organization_id"),
    index("my_feature_table_organizationId_idx").on(table.organizationId),
  ],
).enableRLS();

If any one of these three parts is missing, the table is not isolated. The migration generator will not warn you -- this is a convention enforced by code review and architecture audits.

How tenantPolicies() Works

The tenantPolicies() function in packages/db/src/schema/base.ts generates four PostgreSQL policies for a single table:

export function tenantPolicies(tableName: string, columnName = "tenant_id") {
  const col = sql.raw(columnName);
  const using = sql`${col} = ${tenantIdExpr}`;
  const withCheck = sql`${col} = ${tenantIdExpr}`;

  return [
    pgPolicy(`${tableName}_select`, {
      as: "permissive",
      for: "select",
      to: authenticatedRole,
      using,
    }),
    pgPolicy(`${tableName}_insert`, {
      as: "permissive",
      for: "insert",
      to: authenticatedRole,
      withCheck,
    }),
    pgPolicy(`${tableName}_update`, {
      as: "permissive",
      for: "update",
      to: authenticatedRole,
      using,
      withCheck,
    }),
    pgPolicy(`${tableName}_delete`, {
      as: "permissive",
      for: "delete",
      to: authenticatedRole,
      using,
    }),
  ];
}

Each policy restricts the authenticated PostgreSQL role to rows where the tenant column matches the session variable app.tenant_id.

The initPlan Caching Optimization

The tenant ID expression wraps current_setting() in a SELECT subquery:

const tenantIdExpr = sql`(SELECT current_setting('app.tenant_id'))`;

This is not cosmetic. Without the SELECT wrapper, PostgreSQL evaluates current_setting() per-row. With it, PostgreSQL promotes the expression to an initPlan, evaluating it once per query. The performance difference is 1-3% vs 10-100x overhead on large tables.

Policy Naming

Policies are named {tableName}_{operation}:

• research_plan_select
• research_plan_insert
• research_plan_update
• research_plan_delete

These names appear in EXPLAIN ANALYZE output and in PostgreSQL's pg_policies catalog, making it straightforward to debug which policy is being evaluated.

The authenticated Role

RLS policies target a dedicated PostgreSQL role named authenticated, defined in packages/db/src/schema/roles.ts:

export const authenticatedRole = pgRole("authenticated");

This role has no default privileges. The migration 0001_setup_role_grants.sql grants it SELECT, INSERT, UPDATE, DELETE on all current and future tables in the public schema:

GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

The withTenantContext() function switches to this role inside each transaction via SET ROLE authenticated (done by Drizzle's transaction machinery). Because RLS is enabled on tenant-scoped tables, the authenticated role can only see rows matching its app.tenant_id session variable.

An anonymous role is also defined but currently unused -- reserved for future public access patterns.

Default-Deny Behavior

When app.tenant_id is not set (the session variable is missing or empty), current_setting('app.tenant_id') returns an empty string. The comparison organization_id = '' is always FALSE, so zero rows are returned.

This is critical: a missing withTenantContext() call results in an empty result set, not a data leak. The system fails safe rather than failing open.

This behavior is explicitly tested in the RLS integration tests at packages/db/src/__tests__/rls.test.ts.

Which Tables Are Tenant-Scoped

Tenant-Scoped (RLS Enabled)

Table	Schema file	Notes
member	auth.ts	Better Auth org membership
invitation	auth.ts	Better Auth org invitations
ai_batch	ai.ts	Batch job lifecycle
ai_usage	ai.ts	Per-call metering
ai_call_details	ai.ts	Full request/response content
research_plan	research.ts	Top-level research plans
plan_step	research.ts	Steps within a plan
plan_branching_condition	research.ts	Conditional plan logic
plan_audit_log	research.ts	Immutable event log
research_artifact	research.ts	Research outputs (JSONB)
extraction_result	research.ts	Structured extractions
skill_execution	research.ts	Skill invocation tracking
mcp_tool_call_log	research.ts	MCP tool call audit log
research_output	research.ts	Formatted deliverables
research_feedback	research.ts	User satisfaction data
document_chunk	search.ts	Chunked + embedded content

Global (No RLS)

Table	Schema file	Why no RLS
user	auth.ts	Users exist across organizations
session	auth.ts	Session resolves the user, not the tenant
account	auth.ts	OAuth accounts are user-scoped
verification	auth.ts	Email verification tokens are user-scoped
organization	auth.ts	Organizations are the tenant boundary itself
ai_model	ai.ts	Global reference data (all tenants read)
ai_model_pricing	ai.ts	Global reference data
personal_access_token	mcp.ts	User-scoped, filtered by application code

Column Name: organization_id, Not tenant_id

Better Auth's organization plugin uses organization_id as its column name. Rather than maintain a mapping layer between two naming conventions, all Trovella tables use organization_id directly.

When calling tenantPolicies(), pass the column name explicitly:

...tenantPolicies("my_table", "organization_id"),

The second parameter defaults to "tenant_id" for historical reasons, but every table in the codebase passes "organization_id" explicitly.

Querying Tenant-Scoped Tables

All tenant-scoped queries must use withTenantContext(), not the bare db connection:

import { withTenantContext } from "@repo/db";

const plans = await withTenantContext(orgId, userId, async (tx) => {
  return tx.select().from(researchPlan).where(eq(researchPlan.status, "executing"));
});

The bare db object connects as the superuser and bypasses RLS entirely. It is intentionally not exported from @repo/db's main entry point -- only from @repo/db/client, which is restricted by ESLint no-restricted-imports.

See Identity & Access -- Tenant Isolation for the enforcement layers that prevent accidental bare db usage.