> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coreflux.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Metabase

> Client-mode REST API connector for Metabase: run saved questions and get their rows as JSON for MQTT displays, run ad-hoc native SQL or MBQL queries, and list databases, questions and dashboards, authenticated with a Metabase API key

Technical Preview

## Overview

A client-mode connector for the [Metabase REST API](https://www.metabase.com/docs/latest/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](/latest/lot-language/routes/rest-api-routes).

> [!NOTE]
>
> **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

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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`:

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{}
```

Reply on `analytics/metabase/health/get/response`: `{"status":"ok"}`.

**Run a saved question for a display** — publish to `analytics/metabase/cards/query/json`:

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.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):

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
[
{"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`:

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{"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`:

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{"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.

```lot theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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](https://www.metabase.com/docs/latest/configuring-metabase/webhooks) 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).

## Related

* [REST API Connectors](/latest/lot-language/routes/rest-api-routes)
* [Metabase API documentation](https://www.metabase.com/docs/latest/api)
* [Metabase API keys](https://www.metabase.com/docs/latest/people-and-groups/api-keys)
* [Metabase webhooks](https://www.metabase.com/docs/latest/configuring-metabase/webhooks)
* [Metabase on GitHub](https://github.com/metabase/metabase)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.