logo
ExamplesEditorPricingDocs
37.1kSign in

Last-mile delivery & route planning database schema

Delivery operations modelled properly: depots, drivers and shifts, vehicle capacity constraints, orders with time windows, routes planned per day, stops in planned and actual sequence, geofenced arrivals, failed attempts, proof of delivery and re-sequencing history.

Last Mile Delivery ER diagram

Last-mile delivery & route planning database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 11 n1 depots idBIGINT codeVARCHAR nameVARCHAR address_line1VARCHAR cityVARCHAR latitudeNUMERIC longitudeNUMERIC timezoneVARCHAR dispatch_cutoff_t…TIME loading_baysSMALLINT drivers idBIGINT home_depot_idBIGINT driver_codeVARCHAR full_nameVARCHAR phoneVARCHAR employment_typeVARCHAR licence_classVARCHAR statusVARCHAR vehicles idBIGINT home_depot_idBIGINT registration_plateVARCHAR vehicle_typeVARCHAR max_weight_kgNUMERIC max_volume_cbmNUMERIC max_stopsSMALLINT has_tail_liftBOOLEAN is_temperature_co…BOOLEAN statusVARCHAR driver_shifts idBIGINT driver_idBIGINT depot_idBIGINT shift_dateDATE planned_start_atTIMESTAMPTZ planned_end_atTIMESTAMPTZ actual_start_atTIMESTAMPTZ actual_end_atTIMESTAMPTZ break_minutesSMALLINT max_driving_minut…SMALLINT statusVARCHAR delivery_orders idBIGINT depot_idBIGINT external_referenceVARCHAR recipient_nameVARCHAR recipient_phoneVARCHAR address_line1VARCHAR postcodeVARCHAR latitudeNUMERIC longitudeNUMERIC window_start_atTIMESTAMPTZ window_end_atTIMESTAMPTZ service_secondsSMALLINT weight_kgNUMERIC volume_cbmNUMERIC requires_signatureBOOLEAN requires_age_checkBOOLEAN prioritySMALLINT statusVARCHAR routes idBIGINT depot_idBIGINT route_dateDATE route_codeVARCHAR vehicle_idBIGINT driver_shift_idBIGINT statusVARCHAR plan_versionINTEGER planned_start_atTIMESTAMPTZ actual_start_atTIMESTAMPTZ actual_end_atTIMESTAMPTZ planned_stop_countSMALLINT planned_distance_…NUMERIC actual_distance_kmNUMERIC planned_duration_…INTEGER actual_duration_m…INTEGER route_stops idBIGINT route_idBIGINT planned_sequenceSMALLINT actual_sequenceSMALLINT stop_typeVARCHAR address_line1VARCHAR latitudeNUMERIC longitudeNUMERIC geofence_radius_mSMALLINT planned_arrival_atTIMESTAMPTZ actual_arrival_atTIMESTAMPTZ actual_departure_…TIMESTAMPTZ statusVARCHAR stop_parcels route_stop_idBIGINT delivery_order_idBIGINT load_positionVARCHAR added_atTIMESTAMPTZ route_revisions idBIGINT route_idBIGINT revision_numberINTEGER revised_atTIMESTAMPTZ revised_byVARCHAR reasonVARCHAR sequence_snapshotJSONB stops_remainingSMALLINT distance_delta_kmNUMERIC delivery_attempts idBIGINT route_stop_idBIGINT delivery_order_idBIGINT attempt_numberSMALLINT attempted_atTIMESTAMPTZ outcomeVARCHAR failure_notesTEXT next_actionVARCHAR latitudeNUMERIC longitudeNUMERIC proofs_of_delivery idBIGINT delivery_attempt_…BIGINT proof_typeVARCHAR captured_atTIMESTAMPTZ signer_nameVARCHAR storage_keyVARCHAR latitudeNUMERIC longitudeNUMERIC accuracy_metresSMALLINT device_idVARCHAR geofence_events idBIGINT route_stop_idBIGINT event_typeVARCHAR occurred_atTIMESTAMPTZ latitudeNUMERIC longitudeNUMERIC accuracy_metresSMALLINT is_auto_arrivalBOOLEAN

Related database schemas

PostgreSQL
Logistics

Parcel tracking and courier network schema

A courier network schema built on an append-only scan log: shipments and the packages inside them, hubs and planned routes, scan events as the only truth, derived status, delivery attempts, exceptions, proof of delivery, and promised versus actual delivery.

PostgreSQL
Logistics

Vehicle fleet management database schema

How a vehicle fleet is modelled: vehicles and drivers, licence entitlements with expiry, assignment history, downsampled telematics and fault codes, maintenance due by mileage or date, work orders and parts, fuel, inspections and incidents.

SQL Server
Logistics

International freight forwarding database schema

A SQL Server schema for international freight: bookings and incoterms, shipments and container consolidation, multi-leg sea, air and road journeys, carrier rate contracts, customs declarations, milestone events, documents, charges and invoicing.

PostgreSQL
Logistics

Returns & RMA (reverse logistics) database schema

How returns are actually modelled: RMA authorisations and eligibility windows, versioned policies, return reasons, inbound inspection and grading, dispositions from restock to scrap, refunds against exchanges and store credit, labels, restocking fees and return-abuse signals.