JetZig Transportation Management System project - database layer
About a year ago, I got the itch to start trying to program in Zig, after 15 years of using Python. I wanted to try a language that was closer to the hardware and Zig seemed to be simpler than Rust.
I had a friend that was involved in the trucking industry, so I decided to create a project in Zig to help me learn the language while doing a practical exercise.
After using Tokamak for a couple practice sessions, I decided that I wanted something a bit closer to Django, that had a lot more built in features and would do much of the work server side.
I decided to use Jetzig
Database Schema
I modeled the whole system as a set of relational database tables and used
Jetzig’s built in database migrations to create the PostgreSQL database. In the
end, I came up with the following database tables and relationships, that was set
as my Schema in Jetzig.
Primary objects
The following objects exist in the system, with a DeliveryOrder being the main
object that links everything together. A DeliveryOrder is what a Carrier
(the company that owns trucks that deliver things) starts with. A
DeliveryOrder can contain multiple Bill of Ladings that are used by
the Carrier when they go to the Port to pick up the Containers that the
DeliveryOrder requires them to pick-up and deliver to a final Location.
pub const BillOfLading = jetquery.Model(
@This(),
"bill_of_ladings",
struct {
id: i32,
number: []const u8,
carrier_id: i32,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.carrier = jetquery.belongsTo(.Carrier, .{}),
.delivery_order_bill_of_ladings = jetquery.hasMany(.DeliveryOrderBillOfLading, .{}),
},
},
);
pub const Carrier = jetquery.Model(
@This(),
"carriers",
struct {
id: i32,
name: []const u8,
bol_prefix: []const u8,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.bill_of_ladings = jetquery.hasMany(.BillOfLading, .{}),
},
},
);
pub const Container = jetquery.Model(
@This(),
"containers",
struct {
id: i32,
container_number: []const u8,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.delivery_order_containers = jetquery.hasMany(.DeliveryOrderContainer, .{}),
},
},
);
pub const DeliveryOrderBillOfLading = jetquery.Model(
@This(),
"delivery_order_bill_of_ladings",
struct {
id: i32,
delivery_order_id: i32,
bill_of_lading_id: i32,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.delivery_order = jetquery.belongsTo(.DeliveryOrder, .{}),
.bill_of_lading = jetquery.belongsTo(.BillOfLading, .{}),
},
},
);
pub const DeliveryOrderContainer = jetquery.Model(
@This(),
"delivery_order_containers",
struct {
id: i32,
delivery_order_id: i32,
container_id: i32,
pickup_before: jetquery.DateTime,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.delivery_order = jetquery.belongsTo(.DeliveryOrder, .{}),
.container = jetquery.belongsTo(.Container, .{}),
},
},
);
pub const DeliveryOrder = jetquery.Model(
@This(),
"delivery_orders",
struct {
id: i32,
pickup_location_id: i32,
dropoff_location_id: i32,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.pickup_location = jetquery.belongsTo(.Location, .{ .foreign_key = "pickup_location_id" }),
.dropoff_location = jetquery.belongsTo(.Location, .{ .foreign_key = "dropoff_location_id" }),
.attachments = jetquery.hasMany(.Attachment, .{}),
.delivery_order_containers = jetquery.hasMany(.DeliveryOrderContainer, .{}),
.delivery_order_bill_of_ladings = jetquery.hasMany(.DeliveryOrderBillOfLading, .{}),
},
},
);
pub const Location = jetquery.Model(
@This(),
"locations",
struct {
id: i32,
name: []const u8,
address: []const u8,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
lat: f32,
lon: f32,
},
.{
.relations = .{
.ports = jetquery.hasMany(.Port, .{ .foreign_key = "location" }),
.pickup_delivery_orders = jetquery.hasMany(.DeliveryOrder, .{ .foreign_key = "pickup_location_id" }),
.dropoff_delivery_orders = jetquery.hasMany(.DeliveryOrder, .{ .foreign_key = "dropoff_location_id" }),
},
},
);
pub const Port = jetquery.Model(
@This(),
"ports",
struct {
id: i32,
location_id: i32,
},
.{
.relations = .{
.location = jetquery.belongsTo(.Location, .{ .foreign_key = "location" }),
},
},
);
Driver management
The TMS system also has a set of Users and Roles for different people to
log into the system. Drivers, have a specific Role that they are assigned when
they register.
pub const User = jetquery.Model(
@This(),
"users",
struct {
id: i32,
email: []const u8,
password_hash: []const u8,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.user_roles = jetquery.hasMany(.UserRole, .{}),
},
},
);
pub const Role = jetquery.Model(
@This(),
"roles",
struct {
id: i32,
name: []const u8,
},
.{
.relations = .{
.user_roles = jetquery.hasMany(.UserRole, .{}),
},
},
);
pub const UserRole = jetquery.Model(
@This(),
"user_roles",
struct {
id: i32,
user_id: i32,
role_id: i32,
},
.{
.relations = .{
.user = jetquery.belongsTo(.User, .{}),
.role = jetquery.belongsTo(.Role, .{}),
},
},
);
Job Assignment
The Carrier needs a way to assign each driver a set of jobs, to indicate what
containers they need to pick up and where they need to be delivered, via the
JobAssignment. It contains a link to the DeliveryOrderContainer which
contains all the information required for the driver to pick up and deliver the
container.
pub const JobAssignment = jetquery.Model(
@This(),
"job_assignments",
struct {
id: i32,
delivery_order_container_id: i32,
pickup_location_id: i32,
dropoff_location_id: i32,
assignment_date: jetquery.DateTime,
driver_id: i32,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.delivery_order_container = jetquery.belongsTo(.DeliveryOrderContainer, .{}),
.pickup_location = jetquery.belongsTo(.Location, .{ .foreign_key = "pickup_location_id" }),
.dropoff_location = jetquery.belongsTo(.Location, .{ .foreign_key = "dropoff_location_id" }),
.driver = jetquery.belongsTo(.UserRole, .{ .foreign_key = "driver_id" }),
},
},
);
Location Tracking
Another feature was the ability to integrate with GPS systems so
that the management of a Carrier could track where their drivers are. The idea
was to have either a mobile application that could be installed on a phone, or add location tracking to the web application so that the driver’s location could be tracked.
pub const DriverLocation = jetquery.Model(
@This(),
"driver_locations",
struct {
driver_id: i32,
lat: f32,
lon: f32,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.user_role = jetquery.belongsTo(.UserRole, .{
.foreign_key = "driver_id",
}),
},
},
);
File Attachment handling
The TMS system needed a way to handle attachments. Most DeliveryOrders would
have sets of paperwork that was required to pick up a container at a port, so
this was used to collect those attachments, for display. The url would store
the link to the attachment, so that it could be stored in a system like Amazon
S3 or another file hosting system.
pub const Attachment = jetquery.Model(
@This(),
"attachments",
struct {
id: i32,
url: []const u8,
delivery_order_id: i32,
created_at: jetquery.DateTime,
updated_at: jetquery.DateTime,
},
.{
.relations = .{
.delivery_order = jetquery.belongsTo(.DeliveryOrder, .{}),
},
},
);