Query Patterns

All tenant-scoped data access in Trovella flows through a single function: withTenantContext. This topic explains what that function does, how the tRPC procedure chain builds the context around it, and what patterns you should follow when writing queries.

Core Principle

The bare database connection (db) runs as the PostgreSQL superuser and bypasses RLS. Tenant-scoped queries must always run inside withTenantContext, which opens a transaction, sets session variables, and switches to the authenticated role so that RLS policies filter every row.

For the RLS policies themselves and the CASL permission model, see Identity & Access -- Tenant Isolation.

Two Paths to the Database

Path	Import	RLS Active	Use When
Tenant-scoped	withTenantContext from @repo/db	Yes	Any query touching tenant data
Bare connection	db from @repo/db/client	No	Migrations, seeds, auth bootstrap, health checks

The package enforces this split at the export level. @repo/db does not export db -- you must explicitly import from @repo/db/client to get the bare connection, which makes the intent visible in code review.

How a Typical Request Flows

Browser request
  -> tRPC handler
    -> protectedProcedure   (validates session)
    -> tenantProcedure       (calls withTenantContext, injects ctx.db)
    -> authorizedProcedure   (looks up member, builds CASL ability)
    -> router handler        (uses ctx.db for all queries)

The ctx.db provided to router handlers is the transaction object from withTenantContext. RLS is active on every query through this object. You do not need to add WHERE organization_id = ... filters manually -- the database enforces it.

Pages in This Topic

Page	What It Covers
Tenant Context	withTenantContext internals -- session variables, transaction scope, type signature
Connection Management	Pool configuration, import paths, the Database and Transaction types
Procedure Chain	How tenantProcedure and authorizedProcedure build query context in tRPC
Drizzle Patterns	Common query patterns: select, insert, update, delete, raw SQL, relations
Performance	initPlan caching, parallel queries, pagination, aggregation
Non-Tenant Queries	When bare db is appropriate and how to use it safely

Related Topics

• Schema Design -- table definitions, tenantColumns, tenantPolicies
• Identity & Access -- Tenant Isolation -- RLS policies, CASL roles, the authenticated PostgreSQL role