Time-series Database

TimescaleDB is the time-series database at the core of the OAK 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

See Database Access for the full, current picture - every service account (leaf_portal_user, leaf_grafana_user, leaf_vernemq_user, leaf_nodered_auth_user, leaf_api_user, leaf_backup_user), what each one actually has access to, and the readers/writers/api_readers roles they draw from. Not duplicated here to avoid this page and that one drifting out of sync with each other.

Schema overview

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
vmq_auth_acl	Per-department MQTT credentials for VerneMQ’s Postgres-auth plugin - see VerneMQ
entity	Catalog of known entities (devices/reactors/stations) per department
category	Optional grouping label for entities, global or department-scoped
sensor_data_retention_overrides	Per-entity retention period overrides, enforced by a scheduled purge job

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.