| name | freight-exchange |
| description | Deploy the Freight Exchange page: a dispatcher-grade marketplace cockpit that mirrors what users do today on Timocom, WTransnet, Teleroute, B2P (NA: DAT, Truckstop, Convoy, Uber Freight). Adds a parallel React route to the ORS Control App that lets dispatchers browse, filter, and inspect synthesized freight offers per active preset, with trust scores (credit/KYC/blacklist), market-rate badges (vs. weekly p25/p50/p75 USD/km from a RATE_INDEX dynamic table), and partner lane history. Reads from FLEET_INTELLIGENCE.MARKETPLACE projection views over per-preset SYNTHETIC_DATASETS.UNIFIED data, so switching preset auto-refreshes the page. Use when: setting up the Freight Exchange demo, dispatcher cockpit, freight marketplace, load board, trust-score badges, market-rate badges, Timocom-style UI parity. Do NOT use for: solver-led fleet-wide backload (use backload-matching), route-optimization VRP, route-deviation, retail-catchment, fleet-intelligence car/e-bike demos. Triggers: freight exchange, freight marketplace, load board, dispatcher cockpit, browse offers, filter offers, trust badge, market rate, lane history, partner credit score, Timocom UI, WTransnet UI, Teleroute UI, B2P UI, DAT UI, Truckstop UI, marketplace page, MARKETPLACE schema. |
| depends_on | ["install-fleet-apps","backload-matching"] |
| metadata | {"author":"Snowflake SIT-IS","version":"1.0.0","category":"demo"} |
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
| Privilege | Scope | Reason |
|---|
| USAGE ON DATABASE FLEET_INTELLIGENCE | Database | Demo database |
| CREATE SCHEMA | Database (FLEET_INTELLIGENCE) | Creates MARKETPLACE schema |
| CREATE TABLE | Schema (FLEET_INTELLIGENCE.MARKETPLACE) | CONFIG |
| CREATE VIEW | Schema (FLEET_INTELLIGENCE.MARKETPLACE) | VW_OFFERS, VW_PARTNERS, VW_PARTNER_HISTORY, VW_LANE_HISTORY, VW_OFFER_ENRICHED |
| CREATE DYNAMIC TABLE | Schema (FLEET_INTELLIGENCE.MARKETPLACE) | RATE_INDEX |
| USAGE + SELECT ON SCHEMA SYNTHETIC_DATASETS.UNIFIED | Schema | Source tables for projection views |
| USAGE ON WAREHOUSE ROUTING_ANALYTICS | Warehouse | Powers the page queries + RATE_INDEX refresh |
Note: ACCOUNTADMIN is NOT required.
Configuration
| Parameter | Default | Description |
|---|
| DATABASE | FLEET_INTELLIGENCE | Database for demo objects |
| SCHEMA | MARKETPLACE | Schema for marketplace tables and views |
| WAREHOUSE | ROUTING_ANALYTICS | Warehouse for queries + RATE_INDEX refresh |
| REGION | (active preset) | Auto-derived from MARKETPLACE.CONFIG, mirroring the active Control App preset |
| VEHICLE_TYPE | (active preset) | Same |
| RATE_INDEX_LAG | 15 minutes | Dynamic Table target lag (lower = fresher badges, higher = lower cost) |
| PARTNERS_PER_PRESET | 80 | Partner pool size per preset |
| HISTORY_ROWS_PER_PARTNER | ~6 | Synthetic prior-shipment rows per partner |
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;
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.DIM_PARTNERS;
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.FACT_PARTNER_HISTORY;
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:
- Confirm the region/vehicle picker matches an active preset that has
FACT_FREIGHT_OFFERS rows.
- Click Freight Exchange in the sidebar (under Solution Accelerators).
- 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.
- The map shows offer pickup points clustered, color-coded by source vendor.
- Filter by equipment chip (e.g. REEFER) -> grid + map both refresh.
- 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
| Phase | Status | Scope |
|---|
| A | shipped | Browse + filter shell, grid + map + detail drawer |
| B | shipped | Trust badge, market-rate badge, partner lane-history tooltip |
| E1 | shipped (server) | ORS road km/min cache (FACT_OFFER_ROUTES, routed columns on VW_OFFER_ENRICHED); endpoints POST /api/fx/refresh-routes, POST /api/fx/eta |
| E2 | shipped (server) | Reachability isochrone via POST /api/fx/isochrone |
| E3 | shipped (server) | Deadhead matrix vs. BACKLOAD_MATCHING.VW_TRAILERS (FACT_DEADHEAD_MATRIX, VW_OFFER_DEADHEAD); endpoints GET /api/fx/deadhead, POST /api/fx/refresh-deadhead |
| E4 | shipped (server) | Round-trip 1-vehicle/2-shipment VROOM via POST /api/fx/round-trip |
| E5 | shipped (server) | Multi-offer bundle solver with EU 561/2006 break via POST /api/fx/bundle |
| E6 | shipped (server) | HGV profile selection per vehicle type; ADR offers add avoid_features=ferries,tunnels; VW_OFFER_COMPLIANCE view |
| E7 | shipped (sql) | Lane density H3 res-5 heatmap via VW_LANE_DENSITY + GET /api/fx/lane-density |
| E8 | shipped (server) | Cortex Complete negotiation drafts in OFFER_DRAFTS via POST /api/fx/draft-counter |
| -- | shipped (sql) | PROPOSAL_DECISIONS schema migration: SOURCE_PAGE, DECISION_TYPE, BUNDLE_ID; shared audit endpoint POST /api/fx/decisions |
| C | future | Saved searches, Snowflake Alerts -> SSE, posting (trucks + loads), chat, bids |
| D | future | OFFER_DOCS + AI_PARSE_DOCUMENT, full cabotage cross-border flags, round-trip toggle UI, tariff calculator |
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
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;
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;
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.
