TimescaleDB mapping: what changed and why
Concrete record of the work that moved the EMS dashboards from MariaDB to TimescaleDB. Every entry here was verified against the live stack, and the evidence tables are the actual command output rather than a summary of it.
Companion pages:
- TimescaleDB Component Mapping — how resolution works
- TimescaleDB Connect and Query Guide — connection setup
- PCS collection troubleshooting runbook — collector-side faults
1. The pattern behind every bug
Each fault was the same shape: a declaration that exists and is correct, with a consumer that ignores it. Nothing crashed. The old MariaDB path caught the fall-through, so the chart drew something plausible and wrong.
Four instances:
| # | Declaration | Consumer that ignored it |
|---|---|---|
| 1 | device_key_map_ems.php alias entries |
Chart config asked for a key the payload never carries |
| 2 | mirrors on the PCS map entry |
fetch() filtered on the primary source_model alone |
| 3 | .env present on disk |
PHP-FPM (uid 33) could not read it; Laravel used empty defaults |
| 4 | EMS component names in component_details |
Map keyed them differently; resolve() returned null |
The practical lesson: a silent fall-through to a working-but-wrong source is worse than an error. The unmatched-device rule (section 5) exists to convert that class of failure into a visible empty result.
2. Prerequisite: .env ownership
Symptom. Charts returned {"message":"Server Error"} while every
docker exec check passed.
Cause. .env files are bind-mounted from the host and created root-owned
0600. PHP-FPM runs as uid 33 in the container and cannot read them, so Laravel
fell back to the defaults in config/database.php and TIMESCALE_DB_PASSWORD
became '':
fe_sendauth: no password supplied
The CLI runs as root and reads the file without complaint, which is why the failure was invisible to the obvious check.
Fix. fix_env_ownership() at the end of gess-stack_build.sh. Runs after
every action, idempotent, sets numeric 33:33 640, and skips any app dir without
a .env (powernode and gridsync have none).
before: uv/app/.env root:root 600
after: uv/app/.env 33:33 640
Verified by reading the config through the web server, not the CLI:
pw_len returned 0 before and 14 after.
3. PCS — twelve units
All twelve PCS units are status=active in ems_control_setting.json, the only
component group where all units are enabled.
mirrors was declared for pcs2 and nothing read it: fetch() filtered on
the primary source_model alone. Since each unit is its own source_model, no
device_id value could reach a second unit. The remaining ten mirrors were added.
Per-unit verification, checking source_model rather than merely that rows came
back:
PCS-001 rows=5 pcs1 ok
PCS-002 rows=5 pcs2 ok
PCS-003 rows=5 pcs3 ok
PCS-004 rows=5 pcs4 ok
PCS-005 rows=0 - NO DATA
PCS-006 rows=5 pcs6 ok
...
PCS-012 rows=5 pcs12 ok
own-stream ok: 11/12 no data: PCS-005 wrong stream: none
wrong stream: none is the column that matters. A check that only asserts "rows
returned" passes even when every unit is quietly serving unit 1's data.
3.1 pcs5 — a collection gap, not a mapping one
Symptom. pcs5 produced no rows at all.
Diagnosis. ems-backend addressed the unit at bess-simulator-backend:1520.
Port 1520 is BMU5's Modbus server; pcs5's simulator listens on 1505:
PID 216 python3 pcs_modbus_server_dynamic_nofilter_v15.py
--json emsConfig/pcs/pcs5/pcs_register_full.json --port 1505
Writes were therefore sent to a BMU register map, which has no PCS register:
ERROR COMMAND status=ERROR pcs_id=pcs5 remote=bess-simulator-backend:1520
unit_id=4 action=set_power_kw error=Modbus write failed at step 1: address=0x0D57
The unit was dropped from the poll cycle, and
pcs_insert_mariadb_pcs5.log had stopped a day earlier.
The simulator port map, for reference:
PCS1..12 -> 1501..1512
BMU1.. -> 1516+ (1520 = BMU5)
Fix. Corrected the port, then restarted both containers. The restart was the step that made it take effect — the backend had been running 27 hours and was still using the old address.
Order matters: restart bess-simulator-backend first so it is listening
before ems-backend reconnects.
After:
[INFO] pcs5: device_id=PCS-005 model=EPCS105 | bess-simulator-backend:1505 unit_id=1
[2026-10-03 17:53:22] INFO: Inserted/updated 1 rows into pcs5
4. Grid, solar and frequency meters
All three had live TimescaleDB data and were unreachable for two independent reasons, which is worth separating because either alone produces the same symptom.
4.1 Component name mismatch
The dashboard sends grid_meters / solar_meters / frequency_meters; the map
keys them gridEnergyMeter / solarEnergyMeter / frequencyMeter. resolve()
matches case-insensitively, which bridges PCS/pcs, but it 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.
4.2 Device id mismatch
The dashboard sends the label from ems_control_setting.json; TimescaleDB stores
the collector's id. Counted from the frontend's own logs:
5504 deviceId": "GRID-METER-001" <- dashboard
2764 deviceId": "PCS-001"
2752 deviceId": "PCS-002"
184 deviceId": "PCS-003" ... 104 each for PCS-004..PCS-012
| Layer | Grid unit 1 |
|---|---|
| EMS section key (stable) | grid_meter1 |
EMS device_id label (user-editable) |
GRID-METER-001 |
TimescaleDB source_model |
grid_measurements |
TimescaleDB device_id |
GRIDMETER1 |
FREQUENCY-METER-001 happens to match on both sides, which is why frequency was
the only one that worked. Fixed by adding device_id_aliases to the map and
teaching fetch() to merge it into the stream's device list.
4.3 Verification — with the id the browser sends
component deviceId sent rows source_model returned
grid_meters GRID-METER-001 5 grid_measurements
grid_meters GRIDMETER1 5 grid_measurements
grid_meters GRID-METER-999 0 (no data)
solar_meters SOLAR-METER-001 5 solar_measurements
solar_meters SOLARMETER1 5 solar_measurements
solar_meters SOLAR-METER-999 0 (no data)
frequency_meters FREQUENCY-METER-001 10 frequency_meter_measurements
frequency_meters FREQUENCY-METER-999 0 (no data)
Testing without a device id exercises the default path, not the one the browser uses. Several of these bugs survived an earlier round of checks for exactly that reason — the results were correct and irrelevant to the failing case.
5. fetch() behaviour changes
- Streams are built from primary +
mirrors; each unit has its ownsource_model, so adevice_idfilter alone can never reach a second unit. - Narrowed to exactly one stream when the caller names a matching device.
- An unrecognised device id returns empty and logs, rather than falling back to the primary. Falling back answers a different unit's question under the id that was asked for, which reads as success.
device_idmay be an array, merged withdevice_id_aliases. The previous single-element array wrapping would have nested it.
One caveat on that rule: it converts a mismatch into a visible empty chart. Before
it, GRID-METER-001 fell back to the primary and the grid chart worked by
accident. The rule did not create the mismatch — it exposed it.
6. pcs5, and a claim I had to correct
An earlier check reported that eleven grid and eleven solar units were being
collected but unreachable. That was wrong. It was inferred from
no_of_components: 12 in component_details without checking status:
grid_meters: grid_meter1 active grid_meter2..12 inactive
solar_meters: solar_meter1 active solar_meter2..12 inactive
frequency_meters: frequency_meter1 active
pcs: pcs1 .. pcs12 ALL active
Only the PCS group has all units enabled, so one TimescaleDB stream each for grid and solar is correct, not a gap. The twelve PCS units were the only place a real collection gap existed.
Rule: check status before concluding anything from a component count.
7. Chart configuration lives in the database
The PCS chart's Y axis is not in PHP. It is stored in
register_common_devices.attribute_json at:
[0].component_details.<component>.more_detail_dashboard_conifgs.chart_configs.y_data_source_keys
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. Changed to {"pcs_active_power_set_kw": "Pcs Power Setpoint Kw"}.
Recorded as database/patches/2026-10-03_pcs_chart_y_key.sql in the Laravel repo,
because a dump-based restore would otherwise reintroduce it. Re-applying is a
no-op, verified with all 279 leaf paths intact.
8. Change inventory
| Area | File / location | Change |
|---|---|---|
| Payload keys | config/device_key_map_ems.php |
pcs_active_power_set_kw accepted as a setpoint alias |
| Component map | config/ems_timescale_map.php |
11 PCS mirrors; risk_level_aliases for the three meter components; device_id_aliases for EMS labels |
| Resolution | app/Services/TimescaleReadingService.php |
Streams from primary + mirrors; aliases merged; unmatched id returns empty |
| Chart config | register_common_devices.attribute_json |
PCS Y key corrected, SQL patch committed |
| Provisioning | gess-stack_build.sh |
fix_env_ownership() after every action |
| Backend config | ems_control_setting.json |
pcs5 port 1520 → 1505 (BMU5 → pcs5) |
9. Still open
- Alias fragility.
device_id_aliasesare fixed strings, but the EMSdevice_idis user-editable. Renaming the label empties the chart again. The durable fix is deriving the stream from the section key (grid_meter1→GRIDMETER1), which needs the frontend resolving againstems_control_setting.json— currently only in the backend container. - Nine 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; some map entries (smokeDetector,heatDetector,thermometer,gasDetector) have nosource_modelyet and cannot move until one exists. - Date/time filtering is not implemented in the UI.
fetch()and the controller already accept a window; the blade has no date control, so nothing supplies one. $limit = 100caps every series regardless of window, so a wide range looks sparse rather than obviously wrong.
10. Agent edit markers
Agent edits to PHP source in the Laravel repository
(GESS_SCADA2_V0.0.8_20.02.2025_git) are registered in DEEPCOPILOT_PATCHES.md
and wrapped in marker pairs 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]
Patch ids for the work above: TS-KEY-ALIAS, TS-COMP-ALIASES,
TS-STREAM-RESOLVE.