Skip to content
juancespedes.dev
ES: Leer esta página en español
All projects
Case studyAnonymized

Digital operations logbook for two thermal power plants

I replaced the Excel shift logbooks with a real-time web application, with shifts as a business entity, official reports generated from the data and more than 1,400 automated tests.

Project facts

Role
Full-stack engineer, sole developer (design, backend, frontend, database, deployment)
Period
Mar 2026 to present
Public links
Internal system: the code is private, and names, data and diagrams were left out or recreated.

  • 1,400+

    automated test cases across backend and frontendSource: node:test and Vitest suites in the repository, September 2026

  • 516

    commits over 6 months of continuous developmentSource: Git history, March to September 2026

  • 38 of 39

    findings closed from an in-house security auditSource: Internal security audit report, 2026

The problem

Operations staff at two thermal power plants kept their shift logs in Excel workbooks. Information was scattered across files, closing a shift depended on each person's discipline, and the official daily reports were assembled by hand by copying cells.

My role

  • Gathered requirements with shift supervisors, operations engineers and chemists, and turned them into a domain model of shifts, logbooks and role-based permissions.
  • Designed and built the frontend, the backend, the database and the corporate sign-in integration.
  • Deployed and run the system on a company Linux server, with nginx, systemd and an update script with built-in checks.
  • Documented 71 architecture decisions and drove an in-house security audit until 38 of 39 findings were closed.

Constraints

  • Users on 24-hour shifts whose operating day crosses midnight.
  • Mandatory corporate sign-in with role-based access.
  • Self-hosted server on the corporate network, behind a firewall that inspects TLS.
  • Minimal third-party dependencies to keep auditing and maintenance simple.

Decisions

  1. 01

    Shifts as a business entity, closed atomically

    Closing a shift freezes the team roster into an immutable snapshot, so history does not change when staff changes later.

    Ruled out

    • Store loose events and rebuild shifts at query time
  2. 02

    Real time over WebSockets with origin validation

    Several people work on the same shift at once; changes arrive instantly and origin validation prevents connection hijacking.

    Ruled out

    • Poll the server every few seconds
  3. 03

    In-house .xlsx reader and writer on top of zlib

    The official report had to match the Excel template exactly without adding large libraries to a backend that has only 6 production dependencies.

    Ruled out

    • Use a third-party spreadsheet library
  4. 04

    A tested data contract with the generation dashboard

    Another production system consumes the logbook events. A contract test fails if either side changes the shape of the data, and each system writes only to its own schema.

    Ruled out

    • Let the dashboard read the logbook tables directly

Architecture

Each operator's browser talks to a Node.js API over HTTPS and WebSockets. The API stores everything in SQL Server, runs scheduled jobs that close shifts and ingest plant data, and exposes a data contract to the generation dashboard.

  1. Users

    • Operations web appReact, views per role and per plant

  2. Services

    • Logbook APIExpress, 19 domain modules
    • Real-time channelWebSockets with origin validation
    • Scheduled jobsShift closing, hourly ingestion and dispatch

  3. Data

    • SQL ServerCatalogs and transactions in separate schemas
    • Official Excel reportGenerated from the data

  4. Integrations

    • Microsoft Entra IDSign-in and role-based access
    • Plant historianHourly fuel consumption
    • Generation dashboardConsumes events through the contract

How data moves

  1. Operations web apptoLogbook APILogs events and closes shifts
  2. Real-time channeltoOperations web appPushes shift changes and active users
  3. Logbook APItoSQL ServerStores every entry with its shift
  4. Logbook APItoMicrosoft Entra IDValidates the session and permissions
  5. Plant historiantoScheduled jobsDelivers each hour of consumption
  6. Logbook APItoOfficial Excel reportBuilds the daily workbook
  7. Generation dashboardtoLogbook APIQueries events by period

Results

  • Both plants log their shifts in the application, with each shift's history frozen when it closes.
  • The official daily report comes out of the data in the template's format, with no cells copied by hand.
  • The generation dashboard consumes logbook events through a tested contract, without touching its tables.

What I would do differently

  • From day one I would prioritize the tests that protect dangerous invariants, such as never deleting data from a real plant, over overall coverage.
  • I would move the scheduled jobs into a separate service earlier, so the API can restart without pausing plant data ingestion.

Technologies

  • React
  • Vite
  • Node.js
  • Express
  • WebSockets
  • SQL Server
  • Microsoft Entra ID
  • Microsoft Graph
  • Excel
  • Linux
  • nginx
  • systemd