Automated Water Usage Monitoring for San Francisco Residents
A Home Assistant custom integration for monitoring San Francisco water usage from SFPUC (San Francisco Public Utilities Commission). This integration connects to your SFPUC account to fetch detailed historical water usage data at multiple resolutions and provides it through Home Assistant sensors and the Energy dashboard. Track your consumption, identify usage patterns, and build smart conservation automations—all with data from your actual utility account.
🔗 View on GitHub • 📖 Documentation • ⬇️ Install via HACS
⚠️ Important Disclaimer
This project is not affiliated with or endorsed by the San Francisco Public Utilities Commission (SFPUC) or sfpuc.gov.
This is an unofficial, community-maintained integration that scrapes data from the SFPUC website. As such, it can break at any time due to changes in SFPUC’s website structure, authentication methods, or data formats. Use at your own risk—the integration may stop working without notice. There are no guarantees of functionality, accuracy, or continued operation. Please use responsibly and in accordance with SFPUC’s terms of service.
The Problem: Manual Water Usage Tracking
For San Francisco residents served by SFPUC, understanding water consumption patterns typically requires logging into the SFPUC portal, navigating through multiple screens, downloading Excel files, and manually analyzing usage data. This tedious process means most people only check their water usage when bills arrive, missing opportunities to identify leaks, track conservation efforts, or understand consumption patterns in real time.
The SFPUC portal provides valuable data—hourly, daily, and monthly usage readings—but accessing this information requires deliberate effort. You can’t easily integrate it with other home systems, create automated alerts for unusual usage, or visualize trends alongside your electricity and gas consumption. The data sits isolated in a utility portal rather than flowing into your smart home ecosystem where it could drive intelligent decisions and automations.
This integration solves the problem by automating the entire data retrieval process. It connects to your SFPUC account, downloads usage data at multiple resolutions, processes it locally, and makes it available through Home Assistant sensors and the Energy dashboard. Your water usage becomes as accessible and actionable as any other home automation data, enabling conservation tracking, leak detection, and unified resource monitoring.
How It Works
Automated Data Retrieval
The integration maintains a secure session with SFPUC’s portal using your account credentials. It authenticates automatically, navigates the portal structure, downloads Excel files containing your usage data, and parses them into structured sensor information. All of this happens transparently in the background at regular intervals, requiring no manual intervention once configured.
The data retrieval operates at fixed 12-hour intervals, which aligns well with how utility data becomes available. Water meter readings don’t change instantly—they’re typically recorded at specific intervals and uploaded to SFPUC’s systems on their schedule. The 12-hour update cycle provides fresh data without overwhelming SFPUC’s servers with excessive requests.
Multi-Resolution Statistics
Rather than creating separate sensor entities for hourly, daily, and monthly data, the integration uses Home Assistant’s statistics system intelligently. A single primary sensor displays your current billing period’s cumulative usage—the water consumed from your last bill date until now. This gives you an at-a-glance view of where you stand in the current billing cycle.
Behind this primary sensor, comprehensive statistics streams store detailed historical data at multiple resolutions. Hourly statistics capture granular usage patterns, revealing when water consumption peaks throughout the day. Daily statistics track overall consumption trends, showing which days see higher or lower usage. Monthly statistics enable long-term analysis and year-over-year comparisons, helping you understand seasonal patterns and evaluate conservation efforts.
This architecture keeps your entity list clean while providing rich historical data through Home Assistant’s Energy dashboard, history graphs, and Developer Tools. You’re not overwhelmed with dozens of sensor entities, but you still have access to every data point when you need detailed analysis.
Intelligent Historical Fetching
On initial setup, the integration performs a comprehensive historical data download. It fetches up to two years of monthly data for long-term trend analysis, 90 days of daily data for recent pattern identification, and 30 days of hourly data for detailed consumption understanding. This initial load provides immediate access to your usage history without waiting weeks for data to accumulate naturally.
After the initial historical fetch, subsequent Home Assistant restarts don’t repeat this process. The integration recognizes that historical data already exists and skips straight to updating with new readings. This makes everyday operation efficient—restarts are fast, and the system only fetches what’s actually new since the last update.
The integration includes automatic backfilling with a 30-day lookback window that identifies and fills any gaps in your data. If Home Assistant was offline for several days, sensor data was lost, or SFPUC’s portal was temporarily unavailable, the backfilling process detects missing periods and retrieves the data to maintain complete historical records.
Credential Management
The integration monitors authentication status and detects when credentials become invalid—perhaps because you changed your SFPUC password or your session expired. When authentication fails, Home Assistant’s repair system displays a notification with a simple fix flow. You enter your updated credentials, the integration validates them by testing login, and automatically reloads with the new information.
This graceful handling prevents silent failures where the integration stops updating but you don’t realize anything is wrong. The repair notification appears prominently in Home Assistant’s UI, making credential issues immediately obvious. The fix process is straightforward, taking just minutes to resolve and restore normal operation.
Energy Dashboard Integration
Unified Resource Monitoring
The primary purpose of this integration is enabling water usage tracking in Home Assistant’s Energy dashboard. This dashboard provides unified visualization of your home’s resource consumption—electricity, gas, and now water—allowing you to see the complete picture of your household’s environmental footprint and utility costs.
Water consumption appears alongside your other utilities in the dashboard’s consumption section. You can view usage over different time periods—today, this week, this month, this year—and compare current consumption with previous periods. This comparative view makes it easy to spot unusual usage patterns, track the impact of conservation efforts, or identify seasonal variations in water consumption.
The Energy dashboard’s graphs and statistics automatically incorporate your water data once the sensor is configured. Historical data appears immediately thanks to the comprehensive initial fetch, so you’re not starting from zero. You can analyze trends going back months or years, depending on how long your SFPUC account has been active and how much historical data the portal provides.
Statistics-First Architecture
The integration implements a modern statistics-first approach that aligns with Home Assistant’s best practices for utility monitoring. Instead of relying on sensor state history—which Home Assistant’s recorder database automatically prunes based on retention settings—utility data is stored in dedicated statistics streams designed for long-term retention.
Statistics metadata includes proper configuration for cumulative sum calculations, correct units of measurement for dashboard integration, device class assignments for Energy dashboard recognition, and state class definitions for trend analysis. This metadata ensures the Energy dashboard can properly interpret and display your water usage data without manual configuration.
The statistics approach also enables more efficient database storage. Rather than recording every single sensor state change, the statistics system stores aggregated data at appropriate intervals. This reduces database size while maintaining access to all the historical detail you need for meaningful analysis.
Key Features
Comprehensive Data Coverage
The integration provides water usage data at three time resolutions, each serving different analysis needs. Hourly data reveals detailed consumption patterns—when you shower, when irrigation systems run, when washing machines and dishwashers operate. This granular view helps identify opportunities for load shifting or scheduling high-water activities during off-peak times if tiered pricing exists.
Daily data tracks overall consumption trends without the noise of hourly variations. It shows which days see higher usage, whether weekends differ from weekdays, and how weather or household occupancy affects consumption. Daily totals make it easy to spot anomalies like a running toilet or irrigation leak that might not be obvious in hourly data.
Monthly data enables long-term analysis and planning. It reveals seasonal patterns like increased summer irrigation or decreased winter outdoor water use. It allows year-over-year comparisons to evaluate whether conservation efforts are working. It helps predict future bills based on current consumption trajectories.
Complete Local Processing
All data processing occurs entirely on your Home Assistant instance. The integration downloads raw Excel files from SFPUC, parses them locally into structured data, and stores results in your local database. Your water usage information never passes through external services, third-party APIs, or cloud processors. This local-first approach ensures privacy while eliminating dependencies on external systems that could introduce failures or delays.
The integration requires internet access only for communicating with SFPUC’s portal—the same connection you’d use if accessing the portal manually through a browser. Once data is downloaded, all processing, storage, and analysis happen on your own hardware under your complete control.
Automatic Gap Filling
Home Assistant occasionally restarts for updates, integrations temporarily fail due to network issues, or SFPUC’s portal experiences brief outages. Any of these situations can create gaps in your historical data where readings weren’t recorded. The integration’s automatic backfilling feature detects these gaps and fills them during subsequent updates.
The 30-day lookback window means any gap within the past month gets filled automatically. The system compares what’s in your database with what’s available from SFPUC, identifies missing periods, and retrieves the data to complete your historical records. This background process requires no manual intervention—your data remains complete without you needing to manually trigger catch-up operations.
Credential Security
Your SFPUC credentials are encrypted and stored securely using Home Assistant’s built-in secrets management system. They’re never logged in plain text, transmitted insecurely, or accessible to other integrations. The authentication session with SFPUC uses the same security protocols as logging in through a web browser—HTTPS encryption, secure session tokens, and proper credential handling.
When the integration needs to update credentials through the repair flow, the new credentials are validated before replacing the old ones. This prevents accidentally locking yourself out by mistyping a password. The validation test performs a real login attempt to SFPUC’s portal, ensuring the credentials work before saving them permanently.
Use Cases & Automation Examples
Leak Detection
Create automations that alert you when water usage occurs during periods when no one should be using water. A consistently non-zero hourly reading overnight when everyone is asleep suggests a leak. Sudden spikes in hourly consumption without obvious cause warrant investigation. Daily totals that exceed normal patterns by significant margins indicate potential problems.
automation:
- alias: "Unusual Water Usage Alert"
trigger:
- platform: numeric_state
entity_id: sensor.sfpuc_water_account_current_bill_water_usage_to_date
above: 150 # Daily average threshold
action:
- service: notify.mobile_app
data:
message: "High water usage detected today: {{ states('sensor.sfpuc_water_account_current_bill_water_usage_to_date') }} gallons"
title: "Water Usage Alert"
Conservation Tracking
Monitor the impact of conservation efforts by comparing current consumption with historical baselines. Set monthly water usage goals and track progress. Create dashboards showing daily consumption trends with target lines. Receive notifications when monthly usage exceeds budget thresholds.
Irrigation Optimization
Correlate water usage with irrigation schedules to understand actual irrigation costs. Adjust watering schedules based on consumption trends. Skip irrigation cycles when recent daily usage is high. Create notifications when irrigation days show unexpectedly low water usage, suggesting system malfunctions.
Bill Prediction
Use current billing period consumption to estimate upcoming bills before they arrive. Create graphs showing projected end-of-month usage based on current daily averages. Set budget alerts when projected bills exceed spending limits. Track whether you’re on pace to meet conservation goals for rate tier management.
Usage Pattern Analysis
Build custom dashboards showing hourly usage patterns throughout the day. Identify peak consumption times for potential load shifting. Compare weekday versus weekend usage patterns. Analyze seasonal variations in consumption over multiple years.
Technical Specifications
Architecture
The integration follows Home Assistant best practices with coordinator pattern for data management, config flow for user-friendly setup, proper statistics metadata and insertion, repair system integration for credential management, and graceful error handling with meaningful logging. All processing happens locally with no external dependencies beyond SFPUC’s portal.
System Requirements
Home Assistant version 2023.1.0 or higher provides the platform. Python 3.11+ serves as the runtime environment. The integration requires a valid SFPUC account with portal access. Network connectivity to SFPUC’s website is necessary for data retrieval but not for local data access.
Performance Characteristics
The integration uses scheduled updates at 12-hour intervals rather than constant polling. Historical data fetching occurs once on initial setup, then skips on subsequent restarts. Statistics storage leverages Home Assistant’s recorder database with automatic cleanup based on retention settings. Memory usage remains minimal, typically under 5MB during normal operation.
Data Privacy
All data processing occurs locally on your Home Assistant instance. No external services receive your usage data. No third-party APIs are required or accessed. SFPUC credentials are encrypted and stored securely. The integration communicates only with SFPUC’s portal using standard HTTPS. Your water usage information remains completely private under your control.
Web Scraping Reality
This integration scrapes SFPUC’s website rather than using an official API because SFPUC doesn’t provide a public API for accessing usage data. Web scraping is inherently fragile—website redesigns, authentication changes, or data format modifications can break the integration. While actively maintained and responsive to issues, users should understand that breakage is possible and may occur without advance warning.
When the integration breaks due to SFPUC website changes, fixes typically require code updates to adapt to the new website structure. These fixes are released as new versions through HACS as quickly as possible after changes are identified and resolved. Monitoring the GitHub repository’s issues and discussions helps stay informed about current status and any known problems.