Client WIP Updates — Rules, Templates, Tokens and the Trigger Queue

Consolidated 2026-09-30 from UserStory507 (UserStory507-v2-01-proposal.md, UserStory507-v3-01-trigger-queue.md), checked against the code and DbMigrations definitions on main.

Covers: the rule table, the versioned template and {{Token}} engine, the AdminPortal settings and updates pages, and the WebAPI trigger queue that turns a Prime status change or a sent customer WIP update into a rendered client update. Where the original proposal and the code disagree, this document follows the code. Proposal items that were never built are marked Not built.

State (2026-09-30). Generation is live end to end: triggers queue, the drain evaluates rules, and rendered updates land in ethClientWIPUpdate. Delivery (AB#1954) posts updates as Prime Client Contact notes synced to the client's portal, per client (§3.11, §7.6) — Allianz via Endata and A&G via Nexus (AB#1981). The old A&G client-note prototype (§10) was deleted (AB#1951).

---

1. Purpose and vocabulary

Ethos already sends a weekly WIP update to the customer (the insured/homeowner). This feature adds updates to the client — the instructing party (insurer, broker, body corporate, loss adjuster, law firm) — driven by a configurable rule table instead of hard-coded wording.

Naming, used consistently in pages, models and procs:

Term	Means	Page
Customer WIP Updates	The insured/homeowner weekly update	wipupdatequeue
Client WIP Updates	The instructing client's update (this spec)	clientwipupdates

• Rule (ethClientWIPRule) — for one Prime job status and one trigger: whether to produce an update, which template, for which clients, and how often.
• Template (ethClientWIPTemplate) — versioned client-facing wording with {{Token}} placeholders.
• Token (ethClientWIPToken) — a named value resolved per job by dbo.fnClientWIPJobTokenValues.
• Trigger (ethClientWIPTrigger) — what causes a rule to be evaluated.
• Queue row (ethClientWIPQueue) — one trigger occurrence awaiting evaluation.
• Update (ethClientWIPUpdate) — one rendered update the engine produced.
• Evaluation log (ethClientWIPEvaluationLog) — every evaluation outcome, produced or not.

---

2. End-to-end flow

Prime change-job-status webhook ──> PrimeWebhookQueueService (after job sync)
                                        │ EnqueueAsync(StatusChange)
customer WIP email sent (n8n) ──> spEthWIPJobEmailMarkSent(ByWIPID)
                                        │ EXEC spEthClientWIPQueueEnqueue (NewWIPUpdate)
external caller ──> POST api/clientwipupdate/evaluate
                                        ▼
                               ethClientWIPQueue  (deduped on IdempotencyKey)
                                        │  webhook nudge + 5-minute schedule
                                        ▼
n8n "Client WIP Updates - Process Queue" ──> POST api/clientwipupdate/process
                                        ▼
ClientWIPQueueService.EvaluateAsync, per row, oldest first:
   spEthClientWIPMatchRule ─> spEthClientWIPResolveJobTokens ─> render
        │ no match / blocked                          │ match, all tokens resolved
        ▼                                             ▼
   spEthClientWIPLogEvaluation              spEthClientWIPUpdateCreate
   (outcome + reason)                       (update row + 'Sent' log row, one transaction)
                                                      ▼
                                   ethClientWIPUpdate: Status='Approved', SentUTC NULL
                                                      ▼
                                   spEthClientWIPUpdateQueueDelivery (end of each drain, §7.6)
                                                      ▼
                                   ethPrimeNotificationQueue → Prime 'Client Contact' note

The architecture rule: triggers only record that something happened; evaluation happens on a separate drain. Both built-in triggers sit on paths that retry (Prime redelivers any non-2xx; the n8n email flow marks sent once per contact), so evaluating inline would turn one slow evaluation into two client updates.

---

3. Data model

The rule tables pre-date DbMigrations and are part of its baseline. The queue and update tables are migrations 0007 and 0008/0009. No FK to primeContacts or primeStatuses anywhere: both are sync-replaced from Prime and an FK would fail the sync, so those columns are soft references and the UI surfaces orphans instead. FKs between the ethClientWIP* tables are enforced. Audit timestamps are UTC.

3.1 ethClientWIPRule

Column	Notes
RuleID	PK, uniqueidentifier
PrimeStatusId	Match on the Prime status id, never the name
TriggerID	FK ethClientWIPTrigger
AppliesToAllClients	1 = every client; 0 = the explicit list in ethClientWIPRuleClient
IsEnabled	The on/off switch. A status with no enabled rule produces nothing
TemplateID	FK ethClientWIPTemplate (a specific version, repointed when the template is re-saved)
MaxSendCount	Null = unlimited / no resends. Two uses: caps generated updates per (rule, job) over the evaluation log (§4 step 7), and is the total number of sends including the original for weekly resends of the last real update — 1 = no resends, 3 = two resends (§7.5)
StuckAfterDays	Days after the job's last real update before the stuck check escalates (§7.5). Null = never
EscalationRoleID	FK ethClientWIPRole. Who the stuck-job escalation emails (§7.5)
Priority	Tie-break, lower wins (default 100)
EffectiveFrom / EffectiveTo	Nullable window, compared to UTC now
Notes	Free text; the seed records why a rule was left disabled
CreatedAt/By, UpdatedAt/By	Audit

Dropped after the proposal, because they were stored but changed no behaviour: Audience (every row was "Client"; IsEnabled already means "don't send"), AllowCustomComment / CustomCommentRequired (updates are automated — there is no person in the loop to write a comment). "A column that looks like configuration but changes no behaviour is worse than no column."

3.2 ethClientWIPRuleClient

(RuleID, ClientContactId) PK. Empty when AppliesToAllClients = 1. ClientContactId is a soft reference to primeContacts.id.

3.3 ethClientWIPTemplate

TemplateID, TemplateKey (stable across versions), Name, BodyText, BodyHtml, AuthoringNotes, Version, IsCurrent, audit. Versioned — a save inserts a new row, flips IsCurrent and repoints the rules that used the old version; nothing is overwritten, so an update already generated can always be explained by the exact wording it used. No Subject: a client update is a note, not an email (dropped after the proposal).

3.4 ethClientWIPToken

TokenID, TokenName (no braces, unique), Description, SourceType, SourceRef (nvarchar(1000) — describes source and filters, shown as a tooltip), SampleValue, IsActive.

SourceType is a stored code; the UI shows a display name (ClientWIPTokenModel.SourceTypeDisplay):

Code	Shown as
PrimeField	Prime Fields
Milestone	Prime Milestone
StatusHistory	Prime Status History
WIPEntry	Customer WIP Updates

A token needs a row in both the catalogue (what a WIPManager sees and what save-time validation checks) and fnClientWIPJobTokenValues (what fills it in). See §5.

3.5 Lookups

Table	Rows	Notes
ethClientWIPTrigger	StatusChange (1), NewWIPUpdate (2), MilestoneReached, StuckCheck	Adding a trigger is data, not schema. Only 1 and 2 are raised by anything (§6). Stuck jobs are a daily check, not a trigger (§7.5)
ethClientWIPRole	CaseManager, Estimator, Supervisor, Restoration, StateManager	Escalation recipient, resolved per job by spEthClientWIPStuckEscalationsDue (§7.5)
ethClientTagType	Insurer, Broker, Loss Adjuster, Body Corp, Law Firm, with fixed Prime tag GUIDs	Client picker filter only — never a rule-matching dimension

Client tag GUIDs: Insurer 3DBC6635-69E4-4050-86FE-0C2B4CBB5B58, Broker 174B4F7E-1E98-4285-8589-D6C18920AEF1, Loss Adjuster ECBE8579-ED4E-412F-AD26-93DC46FAF27E, Body Corp A4E53658-6273-413A-B7CA-6B6B38AEDB21, Law Firm 2E09780E-0399-4FEF-BA18-9918E357EFBD.

3.6 ethClientWIPEvaluationLog

EvaluationID, JobId, PrimeStatusId, TriggerID, RuleID (null when nothing matched), ClientContactId, TemplateID, Outcome, OutcomeDetail, RenderedBody, WIPJobHistoryID (unused by the engine), IdempotencyKey (nvarchar(200), one row per queue occurrence), EvaluatedAt, EvaluatedBy, TraceJson (migration 0053, AB#2031 — the evaluation snapshot, §12). Indexed (RuleID, JobId) INCLUDE (Outcome) (migration 0008) because MaxSendCount counts over it on every evaluation.

Outcomes (ClientWIPOutcomes): Sent, NoJob, NoClient, ClientInactive, NoMatchingRule, NoTemplate, MaxSendCountReached, UnresolvedToken.

Sent means "update generated", not "delivered". It is written by spEthClientWIPUpdateCreate when the update row is created. MaxSendCount therefore caps generated updates.

3.7 ethClientWIPQueue — migration 0007_20260914_ClientWIPQueue

Column	Notes
QueueID	bigint identity PK
JobId, TriggerID	FK to trigger
PrimeStatusId	The status when the trigger fired, captured at enqueue — evaluation matches against this, not the job's status at drain time
SourceRef	Pointer to the cause: ethPrimeWebhookLog:{id}, a WIPJobHistoryID, or a caller name
CustomComment	Carried from evaluate; not used by evaluation (the rule columns it served were dropped)
IdempotencyKey	nvarchar(400), unique, unfiltered (§7)
QueuedUTC, QueuedBy
ProcessedUTC	Null = outstanding. Stamping it is what removes the row from the drain
ProcessAttempts	Incremented on every attempt, success or failure
ProcessStatus	Diagnostic text only — the outcome, Created ethClientWIPUpdate n, or a truncated error

Indexes: UX_ethClientWIPQueue_IdempotencyKey; IX_ethClientWIPQueue_Pending filtered on ProcessedUTC IS NULL; IX_ethClientWIPQueue_JobId. Rows are never deleted — a processed row is the record that the occurrence was handled, and its key keeps blocking redeliveries.

3.8 ethClientWIPUpdate — migrations 0008_20260914_ClientWIPUpdate, 0009_20260914_ClientWIPUpdateSentUTC

Column	Notes
ClientWIPUpdateID	bigint identity PK
JobId, QueueID	QueueID nullable (room for a manual "generate now" path — Not built)
TriggerID, PrimeStatusId, RuleID, TemplateID	Provenance; TemplateID is the version used
ClientContactId, ClientName	Captured at generation — who was told, not today's Prime name
BodyText, BodyHtml	Rendered
Status	Defaults 'Approved'. Room for a future Pending/Sent without schema change
Status values	Approved, or Cancelled (migration 0049: every update unsent at AB#1954, so only updates generated from then on are delivered)
PrimeNotificationQueueID	Migration 0049. The ethPrimeNotificationQueue row its note was queued as — whether Prime accepted it is on that row
SentUTC	Null until delivered (queued as a Prime note). Renamed from SentAt (0009) so a sender cannot copy ethWIPJobHistory.ClientNoteSentAt's local-time GETDATE() into a column the page treats as UTC
SendStatus	For the sender to record a failure reason
CreatedUTC, CreatedBy
IsResend, ResendOfClientWIPUpdateID	Migration 0039. A resend is a copy of the real update it points at (§7.5). IsResend = 0 rows are the real updates — "when did the last real update go out?"

Indexes: UX_ethClientWIPUpdate_QueueID (filtered, non-null) — one occurrence, at most one update; IX_ethClientWIPUpdate_Unsent on (Status, CreatedUTC) where SentUTC IS NULL; IX_ethClientWIPUpdate_JobId; IX_ethClientWIPUpdate_ResendOf (filtered, non-null).

Why a new table rather than ethWIPJobHistory.ClientNoteText: that column hangs off a customer WIP row, holds one note, and records no rule, template or client. A status-change trigger has no customer WIP row to attach to, a job legitimately produces several client updates, and MaxSendCount needs something to count. (The proposal's plan to "feed the existing client-note pipeline" was dropped for this reason.) ethWIPJobHistory is untouched.

3.9 ethClientWIPEscalation — migration 0039_20261015_ClientWIPStuckEscalation

One row per escalated real update: JobId, ClientWIPUpdateID (unique — a real update escalates once), RuleID, EscalationRoleID, PrimeStatusId, ToEmail (;-separated, NULL when the role resolved to nobody), RecipientNames, Subject, BodyHtml, CreatedUTC, CreatedBy. A no-recipient row is still written so the gap shows on the page and the check does not retry it daily.

CancelledUTC (migration 0048_20261021_ClientWIPEscalationCancelled, UserStory1953): set on every escalation raised before escalation emails were turned on on Prod, so only escalations raised from then on read as pending or sent. A cancelled row still blocks re-escalation of its update.

3.10 ethClientWIPSetting — migrations 0040_20261015_ClientWIPStuckSettings, 0041_20261015_ClientWIPDeliverySwitches

Single row (SettingID = 1): EscalationsEnabledFromUTC, UpdatedUTC, UpdatedBy. The switch is the time it was turned on; NULL = off. Read/written by spEthClientWIPSettingGet / spEthClientWIPSettingSet. The global ClientUpdatesEnabledFromUTC was dropped in 0049 — client updates are switched per client (§3.11).

The switches gate delivery, never generation. Updates, resends and escalations are always written and always shown on the Admin portal.

• EscalationsEnabledFromUTC — whether an escalation's email is queued. Decided once, by spEthClientWIPEscalationCreate, when the escalation is recorded, and stored as ethClientWIPEscalation.EmailQueued; an escalation raised while it was off is never emailed later.

3.11 ethClientWIPDeliveryClient — migration 0049_20261021_ClientWIPDeliveryPerClient (AB#1954)

One row per client whose updates can be posted to its portal: ClientContactId (PK), EnabledFromUTC (NULL = off), UpdatedUTC, UpdatedBy. Only updates (real and resent) with CreatedUTC >= EnabledFromUTC are delivered, so turning a client on never releases a backlog. Read/written by spEthClientWIPDeliveryClientList / spEthClientWIPDeliveryClientSet.

Rows are added by migration, never in the UI: each client also needs its portal sync mapped in PrimeNotificationService.SetInstructingClientSync (Allianz → syncToEndata, A&G → syncToNexus). Seeded with Allianz Australia Insurance Limited (AB9EB03D-…), off; migration 0051_20261022_ClientWIPDeliveryAutoGeneral adds Auto & General Holdings (405B08A4-…), off, and cancels its unsent updates (AB#1981). "Sedgwick - Allianz" is a separate client and is not included.

---

4. Rule resolution (spEthClientWIPMatchRule)

Read-only; returns exactly one row whose Outcome is the answer. Checks, in order:

1. Job exists in primeJobs — else NoJob.
2. Status = the queue row's PrimeStatusId; falls back to the job's current StatusId only when null.
3. Client — primeJobs.ClientId → primeContacts. No client or no contact name → NoClient. No new client table: this is the same join spPrimeGetWIPUpdatesClients calls InstructingClientName.
4. Inactive client — primeContacts.status = 'Inactive' → ClientInactive. Enforced here, at generation time, not only in the UI. The rule is left enabled and untouched: inactivity is a client attribute, so reactivating the contact in Prime resumes updates with no re-editing.
5. Rule match — IsEnabled = 1, TriggerID matches, PrimeStatusId matches, within EffectiveFrom/EffectiveTo, and the client is on the explicit list or the rule is all-clients. Ordered: explicit list beats all-clients (a rule written for this insurer is a more specific statement of intent), then Priority ascending, then oldest CreatedAt — so a tie always resolves the same way instead of alternating wordings. None → NoMatchingRule.
6. Template — rule has none → NoTemplate.
7. Send cap — count of Outcome = 'Sent' log rows for (rule, job) ≥ MaxSendCount → MaxSendCountReached.
8. Otherwise Match, returning the template's BodyText/BodyHtml unrendered.

Overlapping rules are prevented at save time (§8.2), so the ordering in step 5 is a safety net, not the primary mechanism.

---

5. Templates and tokens

5.1 Placeholders

{{PascalCase}}, matching the existing customer WIP email templates ({{CustomerFirstName}}, {{SupervisorPhone}}). The renderer (WebAPI/Services/ClientWIPTemplateRenderer.cs) matches {{ Name }} with letters, digits and underscore only, so stray braces in prose are never mistaken for a token.

An unresolved token blocks the update. A token that is not in the catalogue, or that resolves to blank for this job, counts as unresolved: a raw {{Foo}} reaching an insurer is worse than no update, and so is "we attended site on ." The engine logs UnresolvedToken with the token names and the half-rendered body (braces left in so the hole is obvious) and produces nothing — after retrying for an hour, since most blanks are data that has not arrived yet (§7.3).

{{TODO_...}} placeholders (left by the seed where the workbook said only [date]) are deliberately absent from the catalogue so they fail validation; the settings page flags such templates as unfinished.

5.2 The one resolver — dbo.fnClientWIPJobTokenValues

DbMigrations/Scripts/Programmable/Functions/fnClientWIPJobTokenValues.sql. An inline TVF returning one row per token for a job: TokenName, Value (rendered text), DateValue (raw UTC datetime for date tokens, else null). This is the only definition of what a token means. The engine reads it through spEthClientWIPResolveJobTokens; the rule preview reads it through spEthClientWIPRulePreview. Preview and generation therefore cannot disagree. (An inline TVF, not a proc, so it composes into both callers and inlines into their plans; the wrapper proc exists because CommonDataAccess is stored-procedures-only.)

Dates. Every date token's Value is dd/mm/yyyy in the job site's timezone, converted once inside the function with AT TIME ZONE from primeJobs.State (Broken Hill 28xx postcodes keep central time; blank/unknown state falls back to eastern). Rendering straight off UTC reported evening appointments in AEDT states as the next day. DateValue stays UTC for arithmetic; anything displaying it must convert it itself.

Missing values are NULL, never a placeholder date. The 01-01-2100 sentinel used by the old prototype (§10) exists only because the A&G portal demands a date in every field — it is not a general "missing date" value and must not be reused here.

5.3 Token catalogue

Token	Source type	Resolves from
CaseManagerName, CaseManagerPhone	PrimeField	primeJobs.CaseManagerId → primeUsers → contact (work, then mobile)
SupervisorName, SupervisorPhone	PrimeField	primeJobs.SupervisorId → primeUsers → contact mobile
EstimatorName	PrimeField	primeJobs.EstimatorId
CustomerFirstName	PrimeField	Primary contact firstName, falling back to name (Company-type contacts have no first name)
InstructingClientName	PrimeField	primeJobs.ClientId → primeContacts.name
JobNumber, ClientReference, SiteAddress	PrimeField	primeJobs
StartDate, CompletionDate	PrimeField	primeJobs.StartDate / EndDate
ScheduleLink	PrimeField	Prime job custom field "Current Schedule Sharelink" (EAA5E8DB-39E4-47D4-BB7D-3ADC0F76363A), newest non-blank
LastAttendanceDate	PrimeField	dbo.fnPrimeJobLastAttendance: appointments that have started (AB#2031 — staff mark Estimator Attended while the appointment is still on the calendar), dated by [end] capped at now, excluding cancelled, ZZ* types and UNAVAILABLE - * roster/leave blocks. [end] is deliberate — a multi-day attendance ends on its last day; one still in progress reads as today
NextAttendanceDate	PrimeField	dbo.fnPrimeJobNextAttendance: MIN([start]) of not-yet-started appointments, same exclusions (migrations 0005/0006)
OwnerApprovalRequestedDate	Milestone	Prime milestone 2.1 (F3720B29-3668-4873-9500-DE0DCE8287ED), first value over current value — the quote-stage owner approval / excess invoice. value is text; parsed with TRY_CONVERT
QuoteAuthorisedDate	StatusHistory	First time the job reached Quote Authorised% (dbo.fnPrimeJobStatusFirstReached)
SupervisorAppointedDate	StatusHistory	First Supervisor Appointed%
OwnerSignOffRequestedDate	StatusHistory	First Works Completed - Awaiting Sign Off — the works sign-off request, a different and much later event than OwnerApprovalRequestedDate
LastBuilderUpdateDate, LastBuilderUpdateSubject	WIPEntry	Latest ethWIPJobHistory row for the job
LastBuilderUpdateBody	WIPEntry	That row's EmailHTML — raw HTML, see below
LastBuilderContactName	WIPEntry	That row's ContactNamesText, then primary contact firstName, then name (migration 0004)

Status-history tokens take the first time a status was reached: the token names the event, and the date must not move if the job re-enters the status. A "most recent" variant would be a new token, not a change to the function. The prefix patterns deliberately pick up both variants of each paired status (and any new variant Prime adds).

LastBuilderUpdateBody is returned untouched by SQL. The engine flattens it per body type with CommonPrimeAPI/Services/ClientWIPTokenFlattener.cs (via PrimeNoteHtmlConverter) so a whole email is not dropped into an update. A second HTML-valued token must be added to the flattener's list.

---

6. Triggers

Trigger	Raised by	PrimeStatusId	Default idempotency key
StatusChange (1)	PrimeWebhookQueueService.QueueClientWIPStatusChangeAsync, after the job is synced from a Prime webhook, no sooner than 30s after Prime sent it - one row per change in Prime's status history since the webhook (§12). SourceRef = ethPrimeWebhookLog:{id}	The history row's NewStatusId (the payload's jobStatusId is not a status id). Fallback when Prime has no change: the status the job fetch returned	StatusChange|{JobId}|{NewStatusId}|{history row Id}, built by the handler. Fallback: built by the proc from the latest primeStatusHistories_Job row into the status (nohistory if none)
NewWIPUpdate (2)	spEthWIPJobEmailMarkSent and spEthWIPJobEmailMarkSentByWIPID, in SQL, when the n8n flow "WIP Updates - Process Email Queue" marks a customer WIP email sent. SourceRef = WIPJobHistoryID	Null (job's current status is used at evaluation)	NewWIPUpdate|{WIPJobHistoryID}
any other active trigger	POST api/clientwipupdate/evaluate with TriggerID	As supplied / current	Trigger{n}|after the job is synced from a Prime webhook, no sooner than 30s after Prime sent it - one row per change in Prime's status history since the webhook (§12). SourceRef = ethPrimeWebhookLog:{id}`

Why these hooks:

• Status change in C#: that is where the codebase hands downstream pipelines their work, and the status is only correct after the sync.
• New WIP update in the proc: the email is sent by n8n, not the solution, so there is no C# to hook — and a future sender calling the same proc can't forget. On sent rather than approved: only then does the update exist for the customer, and only then are the WIP date tokens true.

Both are fire-and-forget: a failed enqueue is logged (C#) or swallowed (proc) rather than failing the webhook event or the send bookkeeping. A missed enqueue is recoverable through evaluate; a replayed Prime event is not worth the risk.

MilestoneReached and StuckCheck are seeded lookups only. Stuck jobs are handled by the daily check (§7.5), not this trigger.

---

7. The queue and the drain

7.1 Enqueue (spEthClientWIPQueueEnqueue)

Validates the trigger is active, fills PrimeStatusId for a status change, builds the key when the caller supplies none (§6), and inserts. Look-first handles the common duplicate; the unique index is the real guarantee, and a lost race (error 2601/2627) returns the winner's row. Returns QueueID, Queued (0 = already present — routine, logged at Debug), IdempotencyKey.

The key names the occurrence, not the call: a job returning to a status it held before is a new occurrence (new history row) and queues again, while a Prime redelivery, an n8n retry or the second contact on one WIP update collapse onto the first row. The unique index is not filtered on ProcessedUTC — a processed row must keep blocking its key, or a redelivery a week later would generate a second update.

When a row is actually created, ClientWIPQueueService nudges the n8n flow through IWebhookHandler.ClientWIPProcessQueue() (WebhookSettings:ClientWIPProcessQueue, path GUID efec4fe1-d4ef-4c27-be45-d9b90aa0ce27) so the update is generated in seconds rather than at the next schedule. A failed nudge is logged, not thrown — the row is already committed.

7.2 Drain (POST api/clientwipupdate/process)

n8n flow "Client WIP Updates - Process Queue" (active): 5-minute schedule plus the webhook above, guarded by the n8nClientWIPProcessQueue SQL task lock (spEthTaskLockIsLocked / Insert / Clear), calling the Prod WebAPI with BasicAuth. In WebAPI the endpoint is also guarded by ControllerSemphoreService (ClientWIPUpdateController:ProcessQueue) and returns 409 if a drain is already running.

ClientWIPQueueService.ProcessQueueAsync:

• spEthClientWIPQueueGetPending — outstanding rows oldest first (so two status changes before a drain are told in order), ProcessAttempts < 5, NextAttemptUTC null or past (migration 0054, a row deferred to retry blank tokens — §7.3), max 250 rows. Rows are not claimed; the semaphore plus per-occurrence idempotency is the protection.
• Per row, EvaluateAsync (§7.3). A decided outcome — including "no update" — closes the row with spEthClientWIPQueueSetProcessed and the outcome in ProcessStatus. An exception calls spEthClientWIPQueueIncrementAttempt (no ProcessedUTC), so a transient failure is retried; after 5 attempts the row stays outstanding and visible for someone to look at.
• Then queues deliverable updates to Prime (§7.6).
• Returns { evaluated, sent, skipped, deferred, failed, delivered } for n8n to log (sent = updates created).

7.3 Evaluation (EvaluateAsync)

1. spEthClientWIPMatchRule (§4). Not Match → spEthClientWIPLogEvaluation with the outcome and reason; row closed.

2. spEthClientWIPResolveJobTokens → flatten (§5.3) → render BodyText and BodyHtml. Any unresolved token in either → log UnresolvedToken with the rendered text; row closed. This is the only outcome the matcher cannot decide, because it needs the rendered body.

   Retried first (AB#2031). Most blanks are timing, not missing data — on dev 92 of 138 blocked Estimator Booked updates had a booked appointment in Prime that had not synced yet. So while the row is under UnresolvedRetryWindow (60 min) old and the job is still at the captured status, it is deferred instead: spEthClientWIPQueueDefer sets NextAttemptUTC 10 minutes out and the reason in ProcessStatus ("… retrying at HH:mm UTC"), without counting an attempt, and nothing is logged. Once the window passes — or the job has moved to another status, because an update for a status the job has left must not go out late, after the next one — the block is logged as above, saying which. The trace shows a deferred row as Waiting for token data.

3. spEthClientWIPUpdateCreate — inserts the ethClientWIPUpdate row and its Sent log row in one transaction. They are one fact: lose the log row and MaxSendCount never trips; lose the update and the cap is charged for nothing. If the queue row already produced an update (replay, or a drain that committed but failed before closing the row), returns Created = 0, writes no second log row, and the queue row closes as AlreadyCreated.

Idempotence therefore holds at both levels: UX_ethClientWIPQueue_IdempotencyKey (one row per occurrence) and UX_ethClientWIPUpdate_QueueID (one update per row).

7.4 POST api/clientwipupdate/evaluate

Body ClientWIPEvaluateRequestModel: JobId (required), Trigger code (StatusChange / NewWIPUpdate) or TriggerID, optional CustomComment, IdempotencyKey, QueuedBy. Only enqueues — it does not evaluate inline (the proposal's "evaluate one job now" is replaced by store-then-process for every caller). Returns { queueId, queued, idempotencyKey }; queued: false means already queued, not an error. Returns 500 if the insert fails, deliberately, since the caller's retry is the only thing that would save the update. Unknown trigger codes are a 400; a trigger seeded later is reachable by TriggerID without a deployment.

The in-solution triggers do not use this route. No caller in the solution or n8n uses it today.

All three routes (evaluate, process, stuckcheck) sit behind WebAPI's BasicAuthMiddleware — no new auth path.

7.5 Stuck jobs — POST api/clientwipupdate/stuckcheck (UserStory1859)

n8n flow "Client WIP Updates - Stuck Check", weekdays 09:00 Melbourne; semaphore-guarded (409 if running). ClientWIPQueueService.RunStuckCheckAsync returns { resent, escalated, emailed, noRecipient, failed }. Both halves always run; the switches (§3.10) only decide delivery.

dbo.fnClientWIPStuckCandidates picks, per job, the newest real update (IsResend = 0) where the job is still in that update's status, its rule is still enabled, and it has not been escalated. A new real update supersedes the old one, so the resend count and the stuck timer reset; a status change with no rule behind it drops the job out (the old wording is stale).

1. Resend (spEthClientWIPStuckResendDue, set-based): the rule has MaxSendCount, the original plus its resends is still under it, and nothing for the job went out in the last 7 days → a copy of the real update's rendered body, IsResend = 1, Approved, unsent — delivered like any other update.
2. Escalate (spEthClientWIPStuckEscalationsDue → spEthClientWIPEscalationCreate): the rule has StuckAfterDays and EscalationRoleID, and the real update is at least that many days old. Resends run first, so both can happen on the same day. Recipients: Case Manager / Estimator / Supervisor = that active Prime user on the job — when the job has no supervisor, or it is inactive or has no email, Supervisor falls back to every active Construction Manager (primeUsers.roles, a comma list) whose fnEthStateFromTimezone(timezone) is the job's state, and when there is none of those either, to laura.westelynck@ethosbuilding.com.au; Restoration = Restoration@ethosbuilding.com.au; State Manager = every active primeUsers.roles = 'State manager' whose fnEthStateFromTimezone(timezone) is the job's state. The create proc writes the escalation row and, when the escalations switch is on, one ethEmailMessage per recipient, in one transaction (sent by the n8n email flow). When the email is queued, one Internal note listing every recipient is queued on the Prime job through ethPrimeNotificationQueue (PrimeMessageNotes-Spec). The escalation stops further resends.

The email is CommonDataAccess/Data/Email/EmailTemplates/ClientWIPEscalationEmail.html (PDP email styling); subject Ethos Job {JobNumber} - Action required - stuck at "{CurrentStatus}" for {DaysInStatus} days. The body states the days in status against the threshold, that no further updates will go to the client, and asks for the job to be progressed or the Case Managers updated; the job number links to the Prime job. DaysInStatus counts from the latest primeStatusHistories_Job row into the current status, falling back to the real update's date.

7.6 Delivery — spEthClientWIPUpdateQueueDelivery (AB#1954)

Called at the end of every drain (§7.2) by ClientWIPQueueService.DeliverUpdatesAsync, so resends from the stuck check go out on the next drain; a failure is logged and retried next drain. In one transaction it queues each deliverable update in ethPrimeNotificationQueue as a Client Contact note (BodyText, SyncToClientId = the update's client) and stamps the update's SentUTC and PrimeNotificationQueueID.

Deliverable: Status = 'Approved', unsent, non-blank BodyText, the client is switched on in ethClientWIPDeliveryClient and the update was generated at or after its EnabledFromUTC — and a resend only if the update it repeats was delivered, so turning a client on cannot release a resend for every job in its cancelled backlog.

The Prime notification queue processor posts the note and applies PrimeNotificationService.SetInstructingClientSync(SyncToClientId) — Allianz → syncToEndata = true, A&G → syncToNexus = true. A note Prime rejects is retried and, after 10 attempts, abandoned on its queue row (PrimeMessageNotes-Spec); the update still reads Sent.

---

8. AdminPortal

Both pages: [Authorize(Roles = "WIPManager,Admin")], and the nav link sits in <AuthorizeView Roles="WIPManager,Admin">. No named policy — a policy called WIPManager would hide that Admin also gets in. All data access through IClientWIPData / ClientWIPData (CommonDataAccess/Data/ClientWIP/), stored procedures only.

8.1 Client WIP Updates page — /clientwipupdates[/{JobId}]

Components/Pages/ClientWIPUpdates.razor, nav "WIP Updates › Client WIP Updates" beside "Customer WIP Updates". Lists what the engine generated (spEthClientWIPUpdateList, newest first, capped at 500) with columns Prime job (link), Client (as captured), Address, Generated, Send status and Sent. The send-status chip reads Awaiting send, Sent (SentUTC set), Cancelled (Status = 'Cancelled', reason in SendStatus) or Failed (unsent with a SendStatus). A sent update whose Prime note had a failed third-party sync (AB#2037) shows an amber Sent - portal sync failed chip; the row preview adds the reason and Prime's raw response, for a Prime support ticket (SyncWarning/SyncResponse on the update's ethPrimeNotificationQueue row, migration 0056). Job-number search and toggle filters per send state. The search is passed to both list procs as @JobNumber (a contains match applied before the 500 cap) and re-queries once typing pauses, so it finds every update for the job, not just those among the newest 500; the send-state toggles filter what the search returned, in memory. Row click previews the HTML body, with trigger / status / template provenance. Today every row is Awaiting send (§11). Resends carry a Resend chip beside Generated. For WIPManager and Admin the preview has a trace icon that opens the job's trace (§12) on the event that produced the update.

The page has two tabs, Updates (real updates and resends together) and Escalations, on one bar with the send-state toggles: Awaiting send / Failed / Sent on Updates, Awaiting send / Failed / Emailed / No recipient / Suppressed / Cancelled on Escalations. The job number search applies to both. An escalation's send state, in order: CancelledUTC set = Cancelled; no ToEmail = No recipient; nothing queued (switch off when raised) = Suppressed; then from the ethEmailMessage rows it queued (ethClientWIPEscalationEmail, migration 0042): any failed or abandoned recipient = Failed, all sent = Emailed, otherwise Awaiting send. The Escalations tab is a grid (spEthClientWIPEscalationList): job, the status it was stuck at, role, recipients (or No recipient), when escalated, send status and when sent. Shares the job number search; row click shows the email as sent.

The old prototype "Clients" tab has been removed from the Customer WIP Updates page (WIPUpdateQueue.razor, still at wipupdatequeue). The proposal's plan to move that grid here was superseded: this page reads ethClientWIPUpdate, not spPrimeGetWIPUpdatesClients.

8.2 Settings — /settings/client-wip (embedded) and /clientwipsettings[/{tab}]

Components/Pages/ClientWIPSettings.razor, shown as the Client WIP section of the Settings page (audience WIPManagerOrAdmin in AdminPortal/Models/SettingsSection.cs) and also routable on its own. Four tabs:

Tab	What it does	Procs
Rules	Delivery switches at the top — one "post client updates to {client}'s portal" per ethClientWIPDeliveryClient row (§3.11), and send escalation emails (§3.10) — each showing when it was turned on. Grid with search and an "enabled only" switch (default on); inline Enabled toggle; scope column "All clients" / the client's name when exactly one / "n clients"; warning icon for inactive clients, error styling when every client is inactive or a scoped rule has none selected ("looks configured, can never fire"); New / Edit / Delete	spEthClientWIPRuleList, RuleSetEnabled, RuleDelete
Templates	List with version, rule usage counts, and an "unfinished" flag for {{TODO_...}} bodies	spEthClientWIPTemplateList
Tokens	The catalogue with source type display name, description and SourceRef	spEthClientWIPTokenList
Lookups	Triggers, roles and client tag types (read-only)	spEthClientWIPLookups

Dialogs (Components/Dialogs/):

• ClientWIPRuleEdit — status via searchable autocomplete (spEthClientWIPStatusList, flags statuses that already have a rule on the chosen trigger), trigger, scope, template (warns on an unfinished one), MaxSendCount, StuckAfterDays, escalation role, priority, effective dates, notes. Save via spEthClientWIPRuleUpsert, which enforces the scope pairing and refuses an enabled rule whose scope overlaps another enabled rule on the same status + trigger (the toggle refuses it too), with a message shown as-is. Delete is refused for a rule that has evaluation history — disable it instead.

• ClientWIPClientPicker — searches primeContacts narrowed to clients (spEthClientWIPClientSearch), filter by client-type tag, multi-select, "Select all shown". Inactive clients are excluded from new selection but shown, marked, when already on a rule; never auto-removed. The tag is not stored: choosing Insurer then Select-all saves explicit rows, so a client tagged Insurer later does not silently join the rule — the dialog says so.

• ClientWIPTemplateEdit — body text/HTML, authoring notes, token palette, sample-value preview, version history (spEthClientWIPTemplateVersions). Every {{Token}} is validated against the catalogue on save; an unknown token blocks the save because it would block every send. spEthClientWIPTemplateSave publishes a new version.

• ClientWIPRulePreview (from the rule editor, saved rules only) — tests a rule against a real job via spEthClientWIPRulePreview: each condition pass/fail with reason (enabled, status matches, client in scope, client active, effective dates, template, under cap with "n of m sent"), every token's value for that job, and which tokens the template uses that would not resolve. "Would send" only when everything passes. Nothing is written.

  Preview differs from the engine in small ways: it compares the rule to the job's current status, does not check the trigger or which of several rules would win, and checks unresolved tokens against BodyText only.

---

9. Seed data

The baseline seed came from the "Client Updates" sheet of WIP Update Prime Status and Actions.xlsx: one template and one StatusChange rule per Prime status matched by name (unmatched or ambiguous names were reported, not guessed; near-duplicate workbook rows skipped). Workbook mapping: "New Update" → template body; "Update Type" and "Comments" → AuthoringNotes; the "stuck" column → MaxSendCount / StuckAfterDays / EscalationRoleID (a named individual in one row was mapped to a role). Placeholders were converted: [Case Manager] → {{CaseManagerName}}, [Direct line] → {{CaseManagerPhone}}, [appointment date] → {{LastAttendanceDate}}, [date] → {{TODO_Date}}.

A rule was seeded enabled only when the workbook said send = Yes, approved wording existed, and no TODO placeholder remained; otherwise disabled with the reason in Notes. Re-seeding never re-enables a rule a WIPManager disabled nor overwrites edited wording (Version > 1).

Known follow-ups (configuration, not code):

• Several templates announce a future attendance using {{LastAttendanceDate}} (e.g. Estimator Booked), which by design only reports past attendances — they should use {{NextAttendanceDate}}.
• "Supervisor assigned - Await Site Establishment" still carries {{TODO_Date}} and stays disabled.
• No rules exist yet on NewWIPUpdate, so that trigger always evaluates to NoMatchingRule.
• Client tagging and the inactivation of ~61 contacts in Prime is a separate data task; the picker's tag filter is only as good as Prime's tags.

---

10. The prototype it replaces — deleted (AB#1951)

POST api/wipupdate/process/primeclientnotes and its call tree in WIPUpdateService (CreatePrimeClientNoteforWIPAutoGeneral and friends, the WIPUpdateClientAutoGeneral.txt template, WIPUpdateData.GetClientNoteQueue / MarkClientNoteSent / GetClientNotes) were deleted. Nothing called the route, and its Prime post was commented out. History is in git.

• Its 01-01-2100 default for a missing date was A&G-specific (their portal demands a date in every field). Do not reuse it; the rule engine renders a missing value as unresolved and blocks.
• The procs spEthWIPJobClientNoteQueue, spEthWIPJobClientNoteMarkSent and spPrimeGetWIPUpdatesClients still exist in the database with no caller.

---

11. Not built

Item (from the proposal)	State
MilestoneReached trigger	Not built (lookup row only)
Evaluating inline for a settings-page "run now"	Not built; evaluate only enqueues and nothing in AdminPortal calls it
Custom comments on updates	Dropped (columns removed); CustomComment is still carried on the queue but ignored
Subject on templates	Dropped — updates are notes
"Which rule applies to client X?" lookup and "clients with no matching rule" view	Not built (spEthClientWIPRuleList accepts @ClientContactId, but the page never passes it)
Rule-grid filter by client / status, "rules referencing inactive clients" filter	Not built; free-text search and the warning icons cover part of it
Read-only evaluation-log tab per job	Superseded by the trace dialog (§12)
Template preview against a real job	Built on the rule preview; the template editor previews sample values only
{{contentHtml}} wrapper-slot rendering into the existing HTML email shell	Not built — BodyHtml is stored as rendered, with no shell

---

12. Tracing a disputed update (AB#2031)

Dialog. Components/Dialogs/ClientWIPTrace.razor, opened from the trace icon on the Client Update Preview (WIPManager / Admin only — the page checks the role before passing the icon's action to HTMLDisplayDialog). Left: the job's events, newest first — every Prime status change (primeStatusHistories_Job), every trigger with no status change behind it (a customer WIP update sent), and every weekly resend — each with its outcome (Sent, NoMatchingRule, …, or Not queued) and a warning icon when any step is a warning. Right: the selected event's steps. Opens on the event that produced the previewed update. All times Melbourne, to the second. Copy trace puts the selected trace on the clipboard as text for a ticket.

spEthClientWIPJobTrace @JobId — read-only, two result sets (events; steps with Seq). Steps: Prime status change → webhook (or customer WIP update) → queue row (captured status, key) → evaluation outcome → rule matched → template version → token values → update → delivery (Prime note queue row, posted / abandoned) → check: was the job still in the update's status when it was delivered (or generated)? Times stay in AtUTC, never in the text, so all of them convert.

A queue row is linked to the status change its key names (exact, since AB#2031 keys every row on its Prime status-history row). Rows queued before that link to the change nearest their webhook (±10 minutes; Prime sends within seconds) — their key's history id was the latest change into the captured status, often an earlier visit to it, and two thirds were nohistory. Warnings:

• Captured the wrong status — the change says X but the job read back from Prime was at Y (often its status before the change). The rules answered for Y.
• Not queued — a change with no queue row, with the webhooks received for it; a webhook that queued nothing collided with an existing key and was dropped as a duplicate.
• Job had moved on before the update was delivered; rule edited after the evaluation; rule now targets a different status; unresolved tokens; a note Prime rejected.

Evaluation snapshot (ethClientWIPEvaluationLog.TraceJson, written for every outcome by WebAPI/Services/ClientWIPEvaluationSnapshot.cs): the queue row and captured status, the match — rule, template, status, the job's status at evaluation, matched on client list / all clients, priority, candidate rule count, send count of cap (returned by spEthClientWIPMatchRule since AB#2031) — and the value of every token the template used. Rules are edited in place, so this is the only record of the settings that applied; the trace shows it as "at the time", and falls back to today's settings, saying so, for older rows. Token values stay out of the application log (names and phone numbers); the log carries the token names.

Logging. ClientWIPQueueService logs, per queue row: the evaluation start (source, captured status, attempt), the match (rule, template, scope, priority, candidates, send count), a warning when the job is no longer at the captured status, the tokens rendered, and the result. A duplicate enqueue is logged at Information with its source — it can drop a real status change.

The cause of wrong updates, and the fix (dev data, 2026-10-24). The status-change trigger reads the job's status from Prime after the webhook; the payload carries no status. 17% of status-change triggers captured a status other than the change that fired them, and about 400 of those produced an update. Most were the status before the change: Prime's API serves the old job for a while after a change — 16% of fetches within 5s of it were stale, 15% at 6–15s, under 1% after 15s — and concurrent syncs were not the cause (stale reads had fewer neighbouring webhooks than correct ones). A status captured wrongly also takes the key of the real change into that status, which is then dropped as a duplicate.

Fixed in PrimeWebhookQueueService (AB#2031):

• Settle. A status change is not read until StatusSettleDelay (30s) after Prime sent the webhook. It waits only for what is left, so a batch waits at most 30s and a retried event not at all. The triage invite, which reads the same synced status, gets the same fix.
• One trigger per status change. The job holds only its latest status, so the trigger is raised from Prime's status history instead: IPrimeJobService.GetJobStatusHistory reads status-histories for the job from 2 minutes before the webhook was received, saves each row (spPrimeUpsertStatusHistory_Job, as the n8n sync does) and the handler queues one trigger per row, oldest first, with that row's status and the key StatusChange|{JobId}|{NewStatusId}|{history id}. A job moved twice inside the settle window gets both changes evaluated, in order — both happened. The two webhooks each see both rows; the keys queue each change once.
• Fallback. No change in the window, or the history call fails: one trigger for the status the job fetch returned (logged as a warning), keyed by the enqueue proc as before.

---

13. Where things live

Piece	Location
Queue / update tables, token rows	DbMigrations/Scripts/Migrations/0004–0009
Token resolver	DbMigrations/Scripts/Programmable/Functions/fnClientWIPJobTokenValues.sql, 01_fnPrimeJobLastAttendance.sql, 01_fnPrimeJobNextAttendance.sql (fnPrimeJobStatusFirstReached is live baseline, not yet a file in DbMigrations)
Engine procs	DbMigrations/Scripts/Programmable/Procedures/spEthClientWIP{QueueEnqueue,QueueGetPending,QueueSetProcessed,QueueIncrementAttempt,QueueDefer,MatchRule,ResolveJobTokens,UpdateCreate,LogEvaluation,UpdateList,RulePreview,JobTrace}.sql; stuck jobs Functions/fnClientWIPStuckCandidates.sql and spEthClientWIP{StuckResendDue,StuckEscalationsDue,EscalationCreate,EscalationList,SettingGet,SettingSet}.sql; delivery spEthClientWIP{UpdateQueueDelivery,DeliveryClientList,DeliveryClientSet}.sql; enqueue hook in spEthWIPJobEmailMarkSent{,ByWIPID}.sql
Rule tables, seed and settings procs (spEthClientWIPRule*, Template*, Token*, ClientSearch, StatusList, Lookups, RuleClientsGet/Set)	Live baseline — not yet captured as files in DbMigrations. Changes need a new Programmable/ file (read the live body first)
Data access	CommonDataAccess/Data/ClientWIP/ClientWIPData.cs, Interfaces/IClientWIPData.cs, Models/ClientWIP/
WebAPI	Controllers/ClientWIPUpdateController.cs, Services/ClientWIPQueueService.cs, Services/ClientWIPTemplateRenderer.cs, Services/ClientWIPEvaluationSnapshot.cs; tests in WebAPI.Tests/ClientWIPTraceTests.cs, trigger in Services/PrimeWebhookQueueService.cs
Flattener	CommonPrimeAPI/Services/ClientWIPTokenFlattener.cs
AdminPortal	Components/Pages/ClientWIPUpdates.razor, ClientWIPSettings.razor; Components/Dialogs/ClientWIP*.razor (trace: ClientWIPTrace.razor)
n8n	"Client WIP Updates - Process Queue" (webhook efec4fe1-d4ef-4c27-be45-d9b90aa0ce27); "Client WIP Updates - Stuck Check" (schedule only)