AutomationPersonal Tooling

Utility Tracker

Year2025–present

RoleSolo

StatusLive

A self-hosted automation that watches my Gmail inbox for utility bills, parses each providers preferred format (PDF, JSON, or CSV), normalizes everything into PostgreSQL, and surfaces monthly cost and usage trends on a single dashboard.

Request Demo Access

Providers

Formats

1×/mo

Cron

Request Demo Access

Utility Tracker dashboard — Live at utilities.wyatt-fleming.com (single-tenant, behind auth)

Overview

Tracking household utility costs by hand was the kind of chore that quietly grew into hours per month: seven different providers, four different formats, and no single source of truth. I built Utility Tracker to absorb that work entirely. It connects to Gmail via OAuth, classifies inbound bill notifications by sender, parses each providers format (Southern California Edison PDFs with NEM/TOU data, SoCalGas JSON, MNWD water tier-usage CSVs, Cox new-charge emails, CR&R PDF invoices, Sunrun monthly kWh exports, fixed HOA dues), and stores everything in a normalized schema. A Recharts dashboard surfaces monthly totals, YTD trends, NEM bank tracking, and water-tier usage. A monthly cron does the ongoing collection so the dataset stays current with zero manual work.

Provider Coverage

Each utility company sends bills in a different way; the tracker absorbs all of them into one schema:

SCE ElectricParses "Bill is Ready" emails and PDF invoices for amount due, NEM bank balance, and TOU usage

SoCalGasReads AutoPay confirmation emails plus JSON exports for therms usage and payment amount

MNWD WaterPulls Paymentus confirmations and CSV imports including tier usage and budgeted gallons

Cox InternetCaptures "your bill is ready" emails for new charges

CR&R TrashParses quarterly PDF invoice attachments

Sunrun SolarCSV import of monthly kWh production from the Sunrun customer portal

HOAAuto-generated fixed monthly amount

Pipeline & Dashboard

A small Next.js app does the ingestion, normalization, and visualization:

OAuth Setup OnceAuthorize Gmail through the web flow; the refresh token is stored in Postgres for ongoing access

PDF ExtractionUses pdf-parse to pull line-item data from invoice attachments

Smart ClassificationEach provider has its own sender match and parser. Failures log loudly instead of silently dropping.

Monthly CronCoolify cron pulls last-30-days of bills automatically; bootstrap endpoint can backfill 12 months on demand

DashboardRecharts-based monthly totals, YTD trends, NEM bank chart, and water-tier usage

Technical Architecture

Deliberately tiny stack (no auth library, no UI library, no analytics). The whole point is the pipeline:

FrontendNext.js 16 App Router with Tailwind

DatabasePostgreSQL with Prisma 7 (4 models: provider, bill, payment, oauth-token)

Email IngestionGmail API via googleapis with OAuth refresh-token rotation

PDF Parsingpdf-parse for invoice extraction

ChartsRecharts for monthly, YTD, and tier-usage views

HostingSelf-hosted on Hetzner via Coolify with monthly cron

Why It Matters

Hours Saved Monthly

Replaced the manual "open every utility website, copy down the number, paste into a spreadsheet" loop with zero touch

Real Visibility

NEM bank, water-tier usage, and YTD spend are visible at a glance instead of buried in seven different portals

Format-Agnostic Parsing

Same dataset whether the bill arrived as a PDF attachment, a JSON payload, or a "your bill is ready" email

Private by Default

Self-hosted and single-tenant. No third party gets a copy of household financial data.

Key Learnings

Gmail OAuth Done RightBuilt the full Gmail OAuth lifecycle: initial consent flow, refresh-token storage in the DB, and silent token rotation. The refresh token is the long-lived credential and lives in Postgres, not env vars.

Parsing Is the ProductEach utility has its own quirks. SCE PDFs encode NEM bank as a separate line, MNWD CSVs roll up gallons by tier, and Sunrun exports use a different timestamp format. The value of the project is in the parsers, not the framework.

Lean by DesignResisted the urge to add multi-tenancy, auth, or a UI library. The app exists for one household with one user; every dependency I did not add is a dependency I will not maintain.

Cron-Driven Data ProductsServer-side crons triggered by header-secret API endpoints turn out to be a beautifully simple pattern for periodic data pipelines: no queues, no workers, no orchestrator. Just curl + cron.