Row, Schema, or Database? Tenant Isolation for Customer-Facing Reporting

Your biggest prospect's security team asks for its own database. The same week, a support ticket arrives: a customer opened the "Shipper" filter on a report and saw another customer's client names in the list.

Both are tenant isolation problems, but they are different decisions. The first is about where each customer's data lives. The second is about whether customer context reaches every query, including the small ones nobody thinks of as queries. The second is easier to miss, and it can happen under any model.

The examples below use DockLine, a fictional warehouse software company, not a real business or customer. Its customers are third-party logistics providers (3PLs). Each 3PL runs several warehouses and stores inventory for many shippers.

The decision: row, schema, or database

There are three ways to separate customer data, and most companies end up mixing them.

Physical separation changes how isolation fails rather than removing the risk. With shared tables, a missing filter exposes everyone. With routing, a bug sends one customer's queries to another customer's data.

The default: start with shared tables and a customer filter that your server applies to every query, where nothing in the browser can change it. Move individual customers to a dedicated schema or database only when there is a concrete reason. Most mid-market companies end up with a hybrid: most customers share tables, and a few large ones get their own schema or database.

A hybrid only works if the same report definitions run against every layout. If moving a customer means editing their reports, your reporting is tied to the physical layout, and every move gets more expensive.

When to move a customer up

A security questionnaire or contract clause. Many are satisfied without moving anything. A clear explanation of how isolation is enforced, plus evidence that you test it, answers most questions. Move only when the contract explicitly requires physical separation.

Data residency. If a customer's data must stay in a region, it needs a database in that region.

Regulated data. Health, financial, or government data can carry separation or key management requirements. Read the actual requirement, because some are met by a dedicated schema and others need a dedicated database or per-customer keys.

One customer's load slowing everyone else. Try indexes, query limits, caching, or a read replica first. If one customer's volume dwarfs the rest, a dedicated database becomes a capacity decision.

Per-customer restore or deletion obligations. If you must restore one customer to a point in time, or prove their data is gone at contract end, a separate schema or database turns that into one operation instead of a careful scripted delete.

Moving a customer

Suppose DockLine's largest 3PL signs a renewal that requires its own database. The move itself is routine if you plan for a few things. Copy every customer-scoped table, including dimension tables like shippers and warehouses. Keep IDs stable, because saved filters, bookmarks, and emailed links point to them. Change where that customer's queries run, not the report definitions. Pause writes briefly for the cutover. Compare reports, filter lists, and exports before and after for users at different levels; row counts and totals should match exactly. Then delete the customer's rows from the shared tables and confirm a query there returns nothing. That last step is the one teams skip, and until it is done, the contract's promise is not true.

What typically breaks:

• Custom SQL reports with hardcoded table or schema names, which keep reading the shared tables.
• Cached results from before the cutover, which keep serving old numbers until they expire.
• Scheduled reports that stored a query or data location when they were created.
• Benchmark and aggregate tables that still include the customer's history.
• Pipelines that write to both places, or only to the old one.

Where isolation leaks

Customer context should be set by your backend when a user signs in and enforced on the server before any query runs. Browser-side filters, hidden dashboard filters, and URL parameters are for presentation. Anyone can edit them.

The main chart on a report is rarely the problem. These paths are.

Filter lists and dimension tables. Filters fill their dropdowns with their own queries, often straight from a dimension table:

SELECT DISTINCT name FROM shippers;                        -- every customer's shippers
SELECT DISTINCT name FROM shippers WHERE tenant_id = :t;   -- correct

You usually find out when a customer reports an unfamiliar name in a dropdown. The fix is to give every table with customer data a customer column and a filter, keep shared reference data in separate tables, and make queries against unclassified tables fail instead of running unfiltered.

Caches. A cache keyed only on the report serves one customer's results to the next, which shows up as "my numbers changed when I refreshed." Key the cache on the final query after customer and user filters are applied, and clear a user's entries when their access changes.

Scheduled reports. These run later, with no user in a session. If a report is generated once as its author and emailed to others, every recipient sees what the author can see. Keep recipients inside the customer and limited to people who should see the author's view. Check the list again at send time, so people who leave the customer stop receiving it, and be careful with external distribution lists, because you cannot tell who is on them.

Support "view as" tools. The safe version signs in as a specific customer user and goes through normal enforcement, with every use logged and access limited in time. An admin screen with a customer dropdown is a cross-customer tool by design.

AI assistants. An assistant that writes queries is a new query author. If it has its own database connection, your filters do not apply to it. Test it by asking for another customer's data by name.

Benchmarks. "How does my warehouse compare to the network?" is a deliberate exception to isolation. Serve it from a precomputed, anonymized table with a minimum group size, and watch small groups. If only three 3PLs run cold storage in one region, a regional cold storage average lets each of them estimate the others.

Missing customer context. A user with no customer value, or a new customer that is not configured yet, should get no rows or an error. Some tools fall back to a default connection or unfiltered data instead. That is acceptable for internal reporting and dangerous for customer-facing reporting.

Scope inside a customer

Tenant isolation is all or nothing. Access inside a customer is graded and changes often: a regional director and a site manager need different views of the same reports. Keep the two as separate rules. Then a bug in your scope logic can only misallocate access within one customer instead of becoming a cross-customer incident.

Derive scope from permissions your application already has. DockLine already assigns users to warehouses for its operational screens, so reporting should use that same assignment rather than a second copy kept by hand. Express scope as allowed values per dimension, such as warehouses and shippers, not as a position in an org chart. A shipper's logistics lead sees only their own inventory, across every warehouse that holds it, and that does not fit a tree.

For roles meant to see everything, grant an explicit "all" so new warehouses are included automatically. Everyone else should be denied a new warehouse until someone assigns it. When a user's assignments change, their schedules and cached results do not follow on their own, so recheck them. Users who work with two customers should get a separate identity for each.

A checklist to save

Security reviewers care more about evidence of testing than about which model you chose. Each item below is something you can test and show.

1. Each customer's isolation model is written down, along with where customer context is set.
2. Customer and user filters are applied on the server, and editing URL parameters or filter state never widens access.
3. A user with no customer value, or a new customer before setup, sees no rows or an error.
4. Users from two customers see different data in every report, filter list, export, and drill-through.
5. The same report loaded for two customers back to back does not return cached results across customers.
6. Scheduled reports only reach people inside the customer, and schedules and caches are rechecked when a user's access changes.
7. The AI assistant returns nothing when asked for another customer's data by name.
8. Support access is logged, time-limited, and reviewed, and deletion covers backups, caches, and aggregate tables.

Run the list before launch, after any customer moves to a new layout, and whenever you add a new way to query data.

Questions to ask any analytics vendor

If you are choosing one analytics layer to power both customer dashboards and AI experiences, isolation has to hold for both. Most tools can filter rows and switch schemas or databases. The differences show up in these questions. Ask for the answers in documentation, not a demo.

1. Where do isolation rules live, and who can change them? Rules defined once on the data connection cover everything built on it. Rules defined inside each report have to be recreated and reviewed every time.
2. What happens when a user's customer value is missing or matches nothing? You want an error or no rows, not a default connection or unfiltered data.
3. Do dashboards and AI use the same security rules and the same definitions? An assistant or agent should query through the same layer as your dashboards, with the same customer filters and the same metric definitions. If AI gets its own connection or its own copy of the logic, you now have two things to secure and two answers to reconcile.
4. Does row security apply to every query path? That includes SQL, API access, filter lists, and exports.
5. How are users who belong to several customers handled? Ask whether customer context is tied to the user or to the session.
6. Can the same reports run across shared, schema, and database layouts? Moving one customer to a dedicated database should be a configuration change.
7. Can you preview exactly what a specific customer user will see? This is how you verify isolation during onboarding and audits.

How Semaphor handles it

Semaphor's Unified Security policies are defined on the data connection and assigned to customers and to individual users, so the customer boundary and each user's access are set separately. Semaphor applies the resulting rules on the server before a query reaches the database. Each customer can be routed to its own database, its own schema, or filtered rows in shared tables, and these can be combined.

When a required value is missing, Semaphor blocks the query or returns no rows, depending on how the rule is set, and it never enforces a partial policy. Security preview shows exactly what a given viewer will receive. AI queries through Semaphor's MCP server go through the same enforcement, and Semantic Domain access controls which modeled datasets each customer or user can explore.

Closing

Where the data lives is a decision you make a few times. Whether customer context reaches every query is something you verify every release. Choose the simplest layout your contracts allow, enforce customer context on the server, and keep testing the quiet paths.