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

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 concept	Grafana concept	How it’s kept in sync
Portal login	Grafana user account	Created via the Admin API on first login; password is pushed on every login and password change so it always matches the portal password
organisation	Grafana org	One-to-one, matched by name; created if missing
department	Grafana team	One 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

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:

Grant	Purpose
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

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

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 (time, entity, metric, value, tags). 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. tags is the bucket’s representative tag set (MIN(tags::text)::jsonb at ingest time - the same raw JSONB shape as the raw-sensor_data functions, just one sample per bucket rather than per row).

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']. There’s no entity-name pattern-match parameter, so a pattern-based filter (e.g. “everything on *.example.com”) still has to run in an outer WHERE t.entity LIKE '%example.com%' clause, applied after p_limit - see the row-cap caveat below.

• Filtering on tags, unlike entity/metric, does have a pre-LIMIT option: four optional trailing parameters, falling into two genuinely different matching styles - don’t reach for p_tag_key/p_tag_pattern out of habit where p_tags already does the job, they’re not interchangeable:

  Parameter(s)	Matching	Use when…	Example
  p_tags JSONB	Exact - JSONB containment (tags @> p_tags)	The tag’s value is what you’re matching, whole and exact	'{"technique": "Mixed Mode"}' - matches only technique = "Mixed Mode" precisely; "mixed mode" or "Mixed Mode " would not match
  p_project_ids TEXT[]	Exact, one-of-several - array membership (tags->>'project_id' = ANY(...))	A Grafana multi-select variable choosing among several exact allowed values	ARRAY['BES3', 'BES4']
  p_tag_key + p_tag_pattern	Partial - regex (tags->>p_tag_key ~* p_tag_pattern), case-insensitive	You only need to match part of the value, or need alternation - something containment can’t express at all	'topic', 'Ivium|MicroGC' - matches a topic value containing either instrument name anywhere in it, e.g. unlock/MBP/Ivium/ivium_potentiostat/...
  p_tag_key alone (p_tag_pattern = NULL)	Missing-or-empty check	Finding rows where a tag key was never set	'topic', NULL

  Real data explains why topic specifically needs the regex column, not containment: its actual values are full paths like unlock/MBP/Ivium/ivium_potentiostat/experiment/undefined/measurement/unknown, never the bare string "Ivium" - p_tags @> '{"topic": "Ivium"}' would require an exact match and return zero rows. And 'Ivium|MicroGC' isn’t a token this project made up to look like regex - it’s real alternation, matching either of two real instruments in one pattern where LIKE would need two separate clauses OR’d together:

  SELECT DISTINCT entity, split_part(tags->>'topic', '/', 3) AS instrument
  FROM leaf_sensor_data_bucketed_by_department(
      '${__user.email}', ARRAY(SELECT department_id FROM dept),
      '1day', NULL, NULL,
      $__timeFrom()::timestamptz, $__timeTo()::timestamptz, 200000,
      NULL, NULL, 'topic', 'Ivium|MicroGC'
  ) AS t;

  entity	instrument
  Ivium testcell MBP	Ivium
  S09009	Ivium
  agilent_990_micro_gc_001	MicroGC
  Agilent 990 Micro GC 001	MicroGC

  It’s real regex, so the usual constructs work too - 'Ivium|Micro..' (. = any single character) matches the same two rows above, since Micro + any 2 characters happens to cover MicroGC. Not a reason to prefer it here though - Micro.. is less precise than spelling out MicroGC (it’d also match a hypothetical Micro01 or MicroXY), it just happens not to collide with anything else in the data today.

  All four parameters default to NULL/no-op, so they’re purely additive - the original 8-argument call still works unchanged:

  -- Old way, still valid - no tag filtering, 8 args
  SELECT time, entity, metric, value
  FROM leaf_sensor_data_bucketed_by_department(
      '${__user.email}',
      ARRAY(SELECT department_id FROM dept),
      '1day', NULL, NULL,
      $__timeFrom()::timestamptz, $__timeTo()::timestamptz, 200000
  ) AS t
  ORDER BY t."time" ASC;

  time	entity	metric	value
  2026-08-16 02:00:00+02	1	CO2 control set point	0
  2026-08-16 02:00:00+02	1	Acid pump	0.044187
  2026-08-16 02:00:00+02	1	Air flow	0

  -- New way, same call plus the 4 optional tag filters
  SELECT time, entity, metric, value, tags->>'project_id' AS project_id
  FROM leaf_sensor_data_bucketed_by_department(
      '${__user.email}',
      ARRAY(SELECT department_id FROM dept),
      '1day', NULL, NULL,
      $__timeFrom()::timestamptz, $__timeTo()::timestamptz, 200000,
      '{"technique": "Mixed Mode"}',       -- p_tags
      ARRAY[$project],                     -- p_project_ids (multi-value variable)
      'topic', 'Ivium'                     -- p_tag_key, p_tag_pattern
  ) AS t
  ORDER BY t."time" ASC;

  time	entity	metric	value	project_id
  2026-08-16 02:00:00+02	S09009	current	-0.0000067	BES3
  2026-08-16 02:00:00+02	S09009	potential	-0.399779	BES3

  Same function, same first 8 positional arguments in both calls - the extra 4 only change behavior when supplied. Filtering this way instead of in an outer WHERE matters because of the next point.

• The row cap applies globally, not per entity/metric, and is applied before any outer WHERE. p_limit takes the most recent N rows across every matching entity/metric combined (after the DESC sort) - a dashboard selecting many entities at a fine bucket size over a long range can silently lose older data for some series before others, and an outer filter (entity LIKE, or tags->>'x' = ... before this function grew the p_tags/p_project_ids/p_tag_key parameters above) only narrows what’s already survived that cap, it can’t recover rows the cap already dropped. Prefer the tag parameters above over an outer WHERE on tags for exactly this reason. For entity-name pattern filters (still outer-WHERE-only), 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

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

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

Real output for a user with grants across four UNLOCK departments:

Biodiscovery Platform
FAIR Data Platform
Modular Bioreactor Platform
Parallel Cultivation Platform

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

Real output for ${department} = 'Modular Bioreactor Platform' - note the sort is text, not numeric, so '10'/'11'/'12' sort before '2':

1
10
11
12
2
3
4
5

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)

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

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

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

What the user sees	Why
Only their departments	leaf_management_for_user filters by ua.email
Only their entities	Management entity scope is a JOIN condition, not a WHERE filter
Only their time window	management.time_start / time_end are hard bounds enforced inside the function
UI time range can only narrow	p_from / p_to are intersected with the management time bounds - cannot expand beyond the grant

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

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

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

---

Variable: department

Setting	Value
Type	Query
Data source	Your LEAF datasource
Refresh	On dashboard load

Query:

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

---

Variable: entity

Setting	Value
Type	Query
Multi-value	On
Include All	On
Data source	Your LEAF datasource
Refresh	On 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

Setting	Value
Type	Query
Multi-value	On
Include All	On
Data source	Your LEAF datasource
Refresh	On 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

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)

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)

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

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

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

Variable	Format modifier	Example 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}	:csv	CASE 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()	::timestamptz	Pass as p_from
$__timeTo()	::timestamptz	Pass as p_to
$__interval_ms	-	Drives the bucket-size CASE - never passed to the function directly

---

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.