Case study — field operations software

Public Lighting
Ops Console

A ticket-to-repair system built for a regional electrical-infrastructure contractor — triage, role-gated dispatch, and a paper-to-digital field report loop, replacing a process that ran on phone calls and loose paper forms.

Reports trackedLive
Access controlDB-level
Field reportsExcel round-trip
Public lookupNo login
The starting point

A network held together by phone calls

Street-lighting faults came in through a public form, then lived in whoever's memory happened to answer the phone. There was no shared view of what was open, what a crew had actually found on-site, or which reports were sitting done-but-undocumented. Two roles needed two different slices of the same list — the intake desk closes a report, the field lead writes up what was done — and neither should be able to quietly do the other's job.
The brief was a single console both roles could work from, with field reporting that didn't force crews to abandon the paper form they already carried on-site.
Triage & dispatch

One list, two roles, no back doors

Every report carries a status (open → resolved → report pending) and a type — an accident report always surfaces above a routine fault, regardless of when either was filed. The intake role can close a report; only the field-operations lead can write the resolution note. That split isn't just hidden in the UI — it's enforced as a rule at the database itself, so it holds even against a direct API call.
Reports Interactive
Data layer

One table, two very different audiences

A managed relational database with row-level access rules scopes every table to the signed-in account — but the public status lookup has no login at all, and can't just read the same table with a relaxed policy without also handing every visitor a caller's name, phone, and email. It reads from a separate SQL view instead, one that never selects those columns in the first place. The two audiences are separated by the schema itself, not by an application-level check a future edit could forget to keep.
Table

tickets

id, ticket_number
address, type, status
submitted_on, resolved_on
name, phone, email
resolution_note
→Access rule + view,
no login required
View

public_tickets

id, ticket_number
address, type, status
submitted_on, resolved_on
Field reporting

The paper form stayed. Only the retyping left.

Crews already worked from a printed repair sheet, filled in by hand at the pole: which parts came off, which went back on, whether the line was re-energized. Replacing that habit wasn't realistic. Instead, the console exports a print-ready sheet — same column layout, same grouped headers, same signature line as the physical form — pre-filled with that day's open tickets. Once it's filled in and scanned back, the same sheet is re-uploaded: rows are matched to tickets by number or by street name, and each crew's checkmarks are turned into a structured resolution note automatically.
01 — EXPORT

Generate

Today's open tickets laid out on the real repair-sheet template, paginated and print-scaled to fit A4.

→
02 — FIELD

Fill in

Crew marks each repair step by hand, same as they always have — no device, no app, no retraining.

→
03 — IMPORT

Reconcile

Sheet comes back in. Rows match to tickets automatically; marked steps become one written note per ticket.

Engineering notes

What made this harder than it sounds

Print math

Deterministic Excel scaling

Letting Excel auto-fit a sheet to one page produces a different result on every renderer. Fixed by computing the scale explicitly — the same 7px-per-character approximation Excel itself uses — then deriving row height and page breaks from that one known number instead of hoping the software agrees with itself.

Session state

No page reload, no leftover role

Switching accounts in the same tab left the previous person's role-specific UI on screen for a beat before the new one loaded. Every one-way DOM change from login now has an explicit, reversible undo on logout — nothing assumes it'll never need to run backward.

Layout

Pages packed by real height, not a guess

A hand-filled street name can wrap to two or three lines — Excel never grows a row to fit that on its own. Each row's height is now computed from its own content, and tickets are packed onto a page by actual accumulated height rather than a fixed count per page.

Matching

One ticket, however it's written

A field-typed 96, a stored TICKET-0096, and a street name missing its "Str." all have to resolve to the same report. Digit-extraction plus stopword-aware address matching handle both directions with the same rule.