In production250K-member electric cooperative Confidential system, demo on request

Wholesale Power Invoice Automation

A full workday of manual invoicing now takes under 30 seconds, with zero transcription errors.

Automated PDF invoicing for the Philippine wholesale electricity spot market. A ~50-invoice settlement batch that took a full workday now runs in under 30 seconds.

  • ~50 invoices per settlement batch
  • 25 API endpoints across 5 routers
  • 59 passing tests across 10 files
  • Shares its invoice-number ledger with a live, third-party-owned billing system
Wholesale Power Invoice Automation screenshot

A full workday of manual invoicing → under 30 seconds, zero transcription errors

Team of one· Solo architect & full-stack engineer

No team, no agency, no hand-offs. I owned this project end to end:

  • Requirements
  • Architecture
  • UI & UX
  • Backend & APIs
  • Database
Stack
React 19FastAPIMS SQL ServerTypeScriptPDF Pipeline

Where AI does the work: Automation handles registry lookups, numbering, and PDF rendering, so staff upload a file and review the result.

What was broken, and what fixed it

  1. Problem: Each batch meant copying data into Word templates, looking up TINs in spreadsheets, and tracking the next invoice number by hand: a full workday.

    Fix: A four-step wizard takes the settlement CSV from upload to PDFs, and a participant registry fills in name, address, and TIN. A ~50-invoice batch now runs in under 30 seconds.

  2. Problem: Invoice numbers come from a ledger shared with a live billing system, so a mistake could collide with a real invoice.

    Fix: A fail-closed gate writes to the live ledger only when a server setting and a runtime toggle both say production. Anything else goes to a sandbox table, with numbers kept above the live sequence.

  3. Problem: Staff use the same live ledger all day, so invoicing could not lock them out.

    Fix: Numbers are allocated without blocking, with a duplicate-key retry up to 5 times and a bounded lock wait, so a slow third-party app makes this one fail fast and retry.

  4. Problem: Every invoice had to match the official pre-printed form.

    Fix: The PDF layout was reverse-engineered from invoice scans and drawn at exact coordinates, with per-invoice, consolidated, and ZIP downloads.

  5. Problem: The database schema belongs to a third-party billing system, so migrations were off the table.

    Fix: The app works through raw parameterized SQL against the existing schema and leaves it as it is.

Approach

  • A fail-closed write gate: a per-server setting and a runtime toggle must both say production, or the app writes to a sandbox table. Any missing, unknown, or failing setting resolves to the sandbox.
  • The same function decides the target table and the mode badge the operator sees, so the UI cannot misreport what will happen.
  • Invoice numbers are allocated without blocking the collection office, with a unique-index retry (up to 5 times) and a bounded lock wait.
  • One source of data from upload to PDF, so nothing is retyped.

What it does

Invoice generation

  • CSV upload with 19-column validation and typed parsing
  • Billing IDs checked against the registry before generating, with a one-click "Add to Registry" for missing ones
  • Live progress bar and log streamed per invoice
  • PDF layout reverse-engineered from invoice scans

Numbering and safety

  • Two-layer fail-closed test and production gate
  • Sandbox numbers kept above the live sequence so they never collide
  • Non-blocking allocation with duplicate-key retry
  • Bounded lock wait, so a slow third-party app makes this one fail fast and retry

Output

  • Per-invoice PDFs, one consolidated PDF, or a ZIP of the batch
  • Save to a folder the operator picks, mirroring the server layout

Registry and dashboard

  • Participant registry with search, filters, side-drawer editing, and bulk CSV upsert
  • 4 KPI cards, monthly trends, and region and facility-type breakdowns
  • Recent batches with batch-level delete

Security

  • JWT in httpOnly cookies, bcrypt passwords, and admin and user roles
  • Parameterized SQL throughout and allowlisted sort columns
  • App refuses to start if required settings are missing

Architecture

Interface

  • React 19
  • Voice

Logic & APIs

  • FastAPI
  • TypeScript
  • PDF Pipeline

Data

  • MS SQL Server
  1. 1
    ReactFastAPI

    A single FastAPI process serves the React 19 app and the API on one port, for simple LAN deployment.

  2. 2

    Raw parameterized SQL through pyodbc, because the schema is owned by a third-party system.

  3. 3
    Voice

    ReportLab draws each invoice at exact coordinates to match pre-printed carbonless forms.

  4. 4
    PDF pipeline

    Each row: look up the participant, allocate a number, insert the audit row, render the PDF, stream progress.

Engineering

  • 59 passing tests across auth, CSV parsing, numbering, invoice mode, and generation.
  • Fail-closed by design: any unexpected exception in the mode check resolves to the sandbox table.
  • Runs alongside the staff who use the same live ledger, without blocking them.

Outcome

  • A full workday of manual invoicing → under 30 seconds, zero transcription errors.
  • Every generated invoice is recorded for audit automatically.