Engineering
How to Design a Shipment Tracking Database Schema
A practical data model for shipments, tracking events, webhook deliveries, provider requests, deduplication, retention, and fast customer-facing queries.
Shipment tracking data is an event stream disguised as a status field. The current status matters for fast page loads, but the history matters for customer support, notifications, analytics, and debugging. A useful schema supports both without duplicating every provider response forever.
This design separates your business shipment, the external tracking identity, normalized events, provider requests, and outgoing webhook attempts. It works in PostgreSQL and maps cleanly to most relational ORMs.
Keep orders and shipments separate
An order can have multiple shipments, and a shipment can contain items from multiple fulfilment decisions. Put carrier, tracking number, current status, estimated delivery, and delivery timestamp on a shipment table linked to the order rather than directly on the order.
This avoids painful migrations when split fulfilment arrives. It also lets one order page show independent timelines for parcels moving through different carriers.
Store a current snapshot for fast reads
Customer pages should not scan the full event table to discover the latest status. Keep normalized_status, carrier_code, estimated_delivery, last_event_at, last_checked_at, and version on the shipment row. Update that snapshot transactionally when a newer event is accepted.
Use an optimistic version or updated_at condition so two workers cannot move a delivered parcel backwards after an older provider response arrives late.
create table shipments (
id uuid primary key,
order_id uuid not null,
tracking_number text not null,
carrier_code text,
normalized_status text not null default 'pending',
estimated_delivery timestamptz,
last_event_at timestamptz,
last_checked_at timestamptz,
version integer not null default 0,
unique (carrier_code, tracking_number)
);Model events as immutable facts
Tracking events should usually be append-only. Store the normalized status, raw description, location, carrier event time, first-seen time, and a provider payload reference. A correction can append a superseding event rather than silently rewriting history.
Carrier timestamps are not always unique or ordered. Build deduplication from a hash of stable fields such as shipment, event time, status, description, and location. Keep the database uniqueness constraint as the final guard against concurrent inserts.
create table tracking_events (
id uuid primary key,
shipment_id uuid not null references shipments(id),
status text not null,
description text not null,
location text,
occurred_at timestamptz not null,
observed_at timestamptz not null default now(),
fingerprint text not null,
unique (shipment_id, fingerprint)
);Record provider requests separately
Operational debugging needs facts that do not belong in customer events: provider request ID, source, latency, HTTP status, cache hit, parser version, and failure category. Store these in a short-retention request table or observability system.
Do not put API keys, full authorization headers, or unnecessary customer data in request logs. Tracking numbers can also be sensitive operational data, so define retention and access rules deliberately.
Make webhook delivery auditable
Outgoing webhooks need their own event identity and attempt history. Store endpoint, event type, payload version, status, attempt number, response code, next retry time, and delivered time. Keep the signing secret encrypted or in a secret manager rather than in delivery rows.
A durable outbox written in the same transaction as the shipment update prevents the classic failure where the database commits but the process crashes before queuing the webhook.
Index the questions your product asks
Common access paths are order-to-shipments, carrier-plus-tracking-number lookup, active shipments due for polling, exceptions by severity, delivered shipments by date, and webhook attempts due for retry. Add composite indexes for those paths and inspect real query plans as volume grows.
Avoid indexing every payload field. Flexible JSON is useful for provider evidence, but normalized columns should power your core filters and dashboards.
Plan retention and deletion from day one
Keep customer-visible tracking history according to product need, then expire verbose request logs and raw provider payloads earlier. If an account is deleted, define how shipments, events, API usage, and webhook logs cascade or become anonymized.
A small, explicit model is easier to secure and cheaper to operate. Trace returns a normalized snapshot and event list that can feed this design without binding your tables to one carrier's schema.
Build tracking into your product
Create a Trace account, generate an API key, and test package tracking from the dashboard.