Relay data model
erd
What a parcel is, where it has been, and who was meant to receive it
/
Fit
SVG
PNG
Booking
Network
Movement
currently at · current_depot_id → id · many ↔ zero-or-one
delivers to · destination_id → id · many ↔ one
addressed to · recipient_id → id · many ↔ one
booked by · merchant_id → id · many ↔ one · on delete restrict
leaves from · depot_id → id · many ↔ one
part of · shipment_id → id · many ↔ one · on delete cascade
sited at · address_id → id · many ↔ one
driven by · driver_id → id · many ↔ zero-or-one
delivers · parcel_id → id · many ↔ one
based at · depot_id → id · many ↔ one
records · parcel_id → id · many ↔ one · on delete restrict
against · parcel_id → id · many ↔ one
on · route_id → id · many ↔ one · on delete cascade
merchants — Businesses that book shipments
merchants
id · uuid · primary key
PK
id
uuid
name · text
name
text
account_ref · citext
UQ
account_ref
citext
created_at · timestamptz · default now()
created_at
timestamptz
shipments — One booking, which may contain several parcels
shipments
id · uuid · primary key
PK
id
uuid
merchant_id · uuid
merchant_id
uuid
reference · text · nullable
reference?
text
service_level · service_level
service_level
service_level
recipient_id · uuid
recipient_id
uuid
destination_id · uuid
destination_id
uuid
booked_at · timestamptz
booked_at
timestamptz
parcels — The physical thing. One row per barcode.
parcels
id · uuid · primary key
PK
id
uuid
shipment_id · uuid
shipment_id
uuid
tracking_number · text
UQ
tracking_number
text
status · parcel_status
status
parcel_status
weight_grams · int
weight_grams
int
attempts · int · default 0
attempts
int
current_depot_id · uuid · nullable
current_depot_id?
uuid
recipients — Who the parcel is for. Deliberately not an account.
recipients
id · uuid · primary key
PK
id
uuid
name · text
name
text
phone · text · nullable
phone?
text
email · citext · nullable
email?
citext
addresses — Validated and geocoded before a shipment is accepted
addresses
id · uuid · primary key
PK
id
uuid
line1 · text
line1
text
line2 · text · nullable
line2?
text
city · text
city
text
postcode · text
postcode
text
country · char(2)
country
char(2)
latitude · numeric · nullable
latitude?
numeric
longitude · numeric · nullable
longitude?
numeric
validated_at · timestamptz · nullable
validated_at?
timestamptz
depots — A sorting site. Every parcel passes through at least one.
depots
id · uuid · primary key
PK
id
uuid
code · text
UQ
code
text
name · text
name
text
address_id · uuid
address_id
uuid
drivers — Couriers, each based at one depot
drivers
id · uuid · primary key
PK
id
uuid
depot_id · uuid
depot_id
uuid
name · text
name
text
licence_ref · text
UQ
licence_ref
text
routes — One driver, one van, one day
routes
id · uuid · primary key
PK
id
uuid
depot_id · uuid
depot_id
uuid
driver_id · uuid · nullable
driver_id?
uuid
service_date · date
service_date
date
sequenced_at · timestamptz · nullable
sequenced_at?
timestamptz
stops — One parcel's place in a route, with the window promised to the recipient
stops
id · uuid · primary key
PK
id
uuid
route_id · uuid
route_id
uuid
parcel_id · uuid
parcel_id
uuid
sequence · int
sequence
int
window_start · timestamptz
window_start
timestamptz
window_end · timestamptz
window_end
timestamptz
scans — Append-only. Every barcode read, in the order it happened, not the order it arrived.
scans
id · uuid · primary key
PK
id
uuid
parcel_id · uuid
parcel_id
uuid
type · scan_type
type
scan_type
depot_id · uuid · nullable
depot_id?
uuid
driver_id · uuid · nullable
driver_id?
uuid
device_id · text
device_id
text
scanned_at · timestamptz
scanned_at
timestamptz
received_at · timestamptz · default now()
received_at
timestamptz
idempotency_key · text
UQ
idempotency_key
text
delivery_attempts — Why a parcel came back on the van
delivery_attempts
id · uuid · primary key
PK
id
uuid
parcel_id · uuid
parcel_id
uuid
stop_id · uuid · nullable
stop_id?
uuid
outcome · text
outcome
text
attempted_at · timestamptz
attempted_at
timestamptz
photo_url · text · nullable
photo_url?
text
parcel_status
parcel_status
enum
created
created
collected
collected
at_depot
at_depot
in_transit
in_transit
out_for_delivery
out_for_delivery
delivered
delivered
attempt_failed
attempt_failed
awaiting_instruction
awaiting_instruction
returning
returning
returned
returned
lost
lost
cancelled
cancelled
scan_type
scan_type
enum
collection
collection
depot_inbound
depot_inbound
depot_outbound
depot_outbound
vehicle_load
vehicle_load
delivery
delivery
attempt_failed
attempt_failed
return
return
currently at · current_depot_id → id · many ↔ zero-or-one
currently at
current_depot_id → id
delivers to · destination_id → id · many ↔ one
delivers to
destination_id → id
addressed to · recipient_id → id · many ↔ one
addressed to
recipient_id → id
booked by · merchant_id → id · many ↔ one · on delete restrict
booked by
merchant_id → id
leaves from · depot_id → id · many ↔ one
leaves from
depot_id → id
part of · shipment_id → id · many ↔ one · on delete cascade
part of
shipment_id → id
sited at · address_id → id · many ↔ one
sited at
address_id → id
driven by · driver_id → id · many ↔ zero-or-one
driven by
driver_id → id
delivers · parcel_id → id · many ↔ one
delivers
parcel_id → id
based at · depot_id → id · many ↔ one
based at
depot_id → id
records · parcel_id → id · many ↔ one · on delete restrict
records
parcel_id → id
against · parcel_id → id · many ↔ one
against
parcel_id → id
on · route_id → id · many ↔ one · on delete cascade
on
route_id → id
drag to pan · wheel to zoom · click a node · Esc clears
Conventions
All ids are uuid v7, so they sort by creation time
Weights are integer grams; there are no floats anywhere in this schema
Timestamps are timestamptz in UTC
Watch out
scans is append-only and never updated — corrections are new rows
scanned_at is device time and can be wrong; received_at is server time and cannot
parcels.attempts is denormalised from delivery_attempts for the route planner