Chat dashboard tutorial

A dashboard you talk to. Someone asks “which call centers have the longest average handle time?”, a model writes a query spec, 0sql plans one SQL statement from it, the app runs that statement against its own warehouse and draws the answer. “Pin that” turns the answer into a dashboard tile. Claude drives it by default; OpenAI works too, because the spec is the contract and not the provider.

The whole thing is a public repo you can clone and run in about five minutes:

github.com/stratasite/0sql-dashboard-demo

It ships three things: a Next.js app, a complete 0sql project (semantic/), and the warehouse it reads, so nothing has to be connected before it works.

What it is a model of

The warehouse is the customer service side of a subscription SaaS business, which is where questions about contact volume stop being simple. A contact is not a row on its own: it belongs to a member, who holds a subscription on a plan, who may have been in a free trial that week, who tried the help center before they called, who is enrolled in two experiments, and who was handled by an agent reporting to a supervisor reporting to a manager — any of whom may have changed team since.

So the model is not a star with one fact in the middle. It is 26 tables and 532 fields across six subject areas that share conformed dimensions:

The data is generated — there is no real customer, agent or account in it — but the shape is the point. A toy schema would not exercise what a semantic layer is for: Memberships, Free Trial Memberships and Paid Memberships live on a different fact from Contacts at a different grain, Recontact Rate is a compound measure over two of them, and Escalation Rate needs the transfer type for its denominator. Asking “did the experiment reduce contacts per paid member” is one spec here, and the planner’s problem rather than the agent’s.

What it is not

It is not text-to-SQL. The model never writes SQL, never sees a connection string, and never chooses a join.

flowchart LR
    Q["question"] --> C["the model"]
    C --> S["query spec (JSON)"]
    S --> Z["0sql: POST /sql"]
    Z --> L["SQL + datasource + adapter"]
    L --> W["your warehouse"]
    W --> R["rows"]
    R --> V["chart or tile"]
    R --> C
    Z -. "422: what is wrong with the spec" .-> C

What the model produces is a spec: fields to project, filters, decorators, order. The planner turns that into SQL using the deployed model — it picks the tables, derives the join route, keeps the grain safe across facts, writes the dialect, and compiles row-level security into the statement. Three consequences, and they are the reason to build an agent this way:

• A wrong field is an error, not a wrong number. A spec naming a field that does not exist comes back 422 FieldNotFound. The agent reads the message and corrects itself. A hallucinated column in generated SQL, by contrast, either errors in the warehouse or quietly returns something plausible.
• Grain is not the model’s problem. Two measures from two fact tables in one request produce one aggregation per fact, stitched on the conformed key. No fan-out, no double counting, nothing for the model to get right. See cross-fact blend.
• Security is not in the prompt. The caller’s context is attached on the server, after the spec exists, and the policy lives in the model. No instruction to the model can widen what the SQL may return.

What you need

Run it

git clone https://github.com/stratasite/0sql-dashboard-demo
cd 0sql-dashboard-demo
npm install
npm run seed

npm run seed builds warehouse/cs_warehouse.duckdb from the Parquet files in the repo and shifts every contact date forward so the last one is today — relative filters like 28d keep answering with rows however long after release you clone it.

Then deploy the semantic model to your own account:

zsql auth --project semantic --api-key zsk_… --server https://app.0sql.io
npm run deploy:model

deploy Customer Service Analytics (4655 bytes) as customer-service/main (the production branch) to https://app.0sql.io
deployed customer-service/main: 26 tables, 532 fields, 32 joins, 3091 paths, 1 policies
PASSED average handle time by day
PASSED handling and escalating sites join separately
PASSED contacts by call center
PASSED cost per contact is one pass over the fact
PASSED escalation rate joins transfer type for its denominator
PASSED escalating call center joins on its own key
tests: 6 passed, 0 failed

The project uid is customer-service, so every request the app makes goes to POST https://app.0sql.io/projects/customer-service/branches/main/sql. Finally, the app’s own keys:

cp .env.example .env.local   # ZSQL_QUERY_KEY, and a model key
npm run dev                  # http://localhost:3000

The model it queries

semantic/ is an ordinary 0sql project, the kind you would keep in your own repo:

semantic/
├── project.yml              name, uid, production branch
├── datasources.yml          one duckdb datasource — adapter only
├── security.yml             one row-level security policy
├── models/
│   ├── tbl.contact.yml      the contact fact, and 25 more table files
│   ├── tbl.subscription.yml accounts, subscriptions, membership days
│   ├── tbl.agent.yml        agents, and the same table as supervisor,
│   │                        manager, current supervisor, current manager
│   ├── rel.contact.yml      the contact star
│   ├── rel.agent.yml        the reporting line
│   ├── rel.memberships.yml  contacts to the members behind them
│   ├── rel.cs_facts.yml     tickets, recontacts, help center sessions
│   └── rel.ab.yml           experiment allocations
└── tests/                   six planner assertions, run on every deploy

532 fields, 106 of them measures, over 32 joins the planner turns into 3,091 routes. A sample of what that buys, by subject area:

Six of those 26 tables are the same physical table in a second role — cs_call_center_d as both the handling and the escalating site, cs_agent_d four times over as agent, supervisor, manager and current manager, geo_country_d as the contact’s country and the member’s signup country. Each is declared once as its own table file, so “which site escalated to which” or “contacts by the supervisor’s manager” is one spec with two dimensions in it, not a self-join anyone writes by hand. That is a role-playing dimension, and the pattern is why the file count is higher than the table count in the warehouse.

datasource: cs_duckdb

contact_to_call_center:
  left: Contact
  right: Call Center
  sql: "left.call_center_id = right.call_center_id"
  cardinality: many_to_one
  join: left

# Second FK to the same physical table -> role-playing dimension.
# Left join so non-escalated contacts (escalating_call_center_id is null) are kept.
contact_to_escalating_call_center:
  left: Contact
  right: Escalating Call Center
  sql: "left.escalating_call_center_id = right.call_center_id"
  cardinality: many_to_one
  join: left

Those two lines are the entire configuration for that pair, and 32 declarations like them produce 3,091 routes. Nobody enumerates the routes; the planner derives every one of them from the cardinalities.

Try these

• Which call centers have the longest average handle time?
• Contacts per week over the last three months
• Top 5 ticket dispositions by volume, and pin it to the dashboard
• How does total contact cost split by BPO vendor?
• Contacts per paid membership by month — two facts at different grains, stitched on the conformed date
• Escalation rate by region for members in their free trial
• Do members who used the help center first recontact less often?
• Drop the resolution mix tile

The last three are the ones worth watching the SQL for. Each spans facts that do not share a grain, and none of the joining is in the spec.

Under every answer there are two disclosures, spec and planned sql. The spec is what the model wrote. The SQL is what the planner made of it, for this model and this caller. Reading the pair is the fastest way to learn what a semantic layer actually does.

Next

• The agent loop: the five tools, the schema the model is given, the turn in full, and what happens when a spec is wrong.
• Tiles, security and deployment: why a tile stores a spec instead of SQL, how the user picker changes the SQL, and what to change before this goes to production.