Case Study: Building a Spreadsheet-First BOM Cleaning and Fast Quote Tool
Overview
BOM Price is a standalone tool connected to the Dannie PCB platform but designed as its own product: a quote desk that feels like a spreadsheet. Electronics teams already live in Excel and CSV files, but those files arrive with inconsistent headers, manufacturer aliases, distributor SKUs instead of real MPNs, missing quantities, DNP rows, duplicate RefDes, package mismatches, lifecycle risks, and procurement fields scattered across columns. Before a buyer can request quotes, the BOM has to be normalized, validated, compared against stock, and converted into a sourcing plan.
I built BOM Price to make that work visible and deterministic. Users drop a CSV/XLSX file, the browser detects structure in a Web Worker, normalizes rows with shared rules, projects issues into an editable virtualized table, and keeps procurement context such as board quantity, on-hand stock, MOQ, SPQ, price tiers, lead time, RoHS, lifecycle, and supplier alternatives next to the original BOM data. The public normalizer is stateless and read-only; saved drafts and Fast Quote are explicit authenticated bridges.
The tool deliberately avoids black-box behavior. It does not quote description-only rows, does not silently substitute alternates, does not create PLM parts, and does not hide uncertainty behind ML confidence scores. A correction either matches a known rule or the row is flagged for the user. For quote-ready rows, the Fast Quote flow submits a bounded job, streams supplier results through the backend, writes quote fields back by stable `_clientId`, and hydrates individual rows as results arrive. The result is a practical bridge between messy engineering spreadsheets and procurement-ready BOMs.
Challenges & Problem
BOM Price had to solve messy spreadsheet intake, electronics-specific validation, procurement math, and asynchronous supplier quoting while keeping strong boundaries from the main manufacturing platform:
The product could not require users to adopt a strict template first. Real BOMs arrive as CSV, XLSX, and legacy spreadsheet exports with arbitrary sheet names, header rows, duplicate columns, multilingual labels, combined fields, hidden DNP markers, vendor SKUs, and inconsistent units. The importer needed to detect usable structure, ask for confirmation only when necessary, and preserve enough original data for audit and export.
Solution
I designed BOM Price as a standalone tool workspace with a public normalizer, a deterministic rule registry, and an authenticated Fast Quote pipeline that writes supplier data back into the spreadsheet model:
Built the Astro resource page around the product language users understand: a quote desk that feels like a spreadsheet. The page explains upload, cleaning, quoting, rule boundaries, and procurement outputs, then hands selected BOM files to the tool through a one-time upload token and configurable BOM_PRICE_TOOL_URL. Basic cleaning is positioned as privacy-first and no-account, while saved drafts and quote progress are explicit authenticated actions.
Features
No-Account Public BOM Cleaning
Users can clean and validate BOMs without signing in. The public normalizer is stateless, payload-limited, rate-limited, and read-only. It can enrich rows from safe catalogue data, but it does not create parts, assemblies, order records, PLM issues, or persisted manufacturing data unless the user explicitly moves into an authenticated draft or quote flow.
Electronics-Specific Rule Library
BOM Price understands electronics domain signals: RefDes ranges, prefix classes, DNP phrases, distributor part numbers, manufacturer aliases, package footprints, lifecycle words, duplicate MPNs, missing or zero quantities, quantity-vs-RefDes mismatch, mixed RefDes classes, and description-vs-MPN mismatches. DNP rows suppress normal issues so intentional no-fit parts do not pollute review.
Procurement Context in the Table
The table goes beyond normalized part fields. It includes board quantity, on-hand stock, computed buy quantity, MOQ, SPQ, supplier, unit price, currency, stock, price tiers, lead time, RoHS, lifecycle, distributor PN, and alternative offers. Quote columns appear when useful, keeping cleaning and buying decisions in the same workspace.
Saved Draft Continuity
Authenticated users can save a normalizer session and return later with rows, analysis, hidden columns, preferences, quote metadata, and selected offers intact. Draft writes are owner-scoped, versioned, and `_clientId` based, preventing row-number drift from corrupting saved work.
Spreadsheet Upload with Structure Detection
The upload flow accepts CSV and XLSX, detects sheets and header rows, handles duplicate headers, maps multilingual column names to canonical fields, and opens a structure picker only when the file is ambiguous. Users can also try the demo BOM template, making the product understandable before they bring real data.
Live Fast Quote Write-Back
Fast Quote runs as a backend job, not a blocking browser request. Supplier results are written to the database first, SSE only notifies the frontend that a row changed, and the UI hydrates row-scoped quote data by `_clientId`. This keeps the database as the source of truth while still giving buyers early row-by-row progress.
Results
- Shipped a standalone BOM Price product inside the Dannie ecosystem, positioned separately from the core PCB manufacturing platform while reusing the same engineering standards and infrastructure.
- Built a public spreadsheet-first normalizer that turns messy CSV/XLSX files into validated BOM rows with deterministic cleaning, issue projection, and export-ready sourcing data.
- Implemented a Web Worker import pipeline with structure detection, header scoring, chunked parsing, stable `_clientId` row identity, and large-file safeguards for up to 10MB spreadsheets and 50k parsed rows.
- Created a shared electronics validation rule system covering 30+ row checks, manufacturer aliasing, RefDes expansion, DNP extraction, lifecycle warnings, package/category inference, duplicate detection, and distributor-SKU detection.
- Designed hard product and architecture boundaries: public analysis is stateless and read-only, saved drafts are JWT-owned, Fast Quote is authenticated and rate-limited, and no PLM or order records are created from the public tool.
- Built a virtualized editable quote desk with issue drawer, cleansing notices, search/focus, hidden columns, procurement context, quote staleness warnings, saved drafts, and clean/sourcing/audit export paths.
- Implemented an incremental Fast Quote backend using NestJS, PostgreSQL, Drizzle, a streaming quote API client, row-level DB write-back, SSE notifications, per-row hydration, watchdog recovery, and deterministic offer ranking.
- Connected engineering BOM cleanup to real procurement concerns: board quantity, stock already on hand, MOQ, SPQ, price tiers, lead time, supplier alternatives, currency, RoHS, lifecycle, and exact-match quote safety.
Conclusion
BOM Price is separate from the Dannie PCB Platform in product shape even though it belongs to the same manufacturing ecosystem. Dannie is the end-to-end PCB ordering platform; BOM Price is the focused spreadsheet tool that cleans, validates, quotes, and preserves sourcing decisions before a BOM becomes operational data.
The strongest architectural decision was drawing boundaries early. Public users can normalize and understand their spreadsheet without an account, but persistence and supplier quoting are explicit authenticated flows. The normalizer can enrich from safe catalogue reads, but it cannot mutate PLM. Fast Quote can stream progress, but the database remains the source of truth and `_clientId` remains the only row identity.
This project shows the kind of tooling manufacturing teams actually need: not another opaque upload form, but a deterministic workspace that respects spreadsheets, explains every correction, flags what needs a human decision, and turns procurement complexity into visible, editable rows.