Data access and tenancy

Generated from .claude/skills/data-layer-and-tenancy/SKILL.md, which ships in every project created from this template. Your agent loads it on demand; this page is the same text. Edit the skill, not this page.

Every request-path read and write goes through one of three primitives from @/db/tenant. There is no exported pool client — @/db/client is importable only from src/db/** and src/lib/auth/**, and a lint rule enforces that.

The three primitives

// Tenant data. Opens a transaction and sets app.current_org_id, which is what
// makes the Postgres policies apply. This is the one you want.
await withTenant(ctx, async (tx) => tx.select().from(projects));

// Untenanted public tables — the waitlist. No org, no policy.
await withPublic(async (tx) => tx.insert(waitlist).values(entry));

// Runs as the table OWNER and therefore BYPASSES row-level security.
await withSystem(async (tx) => /* cross-tenant work */);

withSystem is importable only from src/jobs/**, src/app/api/webhooks/**, src/lib/auth/**, scripts/** and test fixtures. If you find yourself wanting it anywhere else, the answer is almost always withTenant — reach for withSystem only when the work is genuinely cross-tenant, and say why in a comment.

Why this is not a convention you can skip

Tenant tables have a Postgres policy:

USING (organization_id = nullif(current_setting('app.current_org_id', true), '')::uuid)

Competitors enforce tenancy with where(eq(t.organizationId, orgId)) by convention, which leaks the first time anyone hand-writes a query. Here a query that forgets the filter returns zero rows, because the database refuses. The lint rule stops the bypass being accidental; the policy is what stops it being possible.

Still write the explicit filter. The shipped queries include eq(projects.organizationId, ctx.organizationId) even though the policy would enforce it anyway — the predicate is what lets the composite index on (organization_id, created_at) be used, so dropping it turns an index scan into a filtered sequential scan. RLS is the correctness backstop, not the query plan.

The nullif matters: on a pooled connection, once a GUC has been set and reset, current_setting returns an empty string rather than NULL, so ''::uuid would raise 22P02 instead of denying. Do not simplify it away.

This requires a pooler in transaction mode. set_config(..., true) is transaction-scoped, so session pooling would leak one tenant's setting into another's query. Neon's -pooler endpoint is correct; the direct endpoint is used for DATABASE_ADMIN_URL.

TenantContext

export type TenantContext = { userId; organizationId; role /* + brand */ };

It is branded — an unexported unique symbol — so it cannot be constructed outside src/lib/auth/context.ts. That makes "I forgot the auth check" a compile error rather than a runtime hole: a query takes ctx as its first parameter, and the only way to obtain one is requireTenant(), which has already established who the caller is.

Do not add another constructor, and do not cast to it. If you need one in a test, unsafeTestContext exists and is named that way on purpose.

Where authorization lives

At the data layer. Every query and mutation takes ctx: TenantContext first.

src/proxy.ts is redirect UX only — Next renamed this file from middleware precisely because it is a network boundary, not a security one. It checks cookie presence and redirects. It must not import @/db, @/lib/auth or a feature, and both the boundaries graph and no-restricted-imports enforce that.

Its matcher lists PUBLIC paths, so a new route is private by default. Add to PUBLIC_PATHS only deliberately.

Roles and permissions

Never compare a role to a literal:

if (ctx.role === 'owner')
  // lint error
  if (can(ctx, 'projects.delete'))
    // correct
    requireRole(ctx, 'admin'); // correct — reads the rank table
requirePermission(ctx, 'projects.delete'); // throws FORBIDDEN

Grants live in src/config/permissions.ts, so adding a role is one edit rather than a hunt through call sites — and the ones you would have missed fail silently.

Ownership BELOW the tenant is yours to enforce

tenantColumns and the RLS policy model one thing: which organization a row belongs to. They say nothing about which user owns it. So for "the feedback they submitted" or "my drafts", the database will happily show one colleague another's rows — both are in the same organization, so the policy is satisfied.

That needs an explicit column and an explicit predicate on every read and write:

// schema.ts
submittedBy: (text('submitted_by')
  .notNull()
  .references(() => user.id, { onDelete: 'cascade' }),
  // queries.ts — the org comes from RLS, the owner does not
  where(
    and(
      eq(feedback.organizationId, ctx.organizationId),
      eq(feedback.submittedBy, ctx.userId),
    ),
  ));

Apply it to updates and deletes too, or a colleague holding feedback.update can edit your row.

The generated isolation test still passes with the ownership predicate removed, because it only ever creates two organizations. If you build a per-user resource, add a fixture that puts a second member in the same organization and assert they cannot see or modify the first member's rows. Nothing else will tell you.

Reads and writes are separate files

• queries.ts — takes ctx, calls withTenant itself, throws on failure (error.tsx catches it in a Server Component)
• mutations.ts — takes tx and ctx, does not open its own transaction, so the caller composes several writes atomically

// queries.ts
export async function listProjects(ctx: TenantContext, filters: Filters) {
  return withTenant(ctx, (tx) => tx.select().from(projects) /* … */);
}

// mutations.ts
export async function insertProject(
  tx: Transaction,
  ctx: TenantContext,
  data: New,
) {
  const [row] = await tx
    .insert(projects)
    .values({ ...data, organizationId: ctx.organizationId })
    .returning();
  if (!row) throw new Error('Insert returned no row');
  return row;
}

A bare new Error for an invariant violation like that is correct — it should become an opaque INTERNAL. What must not happen is a bare Error at the action boundary, where it reaches the user as "Something went wrong" with the real reason only in the logs.

Adding a tenant table

Do not hand-write it. lq generate feature <name> --fields "..." emits the table, its indexes, the RLS policy and a tenant-isolation test — as two migrations, the table then its policy, both registered in the journal.

If you do add one by hand, it needs all of:

1. ...tenantColumns spread into the table
2. ALTER TABLE … ENABLE ROW LEVEL SECURITY plus a policy, in a migration
3. that migration registered in drizzle/meta/_journal.json and a paired drizzle/meta/<NNNN>_snapshot.json alongside it — copy the previous snapshot and give it a fresh id with prevId pointing at the one before. The generator writes both; a journal entry without its snapshot is not enough.

Point 3 is not optional bookkeeping. A .sql file that is not in the journal is never applied — this exact bug shipped a table with no policy while db:migrate reported success. lq check's structure step now reads the journal rather than the directory for that reason.

Join tables and other hand-written relations

lq generate feature emits exactly one table, so a many-to-many relation is hand-work. Put the join table in the owning feature's schema.ts, spread tenantColumns into it like any other table, give it a composite primary key, and write its RLS migration by hand following the pattern above.

Reaching another feature's table is the one case where @/db/schema is the right import — tables are not part of a feature's barrel, and the layering graph allows feature → db. The shipped projects/schema.ts imports organizations exactly that way.

One trap: a foreign key check does not respect row-level security. So verify that both ends belong to the caller's organization before inserting a link, or a cross-tenant id surfaces as an opaque INTERNAL from the constraint instead of a clean FORBIDDEN.

Never hand-edit a migration after it is committed. Add a new one.