New website under development. In the meantime, follow EV Fleet (Pty) Ltd on LinkedIn for updates.

EV-Fleet

Documentation:

Postgres Summary Storage — Setup Guide

Status: implemented, Phases 0-7 complete (see postgres-implementation-plan.md). Postgres is a required dependency: db_config.ini must exist and be reachable, or every write/read in src/data_processing_ev/db/ raises rather than silently skipping (reversing this doc’s original “no-op if unconfigured” design — see §1 and §5 below).

Scope: this covers Step 2 (save fleet + per-vehicle summary stats to Postgres) and the Postgres-read half of Step 4 from Chris’s board (see ../brain/skills/SIM_DATABASE_IMPLEMENTATION.md → “Current Direction”). The Parquet/DuckDB time-series side (Steps 1’s Parquet half, Step 3, Step 4’s DuckDB half) is Chris’s slice, not covered here.

0. Precondition — reconcile git state first

The checkout was found on branch brain-phase1-skip-plots-flags, 363 commits behind origin/brain-phase1-skip-plots-flags, with 3 files mid-revert (sim_worker/engine.py, ev_box_plots.py, ev_results_analysis.py — rolling back the rejected BRAIN_SIM_SKIP_* env-var MR). Sync with origin and decide what branch to actually build this on before creating any of the files below — don’t layer new commits on a stale/mid-revert tree.

git fetch origin
git log --oneline HEAD..origin/brain-phase1-skip-plots-flags | wc -l   # confirm the gap
git status --short                                                      # confirm the 3 mid-revert files

1. Config file (no env vars, no CLI flag)

Chris’s explicit direction (MR !5 rejection + follow-up call): options via a config file, not env vars, and keep the config surface minimal. No app-config mechanism exists anywhere in data_processing_ev today (confirmed by grep — only CLI flags and, in the separate sim_worker/ component, its own unrelated .env).

File: src/data_processing_ev/db/config.py

db_config.ini.example (commit this at repo root; add bare db_config.ini to .gitignore):

[postgres]
host = localhost
port = 5442
dbname = ev_fleet_sim
user = ev_fleet_sim
password = dev-only-change-me
sslmode = prefer

2. Local dev Postgres (docker-compose)

No docker-compose.yml exists in this repo yet. Add one at repo root:

# docker-compose.yml — local dev Postgres for ev-fleet-sim summary storage.
# Throwaway dev credentials only — not used for Sim's own app config (that's
# db_config.ini, read by db/config.py), just to seed this container.
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: ev_fleet_sim
      POSTGRES_PASSWORD: dev-only-change-me
      POSTGRES_DB: ev_fleet_sim
    ports:
      # Host port 5442, not 5432 — this dev machine already runs a native
      # Postgres on 5432 (found via Phase 1's Agent Self-Test, `lsof -i :5432`).
      - "5442:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ev_fleet_sim -d ev_fleet_sim"]
      interval: 5s
      timeout: 3s
      retries: 10

volumes:
  pg_data:

Bring it up, then copy the example config so Sim can find it:

docker compose up -d postgres
mkdir -p ~/.config/ev-fleet-sim
cp db_config.ini.example ~/.config/ev-fleet-sim/db_config.ini

No manual migration step needed — db/connection.py’s ensure_schema() runs CREATE TABLE IF NOT EXISTS on first write (see §4).

3. Dependency

Declared in setup.cfg (this repo’s single source of dependency truth — pyproject.toml is 4 lines, build-backend only). Add to [options] install_requires:

psycopg[binary] >= 3.1

Why psycopg3, raw SQL, no ORM/no sqlalchemy, no asyncpg:

Also add (doesn’t exist yet in setup.cfg):

[options.extras_require]
test =
    pytest >= 7.0

4. Schema (src/data_processing_ev/db/schema.sql)

Verified against the actual stats_tree shapes in ev_results_analysis.py:

period keys are dynamically generated ("00:00:00 -> 06:00:00", etc.) and contain spaces/ arrows — not valid SQL identifiers, and fragile to flatten into typed columns. Hybrid design: a few typed identity columns + the irregular parts as JSONB, raw_stats kept verbatim as a round-trip safety net.

CREATE TABLE IF NOT EXISTS fleet_stats (
    id                      bigserial PRIMARY KEY,
    run_id                  uuid NOT NULL UNIQUE,
    scenario_name           text NOT NULL,
    scenario_path           text NOT NULL,
    created_at              timestamptz NOT NULL DEFAULT now(),
    averages_energy_diffs   jsonb NOT NULL,
    evs                     jsonb NOT NULL,
    raw_stats               jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS fleet_stats_raw_stats_gin ON fleet_stats USING gin (raw_stats);

CREATE TABLE IF NOT EXISTS vehicle_stats (
    id                      bigserial PRIMARY KEY,
    run_id                  uuid NOT NULL,
    scenario_name           text NOT NULL,
    scenario_path           text NOT NULL,
    ev_name                 text NOT NULL,
    created_at              timestamptz NOT NULL DEFAULT now(),
    averages_energy_diffs   jsonb NOT NULL,
    dates                   jsonb NOT NULL,
    raw_stats               jsonb NOT NULL,
    UNIQUE (run_id, ev_name)
);
CREATE INDEX IF NOT EXISTS vehicle_stats_raw_stats_gin ON vehicle_stats USING gin (raw_stats);

Run identity — append-only (CONFIRMED 2026-07-23, owner decision)

Sim has no existing “run id” concept today — one scenario_dir = one invocation, reruns overwrite the same JSON files in place. This design mints a run_id (uuid.uuid4()) once per run_ev_results_analysis() call, shared by the fleet row and all vehicle rows from that run, and treats Postgres as append-only across reruns — unlike the JSON files, Postgres keeps every run’s history rather than clobbering it. This is the reason to use a database instead of flat files at all, and it’s what Step 5’s REST API will want (list runs for a scenario, not just “the latest”). Confirmed 2026-07-23: append-only, run_id UNIQUE schema — not upsert-on-scenario. PG-T0.2 closed; Phase 1’s schema task (PG-T1.4) is unblocked.

5. Hook-in point — additive, unconditional, no new toggle

src/data_processing_ev/db/summary_writer.py — two functions: write_ev_stats(scenario_dir, run_id, ev_name, stats_tree) and write_fleet_stats(scenario_dir, run_id, stats_tree). Each:

  1. Calls db.connection.get_connection(). No config file, or Postgres unreachable → raises (RuntimeError for unconfigured, psycopg’s own error for unreachable/misconfigured) — Postgres is required, so a broken Postgres now surfaces as a failed run rather than a silently-missing row.
  2. If connected: ensure_schema(), INSERT ... ON CONFLICT (run_id[, ev_name]) DO NOTHING.

Exact edit sites in ev_results_analysis.py:

Runs alongside the JSON write, never replaces it, no gating flag. Other consumers (Brain’s interim sim_export_parser.py bridge) still read the JSON files until Brain’s own Step 6 ships — pulling them now breaks a live consumer. The only switch this introduces is “does db_config.ini exist” — matches the minimal-surface direction from MR !5’s rejection.

auto_run (API vs. CLI) is not a gate for this write — Postgres writing happens the same way regardless of invocation mode; auto_run governs prompt/plot suppression, not persistence.

6. Read functions (src/data_processing_ev/db/reader.py)

For Step 4’s Postgres half — plain functions, plain dicts, no framework, so Step 5’s REST layer (Chris, later) can call them directly and json-serialize the result:

Each returns raw_stats verbatim — guarantees the round-trip property Step 4’s own test demands by construction (it’s the exact dict serialized on write).

7. Testing

No test infra exists in this package today (only sim_worker/tests/, which is Brain-owned; .gitlab-ci.yml only runs a Jekyll docs build). New tests/ at repo root:

docker compose up -d postgres
pytest tests/ -q

Execution order

  1. §0 git reconciliation (blocking)
  2. db/config.py, db/connection.py (§1, §3, §4 schema.sql)
  3. Confirm run-identity decision (§4) with Chris/owner
  4. db/summary_writer.py + setup.cfg dep (§3, §5)
  5. docker-compose.yml + db_config.ini.example (§2) — needed to exercise the above locally
  6. Hook into ev_results_analysis.py (§5) — smallest, most reviewable diff once the above is solid
  7. db/reader.py (§6) — can build in parallel with step 6 once schema is fixed
  8. Tests (§7), written incrementally alongside each step above, not all at the end