Why We're Rebuilding Zunavi's Drawing Ledger From Scratch

The Question Everyone Asks: Why Not Just Patch the Old System?

When we talk about Zunavi — the drawing management platform we’re building for manufacturers — the first question from anyone technical is usually the same one: if a working system already exists, why rebuild it instead of patching it?

The honest answer is that the system Zunavi is meant to eventually replace was never built to be patched into what manufacturers actually need today. It’s a case study in how technical debt accumulates quietly in manufacturing software until the cost of extending it exceeds the cost of starting over.

What the Old System Actually Looks Like

The legacy backend runs on MS SQL Server with TypeORM as the ORM layer. That alone isn’t unusual. What’s unusual — and what makes it hard to maintain — is that the physical column names are in Japanese: 図番 (drawing number), 来歴 (revision history / lineage), 材質 (material), and dozens more. The core draws table has grown to over 60 columns: tolerances, specific gravity, stamped numbers, seating allowances, eyebolt specs, hanging boss and mounting boss dimensions, product series — fields accumulated over years as new requirements got bolted onto a single wide table instead of being modeled as their own concepts.

Wide, ad-hoc tables aren’t automatically wrong. Plenty of production systems have them and work fine. The real problem shows up in how the system tracks which revision of a drawing is the current one.

The “Delete-All-Then-Reinsert” Pattern

The old schema keeps a separate table, latestDraws, that’s supposed to always contain exactly the newest revision of every drawing. It’s not derived by a query — it’s a manually maintained cache, synced by deleting every row in the table and reinserting the current set, wrapped in a transaction. On top of that sits Q_Draws, a SQL view that downstream code reads from, papering over the fact that the underlying “latest” data is a snapshot someone has to remember to rebuild correctly, every time, in the right order (revision DESC, registerDate DESC).

This pattern works until it doesn’t. It’s fragile under concurrent writes, it’s expensive at any real scale (rewriting an entire table to reflect one drawing’s new revision), and — most importantly — it puts the correctness of “what’s the current drawing” in the hands of application code remembering to run the sync step correctly, rather than in the data model itself. A system-of-record for engineering drawings that can silently drift out of sync with itself is not a small bug; it’s the kind of bug that erodes trust in the whole platform.

There’s also a small, telling artifact in the old schema: a dead column called func_history.mongoId, a leftover from an earlier era when part of the system ran on MongoDB before migrating to SQL Server. It doesn’t do anything anymore. It’s just there — a fossil record of a previous migration that also didn’t fully clean up after itself.

What We’re Building Instead

Zunavi’s new backend runs on PostgreSQL with Prisma as the ORM. That switch alone buys a declarative schema, real migrations, and generated types instead of TypeORM’s manual entity-to-table synchronization — but the schema change that actually matters is how we model revisions.

Instead of a draws table plus a separately-synced latestDraws cache, the new model separates concerns explicitly: a Drawing aggregate root (the drawing number itself) and a DrawingRevision child table (each version of that drawing). “Latest” isn’t a separate table anymore — it’s a boolean flag, DrawingRevision.isLatest, living directly on the revision row it describes.

When a drawing gets a new revision, the whole operation happens inside a single database transaction: the previous revision’s isLatest flips to false, and the new revision is inserted with isLatest = true. No delete-everything-and-reinsert step. No separate cache table that can silently disagree with the source of truth. A unique constraint — @@unique([drawingId, revision]) — makes it structurally impossible to insert a duplicate revision number for the same drawing, which the old system had no equivalent guard against.

If we ever need a read-optimized view for “give me the latest revision of every drawing,” that’s a straightforward PostgreSQL view (DISTINCT ON (drawing_id) ... ORDER BY revision DESC) — computed on read, not maintained by hand on every write.

The Rest of the Cleanup

The same philosophy extends past revisions. The old system’s five separate func_* tables for bookmarks, view history, notes, action memos, and change confirmations collapse into a single UserEvent table with a type discriminator. Drawing attachments and part attachments — previously two similar-but-separate entity hierarchies — become one Asset model, keyed by section, with the same soft-delete-on-replace behavior the old system used for PDFs. Parts and the old parts/latestParts split consolidate into a single Item model with usage tracking, rather than a second parallel “latest” table needing its own sync logic.

None of the old system’s 60-plus columns are getting summarily discarded, either — the ones with clear, high-frequency meaning (drawing number, part name, material, dimensions, assignee, status, revision) become proper typed columns on DrawingRevision; the long tail of low-frequency, variable fields moves into a DrawingRevision.extra JSONB column, where it can be queried and, later, promoted to a real column if it turns out to matter.

This Failure Mode Isn’t Unique to Us

None of this is a problem specific to one internal tool. It’s the standard shape technical debt takes in long-lived manufacturing software, and industry writeups on legacy modernization describe the same pattern from the outside. One estimate puts technical debt at 20–40% of a company’s total technology value — and moving a debt-heavy legacy system without cleaning it up just carries that debt forward, at the same ongoing cost (IT Convergence). Manufacturing-specific writeups describe the mechanism behind that number directly: aging ERP and database systems accumulate “many undocumented dependencies,” so changing the structure of a single table breaks processes that looked unrelated, and scripts written as temporary fixes quietly become permanent parts of daily operations for years (Softacom). SQL Server–to–PostgreSQL migration guides list the recurring friction point behind that as well: business logic buried in stored procedures and triggers, tied to SQL Server–specific behavior — T-SQL vs. PL/pgSQL, collations, exception handling — that automated translation tools rarely convert reliably for anything non-trivial (SourceFuse). The latestDraws delete-all-then-reinsert pattern and the Q_Draws view are exactly that kind of undocumented dependency — a fix that was reasonable in isolation, in a system nobody set out to leave brittle on purpose.

Why This Matters Beyond Zunavi

This isn’t a story about SQL Server being bad or PostgreSQL being good. It’s about what happens when “the current version of the truth” is implemented as a procedure instead of a constraint. The old system isn’t broken because someone wrote careless code — it’s the accumulated result of years of reasonable, incremental decisions that never got revisited. That’s exactly how technical debt behaves in long-lived manufacturing software: each individual patch looks fine in isolation, and the pile only becomes visible once you try to build something new on top of it.

Zunavi is still in active development and isn’t released yet — this is deliberately not a “look what we shipped” post. It’s a look at the specific engineering call we made and why, for anyone dealing with the same category of problem in their own systems.

---

Read more on how we’re approaching Zunavi: why “certain vs optional” feature flags matter for engineering data, why we’re building on-premise-first for semiconductor manufacturers, or the original introduction to Zunavi. Working through a similar migration, or want updates as Zunavi develops? Reach out at [email protected].