Time-series Database

TimescaleDB is the time-series database at the core of the MAPLE backend. It is built on top of PostgreSQL and stores all sensor data collected by the LEAF adapters. The schema is deployed by running deploy/deploy.sql from the leaf-portal repository.

Data model

Sensor data is stored in a single EAV (Entity-Attribute-Value) hypertable rather than per-metric tables. One row represents one measurement:

CREATE TABLE sensor_data (
    time          TIMESTAMPTZ      NOT NULL,
    entity        TEXT             NOT NULL,   -- reactor, station, device, ...
    metric        TEXT             NOT NULL,   -- temperature, pressure, pH, ...
    value         DOUBLE PRECISION NOT NULL,
    tags          JSONB,                       -- units, quality flags, location, ...
    department_id UUID             NOT NULL,
    organisation_id UUID           NOT NULL
);

This design means every piece of equipment, regardless of what metrics it produces, writes to the same table. The entity and metric columns replace what would otherwise be dozens of separate tables.

The table is converted to a TimescaleDB hypertable partitioned by time, with compression after 7 days:

SELECT create_hypertable('sensor_data', 'time');

ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'department_id, entity',
    timescaledb.compress_orderby   = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

Access model

All data access is management-based. A user must have at least one user_management entry whose management scope covers the data being requested. Direct SELECT on sensor_data is not allowed — all queries go through SECURITY DEFINER functions that enforce this check automatically.

A management scopes access from broad to narrow:

organisation → department → entity → time window

Field	NULL means
department_id	all departments in the organisation
entity	all entities in scope
time_start	no lower bound
time_end	open-ended (current data)

Roles and service accounts

Three roles are defined:

Role	Purpose
readers	Portal and Grafana service accounts — query via SECURITY DEFINER functions
writers	Node-RED writes sensor data; portal manages all management tables
api_readers	External scripts — token-based functions only, no direct table access

Three service accounts are created by deploy.sql:

Account	Role	Used by
leaf_portal_user	readers + writers	LEAF Portal backend
leaf_grafana_user	readers	Grafana datasource
leaf_api_user	api_readers	External scripts / notebooks

Passwords are passed as psql variables (:'LEAF_PORTAL_PASSWORD' etc.) and must be set before running the script.

Schema overview

erDiagram
    organisation ||--o{ department : contains
    organisation ||--o{ management : scopes
    department ||--o{ management : "scopes (optional)"
    department ||--o{ sensor_data : stores
    organisation ||--o{ sensor_data : stores

    user_account ||--o{ user_management : has
    management ||--o{ user_management : grants

    user_account ||--o{ api_token : owns
    user_account ||--o{ user_organisation : "tracks (org)"
    user_account ||--o{ user_department : "tracks (dept)"

    department ||--o{ alarm_rule : owns
    alarm_rule ||--o{ alarm_recipient : notifies
    alarm_rule ||--o{ alarm_event : logs

    department ||--o{ mapper_language : maps
    department ||--o{ mapper_semantics : maps

    sensor_data ||--o{ sensor_data_1min : "continuous aggregate, refresh 1min"
    sensor_data_1min ||--o{ sensor_data_5min : "rollup, refresh 5min"
    sensor_data_5min ||--o{ sensor_data_10min : "rollup, refresh 10min"
    sensor_data_10min ||--o{ sensor_data_1hour : "rollup, refresh 1hour"
    sensor_data_1hour ||--o{ sensor_data_1day : "rollup, refresh 1day"

    organisation {
        UUID id PK
        TEXT name
    }
    department {
        UUID id PK
        UUID organisation_id FK
        TEXT name
    }
    sensor_data {
        TIMESTAMPTZ time
        TEXT entity
        TEXT metric
        FLOAT8 value
        JSONB tags
        UUID department_id
        UUID organisation_id
    }
    sensor_data_1min {
        TIMESTAMPTZ time
        TEXT entity
        TEXT metric
        JSONB stats "toolkit stats_agg sketch"
        JSONB pctl "toolkit percentile_agg sketch"
        FLOAT8 value "exact mean"
        FLOAT8 min_value
        FLOAT8 max_value
        JSONB tags
        JSONB alternative_tags
        UUID department_id
        UUID organisation_id
    }
    management {
        UUID id PK
        UUID organisation_id FK
        UUID department_id FK
        TEXT entity
        TIMESTAMPTZ time_start
        TIMESTAMPTZ time_end
    }
    user_account {
        UUID id PK
        TEXT email
        TEXT name
        BOOLEAN is_superadmin
    }
    user_management {
        UUID user_id FK
        UUID management_id FK
        TEXT role
    }
    api_token {
        UUID id PK
        UUID user_id FK
        TEXT name
        TEXT token
        TIMESTAMPTZ expires_at
    }
    alarm_rule {
        UUID id PK
        UUID department_id FK
        TEXT entity
        TEXT metric
        TEXT operator
        FLOAT8 threshold
    }

Beyond sensor_data, the schema contains management and configuration tables:

Table	Description
organisation	Top-level tenant
department	Sub-tenant within an organisation
user_account	Platform user (email + password hash)
user_organisation	Schema compatibility; not used for data access
user_department	Organisational tracking only; not used for data access
management	Named, scoped data slice
user_management	Grants a user access to a management
api_token	Long-lived tokens for external access
password_reset_token	Single-use password reset tokens (1-hour expiry)
alarm_rule	Threshold and no-data alert rules
alarm_recipient	Users subscribed to an alarm rule
alarm_event	Triggered/resolved alarm history
setting	Key-value config store (database, SMTP, etc.)
mapper_language	Human-readable label translations for metrics
mapper_semantics	Semantic annotation for metrics

sensor_catalog

The sensor_catalog materialized view stores pre-computed distinct (department_id, organisation_id, entity, metric) triples. It is used by the portal and alarms to quickly populate dropdowns without scanning the full hypertable.

It refreshes automatically every hour via a TimescaleDB background job, and can be refreshed manually:

SELECT leaf_refresh_sensor_catalog();

Bucketed continuous aggregates

Fetching and locally downsampling raw rows doesn’t scale for long time ranges or high-frequency sensors. Five TimescaleDB continuous aggregates — sensor_data_1min, sensor_data_5min, sensor_data_10min, sensor_data_1hour, sensor_data_1day — pre-compute rollups so clients can read (entity, metric, time bucket) summaries directly instead of scanning and averaging raw data on every request.

They’re cascaded, not independently computed from raw data: 5min rolls up from 1min, 10min from 5min, and so on up to 1day. Each level only re-scans the (small) level below it, not the raw hypertable, and each has its own refresh policy running at its own bucket width.

Each level stores:

• stats — a TimescaleDB Toolkit stats_agg() sketch. This is an exact, not approximate, summary (running sum / sum-of-squares / sum-of-cubes / sum-of-4th-powers + count), from which value (the mean), stddev_value, and sample_count are derived — all exact, and correctly mergeable across cascade levels via rollup().
• pctl — a Toolkit percentile_agg() sketch, used for median_value. Percentiles genuinely can’t be derived from sums the way a mean can, so this needs its own, separately approximate sketch structure (a log-linear histogram with a fixed relative-error bound) — unlike stats, this one is never exact.
• min_value / max_value — plain MIN/MAX, exact and trivially mergeable on their own (no toolkit needed).
• tags / alternative_tags — tags is the lexicographically-smallest raw row’s tags in the bucket (via MIN(tags::text)); alternative_tags is NULL if every raw row in the bucket actually had identical tags (so tags is then the exact, complete picture), or a second real sample that differed otherwise — a signal that the entity/metric may need finer-grained filtering rather than being treated as one uniform series.

Why not just cascade AVG() directly? Naively averaging per-level averages is a well-known bias: AVG(AVG(x)) over unevenly-sized groups doesn’t equal the true AVG(x) unless every group has exactly the same count — which raw sensor sampling essentially never guarantees (a sensor going offline for part of a bucket is enough to introduce the bias). Toolkit’s sketches solve this properly instead.

Querying data

Internal (portal and Grafana)

Internal services authenticate by email. The email is resolved to management grants at query time:

-- All sensor data accessible to a user in a given department
SELECT * FROM leaf_sensor_data_for_department('user@example.com', 'MyOrg', 'MyDept');

-- Most recent rows across all accessible departments
SELECT * FROM leaf_sensor_data_recent('user@example.com', 20);

External (API tokens)

External callers connect as leaf_api_user and pass an API token instead of an email address:

-- Time-series data for a Grafana panel (aggregated by time bucket)
SELECT * FROM leaf_sensor_data_timeseries_by_token(
    'your-api-token',
    'MyOrg', 'MyDept',
    now() - INTERVAL '24 hours',
    now(),
    INTERVAL '5 minutes',
    ARRAY['reactor1', 'reactor2'],  -- NULL for all entities
    'temperature'                    -- NULL for all metrics
);

-- Fixed-granularity bucketed rows, read from the continuous aggregates above
-- (this is what powers the REST API's ?bucket= param — see below)
SELECT * FROM leaf_sensor_data_bucketed_by_token(
    'your-api-token',
    'MyOrg', 'MyDept',
    '1hour',                         -- one of: 1min, 5min, 10min, 1hour, 1day
    ARRAY['reactor1', 'reactor2'],    -- NULL for all entities
    ARRAY['temperature'],             -- NULL for all metrics
    now() - INTERVAL '24 hours',
    now()
);

-- Raw rows for a short time window or debugging
SELECT * FROM leaf_sensor_data_raw_by_token(
    'your-api-token',
    'MyOrg', 'MyDept',
    now() - INTERVAL '1 hour',
    now()
);

-- Discover accessible entities and metrics
SELECT entity, metric FROM leaf_sensor_catalog_for_token('your-api-token', 'MyOrg', 'MyDept');

Setup

The schema is deployed automatically when you run Connect & apply schema on the portal’s first-time setup page. See the LEAF Portal setup guide for details.