Keep Home Assistant sensor history for years
A guide for the Home Assistant community. Every command and output below comes from a run against the service on 2026-10-04.
Home Assistant's recorder keeps states for 10 days by default (purge_keep_days), and long-term statistics keep only hourly mean, min and max for sensors with a state_class. This guide sends the state changes of your sensors to a table you own, once a minute, with nothing but rest_command and one automation in configuration.yaml: no add-on, no custom component, no relay. At the end you have a table with one row per entity per minute in which it changed (time, entity, state, numeric value, unit, attributes) that you can query with SQL for as long as you keep it.
The table is an Apache Iceberg table in the open Parquet format, stored in Germany: DuckDB, Spark, Trino, Snowflake and the account page's SQL panel read it, and it never depends on Home Assistant's database.
What you need
- Home Assistant with access to
configuration.yaml(the File editor or Studio Code Server add-on, or the config folder of a container install). The run behind this page used Home Assistant 2026.9.4 in the official container image,ghcr.io/home-assistant/home-assistant:stable. - A LakehouseBox account and the
lhboxcommand line (agent setup), on any computer: it is needed once, to create the table and the address Home Assistant posts to. Home Assistant itself needs nothing installed. - DuckDB 1.5.5 or newer on that computer to query (
lhbox duckdbopens it with your catalog attached).
1. Create a catalog and the table
lhbox login
lhbox catalog create home
lhbox table create home.ha.states --column event_time:timestamptz:required --column entity_id:string:required --column state:string --column value:double --column unit:string --column friendly_name:string --column attributes:stringOne row is one state of one entity: state is the state as Home Assistant shows it (21.1, on, washing, unavailable), value is the same state as a number when it is one (null otherwise), attributes is the entity's attributes as a JSON string (query it with json_extract_string).
2. Create the address Home Assistant posts to
A sink with a device route is a private HTTPS address that accepts a JSON body with no headers to set and commits what it receives to the table every five minutes:
lhbox sink create --catalog home --table ha.states --name ha_states --device --save ha-sink.jsonThe address is shown once and written to ha-sink.json (mode 0600) instead of the terminal. Read it with jq -r .device_url ha-sink.json: it looks like https://in.lakehousebox.com/d/<token>. The address is the password: anyone who has it can add rows to this table (and nothing else). Put it in Home Assistant's secrets.yaml, not in configuration.yaml:
# secrets.yaml
lakehousebox_url: https://in.lakehousebox.com/d/<token>If it leaks, lhbox sink device-token ha_states --save ha-sink.json replaces it and the old one stops working at once.
3. Add the rest_command and the automation
Add this to configuration.yaml exactly as it is (the run used it verbatim). Every minute the automation looks for the entities of the listed domains whose state or attributes changed in the minute that just ended, and the rest_command posts them as one JSON array. A minute with no change posts nothing.
rest_command:
lakehousebox:
url: !secret lakehousebox_url
method: post
content_type: "application/json"
timeout: 20
payload: >-
{%- set ns = namespace(rows=[]) -%}
{%- for s in states | selectattr('domain', 'in', domains) -%}
{%- if since < s.last_updated.timestamp() <= until -%}
{%- set ns.rows = ns.rows + [{
"event_time": s.last_updated.isoformat(),
"entity_id": s.entity_id,
"state": s.state,
"value": s.state | float(none),
"unit": state_attr(s.entity_id, 'unit_of_measurement'),
"friendly_name": state_attr(s.entity_id, 'friendly_name'),
"attributes": s.attributes | to_json
}] -%}
{%- endif -%}
{%- endfor -%}
{{ ns.rows | to_json }}
automation:
- id: lakehousebox_history
alias: Send state changes to LakehouseBox
mode: queued
triggers:
- trigger: time_pattern
minutes: "/1"
variables:
domains: ["sensor", "binary_sensor"]
until: "{{ trigger.now.timestamp() }}"
since: "{{ until - 60 }}"
conditions:
- condition: template
value_template: >
{{ states | selectattr('domain', 'in', domains)
| map(attribute='last_updated') | map('as_timestamp')
| select('>', since) | select('<=', until) | list | count > 0 }}
actions:
- action: rest_command.lakehousebox
data:
domains: "{{ domains }}"
since: "{{ since }}"
until: "{{ until }}"
response_variable: sent
- if: "{{ sent.status not in [200, 202] }}"
then:
- action: system_log.write
data:
level: warning
message: "LakehouseBox answered {{ sent.status }}: {{ sent.content }}"If your configuration.yaml already has an automation: line (the default automation: !include automations.yaml), paste the automation into the automations editor in YAML mode instead, without the automation: line. Change domains to the entity domains you want to keep (sensor, binary_sensor, climate, light, …). The warning step uses system_log.write; the run's minimal configuration had to load it with a system_log: line.
Restart Home Assistant (a new rest_command needs a restart). Each minute with a change, the log shows the post and the answer when homeassistant.components.rest_command logs at debug; from the run (the address masked):
2026-10-04 01:02:00.128 DEBUG (MainThread) [homeassistant.components.rest_command] Calling post https://in.lakehousebox.com/d/<token> with headers: {'Content-Type': 'application/json'} and payload: b'[{"event_time":"2026-10-03T23:01:40.483001+00:00","entity_id":"binary_sensor.front_door","state":"off",...
2026-10-04 01:02:00.395 DEBUG (MainThread) [homeassistant.components.rest_command] Success. Url: https://in.lakehousebox.com/d/<token>. Status code: 202. ...The body is a JSON array, one object per entity; two of the four the run sent at 01:02:
[
{"event_time": "2026-10-03T23:01:40.483001+00:00", "entity_id": "binary_sensor.front_door", "state": "off",
"value": null, "unit": null, "friendly_name": "Front door",
"attributes": "{\"device_class\":\"door\",\"friendly_name\":\"Front door\"}"},
{"event_time": "2026-10-03T23:01:40.483261+00:00", "entity_id": "sensor.living_room_temperature", "state": "21.1",
"value": 21.1, "unit": "°C", "friendly_name": "Living room temperature",
"attributes": "{\"state_class\":\"measurement\",\"unit_of_measurement\":\"°C\",\"device_class\":\"temperature\",\"friendly_name\":\"Living room temperature\"}"}
]4. Check that rows arrive
The rows land in the table at the sink's next roll, within five minutes:
lhbox sink get ha_statesFrom the run, six minutes after the first post (an excerpt, two fields of the answer):
"received": {"batches_total": 6, "bytes_total": 7869, "rows_total": 27, "rows_rejected_total": 0},
"last_roll": {"at": "2026-10-03T23:06:09.515Z", "batches": 6, "rows": 27, "rejected_rows": 0, "seconds": 0.425},rows_rejected_total above zero means a row did not fit the table (a key the table lacks, a value of the wrong type); lhbox sink get names where the rejected rows are kept, with the reason.
5. Query it
lhbox duckdb --catalog homeopens DuckDB with the catalog attached as home. From the run (Home Assistant's own sensor.backup_* entities included, since they are in the sensor domain):
SELECT entity_id, count(*) AS n, min(event_time) AS first, max(event_time) AS last,
round(avg(value), 1) AS avg_value, any_value(unit) AS unit
FROM home.ha.states GROUP BY entity_id ORDER BY entity_id;┌────────────────────────────────────────────────┬───────┬───────────────────────────────┬───────────────────────────────┬───────────┬─────────┐
│ entity_id │ n │ first │ last │ avg_value │ unit │
├────────────────────────────────────────────────┼───────┼───────────────────────────────┼───────────────────────────────┼───────────┼─────────┤
│ binary_sensor.front_door │ 6 │ 2026-10-04 01:00:52.181408+02 │ 2026-10-04 01:05:20.486665+02 │ NULL │ NULL │
│ sensor.backup_backup_manager_state │ 1 │ 2026-10-04 01:00:52.299924+02 │ 2026-10-04 01:00:52.299924+02 │ NULL │ NULL │
│ sensor.living_room_humidity │ 6 │ 2026-10-04 01:00:52.18172+02 │ 2026-10-04 01:05:40.484308+02 │ 53.7 │ % │
│ sensor.living_room_temperature │ 6 │ 2026-10-04 01:00:52.181651+02 │ 2026-10-04 01:05:40.484183+02 │ 21.3 │ °C │
│ sensor.washing_machine_status │ 5 │ 2026-10-04 01:00:52.181775+02 │ 2026-10-04 01:04:40.485877+02 │ NULL │ NULL │
└────────────────────────────────────────────────┴───────┴───────────────────────────────┴───────────────────────────────┴───────────┴─────────┘(three more sensor.backup_* rows left out.) Attributes are JSON:
SELECT event_time, entity_id, state, value, unit,
json_extract_string(attributes, '$.device_class') AS device_class
FROM home.ha.states WHERE entity_id LIKE 'sensor.living_room%' ORDER BY event_time LIMIT 4;┌───────────────────────────────┬────────────────────────────────┬─────────┬────────┬─────────┬──────────────┐
│ event_time │ entity_id │ state │ value │ unit │ device_class │
├───────────────────────────────┼────────────────────────────────┼─────────┼────────┼─────────┼──────────────┤
│ 2026-10-04 01:00:52.181651+02 │ sensor.living_room_temperature │ 22.6 │ 22.6 │ °C │ temperature │
│ 2026-10-04 01:00:52.18172+02 │ sensor.living_room_humidity │ 62 │ 62.0 │ % │ humidity │
│ 2026-10-04 01:01:40.483261+02 │ sensor.living_room_temperature │ 21.1 │ 21.1 │ °C │ temperature │
│ 2026-10-04 01:01:40.483407+02 │ sensor.living_room_humidity │ 67 │ 67.0 │ % │ humidity │
└───────────────────────────────┴────────────────────────────────┴─────────┴────────┴─────────┴──────────────┘Limits and gotchas
- At most one row per entity per minute. The automation sends the current state of each entity that changed in the last minute, so a sensor that updates every 10 seconds gives one row a minute (its last value), not six. In the run the test sensors updated every 20 seconds and each kept one row a minute. Triggering more often (for example
seconds: "/30"withsince: "{{ until - 30 }}") gives finer history; the run used one minute only. A sink accepts 6 posts a minute sustained (bursts of 30), and beyond that answers429 rate_limited. - Keep the template in the
rest_commandpayload. Building the rows in the automation and passing them as a variable does not work: Home Assistant re-reads a rendered variable as a Python value, and the body the run sent that way was not JSON (400 unreadable_body). Passing onlydomains,sinceanduntiland rendering the JSON in the payload withto_jsonis what works. - A failed post is not retried.
rest_commanddoes not queue: when the internet or the service is down, those minutes are not sent. Home Assistant's own recorder still has them for its retention period. - 256 KiB per post. One row with its attributes was about 300 bytes in the run, so a minute can carry roughly 800 changed entities; narrow
domains, or drop theattributeskey from the payload (and keep the column, it stays null), if you have more. - Every key in the payload must be a column. A key the table does not have rejects the row (
unknown_field) rather than being dropped silently. To add one, add the column to the table first. - Five minutes to appear. Rows are committed every 5 minutes by default (
--roll-secondsonlhbox sink create, at least 60); what has been posted but not yet committed shows aslaginlhbox sink get.
Next
- Ingest: sinks and the device route for the roll, rejects and the device route's rules.
- DuckDB for connecting outside
lhbox duckdb, and the other engines that read the table. - Scheduled SQL to build daily summaries from this table on a schedule.