Designing Multi-Tenant APIs That Scale

"Multi-tenant" means one running app serving many separate customers. Most do it the cheap way: one database, shared tables, and a tenant_id column every query filters on.

@itsnas — TypeScript

Hide line numbersCopy code

// GET /invoices?status=overdue
async function listOverdueInvoices(req: Request) {
  const invoices = await db`
    SELECT id, customer_name, amount, due_date
    FROM invoices
    WHERE tenant_id = ${req.tenantId}
      AND status = 'overdue'
    ORDER BY due_date ASC
  `
 
  return json(200, { invoices })
}

main

Nas (@itsnas)

req.tenantId comes from the API key or JWT on the server, never from anything the client sends; otherwise none of this matters. The query is fine. The guarantee isn't: isolation exists because a person typed that WHERE, in every file, every time.

So a teammate ships a debug route, SELECT * FROM invoices WHERE status = 'overdue' LIMIT 20, and it returns the top 20 overdue invoices from every tenant to whoever calls it. Nothing crashes. The demo works. That's a data leak that looks like a feature.

Three ways to keep tenants apart

Most teams start with shared tables because it's cheapest, then find the leak in production or an audit. The usual right move isn't to re-architect; it's to push enforcement into the shared database.

Make the database refuse

#postgres has row-level security: a policy attached to the table once, applied to every query from then on, whether the query filters or not.

@itsnas — SQL

Hide line numbersCopy code

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

main

Nas (@itsnas)

The app sets app.tenant_id once per request, inside the transaction that runs the queries:

@itsnas — TypeScript

Hide line numbersCopy code

async function withTenant<T>(
  tenantId: string,
  fn: (db: Sql) => Promise<T>,
): Promise<T> {
  return db.begin(async tx => {
    await tx`SELECT set_config('app.tenant_id', ${tenantId}, true)`
    return fn(tx)
  })
}
 
// GET /invoices?status=overdue
async function listOverdueInvoices(req: Request) {
  const invoices = await withTenant(
    req.tenantId,
    db => db`
    SELECT id, customer_name, amount, due_date
    FROM invoices
    WHERE status = 'overdue'
    ORDER BY due_date ASC
  `,
  )
 
  return json(200, { invoices })
}

Show all 24 lines

main

Nas (@itsnas)

Re-run the debug route: it still has no tenant_id clause, and it now returns only the caller's rows. The forgotten clause went from a leak to a non-event. The true in set_config scopes the setting to the transaction, so a pooled connection can't carry tenant A's setting into tenant B's request.

One more trap: a route that never calls withTenant. As written, current_setting throws when the setting is missing, so the bug fails loudly. Don't "fix" that error with a fallback:

@itsnas — SQL

Hide line numbersCopy code

-- Dangerous: an unset tenant_id becomes NULL, and NULL = anything is
-- NULL in SQL, which the USING clause treats as "no rows match" - not
-- a leak, but a silent, confusing failure that looks like the tenant
-- has no data.
USING (tenant_id = current_setting('app.tenant_id', true)::uuid)

main

Nas (@itsnas)

Loud failure in the logs beats a tenant who suddenly appears to have no data.

Isolated rows, shared capacity

RLS decides which rows come back, not how much of the database a tenant may spend. One tenant's report scanning millions of rows still makes every other tenant wait: the noisy neighbour. The fixes are rate limits and connection pools, and both share one trap: they look right on one instance and are wrong on ten.

The rate-limit counter needs one shared home, so every instance counts against the same number. #redis is the usual pick:

@itsnas — TypeScript

Hide line numbersCopy code

async function checkRateLimit(tenantId: string): Promise<boolean> {
  const key = `rate-limit:${tenantId}:${currentMinuteBucket()}`
  const count = await redis.incr(key)
  if (count === 1) await redis.expire(key, 60)
 
  return count <= REQUESTS_PER_MINUTE
}
 
// Runs before the handler, for every route.
async function rateLimitMiddleware(
  req: Request,
  next: () => Promise<Response>,
) {
  const allowed = await checkRateLimit(req.tenantId)
  if (!allowed) {
    return json(
      429,
      { error: 'Rate limit exceeded' },
      {
        'Retry-After': '60',
      },
    )
  }
 
  return next()
}

Show all 26 lines

main

Nas (@itsnas)

Connections get the same treatment from the other side. Either divide Postgres's real budget by the instance count, or put a pooler in front that enforces the ceiling no matter how many instances exist:

PgBouncer's fast mode lends a real connection for one transaction at a time. RLS survives that because its setting is transaction-scoped. A schema-per-tenant design that switches search_path per session does not: the next tenant to borrow the connection can inherit the wrong schema.

Another tenant's invoice is a 404, not a 403

Tenant A asks for GET /invoices/:id with tenant B's ID. RLS returns zero rows, so nothing leaks. But if the handler separately checks "does this ID exist?" and answers 403 for "real, not yours" and 404 for "unknown", an attacker can enumerate every real ID on the platform without reading a row: a textbook broken object level authorization bug.

Don't check existence separately. Zero rows is 404, whatever the reason. Map every failure in one middleware, not per handler:

Business logic never sees a tenant id

The original leak was possible because ordinary code held an unscoped database handle. So don't give it one. Pass a context whose db is already inside withTenant:

@itsnas — TypeScript

Hide line numbersCopy code

interface RequestContext {
  tenantId: string
  db: Sql // already inside withTenant's transaction
}
 
// Domain logic. No tenant_id in sight, no way to query outside scope,
// because `ctx.db` is the only database handle this function ever
// sees, and it was already scoped before this function was called.
async function markInvoicePaid(
  ctx: RequestContext,
  invoiceId: string,
) {
  const [invoice] = await ctx.db`
    SELECT id, status FROM invoices WHERE id = ${invoiceId}
  `
  if (!invoice) throw new NotFoundError('invoice')
  if (invoice.status === 'paid')
    throw new ConflictError('already paid')
 
  await ctx.db`
    UPDATE invoices SET status = 'paid' WHERE id = ${invoiceId}
  `
}
 
// Wiring. Every cross-cutting concern this post covered happens here,
// once, not re-implemented per handler.
async function handlePayInvoice(req: Request) {
  return withTenant(req.tenantId, async db => {
    const ctx: RequestContext = { tenantId: req.tenantId, db }
    await markInvoicePaid(ctx, req.params.invoiceId)
    return json(200, { ok: true })
  })
}

Show all 33 lines

main

Nas (@itsnas)

markInvoicePaid has no tenant id to forget and no way to query outside its tenant, and it can be unit tested with no auth or HTTP in sight.

Convention or structure

Every failure in this post had the same shape: a guarantee held up by someone remembering, or by one instance assuming it was alone.

When a guarantee matters, ask what enforces it when somebody forgets. If the answer is "review", it isn't enforced yet.

Some tenants still need their own schema or database, usually for a contract. That trades the forgotten WHERE for knowing where each tenant lives and running every migration hundreds of times; see Migrating Schema-Per-Tenant Databases at Scale.