---
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 `.construction/project.yaml` exists (AgentCM writes it; a `.construction/` folder alone only holds this plugin's own working data), read it for the project name/number to include in the workbook header. Otherwise take them from the bid documents or ask.

## 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 a finding
```

### Step 2: Read First Bid to Discover Structure

Open the first bid PDF to understand what data is available. Dump its text layer
first; the script marks each page TEXT or VISION (fewer than 50 characters):
```bash
"${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/pdf_text.py" "BID.pdf"
```
Read TEXT pages from the dump. Rasterize each VISION page (scanned bids show
VISION on every page) and read it with vision:
```bash
"${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py" "BID.pdf" 1 --dpi 200 --output ".construction/skills/bid-tabulator/pages/{bid_slug}_p1.png"
```
Page images are working data: keep them in `.construction/skills/bid-tabulator/pages/`, never elsewhere under `.construction/` (the rest of that folder is AgentCM's).
Do not write your own extraction code (no `python -c`, no helper script); the
two scripts above are the whole reading toolkit for this skill.

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

```yaml
# 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
addenda_acknowledged: []  # Addenda the bid acknowledges, as written ("Addendum 1"); [] if none
flags: []               # Extraction flags for the engineer: math discrepancies, unclear values, missing acknowledgements
```

The exporter reports every line whose qty × unit price is not its extended
price and every bid whose lines do not sum to its stated base bid, on the
console and on the workbook's Flags tab. Record the numbers as submitted and
let the exporter flag them; do not correct or re-derive them.

**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** with `pdf_text.py` (Step 2 command); read TEXT pages from the dump
2. **Quality gate**: rasterize every page the dump marks VISION and read it with vision
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/skills/bid-tabulator/bids/{company_name_slug}.json` with the Write tool, one file per bid (not through a script, and not with a shell heredoc: `cat > file <<'EOF'` fails here when the content holds an apostrophe)

**State persistence** — write `extraction_state.yaml` after each bid, every run, so an interrupted run resumes:
```yaml
# .construction/skills/bid-tabulator/extraction_state.yaml
scope: "Division 09 - Finishes"
total_bids: 12
processed: 5
current_bid: "Smith_Drywall.pdf"
errors: []
```

**Important:** Each bid's line items, descriptions, and amounts must be recorded exactly as submitted. Different bidders may describe the same work differently, use different units, or break out scope differently. This is expected and intentional — the engineer will reconcile after review.

### Step 4: Generate Comparison Excel

```bash
"${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_SKILL_DIR}/scripts/bid_comparison_to_xlsx.py" \
  --data .construction/skills/bid-tabulator/bids/ \
  --scope "Division 09 - Finishes" \
  --project "Project Name" \
  --output "Bid_Comparison_Div09.xlsx"
```

**Excel workbook structure:**

**Tab 1 — Comparison Summary:**
- Row per key metric: Base Bid, Alternates (each), Bond, Duration
- Column per bidder
- Highlight: lowest base bid (green), highest (red)
- Total row at bottom

**Tab 2 — Per-Bidder Detail Tabs** (one tab per bidder):
- Company info header
- Line items table: #, Spec Section, Description, Qty, Unit, Unit Price, Extended, Notes
- Alternates with descriptions
- Inclusions list
- Exclusions list
- Qualifications / conditions
- Any notes or special terms

**Tab 3 — Exclusions & Qualifications:**
- All bidders' exclusions and qualifications side-by-side
- This is critical for the engineer — scope gaps and risk items live here

**Tab 4 — Scope Gaps (flagged):**
- Items that appear in some bids but not others
- Items where one bidder explicitly excludes what others include
- NOT an attempt to normalize — just a flag: "Bidder A includes 'acoustical caulking', Bidders B and C do not mention it"
- The engineer decides whether this is a real gap or just different terminology

### Step 5: Present Summary to Engineer

After generating the Excel:

"I extracted data from [N] bids for [scope]. Here's the summary:

- **Lowest base bid:** [Company] at [amount]
- **Highest base bid:** [Company] at [amount]
- **Bid spread:** [amount range]
- **[X] scope gap flags** — items that appear in some bids but not others (see Scope Gaps tab)
- **[Y] qualifications/exclusions** across all bidders (see Exclusions tab)
- **[Z] bids** had unclear values that are flagged with [unclear] notes

The Excel file is at [path]. All line items are extracted as-submitted — you'll want to review the Flags tab (math discrepancies, unclear values) and the Scope Gaps tab, and contact subs to clarify any discrepancies before finalizing your comparison."

### Step 6: Write a Finding

Record the tabulation in `.construction/skills/bid-tabulator/findings/` (both modes):

```bash
"${CLAUDE_PLUGIN_ROOT}/bin/construction-python" "${CLAUDE_PLUGIN_ROOT}/scripts/graph/write_finding.py" \
  --skill "bid-tabulator" --type "bid_tabulation_complete" \
  --title "Bid comparison: {scope} — {N} bidders" \
  --output-file "Bid_Comparison_{scope_slug}.xlsx" \
  --data '{"scope": "Division 09", "bidder_count": 12, "low_bid": 450000, "high_bid": 680000}'
```

## Resumption

Check for `.construction/skills/bid-tabulator/extraction_state.yaml`. If `status: in_progress`, resume from the next unprocessed bid.

## Gotchas (measured in eval runs)

- Write JSON and YAML with the Write tool. `cat > file <<'EOF'` failed with "unexpected EOF while looking for matching `'`" whenever the content held an apostrophe (3 of 15 runs, 2026-10-04), costing a turn each time.
- Do not write helper scripts or inline `python -c`, even to assemble your own JSON or to read a PDF; the allowlisted scripts are the toolkit, and the exporters do the arithmetic checks. If the toolkit lacks something you need, say so in the handoff instead (bid-tabulator and subcontract-writer each wrote a helper script in their 2026-10-04 runs; the eval guard blocked both).

## Tips

- Bids over 10 pages likely contain detailed breakdowns — process all pages, not just the summary
- Some bids are submitted on the owner's bid form (standardized) while others are on the sub's letterhead (freeform) — handle both
- Watch for bids that exclude tax, bonds, or general conditions — these are common scope gaps
- If a bid references "per plans and specifications dated [date]" — note the date, as it may not match the current set
- Unit prices are REQUIRED for every line item that has quantity-based pricing. Extract from bid or calculate from extended ÷ qty. Preserve units exactly (SF, LF, EA, LS).
- Alternates may be add or deduct — preserve the sign as written

### File Safety
Never overwrite an existing bid comparison. If a file exists at the target location, save with a versioned name (e.g., `Bid_Comparison_Div09_v2.xlsx`).

---

## Allowed Scripts

- `${CLAUDE_PLUGIN_ROOT}/bin/construction-python`
- `${CLAUDE_PLUGIN_ROOT}/scripts/pdf/pdf_text.py`
- `${CLAUDE_PLUGIN_ROOT}/scripts/pdf/rasterize_page.py`
- `${CLAUDE_SKILL_DIR}/scripts/bid_comparison_to_xlsx.py`
- `${CLAUDE_PLUGIN_ROOT}/scripts/graph/write_finding.py`