Metabase

Technical Preview

Overview

A client-mode connector for the Metabase REST API. Use it to push the result of a saved question (OEE per line, scrap by shift, open work orders) onto MQTT for an HMI, andon board or edge display, or to run a short SQL query against any database connected to Metabase when a broker event arrives.

Event	Request	Notes
Health	`GET /api/health`	No auth. `200` `{"status":"ok"}`
GetCurrentUser	`GET /api/user/current`	Checks the API key
ListDatabases	`GET /api/database`	Database ids for DatasetQuery
ListCards	`GET /api/card?f={filter}`	Saved questions, models and metrics (`f`: `all`, `mine`, `bookmarked`, `archived`)
GetCard	`GET /api/card/{card_id}`	Query definition and template tags
QueryCard	`POST /api/card/{card_id}/query`	Full result: `status`, `row_count`, `data.cols`, `data.rows`
QueryCardJson	`POST /api/card/{card_id}/query/json`	Rows as a JSON array of objects, ready for a display
DatasetQuery	`POST /api/dataset`	Ad-hoc native SQL or MBQL query
ListDashboards	`GET /api/dashboard?f={filter}`	`f`: `all`, `mine`, `archived`
GetDashboard	`GET /api/dashboard/{dashboard_id}`	Dashboard cards and parameters

Set BASE_ADDRESS to your Metabase URL with no trailing slash and no /api, for example https://metabase.example.com or https://yourcompany.metabaseapp.com on Metabase Cloud. Topics use analytics/metabase/<object>/<verb>; replies land on <topic>/response. See the base guide: REST API Connectors.

Auth: create an API key in Admin settings > Settings > Authentication > API keys (Metabase 49 or later) and send it on the x-api-key header. The key is assigned to a group and has only that group’s data and collection permissions, so create a group with access to just the questions and databases the broker needs. Keep the key in Hub secrets (suggested name METABASE_API_KEY) and never commit it into the .lot. Older versions, or setups without API keys, can sign in with POST /api/session ({"username","password"}) and send the returned id on X-Metabase-Session instead (edit the header in the template).

Basic Syntax

DEFINE ROUTE MetabaseRestApi WITH TYPE REST_API
    ADD REST_API_CONFIG
        WITH BASE_ADDRESS "https://YOUR_METABASE_HOST"
        WITH ENABLE_CLIENT "true"
        WITH USE_SSL "true"
        WITH REQUEST_TIMEOUT '120'
        WITH DEFAULT_CONTENT_TYPE "application/json"
        # Smoke: GET /api/health -> 200 {"status":"ok"} (no auth). Auth: x-api-key {value.json.api_key}. Never commit API keys.
    ADD EVENT Health
        WITH SOURCE_TOPIC "analytics/metabase/health/get"
        WITH DESTINATION_TOPIC "analytics/metabase/health/get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/health"
        WITH HEADERS
            ADD "Accept" "application/json"
    ADD EVENT GetCurrentUser
        WITH SOURCE_TOPIC "analytics/metabase/user/current"
        WITH DESTINATION_TOPIC "analytics/metabase/user/current/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/user/current"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
    ADD EVENT ListDatabases
        WITH SOURCE_TOPIC "analytics/metabase/databases/list"
        WITH DESTINATION_TOPIC "analytics/metabase/databases/list/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/database"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
    ADD EVENT ListCards
        WITH SOURCE_TOPIC "analytics/metabase/cards/list"
        WITH DESTINATION_TOPIC "analytics/metabase/cards/list/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/card?f={value.json.filter}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
    ADD EVENT GetCard
        WITH SOURCE_TOPIC "analytics/metabase/cards/get"
        WITH DESTINATION_TOPIC "analytics/metabase/cards/get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/card/{value.json.card_id}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
    ADD EVENT QueryCard
        WITH SOURCE_TOPIC "analytics/metabase/cards/query"
        WITH DESTINATION_TOPIC "analytics/metabase/cards/query/response"
        WITH METHOD "POST"
        WITH ENDPOINT "/api/card/{value.json.card_id}/query"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
            ADD "Content-Type" "application/json"
        WITH BODY "{value.json.body}"
    ADD EVENT QueryCardJson
        WITH SOURCE_TOPIC "analytics/metabase/cards/query/json"
        WITH DESTINATION_TOPIC "analytics/metabase/cards/query/json/response"
        WITH METHOD "POST"
        WITH ENDPOINT "/api/card/{value.json.card_id}/query/json"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
            ADD "Content-Type" "application/json"
        WITH BODY "{value.json.body}"
    ADD EVENT DatasetQuery
        WITH SOURCE_TOPIC "analytics/metabase/dataset/query"
        WITH DESTINATION_TOPIC "analytics/metabase/dataset/query/response"
        WITH METHOD "POST"
        WITH ENDPOINT "/api/dataset"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
            ADD "Content-Type" "application/json"
        WITH BODY "{value.json.body}"
    ADD EVENT ListDashboards
        WITH SOURCE_TOPIC "analytics/metabase/dashboards/list"
        WITH DESTINATION_TOPIC "analytics/metabase/dashboards/list/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/dashboard?f={value.json.filter}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"
    ADD EVENT GetDashboard
        WITH SOURCE_TOPIC "analytics/metabase/dashboards/get"
        WITH DESTINATION_TOPIC "analytics/metabase/dashboards/get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/api/dashboard/{value.json.dashboard_id}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "x-api-key" "{value.json.api_key}"

MQTT examples

Every authenticated event reads api_key from the payload. Query events send body as the JSON request body, so the key is never forwarded to Metabase in the body. Health check — publish anything to analytics/metabase/health/get:

{}

Reply on analytics/metabase/health/get/response: {"status":"ok"}. Run a saved question for a display — publish to analytics/metabase/cards/query/json:

{"api_key": "YOUR_METABASE_API_KEY", "card_id": "40", "body": {"parameters": [], "format_rows": false}}

Reply (tested on v0.63.19.1 with a question on the Sample Database):

[
{"category":"Doohickey","orders":3976,"revenue":297270.99},
{"category":"Gadget","orders":4939,"revenue":406621.25},
{"category":"Gizmo","orders":4784,"revenue":383061.95},
{"category":"Widget","orders":5061,"revenue":423667.5}
]

To filter a question that has a field filter or variable, pass parameters, for example {"type": "category", "target": ["variable", ["template-tag", "line"]], "value": "A"}. format_rows: true returns values formatted as in the Metabase UI (dates, currency). Full result with column metadata — publish the same payload to analytics/metabase/cards/query. Metabase answers 202 with {"status":"completed","row_count":4,"data":{"cols":[...],"rows":[["Doohickey",3976,297270.99], ...]}}. Ad-hoc SQL — publish to analytics/metabase/dataset/query:

{"api_key": "YOUR_METABASE_API_KEY", "body": {"database": 1, "type": "native", "native": {"query": "SELECT CATEGORY, COUNT(*) AS N FROM PRODUCTS GROUP BY CATEGORY"}}}

Reply: 202 with "status":"completed" and data.rows. A failing query returns 400 with "status":"failed" and the database error in error (older Metabase versions answer 202 with "status":"failed", so check status). List saved questions — publish to analytics/metabase/cards/list:

{"api_key": "YOUR_METABASE_API_KEY", "filter": "all"}

Feeding a display

A small action can republish the latest JSON rows on a retained topic for an HMI. Use PUBLISH TOPIC ... RETAIN, not KEEP TOPIC: KEEP TOPIC stores the value inside the broker only, so the HMI would never receive it.

DEFINE ACTION MetabaseOrdersToDisplay
ON TOPIC "analytics/metabase/cards/query/json/response" DO
    PUBLISH TOPIC "display/metabase/orders/rows" WITH PAYLOAD RETAIN

Trigger the question on a schedule with another action that publishes the request payload to analytics/metabase/cards/query/json (build it with GET SECRET "METABASE_API_KEY" rather than hard-coding the key). Restrict who can read analytics/metabase/# with a rule, because request payloads carry the API key.

Alerts and webhooks (companion, not packaged)

Metabase alerts and dashboard subscriptions can send to a webhook destination (Admin settings > Notification channels > Webhook). Receiving them on the broker needs a separate ENABLE_SERVER REST API connector that accepts a POST on a reachable HTTPS address and publishes the alert payload to MQTT. That companion connector is not part of this template.

Notes

/api/card/{id}/query/json is an export endpoint. It returns all rows up to the download row limit, while /api/card/{id}/query and /api/dataset cap results at 2,000 rows. Keep display questions small and aggregated.
Unknown card ids return 404 Not found.; a missing or wrong API key returns 401 Unauthenticated.
Native SQL through /api/dataset needs native query permission on that database for the key’s group.
Metabase API endpoints are not versioned and can change between releases; this template was checked against the OpenAPI spec served by v0.63.19.1 at /api/docs/openapi.json (needs a signed-in user or API key).
Not covered: CSV / XLSX exports (/query/csv, /query/xlsx), creating or editing questions and dashboards, pivot queries, embedding and public links.
Suggested branches: feat/rest-api-route-metabase, feat/docs-rest-api-metabase (Hugo pushes).

Quick Start

Coreflux MQTT Broker

LoT Language

Coreflux HUB

VS Code - LoT Notebooks

Coreflux Portal

Using AI with Coreflux

Product Updates

Overview

Basic Syntax

MQTT examples

Feeding a display

Alerts and webhooks (companion, not packaged)

Notes

​Overview

​Basic Syntax

​MQTT examples

​Feeding a display

​Alerts and webhooks (companion, not packaged)

​Notes

​Related

Overview

Basic Syntax

MQTT examples

Feeding a display

Alerts and webhooks (companion, not packaged)

Notes

Related