Skip to content

Laravel <-> TimescaleDB: component mapping

Companion to TimescaleDB connect and query guide, which covers connecting and querying. This page covers what has to line up for a component to resolve.

How the EMS frontend reads device telemetry from TimescaleDB instead of MariaDB, what has to line up for a component to resolve, and the changes made to connect PCS, grid meters, solar meters and the frequency meter.

Audience: whoever maintains the dashboard or wires up a new component.


1. Why this exists

Device telemetry lands in TimescaleDB (raw_device_readings_ht, a hypertable written by the collector through ems-backend). The dashboard charts used to read MariaDB. For the components that have been migrated, MariaDB no longer holds the data — so if TimescaleDB resolution fails, the chart is empty rather than slow or stale.

That property matters when debugging: an empty chart almost always means a mapping problem, not a data problem.

Data flow

device / device simulator
  -> ems-backend            polls Modbus, inserts its own readings
  -> (MQTT) laravel-mqtt-ems
  -> ems-frontend           RawDeviceReadingService writes the row
  -> TimescaleDB            raw_device_readings_ht
  -> ems-frontend           TimescaleReadingService reads it back for charts

The frontend both writes and reads this table. ems-backend is not on the read path for dashboards.


2. The three configuration surfaces

Getting a component onto TimescaleDB requires three separate things to agree. Each has its own file, and each fails silently on its own.

# What it decides Where
1 Which table/stream, which device, which columns config/ems_timescale_map.php
2 Payload key spelling inside payload JSON config/device_key_map_ems.php
3 Which units are enabled, and their labels ems_control_setting.json (in ems-backend)

2.1 config/ems_timescale_map.php

One entry per component. Minimum shape:

'gridEnergyMeter' => [
    'source_model'      => 'grid_measurements',   // the stream in TimescaleDB
    'device_id'         => 'GRIDMETER1',          // collector's device id
    'device_id_aliases' => ['GRID-METER-001'],    // other accepted spellings
    'live'              => true,
    'columns'           => [
        'gridMeter_active_power_total_kw' => 'gridMeter_active_power_total_kw',
        // raw payload key                  => canonical name used by the chart
    ],
],

Key fields:

  • source_model — the partition of the hypertable. Every device stream is a separate value. This is not optional and cannot be wildcarded.
  • device_id — the collector's device id as stored in the device_id column. A string, or an array for several ids.
  • device_id_aliases — additional accepted spellings, merged with device_id at query time.
  • mirrors — additional streams that serve the same component, each with its own source_model. See §5.
  • columns — maps raw payload keys to the names the chart config asks for. A column missing here is invisible even when the row is fetched.

There is also a top-level risk_level_aliases table (§4) and a connection / table override.

2.2 config/device_key_map_ems.php

Translates the payload JSON key spelling to whatever the chart config requests. Needed because the collector, the backend and the dashboard each spell the same measurement differently, e.g. pcs_power_setpoint_kw, pcs_power_setpoint_KW, PCS_Power_Setpoint_kW, pcs_active_power_set_kw.

Entries are candidate lists; the first key present in the payload wins:

'pcs_active_power_set_kw' => [
    'pcs_active_power_set_kw',
    'pcs_power_setpoint_kw',
    'pcs_power_setpoint_KW',
],

A chart can be correctly mapped at the stream level and still plot nothing because the column resolved to no payload key. Both layers have to line up.

2.3 ems_control_setting.json

Lives in the backend: ems/backend/app/EnergySyc_EMS_ES100Cx12S/emsConfig/ems/ems_control_setting.json.

Two things the frontend depends on:

  • Which units are enabled. general_settings.<section>.<unit>.status must be "active". Only active units are polled, so only active units appear in TimescaleDB.
  • The unit labels. general_settings.grid_meters.grid_meter1.device_id is "GRID-METER-001" — the string the dashboard sends as deviceId.

Structure:

general_settings
  ├── pcs                 pcs1 .. pcs12            device_id: PCS-001 ..
  ├── grid_meters         grid_meter1 .. grid_meter12    GRID-METER-001 ..
  ├── solar_meters        solar_meter1 .. solar_meter12  SOLAR-METER-001 ..
  ├── frequency_meters    frequency_meter1               FREQUENCY-METER-001
  ├── ac_units            ac_unit1 .. ac_unit6
  ├── cooling_unit        cooling_unit1
  ├── fss, door_sensors, flood_sensors, smoke_detectors,
  └── heat_sensors, temperature_sensors, sensor_blocks

The section key is the stable identity; device_id is a user-editable label. TimescaleDB's device_id is the section key flattened: grid_meter1 → GRIDMETER1, solar_meter1 → SOLARMETER1. The label can be renamed from the UI, which is why aliases are fragile (§7).


3. The three naming layers

The same physical device is named differently at each layer. This is the single most common source of empty charts.

Layer Name for grid unit 1
EMS config section key (stable) grid_meter1
EMS device_id label (user-editable) GRID-METER-001
TimescaleDB source_model grid_measurements
TimescaleDB device_id GRIDMETER1
Dashboard component name grid_meters
Map key gridEnergyMeter

All six refer to one device. A component resolves only when the name the controller receives matches something in the map, and the name the controller passes as deviceId matches the stream's device list.


4. How resolution works

App\Services\TimescaleReadingService

resolve($componentName) looks up the component in the map:

  1. Exact key match.
  2. Otherwise, scan risk_level_aliases case-insensitively and follow the alias.
  3. Return null if neither matches.

Because step 2 is case-insensitive, PCS ↔ pcs works for free. It cannot bridge a different word shape, which is why grid_meters needed an explicit alias to gridEnergyMeter.

'risk_level_aliases' => [
    'grid_meters'      => 'gridEnergyMeter',
    'solar_meters'     => 'solarEnergyMeter',
    'frequency_meters' => 'frequencyMeter',
    // ...
],

supports($componentName) is just resolve() !== null. The controller uses it to decide whether to read TimescaleDB or fall back to MariaDB.

fetch($componentName, $gatewayId, $deviceId, $xColumn, $limit, $startTime, $endTime, $ascending) builds one predicate per candidate stream. See §6.


5. Multi-unit components (mirrors)

Each unit is a separate source_model, so a device_id filter alone can never reach a second unit — the query would be scoped to the wrong partition.

For PCS, twelve units means one primary plus eleven mirrors:

'PCS' => [
    'source_model' => 'pcs1',
    'device_id'    => 'PCS-001',
    'mirrors'      => [
        ['source_model' => 'pcs2',  'device_id' => 'PCS-002'],
        ['source_model' => 'pcs3',  'device_id' => 'PCS-003'],
        // ... through pcs12 / PCS-012
    ],
],

fetch() builds a stream list from the primary plus every mirror, and narrows to exactly one stream when the caller names a device that belongs to one.

Two behaviours worth knowing:

  • A unit mapped but with no rows reports "no data". A unit not mapped is indistinguishable from one that was never requested. So map a unit as soon as it is configured, even before it reports.
  • The grid and solar meters each have twelve units configured but only grid_meter1 / solar_meter1 are status=active, so one stream each is correct there. Do not add mirrors for a unit that is not collecting.

6. Device id matching and the unmatched rule

fetch() resolves the caller's deviceId against each stream's device list (device_id + device_id_aliases + the mirror's own values):

Caller passes Behaviour
nothing (null / '') the primary stream — "give me the component"
a known device id that stream, alone
an id matching nothing no rows, and a log entry listing the known streams

An unmatched id deliberately returns nothing rather than falling back to the primary. Falling back answers a different unit's question under the id that was asked for, which looks like success and is worse than an empty result.

grid_meters  GRID-METER-001   -> grid_measurements
grid_meters  GRIDMETER1       -> grid_measurements
grid_meters  GRID-METER-999   -> no data

The log line records the requested id and the streams that do exist. Note it may not appear in storage/logs/laravel.log — this app configures custom channels.


7. Connecting a new component — the procedure

Work in this order. Each step is independently verifiable.

  1. Confirm the unit is enabled. In ems_control_setting.json, the unit's status must be "active". Disabled units never reach TimescaleDB, and no amount of mapping will help.

  2. Confirm the stream exists and is current.

SELECT source_model, device_id, count(*), max(time)
FROM raw_device_readings_ht
WHERE source_model = '<the stream>'
GROUP BY source_model, device_id;

If there is no stream, stop — the problem is collection, not the dashboard.

  1. Find the payload keys in one row of payload and add any missing entries to config/device_key_map_ems.php.

  2. Add or extend the map entry. Set source_model, device_id, and a columns map covering every key the chart config asks for. Add mirrors for additional units.

  3. Alias the component name. If the dashboard's component name (component_details keys: grid_meters, solar_meters, ...) differs from the map key (gridEnergyMeter, ...), add it to risk_level_aliases. If the id the dashboard sends differs from the collector's, add device_id_aliases.

  4. Find the id the dashboard actually sends. Do not assume — read the logs:

docker exec ems-frontend sh -c \
  'grep -ohE "deviceId[^,}]{0,40}" storage/logs/*.log | sort | uniq -c | sort -rn'
  1. Verify by calling fetch() with that id, not with no id:
docker exec ems-frontend php -r '
require "/var/www/html/vendor/autoload.php"; $a=require "/var/www/html/bootstrap/app.php";
$a->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();
$svc = app(App\Services\TimescaleReadingService::class);
$r = $svc->fetch("grid_meters", null, "GRID-METER-001", "time", 5, null, null, true);
printf("rows=%d source_model=%s\n", $r->count(), $r->first()->source_model ?? "-");
';

Testing without a device id exercises the default path, not the one the browser uses. Several mapping bugs survived an earlier round of checks for exactly this reason.

  1. Clear the config cache:
docker exec -u www-data ems-frontend php artisan config:clear

8. Changes made to connect PCS and the meters

8.1 Prerequisite: .env ownership

PHP-FPM runs as uid 33 in the container, but .env files bind-mounted from the host are created root-owned 0600. Laravel cannot read them, silently falls back to config/database.php defaults, and TIMESCALE_DB_PASSWORD becomes empty — so the connection fails with fe_sendauth: no password supplied and charts return {"message":"Server Error"}.

The CLI runs as root and reads the file fine, which is why docker exec checks pass while the browser fails.

gess-stack_build.sh now runs fix_env_ownership() after every action: it sets 33:33 640 on each .env that exists, skips any app dir without one, and does nothing when already correct. Ownership is numeric because the host has no www-data user.

8.2 PCS — twelve units

  • All twelve units are status=active, the only group where all units are.
  • mirrors was already declared for pcs2 and nothing read it: fetch() filtered on the primary source_model alone. Fixed so the candidate set spans primary plus mirrors.
  • The remaining ten mirrors (pcs3..pcs12) were added.
  • Result: 12/12 streams, each verified returning its own source_model.

8.3 pcs5 — a collection gap, not a mapping one

pcs5 produced no rows. The cause was outside the frontend: ems-backend addressed it at bess-simulator-backend:1520, which is BMU5's Modbus server, not the PCS unit's :1505. Writes failed against a BMU register map, the unit was dropped from the poll cycle, and pcs_insert_mariadb_pcs5.log stopped a day earlier. Correcting the port and restarting both containers restored it.

Worth remembering as a pattern: a component that is enabled but empty is a collector problem; a component that resolves but is empty is a mapping problem.

8.4 Grid / solar / frequency meters

All three had live TimescaleDB data but were unreachable, for two independent reasons:

  • Component name mismatch. The dashboard sends grid_meters, solar_meters, frequency_meters; the map keys them gridEnergyMeter, solarEnergyMeter, frequencyMeter. resolve() is case-insensitive but cannot bridge a different word shape, so it returned null and the controller fell through to MariaDB. Fixed by adding the three names to risk_level_aliases.
  • Device id mismatch. The dashboard sends GRID-METER-001 (5,504 requests in the logs); TimescaleDB stores GRIDMETER1. Fixed by adding device_id_aliases to the map and teaching fetch() to merge it into the stream's device list.

FREQUENCY-METER-001 matched on both sides already and needed only the component alias.

8.5 fetch() behaviour changes

  • Streams are built from primary + mirrors, narrowed to one when the caller names a matching device.
  • device_id may be an array; it is merged with device_id_aliases. Previously a single-element array wrapped it and would have nested it.
  • An unmatched device id returns empty and logs, instead of falling back to the primary (§6).

8.6 Chart configuration is data, not code

The PCS chart's Y axis is stored in register_common_devices.attribute_json at [0].component_details.<component>.more_detail_dashboard_conifgs.chart_configs.y_data_source_keys, not in PHP. It held {"pcs_power_setpoint_kw": "PCS DC POWER kw"} — a key that does not exist in the payload, so the chart plotted the wrong field under a misleading label. Recorded as database/patches/2026-10-03_pcs_chart_y_key.sql because a dump-based restore would otherwise reintroduce it.


9. Troubleshooting

Symptom Likely cause
{"message":"Server Error"}, CLI works .env not readable by uid 33 (§8.1)
Chart empty, supports() returns false Component name not in map or risk_level_aliases
Chart empty, supports() true, fetch() no-id returns rows Device id mismatch — check the label the dashboard sends (§7 step 6)
Chart drawn but flat / all zeros Column resolved to the wrong payload key — check device_key_map_ems.php
Second unit of a multi-unit component missing Mirror not declared
Component enabled but no TimescaleDB stream at all Collector side — check ems-backend logs (/app/logs/), not the dashboard
Mapping changed but nothing happened php artisan config:clear not run

Useful commands:

# does the service claim to support it?
docker exec -u www-data ems-frontend php artisan tinker --execute='dump(app(App\Services\TimescaleReadingService::class)->supports("grid_meters"));'

# which streams exist, and are they current?
docker exec mariadb sh -c 'mysql -uroot -p"$MARIADB_ROOT_PASSWORD" -N ems_db -e "..."'

# backend collector errors
docker exec ems-backend sh -c 'tail -30 /app/logs/pcs_modbus.log'

10. Known limitations

  • device_id_aliases are fixed strings. device_id is user-editable in ems_control_setting.json. Rename the label and the chart empties again. The durable fix is to derive the stream from the section key (grid_meter1 → GRIDMETER1), which is stable — but that needs the frontend resolving against ems_control_setting.json, which currently lives only in the backend container.
  • $limit = 100 caps every series regardless of time window. A wide window returns only the newest 100 points and looks sparse rather than wrong.
  • Components still on MariaDB: cooling_unit, ac_units, fss, sensor_blocks, door_sensors, flood_sensors, smoke_detectors, heat_sensors, temperature_sensors. All have live TimescaleDB data and the same two-part fix applies (component alias + device alias). Some map entries (smokeDetector, heatDetector, thermometer, gasDetector) have no source_model yet, so those cannot move until one exists.
  • Date/time filtering is not implemented in the UI: fetch() accepts $startTime / $endTime and applies whereBetween when both are non-empty, and the controller already passes values derived from the chart config's ST_TIME / END_TIME ('dayBack' / 'now' for PCS). The blade has no date control, so nothing supplies a window.

11. Agent edit markers

Per DEEPCOPILOT_PATCHES.md in the Laravel repository (GESS_SCADA2_V0.0.8_20.02.2025_git), agent edits to PHP source are wrapped in a marker pair so they can be re-applied if an upstream release overwrites the file:

// >>> DEEP-COPILOT-PATCH [PATCH-ID] (agent edit - re-apply if file is replaced; see DEEPCOPILOT_PATCHES.md)
... agent code ...
// <<< DEEP-COPILOT-PATCH [PATCH-ID]

Check for missing markers after any file upgrade:

grep -rn "DEEP-COPILOT-PATCH" app/ config/ resources/views/