Freight Exchange

Deploy Freight Exchange Page

Adds a new Freight Exchange entry to the ORS Control App sidebar (under Solution Accelerators) that lives next to Backload Matching. Where Backload Matching is solver-led ("solve my fleet for tomorrow"), Freight Exchange is dispatcher-led ("browse, filter, inspect, post"). The two pages share the same underlying per-preset offer data; this skill adds the trust + market-rate intelligence layer.

Use Case Narrative

Today a DHL Freight / Maersk Inland / DSV / Schneider dispatcher juggles 4 browser tabs (TC Truck&Cargo, WTransnet Bolsa, Teleroute, B2P or DAT/Truckstop in NA) plus their internal scheduling tool. Each tab has its own filter UI, each has its own credit/trust signal, each has its own price benchmark. Information overload is the explicit pain point Martin Ahleff named in the May 5, 2026 NTBO call.

Phase A of this skill replaces those 4 tabs' browse + filter experience with a single sortable grid + map. Phase B adds the two pieces dispatchers actually pay Timocom for: carrier trust score (credit, KYC, blacklist) and market-rate benchmark (is this offer above or below corridor p50?).

Phases C (action engine: post / chat / bid / saved searches + alerts) and D (compliance: docs / cross-border / round-trip / tariff calculator) are tracked in references/productisation.md.

Prerequisites

• install-fleet-apps deployed (OPENROUTESERVICE_APP database, all ORS services running). The page reads only from Snowflake; ORS routing functions are not currently called from this page.
• backload-matching deployed (provides the FACT_FREIGHT_OFFERS projection pattern this skill builds on).
• Synthetic datasets seeded under SYNTHETIC_DATASETS.UNIFIED.* for at least one preset (DIM_FLEET, FACT_FREIGHT_OFFERS, DIM_PARTNERS, FACT_PARTNER_HISTORY). Newly-generated presets after this skill ships populate DIM_PARTNERS + FACT_PARTNER_HISTORY natively. For older presets, re-run a Data Studio job for that preset to seed them.
• For route previews, /api/fx/offer-route is cache-first on FLEET_INTELLIGENCE.MARKETPLACE.V_FACT_OFFER_ROUTES_CURRENT; Data Studio precomputes this cache for active presets.

Required Privileges

Note: ACCOUNTADMIN is NOT required.

Configuration

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: freight-exchange.

Workflow

Step 1: Set Query Tag

ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-fleet","name":"oss-freight-exchange","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Step 2: Verify Prerequisites

SHOW SERVICES IN DATABASE OPENROUTESERVICE_APP;
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.FACT_FREIGHT_OFFERS;   -- > 0
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.DIM_PARTNERS;          -- > 0 after a Data Studio run
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.FACT_PARTNER_HISTORY;  -- > 0 after a Data Studio run

If DIM_PARTNERS or FACT_PARTNER_HISTORY are empty, run a Data Studio job for the active preset. Existing presets that pre-date partner generation will be re-populated automatically the next time a job runs for that preset.

Step 3: Run Bootstrap

snow sql -f .cortex/skills/freight-exchange/references/bootstrap.sql -c <ACTIVE_CONNECTION>

This creates FLEET_INTELLIGENCE.MARKETPLACE.{CONFIG, VW_OFFERS, VW_PARTNERS, VW_PARTNER_HISTORY, VW_LANE_HISTORY, RATE_INDEX, VW_OFFER_ENRICHED}. The same DDL is also embedded in .cortex/skills/install-fleet-apps/fleet_admin_app/ui/src/server/lib/init.ts and runs on every container start.

CRITICAL on a fresh install-fleet-apps install - run this AFTER seed data loads. init.ts seeds MARKETPLACE.CONFIG at container boot, but the boot (install-fleet-apps engine boot) happens BEFORE seed data loads (Step 7). The CONFIG row is data-derived from SYNTHETIC_DATASETS.UNIFIED, so at boot it derives nothing, CONFIG stays empty, and VW_OFFERS returns 0 even though seed FACT_FREIGHT_OFFERS has rows. The container does not auto-restart after the seed load, so you MUST run this bootstrap.sql here (post-seed) to re-derive CONFIG. This is why Freight Exchange is a post-seed step in install-fleet-apps, not a boot-time guarantee. The self-healing MERGE makes it idempotent.

Step 4: Interactive page (retired host)

The legacy Vite control app (ors_control_app) that hosted the Freight Exchange page has been removed from this repo, and the page was not ported to the current fleet_sa_app/fleet_admin_app. Step 3's bootstrap.sql (plus the fleet_admin_app boot init.ts) still creates the full MARKETPLACE.* SQL/data layer, so the objects are queryable, but there is currently no bundled interactive page in the fleet apps. Take the page source from another repo if you need the UI.

Step 5: Verify

In the app:

1. Confirm the region/vehicle picker matches an active preset that has FACT_FREIGHT_OFFERS rows.
2. Click Freight Exchange in the sidebar (under Solution Accelerators).
3. The grid should list ~300 offers with columns: source, pickup, dropoff, distance_km, equipment, ADR, weight, USD, USD/km, age, trust badge, market badge, status.
4. The map shows offer pickup points clustered, color-coded by source vendor.
5. Filter by equipment chip (e.g. REEFER) -> grid + map both refresh.
6. Click any offer -> right-rail drawer shows partner trust details, lane-history aggregate, and the price-vs-market delta.

If the MARKET badge column shows UNKNOWN for every row, the RATE_INDEX Dynamic Table has not yet refreshed. It refreshes within the configured target lag (default 15 minutes). To force-refresh:

ALTER DYNAMIC TABLE FLEET_INTELLIGENCE.MARKETPLACE.RATE_INDEX REFRESH;

Phases

Pending: UI surfaces for E1-E8 (React grid columns, drawers, map layers) were never wired into the page. The original FreightExchange.tsx component lived in the legacy Vite ors_control_app, which has been removed from this repo. Server-side SQL objects exist; UI integration would require re-homing the page in a fleet app.

Enrichment Bootstrap (E1-E8)

Phase E1-E8 MARKETPLACE objects (FACT_OFFER_ROUTES, FACT_DEADHEAD_MATRIX, VW_OFFER_DEADHEAD, VW_LANE_DENSITY, OFFER_DRAFTS, routed columns on VW_OFFER_ENRICHED, and PROPOSAL_DECISIONS discriminator columns) are created automatically on every fleet_admin_app container boot via init.ts. No manual SQL step is required for a normal deploy.

For greenfield audits or out-of-band rebuilds, run:

snow sql -f .cortex/skills/freight-exchange/references/bootstrap.sql -c <ACTIVE_CONNECTION>
snow sql -f .cortex/skills/freight-exchange/references/bootstrap-enrichment.sql -c <ACTIVE_CONNECTION>

The /api/fx/* server endpoints were part of the retired Vite ors_control_app and are not present in the current fleet apps; only the SQL/data objects above are created on a normal deploy.

Cleanup

-- Phase E1-E8 enrichment objects
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.OFFER_DRAFTS;
DROP VIEW  IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_LANE_DENSITY;
DROP VIEW  IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_OFFER_DEADHEAD;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.FACT_DEADHEAD_MATRIX;
DROP VIEW  IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_OFFER_ENRICHED_V2;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.FACT_OFFER_ROUTES;

-- Phase A/B core objects
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.RATE_INDEX;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_OFFER_ENRICHED;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_LANE_HISTORY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_PARTNER_HISTORY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_PARTNERS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.VW_OFFERS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE.CONFIG;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.MARKETPLACE;

-- The PROPOSAL_DECISIONS schema additions (SOURCE_PAGE, DECISION_TYPE,
-- BUNDLE_ID) are NOT dropped automatically - backload-matching also writes
-- to those columns now.

Note: the DIM_PARTNERS, FACT_PARTNER_HISTORY, and the new enrichment columns on FACT_FREIGHT_OFFERS are owned by install-fleet-apps (Data Studio output) and shared. Use the routing-solution-cleanup skill if you also want to remove those rows.

Out of Scope

• Live Timocom / WTransnet / Teleroute / B2P / DAT / Truckstop API integration (synthetic only).
• Reverse posting (free truck / load) - Phase C.
• Chat / bidding / counter-offers - Phase C.
• Saved searches + push notifications - Phase C.
• Document parsing (CMR / POD / ADR certificates) - Phase D.
• Cross-border / customs flags - Phase D.
• Round-trip / multi-leg solver - Phase D.
• Tariff calculator widget - Phase D.
• i18n (multi-language UI) - out of scope per current product direction.