Multi-Tenant SQL Reporting: How to Show Each Customer Only Their Own Data

You're three months into your SaaS. Customers want dashboards. The first one is easy — Acme Corp wants to see their orders, their signups, their revenue. You write a quick WHERE tenant_id = 42 and ship it.

Six months later you have 400 tenants, 30 reports, an AI assistant that writes SQL on the fly, and an engineer who one Tuesday morning forgets the WHERE clause on a single query. Now Acme Corp can see Globex's data. That's a Monday-morning headline you don't want.

This article is about how to architect multi-tenant SQL reporting so a single forgotten clause never becomes a breach. We'll cover tenant schema design, Postgres Row-Level Security, how to wire it to JWTs, indexing for performance, and the gotchas that bite people in production.

The three multi-tenant patterns (and why one wins for reporting)

Before any SQL, pick your isolation model. There are three classic options:

Pattern	Isolation	Cost	Best for
Database-per-tenant	Strongest	Highest (ops, migrations, connections)	Enterprise, regulated industries
Schema-per-tenant	Strong	High (schemas multiply fast)	Mid-market with <1000 tenants
Shared schema with tenant_id	Logical	Lowest	Most B2B SaaS

For 90% of SaaS apps, shared schema with a tenant_id column is the right call. It's cheap, it scales, and Postgres gives you the tools to make it safe. Everything below assumes this pattern.

Step 1: Tag every row with a tenant_id

Every table that stores customer data needs a tenant discriminator. No exceptions. Even your events, audit_logs, and ai_query_history tables.

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  tenant_id    BIGINT NOT NULL REFERENCES tenants(id),
  customer_id  BIGINT NOT NULL,
  amount_cents INT NOT NULL,
  status       TEXT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);

Two things to notice: tenant_id is NOT NULL (a null tenant means an orphan row that bypasses your filters), and the index leads with tenant_id. Every reporting query will filter by tenant, so this becomes your bread-and-butter index.

Step 2: Enable Row-Level Security as a safety net

Application-level filtering is fine until someone forgets. Postgres Row-Level Security (RLS) enforces the filter at the database, so even a raw SELECT * FROM orders returns only the rows the current tenant is allowed to see.

-- 1. Turn on RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 2. Define the policy
CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

The USING clause filters rows you can read; the WITH CHECK clause prevents you from inserting or updating rows into another tenant. Together they cover both directions.

Now every connection has to set app.tenant_id before issuing queries:

SET LOCAL app.tenant_id = '42';
SELECT count(*) FROM orders;  -- automatically scoped to tenant 42

SET LOCAL ties the setting to the current transaction, so it can't leak across requests on a pooled connection — important.

Step 3: Wire the tenant context to your auth layer

The setting has to come from a trusted source. The standard pattern is to extract it from a verified JWT:

# Pseudocode — runs on every request inside a transaction
claims = jwt.decode(request.token, public_key)
tenant_id = claims["tenant_id"]

with db.transaction() as tx:
    tx.execute("SET LOCAL app.tenant_id = %s", [tenant_id])
    # All queries inside this transaction are now tenant-scoped
    run_report(tx)

Two non-negotiables:

1. Decode the JWT server-side with a verified signature. Never trust a tenant_id coming from a query parameter or request body.
2. Use SET LOCAL, not SET. Connection pools recycle connections; SET persists, SET LOCAL doesn't.

Step 4: Reporting queries that "just work"

With RLS on, your reporting queries get simpler — you don't write WHERE tenant_id = ? in every query. Compare:

-- Before RLS — you must remember every time
SELECT date_trunc('day', created_at) AS day,
       count(*) AS orders,
       sum(amount_cents) / 100.0 AS revenue
FROM orders
WHERE tenant_id = $1
  AND created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

-- After RLS — same query, scoped automatically
SELECT date_trunc('day', created_at) AS day,
       count(*) AS orders,
       sum(amount_cents) / 100.0 AS revenue
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

This matters most for AI-generated SQL and user-defined reports. If an AI assistant writes a query against your warehouse, you don't want safety to depend on whether it remembered the tenant filter. RLS makes the database itself the last line of defense.

Step 5: Handle cross-tenant admin queries explicitly

Eventually you'll want internal queries that span tenants — billing rollups, churn cohorts, support tooling. Don't disable RLS; create a separate admin role:

CREATE ROLE app_admin;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY admin_all_access ON orders
  FOR ALL
  TO app_admin
  USING (true);

FORCE ROW LEVEL SECURITY makes RLS apply even to the table owner, which prevents your migrations user from accidentally reading everyone's data. The app_admin role has its own explicit policy.

Indexing for tenant-scoped queries

A common surprise: after adding RLS, queries that used to be fast get slow. The fix is almost always indexes that lead with tenant_id.

-- Good — supports the most common reporting pattern
CREATE INDEX orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at DESC);

-- Also useful for tenant-scoped joins
CREATE INDEX events_tenant_user_idx
  ON events (tenant_id, user_id, created_at DESC);

Rule of thumb: any index you'd create on a single-tenant table, prefix with tenant_id for a multi-tenant one. Postgres can then satisfy both the RLS predicate and your ORDER BY/filter from the same index.

Common gotchas

Here's what bites people in production:

Forgetting to set app.tenant_id. If RLS is on and the setting isn't set, queries return zero rows — not an error. Wrap your transaction setup so missing context throws loudly in development.

Using SET instead of SET LOCAL. On a pooled connection, the next request inherits the previous tenant's context. This is a real, observed bug — and a nasty one because the symptom is "wrong customer's data shown."

RLS on the parent table but not on partitions. Partitioned tables need RLS enabled on each partition, or queries skip the policy entirely. Easy to miss.

Cross-tenant joins. A query like SELECT * FROM orders o JOIN users u ON o.user_id = u.id works fine if both tables have RLS. But if users doesn't, you can leak data through the join. Enable RLS on every tenant-scoped table — not just the ones you think are sensitive.

Bypassing RLS for views. Views run with the privileges of their creator by default. Use CREATE VIEW … WITH (security_invoker = true) (Postgres 15+) so RLS evaluates against the calling user, not the view owner.

No tenant_id on derived tables. Materialized views and reporting tables get rebuilt nightly and someone forgets the tenant column. Now your "fast dashboard" returns everyone's data. Always carry tenant_id through.

Key takeaways

For multi-tenant SQL reporting, shared schema with tenant_id is the pragmatic default. Row-Level Security turns that filter from a convention you have to remember into a constraint the database enforces. Pair it with JWT-driven session context, indexes that lead with tenant_id, and an explicit admin role for cross-tenant queries.

The payoff is that your reporting layer — whether you build it yourself, embed a third-party dashboard, or let an AI assistant write queries on top of your schema — becomes safe by default. The database itself refuses to leak data, no matter what query lands on it.

Over to you

How are you handling tenant isolation in your reporting layer today — RLS, application filters, schema-per-tenant, or something else? Have you hit a multi-tenant gotcha that wasn't on this list? Drop it in the comments — I'd love to compare notes.