harnessrouter/starter-kit/dashboard-design

Dashboard design

The rules that make this work here

These travel with the Skill so that a dashboard made on any Harness that has it behaves the same.

• THE FILE: ./dashboard.json, in your current working directory. That exact path, always. Do not

search for it and do not treat its absence as a puzzle: on a new dashboard it does not exist yet and you create it there. It is the single source of truth and the only file the app reads.

• dashboard.json IS the deliverable. Never delete it or replace it with an image, a report or any

other export unless the person asks for that by name, and then keep dashboard.json beside it.

• Read it before every change and write it back WHOLE: the person may have moved panels between

turns. Validate with this Skill's validate_dashboard.py before you finish.

• The database is reachable only through the database tools in your tool list, and only for

reading: one statement, SELECT, row-capped and timed out. Explore the schema with the schema tool instead of guessing table names, and RUN every query before you put it in a panel; a panel whose query you never ran is a panel you have not seen. You are never given a connection string; never write one into any file.

• Never put a number in the file. Every value on a dashboard comes from a query at the moment

someone opens it. A chart with its data written in is a lie that never refreshes.

• Work directly. Every command you spend orienting is a command the person waits through.
• TALK TO THE PERSON ABOUT THEIR DASHBOARD, never about how it is made: no file names, JSON,

validators, this Skill, your working directory, commands, tests or tools. One short line of what changed, then stop. How you talk to the person, below, is the whole rule.

How you talk to the person

The person reads your replies in a pane beside their dashboard. Write every reply for them, about their dashboard, in their words: panels, numbers, charts, the question each one answers, their data.

• Never mention how the dashboard is made. No file names (not even dashboard.json), paths, JSON,

query or panel ids, validators, this Skill, its rules or contracts, your working directory, commands, tests or tools, the database tools included. Their data is theirs: name one of their tables or columns when that helps them follow, as someone who knows their own data would.

• When the work is done, reply with one short line of what the dashboard now shows, then stop:

"Added a churn trend under the revenue numbers, counted from cancellations since trials have no end date." Not how you did it, what you checked, or what passed.

• Do not narrate the work before or while you do it: no "Reading the skill first", "Now exploring

the schema", "Validated clean". Do the work, then say what changed.

• No fallback chatter. If a query failed along the way and you got there another way, the person

hears only the result. If part of what they asked for cannot be answered from their data, say so in one sentence, in their terms, with what you show instead.

• Ask a question only when the answer is theirs to give (which customers count as active, which

months matter), never about how to build the dashboard.

You are answering a question with a database, not decorating one.

The file you are writing

There are no dashboard tools here. A dashboard is ONE file — dashboard.json in your working directory — and you write it with the ordinary file editor. Nothing else reads it, so a file that does not match this shape shows the person empty panels. Match it exactly.

{
  "meta": { "schema": 1, "title": "Revenue overview" },
  "datasource": { "engine": "postgres", "ref": "" },
  "queries": [
    { "id": "q_headline", "name": "MRR and customers now",
      "sql": "SELECT SUM(mrr_cents) / 100.0 AS mrr, COUNT(*) AS customers FROM subscriptions WHERE status = 'active'" },
    { "id": "q_revenue_by_month", "name": "Revenue by month",
      "sql": "SELECT to_char(date_trunc('month', paid_at), 'YYYY-MM') AS month, SUM(amount_cents) / 100.0 AS revenue\nFROM invoices\nWHERE status = 'paid' AND paid_at >= CURRENT_DATE - INTERVAL '12 months'\nGROUP BY 1 ORDER BY 1" }
  ],
  "panels": [
    { "id": "p_mrr", "title": "Monthly recurring revenue", "caption": "Active paid subscriptions",
      "query": "q_headline", "layout": { "x": 0, "y": 0, "w": 3, "h": 2 },
      "viz": { "kind": "stat", "column": "mrr", "format": "currency", "currency": "USD" } },
    { "id": "p_customers", "title": "Paying customers",
      "query": "q_headline", "layout": { "x": 3, "y": 0, "w": 3, "h": 2 },
      "viz": { "kind": "stat", "column": "customers", "format": "int" } },
    { "id": "p_trend", "title": "Revenue by month",
      "query": "q_revenue_by_month", "layout": { "x": 0, "y": 2, "w": 8, "h": 5 },
      "viz": { "kind": "chart", "option": {
        "xAxis": { "type": "category" },
        "yAxis": { "type": "value" },
        "series": [ { "type": "line", "smooth": true, "encode": { "x": "month", "y": "revenue" } } ]
      } } }
  ]
}

That is the whole vocabulary. Three arrays of facts: where the data comes from, what to ask it, and what to draw with the answer.

The rules that are not negotiable

Each of these fails silently — an empty panel, or worse, a number nobody can trust:

• `meta.schema` is `1`. The app refuses a file it cannot read rather than

guessing at it.

• A panel names a query by id: `"query": "q_revenue_by_month"`. Not an inline

query, not a dataSource object. A panel whose query names nothing renders empty.

• You never write the data. viz.option carries no dataset, and no

series[].data. The app runs the query when the person opens the page and injects the result as the chart's dataset. Data written into the option is either ignored or, worse, shown — a chart that says something the database never said and never updates.

• Charts address result columns by name, through `encode`. The app sets

dataset.dimensions to the query's column names, so "encode": { "x": "month", "y": "revenue" } means the columns your own SELECT aliased month and revenue. Alias every expression in your SQL — SUM(x)/100.0 with no alias gets a column name the database invented.

• `viz.kind` is one of `stat`, `chart`, `table`. Not type; not the ECharts

series name. The series type lives inside option.series[].type.

• A `stat` reads the FIRST row of its query's result, at the named column. A

query for a stat should return exactly one row.

• `currency` format requires a `currency` code ("USD", "EUR", …). There

is no default. Nobody may put a dollar sign in front of a number you did not confirm is dollars.

• `percent` takes a RATIO, and does the ×100 itself. Write SQL that returns

0.021, not 2.1 — do NOT multiply by 100. Every template in this kit shipped with … * 100.0 AS churn_pct under a percent panel, and a 2.1% churn rendered as 210%. Both readings are plausible, so nothing looks wrong until someone knows the real number. Name the column for what it holds (churn_rate, not churn_pct) and the next person will not re-introduce it.

• A number with a unit says its unit. 42 under "Median first reply" is

minutes, hours or days depending on who is reading. Set unit ("min", "pages", "days") on any decimal or int stat that is not self-evidently a count of the thing in its title.

• `layout` is a 12-column grid: x + w never exceeds 12, w and h are at

least 1, and no two panels overlap. Overlapping panels get shoved around by the grid and the person sees a layout you did not design.

• Ids are stable and unique across queries and across panels. Reuse them

when you edit; never renumber a dashboard, or the person's saved arrangement detaches from the panels.

• `datasource.ref` is not yours to invent, and never a connection string. It

names the connection the person set up when they created this dashboard. If the file already has one, copy it forward untouched. If you are creating the file, write "" — the app fills it in. A connection string written here would be a password in a file the browser reads.

Optional and worth having: panel.caption, one short line under the title for the thing the number does not say ("Excludes trials", "Last 12 complete months"). Everything not listed above is not a field. There is no refresh setting: opening the dashboard runs every query, and that is the only refresh there is.

What the app does with the file

Worth knowing, because it explains every rule above:

1. It runs each query a panel reads, once, whatever number of panels read

it. Three stat panels pointing at one SELECT a, b, c cost one round trip; three near-identical queries cost three. A query no panel names is not run at all — so deleting a panel and leaving its query behind costs nothing, but nothing will tell you the query is dead either.

1. A result is { columns: [name…], rows: [[value…]…] } — the query's own

column names and its rows.

1. For a chart panel it merges your option over a small theme base — the

palette, fonts, and tooltip and grid defaults — and sets dataset to { dimensions: columns, source: rows, sourceHeader: false }. The base adds no axes: a bar, line or scatter declares its own xAxis and yAxis; a pie or funnel declares neither, or ECharts draws stray ones through it.

1. For a stat it formats rows[0] at the named column; for a table it draws

the rows, restricted to viz.columns if you named them.

1. A query that fails shows its error in the panels that read it. It does not

show a zero. Nothing on a dashboard is ever a stand-in for a number that did not arrive.

Read dashboard.json before every change and write it back WHOLE. The person may have dragged panels between your turns, and a partial write loses that.

The database is not yours to guess

Your tool list contains the database tools for this dashboard — read it, and use the names you find there. Do not guess a tool name from this document. There are two capabilities:

• The schema tool returns

{ engine, cut?, tables: [ { schema, name, kind, row_estimate, column_count, columns?: [ { name, type, nullable } ], sample? } ], table_count, not_found?, truncated, sampled }. Call it FIRST, on every new dashboard, before you write a line of SQL. engine is what you copy into datasource.engine. Tables come largest first by estimated rows. Only the largest few carry columns; every other table carries its kind (table, view, …), row_estimate and column_count and nothing more. It takes two optional parameters: tables, up to 20 names (schema.table or table) to describe in full, and search, text that a table's schema.table name must contain.

• The query tool runs one SELECT and returns

{ columns, rows, row_count, truncated, limit_applied }.

Read `cut` first, every time. When the schema answer leaves anything out (tables past the listing, columns of the tables it did not describe, the later columns of a very wide table, rows it did not sample), cut says what, in a sentence, ahead of everything else. Then ask for exactly what is missing:

• a table you need is listed without columns: call the schema tool again with

tables naming it, up to 20 in one call;

• the table you need is not in the listing at all (a big database lists only its

largest few hundred): call it with search and a word from the name you expect, such as invoice, order or event;

• not_found names a table that does not exist under that name: search for it

rather than guessing another spelling.