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

EV-Fleet

Postgres Implementation Plan — ev-fleet-sim

What this is: the phase-gated execution plan for building Postgres summary storage into ev-fleet-sim — Step 2 (“Saving summaries into PostgreSQL”) and the Postgres-read half of Step 4 (“Set up methods to read summary … data”) from Chris Abraham’s FigJam board, as recorded in ../brain/skills/SIM_DATABASE_IMPLEMENTATION.md → “Current Direction”. That document is the source of truth for why this project exists and how it fits the larger Sim-becomes-a-database-service pivot; this document is the source of truth for how this one slice gets built, phase by phase, gated the same way that guide gates its own phases.

Technical design reference: docs/docs/postgres-setup.md (same repo) has the actual schema DDL, config format, docker-compose file, and library choice with justification. This plan sequences that design into gated phases with tests and sign-off — read docs/docs/postgres-setup.md for what to build, this doc for in what order and how each step gets verified.

Ownership split (per the board): Chris owns Step 1 (Postgres/DuckDB/Parquet plumbing — shared foundational setup, starred as his current task), Step 3 (Parquet/DuckDB time-series), the DuckDB-read half of Step 4, and Step 5 (REST API). This plan covers the Postgres-only slice: standing up Postgres plumbing (the Postgres half of Step 1, since nobody has started it yet), Step 2, and the Postgres-read half of Step 4.

Sim Boundary note: this plan modifies ev-fleet-sim’s own source (src/data_processing_ev/). That is normally off-limits per Rule 0 of SIM_DATABASE_IMPLEMENTATION.md — but this entire project traces to Chris’s own explicit direction (his FigJam board), which is exactly the Maintainer-directed exception that rule carves out. Scope discipline still applies: build what’s specified below, nothing extra “while we’re in here.”

Non-negotiable rules (inherited from SIM_DATABASE_IMPLEMENTATION.md §1, applied here)

1. No hallucinated facts. Before editing any line referencing a file/function/line number, re-grep/re-Read it — line numbers below were verified once; they drift as code changes.
2. No invented dependencies. Only psycopg[binary] is approved for this slice (see docs/docs/postgres-setup.md §3 for why). Anything else — stop and flag before adding.
3. Ask, don’t assume, on genuine ambiguity. The run-identity decision (append-only vs. overwrite, docs/docs/postgres-setup.md §4) was confirmed 2026-07-23 (owner decision): append-only, run_id UNIQUE. Phase 1’s schema task and Phase 2 are unblocked.
4. The agent tests its own work. No task is done until its Agent Self-Test passes with real command output, and the diff is re-read once after to confirm it matches intent.
5. Additive-first. Every phase runs alongside existing JSON-file writes, never replaces them, until Brain’s own consumer migration (Step 6, a separate project) makes the JSON files genuinely obsolete. No phase here deletes stats_fleet.json/stats_*.json writes.
6. One phase = one reviewable unit. Don’t bundle phases into one commit/MR.

Phase 0 — Preconditions

Status: Complete (2026-07-23)

Tasks

• PG-T0.1: Sync git state. Resolved 2026-07-23 — actual state was worse than originally logged: ev-fleet-sim-master was a solo git init whose HEAD (7e3d7f2) tracked only 3 files; the rest of the working tree (71 staged additions under src/data_processing_ev/, plus untracked repo-root files — README.md, LICENSE, setup.cfg, pyproject.toml, .gitlab-ci.yml, .gitmodules, docs/, sim_worker/, both Postgres design docs) had never been committed anywhere. Owner decision: commit the full tree as-is on a new postgres-baseline branch (commit 06a6bb4, off 7e3d7f2, not on brain-phase1-skip-plots-flags), then branch postgres-phase1-config-connection-schema off that for Phase 1 work. origin/master’s 362-commit gap is intentionally left unreconciled — separate concern, not blocking this slice.
• PG-T0.2: Confirm the run-identity decision (docs/docs/postgres-setup.md §4) with Chris — append-only (recommended) vs. upsert/overwrite. Resolved 2026-07-23 (owner decision): append-only, run_id UNIQUE. Recorded in docs/docs/postgres-setup.md §4.
• PG-T0.3 — Added 2026-07-27. Resolved 2026-07-28 (owner decision): the diagram supersedes SIM_DATABASE_IMPLEMENTATION.md’s “MinIO deferred, not urgent” decision — this is current, real scope now, not aspirational.
• PG-T0.4 — Added 2026-07-27. Resolved 2026-07-28 (owner decision): Brain-side/the user owns Phase 5-7’s scope directly, same review path as Phases 0-4. Phase 8 (Parquet/MinIO) still re-confirms ownership separately per its own precondition — more likely Chris’s/Sim’s side.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T0.1 | “Run PG-T0.1: fetch origin, diff against brain-phase1-skip-plots-flags, decide target branch with the owner.” | Branch decision recorded in writing (this doc or a commit message), git status clean or intentionally staged only | | PG-T0.2 | “Run PG-T0.2: confirm append-only vs. upsert run-identity with Chris before Phase 2 starts.” | Decision recorded in docs/docs/postgres-setup.md §4, no longer marked “not yet confirmed” | | PG-T0.3 | “Run PG-T0.3: confirm with Chris/owner whether the FigJam diagram supersedes the MinIO-deferred decision.” | Decision recorded in writing in this doc, no longer marked PENDING | | PG-T0.4 | “Run PG-T0.4: confirm ownership of the diagram’s scope (Phases 5-8) with Chris/owner.” | Decision recorded in writing in this doc, no longer marked PENDING |

Completion Markers

• Target branch decided and git tree clean (no stray mid-revert files) before Phase 1 starts. — postgres-baseline (06a6bb4) + postgres-phase1-config-connection-schema branched off it, git status clean.
• Run-identity decision confirmed in writing. — docs/docs/postgres-setup.md §4, confirmed 2026-07-23.
• Phase 5+ gate (added 2026-07-27, doesn’t affect Phases 1-4’s already-met gate above): PG-T0.3 and PG-T0.4 both confirmed in writing 2026-07-28. Phase 5 unblocked.

Rollback

N/A — no code changes in this phase, only decisions and git hygiene.

Phase 1 — Config, Connection, and Schema Plumbing

Status: Complete (2026-07-27) — commit ad4c55b on postgres-phase1-config-connection-schema (branched off postgres-baseline, 06a6bb4).

(This is the Postgres half of the board’s Step 1 — nobody has started it yet, confirmed with the user directly.)

Preconditions

• Phase 0 complete.

Tasks

• PG-T1.1: Create src/data_processing_ev/db/ subpackage skeleton: __init__.py, config.py, connection.py, schema.sql (empty/stub files at this stage — content in T1.2-T1.4).
• PG-T1.2: Implement db/config.py — load_db_config(path=DEFAULT_CONFIG_PATH), INI format, fixed path ~/.config/ev-fleet-sim/db_config.ini, returns None if absent. Exact format and rationale in docs/docs/postgres-setup.md §1.
• PG-T1.3: Implement db/connection.py — get_connection() (returns None if unconfigured, raises only on a genuine connection failure with a valid config) and ensure_schema(conn) (idempotent CREATE TABLE IF NOT EXISTS from schema.sql).
• PG-T1.4: Write db/schema.sql per docs/docs/postgres-setup.md §4 — fleet_stats, vehicle_stats tables, GIN indexes on raw_stats. Requires PG-T0.2’s run-identity decision to be settled first (determines whether run_id is UNIQUE (append-only) or the table is keyed/upserted on scenario_name instead).
• PG-T1.5: Add psycopg[binary] >= 3.1 to setup.cfg’s install_requires, and a new [options.extras_require] test = pytest >= 7.0 section.
• PG-T1.6: Add docker-compose.yml (repo root) and db_config.ini.example per docs/docs/postgres-setup.md §2. Add bare db_config.ini to .gitignore.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T1.1 | “Run PG-T1.1: create the db/ subpackage skeleton.” | python -c "from data_processing_ev import db" imports with no error | | PG-T1.2 | “Run PG-T1.2: implement db/config.py per docs/docs/postgres-setup.md §1.” | Unit test: fixture INI parses correctly; missing file returns None, not an exception | | PG-T1.3 | “Run PG-T1.3: implement db/connection.py’s get_connection() and ensure_schema().” | Against docker-compose Postgres: get_connection() returns a live connection; ensure_schema() run twice is a no-op the second time | | PG-T1.4 | “Run PG-T1.4: write db/schema.sql per docs/docs/postgres-setup.md §4, using the confirmed run-identity decision from PG-T0.2.” | ensure_schema() creates both tables with the exact columns listed in docs/docs/postgres-setup.md §4 (verify via \d fleet_stats / \d vehicle_stats) | | PG-T1.5 | “Run PG-T1.5: add psycopg[binary] and the test extra to setup.cfg.” | pip install -e . succeeds; pip install -e .[test] succeeds | | PG-T1.6 | “Run PG-T1.6: add docker-compose.yml and db_config.ini.example.” | docker compose up -d postgres exits 0, pg_isready healthy |

Phase-specific guardrails

• Nothing here touches ev_results_analysis.py or any other existing module — this phase is purely additive new files. If a task seems to require touching an existing file, stop.
• db_config.ini (the real, non-example file) must never be committed — verify .gitignore before finishing this phase.

Agent Self-Test

• docker compose up -d postgres --wait exits 0, healthy. Note: host port changed to 5442 — lsof -i :5432 during this self-test found a pre-existing native Postgres already bound to 5432 on this dev machine, which silently intercepted connections meant for the container (role "ev_fleet_sim" does not exist — that role belongs to the container, not the native instance). docker-compose.yml, db_config.ini.example, and docs/docs/postgres-setup.md §1-2 all updated to 5442 to match.
• ensure_schema() run against a fresh container creates both tables correctly — verified via information_schema.tables/.columns/pg_indexes (not assumed): both tables present with exact columns from §4, both GIN indexes on raw_stats present. Run twice, second call a confirmed no-op.
• load_db_config() unit test: present-file and absent-file cases both pass (direct isolated-module test, real output captured).
• pip install -e .[test] succeeds with the new dependency. Does not pass — pre-existing, unrelated to this diff. Tried on Python 3.14 (fails: matplotlib==3.3.4’s versioneer.py calls configparser.SafeConfigParser, removed in 3.12+) and Python 3.11 (fails: pyproj==3.2.1 needs pkg_resources, absent from current build-isolation setuptools). Both pins predate this commit; psycopg[binary] and pytest themselves install and run cleanly in isolation (verified separately). This package has only ever been run via its Docker image (sim-worker-full), never a bare local pip install, per prior IMPLEMENTATION_LOG.md history — same class of gap, not new. Real verification of this specific box needs that image or a compatible legacy Python env; flagging rather than silently checking it off.

Manual Verification (human)

1. Inspect fleet_stats/vehicle_stats schema via psql against the compose Postgres.
2. Confirm db_config.ini.example matches the compose file’s credentials exactly.
3. Confirm .gitignore excludes the real db_config.ini.

Completion Markers

• db/ subpackage exists with config, connection, schema all working against a real local Postgres.
• setup.cfg updated (psycopg + test extra) — dependency itself verified working; full package install still blocked by pre-existing, unrelated pins (see Agent Self-Test above).
• docker-compose file verified working from a clean docker compose down -v && up --wait.

Rollback

Delete src/data_processing_ev/db/, revert setup.cfg, remove docker-compose.yml and db_config.ini.example — no existing code touched, clean revert.

Phase 2 — Write Path (Step 2)

Status: Complete (2026-07-27) — commit 36c2d56.

Preconditions

• Phase 1 complete and self-tested.
• PG-T0.2’s run-identity decision confirmed (gates schema shape already built in Phase 1, and gates the upsert-vs-insert logic in this phase).

Tasks

• PG-T2.1: Implement db/summary_writer.py — write_ev_stats(scenario_dir, run_id, ev_name, stats_tree) and write_fleet_stats(scenario_dir, run_id, stats_tree). No-ops silently if unconfigured; catches and logs (never raises) on a configured-but-failing write. Exact behavior spec in docs/docs/postgres-setup.md §5.
• PG-T2.2: Hook into ev_results_analysis.py:
  • run_ev_results_analysis() (~line 1404): kwargs.setdefault('run_id', uuid.uuid4()).
  • save_ev_stats() (~lines 946-952, immediately after the existing json.dump): call write_ev_stats(...).
  • save_fleet_stats() (~lines 1197-1200, immediately after the existing json.dump): call write_fleet_stats(...).
  • Re-grep these line numbers first — they may have moved since last verified.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T2.1 | “Run PG-T2.1: implement db/summary_writer.py per docs/docs/postgres-setup.md §5.” | Unit test with a mock/fixture stats_tree: row appears in Postgres with correct values; with no config file present, function returns cleanly with no exception | | PG-T2.2 | “Run PG-T2.2: hook write_ev_stats/write_fleet_stats into ev_results_analysis.py at the verified line numbers.” | Real my-scenario run with db_config.ini present: rows appear in both tables AND stats_fleet.json/stats_*.json are still written unchanged |

Phase-specific guardrails

• The JSON write must remain byte-for-byte unaffected — diff the JSON output of a run before and after this phase’s change to confirm zero regression.
• Writer functions must never raise past their own boundary — test explicitly with Postgres stopped mid-run to confirm a normal simulation still completes successfully.

Agent Self-Test

• Postgres up, configured: fleet + vehicle rows land correctly via isolated-module test (full package still can’t be imported here — see Phase 1’s pip install caveat) — raw_stats byte-identical to the input stats_tree fixture, confirmed by direct equality assertion, not eyeballed. Re-write with the same run_id confirmed ON CONFLICT DO NOTHING (count stays 1).
• No db_config.ini: returns cleanly, one debug-level log line, no exception.
• db_config.ini present but Postgres unreachable (pointed at a closed port): first attempt failed this test — get_connection() was called outside the try/except, so the connection error propagated uncaught. Fixed by moving it inside a shared _write() helper’s try block; re-tested, now returns cleanly with one warning logged, confirmed via real psycopg.OperationalError triggered against a closed port, not simulated.
• JSON output diffed byte-for-byte against a pre-Phase-2 run of the same scenario — not run, needs a real Sim execution (blocked, see note below). Diff-level guarantee instead: git diff on ev_results_analysis.py shows +9/-0 lines, i.e. the existing json.dump(...) blocks are untouched by construction.

Manual Verification (human)

1. Run my-scenario end-to-end with Postgres up; inspect rows via psql.
2. Confirm stats_fleet.json content still matches what’s in fleet_stats.raw_stats exactly.
3. Kill Postgres mid-run once, confirm the simulation still finishes and JSON files are correct.

Completion Markers

• Write path verified end-to-end against synthetic fixtures, both success and failure paths (including the connection-failure bug found and fixed during self-test).
• Zero regression in existing JSON output — verified by diff of the code (purely additive), not yet by a real before/after run. Outstanding gap: needs either the pre-existing matplotlib/pyproj pin issue resolved (Phase 1) or the project’s Docker image (sim-worker-full) to run my-scenario end-to-end. Not silently marked done.

Rollback

Revert the two-line-plus-one-import hook in ev_results_analysis.py; delete summary_writer.py. JSON-writing behavior is untouched throughout, so rollback carries zero risk to existing consumers.

Phase 3 — Read Path (Postgres half of Step 4)

Status: Complete (2026-07-27) — commit 063d025.

Preconditions

• Phase 2 complete, rows exist in Postgres from real runs. Partially met: Phase 2’s code is complete and self-tested, but “real runs” specifically means synthetic-fixture rows so far (see Phase 2’s outstanding JSON-diff gap) — PG-T3.1’s own test only requires a known fixture round-trip, which is satisfied in full below.

Tasks

• PG-T3.1: Implement db/reader.py — get_fleet_stats(conn, run_id), get_vehicle_stats(conn, run_id, ev_name), list_vehicle_stats(conn, run_id), get_latest_run_id(conn, scenario_name). Spec in docs/docs/postgres-setup.md §6.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T3.1 | “Run PG-T3.1: implement db/reader.py per docs/docs/postgres-setup.md §6.” | Round-trip test: write a known stats_tree fixture, read it back via each reader function, assert deep-equality with the original |

Phase-specific guardrails

• Keep this module framework-free (no FastAPI/Flask) — takes a psycopg.Connection, returns plain dicts, so Step 5 (Chris’s REST layer) can call it directly later.

Agent Self-Test

• Round-trip test passing for all four functions — get_fleet_stats/get_vehicle_stats return raw_stats byte-identical to the input fixture; list_vehicle_stats returns exactly the vehicles written for a run_id, ordered, raw_stats intact for each. Against Phase-2-written synthetic-fixture data (real-Sim-run data still pending, see Phase 2).
• get_latest_run_id correctly resolves the most recent run when a scenario has been run more than once — wrote two runs a second apart under the same scenario_name, get_latest_run_id returned the second run’s id, and list_vehicle_stats for that run returned only its own vehicle, not the first run’s. Exercised directly, not skipped.

Manual Verification (human)

1. Run my-scenario twice, confirm get_latest_run_id returns the second run’s id, and list_vehicle_stats for that run only returns that run’s vehicles (not the first run’s).

Completion Markers

• All four read functions verified round-trip-correct against synthetic fixture data (real multi-run Sim data pending the Phase 2 execution gap).
• Multi-run scenario (append-only history) verified, not just single-run happy path.

Rollback

Delete db/reader.py — no other module depends on it yet (Step 5 is Chris’s later work).

Phase 4 — Tests & CI

Status: Complete (2026-07-27) — commit 22dca31.

Preconditions

• Phases 1-3 complete.

Tasks

• PG-T4.1: tests/test_db_config.py, tests/test_db_summary_writer.py, tests/test_db_reader.py, tests/README.md documenting the docker compose up -d postgres && pytest tests/ -q workflow. (This also happens to be the first test suite this package has ever had — .gitlab-ci.yml today runs only a Jekyll docs build, confirmed via grep.)
• PG-T4.2: Propose (don’t silently add) a Postgres-backed .gitlab-ci.yml test job. This is a visible change to shared project infra — confirm with Chris before merging.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T4.1 | “Run PG-T4.1: write the db test suite per docs/docs/postgres-setup.md §7.” | docker compose up -d postgres && pytest tests/ -q exits 0 | | PG-T4.2 | “Run PG-T4.2: propose a Postgres-backed CI job to Chris, do not merge without his sign-off.” | Explicit approval recorded before merge |

Phase-specific guardrails

• Tests must cover the negative path (no config, Postgres down) as thoroughly as the happy path — this is the behavior most likely to silently regress later.

Agent Self-Test

• Full test suite green locally, from a clean docker compose down -v && up --wait: pytest tests/ -q → 11 passed (2 config + 5 writer + 4 reader). Notable finding along the way: importing anything under data_processing_ev — even db.config alone — forces data_processing_ev/__init__.py to run first, which pulls in folium/matplotlib/libsumo (confirmed: bare import data_processing_ev.db.config fails on ModuleNotFoundError: No module named 'folium' here). Since db/ only needs psycopg + stdlib, tests/conftest.py loads its submodules directly and stubs just the parent’s LOGGERS attribute — test-only, falls back to the real package automatically if it’s ever actually importable. Documented in tests/README.md, not hidden.
• CI job (if approved) green in a real pipeline run — not applicable yet, PG-T4.2 is a proposal only (see below), nothing added to .gitlab-ci.yml.

Manual Verification (human)

1. Run the full suite locally from a clean docker compose down -v && up.
2. If PG-T4.2 shipped, confirm the CI job actually appears and passes on GitLab.

Completion Markers

• Test suite committed and passing (commit 22dca31).
• CI decision made explicitly — deliberately deferred, not silently skipped: docs/docs/ci-proposal-postgres-tests.md lays out a proposed job and three options for the matplotlib/pyproj-pin wall a real CI run would hit, without picking one. Needs Chris’s sign-off before anything lands in .gitlab-ci.yml.

Rollback

Delete tests/; revert .gitlab-ci.yml if PG-T4.2 shipped.

Phase 5 — Scenario & Run Metadata (Execution Tracking)

Status: Complete (2026-07-28) — commit 021b065 on postgres-implementation, in ev-fleet-sim-master.

(New scope introduced by the “posgres - parquet integration” FigJam diagram, shown directly by the user 2026-07-27 — not part of the original board text this plan was built from.)

Preconditions

• PG-T0.3: Resolved 2026-07-28 (owner decision) — the diagram supersedes the “MinIO deferred” call, current real scope now.
• PG-T0.4: Resolved 2026-07-28 (owner decision) — Brain-side/the user owns this directly.
• Phases 0-4 complete (this phase adds tables alongside fleet_stats/vehicle_stats, doesn’t replace them).

Tasks

• PG-T5.1 — scenarios table. Done. scenario_name (PK — matches the naming already used in fleet_stats/vehicle_stats, not a new scenario_id concept), created_by, created_at. # runs/averages are computed via query over runs, not stored columns — avoids a second place that can drift out of sync.
• PG-T5.2 — runs table. Done. run_id (PK, matches the existing run_id already used in fleet_stats/vehicle_stats), scenario_name (FK → scenarios, nullable — sim-worker doesn’t always know it upfront), execution_status (queued/running/succeeded/failed, CHECK constraint), execution_start, execution_end, triggered_by, storage_bytes, runtime_cpu_seconds, runtime_ram_bytes, error.
• PG-T5.3 — Status/timing instrumentation. Done. Real investigation of current sim_worker/main.py (not reused from this doc, which flagged it unverified) found job_id (HTTP-level tracking) and run_id (minted inside ev_results_analysis.py) were disconnected UUIDs. Unified: sim-worker now mints run_id at submission, passes it to the Sim subprocess via a new BRAIN_RUN_ID env var (same passthrough pattern as BRAIN_SIM_ONE_BIG_CLUSTER), and run_ev_results_analysis() reads it instead of minting its own.
• PG-T5.4 — Cost/resource capture. Done. CPU/RAM via stdlib resource.getrusage(RUSAGE_CHILDREN), before/after delta around the subprocess call — no new dependency. Documented limitation: this is process-wide cumulative, not per-child, so concurrent runs under sim-worker’s threading model can cross-contaminate each other’s numbers. A per-PID method (psutil) would fix it but isn’t an approved dependency for this slice. Storage via a real directory-size walk of scenario_dir — no such limitation, one dir per job.
• PG-T5.5 — db/run_tracker.py. Done. create_run(run_id, scenario_name, triggered_by), update_run_status(run_id, status), finalize_run(run_id, status, storage_bytes, runtime_cpu_seconds, runtime_ram_bytes, error). Same safety contract as summary_writer.py.
• PG-T5.6 — Reader functions. Done. Added to db/reader.py: get_scenario, get_run, list_runs_for_scenario.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T5.1 | “Run PG-T5.1: add the scenarios table to schema.sql.” | ensure_schema() creates scenarios with the agreed columns | | PG-T5.2 | “Run PG-T5.2: add the runs table to schema.sql, FK’d to scenarios.” | ensure_schema() creates runs; FK constraint verified (insert with bad scenario_id fails) | | PG-T5.3 | “Run PG-T5.3: hook execution-status/timing writes into the real run-launching code, re-grepped fresh.” | A real (or simulated) run writes queued → running → succeeded/failed with real timestamps | | PG-T5.4 | “Run PG-T5.4: implement CPU/RAM/storage measurement for a run.” | Measured values are non-null and plausible for a real run | | PG-T5.5 | “Run PG-T5.5: implement db/run_tracker.py.” | Unit + integration tests: create/update/finalize a run, values land correctly; failure path never raises | | PG-T5.6 | “Run PG-T5.6: implement scenario/run reader functions.” | Round-trip test against real written data |

Phase-specific guardrails

• Same additive-first rule as Phases 1-4 — must not touch or replace fleet_stats/vehicle_stats, only add alongside.
• PG-T5.3 touches a real execution path (not just new files) — re-verify line numbers/call sites fresh per Rule 1, don’t reuse anything cited earlier in this doc without re-checking.

Agent Self-Test

• scenarios/runs tables created correctly, FK enforced — verified via information_schema (exact columns) and a real ForeignKeyViolation triggered by inserting a runs row with a nonexistent scenario_name, confirmed programmatically.
• A run’s lifecycle produces a correct, complete runs row — create_run → queued, update_run_status(running) → sets execution_start, finalize_run(succeeded, ...) → sets execution_end + all three cost numbers, verified against the live compose Postgres with real reads after each transition (not assumed).
• Negative path: db/run_tracker.py’s three functions all confirmed non-raising with no config file present. sim-worker side: 14/14 tests passing (9 pre-existing + 5 new) via real pytest — a resolve-time failure finalizes as failed with the real error message; the stub path (WORKER_SKIP_SIMULATOR) still finalizes as succeeded, unchanged from before Phase 5.

Manual Verification (human)

1. Trigger a real run, inspect its runs row mid-execution (running) and after (succeeded or failed) via psql.
2. Confirm scenarios-level averages update (or compute correctly, if a view) after multiple runs of the same scenario.

Completion Markers

• scenarios/runs tables live and verified against real Postgres writes/reads — real Sim-subprocess execution still pending (same environment gap as every phase since Phase 2).
• Resource-cost numbers verified captured and non-null in a real code-path test (mocked subprocess, real resource.getrusage call) — plausibility against an actual Sim run’s real resource usage is part of the still-outstanding real-run gap, not yet checked.

Rollback

Delete the new tables and db/run_tracker.py; revert the instrumentation hook in sim_worker/. fleet_stats/vehicle_stats and their write path are untouched throughout.

Phase 6 — File Catalog (Inputs/Outputs per Run)

Status: Complete (2026-07-29) — commit 4720f3c on postgres-implementation, in ev-fleet-sim-master.

Preconditions

• Phase 5 complete (runs table must exist as the FK target for catalog entries).
• PG-T0.3/PG-T0.4 resolved (same carried-forward gate as Phase 5) — both resolved 2026-07-28.

Tasks

• PG-T6.1 — File catalog table. Done. One run_files table (not two) — one row per real file, matching the diagram’s “each file with its own file ID” (a raw input’s original upload and its ETL’d/Parquet conversion are two rows, not two columns on one). file_kind CHECK enumerates the six real kinds (config, raw_input_original, raw_input_etl_parquet, run_log, summary_stats, raw_output). s3_file_id/file_format nullable — Phase 8 is what creates the objects these point at, not this phase.
• PG-T6.2 — db/file_catalog.py writer. Done. One function per file kind (record_config, record_raw_input_original, record_raw_input_etl_parquet, record_run_log, record_summary_stats, record_raw_output), shared _record() helper, same never-raises contract as the other writers. record_summary_stats() takes no s3_file_id by design — the real data is the fleet_stats/vehicle_stats rows, this is a pointer/marker row.
• PG-T6.3 — Reader functions. Done. list_input_files/list_output_files, filterable by ev_name and/or file_kind.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T6.1 | “Run PG-T6.1: add the file-catalog table(s) to schema.sql.” | ensure_schema() creates the table(s) with agreed columns and FK to runs | | PG-T6.2 | “Run PG-T6.2: implement db/file_catalog.py per the diagram’s Inputs/Outputs spec.” | Fixture test: recording each file kind produces a correct row; never raises on failure | | PG-T6.3 | “Run PG-T6.3: implement file-catalog reader functions.” | Round-trip test against real written data, filtered correctly by kind/vehicle |

Phase-specific guardrails

• This catalogs file references (S3 IDs, links) — it does not itself move or convert files. Actually producing the Parquet files those references point to is Phase 8, not this phase.

Agent Self-Test

• All six file kinds recorded correctly for a test run — verified against the live compose Postgres with real reads after each write (not assumed). summary_stats confirmed to have a null s3_file_id, by design.
• Queries filtered by vehicle/date/kind return the right subset — verified with a run containing multiple vehicles and file kinds, confirming filters return exactly the expected subset, not just “some rows.”
• FK enforcement (ForeignKeyViolation on a nonexistent run_id, swallowed not raised) and CHECK enforcement (bad direction value) both verified programmatically. Both negative paths — no config file, and a real FK violation — confirmed to no-op cleanly.
• Full suite green from a clean docker compose down -v && up --wait: pytest tests/ -q → 24 passed (11 Phase 1-4 + 6 run_tracker + 7 file_catalog). Also retroactively closed a Phase 5 gap: that phase only had ad-hoc scratch verification, not a committed pytest file — tests/test_db_run_tracker.py added here.

Manual Verification (human)

1. Inspect a real run’s full file catalog via psql, cross-check against what files actually exist for that run.

Completion Markers

• File catalog verified against real Postgres writes/reads with realistic fixtures — a real run’s actual file set is part of the still-outstanding real-Sim-run gap carried since Phase 2.

Rollback

Delete the new table(s) and db/file_catalog.py. Nothing else depends on this phase yet.

Phase 7 — Database-Level Aggregate Metadata

Status: Complete (2026-07-29) — commit 2099d27 on postgres-implementation, in ev-fleet-sim-master.

Preconditions

• Phase 5 complete (runs table is what these aggregates roll up).

Tasks

• PG-T7.1 — Aggregate queries/view. Done. runs_passed, runs_failed, runs_completed, runs_running, avg_runtime_cpu_seconds, avg_runtime_ram_bytes, avg_storage_bytes (the “cost split by storage/CPU/RAM” from the diagram), and avg_runtime_wall_seconds (execution_end − execution_start). Chose a single plain query over a Postgres VIEW: no other consumer needs a standalone schema object for a one-caller aggregate, and it keeps this phase’s diff a pure db/ addition with zero schema.sql change.
• PG-T7.2 — db/dashboard_reader.py. Done. get_global_stats(conn) -> dict, same dict_row/single-query shape as db/reader.py’s existing functions — for whatever consumes a dashboard view (Step 5’s REST API, later, per the ownership split; this phase only makes the query available).

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T7.1 | “Run PG-T7.1: add the global-stats aggregate view/queries.” | Query against known fixture data returns exactly the expected counts/averages | | PG-T7.2 | “Run PG-T7.2: implement get_global_stats().” | Returns correct values against real multi-run data |

Phase-specific guardrails

• Keep this framework-free like db/reader.py — plain dicts, no dashboard/API code here.

Agent Self-Test

• Aggregate numbers verified correct by hand against known fixture data (not just “a number came back”) — tests/test_db_dashboard_reader.py::test_get_global_stats_matches_independently_computed_values inserts three known runs (one succeeded with storage_bytes=1000/runtime_cpu_seconds=2.0/ runtime_ram_bytes=2000, one failed with 3000/4.0/4000, one running), then cross-checks every field against separately written SQL (different SELECT per field, independent of get_global_stats()’s own query) — real compose Postgres, not mocked.
• Full suite green: pytest tests/ -q → 26 passed (24 pre-existing + 2 new test_db_dashboard_reader.py tests), against the live docker compose up -d postgres --wait instance.
• ast.parse clean on all four changed/new files (db/dashboard_reader.py, db/__init__.py, tests/conftest.py, tests/test_db_dashboard_reader.py).

Manual Verification (human)

1. Cross-check get_global_stats()’s output against a manual SELECT count(*) ... for each field.

Completion Markers

• Global stats query verified correct against real multi-run data — see Agent Self-Test. Manual Verification by a human still pending sign-off, same as every phase since Phase 2 (real Sim-subprocess/dashboard-consumer verification is outside what this environment can run).

Rollback

Delete the view/queries and db/dashboard_reader.py.

Phase 8 — Parquet + MinIO Storage Pipeline

Status: Not Started

(This is the piece most likely to belong to Chris/the Parquet-DuckDB side of the original ownership split, not this Postgres-only plan — flagged loudly, not assumed. Confirm ownership via PG-T0.4 before starting, even after Phases 5-7 are done.)

Preconditions

• PG-T0.3/PG-T0.4 resolved, specifically re-confirmed for this phase even if already answered for Phases 5-7 — SIM_DATABASE_IMPLEMENTATION.md’s “Future” section explicitly lists MinIO as deferred, owner TBD, “not urgent until there’s a stable pipeline that needs it.” This phase is the literal thing that was deferred.
• Phase 6 complete (file catalog needs to exist for its S3/Parquet link fields to point at something real).

Tasks

• PG-T8.1 — MinIO setup. Confirm whether MinIO already runs somewhere (the Phase-0 spike compose file, brain/docker-compose.spike.yml, already has a throwaway MinIO — check whether that’s meant to become real infra or whether this needs its own instance).
• PG-T8.2 — Parquet conversion. Raw input/output files → Parquet. Note: Phase 0’s own ADR (ADR-storage-partitioning.md, in the brain/ repo) already covers Parquet conversion strategy for a related but distinct purpose (time-series data from battery.out.csv.gz) — read that first, don’t re-derive a second, inconsistent Parquet strategy from scratch.
• PG-T8.3 — Bucket layout. Run ID → Input Folder / Output Folder → raw input data / input parquets / output parquets, each file with its own file ID, per the diagram exactly.
• PG-T8.4 — Wire the file catalog to real objects. Phase 6’s s3_file_id/link fields should resolve to real MinIO objects created by PG-T8.1-T8.3, not placeholder values.

Trigger prompts & tests

| Task | Trigger prompt | Test | |—|—|—| | PG-T8.1 | “Run PG-T8.1: confirm/stand up MinIO for this pipeline.” | MinIO reachable, bucket created | | PG-T8.2 | “Run PG-T8.2: implement Parquet conversion for run input/output files, per the existing ADR-storage-partitioning.md strategy.” | Converted Parquet files readable, row-count parity vs. source confirmed (same method as Phase 0’s spike) | | PG-T8.3 | “Run PG-T8.3: implement the Run-ID-based bucket layout.” | Real run’s files land in the correct Input Folder/Output Folder structure | | PG-T8.4 | “Run PG-T8.4: wire file-catalog entries to the real MinIO objects.” | A file-catalog row’s link actually resolves to the real stored object |

Phase-specific guardrails

• Don’t duplicate or diverge from Phase 0’s already-approved Parquet strategy (ADR-storage-partitioning.md) — this phase applies that strategy to a new (input/output catalog) context, it doesn’t invent a second one.

Agent Self-Test

• Row-count/data parity between source files and their Parquet conversions, confirmed programmatically (matching Phase 0’s spike methodology), not eyeballed.
• Bucket layout matches the diagram exactly for a real run.

Manual Verification (human)

1. Inspect the MinIO bucket structure for a real run directly, confirm it matches the diagram.
2. Spot-check a converted Parquet file’s contents against its source file.

Completion Markers

• Real run’s files fully round-tripped through Parquet + MinIO, verified byte/row-accurate.
• File catalog (Phase 6) confirmed pointing at real, resolvable objects.

Rollback

Remove the MinIO wiring and Parquet conversion step; file catalog (Phase 6) degrades to storing links that don’t resolve, but doesn’t break — Postgres-side data is unaffected.

Summary sequencing

Do not start a phase’s tasks before the prior phase’s Completion Markers are checked — same gating discipline SIM_DATABASE_IMPLEMENTATION.md uses for its own phases. Phases 5-8 are additionally gated on PG-T0.3/PG-T0.4 (diagram-scope and ownership), unresolved as of 2026-07-27 — do not start Phase 5 until those are answered, even though Phases 0-4 are done.

Do not start a phase’s tasks before the prior phase’s Completion Markers are checked — same gating discipline as SIM_DATABASE_IMPLEMENTATION.md.