Skip to main content

CustomLinc TOL → Queue-it Availability JSON

Overview

Workflow nameCustomLinc 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)https://n8n.pbr.org.au/webhook/availability/tol (GET)
Webhook (test)https://n8n.pbr.org.au/webhook-test/availability/tol
StatusActive
Product coveredTOL (Train of Lights)
Season window2026-06-26 inclusive → 2026-07-20 exclusive (24 departure dates)
Per-departure capacity256 pax (sourced from Manifest.max)
Total season capacity6,144 pax
Trigger nodeWebhook (GET)
Node count12
OwnerMitch Fraser (PBR IT)

Purpose

This workflow exposes a JSON endpoint summarising current availability for the TOL (Train of Lights) 2026 season. It combines two sources:

  • CustomLinc MSSQL — source of truth for departure capacity (Manifest.max) and confirmed booking pax (via Txn and Booking tables).
  • CustomLinc REST API — source of operator-set state such as schedule (isAvailable) and run-status flags (availabilityString values nodeparture / noservice).

The endpoint produces a Queue-it-style availability JSON (with a dynamicMessage string for operators to paste into Queue-it) plus a monitoring block used by the Grafana TOL Onsale dashboard and for ops visibility during active onsale periods.

Architecture and data flow

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 (POST /security/login)
                  ↓
                Query Bookings (MSSQL, returns 24 rows by date)
                  ↓
                Ensure One Item (passthrough bridge for empty SQL)
                  ↓
                Build Date Chunks (splits season into 10-day chunks from today)
                  ↓
                Loop Chunks (splitInBatches, batchSize=1)
                  ===each batch===→ Fetch CustomLinc Availability (POST /product/availabilitytable)
                                       ↓
                                       (back to Loop Chunks for next batch)
                  ===done===→ Classify and Cache (Code: merges SQL+API, builds payload, writes cache)
                                  ↓
                                Respond JSON

Node-by-node reference

All node parameters below are taken verbatim from the active version of the workflow.

1. Availability Webhook

Typen8n-nodes-base.webhook (v2.1)
MethodGET
Pathavailability/tol
Response moderesponseNode (uses the "Respond JSON" node)
AuthenticationNone

2. Check Cache

Reads the per-workflow staticData.availability object and decides whether the cached 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.

3. Cache Fresh? (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 Login

Typen8n-nodes-base.httpRequest (v4.4)
MethodPOST
URLhttps://api.customlinc.com.au/puffingbillyrailway/security/login
Body{} (JSON)
AuthGeneric Custom Auth credential "CustomLinc Agent Key" (sends agentAPIKey in body)

Returns { authToken: "<token>", ... }. The token 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).

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 uniqueIdSubscriber = 22
      AND code = 'TOL'
      AND date >= '2026-06-26'
      AND 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.code = 'WC' THEN t.quantity ELSE 0 END) AS wc_booked,
           COUNT(DISTINCT b.uniqueId) AS booking_count
    FROM Txn t WITH (NOLOCK)
    INNER JOIN Booking b WITH (NOLOCK)  ON b.uniqueId = t.uniqueIdBooking
    INNER JOIN FareType ft WITH (NOLOCK) ON ft.uniqueId = t.uniqueIdFareType
    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, 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_maxManifest.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 = 0 is robust against legitimate seat-occupying statuses appearing later.

7. 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)

Typen8n-nodes-base.splitInBatches (v3)
Batch size1 (one chunk per iteration)

For each iteration, the chunk routes to Fetch CustomLinc Availability, which then routes back to Loop Chunks. Once all chunks are processed, the "done" branch routes to Classify and Cache.

10. Fetch CustomLinc Availability

Typen8n-nodes-base.httpRequest (v4.4)
MethodPOST
URLhttps://api.customlinc.com.au/puffingbillyrailway/product/availabilitytable
BodyJSON, see below
 
{
  "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 date (Microsoft /Date(epoch_ms)/ format), pax (live availability), isAvailable, and availabilityString.

11. Classify and Cache

The core merging and classification node. Reads SQL output (24 date rows) plus the three API 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 — WC reported as "limited" when 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:

  1. Parse /Date(epoch_ms)/ to an ISO date, build a "D/M" token (e.g. 27/6).
  2. If availabilityString is nodeparture or noservice, increment datesNotRunning and skip the rest of the date.
  3. Look up SQL data by token. Compute remaining = max(pax_max - pax_booked, 0).
  4. Compute inflight (see below).
  5. 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.
  6. Cascade: if the date is sold for general pax, force effective WC remaining to 0 regardless of WC bookings.
  7. Classify WC: wc_remaining = max(3 - wc_booked, 0), then wcSold if 0, wcLimited if ≤ 1.
  8. Compute manifest delta: pax_booked - manifest_totalPax. Increment datesManifestMismatch if non-zero.
  9. 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 (apiPax === 0 && paxBooked > 0) {
    // Likely stop-sold / cache lag - cannot infer inflight
    inflight = 0;
  } else {
    inflight = Math.max(pax - apiPax, 0);
  }
}

Dynamic message 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.

12. Respond JSON

Typen8n-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

FieldTypeMeaning
soldarray of "D/M" tokensDates with 0 remaining or isAvailable=false.
limitedarrayDates with remaining pax between 1 and 20 inclusive.
stopSoldarrayDates with isAvailable=false AND a non-empty availabilityString (operator-set stop-sold).
detailobjectPer-date remaining general pax.
wheelchairSoldarrayDates with WC fully booked OR cascaded from general sold.
wheelchairLimitedarrayDates with 1 WC space remaining.
wheelchairDetailobjectPer-date remaining WC spaces (0–3).
dynamicMessagestringOperator-pasteable summary, newline separated.
updatedAtISO timestampWhen the payload was generated (or cached, if from cache).
sourcestring"live" or "cache".
monitoringobjectDiagnostic block; see below.

Monitoring block

{
  "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/6": {
      "max": 256,
      "confirmed": 16,
      "wcConfirmed": 0,
      "bookings": 5,
      "inflight": 0,
      "remaining": 240,
      "apiPax": 240,
      "manifestTotalPax": 12,
      "manifestTotalAvailable": 244,
      "manifestDelta": 4,
      "percentSold": 6.25,
      "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 cached responses, monitoring.health.cacheState is "cache" and a cacheAgeSeconds field is added.

Verified production sample

Execution 1254, captured 2026-05-26 22:07:39Z. Execution duration 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

Top confirmed (execution 1254)

DateConfirmed paxBookings
27/6165
11/7115
4/7103
3/782
5/782

Manifest counter mismatch (execution 1254)

Only one date in the 2026 season currently shows a discrepancy between Manifest.totalPax and the authoritative Txn-derived confirmed pax:

DateTxn confirmedManifest counterDelta
27/616124

Cause not yet identified. The 11/12 other active dates show manifest_totalPax = pax_booked exactly. This is documented further under "Known anomalies" below.

Cache behaviour

TTL30 seconds
Storagen8n workflow 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 from SQL + API.

Classification rules summary

ConditionBucket
availabilityString ∈ {nodeparture, noservice}Excluded from classification; datesNotRunning incremented
isAvailable === falsesold (and stopSold if availabilityString is non-empty)
remaining === 0sold
0 < remaining ≤ 20limited
General sold (any reason)WC cascaded to sold for that date
wc_remaining === 0wheelchairSold
wc_remaining === 1wheelchairLimited

Grafana dashboard

A companion Grafana dashboard reads directly from the CustomLinc MSSQL datasource (it does not scrape this endpoint). Same source of truth, two 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 JSON is held by Mitch and can be re-imported via Grafana → Dashboards → New → Import.

Known anomalies and ops notes

27/6 Manifest counter delta (currently 4)

As of 2026-05-26, Manifest.totalPax for 27 June reports 12 while the Txn-derived confirmed pax sum is 16. The other 11 active TOL dates show exact agreement between the two counters. Root cause not yet identified. The endpoint surfaces this 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.max column — the DB still shows 256. Whether CustomLinc has a separate sales-cap mechanism is not yet known. Action: confirm via CustomLinc UI before relying on operator-set capacity overrides.

Pending bookings not yet observable in production data

Per Mitch (PBR IT): CustomLinc's pending/in-cart booking states exist but auto-cancel via a short timeout workflow, so they rarely persist long enough to snapshot. The endpoint's inflight calculation infers pending pax from 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.

Security and credentials

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 the customlinc-mssql Grafana datasource (UID cf2gv2a7ki328a).
Webhook authenticationNone currently. Pending: bearer token or Palo Alto IP allowlist for defence in depth.

Maintenance notes

Annual rollover for next TOL season

The season window is hardcoded in two places. Both must be updated each year:

  1. Query Bookings node — update the two SQL date literals ('2026-06-26', '2026-07-20') in both the dates CTE and the bookings CTE.
  2. Build Date Chunks node — update SEASON_START and SEASON_END constants. Note: SQL is end-exclusive ('2026-07-20'); chunks builder is end-inclusive ('2026-07-19').

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

Adjusting thresholds

LIMITED_THRESHOLD = 20Pax remaining at which a date becomes "limited". Edit in Classify and Cache 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

The 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.

Back to top