🗄️ EXSPEEDITE CHECKPOINT — Entity Relationship Diagram

checkpoint_db · visibility relationship map · Updated 2026-06-27 · CheckPoint v1.31 Product Polish

Multi-tenant hub

Load lifecycle

Messaging / audit

Configuration

Hard FK

Composite FK (company_id + load_number)

Soft reference (no DB constraint)

Logical load key: (company_id, load_number) — Not a declared foreign key. Every load lifecycle query uses this two-column pair as a compound WHERE / JOIN. The application enforces this relationship in PHP; the database does not enforce it as a constraint.

Private assignment extension: track_driver_assignment_tokens soft-joins to track_load_snapshots by (company_id, load_number) and may also be referenced by track_load_snapshots.driver_assignment_token_id. This supports driver_assignment_status = pending_assignment without exposing the driver's full phone to dispatcher UI.

2026 visibility addendum: newer visibility features add track_eta_settings, track_pod_photos, track_share_tokens, track_geofence_events, track_exception_alerts, track_webhook_subscriptions, and track_webhook_delivery_log. Delivery lifecycle fields now include eta_at, reminder_at, eta_reminder_sent_at, pod_submitted, and pod_submitted_at. Integration Events are outbound 214-style email/JSON visibility messages, while track_dot_feed_items remains the cached DOT/feed overlay source.

track_users

Multi-tenant hub

Column	Type	Flags
id	int	PK
company_name	varchar(150)	NN
password	varchar(255)	NN
is_active	tinyint
timezone	varchar(60)	NN
contact_email	varchar(150)
contact_phone	varchar(30)
created_at	datetime

track_carriers

Carrier roster

Column	Type	Flags
id	int	PK
company_id	int	FK
carrier_name	varchar(150)	NN
dot_number	varchar(20)
mc_number	varchar(20)
source	enum
is_active	tinyint
created_at	datetime

track_carrier_drivers

Driver roster

Column	Type	Flags
id	int	PK
carrier_id	int	FK
company_id	int	FK
driver_name	varchar(150)	NN
driver_phone	varchar(30)	NN
driver_email	varchar(150)
is_active	tinyint
created_at	datetime

track_load_snapshots

Load header — lifecycle root

Column	Type	Flags
id	int	PK
company_id	int	FK
load_number	varchar(50)	NN
carrier_name	varchar(150)
driver_name	varchar(150)
driver_phone	varchar(30)	NN
status	enum
sms_fallback_used	tinyint
last_reminder_sent_at	datetime
created_at	datetime

track_load_stops

Stop rows — composite FK* cascade

Column	Type	Flags
id	int	PK
load_number	varchar(50)	FK*
company_id	int	FK*
stop_sequence	int	NN
milestone_type	enum	NN
location_name	varchar(150)
city	varchar(100)	NN
state	varchar(50)	NN
scheduled_at	datetime
sms_sent_at	datetime

track_tokens

Driver tracking tokens

Column	Type	Flags
id	int	PK
token	varchar(64)	NN
load_snapshot_id	int
load_number	varchar(50)	FK*
company_id	int	FK*
expires_at	datetime	NN
status	enum
created_at	datetime

track_location_pings

GPS trail — append only

Column	Type	Flags
id	bigint	PK
token_id	int	FK
load_number	varchar(50)	NN
company_id	int	FK
stop_sequence	int	NN
milestone_type	enum	NN
lat	decimal(10,7)	NN
lng	decimal(10,7)	NN
accuracy	decimal(8,2)
timestamp	datetime	NN
received_at	datetime
address	text
raw_payload	json	NN

track_messages

Outbound SMS / email

Column	Type	Flags
id	bigint	PK
load_number	varchar(50)
company_id	int	FK
stop_sequence	int
message_type	enum	NN
channel	enum	NN
recipient	varchar(150)	NN
provider	varchar(50)
status	varchar(50)
body	text
sent_at	datetime

track_event_log

Audit log — no FK constraints

Column	Type	Flags
id	bigint	PK
load_number	varchar(50)	SOFT
company_id	int	SOFT
event_type	varchar(50)	NN
event_channel	varchar(30)
event_status	varchar(30)
message	text
actor	varchar(100)
ip_address	varchar(45)
created_at	datetime

track_api_clients

API bearer tokens

Column	Type	Flags
id	int	PK
company_id	int	FK
client_name	varchar(150)	NN
bearer_token_hash	varchar(255)	NN
is_active	tinyint
created_at	datetime
last_used_at	datetime

app_credentials

Per-tenant config

Column	Type	Flags
id	int	PK
service_name	varchar(100)	NN
credential_key	varchar(255)	NN
credential_value	text	NN
description	text
is_active	tinyint
created_at	datetime
updated_at	datetime

system_credentials

Global config — no company scope

Column	Type	Flags
id	int	PK
service_name	varchar(50)	NN
credential_key	varchar(255)	NN
credential_value	text	NN
updated_at	datetime

Load lifecycle chain

snapshots → stopscompany_id + load_number CASCADE DELETE HIGH

snapshots → tokenscompany_id + load_number · multiple tokens per load possible HIGH

snapshots → assignment_tokenscompany_id + load_number · private driver assignment link HIGH

tokens → pingstoken_id = track_tokens.id · clearest ID join in codebase HIGH

snapshots → pingscompany_id + load_number · map trail & ping counts HIGH

Private phone rules

assignment tokentrack_driver_assignment_tokens.token powers /carrier/assign.php.

masked phone onlyDashboard/report queries should use driver_phone_masked or Phone on file, never raw phone.

Messaging & audit

snapshots → event_logcompany_id + load_number · soft ref · survives cleanup HIGH

snapshots → messagescompany_id + load_number · reminder cron · DDL not found MED

Tenant hub

track_users.idIS the company identifier. No company_id column on this table. HIGH

No declared DB FKsApp does not rely on declared foreign keys in PHP. All relationships are application-enforced. APP-LEVEL

Configuration

app_credentialsScoped by company_id per tenant. No direct SQL join to track_users found. HIGH

system_credentialsGlobal — no company scope. Twilio / SendGrid keys only. HIGH