BI node (`/bi`)

A BI node is a third kind of skaidb node, next to a cluster member and a witness. It mirrors the databases you report on and serves the business-intelligence app at /bi, so dashboard queries never land on a member. Every other node redirects /bi to it, which means you can hand out https://<any node>/bi and change your mind later about which machine runs the app.

A BI node serves: a catalog tree of what it mirrors, a tabbed SQL (and PromQL) editor whose drafts and saved queries live on the cluster, a typed result grid with CSV export, charts over any result, dashboards built from saved queries, and a Data page showing how old each mirrored table is.

Why a separate node

Analytical reads are the heaviest shape the engine runs: wide GROUP BY, window functions and DISTINCT ON materialise the whole filtered set, and a join pulls both sides to the coordinator. A long read holds the engine lock, and the lock prefers writers, so one dashboard refresh can stall every writer on the node that serves it. Putting that on a node of its own turns a dashboard storm into a problem for a machine that serves nothing else.

The mirror is a witness, so nothing about replication is new: the same pull loop, the same table selection, the same watermarks. What the BI node adds is an app and an address.

The three shapes

Backend	`[witness] enabled`	`[bi] enabled`	Meaning
standalone (`cluster.seeds = []`)	true	true	a BI node — mirrors the cluster, serves `/bi`
standalone	false	true	BI over the node's own data (single-node deployments, dev)
cluster member	—	true	refused at start-up

A member refuses because serving analytics there defeats the point. The error names the fix.

Configure one

A BI node is an ordinary install (deb, rpm or the Docker image) with two sections added to /etc/skaidb/skaidb.toml:

[server]
read_only = true                 # required: the mirror must not diverge

[witness]
enabled = true
primary_sql_addrs       = ["192.168.7.3:7000", "192.168.7.4:7000"]
primary_internode_addrs = ["192.168.7.3:7100", "192.168.7.4:7100"]
user     = "bi_witness"
password = "..."
databases = ["sales", "ops"]     # what the reports read — or ["*"]: every user
                                 # database, new ones mirrored on the next cycle
witness_id = "bi1"
region     = "eu"

[bi]
enabled = true
advertise_url = "https://bi.example.net:7443/bi"

The role on the primary needs the ordinary witness grants, plus the four on _bi, where the app keeps saved queries and drafts:

CREATE USER bi_witness PASSWORD '...';
GRANT SELECT ON witnesses TO bi_witness;
GRANT INSERT ON witnesses TO bi_witness;
GRANT UPDATE ON witnesses TO bi_witness;   -- one privilege per statement
GRANT SELECT ON DATABASE _bi TO bi_witness;
GRANT INSERT ON DATABASE _bi TO bi_witness;
GRANT UPDATE ON DATABASE _bi TO bi_witness;
GRANT DELETE ON DATABASE _bi TO bi_witness;

No CREATE grant is needed, and that is deliberate: CREATE DATABASE requires CREATE ON GLOBAL, which would let a read-only mirror create tables anywhere in the cluster. Every primary creates _bi at start-up instead, alongside the witness registry it already creates, so the BI node only ever reads and writes rows in it.

Who may use it

Using the BI app is its own privilege:

GRANT BI ON * TO analyst;

It is deliberately not a side-effect of SELECT. A role that may read a table through a driver is not automatically someone who should have an analytics console, a saved-query library and a dashboard on a shared node — those are separate decisions, so they are separate grants. What a role can then read is unchanged: its own SELECT grants, table by table. This privilege opens the door; it does not widen the room.

ADMIN ON * implies it, as it implies everything. A role without it gets 403 naming the exact statement that fixes it, and the app shows that message rather than "sign-in failed" — the password was right, and sending someone to reset it wastes an afternoon.

Accounts on the BI node

BI users are local accounts on the BI node. It is a standalone node with its own [auth], so its users are its own; nothing about them is replicated from the cluster, and a BI login is not a cluster login.

-- on the BI node, as its superuser
CREATE USER analyst PASSWORD '<generated>';
GRANT BI ON * TO analyst;
GRANT SELECT ON DATABASE onet TO analyst;   -- what they may read

Kerberos works here too. A BI node accepts SPNEGO exactly as any REST endpoint does, so an analyst with a ticket signs in without a password:

-- on the BI node, as its superuser
CREATE USER "[email protected]" GSSAPI;      -- quoted: a principal is not a bare identifier
GRANT BI ON * TO "[email protected]";
GRANT SELECT ON DATABASE onet TO "[email protected]";

The app asks the node whether Kerberos is available (/bi/api/meta reports sso) and, if it is, probes once without a credential so the browser can answer the Negotiate challenge. Signed in that way, the badge shows the principal, and Sign out means "do not sign me in that way again in this tab" — clearing a header cannot end a Kerberos session, the browser simply negotiates again.

Four preconditions, none of them code you write:

the node runs the amd64 glibc package (Kerberos is compiled in there; the static musl build can never do SPNEGO),
auth.gssapi_enabled = true with a readable auth.gssapi_keytab,
the keytab holds an HTTP/<host>@REALM principal — browsers ask for the HTTP service name, not skaidb/<host> (see KERBEROS.md),
the browser trusts the host (Chrome AuthServerAllowlist, Firefox network.negotiate-auth.trusted-uris), and that host is the one bi.advertise_url points at.

Principals map to roles exactly — [email protected] is the role [email protected], case and realm included. There is no auth_to_local rewriting, so the CREATE USER has to match what the KDC issues.

Two things follow from local accounts, and both are load-bearing:

Grant only what the reports need. The BI node mirrors whole databases, and witness table selection is not access control — what the node holds is what a SELECT-on-database grant can read. This cuts both ways when choosing what to mirror: a database holding OAuth tokens or API keys puts those bytes on the BI node, where one broad grant exposes them. Mirror such a database by explicit table list (witness.tables), or not at all.
A departing analyst must be removed here too. There is no central directory behind these accounts yet; DROP USER on the BI node is what ends their access to it.

The node is server.read_only, which exempts the superuser — so CREATE USER and GRANT work there as the superuser and nowhere else.

`[bi]` keys

key	default	meaning
`enabled`	`false`	serve `/bi` here and advertise this node. Live-mutable (`config set bi.enabled …`).
`advertise_url`	`""`	the absolute URL browsers reach this node's `/bi` at. Required when enabled.
`max_concurrent_queries`	`4`	statements at once; past it, `429` after a short queue. Live-mutable.
`max_result_rows`	`100000`	rows one response may carry; shown on Run, and a truncated result says so. Live-mutable.
`usage_days`	`30`	how far Insights looks back, and how long local run history is kept. Live-mutable.
`scan_row_budget` / `scan_byte_budget`	`0`	per-statement ceilings; `0` = the node's own budgets.

A BI statement's time ceiling is the node's own storage.statement_timeout_secs, not a [bi] key. On a dedicated BI node that is the same thing — nothing else runs there — so a second knob would only be a second place to look. (There was a bi.query_timeout_secs for one day; it was never wired to anything, and a documented knob that does nothing is worse than no knob.)

advertise_url cannot be derived. The REST gateway never learns its own advertised name — its only other redirect rebuilds the URL from the request's Host header — and a target that browsers must reach is not something to guess. Point it at whatever your users actually type, including a reverse proxy in front of the node.

Where saved work lives

Saved queries, editor drafts and (next) dashboards are kept in a _bi database on the cluster, not on the BI node. They are the only thing a BI node holds that nobody can regenerate, so they get what any cluster table gets: replicated writes, point-in-time recovery, BACKUP CLUSTER, anti-entropy. Lose the BI machine and deploy another: every saved query is back on its first pull, and a second BI node in another region sees the same ones.

The mechanics follow from that:

The BI node stays server.read_only. Metadata writes travel over the same SQL control connection the witness already holds to a primary, so the mirror can never diverge from what it mirrors.
Metadata reads go to the primary too, which buys read-your-writes: a query you just saved is in the list on the next request rather than after the next pull cycle.
_bi does not appear in SHOW DATABASES for a role without ADMIN: it is server-owned bookkeeping, and an analyst has no use for its tables. This is tidiness, not a boundary — RBAC governs access to it either way, and an admin still sees it listed.
_bi is created by the primaries, not by the BI node, and mirrored like any other database; that copy is the fallback. A cluster that does not have one yet costs the mirror nothing — the pull skips it and keeps going with the databases the operator actually asked for. When no primary answers, the app serves saved work from the mirror, says so, and refuses to save rather than writing where the next cycle would overwrite it.
Run history is the exception: it is telemetry, high-churn and worthless after a rebuild, so it stays local to the BI node, in its own _bi.runs. It records what ran, who ran it, how long it took and which tables it read; it is swept to bi.usage_days, and it is what Insights is computed from. It is written as the superuser, which is the one role server.read_only exempts — the same path node_stats and the driver registry take.

Visibility is per row. You always see your own; you see other people's when they marked them shared, read-only; ADMIN ON * sees everything. Drafts are never shared with anyone, admins included — an open editor tab is a thought in progress, not a document.

Every saved query records the tables its statement reads, parsed when it is saved. That is the usage half of lineage, and it is what will answer "what breaks if I drop this column".

How `/bi` finds the node

A BI node writes roles and bi_url into its own row in the witnesses registry on every heartbeat. That is the only place a primary can learn a witness's HTTP address: the registry is pull-only by design, and nothing else on a member knows one.

Every other node then answers /bi:

A member reads the registry rows it already caches for /status, so the redirect costs no query.
A witness uses the list its own pull cycle caches from the primary (it has no local copy of default to read).
A standalone primary reads its own registry, briefly cached. A negative answer is never cached, so a BI node you just deployed shows up as soon as it beats.

The response is 302 Found with the path and query preserved, so https://member/bi/d/sales?range=7d lands on that dashboard on the BI node: /bi/d/<slug> is a dashboard's shareable address, and opening a board puts it in the URL bar. Slugs are not unique — two boards may share a name — so a link resolves to the newest match, and a slug that no longer exists says so rather than showing an empty board. It is a 302 rather than a permanent redirect because which node serves the app is a current choice: a browser that cached a permanent one would keep going to a decommissioned node.

Selection rules:

?bi=<witness_id> pins a node, so a shared link can name the one it was built against. A pinned node that has been quiet for a day falls through to whatever can actually answer — the row is evidence it was retired — while a pin naming a node the registry does not list at all gets the "no BI node" page rather than somebody else's dashboards, because substituting a different node on a name we know nothing about would be silent and wrong.
?region=<name> prefers a BI node there; without it, the region of the node serving the request is preferred, so a browser that reached a London member is sent to the London BI node. A preference is not a filter: when that region has nothing live, the request is still answered from elsewhere.
A member states its region in its identity row rather than in config — it has no [witness] block — with UPDATE node_aliases SET region = 'eu-west' WHERE node_id = '<internode id>'. A witness, BI node or standalone uses [witness] region.
Otherwise the most recently seen node wins.
A node that has not heartbeat for three beats — at least 5 minutes, and longer if witness.interval_secs is above 100 s — stops receiving traffic.
A node never redirects to itself, so a mistyped advertise_url pointing back at a non-BI node shows the "no BI node" page instead of bouncing.

When nothing is registered, /bi answers 200 with a page explaining what a BI node is and the exact config to deploy one. API clients that ask for JSON (Accept: application/json) get 404 {"error": "no bi node deployed"} instead, which is a state they can branch on.

Using it

Open /bi on any node — you land on the BI node either way.

Explore is the working surface: filter the tree, click a table to see its first 100 rows, run with the button or ⌘/Ctrl+Enter. A click is one thing — it shows you the table. The preview lands in a tab named after it, and previewing another table reuses that tab rather than filing a new one; a tab you have typed into is never overwritten. To put a table's name in a statement you are writing, type it and let auto-complete finish it. The « button at the top of the tree folds the whole pane away to a rail and back, and this browser remembers which way you left it. The pane keeps its width whatever the result holds: a row whose cell carries four kilobytes of JSON scrolls inside the grid, it does not push the sidebar off the screen. The Run button carries the row cap (bi.max_result_rows); a result that hits it says truncated rather than quietly clipping. Beside the timing sits data as of: the last successful pull cycle, which is the age of everything this node holds, because a cycle pulls every selected table. Before the first cycle completes it is blank rather than "now".
Expand gives the results the whole pane: the tab strip, the editor and the builder fold away, the toolbar stays — Run and the database are still what you want while reading a result — and Escape or the button brings them back. This browser remembers which way you left it.
Tabs are drafts. Every open tab is stored under your name in _bi, so the statements you left unfinished are still there tomorrow, on another machine, or on a BI node rebuilt from scratch. A dot marks a tab whose text is not saved as a query; closing a tab discards its draft.
Save (⌘/Ctrl+S) turns the current tab into a saved query with a name, optional tags and an optional shared flag. Only reads can be saved, and the refusal says so at save time rather than at run time, because a saved query is something a dashboard will run later when nobody is watching. The Saved panel lists them; clicking one opens it in a new tab and runs it, and someone else's shared query opens as a copy so you cannot edit theirs by accident.
PromQL is the second language in the editor, for the mirrored time-series tables. Pick a table, a range and a step, and write an expression: rate(cpu[5m]), sum by (host) (mem_used_bytes). The result comes back as a timestamp column and one column per series, so it charts, exports and becomes a dashboard tile exactly like a SQL result — a metrics tile is a tile. PromQL has no FROM, so the table you pick is the scope, and it is also what the query counts as reading: a metrics tile shows up in that table's lineage and Insights like any other query.
Sheet turns the grid into a spreadsheet: columns get letters (A, B, C) above their names, the cell you are on shows its address, and a formula bar appears. A formula that reads a RANGE is one answer about the whole result and is shown in the bar — =SUM(C1:C50), =ROUND(AVG(D1:D200),2). A formula that reads the row it is on becomes a COLUMN, filled down, and joins the grid as one: sortable, filterable, totalled, copied, and removable with the × on its header. Write =B*C, =ROUND(D/100,2), =IF(C>50,'high','low'), =[unit price] * [quantity] — a bare letter is that column of the current row, C7 is row 7 of column C, A2:B9 is a rectangle, and a name in brackets is a column whose name has spaces. Click a cell in a formula column and the bar shows the formula behind it.

About 30 functions: SUM, AVG, MIN, MAX, COUNT, COUNTA, COUNTIF, SUMIF, ROUND, FLOOR, CEIL, ABS, SQRT, POWER, MOD, INT, SIGN, IF, AND, OR, NOT, LEN, LOWER, UPPER, TRIM, LEFT, RIGHT, MID, CONCAT, COALESCE, ISBLANK, NUMBER and TEXT, with + - * / % ^ &, comparisons and and/or. The formula language is parsed and evaluated by the app itself — never eval, which the gateway's content-security policy refuses anyway, and which is the last thing anyone should point at a typed-in expression. Formulas work on the rows on screen; to compute over the whole table, put the expression in the statement, where the engine can push it down. - The grid is a sheet. Click a cell and drag, or shift-click, to select a block; the strip says what it adds up to — 4×3 selected · Σ 1,204 · x̄ 100 · min 3 · max 480 — and ⌘/Ctrl+C copies just that block as tab-separated text. Arrow keys move the selection, shift-arrows extend it, Escape lets it go. Columns are divided by a rule and the row numbers sit in a gutter of their own, so a header never reads as part of the column beside it; a numeric column's header sits over its own values. Drag the rule at a column's right edge to resize it — the grip straddles the rule, is twelve pixels wide, and holds the pointer for the whole drag — or double-click it to fit that column to its widest value. Freeze keeps the first column in view while you scroll sideways. Above it: a find box across every column, a filter box under each column name (a substring, or a comparison like >100, <=0, =north), totals for the rows shown (sum and average where a column is numeric, a range where it is time, distinct counts otherwise), a copy button that puts exactly what is on screen on the clipboard as tab-separated text, and Reset. Clicking a column sorts by it; shift-clicking adds a second key, and a third, each numbered in the header. The count says what it is showing — "9 of 60 rows" — and says when the statement itself returned more than the grid holds. All of it works on the rows in hand and none of it changes the statement, so what you filtered is always what you can see, copy and total. To narrow the QUERY rather than the view, double-click a cell: that derives a statement on the server. - Chart turns the current result into a picture: line, area, bar (grouped or stacked), row, bar-plus-line on two axes, scatter, box plot, pie, donut, treemap, funnel, waterfall, sankey, map, a single number with its change against the previous row, a progress bar and a gauge against a target. The app picks a first chart from the shape of the result — a time column and a measure is a line, a label and a measure is a bar, many categories is a row chart — and you change it from there. Types that read an extra option show it and the others hide it: Size for a scatter's third dimension, Target for a gauge or progress bar, Slices for the types that fold a long tail into an explicit "other". The chart belongs to the tab, so switching tabs does not lose it.

A time axis labels what it can fit and no more, and it recognises a timestamp stored as a plain epoch-millisecond integer as one — a column that came from a document store used to be labelled with thirteen-digit numbers: the labels are thinned against the MEASURED width of the axis font, the coarse unit is printed where it changes rather than on every tick — "Apr 23 2026" then "May 19", "Jun 1" — the resolution follows the span from seconds up to years, and the last tick is always drawn. A seven-month range used to print twenty-three overlapping 04-23 20:00s with the year nowhere.

The map has no basemap: every external host is blocked by the app's content-security policy, so points are plotted on a graticule rather than over tiles fetched from somewhere else. - Auto-complete offers tables, columns, functions and snippets as you type, from the catalog you can read — the same filter the tree uses, because a completion list that named a table you cannot select from would answer "does this exist?" for anyone who can type. A declared FOREIGN KEY becomes a JOIN … ON … suggestion, so the join the schema already describes is one the editor writes for you; a join into a parent you may not read is not offered, for the same reason the tree hides it. Arrows move, Tab or Enter accepts, Escape dismisses. - Object pages answer "what is this, and who depends on it". The ⓘ beside a table in the tree opens one: what it holds and how big it is, twenty sample rows, its indexes and constraints, its mirror freshness, and its lineage. Upstream is what feeds it (a view's base tables, a rollup's source, the parents its foreign keys point at). Downstream is what reads it (views and rollups built on it, its child tables, and the saved queries and dashboards that use it). Catalog edges cover views, rollups, foreign keys, indexes, streams, partition children, and the mirror edge that records a table as pulled from the cluster. They are exact. Usage edges come from the tables each saved query was parsed to read, so an ad-hoc statement nobody saved is not in the graph, and the page says so rather than implying the list is complete. Graph switches from the two lists to the neighbourhood as a picture — a layered DAG, one to four hops out, catalog edges solid and usage edges dashed, click a node to open it. It is drawn as SVG because the content-security policy drops a style attribute: an HTML-positioned graph would render as a pile of cards in the corner, silently. The page also carries a The Indexes and Constraints panes describe the CLUSTER's schema: a witness mirrors views, foreign keys and check constraints along with the data. Secondary indexes are mirrored only where the operator asked for it (witness.mirror_indexes), and the pane says which half it can speak for: with the catalog swept but index replay off, "no constraints" is a real answer while the index list is explicitly not one. Until a node has swept at all, neither is. An empty list and "this node cannot see them" are otherwise the same picture and opposite facts, and the second read as the first is how somebody concludes a column is unindexed when it is not. The object page also carries a description and tags anyone who can read the object may write (the engine has no DDL for a comment on a table, so this is an overlay in _bi, and the row records who wrote it; clearing it removes the row, so "never described" and "deliberately blanked" are not two states). Tags become facets over the catalog tree: a chip per tag, and selecting more than one narrows, so a table has to carry all of them. The number on a chip is what clicking it would leave — it counts the intersection with the facets already on and respects the text filter, because a count that promised more than the click delivered would be the reason nobody trusted the next one. The Schema tab also answers "What breaks?" per field: which saved queries read that column, kept in four groups because they carry different certainty — naming it, selecting every column, reaching it through a view or rollup (which may not carry it through at all), and a bare column name with several tables in scope. The answer states its own blind spots every time, including when it finds nothing: it covers saved queries the asker can see, so ad-hoc statements, other people's private work and a read reached only through a subquery are all outside it. The object page also has an Access tab listing the roles whose grants reach the object. Access needs ADMIN ON * to read, so a non-admin sees an explanation rather than a half-list that would read as "nobody else can see this". - Insights answers the question a catalog usually cannot: is this object actually used, and by whom. Runs in the window, how many people, typical and slowest time, the statements run most (named when they are saved queries), and the tables queried alongside it — the join partners, which is how you find out what a table is really used with. Every panel states its scope, because the honest answer is narrow: this is BI traffic on this node, over bi.usage_days (default 30). A driver, a job or another cluster reading the table is invisible here, so "no runs" never means "unused". Every statement in Most run carries a Run button: a saved one opens as itself, with its language and its parameters; an ad-hoc one opens in the editor and runs. A PromQL evaluation is recorded against its scope table as a stand-in SELECT, so it counts as usage but is not offered as a statement to re-run — that text would scan the table rather than evaluate the expression. - Chart it is one click from an object to a dashboard. The starter tile is built from what the object actually holds: a time-series table gets a PromQL sum over its series key; a table with a timestamp column (typed, or epoch milliseconds in a plain integer — a schema-less store has both) gets a count per hour; anything else gets a top-N group-by on its first repeating text column. The board says what it built from, and the tile is an ordinary saved query, so the person who knows the table fixes it in ten seconds. - Fields on the Schema tab lists what the rows carry, and lets anyone write down what each one means. skaidb is schema-less, so the list comes from a sample rather than a declaration — and that is exactly why the note matters: it is often the only place source_type is ever explained. - Recent sits above the tree: the objects you opened, per user, kept in _bi so they follow you to another browser. An alphabetical tree is the right default and the wrong answer for someone who works on four tables out of six hundred. - Pivot turns a GROUP BY a, b result into a crosstab: one dimension down the side, the other across the top, the measure in the cells. The engine has no PIVOT and needs none — that query already returns exactly these triples, so this is presentation. It is careful about three things: an absent combination renders as a gap, not a zero ("did not occur" and "summed to nothing" are different facts), only the widest 40 column values are shown and the rest are counted, not dropped silently, and a truncated result says so loudly, because a partial answer reshaped into a grid looks complete. - Drill down by clicking a bar, point or slice: the rows behind that bucket appear under the chart, with the statement that produced them shown above — a filtered result nobody can inspect is a magic trick, not a tool. The statement is derived, not guessed: the one that drew the chart is parsed, its grouped dimension is pinned to the clicked value, and its aggregates are dropped. Grouping by an alias resolves back to the expression it names, so a time_bucket chart filters on the bucket and not on a column that does not exist. A result with no GROUP BY has no dimension to pin and says so; the rows on screen already are the rows. "Open in the editor" puts the derived statement in a new tab. - Data also names any table the last pull could not fetch. A cycle does not stop at the first failure, so "the mirror pulled" and "the mirror is complete" are separate facts and the page shows both. - Data is the page to open when someone says a number looks stale: what this node mirrors, the pull interval, and — per table — when it was last fully swept and how far through its data the pull has reached. Those are two different clocks. A table whose newest row is months old is not a stale mirror; it is a quiet table. - The layout is fluid. Type, spacing, the sidebar, the editor and the chart are sized in viewport units with floors and ceilings rather than fixed pixels, so a 13-inch laptop is not the same screen as a 4K monitor with the same 190-pixel editor on it. Three breakpoints do the rest: below 1100px the chrome gives back its padding; below 860px the table tree stops taking width from the editor and becomes an overlay with its fold rail always reachable; below 560px rows grow to touch size. Nothing scrolls sideways at any size, and no control ends up off the edge — crates/skaidb-server/tests/bi_responsive.py measures that at sixteen viewports from 360px to 4K, in four states (plain, builder open, a result with 4KB cells, sidebar folded), and exits non-zero if any of them is wrong. A dashboard steps twelve columns → six → three → one as the window narrows, restating the saved placement in each column count so a tile pinned to column nine never invents a thirteenth, and tiles redraw from the rows already in hand when the window changes size rather than re-querying the node. Sizes follow the browser's own font-size preference, since the scale is written in rem. - Writes are refused, with the reason. A BI node reads a mirror; changes go to the cluster. - Load control: bi.max_concurrent_queries (default 4) bounds concurrent statements. Past it a request waits a second and then gets 429 with a retry hint, so a dashboard storm degrades visibly instead of dragging every tile down. bi.scan_row_budget replaces the node's storage.scan_row_budget for BI statements in either direction — a BI node is a dedicated node, and a wider row budget is the point of having one, paid in engine time on its own mirror pulls — while bi.scan_byte_budget can only tighten the node's byte ceiling, which is what keeps a wide scan from materializing more than the node holds. A statement that exceeds one is refused by name: the message says bi.scan_row_budget when the [bi] budget is what bound it, so the knob to raise is the one in the error, and the response carries the knob and the rows examined as data. The app turns that into the remedy that fits: a time-series ts picked without a bucket gets a one-click bucket, a builder query with no filter is offered one, and the knob is named for an admin. Grouped statements over row tables with count/sum/avg/min/max stream one row per group and are not bounded by the row budget at all; the refusal is for raw gathers — an unbucketed time column on a time-series table, a count(*) over one, or a distinct count. - BI traffic is tagged via = bi, so SHOW QUERIES, the slow log and the audit log separate it from driver traffic. KILL QUERY works on it.

The query builder

Builder is the third language in the editor. Instead of a statement you pick: a source (a table in the current database, or a model), the columns to group by, the measures to compute, filters, a sort and a limit. The pane on the left lists what the source offers — every column of a table, with numeric ones also offered as sum and avg, and timestamps bucketable to a minute, an hour or a day; a model's declared dimensions and metrics. The statement the picks compile to appears in the editor beneath, read-only, and runs, charts, exports, saves and becomes a tile like any other.

Nothing picked is ever syntax. The server builds the statement from the picks as an AST: a column has to be an identifier, a filter value becomes a literal, and the text is parsed back and checked against the picks before it is returned. A filter value of x' OR 1=1 -- filters on that string. A {{parameter}} is refused in a filter — the builder takes values; the SQL tab takes parameters.

Types are detected, not only read. The palette shows what each column is, judged from its values where the catalog says too little: an integer in the epoch-millisecond range is epoch ms, an ISO date stored as text is iso date, a number stored as text is number as text. Picking such a column applies the conversion that makes it what it is, so an epoch-millisecond column buckets and charts as time. A time-series table is read from its fixed shape — series-key columns are labels, ts is the time, every field is a measure — and ts arrives bucketed by the hour.

Column type conversion. Every chip on a raw table carries a type select: as is, number, integer, text, timestamp, bool. It compiles to the engine's to_* conversion — sum(to_float(price)), time_bucket(1h, to_timestamp(created_ms)), to_bool(active) = true — applied before bucketing and before aggregation, and a filter value is coerced to the same type so the comparison is the one you meant. A model field carries its own type in its expression and takes no cast.

Time-series sources compile to the shape the engine serves from partial aggregates without a scan: a bucketed ts, label dimensions, and a count/sum/avg/min/max of a field. count(*) over a time-series table reads every raw sample and is not offered; count a field.

A saved builder query stores its picks, not only their text. Reopening it restores the builder; Edit as SQL lets the picks go and keeps the statement, one way — a saved query edited as SQL is a SQL query from then on. The Saved panel marks built queries with ◆. A builder tab's draft carries its picks, so it comes back as a builder tab.

Models

A model is a named set of definitions over a base table and its joins: dimensions (expressions to group by — lower(country) as region) and metrics (expressions that aggregate — sum(total) - sum(cost) as margin). A metric must aggregate and a dimension must not; neither may read another table through a subquery, name a window, take a parameter, or call a function that writes. The Models panel lists them; + model defines one; a table's object page offers Model it, which derives a model from the table in one step — every column a dimension, each numeric column summed and averaged, and a row count — for someone to rename and prune.

A model is what gives a number one definition. revenue means the same thing on every board built from the model, and changing the definition changes every board: when a model or one of its fields is saved, every saved query built on it is recompiled and stored again, so a tile that reads the stored statement gets the new meaning without being touched. A change that would break a dependent — a field renamed out from under a query that picks it — is refused, naming the queries, and deleting a field or a model something is built on is refused the same way. Breaking a dashboard on somebody else's screen at its next refresh is what the refusal exists to prevent.

Models are private to their author unless marked shared or filed in a shared folder. Listing is not a gate: a model over a table you cannot read is not shown, and a shared model is compiled as you — if you may not read its base table, you cannot run a query built on it. A model's page shows its definition, its fields, and what is built on it; the lineage of a table shows the models over it, and the graph draws a model as a hop between the table and the queries built on it, keeping the direct table-to-query edge that carries the columns column impact walks.

Operating it

\bi in skaidbsh prints where the app is served, per node, or says none is deployed.
/status on a BI node reports it; on a member, each entry of witnesses[] carries roles and bi_url.
The web UI badges a BI node as <cluster>.bi.<alias>, and its witness card carries the endpoint.
Switching [bi] enabled off retires the endpoint on the next heartbeat — within one witness.interval_secs — with no admin step.

Checking a deployment

crates/skaidb-server/tests/bi_smoke.py drives the real page in headless Chrome against a running BI node: it signs in, runs a statement, saves it, reopens it from the Saved panel, draws every chart type, confirms a draft reached the cluster, and fails on any uncaught error. It cleans up the rows it makes.

python3 crates/skaidb-server/tests/bi_smoke.py https://bi.example.net:7443 alice hunter2

It exists because the failures that matter here are invisible to the Rust suite. A style attribute dropped by the content-security policy leaves an element on the page and unpainted; the smoke test reads the computed fill of a bar instead of trusting that the element exists. By default it charts the node's own node_stats, so it is safe to run against a live node; pass a statement of your own as the fourth argument.

crates/skaidb-server/tests/bi_storm.py is the load half, and it needs no browser. It builds a dashboard of many tiles over a smaller set of saved queries — so tiles share statements and the cache and the coalescing are both exercised — gives the board a refresh cadence so the node's own scheduler works it too, then drives concurrent viewers at /bi/api/query the way an opening dashboard does.

python3 crates/skaidb-server/tests/bi_storm.py https://bi.example.net:7443 alice hunter2

It reports throughput, p50/p95/p99, what share of answers came from the cache, and how many requests the admission gate turned away — a 429 share is the gate WORKING, so it is reported against a ceiling rather than failed. What it does fail on is a 5xx, a statement whose row count changes between runs, a cache hit rate of zero (the cache never worked), or a p99 past the threshold you pass. Like the smoke test it targets node_stats by default and removes everything it made, so it is safe against a live node — though its own rows land in _bi.runs, so numbers are comparable within a run rather than across many.

Parameters

A saved query can take parameters. Write {{name}} in the statement and declare each one with a type — ts, text, number or list:

SELECT region, sum(total) FROM orders
WHERE ts BETWEEN {{from}} AND {{to}} AND region = {{region}}
GROUP BY region

The editor grows a filter chip per parameter as you type it, and the save bar asks for the types — the one moment somebody is there to answer. Until then the type is read off the value in the box, so WHERE n > {{floor}} with -1 typed compares numbers rather than binding '-1' as text and quietly matching nothing. A saved query never guesses: it carries its own declarations.

Values are bound, never pasted. Each placeholder becomes a ?, the statement is parsed once, and the values are substituted into the parsed statement as literals. A value can therefore never become syntax, whatever anybody types into a filter box. A list is the one shape that changes the statement rather than a literal — IN ({{ids}}) with three values becomes IN (?, ?, ?) — and that expansion is driven by the count, never by the strings.

A {{name}} inside quotes is refused rather than substituted: '{{x}}' would bind nothing and match the literal text, which is precisely the silent wrong answer the types exist to prevent.

A ts takes milliseconds or a relative expression — now, now-7d, now+1h — resolved when the statement RUNS. That is what makes a saved dashboard mean "the last seven days" rather than the seven days that ended when it was saved.

Dropdowns. A declaration may name where its values come from: a static list, or a saved query returning one column. The chip becomes a dropdown over them. The options query runs as the viewer, so a dropdown can never show a value its reader may not select for themselves, and the chosen value is still bound — a dropdown is a convenience over the same binding, not a second path into the statement. A value that is not in the list is still accepted and still bound, because the list may have changed since the dashboard was saved.

On a dashboard the filters belong to the board: the bar is the union of what its tiles declare, so one time range drives every statement that takes one, and a tile that does not declare a parameter ignores it.

Tile layout

Tiles sit on a twelve-column grid — twelve divides by two, three and four, so halves, thirds and quarters all land on whole columns. Drag a tile onto another to move it there; the width control sets how many columns it spans, in the sizes worth having rather than by the column.

The server owns the arrangement. Every drag and resize sends an intent, the whole board is renormalised, and the browser redraws from the answer — so a change that displaced three other tiles shows all four moving, and two people editing the same board cannot save half of each other's work. Normalisation never moves a tile upward: a gap left deliberately survives a save. Only the newest change is drawn, because two in flight at once return in whatever order the network chooses and the older answer describes a board that no longer exists.

Placement is carried by CSS classes rather than inline geometry — the content-security policy drops a style attribute, so a grid positioned that way would pile every tile into column one, unpainted, with nothing reported. Below 900px the board becomes a single column: the saved columns and heights are released, because a quarter-width tile on a phone is not a smaller version of the same chart.

Folders

Saved queries can be filed in folders, nested a few levels deep. A shared folder shows its saved queries to the people it is shared with, read-only; drafts stay private to their author even inside one, because a half-written statement is not a publication.

Folders organise; grants authorize. Moving a query between folders changes nothing about who may RUN it — a folder confers only the ability to see that the query is there. Conflating the two would turn a file tree into an access-control system nobody can audit, so it is deliberately not one: the grants decide, as they do everywhere else.

Deleting a folder that still holds queries is refused rather than cascading, and a folder cannot be moved inside its own descendant — a tree with a loop hangs every reader that walks it.

The query profile

Profile runs the statement again under EXPLAIN ANALYZE and shows where the work went: the plan as steps from the access path up to the result, each with the rows behind it, and a line naming what to look at and the likely fix — an index, a narrower filter, a partition key.

It is honest about what it measures. The engine reports statement totals, not per-operator timings, so each number says whose it is rather than implying a breakdown that does not exist. On a cluster a scattered query's time is the coordinator's share, and the profile says so where that applies — a profile presenting one member's time as the whole is a lie the reader cannot detect.

Profiling runs the statement, so it takes a SELECT, and it goes through the same gate, budgets and RBAC as any other query: it costs what the original cost, and must not have a cheaper way in.

Exports

CSV writes a file a spreadsheet opens correctly: a UTF-8 byte-order mark (without it Excel on Windows reads the file in the system codepage and mangles every non-ASCII cell), CRLF records, RFC-4180 quoting, and a leading =, +, - or @ neutralised so a cell cannot arrive as a formula — numbers excepted, since -42 is a number. NULL and the empty string are written differently, so a reader can still tell them apart. JSON writes the same envelope the app already speaks.

The export runs the STATEMENT rather than serialising the grid, bounded by bi.export_max_rows instead of bi.max_result_rows — the number that suits a screen and the number that suits a file are not the same, and tying them together makes a large export impossible rather than slow. It goes through the same runner as a query, so the admission gate, the budgets, the parameter binding and the RBAC are the ones a query already faces. The response carries X-Skaidb-Rows and X-Skaidb-Truncated: a silently short file is the failure that matters, and a filename is the one thing people rename.

Scheduled queries and alerts

A saved query can carry a schedule, and optionally an alert: a condition over its result and somewhere to send it.

[[bi.destinations]]
name = "ops"
kind = "webhook"        # or "slack"
url  = "https://hooks.example.net/…"

The URL lives in the node's config rather than in _bi, and that is the point: _bi is mirrored and readable by every BI user, so a webhook URL stored there would be a credential sitting on a node analysts read. An alert names a destination; only the operator can see what is behind the name. The app lists the names and never the URLs.

A cadence is EVERY 5m or CRON '0 8 * * *' with a timezone. Timezones are fixed offsets — UTC or +HH:MM — not named zones: skaidb embeds no timezone database, and a schedule that silently ran an hour off twice a year would be worse than one that refuses the spelling. The shortest cadence is 60 seconds.

A condition is one of three, deliberately narrow — anything richer, the query itself expresses better:

condition	fires when
`no_rows`	the query returned nothing
`any_rows`	it returned anything (the query is the predicate)
a cell comparison	the first row's named column crosses a number

A cell condition over a result with no such column, or no rows, does not fire: "the number I watch is missing" is a different fact from "it crossed a line", and firing on it would send somebody hunting for a breach that never happened.

Only a change of state is sent, firing and resolved alike — a recovery is news, and an alert whose result flaps would otherwise notify sixty times an hour. Every send is recorded with its outcome, including failures, because an alert that quietly stopped being delivered is worse than one that never fired and only the history tells them apart.

Each run happens on this node, as the schedule's owner, through the same admission gate and budgets as a person's query — so a scheduled query can never read more than the person who saved it. It bypasses the result cache: an alert evaluating a cached answer cannot see the transition it exists to catch.

Deploying alongside witnesses

A BI node rolls exactly like a witness: same package, same unit, same readiness probe. It is a witness, so everything in CLUSTERING.md about table selection, grace periods and tombstone retention applies unchanged — including that witness table selection is not access control: what the BI node mirrors is what its users can read.

Size it for the mirror, not for the cluster: storage.memory_target should match the machine (cgroup-aware, so a container gets its own cap).

BI node (/bi)