DEVELOPER_NOTES.md

PUE Report Agent

Turns a DREEF Productive Use of Energy (PUE) master-sheet workbook into a community PUE report, using LangChain for the prose and deterministic Python for every number.

This skeleton was reverse-engineered from two real report/master-sheet pairs (Jaji and Kwarua Tasha, both Kaduna State). It already extracts cleanly from both.

---

Why it's split the way it is

The single most important design decision: the LLM never touches a number.

When a report's figures are re-typed by a language model, they drift — that is literally how one of the source reports ended up with budget and equipment numbers that didn't match its own master sheet. So the architecture is:

master.xlsx
   │
   ▼  extractors.py          (pure Python, openpyxl — deterministic)
PUEReportData
   │
   ▼  calc.py                (recompute every total from line items)
PUEReportData (correct totals)
   │
   ▼  validate.py            (reconciliation; appends warnings)
PUEReportData (+warnings)
   │
   ├─►  agent.py  generate_narratives()    LLM — PROSE ONLY
   │      • Community Overview  ............  Gemini  (gemini-2.5-flash)
   │      • Demographic narrative  .........  Gemini
   │      • Processing Insights  ...........  Gemini
   │
   ▼  render.py              →  template-faithful .docx

• schema.py — Pydantic models. The contract between every stage.
• extractors.py — reads the workbook into the schema. Numbers come from here, never from an LLM. Budget extraction is header-aware (each section maps its own columns), so irregular blocks like "OTHERS" parse correctly.
• calc.py — recomputes every total from its line items: budget section totals, BOQ grand total, equipment kW/quantity totals, energy projections, financial-model monthly totals. The template shipped with a Grand Total of ₦30,756,000 when its line items summed to ₦37,256,000; the agent never reproduces that kind of error, and records any disagreement as a warning.
• validate.py — checks interview counts agree, flags stale template text.
• templates.py — the boilerplate sections, verbatim from the reference template (introduction, methodology, infrastructure specs, governance, safety, conditions for scale-up, MER, risks, SDGs, challenges, AI disclosure), filled with str.format. Never sent to a model.
• agent.py — the only LLM code. All three narratives (Community Overview, demographic, processing insights) are generated with Gemini (gemini-2.5-flash). The model is swappable per call, so individual chains can move to another provider later without changing the chains.
• render.py — produces a Word document that mirrors the reference template's structure exactly.

What render.py reproduces from the template

• Title page in the template's layout.
• Auto Table of Contents — a real Word TOC field that builds and renumbers itself on open.
• Auto List of Tables — a Word Table-of-Figures field keyed to the "Table" caption sequence.
• SEQ-field caption numbering — every caption is Table + a SEQ Table field (or Figure + SEQ Figure). Word computes the numbers, so they are always sequential and correct no matter how many tables a community needs — nothing is hard-numbered.
• Every section in template order with the template's headings and tone.
• Community-adaptive tables — one machinery table per processed crop, one equipment table per section, one budget table per section, each with a recomputed TOTAL row. The skeleton stays identical; the rows follow the data.
• Fields are set to update on open, so the TOC, List of Tables and all caption numbers populate the first time the file is opened in Word. (LibreOffice's headless PDF convert doesn't run that update — open in Word, or press Ctrl+A then F9, to populate them.)

---

Quick start

pip install -r requirements.txt

# Extraction + recompute + validation + render — no API key needed.
# Narratives are skipped with --no-llm, but the full template still renders:
python run.py "Jaji-_Kaduna_PUE_Master_Sheet.xlsx" --no-llm \
    --developer "Green Edge Consortium" --date "February 2026" \
    --solar-pv "6.45 MWp" --battery "10 MWh" \
    --docx-out jaji_report.docx

# Full run with AI narratives (all narratives use Gemini):
export GOOGLE_API_KEY=...
python run.py "Jaji-_Kaduna_PUE_Master_Sheet.xlsx" \
    --developer "Green Edge Consortium" --date "February 2026" \
    --docx-out jaji_report.docx --json-out jaji.json

Open the resulting .docx in Word; the Table of Contents, List of Tables and all caption numbers populate automatically on first open.

---

What the data layers in the master sheet mean

Layer	Sheets	Role
Raw survey	Minigrid Farmers Input, Processors, SME, E-Mobility, Market, ESG	One row per respondent
Analysis	* Analysis Sheet	Pre-aggregated label/value blocks
Key Findings	Key Findings	Report-ready tables (the extractor's main source)
Equipment / Budget / Finance	Proposed * PUE Equipment, Budget, Individual Model, Agrohub Model, Summary	Plans & costs

The extractor prefers raw/analysis sheets for survey facts and only uses Key Findings for developer-supplied figures (capacities, revenue-per-user, projected impact) that aren't derivable from survey data — because Key Findings is where stale template text from other communities tends to survive.

Things that are NOT in the survey data

• Mini-grid capacities (Solar PV / Battery / Annual Consumption) — developer input.
• Revenue-per-user table — developer input, often shared across communities. Pass these in explicitly; the validator warns if they're missing.

---

Extending to a new community / new sheet layout

1. Run python run.py new_sheet.xlsx --no-llm.
2. Read the warnings and eyeball the summary against the source.
3. If a block comes back empty, the label probably moved. The extractor finds blocks by label text scanned across all columns (_label_col) rather than fixed cell addresses, so usually only the label string needs updating.
4. Add a validate.py check for any new invariant you discover.

Adapting to a community whose data is shaped differently

The renderer is data-driven, so new crops, new SME types, more or fewer budget sections, etc. flow through automatically — the template skeleton stays fixed while the rows follow the master sheet. The two things to supply per community that are NOT in the survey data:

• Mini-grid / micro capacities (Solar PV, battery, annual consumption, distribution metrics) — developer figures; pass via the CLI flags or set data.minigrid / data.microgrid before rendering.
• Report date for the title page (--date).

---

Tested against

• Jaji-_Kaduna_PUE_Master_Sheet.xlsx — clean extraction, 3 advisory warnings.
• Kwarua_Tasha_-_Kaduna_PUE_Master_Sheet.xlsx — clean extraction, 6 warnings correctly flagging real defects in that workbook.