Our engine never sees your warehouse password
datahashi

BlogGovernance & trust

Multi-tenant customer dashboards: row-level policy isolation

How platform engineers secure embedded customer dashboards using JWT claims, row-level policies, and query metering in a thin semantic layer.

The datahashi team7 min read

The problem

Platform engineering teams building customer-facing analytics portals face strict tenant isolation requirements. Enterprise customers log into your portal to inspect API call volume and incurred cloud costs. The front-end application requests these numbers dynamically over HTTP APIs.

When developers build dashboards with raw database queries, they rely on application-level filtering like WHERE tenant_id = ?. This approach inevitably breaks under operational pressure. A newly hired backend developer forgets the tenant filter on an aggregate query. An analyst building an export widget accepts an untrusted tenant parameter from a query string. A single missed filter clause exposes one customer’s private API volume and billing amounts to a competing business.

Security leads and enterprise compliance auditors reject client-side filtering. They require server-enforced tenant isolation where caller identity is extracted exclusively from cryptographic tokens. You need a semantic layer that binds row-level policies directly to verified JWT claims before SQL compilation occurs.

+--------------------------------------------------------------+
| Customer logs into Portal: Token asserts tenant_id=cust_acme |
+--------------------------------------------------------------+
                               |
               Client-Controlled Parameters ✗
                               |
        Malicious user modifies URL: ?tenant_id=cust_globex
           Raw SQL leaks competing customer usage data! ✗
                               |
          Server-Enforced Semantic Row Security ✓
                               |
      Engine binds token claim: WHERE tenant_id = 'cust_acme'
         Client parameters cannot widen data access ✓

The data

The platform billing database structures customer activity across two physical warehouse tables. The tenants table records organization identifiers and subscription tiers. The billing_events table stores daily metered API usage and aggregated cloud cost amounts.

Each row in billing_events represents daily usage telemetry for a specific organization. The foreign key tenant_id maps each event to its parent tenant organization.

CREATE TABLE tenants (
    tenant_id VARCHAR PRIMARY KEY,
    name VARCHAR NOT NULL,
    tier VARCHAR NOT NULL
);

CREATE TABLE billing_events (
    event_id VARCHAR PRIMARY KEY,
    tenant_id VARCHAR NOT NULL REFERENCES tenants(tenant_id),
    event_date DATE NOT NULL,
    api_calls INTEGER NOT NULL,
    cost_usd DOUBLE NOT NULL
);

INSERT INTO tenants VALUES
    ('cust_acme', 'Acme Corp', 'enterprise'),
    ('cust_globex', 'Globex Corp', 'pro'),
    ('cust_initech', 'Initech LLC', 'starter');

INSERT INTO billing_events VALUES
    ('ev_1', 'cust_acme', '2026-10-01', 150000, 150.0),
    ('ev_2', 'cust_acme', '2026-10-02', 175000, 175.0),
    ('ev_3', 'cust_globex', '2026-10-01', 40000, 40.0),
    ('ev_4', 'cust_globex', '2026-10-02', 45000, 45.0),
    ('ev_5', 'cust_initech', '2026-10-01', 12000, 12.0),
    ('ev_6', 'cust_initech', '2026-10-02', 15000, 15.0);

The relationship connects daily billing telemetry to the owning customer organization:

+--------------------+             +------------------+
|   billing_events   |             |     tenants      |
+--------------------+             +------------------+
| event_id (PK)      |             | tenant_id (PK)   |
| tenant_id (FK) ----+------------>| name             |
| event_date         |  many_to_1  | tier             |
| api_calls          |             +------------------+
| cost_usd           |
+--------------------+

The model

The semantic model pairs entity declarations with a published policy bundle.

The model defines Tenant and BillingEvent entities, their many-to-one relationship, and two business metrics: usage_calls and billed_cost. The policy bundle defines the customer_portal role. It grants access to both entities while attaching a strict row-level filter: tenant_id = current_user.tenant_id.

source: warehouse_main

entities:
  - name: Tenant
    table: tenants
    primary_key: tenant_id
    dimensions:
      - name: tenant_id
        column: tenant_id
        type: string
      - name: name
        column: name
        type: string
      - name: tier
        column: tier
        type: string

  - name: BillingEvent
    table: billing_events
    primary_key: event_id
    dimensions:
      - name: event_id
        column: event_id
        type: string
      - name: tenant_id
        column: tenant_id
        type: string
      - name: event_date
        column: event_date
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: total_calls
        expr: api_calls
        agg: sum
      - name: total_spend
        expr: cost_usd
        agg: sum

relationships:
  - name: billing_tenant
    from: BillingEvent
    to: Tenant
    cardinality: many_to_one
    keys:
      - [tenant_id, tenant_id]

metrics:
  - name: usage_calls
    expr: total_calls
    description: "Total billable API invocations"
  - name: billed_cost
    expr: total_spend
    description: "Total accrued usage charge in USD"
    unit: USD
    format: currency

Accompanying the model is the governed policy configuration:

roles:
  customer_portal:
    description: "External customer dashboard tenant role"
    grants:
      - kind: entity
        target: BillingEvent
        fields: ["*"]
        row_filter: "tenant_id = current_user.tenant_id"
      - kind: entity
        target: Tenant
        dimensions: [name, tier]
        row_filter: "tenant_id = current_user.tenant_id"
      - kind: metric
        target: "*"

Notice how the policy binds directly to current_user.tenant_id. The engine extracts this claim from verified JSON Web Tokens. The client cannot forge or widen access because the token is signed by your identity provider.

When a query requests dimensions or metrics from BillingEvent, the policy engine checks the active role. It finds the grant on BillingEvent and notes the row filter expression. During SQL compilation, the engine appends this predicate to the table scan node. If the query joins to Tenant, the policy engine attaches the row filter for Tenant as well. Both tables remain bounded by the verified caller claim.

Consult the model specification for syntax rules on entities and row security.

The queries

Portal backends send requests adhering to the query specification. Every incoming request presents an Authorization: Bearer <token> header verified by the gateway. Where a request sets include_sql, the engine returns compiled SQL only if it was started with ENGINE_ALLOW_SQL=true. MCP never returns SQL.

The gateway verifies the cryptographic signature before reading the payload. It extracts the caller’s role, user ID, and tenant claim. Then it passes the validated principal context down to the query compiler.

Query 1: Hero customer usage request for Acme Corp

The customer portal requests usage_calls and billed_cost by day for the signed-in tenant:

{
  "metrics": ["usage_calls", "billed_cost"],
  "dimensions": [
    {
      "name": "event_date",
      "entity": "BillingEvent",
      "grain": "day"
    }
  ],
  "order": [
    { "ref": "event_date", "desc": false }
  ],
  "include_sql": true
}

The gateway extracts the verified claim tenant_id: "cust_acme" and returns only Acme Corp rows:

event_date usage_calls billed_cost
2026-10-01T00:00:00Z 150000 150
2026-10-02T00:00:00Z 175000 175

When compiling the query, the engine injects parameterized SQL predicates enforcing tenant boundaries:

SELECT
  q0."event_date" AS "event_date",
  CAST(q0."total_calls" AS DOUBLE) AS "usage_calls",
  CAST(q0."total_spend" AS DOUBLE) AS "billed_cost"
FROM (
  SELECT
    date_trunc('day', "event_date") AS "event_date",
    CAST(SUM(api_calls) AS DOUBLE) AS "total_calls",
    CAST(SUM(cost_usd) AS DOUBLE) AS "total_spend"
  FROM "billing_events" AS "BillingEvent"
  WHERE "BillingEvent"."tenant_id" = ?
  GROUP BY date_trunc('day', "event_date")
) AS q0
ORDER BY "event_date"

The database query binds "cust_acme" as a parameterized value. Globex Corp and Initech records are excluded at the database engine level. The database engine never scans rows belonging to other tenants.

Query execution metering records each execution. The engine records tenant identity, query duration, and scanned bytes in an append-only audit log. Platform teams track tenant resource consumption without logging raw customer data.

Query 2: Refusal on unauthenticated request

If a client sends a query without presenting a verified JWT bearer token, the gateway halts execution before inspecting the model:

{
  "metrics": ["usage_calls"]
}

The engine returns HTTP 401 Unauthorized:

{
  "error": "authentication required",
  "code": "unauthenticated",
  "reason": "unauthenticated"
}

The request never reaches the query compiler or the database pool. Unauthenticated traffic cannot consume warehouse resources or inspect schema metadata.

Query 3: Refusal on internal ungranted metric

If an external portal caller attempts to probe uncertified internal metrics such as profitability numbers (internal_margin), the engine denies the query:

{
  "metrics": ["internal_margin"]
}

The server returns HTTP 404:

{
  "error": "model: resolve: metric \"internal_margin\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "internal_margin"
}

The engine refuses ungranted metrics at the compile layer. Internal metrics remain invisible to external users. The catalog prunes ungranted metrics completely from response schemas.

What to notice

  • Cryptographic tenant isolation. The client cannot pass a foreign tenant ID in the request payload to bypass access controls. Caller identity derives exclusively from cryptographic signature verification on the server.
  • Parameterized SQL injection protection. The engine binds claims as parameters (WHERE tenant_id = ?) rather than string-concatenating user input into SQL text. SQL injection vulnerabilities are eliminated.
  • Query execution metering. The engine logs caller identity, scanned bytes, and duration for audit tracking. Data platform teams observe query costs without logging sensitive customer values.
  • Dynamic catalog pruning. Calling POST /v1/catalog exposes only the entities and metrics granted to the caller’s active data role. Customers never see internal corporate measures.
  • Isolated Query Runner proxy. The Query Runner shields warehouse access. The engine compiles bounded SQL while the runner manages connection pools and credential isolation.

For foundational modeling techniques, see the guide on building your first metric in DuckDB and the overview of self-hosting regulated agents.

Limits

  • Hosted control plane boundaries. Multi-tenant control plane workspaces operate on the hosted platform. The self-hosted community binary runs single-tenant folder configurations with claim-based row security.
  • Warehouse partitioning alignment. High-volume multi-tenant tables benefit from clustering or partitioning on tenant_id to limit scanned bytes in the underlying database.
  • Rollup tenant scoping. Pre-aggregation rollups cannot cross tenant security boundaries unless the rollup explicitly stores the row filter dimension.
  • Token validation scope. The semantic layer enforces row filtering at query compile time. It cannot resolve compromised or improperly minted upstream JWT tokens from an identity provider.
  • Warehouse transformation scope. The semantic layer aggregates clean billing rows. Aggregating raw socket traffic or reconciling credit card payments remain upstream warehouse pipeline responsibilities.
  • Auditing retention pipeline. The semantic layer emits audit events for query metering. Storing these logs long term requires shipping them to an external log sink or object store.

Product notes

An occasional email when we ship something or write something worth reading.