For agents · the instructions your agent follows. The page for people: A home that remembers

Move Home Assistant history to tables you keep for years

A guide for the Home Assistant community. Every command and output below comes from a run against the service on 2026-10-12.

These are the instructions an agent follows to move a Home Assistant's history into LakehouseBox and keep it flowing in. Home Assistant's recorder keeps every state change for 10 days by default (purge_keep_days), and hourly long-term statistics for sensors with a state_class since it started. The steps:

  1. Get access: with no account, lhbox new gives you a catalog you can write at once (§1).
  2. Move the history Home Assistant already has: a snapshot of the recorder's database, one SQL file, two imports: every state of the last 10 days and every hourly statistic it ever kept (§2). Answer a first question.
  3. The person keeps it with a link and a code (§3). This is the one step that needs them: before it the catalog is deleted after 72 hours, and the live feed cannot be created.
  4. Switch on the live feed: rest_command and one automation post every change once a minute to the same table (§4–§6): no add-on, no custom component, no relay.

The tables are Apache Iceberg tables in the open Parquet format, stored in Germany: DuckDB, Spark, Trino, Snowflake and the account page's SQL panel read them, and they never depend on Home Assistant's database.

What you need

  • Home Assistant whose configuration folder you can read and write, and a way to restart it. Home Assistant Container or Core on the same machine; on Home Assistant OS, the Terminal & SSH add-on gives a shell in /config. If you cannot reach the folder, do §1–§3 from any computer the person copies the database file to, and give the person §5's YAML to paste.
  • The default SQLite recorder: home-assistant_v2.db in the configuration folder. A recorder: db_url: pointing at MariaDB or PostgreSQL is not covered by §2 (not run); the live feed (§4–§6) works with any recorder.
  • Python 3 (for lhbox and the snapshot) and DuckDB 1.5.5 or newer for the export: pip install 'duckdb>=1.5.5' pytz (the Python module needs pytz to print times with a time zone), or the duckdb binary.
  • The lhbox command line: curl -fsSL https://lakehousebox.com/install.sh | sh.

The live feed (§4–§6) was recorded on Home Assistant 2026.9.4 in the official container image with the built-in demo integration and template sensors standing in for a solar inverter and a car charger (2026-10-04); its payload was hardened on 2026-10-11 after a real house's Hue and tariff integrations broke the first version. §1–§3 were recorded on 2026-10-12. Entities from any integration (Nest, Hue, Somfy, Nuki, Tesla, an inverter) are the same domains with the same attributes, and are moved and sent the same way.

1. Get access

In this order:

  • lhbox whoami answers: a key is already saved. Use it, and make a catalog for the history: lhbox catalog create lakehouse.
  • The person is at the keyboard: lhbox login prints a link and a code; they sign up or log in, tick write and approve. Then lhbox catalog create lakehouse.
  • Otherwise, and this is the usual case when you are setting this up on your own:
lhbox new

It makes a catalog named lakehouse that you can write at once, with no account. Until a person claims it (§3): 1 GB, nothing public, tables and imports only (no sink: the live feed waits for the claim), and it is deleted with its data 72 hours after it was made (lhbox status says when). Running it accepts the Terms of Service and the Acceptable Use Policy for whoever runs you: tell the person.

The catalog's name is lakehouse below. A claim into an organisation that already has a lakehouse gives it a free name (lakehouse_2): after §3, lhbox catalog list says which.

2. Move the history Home Assistant already has

Snapshot the database first. Never read the live home-assistant_v2.db: Home Assistant writes to it all the time. Python's SQLite backup copies a consistent snapshot, write-ahead log included, without stopping Home Assistant (replace /config with the configuration folder):

python3 -c "import sqlite3; s = sqlite3.connect('file:/config/home-assistant_v2.db?mode=ro', uri=True); d = sqlite3.connect('ha-snapshot.db'); s.backup(d); d.close()"

Choose what to move. The SQL below moves the domains the live feed sends (§5) except device_tracker: phones and cars carry their location, so add it only if the person said yes. Leave out anything they would not want kept.

Export both kinds of history to Parquet with this SQL (move.sql), run in DuckDB next to the snapshot (duckdb < move.sql, or python3 -c "import duckdb; duckdb.connect().execute(open('move.sql').read())"):

INSTALL sqlite; LOAD sqlite;
ATTACH 'ha-snapshot.db' AS ha (TYPE sqlite, READ_ONLY);

-- states: one row per recorded state, in the live feed's thirteen columns
COPY (
  WITH s AS (
    SELECT to_timestamp(st.last_updated_ts) AS event_time,
           coalesce(m.entity_id, st.entity_id) AS entity_id,
           st.state,
           coalesce(a.shared_attrs, st.attributes) AS attrs
    FROM ha.states st
    LEFT JOIN ha.states_meta m ON m.metadata_id = st.metadata_id
    LEFT JOIN ha.state_attributes a ON a.attributes_id = st.attributes_id
    WHERE st.last_updated_ts IS NOT NULL
  )
  SELECT event_time, entity_id, state,
         TRY_CAST(state AS DOUBLE) AS value,
         json_extract_string(attrs, '$.unit_of_measurement') AS unit,
         json_extract_string(attrs, '$.friendly_name') AS friendly_name,
         TRY_CAST(json_extract_string(attrs, '$.current_temperature') AS DOUBLE) AS current_temperature,
         CASE WHEN split_part(entity_id, '.', 1) IN ('climate', 'water_heater')
              THEN TRY_CAST(json_extract_string(attrs, '$.temperature') AS DOUBLE) END AS target_temperature,
         TRY_CAST(json_extract_string(attrs, '$.current_humidity') AS DOUBLE) AS current_humidity,
         json_extract_string(attrs, '$.hvac_action') AS hvac_action,
         TRY_CAST(json_extract_string(attrs, '$.current_position') AS DOUBLE) AS position,
         CASE WHEN split_part(entity_id, '.', 1) = 'light' AND state IN ('on', 'off')
              THEN round(coalesce(TRY_CAST(json_extract_string(attrs, '$.brightness') AS DOUBLE), 0) / 2.55) END AS brightness,
         attrs AS attributes
  FROM s
  WHERE split_part(entity_id, '.', 1) IN ('sensor', 'binary_sensor', 'climate', 'cover', 'light', 'lock', 'event')
    AND NOT regexp_matches(entity_id, '^sensor\.(backup_|sun_)')
  ORDER BY event_time
) TO 'states.parquet' (FORMAT parquet);

-- long-term statistics: one row per entity per hour, kept by Home Assistant since it was installed
COPY (
  SELECT to_timestamp(st.start_ts) AS hour_start,
         m.statistic_id, m.name, m.unit_of_measurement AS unit, m.source,
         st.mean, st.min, st.max, st.state, st.sum,
         to_timestamp(st.last_reset_ts) AS last_reset
  FROM ha.statistics st JOIN ha.statistics_meta m ON m.id = st.metadata_id
  ORDER BY hour_start, statistic_id
) TO 'statistics.parquet' (FORMAT parquet);

SELECT 'states' AS t, count(*) AS rows, count(DISTINCT entity_id) AS entities, min(event_time) AS first, max(event_time) AS last FROM 'states.parquet'
UNION ALL
SELECT 'statistics', count(*), count(DISTINCT statistic_id), min(hour_start), max(hour_start) FROM 'statistics.parquet';

The states come out in the thirteen columns the live feed writes, so both land in one table:

One row is one state of one entity:

column what it holds
event_time when the state or an attribute last changed (last_updated)
entity_id sensor.living_room_temperature, climate.hallway, … ; the domain is the part before the dot
state the state as Home Assistant shows it: 21.1, on, heat, locked, open, home, unavailable
value the state as a number when it is one (sensors), null otherwise
unit unit_of_measurement (°C, W, kWh, %)
friendly_name the name you gave the entity
current_temperature, target_temperature, current_humidity, hvac_action thermostats (climate; water_heater has the first two): the room temperature, the setpoint, the humidity, and what it is doing (heating, cooling, idle, off)
position covers (blinds, shutters, garage doors, cover): 0 is closed, 100 is open; also valves
brightness lights: 0 to 100 %, 0 when off
attributes every attribute of the entity as a JSON string (query it with json_extract_string)

The six middle columns are null for the entities they do not apply to. They are columns, not only keys inside attributes, because they are what you plot and average: a column is typed (no casting), and Parquet keeps its min and max per file, so WHERE target_temperature > 22 skips files instead of parsing JSON in every row.

The statistics are one row per entity per hour: mean, min and max for measurements (temperature, power), state and sum for meters (sum is the running total: the energy of an hour is the difference between two hours' sum).

Import them, each in one commit:

lhbox table import lakehouse.ha.states states.parquet
lhbox table import lakehouse.ha.statistics statistics.parquet

Keep the snapshot's last event_time (the last of the states line): §6 fills the minutes between it and the moment the live feed starts.

Answer a first question to show the person their history is there. lhbox query runs it on LakehouseBox's side, read-only, with nothing to install and no credential written to disk (an unclaimed catalog may query):

lhbox query "SELECT statistic_id, count(*) AS hours, round(avg(mean), 1) AS avg_c FROM lakehouse.ha.statistics WHERE unit = '°C' GROUP BY statistic_id ORDER BY statistic_id"

Name entities by statistic_id: the statistics' name is empty for most entities.

3. Keep it: the person claims the catalog

lhbox claim

It prints a link and a code. Give both to the person: they open the link, sign up or log in, choose where the catalog goes and pick read and write (the page offers read first). Tell them so in those words: with read, the live feed cannot be created. Then:

lhbox claim --resume --wait

saves this machine's new key (the key from lhbox new stops working at that moment). If its answer says "downgraded": true (the person kept read), the key cannot create the sink, and the account page cannot raise it: the claim's key is a connection, approved at one level. Sign this machine in as the person instead (the key acts as them, write included), and give them the new link and code:

lhbox login --new --no-wait
lhbox login --resume

A DuckDB secret saved with lhbox duckdb --persist before the claim holds the old key: run it again, or use lhbox query. If the person is not there, stop here: tell them the link, the code, and when the catalog is deleted if nobody claims it (lhbox status). A code lasts a day at most; lhbox claim again makes a new one.

4. 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 lakehouse --table ha.states --name ha_states --device --save ha-sink.json

The table exists from §2. If you skipped §2, create it first:

lhbox table create lakehouse.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 current_temperature:double --column target_temperature:double --column current_humidity:double --column hvac_action:string --column position:double --column brightness:double --column attributes:string

The 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.

5. Add the rest_command and the automation

Add this to configuration.yaml exactly as it is. 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.

The attr_json macro at the top writes the attributes column. Real integrations put values in attributes that are not JSON: a Hue light's effect list is a set, a tariff integration's validity is a date. With Home Assistant's to_json one such value fails the whole minute's post, for every entity, and rest_command only logs it. The macro keeps lists and maps, turns sets and tuples into lists, NaN and infinity into null, and dates and any other object into text. (This page's first version used a | to_json; a real house failed on Hue and on its tariff integration within the first day. Checked 2026-10-11 with Home Assistant 2026.2.3's own template engine: the old payload failed on a set, a date and an arbitrary object; this one rendered valid JSON for all of them.)

rest_command:
  lakehousebox:
    url: !secret lakehousebox_url
    method: post
    content_type: "application/json"
    timeout: 20
    payload: >-
      {%- macro attr_json(v) -%}
        {%- if v is none or v is boolean -%}{{ v | to_json }}
        {%- elif v is number -%}{{ v if v | string not in ['nan', 'NaN', 'inf', '-inf', 'Infinity', '-Infinity'] else 'null' }}
        {%- elif v is string -%}{{ v | to_json }}
        {%- elif v is mapping -%}{
          {%- for k, x in v.items() -%}{{ k | string | to_json }}:{{ attr_json(x) }}{{ ',' if not loop.last }}{%- endfor -%}}
        {%- elif v is iterable -%}[
          {%- for x in v -%}{{ attr_json(x) }}{{ ',' if not loop.last }}{%- endfor -%}]
        {%- else -%}{{ v | string | to_json }}
        {%- endif -%}
      {%- endmacro -%}
      {%- set ns = namespace(rows=[]) -%}
      {%- for s in states | selectattr('domain', 'in', domains)
                          | selectattr('entity_id', 'search', include)
                          | rejectattr('entity_id', 'search', exclude) -%}
        {%- if since < s.last_updated.timestamp() <= until -%}
          {%- set a = s.attributes -%}
          {%- 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": a.get('unit_of_measurement') | string if a.get('unit_of_measurement') is not none else none,
            "friendly_name": a.get('friendly_name') | string if a.get('friendly_name') is not none else none,
            "current_temperature": a.get('current_temperature') | float(none),
            "target_temperature": a.get('temperature') | float(none)
                                  if s.domain in ['climate', 'water_heater'] else none,
            "current_humidity": a.get('current_humidity') | float(none),
            "hvac_action": a.get('hvac_action') | string if a.get('hvac_action') is not none else none,
            "position": a.get('current_position') | float(none),
            "brightness": ((a.get('brightness') or 0) / 2.55) | round(0)
                          if s.domain == 'light' and s.state in ['on', 'off'] else none,
            "attributes": attr_json(a)
          }] -%}
        {%- 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", "climate", "cover", "light", "lock", "event", "device_tracker"]
      include: ""
      exclude: '^sensor\.(backup_|sun_)'
      until: "{{ trigger.now.timestamp() }}"
      since: "{{ until - 60 }}"
    conditions:
      - condition: template
        value_template: >
          {{ states | selectattr('domain', 'in', domains)
                    | selectattr('entity_id', 'search', include)
                    | rejectattr('entity_id', 'search', exclude)
                    | map(attribute='last_updated') | map('as_timestamp')
                    | select('>', since) | select('<=', until) | list | count > 0 }}
    actions:
      - action: rest_command.lakehousebox
        data:
          domains: "{{ domains }}"
          include: "{{ include }}"
          exclude: "{{ exclude }}"
          since: "{{ since }}"
          until: "{{ until }}"
        response_variable: sent
      - if: "{{ sent.status in [200, 202] }}"
        then:
          - action: input_datetime.set_datetime
            target:
              entity_id: input_datetime.lakehousebox_last_upload
            data:
              timestamp: "{{ now().timestamp() }}"
        else:
          - action: system_log.write
            data:
              level: warning
              message: "LakehouseBox answered {{ sent.status }}: {{ sent.content }}"

input_datetime:
  lakehousebox_last_upload:
    name: LakehouseBox last upload
    has_date: true
    has_time: true

The input_datetime records the last accepted post; Know when uploads stop turns it into an alert. 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. The warning step uses system_log.write; the run's minimal configuration had to load it with a system_log: line. Choose what to send explains domains, include and exclude.

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 09:21:00.249 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-04T07:20:32.204847+00:00","entity_id":"device_tracker.demo_paulus",...
2026-10-04 09:21:00.492 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; that post carried 12 (6,144 bytes: two trackers, the four energy stand-ins, a thermostat, a cover, the button event, two lights and a lock). Three of them, with attributes in full to show what each kind carries (the setpoint the run's own demo automation had just changed):

[
 {"event_time": "2026-10-04T07:20:30.192805+00:00", "entity_id": "climate.hvac", "state": "cool", "value": null,
  "unit": null, "friendly_name": "Hvac", "current_temperature": 22.0, "target_temperature": 22.0,
  "current_humidity": 54.2, "hvac_action": "cooling", "position": null, "brightness": null,
  "attributes": "{\"hvac_modes\":[\"off\",\"heat\",\"cool\",\"auto\",\"dry\",\"fan_only\"],\"min_temp\":7,\"max_temp\":35,\"min_humidity\":30,\"max_humidity\":99,\"target_humidity_step\":5,\"fan_modes\":[\"on_low\",\"on_high\",\"auto_low\",\"auto_high\",\"off\"],\"swing_modes\":[\"auto\",\"1\",\"2\",\"3\",\"off\"],\"swing_horizontal_modes\":[\"auto\",\"rangefull\",\"off\"],\"current_temperature\":22,\"temperature\":22.0,\"target_temp_high\":null,\"target_temp_low\":null,\"current_humidity\":54.2,\"humidity\":67.4,\"fan_mode\":\"on_high\",\"hvac_action\":\"cooling\",\"swing_mode\":\"off\",\"swing_horizontal_mode\":\"auto\",\"friendly_name\":\"Hvac\",\"supported_features\":943}"},
 {"event_time": "2026-10-04T07:20:33.209055+00:00", "entity_id": "cover.hall_window", "state": "open", "value": null,
  "unit": null, "friendly_name": "Hall Window", "current_temperature": null, "target_temperature": null,
  "current_humidity": null, "hvac_action": null, "position": 30.0, "brightness": null,
  "attributes": "{\"is_closed\":false,\"current_position\":30,\"friendly_name\":\"Hall Window\",\"supported_features\":15}"},
 {"event_time": "2026-10-04T07:20:30.195991+00:00", "entity_id": "light.ceiling_lights", "state": "on", "value": null,
  "unit": null, "friendly_name": "Ceiling Lights", "current_temperature": null, "target_temperature": null,
  "current_humidity": null, "hvac_action": null, "position": null, "brightness": 20,
  "attributes": "{\"min_color_temp_kelvin\":2000,\"max_color_temp_kelvin\":6535,\"supported_color_modes\":[\"color_temp\",\"hs\"],\"color_mode\":\"color_temp\",\"brightness\":51,\"color_temp_kelvin\":2631,\"hs_color\":[28.55,67.974],\"rgb_color\":[255,164,82],\"xy_color\":[0.532,0.388],\"friendly_name\":\"Ceiling Lights\",\"supported_features\":0}"}
]

Home Assistant's brightness is 0 to 255 (51 here); the payload turns it into percent (20).

6. Check that rows arrive

The rows land in the table at the sink's next roll, within five minutes:

lhbox sink get ha_states

From the run, 29 minutes after the first post (an excerpt, two fields of the answer):

"received": {"batches_total": 30, "bytes_total": 207364, "rows_total": 421, "rows_rejected_total": 0},
"last_roll": {"at": "2026-10-04T07:32:03.724Z", "batches": 6, "rows": 70, "rejected_rows": 0, "seconds": 0.28},

Fill the gap. The restart that switched the feed on posts every entity at once; the states between the snapshot of §2 and that restart are only in Home Assistant's database. Take a new snapshot, add one line to the WHERE of the states query in move.sql with the two times (the last event_time of §2, and the restart), write gap.parquet instead of states.parquet, and append it:

    AND event_time > TIMESTAMPTZ '2026-10-12 09:02:20+00' AND event_time < TIMESTAMPTZ '2026-10-12 10:15:00+00'
lhbox table import lakehouse.ha.states gap.parquet --mode append

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.

Know when uploads stop

rest_command fails quietly: a post that cannot be sent (no internet, a payload Home Assistant cannot render, the service down) leaves a line in the log and nothing else. Add this template sensor so Home Assistant itself shows when uploads stop. It turns on when no post has been accepted for 15 minutes:

template:
  - binary_sensor:
      - name: LakehouseBox uploads stalled
        unique_id: lakehousebox_uploads_stalled
        device_class: problem
        state: >
          {{ now().timestamp() - (state_attr('input_datetime.lakehousebox_last_upload', 'timestamp') or 0) > 900 }}

If your configuration.yaml already has a template: line, add the binary_sensor item under it. Point a notification at binary_sensor.lakehousebox_uploads_stalled turning on, as you would for any other problem sensor. A house that can go quiet for longer than 15 minutes (nothing in the listed domains changes) raises the 900 seconds. The sensor is on from the moment it is added until the first post is accepted. Checked 2026-10-11 against a local stand-in for the address: the time moved on every accepted post, and with the address unreachable the sensor turned on once the threshold passed (the test used 90 seconds).

7. Query it

lhbox duckdb --catalog lakehouse

opens DuckDB with the catalog attached under its name. The queries and outputs below are from the 2026-10-04 run, whose catalog was named home: write your catalog's name (lakehouse) where they say home. All outputs below are from the run; the times are shown in the computer's time zone (Madrid, +02).

What arrived, per domain:

SELECT split_part(entity_id, '.', 1) AS domain, count(DISTINCT entity_id) AS entities, count(*) AS rows,
       min(event_time) AS first, max(event_time) AS last
FROM home.ha.states GROUP BY domain ORDER BY domain;
┌────────────────┬──────────┬───────┬───────────────────────────────┬───────────────────────────────┐
│     domain     │ entities │ rows  │             first             │             last              │
├────────────────┼──────────┼───────┼───────────────────────────────┼───────────────────────────────┤
│ binary_sensor  │        3 │     6 │ 2026-10-04 09:02:48.389571+02 │ 2026-10-04 09:03:33.635255+02 │
│ climate        │        3 │    30 │ 2026-10-04 09:02:48.391003+02 │ 2026-10-04 09:31:30.18749+02  │
│ cover          │        5 │    37 │ 2026-10-04 09:02:48.39323+02  │ 2026-10-04 09:31:36.22016+02  │
│ device_tracker │        3 │    62 │ 2026-10-04 09:02:48.381112+02 │ 2026-10-04 09:31:32.199813+02 │
│ event          │        2 │    32 │ 2026-10-04 09:02:48.359269+02 │ 2026-10-04 09:31:32.197903+02 │
│ light          │        6 │    58 │ 2026-10-04 09:02:48.39452+02  │ 2026-10-04 09:31:30.190397+02 │
│ lock           │        4 │    36 │ 2026-10-04 09:02:48.394971+02 │ 2026-10-04 09:31:32.195681+02 │
│ sensor         │       16 │   160 │ 2026-10-04 09:02:48.260073+02 │ 2026-10-04 09:32:00.176532+02 │
└────────────────┴──────────┴───────┴───────────────────────────────┴───────────────────────────────┘

Thermostats (climate): the room temperature against the setpoint, and what the thermostat was doing:

SELECT event_time, entity_id, state, hvac_action, current_temperature, target_temperature, current_humidity
FROM home.ha.states WHERE entity_id LIKE 'climate.%' ORDER BY event_time DESC, entity_id LIMIT 6;
┌───────────────────────────────┬──────────────┬─────────┬─────────────┬─────────────────────┬────────────────────┬──────────────────┐
│          event_time           │  entity_id   │  state  │ hvac_action │ current_temperature │ target_temperature │ current_humidity │
├───────────────────────────────┼──────────────┼─────────┼─────────────┼─────────────────────┼────────────────────┼──────────────────┤
│ 2026-10-04 09:31:30.18749+02  │ climate.hvac │ cool    │ cooling     │                22.0 │               21.0 │             54.2 │
│ 2026-10-04 09:29:30.199203+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               23.0 │             54.2 │
│ 2026-10-04 09:27:30.189651+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               18.0 │             54.2 │
│ 2026-10-04 09:26:30.184751+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               22.0 │             54.2 │
│ 2026-10-04 09:25:30.184194+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               18.0 │             54.2 │
│ 2026-10-04 09:24:30.189893+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               21.0 │             54.2 │
└───────────────────────────────┴──────────────┴─────────┴─────────────┴─────────────────────┴────────────────────┴──────────────────┘

The run changed the setpoint of climate.hvac once a minute; the demo thermostats do not change their room temperature by themselves. A real one (Nest, tado, Netatmo) moves current_temperature and hvac_action on its own, and each change is a row. climate.ecobee is in heat_cool mode, where Home Assistant gives a range (target_temp_low, target_temp_high in attributes) instead of one setpoint, so target_temperature is null.

Covers (blinds, shutters, garage doors): the position over time:

SELECT event_time, entity_id, state, position
FROM home.ha.states WHERE entity_id = 'cover.hall_window' ORDER BY event_time DESC LIMIT 5;
┌───────────────────────────────┬───────────────────┬─────────┬──────────┐
│          event_time           │     entity_id     │  state  │ position │
├───────────────────────────────┼───────────────────┼─────────┼──────────┤
│ 2026-10-04 09:31:36.22016+02  │ cover.hall_window │ open    │     70.0 │
│ 2026-10-04 09:30:36.208295+02 │ cover.hall_window │ open    │     10.0 │
│ 2026-10-04 09:29:37.236123+02 │ cover.hall_window │ open    │     70.0 │
│ 2026-10-04 09:28:33.2207+02   │ cover.hall_window │ closed  │      0.0 │
│ 2026-10-04 09:27:32.198656+02 │ cover.hall_window │ open    │     30.0 │
└───────────────────────────────┴───────────────────┴─────────┴──────────┘

Lights: brightness in percent, 0 when off:

SELECT event_time, entity_id, state, brightness
FROM home.ha.states WHERE entity_id IN ('light.bed_light', 'light.ceiling_lights') ORDER BY event_time DESC, entity_id LIMIT 6;
┌───────────────────────────────┬──────────────────────┬─────────┬────────────┐
│          event_time           │      entity_id       │  state  │ brightness │
├───────────────────────────────┼──────────────────────┼─────────┼────────────┤
│ 2026-10-04 09:31:30.190397+02 │ light.ceiling_lights │ on      │       50.0 │
│ 2026-10-04 09:31:30.189496+02 │ light.bed_light      │ on      │       71.0 │
│ 2026-10-04 09:30:30.192124+02 │ light.ceiling_lights │ on      │       10.0 │
│ 2026-10-04 09:30:30.186702+02 │ light.bed_light      │ off     │        0.0 │
│ 2026-10-04 09:29:30.202582+02 │ light.ceiling_lights │ on      │       20.0 │
│ 2026-10-04 09:29:30.201591+02 │ light.bed_light      │ on      │       71.0 │
└───────────────────────────────┴──────────────────────┴─────────┴────────────┘

Locks: how often each one was unlocked, and when last:

WITH l AS (
  SELECT entity_id, event_time, state,
         lag(state) OVER (PARTITION BY entity_id ORDER BY event_time) AS before
  FROM home.ha.states WHERE entity_id LIKE 'lock.%'
)
SELECT entity_id, count(*) FILTER (state = 'unlocked' AND before IS DISTINCT FROM 'unlocked') AS times_unlocked,
       max(event_time) FILTER (state = 'unlocked') AS last_unlocked, arg_max(state, event_time) AS state_now
FROM l GROUP BY entity_id ORDER BY entity_id;
┌────────────────────────────┬────────────────┬───────────────────────────────┬───────────┐
│         entity_id          │ times_unlocked │         last_unlocked         │ state_now │
├────────────────────────────┼────────────────┼───────────────────────────────┼───────────┤
│ lock.front_door            │              9 │ 2026-10-04 09:31:32.195681+02 │ unlocked  │
│ lock.kitchen_door          │              1 │ 2026-10-04 09:03:33.64072+02  │ unlocked  │
│ lock.openable_lock         │              0 │ NULL                          │ locked    │
│ lock.poorly_installed_door │              1 │ 2026-10-04 09:03:33.640754+02 │ unlocked  │
└────────────────────────────┴────────────────┴───────────────────────────────┴───────────┘

Events (event: doorbells, buttons, camera motion and person events where the integration provides them): the state of an event entity is the time of its last event, and the kind of event is the event_type attribute:

SELECT event_time, entity_id, json_extract_string(attributes, '$.event_type') AS event_type
FROM home.ha.states WHERE entity_id LIKE 'event.%' AND state <> 'unknown' ORDER BY event_time DESC LIMIT 3;
┌───────────────────────────────┬─────────────────────────┬────────────┐
│          event_time           │        entity_id        │ event_type │
├───────────────────────────────┼─────────────────────────┼────────────┤
│ 2026-10-04 09:31:32.197903+02 │ event.push_button_press │ pressed    │
│ 2026-10-04 09:30:32.197349+02 │ event.push_button_press │ pressed    │
│ 2026-10-04 09:29:32.210712+02 │ event.push_button_press │ pressed    │
└───────────────────────────────┴─────────────────────────┴────────────┘

Trackers (device_tracker: phones, cars): the state is the zone (home, not_home, a zone name) and the position is in attributes:

SELECT event_time, entity_id, state,
       json_extract(attributes, '$.latitude')::double AS lat, json_extract(attributes, '$.longitude')::double AS lon
FROM home.ha.states WHERE entity_id LIKE 'device_tracker.%' ORDER BY event_time DESC, entity_id LIMIT 3;
┌───────────────────────────────┬──────────────────────────────────┬──────────┬──────────┬───────────┐
│          event_time           │            entity_id             │  state   │   lat    │    lon    │
├───────────────────────────────┼──────────────────────────────────┼──────────┼──────────┼───────────┤
│ 2026-10-04 09:31:32.199813+02 │ device_tracker.demo_anne_therese │ not_home │ 32.87041 │ 117.21973 │
│ 2026-10-04 09:31:32.199531+02 │ device_tracker.demo_paulus       │ not_home │ 32.86445 │ 117.22187 │
│ 2026-10-04 09:30:32.199241+02 │ device_tracker.demo_anne_therese │ not_home │ 32.86539 │ 117.22031 │
└───────────────────────────────┴──────────────────────────────────┴──────────┴──────────┴───────────┘

A tracker sends a row each time its position changes, so this is a location history: leave device_tracker out of domains if you do not want one.

Energy: solar, house and car per day

Power sensors (W) from an inverter, a smart meter and a car charger are plain sensor rows. Because a row is sent only when the value changes, each reading holds until the next one, and the energy of a day is the sum of each reading times the seconds until the next (W·s ÷ 3,600,000 = kWh). The run's three stand-in sensors were sensor.solar_power, sensor.house_consumption and sensor.car_charging_power; use your own entity ids:

WITH p AS (
  SELECT entity_id, event_time, value,
         lead(event_time) OVER (PARTITION BY entity_id ORDER BY event_time) AS next_time
  FROM home.ha.states
  WHERE entity_id IN ('sensor.solar_power', 'sensor.house_consumption', 'sensor.car_charging_power')
)
SELECT date_trunc('day', event_time)::date AS day,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.solar_power') / 3.6e6, 2) AS solar_kwh,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.house_consumption') / 3.6e6, 2) AS house_kwh,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.car_charging_power') / 3.6e6, 2) AS car_kwh,
       round(sum(epoch(next_time - event_time)) FILTER (entity_id = 'sensor.solar_power') / 3600, 2) AS hours_covered
FROM p WHERE next_time IS NOT NULL GROUP BY day ORDER BY day;
┌────────────┬───────────┬───────────┬─────────┬───────────────┐
│    day     │ solar_kwh │ house_kwh │ car_kwh │ hours_covered │
├────────────┼───────────┼───────────┼─────────┼───────────────┤
│ 2026-10-04 │      1.36 │      0.68 │    1.56 │          0.49 │
└────────────┴───────────┴───────────┴─────────┴───────────────┘

hours_covered is how much of the day the readings span: the run lasted 29 minutes (0.49 hours), so these are the kWh of those minutes, not of a day (the stand-ins drew random values, which is why the car outdid the house). Days are cut in the time zone of the computer running DuckDB; SET TimeZone = 'Europe/Madrid'; first fixes it to yours. Two things make this an estimate: within a minute only the last reading is kept (a load that switches on and off inside one minute is missed), and if Home Assistant is down for an hour the last reading before the outage counts for the whole hour.

Where the integration has an energy counter (state_class: total_increasing, in kWh: an inverter's daily or total yield, a meter's import, a car's energy added), the counter is exact: the day's energy is its largest value minus its smallest (or its largest alone, for a counter that resets at midnight). From the run's stand-in counter, sensor.solar_energy:

SELECT date_trunc('day', event_time)::date AS day, round(max(value) - min(value), 3) AS solar_kwh
FROM home.ha.states WHERE entity_id = 'sensor.solar_energy' GROUP BY day ORDER BY day;
┌────────────┬───────────┐
│    day     │ solar_kwh │
├────────────┼───────────┤
│ 2026-10-04 │     1.288 │
└────────────┴───────────┘

The stand-in counter and the stand-in power sensor drew their values independently, so the two solar figures of the run do not agree; on a real installation both describe the same panels.

Choose what to send

A home with hundreds of entities sends only what changed, but some entities change all the time and are of no use later (the sun's position, signal strengths, uptime counters, update entities). Three variables of the automation choose:

  • domains: the entity domains to send. The run used sensor, binary_sensor, climate, cover, light, lock, event and device_tracker; add switch, fan, valve, water_heater, media_player or any other domain the same way. A domain not listed sends nothing.
  • exclude: a regular expression; entities whose entity_id it finds are left out. The run's '^sensor\.(backup_|sun_)' dropped Home Assistant's backup sensors and the sun sensors. To exclude nothing, use '^$' (it matches no entity_id). For several patterns, join them with |: '^sensor\.(sun_|.*_rssi$|.*_uptime$)|^update\.'.
  • include: a regular expression an entity_id must match to be sent; "" (empty) matches every entity. To keep only a list, name them: '^(climate\.|lock\.|sensor\.(solar_power|house_consumption|car_charging_power)$)'. With this include (and exclude: '^$'), the run's posts carried only the three thermostats, the four locks and the three power sensors: 10 entities in the minute after a restart, then 5 a minute.

Keep the three in single quotes in YAML (a backslash inside double quotes is an escape). An entity is sent when its domain is in domains, include finds it, and exclude does not.

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 stand-in sensors updated every 20 seconds and each kept one row a minute. Triggering more often (for example seconds: "/30" with since: "{{ 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 answers 429 rate_limited.
  • 256 KiB per post. In the run a row averaged about 500 bytes with its attributes (6,006 bytes for 12 rows a minute); the largest was a thermostat at 980 bytes, because attributes carries its lists of modes. A post of 256 KiB therefore holds roughly 500 changed entities. If you have more, narrow what is sent (Choose what to send), or replace attr_json(a) with none in the payload: the column stays and is null, and a row shrinks to about 300 bytes.
  • A restart sends every entity. When Home Assistant starts, every entity gets a new last_updated, so the first minute after a restart posts all the entities of the listed domains at once (in the run, 42 entities and 19,906 bytes in one post). That is the minute that comes closest to 256 KiB; if it is answered 413, narrow domains or exclude (the restart minute is lost, the minutes after it are not affected).
  • Keep the template in the rest_command payload. 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 sent that way was not JSON (400 unreadable_body). Passing only domains, include, exclude, since and until and rendering the JSON in the payload with to_json is what works.
  • A failed post is not retried. rest_command does 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. Know when uploads stop makes it visible.
  • 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. A table created with the seven columns of this page's first version (before the thermostat, cover and light columns) rejects the rows of this payload: create the table with all thirteen columns and a sink for it.
  • Five minutes to appear. Rows are committed every 5 minutes by default (--roll-seconds on lhbox sink create, at least 60); what has been posted but not yet committed shows as lag in lhbox sink get.

See it on a dashboard

In Claude, the person adds LakehouseBox under Connectors (https://mcp.lakehousebox.com/mcp) and approves it; on the approval page they tick read on the catalog, since the connector sees only the catalogs ticked there. Then they ask Claude for a dashboard ("solar, house and car energy per day, and inside vs outside temperature, from the lakehouse catalog"): Claude writes the queries and draws it, and each query runs live on the connector with the viewer's own access, so whoever they share it with needs read on the catalog. Recorded 2026-10-12 on the test house of §1–§3.

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 (energy per day, thermostat hours heating) from this table on a schedule.
Followed this guide and something did not work, or your setup differs? Write to hello@lakehousebox.com; we fix the guide, not just the reply.