Bid Detail - Excel imports (estimate / vendor quotes)

Stage	Building
Status	Partial
Design status	Approved
Spec status	In review
Estimate	1w (M)
Confidence	Medium
Linear	PIN-228 ↗ PIN-855 ↗
Linear status	Todo
Cycle	C14
Design	Design ↗
Linear epic	Bid Intake & Pipeline
Module	Bids ↗

Route: seeded via Smart Import → surfaces in /bids/:bidId?tab=estimate

Scope

AI-driven import of estimate sheets and vendor-quote data from Excel/PDF bid documents, feeding line items and vendor prices directly into the Estimate Builder. This is not a standalone upload UI — it runs through the Smart Import pipeline (browse → stage → review → commit) and the estimateparser package. It handles Pinley's standard estimate workbook plus legacy/non-standard sheets, including workbooks where one estimate spans multiple tabs. (Source: PIN-228, PIN-855; Smart Import ruling 2026-08-15.)

Acceptance criteria

Happy path:

• The parser reads Pinley estimate .xlsx/.csv into sections, line items, vendor quotes, equipment, and takeoffs, and writes them into the estimate on commit.
• Multi-tab workbooks are handled two ways: Base→IFB→Buy tabs import as separate EstimateRevisions (Buy = active); section tabs of a single estimate (Equipment / Materials / Labor) merge into one estimate with section boundaries preserved.
• Vendor-quote documents (PDF/Excel) yield vendor name + amounts, matched against the vendor master, and land in the estimate's vendor columns.
• Extraction runs through the staged-review gate: the estimator confirms before anything enters the live estimate.
• EstimateSection.Subtotal and per-revision snapshot_contract are stamped so revision pickers and downstream proposals show real totals.

Negative / error:

• Unparseable documents fall back gracefully (filename-only hint, "needs review") rather than blocking the import.
• Oversized document batches respect the AI encoded-byte budget; skipped docs are named in the progress message, not silently dropped.

Validation rules:

• Currency stored as integer cents; dollarsToCents/centsToDollars on the boundaries.
• Re-import is idempotent only against a fresh estimate tree; re-parsing an existing estimate requires the full FK-ordered delete (see the re-parse path).
• No silent auto-population — every extracted value is reviewed before commit.

Walkthrough scenario(s)

Happy path.

1. A SharePoint bid folder syncs; Smart Import classifies an .xlsx as an estimate sheet.
2. On commit, ImportEstimateSheetData detects the tabs, classifies them via ai.ClassifyEstimateSheets, and imports each estimate tab as a revision (Buy active); Equipment/Takeoff are read once from the final tab.
3. Vendor-quote PDFs under /Vendor Quotes/ are parsed for vendor names via ExtractVendorNamesFromDocs; matched vendors get columns/quotes.
4. Estimator opens the estimate: sections, line items, vendor prices, and CSI codes are populated; revision picker shows real totals.

Exception.

1. A non-standard workbook (e.g., Stephen Gaynor / Hancock) has one estimate spread across labelled tabs.
2. It's flagged "multi-tab = one estimate"; the parser merges the tabs into a single estimate/revision instead of treating each tab as its own revision.
3. Anything the parser can't map is left for the estimator to fix in review; the batch still commits the rest.

Edge cases

• Banner-in-col-B / Vornado Accessories split / Accessories promotion — the parser's section-detection heuristics (sectionBanner, detectAlternateGroup, Accessories split) handle template quirks; new templates that break them route to manual review, not a crash.
• Vendor quotes with multiple unit rates — surfaced as per-line mappings for the estimator to select.
• Revision/rebid quotes — flagged "from original bid — re-confirm pricing" rather than silently overwriting.
• CSI resolution — imported scope AND sub-line items resolve cost codes via the cache-first, Claude-fallback two-step (GetClassifiedLineItemByDescription scoped by category, then estResolveCostCode); the zero-sentinel is never written when no API key is configured.

Flow

sequenceDiagram
    participant SP as SharePoint / upload
    participant SI as Smart Import (stage→review→commit)
    participant EP as estimateparser + ai.ClassifyEstimateSheets
    participant EB as Estimate Builder
    participant Est as Estimator
    SP->>SI: document classified (estimate sheet / vendor quote)
    Est->>SI: review staged batch, confirm
    SI->>EP: ImportEstimateSheetData (detect tabs, parse)
    EP->>EB: sections / line items / vendor quotes / equipment
    EP->>EB: stamp EstimateSection.Subtotal + snapshot_contract
    Note over EB: CSI codes resolved (cache → Claude)
    EB-->>Est: populated estimate + revision picker totals

Backend / API notes

• Package: apps/api/internal/estimateparser (GetSheetNames, ParseSheet, ParsedEstimate); apps/api/seeder/realdata_helpers.go ImportEstimateSheetData orchestrates multi-tab import and snapshot_contract/AreaSF stamping.
• AI helpers: ai.ClassifyEstimateSheets (tab classification, array-payload extractor), ai.ExtractEstimateHeaderFields (engineer, labor type), ai.ExtractVendorNamesFromDocs.
• Commit path: CommitBatchWithProgress starts a REPARSE_ESTIMATE ImportJob when it meets an estimate sheet; RerunEstimateSheetParsing re-parses async via ImportJob (drawer shows "Parsing estimate").
• Re-parse: full estimate-tree delete in FK order (vendor_quotes raw SQL → line_items Unscoped → sections Unscoped → revisions → estimates Unscoped) before re-import.

AI integration

• Prompt intent: classify workbook tabs (estimate vs Equipment/Takeoff/Cover), extract header fields, and extract vendor names from quote docs.
• Model / budget: Claude; document batches respect ai.MaxRequestEncodedBytes (~24 MB base64) to avoid 413s.
• Accuracy target: ~70–80% first pass, improving to ~85–90% as corrections accumulate (correction-example few-shot). Multi-tab / non-standard Excel needs targeted training (Conor + Marko).

External integration

• SharePoint / Smart Import: the only ingestion path — files arrive through the watched-folder browser and staged-import commit; there is no separate estimate-upload UI.

Conor's comments

This should be part of smart import, it just needs to categoize the quotes and assign value in estimate sheet. Needs trainig.

Open questions for Conor

• Resolved 2026-08-27 (call): folds into Smart Import (categorise quotes, assign value to the estimate sheet); multi-tab / non-standard Excel needs AI training (Conor + Marko). The Dropbox/GDrive vs SharePoint line was a spec-author note, not a Conor ask — dropped. Remaining: which specific legacy workbook shapes to prioritise for training.