SQLite Functions

Query Home Assistant’s database directly

SQLite functions query Home Assistant’s database to retrieve historical data, analyze patterns, and answer complex questions about entity states over time.

Security Considerations

SQLite functions provide direct database access. While connections are read-only, they can query data beyond exposed entities. Use validation to ensure queries only access intended data.

Configuration

function:
  type: sqlite
  query: "SELECT * FROM states WHERE entity_id = '{{ entity_id }}'"

query · string · required

SQL query to execute. Can include Jinja2 templates for parameters.

Database Schema

Home Assistant uses SQLite with these main tables:

states

Entity state records

Column	Type	Description
state_id	INTEGER	Primary key
metadata_id	INTEGER	Foreign key to states_meta
state	VARCHAR	Current state value
last_changed_ts	FLOAT	Timestamp of last change
last_updated_ts	FLOAT	Timestamp of last update
old_state_id	INTEGER	Previous state reference

states_meta

Entity metadata

Column	Type	Description
metadata_id	INTEGER	Primary key
entity_id	VARCHAR	Entity identifier

events

System events

Column	Type	Description
event_id	INTEGER	Primary key
event_type	VARCHAR	Type of event
time_fired_ts	FLOAT	Timestamp
event_data	TEXT	JSON event data

Approach 1: AI-Generated Queries

Let the AI generate SQL queries with examples:

- spec:
    name: query_histories_from_db
    description: >-
      Use this function to query histories from Home Assistant SQLite database.
      Example:
        Question: When did bedroom light turn on?
        Answer: SELECT datetime(s.last_updated_ts, 'unixepoch', 'localtime') last_updated_ts FROM states s INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id INNER JOIN states old ON s.old_state_id = old.state_id WHERE sm.entity_id = 'light.bedroom' AND s.state = 'on' AND s.state != old.state ORDER BY s.last_updated_ts DESC LIMIT 1
        Question: Was living room light on at 9 am?
        Answer: SELECT datetime(s.last_updated_ts, 'unixepoch', 'localtime') last_updated, s.state FROM states s INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id INNER JOIN states old ON s.old_state_id = old.state_id WHERE sm.entity_id = 'switch.livingroom' AND s.state != old.state AND datetime(s.last_updated_ts, 'unixepoch', 'localtime') < '2023-11-17 08:00:00' ORDER BY s.last_updated_ts DESC LIMIT 1
    parameters:
      type: object
      properties:
        query:
          type: string
          description: A fully formed SQL query.
  function:
    type: sqlite

Get Last Changed Time

Get State at Specific Time

Flexibility vs Security: This approach is flexible but allows querying any entity, even unexposed ones.

Approach 2: Validated Queries

Add minimal validation to ensure exposed entities are used:

- spec:
    name: query_histories_from_db
    description: >-
      Use this function to query histories from Home Assistant SQLite database.
      Example:
        Question: When did bedroom light turn on?
        Answer: SELECT datetime(s.last_updated_ts, 'unixepoch', 'localtime') last_updated_ts FROM states s INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id INNER JOIN states old ON s.old_state_id = old.state_id WHERE sm.entity_id = 'light.bedroom' AND s.state = 'on' AND s.state != old.state ORDER BY s.last_updated_ts DESC LIMIT 1
    parameters:
      type: object
      properties:
        query:
          type: string
          description: A fully formed SQL query.
  function:
    type: sqlite
    query: >-
      {%- if is_exposed_entity_in_query(query) -%}
        {{ query }}
      {%- else -%}
        {{ raise("entity_id should be exposed.") }}
      {%- endif -%}

The is_exposed_entity_in_query() function checks if the query references at least one exposed entity.

Approach 3: Predefined Queries

Maximum security with predefined, validated queries:

- spec:
    name: get_last_updated_time_of_entity
    description: Use this function to get last updated time of entity
    parameters:
      type: object
      properties:
        entity_id:
          type: string
          description: The target entity
  function:
    type: sqlite
    query: >-
      {%- if is_exposed(entity_id) -%}
        SELECT datetime(s.last_updated_ts, 'unixepoch', 'localtime') as last_updated_ts
        FROM states s
          INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
          INNER JOIN states old ON s.old_state_id = old.state_id
        WHERE sm.entity_id = '{{entity_id}}' AND s.state != old.state ORDER BY s.last_updated_ts DESC LIMIT 1
      {%- else -%}
        {{ raise("entity_id should be exposed.") }}
      {%- endif -%}

Security vs Flexibility: This approach is most secure but less flexible. The AI can only use predefined query patterns.

Template Functions

is_exposed(entity_id)

Check if an entity is exposed to the assistant

{% if is_exposed('light.bedroom') %}
  SELECT * FROM states_meta WHERE entity_id = 'light.bedroom'
{% else %}
  {{ raise("Entity not exposed") }}
{% endif %}

is_exposed_entity_in_query(query)

Check if query contains at least one exposed entity

{% if is_exposed_entity_in_query(query) %}
  {{ query }}
{% else %}
  {{ raise("No exposed entities in query") }}
{% endif %}

raise(message)

Throw an error with a message

{% if not valid_condition %}
  {{ raise("Invalid query parameters") }}
{% endif %}

Time Handling

SQLite stores timestamps in UTC as Unix epochs. Convert to local time:

-- Convert to local time
datetime(last_updated_ts, 'unixepoch', 'localtime')

-- Or adjust with timezone offset
datetime(last_updated_ts, 'unixepoch', '+9 hours')  -- For Asia/Seoul

Set Timezone

Set the TZ environment variable to your timezone:

# configuration.yaml or docker-compose
environment:
  TZ: "America/New_York"

Common Queries

Get Last State Change

SELECT
  datetime(s.last_updated_ts, 'unixepoch', 'localtime') as last_changed,
  s.state
FROM states s
INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'light.bedroom'
  AND s.state != (SELECT state FROM states WHERE state_id = s.old_state_id)
ORDER BY s.last_updated_ts DESC
LIMIT 1

Get State at Specific Time

SELECT
  datetime(s.last_updated_ts, 'unixepoch', 'localtime') as time,
  s.state
FROM states s
INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'sensor.temperature'
  AND datetime(s.last_updated_ts, 'unixepoch', 'localtime') < '2024-01-01 09:00:00'
ORDER BY s.last_updated_ts DESC
LIMIT 1

Count State Changes

SELECT
  COUNT(*) as changes
FROM states s
INNER JOIN states_meta sm ON s.metadata_id = sm.metadata_id
WHERE sm.entity_id = 'binary_sensor.door'
  AND s.state != (SELECT state FROM states WHERE state_id = s.old_state_id)
  AND datetime(s.last_updated_ts, 'unixepoch', 'localtime') > datetime('now', '-7 days')

Use Cases

Historical Analysis

Analyze entity behavior patterns over time

State Tracking

Find when entities changed to specific states

Usage Statistics

Calculate usage statistics and trends

Event Correlation

Correlate events across multiple entities

FAQ

Can the AI modify or delete data?

No. Database connections are opened in read-only mode. Queries can only SELECT data.

Can queries access unexposed entities?

Yes, unless you use validation functions like is_exposed() or is_exposed_entity_in_query(). Choose the appropriate security level for your needs.

How do I handle timezones?

Set the TZ environment variable to your timezone, or use timezone offsets in queries:

datetime(last_updated_ts, 'unixepoch', '+9 hours')

Why use SQLite instead of get_history?

SQLite queries provide:

• More flexible filtering and aggregation
• Better performance for complex queries
• Access to more detailed state information
• Ability to join multiple entities

Best Practices

1. Choose appropriate security

   Select the security approach that matches your needs:

   • Approach 1: Maximum flexibility, minimal security
   • Approach 2: Balanced, validates exposed entities
   • Approach 3: Maximum security, limited flexibility
2. Provide good examples

   When using AI-generated queries, provide 2-3 clear examples in the description showing the expected SQL format.

3. Handle timezones

   Always convert timestamps to local time for user-facing results.

4. Test queries

   Test SQL queries directly in the database before adding to functions.

Debugging

Test queries directly:

# Access Home Assistant database
sqlite3 /config/home-assistant_v2.db

# Run query
SELECT * FROM states_meta LIMIT 5;

Enable logging:

logger:
  logs:
    custom_components.extended_openai_conversation: debug

Next Steps

Native Functions

Use get_history for simpler needs

Composite Functions

Combine SQL with formatting

SQLite Documentation

SQLite reference