Prime Webhook — Job Sync and OptimoRoute Hand-off

Consolidated 2026-09-30 from UserStory1258 and UserStory1274.

Ethos Building / BlazorApps solution. Checked against the code on this branch (WebAPI, CommonDataAccess, CommonWebhooks, CommonOptimoRoute), the live SQL definitions in C:\GitHub\sql-ontology, and the n8n flows in C:\GitHub\n8n-backup\workflows. Where the original proposals and the code disagree, this document describes the code. Anything proposed and never built is marked Not built.

Covers: how Prime's external webhook reaches us, how the queued events are processed into the Prime mirror, what runs the first time we see a job, and how an appointment event gets to OptimoRoute (including why planning is a separate n8n flow). The OptimoRoute side is described in OptimoRoute-Appointments-Bookings-Logic.md. This document links to its sections (§6.3, §7.1, §8.5) and does not repeat them.

---

1. The moving parts

Prime ──POST /api/prime/webhook (API-Key header)──▶ PrimeWebhookController
                                                      │ insert raw row, 200
                                                      ▼
                                              ethPrimeWebhookLog  (the queue)
                                                      │
   n8n 'Prime Webhook Process Events' (every 2 min, + nudge webhook 83a9e4d7-…)
                                                      │ POST /api/prime/webhooklog/process
                                                      ▼
                                          PrimeWebhookQueueService
             ┌──────────────┬───────────────┬──────────┴───────┬───────────────┐
      change-job-status  create/complete-  change-custom-   create-estimate  create-allocation
         │               appointment       form-status          │               │
     sync job,           mirror appt       sync site forms,  sync estimates   mirror allocation,
     Client WIP,         (job first),      AI summary        (job first)      make-safe detection
     triage invite       sync contacts,
                         nudge OptimoRoute ──▶ n8n 'OptimoRoute WebAPI Create and Plan Appointments'
                                                   (9725d89d-…) ──▶ 'OptimoRoute WebAPI Plan Dates'
                                                                     (c1f4d7a2-…)

Piece	Where	Role
Receiver	WebAPI/Controllers/PrimeWebhookController.cs (POST api/prime/webhook)	Authenticates, stores the raw event, returns 200. Does no processing.
Queue	dbo.ethPrimeWebhookLog	Holds every inbound event verbatim, with its processing state.
Processor endpoint	WebAPI/Controllers/PrimeWebhookLogController.cs (POST api/prime/webhooklog/process)	Drains the queue. Behind Basic auth.
Processor	WebAPI/Services/PrimeWebhookQueueService.cs	One handler per event action.
Identity helpers	dbo.ethPrimeJobReference, dbo.spPrimeIdentifyGuid	Turn Prime's numeric job reference and mislabelled GUIDs into ids we can trust.
Scheduler	n8n Prime Webhook Process Events	Calls the processor every 2 minutes, or straight away when nudged.
Legacy ingestion	n8n Prime Job Email Sync	The older mailbox-driven new-job path. Still running alongside the webhook (§7).
OptimoRoute	n8n OptimoRoute WebAPI Create and Plan Appointments and OptimoRoute WebAPI Plan Dates	Order creation, then planning (§8).

---

2. Receiving — PrimeWebhookController

Prime has one webhook per registered URL, and it fires for every event type. eventAction in the body says which event it is. The other properties appear only when that action uses them.

Authentication. Prime sends an API-Key header containing the webhook's Identifier UUID. It does not use Basic auth. Program.cs therefore leaves every path under /api/prime/webhook out of BasicAuthMiddleware (UseWhen(... !StartsWithSegments("/api/prime/webhook") ...)), and the controller compares the header itself against Prime:WebhookApiKey (ordinal comparison). If that setting is blank, every call is rejected with 401. The key is set per environment in app settings or user secrets, never in appsettings.json.

The processing route is api/prime/webhooklog, not api/prime/webhook/..., on purpose. A route under api/prime/webhook would silently inherit the Basic-auth exemption.

What it stores. It stores the raw body. When the body is empty it stores {}, and a body that is not JSON is still stored. It stores all headers with API-Key replaced by (redacted), plus the remote IP and isAuthenticated = 1. Two values are promoted to columns because reading them costs nothing:

• eventAction
• jobId, but only when it is a GUID. Most actions send a numeric jobId instead (§4.1). For change-job-status the controller also accepts the GUID in jobStatusId, which is Prime's label for the job's own id.

Why it does nothing else. Prime retries any non-2xx response. If the event were processed inline, a slow Prime call would time out, Prime would retry, and the same event would be stored and acted on twice. If the insert itself fails, the controller returns 500 so that Prime retries instead of the event being dropped.

The nudge. Once the row is committed, the controller fires IWebhookHandler.PrimeWebhookProcessEvents() (WebhookSettings:PrimeWebhookProcessEvents → n8n webhook 83a9e4d7-3734-485b-aae5-292fc6e55a6a) from a Task.Run, without waiting for it. It is never awaited on Prime's request, and a failure is only logged. If a nudge is missed, the event waits for the next 2-minute tick and nothing else is lost.

---

3. The queue — ethPrimeWebhookLog

The log is the work queue as well as the landing table. The event is not copied into an ethTask row. The reason is that the log already holds the raw payload, so after a parser is fixed the historical rows can be replayed. An ethTask row has no payload, so a parser bug there loses the event. ethTask is still used for downstream work that the handlers hand on.

Column	Meaning
id	Identity. Processing is oldest-first by id.
receivedUTC, eventAction, jobId, payload, headers, remoteIP, isAuthenticated	As received (§2). jobId is backfilled once the processor resolves it.
processedUTC	NULL means the row is still queued.
processAttempts	Count of failed attempts.
processStatus	The outcome or the last error, truncated to 500 characters.

IX_ethPrimeWebhookLog_unprocessed is filtered on processedUTC IS NULL, so claiming a batch never scans the whole log.

Proc	IPrimeData method	Behaviour
spPrimeInsertWebhookLog	InsertWebhookLog	Inserts one row and returns its id.
spPrimeGetWebhookLogUnprocessed @Top=50, @MaxAttempts=5	GetWebhookLogUnprocessed	Returns the oldest unprocessed rows under the attempt cap.
spPrimeSetWebhookLogProcessed @ID, @JobId, @ProcessStatus	SetWebhookLogProcessed	Stamps the row processed, and writes jobId only when one is supplied.
spPrimeIncrementWebhookLogAttempt @ID, @JobId, @ProcessStatus	IncrementWebhookLogAttempt	Records a failure and leaves the row queued.

Ordering. Events for one job only make sense in the order Prime sent them, so the batch is processed oldest-first. Every handler mirrors Prime's current state rather than the state at the time of the event. A superseded status therefore resolves to the current one, and replaying an event does no harm.

Attempt cap. A row that has failed 5 times (MaxAttempts in the service must match the proc's default) is skipped from then on. It stays unprocessed with its last error in processStatus, so a broken payload shows up for a person to look at. It is not retried forever and it is not quietly completed.

Unknown actions are marked processed with No handler for '<action>' so they cannot build into a permanent backlog. The payload stays in the table and can be replayed once a handler exists.

3.1 The scheduler — n8n Prime Webhook Process Events

Triggers: Schedule (every 2 minutes), Webhook 83a9e4d7-… (Basic auth, the nudge from §2), and Manual.

SQL Get Is Locked 'n8nPrimeWebhookProcessEvents'
  → locked: stop (the run is dropped; the next tick covers it)
  → free:   SQL Lock Task (10-min TTL) → POST api/prime/webhooklog/process
              → success: SQL Clear Lock
              → error:   SQL Clear Lock on Error → Stop and Error

The endpoint also takes a ControllerSemphoreService lock (PrimeWebhookLogController:ProcessQueue) and returns 409 if a run is already in progress. If the error message contains Rate Limited, the endpoint returns 429. Any other exception returns 500. In practice ProcessQueueAsync never throws, because each event's failure is recorded against its own row.

After the batch, the service calls TriageInviteService.ProcessQueueAsync() so that any triage invite queued by a status change goes out in the same pass. See TriageInvite-Spec.md.

---

4. Identifying what the payload refers to

Prime's payloads can't be taken at face value, in two ways.

4.1 The numeric job reference — ethPrimeJobReference

"jobId": 2402073 is not the job's GUID, and it is not the human job number (J012345). It is an internal sequential reference that the job object does not return as an attribute. The only way to relate it to a job is to call Prime's /jobs/{identifier}, which accepts either the numeric reference or the GUID and returns the same job.

ethPrimeJobReference (numericReference PK, jobId, firstSeenUTC) records each pairing so that we only have to ask Prime once. The relationship is immutable.

• spPrimeGetJobIDbyReference @NumericReference (GetJobIDByReference) returns no rows if the reference is unknown.
• spPrimeSaveJobReference @NumericReference, @JobId (SaveJobReference) inserts only when the reference is absent. If the reference is already mapped to a different job it raises an error, because that would mean our assumption about the value is wrong.

The mapping is written from every place that has both identifiers in hand: each webhook handler that resolves a job, and the email flow (§7). The Prime notification email's "JobID" is the same numeric reference.

ResolveJobIdAsync(event, forceSync) reads the numeric jobId. When forceSync is false it tries the map first. On a miss, or when forceSync is true, it calls IPrimeJobService.GetJobByIdentifier, which fetches and mirrors the job, and then saves the mapping.

4.2 Mislabelled GUIDs — spPrimeIdentifyGuid

Action	Property	What it actually holds
change-job-status	jobStatusId	the job's id
create-appointment	appointmentTypeId	the appointment's id
create-allocation	allocationTypeId	the allocation's id
change-custom-form-status	customFormStatusId	the site form's id (primeSiteForms.siteFormId)

Prime may correct these names without warning. Prime GUIDs are unique across entity types, so the handlers identify each value by looking it up rather than trusting its label. spPrimeIdentifyGuid @Id, @ExpectedEntity (IdentifyGuid) returns one row for each mirror table the GUID is found in. When @ExpectedEntity is given, the expected case costs one index seek (the proc uses OPTION (RECOMPILE) so the other branches are removed from the plan). The full sweep over all tables runs only when that probe misses. The live proc probes primeJobs, primeAppointments, primeAppointmentType, primeStatuses, primeUsers, primeContacts, primeAllocations, primeSiteForms, primeEstimates, primeWorkOrders, primeAttachment, primeNotifications, primeSchedule, primeScheduleItem, primeFilterTags and primePerils. When a new action needs a new entity type, add it to that list. A longer list costs the expected case nothing.

TryResolveEntityGuidAsync(event, entity, syncFromPrime, preferredProperties…):

1. Collects every GUID-valued property in the payload.
2. Probes the preferred property names first and then every other property. If an id turns up under an unexpected property it logs a warning ("payload shape has changed") and uses it anyway.
3. If no probe hits the expected entity, it calls syncFromPrime(value) for each GUID the mirror doesn't know. On a create-* event a new record is expected to be missing from our mirror. Prime returns 404 for a value of the wrong type, so the fetch also proves what the value is.
4. Returns one of three faults (PrimeWebhookPayloadFault):
   • MissingIdentifier: the payload has no GUID at all.
   • WrongEntityType: something was identified, but not the entity the handler expects.
   • UnknownIdentifier: neither the mirror nor Prime can resolve the GUID.

RequireEntityGuidAsync wraps it and throws PrimeWebhookPayloadException on any fault. That exception is logged separately from other failures, along with the payload, because the fix is different: the parser needs updating, after which the row replays from its stored payload.

4.3 First sight — EnsureJobMirroredAsync

The webhook is often the first we hear of a record. The mirror has no foreign keys, so a child row (appointment, allocation, estimate, site form) written before its job would point at a job that is missing from primeJobs, and every join would quietly return nothing. Any handler that mirrors a child therefore calls EnsureJobMirroredAsync(jobId) before writing the child row. That method does an IdentifyGuid probe. If the job is missing, it calls GetJob and then runs the new-job pipeline (§6).

---

5. Event handlers

eventAction	Payload sample	Handler
change-job-status	{ "jobId": 259047, "jobStatusId": "<job guid>" }	§5.1
create-appointment	{ "jobId": 2402073, "appointmentTypeId": "<appointment guid>" }	§5.2
complete-appointment	{ "jobId": 2413856, "appointmentId": "<appointment guid>" }	§5.2
change-custom-form-status	{ "jobId": 2404179, "customFormStatusId": "<site form guid>" }	§5.3
create-estimate	{ "jobId": 2410965, "estimateId": "<estimate guid>" }	§5.4
create-allocation	{ "jobId": 2274772, "allocationTypeId": "<allocation guid>" }	§5.5
anything else	—	processed as No handler for '…'

5.1 change-job-status — ProcessChangeJobStatusAsync

1. Resolve the job from its GUID properties (preferring jobId, then jobStatusId), using GetJob as the Prime fallback.
   • If the job was already mirrored, call GetJob again to pick up the new status.
   • If the job had to be fetched from Prime, this is first sight.
2. If no GUID resolves, fall back to the numeric reference. First sight is when that reference was not yet in ethPrimeJobReference. This is checked before the fetch writes the mapping. The fallback then calls ResolveJobIdAsync(forceSync: true), because syncing the job is the whole purpose of this handler.
3. Save the numeric → GUID mapping.
4. On first sight, run RunNewJobPipelineAsync (§6).
5. QueueClientWIPStatusChangeAsync enqueues a Client WIP StatusChange trigger with source ethPrimeWebhookLog:{id}. A failure here is logged and swallowed. The job is already mirrored, replaying the event would re-run the new-job pipeline, and a missed enqueue can be recovered through api/clientwipupdate/evaluate.
6. QueueTriageInviteAsync runs ITaskData.QueueTriageInvite(jobId, InitialContactMadeStatusId). The proc reads the status from the mirror and skips any job that already has an invite pending. A failure here is thrown, so the event retries: a missed invite means the customer never hears from us. This step runs last so that nothing after it can fail the event.

If there is no identifier of any kind, the row is marked processed (it will never gain one). If identifiers were present but none resolved to a job, the handler throws PrimeWebhookPayloadException.

5.2 create-appointment / complete-appointment — ProcessAppointmentEventAsync

Both actions mean that Prime's copy of the appointment has changed. A completion moves the status and sometimes the times, and OptimoRoute plans from the mirrored row.

1. RequireEntityGuidAsync(primeAppointments, "appointmentTypeId", "appointmentId"), using MirrorAppointmentAsync as the Prime probe.
2. MirrorAppointmentAsync fetches the appointment, calls EnsureJobMirroredAsync on its job, and then saves the appointment (the job is written first, per §4.3). It runs a second time to refresh an appointment we already held.
3. Save the numeric → GUID mapping. If Prime reported no job on the appointment, fall back to ResolveJobIdAsync(forceSync: false).
4. SyncAppointmentContactsAsync pulls the job's contact relationships from Prime, reads primaryContactId off a fresh GetandSaveJob (not from primeJobs, which may be stale or not yet hold the contact), and then fetches every distinct contact. A failure is logged and swallowed. ethSyncQueue and the job sync still cover contacts later.
5. RequestOptimoRouteCreateAndPlanAsync calls IWebhookHandler.OptimoCreatePlan(). In WebAPI config that is the parent flow 9725d89d-… (§8). It is fire-and-forget, and a failure is logged, not thrown.

Nothing about the appointment is passed to OptimoRoute. Mirroring the appointment is what queues it: spOptimoRouteGetAppointmentQueue compares primeAppointments with optimoRouteOrder by version (OptimoRoute spec §7.1). Then spOptimoRouteUpsertOrder → spOptimoRoutePlanUpsert marks that date NotPlanned, which queues it for planning (§8.5 there). WebAPI calls the parent flow rather than the plan-only one because no order exists yet, so the CreateOrdersFromAppointmentQueue leg still has to run.

5.3 change-custom-form-status — ProcessChangeCustomFormStatusAsync

These events fire in practice when a triage form is completed.

1. ResolveJobIdAsync(forceSync: false), because Prime's site-forms route can only be queried by job. If there is no job reference the handler throws MissingIdentifier.
2. EnsureJobMirroredAsync, then GetSiteFormsbyJob.
3. Confirm that customFormStatusId/siteFormId is now in primeSiteForms.
   • WrongEntityType → throw.
   • Not found → only a warning. The form may hang off a non-Job object, and GetSiteFormsbyJob only queries objectType 'Job'.
4. RequestAIJobSummaryAsync calls IAIData.QueueJobSummaryNow(jobId). If that fails, the event fails. It then fires AIJobSummaryGenerateForJob (n8n 7b2e9c15-…, ?jobId=) as a best-effort nudge. Both halves are needed: each earlier attempt that found no triage form pushed the job's QueuedFor up to 36 hours out, so the nudge on its own would find nothing due.

5.4 create-estimate — ProcessCreateEstimateAsync

1. ResolveJobIdAsync(forceSync: false). If there is no job reference the handler throws.
2. EnsureJobMirroredAsync, then GetEstimatesByJob. That is a delete-and-reinsert of the job's estimates, categories and line items. Prime gives no way to spot deletions, so the whole job is swept.
3. Confirm that estimateId is now in primeEstimates. WrongEntityType throws, and not found is a warning.

5.5 create-allocation — ProcessCreateAllocationAsync

1. RequireEntityGuidAsync(primeAllocations, "allocationTypeId", "allocationId"), using MirrorAllocationAsync as the Prime probe. That method calls GET allocations/{id}, then EnsureJobMirroredAsync, then SaveAllocation, keeping the same job-first order as appointments.
2. Save the numeric → GUID mapping, falling back to ResolveJobIdAsync if needed.
3. RunMakeSafeDetectionAsync calls spMakesafeUpsertPhoneCallFromPrimeJob @QueueMakeAllocationChecks = 0.

A Make Safe allocation appearing is exactly what make-safe detection waits for, and this event is the moment we learn about it. Detection therefore runs straight away, and the ethTask re-check ladder (§6) becomes the fallback. The flag is 0 on purpose: the ladder was already queued at first sight, and re-queueing it on every allocation event would pile up polling for a wait that is already over. The proc owns the whole rule. It raises a makesafePhoneCall only for a job created outside business hours (dbo.fnIsOutsideBusinessHours: weekends, or outside 08:30–17:00 Melbourne). It is also idempotent, keyed on PrimeJobID under UPDLOCK, HOLDLOCK.

---

6. The new-job pipeline — RunNewJobPipelineAsync

This runs once per job, on first sight, whichever event gets there first. It is called from §5.1 and from EnsureJobMirroredAsync (§4.3). It is triggered by first sight rather than by a particular event action, for three reasons:

• it needs no guess about which action Prime uses for a registration;
• it fires exactly once;
• if Prime never fires an event for new jobs, the email flow (§7) still catches them and this code simply never runs.

Steps:

1. spEthSyncQueueMissingObjectsByJobID @JobId (QueueMissingObjectsByJob) queues the job's status histories, contacts, allocations and contact relationships into ethSyncQueue.
2. spMakesafeUpsertPhoneCallFromPrimeJob @JobId, @QueueMakeAllocationChecks = 1 (UpsertMakeSafePhoneCallFromJob).

No waits, and no second make-safe call. When the job has no Make Safe allocation yet, the proc calls spEthTaskInsertMakeSafeAllocation, which queues ethTask re-checks at +1, +4, +10 and +15 minutes. The n8n flow Make Safe Queued Allocation Checks (every 3 minutes) claims each re-check. For that one job it then:

1. calls spEthSyncQueueUpsert for primeAllocations;
2. runs Prime Sync Queued Allocations;
3. waits 3 seconds;
4. re-runs the make-safe proc.

SMS Send Queued Messages (every 3 minutes) sends whatever the proc queued. The ethSyncQueue child queues drain on their own schedules. §5.2 and §5.5 bring in contacts and allocations immediately when their own events arrive.

Worst case for an after-hours make-safe job: it is synced as soon as the webhook is processed, the first allocation re-check runs about 1–3 minutes later, and the SMS goes within 3 minutes of that.

Failures here are thrown, so the event retries. Both procs are idempotent: queueing skips ids that are already queued, and the phone-call insert checks for an existing row under the lock.

---

7. The email path — n8n Prime Job Email Sync (still active)

This is the older new-job trigger. It runs in parallel with the webhook, and both paths are idempotent, so whichever arrives first does the work. The current shape is:

ConnectToMailbox (Outlook, polls 1 folder)
  ├─ SQL Log Request       spPrimeEmailJobRegisteredInsert(bodyPreview)   (receipt only, not outcome)
  └─ Extract JobID (Code) → GetAPIAuthKey → Loop Over Items
       → Request 1st Page   GET /jobs/{JobID}
       → SQL Upsert         spPrimeUpsertJob (~50 interpolated parameters)
       → SQL Save Job Reference   IF NumericReference > 0: spPrimeSaveJobReference
       → Queue Missing Objects    spEthSyncQueueMissingObjectsByJobID
       → Wait 35s → Sync Queued Contacts ∥ Sync Queued Allocations → Merge
       → Upsert Make Safe PhoneCall (@QueueMakeAllocationChecks=1)
       → Send SMS Messages → Sync Queued ContactRelationships → Sync Queued StatusHistories_Job
       → Move a message (to Processed)   ← last, so a failure leaves the mail for the next poll

• Extract JobID reads body.content, falling back to bodyPreview, which is a Graph-truncated preview of about 255 characters. It accepts a labelled JobID that is either a GUID or a numeric reference, quoted or unquoted. If nothing is labelled, it falls back to the first bare GUID in the text. It outputs JobID, JobIDKind, NumericReference and JobNumber, and on failure it throws a message naming the subject line and the first 300 characters. A real body looks like { "JobID": '2262480', "JobNumber": 'J014370' }, so the value is the numeric reference.
• This flow seeds ethPrimeJobReference. At the moment it is the one path that sees every new job.
• The single 35-second wait is deliberate. Prime Sync Queued Allocations treats an empty response from Prime as done and calls spEthSyncQueueDelete. Draining straight after the job upsert could delete the queue row before Prime has the allocation. The wait makes that unlikely, and the ethTask ladder (§6) recovers it when it still happens. The earlier 65-second waits and the second make-safe call were removed because the ladder covers them.
• The flow is idempotent, not exactly-once. A failure after the upsert re-runs the whole pipeline on the next poll, which is safe.

Prime Sync Jobs by Date stays as the scheduled backstop regardless. It keeps its own copy of the pipeline and its own 30-second waits.

7.1 Cutover — Not built

Step	State
Webhook job handler alongside the email flow	Built (§5.1, §6)
Compare coverage for a week: ethPrimeWebhookLog vs PrimeEmailJobRegistered	Not recorded
Strip Prime Job Email Sync back to trigger + SQL Log Request, or disable it	Not built. The flow is active with the full pipeline.
Merge the "post-job-sync" pipeline into one proc (spEthProcessNewPrimeJob @JobId) shared by the webhook, Prime Sync Jobs by Date and any future caller	Not built

Coverage check:

SELECT eventAction, COUNT(*) AS events, MIN(receivedUTC) AS firstSeen, MAX(receivedUTC) AS lastSeen
FROM dbo.ethPrimeWebhookLog WITH (NOLOCK)
GROUP BY eventAction
ORDER BY lastSeen DESC;

Until the one-proc merge is built, three copies of the job → children → make-safe sequence exist, each with different wait timings: the email flow (35s), Prime Sync Jobs by Date (30/30/30s) and the webhook handler (none). Any change to that sequence has to be made in all three.

---

8. OptimoRoute — create-and-plan, and the plan-only split

All the work is in the OptimoRouteController endpoints (OptimoRoute spec §6.3), and every queue is derived in SQL. The n8n nodes are thin callers.

"Replan the day" needs no date passed around. spOptimoRouteUpsertOrder calls spOptimoRoutePlanUpsert, which sets that date's optimoRoutePlan.PlanStatus = 'NotPlanned'. spOptimoRoutePlanGetQueue returns every NotPlanned date, and StartPlanningFromQueue plans them (§8.5 there). So creating, changing or deleting an order marks its date for replanning, and a caller only has to ask for planning to run.

8.1 Parent — OptimoRoute WebAPI Create and Plan Appointments (webhook 9725d89d-7324-437f-ab85-7b90e981e801)

Trigger	Path
Manual, and M–F 15:00–16:59 every 15 min	lock → Prime Sync Contacts by Date → Prime Sync Appointments by Date → CreateOrdersFromAppointmentQueue → CreateOrdersFromWorkOrderQueue → …
Webhook (onReceived), M–F 08:00–14:59 every 30 min, M–F 17:00–23:59 hourly, M–F 00:00–07:59 hourly, Sat–Sun hourly	lock → CreateOrdersFromAppointmentQueue → CreateOrdersFromWorkOrderQueue → …

Both paths then run Wait 3s → SQL Clear Lock → GET the Plan Dates webhook (c1f4d7a2-…). The parent releases its lock and hands over. It never lends the lock across a flow boundary.

Lock: n8nOptimoRouteCreatePlan, with a 12-minute TTL. Every HTTP and sub-workflow node has retryOnFail and an error output to SQL Clear Lock On Error → Stop And Error.

8.2 Plan-only — OptimoRoute WebAPI Plan Dates (webhook c1f4d7a2-1274-4e3b-9d68-5a0e2f8b41c7)

Triggers: Execute Workflow Trigger, Webhook (Basic auth, same credential as the parent) and Manual.

lock 'n8nOptimoRoutePlanOnly' (12 min)
  → StartPlanningFromQueue → Wait 3s → GetPlanningStatusFromQueue
      → If All Plans are Complete ($json == 0)
          false → If Planning Wait Limit Reached ($runIndex >= 60) → yes: clear lock, error
                                                                   → no:  Wait 3s, poll again
          true  → Wait 3s → GetPlanningRoutesRefreshQueue → GetOrderRefreshQueue
                → SQL Clear Lock → Call 'Webhook Send Queued SMS'
  any HTTP error → SQL Clear Lock On Error → Stop And Error

Why this is a webhook and not Execute Workflow. The first version was called as a sub-workflow. A Wait node parks a sub-workflow execution, so the Execute Workflow node treated the call as finished at the first Wait. The status poll, the refresh legs and SQL Clear Lock never ran, and the lock sat until its TTL expired. Over the webhook, Plan Dates runs as a normal top-level execution in which waits resume, and it responds on receipt so the caller is not held.

Who calls which:

Caller	Config (WebhookSettings:OptimoCreatePlan)	Why
WebAPI, from create-appointment / complete-appointment (§5.2)	parent 9725d89d-…	No order exists yet. The create leg has to run.
AdminPortal BookingAdvanced (after CreateBookingOrder)	plan-only c1f4d7a2-…	The portal wrote the order itself, which already marked the date NotPlanned.
FormsPortal Estimator (after CreateBookingOrder on book, DeleteBookingOrder on cancel)	plan-only c1f4d7a2-…	Same reason.

The portal callers skip the Prime contact/appointment sweep and the appointment and work-order queues on the click path. The config key OptimoCreatePlan is now a misnomer for them. Renaming it to OptimoPlan across CommonWebhooks and both portals is Not built.

8.3 Waiting for the lock instead of dropping the run

This applies to both lock checks in the parent and to the one in Plan Dates:

SQL Get Is Locked → If Task is Locked
   locked → If Lock Retry Limit Reached ($runIndex >= 18)
              under: Wait For Lock 20s → SQL Get Is Locked
              at:    Stop And Error Lock Busy
   free   → SQL Lock Task → pipeline
            SQL Lock Task error output → If Lock Retry Limit Reached

• 18 attempts, 20 seconds apart, so 6 minutes. That is long enough to outlast a normal full run.
• Losing the insert race is treated the same as finding the lock taken. spEthTaskLockInsert has a unique index on TaskName and throws 51002 'Task was already locked.'.
• Stop And Error Lock Busy does NOT clear the lock. A run that never acquired the lock must not release someone else's. Doing so would turn a busy-wait into two pipelines running at once.
• Giving up loses nothing, because the dates stay NotPlanned for the next schedule. The error exists so that a 6-minute contention window is visible.

Before this change, a webhook that arrived mid-run was silently dropped. The work was only picked up by the next schedule, up to 30 minutes later, and nothing recorded that it had been dropped.

8.4 The re-queue race — spOptimoRoutePlanSetStatus @OnlyIfStatus

Plan Dates has its own lock, and the portals create orders outside any lock, so order creation is no longer serialised against planning. Without a guard, this sequence loses work:

1. A plan run moves date D to Planning.
2. An order is created for D. spOptimoRoutePlanUpsert sets D back to NotPlanned.
3. The plan run finishes and unconditionally writes PlanningCompleted over it.

D is no longer queued, and the order from step 2 is never planned.

spOptimoRoutePlanSetStatus @PlanDate, @Status, @OnlyIfStatus = NULL writes only when the date is still in the state the caller observed. The callers in OptimoRoutePlanningService are:

Write	onlyIfStatus
Status poll: PlanningCompleted, C:Cancelled by the user, E:Error occurred, Unknown Error	Planning
Routes read-back: RoutesCompleted	PlanningCompleted
StartPlanningFromQueue recording a failed request as E:<code>	(none). The date is still NotPlanned at that point. Guarding the write would leave a permanently failing date queued and retried on every run, instead of parked with its error.

A date that is re-queued mid-run therefore keeps NotPlanned and is planned by the next run.

---

9. Failure modes

Failure	What happens	Recovery
API-Key missing or wrong, or Prime:WebhookApiKey blank	401, nothing stored	Fix config. Prime retries.
Log insert fails	500	Prime retries.
Process Events nudge fails	Logged	2-minute schedule.
Process Events already running	n8n: locked run dropped. Endpoint: 409.	Next tick.
Handler throws (Prime down, rate limit, bad payload)	processAttempts + 1, error in processStatus, row stays queued	Retried each run until 5 attempts. After that it stays visible, unprocessed.
Payload shape changed (PrimeWebhookPayloadException)	As above, logged with the payload	Fix the parser. Reset processAttempts to replay the row from its stored payload.
Id arrives under an unexpected property	Warning logged, event still processed	Update the preferred property names.
Unknown eventAction	Marked processed No handler for '…'	Write a handler, then replay.
Client WIP enqueue fails	Logged only	api/clientwipupdate/evaluate
Contact sync after an appointment fails	Logged only	ethSyncQueue and the job's own sync
OptimoRoute nudge fails	Logged only	The parent flow's schedule (15 min to 1 hour, depending on time)
Queued allocation drain runs before Prime has the allocation	spEthSyncQueueDelete removes the queue row	The make-safe ethTask ladder re-queues it (§6). The create-allocation event (§5.5) also covers it.
Plan Dates lock busy for more than 6 minutes	Stop And Error Lock Busy, lock untouched	Dates stay NotPlanned, so the next schedule plans them.
Plan never completes	Stops after 60 polls, lock cleared, error	Next run.
GetPlanningStatusFromQueue response wrapped in an object	If All Plans are Complete compares {{ $json }} to 0 and would never be true, so the poll stops only at the 60-poll limit	Keep that endpoint returning a bare integer.
Date re-queued during a plan run	Guarded write leaves it NotPlanned (§8.4)	Next run plans it.

---

10. Things to know before changing any of this

1. Never put a route under api/prime/webhook unless it is meant to skip Basic auth.
2. Never trust a payload property name. Identify values through TryResolveEntityGuidAsync, and add any new entity type to spPrimeIdentifyGuid.
3. A handler that writes a child row must call EnsureJobMirroredAsync first. That is also how the new-job pipeline reaches jobs that are first seen through a child event.
4. Keep handlers idempotent. Replay is the recovery mechanism. Mirror Prime's current state, and make every downstream insert de-duplicate.
5. Decide deliberately whether each follow-up step throws or swallows. Throwing replays the whole event, including the mirror and possibly the new-job pipeline. The current rule is: throw when losing the step loses something for good (a triage invite, the AI summary queue row, make-safe detection), and log when a schedule covers it.
6. MaxAttempts in the service must equal the default in spPrimeGetWebhookLogUnprocessed.
7. Planning callers only ask for planning to run. Put dates into the plan queue with spOptimoRoutePlanUpsert (through an order write), never by passing dates to a flow.
8. Any new write to optimoRoutePlan.PlanStatus from a running plan must pass onlyIfStatus. Otherwise it reopens the race in §8.4.
9. Don't call Plan Dates as an Execute Workflow sub-flow from anything that needs it to finish. Its waits park the execution (§8.2).
10. A run that did not take the lock must never clear it.

---

11. Open items

• Prime Sync Queued Allocations still uses cron */47 * * * *, which fires at minutes 0 and 47 (a 47-minute gap and then a 13-minute one). The intended setting is Minutes / 47, matching its siblings' deliberately co-prime intervals of 30, 33 and 55 minutes. Not built.
• The n8n-backup copy of OptimoRoute WebAPI Create and Plan Appointments is stale. It is dated 2026-08-07, marked active: false, and predates the split: it has no hand-over, no lock wait and no retry. §8.1 describes the flow as exported after UserStory1274. Refresh the backup before relying on it.
• The Plan Dates sticky note still says the flow takes n8nOptimoRouteCreatePlan, but the built flow takes n8nOptimoRoutePlanOnly. The Prime Webhook Process Events sticky note says the flow is "schedule-driven rather than webhook-nudged", but it now has a webhook trigger and the controller nudges it.
• ethPrimeWebhookLog, ethPrimeJobReference, the four webhook-log procs, spPrimeGetJobIDbyReference, spPrimeSaveJobReference, spPrimeIdentifyGuid and spOptimoRoutePlanSetStatus are live (confirmed in sql-ontology/schemas) but are not in DbMigrations/Scripts. Capture them as Programmable/ files before changing any of them.
• Prime Webhook Process Events drops a run silently when it finds the lock taken, and its SQL Lock Task has no error output, so a lost insert race fails that execution. This is harmless because the 2-minute schedule follows, but it is noisier than the OptimoRoute flows' lock wait.

---

12. File map

Area	Path
Receiver	WebAPI/Controllers/PrimeWebhookController.cs
Processor endpoint	WebAPI/Controllers/PrimeWebhookLogController.cs
Handlers	WebAPI/Services/PrimeWebhookQueueService.cs, WebAPI/Services/PrimeWebhookPayloadException.cs
Auth exemption, DI	WebAPI/Program.cs
Config	WebAPI/appsettings.json (Prime:WebhookApiKey, WebhookSettings:*), AdminPortal/appsettings.json, FormsPortal/appsettings.json
Outbound n8n calls	CommonWebhooks/WebhookHandler.cs (PrimeWebhookProcessEvents, OptimoCreatePlan, AIJobSummaryGenerateForJob)
Data access	CommonDataAccess/Data/Prime/PrimeData.cs, CommonDataAccess/Interfaces/IPrimeData.cs, CommonDataAccess/Models/Prime/PrimeWebhookLogModel.cs
Plan status guard	CommonDataAccess/Data/OptimoRoute/OptimoRouteData.cs (SetPlanStatus), CommonOptimoRoute/Services/OptimoRoutePlanningService.cs
Portal planning calls	AdminPortal/Components/Pages/BookingAdvanced.razor, FormsPortal/Components/Pages/Estimator.razor
SQL (live)	dbo.ethPrimeWebhookLog, dbo.ethPrimeJobReference, spPrimeInsertWebhookLog, spPrimeGetWebhookLogUnprocessed, spPrimeSetWebhookLogProcessed, spPrimeIncrementWebhookLogAttempt, spPrimeGetJobIDbyReference, spPrimeSaveJobReference, spPrimeIdentifyGuid, spEthSyncQueueMissingObjectsByJobID, spMakesafeUpsertPhoneCallFromPrimeJob, spOptimoRoutePlanSetStatus
n8n	Prime Webhook Process Events (83a9e4d7-3734-485b-aae5-292fc6e55a6a), OptimoRoute WebAPI Create and Plan Appointments (9725d89d-7324-437f-ab85-7b90e981e801), OptimoRoute WebAPI Plan Dates (c1f4d7a2-1274-4e3b-9d68-5a0e2f8b41c7), Prime Job Email Sync, Make Safe Queued Allocation Checks, Prime Sync Queued Allocations, SMS Send Queued Messages, Prime Sync Jobs by Date
Related specs	OptimoRoute-Appointments-Bookings-Logic.md (§6.3, §7.1, §8.5), TriageInvite-Spec.md