5 Multi-Tenancy and Security

Chapter 2 gave you the mechanics of Row-Level Security. This chapter turns those mechanics into a property of the whole system. We build the full tenancy stack for the apparel ERP (Enterprise Resource Planning system): one Postgres database, a tenant_id column on every row, Row-Level Security as the enforced fence, application scoping as a second fence, and tests that actively try to climb both. Then we layer on role-based visibility, encryption of per-tenant integration tokens, session and rate-limit design for people who sit at a desk for eight hours, and the audit-logging habits that make a SOC 2 Type II audit boring instead of terrifying.

What you need to know first

This chapter is about one job: keeping one customer company's data away from another customer company's eyes. Before any of the code makes sense, you need seven ideas. None of them are hard. All of them are new if nobody has ever spelled them out for you.

Multi-tenant. You are building one program. You will run one copy of it, backed by one database. But you will sell it to many separate apparel brands, and those brands compete with each other. Each customer company is called a tenant. "Multi-tenant" means all of those tenants share the same running application and the same database tables, and the only thing keeping their rows apart is code you wrote.

It works like one office building. One front door, one elevator, one cleaning crew, but each company on its own locked floor. In a normal building the lock is metal. Here the lock is software. If the lock has a bug, the floors are not separate at all.

When one tenant manages to see or change another tenant's data, that is called a cross-tenant leak, and in an ERP it is the worst thing that can happen to you.

Sessions, tokens, and JWTs

What actually happens when someone logs in. A user types an email and a password into your web page. The browser sends that to an authentication server, where "authentication" just means proving you are who you claim to be. The server looks up the email, checks the password against a stored scrambled copy, and if it matches, the server needs a way to remember this person on their next request.

That is the whole problem. HTTP requests (from chapter 3) are independent of each other, so the server does not naturally remember anything between them. So the server hands the browser a small piece of proof to send back every time.

Sessions and tokens. That period of "you are logged in" is called a session. The piece of proof the browser carries is called a token. It works like a wristband at a festival. At the gate you show your ID once. They check it carefully, and then they snap a wristband on you. For the rest of the day nobody re-checks your ID. They glance at the wristband.

The specific kind of wristband used here is a JWT (JSON Web Token, pronounced "jot"). A JWT is a short piece of text with three parts, separated by dots:

• a header saying which signing method was used,
• a payload of facts about you (called claims: your user id, your tenant id, when the token expires), and
• a cryptographic signature the server computed with a secret key.

Anyone can read the header and the claims. A JWT is not secret writing. But nobody can change a claim without breaking the signature, and only the server can produce a valid signature. So the server can trust a JWT it is handed without looking anything up in a database.

That speed is the whole point, and it is also the catch: a JWT stays valid until it expires, even if you deleted the user five minutes ago. That is why the tokens in this chapter are deliberately short-lived.

Database roles and connection pools

A database user (a "role"). Postgres keeps its own list of accounts, completely separate from the users of your app. In Postgres these accounts are called roles. Your application does not log into the database as "Dana the sales rep." It logs in as one database role, with one password, and that single role runs the queries for every human using the product.

Each role has permissions attached: may it read this table, may it write to that one. Those permissions are handed out with a SQL command called GRANT (and taken away with REVOKE). That list of permissions is the ceiling on the damage a bug can do: if your app's database role is allowed to do everything, then a bug anywhere in your app can do everything too.

A connection pool. Opening a fresh connection to Postgres is slow and Postgres can only hold a limited number of them open at once. So real applications keep a small set of already-open connections and take turns using them. That shared set is a connection pool. It works like an office with six phone lines and two hundred employees: you grab a free line, make your call, hang up, and the line goes back on the hook for the next person.

This is a security topic as much as a performance one, for one reason: the line is reused. If you scribble a note on the desk next to the phone and walk away, the next person to pick up that line can read your note. Anything your request leaves behind on a database connection is still sitting there for whoever borrows it next — and that "whoever" works for a different company. Section 5.3 is entirely about this.

Encryption and the audit log

Encryption at rest vs. in transit. Encryption means scrambling data with a key so that it is unreadable without that key. At rest means the data is scrambled while sitting on the hard drive: if someone steals the physical disk or a backup file, they get noise. In transit means the data is scrambled while flying across the network between your server and the database (this is what TLS, Transport Layer Security, does: it is the padlock in your browser). Both are essential and both are largely automatic on a modern host.

People often miss the next part: neither one protects you from your own application. A query that runs with a valid connection sees plain, readable data, because the database decrypts it for you. At-rest encryption stops a thief with a disk. It does nothing about a bug with a database connection.

An audit log. An audit log is an append-only diary of who did what, to which record, and when. "Append-only" means rows can be added but never edited or deleted. That is the property that makes it evidence rather than notes. When a brand's controller (the person who runs a company's accounting) asks "who approved this order and changed the price?", the audit log is the only honest answer. Section 5.10 builds one.

Everything in this chapter is built out of those seven ideas. Read them again if any felt slippery. The rest of the chapter assumes them.

5.1 One database, many brands

Your tenants are apparel brands. Each one has its own styles, SKUs (stock-keeping units, meaning a specific color and size of a garment), warehouses, wholesale accounts, and invoices. Each one considers that data radioactive if it leaks.

Suppose Meridian Apparel Group can see Atelier North's ATS figures. ATS means "available-to-sell," the units a brand can still promise to a customer today. Meridian now knows exactly how deep their competitor is on the SS27 chore coat (SS27 is the trade shorthand for the Spring/Summer 2027 season), and therefore how desperate Atelier will be at markdown time, when unsold inventory gets discounted to clear it. A cross-tenant leak in an ERP breaks the one promise the product makes, and it can end the business.

Three ways to carve up tenants

The industry has settled on three layouts, and they differ in isolation, migration cost, and how much operational work each new tenant adds:

Reading that table needs two words. A schema in Postgres is a named folder for tables, so public.styles means "the styles table inside the public schema." A migration is a script that changes the shape of the database (adds a column, creates an index). "Run N times" means you must apply it separately to every tenant, and "drift" means that sooner or later one of those runs fails and that tenant's database silently no longer matches the others.

pg_catalog is Postgres's own internal bookkeeping, where it stores a record of every table you create. Give it thousands of near-identical schemas and it slows down.

Why shared schema wins at this scale

At apparel-SaaS scale (hundreds of brands, not millions), shared schema with RLS wins, because the operational surface stays constant as you add tenants. Adding your three-hundredth brand adds no new database, no new backup job, no new upgrade window.

The trade is real, though. Isolation stops being a physical fact and becomes a correctness property of your code and policies. Everything below is about making that property hold under load, under pooling, and under adversarial tests.

5.2 The fence: tenant_id on every row, RLS on every table

Start with the two central terms.

tenant_id is a plain column you add to every table that holds customer data. It stores the id of the brand that owns that row. Meridian's styles carry Meridian's tenant id, and Atelier's carry Atelier's. That single column is what turns one shared table into many logically separate tables.

Row-Level Security (RLS) is a Postgres feature that filters rows inside the database engine. Once RLS is switched on for a table, Postgres attaches an invisible condition to every query that touches it. A policy is one of those conditions: a named rule, written in SQL, that decides which rows a given database role is allowed to see or write.

Where it runs is what makes it hold. It runs below your code, in the engine, on every query, forever, including the query you write at 2 a.m. and forget to scope. Nothing in your JavaScript can skip it, because your JavaScript never gets a say.

The schema rule: tenant_id on every table

The rule for the schema is absolute. Every tenant-owned table carries a tenant_id uuid not null column that references the tenants table. Natural keys are unique per tenant, so two brands can both have a style numbered "1042". And every index that serves tenant-scoped queries leads with tenant_id.

create table public.tenants (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,              -- "Meridian Apparel Group"
  created_at timestamptz not null default now()
);

create table public.styles (
  id              uuid primary key default gen_random_uuid(),
  tenant_id       uuid not null references public.tenants(id),
  style_number    text not null,        -- "MER-1042"
  name            text not null,        -- "Boxy Twill Chore Coat"
  season          text not null,        -- "SS27" = Spring/Summer 2027
  wholesale_price numeric(10,2) not null,
  -- natural keys are unique PER TENANT:
  unique (tenant_id, style_number)
);

create table public.skus (
  id        uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id),
  style_id  uuid not null references public.styles(id),
  color     text not null,
  size      text not null,
  upc       text,          -- retail barcode number, often absent
  unique (tenant_id, style_id, color, size)
);

-- Every real query starts with "for this one tenant".
-- So lead every index with tenant_id.
create index styles_tenant_season_idx
  on public.styles (tenant_id, season);
create index skus_tenant_style_idx
  on public.skus (tenant_id, style_id);

Take that schema a piece at a time. tenants is the list of brands, one row each. A uuid is a 128-bit identifier that looks like 9f2b1c56-…. gen_random_uuid() fills one with random bits, and because the result is effectively unguessable, nobody can enumerate your records by counting 1, 2, 3.

references public.tenants(id) is a foreign key: Postgres refuses to store a tenant_id that does not exist in tenants, and refuses to delete a tenant that still has rows pointing at it. A upc is a Universal Product Code, the barcode number printed on a retail hangtag, and it is nullable because wholesale-only styles often have none.

Unique per tenant, indexes led by tenant_id

The unique (tenant_id, style_number) line is the one to get straight. It does not say style numbers are globally unique. It says the pair is unique. Meridian can own "MER-1042" and Atelier can own "MER-1042" at the same time, and Postgres is happy. But Meridian cannot have two rows both numbered "MER-1042".

That is exactly the rule a real brand expects. The same logic gives skus its unique (tenant_id, style_id, color, size): one row per color/size combination of a style, per tenant.

The two indexes at the bottom lead with tenant_id on purpose. An index is ordered, like a phone book sorted by last name then first name. Because every real query in this system starts with "for this one tenant…", putting tenant_id first lets Postgres jump straight to that brand's block of rows and scan only inside it. An index led by season instead would force Postgres to wander across every brand's data. Section 5.6 shows what that costs.

Closing the mismatched-parent hole

A subtle integrity hole hides in that schema. skus.style_id references styles(id), but nothing stops a buggy insert from attaching Meridian's SKU to Atelier's style. The foreign key only checks that some style with that id exists. It never checks whose. RLS would then hide the row inconsistently rather than reject it. Close the hole with a composite foreign key, so the database itself proves parent and child share a tenant:

alter table public.styles
  add constraint styles_tenant_id_id_key unique (tenant_id, id);

alter table public.skus
  add constraint skus_style_same_tenant_fk
  foreign key (tenant_id, style_id)
  references public.styles (tenant_id, id);

The first statement looks pointless, because id is already unique on its own, so (tenant_id, id) obviously is too. It exists purely as a target. PostgreSQL requires that a foreign key's referenced columns be covered by a primary key, a unique constraint, or a unique index, so this line creates the thing the next statement can aim at.

The second statement is the real fix. It says: the pair of values in skus.(tenant_id, style_id) must appear together as a pair in styles.(tenant_id, id). Now an insert that tries to hang a SKU with Meridian's tenant_id off a style belonging to Atelier fails outright with a foreign-key violation, at write time, in the open. Mismatched parentage becomes unrepresentable, which is always better than "detectable."

One helper function for tenant identity

Now the fence itself. Tenant identity reaches the database by two roads. Requests through the Supabase client hit PostgREST (Supabase's service that turns HTTP calls into SQL), which verifies the JWT, runs each request inside its own transaction, and exposes the token's claims in a per-transaction setting named request.jwt.claims. Requests through your own server-side pool carry a per-transaction setting named app.tenant_id instead (section 5.3). One helper function reads both roads, so every policy has a single source of truth:

create schema if not exists app;

-- STABLE: the planner may evaluate this once per statement
-- instead of once per row.
create or replace function app.current_tenant_id()
returns uuid
language sql stable
as $$
  select coalesce(
    -- server-side path: SET LOCAL app.tenant_id in the transaction
    nullif(current_setting('app.tenant_id', true), ''),
    -- Supabase/PostgREST path: verified JWT claims land here
    current_setting('request.jwt.claims', true)::jsonb
      -> 'app_metadata' ->> 'tenant_id'
  )::uuid
$$;

This function answers one question ("which tenant is this query for?"), and it is the only place in the system allowed to answer it.

Four pieces, one at a time:

• current_setting(name, true) reads a per-connection variable. Postgres lets you park named string values on a connection, like sticky notes attached to the phone line, and current_setting peels one off and reads it. The second argument is missing_ok: passing true means "if this note does not exist, return null instead of throwing an error." Without it, any query on a connection that never set the variable would explode.
• nullif(x, '') converts an empty string to null, because a setting that was set to nothing should count as absent rather than as a value.
• coalesce(a, b) returns the first argument that is not null: try the server-side note first, and if there isn't one, fall back to the JWT claims.
• ::jsonb, ->, and ->> handle that fallback: the JWT claims arrive as a JSON string, ::jsonb parses it into a JSON value, -> digs into the app_metadata object, and ->> pulls out tenant_id as plain text.

The trailing ::uuid converts whichever answer won into a real UUID.

What STABLE promises the planner

The word stable in the function header is a promise you make to Postgres: within a single SQL statement, this function will return the same answer every time you call it. That promise is what lets the query planner call it once instead of once per row, which in section 5.6 turns out to be the difference between a query that finishes in milliseconds and one that runs for minutes.

If neither road supplied a tenant, the function returns null, and (as you are about to see) every policy comparison against null is false, so the query returns nothing. Failing to zero rows is the correct direction to fail.

Permissive and restrictive policies

Enable RLS on every tenant table and split policy duty in two. There will be one restrictive tenant fence and one or more permissive policies that grant access within it. The difference is how Postgres combines them, and it is the most important mechanic in this chapter:

• A permissive policy grants. Postgres ORs all permissive policies together. If any one of them says yes, the row passes that half of the test. Permissive policies can therefore only ever widen access, because adding one never takes anything away.
• A restrictive policy constrains. Postgres ANDs all restrictive policies together, and ANDs that result with the permissive result. A restrictive policy can only ever narrow access, and no future policy can undo it.

The combined formula is (perm1 OR perm2 OR …) AND restr1 AND restr2 AND …. The PostgreSQL documentation is explicit that a table with RLS enabled and no policy at all uses a default-deny rule: no rows are visible and none can be modified. That shape is why the tenant fence is restrictive: it can never be widened by a policy someone adds in a hurry two years from now.

alter table public.styles enable row level security;
-- table owners get no free pass:
alter table public.styles force row level security;

-- RESTRICTIVE: ANDed with every other policy, forever.
create policy tenant_fence on public.styles
  as restrictive
  for all
  to authenticated, app_user
  using      (tenant_id = (select app.current_tenant_id()))
  with check (tenant_id = (select app.current_tenant_id()));

-- PERMISSIVE: at least one must pass or nobody sees any rows.
create policy tenant_members_read on public.styles
  for select
  to authenticated, app_user
  using (tenant_id = (select app.current_tenant_id()));

What the policies let through, row by row

Follow one query through both policies. Suppose a buyer at Meridian runs select * from styles with no where clause at all, the greediest query they can write. Postgres evaluates, for each candidate row: does the row's tenant_id equal Meridian's tenant id?

For Meridian's own rows, yes on both policies, so the row passes (tenant_members_read) AND (tenant_fence) and is returned. For every one of Atelier's rows, both comparisons are false, so the row is dropped.

The buyer receives a result set containing only Meridian styles, and no error. From the attacker's side there is nothing to probe. The table appears to contain only their own data. Even select count(*) from styles counts only their rows, so they cannot infer how much data exists behind the fence.

USING versus WITH CHECK

Now the write side, which is what with check governs. USING filters what a statement can see: it is applied to rows that already exist, for SELECT, UPDATE, and DELETE. WITH CHECK validates what a statement can write: it is applied to the new version of a row, for INSERT and UPDATE.

You need both, and one attack shows why. Without WITH CHECK, a Meridian user could run UPDATE styles SET tenant_id = '<atelier's id>' WHERE id = '<their own style>'. The USING clause is satisfied, because the row currently belongs to Meridian. The row would then be rewritten into Atelier's tenant, smuggled across the fence, and now visible to Atelier and invisible to its real owner.

With WITH CHECK in place, Postgres inspects the row as it would be after the update, sees a foreign tenant_id, and rejects the statement with error 42501 (Postgres's insufficient_privilege code), reported as "new row violates row-level security policy."

Verb coverage, target roles, and forced RLS

Three more details in that block carry real weight. for all means the fence applies to SELECT, INSERT, UPDATE, and DELETE alike, so there is no verb-shaped gap. to authenticated, app_user names the two database roles the policy applies to: logged-in Supabase users, and the server-side role from section 5.3. That also means it does not burn any effort on anonymous traffic.

And FORCE ROW LEVEL SECURITY exists because of a dangerous default: the owner of a table normally bypasses that table's RLS policies. Your migrations role owns these tables. If your server ever connects as that role and you skipped FORCE, every policy silently evaporates while every screen in the app still appears to work.

Enabling RLS with no policies at all, by contrast, fails closed to zero rows, so a half-finished table hides everything instead of exposing everything.

The BYPASSRLS escape hatch

One more escape hatch to audit: some roles carry an attribute called BYPASSRLS, which skips row-level security entirely, even forced RLS. (Superusers always bypass it too.) Supabase's service role (the backend identity meant for trusted server jobs like migrations and admin scripts) is one of them, and its key is effectively a master key to the whole database.

Check with select rolname, rolbypassrls from pg_roles, where pg_roles is Postgres's built-in list of database accounts, and treat the rule as absolute: the service key must never serve a user-facing request path. One handler that accidentally uses it turns every policy in this chapter into decoration.

Read that callout twice, because it is the cheapest way to lose everything. A JWT carries two bags of claims. user_metadata is the bag the user is allowed to edit (their display name, their preferred timezone). app_metadata is the bag your server controls. Both are signed, both look equally official inside the token, and reading the wrong one means the attacker just types their competitor's tenant id into their own wristband.

5.3 Carrying tenant context through a connection pool

Most home-grown RLS setups break right here. Your Next.js app does not hold one Postgres session per user. It borrows connections from a pool.

Supabase fronts your database with Supavisor, a connection pooler: a small server that sits between your app and Postgres, holds the real database connections, and hands them out as requests need them. (PgBouncer is the older, widely used tool that does the same job. Supavisor is Supabase's newer one, and the concepts transfer.) A pooler offers two very different contracts:

Three notes on that table, current as of July 2026:

• Supabase's shared pooler answers on aws-<region>.pooler.supabase.com, port 6543 for transaction mode and 5432 for session mode, and it is reachable over IPv4 (the older, more widely supported style of internet address), whereas a direct connection to db.<project>.supabase.co is IPv6 by default.
• Paid projects can also run a Dedicated Pooler (PgBouncer on port 6543), which is transaction mode only.
• And LISTEN/NOTIFY is Postgres's built-in message broadcast: one connection announces an event and other connections that are listening receive it, which only works if your connection sticks around.

Transaction mode versus session mode

The two modes are the whole story, so put them in plain terms. A "backend" is one real Postgres process on the database server, one actual phone line. In transaction-mode pooling, you are given a backend only for the length of a single transaction (chapter 1's BEGIN … COMMIT), and the instant you commit it goes back in the pool for someone else. Ten backends can therefore serve hundreds of concurrent requests, which is why this is the right mode for serverless.

In session-mode pooling, a backend is yours from connect to disconnect. That is comfortable, because anything you set stays set, but you get almost none of the sharing benefit, since an idle client still owns a line.

How session state bleeds between tenants

Now the danger. Under transaction pooling, a backend that just served Meridian's ATS export is handed to Atelier's order-entry request milliseconds later. Any session-scoped state the first request left behind is still live for the second: a plain SET app.tenant_id, or a modified search_path (the ordered list of schemas Postgres searches when you write an unqualified table name).

That is a cross-tenant read with no injection string anywhere in sight. Nobody attacked you. Your own code leaked one tenant into another. And it only reproduces under concurrency, which is why it sails through development (one developer, one connection, the state always "happens" to be right) and detonates in production.

You cannot rely on the pooler cleaning up either: PgBouncer's own documentation states that server_reset_query, the command that would wipe leftover state between clients, "is not used" in transaction pooling mode, and the setting that would force it, server_reset_query_always, is off by default.

In the phone-line analogy: SET writes the note in pen on the desk, where the next caller finds it. SET LOCAL writes it on a sticky note that is guaranteed to be thrown away when you hang up. Same syntax otherwise, one extra word, completely different security posture.

The warning is the nastier half of that callout. Outside an explicit transaction, Postgres runs each statement in its own instant transaction, so your SET LOCAL is set and discarded before your next line runs, and every query afterward sees no tenant at all while your application code carries on as if nothing happened.

So make the safe shape the only shape. One wrapper owns the pool, the transaction, and the context, and it also plants the actor and request id that the audit log (section 5.8, and fully in 5.10) will consume:

// packages/db/src/tenant-client.ts
import { Pool, type PoolClient } from "pg";

// Supavisor transaction mode (…pooler.supabase.com:6543),
// logging in as the app_user role.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

export interface TenantContext {
  // from the verified session — never from request input:
  tenantId: string;
  userId: string;
  requestId: string;
}

export async function withTenant<T>(
  ctx: TenantContext,
  fn: (db: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // set_config(…, true) === SET LOCAL: scoped to THIS
    // transaction, so the backend goes back to the pool
    // with no tenant state attached.
    await client.query(
      `select set_config('app.tenant_id',  $1, true),
              set_config('app.actor_id',   $2, true),
              set_config('app.request_id', $3, true)`,
      [ctx.tenantId, ctx.userId, ctx.requestId],
    );
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

The withTenant wrapper, line by line

Read this as a checklist that runs itself. new Pool({… max: 10 }) creates the pool: at most ten open connections, shared by the whole application. TenantContext is the little bundle of verified facts every database call needs: which tenant, which human, which request. The comment on tenantId is a rule, and worth stating in full: that value comes from the session the server verified. It never comes from a query parameter, a header, or a request body, because anything the client sends is something the client can change.

withTenant takes that context plus a function fn containing your actual queries, and wraps it. In order: borrow a connection from the pool; BEGIN a real transaction so SET LOCAL semantics apply; call set_config three times with a third argument of true, which PostgreSQL documents as meaning "the new value will only apply during the current transaction" (the programmatic spelling of SET LOCAL); run your queries; COMMIT.

If anything at all throws (a bad query, a business-rule error, a network blip), the catch block issues ROLLBACK, undoing every write in the transaction. And the finally block runs in both cases, returning the connection to the pool. Skipping that finally leaks a connection on every failed request, and ten leaked connections is a dead application.

The security payoff is the ordering. Because the tenant note is written inside the transaction and the transaction always ends, the connection can never go back on the hook with a tenant still attached to it. The $1, $2, $3 placeholders matter too: values are sent to Postgres separately from the SQL text, so a tenant id can never be interpreted as SQL. That is what stops SQL injection. Finally, the <T> is a TypeScript generic: it means withTenant returns whatever type your function returns, so wrapping a query in it costs you no type information.

A database role that owns nothing

The pool logs in as a dedicated role that owns nothing and bypasses nothing:

create role app_user login password :'APP_USER_PASSWORD' noinherit;
grant usage on schema public, app to app_user;
grant select, insert, update, delete
  on all tables in schema public to app_user;

-- app_user is NOT a table owner and MUST NOT have BYPASSRLS:
select rolname, rolbypassrls
  from pg_roles
 where rolname = 'app_user';

This creates the database account your application connects as. login means it is allowed to connect at all. noinherit means it does not silently pick up the powers of other roles it might be a member of. It gets exactly what you hand it and nothing more. grant usage on schema gives permission to look inside those schemas, and grant select, insert, update, delete gives permission to read and write table contents. Notice what is missing: no CREATE, no DROP, no ownership of anything, no ability to alter or disable a policy.

This is the principle of least privilege in one statement: give every identity the smallest set of powers that lets it do its job, and nothing else. The reason is blunt. A bug, a stolen credential, or a compromised dependency gets to do whatever the account it stole can do, and no more. Give app_user only read/write on public tables and the worst case shrinks to "read/write on rows RLS lets it touch."

The final line is a verification query, and you should run it in CI (continuous integration, the automated checks that run on every code change). It prints whether app_user carries rolbypassrls. The answer must be false forever. If it is ever true, every policy in this chapter is switched off for your entire application and nothing visibly breaks, which is precisely why a machine should check it on every commit rather than a person remembering to.

Prisma, Drizzle, and prepared statements

If you use Prisma or Drizzle instead of raw pg (both are libraries that generate SQL for you from TypeScript), the same rule applies: the set_config call and your queries must share one interactive transaction (Prisma's $transaction(async (tx) => …)).

One extra step comes with transaction mode: Supabase's documentation states plainly that transaction mode "does not support prepared statements," so turn them off in your client. A prepared statement is a query you send once to be parsed and then re-run by name, and the pooler may hand your next call to a different backend that has never heard of that name. With Prisma the fix is to append pgbouncer=true to the connection string (Supabase's documented setting, as of July 2026).

5.4 Belt and suspenders: application-layer scoping

RLS is the fence, and it is one layer of a model that needs two. Your data-access layer should also filter by tenant on every query. There are three reasons, and each one is a real incident you are pre-empting:

• First, defense in depth. The day someone accidentally routes a request through a BYPASSRLS connection, or drops a policy in a migration, the explicit where tenant_id = … is what stands between you and disclosure.
• Second, failure semantics. RLS filters silently: a missing tenant context returns zero rows, not an error. "The ATS screen is empty" is a miserable bug to trace at the end of a long week, and it looks identical to "this brand really has no inventory." An explicit scoping assertion that throws tells you what happened in one line of a stack trace.
• Third, performance. A policy behaves like an implicit WHERE clause, and it gives the planner no hint about which index to use. Supabase's own guidance is to add explicit filters anyway, and its published benchmark measured a query dropping from 171 ms to 9 ms on a 100,000-row table once a matching client-side filter was added alongside the policy. Explicit filters are what land the planner reliably on your (tenant_id, …) composite indexes.

// apps/web/src/app/(orders)/actions.ts
"use server";
import { withTenant } from "@repo/db/tenant-client";
// requireSession verifies the JWT by calling getUser():
import { requireSession } from "@repo/auth/session";

export async function getStyleAts(styleId: string) {
  const session = await requireSession(); // throws if signed out
  return withTenant(session, async (db) => {
    const { rows } = await db.query(
      `select k.id, k.color, k.size,
              i.on_hand - i.allocated + i.on_order as ats
         from skus k
         join inventory i
           on i.tenant_id = k.tenant_id and i.sku_id = k.id
        where k.tenant_id = app.current_tenant_id()
          and k.style_id = $1`,
      [styleId],
    );
    return rows;
  });
}

Reading the canonical query

Every read in the app takes this shape, so read it closely. "use server" marks the file as server-only code, so it never ships to the browser. requireSession() validates the caller's JWT and returns the verified tenant id and user id. It throws if there is no valid session, so an unauthenticated caller stops here and never reaches the database. That result is handed straight into withTenant, which opens the transaction and plants the tenant note from section 5.3.

The SQL joins SKUs to inventory and computes ATS as on_hand - allocated + on_order: units physically in the warehouse, minus units already promised to orders, plus units inbound from the factory.

Two things about the join deserve attention. The join condition matches on two columns, i.sku_id = k.id and i.tenant_id = k.tenant_id. The tenant column travels through every join, so a mismatched pair can never be stitched together even in a subquery. And the where clause repeats k.tenant_id = app.current_tenant_id() even though the RLS policy already enforces exactly that.

That repetition is deliberate: it is the "suspenders" of the section title, it hands the planner a filter it can push into the index, and if the policy were ever dropped this line would still be standing.

Now consider an attacker who calls getStyleAts() with a style id they stole from Atelier. They pass a real, valid UUID. The query runs. The where clause requires the SKU's tenant to be Meridian. Atelier's SKUs fail that test, and RLS independently requires the same thing. The function returns []. The attacker learns nothing, not even whether that id exists.

5.5 Tests that try to break in

You do not get to claim isolation you have not attacked. This is worth naming properly, because two industry terms describe the practice.

A threat model is a written list of who might attack you, what they want, and which paths they could take. For this ERP: a logged-in user at one brand trying to reach a competitor's data, a stolen API key, a curious employee. Writing it down turns security from a vibe into a checklist.

A penetration test ("pen test") is a paid engagement where outside specialists attack your running system on purpose and report what they got through. Enterprise buyers will ask whether you commission one every year. The tests below are the version you run continuously, for free, on every commit.

The tenancy test suite creates two seeded tenants and attacks them with real authenticated clients on the real API path. It deliberately avoids any shortcut that would skip your own middleware (the code that runs on every request before your handler does), because a shortcut proves nothing about what a logged-in user can reach. The suite attempts every category of cross-tenant access: read by primary key, write, and forged insert.

// packages/db/test/tenant-isolation.test.ts
import { beforeAll, describe, expect, it } from "vitest";
import { createClient, type SupabaseClient } from "@supabase/supabase-js";

const url = process.env.SUPABASE_URL!;
const anon = process.env.SUPABASE_ANON_KEY!;
const ATELIER_TENANT_ID = process.env.TEST_ATELIER_TENANT_ID!;

async function signIn(email: string): Promise<SupabaseClient> {
  const c = createClient(url, anon);
  const { error } = await c.auth.signInWithPassword({
    email, password: process.env.TEST_USER_PASSWORD!,
  });
  if (error) throw error;
  return c;
}

describe("cross-tenant isolation", () => {
  let meridian: SupabaseClient; // buyer at Meridian Apparel Group
  let atelier: SupabaseClient;  // admin at Atelier North
  let atelierStyleId: string;

  beforeAll(async () => {
    meridian = await signIn("buyer@meridian.example");
    atelier  = await signIn("admin@ateliernorth.example");
    const { data } = await atelier
      .from("styles").select("id").limit(1).single();
    atelierStyleId = data!.id;
  });

  it("cannot read another tenant's style by exact id", async () => {
    const { data, error } = await meridian
      .from("styles").select("*").eq("id", atelierStyleId);
    expect(error).toBeNull();   // RLS filters silently: no error…
    expect(data).toEqual([]);   // …and no rows.
  });

  it("cannot rewrite another tenant's prices", async () => {
    const { data } = await meridian
      .from("styles")
      .update({ wholesale_price: 0.01 })
      .eq("id", atelierStyleId)
      .select();
    expect(data).toEqual([]);   // zero rows matched, zero harmed
  });

  it("cannot smuggle a forged row into another tenant", async () => {
    const { error } = await meridian.from("styles").insert({
      tenant_id: ATELIER_TENANT_ID,  // forged tenant claim
      style_number: "EVIL-001",
      name: "x",
      season: "SS27",
      wholesale_price: 1,
    });
    expect(error?.code).toBe("42501"); // WITH CHECK rejected it
  });
});

Reading the test setup

The setup first. This file uses Vitest, a test runner: describe groups related tests, it declares one test, expect states what must be true, and beforeAll runs once before the group.

signIn logs in a real test user with a real password and returns a client carrying a real JWT. That is the point of the whole file, because a test that talks to the database as an admin proves nothing about what a user can reach. beforeAll signs in one buyer at Meridian and one admin at Atelier, then uses Atelier's own client to fetch a legitimate Atelier style id.

The attacker in the tests below therefore holds a genuine, correct id for a record they must not be able to touch. That is the strongest form of the test: it removes "they would never guess the id" from the defense.

What each attack proves

Test one, read by primary key. Meridian asks for a style by exact id. Note both assertions. error is null, because the request was well-formed and the database did not complain, and data is an empty array. This is RLS's silent-filter behavior made explicit: the attacker cannot distinguish "that record belongs to someone else" from "that record does not exist." They learn nothing at all, which is the ideal outcome.

Test two, write. Meridian tries to set a competitor's wholesale price to one cent, the kind of sabotage that would show up as an unexplained margin collapse weeks later. The USING clause of the policy runs first and decides which rows the UPDATE is even allowed to see. Atelier's row fails that test, so the update matches zero rows and changes nothing. Again there is no error, and again that is fine: the assertion checks the returned array is empty, which means nothing was modified.

Test three, forged insert. This is the direct assault. Meridian sends an insert with tenant_id explicitly set to Atelier's id, trying to plant a row inside the competitor's data. Here the outcome is different, and deliberately so: WITH CHECK evaluates the proposed new row, sees a tenant that does not match the caller's, and rejects the whole statement with SQL error code 42501 (insufficient privilege). Reads fail silently. Forged writes fail loudly. If this test ever starts returning success, or even returns a different error code, your fence has a hole.

Two tests still missing

Two more tests belong in the suite. One fires fifty concurrent withTenant calls alternating between the two tenants through the transaction-mode pool, and asserts every result set is pure. This is the only test shape that catches session-state bleed, because the bug from section 5.3 needs interleaving to exist at all. Run the calls one at a time and it will never appear.

The other iterates pg_tables (Postgres's built-in catalog of tables) and fails if any table in public has rowsecurity = false, so a new table shipped without a fence cannot reach production. Run all of it in CI against a disposable database. Treat isolation as something that can regress at any time, and run these tests on every commit like any other part of the suite.

5.6 RLS performance engineering

RLS overhead is a choice. Written carelessly, a policy can turn a millisecond lookup into a scan that runs for seconds or minutes, and it always happens on your biggest tenant's biggest table, which in this ERP is order lines. When a query that used to be fast becomes slow because the database chose a worse strategy, that is called a query plan regression, and RLS is a common cause of one.

The mechanics are simple once you see them. A policy expression is evaluated against candidate rows. If it contains a bare function call like app.current_tenant_id() or auth.uid(), Postgres may re-evaluate that call per row: a million rows, a million function calls, each one cheap and the total ruinous.

Wrap the call in a scalar subquery, (select app.current_tenant_id()), and the planner hoists it into an InitPlan: a small piece of the query plan that runs once, before the main scan, whose result is then treated as a constant (named $0) for the rest of the statement.

An index can be searched by a constant. It cannot be searched by a live function call. That single change is what lets Postgres switch from scanning the table to jumping straight into the index. It works only because the function is declared STABLE (its result cannot change mid-statement) and does not depend on row data. The difference is visible directly in the plan:

explain (analyze, costs off)
select * from skus where style_id = '9f2b1c56-…';

-- Bare call in the policy: evaluated for every candidate row
--   Seq Scan on skus
--     Filter: ((style_id = '9f2b…')
--             AND (tenant_id = app.current_tenant_id()))
--
-- Wrapped in (select …): hoisted, cached, index-driven
--   Index Scan using skus_tenant_style_idx on skus
--     Index Cond: ((tenant_id = $0) AND (style_id = '9f2b…'))
--   InitPlan 1 (returns $0)
--     Result

Reading the two plans

EXPLAIN asks Postgres to describe how it intends to run a query. Adding ANALYZE actually runs it and reports real timings. The output is the query plan, the step-by-step strategy the planner chose. You are looking at the same query planned two ways.

In the first plan, Seq Scan on skus means "sequential scan": read every single row in the table and test each one. Notice the policy condition appears under Filter, and it still contains a live function call. Postgres is reading the whole table and calling that function once per row. On 2 million SKUs that is 2 million reads and 2 million calls to produce maybe forty rows.

In the second plan, InitPlan 1 (returns $0) appears: the function ran once, up front, and its answer was stored as $0. Now look at the main line: Index Scan using skus_tenant_style_idx, with Index Cond: ((tenant_id = $0) AND (style_id = '9f2b…')). The word Filter has become Index Cond, and that is the whole win. A filter is applied to rows after reading them. An index condition is used to find the rows without reading the rest. Same policy, same security guarantee, same result set — nine characters of syntax, orders of magnitude of difference.

Recurring regressions and their fixes

Four of these come up again and again:

Two terms from that table: a correlated subquery is an inner query that depends on the current row, so it cannot be computed once. It reruns for every row, and if the table it queries also has RLS, that policy runs every time too, compounding the cost. A lint is an automated check that reads your code (here, your policies) and warns about known-bad patterns. Supabase ships one named auth_rls_initplan that finds exactly the unwrapped-call mistake above.

5.7 RBAC on top of RLS: reps, admins, and policy composition

Inside a tenant, not everyone should see everything. The mechanism for that is RBAC, Role-Based Access Control. The idea is small: instead of granting permissions to individual people, you define a handful of named roles (owner, admin, ops, sales rep, read-only), attach each person to a role, and write your rules against the role. Hiring someone then means assigning one role, instead of ticking eleven separate permission boxes and hoping you remembered them all.

Note the collision of vocabulary: these application roles live in your tables and are unrelated to Postgres database roles from section 5.3. Both are called roles. They are different layers.

The apparel case that forces the issue: sales reps carry a book of wholesale accounts (the boutiques and majors they own, "majors" being the large department-store and chain retailers), and a rep should see the accounts and orders on their own book. They should not see the rest of the tenant's, and they certainly should not see another rep's commission-bearing orders.

When a user manages to act with more authority than they were granted (a rep seeing every rep's book, or a read-only user writing), that is privilege escalation. It is the inside-a-tenant cousin of a cross-tenant leak, and it is easier to introduce by accident. Model membership and roles as data:

create table public.memberships (
  tenant_id uuid not null references public.tenants(id),
  user_id   uuid not null references auth.users(id),
  role      text not null
            check (role in
              ('owner','admin','ops','sales_rep','readonly')),
  primary key (tenant_id, user_id)
);

-- the brand's wholesale customers:
create table public.accounts (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references public.tenants(id),
  name          text not null,  -- "Harborline Goods, Portland"
  rep_user_id   uuid references auth.users(id),
  payment_terms text not null default 'NET30'
);

-- SECURITY DEFINER so the lookup itself is not blocked by RLS
-- on memberships, and so policies can call it once via (select …).
create or replace function app.member_role()
returns text
language sql stable security definer
set search_path = ''
as $$
  select m.role
    from public.memberships m
   where m.tenant_id = app.current_tenant_id()
     and m.user_id   = auth.uid()
$$;

Modeling membership as data

memberships answers "which people belong to which brand, and as what?" Its primary key is the pair (tenant_id, user_id), which encodes a real business fact: one person has at most one role per tenant, but the same person can belong to several tenants with different roles in each, such as a consultant who is ops at Meridian and readonly at Atelier.

The check (role in (…)) constraint is a spelling guard: Postgres will reject a typo like 'sales-rep' at write time rather than letting it sit in the table quietly matching no policy.

accounts holds the brand's wholesale customers. rep_user_id is the ownership link, naming the rep whose book this account sits on, and it is nullable, because house accounts belong to no individual rep. payment_terms defaults to 'NET30', the standard wholesale arrangement in which the retailer has thirty days from the invoice date to pay.

Why the role lookup needs SECURITY DEFINER

Then the function. app.member_role() returns the current user's role string within the current tenant, and it needs one special power to work. SECURITY DEFINER means the function runs with the permissions of whoever created it, rather than the permissions of whoever calls it.

Without it you would have a chicken-and-egg problem: to find out whether you are an admin, the function must read memberships, but memberships has its own RLS, which might block you from reading your own row. SECURITY DEFINER steps over that. It is a sharp tool, because inside the function all bets are off, which is why the body is three lines long, takes no arguments an attacker could bend, and filters on both tenant_id and auth.uid().

The companion line set search_path = '' is a mandatory safety catch: it forces every table name in the body to be fully qualified, so nobody can create a sneaky table in another schema and trick your privileged function into reading it instead. Never write a SECURITY DEFINER function without it.

Composing the role policies

Now compose policies. The restrictive tenant fence stays exactly as before, and role visibility lives entirely in the permissive layer:

alter table public.accounts enable row level security;
alter table public.accounts force row level security;

create policy tenant_fence on public.accounts
  as restrictive for all to authenticated, app_user
  using      (tenant_id = (select app.current_tenant_id()))
  with check (tenant_id = (select app.current_tenant_id()));

-- Admins and ops see the whole tenant's account list…
create policy staff_all_accounts on public.accounts
  for select to authenticated, app_user
  using ((select app.member_role()) in ('owner','admin','ops'));

-- …reps see only the accounts on their book.
create policy rep_own_accounts on public.accounts
  for select to authenticated, app_user
  using (
    (select app.member_role()) = 'sales_rep'
    and rep_user_id = (select auth.uid())
  );

Trace three different people running the same query, select * from accounts.

An admin at Meridian. The fence passes on every Meridian row. staff_all_accounts checks their role, finds admin, and passes on every row too. Note that it does not look at the row's contents at all, because it is a pure role test. rep_own_accounts fails, because they are not a sales rep, but permissive policies are ORed, so one pass is enough. They see the brand's entire account list. Atelier's rows are blocked by the fence, invisibly.

A sales rep at Meridian. The fence passes on Meridian rows. staff_all_accounts fails, because sales_rep is missing from that list. rep_own_accounts passes only where rep_user_id equals their own user id. So the OR yields exactly their book: Harborline Goods appears if they own it, and their colleague's boutiques stay hidden. If they hand-type another rep's account id into the URL, they get an empty result and no error.

A readonly user. The fence passes, but neither permissive policy does. With no permissive policy passing, Postgres denies the row. They see zero accounts, which is the correct fail-closed behavior, and also a reminder that you must deliberately write a policy for every role you expect to be able to see something.

The attacker's view of all of this is uniformly blank. Every one of these failures returns an empty set on SELECT, never an error and never a partial row. There is no timing tell and no count to probe. On the write side, only the fence's with check is defined here, so this table currently permits no user-facing inserts or updates at all. Write policies would be added separately and explicitly, which is the right default.

How permissive policies accumulate

The composition math is the part teams get wrong, so restate it: Postgres ORs all permissive policies together, ANDs all restrictive ones, then ANDs the two groups. Here that is tenant_fence AND (staff_all_accounts OR rep_own_accounts).

That OR is a loaded gun, because permissive policies can only ever widen access. Suppose an early tenant_members_read-style policy from month one ("any member can read accounts") is still sitting on the table. Your carefully scoped rep policy is now dead code. Reps see everything, because the old policy passes the OR by itself.

That is privilege escalation by accumulation, and nothing errors: no test fails, no log line appears, the feature just does not do what its author believed.

The restrictive fence protects the tenant boundary against this permanently. Nothing protects the role boundary except discipline. So: before adding a permissive policy, list what is already there with select * from pg_policies where tablename = 'accounts' (pg_policies is Postgres's built-in listing of every policy on every table), and keep a policy-inventory test that snapshots pg_policies, so any new grant shows up in code review as a diff a human must approve.

Inheriting visibility downstream

Downstream tables inherit that visibility. A policy on orders of using (account_id in (select id from public.accounts)) re-enters RLS on accounts, because the inner query is itself filtered by the policies above, so a rep's orders are exactly the orders of accounts they can already see. One definition of "their book," written once and reused. Watch the plan cost of that subquery on big tables. If it hurts, replace it with a SECURITY DEFINER function returning the rep's account ids as an array and compare with = any(…).

One gap to close on the server-side pool path: auth.uid() is null there, because that path has no JWT, which is exactly why withTenant also plants app.actor_id. If server-path requests must honor rep scoping (they should), extend the helper the same way app.current_tenant_id() reads both roads, and pass the verified user id from the session. Never accept an actor id from request input. An actor id supplied by the caller is just the caller telling you who to bill for their actions.

5.8 Encrypting what matters: tokens, keys, rotation

Get the baseline out of the way. Supabase states that all customer data is encrypted at rest with AES-256 (Advanced Encryption Standard, 256-bit keys, the current default for serious symmetric encryption) and in transit via TLS, and its production checklist tells you to switch on SSL Enforcement in the dashboard so plaintext connections are refused (as of July 2026). Enforce ssl on your own pool connections as well and you have covered stolen-disk and passive-network attacks: someone who walks off with the drive, or who taps the wire, gets noise.

But at-rest encryption is transparent to SQL. The database decrypts on read automatically, which means it does nothing about the threat you actually face: an application-level bug, an over-broad query, or a leaked logical backup exposing plaintext secrets. (A logical backup is a dump of your data as SQL statements, pg_dump output, rather than a copy of the raw disk, so it is readable in any text editor.)

The secrets that matter most here came from chapter 4: per-tenant OAuth tokens for Shopify, NuOrder, Joor, and QuickBooks. (An OAuth token is a credential a tenant granted you that lets your server act on their behalf in that other system.) A Shopify Admin API token in plaintext is full write access to a tenant's store.

Those rows need application-level encryption on top: encrypted with a key your database does not automatically apply, so that reading the row is not the same as reading the secret.

Vault: pointers instead of tokens

The practice of storing, controlling access to, and replacing these credentials is called secrets management, and it deserves a purpose-built tool rather than a column. Supabase Vault does it inside Postgres. Secrets are stored using authenticated encryption built on libsodium, a well-regarded cryptography library, which both scrambles the data and signs it so tampering is detectable.

Because the encryption happens in the stored bytes, the ciphertext is what lands in every backup and replication stream. Decryption happens only through the vault.decrypted_secrets view, and the per-project root key is held outside your database, where SQL cannot reach it. Your integration rows store a pointer to the secret and nothing else:

create table public.integration_connections (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants(id),
  provider     text not null
               check (provider in
                 ('shopify','nuorder','joor','quickbooks')),
  -- e.g. "acme-store.myshopify.com":
  external_account        text not null,
  -- a pointer into vault.secrets(id), never the token itself:
  access_token_secret_id  uuid not null,
  refresh_token_secret_id uuid,
  rotated_at              timestamptz,
  unique (tenant_id, provider, external_account)
);

select vault.create_secret(
  'shpat_9c1f…redacted',                       -- the secret
  'shopify:meridian:acme-store.myshopify.com', -- unique name
  'Shopify Admin token, tenant Meridian'       -- description
);

-- Server-only read path. Use the service role or a SECURITY
-- DEFINER function — never a browser-reachable path.
select decrypted_secret
  from vault.decrypted_secrets
 where id = $1;

The table records which brand connected to which external system, and it is deliberately boring: a provider name, the external account identifier, and — the key design decision — access_token_secret_id, which holds a pointer into the Vault rather than the token itself.

It works like a coat check. The table holds the ticket, and the coat is in the back room. If this table leaks in a support export, a screenshot, or an over-broad query, the attacker gets a list of UUIDs that are useless without Vault access. rotated_at records when the credential was last replaced, which the staleness alerting below depends on. The unique constraint stops the same store from being connected twice to the same brand.

vault.create_secret() takes the actual secret, a unique human-readable name, and a description. It returns the UUID you store in the table. The last query is the only way back to plaintext: selecting from the vault.decrypted_secrets view, which decrypts on the fly. That query must run only in trusted server code, a background job refreshing a Shopify sync for example, and never on any path a browser can reach.

Vault's sharp edges

Vault has sharp edges worth respecting, all three documented by Supabase:

• First, the act of storing a secret can be the thing that leaks it: statements that write secrets get captured by Postgres statement logging by default, which would put your plaintext token in the logs, so turn statement logging off while writing secrets.
• Second, think carefully about which database roles can reach the vault schema at all, because anyone who can read vault.decrypted_secrets has your plaintext secrets, so authenticated must never be granted access.
• Third, a manual pg_dump/pg_restore into a fresh project cannot decrypt the copied secrets, because the new project is created with its own root key. You have to copy the root key across deliberately, via the Management API.

Key rotation in three habits

Which brings us to key rotation: routinely replacing a credential or encryption key with a new one, so that a secret which leaked without your knowledge stops being useful. Rotation is an ongoing lifecycle rather than a one-off task, and it needs only three habits:

• Rotate the tokens on the provider's schedule. OAuth refresh flows already rotate access tokens for you. Your job is to record rotated_at and alert on staleness, so a dead integration surfaces before the tenant's ATS sync silently stops.
• Rotate credentials you issue, such as webhook signing secrets (a webhook is an HTTP call another system makes to your server when something happens there, and the signing secret is what proves it really came from them) and per-tenant API keys, with overlapping validity: accept both old and new for a window, then retire the old one. That overlap is what keeps rotation from becoming an outage.
• And if you ever do application-side crypto instead of Vault, use envelope encryption: a KMS-held (Key Management Service, a hardware-backed vault for keys that never exports them) key-encryption key wraps per-tenant data keys, and every ciphertext carries a key_version column. Rotating the key-encryption key then means re-wrapping a handful of data keys, and you never have to re-encrypt every row in the database.

5.9 Rate limits and the eight-hour session

ERP users behave nothing like consumer users. A production coordinator signs in at 8:30, keeps the order screen open all day, closes the laptop for lunch, and expects to still be working at 5:00 without re-authenticating. Supabase's session model supports this if you understand its parts.

Two separate tokens are in play. The access token is the short-lived JWT the browser sends with every request, with a default lifetime of 3600 seconds, one hour (Supabase's documented default as of July 2026). Do not extend it. If anything, shorten it.

The refresh token is a longer-lived credential whose only job is to obtain a new access token, and it is single-use: exchanging it yields a fresh pair, and the old one is burned. Supabase allows a reuse interval, 10 seconds by default, to absorb races from multiple browser tabs or server-side rendering asking at the same moment.

Reuse outside that window is treated as possible theft, and Supabase revokes the descendants of that refresh token, which is the correct response, because a second party replaying a used refresh token means somebody has a copy they should not have. The browser client auto-refreshes shortly before expiry and re-checks when a tab wakes, so the lunch-break laptop sleep resolves itself on resume.

Why the eight-hour session works

The eight-hour session, in other words, is a chain of one-hour tokens. That is exactly what you want, because it bounds your revocation window: offboarding a fired rep (cutting off their access as they leave the company) or killing a stolen session takes effect within one token lifetime, without anyone re-typing a password all day.

Two server-side rules keep this honest. First, for anything security-sensitive in a Server Component or Server Action (Next.js code that runs on your server rather than in the browser), call getUser(), which revalidates the token against the Auth server, rather than trusting locally decoded claims. A JWT that looks locally valid can outlive a revoked session by up to its expiry, because the signature says nothing about whether the account still exists.

Second, configure both an inactivity timeout and a maximum session lifetime in the Auth settings. Both exist as time-boxing controls, on the Pro plan and above as of July 2026. Note the arithmetic Supabase documents: the real session length is the timeout you configure plus the JWT expiry, because an already-issued access token stays valid until it expires.

"Sessions end after N hours of inactivity" is a control a SOC 2 auditor will ask you to demonstrate, and an idle order-entry terminal in a shared showroom is a genuine risk rather than a paperwork exercise.

Rate limiting by verified tenant

Rate limiting means capping how many requests a given caller may make in a given window, and rejecting the excess. In an ERP the main worry is noisy neighbors and runaway automation more than brute-force password guessing, since Supabase Auth already limits its own auth endpoints, allowing 1,800 token requests per hour per IP address with bursts of up to 30, as of July 2026.

One tenant's integration replaying a webhook storm, or an over-eager script hammering the ATS endpoint, must not degrade every other brand sharing the database. Key limits by verified tenant and user, never by a client-supplied header (anything the client controls, the client can vary to escape the limit), and be generous to humans, strict to machines:

// apps/web/src/middleware.ts (excerpt)
import { Ratelimit } from "@upstash/ratelimit";
import { Redis } from "@upstash/redis";
import { NextResponse, type NextRequest } from "next/server";

const api = new Ratelimit({
  redis: Redis.fromEnv(),
  // 300 requests per rolling 60 seconds: humans never notice
  limiter: Ratelimit.slidingWindow(300, "60 s"),
  prefix: "rl:api",
});

export async function rateLimit(
  req: NextRequest,
  session?: { tenantId: string; userId: string },
) {
  const ip = req.headers.get("x-forwarded-for") ?? "unknown";
  const key = session
    ? `t:${session.tenantId}:u:${session.userId}`
    : `ip:${ip}`;
  const { success } = await api.limit(key);
  if (!success) {
    return new NextResponse("Too many requests", {
      status: 429,
      headers: { "Retry-After": "10" },
    });
  }
  return null; // proceed
}

Redis here is a fast shared counter store that all your server instances can reach. It has to be shared, because a limit counted separately on each of five servers is really a limit five times larger than you think. Redis.fromEnv() builds the client from environment variables so no credentials sit in the code.

slidingWindow(300, "60 s") allows 300 requests per rolling 60 seconds per key. "Sliding" means the window moves continuously rather than resetting on the minute, which stops the classic trick of firing 300 requests at 11:59:59 and 300 more at 12:00:00. The prefix just namespaces the counter keys in Redis so different limiters do not collide.

Keying the limit to a verified session

The key line is the security-relevant one. If there is a verified session, the counter key is built from the tenant id and user id that your server established, and from nothing in the request itself. If there is no session, fall back to the IP address, which is weaker but is all you have for anonymous traffic. Had the key been built from, say, a client-supplied X-Tenant-Id header, an abusive script would send a different value each request and never hit a limit at all.

When the limit is exceeded the function returns HTTP status 429, "Too Many Requests," with a Retry-After: 10 header telling well-behaved clients to wait ten seconds. Returning null means "no objection, carry on," so the caller treats a null result as permission to continue to the real handler. And 300 requests a minute sits far above what a human clicking through an order screen produces. The limit is shaped to catch runaway automation while a coordinator never notices it.

Statement timeouts and webhook queues

Back this with a database-side seatbelt, alter role app_user set statement_timeout = '10s', so that no single tenant's pathological report can hold a pooled backend hostage for minutes while everyone else queues behind it. For integration ingress (Shopify bursts on flash sales), do not reject over-limit webhooks. Put them on a queue instead (enqueue means "add to a waiting list your workers drain at their own pace"), using the background-job machinery from chapter 3. Providers retry automatically, and a retry storm is strictly worse than a queue.

5.10 Audit logging you'll thank yourself for

SOC 2 Type II, stripped of vendor mystique: SOC 2 (System and Organization Controls 2) is a security audit standard published by the AICPA, and an independent CPA (Certified Public Accountant) firm performs it.

The distinction that matters is Type I vs Type II. A Type I report says your controls were designed correctly on one day. A Type II report says an auditor observed them operating over a window and attests that they worked the whole time. The length of that window varies by company and auditor. Three months is common for a first report and twelve months is common thereafter. Supabase's own Type 2 report, for example, covers a rolling twelve-month period.

The audit measures you against the Trust Services Criteria (security, availability, processing integrity, confidentiality, and privacy), of which the security set is mandatory and the rest are add-ons you elect.

From an engineering seat, the controls that land on you are concrete:

• least-privilege access with a provable offboarding path,
• change management, the discipline of not letting code reach production unreviewed, evidenced by protected branches (branches nobody can push to directly), reviewed pull requests, and CI gates, which your Turborepo pipeline already produces,
• monitoring and alerting that someone actually receives,
• an incident-response process that has been exercised at least once,
• and logs and records, the one you must not defer.

Auditors form their opinion from evidence they can inspect, and a policy document on its own is not that evidence. Enterprise buyers increasingly ask for raw logs precisely because paper controls can be manufactured and operational history cannot.

Why you cannot retrofit the log

That last clause is why retrofitting is misery. A Type II observation window means that if you start logging when the first enterprise apparel group asks for your report, your clock starts then, which is months of delay you cannot code your way out of.

And the retrofit itself is invasive, because audit needs the actor and request id available at the database layer, which means threading identity through every write path you have built to date. You already have both: withTenant plants app.actor_id and app.request_id per transaction, and PostgREST carries the JWT. Capture flows from there for free:

create schema audit;

create table audit.entry (
  id          bigint generated always as identity primary key,
  tenant_id   uuid not null,
  actor_id    uuid,             -- who
  action      text not null,    -- did what: 'orders.update'
  entity_type text not null,
  entity_id   text not null,    -- to which record
  before      jsonb,
  after       jsonb,
  request_id  text,             -- correlate with app/API logs
  occurred_at timestamptz not null default now()   -- when
);

-- Append-only, enforced by grants: nobody gets UPDATE or DELETE.
revoke all on audit.entry from public;
grant usage on schema audit to app_user;
grant insert on audit.entry to app_user;
grant select on audit.entry to app_user;  -- reads via scoped views

create index audit_tenant_time_idx
  on audit.entry (tenant_id, occurred_at desc);
create index audit_entity_idx
  on audit.entry (tenant_id, entity_type, entity_id);

The audit entry columns

One row per event, and the columns spell out a sentence: at occurred_at, actor actor_id performed action on entity_type/entity_id belonging to tenant_id, changing it from before to after.

jsonb is Postgres's binary JSON type, which lets one column hold a whole row's worth of fields without you defining a parallel schema for every audited table, and it is queryable, so "show me every change where the price field moved" is a real query.

generated always as identity is an auto-incrementing counter, and always means an ordinary INSERT cannot supply its own value. PostgreSQL accepts a user-specified value only if the statement explicitly says OVERRIDING SYSTEM VALUE. So nobody back-dates an id by accident, and doing it on purpose leaves an obvious fingerprint in the SQL.

Append-only, enforced by grants

The permission lines are where "append-only" stops being an intention and becomes a rule the engine enforces. revoke all on audit.entry from public removes any privilege held by PUBLIC, the catch-all group that every database role belongs to. Then exactly what is needed is handed back: insert and select for the application's own role. There is deliberately no update and no delete for anybody.

This means that even a fully compromised app_user, an attacker with your application's database password, can write new audit rows but cannot erase the ones recording what they just did. That is the entire value of the log. A log an intruder can edit proves nothing.

Note that authenticated gets nothing here: the trigger below runs as SECURITY DEFINER, so it writes with its creator's privileges and does not need end users to hold any grant on the audit table. Letting them hold one would let them forge entries.

The two indexes serve the two questions people actually ask. (tenant_id, occurred_at desc) answers "what happened at this brand recently," with desc so newest-first paging needs no sorting. (tenant_id, entity_type, entity_id) answers "show me the full history of this one invoice." Both lead with tenant_id, for the reasons in section 5.6.

A trigger you cannot forget to call

A row-level trigger covers every data mutation on sensitive tables without touching application code. A trigger is a function Postgres runs automatically whenever a row changes. You cannot forget to call it, because you never call it:

create or replace function audit.track_row_change()
returns trigger
language plpgsql security definer
set search_path = ''
as $$
declare
  v_before jsonb;
  v_after  jsonb;
  v_row    jsonb;
begin
  if tg_op in ('UPDATE','DELETE') then
    v_before := to_jsonb(old);
  end if;
  if tg_op in ('INSERT','UPDATE') then
    v_after := to_jsonb(new);
  end if;
  v_row := coalesce(v_after, v_before);

  insert into audit.entry
    (tenant_id, actor_id, action, entity_type, entity_id,
     before, after, request_id)
  values (
    (v_row ->> 'tenant_id')::uuid,
    coalesce(
      (select auth.uid()),          -- PostgREST path
      nullif(current_setting('app.actor_id', true), '')::uuid
    ),
    tg_table_name || '.' || lower(tg_op),
    tg_table_name,
    v_row ->> 'id',
    v_before,
    v_after,
    nullif(current_setting('app.request_id', true), '')
  );
  return null;   -- AFTER triggers ignore the return value
end
$$;

create trigger orders_audit
  after insert or update or delete on public.orders
  for each row execute function audit.track_row_change();
-- repeat for invoices, accounts, integration_connections, styles

Inside a trigger function, Postgres hands you several special variables. new is the row as it will be after the change, and old is the row as it was before. On an INSERT there is no old, and on a DELETE there is no new.

The two if blocks handle that asymmetry directly: each one touches new or old only for the operations where that record actually exists, and converts it to JSON with to_jsonb(). Then coalesce(v_after, v_before) picks whichever version is present and calls it v_row, the row we are describing, whether it was just created, just changed, or just deleted.

Because v_row is JSON, the rest reads values out of it with the ->> operator, which pulls a named field out as text. (v_row ->> 'tenant_id')::uuid gets the owning brand, and v_row ->> 'id' gets the record's own id. Going through JSON rather than writing new.id keeps one function working for all three operations without any special-casing.

tg_op holds the operation name ('INSERT', 'UPDATE', 'DELETE') and tg_table_name holds the table's name, so tg_table_name || '.' || lower(tg_op) builds an action string like orders.update automatically. The actor lookup is the same both-roads pattern as app.current_tenant_id(): try auth.uid() first for requests that came through PostgREST with a JWT, and fall back to the app.actor_id note that withTenant planted for requests that came through your own pool. Either way the identity is one your server verified.

What the trigger records, and what it misses

v_before and v_after capture the diff. An insert leaves v_before null, and a delete leaves v_after null. That is exactly right. return null is safe here because PostgreSQL documents that the return value of an AFTER row-level trigger "is always ignored."

One caveat about the list at the bottom: this generic function assumes the audited table has both a tenant_id and a single id column. A table keyed on a pair, like memberships with its (tenant_id, user_id) primary key, needs its own small wrapper that builds entity_id from the columns it actually has.

The trigger itself fires after insert or update or delete, and that word after means it only records changes that actually succeeded. It also fires for each row, so it fires once per affected row rather than once per statement. An UPDATE that touches forty order lines produces forty audit entries. Because it lives in the database, it captures writes from your Next.js app, from a background job, from PostgREST, and from a colleague running SQL by hand in a console. There is no code path that goes around it.

Triggers capture what changed. They cannot capture intent. Business events (order approved, invoice sent to Harborline, credit memo issued, rep reassigned) need explicit audit.entry inserts from the application, with meaningful action names, because "who approved this order and when" is the question a tenant's controller (and your auditor) will actually ask, and no diff of column values answers it.

Shipping the log outward

For tamper-evidence beyond grants, ship the log outward on a schedule. A nightly export to object storage (a cloud file store such as S3) with a retention lock switched on is cheap, and a retention lock means the provider itself refuses deletion or modification until a date you set. That upgrades your claim from "trust our grants" to "the copy physically cannot be altered, including by us."

Partition audit.entry by month once volume warrants it, since partitioning splits one huge table into per-month pieces so queries and deletions touch only the months they need. And expose tenant-facing reads through a view that RLS-scopes on tenant_id: a brand's audit trail is their data too, and "export my audit log" is a feature you will sell to tenants as well as a control you will show to an auditor.

Field notes & further reading

• PostgreSQL documentation: Row Security Policies — the authoritative source for permissive/restrictive composition, the default-deny rule, USING vs WITH CHECK, owner and BYPASSRLS semantics.
• PostgreSQL documentation: SET — the exact wording that SET LOCAL outside a transaction block "emits a warning and otherwise has no effect," plus the set_config equivalence.
• Supabase: RLS Performance and Best Practices — the initplan wrap, security-definer helpers, explicit filters, and the measured before/after timings on a 100,000-row table.
• Supabase: Database Advisors — the lint catalog, including 0003_auth_rls_initplan (unwrapped auth calls) and 0015_rls_references_user_metadata (policies that trust user-editable claims).
• AWS Database Blog: Multi-tenant data isolation with PostgreSQL Row Level Security — the SaaS Factory treatment of the shared-schema + RLS model, with a companion sample repo.
• Supabase: Connecting to your database — Supavisor transaction vs session mode, ports, prepared-statement limits, and when to use a direct connection.
• Supabase: Vault — encrypted secrets in Postgres, the decrypted_secrets view, root-key behavior across restores and migrations.
• Supabase: User sessions — access/refresh token lifecycle, the reuse interval, inactivity timeouts and session lifetimes.
• Supabase: Auth rate limits — the default per-IP ceilings on token refresh, sign-in, verification and email endpoints.