Skip to main content
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:
EndpointData Captured
GET /carriers/:dotLegal name, DBA, authority status, insurance on file, physical address
GET /carriers/:dot/basicsCSA BASIC percentile scores (7 BASICs), investigation history
GET /carriers/:dot/oosOut-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:
ParameterTypeDescription
carrierIdstringRequired. The internal carrier ID.
dotNumberstringAlternative to carrierId — look up by DOT number.
endpointstringFilter to one endpoint: carriers, basics, or oos.
fromdateStart of date range (ISO 8601, inclusive).
todateEnd of date range (ISO 8601, inclusive).
limitnumberPage size (default 30, max 90).
cursorstringPagination 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:
MetricSource EndpointDescription
oos_vehicleoosVehicle out-of-service rate (%)
oos_driveroosDriver out-of-service rate (%)
oos_hazmatoosHazmat out-of-service rate (%)
basic_hosbasicsHOS Compliance BASIC percentile
basic_unsafebasicsUnsafe Driving BASIC percentile
basic_driverbasicsDriver Fitness BASIC percentile
basic_vehiclebasicsVehicle 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.