Skip to content

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:


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 own source_model, so a device_id filter 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_id may be an array, merged with device_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_aliases are fixed strings, but the EMS device_id is 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 against ems_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 no source_model yet 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 = 100 caps 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.