A shipping tracking schema is the architectural backbone of modern logistics, providing a structured way to store the lifecycle of a delivery from warehouse dispatch to the customer's doorstep. In its simplest form, this schema captures the where, when, and how of every package movement, serving as the definitive source of truth for both internal operations and external user-facing status pages.
The Structural Core: Shipments and Events
At the heart of any tracking system lies the relationship between the Shipment entity and its associated TrackingEvents. While the shipment table holds static data like the tracking number, destination, and selected courier, the events table is dynamic. It records every granular update—such as "Out for Delivery" or "Processed at Hub"—with a high-precision timestamp. This separation ensures that you maintain a clean history without cluttering the main order records.
Designers often face the choice between using a normalized status lookup table or storing status strings directly. In high-volume systems, a standardized StatusCode system is preferred. It allows the software to map various carrier-specific messages (e.g., FedEx's "Delayed" vs. DHL's "Held in Customs") to a unified internal status, simplifying reporting and automation triggers.
Synchronizing with Third-Party APIs
Most tracking data originates from external courier systems. The intent of your schema should support efficient synchronization. Storing a last_synced_at field and a metadata JSONB blob helps capture carrier-specific details—like the name of the neighbor who signed for a package—without forcing frequent changes to the core table structure. This flexibility is vital when integrating with multiple global partners who all use different data formats.
- Tracking Number: The primary key or unique index for external lookups.
- Event History: A chronological log of every status transition.
- Estimated Delivery Date (EDD): A dynamic field that updates based on real-time transit speed.
- Proof of Delivery: References to signature images or GPS coordinates.
Optimization for Real-Time Queries
Since tracking pages are some of the most frequently visited parts of an e-commerce site, the schema must be optimized for read-heavy workloads. Indexing the tracking_number and shipment_id is mandatory. For enterprise-level logistics, consider a partitioning strategy based on the created_at date to keep the active shipment tables lean and responsive while archiving old data to a separate storage layer.