8000 Update database schema by fetzerch · Pull Request #39643 · home-assistant/home-assistant.io · GitHub
[go: up one dir, main page]
Skip to content

Update database schema #39643

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
wants to merge 1 commit into
base: current
Choose a base branch
from
Open

Update database schema #39643

wants to merge 1 commit into from

Conversation

fetzerch
Copy link
@fetzerch fetzerch commented Jun 19, 2025 •
edited by c0ffeeca7
Loading

The database schema has changed slightly, a few columns and indices have been added in recent versions.
This is the output of Home Assistant 2025.6.1.

https://deploy-preview-39643--home-assistant-docs.netlify.app/docs/backend/database/

Proposed change

Type of change

  • Spelling, grammar or other readability improvements (current branch).
  • Adjusted missing or incorrect information in the current documentation (current branch).
  • Added documentation for a new integration I'm adding to Home Assistant (next branch).
  • Added documentation for a new feature I'm adding to Home Assistant (next branch).
  • Removed stale or deprecated documentation.

Additional information

  • Link to parent pull request in the codebase:
  • Link to parent pull request in the Brands repository:
  • This PR fixes or closes issue: fixes #

Checklist

  • This PR uses the correct branch, based on one of the following:
    • I made a change to the existing documentation and used the current branch.
    • I made a change that is related to an upcoming version of Home Assistant and used the next branch.
  • The documentation follows the Home Assistant documentation standards.

Summary by CodeRabbit

  • New Features

    • Added new columns to statistics-related tables to support additional data tracking.
    • Introduced new database indexes to improve performance on event and state queries.

  • Refactor

    • Adjusted the order of columns and table definitions for improved schema organization.
    • Reordered existing indexes for consistency.

@fetzerch
Update database schema
d08acd5 
The database schema has changed slightly, a few columns and indices
have been added in recent versions.
This is the output of Home Assistant 2025.6.1.
@home-assistant home-assistant bot added the current This PR goes into the current branch label Jun 19, 2025
@netlify Netlify
Copy link
netlify bot commented Jun 19, 2025 •
edited
Loading

✅ Deploy Preview for home-assistant-docs ready!

Name Link
🔨 Latest commit d08acd5
🔍 Latest deploy log https://app.netlify.com/projects/home-assistant-docs/deploys/68547cc8b429e400088c7da0
😎 Deploy Preview https://deploy-preview-39643--home-assistant-docs.netlify.app
📱 Preview on mobile
Toggle QR Code...

QR Code

Use your smartphone camera to open QR code link.

To edit notification comments on pull requests, go to your Netlify project configuration.

@coderabbitai coderabbitai
< 8000 details class="details-overlay details-reset position-relative d-inline-block"> Copy link
Contributor
coderabbitai bot commented Jun 19, 2025
📝 Walkthrough

Walkthrough

The database schema was updated by adding new columns to several tables, changing the order of some columns, and reordering or introducing new indexes. The migration_changes table was moved to a new position within the schema. These changes affect the structure and indexing of the statistics_meta, states, statistics, and statistics_short_term tables.

Changes

File(s) Change Summary
source/_docs/backend/database.markdown Added mean_type column to statistics_meta; added mean_weight to statistics and statistics_short_term; moved last_reported_ts in states; moved migration_changes table; added and reordered several indexes.

Sequence Diagram(s)

sequenceDiagram
    participant Client
    participant Database

    Client->>Database: Alter table statistics_meta (add mean_type)
    Client->>Database: Alter table statistics (add mean_weight)
    Client->>Database: Alter table statistics_short_term (add mean_weight)
    Client->>Database: Alter table states (move last_reported_ts)
    Client->>Database: Drop migration_changes table
    Client->>Database: Recreate migration_changes table at new position
    Client->>Database: Add/reorder indexes on events, states, statistics, statistics_short_term
Loading

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share
🪧 Tips

Chat

There are 3 ways to chat with CodeRabbit:

  • Review comments: Directly reply to a review comment made by CodeRabbit. Example:
    • I pushed a fix in commit <commit_id>, please review it.
    • Explain this complex logic.
    • Open a follow-up GitHub issue for this discussion.
  • Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
    • @coderabbitai explain this code block.
    • @coderabbitai modularize this function.
  • PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
    • @coderabbitai gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.
    • @coderabbitai read src/utils.ts and explain its main purpose.
    • @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.
    • @coderabbitai help me debug CodeRabbit configuration file.

Support

Need help? Create a ticket on our support page for assistance with any issues or questions.

Note: Be mindful of the bot's finite context window. It's strongly recommended to break down tasks such as reading entire modules into smaller chunks. For a focused discussion, use review comments to chat about specific files and their changes, instead of using the PR comments.

CodeRabbit Commands (Invoked using PR comments)

  • @coderabbitai pause to pause the reviews on a PR.
  • @coderabbitai resume to resume the paused reviews.
  • @coderabbitai review to trigger an incremental review. This is useful when automatic reviews are disabled for the repository.
  • @coderabbitai full review to do a full review from scratch and review all the files again.
  • @coderabbitai summary to regenerate the summary of the PR.
  • @coderabbitai generate docstrings to generate docstrings for this PR.
  • @coderabbitai generate sequence diagram to generate a sequence diagram of the changes in this PR.
  • @coderabbitai resolve resolve all the CodeRabbit review comments.
  • @coderabbitai configuration to show the current CodeRabbit configuration for the repository.
  • @coderabbitai help to get help.

Other keywords and placeholders

  • Add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.
  • Add @coderabbitai summary to generate the high-level summary at a specific location in the PR description.
  • Add @coderabbitai or @coderabbitai title anywhere in the PR title to generate the title automatically.

CodeRabbit Configuration File (.coderabbit.yaml)

  • You can programmatically configure CodeRabbit by adding a .coderabbit.yaml file to the root of your repository.
  • Please see the configuration documentation for more information.
  • If your editor has YAML language server enabled, you can add the path at the top of this file to enable auto-completion and validation: # yaml-language-server: $schema=https://coderabbit.ai/integrations/schema.v2.json

Documentation and Community

  • Visit our Documentation for detailed information on how to use CodeRabbit.
  • Join our Discord Community to get help, request features, and share feedback.
  • Follow us on X/Twitter for updates and announcements.

coderabbitai[bot]
coderabbitai bot reviewed Jun 19, 2025
View reviewed changes
Copy link
Contributor
@coderabbitai coderabbitai bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Actionable comments posted: 0

🧹 Nitpick comments (2)
source/_docs/backend/database.markdown (2)

77-77: Document and verify the new mean_type column.
The addition of mean_type INTEGER NOT NULL DEFAULT 0 is correct, but please add a brief description of mean_type in the prose above and confirm that the default value 0 matches Home Assistant version 2025.6.1.

143-143: Maintain one column per line in DDL for readability.
Split combined column declarations (metadata_id, last_reported_ts, sum, mean_weight) so each column appears on its own line, matching the existing style.

Also applies to: 163-163, 181-181

📜 Review details

Configuration used: CodeRabbit UI
Review profile: CHILL
Plan: Pro
Cache: Disabled due to data retention organization setting
Knowledge Base: Disabled due to data retention organization setting

📥 Commits

Reviewing files that changed from the base of the PR and between e2a466b and d08acd5.

📒 Files selected for processing (1)
  • source/_docs/backend/database.markdown (5 hunks)
⏰ Context from checks skipped due to timeout of 90000ms (3)
  • GitHub Check: Redirect rules - home-assistant-docs
  • GitHub Check: Header rules - home-assistant-docs
  • GitHub Check: Pages changed - home-assistant-docs
🔇 Additional comments (2)
source/_docs/backend/database.markdown (2)

206-206: Approve new indexes and reordering.
The additions of ix_events_time_fired_ts, ix_states_context_id_bin, ix_states_last_updated_ts, ix_statistics_start_ts, and the reordered ix_statistics_short_term_statistic_id_start_ts correctly reflect the updated schema for Home Assistant 2025.6.1.

Also applies to: 210-210, 214-214, 220-220, 224-224

228-232: Approve relocated migration_changes table definition.
Moving the CREATE TABLE migration_changes block to the end aligns with the actual table creation order in the current database schema.

@fetzerch
Copy link
Author

Not a big change, but an updated schema on the documentation would have helped me to quicker find find the outdated info while trying to follow this community suggestion for migration back from postgres to sqlite. Therefore: Here is the update to the current version 2025.6.1.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@coderabbitai coderabbitai[bot] coderabbitai[bot] left review comments

At least 1 approving review is required to merge this pull request.

Assignees
No one assigned
Labels
current This PR goes into the current branch
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
1 participant
@fetzerch
0