## Name Database::BI - Web-based Business Intelligence viewer for data files ## Version 0.021.0 ## Description `Database::BI` is a self-contained [Mojolicious](https://metacpan.org/pod/Mojolicious) web application that reads and views arbitrary data (CSV, PSV, TSV, SQLite, JSON, XML, XLSX, etc.) and presents them as styled, sortable, reorderable HTML tables. It has no persistent database of its own, it reads your files on every request. Users navigate to pick a file, open the filesystem browser to navigate anywhere on disc, drag-and-drop files to upload, join multiple tables in memory, apply server-side filters, and export results as CSV, SQLite, JSON, or XLSX. Nine D3.js v7 chart types, a pivot/crosstab table, per-column statistics, and multi-visual saved canvases are available from the toolbar. A tutorial in plain English is built into the application at `/tutorial`. It is written for people who do not know Perl, and it is the place to start. Key features: - **File picker** -- the home page scans `data_dir` and shows a card for every supported file. Recently opened filesystem files appear in a "Recently opened" section powered by `localStorage`. - **Filesystem browser** -- `/browse` lets the user navigate the entire filesystem and open any supported data file, not just files in `data_dir`. - **Column sort and reorder** -- clicking a header sorts the table; headers are draggable to reorder. Both settings are persisted in `localStorage` by column name and survive page reloads. - **Left join** -- the "Merge data" panel on any table view lets the user join one or more additional tables on a shared key via [Database::Join](https://metacpan.org/pod/Database%3A%3AJoin). Every left row is kept; right-table columns are appended for matching rows. Multiple joins can be chained. - **Quick-filter bar** -- a column/operator/value row below the toolbar lets the user add filter conditions without opening any panel. Pressing Enter applies the filter. Each active filter chip has an individual remove (x) button so conditions can be dropped one at a time. Operators: `eq`, `ne`, `contains`, `starts`, `lt`, `le`, `gt`, `ge`, `between`, `empty`, `notempty`; and `in`, which a link can carry but the bar does not offer: any value of a JSON list, as in `f=place:in:["Paris","Rome"]`. Filters are applied server-side after all joins. `lt`, `le`, `gt` and `ge` compare numbers, reading `$1,234.56` and `(1,234.56)` as numbers too; a blank or a word in the cell matches none of them. An operator that is none of these (operators are lower case) is a `400` that lists them, and so is a filter, or a time range's `rcol=`, on a column the view does not have (the message lists the columns): neither is taken as a filter that keeps every row, or none. A canvas card's own saved filters are the exception: one on a column its dataset no longer has is ignored. - **Combine (UNION ALL)** -- the "Combine data" panel stacks rows from a second (or further) file beneath the current rows, using a unified column set and leaving blanks where a source file lacks a column. - **Charts** -- nine [HTML::D3](https://metacpan.org/pod/HTML%3A%3AD3) D3.js v7 chart types are accessible from the toolbar: line chart (brush-to-zoom, reference lines for min/avg/max, multi-series interactive legend), pie chart (click-a-slice to filter, top-N, donut mode, configurable colour scheme), heatmap (two categorical axes, count or sum mode), bar chart (horizontal or vertical, sort, top-N), scatter/bubble plot (optional categorical colour and numeric size dimensions), stacked bar chart, combo bar+line chart, treemap, and histogram (server-side binning, `?bins=N`). All charts can be resized by dragging the bottom-right corner grip, or from the keyboard: the grip takes focus, the arrow keys resize the chart (Shift takes bigger steps, Home and End go to the narrowest and widest it can be) and the size is announced as it changes. All chart pages include Copy-link and Export SVG/PNG/PDF buttons. - **Maps** -- `GET /map` plots the filtered view on an OpenStreetMap map, either from latitude and longitude columns or from a place column geocoded by a [Geo::Coder::List](https://metacpan.org/pod/Geo%3A%3ACoder%3A%3AList) chain: [Geo::Coder::Free](https://metacpan.org/pod/Geo%3A%3ACoder%3A%3AFree)'s local data first, then OpenStreetMap's Nominatim service ([Geo::Coder::OSM](https://metacpan.org/pod/Geo%3A%3ACoder%3A%3AOSM), at most one request a second) for the places the local data does not know. Nominatim is asked over https, and what it finds is also kept for a month in `/geocode-cache/`. Each distinct place is looked up once, remembered in the model store and shared by every dataset that names it. Rows sharing a position become one marker. - **Pivot table** -- the "Pivot table" panel builds a crosstab from any combination of row dimensions, column dimensions, and measures (`sum|avg|min|max|count|count_distinct|median|stdev|first|last|range`, plus `p` for the Nth percentile). The backend aggregates what it can; the rest are computed in Perl from each group's values, subtotals and grand total included, because a median is not the sum of the medians of its parts. Supports subtotals, HAVING filters, top-N row groups, and direct export to CSV, SQLite, JSON, or XLSX. - **Column statistics** -- the column-stats button runs `GET /stats`, which returns one row per column: row count, null count, distinct count, first and last, and (for numeric columns) min, max, range, mean, median, a chosen percentile and the sample standard deviation. The arithmetic is the same one the pivot's measures use. - **Calculated fields** -- virtual columns defined by inline `?c=name:expr` URL params or stored persistently via `POST /calc/save`. Expressions support arithmetic, comparison, `IF`, string, numeric, and date functions, plus `bin(value,width)` for histogram grouping. Calc fields are applied before aggregation on all chart and pivot routes. - **Relative time ranges** -- any data view, chart, pivot or saved canvas can be limited to a named window worked out from today -- the last 24 hours, the last 7, 30 or 90 days, this or last month, quarter or year -- instead of typing dates. The window is resolved on every request (`?range=` with `?rcol=`), so a bookmark or a canvas still means the same thing tomorrow, and the filter bar disables a window the data cannot answer. - **Saved canvases** -- the "Save as Canvas" panel saves the current data configuration as a named multi-visual canvas (`/a/:id`). Visuals are arranged on a 12-column grid: drag a card by its header to any free cell (the header also takes focus, and the arrow keys move the card a cell at a time, Shift going to the edge), and resize it from the corner handle. A card's width and height can also be set without a drag from the Size menu in its header: full, half, a third or a quarter of the width, and one, two, three or six rows high. The size the card is sits on the card itself, beside that menu, so a width the corner grip left it at reads without the menu being opened. Each move is announced -- the column and row the card landed in, and any card the move pushed down -- and saved as you go. The whole position and size is saved with the canvas, so a card put in row 4 stays in row 4 rather than being packed up the page. **Add text** in the toolbar adds a card of plain text, for a title or an explanation, with a font, a size in points, colours and an alignment of its own; it can hide its frame and header until it is pointed at, and a row holding only one-row text cards is as tall as its text. Double-click the text, or use **Edit text**, to change it or to remove the card. **Add current view** in the toolbar appends another table, chart or map without leaving the canvas, and a status region announces the card and the new visual count; when the card's script has drawn it, the region says the chart or map has finished drawing, and if D3 or Leaflet could not be loaded it names the missing library instead. A scatter card can be coloured by a column and its bubbles sized by a number there, with the same choices as the scatter page. A card that shows an aggregated value picks its own function in that panel, from the vocabulary the pivot uses and with the same names: Sum, Average, Min, Max, Count distinct, Median, Std dev, First, Last, Range or a Percentile with its level, so a card can show a median, a spread or a percentile where the chart pages behind it could only sum. Canvases support cross-filtering, drill-through hierarchies, parameterisation, bookmarks with undo/redo, per-visual SVG, PNG and PDF download, and print to PDF/paper. - **Drag-and-drop upload** -- any supported data file can be dropped directly onto the application. The file is opened immediately; when the "Combine data" panel is open the dropped file populates the right-table path field. - **Export** -- the toolbar export panel writes the current logical view (after joins, filters, and dedup) to a chosen filesystem path as CSV (`.csv`), SQLite (`.sql`), or JSON (`.json`). - **Print** -- the Print button, to the right of Refresh, prints the header row and every row of the current view, not just the page on screen: the rows the Export panel would save (after joins, filters, calculated and cross-row columns, and dedup), read from `GET /export?format=json`, with the columns in the order the table shows them and sorted as it is sorted. Nothing else on the page is printed, and the header row repeats on each printed page. A row deleted from the page with Delete selected is still printed; a deleted column is not. - **URL import** -- `/import` fetches an HTML table from any public URL and renders it in the browser without saving to disc. - **Redis data source** (optional) -- when the `Redis` or `Redis::Fast` module is installed, any Redis hash store can be opened as a read-only data table by entering a `redis:tablename|redis://host:port` spec in the path field or in a join/combine panel. Redis keys must follow the `tablename:entry` convention used by [Database::Abstraction](https://metacpan.org/pod/Database%3A%3AAbstraction). Because `Redis` is an optional dependency, the application works fully without it; Redis support activates automatically when the module is available. **MariaDB and MySQL data source** (optional) -- a table on a server named in the `mariadb_servers` configuration opens as `mariadb:server/table`, read-only, with the columns in the table's own order and a page read with `LIMIT`. See ["MariaDB and MySQL servers"](#mariadb-and-mysql-servers). ### UTF-8 and Encoding All file data is returned as Perl character strings. CSV/PSV/TSV files are read by [Text::xSV::Slurp](https://metacpan.org/pod/Text%3A%3AxSV%3A%3ASlurp) or [DBD::CSV](https://metacpan.org/pod/DBD%3A%3ACSV), both of which pass bytes through without re-encoding; the application serves the resulting page as `text/html; charset=UTF-8`, so full Unicode is displayed correctly as long as the source file itself is UTF-8. A UTF-8 byte-order mark at the start of a CSV, TSV, PSV or JSON file (Excel's "CSV UTF-8" writes one) is read past, so it is not part of the first column's name. Filter values (`f=col:op:val`) are decoded from the URL by Mojolicious before reaching the controller and are compared against cell values as Perl character strings. Unicode characters in filter values are therefore supported transparently. URL-imported HTML tables are fetched with [Database::BI::SafeUA](https://metacpan.org/pod/Database%3A%3ABI%3A%3ASafeUA) (see ["Which addresses it may fetch"](#which-addresses-it-may-fetch)); [Database::Abstraction](https://metacpan.org/pod/Database%3A%3AAbstraction) uses the page's own charset declaration to decode the body. If the remote page declares an incorrect charset, cell values may contain mojibake -- this is a limitation of the source data, not the application. ## Synopsis **Start the development server (restarts automatically when you edit a file):** ``` script/database-bi ``` `morbo -l http://127.0.0.1:3000 script/database-bi` does the same thing (a bare `morbo script/database-bi` listens on every interface, so it starts only with a password set), and `script/database-bi daemon` starts a plain server with no file watcher; any other Mojolicious command word (`routes`, `get`, ...) is passed to the application. **Start it showing one database:** ``` script/database-bi data/store.db ``` The home address then shows that database, and its Home link leads to the usual list. Only one can be named: more than one is an error (exit status 2), because a join cannot be given on the command line, so open one and use Merge or Combine in the browser. A file that is missing, cannot be read or is of an unsupported type is the same error, before the server starts. A remote path, Redis spec or MariaDB spec is accepted as it would be by `/open`, and is checked when it is first shown. It listens on port 3000 of this machine only (`http://127.0.0.1:3000`); see ["Who can reach the server"](#who-can-reach-the-server) to change the port or open it to other machines. A plain server does the same with `BI_OPEN=/full/path/store.db script/database-bi daemon`; a file named after `daemon` is ignored. **Start the production server:** ``` hypnotoad script/database-bi # start, or restart with no downtime hypnotoad -s script/database-bi # stop ``` It listens on port 8080 of this machine only and ignores `MOJO_LISTEN`; set the port and anything else in `database_bi.conf`, under `hypnotoad => { listen => ['http://*:8080'] }` (`*` opens it to every machine). It keeps its process id in `script/hypnotoad.pid` unless `pid_file` says otherwise. It takes no database on its command line: use `BI_OPEN=/full/path/store.db hypnotoad script/database-bi`. **Use a different data directory:** ```perl # In database_bi.conf (create this file in the same folder as script/): { data_dir => '/home/user/data' } ``` **Change the language used for templates:** ```perl # In database_bi.conf: { data_dir => 'data', language => 'fr', platform => 'web' } # Then create templates/web/fr/ and put your French .html.tt files there. ``` **Run the test suite to verify everything is working:** ``` make test ``` **Generate the Makefile for the first time or after editing Makefile.PL:** ``` perl Makefile.PL ``` ## Routes - `GET /` Scans `data_dir`, renders a card grid of available tables. When the server was started with the name of a database (see ["SYNOPSIS"](#synopsis)), a request from outside the application is redirected to `/open` for that database. A request whose `Referer` is this server, such as the Home link, gets the card grid, so a browser that withholds `Referer` always sees the database. - `GET /tutorial` Renders the tutorial that is part of the application. The chapters are data in [Database::BI::Tutorial](https://metacpan.org/pod/Database%3A%3ABI%3A%3ATutorial), not in this POD. - `GET /login`, `POST /login`, `POST /logout` The login, when `password_hash` is set (see ["Logging in"](#logging-in)). `GET /login` shows the form (with `return=`, the page to go back to: a path on this site), `POST /login` takes `password=` and `return=`, and `POST /logout` ends the session. With no password set, `/login` says there is nothing to log in to. - `GET /preferences` Sets the reader's preferences: the theme, one of light, dark or the same as the device (the default). They are kept in the browser's `localStorage`, not on the server, and apply to every page. Saved charts, printed pages and embedded canvases stay light. - `GET /view/:table` Opens the named table from `data_dir`. Accepts `?f=col:op:val` (repeatable) to pre-filter results, and `?range=name&rcol=column` to keep only a relative window of rows (see ["Relative time ranges"](#relative-time-ranges)). `?f=col:between:lo:hi` keeps the rows whose `col` lies from `lo` to `hi`, both included, whichever bound is written first. Two numbers (a range of years, `Year:between:1880:1900`) compare as numbers; two dates or timestamps compare as times (`when:between:2026-11-01T09:00:00:2026-11-01T17:00:00` splits at the colon between them); any other pair compares as text, ignoring case. The filter row offers it for date columns (two date boxes) and number columns (two number boxes). A name that is not a table in `data_dir` is a `404` page that says so ("There is no table called ..."), with the breadcrumb and site navigation; it does not show the data directory. - `GET /browse` Filesystem navigator. Accepts `?path=` to set the starting directory (defaults to `$HOME`). - `GET /open` Opens any supported file by absolute path (`?path=`). Accepts `?f=` filters. When the file is a multi-table SQLite database, passing `?it=` selects a specific internal table instead of the auto-detected first one. - `GET /join` Performs one or more left joins and renders the merged table. Parameters: ``` l= left table spec (see below) j=|| join step (repeatable): right-spec, left key, right key c= table to stack after the joins (repeatable; see /combine) f=:: result filter (repeatable) range= relative time range, with rcol= (see below) ``` Spec formats: ``` table:name named table from data_dir table:file/table a table inside a multi-table SQLite file (or a list in a JSON file) in data_dir path:/abs/path/to/file.csv absolute local path path:/abs/path/to/file.db#table one table of such a file anywhere else; a file whose own name has a # is that file /../hostname/dir/file.csv remote file via SFTP redis:tablename|redis://host:port Redis hash store (requires Redis module) mariadb:server/table a table on a server in mariadb_servers mariadb:server lists that server's tables ``` - `GET /api/columns` Returns `{ "columns": [...] }` for a table (`?table=name` or `?table=file/table` for a table inside a multi-table SQLite file) or file (`?path=/abs/path`, or `?spec=path:/abs/path#table` for one of its tables). Used by the join UI to populate the right-key dropdown without a page reload. - `GET /export` Exports the current logical view (same `l=`, `j=`, `f=` parameters as `/join`) as a file download. Additional parameter: ``` format=csv (default) - RFC 4180 CSV; UTF-8; CRLF line endings format=sqlite - SQLite 3 database with a single table named "data" ``` The download filename is derived from the left table label with non-alphanumeric characters replaced by underscores. - `POST /export` Writes the current logical view to a chosen filesystem path. Body params: `l=`, `j=`, `f=` (same as GET), plus `dir=` (target directory) and `filename=` (name including extension; extension determines format: `.csv` or `.sql`). Returns JSON `{ saved: "/abs/path" }` or `{ error: "..." }`. - `GET /api/dirs` Returns a JSON directory listing (subdirectories only) for the export panel's inline directory browser. Accepts `?path=` (defaults to `$HOME`). Returns `{ path, parent, dirs: [{name, path}] }`. - `GET /api/stat` Returns filesystem metadata for a file path (`?path=`). Returns `{ exists, path, mtime, size }`. For SQLite files (`.sql`, `.sqlite`, `.sqlite3`, `.db`) an additional `table_count` integer is included when the file is a valid SQLite database; the field is omitted for Berkeley DB or other non-SQLite payloads. If the file does not exist, `exists` is `false` and the remaining fields are absent (HTTP 200). Returns HTTP 400 when `path` is missing. - `POST /upload` Accepts a multipart file upload (field name: `file`), validates the extension, saves to a managed `.uploads/` subdirectory under the app home, and returns JSON `{ url, path }`. - `GET /import` Fetches an HTML table from a public URL (`?url=`) and renders it as a data grid. A URL that leads to this machine or a private network is refused (see ["Which addresses it may fetch"](#which-addresses-it-may-fetch)). An optional `?t=` parameter (zero-based integer) selects which HTML table on the page to display when the page contains multiple tables. - `GET /tables` Schema browser for multi-table SQLite databases. Accepts `?l=` (same `table:name` or `path:/abs/path` spec as `/join`). Returns a card grid listing every internal table with its columns; each card links to `/open?path=...&it=
`. Redirects to 404 when the spec is absent, invalid, or the resolved file is not a multi-table SQLite database. - `GET /combine` Stacks rows from two or more tables vertically (UNION ALL) into a unified view. All columns from all sources appear as headers; cells are blank where a source file lacks a column. Parameters: ``` l= left table: "table:name" or "path:/abs/path" j=|| join step applied to the left table first (repeatable) c= additional table to stack (repeatable) f=:: result filter applied after combining (repeatable) ``` - `GET /graph` Renders a D3.js v7 zoomable line chart of any two columns. Parameters: ``` l= left table (required) x=X-axis column name (required; any type, shown as labels) y=Y-axis column name (required; must be numeric after stripping currency symbols and commas; accounting-notation negatives like (1,234.56) are handled automatically) span= bucket x into time buckets (optional; see "Time buckets") agg= aggregate y per bucket, which is what makes this a timechart (optional; default: one point per row) series=one line per value of this column (optional) back= URL for the second breadcrumb link (optional; default "/") back2= URL for the outermost breadcrumb link when three levels are needed (optional; e.g. /tables?l=... for the schema browser) back2_label= Label for the back2 link (optional; default "Back") j=, f=, d= pipeline params (same as /join) ``` - `GET /pie` Renders a D3.js v7 animated pie chart grouped by a category column. Clicking a slice or legend entry navigates to the table view filtered to that category. Parameters: ``` l= left table (required) cat=category column to group by (required) val=numeric column to sum per category (required) donut=1 show a hole in the centre (optional) back= URL for the second breadcrumb link (optional; default "/") back2= URL for the outermost breadcrumb link (optional) back2_label= Label for the back2 link (optional; default "Back") f= result filters applied before aggregating (repeatable) ``` - `GET /heatmap` Renders a D3.js v7 grid heatmap with two categorical axes. Each cell colour encodes a summed or counted numeric value. Parameters: ``` l= left table (required) x=X-axis column name (required; categorical) y=Y-axis column name (required; categorical) val=numeric column to sum per cell (optional; omit to count rows) scheme= colour scheme: YlOrRd Blues Greens Purples RdPu YlGnBu (optional; default YlOrRd) show_val=1 print the value inside each cell (optional) back= URL for the second breadcrumb link (optional; default "/") back2= URL for the outermost breadcrumb link (optional) back2_label= Label for the back2 link (optional; default "Back") j=, f=, d= pipeline params (same as /join) ``` - `GET /bar` Renders a D3.js v7 bar chart grouped by a categorical column. Parameters: ```perl l= left table (required) cat=category (X-axis) column (required) val=numeric column to sum per category; omit or use __count__ to count rows instead of summing (optional) orient=h horizontal bars (default: vertical) sort=value|label sort bars by descending value or ascending label max=N collapse bars beyond the top-N into an "Other" bar back= URL for the second breadcrumb link (optional; default "/") back2= URL for the outermost breadcrumb link (optional) back2_label= Label for the back2 link (optional; default "Back") j=, f=, d= pipeline params (same as /join) ``` - `GET /folder` Hierarchical folder view. Groups a data table by the letter-prefix of an order-number column (e.g. A10, CT4, B3) and shows one card per prefix group. Clicking a card with sub-prefixes drills down; clicking a leaf shows the matching data rows. Parameters: ``` l= left table (required) col=order-code column (auto-detected when absent) prefix= current drill-down prefix (top level when absent) back= URL for the back link (optional) f= result filters (repeatable) ``` - `POST /uploads/clear` Deletes every file from the `.uploads/` staging directory. Returns JSON `{ "freed": }>. No request body is needed. - `GET /pivot` Renders a pivot/crosstab table. Parameters: ``` l= left table (required) rows=col1,col2 row-dimension column names (required) cols=col3 column-dimension names (optional) m=col:fn:alias measure spec, repeatable (fn: sum|avg|min|max|count|count_distinct for the aggregates the backend computes, median|stdev|first|last|range for those computed in Perl from the group's values, or p for the Nth percentile -- p50 is the median, and p1 to p100 are accepted) totals=1 include subtotals column and grand-total row topn=N keep only the top-N row groups topn_col= measure alias to rank by (required when topn= is set) max_cols=N collapse tail column combos beyond top-N into "Other" format=csv|sqlite|json|xlsx stream result as a download back= URL for the back breadcrumb link f= result filters applied before aggregating (repeatable) ``` - `GET /stats` Per-column statistics view. Returns one row per column: row count, null count, distinct count, first and last, and (for numeric columns) min, max, range, mean, median, the percentile named by `p=N` and sample standard deviation. Parameters: `l=` (required), `f=` filters and `p=N` for the percentile column (1 to 100, default 95). - `GET /scatter` D3.js v7 scatter/bubble plot. Parameters: ``` l= left table (required) x=X-axis column (required; numeric) y=Y-axis column (required; numeric) series=categorical colour dimension (optional) size=numeric bubble-radius dimension (optional) scheme= colour scheme when series= is set: tableau10 (default), category10, set2, set3 or paired; any other is a 400 min_r=N smallest bubble radius in px with size= (1-40, default 3) max_r=N largest bubble radius in px with size= (1-40, default 15) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` - `GET /stacked` D3.js v7 stacked bar chart. Parameters: ``` l= left table (required) cat=X-axis categories (required) series=stack segments (required; seg= is a backwards-compatible alias) val=measure column (optional; omit to count rows) scheme= colour scheme (optional) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` - `GET /combo` D3.js v7 combo bar+line chart. Parameters: ``` l= left table (required) cat=X-axis categories (required; x= is a backwards-compatible alias) bar_val=bar measure column (required) line_val=line measure column (required) scheme= bar and line colours: the first two of tableau10, category10, set2, set3 or paired (optional; steel blue and red without it, and for any other name) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` - `GET /treemap` D3.js v7 treemap. Parameters: ``` l= left table (required) group=tile label column (required; cat= is a backwards-compatible alias) size=tile area column, summed (optional, counting rows without it; val= is a backwards-compatible alias) topn=N keep only the top-N tiles; remainder merged into "Other" scheme= colour scheme (optional) color=shade each tile by this column instead (optional), folded over the tile's rows; "Other" is folded over the rows it holds color_agg=F how color= is folded: avg (default), sum, min, max, median or any other aggregate function; an unknown one is a 400 color_scheme=S scale for color=: YlOrRd (default), Blues, Greens, Purples, RdPu or YlGnBu; any other name is YlOrRd back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` - `GET /histogram` D3.js v7 histogram with server-side binning. Parameters: ``` l= left table (required) col=numeric column to bin (required) bins=N bin count (default 10; clamped to range 1--200) scheme=S bar colour: the first colour of tableau10, category10, set2, set3 or paired (optional; steelblue without it, and for any other name) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` - `GET /wordcloud` Word cloud of a text column, drawn with HTML::TagCloud. Each row contributes its words once, so a word's size is the number of rows that use it rather than the number of times one long note repeats it. Common English words are removed. Parameters: ``` l= left table (required) col=text column to read (required) topn=N number of different words to show (default 50; range 1--500) minlen=N shortest word kept (default 3; range 1--64) stop= extra words to ignore, comma or space separated (optional) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` With `back=`, each word links to the rows that contain it. The same cloud is available as a card on a saved canvas: `POST /api/analyses/:id/visuals` takes `kind => wordcloud` with `encode.col`, and the card counts the words of its own rows -- its filters, the page's cross-filter and its own options -- so a canvas that is narrowed to one region shows the words of that region. - `GET /map` OpenStreetMap map of the filtered view, drawn with HTML::OSM. A row's position comes from a location column that is geocoded (`loc=`, one lookup per distinct value, remembered once in the model store and shared with every dataset that names it) or from coordinate columns (`lat=` and `lon=`). Rows sharing a position become one marker. When some rows cannot be plotted, the note that says so counts them by reason (empty, not a place name, not found, or not looked up yet), links to all of them in the table, and opens onto a list of their places, each with its number of rows, why it was not plotted and a link to those rows. Markers are clustered with Leaflet.markercluster, so a dense map draws a count at low zoom and splits it as it is zoomed in. Clustering is always on. The map has a scale bar, opens on every marker unless `zoom=` is given, and draws its key (colours, and bubble sizes) on the map; the colours listed are those of the markers in view, so the key changes as the map is zoomed and moved. Places that are not in the cache yet are looked up after the page is shown. The map draws what the cache already knows, and a progress bar names each place as it is looked up ("Looking up 5 of 10: 10 Downing St, London"). Each answer is saved at once, through `POST /geo/lookup`, and the map is drawn again when the last one is in. Cancel stops the lookups and draws the map with the places found so far. The places left are listed as not looked up yet, with a link to look them up. Without JavaScript, or for a client that is not a browser, the page waits for the lookups instead (`geocode=wait`). Parameters: ``` l= left table (required) loc=column of places to geocode (required unless lat= and lon= are); repeat it for a place written across columns, which are joined with ", " in that order (loc=Address&loc=Country) lat=latitude column, -90..90 lon=longitude column, -180..180 popup=column shown in the marker popup (repeatable; default every column but the place or coordinates) max=N markers to draw (default map_max_markers, 2000) zoom=N initial zoom 0..19 (default: fit the markers) max_zoom=N tile layer maxZoom 0..28 (default: map_max_zoom, if set) tiles= named tile source from the tile_sources config key heat=1 add a heatmap layer over the markers weight=numeric heatmap intensity column (normalised to 0..1) size=numeric bubble-radius column; markers become circles color=colour markers from a column color_rule=:: repeatable; op is gt, ge, lt, le, eq or ne color_default= fallback colour (default #3388ff) series=colour markers by category (not with color=) scheme= palette for series=: tableau10, category10, set2, set3, paired geocode=wait look up the places not cached yet before the page is sent (the default for a client that is not a browser) geocode=cached look nothing up: plot what the cache knows (where Cancel leads) back=, back2=, back2_label= breadcrumb links f= result filters (repeatable) ``` A marker stands for every row at its place, and its popup lists them all: a value every row shares is shown once ("Person: Valentin Puntar"), and the columns that differ are a small table, one line a row. The table shows at most `map_popup_rows` rows (default 10) in a box that scrolls, so a busy place does not cover the map; the rest are counted, with a link to them in the table the map came from. An unknown `tiles=` or `scheme=` is a 400, as is an unknown column, `color=` together with `series=`, or a transposed lat/lon pair. The page never shows the [HTML::OSM](https://metacpan.org/pod/HTML%3A%3AOSM) Nominatim search box (`search => 0` is always passed), so nothing typed on the map is sent anywhere. With `loc=`, a place the local data does not know is sent to Nominatim, as the place name alone; set `map_geocode_online` to 0 to keep every lookup local. [HTML::OSM](https://metacpan.org/pod/HTML%3A%3AOSM) 0.14 or later is the renderer; an older optional install still returns the page with a warning. - `GET /line` Alias for `GET /graph`. All `/graph` parameters accepted. - `GET /canvases` Lists the saved canvases, as cards that open them, with Import canvas. It is the main navigation's Canvases link; the home page shows the same list, without Import canvas. With no canvases, the page says how to make one. A canvas's breadcrumb leads back here unless its `back=` names another page. - `GET /a/:id` Renders a saved canvas (analysis) as a full multi-visual page. Accepts cross-filter params `?cf=&cv=`, drill-through params `?dd=&dl=`, parameter overrides `?p.=`, and a relative time range `?range=&rcol=` that narrows every card on the canvas (see ["Relative time ranges"](#relative-time-ranges)). - `GET /api/analyses` Returns `{ analyses: [{id, name, datasets, updated}] }`. - `POST /api/analyses` Creates a new analysis from a JSON request body. Required fields: `name` and `datasets` (array; each entry must have a `spec` starting with `table:`, `path:`, `url:` or `redis:`, or being `mariadb:server/table`). Returns `201 { id, created: 1 }`. - `GET /api/analyses/:id` Returns the full analysis document. - `PUT /api/analyses/:id` Full replacement of an analysis document. - `PATCH /api/analyses/:id` Partial update; merges the supplied top-level keys. `id` and `created` are immutable. The `layout` key places the visuals on the canvas grid: an array of `{ visual, x, y, w, h }`, where `visual` indexes the `visuals` array and `x`, `y`, `w` and `h` are 1-based grid columns and rows (the grid is 12 columns wide, `x + w - 1` cannot exceed 12, and one visual may span at most 6 rows). Positions are stored as given - a gap is a gap - but two visuals may not share a cell, so an overlapping set is rejected with `400` and the stored layout is left alone. - `POST /api/analyses/:id/visuals` Appends one visual to a saved canvas. Body: `{ kind, encode, options, title, query }`, with `kind` one of `table`, `bar`, `pie`, `line`, `graph`, `scatter`, `heatmap`, `stacked`, `combo`, `treemap`, `histogram`, `wordcloud`, `map` or `text`. A `text` card reads no data, and its `options` are `text` (required, up to 20,000 characters), `font` (`arial`, `georgia`, `times`, `verdana`, `trebuchet` or `courier`), `size` (8 to 96 points), `colour` and `background` (`#rrggbb`; leaving out `colour` follows the theme), `align` (`left`, `center` or `right`), `bold`, `italic` and `plain`. The text is shown as plain text, never as markup. A map's `encode` names either `loc` (a place column to geocode) or `lat` and `lon`. A `wordcloud` card's `encode` names the text column to read in `col`, and its `options` take the same `topn`, `minlen` and `stop` the `GET /wordcloud` page takes, so the two count and rank words with one function. A `scatter` card's `encode` names `x` and `y`, and optionally `series` (colour by value) and `size` (bubble size); its `options` take `scheme`, `min_r` and `max_r` as `GET /scatter` does, except that an unknown `scheme` draws the default colours. A `line` or `graph` card's `encode` takes an optional `series`, one line per value. A `histogram` card's `options` take `bins` and `scheme` as `GET /histogram` does. A `treemap` card's `encode` takes an optional `color`, with `options` `color_agg` and `color_scheme` as `GET /treemap`'s `color=`, `color_agg=` and `color_scheme=`; an unknown function is the card's error. A `combo` card's `options` take `scheme` as `GET /combo` does. The optional `query` is the canvas page's query string: `cf` (cross-filter), `c` (calculated columns) and `p.` (bound parameter values) are read from it, so a card added while the canvas is filtered or parameterised is drawn through the same state as the cards already on screen. The visual is rendered against the canvas's datasets, joins and calculated fields before it is stored, so an unknown column or an undrawable visual is rejected with `400` and nothing is appended. Returns `201 { id, visual, kind, title, html, head, nonce, cf_col }`, where `visual` is the new visual's index, `head` is any stylesheet and script the page must add once to draw the card (Leaflet, for a map), `nonce` is the Content-Security-Policy nonce the scripts in `html` carry (the canvas page runs only those, giving each its own nonce), and `cf_col` is the column the card's marks cross-filter by. The card is left unplaced in `layout`; it is auto-placed after the saved cards until the reader moves or resizes it. A `kind` that aggregates a value column -- `bar`, `pie`, `stacked`, `treemap`, `heatmap` and `combo` -- reads an optional `encode.agg`: the function its value column is aggregated with. It takes `sum` (the default), `avg`, `min`, `max`, `count_distinct`, `median`, `stdev`, `first`, `last`, `range`, or a percentile named `p` from `p1` to `p100` \-- the same vocabulary and the same spelling a pivot's `m=` spec uses, so a card can show a median, a spread or a percentile where the chart pages it came from could only sum. `count` is accepted as well: it counts rows and ignores the value column. The function is checked whatever the kind is, so a misspelt one is a `400` rather than a chart drawn some other way, as is a function that needs a column (`median`, unlike `count`) when the card names no value column or names the value menu's "Count (no column needed)". A combo aggregates both its series with the one function. A card's derived title says which function it used (`median(amount) by region`); a plain sum keeps the title it has always had. A card may bring a cross-row plan of its own in `cross_row`, 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 of a canvas can show a running total while the cards beside it show raw rows, and `{ cs => [] }` is a card that runs no cross-row pass at all -- which is how a card opts out of the page's. A card that sends no `cross_row` inherits the page's plan. The plan is checked against the canvas's columns on every render, not only here, so a saved spec that no longer names a column is that card's error while a bad page-level `cs` is the page's `400`. - `DELETE /api/analyses/:id/visuals/:visual` Removes a text card; any other kind answers `400`. Later cards move down one index, and the layout and any `cv=` saved in a bookmark move with them. - `PATCH /api/analyses/:id/visuals/:visual` Changes the columns of one stored visual without removing it. Body: `{ encode, query }`; `encode` replaces the visual's encoding in place (for a map, `{ loc }` or `{ lat, lon }`), and the optional `query` is the canvas page's query string, read for `cf`, `c` and `p.` so the redrawn card matches the page it is on. The visual keeps its index, so its layout 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. Returns `{ id, visual, kind, title, html, head, nonce, cf_col }`; `nonce` is the one the scripts in `html` carry, which the canvas page gives its own when it runs them. - `DELETE /api/analyses/:id` Removes the analysis. Returns `200 { deleted: 1 }`. - `GET /api/analyses/:id/export` Downloads the analysis document as a `.json` file. - `POST /api/analyses/import` Creates an analysis from an uploaded `.json` file (multipart `file` field) or a raw JSON request body. Caller-supplied `id`, `created`, and `updated` are stripped. "Import canvas", on `/canvases`, sends the file that a canvas's Share panel downloads; that panel also makes the read-only `/share/:id` link (`POST /api/analyses/:id/share`) and its `/embed/:id` code. - `GET /calc/list` Returns `{ fields: { name: expr } }` for stored virtual calc fields for a given spec. Accepts `?l=`. - `POST /calc/save` Saves a named calc field to `.bi/models.json`. Body: `l=&name=&expr=`. Returns `{ saved: 1 }` or `{ error: "..." }` (400 for hostile input). - `POST /calc/delete` Removes a named calc field. Body: `l=&name=`. - `GET /groups/list` Returns `{ groups: { label: [vals] } }` for named value groups for a column. Accepts `?l=&col=`. - `POST /groups/save` Saves a named value group. Body: `l=&col=&group=&values=` (values is a newline-separated list of raw cell values). The group column, `_group`, appears in the table page as well as in its charts and exports. - `POST /groups/delete` Removes a named value group. Body: `l=&col=&group=`. - `GET /dates/list` Returns `{ patterns: { col: pattern } }` for stored date-normalisation patterns. Accepts `?l=`. A column with only a date pattern is reported as the pattern string; one with a time part as well is reported as `{ date, time }`, plus `as` when the normalised value is filed under a name of its own. - `POST /dates/save` Saves a date-normalisation pattern for a column. Body: `l=&col=&pattern=`, plus optional `time=` and `as=`. Supported patterns: `%m/%d/%Y` (US), `%d/%m/%Y` (EU), `%d-%b-%Y` (DD-Mon-YYYY). With `time=` (`%H:%M` or `%H:%M:%S`) the column is normalised to a timestamp, `2026-10-27T14:05:00`, which is what `epoch()`, `span()` and a `last_24_hours` range can read; `as=` names the column the normalised value is written to (default: the column itself). Without a time part the stored value is the date pattern string, unchanged, so no existing store needs migrating. - `POST /dates/delete` Removes a date-normalisation pattern. Body: `l=&col=`. Omit `col=` to clear all patterns for the spec. - `GET /lookups/list` Returns `{ lookups: { name: { file, key, col, fields, default } } }` for the stored lookups of `?l=`; `col`, `fields` and `default` appear only when they were given. See ["Stored lookups"](#stored-lookups). - `POST /lookups/save` Saves a lookup. Body: `l=&name=&file=&key=`, plus optional `col=`, `fields=` and `default=`. The file is read when it is saved, so a bad one is a 400 at once. - `POST /lookups/delete` Removes a lookup. Body: `l=&name=`. Omit `name=` to remove every lookup of the spec. - `GET /extract/list` Returns `{ extractions: { name: { col, pattern, fields } } }` for the stored field extractions of `?l=`. See ["Field extraction"](#field-extraction). - `POST /extract/save` Saves an extraction. Body: `l=&name=&col=&pattern=`, plus optional `fields=`. The pattern is compiled when it is saved, so a bad one is a 400 at once. The reply names the columns it will add: `{ ok: 1, fields: [...] }`. - `POST /extract/delete` Removes an extraction. Body: `l=&name=`. Omit `name=` to remove every extraction of the spec. - `GET /geo/list` Returns `{ columns: { col: { kind: "lat"|"lon"|"location" } } }` for the saved coordinate-column preferences of the given `l=` spec. - `POST /geo/save` Saves a coordinate-column preference. Body: `l=&col=&kind=`, where `kind` is `lat`, `lon`, or `location`. `col=` and `kind=` may repeat in pairs, so a latitude and a longitude are saved in one request, and `replace=1` forgets the spec's saved columns first. The map panel's "Remember columns" sends both, so a new choice replaces the old one; its "Forget remembered columns" calls `/geo/delete`. - `POST /geo/delete` Removes a coordinate-column preference. Body: `l=&col=`. Omit `col=` to clear all preferences for the spec. - `POST /geo/lookup` Looks up one place for the map page's progress bar, and saves the answer in the shared place cache at once. Body: `place=&sig=`, where `sig` is the signature the map page gave the place (any other place is refused with 403), plus `online=0` to keep the lookup on this machine. Returns `{ place, status, stored, online_failed, messages }`, where `status` is `found`, `not_found`, `held` (not looked up now, and not remembered) or `refused`. See ["Maps"](#maps). - `GET /insights` Automatic statistical insights for a dataset. Runs the same pipeline as `/join` then detects outliers (Tukey IQR fence), period-over-period deltas, top movers, and seasonality (lag-1 autocorrelation). No LLM or network call is made. Every insight links to a chart or stats view that shows it. Parameters: `l=` (required), repeatable `f=` filters. - `POST /api/ask` Natural language to analysis spec (Phase 5.1). Body params: `l=` (required), `q=` (required), repeatable `f=` filters. Returns `{ spec, url, valid => 1 }` on success. The model receives only column names and types -- never raw row data or file paths. Returns 503 when `ai_api_key` is not configured; 400 for missing params or a question over 500 characters; 422 for an unparseable or invalid-column spec; 502 for a provider error; 429 once the hour's provider calls are used up (`ai_calls_per_hour`, below). Every call spends `ai_api_key`, so each server process makes at most `ai_calls_per_hour` provider calls (default 60; `0` for no limit) in any hour, counting `/api/ask` and `/api/summary` together. Like the other routes, both need the login when `password_hash` is set. - `POST /api/summary` Narrative summary of an analysis (Phase 5.3). Body params: `l=` (required), repeatable `f=` filters, `ai=1` (optional, honours `ai_api_key` config). Returns `{ text, source, cached }`. Two paths: deterministic template sentences (always available, works offline) and optional LLM-polished prose when `ai=1` is passed and `ai_api_key` is set. Only aggregate insight facts are sent to the LLM. Results are cached in-process for one hour keyed on the analysis fingerprint. Once the hour's provider calls are used up (`ai_calls_per_hour`), `ai=1` gives the template text with `source => 'template_limited'`, not cached. ### Breadcrumbs and Missing Pages Every page shows where it is: Home, then each page between Home and this one, then this page, which is not a link. The trail is read from the page's own URL -- a chart's `back=`, drilled rows' `back2=`, a file's folder -- and each step is named from its own URL ("Bar chart: amount by region", "sales (region = North)"). Text from the URL is shown only if it is short and has no markup, a `back=` to another site or scheme is not followed, and a chain of `back=` links stops after eight steps. An address that matches no route, a missing table (`/view/:table`) and a missing canvas (`/a/:id`) answer `404` with an HTML page that keeps the breadcrumb and the site navigation. The JSON API (`/api/...`) still answers a `404` with a JSON body. A request that is refused -- a chart of a column the view does not have, a calculated field that does not compile -- answers `400` with the reason as plain text. When a browser asked for the page (a `GET` whose `Accept` names `text/html`), the reason is shown on the same page as a missing one instead, with the breadcrumb back to the table and a Back button. Scripts, `fetch()` and `/api/...` routes still get plain text or JSON. ### Saved Columns That Cannot Be Applied Calculated fields, lookups and extractions are saved for a dataset and applied to every page of it. When the file changes under one -- a lookup file is deleted, a column an extraction reads is dropped -- its charts and exports refuse with the reason. The table page (`/view`, `/open`, `/join`, `/combine`) is still drawn: without that dataset's saved calculated fields, lookups and extractions, with a note that names the one at fault and buttons that open the panels to correct or delete it. A mistake in the request's own `c=` is still a `400`. ### Relative Time Ranges Every data route accepts a named window in place of a pair of dates: ``` GET /view/sales?range=last_30_days&rcol=sale_date GET /a/mycanvas?range=this_quarter&rcol=order_date ``` `range=` names the window and `rcol=` names the column it applies to. The window is worked out when the request arrives, so a link, a bookmark or a saved canvas keeps meaning the same thing whenever it is opened rather than the dates it happened to resolve to when it was written. The vocabulary is ``` last_24_hours the 24 hours ending now, to the second today, yesterday last_7_days, last_30_days, last_90_days including today this_month, last_month this_quarter, last_quarter this_year, last_year ``` A `this` period ends today; the completed one is `last_month`, `last_quarter` and so on. The range is applied as two ordinary filters on that column -- `col:ge:lo` and `col:le:hi` -- so the table view, every chart, the pivot, the column statistics, the insights page, the exports and a saved canvas all show the same rows, and one chip in the toolbar names the window with an `x` to remove it. Filters the reader wrote are untouched: the range travels in the URL as itself, so submitting a panel form or reopening a link does not turn it into frozen dates. The table page offers Time range in its filter bar only when one of the columns on the page holds dates or date-times (`YYYY-MM-DD`, the same with a time after `T` or a space, or `M/D/YYYY`) in every non-empty cell, and its column menu lists only those columns. A range already in force stays in the bar, so it can be changed or removed. A window narrower than a day (`last_24_hours`) is compared to the second, so it is exact only on a column that holds a time as well as a date. A day-granular window compared against a column of timestamps covers each day it reaches in full, which is what a date filter does on its own as well. The filter bar offers the same windows: choose one and the column it applies to. Entries whose window falls entirely outside the data are disabled, with the column's own earliest and latest value -- from `GET /api/range` -- in the tooltip, so a window that can only show nothing says so before it is chosen. An unknown `range=` is a 400 rather than a silently unfiltered view, and a range with no `rcol=` is a 400 as well. On a saved canvas the range narrows every card, and a card added while one is active is drawn through it: `query` in `POST /api/analyses/:id/visuals` carries `range=`/`rcol=` as it carries `cf=`. ### Time Buckets The aggregating routes bucket the dimension they already take: ``` GET /bar?l=table:sales&cat=at&val=amount&span=1h GET /pivot?l=table:sales&rows=at&m=amount:sum:total&span=1mon GET /view/sales?span=1d&tcol=at GET /graph?l=table:sales&x=at&y=amount&span=1h&agg=sum&series=region ``` The chart and pivot panels carry a `Time bucket` menu for it, and the line graph panel an `Aggregate` menu beside it and a `Lines by` menu, which sends `series=`. `span=` takes a count and one of `s`, `m`, `h`, `d`, `w`, `mon`, `q` or `y` -- `15m`, `6h`, `2d`, `3mon`, `1q` -- and is refused, naming the interval and the parameter, when the count is zero or the unit is not one of those. It expands into the calculated field a reader could have typed, `_span:span(,'1h')`, so there is one pipeline and one set of semantics; `tcol=` names the column where the route has no single dimension or names more than one, and `as=` names the bucket column. `tcol=` without a `span=`, an unknown column, a name that is already a column and a `span=` that disagrees with the reader's own calculated field are each a 400. Labels are ISO at the bucket's own resolution -- `2026-10-27T14:00:00`, `2026-10-27`, `2026-10-01` -- so they sort chronologically as strings and a bucketed chart comes out in the right order with no second sort. `d`, `w`, `mon`, `q` and `y` are computed from the local calendar, so a daylight-saving day is one bucket and a leap day is its own date; `s`, `m` and `h` floor the interval out of the epoch. A value that cannot be read as a time lands in one visible `(unparseable)` bucket rather than disappearing. `agg=` on `/graph` is the timechart: one point per bucket, aggregated with the measure vocabulary a pivot's `m=` uses (`sum`, `avg`, `min`, `max`, `count`, `count_distinct`, `median`, `stdev`, `first`, `last`, `range`, `p`). Without `agg=` every row is still plotted. The same bucket is available by hand, because it is a calculated field: `?c=hour:span(at,'1h')`. The timestamp functions are `epoch(v[, fmt])`, `hour(v)`, `minute(v)`, `second(v)` and `span(v, interval)`; see [Database::BI::Calc](https://metacpan.org/pod/Database%3A%3ABI%3A%3ACalc) for the shapes each reads and the local-time rule. ### Cross-Row Columns Every data route, `/export` and a saved canvas accept a cross-row plan: a column worked out from many rows and written beside each one. ``` cs=::[:] repeatable by=[,...] partition (optional) order=[:asc|desc] the order the rows are read in ``` `pct_of_total`, `rank` and `zscore` need no order. `running_total`, `running_count`, `running_pct`, `window_sum`, `window_avg`, `lag`, `delta` and `pct_change` need `order=`; only `window_sum` and `window_avg` take the `count`, the width of the window. The pass runs after the filters and before pagination, so page 2 continues page 1's running total. An unknown function or column, or a parameter where none is taken, is a 400. A calculated field can read the new column, as in `c=half:ytd/2`, and `cs=` can read a calculated field or a time bucket, as in `span=1mon&tcol=at&order=at_span&cs=amount:running_total:ytd`. A filter runs before the pass, so a window is over the rows it leaves, unless it names a column the pass writes: `f=ytd:gt:50` runs last, and selects rows without changing their totals. On a canvas, a card's own filters and a cross-filter follow the same rule. The table page's Cross-row panel builds the same parameters. See ["Cross-row columns" in Database::BI::Controller::Dashboard](https://metacpan.org/pod/Database%3A%3ABI%3A%3AController%3A%3ADashboard#Cross-row-columns) for the full rules. ### Stored Lookups A lookup adds columns to every row of a dataset from a second file, matched on one key: a shop code in the orders gains the shop's city and region. ``` POST /lookups/save l=table:orders&name=shops&file=lookups/shops.csv &key=code&col=shop&fields=city,region&default=unknown ``` `file=` is a data file under `data_dir` (a relative path is read from there), of at most 8 MiB; a file outside it, a symlink that leads outside it, a URL and a remote path are refused. `key=` is the lookup file's key column and `col=` the dataset's (default: the same name). `fields=` lists the columns to add (default: all but the key). Keys are compared as text after trimming spaces, and case matters. When the file has a key twice, the first row is used and the log says so. A row whose key is not in the file gets `default=`, or nothing. A field keeps its own name unless the dataset already has a column of that name; it is then called `_` (`shops_region`). A source column is never overwritten. The table page's Lookups panel saves, lists and deletes them, and can skip one in the current view. Stored lookups apply on every route, a canvas and `/export`, before calculated fields and filters, so either can read the new columns. A total grouped or filtered by one is worked out in Perl, never sent to SQL. For one request, `nolk=` skips a stored lookup, and `lk=` applies a lookup stored for another dataset. Both repeat, and the panels carry them on. A lookup is not a join: for more than one key, or rows that match many rows, use Merge. ### Accelerated Datasets A large CSV, TSV, PSV, XLSX or XML file can be read from a SQLite copy of itself, an _extract_, which is faster to page through and lets totals be worked out in SQL. `script/bi-extract --spec table:sales` marks the dataset accelerated and builds the extract under `/extracts/`. An extract is used only while it matches the file: its name is a fingerprint of the spec and the file's modification time and size. Once the file changes, the file itself is read, and the table page says the extract is stale and how to rebuild it. Stale numbers are never shown. The table page also says when a dataset is read from its extract, and when that was built. An extract is a cache. Deleting `extracts/` loses nothing: each accelerated dataset reads its file until the next build. Nothing is written under `data_dir`, and a `model_dir` inside it is refused. Only a `table:` or `path:` spec naming a local file can be accelerated; a SQLite file is one already. `table:sales` and `path:/data/sales.csv` are separate datasets, each accelerated on its own. ### Field Extraction A regular expression can pull values out of a column. For one value, use `rx()` in a calculated field: ``` c=country:rx(ref,'^([A-Z]{2})-','1') ``` To make several columns from one pattern, store an extraction. Each named group, `(?...)`, becomes a column: ``` POST /extract/save l=table:invoices&name=parts&col=ref &pattern=^(?[A-Z]{2})-(?\d{4})- ``` `fields=country` keeps only the groups it names. The table page's Extract fields panel saves, lists and deletes them. Stored extractions apply on every route, a canvas and `/export`, after the lookups and before calculated fields and filters, so either can read the new columns. A row the pattern does not match gets `(unparseable)` in each column, as a calculated field's row does, so those rows group visibly instead of vanishing; they are not blank. A group that took no part in a match is empty. Columns are named as a lookup's are: the group's own name, or `_` when the dataset has a column of that name. A capture is cut to 200 characters. A pattern is refused when it is over 200 characters, when it does not compile, or when it uses `(?{`, `(??{`, `(?p` or `(*`, which run code or steer the engine. These checks are not a sandbox. Perl's regular expressions have no timeout, so a pathological pattern such as `(a+)+$` stalls the request that uses it. Patterns are entered by whoever runs the server, never by a remote party. See ["Regular expressions" in Database::BI::Calc](https://metacpan.org/pod/Database%3A%3ABI%3A%3ACalc#Regular-expressions). ## Configuration Place a `database_bi.conf` file in the application root to override defaults: ```perl { data_dir => 'data', # directory scanned for data files on the home page uploads_dir => '.uploads', # staging directory for uploaded files platform => 'web', # VWF template dimension language => 'en', # VWF template dimension # Browser-side libraries. d3_url overrides the chart and canvas # script; the leaflet_* keys override the map assets, and tile_url # overrides the tile server. See "Self-hosting D3 and Leaflet" # below. d3_url => 'https://cdnjs.cloudflare.com/ajax/libs/d3/7.9.0/d3.min.js', } ``` `uploads_dir` is where `POST /upload` stages the files a user drags onto the page, and the directory `POST /uploads/clear` empties. It defaults to `.uploads` under the application home. A relative value resolves against the application home, an absolute one is used as given, so a deployment whose application directory is read-only can stage uploads somewhere writable: ```perl { uploads_dir => '/var/tmp/database-bi-uploads' } ``` ### Who Can Reach the Server A server started by `script/database-bi` (with no arguments, a database, or `daemon` or `prefork`) listens on `http://127.0.0.1:3000`, so only this machine can reach it: unless a password is set (see ["Logging in"](#logging-in)), there is no login, and Mojolicious on its own would listen on every interface. `listen` changes that: ```perl { listen => 'http://127.0.0.1:8000' } # another port, still this machine only { listen => 'http://*:3000' } # every machine that can reach this one { listen => ['http://127.0.0.1:3000', 'http://[::1]:3000'] } # IPv6 loopback too ``` It takes a location, several separated by commas, or a list. A location that is not `http://` or `https://` stops the start. `MOJO_LISTEN` and the `-l` option of `daemon` and `prefork` still win over it. `morbo script/database-bi` opens its port before it loads the application, so it does not read `listen`; give it `-l`, or run `script/database-bi`. Anywhere but this machine needs a password: a `listen` (or `MOJO_LISTEN`, or `-l`, or under hypnotoad its own `listen`) that is not loopback stops the start unless `password_hash` is set (see ["Logging in"](#logging-in)). The message says what to change. The defaults are loopback, so they start as before. `hypnotoad` reads its own `hypnotoad => { listen }`, which defaults to `http://127.0.0.1:8080`, this machine only: ```perl { hypnotoad => { listen => ['http://*:8080'] } } # every machine ``` Behind a reverse proxy on the same machine, leave both at their defaults. The proxy can still bring other machines' requests in, so without a password a request from an address that is not loopback is refused (`403`), including one a trusted proxy names in `X-Forwarded-For`. For the public, set a password. **This machine is not one person.** Loopback keeps other machines out, not other people on this one: any account on the same machine, or a container that shares its network, can reach `127.0.0.1:3000`, and read, list and save files as the account running the server. On a machine with other users, set a password even when it listens on this machine only. ### Which Folders It May Use `/browse`, `/open`, the Export panel's folder list (`/api/dirs`), a `path:` data source anywhere (a table, a chart, a merge, a canvas card) and `POST /export` reach the whole filesystem, as far as the account running the server can. `file_roots` fences them in: ```perl { file_roots => ['/srv/reports', 'shared'] } # 'shared' is under the application home ``` `data_dir` and `uploads_dir` are always among them. Browsing starts at the home folder if it is inside, else at the first root, and stops at the roots. A folder or file outside is refused (`403`, or a `path:` source is not found), whether or not it is there, so the refusal says nothing about what is outside; a link inside that leads outside is outside. A path that climbs out with `..` past what exists is refused too. A root that is not a folder that exists stops the start. Remote files (`/../host/...`), Redis and MariaDB are not files on this machine, and are not affected; neither are the command-line tools. Saving is the riskier half: an export replaces a `.csv`, `.json` or `.sql` file of the same name, and some such files are settings another program obeys (a project's `package.json` names commands that `npm` runs). A server other people use keeps exports to folders of their own, so with `password_hash` set, `export_dirs` is required, and the start stops without it: ```perl { export_dirs => ['/srv/reports/exports'] } # a relative one is under the application home ``` `POST /export` then saves only in them (or below them), and the Export panel opens on the first and lists no other. Reading is unaffected: that is `file_roots`. An export folder that does not exist, is outside `file_roots` or is a dot-folder stops the start. Whatever is set, an export never replaces a link, a folder or anything else that is not a plain file, so a link inside the allowed folders cannot carry it outside them; it is written beside its name and renamed into place, so nobody reads half a file, and a file it replaces keeps its permissions. ### Requests From Other Sites Unless a password is set (see ["Logging in"](#logging-in)), there is no login: whatever can reach the server can read any file it can (`/open`) and write exports into any folder it can (`POST /export`). Even with one, a logged-in reader's browser carries the login to every request it sends here. Listening on this machine only keeps other machines out, but not the pages of other sites open in the reader's browser. So every request is checked first, and refused with `403` when another site's page made it: - A write (any method but `GET`, `HEAD` and `OPTIONS`) is refused when the browser's `Sec-Fetch-Site` says it came from another site, or, without that header, when its `Origin` is not this server. A form posted from another site needs no permission from this one, so this is what stops it. A script such as `curl` sends neither header and is let through. - Any request is refused when its `Host` is a name other than `localhost` or `*.localhost`, and is not an IP address. A page whose own name has been made to resolve to `127.0.0.1` (DNS rebinding) counts as this server to the browser, but it still sends its own name. To reach the server by a name, list it: ```perl { allowed_hosts => ['bi.example.com', '*.corp.example'] } # '*' for any name ``` Behind a trusted reverse proxy, the `Host` is whatever the proxy sends, so it is checked only when `allowed_hosts` is set; set it there too. `POST /export` also refuses a folder or file whose name starts with a dot (`~/.config`, `.vscode/settings.json`), since those hold other programs' settings. The folder list in the Export panel leaves them out already. ### Which Addresses It May Fetch `/import` and a `url:` data source make the server fetch a URL, so a page could point it at what only the server can reach: a service on this machine, a database on the office network, or a cloud's metadata service on `169.254.169.254`, which hands out its credentials. So the server refuses a URL that leads to this machine, a private or shared network (`10.0.0.0/8`, `172.16.0.0/12`, `192.168.0.0/16`, `100.64.0.0/10`, `fc00::/7`), link-local addresses, multicast or a reserved range. It is what the name _resolves to_ that counts, not how the URL reads: `http://localtest.me/` and `http://127.0.0.1.nip.io/` are names whose DNS answers `127.0.0.1`, and `http://2130706433/` or `http://0x7f000001/` are `127.0.0.1` written another way; all are refused. An address is looked up once, checked, and that address is the one connected to, so a name that answers differently the second time (DNS rebinding) gains nothing. Each redirect is checked the same way, and at most 5 are followed. Over `https` the certificate must still be the site's. The refusal says why, for example `localtest.me is 127.0.0.1, which is this machine`. To read from an intranet site, name it: ```perl { url_private_hosts => ['reports.corp.example', '*.corp.example'] } ``` Those names may resolve to a private address; every other name is still checked, and an address written as such (`http://10.1.2.3/`) is never let through. A name that is not a host name, or `*` alone, stops the start. This check is not a firewall: where the server's machine can reach something it should not, rules on outgoing connections are the stronger defence. ### Logging In With `password_hash` set, every page asks for a password first. A server other machines can reach must have one (see ["Who can reach the server"](#who-can-reach-the-server)); on this machine alone it is optional. Make the line with ``` script/database-bi password ``` which asks for the password twice (without showing it) and prints ```perl password_hash => 'pbkdf2-sha256$200000$...', ``` to put in `database_bi.conf`. Only the hash is kept: PBKDF2-HMAC-SHA256 with a random salt ([Database::BI::Password](https://metacpan.org/pod/Database%3A%3ABI%3A%3APassword)). A plain password there stops the start, so it is never left in the file by mistake. To change the password, make a new line; every session is then logged out. There is one password, not one per person. A browser asking for a page is sent to `/login` and, once the password is right, back to that page; any other request (a script, a panel's request, a download) gets `401` until it carries a logged-in session. `POST /logout`, the Log out button at the top of each page, ends it. `/share/:id` and `/embed/:id` need no login: their signed, expiring token is their permission, so anyone holding the link sees that canvas, and only that one, for 30 days. The canvas itself (`/a/:id`) and the rest still ask for the password. The token is signed with `share_secret` when it is set (at least 16 characters), else with a key derived from `secrets`. Without either, the key is new at each start, so a restart ends every share link, and the Share panel says so; set `secrets` (which keeps logins too) or `share_secret` to keep them. Changing either ends the links made with the old one. Before 0.021.0 a missing `share_secret` meant a fixed key, the same on every install, with which anyone could sign a link to any canvas; that key in `share_secret` stops the start. ```perl { password_hash => 'pbkdf2-sha256$200000$...', export_dirs => ['/srv/reports/exports'], # required with a password login_expiration => 8 * 60 * 60, # seconds idle before logging out; the default secrets => ['a long random string'], # or each restart logs everyone out } ``` The session is a cookie signed with `secrets`: `HttpOnly`, `SameSite=Lax`, and `Secure` whenever the request came over https (directly, or through a trusted proxy that says so), so it is never sent back over plain http. Over plain http anyone on the network path can read the password and the cookie: a server other machines reach should be behind https. Ten wrong passwords from one address within 15 minutes lock that address out for the rest of the 15 minutes (`429`); each server process counts its own. ### MariaDB and MySQL Servers A server is opened by name. Each entry of `mariadb_servers` is one database: ```perl { mariadb_servers => { shop => { host => 'db.example.com', # default: localhost port => 3306, # optional database => 'shop', # required username => 'bi_reader', password => 'secret', }, }, } ``` The home page then has a card for each name, leading to the list of its tables. `mariadb:shop/orders` opens one anywhere a source is taken: the path field, `l=`, `j=`, `c=` and a canvas's `datasets`. `mariadb:shop` lists them. The spec holds a name and no more, because a spec travels in URLs, bookmarks, shared links, saved canvases and server logs. So the password stays in the configuration file, which should be readable only by the account that runs the application, and no request can point the application at another server. [DBD::MariaDB](https://metacpan.org/pod/DBD%3A%3AMariaDB) is recommended; [DBD::mysql](https://metacpan.org/pod/DBD%3A%3Amysql) serves if it is the one installed. Without either, opening a server says so and the rest of the application is unaffected. Rows are read from the server each time, and never cached. The tests are `t/mariadb.t`, which mocks the server, and `t/mariadb_live.t`, which runs when `MARIADB_SERVER` is set, for example to `mariadb://user:pass@host/test`, and which creates its own tables and drops them. ### Compression and the Page Cache Every response goes through [Mojolicious::Plugin::Buffer](https://metacpan.org/pod/Mojolicious%3A%3APlugin%3A%3ABuffer): it is compressed (gzip or Brotli, as the browser accepts), HTML whitespace is minified (inline scripts and styles are left alone), and a `200` to `GET` or `HEAD` carries an `ETag`, so a repeat request can be answered `304 Not Modified`. There is no `Last-Modified`: the plugin would date a page by the code, not the data, which lets a browser reuse a stale reply without asking. File downloads (`/export` as CSV, SQLite or JSON, the pivot as XLSX, and a canvas card's data from `/a/:id/visual/:n/:fmt`) opt out and are sent as they are. Two optional keys keep finished pages on disc: ```perl { page_cache_dir => '/var/cache/database-bi', # relative: under the application home page_cache_duration => '5 minutes', # the default } ``` When `page_cache_dir` is set, `/catalog` and `/tutorial` are stored in a [CHI](https://metacpan.org/pod/CHI) File cache there, one copy per `Accept-Language`, and served from it until they expire. No other page is cached, because the rest read data files that can change between requests. Without the key nothing is cached. ### Self-Hosting D3 and Leaflet The chart pages load D3 from a public CDN by default, and the map pages load Leaflet and leaflet.markercluster from unpkg. A deployment with no outbound network access, or one that prefers to serve its own assets, sets the URLs in `database_bi.conf`: ```perl { d3_url => '/static/d3/d3.min.js', leaflet_js_url => '/static/leaflet/leaflet.js', leaflet_css_url => '/static/leaflet/leaflet.css', leaflet_cluster_js_url => '/static/leaflet/leaflet.markercluster.js', leaflet_cluster_css_url => '/static/leaflet/MarkerCluster.css', leaflet_cluster_default_css_url => '/static/leaflet/MarkerCluster.Default.css', tile_url => 'https://tiles.internal/{z}/{x}/{y}.png', } ``` An absolute path is served like any other static file: put the library under the application's `public/` directory, so `/static/d3/d3.min.js` is `public/static/d3/d3.min.js`. The five Leaflet keys may be set on their own; any left out keeps [HTML::OSM](https://metacpan.org/pod/HTML%3A%3AOSM)'s pinned CDN URL. Use a path beginning with `/`, or a full URL; a path without the leading slash is relative to the page. The versions to copy are the ones the defaults name: D3 7.9.0, Leaflet 1.9.4, and leaflet.markercluster 1.5.3. `tile_url` points the map layer at a self-hosted tile server instead of OpenStreetMap. It keeps Leaflet's `{z}`, `{x}` and `{y}` placeholders, and `{s}` too if the server uses subdomains. Set it, and `map_geocode_online` to 0, and every request needed to draw a map can stay on the local network. The OpenStreetMap attribution stays on the map, since it is the usual credit for OSM-derived tiles. There is no in-page place search: [HTML::OSM](https://metacpan.org/pod/HTML%3A%3AOSM) is always constructed with `search => 0`, so nothing typed on a map page is sent anywhere. Map-specific config keys: `map_geocode_max` (new geocoder lookups per request, default 500), `map_geocode_online` (0 keeps geocoding on this machine: a place the local data does not know is not sent to OpenStreetMap's Nominatim; default 1), `map_geocode_online_max` (places asked of Nominatim per request, default 20; at one a second, the rest are left for the next request or for `script/bi-geocode`; the map page's progress bar asks one place a request, so it reaches every place), `secrets` (a list; the first signs the places a map page lists for `POST /geo/lookup`. Without it each start makes a random one), `map_max_markers` (markers per request, default 2000), `map_popup_rows` (rows listed in one marker's popup, default 10), `map_region` (a two-letter country hint for ambiguous places), `map_max_zoom` (the default tile-layer maxZoom) and `tile_sources` (a hashref of named tile sources, each `{ url => ... }`, selected with `tiles=`). **Map colour rules.** `color=` names the column and `color_rule=` supplies `::` rules; they are tried in the order given and the first match wins. The operators are `gt`, `ge`, `lt`, `le`, `eq` and `ne`; a comparison is numeric when both the cell and the rule's value look like numbers, and otherwise only `eq`/`ne` are meaningful (case-insensitive). A colour is `#rgb`, `#rrggbb` or a CSS name such as `red` or `teal`. A value that matches no rule gets `color_default=`, which the legend shows as "Other". `series=` is the categorical alternative: each distinct value takes the next colour from `scheme=`, cycling through the palette, and the legend lists the categories. `color=` and `series=` are mutually exclusive. ### Content-Security-Policy Every HTML page is sent with a `Content-Security-Policy` header. The pages show cells from files this application did not write, so the policy backs up the escaping. Scripts, stylesheets and images may come only from this site and from the hosts that `d3_url`, the `leaflet_*_url` keys, `tile_url` and `tile_sources` name. The defaults allow cdnjs.cloudflare.com, unpkg.com and OpenStreetMap's tile servers. A page's scripts may talk only to this site (`connect-src 'self'`), and no other site may show a page in a frame (`frame-ancestors 'self'`). JSON replies and downloads carry no policy. Every HTML page also carries `X-Frame-Options: SAMEORIGIN`, whatever the policy keys below say. A report-only policy only reports `frame-ancestors`, a policy written out in full may leave it out, and older browsers read only this header, so framing by another site is refused in each of those cases. Every reply carries `Referrer-Policy: strict-origin-when-cross-origin`. A request a page starts sends its whole URL to this site, only the origin to another, and nothing from https to http. Specs, filter values and share tokens travel in the query string, and an older browser's default would send all of it to the CDNs, the tile servers and any site a reader follows a link to. It is not `no-referrer`, because OpenStreetMap's tile policy asks pages for a `Referer`. Every reply also carries `X-Content-Type-Options: nosniff`, so a browser takes it for what its `Content-Type` says and never guesses. A guess could read a CSV export, or text from a data file, as HTML or script. A self-hosted library under `public/` keeps working, because Mojolicious names `.js` and `.css` files for what they are; one served some other way must be sent as `text/javascript` or `text/css`, or the browser will not load it. Every page carries `Cross-Origin-Opener-Policy: same-origin`: a window that another site opened, or that a page opened on another site, gets no handle on the page through `window.opener`. No page opens a window or reads an opener, and every link to a new window is `rel="noopener"`. `cross_origin_opener_policy` sets another of `same-origin`, `same-origin-allow-popups`, `noopener-allow-popups` and `unsafe-none`, or `''` or `0` for none; any other value stops the application at startup. No `Cross-Origin-Embedder-Policy` is sent: it would refuse the libraries and tiles the CDNs and OpenStreetMap serve, and nothing here needs the isolation it gives. Every reply carries a `Permissions-Policy` that turns off the browser features no page uses: the camera, the microphone, location, payment, USB, serial and HID devices, screen capture, full screen, reading the clipboard and the rest, for this site and any frame it holds. Writing to the clipboard stays (`clipboard-write=(self)`), for the Copy link buttons. A script that got in cannot ask the reader for any of them. `permissions_policy` replaces the value (`''` or `0` for none); one that cannot be a header value stops the application at startup: ```perl { permissions_policy => 'camera=(), geolocation=(self)' } ``` A reply to a request that came over https carries `Strict-Transport-Security: max-age=31536000`, so the browser comes back over https for a year. It binds this host only; set `strict_transport_security` to add `includeSubDomains` or `preload`, which reach every site of the domain and are hard to take back, or to `''` or `0` to send none. A value a browser would not read stops the application at startup: ```perl { strict_transport_security => 'max-age=63072000; includeSubDomains' } ``` Over http no such header is sent, since a browser ignores it there, and none is sent to `localhost` or to an IP address: it binds the host, not the port, so from `https://localhost:8443` it would send every other server on that machine to https, and browsers ignore it for an address. Behind a proxy that ends TLS, a request is https only when Mojolicious is told to trust the proxy (`MOJO_REVERSE_PROXY=1`, or `hypnotoad => { proxy => 1 }`) and the proxy sends `X-Forwarded-Proto: https`; otherwise the proxy must send the header itself. An inline script runs only if it carries the request's nonce, a random value made for each request and named in the policy (`'nonce-...'`). Every `