UserStory1727 - Location point distances (temporary estimator relocation)

Status: built and merged (PR #305, 2026-09-30). Migrations 0021–0029 are applied on dev only; Test/Prod pending. The "Proposal" wording below is kept as the design record. Moved here from docs/UserStory1727/ on 2026-09-30. How it fits the wider booking system: OptimoRoute-Appointments-Bookings-Logic.md §5.6, §8.2, §8.3, §8.9.

The requirement

During a CAT event an estimator may be temporarily based somewhere else. For example, an estimator who normally lives in VIC works out of a QLD hotel for two weeks and then goes home. While they are away, availability and closest-estimator calculations must use travel from the temporary base. When they return, travel from home must work again straight away, without recalculating everything through the Google API.

A separate work item will use job-to-job travel to find the shortest travel option around existing bookings. This design is meant to serve that work too, so both kinds of distance share one cache.

How it works today

Where locations come from

Location	Source	Geocoded into	Change detection
Estimator home	Prime contact address (`primeContacts`)	`primeContactLocation` (one row per contact, overwritten)	`spPrimeReprocessContactLocation` hashes the address text; a changed hash sets `reprocess = 1`
Job	Prime job address (`primeJobs`)	`primeJobsLocation` (one row per job, overwritten)	`spPrimeUpsertJob` calls `spPrimeReprocessJobsLocation` with the same hash rule

The flagged rows are picked up by spPrimeGetContactLocationQueue and spPrimeGetJobsLocationQueue, re-geocoded, and written back by spPrimeUpsertContactLocation and spPrimeUpsertJobsLocation, which reset reprocess = 0.

The hash decides when to re-geocode. It plays no part in deciding when to recalculate distances.

Distance caches

Table	Key	Rows	Queue	"Needs calculating" test
`ethBookingEstimatorJobDistance`	(estimator, job)	~18.7k	`spEthBookingEstimatorJobDistanceQueue`: jobs updated in the last 2 months, in a state where an enabled estimator skill exists. Then `spEthBookingGetMissingEstimatorLocations` returns the estimators for each job.	`NOT EXISTS` (job, estimator)
`ethBookingJobJobDistance`	normalised (job A, job B), A < B	~19k	`spEthBookingJobJobDistanceQueue`: pending bookings × all future bookings in the same state, within a straight-line radius (30 km metro, 70 km regional or during a CAT event), at most 100 pairs per run	`NOT EXISTS` (A, B)

WebAPI/Services/GeoLocationService.cs works through both queues (ProcessEstimatorJobDistanceQueue, ProcessJobJobDistanceQueue) and upserts each result.

Why this can't support a relocation

Keyed by the person, not the place. An estimator has exactly one set of distances. Pointing them at a QLD address either does nothing, because the (estimator, job) rows already exist, or means deleting their VIC rows. Those rows then cost Google calls again when the estimator goes home.
Distances are never invalidated (an existing bug). When a job or estimator address changes, the location is re-geocoded correctly, but the distance rows built from the old coordinates stay. The queues only test whether a row exists, so they never recalculate. Booking and "nearby" decisions keep using the old address's travel times.
No notion of date. Readers ask "what is this estimator's travel to this job", never "...on this date". A relocation is only true for part of the booking horizon.

Proposal

Key every distance by a location point, a distinct geocoded coordinate, rather than by an estimator or a job. Estimator homes, temporary bases and jobs all resolve to points. A date-aware function answers "where is estimator X based on date D".

1. `ethBookingLocationPoint`: one row per distinct coordinate

sql

CREATE TABLE dbo.ethBookingLocationPoint
(
    PointID    int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ethBookingLocationPoint PRIMARY KEY,
    Lat        decimal(9,5)      NOT NULL,   -- ROUND(Location.Lat, 5)  (~1.1 m)
    Lng        decimal(9,5)      NOT NULL,   -- ROUND(Location.Long, 5)
    Location   geography         NOT NULL,
    CreatedAt  datetime2         NOT NULL CONSTRAINT DF_ethBookingLocationPoint_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_ethBookingLocationPoint_LatLng UNIQUE (Lat, Lng)
);

Keyed on coordinates, not address text. Google calculates travel from the lat/lng, so the geocoded point is what identifies a location. Two spellings of one address share a point. An address that re-geocodes somewhere else correctly gets a new one.
No hash column. A unique key on the two rounded columns does what a hash of them would, and stays readable. The address-text hash on primeContactLocation and primeJobsLocation still decides when to re-geocode.
One way in: spEthBookingLocationPointGetOrCreate @Location returns the PointID. It is the only place that rounds, so no caller can round differently.

2. Point stamps on the existing location tables

primeContactLocation.PointID int NULL is set by spPrimeUpsertContactLocation.
primeJobsLocation.PointID int NULL is set by spPrimeUpsertJobsLocation.
Both upserts call spEthBookingLocationPointGetOrCreate with the new @Location.
Add an index on primeJobsLocation (PointID) INCLUDE (jobID), because several jobs can share a point (multi-unit sites).

3. `ethBookingPointDistance`: the single distance cache

sql

CREATE TABLE dbo.ethBookingPointDistance
(
    PointID_A        int       NOT NULL,     -- always the smaller PointID
    PointID_B        int       NOT NULL,
    DistanceMeters   int       NOT NULL,
    DurationSeconds  int       NOT NULL,
    CreatedAt        datetime2 NOT NULL CONSTRAINT DF_ethBookingPointDistance_CreatedAt DEFAULT SYSUTCDATETIME(),
    UpdatedAt        datetime2 NULL,
    CONSTRAINT PK_ethBookingPointDistance PRIMARY KEY (PointID_A, PointID_B),
    CONSTRAINT CK_ethBookingPointDistance_Order CHECK (PointID_A < PointID_B),
    CONSTRAINT FK_ethBookingPointDistance_A FOREIGN KEY (PointID_A) REFERENCES dbo.ethBookingLocationPoint (PointID),
    CONSTRAINT FK_ethBookingPointDistance_B FOREIGN KEY (PointID_B) REFERENCES dbo.ethBookingLocationPoint (PointID)
);
CREATE INDEX IX_ethBookingPointDistance_B
    ON dbo.ethBookingPointDistance (PointID_B) INCLUDE (PointID_A, DistanceMeters, DurationSeconds);

One row per pair, both directions. ethBookingJobJobDistance already treats travel as symmetric, and the estimator cache only ever calculated home→job and assumed the return was the same. This changes neither.
The CHECK is the normalisation rule. The upsert proc swaps A and B, following the existing spEthUpsertBookingJobJobDistance pattern.
Two jobs at the same point (A = B) have zero travel and are never stored or sent to Google. Readers treat a same-point pair as 0 minutes.
One read path: vEthBookingPointDistance returns (FromPointID, ToPointID, DistanceMeters, DurationSeconds) as a UNION ALL of both directions. Readers filter FromPointID = @x and get an index seek on each half. That is the shape spEthBookingGetAvailabilityGeo's Neighbours CTE already uses.

4. `ethBookingEstimatorRelocation`: the temporary base

sql

CREATE TABLE dbo.ethBookingEstimatorRelocation
(
    RelocationID          int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ethBookingEstimatorRelocation PRIMARY KEY,
    primeEstimatorUserID  uniqueidentifier  NOT NULL,
    PointID               int               NOT NULL CONSTRAINT FK_ethBookingEstimatorRelocation_Point REFERENCES dbo.ethBookingLocationPoint (PointID),
    State                 nvarchar(50)      NOT NULL,
    AddressText           nvarchar(500)     NOT NULL,   -- what was geocoded, for display
    StartDate             date              NOT NULL,
    EndDate               date              NOT NULL,   -- inclusive; a relocation is temporary by definition
    EventName             nvarchar(200)     NULL,
    IsCancelled           bit               NOT NULL CONSTRAINT DF_ethBookingEstimatorRelocation_IsCancelled DEFAULT 0,
    CreatedBy             nvarchar(200)     NOT NULL,
    CreatedAt             datetime2         NOT NULL CONSTRAINT DF_ethBookingEstimatorRelocation_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_ethBookingEstimatorRelocation_Dates CHECK (EndDate >= StartDate)
);
CREATE INDEX IX_ethBookingEstimatorRelocation_Estimator
    ON dbo.ethBookingEstimatorRelocation (primeEstimatorUserID, StartDate, EndDate)
    INCLUDE (PointID, State) WHERE IsCancelled = 0;

One active relocation per estimator per day. spEthBookingEstimatorRelocationUpsert rejects overlapping, non-cancelled date ranges for the same estimator, because a CHECK constraint can't express that.
Day granularity. The base changes at midnight, so a relocation can't start partway through a day.

5. `fnEthBookingEstimatorBase(@Date)`: where an estimator is based on a date

An inline table-valued function returning (primeEstimatorUserID, PointID, State, IsRelocated, RelocationID):

Relocated estimators: a non-cancelled relocation covering @Date wins.
Everyone else: the home point from primeContactLocation.PointID and primeContacts.addressState.
Enabled estimators only, which means an enabled row in ethBookingEstimatorSkill.

It is inline, so CROSS APPLY dbo.fnEthBookingEstimatorBase(day.Date) expands into the calling query without a per-row function call.

6. `fnEthBookingEstimatorSkillOnDate(@Date)`: eligibility follows the relocation

Decision: a relocation makes the estimator eligible in the relocation's state, for exactly its StartDate–EndDate. No skill rows are added or edited by hand.

An inline table-valued function returning (primeEstimatorID, SkillCode, State, IsRelocated, RelocationID), the effective skill set on @Date:

No relocation covering @Date: the estimator's enabled ethBookingEstimatorSkill rows, unchanged.
Relocation covering @Date:
- the estimator's enabled skill codes (distinct), each with State = the relocation's State;
- the home-state rows are suppressed for those dates. An estimator based in Brisbane for the fortnight shouldn't be offered Melbourne jobs, and that also stops the availability engine counting them in two states' capacity.
No skills are gained. A relocation moves the skills an estimator already has; it never grants new ones. An estimator with only disabled skills stays ineligible.

ethBookingEstimatorSkill stays the permanent record. The relocation is a date-bounded overlay on it, so when the relocation ends, or is cancelled, eligibility reverts with no clean-up.

Per date, not per booking request. Readers that work over a date range apply the function per day, e.g. CROSS APPLY dbo.fnEthBookingEstimatorSkillOnDate(ds.[Date]) against ethBookingDaySlot. A relocation from 6–19 October then makes the estimator available on QLD's days 6–19 October and on VIC's days either side, from one query.

Bookings already made in the home state. Creating a relocation doesn't move or cancel existing bookings that fall inside it. The AdminPortal dialog warns before saving (see below).

Change detection: the point replaces the hash at the distance level

Event	Result
Prime re-syncs a job or contact with no address change	Hash unchanged, so no re-geocode and nothing queued
Address changes	Hash changes → re-geocoded → new coordinates → new PointID → every pair involving it is missing → queue recalculates
Address text changes but geocodes to the same spot	New geocode, same PointID, so no Google distance calls
Estimator relocates	Relocation point is new or reused (for example a hotel several estimators share); missing pairs are queued
Estimator returns home	Home point's rows were never touched, so no calls

The "does a row exist" test stays. It simply tests the right thing now. This also fixes the stale-distance bug in both existing caches.

Minor cost: if a re-geocode moves the coordinates slightly across a 5-decimal rounding boundary, that job or contact gets one unnecessary round of distance calls.

Queues

Both queues return point pairs with the coordinates of each point. The WebAPI loop no longer needs to know whether a point is a home, a temporary base or a job.

Estimator → job (this story)

spEthBookingEstimatorJobDistanceQueue v2 returns (base point, job point) pairs:

Jobs: the same selection as today, meaning jobs updated in the last 2 months with a primeJobsLocation.PointID.
Base points for a job's state:
- home points of enabled estimators whose skill State is the job's state; and
- relocation points where State is the job's state and EndDate >= today. A relocation that has ended never generates new work.
Missing test: NOT EXISTS the normalised pair in ethBookingPointDistance, and the two points differ.

This replaces the two-step job-then-estimators call (spEthBookingEstimatorJobDistanceQueue followed by spEthBookingGetMissingEstimatorLocations) with one proc that returns pairs. It also clears up two quirks:

spEthBookingGetMissingEstimatorLocations doesn't filter on Enabled, although spEthGetBookingEstimatorLocations does.
The current queue's DISTINCT includes EstimatorFirstName, so it returns one row per job per estimator even though each row is treated as a job.

Per-run limit: doubled. Decision: the queue takes TOP (500) jobs per call, up from today's TOP (250), and returns every missing pair for those jobs. Make it a parameter (@MaxJobs int = 500) so it can be changed without altering the proc.

First-day backlog: a new relocation needs a distance from its point to every recent job in the state. The doubled limit helps clear it. The queue is ordered by UpdatedAt DESC, as today, so the most recently active jobs get their distances first.

Job → job (the separate work item)

spEthBookingJobJobDistanceQueue keeps its current selection with two changes:

Bounded window. Future bookings are limited to FromDate BETWEEN @Today AND DATEADD(DAY, @WindowDays, @Today), where @WindowDays comes from ethBookingConfig through fnEthBookingConfigValue (proposed key JobJobDistanceWindowDays, default 31). Today there is no upper bound.
Pairs come from points. Pairs are (pending job point, future job point), with same-point pairs skipped.

The straight-line radius stays as the proximity filter. The radius already exists and is metro/regional/CAT-event aware.

A postcode-range filter (±100) was considered and rejected. Postcode numbers only loosely follow geography. The band lets in regional pairs hundreds of kilometres apart (4810 Townsville and 4870 Cairns) and leaves out metro neighbours (2000 CBD and 2150 Parramatta; 3000 and 3150 Glen Waverley).

Volume isn't a concern: ethBooking holds about 900 rows, so pending × next-month bookings in one state is a few thousand STDistance checks per run. That is cheap even without a spatial index on primeJobsLocation.

Readers to change

Estimator → job (this story)

Object	Today	Proposed
`fnEthBookingDayAvailability`: `Travel` CTE	This estimator's `MIN(Duration)` to the job, one value per estimator	Per estimator per day: `CROSS APPLY fnEthBookingEstimatorBase(Day)`, then the distance from that base point to the job point
`fnEthBookingDayAvailability`: `FallbackTravel`, `Pending`	`MIN(Duration)` across every estimator for the job	`MIN` across the bases in use that day for the job's state, not every point ever cached
`vEthBookingEstimatorDayLoad`	Same team `MIN`	Same change. Optional improvement: use the booked estimator's base on the booking date instead of the team minimum.
`spEthBookingSetDate`, `spEthBookingClear`	`TOP 1 Duration ... WHERE primeJobID = @JobID ORDER BY Duration` (team minimum)	Team minimum over the bases in use on the booking date
`spOptimoRouteGetAllowSpecificTimeBookings`	This estimator's duration to the job	The estimator's base on the booking date → the job point
`spEthUpsertBookingEstimatorJobDistance`	Upserts (estimator, job)	Replaced by `spEthUpsertBookingPointDistance @PointID_A, @PointID_B, ...`
`spEthGetBookingEstimatorLocations` / `GeoLocationService.CalcEstimatorJobDistance` (single-job on-demand path)	Estimator home coordinates	Base points in the job's state as of today

fnEthBookingDayAvailability is on the performance-sensitive booking path; see its v5 history. Check the query plan after the per-day change, because the Travel CTE grows from one row per estimator to one row per estimator per day in the horizon.

Eligibility: readers of `ethBookingEstimatorSkill` (this story)

These objects read the skill table live (from the sql-ontology). The ones that decide who can take work in a state on a date move to fnEthBookingEstimatorSkillOnDate:

Object	How it uses the skill state today	Change
`fnEthBookingDayAvailability`	Eligible estimators: `es.[State] = jt.[State]` (the job's state)	Per day: `CROSS APPLY fnEthBookingEstimatorSkillOnDate(Day)`, same state test
`vEthBookingEstimatorDayLoad`	Joins skills to day slots on `State`	`CROSS APPLY fnEthBookingEstimatorSkillOnDate(ds.[Date])`; slot and skill state must match on that date
`spOptimoRouteGetAllowSpecificTimeBookings`	`[ethBookingEstimatorSkill].[State] = @State` (the job's state)	`fnEthBookingEstimatorSkillOnDate(@PlanDate)`
`spEthBookingEstimatorJobDistanceQueue` / `spEthBookingGetMissingEstimatorLocations`	Skill state = job state	Already covered by the base points in the estimator→job queue
`spEthBookingDaySlotPopulate`	States to generate day slots for = `DISTINCT` skill states	Also include the states of non-cancelled relocations with `EndDate >= today`. Otherwise a relocation to a state no estimator normally works in (e.g. NSW) would have no slots and so no availability.
`vOptimoRouteDriverDay`	Labels each driver's plan day with the skill state	Use the state on `PlanDate`, so a relocated estimator's route shows under QLD

Unchanged, because they are not date-bound eligibility decisions:

spBookingUpsertEstimatorSkill, spEthBookingUpsertEstimatorSkill, spEthBookingDeleteEstimatorSkill, spEthBookingGetEstimatorsSkills: the permanent skill record and the Estimator Skills tab.
spEthBookingGetAppointmentTypesByEstimator, spEthBookingIsFieldEstimator, spPrimeGetEstimatorIDFromName, spPrimeGetContactLocationQueue: they use skill codes or membership, not state.
spEthVABookingGetDesktopEstimatorAvailabilityDetailsAll: desktop estimators aren't state-specific.
spOptimoRouteGetAppointmentQueue, spOptimoRouteGetWorkOrderQueue, spOptimoRouteDriverDayUpsert, spOptimoRouteUpsertStop: they read the skill table but not its state for eligibility. Check each one's body when building.

To decide when building: spEthBookingGetEstimators(@State) and spEthBookingGetEstimatorStates feed AdminPortal state filters. Showing a relocated estimator under both states (home, and relocation while it's active) is probably the most useful behaviour for staff looking for them.

Hard-coded states: the AdminPortal's BookingEstimatorEdit lists only VIC and QLD. The relocation dialog takes its state from the geocode result instead, so it isn't limited to that list.

Job → job (the separate work item)

Object	Change
`spEthBookingGetAvailabilityGeo`: `Neighbours` CTE	Job → its point → `vEthBookingPointDistance` → neighbour points → the jobs at those points (via `primeJobsLocation.PointID`). Jobs sharing the point are 0 minutes.
`spEthUpsertBookingJobJobDistance`	Replaced by `spEthUpsertBookingPointDistance`
Route / shortest-travel calculation (new)	Each estimator's day is start → booking 1 → … → booking n → end. Start and end are the day's base (relocation, else home), so a relocated estimator's day starts at the hotel. Every leg is a lookup in the two point caches. Built as `spEthBookingJobMarginalTravel`; see "Shortest travel" under Build status.

C# (`CommonDataAccess` / `WebAPI`)

IBookingData / BookingData:
- replace UpsertEstimatorDistance, GetMissingEstimatorLocations and (in the job-job item) UpsertJobJobDistance with GetPointDistanceQueue / UpsertPointDistance;
- add relocation CRUD.
GeoLocationService: both loops become "for each (point A, point B): call Google, then UpsertPointDistance". ProcessEstimatorJobDistanceQueue and ProcessJobJobDistanceQueue can stay as separate endpoints over the same upsert, keeping their different per-run caps.
Relocation CRUD in the AdminPortal: see below.

AdminPortal: Estimator Locations

Each estimator has a default location plus any number of date-based overrides. The default is where they travel from on any day no override covers.

Where it lives

A new Estimator Locations tab on the Settings page, next to Estimator Skills. It is embedded the same way (<BookingEstimatorLocations Embedded="true" />) and has its own /bookingestimatorlocations route.

It is not part of Estimator Skills. BookingEstimators.razor lists one row per skill × state, so an estimator appears once for each skill. A location belongs to the estimator, not to a skill.

Grid: one row per estimator with at least one enabled skill

Column	Source
Name	`primeUsers`
Default location	Prime contact address (suburb, state), from `primeContacts`
Geocoded	Whether `primeContactLocation` has a location/`PointID`. A warning icon if not, because that estimator currently gets no distances at all.
Based today	"Default" or "override suburb, state until EndDate", from `fnEthBookingEstimatorBase(today)`
Upcoming overrides	Count of non-cancelled overrides with `StartDate > today`

Requirement: highlight a missing Prime address. If the estimator's Prime contact has no address, the row is highlighted (warning colour, not red, and theme-safe per CLAUDE.md), with the Default location cell reading "No address in Prime" and a tooltip saying to add it to the contact in Prime.

What counts as missing: no Prime contact linked to the user, or a blank street line or suburb. The contact geocode queue (spPrimeGetContactLocationQueue) skips a contact with a blank suburb, so a missing street line or suburb means the estimator will never be geocoded and never gets distances.
A separate case from "Not geocoded": the address is present but the geocode hasn't run yet, or failed. That keeps the Geocoded warning icon.
Summary proc columns: HasPrimeAddress and IsGeocoded.
Found on dev on 2026-09-29: Theo Kastoras had a blank Prime contact address. It has now been fixed in production.

A "Show past overrides" switch, like the existing "Show disabled entries" switch, controls whether ended and cancelled overrides are listed in the dialog.

Row click: the `BookingEstimatorLocationEdit` dialog

Default location: read-only, showing the Prime address and its geocoded suburb. Decision: the Prime contact address is the only default. There is no permanent default override in the portal. A wrong default is corrected in Prime, and the next sync re-geocodes it and gives it a new point.
Overrides list: Start, End, Address, State, Event, Status (Upcoming / Active / Ended / Cancelled).
Add / edit an override:
- Address, geocoded with ILocationService.GetAddressGeoDataAsync when saved. Show the resolved address and state so a typo that geocodes somewhere else is caught before saving. The state defaults from the geocode result.
- Start and End dates, both Editable="true" so they can be typed as well as picked.
- Event name (optional).
Validation, enforced again in spEthBookingEstimatorRelocationUpsert:
- EndDate >= StartDate;
- EndDate >= today;
- no overlap with another non-cancelled override for the same estimator;
- the address geocoded successfully.
Existing-bookings warning: before saving, list the estimator's non-cancelled bookings that fall inside the override's dates but are in another state. Saving is still allowed, because staff may intend to reassign them, but those bookings need a decision.
Plan-window warning: if any date being added, removed or changed is inside the OptimoRoute plan window, the dialog warns before saving. Saving is allowed, and those dates are flagged for a manual change in OptimoRoute; see the OptimoRoute section. The warning recommends starting after the next plan date, where the push and re-plan are automatic.
Manual OptimoRoute dates: the dialog lists any ManualRequired dates for the estimator, each with a Done in OptimoRoute button.
Delete vs cancel:
- An override that hasn't started can be deleted outright.
- One that is active or ended can only be cancelled, because bookings may already have been made on it.
- Cancelling an active override can instead set EndDate = today ("End now"), which covers an estimator coming home early.
Saving creates or reuses the location point (spEthBookingLocationPointGetOrCreate). A new point is picked up by the estimator→job queue on its next run.

Data access

New methods in IBookingData / BookingData, each over its own proc:

GetEstimatorLocations() for the grid;
GetEstimatorRelocations(estimatorId, includePast);
UpsertEstimatorRelocation(...);
CancelEstimatorRelocation(relocationId, endNow);
DeleteEstimatorRelocation(relocationId).

GetEstimatorLocations would clash with the existing GetEstimatorLocations(state), which feeds the distance calculation. Name the grid method GetEstimatorLocationSummary instead.

OptimoRoute: moving the driver's start and end location

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

The API supports it, per driver per date

From the public OptimoRoute API reference (<https://optimoroute.com/api/>, read 2026-09-29). The repo has no copy of the spec.

update_driver_parameters (single) is already called by OptimoRouteOrderService.UpdateDriver for work times. It takes externalId, date, workTimeFrom / workTimeTo, and also startLatitude / startLongitude / startAddress and endLatitude / endLongitude / endAddress.
update_drivers_parameters (bulk, up to 500 updates per call) takes startLocation / endLocation as a LocationType: type = custom (with latitude, longitude, address), employeeDefault, or startLocation (only valid for endLocation). It returns a result for each update, with error codes including ERR_DRIVER_NOT_FOUND and ERR_LOC_NOT_VALID.

Use the bulk endpoint.

One call per relocation. A two-week relocation is one call, not fourteen.
Clean reversal. employeeDefault puts a date back to the driver's normal location without us needing to know what that location is. That's what shortening or cancelling a relocation needs.

The driver is identified the same way as the existing work-time calls: externalId is the estimator's full name.

Parameters are per date, so there is no "move back"

Each update applies to one driver on one date. Pushing the hotel for 6–19 October leaves 20 October on the driver's default (home) location with no call at all. The only reverse calls needed are for dates removed from a relocation that was already pushed, and those are sent with employeeDefault.

When to push: immediately on save

The API warns: "Any existing routes for the specified date/driver will be unscheduled." That, combined with how planning runs today, decides the timing:

Planning isn't a single run at the cutoff. StartPlanningFromQueue plans every date in the plan queue (spOptimoRoutePlanGetQueue) whenever orders change, including dates weeks ahead.
- Outside the window, meaning before the cutoff on the previous business day, a date is re-planned from scratch.
- Inside the window it is planned with StartWith = CURRENT and LockType = ROUTES / ROUTE_SEQ, so notified routes are kept.
Pushing only just before the cutoff is too late for the earlier runs. Any date in the relocation that is planned earlier, which is every date with a booking, would be planned from the VIC home until then, and ops would see nonsense routes in OptimoRoute. The late push would then unschedule those routes and force a re-plan in the last minutes before the cutoff.
Pushing after the cutoff is harmful. It unschedules routes that are locked and may already have been sent to the driver and customers.

So:

Immediately on save of a relocation (create, change of dates or location, cancel, "End now"). This is deliberate: pushing and re-planning as soon as the relocation is saved means orders pinned to the estimator's old location drop out of the plan straight away. The resulting under-capacity shows up days ahead, while there is still time to reassign, instead of the evening before. Then, push every affected date that is still outside the plan window:
- custom start and end at the relocation point for its dates;
- employeeDefault for dates it no longer covers.
Then enqueue each pushed date for re-planning with spOptimoRoutePlanUpsert @PlanDate, because the push unscheduled any routes that date had.
No separate pre-cutoff job. Retries ride on the existing re-plan trigger. Every order change already queues a re-plan for its date (spOptimoRoutePlanUpsert), and StartPlanningFromQueue works through that queue. Before it plans a date that is outside the window, it pushes any outstanding driver locations for that date: rows from spOptimoRouteDriverLocationPushQueue for that one date, i.e. failed pushes, or a desired location that differs from the recorded one. Then it plans.
- The push unschedules only routes that the plan is about to rebuild from EMPTY anyway.
- A failed push is retried the next time anything queues that date. Until then it also shows as a "Push failed" chip on the Estimator Locations grid, with a Retry button that pushes and re-queues the date.
- Issues come out as early as possible, even for a plan date a week away: at the save itself, or at the first re-plan of that date after it.
Inside the window: allowed, flagged for a manual change in OptimoRoute. Decision: a relocation change that touches a date inside the plan window (today, or tomorrow once the cutoff has passed) is saved, but those dates are not pushed automatically. A push would unschedule routes that are locked and may already have been sent.
- Warning before saving. The dialog lists the dates inside the window and says the driver's start/end location for them must be changed in OptimoRoute by hand, and that their routes may already have gone to the estimator and customers. It recommends starting relocations on a date after the next plan date: future dates are pushed and re-planned automatically, which is safer.
- Flagged until done. Each of those dates is recorded in ethOptimoRouteDriverLocationPush with Status = ManualRequired (see below). The Estimator Locations grid shows a "Manual OptimoRoute change" chip on the estimator until someone marks each date Done in OptimoRoute, which records who and when.
- Automatic pushes leave them alone. Neither the on-save push nor the push before planning ever sends a ManualRequired date. A date that is flagged but not confirmed when its plan day arrives is logged with _optimoRouteDB.Log(...), so it's visible after the fact.
- Booking-side eligibility still changes on those dates, because that is our data.

Re-planning the moved dates

Every date whose driver location is pushed is re-planned. The push itself unschedules that driver's routes for the date, so a re-plan is required anyway. It is also what shows the effect of the move on both states.

The re-plan uses the existing date-wide path: spOptimoRoutePlanUpsert @PlanDate, then StartPlanningFromQueue. Outside the plan window that is startWith = EMPTY with balancing = ON_FORCE by working time, across all drivers, which gives:

Where	Result
New state (QLD)	Unpinned QLD orders are rebalanced across the QLD drivers, now including the relocated estimator starting from the hotel.
Old state (VIC)	The relocated estimator's unpinned VIC orders are spread across the remaining VIC drivers. Whatever doesn't fit is left unscheduled.
Under-capacity signal	Those unscheduled VIC orders show up in the AdminPortal's existing Orders Not Scheduled dialog (`spOptimoRouteGetNotScheduledOrders`), with `NotScheduledSince` showing when they dropped out. No new alerting is needed to see the gap.
Booking side	Independently of OptimoRoute, `fnEthBookingEstimatorSkillOnDate` has already removed the estimator from VIC capacity for those dates, so no new VIC bookings are offered against them.

Pinned orders don't rebalance. OptimoRoute treats assignedTo as a hard constraint: the order is served by that driver or not at all. We pin an order to a named driver when:

the Prime appointment's estimator is a real estimator, not the VIC/QLD placeholder (BuildOrderRequestAsync(OptimoRouteAppointmentQueueModel ...)); or
the booking has a PrimeUserID, i.e. an estimator was chosen in the AdminPortal (BuildOrderRequestAsync(BookingModel ...)).

So on the moved dates:

VIC orders pinned to the relocated estimator go unscheduled. They can't be reached from the hotel, and OptimoRoute won't give them to another VIC driver. They appear in Orders Not Scheduled, but fixing them means reassigning the appointment or booking. That is exactly the list the relocation dialog's existing-bookings warning shows. It should make clear that these orders will be unscheduled, not just that they exist. This is intended, not a side effect to avoid. Because the push and re-plan happen immediately on save, these orders surface in Orders Not Scheduled as soon as the relocation is created. The warning is information for the person saving, not a blocker.
QLD orders pinned to other QLD estimators stay with them. The relocated estimator picks up only the unpinned (placeholder / unassigned) QLD work, plus new bookings made against them.

Scoped planning was considered and not proposed. start_planning can restrict a run to some drivers (useDrivers) and orders (useOrderObjects), which could re-plan only QLD. It doesn't last, though. The next routine queue run for that date plans date-wide again, and until then the VIC gap wouldn't be redistributed or shown. Keeping VIC's plan untouched would need planning to become per-state in general. That is a bigger change than this story, and it doesn't match the Melbourne-anchored planning run (fnOptimoRoutePlanningCutoffUtc).

Inside the plan window nothing is pushed automatically, so nothing is re-planned by this feature. The dates are flagged for a manual change in OptimoRoute, and the existing CURRENT + ROUTES / ROUTE_SEQ behaviour is left alone.

Working hours and time zone

Decision: a relocated estimator's working hours follow the relocation state's time zone. They keep the same wall-clock hours, in that state's local time.

Booking side: 8 AM–2 PM standard hours. These come from ethBookingDaySlot, which has rows per state and is converted by state time zone in spEthBookingDaySlotPopulate. Under fnEthBookingEstimatorSkillOnDate a relocated estimator is booked on the relocation state's slots, so 8–2 QLD time while in QLD. Nothing extra is needed beyond spEthBookingDaySlotPopulate including relocation states, which is already listed above.
OptimoRoute: driver standard hours 6 AM–4 PM, which allow for travel and variance. OptimoRoute's public API reference doesn't say how it interprets time zones, but our code already treats every time as local wall-clock time:
- orders are converted to the job's own time zone (_timeZoneHelper.GetTimeZoneId(JobState, ...) for appointments, booking.Timezone for bookings) before they're sent;
- driver hours are sent as plain 06:00 / 16:00 with no conversion;
- QLD drivers already work 6–4 QLD time on that basis.
So a relocated estimator's 6–4 is automatically 6–4 in QLD, with no conversion. During daylight saving that is 7 AM–5 PM Melbourne time, which is correct.
The location push does not send work times. The bulk update omits workTime, which means "unchanged". That keeps two things intact:
- the driver's standard 6–4;
- any extension CheckEstimatorWorkTime has already made for that date. It widens a driver's hours when a manually-set appointment falls outside them, records the change in the work-time table (UpsertWorkTime), and calls update_driver_parameters.
Extensions keep working while relocated. A QLD appointment for the relocated estimator arrives in QLD local time (the job's zone), so CheckEstimatorWorkTime compares and widens in the same local terms. Its 6:00 / 16:00 floor and ceiling are unchanged.
Stale extensions. An extension made before the relocation, for a home-state appointment on a date that is now relocated, stays in place. That appointment will be unscheduled (it's pinned; see above), but the driver keeps the wider hours for that date and OptimoRoute may use them for QLD work. That is harmless in most cases, so the proposal leaves it. If it matters, the push could reset workTime to 06:00–16:00 on dates whose only extension came from appointments now unscheduled, but that needs the extension's source appointment recorded, which the work-time table doesn't hold today.
The planning cutoff stays Melbourne-anchored (fnOptimoRoutePlanningCutoffUtc), as it already is for every QLD date.

Tracking what was pushed: `ethOptimoRouteDriverLocationPush`

primeEstimatorUserID, PlanDate          (PK)
PointID NULL                            -- NULL = employeeDefault
RelocationID NULL
Status                                  -- Pushed | Failed | ManualRequired | ManualDone
PushedAt, ResponseCode, ResponseMessage
ManualConfirmedBy, ManualConfirmedAt NULL

Desired state comes from fnEthBookingEstimatorBase(PlanDate): a relocation point, or no relocation, which means employeeDefault.
Recorded state is this table.
Queue: spOptimoRouteDriverLocationPushQueue @FromDate, @ToDate returns the (estimator, date) rows where desired ≠ recorded, for dates outside the plan window, plus Failed rows to retry. The on-save push, the push before planning, and the grid's Retry all read it, so there is one rule for what needs sending.
Manual dates: a change to a date inside the window writes ManualRequired instead of pushing. spOptimoRouteDriverLocationManualConfirm sets it to ManualDone from the AdminPortal. If the relocation changes again for that date before it is confirmed, the row is updated to the new desired location and stays ManualRequired.
Logging: each call is also logged with the existing LogApiJsonAsync / _optimoRouteDB.Log(...) calls, like the work-time updates.

Code

CommonOptimoRoute:
- OptimoRouteUpdateDriversParametersModel (the bulk request and response);
- OptimoRouteOrderService.UpdateDriversParameters(...), which posts to update_drivers_parameters with the same key, base URL and Accept header as UpdateDriver;
- PushDriverLocationsFromQueue(fromDate, toDate), which reads the queue, sends one bulk call, records each result, and enqueues the successfully pushed dates for planning;
- OptimoRoutePlanningService.StartPlanningFromQueue calls PushDriverLocationsFromQueue(planDate, planDate) before StartPlanning for each date outside the window. No new endpoint or schedule is needed.
AdminPortal: the relocation dialog pushes on save through the same service, and the grid's Retry button uses it too. Pushing must not block the save; if it fails, the save stands, the row is Failed, and the next re-plan of that date retries it.

Existing behaviour worth knowing

The work-time adjustment in CheckEstimatorWorkTime already calls update_driver_parameters for a driver and date. By the API's own warning, that also unschedules that driver's routes for the date. The location push adds nothing new here, but it is the same mechanism.

Migrating the data

Points for existing locations. Create a point for every distinct rounded coordinate in primeContactLocation and primeJobsLocation, then stamp PointID on both tables.
Estimator distances (this story). Map each ethBookingEstimatorJobDistance row to (estimator's current home point, job point). Normalise the pair and insert it, keeping MIN(Duration) where two rows collapse into one pair. This needs no Google calls. Rows affected by the stale-address bug carry their old values across, because there's no timestamp to spot them. That is no worse than today, and those rows are recalculated the next time either address changes.
Job-job distances (the separate work item). Map ethBookingJobJobDistance rows to point pairs the same way.
Retire the old tables through zz_DropDeprecated once no reader uses them.
- Optional shim: during the move, a view with the old (primeEstimatorUserID, primeJobID, Distance, Duration) shape, resolved for today, so readers can move one at a time.

Clean-up

Pairs whose points no longer belong to any job, any home, or any relocation that is active or ended recently are dead weight. A scheduled proc spEthBookingPointDistancePurgeOrphans deletes them, followed by points referenced by nothing. Keep points for ended relocations for a while (proposed ethBookingConfig key RelocationPointRetentionDays), so a team returning to the same hotel for a follow-up event reuses its distances.

Phasing

Phase	Scope
UserStory1727	Point table and stamps, `ethBookingPointDistance`, relocation table and upsert, `fnEthBookingEstimatorBase`, `fnEthBookingEstimatorSkillOnDate` and the eligibility readers, estimator→job queue and upsert, the estimator→job readers above, estimator distance backfill, C# queue loop and relocation CRUD, OptimoRoute driver-location push (on save, and before each re-plan of a date)
Job-job work item	Job-job queue onto points with the bounded window, `spEthBookingGetAvailabilityGeo` neighbours onto points, job-job backfill, retire `ethBookingJobJobDistance`, route / shortest-travel calculation

1727 stamps points on jobs even though job-job pairs don't move until the second phase. The estimator distances need job points anyway, and it means the estimator rows migrate once, not twice.

Objects (per `DbMigrations/README.md`)

Migrations (next free number at build time; currently 0021):

the three new tables;
the two PointID columns and the primeJobsLocation (PointID) index;
the point and estimator-distance backfill;
the ethBookingConfig keys.

Programmable (CREATE OR ALTER, one file per object):

New: spEthBookingLocationPointGetOrCreate, spEthUpsertBookingPointDistance, vEthBookingPointDistance, fnEthBookingEstimatorBase, fnEthBookingEstimatorSkillOnDate, spEthBookingEstimatorRelocationUpsert (plus get, list, cancel and the existing-bookings check), spEthBookingPointDistancePurgeOrphans, spOptimoRouteDriverLocationPushQueue, spOptimoRouteDriverLocationPushRecord, spOptimoRouteDriverLocationManualConfirm.
New table (migration): ethOptimoRouteDriverLocationPush.
Changed: spPrimeUpsertContactLocation, spPrimeUpsertJobsLocation, spEthBookingEstimatorJobDistanceQueue, fnEthBookingDayAvailability, vEthBookingEstimatorDayLoad, spEthBookingSetDate, spEthBookingClear, spOptimoRouteGetAllowSpecificTimeBookings, spEthGetBookingEstimatorLocations, spEthBookingDaySlotPopulate, vOptimoRouteDriverDay.
Not yet in DbMigrations: several of the changed objects exist only live. Capture each one's current live body as a file first, from the sql-ontology, as UserStory1637 did, so the change shows up as a diff.

Decisions

All the design questions are closed:

Default location: the Prime contact address only. Dated overrides are the only portal setting.
A relocation's state is the state they're booked in, not the address's state. It must be a state estimators are based in: one with enabled skills, which is VIC and QLD today. It grows on its own if staff are ever based elsewhere. A QLD team staying at Tweed Heads (NSW) to cover the top edge of NSW is a QLD relocation. The dialog defaults "Booked in" from the geocode, and asks when the address is outside a base state. Enforced in spEthBookingEstimatorRelocationUpsert. Because a base state always has day slots, no slot generation is needed when a relocation is saved.
NSW jobs are booked as QLD until there is a NSW office. This is configuration: ethBookingConfig BookingStateMap, State = NSW → QLD (migration 0027), read through fnEthBookingStateMap(). These all use the mapped state:
- fnEthBookingDayAvailability, for the job and for other pending bookings;
- vEthBookingEstimatorDayLoad;
- spOptimoRouteGetAllowSpecificTimeBookings;
- spEthBookingPointDistanceQueue, so QLD bases get distances to NSW jobs;
- spEthBookingEstimatorRelocationBookingConflicts;
- spEthBookingSetDate / spEthBookingClear.
A top-edge NSW job is offered QLD estimators and QLD's 8–2 in QLD time. Northern NSW observes daylight saving and QLD doesn't, so in summer that is 9–3 NSW local time. To open a NSW office, delete the row. The job-to-job queue is mapped too (spEthBookingJobPointDistanceQueue, 0028), as is the Geo proc. Not mapped yet: spOptimoRouteGetAllowAdvancedBookings' unassigned-state path.
Eligibility: a relocation makes the estimator eligible in its state, for exactly its dates. Home-state eligibility is suppressed for those dates. Border eligibility (keeping the home state as well, e.g. Albury/Wodonga) is out of scope.
Working hours: they follow the relocation state's time zone. The 8–2 booking slots and the 6–4 OptimoRoute hours are local wall-clock times, so no conversion is needed.
OptimoRoute: push the location and re-plan immediately on save, so issues appear as early as possible. Retries run before each re-plan of a date; there is no separate pre-cutoff job. Dates inside the plan window are allowed, warned about, and flagged for a manual change.
Distance queue: 500 jobs per call, double the current 250.

Build status

Migrations written (DbMigrations/Scripts/Migrations/, not yet applied anywhere):

Script	Contents
`0021_20260929_BookingLocationPoint.sql`	`ethBookingLocationPoint`; `PointID` column, FK and index on `primeContactLocation` and `primeJobsLocation`
`0022_20260929_BookingPointDistance.sql`	`ethBookingPointDistance`
`0023_20260929_EstimatorRelocation.sql`	`ethBookingEstimatorRelocation`; config `RelocationPointRetentionDays` = 90
`0024_20260929_OptimoRouteDriverLocationPush.sql`	`ethOptimoRouteDriverLocationPush`
`0025_20260929_BookingLocationPointBackfill.sql`	Points and stamps for existing geocodes; estimator distances copied onto point pairs

Programmable objects written (DbMigrations/Scripts/Programmable/, not yet applied anywhere; every file parses with ScriptDom):

Kind	New	Changed (live-only objects are captured as files for the first time, with the change marked)
Functions	`02_fnEthBookingPointDistance`, `03_fnEthBookingEstimatorBase`, `04_fnEthBookingEstimatorSkillOnDate`, `05_fnEthBookingJobMinTravelMins`	`fnEthBookingDayAvailability` (eligibility and travel per day)
Views	`vEthBookingPointDistance`	`01_vEthBookingEstimatorDayLoad`; `vOptimoRouteDriverDay` (first capture)
Distance procs	`spEthBookingLocationPointGetOrCreate`, `spEthUpsertBookingPointDistance`, `spEthBookingPointDistanceQueue`, `spEthBookingPointDistancePurgeOrphans`	`spPrimeUpsertContactLocation`, `spPrimeUpsertJobsLocation` (stamp PointID); `spEthUpsertBookingEstimatorJobDistance` (transitional: also writes the point cache)
Booking procs	—	`spEthBookingSetDate`, `spEthBookingClear`, `spOptimoRouteGetAllowSpecificTimeBookings`, `spEthBookingDaySlotPopulate` (all first capture)
Relocation procs	`spEthBookingEstimatorRelocationUpsert`, `...List`, `...Cancel`, `...Delete`, `...BookingConflicts`, `spEthBookingEstimatorLocationSummary`	—
OptimoRoute push procs	`spOptimoRouteDriverLocationPushQueue`, `...PushRecord`, `...ManualConfirm`, `...PushOutstanding`	—

Deploy order. The database can deploy before the WebAPI and AdminPortal changes:

Distance queue. The new pair-based queue is a new proc (spEthBookingPointDistanceQueue), so the running loop keeps using spEthBookingEstimatorJobDistanceQueue and spEthBookingGetMissingEstimatorLocations unchanged.
Distance writes. The loop's upsert, spEthUpsertBookingEstimatorJobDistance, now also writes the point cache, so readers that have already switched over still see new distances.
Retirement. Once the new C# is live, retire those three procs and ethBookingEstimatorJobDistance through zz_DropDeprecated.

Left unchanged, deliberately:

spEthGetBookingEstimatorLocations: the single-job on-demand path moves to spEthBookingPointDistanceQueue @JobID in C#.
spEthBookingGetEstimators / spEthBookingGetEstimatorStates: AdminPortal state filters only, not eligibility.
An existing quirk in spOptimoRouteGetAllowSpecificTimeBookings, recorded in its header: a missing distance is charged 0 minutes, not 30.

Dev verification (2026-09-29). 0021–0026 applied to dev with no errors.

Backfill: every geocode was stamped (1,078 contacts, 10,644 jobs). 20,164 of 20,183 estimator distances mapped, collapsing to 19,286 pairs; the other 19 rows are jobs with no geocode.
Performance: the first per-day fnEthBookingDayAvailability returned the same rows as v5, but cost ~730 ms CPU per job against v5's 11 ms. Dev elapsed time runs about 10× CPU because dev is throttled, so CPU is the fair measure. The plan re-ran the nested date functions per row. The function is now restructured onto v5's shape, which measured 28 ms CPU per job with results identical to the per-day version across 3,394 rows. Migration 0026 adds the two covering indexes it seeks on.
Fallback travel: it now uses enabled estimators only. For an estimator with no geocode on dev (Theo Kastoras), it changed from 36 to 72 minutes, because the old fallback included disabled estimators' cached rows.
Found on dev: Theo Kastoras had no Prime contact address, which led to the new grid requirement above. It's fixed in production, and dev was patched by hand.

Still to do on the database side:

Run DbMigrations against dev again, so the restructured function replaces the slow per-day version currently deployed there.
Re-check vEthBookingEstimatorDayLoad (it still applies fnEthBookingEstimatorSkillOnDate per slot) over a realistic 60–90-day window.

C# written (builds; all 87 tests pass; not yet run end to end):

Area	Change
`CommonDataAccess`	Models and `IBookingData` / `IOptimoRouteData` methods for every new proc
`CommonOptimoRoute`	`OptimoRouteDriverLocationService`: one bulk `update_drivers_parameters` call per ≤500 driver-dates, `custom` or `employeeDefault`, start = end location, work times omitted. Dates inside the plan window are recorded ManualRequired, past dates skipped, and each result is recorded. Pushed dates are re-queued for planning. `StartPlanningFromQueue` calls it (`requeuePlanning: false`) before planning each date outside the window.
`WebAPI`	`GeoLocationService.ProcessEstimatorJobDistanceQueue` and `CalcDistance` use `spEthBookingPointDistanceQueue` / `spEthUpsertBookingPointDistance`. The service is registered in DI.
`AdminPortal`	Settings > People > Estimator Locations (`BookingEstimatorLocations.razor` and the `EstimatorLocationEdit` dialog), described below

The Estimator Locations section

Who can use it: Admin and BookingManager, through a new SettingsAudience.BookingManagerOrAdmin. The nav menu and the Settings page both pass the role.
The grid: highlights an estimator with no Prime address.
The dialog covers:
- add, edit, end now, cancel and delete;
- geocoding the address and showing the result back;
- the pinned-work and plan-window warnings;
- OptimoRoute Retry and "Done in OptimoRoute".

Job-to-job on points - done (2026-09-30). It was brought forward from the phasing table above, and one design change came out of measuring it.

What moved.
- Job-to-job travel is keyed by location points: spEthBookingJobPointDistanceQueue, spEthUpsertBookingJobPointDistance, and the Geo proc's nearby lookup through vEthBookingJobPointDistance.
- It has the 31-day window (JobJobDistanceWindowDays, migration 0028) and the NSW → QLD mapping.
- Jobs at the same address count as nearby at 0 minutes.
Design change: separate distance tables (0029). It was first built on the shared point cache (0028), which grew it from 19k to 50k pairs. That slowed the booking queries on dev:
- the availability function went from 30 to 71 ms CPU per job;
- the Geo proc went from 57 to about 150.
Each job's point gained hundreds of job-to-job pairs the availability engine never needs, and the cost grows with job density. Job-to-job pairs therefore live in ethBookingJobPointDistance. Points are still one shared table; only the pairs split by purpose. Measured after 0029, against the baselines:
- availability function 29 ms;
- Geo proc 55–60 ms;
- day-load view 32 ms, details view 39 ms;
- Geo output identical, all 150 rows.
Transitional. spEthUpsertBookingJobJobDistance mirrors into the new table until the new WebAPI deploys. The purge cleans both tables.
Tested end to end on dev. 3 pairs deleted, the queue returned exactly those 3, and WebAPI ProcessJobJobDistanceQueue recalculated them into the job-pair table.

Shortest travel - built (2026-09-30). It replaces the "a booked job nearby" rule. It drives the booking page's ranking and is shown in AdminPortal Booking Advanced.

What it computes. spEthBookingJobMarginalTravel builds each candidate estimator-day as it stands:
- base → stops, open-ended: travel home isn't counted, matching the engine's one-way base → job. The base is a relocation covering the date, else home.
- The stops are planned OptimoRoute orders by ScheduledAt, then assigned dated bookings not yet in a planned order, by FromDate.
- It prices each gap as d(k, job) + d(job, k+1) − d(k, k+1), and after the last stop as d(last, job).
MarginalTravelMins is the cheapest gap; on an empty day it is base → job, the same as the engine's travel. The caller fills #MarginalTravel with its estimator-days and passes the engine's own TravelMins as BaseToJobMins. Outputs:
- DayTravelMins;
- NearestStopMins, from cached legs only;
- StopCount;
- MissingLegs.
Missing legs are estimated from the straight-line distance between the two points:
- under 25 km: 8 + 1.27 min/km;
- 25 km and up: 19 + 0.76 min/km.
The lines were fitted by least squares to every cached Google pair on dev: 30,300 short and 21,083 long pairs. They meet at about 25 km. A flat 30 made far-apart legs look cheap, because two guesses cancel out in the insertion cost. On J014187, Theo's day with a Patterson Lakes stop priced at +30, when the Patterson Lakes → Loch drive is about an hour; it is now +63.
The * still marks an estimated leg. Only when a point is missing does it fall back:
- base ↔ job uses BaseToJobMins, the engine's figure with its own fallbacks (an estimator with no geocode);
- anything else uses 30.
Open-ended day. Travel home isn't counted, matching the engine's one-way base → job. An empty day's figure is the same as the travel summary. As a result, any job within the nearby limit of a base counts as efficient on an empty day, not just one near a booked job. This is intended (decided 2026-09-30): a job close to home is cheap either way, and OptimoRoute's planning will reorder the day around it.
Geo proc (change 3).
- Per date, it picks the estimator whose day the job adds least to, then the most room.
- An efficient day (marginal ≤ the nearby limit) takes "nearby"'s place in the priority tiers.
- The customer's "we're in your area" tag now means a real stop that day is within the limit.
- New columns: MarginalTravelMins and NearestStopMins.
- Feasibility is untouched.
Details proc (spEthBookingGetAvailabilityDetails, first capture). It returns the same 40 rows, plus the five columns. Booking Advanced shows "+X travel", with * when a leg was estimated.
Queue. spEthBookingJobPointDistanceQueue also queues every pair of stops on the same estimator-day in the window, with no radius, so day legs fill in over time.
Measured on dev, 15 jobs, warm, A/B against the committed proc in the same batch:
- The Geo proc is 62 → 79 ms CPU per job. The helper is about 15 ms of that, after two fixes:
  - fetch the job's cached distances once (four index seeks), not two cache probes per node;
  - table variables for its working sets.
- The Details proc is 41 ms.
- The Geo output has the same dates in the same order for all 13 jobs. Dev has one estimator-day with a stop, so no day was efficient.
- The helper's results matched exactly across both optimisations.

Still to do:

Retire the old estimator-distance objects once the new WebAPI is deployed: spEthBookingEstimatorJobDistanceQueue, spEthBookingGetMissingEstimatorLocations, spEthUpsertBookingEstimatorJobDistance and ethBookingEstimatorJobDistance.
At the same time, retire the old job-job objects: spEthBookingJobJobDistanceQueue, spEthUpsertBookingJobJobDistance and ethBookingJobJobDistance.
The push inside StartPlanningFromQueue is untested, because planning on dev may queue admin SMS.

UserStory1727 - Location point distances (temporary estimator relocation) ​

The requirement ​

How it works today ​

Where locations come from ​

Distance caches ​

Why this can't support a relocation ​

Proposal ​

1. ethBookingLocationPoint: one row per distinct coordinate ​

2. Point stamps on the existing location tables ​

3. ethBookingPointDistance: the single distance cache ​

4. ethBookingEstimatorRelocation: the temporary base ​

5. fnEthBookingEstimatorBase(@Date): where an estimator is based on a date ​

6. fnEthBookingEstimatorSkillOnDate(@Date): eligibility follows the relocation ​

Change detection: the point replaces the hash at the distance level ​

Queues ​

Estimator → job (this story) ​

Job → job (the separate work item) ​

Readers to change ​

Estimator → job (this story) ​

Eligibility: readers of ethBookingEstimatorSkill (this story) ​

Job → job (the separate work item) ​

C# (CommonDataAccess / WebAPI) ​

AdminPortal: Estimator Locations ​

Where it lives ​

Grid: one row per estimator with at least one enabled skill ​

Row click: the BookingEstimatorLocationEdit dialog ​

Data access ​

OptimoRoute: moving the driver's start and end location ​

The API supports it, per driver per date ​

Parameters are per date, so there is no "move back" ​

When to push: immediately on save ​

Re-planning the moved dates ​

Working hours and time zone ​

Tracking what was pushed: ethOptimoRouteDriverLocationPush ​

Code ​

Existing behaviour worth knowing ​

Migrating the data ​

Clean-up ​

Phasing ​

Objects (per DbMigrations/README.md) ​

Decisions ​

Build status ​

UserStory1727 - Location point distances (temporary estimator relocation)

The requirement

How it works today

Where locations come from

Distance caches

Why this can't support a relocation

Proposal

1. `ethBookingLocationPoint`: one row per distinct coordinate

2. Point stamps on the existing location tables

3. `ethBookingPointDistance`: the single distance cache

4. `ethBookingEstimatorRelocation`: the temporary base

5. `fnEthBookingEstimatorBase(@Date)`: where an estimator is based on a date

6. `fnEthBookingEstimatorSkillOnDate(@Date)`: eligibility follows the relocation

Change detection: the point replaces the hash at the distance level

Queues

Estimator → job (this story)

Job → job (the separate work item)

Readers to change

Estimator → job (this story)

Eligibility: readers of `ethBookingEstimatorSkill` (this story)

Job → job (the separate work item)

C# (`CommonDataAccess` / `WebAPI`)

AdminPortal: Estimator Locations

Where it lives

Grid: one row per estimator with at least one enabled skill

Row click: the `BookingEstimatorLocationEdit` dialog

Data access

OptimoRoute: moving the driver's start and end location

The API supports it, per driver per date

Parameters are per date, so there is no "move back"

When to push: immediately on save

Re-planning the moved dates

Working hours and time zone

Tracking what was pushed: `ethOptimoRouteDriverLocationPush`

Code

Existing behaviour worth knowing

Migrating the data

Clean-up

Phasing

Objects (per `DbMigrations/README.md`)

Decisions

Build status