Service Classes - Piping Spec Revision System

Service Classes is a web application I built to replace a legacy Microsoft Access database that held Syncrude's Standard L-1 'Pre-Engineered Piping Service Classes' - the specifications that tell engineers which piping components, pressure and temperature limits, and materials are allowed for a given service. I rebuilt it as a modern, revision-first Next.js app so those specs are versioned, traceable, and reproducible.

The problem

Piping service classes are engineering law on a plant - they define what is allowed to be built. The reference data lived in an aging Access database where history was hard to reconstruct: once a spec changed, it was difficult to prove exactly what an earlier revision contained. For pressure equipment that has to be auditable, that is a real risk. I set out to make every revision an exact, immutable snapshot you can always fetch and reproduce.

What it does

• Stores every service class as a series of revisions, where each revision is a complete, self-contained snapshot - components, pressure/temperature tables, branch rules, notes, and drawings.
• Supports hard (official numbered) and soft (in-progress) revisions, with a clean workflow to create a new revision by cloning any previous one.
• A controlled publishing model: revisions are drafted, then issued, then explicitly published to specific audiences (admins, engineers, users), with per-audience access to history.
• Regenerates any past stamped document release - for example an Appendix D index - exactly as it was originally published.
• Exports revisions to PDF straight from the snapshot data.

How it is built

Service Classes is a Next.js (App Router) + TypeScript app backed by Prisma and Postgres, with Azure AD sign-in and a shadcn/ui + Tailwind interface.

• Next.js App Router + TypeScript, with server-side writes via Server Actions and Zod validation
• Prisma + Postgres; every physical quantity is stored as an exact Decimal (never a float) so engineering values stay precise
• Azure AD / NextAuth authentication with admin / engineer / user roles
• pdfkit for the stamped PDF export

Immutability by design

The core rule is that issued revisions can never change. I enforced that in two places at once - in the application logic and at the database level with Postgres triggers - so even a direct database write cannot mutate a released spec. New revisions are always created by cloning a prior one inside a single transaction, every edit to a draft is written to an append-only change log (JSON before and after), and a partial unique index guarantees exactly one current published revision per class. The result is a system where Rev 0 always stays Rev 0.

Migrating the legacy data

Beyond the app itself, I built the pipeline to migrate the legacy Access data into the new revision model - raw extract, to staging, to reconciliation, to immutable snapshots - and validated the result against the original stamped PDFs, keeping a legacy-ID cross-reference so every migrated record traces back to its source.

What I am proud of

This is the kind of software I most want to build: it takes something engineers rely on every day, and that has to be auditable, and makes it trustworthy by design. It sits right next to my reliability and pressure-equipment work, and it forced the hard, satisfying engineering of getting versioning and immutability genuinely right.