Tiles, security and deployment

The chat is half the demo. The other half is what happens to an answer you keep.

A tile is a spec

Every tile in the dashboard stores a title, a chart kind and a spec. None of them store SQL:

{
  id: 'starter-volume',
  title: 'Contacts per day, last 28 days',
  chart: 'line',
  spec: {
    projections: [
      { field: 'Contact Date', decorators: [{ type: 'truncate', grain: 'day' }], order_by: 'asc' },
      { field: 'Contacts' },
    ],
    filters: [{ field: 'Contact Date', predicate: 'greater_than_or_equal_to', value: '28d' }],
  },
}

Loading the dashboard re-plans all of them:

const data = await Promise.all(
  tiles.map(async (tile) => {
    const planned = await planSql(tile.spec, user.context);
    const result = await runSql(planned.sql);
    return { tile, sql: planned.sql, datasource: planned.datasource, adapter: planned.adapter, result };
  }),
);

Planning is fast enough that re-planning a dashboard is free next to running the queries. What it buys:

  • The model can change underneath a saved tile. Rename a physical column, add a join, introduce a pre-aggregated table with a lower cost — the tile’s SQL changes on the next load, because the tile only ever said what it wanted. See cost optimization for the routing part.
  • A tile is portable. It is JSON with no dialect in it. The same tile works against DuckDB here and Snowflake in production.
  • A tile obeys today’s policies, not the policies of the day it was pinned. This is the one that matters: frozen SQL is a permission leak waiting to happen.
  • 28d is evaluated at plan time. A relative filter stays relative.

The same applies to the pin itself. The agent pins a query it has already run, by id, and what gets stored is the spec:

const tile = await addTile({
  title: title ?? query.title,
  chart: query.chart,
  spec: query.spec,
});

The user picker is the security model

The header has a picker. Each demo user stands in for a session, carrying an email and groups with region: tags:

{
  id: 'amer',
  label: 'Dana — AMER ops',
  context: {
    email: 'dana@example.com',
    groups: [{ name: 'CS Ops AMER', tags: ['region:AMER'] }],
  },
}

The policy that reads those tags is one file in the model:

policies:
  - name: Regional Cost Visibility
    mode: filter_data
    triggers:
      field_tags: [cost-sensitive]
    context_dimension: Call Center Region
    permission_resolution:
      source: groups
      value_from: tag
      tag_key: region
      unresolved: deny
    bypass:
      system_admin: true
      project_admin: true

The trigger is a tag on the measures themselves:

  - type: measure
    name: Total Contact Cost USD
    tags: [cost-sensitive]
    description: CSR1 + CSR2 + non-BPO + telecom cost.
    data_type: decimal
    expression:
      sql: sum(coalesce(csr1_cost_usd, 0) + coalesce(csr2_cost_usd, 0) + ...)

Ask total contact cost by call center as two different users and compare the planned SQL under the answer. Same spec, same model, two statements:

SELECT
	T1."call_center_desc" AS "Call Center",
	sum(coalesce(T0."csr1_cost_usd", 0) + coalesce(T0."csr2_cost_usd", 0) + coalesce(T0."non_bpo_cost_usd", 0) + coalesce(T0."telecom_cost_usd", 0)) AS "Total Contact Cost USD"
FROM
	cs_contact_f T0
	LEFT JOIN cs_call_center_d T1
		ON T0.call_center_id = T1.call_center_id
WHERE
	LOWER(T1."region") IN ('amer')
GROUP BY
	T1."call_center_desc"
ORDER BY
	... desc

One group, tagged region:AMER. The policy resolved one allowed value and the planner joined cs_call_center_d to filter on it.

WHERE
	LOWER(T1."region") IN ('amer', 'emea', 'apac')

Same statement, three allowed values. The group carries region:AMER, region:EMEA and region:APAC, and every tag whose key matches tag_key contributes a value.

WHERE
	1 = 0

A group with no region: tag resolves nothing. unresolved: deny turns that into 1 = 0 — the query runs, costs nothing to run, and returns no rows. The caller can see that restricted data exists without seeing any of it.

Three properties of that, none of which a prompt can provide:

  1. The agent cannot opt out. context is attached in the route handler, from the session, after the spec exists. There is no tool that takes a context and no field in the spec that holds one.
  2. A caller with no region sees nothing, not everything. unresolved: deny is the default, and the demo’s floor supervisor is in the picker to show it: the cost tiles come back empty rather than open. Switch the policy to unresolved: allow and the same user sees every region — which is the right behaviour for a policy meant to restrict only some callers, and the wrong default for one that is not.
  3. Queries with no cost measure are untouched. The policy triggers on a tag; contacts by call center plans the same statement for everyone.

Branches matter here. Policies are branch-scoped, so a staging deploy can carry a policy main does not — which is how you test one. See row-level security for the resolution table and the mask mode.

Tests run on every deploy

semantic/tests/*.yml holds planner assertions. They project a couple of fields and assert on the statement:

name: contacts by call center
projections:
  - Call Center
  - Contacts
assert_regex: count\(\*\).*cs_contact_f.*join cs_call_center_d

zsql deploy runs them on the server and prints the result — and deploy returns success even when tests fail, so CI should check them explicitly:

- name: Validate the model and run its tests
  # CI checkouts are detached, so pass the branch explicitly.
  run: zsql check --project semantic --branch "${{ github.head_ref }}"
  env:
    ZSQL_API_KEY: ${{ secrets.ZSQL_API_KEY }}   # a personal key; query keys cannot deploy
    ZSQL_SERVER: https://app.0sql.io

zsql check is deploy --dry-run: it validates the project and runs the tests without storing anything. See tests and CI/CD.

Swapping the warehouse

The demo runs DuckDB because it ships in the repo. Three things change to point it at a real one, and none of them are in the agent:

  1. adapter in semantic/datasources.yml — postgres, snowflake, bigquery, databricks, redshift, trino, athena, clickhouse, druid, mysql, sqlserver, sqlite.
  2. physical_name on the table files, if your tables are not called cs_contact_f and cs_call_center_d.
  3. src/lib/warehouse.ts, which is 40 lines around one runSql.
export async function runSql(sql: string): Promise<ResultSet> {
  const reader = await (await connection()).runAndReadAll(sql);
  const rows = reader.getRowObjectsJson() as Record<string, unknown>[];
  return {
    columns: reader.columnNames(),
    rows: rows.slice(0, ROW_CAP),
    truncated: rows.length > ROW_CAP,
  };
}

Redeploy and the planner emits the new dialect. The specs, the tiles and the prompt are unchanged. See adapters.

Before you put this in production

The demo takes four shortcuts on purpose. Each one is a comment in the file that takes it:

ShortcutWhat to do instead
Demo users in a pickeryour session; build the context server-side from the authenticated user’s groups
Conversations in a MapRedis or a table, keyed by session — see src/lib/conversations.ts
Tiles in a JSON filea table, scoped per user or per dashboard
A query key in .env.locala server-side secret; it must never reach the browser

Two more things worth adding, which the demo leaves out to stay readable: a row cap per chart appropriate to your warehouse (ROW_CAP is 500 here), and a per-user rate limit on /api/chat, since every question is a model call and a warehouse query.

What does not need hardening is the planning path. A spec is not SQL and cannot be turned into SQL that reads something the model does not describe: unknown keys are rejected, field references resolve against the deployed branch, and the security policies run on the server. The agent’s blast radius is the set of questions your semantic model can answer, which is exactly the point.

Next