E4: Pricing Pipeline — Epic Design Doc#

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

Overview#

What Is This Epic?#

Enhances the existing /quotes/new form rather than replacing it. Salesperson uploads their existing Brehob quote spreadsheet (the standardized QUOTE FORM .xls / .xlsx); the system parses it and pre-populates the form fields. Salesperson reviews, edits if needed, submits — existing draft-generation pipeline takes over from there. New output: profit + commission take-home surfaced live in the MetaRail.

Single source of truth: the salesperson's spreadsheet already contains everything — customer info, equipment specs, list price, multiplier, cost, markup, totals. No AI extraction required.

Why "populate the form" rather than "replace the form" (E4-D6 — "enhance don't change"): the existing form is Zod-validated, familiar to the salesperson, and provides a natural review / correction step. Upload is "import into the form," not a separate path. When / if we ever deprecate the spreadsheet entirely post-MVP, the form remains as the sole surface and nothing else changes. See E4-D6 in quoteai/decisions.md for full rationale.

Goals#

• Spreadsheet upload UI (new route or augmented /quotes/new)
• Deterministic .xls → form-data parser (known 23×16 layout; no NLP)
• Commission calc engine — markup × cost × commission_rate_per_category
• Per-user settings page with commission-rate matrix (user-entered — liability dodge)
• MetaRail pricing surface: base cost / markup / customer price / profit $ / commission $

Non-Goals#

• In-app editable spreadsheet grid (post-MVP; see Post-MVP Backlog)
• Multi-tab / multi-option quotes (separate post-MVP epic)
• AI-suggested pricing (violates "no AI pricing" rule)
• Vendor website scraping for live MSRP (future; requires separate equipment-pricing data model)

Problem Statement#

From John-Dad demo 2026-04-22: current form asks salesperson to retype data they've already entered in Excel. Spreadsheet is the single source of truth for every Brehob deal. Pulling directly removes friction and unlocks the commission take-home surface — the adoption hook for salespeople ("I make $X on this deal").

Context#

Current State#

• /quotes/new form with line-item repeater; manual entry.
• No commission surface.
• app/lib/cc/generate.ts takes form JSON → generates markdown draft.

Dependencies#

• E3 (UI) — existing form + draft machinery
• E2 (MCP servers) — unchanged
• No new infra

Affected Systems#

Design#

Spreadsheet Parser#

Confirmed layout (3 sample files inspected 2026-04-22):

• 1 sheet named QUOTE FORM, 23×16
• R0-R4: header grid (salesman, dates, customer, addresses, FOB, freight)
• R7: column headers — QTY | DESCRIPTION/PART NO. | | | CFM | PSI | HP | V/PH/HZ | COOLING(A/W) | LIST EA. | MULTIPLIER | COST EA. | EXTENDED COST | MARKUP | SELL PRICE EA. | EXTENDED SELL PRICE
• R8-R20: up to 13 line-item rows
• R21: TOTALS
• R22: notes / options

File formats accepted: .xls and .xlsx (both handled by the xlsx npm package).

Multi-tab files: parser returns Sheet[]. If >1 tab detected, show a dropdown above the parsed preview — Tabs: [Option A ▼] (3 detected) — defaulting to the first tab. Selection drives which tab populates the form. True multi-option comparison UI is post-MVP.

Strictness — hybrid (E4-D3): required cells are strict; everything else is lenient.

Required fields for generation:

Optional fields (missing → default or blank):

• Shipping address (R2C9) → "Same as billing"
• Email / fax (R1C8) → blank
• Line-item specs (CFM / PSI / HP / V/PH/HZ / COOLING) → blank in the form
• Notes / options (R22) → blank
• FOB / freight terms (R2C12, R3C12) → blank
• Quote validity days (R0C13) → 30 (Brehob standard)

Rejection UX: on required-field failure, surface the specific cell reference and rule — e.g., "Cell R0C4 (Customer) is empty — required" — not a generic "parse failed." Makes the salesperson's fix path obvious.

Dates: Excel stores dates as serial numbers (e.g., 38541 = Aug 25, 2005). Parser converts to ISO. For the quote's Generation date, use the upload timestamp, not the spreadsheet's "Today's Date" cell — the sheet may be days / weeks old at upload.

Implementation: xlsx npm package + typed schema + Zod validation. Parser returns a ParsedQuoteForm that maps directly onto the existing form input shape; form fields get pre-populated on upload, salesperson reviews / edits, then submits.

Commission Math#

Per line item:

• cost = list × multiplier × qty
• customer_price = cost × (1 + markup)
• profit = customer_price - cost
• commission = profit × commission_rate[category]

Quote total commission = sum of per-line. Surfaced in MetaRail.

Equipment Category Classification#

Commission rates vary by category (from transcript): compressors 15%, recips 6-9%, dryers/vacuums TBD.

Decided: Option B for MVP (E4-D2) — infer category from the spreadsheet multiplier. Brehob uses a deterministic multiplier per category (per the John-Dad conversation). Mapping is a pure lookup — no catalog dependency, consistent with how John thinks about pricing.

Multiplier table (to be sourced from John):

Codified as a constant in code once the canonical list is in hand.

Alternatives deferred:

• Option A — partNo catalog lookup via E2 MCP. Refinement path when catalog coverage improves.
• Option C — per-line user tag. Only as fallback for unknown multipliers (see open annotation on unknown-multiplier handling).

See E4-D2 in quoteai/decisions.md for full rationale.

Settings Page#

New /settings/commissions page. Per-user editable table:

Persistence: localStorage for MVP (E4-D1). Commission rates live in localStorage; no DB dependency until auth lands. Migration path when auth arrives: read from localStorage on first authenticated login, push to user record, clear localStorage. See E4-D1 in quoteai/decisions.md.

No defaults shipped — we don't know Brehob's full table; wrong defaults = liability. User populates at first use.

Export / Import rates (JSON backup for cross-device sync pre-auth) → deferred to post-MVP backlog — overkill for the demo.

MetaRail Pricing Surface#

Pricing card — new card on the right rail, below the existing MetaRail card (E4-D4). Pricing is its own concept (live computation + salesperson interaction), distinct from MetaRail's doc-context role (sources, contacts, dates). Deserves its own visual container.

Default state — summary:

─── Pricing ───
Base cost:      $[cost]
Markup:         [avg markup %]
Customer price: $[total]
Profit:         $[total]
Commission:     $[take-home]   ← the hook

Expanded state (click disclosure caret) — per-line breakdown table showing each line item's cost / markup / profit / commission. Gives salesperson visibility into which line is the high-margin one.

Live updates — the card re-computes the moment the salesperson adjusts markup via the existing blue-pill inline edit. "I see my commission go up as I bump the margin from 12% to 15%" is the core feedback loop for salesperson adoption. Hard requirement, not implementation nicety.

Corrections — inline edit (E4-D5): when the salesperson spots a wrong price, qty, or description post-upload, they edit inline via the MetaRail blue pills and line-item editor. Draft re-renders. No re-upload required. Divergence detection (flagging when the doc no longer matches the originally-uploaded spreadsheet) is deferred to post-MVP — it matters once Brehob's accounting uses these for reconciliation, which isn't a demo concern.

Data Model#

ALTER TABLE quote_drafts
    ADD COLUMN pricing_json JSONB;

Commission rates persist in localStorage for MVP (E4-D1) — no user_commission_rates table until auth lands. Future schema when auth arrives:

CREATE TABLE user_commission_rates (
    user_id UUID NOT NULL,
    category TEXT NOT NULL,
    rate_pct NUMERIC(5,4) NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (user_id, category)
);

API / Interface Changes#

Testing Strategy#

• Unit: parser against 10+ sample files from ingestion/cache/
• Integration: upload → parse → generate → MetaRail shows commission
• Visual: MetaRail pricing section dark-mode baseline

Stories (TBD)#

Risks#

Decisions Log#

New E4 decisions go here. Active ones live in quoteai/decisions.md until graduated.

E4 is the "real Brehob data + salesperson take-home" epic. If Andy's pitch lands, this is the first 30-day deliverable.