Architecture

This page is a detailed reference for the database-level mechanisms that power UseBetter Tenant: transaction-scoped session variables, Row-Level Security policies, RLS bypass for admin operations, and slug-to-UUID resolution.

Note

For a narrative walkthrough of the full request lifecycle and why this approach is safe, start with How RLS Works.

Request-scoped tenant and SET LOCAL

For each request (or explicit runWithTenant / runAs call), the adapter runs your code inside a transaction. At the start of that transaction it runs:

SELECT set_config('app.current_tenant', '<tenant-uuid>', true);

The third argument true means local to the transaction: the setting is only visible inside that transaction and is automatically cleared when the transaction ends.

• Pooling-safe: Connection pools can reuse connections; the next request gets a new transaction and its own app.current_tenant.
• No cross-request leakage: Session state is transaction-scoped, not connection-scoped.

Your tenant-scoped queries run in that same transaction, so Postgres RLS can read current_setting('app.current_tenant', true) and restrict rows to that tenant.

RLS: USING, WITH CHECK, and FORCE ROW LEVEL SECURITY

Tenants lookup table

The tenants table is a lookup table (no tenant_id column). The CLI generates two PERMISSIVE policies for it:

1. rls_tenants_read — FOR SELECT USING (true). All roles can read tenants — needed for tenant resolution (slug-to-UUID lookup) and dashboard listing.
2. rls_tenants_write — FOR ALL with USING and WITH CHECK requiring current_setting('app.bypass_rls', true) = 'true'. Only system operations (runAsSystem) can INSERT, UPDATE, or DELETE tenants.

This provides defense-in-depth: even direct database access as the application role cannot modify tenants without bypass_rls.

Tenant-scoped tables

Each tenant-scoped table needs:

1. tenant_id column — UUID NOT NULL REFERENCES tenants(id). Your ORM schema defines this (see Quick Start).
2. Row Level Security — ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY. Generated by the CLI. FORCE means RLS applies to the table owner role too — without it, the role that owns the table would bypass policies. Note that PostgreSQL superusers always bypass RLS regardless of FORCE; your application must connect as a regular (non-superuser) role.
3. Policy — one policy for ALL (SELECT, INSERT, UPDATE, DELETE) generated by the CLI, with:
   • USING: Rows are visible when (tenant_id)::text = current_setting('app.current_tenant', true) (or when bypass is set — see below).
   • WITH CHECK: New/updated rows must satisfy the same condition, so inserts and updates cannot set tenant_id to another tenant.

The set_tenant_id() trigger (also generated by the CLI) sets NEW.tenant_id from current_setting('app.current_tenant', true) on INSERT, so application code does not have to pass tenant_id manually (and cannot override it).

runAsSystem and RLS bypass

Some operations must see or change data across tenants: creating/updating/listing/deleting tenants, seeding, or admin/cron jobs. Doing that with a superuser would be a security anti-pattern. UseBetter Tenant uses a session flag instead.

Session flag: app.bypass_rls

The adapter’s runAsSystem(fn) runs fn inside a transaction that first runs:

SELECT set_config('app.bypass_rls', 'true', true);

Again, true = local to the transaction, so the flag is cleared when the transaction ends.

The CLI-generated RLS policies include an OR so that rows are allowed when either:

• the row’s tenant_id matches app.current_tenant, or
• current_setting('app.bypass_rls', true) = 'true'.

Example policy (conceptually):

USING (
  (tenant_id)::text = current_setting('app.current_tenant', true)
  OR current_setting('app.bypass_rls', true) = 'true'
)
WITH CHECK (
  (tenant_id)::text = current_setting('app.current_tenant', true)
  OR current_setting('app.bypass_rls', true) = 'true'
)

When the adapter runs with app.bypass_rls = 'true', the same RLS policies allow access to all rows in that transaction. No superuser or special role is required; the app role just needs the usual table privileges.

When to use runAsSystem

• Use for: tenant.api.* (create/update/list/delete tenants), CLI seed, migrations, or cron jobs that must touch multiple tenants.
• Do not use for normal request handling. Normal requests should use runWithTenant (or framework middleware that does), so RLS restricts data to a single tenant.

Caution

If you expose an endpoint that calls runAsSystem, that endpoint effectively has full read/write to all tenant data. Protect it with authentication and access control.

Non-tenant-aware tables

RLS is opt-in per table in Postgres. When the adapter runs SET LOCAL app.current_tenant = '<uuid>', only tables with ENABLE ROW LEVEL SECURITY and a matching policy are affected. Tables without RLS policies ignore the session variable — all rows remain visible regardless of which tenant is active.

This means you can freely mix tenant-scoped and shared tables in the same transaction:

const db = tenant.getDatabase();

// Table has RLS → filtered to current tenant
const projects = await db.select().from(projectsTable);

// Table has no RLS → all rows visible
const categories = await db.select().from(categoriesTable);

Which tables have RLS? Only the tables listed in tenantTables in your better-tenant.config.json (and processed by the CLI migrate command) get RLS policies, a tenant_id column, and the set_tenant_id() trigger. Everything else is untouched and behaves like a normal Postgres table.

For the recommended usage pattern (wrapping getDatabase() as your default db() handle), see Configuration — Non-tenant tables.

---

Slug-to-UUID resolution

The resolver extracts a raw identifier from the request (header, subdomain, path, JWT, or custom). This identifier may be a UUID or a slug (e.g., "acme" from acme.app.com). Since RLS requires a UUID, the library normalizes the identifier before it reaches the adapter.

How it works

Identifier	What happens
UUID (e.g. 550e8400-...)	Passes through unchanged
Slug (e.g. "acme")	Looked up via runAsSystem → getBySlug(slug) → returns the tenant’s UUID
Any value + resolveToId configured	resolveToId is called instead — skips all auto-resolution
Non-UUID without resolveToId	Looked up via getBySlug

Where it applies

• resolveTenant(request) — returns the normalized UUID (or undefined).
• handleRequest(request, next) — uses the normalized UUID for SET LOCAL and RLS.
• runAs(tenantId, fn) — passes tenantId through as-is; callers must provide a valid UUID.

resolveToId escape hatch

For custom mappings (e.g., custom domains → tenant UUIDs), configure resolveToId on the tenant resolver:

tenantResolver: {
  custom: (req) => req.host,
  resolveToId: async (domain) => {
    const mapping = await lookupCustomDomain(domain);
    return mapping.tenantId;
  },
}

resolveToId always takes precedence over auto-resolution. When provided, the library does not check if the identifier is a UUID or look up by slug — it trusts the transform.

Summary

Mechanism	Purpose
app.current_tenant	Set per transaction by the adapter; RLS uses it to restrict rows to one tenant.
app.bypass_rls	Set per transaction by the adapter in runAsSystem; policies allow all rows when 'true'.
Transaction-scoped	Both settings use set_config(..., true) so they are local to the transaction and safe with connection pooling.
FORCE ROW LEVEL SECURITY	Ensures RLS applies to all roles, including table owner.
Tenants table policies	Open SELECT for resolution; writes require bypass_rls (defense-in-depth for the lookup table).
runAsSystem	For admin/cron only; uses session flag, not superuser.
Non-tenant tables	Tables without RLS work through getDatabase() unchanged — session variables are ignored.

Next steps

• Configuration — resolver strategies, tenant API, and admin operations
• Framework Adapters — per-adapter setup for Drizzle, Prisma, Hono, Express, and Next.js
• CLI & Migrations — all CLI commands and workflows