Booking KPI Alerts & the Availability-Details View — How It Works

Consolidated 2026-09-30 from UserStory1288, UserStory1584 and the BookingKPIAlerts notes. Checked against the code on UserStory1775updatedesignspecsbookings and the DbMigrations definitions, including migration 0031 (UserStory1774).

Covers: the Booking KPI alert (WebAPI + n8n + AdminPortal Settings), and the estimator day-load view that the alert shares with the AdminPortal Booking Availability pivot and the estimator calendar.

---

1. What the alert does

Emails a nominated list when the next available booking date for an estimator type (in a state) is further out than its KPI. For example: "no VIC field estimator is free for 8 business days, KPI is 5".

Piece	Where
Endpoint	POST /api/BookingKPIAlert/Run on WebAPI (BookingKPIAlertController), basic auth like every WebAPI endpoint
Logic	WebAPI/Services/BookingKPIAlertService.cs (scoped, registered in WebAPI/Program.cs)
Config table	dbo.ethBookingKPIAlert
Config procs	spEthBookingKPIAlertGet, spEthBookingKPIAlertUpsert, spEthBookingKPIAlertDelete via BookingKPIAlertData / IBookingKPIAlertData
Holidays	spEthPublicHolidayGet (reads ethPublicHoliday)
Availability	spEthBookingGetAvailabilityDetailsAll (field) and spEthVABookingGetDesktopEstimatorAvailabilityDetailsAll (desktop)
Email	spEthEmailMessageInsertHtml via IEmailMessageData.InsertHtmlMessage
Schedule	n8n flow Booking KPI Alert - Run (C:\GitHub\n8n-backup\workflows)
UI	AdminPortal Settings → KPI Alerts (/settings/kpi-alerts, Admin only)

The KPI config procs and table are not yet captured in DbMigrations; their live definitions are in the sql-ontology (schemas/procedures, schemas/tables). The availability view and procs are in DbMigrations/Scripts/Programmable/.

---

2. Configuration

2.1 The rule (ethBookingKPIAlert / BookingKPIAlertModel)

Column	Meaning
EstimatorType	Field or Desktop (BookingEstimatorType). Strings, not an enum, so they round-trip without a converter.
State	NULL = all states. Ignored for Desktop — desktop estimators work nationally.
StateKey	Computed, persisted ISNULL(State, '*'). Rules are unique on type + state, so VIC field can notify different people to QLD field.
ThresholdBusinessDays	Next available date further out than this is a breach.
MinimumUsableMins	The smallest gap that counts as availability (§3.2). CHECK >= 1. Default 60.
Recipients	Comma-separated addresses (semicolons also accepted). Empty = sends nothing.
IsEnabled	Off rules are skipped entirely.
CreatedAt, UpdatedAt	Audit.

The rules were first seeded as disabled rows with no recipients (one Field row per state with field estimators, plus one national Desktop row), so creating the table could not start emailing anyone.

2.2 MinimumUsableMins defaults

Type	Default	Made of
Field	60	Field Quotation 45 mins on site + 15 travel
Desktop	30	Shortest desk appointment; no travel

BookingEstimatorType.DefaultMinimumUsableMins in C# and the column default DF_ethBookingKPIAlert_MinimumUsableMins agree. Migration 0031 (UserStory1774, 2026-09-30) moved Field Quotation from 30 to 45 minutes, and with it moved every Field rule still on the old 45 default to 60 and changed the column default to 60. A rule someone had deliberately set to another figure was left alone.

2.3 AdminPortal Settings → KPI Alerts

One row per rule: Estimator type, State, KPI (business days), Min usable gap (mins), Notify, On, and a delete button.

• State is disabled for Desktop ("Desktop covers all states"). Switching a rule to Desktop clears its state, so the page never shows a filter the alert isn't applying.
• Min usable gap follows the type while it is still that type's default. A tuned figure is kept when the type changes. Minimum 1.
• Notify is validated as email addresses and is required when the rule is On. An enabled rule with nobody to tell would otherwise look configured and never send.
• Add rule creates a Field rule, KPI 5, gap 60, off, no recipients.
• Everything saves with the page's single Save button, including deletes. Deleted rows are held until Save, and deletes run before upserts, so removing a rule and adding a replacement for the same type + state doesn't hit the unique index.

---

3. How a run evaluates a rule

for each enabled rule:
    no recipients           -> SkippedNoRecipients (logged as a warning, not a failure)
    nextAvailable           = earliest bookable day with a usable gap (§3.1, §3.2)
    businessDays            = business days today -> nextAvailable (§3.3)
    breach if nextAvailable is null OR businessDays > ThresholdBusinessDays
    breach and not dryRun   -> queue one email per recipient

Both availability sets are loaded once per run with the procs' default window (today onward), and every rule is evaluated against them in memory.

3.1 Which days are candidates

Filter	Rule
Source	Field rules read the field set; Desktop rules read the desktop set.
Date	Date >= today (Melbourne, §3.4) and IsAfterCutoff = 1.
State	Field rule with a state: the row's own State (the slot's state) must match. Desktop: state ignored.

IsAfterCutoff is computed in SQL from the state's own clock: the first bookable day is tomorrow, or the day after once local time is past the route-planning cutoff (ethBookingConfig RoutePlanningCutoffTime, falling back to 16:00). A run in the late afternoon must not count a day OptimoRoute's planning run has already closed. On the desktop side there is no planning run, so IsAfterCutoff is simply Date > today.

3.2 The usable-gap test

A candidate day counts only when, for at least one matching estimator:

SlotMins - RouteMins - AssignedPendingMins >= MinimumUsableMins

Term	Meaning
SlotMins	The estimator's own working window that day (estimator schedule, UserStory1727).
RouteMins	Minutes already on the planned OptimoRoute route (optimoRouteDriverDay); desktop: actual Prime appointments.
AssignedPendingMins	Dated bookings assigned to that estimator with no scheduled OptimoRoute order yet — service minutes plus team-minimum travel. Always 0 for desktop.
MinimumUsableMins	The rule's floor (§2.2).

Why a floor and not the canonical rule. The canonical rule is dbo.fnEthBookingDayAvailability:

RouteMins + AssignedPendingMins + JobMins + HomeTravelMins <= SlotMins

The alert can't call it. The function takes a @JobID because HomeTravelMins is one estimator's drive to one job, and the alert has no job. It asks how far out the diary is, not whether a given job fits. So it applies every term that doesn't need a job, and replaces JobMins + HomeTravelMins with the configured floor. Because it is a floor it can only be more cautious than the canonical rule, which is the safe direction for something that emails people. The email states the floor, so nobody has to work it out from a surprising figure ("but the pivot shows 34 minutes free").

No availability at all is a breach. If nobody is free anywhere in the window the data covers, nextAvailable is null and the rule alerts. That is worse than a late date, not better.

3.3 Business days

• Counted from the day after today up to and including the next available date. Today counts as zero, so a same-day slot is never a breach.
• Weekends are skipped, along with public holidays from spEthPublicHolidayGet for the rule's state. A rule with no state (Desktop or all-states Field) passes NULL, which returns every state's holidays. That's deliberate: a national team loses capacity on any state's holiday.
• If the holiday lookup fails, the run counts weekdays only and logs an error. That overstates the gap slightly, which can only make the alert keener, and is better than losing the run.

3.4 "Today"

Today is Melbourne's date (Australia/Melbourne), matching the booking stack's AUS Eastern day windows. On a UTC host the server's own date is behind Melbourne's for the first ten hours of the day, which is long enough to change the business-day count. If the time zone can't be found, it falls back to the UTC date.

---

4. Running it

4.1 Endpoint

POST /api/BookingKPIAlert/Run[?dryRun=true]

• dryRun=true evaluates everything and returns the breaches without queueing email. Use it when setting thresholds for the first time.
• The controller holds its own semaphore (BookingKPIAlertController:Run). A second call while one is in flight gets 409 Conflict, so a slow availability query can't queue the same alert twice.
• An unhandled error returns 500 and is logged.

Response (BookingKPIAlertRunResult):

{ "rulesEvaluated": 3, "emailsQueued": 1, "withinKPI": 2, "skippedNoRecipients": 0,
  "breaches": [ { "estimatorType": "Field", "state": "VIC", "thresholdBusinessDays": 5,
                  "minimumUsableMins": 60, "nextAvailableDate": "2026-08-27",
                  "businessDaysOut": 8 } ] }

emailsQueued counts breaching rules, not messages. Each rule queues one message per recipient.

4.2 Schedule (n8n)

The Booking KPI Alert - Run flow runs at 08:00 on weekdays (0 8 * * 1-5), and it's active on Prod. It has the same shape as Recurring Appointments - Reconcile from Prime: manual + schedule triggers, spEthTaskLock* around the call, then the POST.

The task lock isn't redundant with the controller's semaphore. The n8n HTTP node reports a 409 as a failed execution, and the lock makes a skipped run look like a skipped run instead of an error. For a Test copy, change the URL host and the two credentials (the Prod MSSQL and the Ethos Prod WebAPI basic auth), which are referenced by Prod id.

4.3 Email

• Goes onto the existing queue (spEthEmailMessageInsertHtml), which the n8n Email Queue - Send flow drains every three minutes. From noreply@ethosbuilding.com.au.
• One message per recipient, not a shared To line, so a reply doesn't fan out to the whole list.
• Subject: "Booking KPI alert - {scope} is N business days out (KPI K)", or "… no availability for {scope}".
• The body gives the next available date, the business days out, the KPI, the check date, a note that weekends and state holidays are excluded, and the usable-gap floor.

4.4 Behaviour worth knowing

• Only breaches are emailed. A run where everything is inside KPI sends nothing, so an email always means something needs attention.
• No de-duplication between runs. A breach that lasts three days emails on each of those days. That's intentional for a daily KPI, and it's the first thing to change if it proves noisy.
• Availability is not recomputed. The alert reads the same procs as the Booking Availability pivot. A second SQL definition of "available" could drift from the page staff are sent to.

---

5. The availability-details objects (UserStory1584)

The alert, the Booking Availability pivot and the estimator calendar all used to read a single object with a date window built in. Now the shared facts live in one view, and each caller states its own window.

Object	Role
dbo.vEthBookingEstimatorDayLoad (Views/01_…)	The facts. One row per estimator per working day slot, with SlotMins, RouteMins, AssignedPendingMins, State, IsAfterCutoff, and the state's LocalToday and CutoffDate. No date window.
dbo.vEthBookingAvailabilityDetails (Views/02_…)	Thin wrapper: the load view with Date > LocalToday, without LocalToday/CutoffDate. Kept for its original "future bookable days" shape.
dbo.spEthBookingGetAvailabilityDetailsAll	Field half of the pivot, and the alert's field source. @PastDays INT = 0, @FutureDays INT = NULL (no cap). Reads the load view directly.
dbo.spEthVABookingGetDesktopEstimatorAvailabilityDetailsAll	Desktop half, and the alert's desktop source. Same two parameters. @FutureDays = NULL keeps "next 30 weekdays". Returns State = NULL, AssignedPendingMins = 0, IsAfterCutoff = Date > today, so it binds to the same BookingAvailabilityDetailsAllModel.
dbo.spEthBookingIsFieldEstimator	EXISTS on an enabled ethBookingEstimatorSkill row for an active Prime user. No dates. IBookingData.IsFieldEstimator.

Programmable scripts run in resource-name order, and a view has no deferred name resolution, so the view files are numbered: 02 selects from 01.

5.1 What stays shared and what each caller decides

Shared (in the load view)	Per caller
The days an estimator works: weekends and public holidays are excluded unless dated schedule hours make them working days	The date window
The state time-zone mapping, LocalToday, and the route-planning cutoff → IsAfterCutoff	Whether to filter on IsAfterCutoff
Who works each slot (relocations, UserStory1727) and pending-booking load

Callers filter on LocalToday/IsAfterCutoff and never re-derive them. A caller that computed its own "today" is how this stack came to disagree with itself.

5.2 The three consumers

Consumer	Needs	Reads
BookingKPIAlertService	Bookable days only	GetAvailabilityDetailsAll() defaults (today onward), then filters IsAfterCutoff in C#
Booking Availability pivot (/bookingavailability)	Busyness overview, today included	Both procs, defaults. The "Set Unavailable" action is offered from today onward regardless of existing bookings or IsAfterCutoff; its dialog warns of the orders already on the day and re-plans the date afterwards. If the day had existing orders/appointments it queues an email to bookings@ethosbuilding.com.au listing them, with who booked the leave, its type and window (UserStory1827). The appointment goes on the per-state bookings job: a field estimator's skill state, or a choice when they have both or none; a desktop estimator chooses, defaulted to their Prime user's state via spEthCalendarGetUserHolidayState (UserStory1834).
Estimator calendar (/bookingavailabilitycalendar)	"Is this a field estimator?", then 60 days back of routes	IsFieldEstimator, not an availability query

---

6. History and rationale

6.1 UserStory1288 — the alert could not fire (19 Aug 2026)

On Wed 19 Aug the alert reported "3 rules evaluated, 3 within KPI, 0 breaches". VIC field estimators' first genuinely bookable day was the following Monday, three business days out against a KPI of one.

• Zero bar. The day test was SlotMins - RouteMins > 0. Grant Mills had 34 minutes left on Thu 20 Aug. The shortest field appointment plus travel couldn't fit, and Advanced Field Booking correctly showed the day as Busy. Any leftover minute made a day "available". Don't reintroduce a bar of zero. This is why MinimumUsableMins exists and has CHECK >= 1.
• AssignedPendingMins ignored. Dated bookings with no OptimoRoute order yet were invisible, so a day already spoken for read as free.
• No cutoff. A run after 16:00 counted the next day, which the planning run had already closed.
• State matched on the estimator. Rules used spEthBookingGetEstimatorStates, so an estimator enabled in VIC and QLD fed their QLD rows into a VIC rule. Rules now match on the row's own state, and the alert no longer calls that proc (it has other callers).
• The old view's "future" used the SQL host's clock (UTC in Azure). For the first ten hours of every Australian day it offered a date that had already passed. It now uses the state's local today.

All four faults made the alert quieter than the truth. The alert had been honouring only the first term of fnEthBookingDayAvailability, making it the sixth copy of a calculation that function exists to stop being copied.

6.2 UserStory1584 — one query, three conflicting requirements

vEthBookingAvailabilityDetails ended AND [Date] > LocalToday, and its own header admitted the conflict: "today is deliberately still excluded … admitting it would change what the Booking Availability pivot renders." Two symptoms followed:

• No "today" column for field estimators on the pivot, while desktop estimators had one. The desktop proc included today on purpose. Two halves of one grid had two date definitions.
• Blank past days on the calendar for some field estimators. The calendar decided field-vs-desktop with GetAvailabilityDetailsAll().Any(a => a.EstimatorID == id), an availability query used as an identity lookup. Any estimator it returned nothing for (date window, weekends, holidays, no slots for their state) became "desktop". The page then took the desktop branch and never called the fixed spOptimoRouteGetScheduledOrders.

The fix moved the body into vEthBookingEstimatorDayLoad, gave the procs explicit windows, and added spEthBookingIsFieldEstimator. The pivot's C# date.Date > DateTime.Today was a third clock, with no cutoff rule and one answer for every state. It was replaced by the row's IsAfterCutoff.

One behaviour change beyond the fixes: the pending-bookings FromDate floor widened from 2 days back to 90. Otherwise a pending booking on a past day would be dropped and that day would read as free on the calendar's 60-day history. The floor is still SARGable, so IX_ethBooking_FromDate still serves it.

Advanced Booking was never on the view. It goes through spEthBookingGetAvailabilityDetails → fnEthBookingDayAvailability. The view was only built to mirror that function, and it inherited a forward-only rule it didn't need.

6.3 Later changes that touch this

Story	Effect here
1619	Desktop availability excludes public holidays (VIC, from ethCalendar) as well as weekends.
1637	The route-planning cutoff is configuration (RoutePlanningCutoffTime, per state, fallback 16:00), not a literal.
1727	SlotMins is the estimator's own scheduled window. Saturdays and event days with dated hours appear as working days. Relocated estimators sit under the relocation's state. Pending travel is the team minimum over location points. NSW jobs load QLD days.
1774	Field Quotation is 45 mins, so the Field floor went from 45 to 60 (migration 0031). Pending bookings are costed at the new duration straight away.