What's new in round 17 LATEST

• Cost Build-up panel on Bid Strategy detail — every advertised pay item gets a coverage tag and a suggested unit price. Filter chips at the top jump between tiers; 5-column totals strip shows priced $ by tier vs engineer estimate.
• 5-tier coverage classification — green FDOT MA > amber Statewide > blue Market > purple FDOT sibling > red Gorman call. Most upcoming lettings now hit 95–100% coverage.
• Pay-item-composition project_type classifier — historical lettings are now classified by what's actually in their bid_tab_items (Bridge / Resurfacing / Drainage / ITS) rather than by the description text. Bridge category jumped from 16 → 34 lettings.
• Size-matched cluster cascade on Predicted Winning Bid — comp clusters now filter to lettings within 0.5×–2× of your bid before computing percentiles. Three-tier fallback (MA × sized → Statewide × sized → Statewide × all) with a data-quality badge.
• Bayesian renorm on Predicted Competitor Mix — top-5 bidder probabilities now sum to ~100% instead of each being independent.
• Auto-route after Upload Bid — drop the EBS xlsx, click Import, and the dashboard auto-opens the new project in Bid Review within 2 seconds.
• canonicalize_pay_item_code() — fixed the FDOT dash-only vs upcoming dash-dash format mismatch that was hiding utility-section pricing data.

Tab Map — what each section does

• Overview — KPIs across all bids: win rate, $ volume, benchmark coverage, vs-low spread.
• Projects — Every Gorman bid in one table. ✎ on each row opens an outcome editor that writes straight to Postgres.
• Benchmark Review — Every Gorman pay item vs FDOT Market Area average. Filter by project, division, search code or description. Market Area dropdown and LS-reference toggle at the top.
• Bid Review — Single-bid sign-off view. Pick a project → see verdict, Predicted Winning Bid range against historical comps (size-matched), FDOT exception flags, CBS division risk, competitor positioning. Inline edit Gorman $/u or Qty. Print / Save PDF and Export to Excel output meeting-ready artifacts.
• ◆ Bid Strategy NEW — Pre-letting decision support. 29 currently-open upcoming FDOT lettings ranked by Gorman fit. Click any row → meta grid + Cost Build-up (every advertised pay item priced by tier) + Predicted Competitor Mix + win-probability curve + Historical Cluster Comparables + strategy hint. Generate Strategy Memo button outputs a print-ready PDF.
• FDOT Pricing — Every FDOT pay item × 14 Market Areas + Statewide. Cells colored red/green by ±25% vs Statewide.
• Competitor Intel — Two views: Section A–C are Gorman head-to-head (only lettings Gorman bid). Section D — Market Pricing covers all 138 tracked bidders × every pay item × Market Area, including jobs Gorman didn't bid.
• Competitor Profiles — One-page dossier per bidder. Pick any of the 138 tracked bidders → win rate, total $ bid/won, market-area footprint, top-25 pay-item pricing tendencies (Hunter / Aggressive / Normal / Conceder / Sparse), full letting history with PDF links, Head-to-Head with Gorman.
• Gorman vs Gorman — Current bid vs Gorman's own past unit prices, by CBS division. Multi-select Historical Pool to slice. Export to Excel available.
• Upload Bid — Drag InEight EBS .xlsx → fill County / Letting Date / Project Type / Contract Days → click Import. Lands directly in Postgres; auto-routes to Bid Review with the new project pre-selected.
• Activity Log — Every INSERT/UPDATE/DELETE on projects, pay items, and bid tabs captured by Postgres triggers. Filterable by user / table / operation / time-range.
• 🔄 Pull from FDOT — Triggers the discovery scraper. Runs daily at 11:15 UTC automatically (~7:15 AM ET in summer). Manual button forces an immediate run.

Daily / weekly workflow

• Scanning upcoming opportunities: ◆ Bid Strategy tab → filter chips at top (project type, district) → click any letting in the table. Cost Build-up shows every advertised pay item priced at the best tier available — use the chip row to zoom into the wildcards Gorman needs to price by hand.
• When you have a new bid: Upload Bid tab → drop the InEight EBS xlsx → fill metadata → Import. The success card auto-opens Bid Review with your bid pre-selected within 2 seconds.
• Pre-letting bid review: Bid Review tab → walk through the Predicted Winning Bid verdict (size-matched cluster), FDOT exceptions, CBS division risk, competitor positioning. Tweak unit prices inline if needed. Print or Export to Excel for the meeting.
• After a letting closes: Projects tab → ✎ on the row → fill Low Bid, # Bidders, Outcome, Award Date → Save. No xlsx, no scripts.
• Weekly maintenance: nothing required. Both scrapers (post-letting bid tabs at 11:15 UTC and pre-letting BSN PDFs at 11:25 UTC) run automatically. Optionally click 🔄 Pull from FDOT mid-day to force an immediate refresh.

Cost Build-up coverage tiers NEW

Every pay item on Bid Strategy is tagged with a coverage tier showing where the suggested price came from. Tiers cascade from most-precise to least-precise:

• FDOT MA — Exact pay-item-code match in FDOT's Market Area moving averages for this project's MA. The strongest reference.
• Statewide — Exact code match in FDOT's Statewide rollup (fallback when no MA-specific row exists).
• Market — No FDOT data, but the same item has been bid on past FL lettings. Median across all past competitor bids (digit-normalized so 0285-709 / 0285709 / 0-285-709 all collapse).
• FDOT sibling — No exact match, but the same product family at a different spec dimension (e.g. 0455-15-16 36"-43" pile holes used to inform 0455-15-15 30"-34"). Median across siblings. Soft signal.
• Gorman call — No data anywhere. Truly project-specific. Gorman has to price from judgment / sub quotes.

The 6-column totals strip below the chips breaks down priced $ by tier and compares the sum to the engineer estimate. If Priced > engineer estimate the model is hot (engineer's number is lean); if < it's cool (engineer's number is rich).

Project-type classifier — how Bridge / Resurfacing / etc. is decided

Historical lettings are classified by pay-item composition, not by description text. A letting with $5M+ in section 0455 (foundation piles) is a Bridge job regardless of whether the description mentions a bridge.

• Bridge — ≥10% of low-bidder dollars in bridge-core sections (0450/0455/0457/0458/0460/0521-0524) OR ≥20% in broader 0400-0599 structures.
• Resurfacing/Paving — ≥40% in asphalt sections (0327/0334/0337/0339, plus 0285-0287 base).
• Signing/ITS/Lighting — ≥40% in 0635-0660 / 0700-0720 / 0780-0790.
• Drainage/Culvert — ≥15% in 0410-0445 (pipe culverts).
• Safety/Guardrail — ≥30% in 0536/0537/0544/0550.
• Otherwise — falls through to text-based derive_project_type() classifier on the description.

Distribution across 160 historical lettings: 52 Resurfacing · 38 Roadway/Other · 34 Bridge · 24 Signing/ITS/Lighting · 6 Drainage · 5 Interchange · 1 Intersection.

Predicted Winning Bid — size-matched cluster

On the Bid Review tab between the KPI cards and the FDOT exceptions table. Shows the distribution of past low bids in a comparable cluster, with the Gorman bid pinned in place.

Cluster cascade (always size-matched within 0.5×–2× of Gorman's bid, always excludes the project under review):

• Tier 1: (project_type × MA × size-matched) — best, used when ≥ 3 comps available.
• Tier 2: (project_type × Statewide × size-matched) — fallback when MA cluster is sparse.
• Tier 3: (project_type × Statewide × all sizes) — last resort, flagged in the source label as "sparse".

Verdict is outcome-aware. If Gorman WON the project: Won at fair price / Won at premium price / Won at high end of range / Won at aggressive price based on percentile rank. If the bid sits 1.5×+ above the cluster max, special verdict Won — above comp scale warns that the comp set isn't representative.

No ML. Pure percentile_cont over the size-matched cluster's low-bid distribution.

Predicted Competitor Mix — scoring

On Bid Strategy detail. For a given upcoming letting, surfaces the top 5 bidders most likely to show up, with bid probability + expected price band. Score formula: 0.35×geo + 0.30×type + 0.20×$ + 0.15×recency, then renormalized so top-5 prob_pct sums to ~100%.

• geo — share of bidder's past lettings in this MA.
• type — share of bidder's past lettings matching this project_type.
• $ — proximity of this letting's engineer estimate to the bidder's median historical bid (log-scale, clamped 0–1).
• recency — exponential decay with 6-month half-life since the bidder's last bid.

Eligible bidders need ≥ 3 historical lettings. Gorman is excluded from the predicted mix.

Reading the flags & colors (Bid Review / Benchmark Review)

Item-level FDOT flags:

• Review — Gorman's $/u is >15% above the FDOT Market Area average.
• In Range — Within ±15% of FDOT average.
• Aggressive — >15% below FDOT average. Sanity-check quantities and production rates.
• No FDOT Data — No Market Area benchmark for this item.

CBS division risk (Gorman vs Gorman tab):

• High — >20% above Gorman's historical average for that division
• Medium — 10–20% above
• Normal — Within ±10%
• Aggressive — >20% below historical

FDOT Pricing cell colors (per Market Area, vs Statewide):

• Red — MA price >25% above Statewide. That market pays a premium for this item.
• Green — MA price >25% below Statewide. Sharp pencils there.
• White — Within ±25% of Statewide (typical).

Competitor Profiles — stance labels

The Pay-Item Pricing Tendencies table on each bidder's profile classifies every pay item the bidder has touched.

• Hunter — Lowest bidder ≥ 50% of the time on this item.
• Aggressive — Avg unit price < 85% of market average.
• Normal — Within ±15-20% of market average.
• Conceder — Avg unit price > 120% of market average. They concede this item.
• Sparse — Only 1 historical observation. Don't read into it.

Pay-item tendency rows for the picked bidder are loaded lazily (~200-900 rows per bidder). Re-selecting a previously-viewed bidder is instant.

Header indicators

• Data status pill (top center) — Project / FDOT item / bidder counts and last refresh timestamp.
• LIVE pill (top right): green=subscribed · flashes on refresh · amber=signed out · red=channel error.
• Live Presence widget (next to LIVE) — Avatars for everyone signed in right now. Click → dropdown of names + locations. Click a teammate → snap to their tab and same selected project.
• ☀ / ☾ — Toggle light / dark theme; preference persists.
• 🔄 Pull from FDOT — Manually trigger the discovery scraper. Service-role JWT is server-side, button only POSTs your user JWT.

Automation — what runs without you touching anything

Three pg_cron jobs run nightly + one hourly. All write directly to Postgres; the dashboard picks up via Realtime within ~2 seconds of each insert.

• 11:15 UTC daily — pull-fdot-lettings-nightly — Walks FDOT's letting calendar, downloads new post-letting bid tab PDFs into Supabase Storage bid-tabs/, inserts stub bid_tab_lettings rows.
• 11:25 UTC daily — pull-fdot-prebid-nightly — Scrapes the LPI page for currently-advertised upcoming lettings, downloads each contract's BSN PDF into upcoming-bsn/ Storage, upserts upcoming_lettings stub rows.
• 11:30 UTC daily — upcoming-lettings-snapshot-daily — Transitions past-letting status flags, captures KPI snapshot for the sparklines.
• Every hour — refresh-dashboard-mvs — Refreshes all 15 materialized views (competitor rollups, benchmark engine, letting features, lowbid predictions, market prices, FDOT pricing canon, etc.).

An auto-archive trigger flips upcoming_lettings.status from 'open' → 'archived' the moment its bid tab gets imported via the post-letting flow. No manual cleanup.

Where data lives

Backed by Supabase Postgres. Every action — uploads, outcome edits, FDOT pulls — writes directly to the database. The dashboard reads back via materialized views refreshed hourly (round-16 resource fix).

Base tables:

• projects + pay_item_details — Gorman bids and line items. Editable by any tenant member.
• bid_tab_lettings (160) + bid_tab_items (87,977) — FDOT bid tab data. PDFs in Storage bid-tabs/.
• upcoming_lettings (29 open) + upcoming_letting_items (3,918) — Pre-letting opportunity data. BSN PDFs in Storage upcoming-bsn/.
• fdot_historical_costs (9,784) — FDOT Market Area moving averages. Refreshed monthly.
• bidders (138) — Auto-discovered from FDOT preliminary reports.

Materialized views (refresh hourly):

• Competitor rollups: mv_competitor_summary, mv_competitor_profile_summary, mv_competitor_payitem_tendency, mv_competitor_letting_history.
• Bid engine: mv_benchmark_engine, mv_cbs_benchmarks, mv_fdot_pivot.
• Gorman views: mv_gorman_letting_record, mv_gorman_head_to_head, mv_gorman_item_compare.
• Letting features + predictions: mv_letting_features, mv_lowbid_predictions.
• Cost Build-up pricing: mv_market_prices_by_code_digits, mv_fdot_prices_by_prefix, mv_fdot_prices_by_digit_prefix, mv_fdot_pricing_canon.

Multi-tenant isolation enforced by Postgres Row-Level Security. Each user only sees their tenant's data. The canonicalize_pay_item_code() IMMUTABLE function bridges the 1XXX dash-only ↔ dash-dash format mismatch at query time.

Keyboard shortcuts

• Ctrl+Z — Undo last inline edit on Bid Review. Stack holds 50 edits since last Save / Discard.
• Ctrl+Y or Ctrl+Shift+Z — Redo.
• Ctrl+P — Print. Bid Review and Bid Strategy both have custom print stylesheets for meeting handouts.
• Ctrl+F5 / Ctrl+Shift+R — Hard reload, bypassing cache. Use after platform updates.
• Enter in an inline-edit cell — Commits the value and moves focus out, triggering recompute.

Undo / redo only fire when Bid Review is active so they don't hijack browser text-undo elsewhere.

Persistence — what's remembered

• Active tab — The tab you were on when you refreshed gets restored.
• Theme choice — Light / dark persists across reloads.
• Bid Strategy filters — Project Type + District chip selections persist via localStorage.
• Cost Build-up filter — Resets to "All" when switching to a different upcoming letting; chip clicks within the same letting preserve the filter.
• Sign-in session — Valid for ~1 hour; auto-refreshes silently if active.
• Inline edits — Pending until you click Save Changes or Discard. Browser refresh while unsaved will lose them.

Admin: monthly FDOT averages refresh

1. FDOT publishes new Market Area moving-average reports each month at fdot.gov.
2. Re-download the 14 .xlsx files into fdot/market_area_moving_averages/excel/ overwriting the old ones.
3. Run on Windows: py process_market_area_files.py
4. Then: py import_to_supabase.py to push the new period to Postgres.
5. Materialized views refresh on the next hourly tick. Dashboard picks up via Realtime within 2 seconds of MV completion.

Postgres is the single source of truth — no more "refresh MasterExport". The canonicalize_pay_item_code() function handles format inconsistencies (e.g. 1050-42210 vs 1050-42-210) at query time, so the source data doesn't need normalization.