Lagotto BI — Architecture and Technical Overview

7/9/2026 / 11 minutes to read / Tags: lagotto-bi, BI, self-hosted, architecture, MSSQL

Lagotto BI reads from an MSSQL ERP, synchronizes data to an internal MariaDB database every 5–15 minutes, and exposes it through drag&drop configurable dashboards without writing code. The time between “I haven’t connected the ERP yet” and “I have a dashboard with real data” is measured in wizard steps, not weeks of custom development.

Technical Stack

Real Configuration Times

Every step has an integrated test or preview. Nothing is ever saved blind.

From unconnected ERP to dashboard with real data: the order of magnitude is half an hour, not the typical analysis-quote-development-testing cycle of a custom BI project.

Aggregation Engine

The difference that is least visible but weighs most: aggregation happens on the database, not in the browser. A chart with Top 10 + “Other” on a view with hundreds of thousands of rows does not download those rows to the browser to then slice them in JavaScript — the backend executes GROUP BY + SUM with ordering already applied, returning only the rows that will be rendered.

GET /api/data/v_monthly_revenue
    ?_top_n_x=month&_top_n_y=total&_top_n=10&_grouping=customer_region

-- generated by backend, parameterized — never string interpolation
SELECT month, customer_region, SUM(total) AS total
FROM v_monthly_revenue
WHERE year = :year
GROUP BY month, customer_region
ORDER BY SUM(total) DESC

The Pivot widget (PRO) follows the opposite logic when needed: raw data without LIMIT, aggregated client-side by pivottable.js. Verified on 100,576 real rows with € 21,142,336 summed in real time while the user drags fields.

An in-memory cache (TTL 60 seconds, per user) prevents a tab switch or double-click on a filter from regenerating the same query twice.

Widget Catalog

Seven widget types, all configurable from the panel — none require hand-written JSON.

Example: combo chart configuration

{
  "type": "combo",
  "title": "Revenue vs margin",
  "view": "v_monthly_margin",
  "x_axis": "month",
  "series": [
    { "field": "revenue",    "label": "Revenue",  "type": "bar",  "y_axis": "left",  "color": "#4a90d9" },
    { "field": "margin_pct", "label": "Margin %", "type": "line", "y_axis": "right", "color": "#e85d4a" }
  ]
}

This JSON block — generated by the Widget Builder, never hand-written — is all that is needed for a chart that in many BI tools requires a dedicated SQL view plus manual configuration of the second axis.

Actions and drill-through

Every widget can react to a click. The drill-through stack is recursive — a click inside a modal opened by a previous click opens a third level, with breadcrumbs to go back. Configurable from the panel, not from code.

Mapper Builder and Query Manager

Every ERP has a JSON file mapping its tables to Lagotto’s standard schema:

{
  "erp": "custom_erp",
  "tables": {
    "INVOICE_HEADERS": {
      "target_view": "v_monthly_revenue",
      "columns": {
        "DOC_DATE":   { "target": "invoice_date", "type": "date" },
        "CLIENT_CODE":{ "target": "client_code",  "type": "string" },
        "DOC_TOTAL":  { "target": "revenue",       "type": "decimal",
          "display": { "type": "currency", "symbol": "€", "decimals": 0 } }
      }
    }
  }
}

Six predefined transformations cover the cases that normally end up in a hand-written script:

• simple — direct copy
• map — dictionary lookup
• lookup — external table (e.g. province→region)
• coalesce — first non-null value among multiple fields
• concat — concatenation
• arithmetic — sum/subtraction between fields

The display field automatically propagates formatting all the way to the final widget without configuring it a second time.

Query Manager (View Builder, PRO): SQL editor with the panel of staging tables available for that datasource. A view created here is available in the Widget Builder immediately — no backend restart, no deploy.

-- margin by category, calculated in a custom view
CREATE OR REPLACE VIEW v_category_margin AS
SELECT category,
       SUM(revenue) AS total_revenue,
       SUM(cost)    AS total_cost,
       ROUND((SUM(revenue) - SUM(cost)) / NULLIF(SUM(revenue),0) * 100, 1) AS margin_pct
FROM raw_revenue
GROUP BY category

Security and Multi-Tenant Isolation

• Mandatory view whitelist: the data endpoint only accepts views in the explicit whitelist — no arbitrary queries from the frontend
• Always parameterized queries: never string interpolation, even for column names with spaces or accented characters
• Server-side forced agent filter: a user with agent role sees only their own data — the filter is applied in the backend, not bypassable from the frontend
• Schema per tenant: every client has their own MariaDB schema (lagotto_{slug}) — zero shared data, zero cross-tenant queries possible by construction
• Granular per-user profiling: an admin can restrict which dashboards, tabs, widgets and permanent filters a single user sees — filtered server-side

Plans and Licensing

The user limit is verified server-side on every new user creation. The plan is re-verified live against the License Server every 30 minutes — revocations and upgrades propagate without restarting anything. Prices shown to the user are read in real time from the License Server, not a fixed number in the code.

Performance — Numbers, Not Adjectives

Declared Roadmap

Not yet in production:

• Extended Query Manager: materialize a query result into a staging table, not just a view calculated on every read — for heavy joins on millions of rows
• Certified mapper library: export/import a tested mapping schema between different installations of the same ERP
• Full dashboard import/export: layout, widgets and filters as an exportable file, reusable as a starting point for a new client

Technical document — Lagotto BI. Roadmap features are not yet available in production.

Sources

Author: Giantommaso Fogli
Publication Date: 2026-07-09

Rights and attributions

Images, logos and photographs are the property of their respective owners. Used for commentary purposes.