E1: Ingestion + Vector DB — Epic Design Doc#

Status: 🔄 In Refinement (Step 0) Authors: Dan Hannah & Clay Created: 2026-04-18 Parent: QuoteAI Project Design Doc

Overview#

Goals & Non-Goals#

Goals:

• Pull a curated subset of Brehob source docs from Google Drive
• Convert .doc / .xlsx / .pdf → clean structured data using Billet-shaped parser modules
• Extract structured fields for products, quotes, and line items (the atomic retrieval unit)
• Generate embeddings for products, quotes, and individual line-item descriptions
• Load everything into the pgvector schema created in E0
• Provide a reproducible ingestion CLI (pnpm ingest) so we can rebuild the DB from scratch

Non-Goals:

• No full-corpus ingestion — curated ~20 quotes + ~20 product specs for demo
• No real-time sync with Drive — one-shot ingestion triggered manually
• No admin UI — CLI only
• No vendor quote ingestion — manual upload deferred to Full MVP
• No pricing sheet ingestion (except as text reference — not for AI pricing, per design doc)

Problem Statement#

QuoteAI's core value is retrieval quality on John's 22-year description library. Without real data in the DB, the MCP tools (E2) have nothing to query and the UI (E3) has nothing to assemble. Worse: bad retrieval = bad demo = no customer. The ingestion pipeline IS the product's moat.

The data is messy (.doc files from 2008-2025, inconsistent spreadsheet layouts, varying quote structures), lives in Google Drive, and needs structured field extraction plus embedding generation before it's useful.

What Is This Epic?#

A reproducible ingestion pipeline that takes raw Drive documents and produces a populated pgvector database with:

• Product catalog — structured specs from .doc spec sheets
• Past quotes — text + embeddings for semantic search over full quotes
• Line items — individually embedded descriptions, the atomic retrieval unit per the design doc
• Vendor quotes — text + embeddings (reference material)

Parsers are Billet-shaped (per the main design doc's Billet/Anvil decision) so they can be extracted into Billet v0 later.

Context#

Dependents#

• E2 (MCP Servers) — every MCP tool queries data loaded by E1
• Demo experience — retrieval quality is the demo's single biggest make-or-break

Dependencies#

• E0 (Foundation) — schema must exist, DB must be runnable
• Google Drive MCP — already connected and working (confirmed in this session)
• OpenAI API key — for text-embedding-3-small (per design doc)

Current State#

Source data lives in QuoteAI - Brehob Quote History Google Drive folder (owner: johnhannah0624@gmail.com, shared with Dan). No local copy, no DB rows, no embeddings.

Affected Systems#

Design#

Module Structure (Billet-Shaped)#

ingestion/
├── download.ts              # Pull curated file list from Drive
├── parsers/                 # Format conversion ONLY (Billet-shaped)
│   ├── word.ts              # textutil / mammoth — .doc / .docx → clean text
│   ├── excel.ts             # SheetJS — .xlsx → markdown tables
│   ├── pdf.ts               # pdf-parse → text
│   └── types.ts             # shared ParsedDoc interface
├── extractor/               # Haiku-based structured extraction (QuoteAI-specific)
│   ├── extractor.ts         # invoke Haiku with text + schema, validate output
│   ├── schemas.ts           # Zod schemas for product / quote / line-item
│   ├── prompts.ts           # extraction prompts (versioned, hand-tuned)
│   └── evals.ts             # hand-labeled fixture runner
├── embed.ts                 # OpenAI text-embedding-3-small
├── load.ts                  # pgvector inserts with UPSERT
├── cli.ts                   # `pnpm ingest --subset=demo` entry point
└── fixtures/
    ├── demo-subset.json     # curated file IDs for demo
    └── eval-labeled/        # hand-labeled extraction targets

Separation of concerns (post-Haiku-extractor decision):

Why this shape: Parsers stay dumb and reusable — exactly what Billet's adapter pattern wants. The intelligence (knowing what a quote_line_item looks like, what a product row contains) lives in QuoteAI's extractor, where it belongs. When Billet ships, parsers/ extracts cleanly. The extractor stays here since it's schema-specific to QuoteAI.

Curated Subset Selection#

~20 quotes + ~20 product specs, covering:

Product specs (by manufacturer):

• Quincy: QMB30, QSI-100, QGV-100, QSV-430, QT-10, QOF-400
• Zeks: 500HSF, ZFC filters, refrigerated dryer
• Hankison: 505BC, refrigerated dryer
• Powerex: oilless scroll, vacuum
• Dekker: vacuum
• CRP: oil-water separator

Past quotes (by complexity tier):

• Simple (5): Single-product + price — e.g., Henry Ford refurbished dryer
• Standard (10): Multi-line system — e.g., Bowen Engineering 15hp duplex, Corwell Health Royal Oak, Purforms 50hp
• Complex (5): Multi-option, controls, installation — e.g., 4M Industries (our template reference), Slate Trucks 6-Turbo, Munson Hospital

Criteria: Coverage of complexity tiers, representative manufacturers, at least 2 multi-option quotes, the 4M reference for template extraction, John's language across ≥10 years.

Stored in ingestion/fixtures/demo-subset.json with Drive file IDs and metadata.

Key Algorithms / Logic#

Line-Item Description Extraction (Critical)#

Regex-based extraction of line-item blocks from 22-year-old .doc files is a losing game — formatting drift, multi-option variations, and inconsistent CHARACTERISTICS blocks all break brittle parsers. We use a Haiku agent with structured output instead.

Pipeline per document:

1. Parser converts .doc / .xlsx / .pdf → clean text (Billet-shaped)
2. Extractor invokes Haiku with a document-type-specific prompt + Zod input schema (via tool-use mode)
3. Haiku returns structured JSON matching the schema:
   • Product specs: manufacturer, series/model, cooling, pressure, CFM, HP, voltage, drive, dimensions, weight, noise_dba, raw description block
   • Past quotes: customer info, date, quote number, line items (nested), totals, scope of work (if present)
   • Line items: category (ROTARY SCREW AIR COMPRESSOR etc.), verbatim description block, qty, specs, unit price, delivery text
4. Zod validates the response; schema violations fail loud with the raw response logged
5. On validation failure: retry once with an error-repair prompt; if still failing, write to errors.log and move on (continue-on-error for demo)

Why Haiku specifically:

• Cheap: ~$0.01/doc × 40 docs = ~$0.40 for the full demo subset
• Structured output via tool-use is reliable for this pattern
• Handles format drift gracefully where regex would break
• Dan's Claude Max plan absorbs dev iteration cost

What gets embedded (critical):

• Embed verbatim line-item description blocks from the source — NOT Haiku's paraphrase
• John's exact phrasing is the moat. Haiku's job is to identify block boundaries and structured fields, not to rewrite descriptions
• Each quote_line_items.embedding captures the source language that will eventually be reused by Claude Code when assembling future drafts

Prompt versioning:

• Prompts live in extractor/prompts.ts with version numbers
• Every extraction stores extractor_version in the row metadata (add to products, past_quotes, quote_line_items if not already there — small migration)
• Enables re-extraction with a new prompt and A/B comparison against baseline

Embedding Strategy#

Batching: OpenAI allows ~2048 strings per request. Line items for 20 quotes ≈ 100-200 items total — one batch call.

Re-embed detection: Hash the source text; store hash in DB. On re-ingest, only re-embed changed hashes. (Not strictly needed for demo but cheap to add.)

Data Model Changes#

No schema changes — E0 created everything. E1 populates:

• products
• past_quotes
• quote_line_items
• vendor_quotes

Leaves untouched:

• quote_log, quote_log_items, feedback (Full MVP)

Edge Cases & Gotchas#

Testing Strategy#

Test Layers#

Required Fixtures#

Verification Rules#

1. Every parser has a fixture-driven unit test. No exceptions.
2. The golden test runs on every CI (once CI exists post-demo) — retrieval regression is the most important signal.
3. Line-item count spot check — after ingest, SELECT COUNT(*) FROM quote_line_items must be within expected range (~100-200 for demo subset).

Stories#

Known Issues / Tech Debt#

Open Questions#

All E1 open questions resolved as of 2026-04-18. See Decisions Log for the locked-in outcomes. Add new questions here as they emerge during implementation.

Risks#

Decisions Log#