Multi-tenancy in Drizzle, from a shipped product

Everything below is how Easinvy Retail, a multi-tenant billing and inventory product for shops, keeps one shop's rows away from another's. Easinvy Retail runs on Drizzle and PostgreSQL with every shop's data in the same tables, and its code was read for this note on 31 October 2026: the schema, the session check, every exported query function, and the places where each of them stops.

Drizzle will run a query with no tenant filter

Drizzle compiles db.select().from(bills) without complaint, and in a shared-table app that query returns every shop's bills. Drizzle is a query builder. It turns code into SQL, and it has no idea which rows a request is allowed to see.

The question is asked in exactly those terms on Drizzle's own repository. Discussion #1539, opened on 21 November 2023, asks: "Is there a way to enforce that all queries include a where clause on tenantId?" The asker wanted something that would "just check if it's there and throw an exception if it isn't." On 31 October 2026 the discussion had 13 comments and 13 replies from 16 participants, and it was still marked Unanswered. The proposals in it include branded types, a Proxy around the client, an ESLint rule, a wrapper that rolls a transaction back, and row-level security.

None of those is something Drizzle does for you. What follows is what one shared-table Drizzle application in production relies on instead, where each piece stops, and why the strongest proposal in the thread, row-level security, would have changed nothing for Easinvy Retail as it is deployed.

Ownership and uniqueness live in the schema

Easinvy Retail's schema decides who owns each row. On 31 October 2026, 19 tables carried a tenant_id. In 18 of them it cannot be missing: 16 declare it NOT NULL, two per-shop settings tables use it as their primary key, and all 18 reference tenants.id with a cascading delete:

tenantId: uuid("tenant_id")
  .notNull()
  .references(() => tenants.id, { onDelete: "cascade" }),

No shop row exists without a shop, and deleting a shop deletes its rows. The nineteenth table, callback_requests, is nullable on purpose: a request for a support call is kept as a record after the shop that made it is gone.

Uniqueness is per shop, through composite unique indexes that lead with tenant_id. bills_tenant_no_key is (tenant_id, bill_no), so every shop's first bill is number 1. The same pattern numbers purchases per shop, lets one customer exist in two shops under (tenant_id, name, mobile), and allows one attendance mark per person per day. A newer index adds a condition: bills_tenant_misc_day_key allows one live miscellaneous bill per shop per day, and only bills with status ACTIVE count towards it.

Where uniqueness is global, it is meant to be: an email address signs in to one account, and a shop's slug is part of a URL.

Easinvy Retail's schema guarantees that every shop row has an owner. It does not guarantee that a query asks for the right owner's rows.

The tenant comes from the session, checked on every request

Easinvy Retail takes the tenant from the verified session token. A shop slug in a URL is checked against the token, never used in its place. Every authenticated request also reads the membership row again, findSessionMembership(userId, tenantId), joined to the user. A removed member is refused on the next request, and so is a session that began before the user's sessions_valid_from. Why that read runs on every request, and the eighteen routes that once trusted the token instead, is the subject of how to revoke a JWT.

Routes that name a shop in their path add one more check. getShopSession in lib/route-auth.ts requires the slug in the path to equal the slug in the token, and the tenant that slug names to equal the tenant in the token. The comment above it explains why every failure gets the same answer:

Returns null for "no session", "no such shop", "token is for a different shop" and "membership revoked" alike. Callers turn all of these into the same response: distinguishing them would confirm which shop slugs exist to someone with no access to them.

Below the route, the session's tenant id is a value every query function receives as an argument. Nothing below the route decides the tenant again.

A missed filter did leak rows under MongoDB

Easinvy Retail ran on MongoDB before PostgreSQL, and its tenancy was a string. The comment at the top of lib/route-auth.ts records how that worked:

Under the Mongo model each query re-derived tenancy from a shopSlug string, so one forgotten filter leaked another shop's rows.

The audit written before the move listed it among defects found in the code: "Tenancy is enforced only by every query remembering to filter on shopSlug. One missed filter leaks another tenant's data."

It was not hypothetical. A query that was not scoped to its shop returned another shop's rows, and the fault was in the code. How the leak was noticed, and what was seen, is not recorded, so this note does not describe either.

The PostgreSQL rewrite did not ask for more care. It changed where the tenant lives: read once from the session, held on the rows as a tenant_id foreign key, and passed as a required argument to every query function. Whether that closes the gap the MongoDB version had is a narrower question than it sounds, and the answer has three parts: what the query audit found, what TypeScript catches, and what the database still accepts.

Every query function takes a tenant id

Easinvy Retail's data access lives in one layer, db/queries/, and its rule is that every function serving a shop's own requests takes the tenant id and filters on it. Even a read by primary key filters on the shop:

.where(and(eq(items.tenantId, tenantId), eq(items.id, itemId)))

A script read every exported function in that layer on 31 October 2026, and the exceptions were then read by hand. A text search is a heuristic, not a proof.

No read of shop data without the shop's id was found. Three child tables, bill_items, bill_returns and supplier_purchase_items, carry no tenant_id and belong to a shop through their parent. getBillLines(billId) selects by bill id alone, and each of its five call sites holds a bill it loaded or created under the session's tenant first. Easinvy Retail is safe there because of the call order, and only because of it: handed another shop's bill id, the child read would return another shop's lines.

Run on the code as it stood on 27 September 2026, the same script counts 145 functions. Fifteen arrived in five weeks, and every one of them had to be written correctly by hand.

What TypeScript catches, and what it does not

TypeScript makes Easinvy Retail's tenant id hard to forget and easy to get wrong. Every query function declares tenantId: string, and the compiler treats three mistakes differently:

• A missing argument fails to compile. That is the mistake the MongoDB version had no defence against.
• A wrong argument compiles. tenantId is a plain string, so a user id or a bill id passed in its place type-checks.
• An unused argument compiles. A function can take the tenant id and leave it out of its WHERE, and nothing notices except a reader.

The branded-type proposal in Discussion #1539 closes the second gap: a TenantId type that only the session code can produce, so a stray string no longer fits. Easinvy Retail has not taken that step. A search of the backend on 31 October 2026 found no branded type, and tenantId: string appears 121 times in the query layer.

Nothing in the type system closes the third gap. Whether a Drizzle query has a tenant condition in its WHERE is visible only at runtime, or to a person reading it.

A foreign key that lets another shop's item onto a bill

Easinvy Retail's schema has 14 foreign keys from one shop table to another, and none of them includes tenant_id. A bill line's item is bill_items.item_id, which references items.id alone. PostgreSQL, by itself, would accept a bill line in one shop that points at an item in another.

The item id arrives from the browser. When a line's itemId is shaped like a UUID, the bill code treats it as a catalogue item and writes it into the line. What refuses a foreign one is the stock update in the same transaction. decrementStock in db/queries/stock.ts updates the item where(and(eq(items.id, itemId), eq(items.tenantId, tenantId))), and when that matches no row it throws:

The only way to match no rows now is that the item is gone, or belongs to another tenant. Short stock is no longer a failure.

The throw rolls back the whole bill, and the route answers 404 with "One or more items no longer exist", the same answer for an item that was deleted and one that belongs to someone else. A new bill, an edited bill and the day's miscellaneous bill all take stock through the same function, so all three refuse a foreign item the same way.

The guard is a tenant-scoped write inside a transaction, not the schema. A tenant_id on bill_items and a composite foreign key from (tenant_id, item_id) to a unique (tenant_id, id) on items would make PostgreSQL refuse the row itself. Neither exists.

Returns and purchases are refused one layer up

Easinvy Retail's stock increments cannot refuse anything. incrementStock is scoped to the shop, but when its update matches no row it returns quietly, so the two paths that add stock for an item id sent by the browser need their own check. Both have one, in the route:

The query functions underneath, applyBillReturn, createSupplierPurchase and updateSupplierPurchase, would write whatever UUID they are handed into the child table. A caller that skips the route skips the check. One such function already exists, unused: applyBillReturns, plural, is exported from db/queries/bills.ts, has no caller anywhere in the backend, and writes any UUID-shaped item id into bill_returns unchecked.

Newer code puts the check lower. The staff dues ledger, added on 2 October 2026, locks the person's roster row with a tenant-scoped SELECT … FOR UPDATE inside the query function, and writes nothing for a person who is not on that shop's roster. A check inside the query function is one no future caller can skip.

Row-level security would not have applied to Easinvy Retail

PostgreSQL row-level security is the proposal in Discussion #1539 that moves the check into the database. A policy on each table compares tenant_id with a per-request setting, and a query that forgets its filter gets no rows instead of every shop's. Easinvy Retail does not use it: no table declares pgPolicy or .withRLS(), and no migration enables row security.

Had Easinvy Retail enabled it, nothing would have changed. PostgreSQL's documentation is plain about who is exempt: "Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well". The backend connects as neondb_owner, and drizzle.config.ts runs migrations from the same DATABASE_URL, so the role that created every table, and owns it, is the role every query runs as. Policies on those tables would not apply to the application.

AWS's post on multi-tenant isolation says the same: "If your application code connects to the database as the same PostgreSQL role as the table owner …, your security policies aren't in effect by default." Its advice is to have the application "connect to the database as a user other than the owner of the database objects." The alternative is ALTER TABLE … FORCE ROW LEVEL SECURITY on every table, which a comment in Discussion #1539, posted on 21 October 2025, gives for exactly this setup: a database user that is "both the owner (used for migration) and the user (inside the app)".

Drizzle's RLS page does not mention table owners

Drizzle's row-level security page is the page a Drizzle user reads before turning policies on. It covers pgPolicy, roles and pgTable.withRLS(), and says: "If you add a policy to a table, RLS will be enabled automatically."

Drizzle's page never mentions table owners. A reader whose application connects as the role that ran the migrations, which is the default when one connection string does both, can follow it, add policies, see RLS enabled, and still have every policy skipped for every query the application makes.

The page's Supabase example contains a way round it, without naming it. Inside the transaction it switches role with set local role, to the role in the user's token or anon. Running as a role that does not own the tables is one way past the owner exemption, and the page does not say that is why the line is there. A reader adapting the example to a plain Postgres database could drop that line without knowing what it did.

For Easinvy Retail, row-level security would have taken three things: policies on every shop table, a non-owner role for the application or FORCE on every table, and a tenant setting that survives the connection pooler.

The pooler decides how a tenant setting must be set

Easinvy Retail connects through Neon's pooled endpoint: node-postgres, with a pool of 10 connections by default, to the -pooler host Neon gives a project. Neon's pooling documentation says Neon uses "PgBouncer in transaction mode (pool_mode=transaction), which means connections are returned to the pool after each transaction completes", and the first entry under "Not supported with pooled connections" is "SET / RESET (session variables)".

Neon's list rules out the usual way of telling a policy which tenant is asking. Under transaction pooling a session setting stays with the server connection, not with the request, and the next transaction on that connection may belong to another shop. AWS's post warns that session variables "may be incompatible with server-side connection pooling such as pgBouncer."

The pattern that fits a transaction pooler is transaction-local: begin a transaction, call set_config('app.tenant_id', $1, true) or SET LOCAL, run the request's queries, commit. PostgreSQL's set_config reference states it: "If is_local is true, the new value will only apply during the current transaction." Drizzle's Supabase example passes TRUE inside client.transaction, the transaction-local form, though its page does not say why the third argument matters. The #1539 comment that settled on RLS does the same with SET LOCAL.

Easinvy Retail already keeps one piece of state at transaction scope. The daily miscellaneous bill serialises its writes with pg_advisory_xact_lock, a lock released when the transaction ends, and Neon lists session-level advisory locks among the features its pooler does not support.

A tenant slug at the root owns the whole URL space

Easinvy Retail's shop pages once lived at the root of the site, in app/[shop]/, with the shop's slug as the first segment of every URL. The commit that moved them, on 6 September 2026, says app/[shop]/ "was a dynamic segment at the root, so every path resolved as a shop slug". A marketing page at /services/... would have loaded the shop named services.

The fix moved the product under /saas, so a shop's pages sit under /saas/ followed by the slug, and gave the root to the marketing site. Moving it broke two things that a search for the old paths would not have found, because both read the slug out of a URL by index rather than building the URL from it. The sign-in page's getRequestedShopSlug returned "saas" as the shop for any next= target, and the top bar's page label read the shop slug where it expected the page name. The same commit fixed both.

A tenant slug in a URL is an identifier that competes with every other path for the same segment. Putting tenants behind a fixed prefix settles that contest once, before a marketing site or a sign-in page has to share the space.

What Easinvy Retail's tenancy does not check

Easinvy Retail has no test that puts two shops side by side. The backend had 15 self-test files on 31 October 2026, and each creates one shop at most. The one cross-shop assertion in any of them is about payments: "a real signature for another shop's subscription is refused". Nothing creates two shops and checks that one cannot read the other's bills, customers or items, so the property this note is about is the least tested one in the product.

The query audit is one day's code, read by a text search and a person. It found no unscoped read on 31 October 2026, which says nothing about the next function written.

The route checks on returns and purchases hold for the routes that exist. A future caller of applyBillReturn, createSupplierPurchase or updateSupplierPurchase, or of the unused applyBillReturns, would not pass through them.

Whether neondb_owner has the BYPASSRLS attribute was not checked. Neon's roles page says the project's role is granted membership in neon_superuser, which carries BYPASSRLS in projects created after the 15 August 2023 release, while PostgreSQL's CREATE ROLE says role attributes "are non-inheritable". Settling it takes a query against the live database. The conclusion about row-level security rests on ownership, which does not need it.

Nothing here measures what row-level security would cost, or compares it with Easinvy Retail's approach. The composite foreign key, the branded type and the two-shop test are not built; this note describes the code as it runs.

What keeps Easinvy Retail's shops apart

Easinvy Retail relies on four mechanisms, not one, and each of them stops somewhere:

• The schema gives every shop row an owner and makes numbering and uniqueness per shop. It says nothing about which owner a query asks for.
• The session takes the tenant from a verified token and re-reads the membership on every request. It hands the tenant id down; it cannot make anything use it.
• The query layer takes a tenant id in every function a shop's own requests reach, checked across 160 functions on 31 October 2026. A wrong id or an unused one still compiles.
• A tenant-scoped write in a transaction refuses another shop's item on a sale, and a lookup in the route refuses it on a return or a purchase. The foreign keys themselves would accept it.

Row-level security is not on that list. With the application connecting as the table owner, it would not have been on the list even with the policies written. For Easinvy Retail, the answer to Discussion #1539 is a convention, a type and a transaction, kept by people, with the test that would check all of them still to be written.

This is the tenancy of a product we build and operate, Easinvy Retail. Designing tenancy that still holds as the code grows is part of SaaS product development.