PO Management System

Procurement at Creative Lighting ran on disconnected Excel workbooks — one file per product SKU with BOM lines, embedded part photos, and free-text specs — plus ad-hoc Word or PDF documents emailed to vendors when parts were needed. There was no shared parts catalog: the same LED driver might appear under slightly different names across products. Vendor POs had no revision history; when quantities changed mid-order, staff reassembled documents manually and lost track of which version a supplier had received. Product BOMs were useful for knowing what belonged in a fixture, but there was no clean path from BOM reference to a vendor-scoped PO with only the parts that supplier actually provides.

The client manufactures LED flood lights and related fixtures. Their engineering team already maintains product BOMs in a fixed .xlsx layout (model code in B3, display name in B2, line items from row 6 with optional images in columns H–J). Parts span category-specific specs — input voltage on drivers, CCT and CRI on chips, series/parallel on PCB boards — parsed from description text during import. Vendors are scoped: each supplier is assigned a subset of parts, and PO line pickers only show parts linked to the selected vendor. BOMs do not auto-generate POs; procurement staff deliberately choose vendor and quantities. The deployment is internal (no end-user auth layer) on Vercel with Neon Postgres and Vercel Blob for PDFs, BOM images, and large Excel staging uploads.

1. Catalog from Excel first — Parse the client's spreadsheet layout with xlsx, normalize part names for deduplication, extract structured specs from description text, and pull embedded images via JSZip from the .xlsx ZIP archive. Support bulk CLI import and browser upload with Blob multipart for files over 10 MB (up to 100 MB).
2. Reference BOMs, explicit POs — Products hold BOM lines for engineering visibility; vendor POs are created independently with vendor-scoped part pickers and quantity validation.
3. Immutable PO versions — Each line-item change snapshots a new vendor_po_versions row with its own line set and PDF. Unchanged saves are no-ops. POs remain editable — no delivery lock or status gate.
4. Branded supplier PDFs — @react-pdf/renderer generates Creative Lighting–headered documents with vendor contact block, sorted line table, formatted part specs, and thumbnails from BOM images when available.

Manual CRUD covers vendors, parts, products, vendor–part assignments, and BOM line editing for one-off changes outside Excel.

Web application — Next.js 16 App Router with React 19, Drizzle ORM on Neon Postgres, and shadcn/ui dashboard pages for vendors, parts, products, and vendor POs. Server Actions handle mutations; TanStack Table powers list views. The dashboard surfaces entity counts and recent PO activity.

Data model — Normalized procurement graph:

Excel import pipeline (lib/services/sku-import.ts) — parseSkuWorkbook reads B2/B3 metadata and row 6+ BOM lines; extractImagesFromXlsxBuffer maps cell anchors to side/front/bottom views; importParsedSku upserts product, parts, and BOM in a transaction. Staged uploads land in Vercel Blob via POST /api/sku-import/upload and POST /api/sku-import/stage to bypass Vercel's 4.5 MB function body limit.

PO versioning (lib/services/vendor-po.ts) — createVendorPo inserts PO + version 1 + lines in one transaction, then renders PDF. saveVendorPoVersion compares normalized line sets against the latest version; on diff, inserts the next version number and regenerates PDF. Validation enforces positive integer quantities, unique parts per PO, and vendor-part assignment membership.

PDF generation (lib/pdf/) — VendorPoDocument React-PDF template with company header, vendor block, and line table. Thumbnails resolve from product_parts image URLs (front → side → bottom priority). PDFs store to Vercel Blob (local filesystem fallback in dev) at vendor-pos/{versionId}.pdf.

Master data

• CRUD for vendors, parts, and products with category-aware spec suggestions (LED driver, chip, PCB, fastener, etc.)
• Vendor detail pages for assigning parts to suppliers
• Product detail pages for manual BOM add/edit/remove
• Excel import from Products list or product detail — single file or batch folder via bun run import:skus

Vendor PO workflow

• Create PO: select vendor, add lines from vendor-scoped parts with quantities → version 1 + PDF
• Edit PO: adjust lines on detail page → save creates new version only when lines change
• Version history panel with per-version PDF download via GET /api/pdf/[versionId]

Excel BOM import

• Expected layout: B2 display name, B3 model code, row 6+ item no / part name / description / quantity / remarks
• Embedded images in columns H–J extracted and uploaded per BOM row
• Duplicate model codes in a batch rejected; existing products updatable when allowExistingProduct is set
• Import summary reports created/updated/skipped rows and image upload stats

Quality and ops

• bun run smoke:vendor-po — end-to-end PO create, version, PDF, cleanup
• bun run lint && bun run typecheck && bun run build — Biome + TypeScript gate

Excel as source of truth — The client's BOM format is fixed but informal: typos in spec keys (weigth), merged semantics in description cells, and embedded images tied to cell anchors rather than row IDs. The parser normalizes part names, applies spec-key aliases, and maps rich-value image relationships through JSZip XML traversal.

Large file uploads on Vercel — BOM spreadsheets with many embedded photos exceed serverless body limits. Files over 10 MB use direct client-to-Blob multipart upload; staging API processes from Blob URL rather than inline multipart.

Vendor scope enforcement — PO lines must only include parts assigned to the selected vendor. Validation runs at save time against vendor_parts to prevent cross-supplier ordering mistakes.

PDF thumbnails without per-part image store — Part images live on product_parts BOM rows, not on parts directly. PDF generation joins through any BOM row referencing the part to pick a thumbnail.

BOM reference vs procurement — Engineering BOMs list every part in a fixture across all vendors; POs are vendor-specific subsets. Keeping BOMs reference-only avoids incorrect auto-PO generation while still aiding part identification during manual PO assembly.

No auth layer — Scoped as a single-tenant internal tool deployed on Vercel; access control relies on deployment URL obscurity and org network policy rather than application-level login.

A single Postgres schema models vendors, parts, products, and versioned POs with clear separation between BOM reference data and procurement snapshots. The Excel import path reuses the same parseSkuWorkbook / importParsedSku functions for CLI and UI, so bulk onboarding and incremental uploads behave identically.

PO versioning uses content-addressed comparison (sorted partId + quantity tuples) so spurious versions are never created. PDF generation is decoupled: each version row gets an independent render and Blob URL, preserving a complete audit trail for supplier disputes.

Part specs use category-driven parsing (led_driver, led_chip, pcb_board, etc.) with alias normalization so imported description text becomes structured JSONB usable in PDF output and catalog search. Image extraction handles both drawing-anchor and rich-value Excel image placements.

• Bulk catalog onboarding from existing Excel — Dozens of product BOMs import in one step instead of re-keying parts, quantities, and specs manually.
• Eliminate manual PO document assembly — Branded PDFs generate on every PO create and revision; staff send a download link instead of rebuilding Word tables.
• Full PO revision trail — Each quantity or line change retains a numbered version with its own PDF, replacing email threads with ambiguous "final_v3" attachments.
• Consolidated parts catalog — Normalized part names deduplicate drivers and chips referenced across multiple LED SKUs.
• Vendor-scoped ordering — Part pickers filtered by supplier assignment reduce wrong-vendor PO mistakes during fast procurement cycles.