Database::BI business intelligence for data files

JSON, same process, same files

API

Every page is built from the same pipeline, and the interesting parts are available as JSON: saved canvases, their cards, the columns of a source, the filesystem, stored calculated fields and the optional question and summary endpoints.

Saved canvases

Method and pathDoesAnswers
GET /api/analyses Lists every saved canvas. { analyses: [{ id, name, datasets, updated }] }
POST /api/analyses Creates one. Needs name and datasets; each dataset needs a spec starting table:, path:, url: or redis:, or being mariadb:server/table. 201 { id, created: 1 }
GET /api/analyses/:id Returns the whole document. The canvas
PUT /api/analyses/:id Replaces the document. The saved canvas
PATCH /api/analyses/:id Merges the supplied top-level keys. id and created are immutable; unknown keys are dropped. The saved canvas
DELETE /api/analyses/:id Removes it. { deleted: 1 }
GET /api/analyses/:id/export Downloads the document. A .json file
POST /api/analyses/import Creates one from an uploaded .json file or a raw JSON body. Caller-supplied id, created and updated are stripped. The new canvas
POST /api/analyses/:id/share Issues a share token for the canvas. A share link

The layout

layout is an array of { visual, x, y, w, h }, where visual indexes the visuals array and the rest are 1-based grid cells. The grid is twelve columns wide, x + w - 1 cannot exceed twelve, and one visual may span at most six rows. Positions are stored as given — a gap is a gap — but two visuals may not share a cell: an overlapping set is a 400 and the stored layout is left alone. A canvas has a single grid, so the layout moves as a whole and is only checked when the new set is complete.

Appending a card

POST /api/analyses/:id/visuals
Content-Type: application/json

{
  "kind":    "bar",
  "encode":  { "cat": "region", "val": "amount", "agg": "median" },
  "options": {},
  "title":   "Median amount by region",
  "query":   "cf=year&cv=2025"
}

kind is one of table, bar, pie, line, graph, scatter, heatmap, stacked, combo, treemap, histogram, wordcloud, map or text. The optional query is the canvas page's own query string: cf (cross-filter), c (calculated columns), p.<name> (bound parameters) and range/rcol are read from it, so a card added while the canvas is filtered is drawn through the same state as the cards already on screen.

The card is rendered against the canvas's datasets, joins and calculated fields before it is stored, so an unknown column or an undrawable visual is a 400 and nothing is appended. The answer is: 

201 { id, visual, kind, title, html, head, cf_col }

visual is the new card's index, head is any stylesheet or script the page must add once to draw it (Leaflet, for a map), and cf_col is the column its marks cross-filter by. The card is left unplaced until the reader moves it.

The measure a card aggregates with

A kind that aggregates a value column — bar, pie, stacked, treemap, heatmap and combo — reads an optional encode.agg: sum (the default), avg, min, max, count, count_distinct, median, stdev, first, last, range, or p<N> from p1 to p100 — the same vocabulary and spelling a pivot's m= uses.

The function is checked whatever the kind is, so a misspelt one is a 400 rather than a chart drawn some other way. So is a function that needs a column (median, unlike count) when the card names no value column. A combo aggregates both its series with the one function.

A card's own cross-row plan

A card may bring a cross_row plan in the spelling the canvas page's cs, by and order use: { cs: [ "col:fn:name[:arg]" ], by: "col", order: [ "col:dir" ] }. The card then runs that plan instead of the page's, so one card can show a running total while its neighbours show raw rows; { cs: [] } is a card that runs no cross-row pass at all. A card that sends no cross_row inherits the page's plan.

Redrawing one card

PATCH /api/analyses/:id/visuals/:visual
{ "encode": { "loc": "city" }, "query": "cf=region&cv=North" }

This replaces a stored visual's encoding in place; the optional query is read the same way as above. The visual keeps its index, so its position, size and title are unchanged. The replacement is rendered before it is stored, so an unknown column or an undrawable map is a 400 and the saved visual is left alone. The answer is the same shape as the append.

A card's data can be downloaded as GET /a/:id/visual/:n/:fmt, which uses the canvas's own rows and does not accept an l= pointing anywhere else.

Text cards

POST /api/analyses/:id/visuals
Content-Type: application/json

{
  "kind":    "text",
  "options": {
    "text":   "Quarterly sales",
    "font":   "georgia",
    "size":   24,
    "align":  "center",
    "bold":   1,
    "plain":  1
  }
}

A text card reads no data, so it has no encode, query or cross_row, and /a/:id/visual/:n/csv answers 404 for it. A setting outside these is dropped; one with a bad value is a 400 and nothing is stored.

text
Required, up to 20,000 characters. It is shown as plain text, never as markup.
font
arial, georgia, times, verdana, trebuchet or courier. Left out, the card uses the page's font.
size
A whole number of points from 8 to 96.
colour, background
Written #rrggbb. Left out, the text follows the theme and the card has no fill of its own.
align
left, center or right.
bold, italic, plain
True to turn on. plain hides the frame and header until the card is pointed at.
PATCH  /api/analyses/:id/visuals/:visual     { "options": { ... } }
DELETE /api/analyses/:id/visuals/:visual

PATCH replaces all the options, keeping the card's place and size. DELETE answers { "deleted": 1 }; it accepts only text cards, and any other kind is a 400. Later cards move down one index, and the layout and any cv in a bookmark move with them.

Data and filesystem

GET /api/columns?table=… or ?path=…
The column names of a source, used to populate the join panel without a page reload.
GET /api/dirs?path=…
Subdirectories of a path, for the export panel's inline browser.
GET /api/stat?path=…
{ exists, path, mtime, size }, plus table_count for a valid SQLite file.
GET /api/range?l=…
The earliest and latest value of a date column, so the filter bar can disable a window the data cannot answer.
POST /api/ask
A question in words, answered with an analysis spec and a URL. The model sees column names and types only — never rows or file paths. 503 when no key is configured, 422 when the question cannot be mapped to valid columns.
POST /api/summary
A narrative summary from deterministic sentences, works offline, with an optional polished version when ai=1 and a key is set. Cached for an hour.

Stored fields

The definitions saved beside the data — calculated fields, value groups, date patterns, lookups, extractions and coordinate columns — have a list, a save and a delete endpoint each, all keyed by l=<spec>:

AreaListSaveDelete
Calculated fields GET /calc/list POST /calc/save (name, expr) POST /calc/delete
Value groups GET /groups/list POST /groups/save (group, values) POST /groups/delete
Date patterns GET /dates/list POST /dates/save (pattern, optional time, as) POST /dates/delete
Lookups (how they apply) GET /lookups/list POST /lookups/save (name, file, key, optional col, fields, default) POST /lookups/delete
Field extractions (how they apply) GET /extract/list POST /extract/save (name, col, pattern, optional fields) POST /extract/delete
Coordinate columns GET /geo/list POST /geo/save (col, kind, repeatable in pairs; replace=1 forgets the old ones first) POST /geo/delete

Behaviour to rely on