ev-charging-status 0.1.0

Poll EV charging APIs and write station status events to TimescaleDB/PostgreSQL.

EV Charging Status

Python app that polls Shell Recharge/Greenlots and SEMA/Blink charger APIs and writes station status events to TimescaleDB/PostgreSQL.

Setup

uv sync
cp .env.example .env

Put real DB credentials and API tokens in .env. .env is ignored by git.

Run commands through uv run, for example:

uv run ev-status --help

Database

Initialize schema and seed default sites/statuses/stations:

uv run ev-status init-db

Only rows with sites.enabled = true are polled.

Run

First run, or after recreating/changing the database schema, initialize the database once:

uv run ev-status init-db

Routine one-time poll:

uv run ev-status poll

You can combine initialization and polling for a safe first run:

uv run ev-status poll --init-db

--init-db is not required for every poll. It only ensures the database, tables, indexes, default sites, default statuses, default stations, and TimescaleDB setup exist before polling.

Continuous 5-minute loop (or POLL_INTERVAL_SECONDS):

uv run ev-status run-loop

For a safe first continuous run:

uv run ev-status run-loop --init-db

Sync historical rows from the public Google Sheet CSV into station_status_events:

uv run ev-status sync-sheet

Set SYNC_SHEET_ID in .env. By default the command reads Sheet1 using:

https://docs.google.com/spreadsheets/d/<SYNC_SHEET_ID>/gviz/tq?tqx=out:csv&sheet=Sheet1

The sheet must include columns like current_time, station_name, and status. The sync command inserts only rows that do not already exist with the same (time, station_id, status). Rows for station IDs not present in the stations table are ignored.

You can override the tab or full CSV URL:

uv run ev-status sync-sheet --sheet-name Sheet1
uv run ev-status sync-sheet --url 'https://docs.google.com/spreadsheets/d/{SHEETID}/gviz/tq?tqx=out:csv&sheet=Sheet1'

Optimize station_status_events storage:

uv run ev-status optimize-db

This rounds existing event timestamps to second precision, drops the redundant (time DESC, station_id) index, and compresses TimescaleDB chunks older than 7 days. Timestamp precision is also second-level for new polls.

You can change the compression cutoff:

uv run ev-status optimize-db --compress-older-than '1 day'

Show total database size:

uv run ev-status db-size
# include table/relation sizes
uv run ev-status db-size --tables

Tables

• sites: site enable/disable control
• status_lookup: status IDs; new normalized statuses are inserted automatically
• stations: station metadata inserted once on first sight
• station_status_events: Timescale hypertable with exactly (time, station_id, status)
• site_latest_snapshot: latest raw JSONB API response per site