The WHERE clause you have to remember on every query

In most embedded analytics, the only thing standing between two customers' data is a WHERE workspace_id = :me that your application code has to remember on every single query.

It holds for the first dashboard and the tenth. Then one query forgets the filter, and customer A is looking at customer B's revenue inside a tile that renders without complaint.

That is not an analytics bug. It is a data-leak security bug wearing an analytics costume. The output looks like a chart, so it gets reviewed like a chart, when it should be reviewed like an auth boundary.

The story

Here is an illustrative scenario. The company is invented; the mechanism is not, and I have watched the shape of this happen more than once.

A retail-analytics SaaS sells a dashboard to store chains. Each chain is a workspace. Inside a workspace, a regional manager drills into a single store: revenue today, orders this week, a trend line by day.

The isolation lives where it usually lives, in the API layer. Every handler pulls the workspace off the authenticated session and appends the filter before the query runs.

SELECT sum(revenue), sum(orders)
FROM store_daily_sales
WHERE workspace_id = $1   -- from the session, never the client
  AND store_id = $2
  AND day >= $3;

For a year this is fine. There are maybe fifteen endpoints. Code review catches the one time someone pastes a query without the workspace_id line. The team feels good about it, and they are not wrong to.

Then product ships the feature everyone asks for: "ask your data." A chat box on the dashboard. A regional manager types "which of my stores grew fastest last month" and gets an answer in plain language.

Under the hood, a model now writes the SQL.

Read that again, because it is the whole essay. The filter that used to live in fifteen hand-reviewed endpoints is now the responsibility of a language model generating novel SQL on every question, for every user, in production. You are trusting the model to remember WHERE workspace_id = :me on a query no human will read before it runs.

The dashboard tiles and the copilot are both software consumers. Neither of them can look at a result and think "wait, these numbers are too big, this must be someone else's chain." A human analyst might catch it. A tile just renders whatever it is handed, and a model answers fluently in the voice of confidence.

The insight

Isolation re-implemented per query is a breach waiting for the one query that forgets. That was already true when the queries were handwritten, when the same risk sat hidden behind a small, countable set of endpoints a person could review. The copilot just removed that cover.

When the consumer is software, "I'll remember the filter" stops being a plan. The dashboard tile cannot sanity-check its own scope. The model cannot either, and it fails in the most dangerous direction, by producing a plausible answer rather than an error. A missing WHERE does not throw an error. It silently returns the wrong rows.

The deeper problem is where the tenant boundary lives. Put it in the SQL and everything that writes SQL, including a model, has to remember it on every query.

How teams usually try to hold the line

There is an honest progression here, and each step is a real improvement that runs out of road.

Roll-your-own filter in application code. The WHERE workspace_id = :me pattern. The rule is correct, but it lives in application logic and gets re-typed on every query. Its safety is a function of discipline and how many surfaces you have. Add a surface that writes its own queries, like a copilot, and discipline is no longer enough, because there is no fixed set of queries to be disciplined about.

Database session-variable row-level security. Stronger, because now the database enforces the predicate instead of your handlers. This is a genuine step up. But it has sharp edges: under a connection pooler in transaction mode, a session variable set for one tenant can bleed into the next borrower of that connection unless you are meticulous about resetting it. And it only covers the SQL layer. It does nothing for the analytics API contract above it, and nothing for the model-driven interface that calls that API. Your isolation and your product surfaces are enforced in different places.

Generic embedded-dashboard tools with signed or locked parameters. These scope a human-facing dashboard: you sign the tenant into an embed token, the tool refuses to let the browser widen it. That is the right idea, and for a rendered dashboard it works. But it secures a dashboard, not a governed API and not an AI copilot. The moment your consumer is software that composes its own queries, a locked dashboard parameter is not the boundary anymore.

Each of these helps. Each of them enforces isolation at a different layer than where your next consumer will show up. That is the pattern worth naming: isolation keeps getting re-implemented per surface, and every new surface is a new place to forget.

The principle

Isolation belongs at the interface, declared once and enforced by the runtime, not re-added per query or per surface.

If the boundary is a property of the interface that every consumer calls, then the dashboard, the reporting API, and the copilot all inherit the same rule without any of them writing a WHERE. A caller that never writes the filter cannot forget the filter.

How Gaur does this

In Gaur, consumers do not send SQL. They call a contract, and the row-level security is part of the contract, so it is applied to every query the runtime generates, on every protocol, whether the caller is a dashboard tile or a model.

Two things make this hold up in a multi-tenant setting, and the distinction between them is the important part.

Auth context is bound to the API key when the key is created. It is immutable and workspace-level, for example workspace_id. The caller cannot change it, because it was fixed the moment the key was issued.

Request context is sent per call by your backend, derived from the authenticated session, and varies per end user, for example store_id. This is the piece the browser must never set. If an end user could put their own store_id on the request, they could read other stores. Your backend sets it from the session it already trusts; the client never touches it.

The rule itself is a boolean sql condition with {{ placeholder }} tokens, plus a parameters map that binds each token to either auth.<field> or request.<field>. Here is an illustrative contract. The retail data model is invented; the rls structure and its enforcement are the real, documented mechanism.

{
  "name": "store_sales",
  "sources": [{ "source_type": "model", "name": "store_daily_sales", "alias": "s" }],
  "dimensions": {
    "day": { "sql": "s.day", "type": "date", "semantic_type": "time", "time_grains": ["day","week","month"] }
  },
  "measures": {
    "revenue": { "sql": "sum(s.revenue)", "type": "number", "additivity": "additive" },
    "orders":  { "sql": "sum(s.orders)",  "type": "number", "additivity": "additive" }
  },
  "rls": {
    "sql": "s.workspace_id = {{ workspace_id }} AND s.store_id = {{ store_id }}",
    "parameters": {
      "workspace_id": "auth.workspace_id",
      "store_id": "request.store_id"
    }
  }
}

Both tokens are injected as bound SQL parameters, never string-interpolated into the query text. So the isolation predicate is not a place where injection lives, and the values arrive as parameters the caller did not get to write.

The same rule is enforced identically across the REST query API, the OpenAI-compatible chat endpoint, and MCP. That is the line that matters for the story above. When the team adds the copilot, the copilot talks to the chat endpoint, which calls the same contract, which carries the same rls block. The AI feature inherits the exact isolation the dashboard already had. Nothing needs to be kept in sync, because no caller, human or model, ever writes the filter at all.

Now the honest part. A contract does not stop you from writing the wrong rule. If you bind store_id to auth when it should be request, or you forget a clause, the runtime will faithfully enforce your mistake. What the contract changes is where the risk lives. It moves you from "every endpoint and every generated query must remember the filter" to "one rule, written once, tested once." You review the boundary in a single place instead of trusting every surface to re-add it. This does not guarantee correctness, and a wrong rule is still enforced faithfully. What it does is make the forgotten WHERE structurally hard to ship, a narrower and more honest promise.

Shameless plug

If you are shipping customer-facing analytics or an AI copilot over per-tenant data, the failure in this post is your default failure, not an edge case. Anywhere the consumer is software (an embedded dashboard, a reporting API, a tool-calling agent, a chat box over your business data) the tenant boundary cannot depend on every query remembering a WHERE.

Gaur lets you declare the isolation rule once on the contract and have the runtime enforce it identically across REST, chat, and MCP, so a new surface inherits the boundary instead of re-implementing it. It helps make the cross-tenant leak structurally hard to ship, not impossible to write.

If that is the boundary you are trying to hold, book a demo at gaur.run and bring your messiest multi-tenant case.