Appointment Instance Reconcile Queue — Spec

UserStory1132. Pull Prime-side appointment status changes back onto ethAppointmentInstance, so our recurring-appointment rows stop diverging from Prime when someone changes an appointment directly in Prime.

Status: built and live. The log table, reconcile proc and CancelFuture guard are all in the live schema (checked 2026-09-30). Moved to docs/specs/ on 2026-09-30; the legacy SQL files and n8n export it listed were removed from docs/. Two deliberate deviations from the spec as first written are marked [CHANGED] below.

---

1. The problem

ethAppointmentInstance.Status is written by exactly one path: spEthAppointmentInstanceSyncResult, called from AppointmentController when we push a change to Prime. There is no inbound path.

So when a user opens Prime and sets a recurring appointment to Cancelled:

primeAppointments.appointmentStatus	Cancelled — correct, the Prime sync mirrors it
optimoRouteOrder	order deleted — correct, spOptimoRouteGetAppointmentQueue picks up the status
ethAppointmentInstance.Status	still Synced — wrong, permanently

Consequences, in order of how much they bite:

1. Any reader that trusts Status is wrong. The instance-count badge on the definitions list was the first one found, and is patched at the reader (spEthAppointmentInstanceCounts reads the status through Prime with COALESCE). Every future reader has to remember to do the same, or repeat the bug.
2. spEthAppointmentInstancesCancelFuture skips these rows. It excludes Status IN ('Cancelled','CancelPending') — a row still reading Synced is therefore treated as live, and deactivating the definition sends a pointless cancel to Prime for an appointment already cancelled.
3. Nobody can tell "cancelled in Prime" from "cancelled here". No timestamp, no actor, no history.

Not a consequence: the instance is not regenerated. spEthAppointmentInstancesGenerate dedupes on OccurrenceKey via NOT EXISTS regardless of status, so writing a terminal status onto a row is safe and will not cause a duplicate on the next generation pass. This is what makes the whole approach cheap.

---

2. Approach: read our own mirror, not the Prime API

The reconcile reads primeAppointments, which the existing Prime sync already keeps current via spPrimeUpsertAppointments. It does not call the Prime API per instance.

Why:

• Set-based and effectively free. One UPDATE ... FROM join over an indexed key, versus one API round trip per instance. With recurring definitions generating instances out to a horizon, per-instance calls would be hundreds of requests per pass to discover that nothing changed.
• Staleness degrades gracefully. If the mirror lags, the reconcile is late, never wrong — it converges on the next pass. A stale read cannot produce an incorrect terminal status, only a delayed one.
• It is the same source the rest of the system already trusts. The OptimoRoute queue and every booking availability proc read primeAppointments, not the API. Reconciling from somewhere else would let our two views of Prime disagree.

Dependency to state plainly: this queue is only as fresh as the Prime appointment sync. If that sync stops, reconcile silently stops finding changes. Its monitoring is therefore load-bearing here, which it was not before.

Rejected: a per-instance GetAppointment fallback for instances the mirror has no row for. It converts a cheap set operation into an unbounded API loop to handle a case (§4, transition D) that should not mutate state anyway.

---

3. New stored procedure

spEthAppointmentInstancesReconcileFromPrime.

@AsOf     DATETIME2 = NULL   -- defaults to SYSUTCDATETIME()
@MaxRows  INT       = NULL   -- optional cap; NULL = no limit

Returns one row: InstancesReconciled, SetCancelled, SetCompleted, Settled — and a second result set of the affected AppointmentInstanceId / old status / new status, for the caller to log (§6).

Joins ethAppointmentInstance i to primeAppointments a on i.PrimeAppointmentId = a.[id]. Only rows where PrimeAppointmentId IS NOT NULL are in scope — an instance that never reached Prime has no Prime status to inherit.

Index

ethAppointmentInstance has no index on PrimeAppointmentId (see the data dictionary — the indexes are the PK, UX_RecurringApptInst_Def_OccurrenceKey, and IX_RecurringApptInst_Pending). The reconcile join drives off it, so add:

CREATE NONCLUSTERED INDEX IX_RecurringApptInst_PrimeAppointmentId
    ON dbo.ethAppointmentInstance (PrimeAppointmentId)
    INCLUDE (Status, StartAt, AppointmentDefinitionId)
    WHERE PrimeAppointmentId IS NOT NULL;

Filtered, because the never-synced rows are exactly the ones the join excludes.

---

4. Transitions

The governing rule: the outbound sync queue owns rows in a pending state; reconcile only touches settled rows. spEthAppointmentInstancesGetPendingSync claims Status IN ('Pending','Changed','CancelPending'). Reconcile must not overwrite those or the two queues will fight — a reconcile that stamps Cancelled over a Changed row would silently discard a user's edit before it was ever pushed.

	Our status	Prime status	→	Notes
A	Synced	Cancelled	Cancelled	The main case.
B	Synced	Completed	Completed	New status value — see below.
C	CancelPending	Cancelled	Cancelled	Settle: Prime already agrees, so the queued API call is redundant. Idempotent — worst case the sync runs first and reconcile finds nothing.
D	any	no mirror row	no change	Log only. Indistinguishable from mirror lag; see below.
E	Cancelled	live (Booked etc.)	no change	Log only. Do not resurrect; see below.
F	Pending / Changed	anything	no change	Sync owns it.

B — introducing Completed

Status is nvarchar(30) with no constraint, so the value fits, but Completed is not currently produced anywhere. Adding it means auditing every reader of Status for NOT IN ('Cancelled', ...)-style predicates that would silently misclassify it. Known readers: spEthAppointmentInstancesGetPendingSync (safe — positive IN list), spEthAppointmentInstancesCancelFuture (needs review — its Status NOT IN ('Cancelled','CancelPending') guard would mark a completed instance CancelPending and send a cancel to Prime for finished work), spEthAppointmentInstanceCounts (already reads status through Prime).

Cheaper alternative if that audit is unwelcome: land transition A only, and leave completion to be read through Prime as the counts proc now does. Recommendation: do A + C in phase 1, B in phase 2 once spEthAppointmentInstancesCancelFuture is fixed.

[CHANGED] B is included, and spEthAppointmentInstancesCancelFuture is fixed alongside it. The phase split was priced on the audit of Status readers being open-ended. It is not: the readers are exactly five procs (sql-ontology confirms nothing else references ethAppointmentInstance) plus one positive == "CancelPending" test in the controller. Of those, only spEthAppointmentInstancesCancelFuture needed changing. Deferring B to buy a one-line change was not worth carrying two phases.

spEthAppointmentInstancesCancelFuture adds 'Completed' to its NOT IN guard. Without it, deactivating an old recurring definition would set completed instances to CancelPending and push Prime a cancellation for work already done — rewriting the history of every completed appointment under that definition. Note the StartAt >= @AsOf gate does not make this unreachable on its own: an appointment can be marked Completed in Prime before its scheduled end, so a future-dated Completed row is a real state.

D — missing mirror row

A newly created appointment legitimately has no mirror row until the Prime sync catches up. Treating absence as deletion would mark healthy new instances as gone. Any future version that does act on this needs a grace guard — PrimeCreatedAt < DATEADD(HOUR, -24, @AsOf) and still absent — and even then a status of Orphaned is more honest than Cancelled. For phase 1: count and log, mutate nothing.

E — reversal

Cancelling then un-cancelling in Prime is legitimate, but resurrecting the instance would need to re-drive the OptimoRoute order and possibly a booking, which is a materially larger change than this queue. Log it so we find out whether it ever actually happens before building for it.

---

5. Endpoint

POST api/Appointment/Instances/Reconcile on the existing AppointmentController.

• Guard with ControllerSemphoreService under its own lock key, AppointmentController:ReconcileInstances, returning 409 Conflict when already running — matching Instances/Generate. It does not share the generate lock: different table columns, no overlap.
• Optional body { "maxRows": int? }, mirroring the existing batch-request shape.
• Returns the proc's counts. Log at Information with the counts, and at Warning for each transition D and E, since those are the cases nobody has yet decided how to handle.
• Behind BasicAuthMiddleware, like the rest of WebAPI.

6. Audit

[CHANGED] the audit table is included, not deferred. ethAppointmentInstanceReconcileLog (AppointmentInstanceId, PrimeAppointmentId, OldStatus, NewStatus, PrimeStatus, ReconciledAt). The instance row keeps no history — Status is overwritten in place — so without it an externally-driven change is invisible after the fact, and that matters more here than for our own writes because nobody in this system initiated it. It is one table and one INSERT off a table variable already being populated.

Deliberately its own table rather than a LastError overload: that column feeds the sync queue's retry reasoning, and a reconcile note is not an error — writing one there would make a healthy row look failed. No FK to ethAppointmentInstance, so the log can outlive a future purge and a constraint failure can never roll back a reconcile pass.

PrimeStatus is joined in for the log rather than captured in the OUTPUT clause, which can only reference inserted/deleted — not the joined mirror table.

The NLog entries remain the operational view: Information per applied change, Warning per reported-but-not-acted-on finding, and Debug for a quiet pass. Quiet is the normal case — the mirror only changes when someone edits an appointment in Prime — so logging those at Information would bury the real ones.

7. Schedule

An n8n flow — a standalone flow modelled on "Recurring Appointments - Sync to Prime": schedule trigger, spEthTaskLock* guard under its own lock name [redacted-blob], then the HTTP POST. No webhook trigger, so no webhook GUID is needed; node IDs are freshly generated GUIDs.

Cron 5,36 * * * *, not the every-15-minutes first proposed. The mirror this reads is refreshed by "Prime Sync Appointments by Date", which runs at :00 and :31 on weekdays (0,31 9-17 * * 1-5 plus two-hourly cover outside business hours). A blind 15-minute timer therefore spends half its passes reading a mirror that has not changed since the last one. Running at :05 and :36 puts each reconcile a few minutes behind a mirror refresh, which is the only ordering that makes it useful. Off-hours the mirror updates two-hourly and most passes find nothing — harmless, since a pass is one indexed join.

Kept as its own flow rather than appended to the Prime sync flow: chaining would give strictly better ordering, but it means editing an active production flow that has nothing to do with recurring appointments. If the two ever drift out of alignment, chaining is the fix.

No backfill needed beyond the first run: the reconcile is naturally a backfill, and will settle every diverged row that exists today on its first pass. Expect the first run's counts to be large and non-representative.

8. Interaction with what was just changed

• OptimoRoute needs nothing. spOptimoRouteGetAppointmentQueue already reads primeAppointments.appointmentStatus, so the order is deleted whether or not this queue exists. This queue fixes our instance rows, not the field schedule.
• Keep the COALESCE in spEthAppointmentInstanceCounts. Once reconcile runs, the counts proc would be correct reading i.Status alone — but only ever after a reconcile pass, and only while this queue keeps running. Leaving the COALESCE makes the counts correct in the gap and correct if the queue stalls. It costs one LEFT JOIN.
• Fix spEthAppointmentInstancesCancelFuture alongside transition B, per §4.

9. Testing

Not yet run — these are DB-shaped and need the SQL applied to a test database. The solution builds with 0 errors; there is no unit-testable surface here, since the logic is entirely in set-based SQL over a mirror table.

Set-based SQL against a mirror table, so the valuable tests are integration-shaped, not unit:

1. A — instance Synced, mirror Cancelled → becomes Cancelled; run twice, second pass reports 0.
2. F — instance Changed, mirror Cancelled → unchanged, and spEthAppointmentInstancesGetPendingSync still returns it.
3. C — instance CancelPending, mirror Cancelled → Cancelled, and it drops out of pending sync.
4. D — instance with a PrimeAppointmentId that has no mirror row → unchanged, counted.
5. Generation safety — reconcile a row to Cancelled, run spEthAppointmentInstancesGenerate, assert no duplicate for that OccurrenceKey. This is the assertion that protects the core assumption in §1.
6. Counts — after reconcile, spEthAppointmentInstanceCounts returns the same numbers it did before (it was already reading through Prime). A change here means one of the two is wrong.

10. Deliverables

All built and applied.

ethAppointmentInstanceReconcileLog	audit table
IX_RecurringApptInst_PrimeAppointmentId	supporting index
spEthAppointmentInstancesReconcileFromPrime	the proc
spEthAppointmentInstancesCancelFuture	'Completed' added to its guard — required, see §4
CommonDataAccess	AppointmentInstanceReconcileResultModel + ...DetailModel, IAppointmentData.ReconcileInstancesFromPrime
AppointmentController	POST api/Appointment/Instances/Reconcile
n8n reconcile flow	scheduled, cron 5,36 * * * *

Deploy order matters in one direction only: spEthAppointmentInstancesCancelFuture must be applied before or with the reconcile proc. If reconcile runs first and writes Completed, and someone then deactivates that definition, the old CancelFuture sends Prime a bogus cancellation. Reverse order is harmless — Completed simply never appears until reconcile runs.

The first pass is the backfill and will settle every diverged row that exists today. Expect its counts, its log rows, and its MissingInPrime warnings to be large and unrepresentative; the second pass onwards is the steady state.