CustomLinc TOL → Queue-it Availability JSON

Overview

Workflow name	CustomLinc TOL → Queue-it Availability JSON

Workflow IDT76MXW3QRGcoIBbo Active version ID3c51da4e-7cc1-4efc-bd6f-eaeff2974593 (as of 2026-05-26) Workflow URLhttps://n8n.pbr.org.au/workflow/T76MXW3QRGcoIBbo Webhook (production)URLhttps://n8n.pbr.org.au/webhook/availability/tol (GET) Webhookn8n (test)editorhttps://n8n.pbr.org.au/webhook-test/availability/tolworkflow/T76MXW3QRGcoIBbo TriggerHTTP GET/POST webhook Season2026-06-26 → 2026-07-19 (24 dates) ProductTOL (18:00 Lakeside Twilight Train) Base capacity256 pax / date WC capacity3 slots / date StatusActive (published) ProductActive coveredversionTOL (Train of Lights)391004ab-dcc3-48e9-92e7-62cc2a70b478 SeasonLast windowupdated2026-06-26 inclusive → 2026-07-20 exclusive (24 departure dates)05-27 Per-departure capacity256 pax (sourced from Manifest.max) Total season capacity6,144 pax Trigger nodeWebhook (GET) Node count12 OwnerAuthorMitch Fraser (PBR/ IT)Claude AI

---

Purpose

This n8n workflow exposesis athe JSONbackend endpointfor summarisingPBR's currentQueue-it availabilityintegration for the 2026 TOL (Train of Lights) 2026 season. ItQueue-it queries this endpoint to determine which dates are sold, limited, or available, and uses the data to gate the onsale queue appropriately.

The endpoint combines two independent data sources:

• CustomLinc MSSQLavailability API — sourcereal-time ofcheckout truth for departure capacityavailability (Manifest.maxapiPax): andwhat confirmedthe bookingnext paxcustomer (viacan Txnactually andbook Bookingright tables).now
• CustomLinc RESTSQL API(Txn/Booking tables) — sourceconfirmed ofbooking operator-settruth: statewhat suchhas asactually schedulebeen (isAvailable)paid and run-statusis flagsgenuinely (availabilityString values nodeparture / noservice).sold

TheThis endpointdual-source producesapproach ais Queue-it-stylerequired because the CustomLinc availability JSONAPI (withis a dynamicMessage string for operators to paste into Queue-it) plus a monitoring block usedinfluenced by thein-cart Grafana(pending) TOLbookings, Onsaleoperator dashboardsales caps, and forManifest opscounter visibilitydrift during— activenone onsaleof periods.which should drive the public SOLD/LIMITED classification.

---

ArchitectureArchitecture: andNode data flowFlow

Twelve nodes, one webhook trigger, three execution branches: cached response, full live build, and per-chunk API loop.

Availability Webhook
  (GET)
  ↓
Check Cache  — reads workflow staticData.availability, computes age vs 30s TTL
  ↓
Cache Fresh?  (IF, true if cached && cachedAt within 30s)
  —true→  Return Cached → Respond JSON
  —false→ CustomLinc Login  (POSTparallel)
  /security/login)
                  ↓→ Query Bookings    (MSSQL,SQL returnsvia 24Microsoft rowsSQL bynode)
  date)
                  ↓→ Ensure One Item   (passthrough bridge- forensures emptyClassify SQL)fires ↓even if SQL returns 0 rows)
  → Build Date Chunks (splits season into 10-day chunksAPI fromrequest today)chunks)
  ↓→ Loop Chunks
      (splitInBatches, batchSize=1)
                  ===each batch===→ Fetch CustomLinc Availability  (POST /product/availabilitytable)per ↓chunk)
      (back→ to[loop Loopback]
  Chunks for next batch)
                  ===done===→ Classify          and(Code Cachenode (Code:- mergescross-joins SQL+SQL + API, buildsoutputs payload,payload)
  writes cache)
                                  ↓→ Respond JSON      (HTTP 200 with JSON body)

The CustomLinc Login and Query Bookings nodes run in parallel at workflow start. Both results are available by the time Classify executes after the loop completes.

---

Node-by-nodeSQL referenceQuery: Query Bookings

AllThe nodeSQL parametersquery belowruns against the CustomLinc MS SQL database (subscriber 22). It joins Manifest (per-date capacity) against confirmed (statusType=0) and pending (statusType=6) Txn/Booking records.

Key design decisions

Manifest is the primary table (LEFT JOIN). Dates with no bookings still appear (pax values default to 0 via COALESCE). statusType IN (0, 6) — includes both Confirmed (0) and Pending Web Payment (6) bookings as separate columns. WC (Wheelchair Add-on fare code) is counted separately from standard pax (ADULT, CHILD, TODDLER). Booking counts are takentracked verbatimper fromstatus thefor activeops versionmonitoring. ofHardcoded theseason workflow.window: 2026-06-26 to 2026-07-20 (exclusive upper bound).

1.Output Availability Webhookcolumns

TypeColumn	Description

n8n-nodes-base.webhooktravel_dateDate (v2.1)of departure MethodGETpax_maxManifest.max — base capacity (typically 256) Pathavailability/tolmanifest_totalPaxManifest counter (cumulative, never decremented on cancel — known drift issue) Responsemanifest_totalAvailableManifest moderesponseNodeavailable (uses the "Respond JSON" node)counter AuthenticationNonepax_confirmedPaid pax from statusType=0 bookings (ADULT+CHILD+TODDLER) wc_confirmedPaid WC slots from statusType=0 bookings pax_pendingIn-cart pax from statusType=6 bookings (Pending Web Payment) wc_pendingIn-cart WC slots from statusType=6 bookings booking_count_confirmedNumber of confirmed booking records booking_count_pendingNumber of pending booking records

2.Manifest Checkcounter Cachedrift

ReadsThe Manifest.totalPax counter increments on booking but does not decrement on cancellation. This is a confirmed CustomLinc bug. The endpoint does not rely on Manifest counters for classification — it uses direct Txn aggregation instead. Manifest mismatches are flagged in monitoring.manifestMismatches[] for ops awareness only.

---

Classification Model: Pending-Blind

Sold/limited classification is driven exclusively by confirmed seats (statusType=0). Pending bookings (statusType=6, i.e. in-cart / Pending Web Payment) are tracked and reported in monitoring but do not affect the per-workflowpublic staticData.availabilityclassification object and decides whetheror the cacheddynamic payload is fresh (within 30 seconds).

const staticData = $getWorkflowStaticData('global');
const ttlSeconds = 30;
const cached = staticData.availability;
const now = Date.now();
const isFresh = cached && cached.cachedAt && (now - cached.cachedAt) / 1000 < ttlSeconds;
return [{ json: { isFresh: !!isFresh, cached: cached || null, now, startedAt: now } }];

Note: staticData is per-workflow process state; it survives between executions but is lost on n8n restart. startedAt is captured here so the Classify node can compute executionDurationMs.message.

3.Classification Cacheinputs Fresh?per (IF)

Single boolean condition: $json.isFresh === true. True branch goes to Return Cached, false branch goes to CustomLinc Login.

4. Return Cached

Strips the internal cachedAt field, adds cacheAgeSeconds to monitoring.health, and sets source: "cache".

const cached = $json.cached || {};
const { cachedAt, ...payload } = cached;
const ageSeconds = cachedAt ? Math.round((Date.now() - cachedAt) / 1000) : null;
const monitoring = payload.monitoring || {};
monitoring.health = {
  ...(monitoring.health || {}),
  cacheState: 'cache',
  cacheAgeSeconds: ageSeconds,
};
return [{ json: { ...payload, monitoring, source: 'cache' } }];

5. CustomLinc Logindate

TypeVariable	Source	Description

n8n-nodes-base.httpRequestpaxConfirmedSQL (v4.4)statusType=0Paid seats — the revenue truth MethodPOSTpaxPendingSQL statusType=6Seats in active checkout carts URLhttps://api.customlinc.com.au/puffingbillyrailway/security/loginapiPaxCustomLinc APISeats available to buy right now (reduced by confirmed + pending + any operator cap) Body{}isAvailableAPI (JSON).isAvailableWhether AuthGenericthe Customoperator Authhas credentialstopped "CustomLinc Agent Key" (sends agentAPIKey in body)sales

effectiveCap derivation

ReturnsIf the API reports fewer seats than {max authToken:- "<token>"confirmed - pending, ...an }.operator-set Thesales tokencap is consumed by the Build Date Chunks node and forwarded into each Fetch call.

6. Query Bookings (MSSQL)

Single query against the CustomLinc MSSQL database. Returns 24 rows — one per TOL departure date in the season window. Filter is b.statusType = 0, which covers all sold-class booking statuses (Confirmed, Boarded, Gift Voucher redeemed, Confirmed - Outstanding balance).inferred:

WITH dates AS (
    SELECT date AS travel_date,
           SUM([max])           AS pax_max,
           SUM(totalPax)        AS manifest_totalPax,
           SUM(totalAvailable)  AS manifest_totalAvailable
    FROM Manifest WITH (NOLOCK)
    WHERE uniqueIdSubscriberremainingIfNoCap = 22max(paxMax - paxConfirmed - paxPending, 0)
if isAvail AND codeapiPax < remainingIfNoCap:
    effectiveCap = 'TOL'apiPax AND+ datepaxConfirmed >=+ '2026-06-26'paxPending
ANDelse:
    date <  '2026-07-20'
    GROUP BY date
),
bookings AS (
    SELECT CAST(b.dateTimeOfTravel AS date) AS travel_date,
           SUM(CASE WHEN ft.code IN ('ADULT','CHILD','TODDLER') THEN t.quantity ELSE 0 END) AS pax_booked,
           SUM(CASE WHEN ft.codeeffectiveCap = 'WC'null

THEN

effectiveCap t.quantityis ELSEsuppressed when !isAvail (stop-sold) because in that case apiPax=0 END)is AScaused wc_booked,by COUNT(DISTINCToperator b.uniqueId)action, ASnot booking_counta FROMcap.

Txn t WITH

confirmedRemaining (NOLOCK)classification INNERsource)

JOIN Booking b WITH (NOLOCK) ON b.uniqueId

effectiveCeiling = t.uniqueIdBookingeffectiveCap INNER?? JOINpaxMax
FareType ft WITH (NOLOCK) ON ft.uniqueIdconfirmedRemaining = t.uniqueIdFareTypemax(effectiveCeiling WHERE- t.uniqueIdSubscriber = 22
      AND t.type = 'Booking'
      AND b.uniqueIdSubscriber = 22
      AND b.productCode = 'TOL'
      AND b.statusType = 0
      AND b.dateTimeOfTravel >= '2026-06-26'
      AND b.dateTimeOfTravel <  '2026-07-20'
      AND ft.code IN ('ADULT','CHILD','TODDLER','WC')
    GROUP BY CAST(b.dateTimeOfTravel AS date)
)
SELECT d.travel_date,
       d.pax_max,
       d.manifest_totalPax,
       d.manifest_totalAvailable,
       COALESCE(bk.pax_booked,paxConfirmed, 0) AS pax_booked,
       COALESCE(bk.wc_booked, 0)  AS wc_booked,
       COALESCE(bk.booking_count, 0) AS booking_count
FROM dates d
LEFT JOIN bookings bk ON bk.travel_date = d.travel_date
ORDER BY d.travel_date;

Field semantics

FieldSourceMeaning

pax_max

Manifest.maxConfigured capacity for the departure (256 across all TOL dates). manifest_totalPaxManifest.totalPaxCustomLinc-maintained running counter of sold pax. Surfaced for cross-check; not the authoritative figure. manifest_totalAvailableManifest.totalAvailableCompanion counter; should equal pax_max - manifest_totalPax. pax_bookedSum of Txn.quantity for fare types ADULT/CHILD/TODDLER on confirmed bookingsAuthoritative confirmed pax count. INFANT and WC are excluded. wc_bookedSum of Txn.quantity for fare type WC on confirmed bookingsWheelchair add-ons booked (separate cap of 3). booking_countCOUNT(DISTINCT Booking.uniqueId)Number of distinct confirmed booking transactions for the date.

Why statusType = 0 instead of status = 'Confirmed'

The Booking table has 19 distinct status values across 5 statusType codes. statusType = 0 covers the four "sold and counts against capacity" statuses:

Confirmed Boarded (post-trip, retained against capacity) Gift Voucher redeemed Confirmed - Outstanding balance

For TOL 2026 in its current state the two filters return identical results (all bookings are status Confirmed), but statusType = 0confirmedRemaining is robustthe againstkey legitimateclassifier seat-occupyinginput. statusesIt appearingrepresents later.how many confirmed seats are genuinely still available, ignoring pending carts.

7.Classification Ensure One Item

Passthrough bridge to guarantee downstream Code nodes always fire even if SQL returns zero rows. The Classify node reads the SQL output via $('Query Bookings').all() rather than via direct input.

return [{ json: { sqlRowCount: $input.all().length } }];

8. Build Date Chunks

Splits the remaining season window into 10-day chunks starting from today (or the season start, whichever is later). The CustomLinc availability API requires a start date plus a day count; chunking limits the per-call payload size.

const SEASON_START = '2026-06-26';
const SEASON_END = '2026-07-19';
const CHUNK_DAYS = 10;
const loginItems = $('CustomLinc Login').all();
const authToken = loginItems[0]?.json?.authToken;
if (!authToken) throw new Error('No authToken from login step');
const today = new Date(); today.setUTCHours(0, 0, 0, 0);
const seasonStart = new Date(SEASON_START + 'T00:00:00Z');
const seasonEnd = new Date(SEASON_END + 'T00:00:00Z');
let cursor = today > seasonStart ? today : seasonStart;
const chunks = [];
while (cursor <= seasonEnd) {
  const remainingMs = seasonEnd.getTime() - cursor.getTime();
  const remainingDays = Math.floor(remainingMs / 86400000) + 1;
  const days = Math.min(CHUNK_DAYS, remainingDays);
  chunks.push({
    json: { authToken, dateFrom: cursor.toISOString().slice(0, 10), days },
  });
  const next = new Date(cursor);
  next.setUTCDate(next.getUTCDate() + days);
  cursor = next;
}
return chunks;

For the 24-date 2026 season (26 Jun → 19 Jul) this produces three chunks: 26 Jun + 10 days, 6 Jul + 10 days, 16 Jul + 4 days.

9. Loop Chunks (Split In Batches)rules

TypeCondition	Result

n8n-nodes-base.splitInBatches!isAvail (v3)operator stop-sold)sold[] + stopSold[] BatchconfirmedRemaining size=== 01sold[] (onetruly chunksold perout iteration)by confirmed bookings) confirmedRemaining <= 20limited[] confirmedRemaining > 20plenty (not in any list)

apiBlocked flag (monitoring only)

isApiBlocked = (apiPax === 0) AND (confirmedRemaining > 0) AND isAvail

ForWhen eachpending iteration,bookings fill remaining seats, the chunkAPI routesreturns toapiPax=0 Fetchbut CustomLincconfirmed Availability,seats whichremain. thenThis routesis backan toops Loopsignal Chunks.— Oncepending allbookings chunkswill are processed, the "done" branch routes to Classifyexpire and Cache.release those seats. The date does not appear in sold[] or dynamicMessage. It is reported in monitoring.apiBlocked[].

10.boundBy Fetch CustomLinc Availabilityfield

TypeValue	Meaning

n8n-nodes-base.httpRequest"capacity"No (v4.4)bookings on this date MethodPOST"confirmed"Confirmed bookings are the limiting factor URLhttps://api.customlinc.com.au/puffingbillyrailway/product/availabilitytable"cap"Operator-set BodyJSON,sales seecap belowis binding (effectiveCap is set)

{

Wheelchair "authToken": "{{ $json.authToken }}", "productCode": "TOL", "date": "{{ $json.dateFrom }}", "days": {{ $json.days }}, "totalPax": 1, "returnUnavailableDates": true }

The response contains a productAvailability[0].availability[] array, one entry per date. Each entry includes dateclassification (Microsoft /Date(epoch_ms)/ format), pax (live availability), isAvailable, and availabilityString.

11. Classify and Cachepending-blind)

TheWC core merging and classification node. Reads SQL output (24 date rows) plusfollows the threesame APIpending-blind chunk responses, produces the final payload, and writes it to staticData.availability with a cachedAt timestamp.

Constants

LIMITED_THRESHOLD20 pax — date is reported as "limited" when remaining pax ≤ 20. WC_CAP3 wheelchair spaces per departure. WC_LIMITED_THRESHOLD1 —rule. WC reportedcap as= "limited"3 whenseats. WC limited threshold = 1 remains. SQL_EXPECTED_ROWS24 — total TOL season dates; used for the health check. NOT_RUNNING_VALUES{'nodeparture', 'noservice'} — availabilityString values that indicate the departure isn't running; excluded from classification.

Per-date computation

For each API entry:remaining.

Parse

/Date(epoch_ms)/ to an ISO date, build a "D/M" token (e.g. 27/6).

If availabilityString is nodeparture or noservice, increment datesNotRunning and skip the rest of the date. Look up SQL data by token. Compute remaining = max(pax_max - pax_booked, 0). Compute inflight (see below). Classify the date: sold if isAvailable=false or remaining=0; stop-sold if isAvailable=false with a non-empty availabilityString; limited if 0 < remaining ≤ 20. Cascade: if the date is sold for general pax, force effective WC remaining to 0 regardless of WC bookings. Classify WC: wc_remainingwcConfirmedRemaining = max(3 - wc_booked,wcConfirmed, 0), theneffectiveWcRemaining wcSold= dateSold ? 0 : wcConfirmedRemaining if 0,effectiveWcRemaining wcLimited<= 0 → wheelchairSold[] if ≤ 1. Compute manifest delta: pax_booked - manifest_totalPax. Increment datesManifestMismatch if non-zero. Add the per-date entry to byDate[token].

Inflight calculation (with guard)

The CustomLinc availability API pax field returns capacity minus all in-flight bookings (carts in checkout, pending payment). Subtracting our confirmed remaining from their live pax should give the in-flight count:

inflight = max(remaining - apiPax, 0)

Guard: when the API returns apiPax = 0 alongside confirmed bookings (a stop-sold or cache-lag scenario), naive subtraction produces a misleadingly large inflight figure (e.g. 246 on a date with 10 confirmed pax). The classifier returns 0 inflight in that case:

let inflight = 0;
if (paxMax !== null) {
  if (apiPaxeffectiveWcRemaining === 01  &&→ paxBooked > 0) {
    // Likely stop-sold / cache lag - cannot infer inflight
    inflight = 0;
  } else {
    inflight = Math.max(pax - apiPax, 0);
  }
}wheelchairLimited[]

Dynamic message

Output format

For ops to paste into Queue-it:

SOLD: 26/6, 27/6
LIMITED: 5/7
WC: 26/6, 27/6

Empty lines are omitted; if nothing is sold/limited the field is an empty string.

Payload

12.Public Respondclassification JSONarrays

Type	n8n-nodes-base.respondToWebhook (v1.5)

Body{{ JSON.stringify($json) }} HeadersContent-Type: application/json; charset=utf-8, Cache-Control: public, max-age=15

---

Response contract

The endpoint returns a single JSON object. Top-level shape:

{
  "sold": ["27/6"],
  "limited": ["5/7"],
  "stopSold": [],
  "detail": { "26/6": 256, "27/6": 0, "...": 0 },
  "wheelchairSold": ["27/6"],
  "wheelchairLimited": [],
  "wheelchairDetail": { "26/6": 3, "27/6": 0 },
  "dynamicMessage": "SOLD: 27/6\nWC: 27/6",
  "updatedAt": "2026-05-26T22:07:45.148Z",
  "source": "live",
  "monitoring": { "...": "see below" }
}

Top-level fields

FieldTypeMeaningDescription soldarray of "D/M" tokensstring[]DatesTokens withfor sold-out dates (e.g. "26/6"). Driven by confirmedRemaining=0 remaining or isAvailable=false.stop-sold. limitedarraystring[]DatesTokens withfor remaininglimited paxdates between(confirmedRemaining 1 and 20 inclusive.1–20). stopSoldarraystring[]Subset of sold[] where isAvailable=false (operator stop-sold). apiBlockedstring[]Dates withwhere isAvailable=falseapiPax=0 ANDbut aconfirmedRemaining>0. non-emptyOps availabilityStringmonitoring (operator-setonly stop-sold).— not in dynamic message. detailobjectPer-dateToken remaining→ generalapiPax. pax.Live checkout figure for Queue-it. Reflects confirmed + pending + cap. wheelchairSoldarraystring[]Dates with WC fullysold-out booked OR cascaded from general sold.dates. wheelchairLimitedarraystring[]DatesWC withlimited dates (1 WC space remaining.remaining). wheelchairDetailobjectPer-dateToken remaining→ WC spaceswcConfirmedRemaining (0–3)pending-blind). dynamicMessagestringOperator-pasteableQueue-it summary,dynamic newlinemessage separated.string. Format: "SOLD: d1, d2 LIMITED: d3 WC: d4". Empty if no sold/limited dates. updatedAtISO timestamp8601WhenTimestamp theof payloadthis was generated (or cached, if from cache). sourcestring"live" or "cache". monitoringobjectDiagnostic block; see below.response.

MonitoringdynamicMessage blockformat

SOLD: 26/6, 4/7
LIMITED: 11/7
WC: 26/6

Lines are only included if the respective array is non-empty. The message is blank if all dates are available. Queue-it reads this field to populate availability messaging on the waiting room page.

monitoring object

The monitoring object is not consumed by Queue-it but is available for ops dashboards and troubleshooting.

monitoring.season (season-level aggregates)

FieldDescription totalCapacitySum of pax_max across all dates totalConfirmedPaxTotal paid pax across season totalConfirmedWcTotal paid WC slots totalConfirmedBookingsBooking record count (confirmed) totalPendingPaxTotal in-cart pax across season totalPendingBookingsBooking record count (pending) totalInflightPaxAlias of totalPendingPax percentSoldtotalConfirmedPax / totalCapacity × 100 datesTotalDates processed by the classifier datesWithBookingsDates with at least one confirmed booking datesWithPendingDates with at least one pending booking datesSold / datesLimitedClassification counts datesStopSoldOperator stop-sold count datesApiBlockedDates where apiPax=0 but confirmedRemaining>0 datesManifestMismatchDates where Txn sum ≠ Manifest counter datesCappedDates with an operator-set sales cap detected

monitoring.byDate

Per-date object keyed by token (e.g. "26/6") with full classification detail:

{
  "season": {
    "totalCapacity": 6144,
    "totalConfirmedPax": 73,
    "totalConfirmedWc": 0,
    "totalConfirmedBookings": 23,
    "totalManifestTotalPax": 69,
    "totalInflightPax": 0,
    "percentSold": 1.19,
    "datesTotal": 24,
    "datesWithBookings": 11,
    "datesNotRunning": 0,
    "datesSold": 0,
    "datesLimited": 0,
    "datesStopSold": 0,
    "datesWcSold": 0,
    "datesWcLimited": 0,
    "datesManifestMismatch": 1
  },
  "byDate": {
    "27/26/6": {
    "max": 256,
    "confirmed": 16,2,          // paid pax
    "wcConfirmed": 0,
    "bookings"pending": 5,4,            // in-cart pax
    "wcPending": 0,
    "bookingsConfirmed": 1,
    "bookingsPending": 1,
    "inflight": 0,4,           // alias of pending (from SQL statusType=6)
    "remaining"confirmedRemaining": 240,254, // classification source
    "apiPax": 240,250,           // live checkout figure
    "apiBlocked": false,
    "effectiveCap": null,    // operator cap if detected
    "boundBy": "confirmed",  // "capacity" | "confirmed" | "cap"
    "manifestTotalPax": 12,237,
    "manifestTotalAvailable": 244,19,
    "manifestDelta": 4,-231,   // negative = Manifest counter ahead of Txn (cancelled bookings not decremented)
    "percentSold": 6.25,0.78,
    "isAvailable": true,
    "availabilityString": "plenty"
  }
},
  "topInflight": [],
  "topConfirmed": [
    { "date": "27/6", "confirmed": 16, "bookings": 5 },
    { "date": "11/7", "confirmed": 11, "bookings": 5 }
  ],
  "manifestMismatches": [
    { "date": "27/6", "txnConfirmed": 16, "manifestCounter": 12, "delta": 4 }
  ],
  "health": {
    "sqlRowCount": 24,
    "sqlExpected": 24,
    "sqlOk": true,
    "apiResponseCount": 3,
    "cacheState": "live",
    "executionDurationMs": 5890,
    "wcCap": 3,
    "limitedThreshold": 20,
    "confirmedFilter": "statusType=0"
  }
}

On

monitoring.topInflight cached/ responses, monitoring.health.cacheState is "cache" and a cacheAgeSeconds field is added.

Verified production sample

topConfirmed

ExecutionTop-5 1254,dates capturedsorted 2026-05-26by 22:07:39Z.inflight Execution(pending) durationpax 5,890 ms cold, 24 SQL rows returned, 3 API chunk responses, source live.

Season totals from execution 1254

MetricValue totalCapacity6,144 totalConfirmedPax73 totalConfirmedWc0 totalConfirmedBookings23 totalManifestTotalPax69 totalInflightPax0 percentSold1.19% datesTotal24 datesWithBookings11 datesManifestMismatch1

Topor confirmed (executionpax 1254)

respectively. Useful DateConfirmedfor paxBookingsops dashboards. 27/6165 11/7115 4/7103 3/782 5/782

Manifest counter mismatch (execution 1254)

monitoring.manifestMismatches

OnlyDates one date inwhere the 2026 season currently shows a discrepancy between Manifest.totalPax and the authoritative Txn-derived sum (confirmed pax:+ pending) differs from the Manifest counter. Reported for visibility of the CustomLinc counter drift bug.

DateTxn confirmedManifest counterDelta 27/616124

monitoring.capped

CauseDates notwhere yetan identified.operator-set Thesales 11/12cap otherwas activedetected (effectiveCap is non-null), sorted by cap size ascending.

monitoring.apiBlocked

Detailed array of dates showwhere manifest_totalPax = pax_booked exactly. Thischeckout is documentedblocked furtherby underpending "Knownbookings anomalies"(not below.truly sold). Each entry includes: confirmed, pending, confirmedRemaining.

---

Cache behaviour

monitoring.health

TTLField	30 seconds	Description
Storage	n8nsqlRowCount	Rows workflowreturned staticData.availability (per-workflow, process-local)

Cold execution~5–6 seconds (SQL + 3 API calls + classify) Cache hit responseSub-second; source: "cache"; includes monitoring.health.cacheAgeSeconds HTTP response cache hintCache-Control: public, max-age=15

Caveat: staticData does not survive an n8n process restart. After a restart the next call rebuilds fromby SQL + API.

---

Classification rules summary

ConditionBucket availabilityString ∈ {nodeparture, noservice}Excluded from classification; datesNotRunning incrementedquery isAvailablesqlExpectedExpected row count (24 for full season) sqlOktrue if sqlRowCount === falsesold (and stopSold if availabilityString is non-empty)sqlExpected remaining === 0apiResponseCountsoldNumber of CustomLinc API chunk responses received 0 < remaining ≤ 20executionDurationMslimitedClassify node execution time in ms General sold (any reason)wcCapWC cascadedcap toconstant sold for that date(3) wc_remaining === 0limitedThresholdwheelchairSoldLimited threshold constant (20) wc_remaining === 1confirmedFilterwheelchairLimitedstatusType IN (0,6) — SQL filter in use classificationSourceconfirmedRemaining detailSourceapiPax inflightSourcestatusType=6

---

GrafanaDesign dashboardDecisions & Rationale

Why pending-blind classification?

APending companionbookings Grafana(statusType=6) dashboardrepresent readsseats directlyin active checkout carts. They auto-expire after a timeout if payment is not completed. Classifying a date as SOLD because of pending bookings would:

Show false SOLD status to Queue-it and customers Prevent genuine sales when carts expire Cause a poor customer experience during high-demand onsales

The apiBlocked flag is provided for ops monitoring so the team is aware when this condition exists, without affecting public availability messaging.

Why apiPax in detail[] rather than confirmedRemaining?

The detail[token] value is the live checkout figure that Queue-it can pass to a checkout integration. It reflects what the next customer can actually book, accounting for confirmed + pending + operator cap. Using confirmedRemaining here would overstate available seats (it ignores in-cart bookings).

Why suppress effectiveCap when stop-sold?

When a date is stop-sold (!isAvail), apiPax is forced to 0 by operator action. The residual cap calculation would produce a false cap equal to confirmed pax. Suppressing effectiveCap in this case avoids confusing monitoring output.

Manifest counter not used for classification

The Manifest.totalPax counter is unreliable for classification because cancelled bookings do not decrement it. On 2026-06-26 the counter shows 237 (from earlier test bookings) while the actual Txn sum is 2 confirmed + 4 pending. The endpoint uses direct Txn aggregation (statusType 0 and 6) as the authoritative source. Manifest data is retained in monitoring for counter drift visibility.

SQL Manifest-join pattern

The SQL query uses Manifest as the primary table (not Booking), joined to Txn/Booking via LEFT JOINs. This ensures all 24 season dates appear in the result even if they have zero bookings. An earlier version grouped by b.dateTimeOfTravel which collapsed booking-less Manifest rows into NULLs.

Operator cap detection

CustomLinc allows operators to set a per-departure sales cap via the UI. The cap column was not found in any accessible schema table (Manifest, ManifestAllocation, Movement, ProductOption all checked). The cap is inferred from the CustomLincresidual: MSSQLif datasourceapiPax < (itmax does- notconfirmed scrape- thispending), endpoint)the cap must equal (apiPax + confirmed + pending). SameThis sourceis a forward-compatible inference that works regardless of truth,where twothe consumers serving different jobs.

TitleTOL 2026 Onsale Monitoring UIDtol-onsale-2026 Datasourcecustomlinc-mssql (UID cf2gv2a7ki328a) Refresh30 seconds TimezoneAustralia/Melbourne Template variables$season_start (default 2026-06-26), $season_end (default 2026-07-20)

Panel inventory

RowPanelType Season summarySeason capacity, Confirmed pax, Season % sold, Confirmed bookings, Manifest mismatches, Confirmed WC6 × stat Per-date breakdownCapacity vs Confirmed by dateBar chart (stacked) Per-date breakdownPer-date snapshot (full table)Table with % sold gauge cells, Manifest Δ coloured Per-date breakdownManifest counter discrepancies (mismatched dates only)Table Sales paceCumulative confirmed pax per departure (hourly)Time series (stepped) Sales paceConfirmed pax per hour (total)Bar chart Sales paceBookings per hourBar chart CancellationsBooking status mixDonut CancellationsCancelled bookings (last 50)Table

The dashboard JSONcap is held by Mitch and can be re-imported via Grafana → Dashboards → New → Import.stored.

---

Known anomalies and ops notesIssues

27/6Silent Manifestfailure counter delta (currently 4)mode

As of 2026-05-26, Manifest.totalPax for 27 June reports 12 whileIf the Txn-derivedCustomLinc confirmedavailability paxAPI sumreturns isa 16.response Thewith othernull/empty 11 active TOL dates show exact agreement betweenproductAvailability, the twoworkflow counters.still Rootreturns causeHTTP not200 yetwith identified.sqlOk: Thetrue endpointbut surfacesno thisdate data in monitoring.manifestMismatches and monitoring.season.datesManifestMismatch. Action: investigate when convenient. Not blocking.

27/6 Manifest.max override didn't land

An operator attempt to set Manifest.max to 10 on 27/6 (to simulate a sold-out scenario for testing) did not propagate to the Manifest.maxpayload. columnQueue-it —would thereceive DBan stillempty showsavailability 256.structure. WhetherThis CustomLincedge has a separate sales-cap mechanismcase is not yet known.handled with an explicit error response. Action:Status: confirmopen.

via

Manifest counter drift (CustomLinc bug)

Cancelling a booking does not decrement the Manifest.totalPax counter. This causes growing divergence between the counter and actual bookings on dates with cancellation activity. Does not affect classification (Txn is used directly). Reported to CustomLinc UIsupport. beforeStatus: relyingvendor onbug, operator-setmonitoring capacity overrides.only.

Pending booking expiry timing

Pending bookings (statusType=6) auto-expire on a CustomLinc internal timeout. The expiry duration is not yetdocumented. observableDuring inhigh-demand productiononsales, datamany

Percarts Mitchmay (PBRbe IT):open CustomLinc'ssimultaneously, pending/in-cartcausing bookingapiBlocked states exist but auto-cancel via a short timeout workflow, so they rarely persist long enoughdates to snapshot.appear Thetransiently. endpoint'sThese inflightresolve calculationautomatically inferswhen pendingcarts paxexpire fromwithout the difference between Txn-derived remaining and API live pax. Action: re-test inflight figures during a live onsale period to validate.

API stop-sold ambiguity

An earlier execution observed apiPax = 0 with isAvailable = true and availabilityString = "none" on 27/6, despite 10 confirmed bookings. This caused the previous (pre-guard) inflight calculation to report a misleading 246. The inflight guard in the Classify node prevents this. Action: if this pattern recurs during onsale, investigate whether CustomLinc has a per-departure stop-sold flag distinct from isAvailable.payment.

---

SecuritySeasonal and credentialsMaintenance

CustomLinc agent key credential"CustomLinc Agent Key" (Generic Custom Auth, body field agentAPIKey). Pending rotation — was exposed earlier in chat. CustomLinc MSSQL credentialRead-only account. Shared with

At the customlinc-mssqlstart Grafanaof datasourceeach (UID cf2gv2a7ki328a).

Webhook authenticationNone currently. Pending: bearer token or Palo Alto IP allowlist for defence in depth.

---

Maintenance notes

Annual rollover for nextnew TOL season

season,

Thethe seasonfollowing window is hardcoded in two places. Bothconstants must be updated each year:

Query Bookings node — update the two SQL date literals ('2026-06-26', '2026-07-20') in both the datesSQL CTEquery and the bookings CTE. Build Date Chunks nodenode: —

update SEASON_START and— first date of the new season SEASON_END constants.— Note:last SQLdate isof end-exclusivethe ('2026-07-20');new chunks builder is end-inclusive ('2026-07-19').season

The Classify node's

SQL_EXPECTED_ROWS constant (24) should be updated if next year's season has a different— number of dates.dates

Adjustingin thresholds

the season (for LIMITED_THRESHOLDhealth =check) 20PaxSQL remainingWHERE at which aclause date becomesbounds "limited". Edit(hardcoded in Classifythe andQuery CacheBookings node.node) WC_CAP = 3Wheelchair spaces per departure. Edit in Classify and Cache node. WC_LIMITED_THRESHOLD = 1WC remaining at which WC becomes "limited". Edit in Classify and Cache node. Cache TTL (30s)ttlSeconds = 30 in Check Cache node.

Cancelled bookings policy

TheAlso current SQL excludes cancelled bookings entirely (filter is statusType = 0, which does not include cancelled types). The Grafana dashboard has a separate "Cancelled bookings (last 50)" panel for visibility, but this endpoint deliberately does not surface them.verify:

Related artifacts

• n8nCustomLinc workflowAuth editor:credentials https://n8n.pbr.org.au/workflow/T76MXW3QRGcoIBboare still valid (Login node)
• StatusThe probeSQL workflowsubscriber ID (discovery22) /has reconciliationnot tool): https://n8n.pbr.org.au/workflow/g42iOXUBX8QIkrzjchanged
• LiveThe endpoint:product https://n8n.pbr.org.au/webhook/availability/tolcode (TOL) matches the new season's product
• GrafanaQueue-it dashboarddynamic UID:message tol-onsale-2026format matches the new season's campaign configuration