Skip to content

Grafana Integration

Grafana connects to the LEAF platform database as leaf_grafana_user. All data access goes through SECURITY DEFINER functions that enforce the same management-based access control as the portal. A Grafana user only sees data their LEAF account has been granted access to.

Account, organisation, and team sync

Section titled “Account, organisation, and team sync”

Grafana accounts, orgs, and teams are never set up by hand - the portal provisions and maintains them automatically via Grafana’s Admin API (grafana_sync.py). This is event-driven, not a scheduled job: a sync runs in the background whenever a user logs in, changes their password, or an admin grants/revokes their access.

LEAF conceptGrafana conceptHow it’s kept in sync
Portal loginGrafana user accountCreated via the Admin API on first login; password is pushed on every login and password change so it always matches the portal password
organisationGrafana orgOne-to-one, matched by name; created if missing
departmentGrafana teamOne team per department, named "{organisation} :: {department}"; membership is fully synced both ways

A few things worth knowing:

  • Org membership is add-only. A Grafana org has no field marking it as LEAF-managed, so there’s no reliable way to tell a LEAF-derived membership apart from one an admin added by hand - removing membership automatically could revoke access to something unrelated. Deleting a LEAF organisation does not delete or touch its Grafana org either.
  • Team membership is fully bidirectional. Teams carry a synthetic marker (an @leaf-dept.local email) identifying them as LEAF-managed, so a user leaving a department is also removed from the matching team - no stale access left behind.
  • Admin-provisioned accounts get a throwaway password. If an admin grants access before the user has ever logged into the portal, the Grafana account is still created immediately, with a random password that’s unusable until the user’s first real portal login overwrites it.
  • User deletion is real. Deleting a user in the portal deletes their Grafana account outright via the Admin API.

This is what backs the ${__user.email} / ${__org.name} variables used throughout the rest of this page - a Grafana viewer’s org and login are always the synced reflection of their LEAF portal account, not a separately managed Grafana identity.

How access control works

Section titled “How access control works”

Every query passes the logged-in Grafana user’s email address via Grafana’s built-in ${__user.email} variable. The database function resolves that email to the user’s management grants and returns only the rows within the user’s authorised organisation, department, entity scope, and time window.

leaf_grafana_user has no readers role membership at all, and starts with zero table/function access by default (see Database Access for why - readers carries broader access than a dashboard needs, including user_account.password_hash). It only has the specific GRANTs below, added one at a time as real dashboards needed them:

GrantPurpose
EXECUTE on leaf_organisations_for_user(email)Org picker / lookups
EXECUTE on leaf_management_for_user(email)Department picker, name->UUID resolution
EXECUTE on leaf_sensor_data_bucketed_by_department(...)The actual time-series data

No direct SELECT on sensor_data, sensor_catalog, or anything else - raw SQL panels cannot bypass the access model, and there’s no broader grant to fall back on if a query needs something not in this list. If a panel needs a function that isn’t here, it needs a new GRANT EXECUTE in deploy.sql first (and a real admin/superuser to apply it in production - leaf_grafana_user’s own privileges can’t self-extend).

Every query on this page is built from only those three grants - none of them need sensor_catalog or a function that isn’t in the table above.

Database connection setup

Section titled “Database connection setup”

This is automatic - no manual Connections -> Add new connection -> PostgreSQL step needed. The same sync that creates a Grafana org provisions a TimescaleDB datasource in it right after, using leaf_grafana_user and the connection details from the portal’s own environment (PGHOST/PGPORT/PGDATABASE, GRAFANA_DB_USER/GRAFANA_DB_PASSWORD). It’s created via the Admin API and usable immediately - there’s no “Save & test” step to click through, and it’s only skipped (silently) if those env vars aren’t set on the portal.

A couple of details worth knowing if you’re troubleshooting a missing or misconfigured datasource:

  • It’s only created once per org, at org-creation time - if a datasource already exists for that org (checked via GET /api/datasources scoped with the X-Grafana-Org-Id header), nothing is touched, so hand-edits in the Grafana UI won’t get silently overwritten on the next sync.
  • sslmode is hardcoded to require.
  • It’s marked as the org’s default datasource (isDefault: true).

The bucketed function - what actually works today

Section titled “The bucketed function - what actually works today”

leaf_sensor_data_bucketed_by_department is the one time-series function leaf_grafana_user is actually granted, and it’s built for exactly this use case: it reads from TimescaleDB continuous aggregates (pre-downsampled at 1min/5min/10min/1hour/1day granularity) rather than live-aggregating over raw sensor_data, so it stays fast at any zoom level without needing a separate “raw vs. bucketed” panel query.

Two dashboard variables cover org/department picking without needing direct table access - leaf_management_for_user returns both the display name and the UUID together, so it doubles as the name->UUID resolver:

-- ${department} variable (organisation comes from Grafana's own org context, see below)
SELECT DISTINCT department_name
FROM leaf_management_for_user('${__user.email}')
WHERE organisation_name = '${__org.name}'

${__org.name} is a Grafana built-in (same family as ${__user.email}) that resolves to the name of the Grafana org the viewer is currently in - useful here specifically because grafana_sync.py provisions one Grafana org per LEAF organisation with matching names, so there’s no need for a separate organisation-picker variable at all.

Full panel query, picking the bucket size from $__interval_ms so it tracks the panel’s zoom level automatically:

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT time, entity, metric, value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    CASE
        WHEN $__interval_ms <= 60000   THEN '1min'
        WHEN $__interval_ms <= 300000  THEN '5min'
        WHEN $__interval_ms <= 600000  THEN '10min'
        WHEN $__interval_ms <= 3600000 THEN '1hour'
        ELSE '1day'
    END,
    NULL, -- entities[] -- NULL = all entities in the department
    NULL, -- metrics[] -- NULL = all metrics
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    20000 -- p_limit, default 20000
) AS t
ORDER BY t."time" ASC;

The function returns exactly (time, entity, metric, value) - no tags column, unlike the raw-sensor_data functions elsewhere on this page. value is the pre-computed mean for that bucket (pulled from the continuous aggregate’s stats_agg() sketch, see TimescaleDB) - the richer stats stored there (stddev, min, max, sample_count) aren’t exposed by this function.

A few things worth knowing:

  • The bucket parameter is a strict whitelist, not an arbitrary interval - only '1min', '5min', '10min', '1hour', or '1day' are accepted (they map directly to the five continuous aggregates); anything else raises p_bucket must be one of: 1min, 5min, 10min, 1hour, 1day. There’s no way to ask for one bucket covering an arbitrary custom range - see the Min/Max/Avg step below for the caveat that follows from this.
  • ORDER BY t."time" ASC is required, not optional. The function itself orders DESC internally (it’s built for “give me the latest N points” via its p_limit parameter), so without re-sorting ascending on the outside, Grafana’s long-to-wide conversion fails outright with long series must be sorted ascending by time.
  • Filtering to specific entities: pass an array instead of NULL for the entities[] parameter, e.g. ARRAY['sensor-1', 'sensor-2']. For a pattern-based filter (e.g. “everything on *.example.com”) that the function itself doesn’t support, filter in an outer WHERE t.entity LIKE '%example.com%' clause instead - it’s cheap since it runs after the row cap, not before.
  • The row cap applies globally, not per entity/metric. p_limit takes the most recent N rows across every matching entity/metric combined (after the DESC sort), not N rows per series - a dashboard selecting many entities at a fine bucket size over a long range can silently lose older data for some series before others. Widen the time range or bucket size, or narrow the entity/metric selection, rather than raising p_limit past what a panel actually needs.

Grafana variables

Section titled “Grafana variables”

Add these as dashboard variables so users can select department, entity, and metric from drop-downs. Organisation doesn’t need its own variable - ${__org.name} (a Grafana built-in) already carries it, since grafana_sync.py provisions one Grafana org per LEAF organisation with a matching name. All variable queries are scoped to the logged-in user’s access.

Available departments

Section titled “Available departments”
SELECT DISTINCT department_name
FROM leaf_management_for_user('${__user.email}')
WHERE organisation_name = '${__org.name}'
ORDER BY 1

Entities within a department

Section titled “Entities within a department”

leaf_grafana_user has no direct access to sensor_catalog (see How access control works), so entities and metrics are derived from leaf_sensor_data_bucketed_by_department itself rather than from a catalog table - the only cost is that an entity/metric with no data in the lookback window below won’t show up until it does:

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT DISTINCT entity
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1day',
    NULL, -- entities[]
    NULL, -- metrics[]
    now() - INTERVAL '30 days',
    now(),
    50000
) AS t
ORDER BY entity

Available metrics

Section titled “Available metrics”

Same pattern, one column swapped:

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT DISTINCT metric
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1day',
    NULL, -- entities[]
    NULL, -- metrics[]
    now() - INTERVAL '30 days',
    now(),
    50000
) AS t
ORDER BY metric

Time-series panel (aggregated)

Section titled “Time-series panel (aggregated)”

This is the same query as The bucketed function above - a time-series panel just maps its result columns directly: time to the X axis, value to Y, and entity/metric (or entity || ' - ' || metric if a panel mixes several metrics) as the series name.

Bucket size: the $__interval_ms CASE picks the bucket automatically as the panel zooms. To hardcode a fixed granularity instead, pass one of '1min' / '5min' / '10min' / '1hour' / '1day' directly - it’s a strict whitelist, not a free-form interval.

Under the hood: how these SECURITY DEFINER functions enforce scoping (illustrative, not the literal bucketed-function query)

leaf_sensor_data_bucketed_by_department’s own body isn’t reproduced here, but every one of these access-controlled functions follows the same pattern, shown below with a sibling function that reads raw sensor_data directly (leaf_sensor_data_for_department_per_time - not currently granted to leaf_grafana_user, see How access control works). This is the literal SQL PostgreSQL receives and then runs for that function - first the call as sent by a caller, then the function body expanded with those values inlined - useful for understanding why parameters matter, even though Grafana itself calls the bucketed function instead.

-- -- What Grafana sends to PostgreSQL --------------------------------------
--
-- Variables resolved for this example:
-- ${__user.email} -> 'user@example.com' (logged-in Grafana account)
-- ${organisation} -> 'WUR'
-- ${department} -> 'Greenhouse A'
-- $__timeFrom() -> '2025-01-15 09:00:00+00' (left edge of time picker)
-- $__timeTo() -> '2025-01-15 21:00:00+00' (right edge of time picker)
-- $__interval -> '3 minutes' (auto-sized to panel width)
-- ${entity:csv} -> 'sensor-1,sensor-2' (multi-select variable)


SELECT *
FROM leaf_sensor_data_for_department_per_time(
    'user@example.com', -- ${__user.email}
    'WUR', -- ${organisation}
    'Greenhouse A', -- ${department}
    '2025-01-15 09:00:00+00'::timestamptz, -- $__timeFrom()
    '2025-01-15 21:00:00+00'::timestamptz, -- $__timeTo()
    '3 minutes'::interval, -- $__interval
    ARRAY['sensor-1', 'sensor-2'] -- NULLIF(ARRAY[...], ARRAY['']) with entity:csv
    -- NULL here when "All" is selected -> function returns all accessible entities
)
ORDER BY time;




-- -- What PostgreSQL runs (function body with values inlined) ---------------
--
-- The SECURITY DEFINER function runs as the database owner (postgres), not as
-- leaf_grafana_user. PostgreSQL cannot inline SECURITY DEFINER functions, so
-- the query planner sees the function as a black box. Time bounds must be
-- passed as p_from/p_to parameters so TimescaleDB can prune chunks *inside*
-- the function. A WHERE clause applied outside the function call arrives too
-- late for chunk exclusion.


WITH


-- Step 1: resolve org/dept names -> UUIDs
-- This is a tiny indexed lookup; result is 1 row.
dept AS MATERIALIZED (
    SELECT d.id AS dept_id, o.id AS org_id
    FROM department d
    JOIN organisation o ON o.id = d.organisation_id
    WHERE o.name = 'WUR' -- p_organisation
      AND d.name = 'Greenhouse A' -- p_department
    LIMIT 1
),


-- Step 2: resolve user email -> management grants
-- Each row represents one data slice the user is allowed to see:
-- (organisation_id, optional entity scope, optional time window).
-- Result is typically a handful of rows - this CTE is materialised once
-- and reused for every sensor_data row in step 3.
user_access AS MATERIALIZED (
    SELECT m.organisation_id, m.entity, m.time_start, m.time_end
    FROM user_management um
    JOIN management m ON m.id = um.management_id
    JOIN user_account ua ON ua.id = um.user_id
    JOIN dept ON TRUE -- binds to the dept CTE
    WHERE ua.email = 'user@example.com' -- p_user_email
      AND (m.department_id IS NULL -- NULL = dept-wide grant
           OR m.department_id = (SELECT dept_id FROM dept))
)


-- Step 3: scan sensor_data and aggregate into time buckets
-- TimescaleDB evaluates the WHERE sd.time >= / < conditions during
-- planning and excludes chunks outside the range before the scan starts.
-- Chunk exclusion is the primary performance lever for large datasets.
SELECT
    time_bucket('3 minutes', sd.time) AS time, -- bucket aligned to interval start
    sd.entity,
    sd.metric,
    AVG(sd.value)::double precision AS value, -- average of all readings in the bucket
    sd.tags
FROM sensor_data sd
JOIN dept ON sd.department_id = dept.dept_id
JOIN user_access ua ON (
    ua.organisation_id = sd.organisation_id
    -- Entity scope: NULL in management = access to all entities in the dept
    AND (ua.entity IS NULL OR ua.entity = sd.entity)
    -- Management time bounds: hard limits set by the administrator.
    -- The UI time range (p_from/p_to) can only narrow this window, never expand it.
    AND (ua.time_start IS NULL OR sd.time >= ua.time_start)
    AND (ua.time_end IS NULL OR sd.time < ua.time_end)
)
WHERE sd.time >= '2025-01-15 09:00:00+00' -- p_from <- chunk exclusion happens here
  AND sd.time < '2025-01-15 21:00:00+00' -- p_to
  AND sd.entity = ANY(ARRAY['sensor-1', 'sensor-2']) -- p_entities filter
GROUP BY 1, sd.entity, sd.metric, sd.tags
ORDER BY 1;

Raw data table panel

Section titled “Raw data table panel”

leaf_grafana_user has no access to raw sensor_data - the finest granularity available is the '1min' bucket, which is close enough to raw for most sensor polling frequencies. Use The bucketed function with '1min' hardcoded instead of the $__interval_ms CASE, and a short time range so it stays readable as a table.

Entity filter - passing multiple entities

Section titled “Entity filter - passing multiple entities”

When ${entity} is a multi-value variable, Grafana renders it as a comma-separated string with the :csv format modifier. Convert it to an array for the entities[] parameter, with NULLIF(..., ARRAY['']) turning an empty selection (“All”) into NULL:

entities[] parameter: NULLIF(ARRAY['${entity:csv}']::text[], ARRAY[''])

If the entity variable is single-value only, pass it directly:

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT time, entity, metric, value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    CASE
        WHEN $__interval_ms <= 60000   THEN '1min'
        WHEN $__interval_ms <= 300000  THEN '5min'
        WHEN $__interval_ms <= 600000  THEN '10min'
        WHEN $__interval_ms <= 3600000 THEN '1hour'
        ELSE '1day'
    END,
    CASE WHEN '${entity}' = '' THEN NULL ELSE ARRAY['${entity}']::text[] END,
    NULL, -- metrics[]
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    20000
) AS t
ORDER BY t."time" ASC

Access model summary

Section titled “Access model summary”
What the user seesWhy
Only their departmentsleaf_management_for_user filters by ua.email
Only their entitiesManagement entity scope is a JOIN condition, not a WHERE filter
Only their time windowmanagement.time_start / time_end are hard bounds enforced inside the function
UI time range can only narrowp_from / p_to are intersected with the management time bounds - cannot expand beyond the grant

Network configuration

Section titled “Network configuration”
  • Port: 3000 (Grafana web interface)
  • Internal DB host: timescaledb:5432 (Docker backend network)
  • External DB host: expose via SSH tunnel or VPN - do not expose port 5432 publicly

Building a dashboard - step by step

Section titled “Building a dashboard - step by step”

This walkthrough creates a complete sensor dashboard from scratch. All queries are copy-paste ready - replace YourDepartment only if you hardcode it instead of using the variable. Organisation never needs a variable: ${__org.name} already resolves to it from Grafana’s own org context.

Step 1 - Add dashboard variables

Section titled “Step 1 - Add dashboard variables”

Go to Dashboard settings -> Variables and add these three variables in order. entity and metric both depend on department.

Variable: department

SettingValue
TypeQuery
Data sourceYour LEAF datasource
RefreshOn dashboard load

Query:

SELECT DISTINCT department_name
FROM leaf_management_for_user('${__user.email}')
WHERE organisation_name = '${__org.name}'
ORDER BY 1

Variable: entity

SettingValue
TypeQuery
Multi-valueOn
Include AllOn
Data sourceYour LEAF datasource
RefreshOn time range change

Query - derived from the last 30 days of bucketed data, since leaf_grafana_user has no direct access to sensor_catalog (see Entities within a department):

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT DISTINCT entity
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1day',
    NULL,
    NULL,
    now() - INTERVAL '30 days',
    now(),
    50000
) AS t
ORDER BY entity

Variable: metric

SettingValue
TypeQuery
Multi-valueOn
Include AllOn
Data sourceYour LEAF datasource
RefreshOn time range change

Query - same pattern, one column swapped:

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT DISTINCT metric
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1day',
    NULL,
    NULL,
    now() - INTERVAL '30 days',
    now(),
    50000
) AS t
ORDER BY metric

Step 2 - Time-series panel

Section titled “Step 2 - Time-series panel”

Panel type: Time series

Shows one line per entity-metric combination for the selected time range, bucketed to match the panel’s zoom level.

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT time, entity || ' - ' || metric AS metric, value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    CASE
        WHEN $__interval_ms <= 60000   THEN '1min'
        WHEN $__interval_ms <= 300000  THEN '5min'
        WHEN $__interval_ms <= 600000  THEN '10min'
        WHEN $__interval_ms <= 3600000 THEN '1hour'
        ELSE '1day'
    END,
    CASE WHEN '${entity}' = 'All' THEN NULL ELSE string_to_array('${entity:csv}', ',') END,
    NULL, -- metrics[]
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    20000
) AS t
ORDER BY t."time" ASC

In the Field tab set String as the Series name override to ${__field.labels.metric} if you want entity and metric as separate labels.

Step 3 - Current value (Stat panel)

Section titled “Step 3 - Current value (Stat panel)”

Panel type: Stat Calculation: Last (not null)

Shows the most recent 1-minute-bucket reading for a single metric across all selected entities.

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT time, entity AS metric, value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1min',
    CASE WHEN '${entity}' = 'All' THEN NULL ELSE string_to_array('${entity:csv}', ',') END,
    ARRAY['${metric}'],
    now() - INTERVAL '2 hours',
    now(),
    20000
) AS t
ORDER BY t."time" ASC

Set Value options -> Calculation to Last * in the panel options.

Step 4 - Min / Max / Avg for the selected time range (Stat panel)

Section titled “Step 4 - Min / Max / Avg for the selected time range (Stat panel)”

Panel type: Stat Calculation: set individually per stat

One panel per stat - duplicate and change the SELECT expression:

-- For average:
WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT
    AVG(value) AS "Average",
    MIN(value) AS "Minimum",
    MAX(value) AS "Maximum"
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    CASE
        WHEN $__interval_ms <= 60000   THEN '1min'
        WHEN $__interval_ms <= 300000  THEN '5min'
        WHEN $__interval_ms <= 600000  THEN '10min'
        WHEN $__interval_ms <= 3600000 THEN '1hour'
        ELSE '1day'
    END,
    CASE WHEN '${entity}' = 'All' THEN NULL ELSE string_to_array('${entity:csv}', ',') END,
    ARRAY['${metric}'],
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    20000
) AS t

Unlike leaf_sensor_data_for_department_per_time, this function has no “single bucket for the whole range” mode (see The bucketed function) - these three numbers are computed over whatever buckets the CASE picks for the current zoom level, not over raw readings. Average is therefore an average of per-bucket means, a good approximation but not exactly the same as averaging every raw reading directly. Minimum/Maximum are the smallest/largest bucket mean, not the smallest/largest individual raw reading - treat this panel as “typical range for the period”, not an exact min/max.

Step 5 - Recent readings table

Section titled “Step 5 - Recent readings table”

Panel type: Table

Shows the last N 1-minute-bucketed readings for quick inspection - '1min' is the finest granularity available to leaf_grafana_user (see Raw data table panel), so there’s no true raw/sub-minute view here.

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT
    time,
    entity,
    metric,
    ROUND(value::numeric, 4) AS value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1min',
    CASE WHEN '${entity}' = 'All' THEN NULL ELSE string_to_array('${entity:csv}', ',') END,
    ARRAY['${metric}'],
    $__timeFrom()::timestamptz,
    $__timeTo()::timestamptz,
    500
) AS t
ORDER BY t."time" DESC

The 500 in the last argument is p_limit - the function already returns its most recent rows first (DESC internally), so it doubles as the row cap here; no separate LIMIT clause needed.

Step 6 - Multi-entity bar gauge

Section titled “Step 6 - Multi-entity bar gauge”

Panel type: Bar gauge

Compares the most recent value across all entities in the department. Useful for seeing which sensors are highest/lowest right now.

WITH dept AS (
    SELECT DISTINCT department_id
    FROM leaf_management_for_user('${__user.email}')
    WHERE organisation_name = '${__org.name}'
      AND department_name   = '${department}'
)
SELECT entity, AVG(value) AS value
FROM leaf_sensor_data_bucketed_by_department(
    '${__user.email}',
    ARRAY(SELECT department_id FROM dept),
    '1hour',
    NULL, -- all entities
    ARRAY['${metric}'],
    now() - INTERVAL '1 hour',
    now(),
    20000
) AS t
GROUP BY entity
ORDER BY value DESC

Complete variable reference

Section titled “Complete variable reference”
VariableFormat modifierExample usage
${__user.email}-Always pass as-is to functions
${__org.name}-Grafana built-in; resolves to the LEAF organisation name, no variable needed
${department}-Single-value text; filtered inside the dept CTE
${entity}:csvCASE WHEN '${entity}' = 'All' THEN NULL ELSE string_to_array('${entity:csv}', ',') END
${metric}-Pass as ARRAY['${metric}'], or NULL for all metrics filtered by an outer WHERE
$__timeFrom()::timestamptzPass as p_from
$__timeTo()::timestamptzPass as p_to
$__interval_ms-Drives the bucket-size CASE - never passed to the function directly

Troubleshooting

Section titled “Troubleshooting”

No data in panel : Check that the logged-in Grafana user’s email matches a user_account entry in LEAF. The user must also have at least one user_management grant for the selected department.

permission denied for function ... / relation error : leaf_grafana_user isn’t a readers member - it only has the three specific grants listed above. Check what it actually has: \du leaf_grafana_user shows role memberships (should be none), and SELECT routine_name FROM information_schema.role_routine_grants WHERE grantee = 'leaf_grafana_user'; in psql shows exactly which functions it can call. If the function you need isn’t there, it needs a new GRANT EXECUTE ... TO leaf_grafana_user in deploy.sql, applied by an admin.

All time ranges return the same data : You have a WHERE time BETWEEN ... clause outside the function call. Move the time bounds inside as p_from / p_to parameters.

long series must be sorted ascending by time : leaf_sensor_data_bucketed_by_department orders its results DESC internally. Add ORDER BY t."time" ASC around the function call - see The bucketed function.

Slow queries on large datasets : Always provide a time range. Without p_from/p_to, the function scans all chunks. With a time range, TimescaleDB prunes to only the relevant chunks - typically 1-3 for recent data.