quote-compare

Ingest messy supplier price data (CSV, Excel, unstructured email text), normalize it into Postgres, and compare quotes across suppliers for a bill of materials.

It's a small, purpose-built version of a real problem: components moving through spreadsheets, emails, and disconnected systems, and the query work of turning that into "who's cheapest, and what does mixing suppliers actually save."

Running it

npm install
docker compose up -d   # postgres
npm run db:migrate
npm run db:seed        # 14 suppliers, 500 parts, ~60k quotes, 8 BOMs
npm run dev

Copy .env.example to .env first. ANTHROPIC_API_KEY is optional — everything works without it except the email-extraction path, which degrades to a clear "disabled" message rather than a broken form.

npm test        # unit -> integration -> e2e, in that order
npm run lint    # prettier + eslint
npm run check   # svelte-check

Worth a look

• docs/query-plans.md — the four core queries (cheapest supplier per part, price history, BOM cost with partial coverage, best-split vs. single-supplier), with real EXPLAIN ANALYZE before/after. The index that mattered wasn't the obvious one — Postgres ignored a correctly-shaped index because of a NULLS FIRST vs NULLS LAST mismatch between the query and the index definition. Fixing that cut the three heaviest queries 35–45%.
• The ingestion pipeline (src/lib/server/ingest/) is one set of pure, unit-tested functions (column mapping, fuzzy part matching, unit/currency normalization) shared by both the spreadsheet upload path and the email-extraction path, rather than two parallel implementations.
• Email extraction (extractEmail.ts) calls Claude once per email with a strict Zod schema on the response — an unexpected field from the model fails validation instead of being silently accepted, and anything that doesn't parse goes to a review queue instead of the database.
• The .xlsx parser changed twice during the build: xlsx (has an unpatched high-severity CVE on npm), then exceljs (unmaintained since 2023), landing on read-excel-file — actively maintained and read-only, which is all this app ever needed. Neither earlier pick had actually broken; the maintenance and security story just didn't hold up once checked properly instead of assumed.

Stack

SvelteKit 5 (runes, form actions), Postgres via Drizzle, Vitest (unit + integration against a real Docker Postgres), Playwright for the ingestion flow end to end.

Left out

Unit conversion covers three families (each/mm/kg) rather than a general unit system. The mapping UI re-submits the uploaded file inline as base64 between its two steps rather than staging it server-side — fine at this scale, not how it'd work with large files. No auth: every supplier's data is visible to everyone, which is a real gap this project doesn't attempt to close.