TimescaleDB in ems-frontend — Connect and Query Guide
Purpose
ems-frontend (the Laravel app served at ems.gess-sweden.com) reads from two
databases:
| Database | Role | Laravel connection | Server |
|---|---|---|---|
| MariaDB | Application tables — users, sites, configs, everything the app owns | mysql (default) |
mariadb:3306 |
| TimescaleDB | Time-series history — device readings and rolled-up analytics | timescale |
timescaledb:5432 |
MariaDB is not replaced. TimescaleDB is a second connection, so both are available in the same request:
$users = DB::connection('mysql')->table('users')->get();
$rows = DB::connection('timescale')->table('raw_device_readings_ht')->limit(5)->get();
Each service has its own TimescaleDB database: ems_db for ems-frontend,
scada_db for scada-web, gessgo_db for gessgo-web. This guide covers
ems-frontend, so every example uses ems_db.
Companion documents in this folder:
site-analytics-ht-value-guide.md— field-by-field meaning ofsite_analytics_htbess-analytics-ht-value-guide.md—bess_analytics_htdevice-analytics-ht-value-guide.md—device_analytics_htsite_level_data_flow_guide.md— how the rolled-up numbers are calculated
This document covers how to reach the data from Laravel. Those cover what the values mean.
1. Connection reference
These are the values that matter. Everything below is derived from them.
| Setting | Value |
|---|---|
| Host | timescaledb |
| Port | 5432 |
| Database | ems_db |
| Username | scada_user |
| Password | scada_password |
| Schema | public |
| Laravel connection name | timescale |
| Driver | pgsql (PDO) |
timescaledb is a Docker network alias on the gess-stack_db network, which is
why the host is a name and not an IP. The app container resolves it directly.
The PHP container already has the pdo_pgsql extension loaded — no image change
is required.
2. What is already configured
You do not need to change anything to start querying. This section is reference, and matters if the environment is ever rebuilt.
2.1 .env
/opt/gess-stack/ems/frontend/app/.env contains, below the existing DB_*
block:
# ---------------------------------------------------------------------------
# TimescaleDB - secondary connection for time-series data.
# ---------------------------------------------------------------------------
TIMESCALE_DB_HOST=timescaledb
TIMESCALE_DB_PORT=5432
TIMESCALE_DB_DATABASE=ems_db
TIMESCALE_DB_USERNAME=scada_user
TIMESCALE_DB_PASSWORD=scada_password
TIMESCALE_DB_SCHEMA=public
Two things to note:
- These use
TIMESCALE_DB_*rather than the stockDB_*names. The stockpgsqlblock inconfig/database.phpreadsDB_*, which point at MariaDB — reusing them would have pointed both connections atmariadb. Keep them separate. TIMESCALE_DB_DATABASEhas no default inconfig/database.phpon purpose. If it is ever unset, the connection fails loudly instead of quietly opening the wrong database.
This file is not tracked in git (.env is gitignored). The versioned
template lives in the gess-stack repository as the TIMESCALE_DB_* block in
ems.env, which move_and_rename_files.sh copies here on deploy.
The same block is present in local_gess_stack_conf/ems/frontend/app/.env, so
the local-override script cannot undo it.
2.2 config/database.php
The timescale connection sits inside the connections array, immediately
after the stock pgsql block:
'timescale' => [
'driver' => 'pgsql',
'host' => env('TIMESCALE_DB_HOST', 'timescaledb'),
'port' => env('TIMESCALE_DB_PORT', '5432'),
'database' => env('TIMESCALE_DB_DATABASE'),
'username' => env('TIMESCALE_DB_USERNAME', 'scada_user'),
'password' => env('TIMESCALE_DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'schema' => env('TIMESCALE_DB_SCHEMA', 'public'),
'sslmode' => env('TIMESCALE_DB_SSLMODE', 'prefer'),
],
'default' => env('DB_CONNECTION', 'mysql') is untouched, so Eloquent models
without an explicit $connection still use MariaDB. That is what makes the
two coexist safely.
2.3 Verifying the connection
Inside the ems-frontend container:
docker exec ems-frontend php -r '
require "/var/www/html/vendor/autoload.php";
$app = require "/var/www/html/bootstrap/app.php";
$app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();
$r = DB::connection("timescale")->selectOne("select current_database() as db, current_user as usr");
echo "connected to {$r->db} as {$r->usr}\n";'
Expected: connected to ems_db as scada_user.
3. Step-by-step: query a table from a controller
Five steps, using raw_device_readings_ht as the example.
Step 1 — confirm the connection resolves
docker exec -u www-data ems-frontend php artisan tinker --execute="dump(config('database.connections.timescale'));"
If this prints null, a cached config is in the way:
docker exec -u www-data ems-frontend php artisan config:clear
Laravel caches config/ into bootstrap/cache/config.php. A cached config will
not see edits to config/database.php or .env until it is cleared. This is
the single most common cause of "the connection exists but Laravel says it
doesn't".
Step 2 — create the controller
app/Http/Controllers/DeviceReadingController.php:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
class DeviceReadingController extends Controller
{
/**
* Latest N readings for one device type.
*/
public function latest(string $deviceType)
{
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->select('time', 'device_id', 'device_type', 'source_model', 'status', 'soc', 'soh')
->where('device_type', $deviceType)
->orderByDesc('time')
->limit(20)
->get();
return response()->json([
'device_type' => $deviceType,
'count' => $rows->count(),
'data' => $rows,
]);
}
}
Step 3 — register the route
routes/web.php (or routes/api.php for an API route):
use App\Http\Controllers\DeviceReadingController;
Route::get('/device-readings/{deviceType}', [DeviceReadingController::class, 'latest']);
Step 4 — try it
curl -s "http://127.0.0.1:9595/device-readings/grid_meter" -H "Host: ems.gess-sweden.com" | head -40
The web server is the ems-nginx container, published on host port 9595. The
ems-frontend container itself does not publish a port — PHP-FPM listens on
9000 internally and nginx proxies to it.
This app runs Laravel 8.83.
Step 5 — read a device-specific value out of the JSONB payload
Most measurements are not columns. They live in payload, so extract them with
PostgreSQL's ->> operator. Note the explicit ::numeric cast — ->> always
returns text, and comparing text numerically gives wrong answers.
public function frequency(string $deviceType, int $hours = 6)
{
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->selectRaw("date_trunc('hour', time) as bucket")
->selectRaw("count(*) as samples")
->selectRaw("round(avg((payload->>'gridMeter_frequency_hz')::numeric), 3) as avg_hz")
->where('device_type', $deviceType)
->where('time', '>=', now()->subHours($hours))
->groupByRaw("date_trunc('hour', time)")
->orderByDesc('bucket')
->get();
return response()->json($rows);
}
That is the whole pattern. Everything else is a variation on it.
4. Table reference
Seven tables in ems_db, all in the public schema. Five are TimescaleDB
hypertables (physically split into time-based chunks); two are ordinary
PostgreSQL tables.
| Table | Kind | Chunk interval | Rows (2026-10) | Written by |
|---|---|---|---|---|
raw_device_readings_ht |
hypertable | 1 day | ~184,000 | the device collectors, continuously |
alarm_events_ht |
hypertable | 7 days | 0 | alarm service |
bess_analytics_ht |
hypertable | 7 days | 0 | BESS roll-up |
device_analytics_ht |
hypertable | 1 day | 0 | device roll-up |
site_analytics_ht |
hypertable | 30 days | 0 | site roll-up |
active_alarms_current |
plain table | — | 0 | alarm service |
device_metric_config |
plain table | — | 0 | configuration |
Row counts are a snapshot and grow continuously for raw_device_readings_ht.
Only that one table currently holds data.
None of the five hypertables has a primary key. This is a TimescaleDB constraint: a unique index on a hypertable must include the partitioning column, and the collectors write unordered batches. Consequences for Laravel are covered in section 7.
4.1 raw_device_readings_ht
The raw measurements. One row per device per poll. This is the table you will use most.
| Column | Type | Notes |
|---|---|---|
time |
timestamptz |
measurement time; the hypertable partition column |
site_id |
bigint |
|
bess_id |
bigint |
|
device_id |
varchar |
e.g. GRIDMETER1, BATTERY-01 |
gateway_id |
varchar |
|
device_type |
varchar |
e.g. grid_meter, bmu, pcs — see section 5 |
source_model |
varchar |
the collector's model label, e.g. grid_measurements |
payload |
jsonb |
the complete device reading; see section 5 |
soc |
numeric |
state of charge, where the device reports it |
soh |
numeric |
state of health, where the device reports it |
status |
varchar |
normalised status |
status_color |
varchar |
UI hint |
has_alarm |
boolean |
|
ingested_at |
timestamptz |
when the row reached the database |
poll_interval_ms |
integer |
the device's configured poll interval |
save_interval_ms |
integer |
the device's configured save interval |
time and ingested_at normally differ by a fraction of a second. Use time
for querying — it is the partition column, so filtering on it lets TimescaleDB
skip chunks. Filtering on ingested_at reads everything.
4.2 alarm_events_ht
Alarm lifecycle events. Empty at the time of writing.
time, site_id, bess_id, device_id, gateway_id, device_type,
raw_reading_id, parameter, value, severity, error_code,
error_message, alarm_source, color, priority_level, active,
acknowledged, acknowledged_by, acknowledged_at, cleared_at,
cleared_by, notified, created_at.
The nested fields on this table make it the right choice for an alarm history screen. For the current alarm set, see 4.6.
4.3 bess_analytics_ht
Per-BESS roll-up. Empty at the time of writing. See
bess-analytics-ht-value-guide.md for field semantics.
Key columns: time, site_id, bess_id, overall_soc, overall_soh,
battery_health_score, max_cell_voltage, min_cell_voltage,
voltage_imbalance, max_cell_temp, min_cell_temp, temp_imbalance,
active_cluster_count, total_cluster_count, pcs_online_count,
pcs_total_count, pcs_health_score, pcs_derated_count, max_igbt_temp,
cooling_health_score, cooling_units_online, cooling_units_total,
ambient_temp_avg, fire_system_status, gas_detection_status, door_status,
ems_communication_health, ems_command_mode, power_output_kw,
energy_storage_kwh, energy_charged_kwh_24h, energy_discharged_kwh_24h,
bess_health_score, active_alarm_count, critical_alarm_count,
final_status.
4.4 device_analytics_ht
Per-device roll-up. Empty at the time of writing. See
device-analytics-ht-value-guide.md.
time, site_id, bess_id, gateway_id, device_id, device_type, model,
health_score, final_status, communication_ok, alarm_count,
critical_alarm_count, metrics (jsonb), created_at.
4.5 site_analytics_ht
Per-site roll-up, the widest and coarsest table. Empty at the time of writing.
See site-analytics-ht-value-guide.md and site_level_data_flow_guide.md.
Covers BESS (total_bess_count, normal_bess_count, warning_bess_count,
alarm_bess_count, bess_health_score, bess_energy_storage_kwh,
bess_power_output_kw), solar (solar_inverter_count, solar_online_count,
solar_power_output_kw, solar_energy_today_kwh, solar_health_score), EV
charging (ev_charger_count, ev_chargers_online, ev_chargers_in_use,
ev_chargers_faulted, ev_power_draw_kw, ev_energy_delivered_today_kwh,
ev_health_score), grid (grid_import_kw, grid_export_kw,
grid_connection_status), and totals (net_site_power_kw, site_health_score,
total_energy_kwh, total_power_kw, total_alarm_count,
critical_alarm_count, final_status).
4.6 active_alarms_current
Plain table, not a hypertable. The currently active alarm set, keyed by id.
id (PK), site_id, bess_id, device_id, severity, opened_at,
error_message.
Use this for a "what is wrong right now" panel; use alarm_events_ht for
history.
4.7 device_metric_config
Plain table, not a hypertable. Maps raw payload keys to display metadata so the UI knows how to label and threshold a metric.
id (PK), device_type, model, source_key, source_key_pattern,
metric_key, agg_method, unit, is_critical, warn_threshold,
critical_threshold, direction, active, created_at, updated_at.
Pair this with payload when building a chart: look the metric up here to get
its unit and thresholds rather than hardcoding them in the frontend.
5. The JSONB payload: device types and keys
payload holds the complete reading as sent by the collector. Column values
such as soc and status are extracted copies for indexing and filtering; the
payload is the source of truth and contains everything the device reported.
Payload keys are device-type specific, which is why they are not columns — they would be mostly NULL.
Observed device types in raw_device_readings_ht:
device_type |
Readings (2026-10) | Devices |
|---|---|---|
bmu |
85,436 | 1 |
ffr_energy_meter |
24,063 | 1 |
solar_meter |
8,595 | 1 |
grid_meter |
8,593 | 1 |
cooling |
8,561 | 1 |
bess_energy_meter |
8,553 | 1 |
ups |
8,551 | 1 |
fss |
8,549 | 1 |
ac |
8,547 | 1 |
pcs |
8,085 | 2 |
sensor_block |
5,215 | 1 |
frequency_meter |
1,442 | 1 |
Real payload keys for device_type = 'grid_meter':
device_id, meter_key, model, timestamp,
gridMeter_timestamp, gridMeter_voltage_ab_v, gridMeter_current_a_a,
gridMeter_frequency_hz, gridMeter_pf_total, gridMeter_active_power_total_kw,
gridMeter_reactive_power_total_kvar, gridMeter_active_energy_delivered_kwh,
gridMeter_active_energy_received_kwh,
gridMeter_active_energy_delivered_minus_received_kwh,
gridMeter_active_energy_delivered_plus_received_kwh,
gridMeter_alarms, gridMeter_alarms_hex,
gridMeter_digital_input_status_validity_base,
gridMeter_digital_input_status_validity_base_hex
A real row, payload in full:
{
"time": "2026-10-02T23:03:59.044+02:00",
"bess_id": null,
"soc": null,
"soh": null,
"status": null,
"payload": {
"model": "ME231",
"device_id": "GRID-METER-001",
"meter_key": "grid_meter1",
"timestamp": 1790975039044,
"gridMeter_alarms": 0,
"gridMeter_pf_total": 1.0,
"gridMeter_timestamp": 1790975039044,
"gridMeter_alarms_hex": "0x00000000",
"gridMeter_current_a_a": 0.0,
"gridMeter_frequency_hz": 50.0,
"gridMeter_voltage_ab_v": 0.0,
"gridMeter_active_power_total_kw": 30.0,
"gridMeter_reactive_power_total_kvar": 0.0,
"gridMeter_active_energy_received_kwh": 0.0
}
}
Two details worth noticing, because both are easy to get wrong:
device_idappears twice — as a column and insidepayload. The column is indexed and is the one to filter on. The value inside the payload is the device's own label (GRID-METER-001) and can differ in formatting from the column (GRIDMETER1).modeldiffers too: the columnsource_modelholds the collector's label (grid_measurements), whilepayload->>'model'holds the device model (ME231).
Always check which one a requirement means before writing the query.
6. Query cookbook
Every example below was run against the live ems_db and returns results.
Latest readings for a device type
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->where('device_type', 'grid_meter')
->orderByDesc('time')
->limit(10)
->get();
Count in a time window
Use Carbon objects, not concatenated strings — the query builder turns them into bound parameters.
$count = DB::connection('timescale')
->table('raw_device_readings_ht')
->where('time', '>=', now()->subHours(2))
->count();
Read a payload field
$row = DB::connection('timescale')
->table('raw_device_readings_ht')
->selectRaw("payload->>'model' as model")
->selectRaw("(payload->>'gridMeter_frequency_hz')::numeric as frequency_hz")
->where('device_type', 'grid_meter')
->orderByDesc('time')
->first();
echo $row->model; // ME231
echo $row->frequency_hz; // 50.0
Filter on a payload field
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->where('device_type', 'ups')
->whereRaw("(payload->>'ups_battery_voltage_v')::numeric < ?", [220])
->orderByDesc('time')
->limit(50)
->get();
Hourly average of a payload metric
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->selectRaw("date_trunc('hour', time) as bucket, count(*) as samples, round(avg((payload->>'gridMeter_frequency_hz')::numeric), 3) as avg_hz")
->where('device_type', 'grid_meter')
->where('time', '>=', now()->subDay())
->groupByRaw("date_trunc('hour', time)")
->orderByDesc('bucket')
->get();
time_bucket() — the TimescaleDB way to group by time
time_bucket() is what date_trunc() becomes on a hypertable. It is chunk-aware
and handles intervals date_trunc cannot express ('15 minutes',
'5 minutes'). Prefer it for anything finer than an hour.
$row = DB::connection('timescale')->selectOne("
SELECT time_bucket('1 hour', time) AS bucket, count(*) AS samples
FROM raw_device_readings_ht
WHERE device_type = ?
GROUP BY bucket
ORDER BY bucket DESC
LIMIT 1
", ['bmu']);
Distinct device types
$rows = DB::connection('timescale')
->table('raw_device_readings_ht')
->select('device_type', DB::raw('count(*) as readings'))
->groupBy('device_type')
->orderByRaw('count(*) desc')
->get();
Paginate for a list endpoint
$page = DB::connection('timescale')
->table('raw_device_readings_ht')
->where('device_type', 'pcs')
->orderByDesc('time')
->paginate(25);
Always combine paginate() with a time filter on this table. count(*) over
the whole hypertable is expensive and gets slower every day.
Inspect hypertable metadata
$hypertables = DB::connection('timescale')
->table('timescaledb_information.hypertables')
->select('hypertable_name')
->get();
Both databases in one request
$site = DB::connection('mysql')->table('sites')->find($siteId);
$readings = DB::connection('timescale')
->table('raw_device_readings_ht')
->where('site_id', $siteId)
->orderByDesc('time')
->limit(100)
->get();
Note that Laravel does not give you a cross-database join here — they are
different servers. Query both and join in PHP, or use whereIn() with a list of
IDs from MariaDB.
7. Eloquent models for hypertables
Eloquent works, with two constraints that come from the tables themselves.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class DeviceReading extends Model
{
protected $connection = 'timescale';
protected $table = 'raw_device_readings_ht';
// Hypertables have no auto-incrementing primary key.
protected $primaryKey = null;
public $incrementing = false;
// No created_at / updated_at columns.
public $timestamps = false;
protected $guarded = [];
// "time" is a real timestamp; it is not the model's key.
protected $casts = [
'time' => 'datetime',
'ingested_at' => 'datetime',
'payload' => 'array',
'has_alarm' => 'boolean',
];
}
Then:
$rows = DeviceReading::where('device_type', 'ups')->orderByDesc('time')->limit(10)->get();
foreach ($rows as $row) {
echo $row->device_id;
echo $row->payload['ups_battery_voltage_v']; // cast to array, so no ->> needed
}
Casting payload to array is convenient and is what most controllers should
do. It does mean the whole payload is decoded, so for a large list prefer
selectRaw("payload->>'some_key' as some_key") and fetch only what the response
needs.
Reading is safe; writing is not. Without a primary key, save(), update()
and delete() on a retrieved model have no reliable row to target. Treat these
tables as append-only from the collector side and read-only from Laravel. If you
need to write, use the query builder with an explicit insert() of the full row,
including time.
8. Gotchas
config:clear after changing .env or config/database.php. A cached
config silently ignores both. If a working connection suddenly "disappears",
this is almost always why.
No primary keys on hypertables. Do not write ->find($id) against them.
Filter on time and the entity columns.
->> returns text. Always cast before comparing or aggregating numerically:
(payload->>'some_kw')::numeric, not payload->>'some_kw'. Comparing text sorts
'100' before '9'.
time is timestamptz, not timestamp. PostgreSQL returns it in the
server session's timezone (currently +02). If the API contract is UTC, convert
explicitly — for example ->selectRaw("time AT TIME ZONE 'UTC' as time_utc") or
format the Carbon instance — rather than assuming the offset.
Filter on time, not ingested_at. time is the partition column and lets
TimescaleDB prune chunks. ingested_at reads every chunk.
The database is per service. ems-frontend reads ems_db. scada_db and
gessgo_db exist on the same server, and at the time of writing are empty — do
not point the ems connection at them.
Five of the seven tables are empty. Only raw_device_readings_ht has data.
Code written against site_analytics_ht will return zero rows until the roll-up
service is producing them; that is not a connection problem.
9. Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
Connection [timescale] not configured |
Stale config cache | php artisan config:clear |
SQLSTATE[08006] could not connect to server: Name or service not known |
TIMESCALE_DB_HOST not timescaledb, or app not on the gess-stack_db network |
docker inspect ems-frontend --format '{{json .NetworkSettings.Networks}}' |
SQLSTATE[28P01] password authentication failed |
Credentials in .env differ from POSTGRES_PASSWORD on the timescaledb container |
docker inspect timescaledb --format '{{range .Config.Env}}{{println .}}{{end}}' \| grep POSTGRES |
SQLSTATE[3D000] database "..." does not exist |
TIMESCALE_DB_DATABASE is unset or wrong |
Confirm with \l inside the container |
SQLSTATE[42P01] relation "..." does not exist |
Wrong table, or right table but wrong database | \dt in ems_db |
could not find driver |
pdo_pgsql missing |
php -m \| grep pgsql inside the container |
| Empty result, no error | Table is genuinely empty, or the time window has no data | Check with SELECT count(*) first |
Handy one-liners:
# What the app actually resolved
docker exec -u www-data ems-frontend php artisan tinker --execute="dump(config('database.connections.timescale'));"
# List tables
docker exec timescaledb psql -U scada_user -d ems_db -c '\dt'
# Describe one table
docker exec timescaledb psql -U scada_user -d ems_db -c '\d raw_device_readings_ht'
# Why is a query slow
docker exec timescaledb psql -U scada_user -d ems_db -c "EXPLAIN ANALYZE SELECT count(*) FROM raw_device_readings_ht WHERE time >= now() - interval '1 day';"
10. Checklist for a new endpoint
- Start from
DB::connection('timescale')— never bareDB::table(). - Filter on
device_typeand atimewindow. Unbounded queries get slower daily. - Pull payload fields with
selectRaw("payload->>'key' as alias")and cast with::numericwhen the value is a number. - Use
time_bucket()for sub-hour grouping,date_trunc()for hour and above. - Return
timeas UTC if that is the API contract. - Paginate list endpoints, always with a
timebound. - Never write to a hypertable through Eloquent.