dbo.optimoRouteOrder

Info Generated by scripts/Generate-DataDictionary.ps1 - do not edit by hand. Edit descriptions in schemas/tables/dbo.optimoRouteOrder.json and regenerate.

Columns

#	Column	Type	Nullable	Flags	Description
1	ID	int	no	identity	Surrogate primary key of the OptimoRoute order record.
2	primeJobID	uniqueidentifier	no		Prime Ecosystem job this OptimoRoute order/stop is for.
3	primeAppointmentID	uniqueidentifier	yes		Prime Ecosystem appointment linked to this order, once one has been scheduled.
4	bookingID	int	yes		ethBooking record linked to this order, once one has been scheduled.
5	optimoRouteID	uniqueidentifier	yes		ID of the order/stop as created in OptimoRoute, once it has been submitted; null until the order exists in OptimoRoute.
6	optimoRouteDay	datetime	no		Date of the OptimoRoute route plan this order/stop belongs to.
7	Version	bigint	no		Monotonically increasing version stamp used to detect whether the job/appointment data has changed since this order was last synced to OptimoRoute.
8	CreatedAt	datetime	no		Timestamp when this order record was first created.
9	UpdatedAt	datetime	no		Timestamp when this order record was last updated.
10	ScheduledEstimatorID	uniqueidentifier	yes		Prime Ecosystem user ID of the estimator/driver OptimoRoute assigned to this stop.
11	ScheduledAt	datetime	yes		Timestamp when OptimoRoute scheduled this stop (assigned a driver/time slot); null if not yet scheduled.
12	CompletionDate	datetime	yes		Timestamp when the driver marked/OptimoRoute recorded this stop as completed.
13	CompletionStatus	nvarchar(100)	yes		Completion outcome reported by OptimoRoute for the stop (e.g. success/failed).
14	CompletionTrackingURL	nvarchar(512)	yes		OptimoRoute live tracking URL customers/staff can use to follow the driver's progress to this stop.
15	CompletionUpdateAppointment	bit	no		Flag set to 1 when the stop's completion has not yet been propagated back to the Prime Ecosystem appointment, so a background job can pick it up.
16	ScheduledEstimatorUpdateAppointment	bit	no		Flag set to 1 (via spOptimoRouteSetAppointmentScheduledEstimator) when the scheduled estimator needs to be propagated back to the Prime Ecosystem appointment, so a background job can pick it up.
17	optimoRouteCountAddresses	int	yes		Number of addresses/stops OptimoRoute returned for this order's plan, used to detect address-count mismatches.
21	ResponseDurationMins	int	yes		On-site job duration in minutes as returned by OptimoRoute for this stop.
22	ResponseTravelMins	int	yes		Travel time in minutes to this stop as returned by OptimoRoute.
23	ResponseDurationMinsUpdatedAt	datetime	yes		Timestamp when ResponseDurationMins was last updated from OptimoRoute.
24	ResponseTravelMinsUpdatedAt	datetime	yes		Timestamp when ResponseTravelMins was last updated from OptimoRoute.
25	NotScheduledVersion	bigint	yes		Version stamp used to track whether an unscheduled-order alert has already been sent for the current state, to avoid duplicate notifications.
26	primeWorkOrderID	uniqueidentifier	yes		Prime Ecosystem work order ID this order/stop is linked to, if applicable.
27	optimoRouteJSON	nvarchar(MAX)	yes		Raw JSON payload of the order/stop data last sent to or received from the OptimoRoute API.
28	ContactVersion	bigint	yes		Version stamp of the customer/contact details used to detect whether they've changed since last synced to OptimoRoute.

Indexes

Name	Type	Unique	PK	Key columns	Included	Filter
PK_optimoRouteOrder	CLUSTERED	yes	yes	ID

Relationships (inferred)

From	To	Confidence
dbo.optimoRouteOrder.bookingID	dbo.ethBooking.ID	high
dbo.optimoRouteOrder.NotScheduledVersion	dbo.optimoRouteOrder.Version	medium
dbo.optimoRouteOrder.primeAppointmentID	dbo.primeAppointments.id	high
dbo.optimoRouteOrder.primeJobID	dbo.primeJobs.Id	high
dbo.optimoRouteOrder.primeJobID	dbo.primeAppointments.jobId	medium
dbo.optimoRouteOrder.primeJobID	dbo.primeWorkOrders.jobid	medium
dbo.optimoRouteOrder.primeWorkOrderID	dbo.primeWorkOrders.id	high
dbo.optimoRouteOrder.ScheduledEstimatorID	dbo.primeUsers.id	high
dbo.optimoRouteOrder.ScheduledEstimatorID	dbo.ethBookingEstimatorSkill.primeEstimatorID	medium
dbo.optimoRouteOrder.Version	dbo.primeAppointments.version	medium
dbo.optimoRouteOrder.Version	dbo.primeWorkOrders.version	medium
dbo.primeJobsLocation.jobID	dbo.optimoRouteOrder.primeJobID	high

Referenced by

Procedures: dbo.spEthAIPrimeJobSummaryQueueUpdateApptTask, dbo.spEthBookingGet, dbo.spEthBookingGetAvailabilityGeo, dbo.spEthBookingSetAppointmentID, dbo.spOptimoRouteDriverDayClear, dbo.spOptimoRouteDriverDayUpsertDurationMins, dbo.spOptimoRouteGetAllowAdvancedBookings, dbo.spOptimoRouteGetAppointmentCompletionQueue, dbo.spOptimoRouteGetAppointmentQueue, dbo.spOptimoRouteGetAppointmentScheduledEstimatorQueue, dbo.spOptimoRouteGetBookingID, dbo.spOptimoRouteGetCompletionQueue, dbo.spOptimoRouteGetCountOrderFixedTime_Deprecated, dbo.spOptimoRouteGetNearbyJobs, dbo.spOptimoRouteGetNotScheduledOrders, dbo.spOptimoRouteGetNotScheduledOrdersEmailQueue, dbo.spOptimoRouteGetScheduledOrders, dbo.spOptimoRouteGetWorkOrderQueue, dbo.spOptimoRouteOrderGetActiveByJob, dbo.spOptimoRouteOrderGetRefreshQueue, dbo.spOptimoRouteOrderSetCompletion, dbo.spOptimoRoutePlanSetID, dbo.spOptimoRouteSetAppointmentCompletion, dbo.spOptimoRouteSetAppointmentScheduledEstimator, dbo.spOptimoRouteSetNotScheduledOrdersEmailSent, dbo.spOptimoRouteUpdateOrderDurationMins, dbo.spOptimoRouteUpdateOrderID, dbo.spOptimoRouteUpdateOrderTravelMins, dbo.spOptimoRouteUpsertOrder, dbo.spOptimoRouteUpsertOrder_Depcrecated, dbo.spOptimoRouteUpsertOrder_V2, dbo.spOptimoRouteUpsertStop