MySQLEXSPEEDITE CHECKPOINT — Schema Reference

checkpoint_db · visibility schema reference · Updated 2026-07-03 · CheckPoint v1.31+ Asset GPS

Logical load key — application-level, not a declared FK

(company_id, load_number)

The application treats this two-column pair as the primary join key across the entire load lifecycle. It is not enforced as a foreign key at the database level — the app does not rely on declared FKs in PHP. Instead, every relevant SQL query uses both columns as a compound WHERE / JOIN condition.

This is confirmed by the repository's own dashboard documentation and repeated in every load-init, ping, close-load, and reminder-cron code path.

track_load_snapshots track_load_stops track_tokens track_location_pings track_event_log track_messages track_driver_assignment_tokens

Private driver assignment addendum

Purpose: allows a load to be created without exposing the driver's phone number to the broker or dispatcher. track_driver_assignment_tokens stores the tokenized assignment link and masked phone display. track_load_snapshots.driver_assignment_status shows whether the load is still waiting for private assignment or has an assigned driver.

Privacy rule: full phone is used only for SMS dispatch. Dashboard and reports should show driver_phone_masked, Phone on file, or last four digits only.

2026 visibility feature addendum

ETA Intelligence: track_eta_settings stores per-tenant reminder configuration used by EtaService and cron/send_eta_reminders.php. Stop records now include eta_at, reminder_at, and eta_reminder_sent_at.

POD capture: track_pod_photos stores delivery proof metadata uploaded via /v1/tracking/pod-upload.php. Delivery stop fields include pod_submitted and pod_submitted_at. Company POD requirement is in app_credentials service pod key required_on_delivery.

Share tracking: track_share_tokens stores public-link scope controls consumed by ShareTokenService and /v1/share/data.php. Public page /share.php is branded "Powered by Exspeedite CheckPoint".

Arrival Zone and exceptions: track_geofence_events records automatic arrival-zone hits from driver GPS pings. track_exception_alerts stores missed ETA, no-update, geofence, and POD follow-up alerts raised by ExceptionService and cron/detect_exceptions.php.

Integration Events: track_webhook_subscriptions stores dispatcher-configured 214-style email and JSON webhook outputs. track_webhook_delivery_log records delivery attempts. IntegrationEventService fires soft-fail events for dispatch, pickup, arrival, delivery, POD, ETA, geofence, and exception milestones.

Rich load details: track_load_snapshots now stores optional reference, commodity, freight, equipment, operational, documentation, special-instruction, broker context fields, and stable tenant custom fields user1 through user10. track_load_stops stores optional appointment windows, facility hours, dock/access rules, load/unload type, and driver-safe stop instructions.

Custom field labels: per-company display names for user1 through user10 live in app_credentials service custom_fields keys user1_label through user10_label. Blank labels default to User 1 through User 10.

Asset GPS / ELD providers: per-company Samsara and Motive API keys live in app_credentials services gps_samsara and gps_motive (key api_token). Active provider selection uses service gps_provider key active_provider (samsara | motive). Connection settings and refresh cadence live in gps_provider_connections. Admin UI: /gps-integrations.php. Developer docs: /developers.php#asset-gps.

PK Primary key

FK Foreign key (single column)

FK* Composite FK — part of a two-column key

SOFT Soft reference — no constraint enforced

NN NOT NULL

HIGH Relationship confidence (code-proven)

MED Relationship confidence (partial evidence)

Core — multi-tenant anchor

track_users Hub — every other track_ table FK's here via company_id

Column	Type	Flags	Notes
id	int	PKNN	Auto-increment
company_name	varchar(150)	NN
password	varchar(255)	NN	Hashed
is_active	tinyint		Default 1
timezone	varchar(60)	NN	IANA string, e.g. America/Chicago. For alert formatting only — never for storage
contact_email	varchar(150)		Dispatcher email — required for driver-silent alerts
contact_phone	varchar(30)		Reserved for future SMS alert feature
created_at	datetime		Default CURRENT_TIMESTAMP

Carrier & driver roster

track_carriers

Column	Type	Flags	Notes
id	int	PKNN
company_id	int	FKNN	→ track_users.id
carrier_name	varchar(150)	NN
legal_name	varchar(150)
dot_number	varchar(20)		Indexed
mc_number	varchar(20)		Indexed
city	varchar(100)
state	varchar(10)
source	enum(manual,fmcsa,exspeedite,mcleod)		Default: manual
external_id	varchar(100)		TMS reference ID
is_active	tinyint		Default 1
created_at	datetime		Default CURRENT_TIMESTAMP

track_carrier_drivers

Column	Type	Flags	Notes
id	int	PKNN
carrier_id	int	FKNN	→ track_carriers.id
company_id	int	FKNN	→ track_users.id
driver_name	varchar(150)	NN
driver_phone	varchar(30)		Nullable for private-link loads until the carrier/driver submits the private assignment page. Full value is send-only.
driver_phone_masked	varchar(30)		Dispatcher-safe display value, for example Phone on file or *-*-1234.
driver_assignment_status	varchar(40)	NN	Default assigned. Private flow uses pending_assignment until assign-driver.php completes.
driver_assignment_token_id	int	SOFT	Soft reference to track_driver_assignment_tokens.id for private-link loads.
driver_email	varchar(150)
is_active	tinyint		Default 1
created_at	datetime		Default CURRENT_TIMESTAMP

track_driver_assignment_tokens Private carrier/driver assignment links. Soft joins by company_id + load_number.

Column	Type	Flags	Notes
id	int	PKNN
company_id	int	FK*NN	Tenant scope. Used with load_number for soft load join.
load_number	varchar(80)	FK*NN	Soft join to track_load_snapshots(company_id, load_number).
token	varchar(128)	NN	Unique public token for /carrier/assign.php.
carrier_name	varchar(180)		Display context on assignment page.
assignment_status	varchar(40)	NN	pending_assignment or assigned.
assigned_by	varchar(40)		private_link for carrier/driver submitted assignments.
driver_name	varchar(120)		Captured on private assignment submit.
driver_phone_encrypted	text		Send-only storage. Never display raw phone in dashboard/report UI.
driver_phone_masked	varchar(30)		Safe display value.
driver_email	varchar(180)		Optional reference only.
assigned_at	datetime		UTC submit time.
active	tinyint	NN	Default 1. Completed/cancelled loads should no longer accept assignment.
created_at	datetime		Default CURRENT_TIMESTAMP
updated_at	datetime		Set when assignment is submitted.

Load lifecycle — snapshots → stops → tokens → pings

track_load_snapshots Load header/master row. Unique key on (company_id, load_number) — the logical load key root

Column	Type	Flags	Notes
id	int	PKNN
company_id	int	FKNN	→ track_users.id · join proven: `u.id = s.company_id` in reminder cron
load_number	varchar(50)	NN	Unique key with company_id. One snapshot per (company_id, load_number). Load-init checks this before insert/update.
carrier_name	varchar(150)		Denormalized snapshot — not a live FK to track_carriers
driver_name	varchar(150)		Denormalized snapshot
driver_phone	varchar(30)	NN
driver_email	varchar(150)
reference_numbers	text		BOL, PO, shipment, or customer reference numbers
commodity_description	text		Freight description
total_weight_lbs	decimal(10,2)		Total weight in pounds
piece_count	int		Piece count
pallet_count	int		Pallet or skid count
freight_dimensions	text		Dimensions, cube, or stackability context
freight_class	varchar(40)		LTL freight class
nmfc_number	varchar(80)		NMFC reference
special_properties	text		Hazmat, reefer, fragile, odor-sensitive, no-stack, or other properties
trailer_type	varchar(80)		Dry van, reefer, flatbed, length, or similar equipment type
equipment_specs	text		Air-ride, liftgate, trailer specs, or equipment notes
driver_assist	text		Driver assist requirements
securement_requirements	text		Tarping, chains, straps, blocking, or other securement
onboard_tools	text		Pallet jack, PPE, load bars, straps, or other tools
detention_layover_terms	text		Free time and layover terms
tracking_expectations	text		Ping, photo, or update expectations
routing_hos_notes	text		Routing, HOS, or team-driver notes
team_driver_required	tinyint(1)	NN	Default 0
required_paperwork	text		BOL, POD, seals, weight tickets, signatures, photos
special_instructions	text		Customer/site-specific rules and security protocols
broker_name	varchar(180)		Dispatcher/internal broker context; excluded from driver, public share, and customer-facing Integration Events payloads
broker_contact	varchar(255)		Dispatcher/internal primary contact for load questions; excluded from customer-facing visibility outputs
user1 ... user10	text		Tenant-configurable load-level custom fields for customer-safe business context. Payload keys remain stable; labels are stored in app_credentials/custom_fields.
status	enum(active,completed,cancelled)		Default: active
sms_fallback_used	tinyint		Default 0
last_reminder_sent_at	datetime
created_at	datetime		Default CURRENT_TIMESTAMP

track_load_stops CASCADE DELETE from track_load_snapshots

Column	Type	Flags	Notes
id	int	PKNN
load_number	varchar(50)	FK*NN	Composite FK with company_id → track_load_snapshots(company_id, load_number)
company_id	int	FK*FKNN	Also standalone FK → track_users.id
stop_sequence	int	NN	1-based ordering within the load; controls driver workflow order and supports multiple pickups/deliveries/stop-offs
milestone_type	enum(pickup,transit,delivery)	NN	Use transit for Stop / Stop-off, crossdock, partial delivery, or other intermediate stops
location_name	varchar(150)
address1	varchar(200)
city	varchar(100)	NN
state	varchar(50)	NN
zip	varchar(20)
scheduled_at	datetime		Appointment window start
time_window_start	datetime		Exact appointment or FCFS window start
time_window_end	datetime		Exact appointment or FCFS window end
appointment_type	varchar(80)		Appointment, FCFS, call-for-appointment, or other process
load_unload_type	varchar(80)		Live load/unload, drop, preloaded, or similar handling type
facility_hours	varchar(255)		Facility receiving/shipping hours
appointment_process	text		Call-ahead or scheduling process
dock_instructions	text		Dock and check-in details
access_rules	text		Gate, access, parking, PPE, or security rules
stop_restrictions	text		Stop-specific restrictions
driver_instructions	text		Driver-safe stop instructions
sms_sent_at	datetime		Timestamp of advance SMS for this stop

track_tokens Driver tracking link tokens. Unique on token varchar(64). No DB uniqueness enforced on (company_id, load_number) — app may create multiple tokens per load.

Column	Type	Flags	Notes
id	int	PKNN
token	varchar(64)	NN	Unique. SHA-256 hex. Sent to driver via SMS link. Ping endpoint selects this to get token.id for pings.
load_snapshot_id	int		Optional soft ref to track_load_snapshots.id — not used by primary join path
load_number	varchar(50)	FK*NN	Composite FK with company_id → track_load_snapshots(company_id, load_number). Index on (company_id, load_number, status) used by dashboard join and close-load expiry.
company_id	int	FK*FKNN	Also standalone FK → track_users.id
expires_at	datetime	NN	Hard expiry — app_credentials.token_expiry_days controls generation
status	enum(active,expired)		Default: active
created_at	datetime		Default CURRENT_TIMESTAMP

track_location_pings Append-only GPS trail. bigint PK — high volume expected

Column	Type	Flags	Notes
id	bigint	PKNN
token_id	int	FKNN	→ track_tokens.id — inserted directly from token lookup in ping endpoint. Index confirmed. No declared DB FK constraint found.
load_number	varchar(50)	NN	Denormalized for query performance. Indexes on (company_id, load_number, timestamp) and (company_id, load_number, id) used by dashboard, map trail, and ping-count queries.
company_id	int	FKNN	→ track_users.id
stop_sequence	int	NN	Which stop this ping is headed toward
milestone_type	enum(pickup,transit,delivery)	NN	transit means Stop / Stop-off in dispatcher/API docs
lat	decimal(10,7)	NN
lng	decimal(10,7)	NN
accuracy	decimal(8,2)		Meters
accuracy_quality	enum(good,fair,poor)
ip_address	varchar(45)		IPv4 or IPv6
user_agent	text
timestamp	datetime	NN	Device time — may differ from received_at
received_at	datetime		Default CURRENT_TIMESTAMP — server receipt time
address	text		Reverse-geocoded via Nominatim
raw_payload	json	NN	Full browser Geolocation API payload

Messaging & event log

track_messagesNo DDL found in repo — relationship proven by cron insert behavior only

Column	Type	Flags	Notes
id	bigint	PKNN
load_number	varchar(50)		Written from track_load_snapshots.load_number by reminder cron. No FK constraint — messages survive load deletion.
company_id	int	FKNN	→ track_users.id
stop_sequence	int
message_type	enum(initial,advance,reminder,manual_reminder, completion,email_fallback,dispatcher_alert)	NN
channel	enum(sms,email)	NN
recipient	varchar(150)	NN	Phone number or email address
provider	varchar(50)		twilio / sendgrid
provider_message_id	varchar(150)		External ID for delivery status lookup
status	varchar(50)		Provider delivery status
body	text		Full message text sent
sent_at	datetime		Default CURRENT_TIMESTAMP

track_event_log Audit log — NO FK constraints. Index on (company_id, load_number, created_at). Survives load/company cleanup by design.

Column	Type	Flags	Notes
id	bigint	PKNN
load_number	varchar(50)	SOFT	Soft ref — written by load-init, ping endpoint (ping_received, load_completed, sms_sent), and close-load (manual_close) using token-derived value
company_id	int	SOFT	Soft ref — no FK enforced. Scoped by session company_id at insert time.
event_type	varchar(50)	NN
event_channel	varchar(30)
event_status	varchar(30)
message	text
actor	varchar(100)		User or system that triggered the event
ip_address	varchar(45)
user_agent	text
raw_payload	json
created_at	datetime		Default CURRENT_TIMESTAMP

API access

track_api_clients

Column	Type	Flags	Notes
id	int	PKNN
company_id	int	FKNN	→ track_users.id — proven by setup script: inserts track_users row, uses returned ID here. No declared DB FK found in repo.
client_name	varchar(150)	NN
bearer_token_hash	varchar(255)	NN	Hashed bearer token for webhook ingest
is_active	tinyint		Default 1
created_at	datetime		Default CURRENT_TIMESTAMP
last_used_at	datetime		Updated on each authenticated request

GPS Provider Integrations — asset / ELD (company-scoped)

gps_provider_connections Unique on (company_id, provider). One row per company per provider (samsara, motive). Admin UI: /gps-integrations.php

Column	Type	Flags	Notes
id	int	PK
company_id	int	FKNN	→ companies.company_id
provider	varchar(50)	NN	samsara \| motive (peoplenet planned)
enabled	tinyint(1)		Provider connection on/off
auth_mode	varchar(40)		api_token (Samsara) \| api_key (Motive)
api_base_url	varchar(255)		Region/base URL for provider API
region	varchar(20)		us \| eu \| ca \| custom (Samsara)
refresh_mode	varchar(20)		manual \| scheduled
refresh_interval_minutes	int		10, 15, 30, or 60 when scheduled
status	varchar(40)		not_tested \| saved \| verified \| connected \| error
last_poll_at	datetime		Last scheduled ELD poll
last_manual_refresh_at	datetime		Last manual Refresh ELD GPS

gps_vehicle_mappings Unique on (company_id, provider, provider_vehicle_id). Maps TMS tractor to provider vehicle.

Column	Type	Flags	Notes
company_id	int	FK	Tenant scope
provider	varchar(50)		samsara \| motive
tms_vehicle_id	varchar(100)		Matches initialize tms_vehicle_id / power_unit
provider_vehicle_id	varchar(100)		Provider fleet vehicle ID
provider_vehicle_name	varchar(190)		Display name from provider sync
license_plate	varchar(60)		Optional match aid

gps_vehicle_telemetry Unique on (company_id, provider, provider_vehicle_id). Latest lat/lng snapshot per provider vehicle.

Column	Type	Notes
company_id	int	Tenant scope
provider	varchar(50)	samsara \| motive
lat / lng	decimal	Map marker coordinates
freshness_status	varchar(30)	current \| stale \| offline flags
provider_event_time	datetime	Provider-reported GPS time (UTC)

gps_poll_cursors Unique on (company_id, provider, feed_name). Feed cursor state for scheduled polling.

Column	Type	Notes
company_id	int	Tenant scope
provider	varchar(50)	samsara (V1 cron)
feed_name	varchar(80)	e.g. vehicle_stats_gps
cursor_value	varchar(500)	Provider feed pagination cursor

admin_user_company_access Unique on (admin_user_id, company_id). Controls workspace switcher + GPS Integrations multi-tenant overview visibility. Admin UI: /user-access.php

Column	Type	Flags	Notes
admin_user_id	int	FK	→ admin_users.id
company_id	int	FK	→ companies.company_id
role	varchar(40)		admin \| dispatcher per company
can_dispatch	tinyint(1)		Dispatch permission for that company

Configuration — no FK relationships

app_credentials Unique on (company_id, service_name, credential_key) — scoped per tenant. No direct FK join to track_users found in credential queries.

Column	Type	Flags	Notes
id	int	PKNN
company_id	int	FKNN	→ companies.company_id — tenant scope for every row
service_name	varchar(100)	NN	pod, custom_fields, continuous_tracking, routing, gps_samsara, gps_motive, gps_provider, dot_layers
credential_key	varchar(255)	NN
credential_value	text	NN
description	text
is_active	tinyint(1)		Default 1
created_at	datetime		Default CURRENT_TIMESTAMP
updated_at	datetime		ON UPDATE CURRENT_TIMESTAMP

system_credentials Unique on (service_name, credential_key)

Column	Type	Flags	Notes
id	int	PKNN
service_name	varchar(50)	NN	twilio, sendgrid
credential_key	varchar(255)	NN
credential_value	text	NN
updated_at	datetime		Default + ON UPDATE CURRENT_TIMESTAMP

Relationship summary — code-proven

track_users → everything (company_id hub) · No declared DB FK found; relationships proven by code joins and setup scripts

track_users⟶track_load_snapshotstrack_users.id = s.company_idHIGH

Proven: reminder cron LEFT JOIN track_users u ON u.id = s.company_id. Dashboard docs confirm track_users has no company_id column — track_users.id IS the company identifier.

track_users⟶track_api_clientscompany_id = track_users.idHIGH

Proven: setup script inserts track_users, uses returned ID as track_api_clients.company_id. No declared DB FK found in repo.

track_users⟶track_carriers / track_carrier_drivers / track_tokens / track_location_pings / track_messagescompany_idHIGH

All tables scoped to company_id matching track_users.id. No declared DB FK constraints confirmed in repo — app relies on application-level enforcement.

Load lifecycle chain — (company_id, load_number) logical key

track_load_snapshots⟶track_load_stopscompany_id + load_number — CASCADE DELETEHIGH

Load-init deletes existing stops then reinserts by same (company_id, load_number). Ping endpoint validates stops by (company_id, load_number, stop_sequence, milestone_type). Unique key on (company_id, load_number, stop_sequence) confirmed in schema.

track_load_snapshots⟶track_tokenscompany_id + load_numberHIGH

Dashboard joins active tokens to snapshots on both load_number and company_id with status = 'active'. Token-expiration cron and close-load endpoint both expire tokens by (company_id, load_number). DB uniqueness not enforced on (company_id, load_number) — multiple tokens per load are possible.

track_tokens⟶track_location_pingstoken_id = track_tokens.idHIGH

Clearest ID-based join in the codebase. Ping endpoint: SELECT id FROM track_tokens WHERE token = ?, then inserts that id into track_location_pings.token_id. Index on token_id confirmed. No declared DB FK constraint found.

track_load_snapshots⟶track_location_pingscompany_id + load_numberHIGH

Dashboard counts pings per snapshot by (company_id, load_number). Map-data endpoint builds latest-ping subquery grouped by (company_id, load_number), joined to snapshots on both fields. Trail query filters track_location_pings by (company_id, load_number) directly.Note: dashboard/data.php joins only on load_number in outer join; company scoping is parameter-driven in that specific query. map-data.php joins explicitly on both columns.

track_load_snapshots⟶track_event_logcompany_id + load_number (soft)HIGH

Load-init, ping endpoint (ping_received, load_completed, sms_sent), and close-load (manual_close) all insert event rows using the same token-derived (company_id, load_number). Index on (company_id, load_number, created_at) confirmed in schema.

track_load_snapshots⟶track_messagescompany_id + load_numberMED

Reminder cron inserts into track_messages using (company_id, load_number) sourced from track_load_snapshots. Insert usage confirmed. track_messages DDL not found in repo — no schema-level relationship can be stated.

track_load_snapshots⟶track_driver_assignment_tokenscompany_id + load_number and optional token idHIGH

run_initialize() creates assignment tokens for private-link loads. assign-driver.php joins token rows to snapshots, masks phone display, creates a driver tracking token, and updates driver_assignment_status.

Carrier roster

track_carriers⟶track_carrier_driverscarrier_id + company_idMED

Carrier-drivers endpoint filters by carrier_id and company_id from session. Parent table for carrier_id not found in repo — cannot confirm carrier_id references track_carriers.id from schema alone.

Configuration scoping

app_credentials⟶track_users (implicit)company_id scopeHIGH

Unique key on (company_id, service_name, credential_key). Admin settings, track-init webhook lookup, and settings-save all filter by company_id. No direct SQL join to track_users found in credential queries — relationship is by convention, not enforced.

system_credentials⟶noneglobal — no company scopeHIGH

Global Twilio / SendGrid credentials keyed only by (service_name, credential_key). No company scoping in any credential query found.