VIA Platform Database Schema

Total Tables: 10 ORM: SQLAlchemy Database: PostgreSQL + TimescaleDB

Tables

User (users)

Column	Type	Attributes
user_id	Integer	🔑 Primary Key
email	String	—
password_hash	String	—
name	String	—
role	String	—
status	String	—
company_id	Integer	—
created_at	DateTime	—
updated_at	DateTime	—

---

RefreshToken (refresh_tokens)

Column	Type	Attributes
id	Integer	🔑 Primary Key
token	String	—
user_id	Integer	🔗 Foreign Key
expires_at	DateTime	—
revoked	Boolean NOT NULL	📝 Default
created_at	DateTime	—

---

PasswordResetToken (password_reset_tokens)

Column	Type	Attributes
id	Integer	🔑 Primary Key
token	String	—
user_id	Integer	🔗 Foreign Key
expires_at	DateTime	—
used	Boolean NOT NULL	📝 Default
created_at	DateTime	—

---

Company (companies)

Column	Type	Attributes
company_id	Integer	🔑 Primary Key
name	String	—
rfc	String	—
status	String	—
contact_email	String	—
contact_phone	String	—
address	String	—
city	String	—
state	String	—
country	String	—
fleet_size	String	—
total_violations	String	—
compliance_score	String	—
created_at	DateTime	—
updated_at	DateTime	—

---

Truck (trucks)

Column	Type	Attributes
truck_id	Integer	🔑 Primary Key
company_id	Integer	🔗 Foreign Key
registration	String	—
truck_type	String	—
status	String	—
brand	String	—
model	String	—
year	String	—
current_driver_id	Integer	🔗 Foreign Key
current_location	String	—
capacity_tons	String	—
gps_status	String	—
created_at	DateTime	—
updated_at	DateTime	—

---

Driver (drivers)

Column	Type	Attributes
driver_id	Integer	🔑 Primary Key
company_id	Integer	🔗 Foreign Key
name	String	—
license_number	String	—
status	String	—
phone	String	—
email	String	—
total_trips	String	—
total_violations	String	—
safety_score	String	—
speed_violations	String	—
created_at	DateTime	—
updated_at	DateTime	—

---

Terminal (terminals)

Column	Type	Attributes
terminal_id	Integer	🔑 Primary Key
name	String	—
port	String	—
status	String	—
location	String	—
city	String	—
country	String	—
contact_email	String	—
contact_phone	String	—
operating_hours	String	—
recent_sequences	String	—
created_at	DateTime	—
updated_at	DateTime	—

---

Patio (patios)

Column	Type	Attributes
patio_id	Integer	🔑 Primary Key
name	String	—
zone	String	—
status	String	—
location	String	—
city	String	—
country	String	—
contact_email	String	—
contact_phone	String	—
total_capacity	String	—
available_capacity	String	—
capacity_history	String	—
created_at	DateTime	—
updated_at	DateTime	—

---

Convoy (convoys)

Column	Type	Attributes
convoy_id	Integer	🔑 Primary Key
convoy_code	String	—
destination_patio_id	Integer	🔗 Foreign Key
terminal_id	Integer	🔗 Foreign Key
sequence_id	Integer	—
leader_truck_id	Integer	🔗 Foreign Key
truck_count	Integer NOT NULL	📝 Default
container_count	Integer NOT NULL	📝 Default
departure_time	DateTime	—
eta	DateTime	—
actual_departure	DateTime	—
actual_arrival	DateTime	—
distance_km	Float	—
max_speed_kmh	Integer	📝 Default
travel_time_min	Float	—
loading_time_min	Integer	—
total_time_min	Float	—
current_speed_kmh	Float	—
current_lat	Float	—
current_lng	Float	—
throttle_active	Boolean NOT NULL	📝 Default
status	String	—
notes	Text	—
created_at	DateTime	—
updated_at	DateTime	—

---

ConvoyTruck (convoy_trucks)

Column	Type	Attributes
id	Integer	🔑 Primary Key
convoy_id	Integer	🔗 Foreign Key
truck_id	Integer	🔗 Foreign Key
position	Integer NOT NULL	📝 Default
is_leader	Boolean NOT NULL	📝 Default
driver_id	Integer	🔗 Foreign Key
container_numbers	JSON	📝 Default
truck_type	String	—
sequence_unit_id	Integer	—
status	String	—
created_at	DateTime	—

---

Relationships

Foreign key relationships are defined using SQLAlchemy's ForeignKey and relationship().

Common Patterns:

• One-to-Many: Parent uses relationship() with back_populates
• Many-to-One: Child uses ForeignKey() column
• Many-to-Many: Junction table with two ForeignKey columns

---

Last Updated: 2026-02-28 11:36:05 Auto-generated from SQLAlchemy models Models Location: {models_file.name}