Sourcing Gap Dashboard — Data Architecture & Computation Guide

Package: packages/sourcing/ (@efi/sourcing) Server: http://localhost:3002 (start with npm run sourcing) Last updated: March 2026

Overview

The Supply-Demand Gap Dashboard shows a product x month matrix answering: for each EFI product and upcoming month, how much do we need, how much have we sourced, what's on hand, and what's the gap?

Data comes from two systems — Airtable CRM (demand + inventory) and PostgreSQL ERP (supply + shipping) — with no shared IDs between them. Product names are normalized across systems via substring matching against the PG products table.

The dashboard includes:

• Three-state cell coloring — red/yellow for unplanned gaps, light blue for planned gaps, green for covered
• Warehouse gap indicators — product-level cells reveal hidden per-warehouse shortfalls
• Clickable gap cells — navigate directly to AI recommendations for that product/month/warehouse
• Forecast confidence override — what-if scenarios (0% to 100%) for forecast reliability
• Category breakdown — Sourcing/Conversion/Spot demand from Inventory Availability
• AI recommendations — Claude-generated purchasing suggestions with supplier, price, and route specifics

Architecture

The Gap Formula

For each product x month cell:

totalDemand  = Total Sales Orders (C) + Total Unsold Adjusted (F)
totalSupply  = Total Expected Inventory (Beginning Inv + Arrived + Expected)
gap          = totalDemand - totalSupply       (positive = shortfall)
coverage     = (totalSupply / totalDemand) x 100
unplannedGap = max(0, gap - plannedSupply)     (what's neither ordered nor planned)

Where:

• C (Total Sales Orders) = Sourcing + Conversion + Spot from Inventory Availability (contractsByCategory)
• F (Total Unsold Adjusted) = Unsold Sourcing + Unsold Conversion + Unsold Spot, each × confidence factor (unsoldAdjustedByCategory)
• Total Expected Inventory = Beginning Inventory (chained) + Actual Arrivals + Expected Arrivals

Balance Chain

Each month's Final Balance carries forward to become the next month's Beginning Inventory:

Month 1 (March):  beginningInventory = raw IA "Beginning Inventory (Rollover or Hard Count)"
Month 2 (April):  beginningInventory = Month 1 finalBalance
Month 3 (May):    beginningInventory = Month 2 finalBalance
...

Ending Balance = Beginning Inv + Arrivals − Total Current Forecast − Allocations − Customer Holds

• Total Current Forecast = all Sales Orders + all Unsold (raw, not confidence-adjusted)
• Allocations = General Pool + Strategic + Liquidation

Final Balance = max(0, Ending Balance + Approved Demand Plan + Forced Inventory Adjustment)

• Floored at 0 — negative balances don't carry forward

Implied Inventory Adjustment (display only) = Ending Balance + Approved Demand Plan

---

Data Sources — Demand Side

1. Committed Demand (Contracts)

Fetcher	fetchers/demand-contracts.ts
Source	Airtable — Contract Line Items table
Fields	Product (Lookup), Sales Month (Calculated), Qty (ST), Customer Copy, Contract Type, Warehouse Location
Filter	Qty (ST) > 0 AND month matches target range
Units	Short Tons (ST)
Aggregation	Sum by product x month
What it captures	Forward, Conversion, and Spot contracted tons that customers have committed to buy

Notes:

• Product (Lookup) is a formula field returning the product name as text (not a linked record ID)
• Sales Month (Calculated) returns labels like "March 2026"
• Customer Copy is used for per-customer filtering
• Warehouse Location identifies the destination warehouse (e.g. "HOU", "SAV")
• Also returns distinctCustomers list for the dropdown filter

2. Open Orders (Unfulfilled Sales Orders)

Fetcher	fetchers/demand-open-orders.ts
Source	Airtable — Sales Orders table
Fields	Product Name, Month (Calculated), Qty Fulfilled, Customer, Warehouse (Sales Order Fulfillment), Packaging
Filter	Open/Invoiced = "Open" AND Qty Fulfilled > 0 AND month matches
Units	Short Tons (ST)
Aggregation	Sum by product x month
What it captures	Orders placed by customers that haven't been invoiced/shipped yet

Notes:

• Product Name is a singleLineText field (not a linked record)
• Month (Calculated) returns labels like "March 2026"
• Despite the field name Qty Fulfilled, this represents the order quantity on open orders
• Warehouse (Sales Order Fulfillment) is a lookup field (multipleLookupValues) identifying the destination warehouse

3. Forecast Remaining

Fetcher	fetchers/demand-forecast.ts
Source	Airtable — Sales Rep Forecasts table
Fields	Product (Lookup), Month (Lookup), Total Forecast Remaining, Customer (Lookup), Sales Rep (Lookup), WH
Filter	Total Forecast Remaining > 0 AND month matches AND Sales Representatives != "Inventory Adjustment"
Units	Short Tons (ST)
Aggregation	Sum by product x month (across all reps, ports, packaging)
What it captures	Forecasted demand that hasn't yet been contracted or ordered — the unfulfilled portion of each sales rep's forecast

Notes:

• Uses Total Forecast Remaining (not Current Forecast) to avoid double-counting with contracts and open orders. This field = forecast - qty_sold per window.
• Product (Lookup) is a multipleLookupValues field — may return an array, handled by normalizeProductName()
• Month (Lookup) is a multipleLookupValues field returning "March 2026" format
• Customer (Lookup) is a rollup returning the customer name as text
• WH is a linked record field returning record IDs — resolved to warehouse codes via resolveWarehouseId() from warehouse-metadata.ts
• "Inventory Adjustment" records are excluded (not real demand)
• "General Pool" records ARE included (unallocated but real forecast demand)
• Each entry includes a confidencePercent (from rep's Confidence Rating field) and adjustedTons (confidence-weighted). The confidence override feature replaces these per-rep values with a uniform percentage.

Sales Rep Forecasts table structure: Each record is keyed by: Sales Rep x Customer x Product x Month x Port x Packaging x Warehouse. Forecasts are broken into three sourcing windows:

• Forward (B2B): Approved FC (B2B) — contracted ahead, 78-day cutoff
• Conversion: Approved FC (Conversion) — from existing inventory, 17-day cutoff
• Spot: Approved FC (Spot) — immediate/short-term

The Current Forecast formula fields are time-gated — they progressively lock to actual sold quantities as the delivery month approaches, preventing inflation after the window closes.

4. Category Breakdown (Inventory Availability)

Fetcher	fetchers/demand-categories-ia.ts
Source	Airtable — Inventory Availability table (ID: tblYKi4hq39W0TXiN)
Name format	"MagnaFat (Tote) (BAL) (February 2026)"
What it captures	Breakdown of demand into Sourcing/Conversion/Spot categories, plus slippage, amendments, cross fulfillment, and unsold forecast remaining

Fields queried per record:

Display Row	IA Field	Notes
Sales Orders: Sourcing	Qty Contracted (Forward)
Sales Orders: Conversion	Qty Contracted (Conversion)
Sales Orders: Spot	Current Forecast (Spot) - Current Forecast Remaining (Spot)	Must subtract remaining!
Contract Slippage	Total Amount Slipped	Single total, no per-category
Contract Amendments	-Qty Amended (Sourcing)	Negated for display
Cross Fulfillment	-Qty Shipped (Contracts Fulfilled In Earlier Months)	Negated for display
Unsold Sourcing	Current Forecast Remaining (Forward)
Unsold Conversion	Current Forecast Remaining (Conversion)
Unsold Spot	Current Forecast Remaining (Spot)

Additional fields from IA (same query):

Display Row	IA Field	Notes
General Pool	General Pool (OH) (Published)
Strategic	Strategic Qty
Liquidation	Qty Liquidation
Forced Inv. Adjustment	Forced Inventory Adjustment
Temporary Inv. Hold	Inventory Hold (ST)
Customer Inv. Hold	Customer Inventory Hold
Ending Balance	Current Ending Balance
Approved Demand Plan	Current Locked Demand Plan
Beginning Inventory	Beginning Inventory (Rollover or Hard Count)
Actual Arrivals	Actual Arrivals
Expected Arrivals	Expected Arrivals

Important: The category breakdown comes from the IA table, NOT from summing Contract Line Items. This matches the source Airtable Interface.

---

Data Sources — Supply Side

5. Supplier Purchase Orders

Fetcher	fetchers/supply-purchase-orders.ts
Source	PostgreSQL — supplier_purchase_orders table
Joined tables	products (product name), ref_sales_months (year/month resolution), ref_efi_ports (destination port name)
Key columns	metric_tons, price_per_mton, product_id, efi_sales_month_id, meta_created, dest_port_id
Filter	meta_deleted = 0 AND efi_sales_month_id matches target months
Units	Metric Tons (MT) — note: ~10% larger than Short Tons
Aggregation	SUM(metric_tons), COUNT(*), weighted avg FOB per product x month x port
What it captures	Purchase orders placed to SE Asia suppliers, grouped by which sales month the product is destined for

SQL query:

SELECT
  p.product_name,
  rsm.year,
  rsm.month,
  ep.port_name,
  SUM(spo.metric_tons) AS total_tons,
  COUNT(*)::int AS po_count,
  SUM(spo.price_per_mton * spo.metric_tons) / NULLIF(SUM(spo.metric_tons), 0) AS avg_fob,
  MAX(spo.meta_created)::text AS latest_po_date
FROM supplier_purchase_orders spo
JOIN products p ON p.id = spo.product_id
JOIN ref_sales_months rsm ON rsm.id = spo.efi_sales_month_id
LEFT JOIN ref_efi_ports ep ON ep.id = spo.dest_port_id
WHERE spo.meta_deleted = 0
  AND spo.efi_sales_month_id IN ($1, $2, ...)
GROUP BY p.product_name, rsm.year, rsm.month, ep.port_name

Two-step resolution:

1. First query ref_sales_months to resolve target months (year + month number) → sales month IDs
2. Then query supplier_purchase_orders using those IDs

What this provides per row (one per product x month x port):

• sourcedPOs: total metric tons ordered from suppliers
• poCount: number of POs placed
• avgFobPrice: weighted average FOB price per metric ton ($/MT)
• destPort: destination port name (e.g. "Houston", "Savannah") or null

Multiple rows per product x month are accumulated in the gap calculator, with weighted-average FOB across ports. Warehouse filtering uses resolvePortToWarehouse() to map port names to warehouse codes.

6. Sourcing Plans

Fetcher	fetchers/supply-sourcing-plans.ts
Source	Airtable — Sourcing Plans table
What it captures	Planned but not yet ordered supply, broken into per-supplier segments with target FOB, transit days, estimated arrival, and destination port

Each plan has multiple segments (one per supplier allocation). Segments are resolved to warehouses via resolvePortToWarehouse() on the destination port.

7. Inventory On-Hand

Fetcher	fetchers/inventory.ts
Source	Airtable — Inventory Availability table
Fields	Name, Total Inventory For Month
Filter	FIND("March 2026", Name) AND Total Inventory For Month > 0
Units	Short Tons (ST)
Aggregation	Sum by product x warehouse x month
What it captures	Available warehouse inventory per product and warehouse for each month

Name field format: "MagnaPalm (Bags) (BAL) (March 2026)"

• Product name extracted as text before first (
• Warehouse code extracted from parenthesized segments (e.g. "(BAL)" → "BAL")
• Aggregation key is product::warehouse — inventory is warehouse-specific, not pooled
• Queried per month (one Airtable call per month in the range)

---

Data Sources — Shipping & Deadlines

8. Transit Times → Order-By Deadlines

Fetcher	fetchers/supply-transit.ts
Source	PostgreSQL — ref_otw_transit_times table
Filter	meta_deleted = 0
What it computes	"Order By" deadline = last date to place a supplier PO for each target month

Calculation:

avg_transit_days = AVG(transit_time) across all SE Asia → US routes
total_lead_days  = avg_transit_days + 14 (port buffer)
order_by_date    = 1st_of_delivery_month - total_lead_days

Deadline status:

• ok: deadline > 14 days from now
• warning: deadline within 14 days
• overdue: deadline has passed

Current simplification: Uses a single average across all 84 routes. Cached for server lifetime.

Shipping tables available in PostgreSQL (not all currently used)

Table	Rows	Used?	Description
ref_otw_transit_times	84	Yes	Transit days per origin → destination port pair
ref_ports_of_lading	13	No	SE Asia origin ports (could be used for per-route deadlines)
ref_efi_ports	11	Yes	US destination ports (joined in supply PO query for warehouse mapping)
ref_landed_cost_estimates	1,564	No	Ocean freight contract/spot rates by port pair + month
ref_shipment_windows	23,448	No	Half-month shipping windows with start/end dates
supplier_purchase_orders.port_of_lading_id	—	No	Actual origin port per PO
supplier_purchase_orders.dest_port_id	—	Yes	Actual destination port per PO (joined to ref_efi_ports for warehouse mapping)
supplier_purchase_orders.shipping_window_id	—	No	Actual shipping window assigned to PO

---

Product Normalization

Both Airtable and Postgres use product names with no shared ID. The models/product-map.ts module bridges them:

1. Load canonical products from PG products table (36 products, cached for server lifetime)
2. Exact match (case-insensitive): "MagnaPalm" → "MagnaPalm"
3. Known aliases: "magnapalm (drum)" → "MagnaPalm" (packaging variants)
4. Substring match: "MagnaPalm 85 Bags" contains "MagnaPalm" → match
5. No match: returns raw name, logs warning

Dependency: If PostgreSQL is unavailable when the server starts, the product cache is empty and normalization falls back to raw names. This causes mismatches between Airtable sources (different naming conventions per table). Always have the SSH tunnel running when starting the server.

---

Customer Filtering

Default: Company-wide — all customers' demand aggregated against total supply.

Per-customer: Add ?customer=Name to filter demand to a single customer.

• All fetchers pull full data (unfiltered)
• gap-calculator.ts filters demand entries by customer (case-insensitive) before building the matrix
• Supply stays company-wide — supplier POs are not customer-specific (but can be warehouse-filtered)
• Inventory stays company-wide — warehouse stock serves all customers (but can be warehouse-filtered)
• Customer dropdown populated from distinct names in Contract Line Items
• buildGapReport(customer, warehouse, packaging, monthCount, confidenceOverride) — accepts all filter dimensions

---

Warehouse Filtering

Default: All warehouses — demand, supply, and inventory aggregated across all locations.

Per-warehouse: Add ?warehouse=BAL to filter to a single warehouse.

• Demand fetchers include a warehouse field on each entry (from Airtable fields or resolved via warehouse-metadata.ts)
• gap-calculator.ts filters demand entries by warehouse (exact match) after customer filtering (same pattern)
• Supply filtered by port mapping — supplier POs have a destPort (e.g. "Houston"), mapped to warehouse codes via resolvePortToWarehouse():
  • Houston → HOU, Savannah → SAV, Baltimore → BAL, Oakland → OAK, Tacoma → TAC
• Inventory filtered by warehouse — parsed from the Name field (e.g. "(BAL)")
• Warehouse dropdown populated from Airtable Warehouses table via warehouse-metadata.ts

Combined filters: ?customer=Name&warehouse=BAL applies both filters (customer first, then warehouse).

Warehouse Metadata Fetcher (fetchers/warehouse-metadata.ts)

Source	Airtable — Warehouses table
Fields	Name, Name (Select), Warehouse State, Capacity (in MT)
Cache	Server lifetime (same pattern as product-map.ts)
Exports	getWarehouses(), resolveWarehouseId(recordId), resolvePortToWarehouse(portName)

resolveWarehouseId() takes an Airtable record ID (from linked record fields like WH on forecasts) and returns the warehouse code. resolvePortToWarehouse() maps destination port names from PG to warehouse codes.

Warehouse Breakdown (services/warehouse-breakdown.ts)

Splits each product-level row into per-warehouse sub-rows. Groups raw fetcher outputs by product × month × warehouse and builds GapCell objects for each triple. Returns WarehouseBreakdown = Map<productName, WarehouseSubRow[]>.

Each sub-row has cells parallel to the months array, containing the same fields as product-level cells. Used to render expandable warehouse sub-rows in the gap matrix.

Warehouse Actions (services/warehouse-actions.ts)

Builds per-warehouse gap actions from raw fetcher outputs. For each product × month × warehouse triple with a positive gap, creates a SourcingAction with demand/supply breakdown.

Returns WarehouseActionsResult:

• actionRequired: gaps with unplannedGap > 0 — sent to AI for recommendations
• fullyCovered: gaps with unplannedGap === 0 — displayed for verification only

Accepts optional filterProduct and filterMonth params for cell-specific recommendation filtering.

---

Forecast Confidence Override

Users can run "what-if" scenarios to see how gaps change if forecast is more or less reliable.

UI

Chip-style filter in the gap matrix filter bar:

• Historical (default) — uses per-rep confidence from Airtable
• 100% — treat all forecast as fully reliable
• 80% / 50% / 20% — progressive discount
• 0% (contracts only) — zero out all forecast, show only firm demand

URL param: ?confidence=historical|100|80|50|20|0

Implementation

• gap-calculator.ts accepts confidenceOverride: number | null. When provided, applies confFactor = confidenceOverride / 100 to unsold category values: unsoldAdjustedByCategory = unsoldByCategory × confFactor.
• warehouse-breakdown.ts applies the same override to unsold values in warehouse-level aggregation.
• The override flows through to unsoldAdjustedByCategory → totalDemand (via F component) → gap → coveragePercent — all recalculated by existing derived-field logic.
• At 0%, only Total Sales Orders (C) counts as demand — all unsold forecast (F) is zeroed out.
• Cache key includes the confidence value so each scenario is cached independently.

---

Three-State Cell Coloring

The gap matrix uses three visual states to distinguish between gaps needing action vs gaps already addressed:

Color	Condition	Meaning
Red (#fed7d7)	coveragePercent < 50 AND unplannedGap > 0	Severe shortfall needing action
Yellow (#fefcbf)	coveragePercent 50-99% AND unplannedGap > 0	Partial coverage needing action
Light blue (#e6f6ff)	gap > 0 AND unplannedGap === 0	Gap covered by sourcing plans
Green (#c6f6d5)	coveragePercent >= 100	Fully covered
Gray	No demand or no activity	Inactive

Warehouse Gap Indicators

When a product-level cell shows green (net covered across all warehouses) but individual warehouses have gaps, the WarehouseGapSummary detects this:

interface WarehouseGapSummary {
  worstCoverage: number;       // Lowest coverage across WHs with demand
  gapCount: number;            // Warehouses with gap > 0
  grossGap: number;            // Sum of positive warehouse gaps
  unplannedGapCount: number;   // Warehouses with unplannedGap > 0
  grossUnplannedGap: number;   // Sum of unplanned gaps only
}

• If unplannedGapCount > 0: cell escalates to yellow/red + shows "N WH gaps (+X)" badge
• If all gaps are planned: cell shows light blue + "N planned" badge
• Single-warehouse products skip this check (no cross-WH netting issue)

---

Clickable Gap Cells

Gap cells with unplanned shortfalls are clickable links to the recommendations page:

• Warehouse-level cells (renderWhCell): link to /gap/recommendations?product=X&month=Y&warehouse=Z
• Product-level cells (renderFullCell): link to /gap/recommendations?product=X&month=Y (no warehouse filter — shows all WHs)
• Existing query params (customer, packaging, confidence) are preserved in the link

Non-gap cells and fully-planned cells are not clickable.

---

Units Mismatch (Known Issue)

Source	Unit	Difference
Airtable (contracts, orders, forecast, inventory)	Short Tons (ST)	—
Postgres (supplier POs)	Metric Tons (MT)	~10% larger

1 Metric Ton = 1.10231 Short Tons. Currently displayed side by side without conversion. The dashboard labels POs as "MT" and Airtable data as "ST" in the summary cards, but cell-level totals mix units.

---

Web Routes

Route	Description
GET /gap	Main gap matrix dashboard (HTML)
GET /gap?customer=Name	Filtered to single customer
GET /gap?warehouse=BAL	Filtered to single warehouse
GET /gap?packaging=Bags	Filtered to packaging type
GET /gap?confidence=80	Forecast confidence override (0/20/50/80/100 or historical)
GET /gap?nocache=1	Bypass report cache
GET /gap/product?name=MagnaPalm	Product drill-down (all months)
GET /gap/recommendations	AI recommendations (all gaps with unplannedGap > 0)
GET /gap/recommendations?product=MagnaFat&month=March+2026	Recommendations filtered to a specific gap
GET /gap/recommendations?warehouse=BAL	Recommendations filtered to warehouse
GET /gap/recommendations?nocache=1	Force fresh AI generation
GET /api/gap	JSON API returning full GapReport (same query params)
GET /api/gap/recommendations	JSON API returning SourcingBrief
GET /api/gap/analytics	JSON API returning all pre-computed analytics
GET /gap/forecast-audit	Forecast audit dashboard (HTML)
GET /gap/forecast-audit?month=March+2026	Filter by month (comma-separated for multi-select)
GET /gap/forecast-audit?warehouse=BAL,OAK	Filter by warehouse (comma-separated for multi-select)
GET /gap/forecast-audit?sort=severity	Sort by severity (default), name, accuracy, delta
GET /gap/forecast-audit?issue=over_forecasting	Filter by issue type
GET /gap/forecast-audit?rep=John	Filter by sales rep
GET /gap/forecast-audit?nocache=1	Bypass audit cache
GET /api/gap/forecast-audit	JSON API returning full ForecastAuditReport
GET /api/gap/debug-ia?product=X	Debug: logs raw IA field values to server console
GET /optimize	LP optimization dashboard (admin only)
GET /optimize?nocache=1	Force LP re-solve
GET /optimize/scenarios	Scenario comparison view (admin only)
GET /api/optimize	JSON: LP result + rules-vs-LP comparison (admin only)
GET /api/optimize/scenarios	JSON: all scenario results (admin only)

All routes accept customer, warehouse, and packaging query params. Recommendation routes additionally accept product and month for cell-specific filtering. Forecast audit routes accept month (comma-separated), warehouse (comma-separated), sort, issue, rep, and nocache params. Optimizer routes accept nocache only and require admin auth.

---

Infrastructure Requirements

Airtable: Always available (cloud API). Requires AIRTABLE_API_KEY and AIRTABLE_BASE_ID in .env.

PostgreSQL: Requires SSH tunnel for local development:

ssh -L 5432:<DB_HOST>:<DB_PORT> root@<DROPLET_IP> -N

If tunnel is down:

• Supplier PO data → empty (supply = 0, FOB = null)
• Transit times → 45-day fallback
• Product normalization → degraded (no canonical product list)

Anthropic API: Required for AI recommendations. If ANTHROPIC_API_KEY is not set, the recommendations page shows an error message but the gap matrix works normally.

---

Sourcing Recommendations (Deterministic Rules Engine)

The recommendations layer sits on top of the gap dashboard. It uses a deterministic rules engine to generate all numeric and categorical recommendation data, with an optional AI narrative enricher that fills 5 text fields. Every number on screen is traceable to waterfall / scorecard / landed cost data — the AI explains why, not what.

Architecture

Gap Dashboard (existing)     Enriched Context Builder        Deterministic Builder      Narrative Enricher (optional)
────────────────────────    ────────────────────────────     ─────────────────────      ──────────────────────────────
buildGapReport()            ├── supply waterfalls            recommendation-builder.ts   Claude Haiku 4.5
      │                     ├── supplier scoring             (rules engine)              (5 text fields only)
      ▼                     ├── landed costs                        │                            │
buildWarehouseActions()     ├── timing chains                       ▼                            ▼
      │                     ├── demand tiers                 SourcingRecommendation[]    SourcingRecommendation[]
      ▼                     └── forecast adjustments         (all numbers final)         (narratives added)
actionRequired[] ──────────────────────────────────────────────────────────┘
fullyCovered[] ──→ shown for verification only

Why this architecture? The previous approach used a single Claude API call to generate all recommendation data (numbers and text). This was unreliable — the AI would hallucinate tonnages, invent supplier names, and produce inconsistent numbers. The new architecture pre-computes all numeric data deterministically, then optionally asks Claude to write natural-language explanations for 5 text fields only.

Analytics Layer (4 modules)

All analytics modules live in analytics/ and query data sources independently. These feed into the rules engine services.

1. Supplier Scorecard (analytics/supplier-scorecard.ts)

Source	PostgreSQL — supplier_purchase_orders JOIN suppliers, products, ref_shipment_windows
Output	SupplierScorecard[] — one entry per supplier × product pair

What it computes per supplier × product:

• totalPOs — number of historical purchase orders
• totalTons — total metric tons ever ordered
• avgFob, minFob, maxFob — FOB price statistics (weighted average)
• maxMonthlyCapacity — largest single-month volume (proxy for capacity)
• lastPoDate — most recent PO (recency of relationship)
• avgLeadDays — average days from PO creation (meta_created) to shipping window start

2. Price Trend Analysis (analytics/price-trends.ts)

Source	PostgreSQL — supplier_purchase_orders JOIN products, ref_sales_months
Output	PriceTrend[] — one entry per product

What it computes per product:

• monthlyPrices[] — weighted average FOB and total tons per month
• currentFob — most recent month's price
• avgFob12m — 12-month tonnage-weighted average
• trend — "rising", "falling", or "stable" (computed from last 3 months; ±3% threshold)
• seasonalPattern — e.g. "Q1 typically 5% below annual avg" (compares quarterly averages across all years)

3. Forecast Accuracy (analytics/forecast-accuracy.ts)

Source	Airtable — Sales Rep Forecasts table
Output	{ byProduct: ForecastAccuracy[], byRep: RepAccuracy[] }

Key insight: If MagnaFat forecasts historically convert at 72%, the rules engine adjusts sourcing targets to cover 100% of firm demand + ~72% of forecast demand.

4. Route Cost Analysis (analytics/route-costs.ts)

Source	PostgreSQL — ref_landed_cost_estimates JOIN ref_ports_of_lading, ref_efi_ports, ref_otw_transit_times
Output	RouteCost[] — one entry per origin → destination port pair

Rules Engine Services (8 modules)

All rules engine services live in services/ and transform analytics + gap data into deterministic recommendations.

1. Sourcing Context Builder (services/sourcing-context-builder.ts)

Orchestrates all rules engine services. Takes GapReportResult + WarehouseBreakdown + analytics and produces an EnrichedContext containing supply waterfalls, supplier scores, landed costs, timing chains, demand tiers, and forecast adjustments. Also builds product timelines and cross-warehouse opportunities.

Sourcing Context Builder orchestration:
┌─────────────────────────────────────────┐
│  Inputs: gap actions + analytics data   │
├─────────────────────────────────────────┤
│  ├── Supply Waterfall (7 steps)         │
│  ├── Demand Tiers (3 tiers)             │
│  ├── Supplier Scoring (5 weights)       │
│  ├── Landed Cost (total cost)           │
│  ├── Timing Chain (PO → arrival)        │
│  └── Forecast Adjustments (6 types)     │
├─────────────────────────────────────────┤
│  Also receives externally:              │
│  ├── Price Trends (per-product FOB)     │
│  └── Supplier Capacities (per-supplier) │
├─────────────────────────────────────────┤
│             ↓ EnrichedContext            │
│  Recommendation Builder (deterministic) │
│             ↓ SourcingRecommendation[]   │
│  Narrative Enricher (optional AI)       │
│             ↓ SourcingRecommendation[]   │
│               (with 5 text fields)      │
└─────────────────────────────────────────┘

2. Supply Waterfall (services/supply-waterfall.ts)

Evaluates supply sources in 7-step priority order for each gap:

Step	Source	Description
1	General pool (same WH)	Unallocated inventory at the gap's warehouse
2	General pool (nearby WH)	Unallocated inventory at adjacent warehouses
3	Strategic reserve	Reserved inventory that can be released
4	Cross-warehouse excess	Surplus from warehouses with no deficit
5	Existing sourcing plan	Planned supply already in the pipeline
6	New PO	Fresh purchase order to a supplier
7	Buy-ahead	Over-source this month to cover next month's gap

Shared pool budget: Nearby warehouse general pool is a shared resource. The waterfall tracks a nearbyPoolBudget to prevent multiple gap actions from double-counting the same pool inventory.

Cross-WH firm threshold (Rule 11): Cross-warehouse transfers only allowed when the deficit warehouse's demand is ≥ 50% firm (contracts + open orders). Prevents speculative transfers based on uncertain forecast.

3. Recommendation Builder (services/recommendation-builder.ts)

Walks the pre-computed supply waterfall for each gap action and maps each step to a concrete SourcingRecommendation with the appropriate RecommendationType. All numbers are traced to actual data — no AI involvement.

4. Demand Tiers (services/demand-tiers.ts)

Computes per-action demand breakdown using confidence by rep × product × contract type:

Tier	Description	Sourcing Action
Tier 1 (Firm Floor)	Contracts + open orders	100% source — always cover
Tier 2 (High Confidence)	Unsold from reps with > 80% accuracy	Include in sourcing target
Tier 3 (Low Confidence)	Unsold from reps with < 60% accuracy	Reduce exposure
Buffer	60-80% accuracy, scaled proportionally	Proportional sourcing

Produces conservativeGap (firm only), expectedGap (confidence-adjusted), and maxDemand (all tiers).

5. Supplier Scoring (services/supplier-scoring.ts)

Weighted composite scoring per supplier per gap:

Weight	Factor	Description
35%	Landed cost	Lower total cost = higher score
25%	Capacity	More available capacity = higher score
20%	Reliability	More POs + higher capacity = higher score
10%	Recency	More recent activity = higher score
10%	Relationship	More total tonnage = higher score

Diversification (Rule 13): Warns if any supplier would exceed 60% of monthly sourcing volume. When triggered, the builder splits recommendations across top 2-3 suppliers.

6. Landed Cost (services/landed-cost.ts)

Computes total landed cost per supplier × route:

Total Landed = FOB + Ocean Freight + Tariff + Drayage + Handling + Broker Fee

Uses contract freight when volume ≥ 5 containers/month for the route; otherwise spot rate. Falls back to the other if preferred rate is unavailable.

7. Timing Chain (services/timing-chain.ts)

Computes full timeline for each sourcing action:

PO Confirmation (3-5 days) → Production (7-14 days) → Ship Window → Transit → Port Clearance (5-7 days) → Receipt

Uses supplier-specific avgLeadDays from scorecards for production estimates. Produces urgency classification: immediate (< 14 days), soon (14–41 days), plan (42+ days).

8. Forecast Adjustments (services/forecast-adjustments.ts)

Maps forecast audit findings to concrete sourcing adjustments:

Audit Issue	Adjustment
over_forecasting	Reduce confidence
under_forecasting	Increase confidence
forecast_exceeds_opportunity	Cap at opportunity
ignores_trailing_trend	Use trailing avg
contract_non_delivery	Reduce by non-delivery rate
missing_customer	Add missing demand

Narrative Enricher (services/narrative-enricher.ts)

Optional AI text overlay using Claude Haiku 4.5. Takes fully-computed SourcingRecommendation[] (all numbers finalized) and fills only 5 text fields:

Field	Description
reasoning	2-3 sentence justification for the recommendation
riskNote	What happens if forecast doesn't convert
certaintyNote	How confident we are in the demand signal
priceRationale	Why the target price is appropriate
capacityNote	Supplier capacity context

Post-validation: After AI response, validates that no numeric fields were changed. If the AI is unavailable, recommendations display with structured placeholder text — fully functional without AI.

Recommendation Types

Type	When it Fires	Description	Urgency
fill_gap	Firm demand has unmet gap after existing sources	Standard new PO to cover shortfall	From timing chain
buy_ahead	Falling price trend + consecutive month gaps + savings ≥ 2%	Over-source this month to cover next month's gap	From timing chain
redirect_po	Excess at one warehouse, deficit at another (≥ 50% firm at deficit)	Move planned PO from excess WH to deficit WH	Always soon
reallocate_inventory	General pool or strategic reserve available	Release existing inventory to cover gap	Always plan
reduce_exposure	Confidence-adjusted demand < raw demand (deferred portion)	Wait — don't source against uncertain forecast	Always plan
hold_position	Gap covered by existing plans or no action needed	No action needed, explain why	Always plan

Urgency Classification

Computed from daysRemaining to recommended PO date (includes 7-day buffer):

Urgency	Days Remaining	Meaning
immediate	< 14	Must act now or miss ship window
soon	14–41	Prioritize in current sourcing cycle
plan	42+	Queue for next cycle

Fixed urgency: Reallocate, Hold, and Reduce Exposure are always plan. Redirect is always soon. Fill Gap and Buy Ahead derive urgency from the timing chain.

Key Engine Thresholds

Constant	Value	Purpose
MIN_SUPPLIER_SCORE	30	Minimum composite score for supplier viability
CONCENTRATION_CAP	60%	Max share of monthly sourcing from one supplier
CONCENTRATION_MIN_TONS	500 ST	Don't split orders below this volume
BUY_AHEAD_SAVINGS_THRESHOLD	2%	Minimum savings to trigger buy-ahead
Cross-WH firm threshold	50%	Requires ≥ 50% firm demand at deficit warehouse

Cross-Warehouse Logic

The position map computes per-warehouse net position using the same formula as the gap matrix:

position = totalSupply - totalDemand   (positive = excess, negative = deficit)

Deficit warehouse exclusion: Warehouses with deficit positions are excluded from cross-warehouse supply sources. Only warehouses with genuine excess can donate inventory. This prevents the waterfall from recommending transfers from a warehouse that itself has a shortfall.

Orchestration Flow (in server.ts)

GET /gap/recommendations?product=MagnaFat&month=March+2026&warehouse=BAL
      │
      ├── Parse query params: customer, warehouse, packaging, product, month
      ├── Check cache (memory → disk) → return if hit (skipped when product/month filtered)
      ├── Check failure cooldown → error if recently failed
      ├── Check dedup → await if in-flight
      │
      ├── 1. buildGapReport(customer, warehouse, packaging, 6, confidenceOverride)
      │      └── buildWarehouseActions(fetcherOutputs, filterProduct, filterMonth)
      │           → { actionRequired, fullyCovered }
      │
      ├── 2. Promise.all([            ← analytics in parallel
      │        fetchSupplierScorecards(),
      │        fetchPriceTrends(),
      │        fetchForecastAccuracy(),
      │        fetchRouteCosts(),
      │        fetchSupplierCapacities()
      │      ])
      │
      ├── 3. buildEnrichedContext(gapResult, warehouseBreakdown, analytics)
      │      ├── supply waterfalls (per action)
      │      ├── supplier scores (per action)
      │      ├── landed costs (per supplier × route)
      │      ├── timing chains (per action)
      │      ├── demand tiers (per action)
      │      └── forecast adjustments (per product)
      │
      ├── 4. buildRecommendations(enrichedContext)   ← deterministic, no AI
      │
      ├── 5. enrichRecommendationNarratives(recs)    ← optional AI text overlay
      │
      ├── 6. Cache result (memory + disk)
      │
      └── 7. renderRecommendations(brief, error, activeFilters, fullyCovered)

Recommendations View (web/views/recommendations.ts)

Layout:

1. Filter chips — active filters shown as removable chips (product, month, warehouse, customer, packaging) with "Clear All"
2. Summary cards — gap count, recommendation count, total tons, estimated cost, immediate-urgency count
3. Urgency groups — collapsible sections (Immediate / Soon / Plan Ahead), each containing recommendation cards
4. Fully Sourced section — collapsed <details> showing gaps covered by sourcing plans (greyed out, for verification)
5. Back link — returns to gap dashboard preserving current filter context

Each recommendation card shows:

• Product + month + warehouse badge + urgency badge + recommendation type badge
• Order instruction: tons, supplier, target price, route, landed cost breakdown
• Demand rationale: firm floor vs confidence-adjusted sourcing target
• Price rationale: current vs 12-month avg, seasonal notes
• Timing chain: PO date → ship → arrive → warehouse by date
• Reasoning paragraph (AI-generated or placeholder)
• Risk note + total estimated cost

Caching (agent/cache.ts)

Layer	Storage	Key	TTL
Memory	Map<string, SourcingBrief>	{customer|"all"}__{warehouse|"all"}__{YYYY-MM-DD}	Server lifetime
Disk	.sourcing-cache/{key}.json	Same	Survives restarts
Failure cooldown	Map<string, timestamp>	Same	5 minutes
Dedup	Map<string, Promise>	Same	Duration of generation

Cache key changes daily (date-based) since gap data changes with new contracts/orders. When product/month filters are active (cell-specific recommendations), cache is bypassed.

Gap report cache (in server.ts): Key format is ${customer}::${warehouse}::${packaging}::${confidence}. All routes extract all query params.

---

File Map

packages/sourcing/src/
├── models/
│   ├── gap-report.ts              # GapReport, GapCell, ProductRow, MonthColumn, CategoryBreakdown, PlanSegmentDetail
│   └── product-map.ts             # Cross-system product name normalization
├── fetchers/
│   ├── demand-contracts.ts        # Airtable Contract Line Items → committedDemand + warehouse
│   ├── demand-open-orders.ts      # Airtable Sales Orders (Open) → openOrders + warehouse
│   ├── demand-forecast.ts         # Airtable Sales Rep Forecasts → forecastDemand + warehouse
│   ├── demand-categories-ia.ts    # Airtable Inventory Availability → category breakdown (Sourcing/Conversion/Spot)
│   ├── forecast-audit-data.ts     # Airtable Sales Rep Forecasts (all months) + Sales Orders (historical customers)
│   ├── supply-purchase-orders.ts  # Postgres supplier_purchase_orders → sourcedPOs + avgFob + destPort
│   ├── supply-sourcing-plans.ts   # Airtable Sourcing Plans → planned supply per supplier segment
│   ├── supply-transit.ts          # Postgres ref_otw_transit_times → order-by deadlines
│   ├── inventory.ts               # Airtable Inventory Availability → inventoryOnHand (per warehouse)
│   └── warehouse-metadata.ts      # Airtable Warehouses → warehouse codes, port mapping (cached)
├── analytics/                     # Pre-computed analytics for rules engine + AI
│   ├── supplier-scorecard.ts      # PG → supplier × product performance + capacities
│   ├── price-trends.ts            # PG → FOB trends, seasonality, 12-month avg
│   ├── forecast-accuracy.ts       # AT → forecast conversion rates by product + rep
│   ├── forecast-audit.ts          # AT → customer-level forecasting audit (11 issue types, recommended forecasts)
│   └── route-costs.ts             # PG → freight rates + transit times per port pair
├── agent/                         # Types and caching for sourcing recommendations
│   ├── types.ts                   # SourcingContext, SourcingRecommendation, SourcingBrief, SourcingAction, RecommendationType
│   ├── sourcing-agent.ts          # Legacy AI agent (not imported by server — superseded by deterministic builder + narrative enricher)
│   └── cache.ts                   # Memory + disk cache, failure cooldown, dedup (key includes warehouse)
├── optimizer/
│   ├── parameters.ts             # LP input types (demand, supply, capacity, costs, timing)
│   ├── param-assembler.ts        # Bridges sourcing enriched context → LP parameters
│   ├── optimizer.ts              # Builds CPLEX LP model, calls HiGHS WASM, parses solution
│   ├── result.ts                 # LP output types (purchases, coverage, allocations, scenarios)
│   └── scenario-runner.ts        # What-if scenario engine (7 built-in scenarios)
├── services/
│   ├── gap-calculator.ts          # Assembles all fetchers → GapReport matrix (customer + warehouse + packaging + confidence filter)
│   ├── warehouse-breakdown.ts     # Per-warehouse sub-rows from raw fetcher outputs (accepts confidenceOverride)
│   ├── warehouse-actions.ts       # Per-warehouse gap actions → { actionRequired, fullyCovered } (accepts product/month filters)
│   ├── sourcing-context-builder.ts # Orchestrates rules engine — builds enriched context from analytics + gap data
│   ├── supply-waterfall.ts        # 7-step priority supply source evaluation with shared pool budget
│   ├── recommendation-builder.ts  # Deterministic recommendation generation from waterfall steps
│   ├── demand-tiers.ts            # Firm/sourcing/max demand tiers per gap action
│   ├── supplier-scoring.ts        # Weighted composite supplier scores (35% cost + 25% capacity + 20% reliability + 10% recency + 10% relationship)
│   ├── landed-cost.ts             # FOB + ocean freight + tariff + drayage + handling + broker = total landed cost
│   ├── timing-chain.ts            # PO → ship → arrive timeline with urgency classification
│   ├── forecast-adjustments.ts    # Forecast audit → concrete sourcing adjustments (6 adjustment types)
│   ├── narrative-enricher.ts      # AI text overlay — fills 5 text fields only (reasoning, riskNote, certaintyNote, priceRationale, capacityNote)
│   └── month-utils.ts             # Month list generation, parsing, toNumber helper
└── web/
    ├── server.ts                  # Express app on port 3002 (gap + recommendations routes, all query params)
    └── views/
        ├── layout.ts              # HTML shell (EFI brand, nav with recommendations link)
        ├── gap-matrix.ts          # Main view: heatmap + WH gap indicators + confidence filter + clickable cells
        ├── product-detail.ts      # Drill-down: single product breakdown by month
        ├── recommendations.ts     # Recommendations: filter chips, urgency groups, fully-sourced section
        ├── optimization.ts        # LP optimizer: purchase plan, coverage, supplier allocation, scenarios, binding constraints
        └── forecast-audit.ts      # Customer audit: severity table, product breakdown, issue cards, missing customers

---

Forecast Audit Dashboard

Customer-level forecasting accuracy analysis with corrected forecast recommendations. Accessible at /gap/forecast-audit.

Data Sources

Two Airtable fetchers in fetchers/forecast-audit-data.ts:

1. fetchForecastAuditData() — Queries all Sales Rep Forecast records (historical + future) with 21 fields including approved forecasts (B2B/Conversion/Spot), Qty Sold, Total Opportunity, trailing 3-month average, last month actual, confidence rating, loss reports, and current forecast breakdowns. Filters out "Inventory Adjustment" reps. Marks each record as historical by comparing month to current date. The WH linked record field is resolved to warehouse abbreviations (BAL, OAK, etc.) via resolveWarehouseId() from warehouse-metadata.ts.

2. fetchHistoricalCustomers() — Queries invoiced Sales Orders for trailing 12 months to detect customers with purchase history but no current forecast (missing customer detection).

The audit also reuses fetchDemandContracts() for delivery rate calculation.

Analytics Engine (analytics/forecast-audit.ts)

Groups forecast records by customer and computes per-customer metrics:

Metric	Computation
overallAccuracyPct	totalSold / totalForecast × 100 (historical months only)
forecastBias	"over" if accuracy < 90%, "under" if > 110%, else "accurate"
deliveryRate	totalDelivered / totalContracted × 100 (from Contract Line Items)
trailing3MonthAvg	From Airtable 3-Month Trailing field
lastMonthActual	From Airtable Last Month Actual field

11 Issue Types

Issue	Flag When
over_forecasting	Accuracy < 70% for 3+ months
under_forecasting	Accuracy > 140% for 3+ months
forecast_exceeds_opportunity	Forecast > Total Opportunity for any future month
ignores_trailing_trend	Forecast outside ±25% of trailing 3-month avg
ignores_lost_sales	Loss count > 0 but future forecast not reduced
inconsistent_with_history	Future forecast deviates >50% from historical avg
missing_customer	Customer has 12-month purchase history but no current forecast
inventory_stockout_loss	Forecast decline after zero-sold month
large_customer_not_contracted	Top-quartile customer, >2 months out, no sourcing/conversion contract
contract_non_delivery	Delivery rate < 80%
weekly_distribution_risk	Single product >75% of total weekly forecast

Recommended Forecast Formula

Per customer × product, a weighted blend:

base = trailing3MonthAvg × 0.40
     + (accuracyPct/100 × currentForecast) × 0.30
     + lastMonthActual × 0.20
     + totalOpportunity × 0.10

Adjustments:
  - Lost sales: base *= (1 - 0.15)     [if loss reports > 0]
  - Low delivery: base *= (deliveryRate / 100)
  - Cap at totalOpportunity
  - Floor at 0

Severity Score

Weighted sum of flagged issues (0–100). Each issue type has a fixed weight (e.g., over_forecasting = 20, large_customer_not_contracted = 16, weekly_distribution_risk = 8). Levels: high (≥40), medium (≥20), low (<20).

View (web/views/forecast-audit.ts)

• Summary cards: Customers audited, issues by severity, over/under forecasters, missing customers, net forecast adjustment
• Month filter: Checkable chip buttons (multi-select) showing all distinct months from forecast data, sorted chronologically. Labels abbreviated (e.g., "Mar 26"). Same visual pattern as the gap dashboard warehouse chips.
• Warehouse filter: Checkable chip buttons (multi-select) showing warehouse abbreviations (BAL, OAK, HOU, etc.). Resolved from Airtable record IDs via resolveWarehouseId().
• Sort/Issue/Rep controls: Dropdown selects for sort order (severity/name/accuracy/delta), issue type filter, and sales rep filter.
• Severity groups: Collapsible <details> sections grouping customers by high/medium/low severity. All collapsed by default. Each group shows customer count, issue count, and net forecast adjustment.
• Customer table: Expandable <details> rows within each severity group, with severity-colored left border, showing product breakdown, issue cards, and historical context metrics
• Missing customers section: Collapsible table of customers with purchase history but no forecast

Caching

Same 60-minute TTL pattern as the gap report. Cache key is ${month}::${warehouse} — each filter combination is cached independently. Busted with ?nocache=1.

---

Potential Improvements

• Per-route deadlines: Use actual port pairs from historical POs instead of single average
• Units normalization: Convert MT ↔ ST consistently (1 MT = 1.10231 ST)
• Feedback loop: Capture user approval/rejection/modifications of AI recommendations and reinject into future prompts (see docs/ai-recommendation-feedback.md)
• Alerts: Email when coverage drops below threshold or deadline approaches
• Approval workflow: Let sourcing team approve/reject/modify recommendations before acting
• Warehouse capacity tracking: Use Capacity (in MT) from Warehouses table to flag over-allocation