Timescale Database Reader

A Home Assistant integration that reads historical data from a TimescaleDB database filled by the LTSS integration or the Scribe integration, and exposes it over the Home Assistant WebSocket API.

This integration does not work with arbitrary TimescaleDB databases; the schema has to match LTSS or Scribe. If you need another schema, open an issue or contribute a reader for it.

Companion card: timescale-plotly-card.

---

Supported databases

Source	Hypertable	Notes
LTSS	ltss	Columns time, entity_id, state. No numeric column.
Scribe	states_raw	Columns time, metadata_id, state, value, attributes. entity_id lives in the separate entities table; the states view joins the two.

Scribe schema note: current Scribe versions write states_raw keyed on metadata_id, not entity_id. states is a view over states_raw joined with entities. Anything that has to be fast should read a minute table rather than that view.

state vs value

Numbers and text live in different columns, and this trips people up:

	Numeric sensor	Text sensor (on/off, Cooling in progress, …)
states_raw.value	the number (23.24)	0
states_raw.state	NULL	the text
sensor_minute.value	the number (23.24)	0
sensor_minute.state	'0'	the text

For a numeric entity only value carries the reading. In sensor_minute the state column additionally shows the literal string '0' rather than NULL, because the fill procedure substitutes COALESCE(latest.state, '0').

So anything consuming this data must read value — or the avg_state field of the query response, which already resolves it — and fall back to state only for text entities. Reading state first flattens every numeric series to zero, and because '0' parses as a perfectly valid number, nothing errors: you just get a chart full of zeroes.

---

Requirements

TimescaleDB	2.13 or newer. The SQL uses by_range(), which does not exist before 2.13
PostgreSQL	14 or newer
Scribe or LTSS	installed and already recording, before you run any of the SQL
Database role	one that may create tables, views and jobs in the target database

Install order

The steps depend on each other; doing them out of order is the most common way to end up with empty charts.

1. Install Scribe (or LTSS) and let it record for a few minutes, so states_raw has data
2. Run SQL/scribe/01_sensor_minute_aggregate.sql, then backfill the aggregate
3. Run SQL/scribe/02_sensor_minute_table.sql, then seed sensor_minute
4. Install this integration and add a connection, with table set to sensor_minute
5. Install the card and build a chart

Step 4 does not verify that the table exists — the connection test only runs SELECT 1 — so you can fill it in before or after, but nothing will chart until steps 2 and 3 are done.

Installation

HACS (recommended)

1. HACS → ⋮ → Custom repositories (older HACS versions: HACS → Integrations → ⋮)
2. URL: https://github.com/remmob/timescale_database_reader, category: Integration
3. Search for Timescale Database Reader, install
4. Restart Home Assistant

Manual

1. Copy custom_components/timescale_database_reader into your Home Assistant custom_components directory
2. Restart Home Assistant

Configuration

Settings → Devices & Services → Add Integration → Timescale Database Reader, then fill in host, port, username, password, database name and the default table.

Field	Example	Meaning
host / port	192.168.1.10 / 5432	TimescaleDB server
database	statistics	Database name
name	Statistics	Friendly name; also what database: in a card matches on
table	sensor_minute	Default table when a query does not pass table

Add one integration entry per database if you have several (for example LTSS and Scribe).

Two places hold these settings. The integration reads {**entry.data, **entry.options}, so anything set through the options dialog wins over what you entered when first adding the connection. If a changed table does not seem to take effect, check both — reconfigure updates data, the options flow writes options.

Database roles and grants

If the role that creates the SQL objects is the same one the integration connects with, there is nothing to do. If they differ — which happens easily when an admin runs the SQL as postgres while Scribe and the reader use their own roles — the reader will fail with permission denied. Both SQL files end with a commented grant block; uncomment it and substitute your role:

GRANT USAGE ON SCHEMA public TO <reader_role>;
GRANT SELECT ON sensor_minute, sensor_minute_aggregate_entity, entities TO <reader_role>;

Check who owns what with:

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public'
UNION ALL
SELECT viewname, viewowner FROM pg_views WHERE schemaname = 'public' ORDER BY 1;

Matching a card to a connection: a card's database: option is matched case-insensitively against both the entry's database name and its friendly name. database: scribe only resolves to a connection literally called scribe; otherwise the first configured connection is used as a fallback. Name the entry after what you write in your cards to avoid surprises.

---

Recommended: build the minute tables

Charts want one value per entity per minute, with the last known value carried forward so lines and bars have no gaps. Raw state rows cannot give you that: Home Assistant only writes a row when a value changes.

Two layers do this. Install them in order.

Step 1 — the 1-minute continuous aggregate

SQL/scribe/01_sensor_minute_aggregate.sql

Creates sensor_minute_aggregate (one row per entity per minute in which something changed) plus sensor_minute_aggregate_entity, a thin view that joins entities back on so downstream queries can use entity_id. It also sets the retention and compression policies and a refresh policy that runs every minute.

The aggregate has to group by metadata_id, because a continuous aggregate may only read one hypertable — it cannot join entities itself. That is what the extra view is for.

After creating it, backfill the history you already have. Run this outside a transaction block:

CALL refresh_continuous_aggregate('sensor_minute_aggregate', NULL, NULL);

Step 2 — the prefilled sensor_minute table

SQL/scribe/02_sensor_minute_table.sql

Creates the sensor_minute hypertable (minute, entity_id, state, value), its indexes, its policies, the sensor_minute_refresh() procedure, and a job that runs it every minute. Each run carries the last known value forward for every entity for every minute.

Seed the table once before the job can take over — the procedure only extends an existing series and does nothing on an empty table. The commented-out INSERT at the bottom of the file does that.

Start small. The seed writes one row per entity per minute: entities × days × 1440. With 443 entities and 30 days that is roughly 19 million rows in one transaction, which can run for a long time, bloat the WAL and hold locks while it does. Seed a couple of days first, confirm your charts look right, and only then widen. The file also contains a day-at-a-time loop that commits per day — use that for a long backfill.

Check that the job is running:

SELECT job_id, proc_name, schedule_interval, next_start
FROM timescaledb_information.jobs
WHERE proc_name = 'every_minute_refresh';

SELECT last_run_status, last_run_started_at, total_failures
FROM timescaledb_information.job_stats
WHERE job_id = (SELECT job_id FROM timescaledb_information.jobs
                WHERE proc_name = 'every_minute_refresh');

Which table should a card use?

Table	Speed	When to use
sensor_minute	fast, indexed on (entity_id, minute DESC)	The default choice. Every chart, every range.
sensor_minute_aggregate_entity	fast	When you want only the minutes in which something actually changed, without the carried-forward filler.
states_raw / ltss	moderate	Raw, unaggregated history.
states	slow	Avoid for charts; it is a view that joins on every read.
sensor_minute_scribe	very slow	Legacy LOCF view, superseded by sensor_minute. See the warning below.

type: custom:timescale-plotly-card
database: statistics
table: sensor_minute
sensor_id: sensor.temperature_woonkamer

⚠️ Avoid sensor_minute_scribe (and sensor_minute_ltss). These views build a grid of every minute since the oldest bucket × every entity and run a correlated subquery per cell against another view, so there is no usable index. It gets pathological for recently added entities: for every minute before their first reading the subquery has to walk the entire series to prove there is nothing there, so a window reaching further back than that entity's first reading times out (>120 s) while a window starting after it returns instantly. One such entity is enough to stall the queries of other cards on the same page. The sensor_minute table exists precisely to avoid this. The old files are kept in SQL/ for reference.

Disk usage

Raw state rows are the bulky part. Every row carries the entity's attributes as JSON — icon, friendly name, unit — repeated in full on each write. On a real installation that was 154 of roughly 205 bytes of payload per row.

Compression fixes most of it. Measured on 443 entities writing about a million rows a day:

	before	after
One weekly states_raw chunk	300 MB	8.9 MB
Thirteen older chunks	383 MB	16 MB
sensor_minute (LOCF data compresses extremely well)	10.9 GB	22 MB

Set compress_after well below drop_after. If they are equal — both three months, say — the retention policy deletes each chunk at the exact moment it becomes eligible for compression, so nothing is ever compressed and the policy does nothing at all. The SQL here uses seven days.

Check whether it is actually working:

SELECT hypertable_name,
       count(*) FILTER (WHERE is_compressed) AS compressed,
       count(*) AS chunks
FROM timescaledb_information.chunks GROUP BY 1;

Only the chunk currently being written to should show up as uncompressed. If that chunk itself is large, shorten chunk_time_interval: the active chunk cannot be compressed, so its interval sets the floor on your uncompressed working set.

Compression is a storage format change, not a summarisation: row counts, values and timestamps are unchanged, and decompress_chunk() reverses it.

Keeping the minute table in step with reality

The continuous aggregate trails real time by a minute or two. If the refresh procedure only ever appends rows after max(minute), those trailing rows keep the value they happened to have when they were written and are never corrected — the lag becomes permanent.

You notice it on counters. A utility_meter with cycle: hourly resets exactly at :00, but in a lagging sensor_minute the reset only shows up two minutes later. A chart that buckets on the clock hour then samples each hour a couple of minutes too early, so every bar under-reports and the tail of each period lands in the next bar.

sensor_minute_refresh() therefore reprocesses a short trailing window (5 minutes by default) instead of only appending, and upserts on (minute, entity_id). Pass a different overlap if your setup needs it:

CALL public.sensor_minute_refresh(INTERVAL '10 minutes');

Check the lag with:

SELECT max(minute) AS last_row,
       date_trunc('minute', now()) AS now_minute,
       date_trunc('minute', now()) - max(minute) AS lag
FROM sensor_minute;

---

Querying over the WebSocket API

Message type: timescale/query.

Field	Type	Required	Description
sensor_id	string	yes	Entity ID to query
start	ISO string or Unix timestamp	yes	Window start
end	ISO string or Unix timestamp	yes	Window end
limit	int	no	Max rows returned (0 = no limit, max 10000). Applied to the tail of the result
downsample	int	no	Bucket size in seconds (0 = raw rows)
downsample_method	avg | last	no	Aggregation within a bucket. Defaults to last for minute/aggregate tables, otherwise avg
table	string	no	Table or view to read; falls back to the entry's configured table
database	string	no	Which connection to use, matched on database name or friendly name
entry_id	string	no	Config entry to use; takes precedence over database

Limits enforced by the integration: window ≤ 365 days, limit ≤ 10000, result ≤ 50000 rows.

The reader picks the time column automatically: time, else bucket, else minute. If the table has a value column it is preferred, with a numeric cast of state as fallback.

Response

With downsample > 0:

Field	Description
bucket	Bucket start
avg_state	Aggregated numeric value (avg() or last()) — use this
state	Last raw text state in the bucket
min_state / max_state	Numeric min and max within the bucket

With downsample = 0:

Field	Description
time	Row timestamp
state	Raw text state
avg_state	Resolved numeric value — use this

Remember the state / value split above: for numeric entities state is the placeholder '0'. Always read avg_state, and fall back to state only when you are mapping text states.

Example

import asyncio, json, websockets

async def query_timescale():
    uri = "ws://homeassistant.local:8123/api/websocket"
    async with websockets.connect(uri) as ws:
        await ws.send(json.dumps({"type": "auth", "access_token": "YOUR_LONG_LIVED_TOKEN"}))
        print(await ws.recv())

        await ws.send(json.dumps({
            "id": 1,
            "type": "timescale/query",
            "sensor_id": "sensor.temperature_woonkamer",
            "database": "statistics",
            "table": "sensor_minute",
            "start": "2026-01-01T00:00:00Z",
            "end": "2026-01-01T12:00:00Z",
            "downsample": 300,
            "downsample_method": "last",
        }))
        while True:
            msg = await ws.recv()
            print(msg)
            if '"result"' in msg or '"error"' in msg:
                break

asyncio.run(query_timescale())

---

Multiple databases

Add one integration entry per database. Cards select a connection per card or per series:

type: custom:timescale-plotly-card
title: Living room climate
entities:
  - sensor_id: sensor.temperature_woonkamer
    database: statistics
    table: sensor_minute
  - sensor_id: sensor.amber_4h_average_ambient_temperature
    database: ltss
    table: ltss

A series-level database or table overrides the card-level one. Without either, the first configured connection and its default table are used.

---

Troubleshooting

"No database connection available" — no config entry is loaded. Check the integration page for a failed entry.

Every series is flat zero — something is reading state instead of value/avg_state. See state vs value.

Queries time out — you are almost certainly on sensor_minute_scribe or states. Switch to table: sensor_minute. If that is also slow, verify sensor_minute_entity_idx exists.

A newly added entity has no history — sensor_minute only starts at the point the entity first appeared in the aggregate. Re-run the backfill after CALL refresh_continuous_aggregate(...).

Counters look a couple of minutes off — see Keeping the minute table in step with reality.

Nothing at all comes back — the reader logs every query at warning level with prefix [WEBSOCKET]. Check the Home Assistant log.

---

Issues & contributions

Open an issue or pull request on GitHub.

---

©2026 Bommer Software | Author: Mischa Bommer

Note: This integration is a work in progress. Features and functionality may change or be incomplete.