Skip to content

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 of site_analytics_ht
  • bess-analytics-ht-value-guide.md — bess_analytics_ht
  • device-analytics-ht-value-guide.md — device_analytics_ht
  • site_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 stock DB_* names. The stock pgsql block in config/database.php reads DB_*, which point at MariaDB — reusing them would have pointed both connections at mariadb. Keep them separate.
  • TIMESCALE_DB_DATABASE has no default in config/database.php on 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_id appears twice — as a column and inside payload. 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).
  • model differs too: the column source_model holds the collector's label (grid_measurements), while payload->>'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

  1. Start from DB::connection('timescale') — never bare DB::table().
  2. Filter on device_type and a time window. Unbounded queries get slower daily.
  3. Pull payload fields with selectRaw("payload->>'key' as alias") and cast with ::numeric when the value is a number.
  4. Use time_bucket() for sub-hour grouping, date_trunc() for hour and above.
  5. Return time as UTC if that is the API contract.
  6. Paginate list endpoints, always with a time bound.
  7. Never write to a hypertable through Eloquent.