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 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
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.

`[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.
`query_timeout_secs`	`0`	per statement; `0` = the node's `storage.statement_timeout_secs`.
`scan_row_budget` / `scan_byte_budget`	`0`	per-statement ceilings; `0` = the node's own budgets.

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 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.

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 the same dashboard. 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.
Otherwise the most recently seen node wins.
A node that has not heartbeat for 5 minutes 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 drop its name into the editor (double-click previews 100 rows), run with the button or ⌘/Ctrl+Enter. 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".
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.
Chart turns the current result into a picture: line, area, bar (grouped or stacked), row, scatter, pie, donut, a single number with its change against the previous row, and a progress bar against a goal. 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. The chart belongs to the tab, so switching tabs does not lose it.
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 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. The 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), and 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".
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.
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.
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 / bi.scan_byte_budget bound a single statement — they can only tighten against the node's own storage.* ceilings.
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.

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.

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)