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 thedevice_idcolumn. A string, or an array for several ids.device_id_aliases— additional accepted spellings, merged withdevice_idat query time.mirrors— additional streams that serve the same component, each with its ownsource_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>.statusmust be"active". Only active units are polled, so only active units appear in TimescaleDB. - The unit labels.
general_settings.grid_meters.grid_meter1.device_idis"GRID-METER-001"— the string the dashboard sends asdeviceId.
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:
- Exact key match.
- Otherwise, scan
risk_level_aliasescase-insensitively and follow the alias. - Return
nullif 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_meter1arestatus=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.
-
Confirm the unit is enabled. In
ems_control_setting.json, the unit'sstatusmust be"active". Disabled units never reach TimescaleDB, and no amount of mapping will help. -
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.
-
Find the payload keys in one row of
payloadand add any missing entries toconfig/device_key_map_ems.php. -
Add or extend the map entry. Set
source_model,device_id, and acolumnsmap covering every key the chart config asks for. Addmirrorsfor additional units. -
Alias the component name. If the dashboard's component name (
component_detailskeys:grid_meters,solar_meters, ...) differs from the map key (gridEnergyMeter, ...), add it torisk_level_aliases. If the id the dashboard sends differs from the collector's, adddevice_id_aliases. -
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'
- 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.
- 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. mirrorswas already declared forpcs2and nothing read it:fetch()filtered on the primarysource_modelalone. 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 themgridEnergyMeter,solarEnergyMeter,frequencyMeter.resolve()is case-insensitive but cannot bridge a different word shape, so it returnednulland the controller fell through to MariaDB. Fixed by adding the three names torisk_level_aliases. - Device id mismatch. The dashboard sends
GRID-METER-001(5,504 requests in the logs); TimescaleDB storesGRIDMETER1. Fixed by addingdevice_id_aliasesto the map and teachingfetch()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_idmay be an array; it is merged withdevice_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_aliasesare fixed strings.device_idis user-editable inems_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 againstems_control_setting.json, which currently lives only in the backend container.$limit = 100caps 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 nosource_modelyet, so those cannot move until one exists. - Date/time filtering is not implemented in the UI:
fetch()accepts$startTime/$endTimeand applieswhereBetweenwhen both are non-empty, and the controller already passes values derived from the chart config'sST_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/