FMCSA Data Pipeline

The FMCSA data pipeline captures a full snapshot of each carrier's FMCSA record once per day and stores it in an append-only time series. This powers the carrier alert diff (see Carrier Alerts), historical trend analysis, and MCP tool access for AI-assisted underwriting.

Storage — fmcsa_snapshots Table

Every successful FMCSA fetch for a carrier produces a row in fmcsa_snapshots. Rows are never updated or deleted — the table is a pure time series.

CREATE TABLE fmcsa_snapshots (
  id              varchar(26) PRIMARY KEY,   -- ULID
  org_id          varchar(26) NOT NULL,
  carrier_id      varchar(26) NOT NULL,
  dot_number      varchar(20) NOT NULL,
  snapshot_date   date NOT NULL,
  endpoint        enum('carriers','basics','oos') NOT NULL,
  payload         json NOT NULL,             -- raw FMCSA response
  fetched_at      timestamp NOT NULL,
  INDEX idx_fmcsa_snapshots_carrier_date (carrier_id, snapshot_date DESC),
  INDEX idx_fmcsa_snapshots_dot_date (dot_number, snapshot_date DESC)
);

Three rows are inserted per carrier per day — one for each of the three FMCSA endpoints.

note

snapshot_date is set to the cron run date (UTC), not fetched_at. This makes it safe to backfill or re-run a cron for a given date without creating duplicate trend data — inserts use INSERT IGNORE keyed on (carrier_id, snapshot_date, endpoint).

Three Endpoints Per Carrier

Each daily run calls three FMCSA QCMobile API endpoints for every carrier:

Endpoint	Data Captured
GET /carriers/:dot	Legal name, DBA, authority status, insurance on file, physical address
GET /carriers/:dot/basics	CSA BASIC percentile scores (7 BASICs), investigation history
GET /carriers/:dot/oos	Out-of-service rates (vehicle, driver, hazmat) vs. national averages

See FMCSA API Integration for authentication details, field mappings, and rate limit handling.

History API Routes

Snapshot History

Returns paginated snapshots for a single carrier, newest first.

GET /v1/fmcsa-history?carrierId=car_01J8...&endpoint=basics&limit=30
Authorization: Bearer <token>

Query parameters:

Parameter	Type	Description
carrierId	string	Required. The internal carrier ID.
dotNumber	string	Alternative to carrierId — look up by DOT number.
endpoint	string	Filter to one endpoint: carriers, basics, or oos.
from	date	Start of date range (ISO 8601, inclusive).
to	date	End of date range (ISO 8601, inclusive).
limit	number	Page size (default 30, max 90).
cursor	string	Pagination cursor from previous response.

Trend Data

Aggregates key metrics across snapshots into a time series suitable for charting.

GET /v1/fmcsa-history/trend?carrierId=car_01J8...&metric=oos_vehicle&days=90
Authorization: Bearer <token>

metric options:

Metric	Source Endpoint	Description
oos_vehicle	oos	Vehicle out-of-service rate (%)
oos_driver	oos	Driver out-of-service rate (%)
oos_hazmat	oos	Hazmat out-of-service rate (%)
basic_hos	basics	HOS Compliance BASIC percentile
basic_unsafe	basics	Unsafe Driving BASIC percentile
basic_driver	basics	Driver Fitness BASIC percentile
basic_vehicle	basics	Vehicle Maintenance BASIC percentile

Response:

{
  "carrierId": "car_01J8...",
  "dotNumber": "1234567",
  "metric": "oos_vehicle",
  "unit": "percent",
  "nationalAverage": 21.4,
  "series": [
    { "date": "2026-01-01", "value": 18.2 },
    { "date": "2026-01-08", "value": 19.7 }
  ]
}

Data Stats

Returns coverage statistics — how many carriers have snapshots, how far back data goes, and whether any carriers are missing recent snapshots.

GET /v1/fmcsa-history/stats?orgId=org_01J8...
Authorization: Bearer <token>

Response:

{
  "orgId": "org_01J8...",
  "totalCarriers": 142,
  "carriersWithSnapshots": 138,
  "oldestSnapshot": "2025-09-01",
  "newestSnapshot": "2026-03-24",
  "staleCarriers": [
    { "carrierId": "car_01J8...", "dotNumber": "9876543", "lastSnapshotDate": "2026-03-18" }
  ]
}

staleCarriers lists carriers whose most recent snapshot is more than 2 days old — indicating a fetch failure that should be investigated.

MCP Tool Integration

The MCP server exposes three tools that give AI agents direct access to FMCSA snapshot data. These are used by the underwriting AI assistant to answer questions about carrier safety history without requiring the underwriter to navigate to the workbench.

get_fmcsa_snapshot

Returns the most recent (or a specific date's) snapshot for a carrier.

// Tool input schema
{
  dotNumber: string;       // USDOT number
  endpoint: 'carriers' | 'basics' | 'oos';
  date?: string;           // ISO date — defaults to most recent
}

get_fmcsa_trend

Returns a time series of a single metric for use in AI analysis or narrative generation.

{
  dotNumber: string;
  metric: FmcsaTrendMetric; // one of the metric keys in the table above
  days: number; // lookback window (max 365)
}

get_fmcsa_data_stats

Returns coverage stats for a given organization. Useful for compliance review agents checking data completeness.

{
  orgId: string;
}

note

MCP tool calls are authenticated using the system service account JWT and are scoped to the requesting organization's carriers. Cross-org access requires superadmin elevation.

Data Retention

FMCSA snapshots are retained indefinitely — the time series is the source of truth for carrier safety history and supports actuarial trend analysis, underwriting review, and regulatory audit requests. There is no TTL or purge policy.