Tenant Context Wrapper

withTenantContext is the single entry point for all tenant-scoped database access. It wraps a callback in a PostgreSQL transaction with session variables that RLS policies read to filter rows.

Signature

// packages/db/src/client.ts
async function withTenantContext<T>(
  tenantId: string,
  userId: string,
  fn: (tx: typeof db) => Promise<T>,
): Promise<T>;

Parameters:

• tenantId -- the organization.id of the active tenant (from session.activeOrganizationId)
• userId -- the authenticated user's ID (from session.user.id)
• fn -- an async callback that receives the transaction object and returns a result

Returns: whatever fn returns, after the transaction commits.

What It Does

export async function withTenantContext<T>(
  tenantId: string,
  userId: string,
  fn: (tx: typeof db) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`);
    await tx.execute(sql`SELECT set_config('app.user_id', ${userId}, true)`);
    return fn(tx as unknown as typeof db);
  });
}

Step by step:

1. Opens a Drizzle transaction on the connection pool
2. Sets app.tenant_id as a transaction-local session variable (true = local to this transaction)
3. Sets app.user_id as a transaction-local session variable
4. Calls the provided function with the transaction object
5. Commits on success, rolls back on any thrown error

The set_config(..., true) third parameter is critical -- it scopes the setting to the current transaction only, so concurrent requests on different connections never see each other's tenant IDs.

How RLS Reads the Session Variable

Every tenant-scoped table has four policies (SELECT, INSERT, UPDATE, DELETE) generated by tenantPolicies() in packages/db/src/schema/base.ts. Each policy compares the row's organization_id column against the session variable:

// packages/db/src/schema/base.ts
const tenantIdExpr = sql`(SELECT current_setting('app.tenant_id'))`;

The expression is wrapped in a subselect (SELECT current_setting(...) instead of bare current_setting(...)) so PostgreSQL can evaluate it once as an initPlan and cache the result for the entire query. See Performance -- initPlan Caching for details.

The authenticated Role

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

export const authenticatedRole = pgRole("authenticated");

This role is granted basic CRUD on all tables via the 0001_setup_role_grants.sql migration. The connection pool itself runs as the superuser (which bypasses RLS), but withTenantContext ensures queries run within a transaction where the authenticated role's policies are active because the session variable is set. The policies use TO authenticated in their definitions, meaning they only apply when the role is authenticated.

In the tRPC middleware, the transaction is initiated by the superuser connection, but the RLS policies still fire because they are defined as PERMISSIVE FOR ... TO authenticated and the session variables are set. The key is that the authenticated role's policies are evaluated against the session variable values -- not against the connection role directly.

Transaction Scope

Everything inside the callback is a single PostgreSQL transaction:

• If the callback throws, the entire transaction rolls back
• If it returns successfully, all writes commit atomically
• Session variables are automatically cleaned up when the transaction ends

This means you can do multi-table writes and they either all succeed or all fail:

await withTenantContext(orgId, userId, async (tx) => {
  // Both inserts are in the same transaction
  await tx.insert(researchPlan).values({ ... });
  await tx.insert(planStep).values({ ... });
  // If planStep insert fails, the researchPlan insert is also rolled back
});

Type Signature of the Transaction Object

The tx parameter is typed as typeof db (the full Drizzle instance type), which means it supports the complete Drizzle query API:

• tx.select(), tx.insert(), tx.update(), tx.delete()
• tx.query.* (relational query API)
• tx.execute() (raw SQL)

The Database and Transaction types are exported from @repo/db for use in function signatures:

import type { Database } from "@repo/db";

async function myHelper(tx: Database, planId: string) {
  return tx.select().from(researchPlan).where(eq(researchPlan.id, planId));
}

Rules

1. Never call withTenantContext inside another withTenantContext -- Drizzle does not support nested transactions. The tRPC tenantProcedure already opens the context; router handlers receive ctx.db which is already the tenant-scoped transaction.

2. Never manually filter by organization_id in tenant-scoped queries. RLS handles it. Adding redundant WHERE clauses is not harmful but creates a false sense of security and clutters the code.

3. Both tenantId and userId must be non-empty strings from an authenticated session. Passing empty strings or fabricated values is a security violation.

4. The callback must not hold the transaction open for long-running work (HTTP calls, AI inference). Do the external work first, then open the transaction to write results.