> ## Documentation Index
> Fetch the complete documentation index at: https://extended-openai-conversation.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

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

<Warning>
  **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.
</Warning>

## Configuration

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

<ParamField path="query" type="string" required>
  SQL query to execute. Can include Jinja2 templates for parameters.
</ParamField>

## Database Schema

Home Assistant uses SQLite with these main tables:

<AccordionGroup>
  <Accordion title="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    |
  </Accordion>

  <Accordion title="states_meta">
    Entity metadata

    | Column       | Type    | Description       |
    | ------------ | ------- | ----------------- |
    | metadata\_id | INTEGER | Primary key       |
    | entity\_id   | VARCHAR | Entity identifier |
  </Accordion>

  <Accordion title="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 |
  </Accordion>
</AccordionGroup>

## Approach 1: AI-Generated Queries

Let the AI generate SQL queries with examples:

```yaml theme={null}
- 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
```

<Columns cols={2}>
  <div>
    **Get Last Changed Time**

    <Frame>
      <img width="300" alt="SQL Query 1" src="https://github.com/jekalmin/extended_openai_conversation/assets/2917984/5a25db59-f66c-4dfd-9e7b-ae6982ed3cd2" />
    </Frame>
  </div>

  <div>
    **Get State at Specific Time**

    <Frame>
      <img width="300" alt="SQL Query 2" src="https://github.com/jekalmin/extended_openai_conversation/assets/2917984/51faaa26-3294-4f96-b115-c71b268b708e" />
    </Frame>
  </div>
</Columns>

<Info>
  **Flexibility vs Security**: This approach is flexible but allows querying any entity, even unexposed ones.
</Info>

## Approach 2: Validated Queries

Add minimal validation to ensure exposed entities are used:

```yaml theme={null}
- 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 -%}
```

<Tip>
  The `is_exposed_entity_in_query()` function checks if the query references at least one exposed entity.
</Tip>

## Approach 3: Predefined Queries

Maximum security with predefined, validated queries:

```yaml theme={null}
- 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 -%}
```

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

## Template Functions

<AccordionGroup>
  <Accordion title="is_exposed(entity_id)">
    Check if an entity is exposed to the assistant

    ```jinja2 theme={null}
    {% if is_exposed('light.bedroom') %}
      SELECT * FROM states_meta WHERE entity_id = 'light.bedroom'
    {% else %}
      {{ raise("Entity not exposed") }}
    {% endif %}
    ```
  </Accordion>

  <Accordion title="is_exposed_entity_in_query(query)">
    Check if query contains at least one exposed entity

    ```jinja2 theme={null}
    {% if is_exposed_entity_in_query(query) %}
      {{ query }}
    {% else %}
      {{ raise("No exposed entities in query") }}
    {% endif %}
    ```
  </Accordion>

  <Accordion title="raise(message)">
    Throw an error with a message

    ```jinja2 theme={null}
    {% if not valid_condition %}
      {{ raise("Invalid query parameters") }}
    {% endif %}
    ```
  </Accordion>
</AccordionGroup>

## Time Handling

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

```sql theme={null}
-- 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](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones):

```yaml theme={null}
# configuration.yaml or docker-compose
environment:
  TZ: "America/New_York"
```

## Common Queries

### Get Last State Change

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

<CardGroup cols={2}>
  <Card title="Historical Analysis" icon="chart-line">
    Analyze entity behavior patterns over time
  </Card>

  <Card title="State Tracking" icon="clock-rotate-left">
    Find when entities changed to specific states
  </Card>

  <Card title="Usage Statistics" icon="chart-bar">
    Calculate usage statistics and trends
  </Card>

  <Card title="Event Correlation" icon="diagram-project">
    Correlate events across multiple entities
  </Card>
</CardGroup>

## FAQ

<AccordionGroup>
  <Accordion title="Can the AI modify or delete data?">
    No. Database connections are opened in read-only mode. Queries can only SELECT data.
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="How do I handle timezones?">
    Set the `TZ` environment variable to your timezone, or use timezone offsets in queries:

    ```sql theme={null}
    datetime(last_updated_ts, 'unixepoch', '+9 hours')
    ```
  </Accordion>

  <Accordion title="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
  </Accordion>
</AccordionGroup>

## Best Practices

<Steps>
  <Step title="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
  </Step>

  <Step title="Provide good examples">
    When using AI-generated queries, provide 2-3 clear examples in the description showing the expected SQL format.
  </Step>

  <Step title="Handle timezones">
    Always convert timestamps to local time for user-facing results.
  </Step>

  <Step title="Test queries">
    Test SQL queries directly in the database before adding to functions.
  </Step>
</Steps>

## Debugging

Test queries directly:

```bash theme={null}
# Access Home Assistant database
sqlite3 /config/home-assistant_v2.db

# Run query
SELECT * FROM states_meta LIMIT 5;
```

Enable logging:

```yaml theme={null}
logger:
  logs:
    custom_components.extended_openai_conversation: debug
```

## Next Steps

<CardGroup cols={3}>
  <Card title="Native Functions" icon="microchip" href="/functions/native">
    Use get\_history for simpler needs
  </Card>

  <Card title="Composite Functions" icon="layer-group" href="/functions/composite">
    Combine SQL with formatting
  </Card>

  <Card title="SQLite Documentation" icon="book" href="https://www.sqlite.org/docs.html">
    SQLite reference
  </Card>
</CardGroup>