The AI Workbench:
Accelerating Engineering with Gemini & Codex
A transparent look at how Dinesh Mahajan used Gemini CLI and AI coding assistants to build a multi-layer ETL pipeline in record time—from architecture design to terminal-based validation.
At pchomes inc, an EPC company in the residential solar space, we have a small team of developers working on a disruptive business model in the residential solar niche. One of the key differentiators is an ROI analysis tool accessible as a free service. This service provides an arms-length comparison of a homeowners present electricity monthly bill with 500 + other energy plans for the state of Texas. We want to share how one of our frontend developers was able to create parsing code for plans offered by Octopus Energy, which come in the form of PDF files, one for each plan.
Project Context: Parsing Complex Energy Tariffs
At pchomes inc (an EPC company in the residential solar space), we developed an ROI analysis tool that compares a homeowner's current electricity bill with 500+ other available utility plans. To make these comparisons accurate, we must extract precise rates from utility rate sheets, which are published as unstructured PDFs—known in Texas as Electricity Facts Labels (EFLs).
A parser is a specialized software program designed to scan these unstructured documents, identify key text patterns, and extract numerical data (such as base charges, volumetric rates, and seasonal peak windows) into structured database fields.
This case study focuses on Octopus Energy, a global retail electric provider renowned for innovative, dynamic, and time-of-use tariffs. Because Octopus's plans feature complex, multi-tiered rates that vary by season and time of day, automated parsing is essential. It replaces manual data entry with a scalable, error-free pipeline, enabling the high-fidelity simulations needed to calculate precise solar and battery ROI.
The parsing engine was not built using traditional manual coding alone. Instead, it was developed through a high-velocity collaboration between a human architect and an AI agent operating directly in the terminal via the Gemini CLI. This document explains the tools and workflows that made this possible.
"The terminal is no longer just for commands; it's a conversational space where architecture is discussed, code is written, and bugs are fixed in real-time by a human-AI duo."
The Dual-AI Strategy
To achieve 100% completion of the workflow, we utilized two distinct AI capabilities in a unified, high-velocity loop:
Gemini CLI: The "Hands" of Execution
The Gemini CLI served as the operational layer. It autonomously navigated the directory structure, read the existing source code to learn the project's patterns, and executed terminal commands (dotnet build, dotnet test). By using tools like replace and write_file, it performed the heavy lifting of code integration, ensuring that new logic was woven seamlessly into the existing USRoofs projects without a single manual edit by the human researcher.
Claude.ai & Codex: The "Brain" of Logic
The underlying Claude.ai and Codex-class models acted as the intelligence layer. This "brain" was responsible for the complex synthesis of business logic. It translated raw requirements from the project brief and PDFs into precise C# Entity Models and surgical Regular Expressions. Whether it was calculating seasonal date ranges or capturing multi-tier electricity rates directly from PDF text, this layer provided the high-fidelity code that the CLI then executed.
Choosing the Right Tool: The Triple-AI Strategy
To maximize velocity, we treat AI tools as specialized departments within a single engineering firm. Here is the decision matrix used for this project:
| Role | AI Tool | Specific Use Case |
|---|---|---|
| The Architect | Claude.ai | High-level architectural design, discussing "why" decisions, and complex logical synthesis. |
| The Builder | Codex | Idiomatic C# generation, surgical Regex engineering, and EF Core mapping implementation. |
| The Executor | Gemini CLI | Autonomous terminal orchestration, codebase navigation, code injection, and validation. |
Rule of thumb: Claude designs the strategy, Codex builds the logic, and Gemini CLI applies and validates it in the terminal.
The Workflow: Step-by-Step
Codebase Discovery
The human (Dinesh Mahajan) initiated the session by providing the context of the existing backend. Using the glob and read_file tools, the AI agent scanned the project structure to understand the existing repository patterns and database schemas without any manual documentation. It "learned" the project by reading it.
Architectural Strategy & Documentation
Instead of writing code immediately, Dinesh Mahajan used Claude.ai to discuss the overall strategy and draft high-level documentation. This phase established the "two-layer approach" to keep PDF logic separate from ETL orchestration.
Incremental Implementation
Implementation was done in small, verifiable chunks. The AI used the write_file tool to create one class at a time. After each file, Dinesh Mahajan would inspect the code or ask the AI to run a build command (dotnet build) to ensure no syntax errors were introduced.
Integration & Verification
The AI was tasked with "wiring up" the new services into the existing controllers. It performed the dependency injection and created the new API endpoints automatically, then verified them by running the project in the background and checking the health endpoints.
Collaborative Roles: Human + AI
The success of this project relied on a clear division of labor between the researcher (Dinesh Mahajan) and the specialized AI layers:
Dinesh Mahajan + Claude.ai: Synthesizing the "What"
Dinesh Mahajan's Role: Provided business context, shared the technical brief, and established the architectural direction.
Claude.ai's Role: Acted as the high-level reasoning architect. It processed Dinesh Mahajan's intent to generate the complex C# models, design the report structures, and provide refined technical explanations for the methodology.
Dinesh Mahajan + Gemini CLI: Executing the "How"
Dinesh Mahajan's Role: Acted as the Project Director, issuing high-level execution directives and validating the AI's terminal output.
Gemini CLI's Role: Acted as the autonomous developer. It received Dinesh Mahajan's instructions to navigate the codebase, apply surgical edits via replace, run dotnet build, and execute the test suite to ensure every change was functional and error-free.
Case Study: The Octopus Energy Parsing Engine
The "Octopus Parsing" task involved creating a scalable system to ingest electricity plan documents (PDFs) from Octopus Energy and convert them into structured database records. This documentation outlines the architectural decisions and implementation steps used to bridge the gap between messy document text and high-integrity data.
Data integrity is the foundation of energy analysis. By combining deterministic regex-based parsers with a flexible ETL orchestration layer, we ensure that every watt and cent is accounted for exactly as printed in the source PDF.
The Architecture: A Two-Layer Approach
To maintain separation of concerns and maximize reusability, the engine was split into two distinct C# projects:
- USRoofs.Energy.PDF: The "Brain." It handles raw text extraction, deterministic type classification, and specific parsing logic for each plan type (A through F).
- USRoofs.Energy.ETL: The "Orchestrator." It manages manifests (JSON/CSV/Text), handles local or remote file resolution, and drives the multi-threaded processing loop using
OctoEtlProcessor.
Step-by-Step Execution Flow
Manifest-Driven Input
The system is "manifest-first." Whether it's a simple list of file paths, a CSV with plan names, or a structured JSON object, the OctoEtlManifestReader normalizes these inputs into a common processing queue. This allows the same engine to process 10 local files or 10,000 remote URLs without code changes.
Deterministic Classification
Before parsing, every PDF is run through the PdfTemplateClassifier. Instead of guessing, it uses strict string-matching rules to identify the template (Type A: Fixed, Type C: Flex, Type E: Bill Credit, etc.). This ensures the correct specialized parser is always applied to the correct document layout.
Specialized Regex Parsing
Each plan type has a dedicated parser (e.g., TypeCFlexParser). These parsers use complex Regular Expressions to anchor on specific labels like "TDU Delivery Charges" or "Base Charge." This approach is 100% deterministic, meaning it will never "hallucinate" a value—it either finds the exact match or it fails, maintaining total data reliability.
AI-Assisted Augmentation
For documents that don't fit a standard template or for fields where deterministic rules are too brittle, the system leverages PDFUtils integrated with Google Gemini. This provides a "safety net" that can extract structured JSON from unstructured text when enabled, combining the speed of code with the intelligence of AI.
Workbook-Aligned Persistence
The output of the parsers is mapped directly to a SQL schema defined by the business workbook. For Type C plans, this involves a parent OctopusParse record and four SeasonDependent child records, capturing seasonal peak/off-peak rates and time windows with sub-second precision.
Idempotent Import Flow
To prevent data duplication during re-runs, the import logic is idempotent. It checks if a record for a specific PDF already exists; if so, it performs an update on the parent and replaces the child rows, ensuring the database always reflects the latest state of the files without clutter.
Parallelized Processing
The OctoEtlProcessor utilizes SemaphoreSlim to process up to 8 PDFs in parallel. This optimizes throughput when dealing with hundreds of documents, significantly reducing the "wall-clock" time for a full folder scan.
Automated Validation
A comprehensive test suite (WebHooks.Tests) validates every parser against real-world PDF text samples. These tests ensure that changes to one parser don't break others and that new document variations are handled correctly before they ever reach the production database.
The Octopus Parsing Tools
| Component | Role in the Pipeline |
|---|---|
| OctoEtlProcessor | Orchestrates the entire flow: manifest reading, file fetching, classification, parsing, and result aggregation. |
| PdfTemplateClassifier | Determines if a PDF is Type A, C, D, E, or F based on layout and keyword signatures. |
| TypeCFlexParser | Specialized logic for the complex "Octopus Flex" plans, extracting seasonal time-of-use rate matrices. |
| PDFUtils (Gemini) | Provides AI-driven structured data extraction from PDF text using Google's Gemini models. |
| Entity Framework Core | Handles persistence to the Postgres database with precise column mapping to match workbook requirements. |
| xUnit / Moq | Provides the testing framework to verify parser accuracy against known "Golden Samples." |
Sample Manifest Structure
The engine can consume a simple JSON manifest to drive batch processing:
{
"sourceRootPath": "/data/octopus/pdfs",
"pdfs": [
{ "source": "25.pdf", "name": "Octopus Flex April" },
{ "source": "38.pdf", "name": "Octo Green Prepay" },
{ "source": "https://example.com/plan.pdf", "name": "Remote Plan" }
]
}
What This Demonstrates
This project is a masterclass in Practical ETL Engineering. It demonstrates that while AI is a powerful tool for unstructured data, it is most effective when wrapped in a rigid, deterministic framework. The Octopus Parsing Engine provides:
- Reliability: 100% accuracy on standard templates using regex anchors.
- Scalability: Multi-threaded processing and manifest-driven batching.
- Maintainability: Layered architecture allowing for easy addition of new plan types (e.g., Type G, H).
- Auditability: Every database record can be traced back to its specific source PDF and template classification.
The result is a production-ready data pipeline that transforms a folder of PDFs into a rich, queryable dataset, powering the BessAndSolar energy analysis platform.
Tools Used: Gemini CLI (Terminal-based AI Agent), Google Gemini 1.5 Pro, .NET 8 SDK, Bash. The entire pipeline was developed, tested, and documented within a single AI-assisted session.