OptimoRoute, Appointments & Bookings — How It All Works

Ethos Building / BlazorApps solution. Written 2026-09-21 from the code in C:\GitHub\BlazorApps and the live SQL definitions in C:\GitHub\sql-ontology. Updated 2026-09-30 for the PRs merged since (#295–#307), from the DbMigrations definitions on main.

Covers: FormsPortal (customer-facing booking), AdminPortal (staff), and WebAPI (the sync engine between Prime, our database and OptimoRoute) — including the cutoff, planning and scheduling rules that tie them together.

Changes since the first version (2026-09-21)

PR	Story	What changed	Sections
#295, #301	1731, 1751	Completed appointments keep their OptimoRoute order, unless they were completed ahead of a day still beyond the planning window. Judged when the appointment was completed, against the order's own day.	§7.1
#297	1747	The Scheduled Orders drill-down always includes completed work. Route Dispatches now shows yesterday as well as today and the next route date.	§10
#298	1658	AdminPortal Public Holidays page filters by year and state.	§5
#305	1727	Temporary estimator relocations (CAT events). Location points: distances keyed by place, not by person. NSW jobs booked as QLD. Marginal travel replaces "nearby" for ranking. OptimoRoute driver start/end location push.	§2, §5.6, §8.2, §8.3, §8.5, §8.9, §12
#306	1727	Estimator schedules: a normal week per estimator, plus dated extra hours (Saturdays, event days). OptimoRoute driver hours follow them.	§5.4, §5.7, §7.1, §8.2, §8.10
#307	1774	Field Quotation service duration 30 → 45 minutes. KPI alert floor for Field 45 → 60.	§7.2, §8.2, §9.2
—	1852	Booking capacity is charged the travel a job adds to the day, not a second drive from home on top of a route that already starts there. Bookings on a cancelled Prime appointment no longer count as pending work.	§8.2, §8.3, §12
—	1886	The drive to the first stop is free up to the lead-in between driver start and the booking window (normally 06:00–08:00 = 120 min). Only the excess counts against the booking window. Migration 0043.	§8.2, §8.3, §9.2, §13

Deployment state (2026-09-30). Migrations 0021–0030 (UserStory1727) are applied on dev only; Test and Prod are still to come. Until they are, the live behaviour in those environments is the pre-1727 behaviour described in the first version of this document. The status of 0031 (UserStory1774) has not been checked.

---

1. The moving parts

System	Role
Prime	The system of record for jobs and appointments. Ethos staff live here.
Our SQL database	Mirrors Prime jobs/appointments (primeJobs, primeAppointments, primeUsers), and owns the booking layer (ethBooking, ethVABooking, ethAppointmentDefinition/Instance), the location layer (ethBookingLocationPoint, ethBookingPointDistance, ethBookingJobPointDistance, ethBookingEstimatorRelocation, estimator schedules) and the OptimoRoute layer (optimoRouteOrder, optimoRouteDriverDay, optimoRoutePlan, OptimoRouteDispatch, ethOptimoRouteDriverLocationPush).
OptimoRoute	Route optimisation. Holds orders (a job to attend), plans (an optimisation run for one date), routes (the planned sequence per driver/day) and dispatch (routes actually sent to drivers).
FormsPortal	Where the insured picks a date. /booking/estimator/{sharingCode} (field) and /booking/va/{sharingCode} (virtual assessment).
AdminPortal	Staff create booking links, do advanced/manual bookings, view availability, manage recurring appointment definitions and read OptimoRoute logs.
WebAPI	All queue processing and every Prime/OptimoRoute API call. Nothing calls Prime inline from a customer request.
n8n	The scheduler. Polls WebAPI endpoints on timers; portals also fire immediate webhooks for responsiveness.

Golden rule of the architecture: portals write to our database and (at most) fire a webhook. WebAPI drains queues and does the outbound API calls. That is why a slow Prime or OptimoRoute never blocks a customer clicking "Book".

---

2. Core concepts and vocabulary

• Booking (ethBooking) — a field-estimator booking link for one Prime job + appointment type. Created unbooked (no FromDate), the insured picks a day, then it becomes a Prime Appointment and an OptimoRoute Order.
• VA Booking (ethVABooking) — the desktop/virtual-assessment equivalent. Books a specific desktop estimator at a specific time slot — no OptimoRoute involvement (nobody drives anywhere).
• Appointment (primeAppointments) — Prime's record. Statuses used here: New, Cancelled, Completed. Only New appointments belong in OptimoRoute.
• Recurring Appointment Definition (ethAppointmentDefinition) — a repeating pattern that materialises instances which are pushed into Prime as appointments.
• Order (optimoRouteOrder) — our row per (job, date) sent to OptimoRoute. Its orderNo is always {JobNumber}-{optimoRouteOrder.ID}. optimoRouteID non-null means "OptimoRoute currently holds this order".
• Plan / Route / Dispatch — three separate stages, in that order. Planning decides who goes where; dispatch decides when the driver is told.
• Location point (ethBookingLocationPoint) — one row per distinct geocoded coordinate (rounded to 5 decimals). Estimator homes (primeContactLocation.PointID), jobs (primeJobsLocation.PointID) and relocation bases all resolve to points, and every travel distance is cached between points, not between people. A changed address geocodes to a new point, so its old distances simply stop being used — which also fixed the old bug where a changed address kept its stale distances forever.
• Relocation (ethBookingEstimatorRelocation) — an estimator temporarily based somewhere else for a date range (e.g. a QLD hotel during a CAT event). While it covers a date, the estimator is bookable only in the relocation's state, with travel from the relocation point.
• Estimator schedule — each estimator's working hours on a date, from three layers: the state default, their normal week (ethBookingEstimatorWeeklyHours) and dated extra hours (ethBookingEstimatorDayHours). See §8.10.
• Booking state — the state a job is booked as. fnEthBookingStateMap() maps NSW → QLD (config BookingStateMap) until there is a NSW office.

---

3. The end-to-end flow (field estimator booking)

AdminPortal "Create booking link"      ─┐
Prime job reaching a bookable status   ─┴─► ethBooking row (unbooked, sharing code)
                                              │
                          WebAPI BookingService.ProcessEventOnCreate
                          • Prime internal note with the booking link
                          • SMS + email to the insured (unless SkipNotifyContact)
                                              │
                    Insured opens FormsPortal /booking/estimator/{code}
                    • sees availability (fnEthBookingDayAvailability)
                    • picks a day → SetDate (UTC + job timezone)
                    • CreateBookingOrder → OptimoRoute order created immediately
                    • fires the OptimoCreatePlan webhook
                                              │
                          WebAPI BookingService.ProcessEventOnBook
                          • creates/updates the Prime Appointment (Estimator TBA VIC/QLD)
                          • patches Prime job status → "Estimator Booked" (guarded)
                          • Prime note + confirmation SMS + email
                                              │
                    Prime appointment mirrors back → OptimoRoute appointment queue
                    • order re-created/updated against the real appointment
                                              │
                          Planning → Routes → Scheduled estimator → Dispatch

---

4. FormsPortal

4.1 Estimator booking — FormsPortal/Components/Pages/Estimator.razor

Anonymous, reached by a time-limited sharing code in the URL. No EntraID.

State machine, driven purely off the booking row:

Condition	Component shown
CancelReasonCode != null	EstimatorCancelled
FromDate == null (or "change booking") and availability is empty	EstimatorNoAvailability
FromDate == null (or "change booking")	EstimatorUnbooked (the date picker)
otherwise	EstimatorBooked

Key behaviours:

• Availability comes from IBookingData.GetAvailabilityGeo(PrimeJobID, primeAppointmentTypeID) — the geo-aware wrapper over fnEthBookingDayAvailability (§8.2, §8.3). Each date's From/To is the chosen estimator's own window that day, so a Saturday or an extended event day simply appears (§8.10). The page books the row's own From/To and needed no change for it.
• Polling. While unbooked, a background loop re-reads availability every 5–25 s (jittered) so a day that fills up while the customer deliberates disappears from the list.
• Timezones. The page resolves the job's timezone from State/Suburb/Postcode (ITimeZoneHelper), and separately reads the browser's offset via JS. If they differ it shows a timezone warning — the insured may be interstate from the property. Before booking, the browser's offset is compared with the job zone's offset now. Once booked, EstimatorBooked (and VABooked) compare them at the booked time, using the browser's IANA zone (BrowserTimezoneService.TimeZoneId). "In your local time this is …" is converted through that zone too. So a Brisbane customer booking a Melbourne job sees the warning for a daylight-saving date but not for a standard-time date, and the time shown is right. Same zone names aren't required (Sydney and Melbourne never warn). If the browser's zone doesn't resolve on the server, it falls back to the browser's offset today. Local slot times are converted to UTC before SetDate.
• The stored offset is the one at the booked time. ethBooking.TimezoneOffsetMins (JS sign convention, -600 = +10:00) is GetUtcOffset(fromUTC) for the job's zone. Every reader converts FromDate/ToDate with it: the AdminPortal Bookings list, the FormsPortal booked page, the Prime on-book note and spEthBookingSMSOnBook. Until UserStory1777, FormsPortal and AdminPortal's advanced booking saved the offset at the moment of saving instead. A booking made before a daylight-saving change for a date after it then showed an hour out everywhere. J015340, booked on 1 Oct for 10am on 5 Oct, showed 9am. Migration 0033 corrected the rows already saved. VA bookings always stored the slot's offset.
• Booking a slot (ShowBookingDialogAsync) does, in order:
  1. _bookingDB.SetDate(ID, fromUTC, toUTC, tzId, offsetMins, notes, BookingTimeWindow.AllDay)
  2. re-read the booking
  3. _optimoRouteOrderService.CreateBookingOrder(booking) — an OptimoRoute order immediately, before Prime knows anything. This is what makes the day's capacity shrink for the next customer.
  4. _webhookHandler.OptimoCreatePlan() — nudge n8n to re-plan so availability refreshes.
• Cancelling (OnCancelBookingAsync): Clear(ID, reasonCode, reasonText) → DeleteBookingOrder → same planning webhook.
• SLA hint. IsDateOutOfSLA = more than 5 days after JobCreatedAt. Purely presentational — it flags dates that breach the standard SLA, it does not block them.
• "None of these work for me" is instrumented: each step (NoneOfTheseWorkClicked, ShowMoreDatesDialogShown/Confirmed/Cancelled) stamps a timestamp on the booking. The dialog can reveal more dates, keep the recommended ones, or fall through to the cancellation flow. Tracking failures are swallowed — they must never block the customer.

4.2 VA booking — FormsPortal/Components/Pages/VirtualAssessment.razor

Same shape, different rules:

• Availability from spEthVABookingGetAvailability(@VABookingID) — real time slots against named desktop estimators, over a horizon of roughly −2 to +16 days.
• SetDate(VABookingID, fromUtc, toUtc, offsetMins, DesktopEstimatorUserId, notes, callbackPhone) binds a specific estimator, unlike field bookings which book a day and let OptimoRoute decide who attends.
• No OptimoRoute order is created.
• Fires _webhookHandler.VAProcessEvents() so VABookingService processes the event promptly (n8n also polls every 3 minutes as the fallback).

VA availability exclusions (spEthVABookingGetAvailability): an estimator is out if they have an overlapping Prime appointment, another pending VA booking, or — the dual-role case — a field ethBooking or a planned optimoRouteOrder on that day, in which case they are excluded for the whole local day, because they are out in the field.

---

5. AdminPortal

Page	Route	What it does
Bookings	/bookings, /bookings/{tab}	Unified list of field + VA bookings with tab counts (Pending / Booked / Expired / Completed / Cancelled). Creates booking links.
Booking Advanced	/bookingadvanced/{jobNumber}/{appointmentTypeId?}	Staff override path — pick the estimator and the exact window, bypassing the customer journey.
Booking Availability	/bookingavailability	Field (GetAvailabilityDetailsAll) and desktop (GetDesktopEstimatorAvailabilityDetailsAll) capacity, one week at a time from today (pager steps through the procs' full window). Each day is a section with a team summary (estimators with 2h+ free, busy count, % free for Field and Desktop separately) and a wrapping grid of estimator cards, so it fits any number of estimators without horizontal scroll and stacks to one column on mobile. Card bands: 100% free, 30%+, under 30%, Busy (under 5% or over). Each day's header has a change-log icon (§9.4). Set Unavailable (leave, office time) is Admin and EstimatingManager since AB#1922 (it was BookingManager).
Booking Availability Calendar	/bookingavailabilitycalendar/{user}/{view}	Calendar for any active Prime user, picked on the page (one nav link). Field estimators see their OptimoRoute route (unchanged); everyone else sees their Prime appointments plus the Prime work orders assigned to them (spEthCalendarGetUserWorkOrders, via primeUsers.contactId = primeWorkOrders.assignedid; Draft and Cancelled excluded, no start time = all-day). With no user, opens on the signed-in user. Public holidays are for the user's state (spEthCalendarGetUserHolidayState): an enabled field skill row's state, else their Prime timezone via fnEthStateFromTimezone, else VIC. The view (day/workweek/week/month) is kept in the URL.
Booking Estimators	/bookingestimators	Estimator enable/disable, state, skills.
Estimator Locations & Hours	/bookingestimatorlocations (Settings → People)	Relocations (§5.6) and schedules (§5.7). Admin and BookingManager (SettingsAudience.BookingManagerOrAdmin).
Public Holidays	/publicholidays (Settings → Bookings)	Public holidays, filterable by year and state (UserStory1658). Admin only.
Appointment Definitions	/AppointmentDefinitions	CRUD for recurring appointment definitions.
OptimoRoute Logs	/optimoroutelogs	The optimoRouteLog audit trail — every module/action written by the services below.

Supporting dialogs: BookingCreate, BookingConfirmTime, BookingCustomerLink, AppointmentUpsert, DesktopEstimatorScheduledAppointments, OptimoRouteScheduledOrders, OptimoRouteOrdersNotScheduled, OptimoRouteCalendarItem, OptimoRouteDispatchEvents, AppointmentCancel.

Cancelling an appointment (AB#1922). BookingManager, EstimatingManager (AB#1121) and Admin get a cancel action on each appointment, but not on work orders. It appears in the Not Scheduled dialog, and on an estimator's scheduled orders (both the run sheet and the table) for today onward. The AppointmentCancel dialog does the following:

1. Reads the appointment from Prime and shows its details. It refuses one that is already Cancelled or Completed.
2. Takes a required reason and sets the Prime appointment to Cancelled. It re-reads the appointment first, and the reason is added to its notes.
3. Refreshes our mirror and writes the day's change log.

It works with or without a booking. The rest is the normal sync (§7.1): on its next run, the appointment queue deletes the order and re-queues the day's plan. If there is a booking, the queue also calls spEthBookingSetToExpired, which stops the link and sends the cancellation SMS when the on-book SMS went out. Until that run, the order is still live, so the dialogs only drop the row locally.

Desktop estimators (AB#1924). The same action, for the same roles and rules, is on a desktop estimator's Scheduled Appointments dialog (DesktopEstimatorScheduledAppointments, from Booking Availability and the estimator calendar). spEthVABookingGetEstimatorAppointmentsForDate returns the appointment id, the job address and the live ethVABooking behind it, if there is one. There is no route to re-plan. When there is a VA booking, the dialog also runs spEthVABookingCancel after the Prime cancel. VABookingService's OnCancel run then adds the Prime note and texts/emails the customer. It skips its own Prime cancel because the appointment is already Cancelled.

5.1 Creating a booking link (BookingCreate)

Staff pick a Prime job and one of four types: Field Quotation, Complex Quotation, Major Loss Estimate, Desktop VA. The first three call _bookingDB.Create(...); Desktop VA calls _vaBookingDB.Create(...). A notifyContact checkbox maps to SkipNotifyContact, and the dialog warns when the BookingSMSEnabled site option is 0 (nothing will be texted).

Provenance is recorded on every field booking: CreatedBy (the EntraID UPN) and CreatedSource (e.g. AdminPortal.ManuallyCreated, AdminPortal.AdvancedBooking). Who made the first booking is recorded separately as FirstBookedBy/FirstBookedSource (and ethVABooking.FirstBookedBy); staff booking on the customer's behalf use FormsPortal's /booking/estimator/staff/{code} and /booking/va/staff/{code} routes - see TriageReporting-Spec.md. VA bookings (ethVABooking) have no creation provenance columns. Staff-created VA links also have no skip-notify option and don't fire the VA webhook, so they wait for the 3-minute schedule.

5.2 Advanced booking (BookingAdvanced)

The staff escape hatch when the normal availability rules would say no:

1. Choose state → estimators (GetEstimators), then estimator → appointment types (GetAppointmentTypesByEstimator).
2. GetAvailabilityDetails(...) shows the numbers behind the answer (slot minutes, route minutes, pending minutes, remaining). Since UserStory1727 each day also shows "+X travel" — the travel this job would add to that estimator's day (§8.3) — with * when a leg was estimated rather than taken from the distance cache. The page lists one row per day. Its free estimators are tiles sorted by least added travel. Busy estimators share one line, each linking to their scheduled orders. This replaced a column per estimator, which didn't fit many estimators or a narrow window.
3. Upsert(JobID, appointmentTypeId, true, estimatorId, upn, "AdminPortal.AdvancedBooking") then SetDate(...) with a chosen time window.
4. CreateBookingOrder(booking) and the OptimoCreatePlan webhook — same two calls as FormsPortal.

Because PrimeUserID is set here, the order is built with an explicit AssignedTo.DriverExternalId (the estimator's Prime full name) — OptimoRoute is told who is going rather than deciding.

Booking after the cutoff (AB#1922). The page asks for availability with @AfterCutoff = 1, so it also lists the days the normal cutoff hides (§8.2), marked After cutoff:

• Only BookingManager or Admin may book them. Anyone else is told only a Booking Manager can.
• A Booking Manager is first warned that the job's state will be planned again from scratch for that day. The warning says how many of that state's routes (and orders) may change, and that drivers and customers may be re-notified (spOptimoRoutePlanGetStateScope).
• The booking then calls spOptimoRoutePlanRequestFullReplan before the plan webhook (§8.5).

Changes from Prime and FormsPortal never ask for a full re-plan. After the cutoff they still keep the existing routes.

5.3 Recurring appointment definitions

AppointmentDefinitions.razor → WebAPI/Controllers/AppointmentController:

• GET/POST /api/Appointment/Definitions — read and upsert (definition + assigned Prime users).
• POST /api/Appointment/Definitions/{id}/SetActive — the proc reconciles instances to the flag in one transaction: cancels future instances on the way down, revives our own cancellations on the way up. No caller can do half a toggle.
• After any definition write, the controller fires the RecurringAppointmentProcess webhook fire-and-forget, so the instances catch up immediately instead of waiting for the schedule. The n8n flow only calls Instances/GenerateBatch and Instances/Sync, never Definitions, so there is no loop.

Instance lifecycle endpoints:

Endpoint	Purpose
Instances/Generate	Materialise instances for every eligible definition, to the horizon. Can run long.
Instances/GenerateBatch	Same, self-limited to stay under 120 s. Call repeatedly until DefinitionsRemaining = 0. Shares a lock key with Generate.
Instances/Sync	Push pending instances to Prime — create/update for new, cancel for CancelPending — and record the outcome.
Instances/Reconcile	The inbound counterpart: pull Prime-side status changes back onto our instances, so an appointment cancelled directly in Prime doesn't read Synced forever. Own lock key.
Definitions/GenerationFailures	Surfaces definitions the generator skipped because expansion threw (their NextGenerationAt is pushed out a full horizon, so they go quiet rather than obviously broken).

Notes pushed to Prime carry a marker line identifying them as generated from a recurring definition (and telling staff to change it here, not in Prime). Existing marker lines are stripped before it is re-appended, so repeated edits don't accumulate markers. The sync write is guarded by the status it acted on — a definition disabled mid-flight must not have Synced stamped over its CancelPending.

---

5.4 Time windows and what staff are allowed to offer

BookingTimeWindow (CommonDataAccess/Data/Booking/BookingData.cs) has four values, and only the first is ever available to the customer:

Value	Window written	Offered by
AllDay = 1	The estimator's window that day, as-is	FormsPortal and AdminPortal
Morning = 2	First four hours of the window (08:00 – 12:00 on a normal day)	AdminPortal only
Afternoon = 3	Last four hours of the window (10:00 – 14:00 on a normal day)	AdminPortal only
SpecificTime = 4	Chosen time → + JobMins	AdminPortal only

Since UserStory1727 (#306) the window is the chosen estimator's window for that day (§8.10), not a fixed 08:00–14:00. On an 08:00–16:00 event day, Morning is 8–12 and Afternoon is 12–4. The radio labels in BookingConfirmTime show the real times.

FormsPortal always passes BookingTimeWindow.AllDay — the insured picks a day, never a window. The narrower windows exist only on BookingAdvanced, through the BookingConfirmTime dialog, and the dialog asks OptimoRoute's own rules whether it may even offer them:

• AllowAdvancedBookings(estimatorId, jobState, planDate) → spOptimoRouteGetAllowAdvancedBookings. False disables Morning/Afternoon/SpecificTime entirely for that estimator and day.
• AllowSpecificTimeBookings(jobId, estimatorId, appointmentTypeId, planDate) → spOptimoRouteGetAllowSpecificTimeBookings. Only checked when advanced windows are allowed at all. For a named estimator the slot is their window that day, and it returns 0 on a day they don't work. Eligibility and travel follow any relocation on planDate. A known quirk is recorded in its header: a missing distance is charged 0 minutes, not 30.

Narrowing a window tightens the TimeWindowFrom/TimeWindowTo sent to OptimoRoute, which constrains the optimiser — that is why it is gated per estimator/day rather than freely available.

Booking Managers and Admins skip both rules (AB#1922). With a named estimator they are offered Morning, Afternoon and Specific time whenever the day has capacity. Capacity is the page's own answer: it only offers estimator-days that fit (IsFeasible, on added travel). This drops the double-travel test (2 × travel) and the "already has a non-8–2 order" test for them, on any day.

Note SetSpecificTimeOffsets in that dialog is deliberately inert: the travel/job-duration buffer it used to apply to specific-time slots is commented out, "Buffer time Disabled as request by Aaron". The commented body is kept as the record of what it did.

5.5 Booking lifecycle states

A booking is not a simple booked/unbooked flag. The list page's tabs are the practical state model:

State	How it is reached
Pending	Created, FromDate null, sharing code still valid
Booked	FromDate set
Expired	SharingCodeExpiry < UtcNow, or SetToExpired called
Completed	Prime appointment reported Completed
Cancelled	CancelReasonCode set (by the insured, or by staff)

spEthBookingSetToExpired is called automatically when an appointment behind a booking is cancelled and its OptimoRoute order is deleted (§7.1). The link must stop working once the work it referred to is gone. The proc also sends the cancellation SMS if the on-book SMS went out. So it is not called when the order is deleted because the appointment moved to an out-of-scope estimator, or was completed ahead of the planning window. In both cases the work is still happening or has happened, and the customer must not be told it is cancelled. BookingCustomerLink re-surfaces or re-sends the link for a still-valid code; expiry itself is managed through the shared SharingCodeExpiry mechanism used by all FormsPortal sharing codes.

5.6 Estimator relocations (UserStory1727)

BookingEstimatorLocations.razor lists one row per estimator with at least one enabled skill, and highlights anyone with no Prime contact address (they have no home point, so no travel). A row opens the EstimatorLocationEdit dialog, which covers:

• add, edit, End now, cancel and delete a relocation;
• geocoding the address and showing the result back;
• Booked in state — the state the estimator is booked in, which defaults from the geocode and is not necessarily the address's state. It must be a base state (one with enabled skills: VIC and QLD today). A QLD team at Tweed Heads (NSW) is a QLD relocation. Enforced in spEthBookingEstimatorRelocationUpsert, which also rejects overlapping ranges for the same estimator;
• the pinned-work warning (spEthBookingEstimatorRelocationBookingConflicts): existing home-state bookings and appointments pinned to this estimator inside the range. They are not moved or cancelled. They will be unscheduled in OptimoRoute and show up in Orders Not Scheduled (§9.3). This is information, not a blocker;
• the plan-window warning: dates inside the plan window aren't pushed to OptimoRoute and must be changed there by hand (§8.9). The dialog recommends starting a relocation after the next plan date;
• OptimoRoute Retry for failed pushes, and Done in OptimoRoute for manual dates.

The grid shows "Push failed" and "Manual OptimoRoute change" chips until they are resolved.

What a relocation does to booking (fnEthBookingEstimatorSkillOnDate, fnEthBookingEstimatorBase):

• On its dates the estimator is eligible in the relocation's state, with the skills they already hold. A relocation never grants a skill.
• Home-state eligibility is suppressed on those dates, so the estimator isn't counted in two states' capacity. Border eligibility (keeping both, e.g. Albury/Wodonga) is out of scope.
• Travel is from the relocation point. When the relocation ends, the home point's distances were never touched, so no Google calls are needed to come home.
• Working hours stay the same wall-clock hours, in the relocation state's local time. 8–2 means 8–2 QLD time while in QLD.

5.7 Estimator schedules (UserStory1727, #306)

On the same page:

• an Hours column ("Custom week", "Extra days (n)") and an Hours button that opens EstimatorScheduleEdit. It holds the estimator's normal week (blank = default) and their extra-hours dates, with push status, add and remove;
• an Extra hours toolbar button that opens EstimatorDayHoursBulk. Choose estimators (with "All in VIC/QLD" shortcuts), a date range and the weekdays to include, then the booking hours and a reason. Each estimator's dates are then pushed to OptimoRoute.

The weekly pattern is not pushed to OptimoRoute. Set the driver's normal hours on the driver in OptimoRoute to match; the Hours dialog says so on save. Dated extra hours are pushed (§8.10). Leave is out of scope: staff already block it out with appointments.

6. WebAPI — the sync engine

Everything is Basic-Auth protected (BasicAuthMiddleware) and driven by n8n on timers. Overlapping runs are prevented by ControllerSemphoreService lock keys.

6.1 Booking events — BookingController + BookingService

An outbox pattern over ethBooking: three event flags, three drains.

POST /api/booking/event/oncreate → ProcessEventOnCreate

• Prime internal note: Estimator Booking Link: https://forms.ethosbuilding.com.au/booking/estimator/{sharingCode}
• SMS to the contact via SMSOnCreate (unless SkipNotifyContact) and, since UserStory1802, the matching email, each recorded as a Prime "Customer Contact" note (see Customer Contact notes below) - only when the SMS proc actually queued a message.
• SetOnCreate(ID) marks it done.

POST /api/booking/event/onbook → ProcessEventOnBook

• If the booking already has a primeAppointmentID → UpdatePrimeAppointment (re-reads the appointment from Prime first to get a current version, then patches start/end/status). Otherwise → CreatePrimeAppointmentOnBook.
• The create goes through PrimeAppointmentService.CreateAppointmentOnce: if Prime already has a New appointment on the job with the same type, start and end, that one is adopted and saved to the booking instead. A run that dies after Prime commits the create but before the ID is saved would otherwise make a second appointment (and a second OptimoRoute order) on the next tick (AB#1837). VA bookings use the same guard.
• The appointment ID is saved to the booking and its order (spEthBookingSetAppointmentID) only after the create returns. If the appointment queue (§7.1) mirrors the appointment first, spOptimoRouteUpsertOrder adopts the booking's order instead of inserting a second one: the order of a booking on the same job with no appointment yet, and the same appointment type, start and end. Without that, the gap made a second OptimoRoute order every time. From 2026-10-13 the gap was routinely the 3 minutes to the next on-book run (AB#1851).
• New appointments are created with appointmentStatus = "New" and a placeholder primary user — Estimator TBA QLD 0C8A4F7C-… or Estimator TBA VIC 2C8BA534-… depending on job state. The real estimator is written back later by the scheduled-estimator queue (§7.4).
• Notes are assembled from the insured's booking notes plus the AI job summary, falling back to "No additional notes provided".
• Job status patch is guarded by BookingJobStatus.CanPatchToEstimatorBooked — the job is only moved to Estimator Booked from a status a booking is meant to progress. Anything else has been advanced elsewhere and must not be pulled back. Guarded by primeJobStatusUpdatedAt so a reschedule doesn't re-patch a status the job already holds.
• Prime note "Estimator Booked for {date range}" + confirmation SMS and email.

POST /api/booking/event/oncancel → ProcessEventOnCancel

• CancelPrimeAppointment = UpdatePrimeAppointment(job, "Cancelled"), plus a cancellation note carrying the reason code/text, + the "booking is now complete" SMS and email when the visit had been booked - including a customer cancelling their own booked visit. spEthBookingClear no longer resets primeNoteOnCreate, so the booking link is not re-sent after a cancel.

Customer messaging (UserStory1802). Each event's SMS proc (spEthBookingSMSOn*) decides whether the customer is messaged at all and returns how many it queued; the email follows that decision. spEthBookingGetSMSContactDetails returns the email with BookingEmailOverride applied and NULL when BookingSMSEnabled = '0', so the same two switches cover SMS and email, as for VA bookings. Emails come from noreply@ethosbuilding.com.au with the subject prefix "Ethos Job {JobNumber} - ". Any failure in the contact lookup, SMS, email or note is logged and dropped rather than thrown, so the event is still marked done and neither the event's Prime note nor the SMS is re-sent (UserStory1915: a contact with no mobile used to fail the SMS insert and re-post the booking-link note every run). spMakesafeQueueSMSMessage skips a blank mobile, so a contact without one still gets the email.

Customer Contact notes (UserStory1847). Every message sent to the customer - by the field booking events here, the VA booking events (§6.2) and the triage invite (TriageInvite-Spec.md) - is recorded on the Prime job as its own note of type Customer Contact, through IPrimeNotificationService.QueueCustomerContactSMSNote / QueueCustomerContactEmailNote:

SMS to {mobile}
{body}

Email from {from} to {to}
Subject: {subject}

{HTML body flattened by PrimeNoteHtmlConverter.ToPlainText}

• The SMS procs (spEthBookingSMSOn*, spEthVABookingSMSOn*) return ToMobile, Body and MobileOverridden alongside CountMessages, because the SMS text is built in SQL.
• The note is never synced to the insurer's portal: it is sent with syncToEndata = false and syncToNexus = false for every client (UserStory1854). It used to sync Allianz → Endata and Auto & General → Nexus, but a verbatim copy put the booking/triage link in front of the insurer.
• A message a test override redirected (BookingMobileOverride, BookingEmailOverride, TriageInvite*Override) never reached the customer, so its note goes in as an unsynced Internal note marked "Redirected by a test override" instead.
• The note is queued in ethPrimeNotificationQueue when the message is queued, and posted to Prime later by WebAPI (UserStory1949) - see PrimeMessageNotes-Spec.md. A failure to queue it is logged and swallowed, never thrown - the message is already queued and every caller's retry would send it again.
• Messages to staff (case manager, Make Safe team, estimator) are noted too, as Internal notes (UserStory1949). Ops alerts get no note. Nothing in triage or booking messages the instructing client, so there are no Client Contact notes here.
• The manual re-send endpoints (/sms/oncreate, /sms/onbook) do not write a note.

Other endpoints: POST /api/booking/{jobId}/{appointmentTypeId} (create), GET /api/booking/job/{jobId}, GET /api/booking/share/{sharingCode}, POST /api/booking/date, and manual SMS re-sends (/sms/oncreate, /sms/onbook).

Every step is written to optimoRouteLog via _optimoRouteDB.Log(module, action, jobId, appointmentId, bookingId, estimatorId, details, date) — that table is the audit trail for the whole feature, not just OptimoRoute. Per-booking exceptions are caught and logged so one bad row cannot stall the batch.

6.2 VA booking events — VABookingController + VABookingService

Same three-event outbox (event/oncreate, event/onbook, event/oncancel), fired by a 3-minute n8n schedule plus the immediate FormsPortal webhook.

• On book, the Prime job is moved to Estimator Booked — the same status as field bookings, because all three booking types mean an estimator is booked and the job must leave triage. Guarded by PrimeJobStatusUpdatedAt, stamped immediately after the patch so a later failure (note, SMS, estimator email) retries without re-patching.
• Customer messaging respects two site options, applied to both SMS and email:
  • BookingSMSEnabled = '0' → nothing goes out on either channel.
  • BookingEmailOverride / BookingMobileOverride → everything is redirected to the test address/number. The override deliberately wins even when the contact has no email of their own.
  • Internal recipients (assigned estimator, ops alerts) are not silenced by BookingSMSEnabled, because silencing customer contact during a test must not blind the people running it. The estimator email also ignores the overrides. The estimator SMS proc, however, does honour BookingMobileOverride (see VirtualAssessmentBooking-Spec.md).

6.3 OptimoRoute — OptimoRouteController

Endpoint	Method	What it drains
CreateOrdersFromAppointmentQueue	POST	Prime appointments → create/update/delete orders
CreateOrdersFromWorkOrderQueue	POST	Work orders (multi-day) → one order per day
UpdateOrderNotesFromAISummaryQueue	POST	Merge regenerated AI job summaries into order notes
StartPlanningFromQueue	POST	Kick off optimisation runs per date
GetPlanningStatusFromQueue	POST	Poll plan runs to completion
GetPlanningRoutesFromQueue / GetPlanningRoutesRefreshQueue	POST	Pull routes, driver-days and stops back
GetOrderRefreshQueue	POST	Re-push orders whose data changed; self-heal stale IDs
GetCompletionQueue / GetAppointmentCompletionQueue	POST	Mark Prime appointments Completed from OptimoRoute completion status
GetAppointmentScheduledEstimatorQueue	POST	Write the planned estimator back into Prime
ProcessDispatch	POST	The dispatch poll (§8)
EvaluateDispatch	POST	Dry-run the dispatch decision — sends and writes nothing
PushDriverLocations?fromDate&toDate&estimatorID	POST	Re-push relocated drivers' start/end locations and dated hours for a range, by hand (§8.9). Not needed on a schedule.
SendRoutes?date=	POST	Manual/recovery send of one date
DispatchStatus?date=	GET	OptimoRoute's dispatch status for a date (read-only)
OrphanedOrders?fromDate&toDate	GET	Reconciliation report (read-only, capped at 62 days, default today+30)

---

7. Appointment ⇄ Order synchronisation

CommonOptimoRoute/Services/OptimoRouteOrderService.cs.

7.1 The appointment queue → orders

CreateOrdersfromAppointmentQueue() reads spOptimoRouteGetAppointmentQueue and, per appointment:

1. Resolve the job's timezone from State/Suburb/Postcode and convert the UTC start/end to local.
2. UpsertOrder(jobId, appointmentId, bookingId, version, day, workOrderId, contactVersion) — this returns the optimoRouteOrder.ID and an OptimoRouteAction of CREATE or UPDATE. The row is written before any decision, so the ID exists for the orderNo.
3. CheckEstimatorWorkTime — New appointments only. If the appointment falls outside the estimator's work window for that day, their OptimoRoute driver work time for that day is widened to include it, and the change is recorded. Every other status only deletes the order. An OutOfScope appointment's estimator is by definition not an OptimoRoute driver, so their update_driver_parameters call fails. Until UserStory1777 this step ran for every status, so that failure stopped the delete. J015601 on 2026-10-01 was left live under the previous estimator that way. Since #306 the baseline (ResolveWorkTime) is the estimator's own driver hours that date (§8.10), not a fixed 06:00–16:00. It no longer narrows someone on a longer normal week, or an event day, back to 6–4. The hours only ever widen. This also fixed an old quirk: extending only the end time used to reset the start to 06:00.
4. Branch on status:
   • New and SyncToOptimoRoute → build the order and CreateOrUpdateOrder, then store the returned optimoRouteID, geocode result count and the request JSON. A failure throws with OptimoRoute's own code/message.
   • New but sync disabled → log and skip.
   • anything else (Cancelled, Completed, OutOfScope) and the action is UPDATE → delete_order, then EnsureOrderDeleted, UpdateOrderID(ID, null, …) andClearOrderScheduling(ID). If the order had a booking and the appointment is Cancelled, SetToExpired(bookingID) too. That proc sends the customer a cancellation SMS. An OutOfScope or Completed appointment is still going ahead, or has already happened, so its booking is left alone.

The queue's scope (from the proc): branch 1 is version-driven for in-scope estimators (an Enabled row in ethBookingEstimatorSkill, or a QLD/VIC placeholder). Branch 2 returns OutOfScope for any appointment from today on that still holds an optimoRouteID and whose estimator is not in scope. It has no version test (UserStory1801): disabling an estimator in AdminPortal Booking Estimators doesn't change their appointments, so a version-gated branch 2 left their orders live. Branch 3 catches Cancelled appointments that have not yet finished (End >= @Today) and still hold an optimoRouteID. Appointments wholly in the past are deliberately left alone — the order is the record of work that happened, and past orders cost nothing because planning only looks forward.

Completed appointments keep their order — with one exception (UserStory1731 #295, UserStory1751 #301). Completion bumps the Prime version, because GetAppointmentCompletionQueue marks the appointment Completed as soon as the driver closes the stop. Without a guard, the version branches would then delete the order for work that just happened and erase the route record. So a Completed appointment is excluded from all three branches, unless it is in @CompletedBeyondWindow:

• it has a live order (optimoRouteID not null) on a current or future optimoRouteDay; and
• that day was still beyond the planning window at the moment the appointment was completed: optimoRouteDay > fnOptimoRoutePlanningCutoffUtc('VIC', primeAppointments.updatedAt) (as a Melbourne date).

That is work done early, ahead of a day nobody had been routed for yet. Its order would otherwise sit on a route the estimator doesn't need, so it is removed. A completion inside the window is the normal end of the job and keeps its order.

Two details matter:

• Judged at completion time, not run time. Judging it when the queue runs made the result depend on how fast the webhook and queue caught up. An appointment completed at 3pm for tomorrow, but processed after the 4pm cutoff, found tomorrow already inside the window and kept its order. updatedAt is Prime's timestamp for the mirrored version; a later edit only moves it later, which only ever keeps an order.
• The order's day, not the appointment's End. Completing in Prime can rewrite the times to when the work was actually done, so an early completion can carry today's End while its order still sits on tomorrow.

If ethCalendar coverage runs out, the cutoff function returns NULL and the order is kept. Missing calendar data stops completions being deleted rather than deleting the wrong ones.

7.2 Order payload

orderNo is always {JobNumber}-{optimoRouteOrder.ID}. Type = "T", Priority = "M", Location.AcceptPartialMatch = true, Skills = [SkillCode], plus custom fields carrying PrimeJobNumber, PrimeAppointmentId, PrimeAppointmentType, EthosBookingId and client/contact details. Duration is the service duration (e.g. a 45-minute activity inside a 4-hour window), while TimeWindowFrom/To is the window the customer was promised. AssignedTo is omitted entirely when there's no estimator (an empty string is rejected by the API).

Service duration comes from ethBookingService.ServiceDurationMins for the appointment type, read live everywhere a job is costed. Field Quotation is 45 minutes since UserStory1774 (#307, migration 0031; it was 30). That row change is the whole feature. Pending bookings are costed by type, not by what they were booked at, so existing bookings took the new figure immediately. Orders already in OptimoRoute keep 30 until they are re-sent.

ServiceDurationMethod picks how Duration is derived: anything but 2 or 3 = the fixed figure, 2 = start to end, 3 = hybrid (start to end, but never less than the fixed figure). When hybrid extends the duration, the time window's end is stretched to match and a note is added. #307 also fixed a bug here: the window end was stretched by AddHours(minutes), so a 45-minute extension pushed the window out 45 hours. It is now AddMinutes.

AI job summary in notes. MergeAISummaryIntoNotes wraps the summary in sentinel markers (--- AI Job Summary --- … --- End AI Job Summary ---) so regeneration replaces rather than duplicates it. Notes are capped at 20,000 chars. A summary lookup failure is logged and swallowed — it can never block order creation.

7.3 Error handling that matters

• ERR_ORD_NOT_FOUND on an UPDATE → our optimoRouteID is stale. RecoverFromMissingOrderAsync clears it and resends the same order as a CREATE, and logs the self-heal to optimoRouteLog (not just the app log) so the divergence is queryable.
• ERR_MULTIPLE_ORD_FOUND → deliberately does not clear the ID. A CREATE would add a third duplicate, each consuming the estimator's time. Logged as an error for a human to fix in OptimoRoute.
• Unknown failures → left alone so the next run retries, but recorded.
• Timeouts. get_orders and update_driver_parameters give up after 20 s. A healthy call takes under a second. When OptimoRoute's backend stalls, its gateway only returns a 504 after about 60 s, which would use up half of a queue's 120 s budget. In GetOrderRefreshQueue a timeout stops the run without failing it. That run returns the count processed so far and logs the stop to optimoRouteLog; the stalled order and the rest stay queued for the next run. This is safe because get_orders only reads. Any HTTP error status, 5xx included, still fails the call. In CreateOrdersFromAppointmentQueue a timeout still fails the call, because spOptimoRouteUpsertOrder has already recorded the appointment version by then.
• Deletes — OptimoRoute reports business failures as HTTP 200 with success = false. EnsureOrderDeleted throws on a rejected delete so the local optimoRouteID is not cleared and the delete retries. Clearing it on a failed delete is exactly what creates an invisible orphan: the order is still live in OptimoRoute with nothing on our side pointing at it. forceDelete is now sent, because OptimoRoute otherwise refuses to delete any order already on a live plan. "Order not found" counts as success.
• Scheduling columns are cleared alongside the ID. PlanMarkStale skips rows with a null optimoRouteID, so once the ID is gone the plan refresh can never sweep them and the order keeps showing on the calendar as if the estimator were still booked.

7.4 Completion and estimator write-back

• GetCompletionQueue / GetAppointmentCompletionQueue — where OptimoRoute reports completion success, the Prime appointment is marked Completed.
• GetAppointmentScheduledEstimatorQueue — the reverse direction. Once planning has chosen a driver, the appointment in Prime is updated to that primaryUserId (replacing "Estimator TBA"). It also calls SetJobEstimator(..., overwrite: false) — a job that already has an estimator keeps them, because the job estimator and the person who attends the appointment are legitimately different people. Each appointment is re-read from Prime first for a current version; one failure doesn't stop the batch.

7.5 Work orders

CreateOrdersfromWorkOrderQueue handles multi-day work orders by looping every date from Start to End and creating one order per day with the same start/end times. Orders exist only while the work order is Locked or In Progress. CheckEstimatorWorkTime runs only for those two statuses, for the same reason as §7.1 step 3: a Cancelled or Completed work order only deletes its orders.

Only work orders assigned to an enabled field estimator are sent. spOptimoRouteGetWorkOrderQueue has a second branch, like §7.1 branch 2: a work order not yet finished (end >= @Today) that still holds an optimoRouteID, and whose assignee has no Enabled skill row, comes back as OutOfScope and its order is deleted. That covers an estimator disabled or deleted in AdminPortal and a work order reassigned to someone who isn't a field estimator. Before UserStory1801 the proc joined the skill table without testing Enabled, so a disabled estimator's work orders kept syncing.

Re-enabling an estimator does not re-send their deleted orders until the appointment or work order next changes in Prime: the order row already holds the current version.

---

8. Cutoffs, planning and scheduling

This is the part most worth understanding, because one configured time drives several different behaviours and they are deliberately kept in step.

8.1 The one setting

dbo.ethBookingConfig → RoutePlanningCutoffTime, default 16:00, edited in AdminPortal Settings → Booking, migration 0011. It is read by four things that must never disagree:

Consumer	Object	What the cutoff means there
Booking availability	dbo.fnEthBookingDayAvailability (inline TVF)	The earliest bookable day
Planning window	dbo.fnOptimoRoutePlanningCutoffUtc	When the window for new orders closes
Dispatch	dbo.fnOptimoRouteDispatchDate	Which date's routes should be live
Planning run	OptimoRoutePlanningService.GetRoutePlanningCutoffTime() (C#)	Whether to re-plan freely or lock existing routes

Every one of them falls back to 16:00 if the row is missing or unparseable — a bad config edit must not stop route planning or change who is bookable by accident. The C# constant DefaultRoutePlanningCutoff must stay identical to the SQL ISNULL fallbacks; a divergence means C# and SQL disagree about which days are bookable.

ManagerBookingCutoffTime (default 06:00, AB#1922) is separate: how long into the planned day a Booking Manager may still book it (§8.2). It changes nothing else.

Related config: DispatchOperatingStates (default VIC,QLD,NSW), DispatchEnabled, DispatchMinResendGapMins (default 15), DispatchAlertAfterMins (default 30), DispatchDriverEmailEnabled, DispatchDriverEmailCc.

8.2 The booking cutoff — which days are offered

In fnEthBookingDayAvailability, using the job's timezone:

CutoffDate = local today + 1     -- before the cutoff time
           = local today + 2     -- at/after the cutoff time

So booking on Tuesday morning offers Wednesday onward; from 16:00 Tuesday it offers Thursday onward.

@AfterCutoff = 1 (AB#1922, Booking Advanced only). A Booking Manager may book a day until ManagerBookingCutoffTime (default 06:00, migration 0045) on that day itself:

CutoffDate = local today        -- before 06:00
           = local today + 1    -- from 06:00
IsAfterCutoff = Date < the normal CutoffDate

So from 16:00 Tuesday Wednesday is offered again, until 06:00 Wednesday. Every other caller passes 0, which gives the old rows, and IsAfterCutoff is then always 0.

Days are then filtered per estimator to the days they work (IsWorking = 1 from fnEthBookingEstimatorDayHours, §8.10). With no schedule rows that is exactly the old rule: Mon–Fri and not a public holiday (ethBookingDaySlot.IsPublicHoliday). A dated extra-hours row makes that date a working day for that estimator, whatever the weekday or holiday.

The job's state is its booking state. The job, and every other pending booking, is booked as the state fnEthBookingStateMap() maps it to. NSW → QLD (config BookingStateMap, migration 0027) until there is a NSW office. A top-edge NSW job is offered QLD estimators, QLD day slots and QLD's 8–2 in QLD time. That is 9–3 NSW local time in summer, because QLD has no daylight saving. To open a NSW office, delete the config row. Not mapped yet: spOptimoRouteGetAllowAdvancedBookings' unassigned-state path.

The capacity calculation, per eligible estimator per day:

• Eligible estimators:

  • home estimators — enabled ethBookingEstimatorSkill rows in the job's booking state, holding the skill the appointment type needs, except on dates they are relocated to another state;
  • relocated in — estimators with that skill enabled in any state and a relocation into the job's booking state covering the date.

• SlotMins, FromTime, ToTime = the estimator's own booking window that day (§8.10), not the state's. On a normal day that is still the state's 08:00–14:00.

• JobMins = the appointment type's ServiceDurationMins (default 30; Field Quotation 45).

• TravelMins = this estimator's driving time to this job from their base that day (a relocation point in this state, else their home point) via ethBookingPointDistance. It falls back to the closest candidate base, then 30. (It replaced ethBookingEstimatorJobDistance, which is kept only until the new WebAPI is live everywhere.) The fallback now uses enabled estimators only.

• RouteMins = optimoRouteDriverDay.DurationMins — what OptimoRoute has already planned for them.

• AssignedPendingMins = bookings that have a date but no planned order yet (the gap between a customer booking and the next planning run), assigned to this estimator. The job's own booking is excluded so a rebooking doesn't count against itself. So is a booking whose Prime appointment is Cancelled (UserStory1852): cancelling in Prime doesn't set CancelReasonCode, and the AB#1851 cleanup left bookings on cancelled duplicates that were counted again on top of the kept appointment's routed order. Each is charged its service minutes plus the team minimum travel to it, over every enabled estimator's home plus relocations that haven't ended. vEthBookingEstimatorDayLoad and the marginal-travel stops (§8.3) apply the same two exclusions.

• UnassignedPendingMins = the same pool, not yet assigned to anyone.

• FirstLegAllowanceMins (UserStory1886) = the lead-in: booking window start − driver start that day. Driver start is the weekly DriverFrom, else the window − DriverHoursBufferMins (120), so normally 08:00 − 06:00 = 120. A dated-hours day uses the plain buffer.

• FirstLegCreditMins (UserStory1886) = MIN(first leg, FirstLegAllowanceMins) on a planned route day, where the first leg is the earliest planned order's ResponseTravelMins. 0 when nothing is routed or OptimoRoute didn't report the leg. Pending bookings get no credit: they're charged team-minimum travel, not a real leg.

The function returns the conservative answer, charging the full TravelMins unless the day is empty:

JobTravel     = MAX(TravelMins - FirstLegAllowanceMins, 0)   -- empty day: the job is the first stop
              = TravelMins                                   -- otherwise
RequiredMins  = JobMins + JobTravel
CommittedMins = RouteMins - FirstLegCreditMins + AssignedPendingMins
RemainingMins = SlotMins - CommittedMins
IsFeasible    = CommittedMins + RequiredMins <= SlotMins

Why the first leg is free (UserStory1886). OptimoRoute's driver hours start before the booking window (06:00 against 08:00) so the estimator can reach the first job by its start. That drive used to come out of the six-hour booking window as part of RouteMins. Now only the part longer than the lead-in does: an estimator who must leave at 05:15 to reach an 08:00 job is charged 45 minutes. Grant Mills (Melbourne's far side) drives 55–120 minutes to his first job, so on dev his 16 Oct 2026 day went from 285 route → 75 remaining to 192 committed → 168 remaining (first leg 93). RouteMins is still OptimoRoute's raw figure. Only CommittedMins/RemainingMins change.

And for the day as a whole (can the team absorb the unassigned pool and this job?):

TeamRemainingMins = SUM(MAX(RemainingRaw, 0)) OVER (PARTITION BY Date)
IsDayFeasible     = UnassignedPendingMins = 0
                    OR TeamRemainingMins >= UnassignedPendingMins + RequiredMins

Negative remaining is clamped to zero so an overbooked estimator can't lend capacity they don't have.

What the callers actually offer (UserStory1852). RouteMins already starts at the base: optimoRouteDriverDay.DurationMins is the sum of each planned order's travel-in plus its work, so it runs base → stop 1 → … → last stop and ends there, with no drive home. Charging TravelMins on top of it counts the drive from home a second time. So spEthBookingGetAvailabilityDetails and spEthBookingGetAvailabilityGeo re-test every estimator-day with the travel the job adds to that day (MarginalTravelMins, §8.3) in place of TravelMins:

AddedTravel   = ISNULL(ChargedTravelMins, RequiredMins - JobMins)
fits          = CommittedMins + JobMins + AddedTravel <= SlotMins
                AND (UnassignedPendingMins = 0
                     OR TeamRemainingMins >= UnassignedPendingMins + JobMins + AddedTravel)

ChargedTravelMins (UserStory1886) is MarginalTravelMins with the first-leg lead-in applied (§8.3). On an empty day it is MAX(base → job − lead-in, 0), the same as the function. The function itself still returns the conservative bits: the insertion cost needs procedural code, which an inline TVF can't hold. Example, J015799 (Chirnside Park) on Fri 16 Oct 2026: the estimator's route already passed Croydon, 4 minutes away. The old rule charged 285 route + 45 job + 88 base leg = 418 against 360 and showed Busy. Slotting it in really costs 285 + 45 + 4 = 334.

SlotMins stays the measure, not the OptimoRoute driver hours. The driver hours are wider (e.g. 06:00–16:00) to leave room for travel at either end of the day. Since UserStory1886 the morning end of that room is used: it absorbs the first leg (above). The afternoon end isn't. The day is open-ended, so the last job's drive home was never charged.

The day-load view applies the same credit. vEthBookingEstimatorDayLoad.RouteMins is DurationMins − FirstLegCreditMins, using fnEthBookingEstimatorDayHours' DriverFrom. So the KPI alert (§9.2) and the Booking Availability overview (§10) read the same load the booking page does. There the column is the load on the booking window, not OptimoRoute's raw duration.

Performance note baked into the definition (v5): fnOptimoRouteSearchCutOff is no longer called here. It is a multi-statement scalar UDF — invoked per row, forcing a serial plan and hiding its own cost from the optimiser — and it computed this exact rule anyway. Inlining it is also more correct: the old UDF hardcoded AUS Eastern for its base date, so WA/QLD/SA/NT could land a day out. This uses the job timezone throughout. fnOptimoRouteSearchCutOff still exists for other callers; prefer the calendar-aware functions.

Performance note (UserStory1727): the first relocation-aware version called fnEthBookingEstimatorSkillOnDate / fnEthBookingJobMinTravelMins per day and per pending booking. Same rows, but ~730 ms CPU per job against v5's 11 ms, because the optimiser re-ran each nested function per row. The shipped version keeps v5's single-pass shape: the job's distances are fetched once, relocations are small side lookups, and the team bases are joined to pending bookings, not correlated. Measured 28–30 ms CPU per job on dev. Migration 0026 adds the covering indexes it seeks on. The schedule tables (#306) are joined on plain columns for the same reason: going through the hours function's own eligibility logic cost 78 → 256 ms. Do not reintroduce per-row calls to the date functions here. They remain the single-date lookups for procs that call them once (spOptimoRouteGetAllowSpecificTimeBookings, spEthBookingSetDate, spEthBookingClear).

8.3 Marginal travel and nearby — capacity and ranking

Capacity is charged the travel the job adds to the day (UserStory1852). In fnEthBookingDayAvailability, TravelMins is the estimator's one-way drive from their base that day to this job (§8.2). The two booking procs replace it with MarginalTravelMins when they test whether the job fits. RouteMins already includes the drive from the base, so charging the base leg again double-counted it (§8.2, "What the callers actually offer"). On an empty day the two figures are the same.

spEthBookingGetAvailabilityGeo, the proc FormsPortal calls, takes every estimator-day that could fit at zero added travel (CommittedMins + JobMins <= SlotMins, and the team gate likewise). It prices them all with the helper below, drops the ones that don't fit, then ranks what's left. The nearby tag and the KPI tiers only ever order the dates that fit.

Before UserStory1852 (2026-10-14) marginal travel was ranking only, and capacity always charged the full base leg. §12 records why that was kept, and why it changed.

Charged travel (UserStory1886). The helper also returns ChargedTravelMins, which is what capacity is tested with. Each position is priced as for MarginalTravelMins, except a job slotted in front of the first stop (or onto an empty day) becomes the new first leg:

charged = insertion cost - MIN(base -> job, lead-in) + FirstLegCreditMins

The job's own leg gets the lead-in, and the old first stop's credit (already taken off CommittedMins) is handed back, because its leg is now an ordinary one. Anywhere later, the first leg is unchanged and the charge is the plain insertion cost. The callers pass the function's FirstLegAllowanceMins and FirstLegCreditMins in #MarginalTravel.

MarginalTravelMins stays the real added travel, and ranking still uses it. With the lead-in in it, every empty day near home would count as "efficient" and outrank a day with a booked job next door.

Marginal travel (UserStory1727, #305)

Marginal travel replaced the old "a booked job nearby" rule. That rule only asked whether one other booking was within a threshold. It also generalises the old rule: it counts where the day starts, and a day with two stops five minutes either side of the job wins even when neither stop is "nearby" on its own.

spEthBookingJobMarginalTravel builds each candidate estimator-day as it stands:

• Start at the estimator's base that day (a covering relocation, else home). The day is open-ended: travel home isn't counted, matching the engine's one-way base → job.
• Stops, in time order: planned OptimoRoute orders by ScheduledAt, then assigned dated bookings not yet in a planned order, by FromDate. Bookings whose Prime appointment is Cancelled are skipped, the same as the pending pool (§8.2).
• Price every gap as the insertion cost d(k, job) + d(job, k+1) − d(k, k+1), and after the last stop d(last, job). MarginalTravelMins is the cheapest gap. On an empty day it is base → job, the same as the engine's TravelMins.
• Missing legs are estimated from the straight-line distance, with lines fitted by least squares to every cached Google pair on dev. Under 25 km the estimate is 8 + 1.27 min/km; from 25 km it is 19 + 0.76 min/km. A flat 30 made far-apart legs look cheap, because two guesses cancel out in the insertion cost. If a point itself is missing, base ↔ job falls back to the engine's TravelMins and anything else to 30. MissingLegs counts the estimates, and AdminPortal marks them with *.
• Other outputs: DayTravelMins, NearestStopMins (from cached legs only) and StopCount.

The caller fills #MarginalTravel with the candidate estimator-days and passes the engine's own TravelMins as BaseToJobMins, so an unknown distance never outranks a known one. The Details proc passes its TOP 40 rows. The Geo proc passes every day that could fit at zero added travel.

An efficient day adds no more than the nearby limit:

Config key	Default	When
NearbyTravelMaxMins	20 min	Metro
NearbyTravelMaxMinsRegional	45 min	Regional
MetroRadiusKm	50 km	Job further than this from every ethBookingOffice ⇒ regional

All keys resolve state-specific first, then the global row. Because the day is open-ended, any job within the limit of an estimator's base counts as efficient on an empty day, not only one near a booked job. That was decided on 2026-09-30: a job close to home is cheap either way, and OptimoRoute will reorder the day around it.

What it influences — whether the job fits (above), and two kinds of ordering:

1. Which estimator represents the day. One row per date, chosen by ROW_NUMBER() OVER (PARTITION BY Date ORDER BY MarginalTravelMins, RemainingMins DESC, EstimatorID). Of the estimators who can take it, the proc prefers the one whose day it adds least travel to, then the one with the most room. "Chooses among options; never creates one."

2. The order of the dates shown to the customer, by a four-tier priority, then date, then marginal travel, capped at TOP 10:

   Priority	Meaning
   1	Efficient and within the booking KPI
   2	Within the KPI (BookingKPIDays, default 3)
   3	Efficient
   4	Everything else

"WE'RE IN YOUR AREA" (NearbyBooking, rendered by EstimatorUnbooked.razor alongside the RECOMMENDED / Later date tags) is kept separate from the ranking so that it stays literally true. It means a real stop on that estimator's day is within the nearby limit (NearestStopMins <= limit), not just that the day is efficient.

New Geo proc columns: MarginalTravelMins, NearestStopMins. spEthBookingGetAvailabilityDetails (Booking Advanced) returns the same five helper columns, shown as "+X travel" (§5.2).

Cost. On dev, the Geo proc went from 62 to 79 ms CPU per job; the helper is about 15 ms of that. The Details proc is 41 ms. UserStory1852 prices more candidate days. Over 15 VIC/QLD jobs on a fresh prod copy (2026-10-14, minimum of 3 runs), total CPU went from 2,772 to 2,964 ms for Geo (+7%) and from 2,061 to 2,221 ms for Details (+8%). The function and the day-load view were unchanged (809 → 803 ms, 66 → 64 ms). The cancelled-appointment check costs nothing only in the shape that shipped. It is an OUTER APPLY TOP (1) PK seek, plus migration 0038, which adds primeAppointmentID to IX_ethBooking_FromDate_Pending. Written as NOT EXISTS it was planned as two scans of primeAppointments. Without the include, ethBooking was scanned. Together that made the function +53%.

UserStory1886's first-leg lookup is one pass over planned orders from the cutoff on, seeking on migration 0043's filtered index (optimoRouteDay, ScheduledEstimatorID, ScheduledAt). Without the index it was a clustered scan: optimoRouteOrder went from 78 to 1,124 reads per call, because the row carries the order JSON. With it, 81. Over the same 15 VIC jobs on dev (2026-10-15, minimum of 3): function 753 → 804 ms, Details 1,161 → 1,147, Geo 1,904 → 2,014, and the day-load overview 63 → 78 ms.

Where the job-to-job legs come from

Job-to-job travel is keyed by location point in its own table, ethBookingJobPointDistance (migration 0029), read through vEthBookingJobPointDistance. It was first built on the shared point cache (0028). That grew the cache from 19k to 50k pairs and more than doubled the availability function's cost (30 → 71 ms), because each job point gained hundreds of pairs the availability engine never reads. Points stay one shared table; only the pairs are split by purpose. Jobs at the same address are 0 minutes apart.

spEthBookingJobPointDistanceQueue fills it with:

• pending-booking job × future booked job, same booking state, inside a straight-line radius (30 km metro, 70 km regional or during a CAT event), with future bookings bounded to JobJobDistanceWindowDays (default 31; there used to be no upper bound);
• every pair of stops on the same estimator-day in the window, with no radius, so day legs fill in over time.

Also display-only: the travel/work ratio. RatioValue is the chosen estimator's planned day: (SUM(ResponseTravelMins) + this job's travel) / (SUM(ResponseDurationMins) + this job's minutes). RatioExceeded compares it to MaxTravelWorkRatio, or to MaxTravelWorkRatioCATEvent when the CATEventStatus site option is level1. It lives in the proc rather than the function only because a function cannot EXEC spMakesafeSiteOptionGet. The header notes that this is safe precisely because it cannot affect whether a date is available.

Performance history: nearby used to be two CTEs inside fnEthBookingDayAvailability, one referencing the other twice, in a pipeline that was itself expanded twice. That was about eight scans of the job-pair table, which grows with the square of the job count. v5 moved it out to the Geo proc, built once and referenced once. If you add a ranking input, add it to the proc's ranking, not to the function's feasibility.

AdminPortal equivalents (both on BookingAdvanced, staff-facing, informational):

• OptimoRouteNearbyJobs (spOptimoRouteGetNearbyJobs) leads with one suggested job as a sentence (its date is the Jump to target), with the full grid behind a toggle. It flags whether that estimator or anyone else is free on the neighbouring job's plan date: an opportunity. The suggestion is ranked by: that job's estimator is free that day; the date is within BookingKPIDays (today in the job's timezone + N, as in the Geo proc); least "+X travel" for that estimator that day; anyone free; then distance and date. So it is the least added travel inside the KPI, else the least added travel overall. Straight-line distance is only a tie-breaker. A job slotted between two stops can add less travel than one next door. The page loads the list (on job load and on every Search, so a booking made while the page is open shows up) and uses the same rows to highlight each availability date with a nearby job, and to put a Nearby chip on the estimator whose job it is. The estimator is matched by name (ScheduledEstimator). This is spOptimoRouteGetNearbyJobs's straight-line radius, not FormsPortal's NearestStopMins, so it works before Google has fetched the leg. A separate starts nearby banner appears when any bookable estimator-day starts (home, or a relocation base) within NearbyTravelMaxMins of the job, using the engine's TravelMins. It shows the shortest such travel time and, like the nearby banner, doesn't name the estimator. It's a banner rather than a per-cell mark because it applies to every day that estimator works. That is the other reason "+X travel" can be low with no booked job close by. It uses the metro limit only. FormsPortal's ranking already counts the start point, because marginal travel begins at the base. Its "We're in your area" tag deliberately doesn't: it would tell a customer the estimator lives nearby.
• BookingSameSite (GetBookingsBySite) is warning-toned, because "double-booking a site is a mistake worth noticing rather than an opportunity like the nearby job."

8.4 The planning window — fnOptimoRoutePlanningCutoffUtc(@State, @NowUtc)

Returns the UTC instant at which the planning window for new orders closes: end of the current Melbourne business day (23:59:59 MEL) when it is before the cutoff and today is a business day, otherwise the end of the next business day. Business days come from dbo.ethCalendar, so public holidays are honoured — that is why this, not the weekend-only arithmetic in fnOptimoRouteSearchCutOff, is the function to call. Returns NULL rather than guessing when calendar coverage runs out.

Melbourne is deliberate. @State selects which holidays apply, never which clock is used — the OptimoRoute planning run is Melbourne-anchored. The booking-side objects are per-state by design; this one is not. Don't "fix" it into a per-state timezone without moving the planning run with it.

8.5 The planning run — OptimoRoutePlanningService.StartPlanningFromQueue()

Dates to plan come from GetOptimoRoutePlanQueue, which returns every NotPlanned date that spOptimoRoutePlanUpsert queued. Today can be queued. Only days already gone are parked as E:PastDate (UserStory1827; it was >= and parked today too). So a cancelled order or leave set today re-plans the day under the inside-window rules below, and route dispatch resends to the drivers whose day changed. The cutoff is read once for the whole queue, not per date — it's a single row, and it must not change midway through a run or two dates in the same batch would be planned under different rules.

Per date:

• Outside the window (i.e. the plan date is far enough out) → a free optimisation: OptimoRoute may move anything. First, since UserStory1727, it pushes any outstanding driver locations and dated hours for that date (PushDriverLocations(planDate, planDate, requeuePlanning: false), §8.9). This is the retry for failed pushes; there is no separate schedule. The push unschedules that driver's routes, which is harmless here because the plan is about to rebuild them. A push failure is logged and never stops the plan. (This path was untested on dev at merge, because planning on dev may queue admin SMS.)
• Inside the window — after the cutoff the day before, or the plan date is today → StartWith = "CURRENT", LockType = "ROUTES". Existing routes are retained; only new orders get fitted in. Drivers may already have been told their day; it must not be reshuffled underneath them.
• A full re-plan was requested (AB#1922). A Booking Manager booked the date after the cutoff (§5.2), which sets optimoRoutePlan.FullReplanStates. That date is planned with StartWith = EMPTY, LockType = NONE, and only those states' drivers and orders: useDrivers by driverExternalId, and useOrderObjects by orderNo, from spOptimoRoutePlanGetStateScope.
  • The other states' routes are left out of the plan. This was tested on dev 2026-10-17 with one driver only: EMPTY is accepted after send_routes and fully re-sequences a sent route, while CURRENT/ROUTES returns ERR_OPT_NO_RESOURCES. Whether drivers left out of useDrivers keep their routes untouched could not be proven with one driver. Watch the first real run.
  • The driver-location push is skipped.
  • If OptimoRoute refuses the full plan, the date is planned the normal inside-window way instead, and the ROUTE_SEQ retry still applies.
  • FullReplanStartedAt is set once the plan is sent, so a later re-queue of the date plans normally.
  • When the routes are back (GetPlanningRoutesFromQueue), spOptimoRouteGetNotScheduledForDate lists anything left unscheduled. Those orders are emailed to bookings@ethosbuilding.com.au (OrdersNotScheduledEmail.html, PDP-style chrome), and the request is cleared.
  • Each step is in the day's change log (§9.4). Dispatch then resends the changed routes as usual (§8.7).
• ERR_OPT_NO_RESOURCES with ROUTES → retry with the undocumented LockType = "ROUTE_SEQ", which also preserves the planned sequence. This has been seen when route notifications have already gone out.
• ERR_OPT_NO_REQUESTS ("no orders for this date") → not an error. DriverDayClear(planDate) and log it.
• Any other error → SetPlanStatus(date, "E:{code}") and an exception SMS to admins.

Success stores the PlanningID and sets the plan to Planning; GetPlanningStatusFromQueue polls it to completion, then GetPlanningRoutesFromQueue pulls the result back.

8.6 Pulling routes back — the run-ID tagging trick

get_routes only returns orders OptimoRoute has actually planned, so unplanned ones must be worked out by elimination. The day is not blanked first. Every reader in the solution uses WITH (NOLOCK), so a cleared-then-refilled day is visible as "nothing is scheduled" for the whole width of the API call — which fires spurious not-scheduled alerts — and an API failure would leave the day blanked until the next run.

Instead: tag every row for the date with a run ID (touching no scheduling columns) → the upserts clear that tag in the same write that stores the real value → only after the whole response is processed successfully is whatever still carries the tag cleared as genuinely unplanned.

This populates optimoRouteDriverDay (per-estimator planned minutes — which feeds straight back into availability) and the per-order stop/scheduling columns.

8.7 Dispatch — fnOptimoRouteDispatchDate and OptimoRouteDispatchRules

Which date is live (fnOptimoRouteDispatchDate(@NowUtc)): today while before the cutoff, otherwise the next business day — in any operating state.

Now (Melbourne)	Returns	Why
Tue 10th 09:00	Tue 10th	still inside the window Mon 16:00 opened
Tue 10th 16:00	Wed 11th	the cutoff send
Tue 10th 16:30	Wed 11th	the resend window for Wed
Fri 18th 16:00	Mon 21st	skips the weekend
Fri 18th 16:00, Mon a holiday	Tue 22nd	skips the holiday too

send_routes takes one account-wide date and OptimoRoute allows only one live date at a time, so per-state dispatch isn't expressible. On a VIC-only public holiday the any-state rule still dispatches, because QLD is working — VIC estimators have no orders that day, so it costs them nothing. Narrowing it to VIC would strand QLD. NULL (calendar exhausted) means do not dispatch.

The decision is a pure function — OptimoRouteDispatchRules.Decide(...), no clock, no database, no network — so every branch is a unit test. The service does the I/O and hands it the answers.

Situation	Action
No dispatch date resolved	Alert — ethCalendar coverage has run out
We sent it, OptimoRoute says it isn't live	Alert — somebody sent another date from the OptimoRoute UI; a person must decide
Before the cutoff, never sent	None
Past the cutoff, plan not finished	None — only a FINISHED plan may be dispatched
Past the cutoff, plan finished, never sent	SendAtCutoff
Sent, no changes reported	None
Sent, changes, last send < DispatchMinResendGapMins	None — throttled, so a run of small changes becomes one resend
Sent, changes, throttle elapsed	Resend

The finished-plan guard exists because without it a quiet day gets a send_routes call on every poll: OptimoRoute refuses with ERR_NOTHING_TO_SEND, the refusal is recorded as Success = 0 so the date stays "not sent", and the next poll tries again — roughly 200 pointless calls overnight. It also stops a half-built plan reaching drivers.

Adopting an external send. If OptimoRoute reports the date as transmitted but our history has no successful send, AdoptExternalSend records it — someone pressed Send in the OptimoRoute web UI. The recorded time is OptimoRoute's sentAtUtc, not now, because the throttle and every "minutes since" figure must describe the send that actually happened. This is the one write in an otherwise read-only evaluation, and it corrects what the decision is made from, not the decision itself.

Alerting is separate from action (EvaluateAttention). Folding an alert into the action would stop the send, and the point of a late-dispatch alert is that the dispatcher should keep trying while someone looks. It alerts on the outcome — "past the cutoff and still not dispatched by DispatchAlertAfterMins" — not the cause, so it catches causes this code doesn't know about. The exceptions, which can't self-heal by retrying, alert on sight: no dispatch date, a live-date mismatch, and a send accepted but never confirmed as transmitted.

The two-switch interlock. Nothing sends unless both are true:

1. OptimoRoute:DispatchAllowed — per-environment appsettings/user-secrets.
2. ethBookingConfig.DispatchEnabled — the operational on/off in AdminPortal Settings.

Two switches because they fail differently and one of them travels. DispatchEnabled lives in the database, and dev is restored from prod — so the moment dispatch is enabled in production, every subsequent dev refresh copies DispatchEnabled = 1 into dev, silently arming a non-production environment. Pointed at a production OptimoRoute key it would dispatch real routes to real drivers and SMS real customers. The appsettings flag doesn't travel with a restore.

With either switch off, ProcessDispatch is a dry run and blockedReason says what it would have done. EvaluateDispatch is the endpoint to poll while watching a cycle before anything reaches a driver's phone.

Polling, not cron. ProcessDispatch should be called every few minutes, not on a 16:00 cron: it decides for itself whether the cutoff has passed, so it honours RoutePlanningCutoffTime rather than drifting from it, and a missed run sends late instead of never. ProcessDispatch and the manual SendRoutes share the same lock key — a manual send racing the poll could send the same date twice.

SendRoutesManual skips the cutoff and the throttle (that's what makes it a recovery tool) but not the interlock. Sending a date makes it OptimoRoute's live date, replacing whatever was live — don't point it at an arbitrary date on a working system.

8.8 Driver route emails

A successful dispatch queues one email per driver (QueueDriverEmails → OptimoRouteDriverEmailBuilder).

• Gated by DispatchDriverEmailEnabled (default on). DispatchDriverEmailCc is a semicolon-separated copy list, matching the n8n queue flow's parseRecipients; blank means no copy.
• Stops come from spOptimoRouteDispatchGetDriverRoutes, grouped by estimator and ordered by scheduled time.
• Only drivers whose day actually changed are emailed. BuildRouteHash is a stable hash of exactly the fields the email shows — ordered by scheduled time, because a resequenced day IS a changed day even when the same stops are on it. It deliberately does not hash the rendered body (which carries a send time, so every route would look changed) or the database row (whose UpdatedAt moves when nothing the driver cares about has). If you add a column to the template, add it to the hash.
• A matching hash means their day is identical to what they already hold — someone else's changed — so they get nothing. A non-empty previous hash makes it an UPDATED route email.
• Subject is Route - {ddd d MMM} - {EstimatorName} (date first so subjects sort and group by day). The driver's name is there for the Cc list, who receive a copy of every driver's email.
• A driver with stops but no email address is logged as a warning rather than silently skipped — the proc returns them deliberately so "why did this driver get nothing" is answerable.
• RecordDriverRouteEmail is written after queueing. The other order would mark a driver as told about a route they never received, and no later resend would correct it, because the hash would then match.
• Styling follows the PDP form emails: an embedded-resource chrome template (CommonDataAccess.Data.Email.EmailTemplates.DriverRouteEmail.html) with { @Token } placeholders and a generated middle. It departs from PDP in one place — a route is a list, not label/value pairs, so stops get a three-column table that wraps properly on a phone, which is where it is actually read. The builder is pure (no clock, database or network), so the email can be rendered and eyeballed without sending anything.

8.9 Driver start/end location push — relocations (UserStory1727)

A relocated estimator's OptimoRoute day must start and end at the temporary base. Otherwise OptimoRoute plans a QLD day as if it starts from the VIC home.

OptimoRouteDriverLocationService sends one bulk update_drivers_parameters call per ≤500 driver-dates. The driver is identified by externalId = the estimator's full name, as for work times. Each date is sent either as custom (start = end = the relocation point) or as employeeDefault, which puts the date back to the driver's normal location without us needing to know it. Parameters are per date, so the day after a relocation ends needs no call at all. Only dates removed from an already-pushed relocation are sent back to employeeDefault. The location push omits workTime, so the driver's standard hours and any CheckEstimatorWorkTime extension for that date survive. Dated hours (§8.10) ride on the same call.

The API warns that any existing routes for that driver and date are unscheduled. That decides when to push:

When	What happens
On save in AdminPortal (create, change, cancel, End now, Retry)	Every affected date outside the plan window is pushed, then re-queued for planning (spOptimoRoutePlanUpsert). Orders tied to the old location drop out straight away, so under-capacity shows days ahead rather than the evening before. A push failure never blocks the save; the row is recorded Failed.
Before each re-plan of a date outside the window	Outstanding pushes for that date are sent (§8.5). This is the retry.
Inside the plan window (today, or tomorrow after the cutoff)	Never pushed automatically. Pushing would unschedule locked routes that may already have gone to drivers and customers. The date is recorded ManualRequired, and someone changes it in OptimoRoute by hand and clicks Done in OptimoRoute. Booking-side eligibility still changes, because that is our data.
Past dates	Skipped.
By hand	POST /api/OptimoRoute/PushDriverLocations.

Tracking is ethOptimoRouteDriverLocationPush (migration 0024; PK estimator + PlanDate): PointID (NULL = employeeDefault), RelocationID, Status (Pushed / Failed / ManualRequired / ManualDone), response code and message, and who confirmed a manual change and when. spOptimoRouteDriverLocationPushQueue returns the (estimator, date) rows where the desired state (fnEthBookingEstimatorBase) differs from the recorded state, plus Failed rows. The on-save push, the pre-planning push and Retry all read it, so there is one rule for what needs sending.

What the re-plan does across the two states. Outside the window the re-plan is date-wide (EMPTY, balanced by working time):

• in the new state, unpinned orders are rebalanced across its drivers, now including the relocated estimator starting from the hotel;
• in the old state, the relocated estimator's unpinned orders are spread over the remaining drivers, and whatever doesn't fit is left unscheduled and shows in Orders Not Scheduled (§9.3);
• orders pinned to the relocated estimator (AssignedTo set, §5.2 / §7.2) cannot move and go unscheduled. Fixing them means reassigning the appointment or booking. This is the list the relocation dialog warns about.

Scoped planning (useDrivers / useOrderObjects) was considered and rejected: the next routine date-wide run would undo it, and per-state planning doesn't fit the Melbourne-anchored planning run.

8.10 Estimator schedules — booking hours and driver hours (UserStory1727, #306)

dbo.fnEthBookingEstimatorDayHours() resolves one row per estimator, state and date. The most specific layer wins:

Layer	Where	Meaning
1. Default	ethBookingDaySlot + config	Mon–Fri, not a public holiday. The state's day slot (08:00–14:00) is the booking window. Driver hours are the window ± DriverHoursBufferMins (120), so 06:00–16:00.
2. Weekly pattern	ethBookingEstimatorWeeklyHours	One row per estimator per weekday that differs. NULL times fall through to the default. E.g. an estimator with more travel: Mon–Fri driver hours to 16:30.
3. Dated extra hours	ethBookingEstimatorDayHours	One row per estimator per date. That date becomes a working day with this booking window, whatever the weekday or public holiday.

Driver hours on a dated row:

• On a normal working day, the window ± the buffer, never narrower than normal. An 8–4 event day gives 06:00–18:00.
• On a day off, just the window ± the buffer. A Saturday booked 8–12 gives 06:00–14:00.

Dated rows only ever add time. They never remove a day; leave stays as appointments.

Readers:

Object	Uses it for
fnEthBookingDayAvailability	FromTime, ToTime, SlotMins and remaining minutes; IsWorking replaces the Mon–Fri and holiday tests (§8.2)
vEthBookingEstimatorDayLoad	Same window and IsWorking (in place of its own slot-estimator copy)
spOptimoRouteGetAllowSpecificTimeBookings	The named estimator's window; 0 on a day they don't work
spEthBookingGetAvailabilityGeo / ...Details	Unchanged — a Saturday or event day just appears
BookingAdvanced / BookingConfirmTime	The chosen estimator's window; Morning/Afternoon are its first/last four hours (§5.4)
OptimoRouteOrderService.ResolveWorkTime	The baseline for CheckEstimatorWorkTime (§7.1)

To OptimoRoute. The weekly pattern is not pushed; set the driver's normal hours in OptimoRoute to match. Dated hours ride on the relocation push (§8.9). Migration 0030 added Enabled, WorkTimeFrom, WorkTimeTo and HoursOverride to ethOptimoRouteDriverLocationPush, and the queue returns an HoursAction:

• Override — dated hours apply. It sends enabled: true and workTime = the driver hours.
• Reset — dated hours were pushed and then removed. It sends the normal hours, and switches a normally-off day back off.
• NULL — no work time is sent, so appointment-driven extensions survive.

The same timing rules as relocations apply: push on save and re-queue planning, record plan-window dates as ManualRequired, and retry failed dates before each re-plan.

Known gap: a ManualRequired date caused by hours shows in the relocation dialog's "OptimoRoute changes" list with the location wording.

---

9. Alerting and monitoring

9.1 Dispatch alerts

Raised by EvaluateAttention (§8.7) and delivered through the exception SMS list via spOptimoRouteDispatchAlertRaise, which throttles so the same alert type for the same date does not go out repeatedly. Alert types: NoDispatchDate, LiveDateMismatch, NotDispatched, Unconfirmed. Alerting never throws — an alerting problem must not fail a dispatch that otherwise worked — and when no dispatch date could be resolved the alert is keyed to today, since there is nothing else to key it to. OptimoRouteDispatchEvents (AdminPortal dialog) shows the dispatch history.

9.2 Booking KPI alerts — POST /api/BookingKPIAlert/Run?dryRun=

Emails a nominated list when the next available booking date for an estimator type in a state is further out than its KPI — e.g. "no VIC field estimator is free for 8 business days, KPI is 5". Called once each weekday morning by n8n. It only emails combinations currently breaching, so an email in the inbox always means something needs attention. dryRun=true reports without queueing — useful when setting thresholds for the first time.

It reads availability through the same data access the AdminPortal Booking Availability page uses, deliberately: a second definition of "available" in SQL is how an alert and the page it refers people to end up contradicting each other.

What it can and cannot know (read before changing NextAvailableDate): the canonical rule is RouteMins + AssignedPendingMins + JobMins + HomeTravelMins <= SlotMins, but this service cannot call fnEthBookingDayAvailability because that function needs a @JobID — HomeTravelMins is this estimator's drive to this job, and the alert has no job. It asks how far out the diary is, not whether a given job fits. So it applies the three job-independent terms and replaces JobMins + HomeTravelMins with the configured MinimumUsableMins floor. That is an approximation by design, and being a floor it can only ever be more cautious than the canonical rule — the safe direction for something that emails people.

Its RouteMins comes from vEthBookingEstimatorDayLoad, so since UserStory1886 it is already net of the first leg's lead-in credit (§8.2), like the booking page's CommittedMins.

The default floor is 60 for Field (45-minute Field Quotation + 15 travel) and 30 for Desktop, which has no travel. Field was 45 (30 + 15) until UserStory1774. Migration 0031 moved only Field rules still on the old 45 default, and the column default. A rule someone deliberately set to another figure was left alone. BookingEstimatorType.DefaultMinimumUsableMins must stay in step with the column default.

Do not reintroduce a bar of zero. It originally tested SlotMins - RouteMins > 0; on 19 Aug 2026 that made the next day "available" for VIC field off 34 leftover minutes against a 30-minute minimum appointment plus travel, while the Advanced Field Booking page showed that day and the next as Busy. The alert reported "within KPI" on a gap of three business days against a KPI of one.

"Today" is Melbourne (BookingTimeZoneId), because booking dates are quoted in Melbourne throughout the booking stack and the server may be on UTC — where the calendar date is behind Melbourne's for the first ten hours of the day, long enough to change the business-day count.

9.3 Orders not scheduled

vOptimoRouteOrdersNotScheduled / spOptimoRouteGetNotScheduledOrders — orders that exist in OptimoRoute but that planning has not placed on anyone's route. Visible in AdminPortal via the OptimoRouteOrdersNotScheduled dialog, with an email queue proc (spOptimoRouteGetNotScheduledOrdersEmailQueue) driven from n8n. This is the reason the route refresh uses run-ID tagging rather than blanking the day (§8.6) — a blanked day reads as "nothing is scheduled" to every NOLOCK reader and fires spurious alerts here.

Emails to the bookings team (AB#1922). Both use spOptimoRouteGetNotScheduledForDate: live orders with no ScheduledAt, today included. Unassigned appointments are listed, and cancelled or completed ones awaiting the queue are skipped. Both are built in WebAPI from OrdersNotScheduledEmail.html (PDP-style chrome), and are sent to bookings@ only when there are orders.

• Daily report (POST api/OptimoRoute/EmailNotScheduledOrders): every order from today (Melbourne) onward. It is called at 3:07pm by the n8n flow "OptimoRoute Email Orders Not Scheduled to Bookings". This replaces the "OptimoRoute Email Orders Not Scheduled" and "…24 Hours" flows, which sent plain tables to teamclaims@.
• After a full re-plan (§8.5): that date only. It also shows who booked after the cutoff and when, in Melbourne time.

"OptimoRoute Email Orders Not Scheduled To Users" (per assignee, from spOptimoRouteGetNotScheduledOrdersEmailQueue) is unchanged.

9.4 The audit trail

optimoRouteLog, written by _optimoRouteDB.Log(module, action, jobId, appointmentId, bookingId, estimatorId, details, planDate), is the trail for the whole feature — booking events, Prime calls, order create/update/delete, planning, self-heals and dispatch — not just OptimoRoute. Browsable in AdminPortal at /optimoroutelogs, filterable by module and action (spOptimoRouteGetDistinctModules / spOptimoRouteGetDistinctActions, paged via spOptimoRouteGetLogsPaged, which pages the IDs before joining). Self-heals and divergences are written here specifically so they are queryable after the fact rather than buried in log files.

Rebuilt in AB#1922 (migration 0044). The old table was a heap with no key, ~740k rows on dev and ~4k more a day. It is archived as OptimoRouteLog_Archive and the new one started empty. The new table is clustered on an ascending bigint ID. UserID is now EstimatorID, because every caller passed the estimator. PlanDate is a date. A route refresh writes one summary row per date, not a row per driver and per stop.

The change log. A row with a Category is a change-log entry: what changed on that day, and who changed it (ChangedBy, State). Booking Availability shows it per day through the history icon in each day's header (BookingChangeLog dialog, spOptimoRouteGetChangeLog, filtered index IX_OptimoRouteLog_Changes).

Category	Written by	Who
Booking	spEthBookingSetDate: booked, time changed, rebooked onto / moved off a day	@BookedBy (staff UPN), else "Insured (booking link)" for FormsPortal, else the source
Appointment	spOptimoRouteUpsertOrder, when a Prime appointment version arrives: created, changed, moved onto / off a day, cancelled, completed. Contact-only changes and work orders are not logged	Prime updatedBy; our integration accounts show as "System (…)"
Leave	EstimatorSetUnavailable	The EstimatingManager's or Admin's UPN
Planning	OptimoRoutePlanningService: planning started (with the plan mode), finished (routes and stops), failed, routes cleared	System
Dispatch	Not written. Read from OptimoRouteDispatch	System, or "Staff (manual send)"

The change log is new from the migration onward. Nothing before it is back-filled.

---

10. Other staff-facing views

Component	What it answers
OptimoRouteScheduledOrders	What is on a given day/estimator, including completed work (below)
OptimoRouteOrdersNotScheduled	Orders OptimoRoute has not placed (§9.3)
OptimoRouteCalendarItem	Drill-down on one calendar entry
OptimoRouteDispatchEvents	Dispatch send history for yesterday, today and the next route date (below)
EstimatorLocationEdit	Relocations for one estimator, push status, Retry / Done in OptimoRoute (§5.6)
EstimatorScheduleEdit / EstimatorDayHoursBulk	One estimator's normal week and extra-hours dates / extra hours for many estimators at once (§5.7)
DesktopEstimatorScheduledAppointments	A desktop estimator's booked VA appointments
BookingSameSite	This site already has bookings — a warning (§8.3)
OptimoRouteNearbyJobs	Nearest job and who is free that day — an opportunity (§8.3)
BookingCustomerLink	Re-surface or re-send the customer's booking link
BookingEstimatorInsert / BookingEstimatorEdit	Estimator records, state and skills
AppointmentUpsert	Edit a recurring appointment definition

UserStory1747 (#297) fixes:

• Scheduled orders were blank once work was completed. The Booking Availability route bar is built from optimoRouteDriverDay.DurationMins, which has no status filter. The dialog behind it called spOptimoRouteGetScheduledOrders with IncludeCompleted = 0 except for past dates. So the moment today's appointments were marked Completed in Prime, the cell showed booked hours and the dialog was empty. It now always passes IncludeCompleted: true, for any date. Cancelled work is still excluded by the proc.
• Route Dispatches starts at yesterday. spOptimoRouteDispatchGetCurrent takes a new @PastDays parameter (default 0 = the old window starting at today; negative is clamped to 0). AdminPortal passes OptimoRouteDispatchEvents.DispatchPastDays = 1, because "did the routes go out?" is usually answered by comparing with yesterday's cutoff. DispatchEventsButton passes the same constant, so its badge counts exactly what the grid lists. The window is the caller's to state rather than a literal baked into the shared proc.

UserStory1783 — route map. OptimoRouteScheduledOrders opens on a run sheet beside the route map (stacked, map on top, below 960px). The header shows jobs, time on site, driving and the day's span (departure = first stop less its travel, finish = last stop's end). The run sheet is a timeline: start, the drive to each stop, one card per order (time, suburb with the full address under it, Prime chip, type, and Apple Maps / Waze / Google Maps directions links — to the address from primeJobs.AddressLine1/Suburb/State/Postcode, else the job's coordinates), then finish. Clicking a card opens the order's detail dialog. "Table view" swaps in the filterable grid. Orders are sorted by Time so the numbering matches the map. The map shows the day's stops as numbered markers in ScheduledAt order, joined by straight lines (not road routes). Coordinates come from the job's primeJobsLocation, returned by spOptimoRouteGetScheduledOrders as Latitude/Longitude (latest row per job, NULL when not geocoded). Orders without a location are left off the map and counted in a caption above it. The line starts from an "S" marker at the estimator's base that day — fnEthBookingEstimatorBase, so a relocation covering the day, else their geocoded home — returned on every row as StartLatitude/StartLongitude. No base point, no start marker. The dialog opens from the route bar on Booking Availability and from the "Xh scheduled" note in each estimator cell on Booking Advanced.

11. Reconciliation and orphans

FindOrphanedOrders(fromDate, toDate) — GET /api/OptimoRoute/OrphanedOrders.

It starts from OptimoRoute, not from our rows, because an order whose delete silently failed has already had its local optimoRouteID cleared — no query over optimoRouteOrder can find it. Only OptimoRoute still knows it exists.

It uses get_routes, so it sees orders that have been planned onto a driver. That's the set that matters: a planned orphan consumes the estimator's day and makes them look busy for work that isn't happening. An unplanned order costs nothing and isn't reported.

For each planned order it parses optimoRouteOrder.ID out of the orderNo — from the last hyphen, because job numbers themselves contain hyphens — looks up our row, and reports a reason (missing row first, then stale status). It is read-only and deletes nothing: bulk-deleting orders off the first run of a new reconciliation is not something to do unattended. One OptimoRoute call per day in the range, sequential, capped at 62 days.

---

12. FEASIBILITY ASSESSMENT — using job-to-job distance in availability

Update 2026-10-14 (UserStory1852): the rule HAS changed. The booking procs now test fit with MarginalTravelMins in place of the base leg (§8.2, §8.3). That is §12.6 step 3 without its guards: no floor, no config switch, and keyed per estimator. The deciding argument was that the base leg isn't a conservative estimate of anything. RouteMins already starts at the base, so the old rule counted the drive from home twice on every non-empty day. The §12.3 risk remains: the estimator is provisional, so a re-plan can move the job onto a day whose real added travel is higher. That is accepted. OptimoRoute re-plans the whole day at planning time, and the slot window is narrower than the driver hours. Still not done: §12.6 step 1, comparing against what the planner actually did. BookingKPIAlertService still uses its floor (§9.2), which is cautious, so it can now under-report days the page offers. That is the safe direction. The rest of this section is kept as written.

This section is a feasibility assessment, not a description of current behaviour. Nothing described here is implemented. Sections 1–11 describe the system as it actually is today; this section describes something that has been considered and not built. Written 2026-09-21.

Update 2026-09-30 (UserStory1727). Feasibility is still untouched, so the verdict (§12.5) stands. But part of §12.6 has now happened. The detour figure this section asks for now exists as MarginalTravelMins (§8.3), computed by insertion into each estimator-day. The Geo and Details procs emit it as an output column without touching IsFeasible, which is what §12.6 step 2 ("shadow it") proposed. Two further changes affect the analysis:

• The job-to-job data now lives in ethBookingJobPointDistance, keyed by location point, and its queue is bounded to 31 days. It also pairs every stop on the same estimator-day. That reduces, but does not remove, the coverage risk in §12.4.2.
• The home → job figure is now per base per day (relocations), from ethBookingPointDistance.

Still not done: comparing MarginalTravelMins against what the planner actually did (§12.6 step 1–2), and any change to the rule (§12.6 step 3). The old table names below are kept as written.

12.1 The question

(As of 2026-09-21; superseded by UserStory1852, see the update above.) Today job-to-job travel influences ranking only (§8.3): efficient days are shown first and badged "WE'RE IN YOUR AREA", but they cost exactly the same capacity as a day with nothing nearby. Could the same data instead reduce the estimated travel time, so a day with a neighbouring job is genuinely cheaper and therefore more often bookable?

12.2 Why the idea is coherent

The current feasibility rule is:

RouteMins + AssignedPendingMins + JobMins + TravelMins <= SlotMins

RouteMins is optimoRouteDriverDay.DurationMins, which spOptimoRouteDriverDayUpsertDurationMins computes as SUM(ResponseDurationMins + ResponseTravelMins) over the estimator's planned orders for the day. So the planned part of the day already carries real, OptimoRoute-computed inter-stop travel.

The only crude term is TravelMins for the new job — the estimator's own drive to it, from ethBookingEstimatorJobDistance. It is a proxy for marginal cost that ignores where the estimator already is that day. For someone already working in the area the true marginal cost is the detour, usually far less. So the proposal replaces a deliberately pessimistic term with a better estimate. The arithmetic is sound.

12.3 THE BLOCKER: the estimator is not fixed

A discount cannot be keyed to "the estimator going", because at booking time there is no such person. optimoRouteOrder.ScheduledEstimatorID is the output of the last planning run, not a commitment. Every new order re-plans the date, and OptimoRoute will reassign a day's work whenever the resulting plan is better. This is exactly why bookings create Prime appointments against Estimator TBA VIC/QLD, with the real estimator only written back after planning (§7.4).

That undermines both obvious ways of keying a discount:

Keying	Problem
Per estimator (as NearbyByEstimator does for ranking)	Unstable. The discount is computed against a candidate who may not be the one who goes. The very next order created can reshuffle the day and invalidate it — and nothing recomputes a booking already accepted on the strength of it.
Per day / team (any neighbouring job that day, whoever holds it)	Stable, but it is precisely the borrowed discount the Geo proc warns about: "A discount borrowed from another estimator is what put 14 Aug in front of a customer in the first place." If the estimator near the neighbour is full and the one with capacity is far away, the day gets a discount nobody can realise.

This is tolerable for ranking — a wrongly ordered list is a cosmetic fault, which is why the existing per-estimator keying is good enough there. It is not tolerable for feasibility, where the same error overbooks a day that has already been promised to a customer.

Any workable design must survive the assignment changing after the booking is taken. That points away from "discount this estimator's travel" and towards "this day has a cluster on it, so the team's real travel load will be lower than modelled" — a day-level adjustment, bounded tightly enough that it can only ever act as a tie-breaker.

12.4 The other four risks

1. Performance — this was deliberately removed from the function once already. v5 moved nearby out of fnEthBookingDayAvailability because two nested CTEs inside a twice-expanded pipeline produced roughly eight scans of ethBookingJobJobDistance, a table that grows with the square of the job count. Reintroducing it means re-entering that trap, in an inline TVF with no DECLARE to hold anything. Achievable as a single-pass CTE referenced once — but this is the one object where the mistake has already cost 45-second availability loads and a user story to fix.

2. Coverage is partial and asynchronous by design. spEthBookingJobJobDistanceQueue calculates only (pending-booking job × future booked job, same state), inside a crow-flies prefilter of 30 km metro / 70 km regional (widened during CAT events), capped at 100 pairs per run to protect Google API spend, skipping any pair already stored. Under a discount model that means:

   • availability changes as the queue drains — a customer refreshing the page could watch a day become bookable;
   • genuinely nearby pairs outside the crow-flies radius silently get no discount;
   • stored durations are never recalculated, so a stale figure persists indefinitely.

   None of this matters while the data only sorts a list. All of it matters once it decides what is bookable.

3. It inverts the safety direction. The present model overstates travel, so it errs towards hiding days that would have fitted. A discount makes it optimistic: it can offer a day that does not fit, after the customer has been told they have it. Compare BookingKPIAlertService, built explicitly as a floor so that "it can only ever be more cautious than the canonical rule, never less — which is the safe direction for something that emails people" (§9.2).

4. Internal inconsistency, and a consumer that cannot follow. AssignedPendingMins charges neighbouring pending bookings ServiceDuration + min(job distance) with no nearby logic at all; discounting the new job but not those makes the day's arithmetic incoherent. And BookingKPIAlertService cannot apply a job-to-job discount — it has no @JobID, which is the entire reason it substitutes the MinimumUsableMins floor — so the alert would drift further from the page it refers people to, the exact failure §9.2 exists to prevent.

12.5 Verdict

Technically feasible — roughly a day or two of SQL plus tuning — but it should not go straight into IsFeasible. The data, the join and the right grain already exist; writing it is not the hard part. The hard part is that it converts an approximate, deliberately conservative model into an optimistic one, keyed to an assignment that is provisional, in the one function whose performance has already had to be rescued once.

12.6 Suggested order of work, if pursued

1. Establish whether the constraint even binds. ResponseTravelMins is stored per order, so the actual marginal travel OptimoRoute incurred can be compared after the fact against the home→job figure the model charged. If days rarely fail feasibility by less than the plausible discount, the change buys nothing — and that is learned for free, before any SQL is written.
2. Shadow it. Emit the discounted figure as an extra output column on spEthBookingGetAvailabilityGeo, without touching IsFeasible. Compare for a few weeks against what the planner actually did. That produces an evidence-based detour allowance rather than a guessed one — and measures how often re-planning would have invalidated the assumption (§12.3).
3. Only then move it into the rule, bounded so it can only ever be a tie-breaker:
   • a floor, never zero — e.g. EffectiveTravel = MAX(MIN(HomeTravel, NeighbourTravel + DetourAllowance), FloorMins);
   • day-level, not estimator-level, per §12.3 — and granted only where the day still passes IsDayFeasible on undiscounted figures, so a discount can never be the sole reason a day is offered;
   • config-gated (NearbyTravelDiscountEnabled, NearbyTravelDiscountFloorMins, NearbyDetourAllowanceMins) so it can be switched off without a deploy, consistent with how everything else in this stack is controlled;
   • a single-pass CTE referenced once, per §12.4.1;
   • C# fallbacks kept in step with the SQL defaults, per §8.1.

12.7 Cheaper alternatives to rule out first

• Widen NearbyTravelMaxMins so more days qualify for the existing ranking boost. No change to the capacity model at all.
• Let clustering act through TeamRemainingMins rather than per-estimator feasibility — closer to how the planner actually behaves, and it sidesteps §12.3 entirely.
• Improve TravelMins itself rather than discounting it. It is currently the estimator's drive to the job, with a fallback chain ending at a flat 30 minutes; if that fallback fires often, the model is imprecise for a much simpler reason.

---

13. Things to know before changing any of this

1. Cutoff changes are cross-cutting. Moving RoutePlanningCutoffTime moves booking availability, the planning window and the dispatch date together. That is intended. If the rule changes, both fnOptimoRoutePlanningCutoffUtc and fnOptimoRouteDispatchDate change, and so does the C# fallback constant.

2. Never clear optimoRouteID on an unconfirmed delete. That is how invisible orphans are made. Clear scheduling columns whenever you clear the ID.

3. orderNo is {JobNumber}-{optimoRouteOrder.ID} everywhere. Parse from the last hyphen.

4. Timezones: field booking availability and order building use the job's timezone; planning and dispatch are Melbourne-anchored; state selects which holidays apply, never which clock. Booking hours and driver hours are local wall-clock times, so a relocated estimator's 8–2 is 8–2 in the relocation state, and a NSW job booked as QLD gets QLD time.

5. Don't call multi-statement scalar UDFs from row-level expressions. It serialises the plan and hides its cost. fnEthBookingConfigValue is designed to be cross-joined once.

6. Keep EvaluateDispatch genuinely read-only apart from AdoptExternalSend, and keep OptimoRouteDispatchRules free of DateTime.UtcNow — that purity is the only reason the rule is testable.

7. Portals must not call Prime or OptimoRoute inline, with the deliberate exception of CreateBookingOrder/DeleteBookingOrder, which must be immediate so the day's capacity is correct for the next customer.

8. Database changes go through DbMigrations (DbUp): numbered scripts for schema, CREATE OR ALTER files under Scripts/Programmable/ for procs, functions and views.

9. Book by the mapped state. Anything that decides eligibility or hours for a job goes through fnEthBookingStateMap() (NSW → QLD), and anything that decides who is eligible on a date honours relocations. A new reader of ethBookingEstimatorSkill.State that skips either will disagree with the availability engine.

10. Don't reintroduce per-row date functions in fnEthBookingDayAvailability (§8.2). The relocation and schedule logic is joined in on plain columns for performance; the per-day version cost ~730 ms per job.

11. Keep job-to-job pairs out of ethBookingPointDistance. The availability engine reads that table; mixing job pairs in more than doubled its cost (§8.3).

12. Never push driver parameters inside the plan window. update_drivers_parameters unschedules that driver's routes. Inside the window it is ManualRequired only (§8.9).

13. Service duration is live by type. Changing ethBookingService.ServiceDurationMins re-costs every pending booking at once. Also update the KPI floor (MinimumUsableMins) and its C# default to match (§9.2).

14. Run the availability simulation after any change to §8.2–§8.3 (UserStory1852). SQLFunction.Tests/Scenarios/BookingAvailabilitySimulation.sql builds a made-up state ZZ covering:

    • estimators, an out-of-scope estimator and a non-estimator;
    • assigned, unassigned, routed and cancelled bookings, including appointments cancelled in Prime;
    • manual appointments, relocations in and out, and weekly, dated and holiday hours;
    • the first-leg lead-in (UserStory1886): credited, capped, handed back, and a later driver start.

    It checks the function and the Details proc against hand-worked answers (247 checks), then rolls everything back. It refuses to run on prod. Run it in SSMS against dev or test, or with dotnet test SQLFunction.Tests --filter BookingAvailabilitySimulation. The PR build skips it until migration 0038 and the UserStory1886 function (its FirstLegCreditMins column) have reached the Test database. To cover a new rule, add a scenario block and its expectation rows; the file explains how. It doesn't call Prime, OptimoRoute or the Geo proc; see its header.

---

14. File map

Area	Path
Order sync	CommonOptimoRoute/Services/OptimoRouteOrderService.cs
Planning & routes	CommonOptimoRoute/Services/OptimoRoutePlanningService.cs
Dispatch I/O	CommonOptimoRoute/Services/OptimoRouteDispatchService.cs
Dispatch rule (pure)	CommonOptimoRoute/Services/OptimoRouteDispatchRules.cs
Driver emails	CommonOptimoRoute/Services/OptimoRouteDriverEmailBuilder.cs
Plan-window helper	CommonOptimoRoute/Services/OptimoRouteDateHelpers.cs
KPI alerting	WebAPI/Services/BookingKPIAlertService.cs, WebAPI/Controllers/BookingKPIAlertController.cs
Booking events	WebAPI/Services/BookingService.cs, WebAPI/Controllers/BookingController.cs
VA booking events	WebAPI/Services/VABookingService.cs, WebAPI/Controllers/VABookingController.cs
Recurring appointments	WebAPI/Controllers/AppointmentController.cs
OptimoRoute endpoints	WebAPI/Controllers/OptimoRouteController.cs
Data access	CommonDataAccess/Data/{Booking,Bookings,Appointment,OptimoRoute}/
Customer booking (field)	FormsPortal/Components/Pages/Estimator.razor + Components/Forms/EstimatorBooking/
Customer booking (VA)	FormsPortal/Components/Pages/VirtualAssessment.razor + Components/Forms/VABooking/
Staff pages	AdminPortal/Components/Pages/Booking*.razor, AppointmentDefinitions.razor, OptimoRouteLogs.razor
Staff dialogs	AdminPortal/Components/Dialogs/, AdminPortal/Components/Summaries/
Driver location / hours push	CommonOptimoRoute/Services/OptimoRouteDriverLocationService.cs, CommonOptimoRoute/Models/OptimoRouteUpdateDriversParametersModel.cs
Distance queues	WebAPI/Services/GeoLocationService.cs
Relocations & schedules (staff)	AdminPortal/Components/Pages/BookingEstimatorLocations.razor, Dialogs/EstimatorLocationEdit.razor, Dialogs/EstimatorScheduleEdit.razor, Dialogs/EstimatorDayHoursBulk.razor
Availability SQL	dbo.fnEthBookingDayAvailability, dbo.spEthBookingGetAvailabilityGeo, dbo.spEthBookingGetAvailabilityDetails, dbo.spEthBookingJobMarginalTravel
Estimator base / eligibility / hours SQL	dbo.fnEthBookingEstimatorBase, dbo.fnEthBookingEstimatorSkillOnDate, dbo.fnEthBookingEstimatorDayHours, dbo.fnEthBookingStateMap
Distance SQL	dbo.ethBookingLocationPoint, dbo.ethBookingPointDistance (estimator ↔ job), dbo.ethBookingJobPointDistance (job ↔ job), spEthBookingPointDistanceQueue, spEthBookingJobPointDistanceQueue, spEthBookingPointDistancePurgeOrphans
Driver push SQL	dbo.ethOptimoRouteDriverLocationPush, spOptimoRouteDriverLocationPushQueue / ...PushRecord / ...ManualConfirm / ...PushOutstanding
Cutoff SQL	dbo.fnOptimoRoutePlanningCutoffUtc, dbo.fnOptimoRouteDispatchDate
Appointment → order queue	dbo.spOptimoRouteGetAppointmentQueue
Migrations	DbMigrations/Scripts/Migrations/0021–0031
Specs	RouteDispatch-Spec.md, EstimatorRelocation-LocationPoints-Spec.md, EstimatorSchedule-Spec.md, BookingKPIAlerts-Spec.md, VirtualAssessmentBooking-Spec.md, AppointmentInstanceReconcile-Spec.md

Still to retire once the new WebAPI is deployed everywhere (via zz_DropDeprecated): ethBookingEstimatorJobDistance, spEthBookingEstimatorJobDistanceQueue, spEthBookingGetMissingEstimatorLocations, spEthUpsertBookingEstimatorJobDistance, and the old job-job objects ethBookingJobJobDistance, spEthBookingJobJobDistanceQueue, spEthUpsertBookingJobJobDistance. Until then the old upserts also write to the new point tables.