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

    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
    }
    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 refresh_sensor_catalog();

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 sensor_data_for_department('user@example.com', 'MyOrg', 'MyDept');

-- Most recent rows across all accessible departments
SELECT * FROM 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 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
);

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

-- Discover accessible entities and metrics
SELECT entity, metric FROM 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.