Overview
The Data API is an internal Express server that sits between the agent applications (Market Research, Sourcing, Pricing) and the underlying data stores (Airtable CRM and PostgreSQL ERP). Each agent fetcher calls the Data API instead of querying databases directly.
No authentication. The Data API is an internal service — agents handle auth at their own server level. The API returns raw facts; agents apply interpretation (trends, narratives, optimization).
Architecture
All agent fetchers import the singleton dataApi from @efi/shared and call typed methods.
No agent package makes direct Airtable or Postgres calls — all data flows through the centralized API.
Response Format
All endpoints return a standard envelope:
{
"data": [ ... ],
"meta": {
"count": 42,
"cached": true,
"cacheAge": "14m"
}
}
Bypass cache on any endpoint with ?nocache=1.
Caching
| Data Type | TTL | Rationale |
|---|---|---|
| Reference data (products, customers, ports) | 24 hours | Rarely changes |
| Product logos | 2 hours | Airtable attachment URLs expire |
| Everything else (sales, supply, inventory, demand, costs) | 1 hour | Operational data, moderate freshness |
Reference Endpoints
Static catalog data. Sources: Airtable + PostgreSQL. Cached 24 hours.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/reference/products | — | All products with logo URLs |
GET | /api/reference/customers | — | All customers with city, state, sales rep |
GET | /api/reference/customer | ?name= | Single customer lookup by name |
GET | /api/reference/suppliers | — | All suppliers from PostgreSQL |
GET | /api/reference/warehouses | — | Main warehouses (TAC, HOU, SAV, OAK, CHI, BAL, ORF) |
GET | /api/reference/ports | — | Origin (SE Asia) + destination (US) ports |
GET | /api/reference/product-logos | — | Product name → logo URL map |
GET | /api/reference/transit-times | — | Ocean transit days per port pair + average |
Sales Endpoints
Sales orders, aggregations, logistics, and product-level analytics. Source: Airtable. Cached 1 hour.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/sales/orders | ?customer=, ?month=, ?months=, ?product=, ?status= | Filterable sales orders (status: invoiced | open | all) |
GET | /api/sales/snapshot | ?customer=, ?month= | Monthly tonnage snapshot by product |
GET | /api/sales/ytd | ?customer=, ?month= | YTD totals by contract type with savings |
GET | /api/sales/loadout-schedule | ?customer= | Upcoming (Open) + recent (Invoiced last 60 days) shipments |
GET | /api/sales/purchase-chart | ?customer=, ?product=, ?month=, ?region= | Trailing 12-month purchase data with pricing lines |
GET | /api/sales/contracts | ?months= | Contract line items by month with slippage and warehouse |
GET | /api/sales/product-monthly | ?months= | Invoiced sales by product × month (total MT, avg price, order count) |
Supply Endpoints
Supplier purchase orders, sourcing plans, and scorecards. Sources: PostgreSQL + Airtable. Cached 1 hour.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/supply/sourcing-windows | ?month= | 5-month PO pipeline by supplier × product with FOB trends |
GET | /api/supply/sourcing-chart | ?customer=, ?month= | Contract line items + inventory curve + sourcing window bands |
GET | /api/supply/suppliers | — | Suppliers with capacity allocations (total US + EFI) |
GET | /api/supply/sourcing-plans | ?months= | Approved/pending sourcing plans with supplier segments |
GET | /api/supply/supplier-scorecards | — | Supplier performance by product (POs, tonnage, FOB range, lead days) |
Inventory Endpoints
Warehouse inventory levels and demand category breakdown. Source: Airtable. Cached 1 hour.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/inventory/availability | ?months= | Beginning/actual/expected arrivals by product × warehouse × packaging |
GET | /api/inventory/categories | ?months=, ?warehouse=, ?packaging= | Sold/unsold breakdown by contract type, allocations, ending balance |
Demand Endpoints
Forecast demand, open orders, and forecast accuracy audit. Source: Airtable. Cached 1 hour.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/demand/forecasts | ?months= | Sales rep forecasts with confidence scoring |
GET | /api/demand/open-orders | ?months= | Open/unfulfilled sales orders as demand signal |
GET | /api/demand/forecast-audit | — | All active forecasts + trailing 12-month historical customers |
Cost Endpoints
Landed costs, FOB history, ocean freight, and route costs. Sources: PostgreSQL + Airtable. Cached 1 hour.
| Method | Route | Params | Description |
|---|---|---|---|
GET | /api/costs/landed | ?months= | Landed cost components by port × month (OF, customs, tariffs, drayage, handling) |
GET | /api/costs/historical-fob | ?months= | Tonnage-weighted avg FOB prices by product × month |
GET | /api/costs/ocean-freight | — | Ocean freight rates, transit times, MoM changes, chart time series |
GET | /api/costs/routes | — | All transit routes with latest landed costs (contract/spot, tariff, drayage, broker) |
Client Library
@efi/shared exports a typed DataApiClient with convenience methods for every endpoint:
import { dataApi } from "@efi/shared";
// Reference data
const logos = await dataApi.productLogos();
const customer = await dataApi.customer("Lash Legacy");
// Sales data
const snapshot = await dataApi.salesSnapshot("Lash Legacy", "March 2026");
const ytd = await dataApi.salesYtd("Lash Legacy", "March 2026");
const loadout = await dataApi.salesLoadoutSchedule("Lash Legacy");
const orders = await dataApi.salesOrders({ customer: "Lash Legacy", status: "all" });
// Supply data
const windows = await dataApi.sourcingWindows("March 2026");
const chart = await dataApi.sourcingChart("Lash Legacy");
// Costs
const freight = await dataApi.costsOceanFreight();
// Every response has the same shape
console.log(freight.data); // OceanFreightData
console.log(freight.meta); // { count, cached, cacheAge }
Configure the base URL via DATA_API_URL env var (defaults to http://localhost:3004).
Agent Migration Status
All agent packages now consume data through the Data API instead of making direct database calls.
| Agent | Status | Fetchers via Data API | Notes |
|---|---|---|---|
| Sourcing | Migrated | 10 fetchers | First package migrated — 8 Airtable + 2 Postgres queries replaced |
| Market Research | Migrated | 12 fetchers | 10 Airtable + 1 Postgres + 1 mixed queries replaced. AI fetchers (narrative, raw-material-pricing) unchanged. |
| Pricing | Pending | — | Still uses direct Airtable/Postgres access |
File Structure
packages/data-api/
├── package.json
├── tsconfig.json
├── README.md
└── src/
├── index.ts # Express server, route mounting, startup
├── cache.ts # In-memory TTL cache (Map-based)
└── routes/
├── reference.ts # /api/reference/* (8 endpoints)
├── sales.ts # /api/sales/* (7 endpoints)
├── supply.ts # /api/supply/* (5 endpoints)
├── inventory.ts # /api/inventory/* (2 endpoints)
├── demand.ts # /api/demand/* (3 endpoints)
└── costs.ts # /api/costs/* (4 endpoints)
packages/shared/src/clients/
└── data-api.ts # DataApiClient + all typed response interfaces
Endpoint Summary
| Group | Count | Primary Source |
|---|---|---|
| Reference | 8 | Airtable + PostgreSQL |
| Sales | 7 | Airtable |
| Supply | 5 | PostgreSQL + Airtable |
| Inventory | 2 | Airtable |
| Demand | 3 | Airtable |
| Costs | 4 | PostgreSQL + Airtable |
| Total | 29 |