/bid-tabulator

name: bid-tabulator
description: >
  Extract data from subcontractor bid PDFs and produce a comparison
  spreadsheet. Feeds into /bid-evaluator. Triggers: 'tabulate bids',
  'bid comparison', 'compare bids', 'buyout analysis', 'bid tab'.
argument-hint: "<bid_folder_or_files>"
disable-model-invocation: true

Bid Tabulator

Processes multiple subcontractor bid PDFs for a scope of work and produces an Excel comparison workbook. Extracts each bid's data **as-submitted** — the engineer handles normalization and alignment after reviewing discrepancies and contacting subcontractors as needed.

RIGID Rules (non-negotiable)

1. **Complete extraction — zero tolerance on omission.** If a bid contains data that fits ANY field in the schema, extract it. When in doubt, extract it. Extra data can be filtered; missing data cannot be recovered. 2. **Every page of every bid.** Process ALL pages — do not stop at the first page or summary page. Bids over 10 pages contain detailed breakdowns, alternates, and qualifications that MUST be captured. 3. **Numeric verification.** After extracting each bid, verify: (a) line item amounts sum to the stated subtotal, (b) subtotal + alternates/adjustments = base bid total. Flag any mismatch as `[MATH ERROR: line items sum to $X, bid states $Y]`. 4. **Formulas in Excel, not hardcoded values.** Subtotals use SUM formulas. Reconciliation uses difference formulas. The Comparison Summary uses cross-sheet formula references to per-bidder tabs. Never paste calculated values — the engineer must be able to trace every number. 5. **Preserve original language verbatim.** Line item descriptions, exclusions, qualifications, and notes are extracted exactly as written. No paraphrasing, normalization, or cleanup.

Step 1: Gather Inputs

Ask the user for: 1. **Bid PDFs** — folder path or list of individual files 2. **Scope description** — what trade/division is being bought out (e.g., "Division 09 - Finishes", "Structural Steel") 3. **Any specific data points** the user wants extracted beyond the defaults

If project context is available (`.construction/` directory), read `project.yaml` for project name/number to include in the workbook header.

Pipeline Position

THIS SKILL → /bid-evaluator → user confirms → /subcontract-writer

This skill is the entry point of the bid pipeline. It produces the tabulated data that `/bid-evaluator` consumes for analysis.

Workflow

Bid Tabulation Progress:
- [ ] Step 1: Gather inputs (bid PDFs, scope)
- [ ] Step 2: Read first bid to discover structure
- [ ] Step 3: Process all bids
- [ ] Step 4: Generate comparison Excel
- [ ] Step 5: Present summary to engineer
- [ ] Step 6: Write graph entry

Step 2: Read First Bid to Discover Structure

Open the first bid PDF to understand what data is available:

Try pdfplumber first. If text extraction returns meaningful content (>50 chars per page), use text mode. Otherwise fall back to vision.

**Vision fallback** for scanned bids:

${CLAUDE_SKILL_DIR}/../../bin/construction-python ${CLAUDE_SKILL_DIR}/../../scripts/pdf/rasterize_page.py BID.pdf 1 --dpi 200 --output bid_page.png

From the first bid, identify what data fields are present. Common bid data:

# Bidder info
company_name: ""
contact_name: ""
contact_phone: ""
contact_email: ""
bid_date: ""
bid_validity_period: ""

# Financial
base_bid_amount: ""
line_items:             # Every line item as an object:
  - spec_section: ""    # CSI section if shown (e.g., "09 65 19")
    description: ""     # Original description verbatim
    qty: null           # Quantity (numeric or null if lump sum)
    unit: ""            # Unit as written (SF, LF, EA, LS, etc.)
    unit_price: null    # Per-unit cost (numeric — extract or calculate)
    extended_price: null # Line total (qty × unit_price, or lump sum amount)
    notes: ""           # Flags, clarifications
alternates: []          # Alternate pricing: [{name, description, amount}]
allowances: []          # Allowances included

# Terms
scope_inclusions: []    # What the bid explicitly includes
scope_exclusions: []    # What the bid explicitly excludes
qualifications: []      # Conditions, assumptions, caveats
schedule_duration: ""   # Proposed duration if stated
payment_terms: ""       # Net 30, etc.
bond_included: false    # Whether bid/performance bond is included
insurance_confirmed: false

**Line item extraction rules:**

• Every line item MUST be an object with the fields above — never a bare string.
• If the bid shows qty, unit, and unit_price explicitly → extract all three.
• If the bid shows only extended_price and qty → calculate: `unit_price = extended_price / qty`.
• If the bid shows only a lump sum amount → set `qty: null`, `unit: "LS"`, `unit_price: null`, `extended_price: <amount>`.
• `spec_section` may not always be present — extract it if the bid references CSI section numbers.

**Present the discovered structure to the user** before processing remaining bids: "I found these data fields in the first bid: [list]. Should I extract all of these, or add/remove any?"

Step 3: Process All Bids

Process each bid PDF individually. For each bid:

1. **Extract text** via pdfplumber (preferred) or vision (fallback) 2. **Quality gate**: If pdfplumber returns less than 100 characters per page, switch to vision for that bid 3. **Extract all identified fields** from the bid document 4. **Preserve original language** — do NOT paraphrase, normalize, or reformat line item descriptions. Extract them exactly as written in the bid. 5. **Flag ambiguities** — if a value is unclear or could be interpreted multiple ways, include it with a note in brackets: `[unclear: possibly $45,000 or $45/SF]` 6. **Save per-bid JSON** to `.construction/bid_tab/bids/{company_name_slug}.json`

**State persistence** — write progress after each bid:

# .construction/bid_tab/extraction_state.yaml
scope: "Division 09 - Finishes"
total_bids: 12
processed: 5
current_bid: "Smith_