/ 
Database-BI-0.023.0
/ README.md

Name

Database::BI - Web-based Business Intelligence viewer for data files

Version

0.023.0

Description

Database::BI is a self-contained 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:

UTF-8 and Encoding

All file data is returned as Perl character strings. CSV/PSV/TSV files are read by Text::xSV::Slurp or DBD::CSV, 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 (see "Which addresses it may fetch"); Database::Abstraction 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" 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:

# In database_bi.conf (create this file in the same folder as script/):
{ data_dir => '/home/user/data' }

Change the language used for templates:

# In database_bi.conf:
{ data_dir => 'data', language => 'fr', platform => 'web' }
# Then create templates/web/fr/ and put your French .html.tt files there.

American spelling: a browser that asks for English (United States) gets the pages from templates/web/en-us/; set language => 'en-us' to make that the default for every browser that names no other English.

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

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, <col>_span:span(<col>,'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<N>). 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 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=<column>:<function>:<alias>[:<count>]   repeatable
by=<column>[,<column>...]                  partition (optional)
order=<column>[: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 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 <name>_<field> (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=<name> skips a stored lookup, and lk=<name> 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 <model_dir>/extracts/, or in the cache directory set (see "Where caches are kept").

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 its folder 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, (?<name>...), becomes a column:

POST /extract/save  l=table:invoices&name=parts&col=ref
                    &pattern=^(?<country>[A-Z]{2})-(?<year>\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 <name>_<group> 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.

Configuration

Place a database_bi.conf file in the application root to override defaults:

{
    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:

{ uploads_dir => '/var/tmp/database-bi-uploads' }

Where Caches Are Kept

The places OpenStreetMap found for maps, the extracts of accelerated datasets (see "Accelerated datasets"), the page cache when page_cache_dir turns it on, and the table cache when cache => { driver => 'File' } (or FastMmap) asks for one, are kept on disc. Each object that keeps one chooses its directory when it is made (Database::BI::CacheDir), from the first of these that is set:

Caches sharing a directory each keep to their own namespace in it; the extracts go in its database-bi-extracts/ folder. A cache directory inside data_dir is not used for the extracts, which then stay in model_dir: each is a .db file the home page would list. page_cache_dir still decides whether pages are cached at all (see "Compression and the page cache"): a cache directory set only says where. A cache => { root_dir } names the table cache's directory outright. script/bi-geocode and script/bi-extract read the same settings, and database_bi.conf from MOJO_CONFIG or the current folder (bi-extract through the application).

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"), there is no login, and Mojolicious on its own would listen on every interface. listen changes that:

{ 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"). 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:

{ 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:

{ 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:

{ 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"), 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:

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:

{ 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"); 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

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

{
    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:

{
    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 is recommended; DBD::mysql 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: 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:

{
    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 File cache there (or in the cache directory set: see "Where caches are kept"), 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:

{
    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'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 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, nor a marker's position for its county outline; default 1), map_geocode_online_max (places, and outlines, 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_bounds_ranks (the place rank that is a county, by country; see above), 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 <op>:<value>:<colour> 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:

{ 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:

{ 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 <script> the templates write carries it, and so do those that HTML::D3 (0.30 or later) and HTML::OSM (0.14 or later) write, because the application passes it to them. A script that came in with a cell's text cannot know it, so it does not run; nor does an onclick or onerror attribute, which no nonce can cover, so the pages use none. Style attributes are still allowed (style-src 'unsafe-inline').

A canvas card added without a reload arrives as JSON with the nonce its scripts were written with. The page runs only the scripts that carry it, and gives each the page's own nonce. A page kept in the page cache is stored with its header, so the nonce in each still matches when it is served again.

Two keys change the policy:

{
    content_security_policy_report_only => 1,  # report breaches, block nothing
    content_security_policy => "default-src 'self'; script-src 'self' 'nonce-{nonce}'",
}

content_security_policy is the whole policy, sent as written except that {nonce} becomes the request's nonce; without it no inline script runs. Set to '' or 0 it sends no header. A policy written out in full is not updated when the library keys change.

Embedding a Canvas on Another Site

By default only pages of this site may show /embed/:id in a frame. embed_frame_ancestors names other sites that may:

{ embed_frame_ancestors => [ 'https://intranet.example.com', 'https://*.corp.example' ] }

Each entry is an origin: http or https, a host (a * may stand for its first label only) and an optional port, with no path. Anything else stops the application at startup, because a security setting that does not do what it says should not be dropped quietly. The sites are added to that page's frame-ancestors, and to no other page's. With a report-only policy, with none, or with one written out without frame-ancestors, the page carries a second, enforced policy of frame-ancestors alone, so naming one site never lets every site in. A policy written out with its own frame-ancestors is left to say who may. X-Frame-Options: SAMEORIGIN stays: browsers that know frame-ancestors follow it instead, and those that know only the older header refuse the frame. The canvas's Share panel names the sites its embed code works on.

Methods

Startup

Called once by Mojolicious when the application is loaded, by script/database-bi, morbo, hypnotoad or Test::Mojo->new('Database::BI'). It is not called directly.

Api Specification

Input
    $self   # the Database::BI application object

Configuration comes from database_bi.conf in the application home, if it is there (see "CONFIGURATION"), or from the hashref given to Test::Mojo->new('Database::BI', { ... }). Unset keys default to data_dir => 'data', platform => 'web', language => 'en' and the public D3 URL.

Output

Nothing useful is returned. By the time it returns, the application has:

A configuration file that does not parse makes the Config plugin croak, and the application does not start.

Command-Line Tools

The scripts in script/ are installed with the distribution, each with a manual page (man bi-extract, man bi-geocode, man bi-alert, man database-bi). The maintenance scripts use the same data and model directories as the web application, found from the folder they are run in or given with --data-dir and --model-dir, and are safe to run from cron.

Script/Database-Bi Password

Asks for a password (twice, without echo, at a terminal; once from a pipe) and prints the password_hash line for database_bi.conf. See "Logging in".

Script/Bi-Geocode

Warms the shared geocode cache for one column, so later map requests make no lookups:

./script/bi-geocode --spec table:customers --col city \
                    [--text TEXT] [--data-dir DIR] [--model-dir DIR] \
                    [--openaddr PATH] [--region CC] [--dry-run] [--verbose]

--text is the map panel's Add to each place (loc_text=): with --col county --text 'Virginia, USA' it looks up Accomack, Virginia, USA, the place the map then asks for.

It is idempotent: a second run against a warm cache resolves nothing and says so. Exit status is 0 on success, 1 when the column is missing or no rows load, and 2 on bad arguments or an unopenable source.

Script/Bi-Extract

Builds the extracts of accelerated datasets (see "Accelerated datasets"):

./script/bi-extract --spec table:sales [--spec ...]   # accelerate, and build
./script/bi-extract --all                             # rebuild every one
./script/bi-extract --remove --spec table:sales       # stop, and remove its extract
./script/bi-extract --list
                    [--data-dir DIR] [--model-dir DIR]

It prints a line for each extract it builds: the rows, the columns, the file and the time. Exit status is 0 on success, 1 when an extract could not be built (the others still are), and 2 on bad arguments. Run --all from cron after the data files are refreshed.

Script/Bi-Alert

Reports one or more saved analyses, and turns a crossed threshold into a non-zero exit status so cron can act on it:

./script/bi-alert --analysis ID [--analysis ID ...] \
                  [--threshold MEASURE:OP:VALUE] \
                  [--threshold ANALYSIS:MEASURE:OP:VALUE ...] \
                  [--suppress DURATION] [--state-file PATH] [--recovery] \
                  [--notify-cmd COMMAND] \
                  [--data-dir DIR] [--model-dir DIR] [--dry-run]

The digest goes to STDOUT and nothing leaves the machine unless --notify-cmd says where: otherwise pipe it into mail, a webhook or a ticket creator yourself. Exit status is 0 when nothing is announced, 1 when a threshold is crossed and announced, 2 on error, and 3 when a crossing that was announced has ended and --recovery was asked to announce that. --dry-run reports without recording anything against the throttle and without running the delivery command.

Both of those options may be repeated, so one run can watch several analyses and several measures:

./script/bi-alert --analysis abc123 --analysis def456 \
                  --threshold count:gt:1000 \
                  --threshold def456:sum:lt:50 --suppress 1h

A threshold with no analysis prefix belongs to the first --analysis; the ANALYSIS:MEASURE:OP:VALUE form names another of those listed. Each analysis is reported in full, and each (analysis, threshold) pair is a check of its own: it prints its own line, keeps its own entry in the state file, and is throttled and recovered on its own schedule. One crossing never silences, recovers or delivers another. When a run announces both a crossing and a recovery it exits 1, so a threshold that is still crossed is not reported as an all-clear.

--suppress throttles a threshold that stays crossed, so a condition that is still true on the next cron run is not reported as news again: it takes a duration (30s, 15m, 6h, 2d, 1w, or a bare number of seconds), the first crossing is announced, and repeats inside the period are not. The throttle is one small JSON file, alerts-state.json in the model directory unless --state-file names elsewhere, keyed by analysis and threshold and written atomically. It is kept only when --suppress is used or such a file already exists, so with no --suppress the script is as stateless as it has always been and writes nothing.

A run inside the period prints THRESHOLD SUPPRESSED and exits 0: it is not announcing anything, so a cron wrapper keyed on the exit status stays quiet. When the crossing ends the run prints THRESHOLD CLEARED once and drops its entry, which means the next crossing is announced immediately instead of waiting out a period that belonged to an incident that is over. A state file that cannot be read is reported and treated as empty, so a damaged file costs one repeated alert rather than silence, and --dry-run records nothing, so a rehearsal cannot use up the alert a real run depends on.

--recovery announces that ending instead of merely reporting it: the run prints THRESHOLD RECOVERED and exits 3, so a wrapper that mails on any non-zero status mails the all-clear as well as the alert, and a wrapper that should not is a wrapper that does not pass --recovery. The recovery goes through the same state file, which is what makes it possible: an entry is the record of a crossing that was announced, so finding one is what tells the run that an incident has ended rather than that a threshold was never crossed. It is therefore said once per crossing and only for a crossing somebody was told about, which is also why --recovery keeps that file even with no --suppress to throttle. The recovery names both dates -- crossed since and cleared.

--notify-cmd delivers the announcement itself, so a cron line is one command rather than a shell wrapper around this one:

./script/bi-alert --analysis abc123 --threshold count:gt:1000 --suppress 1h \
    --recovery --notify-cmd 'mail -s "$BI_ALERT_SUBJECT" oncall@example.com'

The command runs once per run, through the shell, and only when something is announced: a crossing (exit 1), or its end with --recovery (exit 3). A suppressed repeat, an uncrossed threshold, an error and a --dry-run run it not at all, and twenty checks still send one mail. The whole run's text arrives on standard input, and three variables say what it is -- BI_ALERT_EVENT (crossed if any crossing was announced, otherwise recovery), BI_ALERT_SUBJECT (the verdict line when one thing was announced, otherwise a count such as 2 threshold crossings, 1 recovery) and BI_ALERT_MESSAGE_FILE (the same text in a file, for a command that has to post a body: curl -d @$BI_ALERT_MESSAGE_FILE ...). A delivery that fails is reported on STDERR and leaves the exit status alone, but the announcements are not recorded, so the next run announces them again rather than the alert being lost for the rest of the period.

script/database-bi is the web application launcher: run with no arguments it starts the reloading development server, with the name of one database it does the same and shows that database, with a command word it runs that command, and hypnotoad script/database-bi starts a preforking server (see "SYNOPSIS").

Common Pitfalls

Limitations

Roadmap

Features planned for future releases (post-0.019.0). Items are ordered by priority. Items shipped before this release (XLSX export, the copy-link button, the column statistics panel, folder filter/join propagation, the aggregate/measure layer and the between and in filter operators) are no longer listed.

See Also

Repository

https://github.com/nigelhorne/Database-BI

Support

This module is provided as-is without any warranty.

Author

Nigel Horne <njh@nigelhorne.com>

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms. If you use it, please let me know.