Live250K-member electric cooperative

Consumer Billing & Self-Service Portal

Members check bills and track outage repairs from their phones, and the billing database is never exposed to the internet.

A self-service portal where consumers see their bills, payments, and usage, report outages and follow the repair, while the billing system itself never touches the internet.

  • Live in production, serving member-consumers across 17 towns and cities
  • 370+ test cases across 29 suites
  • 13-month usage history per account
Consumer Billing & Self-Service Portal screenshot

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
  • Infra & deployment
Stack
Next.jsSupabaseCloudflare TunnelSync AgentMS SQL Server

Where AI does the work: Automation keeps the portal current and the queue moving: the sync agent refreshes billing data on demand, reports route themselves to the right office, and unacknowledged tickets escalate on their own.

What was broken, and what fixed it

Screens show demo data; client data stays private.

  1. Problem: Members had to wait for the paper bill, or line up at an office, just to learn what they owed.

    Fix: Balance, due date, and an unpaid-month warning on one screen, for every account the member owns, on any phone.

    Overview: balance, due date, unpaid warning, last payment, latest usage, and a severe-weather alert.
    Overview: balance, due date, unpaid warning, last payment, latest usage, and a severe-weather alert.
    Every bill for every account the member owns, with paid and unpaid status.
    Every bill for every account the member owns, with paid and unpaid status.
    Home on a phone: balance and one-tap actions.
    Home on a phone: balance and one-tap actions.
    Bills on a phone.
    Bills on a phone.

  2. Problem: A higher bill was one number nobody could explain, so members called or walked in to ask why.

    Fix: Every bill reads line by line (generation, transmission, distribution, taxes) with a plain-language reason it changed, next to the published rate schedule.

    One bill, line by line, with a plain-language reason it changed from last month.
    One bill, line by line, with a plain-language reason it changed from last month.
    The published rate schedule, so members can check the math themselves.
    The published rate schedule, so members can check the math themselves.

  3. Problem: Members only found out their bill would jump after it arrived.

    Fix: 13 months of usage, comparisons to last month and last year, a next-bill estimate, and an appliance calculator at the member's own rate.

    13-month usage, comparisons to last month and last year, and a next-bill estimate.
    13-month usage, comparisons to last month and last year, and a next-bill estimate.
    Appliance cost estimator at the member's own effective rate per kWh.
    Appliance cost estimator at the member's own effective rate per kWh.
    Usage chart on a phone.
    Usage chart on a phone.

  4. Problem: A payment made through GCash, Bayad Center, or another office could not be confirmed without a call.

    Fix: Payment history across every channel, plus a where-to-pay map with the account number ready to copy.

    Payment history across offices, GCash, Bayad Center, and ECPay.
    Payment history across offices, GCash, Bayad Center, and ECPay.
    Where to pay: the account number to copy, channels, and a map of offices and mall counters.
    Where to pay: the account number to copy, channels, and a map of offices and mall counters.

  5. Problem: Outage complaints came by phone and walk-in, with no record anyone could follow.

    Fix: Members pin the spot on a map. Each report becomes a numbered job order, routed to the office that serves that town, and the member sees its live status.

    Report an outage or hazard by pinning the spot on a map, then follow its status.
    Report an outage or hazard by pinning the spot on a map, then follow its status.
    Staff outage map: every open report pinned, with status and next steps.
    Staff outage map: every open report pinned, with status and next steps.

  6. Problem: Nobody could answer “where is the lineman on this one?”, and some tickets sat unnoticed.

    Fix: A dispatch timeline stamps every step with who and when. Unacknowledged tickets escalate to the supervisor, then the manager, and staff get push alerts. Linemen read the same job order on their phones.

    Open job orders across offices, grouped by status, with one-tap actions.
    Open job orders across offices, grouped by status, with one-tap actions.
    A job order's timeline: who acknowledged, who was dispatched, and every crew note.
    A job order's timeline: who acknowledged, who was dispatched, and every crew note.
    The same job order on the lineman's phone.
    The same job order on the lineman's phone.

  7. Problem: Management had no view across area offices.

    Fix: One board shows open tickets per office, the oldest unacknowledged one, escalations, and each lineman's workload.

    All offices at a glance: new, acknowledged, dispatched, oldest new, escalations, and lineman workload.
    All offices at a glance: new, acknowledged, dispatched, oldest new, escalations, and lineman workload.

  8. Problem: Most members are on phones over slow mobile data.

    Fix: The phone layout is designed on its own, not shrunk from desktop, and a device cache paints the last known data before the network answers.

    Public landing page where members register and sign in.
    Public landing page where members register and sign in.
    Landing page on a phone.
    Landing page on a phone.

Approach

  • An on-prem sync agent is the only part that reads the billing database. It mirrors only what the portal needs, pushes outbound, and opens no inbound ports.
  • Read-through, not a bulk copy: a member's data is mirrored only after they claim the account, and refreshed on demand.
  • Data isolation enforced in SQL: every account-scoped query is guarded against the caller's linked accounts, with row-level security set to default-deny.
  • Self-hosted behind a tunnel, with automatic deploys, health checks, and rollback, and a cloud host as a warm fallback.

What it does

Member self-service

  • Bill and balance for every account a member owns, with due date and unpaid-month warning
  • Line-by-line bill breakdown with a plain-language reason it changed from last month
  • 13-month usage, comparisons to last month and last year, and a next-bill estimate
  • Payment verification across channels
  • Where to pay, with a map of offices and mall counters
  • Appliance cost estimator at the member's own effective rate
  • Severe-weather warnings for the franchise area

Outage and job orders

  • Report an outage, hazard, meter, or billing problem with a map pin
  • Every report becomes a numbered job order routed to the office that serves that town
  • Dispatch timeline: acknowledged, assigned, en route, on site, restored
  • Unacknowledged tickets escalate to the supervisor, then the manager, on a schedule
  • Members see live status in the app

Staff tools

  • Ticket board across all area offices, with transfers
  • Office tenders file for phone and walk-in callers
  • Web Push alerts for new tickets in the staffer's area
  • All-offices management view: open tickets, oldest unacknowledged, escalations, lineman workload
  • Account lookup and user management

Security and privacy

  • Account claim with an email code plus a detail from a real bill, with rate limits
  • Unauthorized and missing records return the same 404, so accounts cannot be probed
  • Role and office scoping re-checked on every admin page and server action
  • A lineman sees a caller's number only on a job assigned to them
  • Strict CSP, HSTS, database TLS pinned to its CA, bot checks on auth, WAF and rate limits

Architecture

Interface

  • Next.js

Logic & APIs

  • Sync Agent

Data

  • Supabase
  • MS SQL Server
  • PostgreSQL
  • Row-level security

Infrastructure & delivery

  • Cloudflare Tunnel
  • Server
  1. 1
    Next.jsSupabasePostgreSQLSQL Server

    Billing system (SQL Server, office LAN) → on-prem Node sync agent → Supabase Postgres mirror with default-deny row-level security → Next.js 16 portal.

  2. 2
    CloudflareServer

    The portal runs on the cooperative's own server behind a Cloudflare Tunnel; a poller deploys the main branch within minutes, health-checks it, and rolls back on failure.

  3. 3

    If a sync fails, the portal keeps serving the last data with an "as of" time instead of going down.

  4. 4

    Charts are hand-built SVG and maps are hand-rolled map tiles, with no chart or map library.

Engineering

  • A test asserts the account guard on every exported data function.
  • Moved to a transaction pooler after per-instance session pools ran out of connections under load.
  • Replaced a 170 ms auth round-trip per request with local JWT verification.
  • A stale-while-revalidate device cache paints the last known data before the first network byte on slow mobile data.
  • 370+ test cases across 29 suites in Vitest.

Outcome

  • Live in production for members across 17 towns and cities.
  • Bill, payment, and usage questions that used to need a call or an office visit are answered in the app.
  • Every outage report is a numbered job order with a timeline, so members, dispatchers, and management follow the same record.
  • The portal reads from a mirror, so member traffic never reaches the billing database.