# CustomLinc TOL → Queue-it Availability JSON

## Overview

<table id="bkmrk-workflow-idt76mxw3qr"><tbody><tr><th>Workflow ID</th><td>T76MXW3QRGcoIBbo</td></tr><tr><th>Webhook URL</th><td>https://n8n.pbr.org.au/webhook/availability/tol</td></tr><tr><th>n8n editor</th><td>https://n8n.pbr.org.au/workflow/T76MXW3QRGcoIBbo</td></tr><tr><th>Trigger</th><td>HTTP GET/POST webhook</td></tr><tr><th>Season</th><td>2026-06-26 → 2026-07-19 (24 dates)</td></tr><tr><th>Product</th><td>TOL (18:00 Lakeside Twilight Train)</td></tr><tr><th>Base capacity</th><td>256 pax / date</td></tr><tr><th>WC capacity</th><td>3 slots / date</td></tr><tr><th>Status</th><td>Active (published)</td></tr><tr><th>Active version</th><td>391004ab-dcc3-48e9-92e7-62cc2a70b478</td></tr><tr><th>Last updated</th><td>2026-05-27</td></tr><tr><th>Author</th><td>Mitch Fraser / Claude AI</td></tr></tbody></table>

---

## Purpose

This n8n workflow is the backend for PBR's Queue-it integration for the 2026 TOL season. Queue-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 availability API** — real-time checkout availability (`apiPax`): what the next customer can actually book right now
- **CustomLinc SQL (Txn/Booking tables)** — confirmed booking truth: what has actually been paid and is genuinely sold

This dual-source approach is required because the CustomLinc availability API is influenced by in-cart (pending) bookings, operator sales caps, and Manifest counter drift — none of which should drive the public SOLD/LIMITED classification.

---

## Architecture: Node Flow

```
Availability Webhook
  → CustomLinc Login  (parallel)
  → Query Bookings    (SQL via Microsoft SQL node)
  → Ensure One Item   (passthrough - ensures Classify fires even if SQL returns 0 rows)
  → Build Date Chunks (splits season into 10-day API request chunks)
  → Loop Chunks
      → Fetch CustomLinc Availability  (POST per chunk)
      → [loop back]
  → Classify          (Code node - cross-joins SQL + API, outputs payload)
  → 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.

---

## SQL Query: Query Bookings

The SQL query runs 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 tracked per status for ops monitoring.
- Hardcoded season window: 2026-06-26 to 2026-07-20 (exclusive upper bound).

### Output columns

<table id="bkmrk-columndescription-tr"><tbody><tr><th>Column</th><th>Description</th></tr><tr><td>travel\_date</td><td>Date of departure</td></tr><tr><td>pax\_max</td><td>Manifest.max — base capacity (typically 256)</td></tr><tr><td>manifest\_totalPax</td><td>Manifest counter (cumulative, never decremented on cancel — known drift issue)</td></tr><tr><td>manifest\_totalAvailable</td><td>Manifest available counter</td></tr><tr><td>pax\_confirmed</td><td>Paid pax from statusType=0 bookings (ADULT+CHILD+TODDLER)</td></tr><tr><td>wc\_confirmed</td><td>Paid WC slots from statusType=0 bookings</td></tr><tr><td>pax\_pending</td><td>In-cart pax from statusType=6 bookings (Pending Web Payment)</td></tr><tr><td>wc\_pending</td><td>In-cart WC slots from statusType=6 bookings</td></tr><tr><td>booking\_count\_confirmed</td><td>Number of confirmed booking records</td></tr><tr><td>booking\_count\_pending</td><td>Number of pending booking records</td></tr></tbody></table>

### Manifest counter drift

The 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 public classification or the dynamic message.

### Classification inputs per date

<table id="bkmrk-variablesourcedescri"><tbody><tr><th>Variable</th><th>Source</th><th>Description</th></tr><tr><td>`paxConfirmed`</td><td>SQL statusType=0</td><td>Paid seats — the revenue truth</td></tr><tr><td>`paxPending`</td><td>SQL statusType=6</td><td>Seats in active checkout carts</td></tr><tr><td>`apiPax`</td><td>CustomLinc API</td><td>Seats available to buy right now (reduced by confirmed + pending + any operator cap)</td></tr><tr><td>`isAvailable`</td><td>API .isAvailable</td><td>Whether the operator has stopped sales</td></tr></tbody></table>

### effectiveCap derivation

If the API reports fewer seats than `max - confirmed - pending`, an operator-set sales cap is inferred:

```
remainingIfNoCap = max(paxMax - paxConfirmed - paxPending, 0)
if isAvail AND apiPax < remainingIfNoCap:
    effectiveCap = apiPax + paxConfirmed + paxPending
else:
    effectiveCap = null
```

effectiveCap is suppressed when `!isAvail` (stop-sold) because in that case apiPax=0 is caused by operator action, not a cap.

### confirmedRemaining (classification source)

```
effectiveCeiling = effectiveCap ?? paxMax
confirmedRemaining = max(effectiveCeiling - paxConfirmed, 0)
```

`confirmedRemaining` is the key classifier input. It represents how many confirmed seats are genuinely still available, ignoring pending carts.

### Classification rules

<table id="bkmrk-conditionresult-%21isa"><tbody><tr><th>Condition</th><th>Result</th></tr><tr><td>`!isAvail` (operator stop-sold)</td><td>`sold[]` + `stopSold[]`</td></tr><tr><td>`confirmedRemaining === 0`</td><td>`sold[]` (truly sold out by confirmed bookings)</td></tr><tr><td>`confirmedRemaining <= 20`</td><td>`limited[]`</td></tr><tr><td>`confirmedRemaining > 20`</td><td>plenty (not in any list)</td></tr></tbody></table>

### apiBlocked flag (monitoring only)

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

When pending bookings fill remaining seats, the API returns apiPax=0 but confirmed seats remain. This is an ops signal — pending bookings will expire and release those seats. The date does **not** appear in `sold[]` or `dynamicMessage`. It is reported in `monitoring.apiBlocked[]`.

### boundBy field

<table id="bkmrk-valuemeaning-%22capaci"><tbody><tr><th>Value</th><th>Meaning</th></tr><tr><td>`"capacity"`</td><td>No bookings on this date</td></tr><tr><td>`"confirmed"`</td><td>Confirmed bookings are the limiting factor</td></tr><tr><td>`"cap"`</td><td>Operator-set sales cap is binding (effectiveCap is set)</td></tr></tbody></table>

### Wheelchair classification (pending-blind)

WC follows the same pending-blind rule. WC cap = 3 seats. WC limited threshold = 1 remaining.

```
wcConfirmedRemaining = max(3 - wcConfirmed, 0)
effectiveWcRemaining = dateSold ? 0 : wcConfirmedRemaining
if effectiveWcRemaining <= 0  → wheelchairSold[]
if effectiveWcRemaining === 1  → wheelchairLimited[]
```

---

## Output Payload

### Public classification arrays

<table id="bkmrk-fieldtypedescription"><tbody><tr><th>Field</th><th>Type</th><th>Description</th></tr><tr><td>`sold`</td><td>string\[\]</td><td>Tokens for sold-out dates (e.g. "26/6"). Driven by confirmedRemaining=0 or stop-sold.</td></tr><tr><td>`limited`</td><td>string\[\]</td><td>Tokens for limited dates (confirmedRemaining 1–20).</td></tr><tr><td>`stopSold`</td><td>string\[\]</td><td>Subset of sold\[\] where isAvailable=false (operator stop-sold).</td></tr><tr><td>`apiBlocked`</td><td>string\[\]</td><td>Dates where apiPax=0 but confirmedRemaining&gt;0. Ops monitoring only — not in dynamic message.</td></tr><tr><td>`detail`</td><td>object</td><td>Token → apiPax. Live checkout figure for Queue-it. Reflects confirmed + pending + cap.</td></tr><tr><td>`wheelchairSold`</td><td>string\[\]</td><td>WC sold-out dates.</td></tr><tr><td>`wheelchairLimited`</td><td>string\[\]</td><td>WC limited dates (1 remaining).</td></tr><tr><td>`wheelchairDetail`</td><td>object</td><td>Token → wcConfirmedRemaining (pending-blind).</td></tr><tr><td>`dynamicMessage`</td><td>string</td><td>Queue-it dynamic message string. Format: "SOLD: d1, d2 LIMITED: d3 WC: d4". Empty if no sold/limited dates.</td></tr><tr><td>`updatedAt`</td><td>ISO 8601</td><td>Timestamp of this response.</td></tr></tbody></table>

### dynamicMessage format

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

<table id="bkmrk-fielddescription-tot"><tbody><tr><th>Field</th><th>Description</th></tr><tr><td>totalCapacity</td><td>Sum of pax\_max across all dates</td></tr><tr><td>totalConfirmedPax</td><td>Total paid pax across season</td></tr><tr><td>totalConfirmedWc</td><td>Total paid WC slots</td></tr><tr><td>totalConfirmedBookings</td><td>Booking record count (confirmed)</td></tr><tr><td>totalPendingPax</td><td>Total in-cart pax across season</td></tr><tr><td>totalPendingBookings</td><td>Booking record count (pending)</td></tr><tr><td>totalInflightPax</td><td>Alias of totalPendingPax</td></tr><tr><td>percentSold</td><td>totalConfirmedPax / totalCapacity × 100</td></tr><tr><td>datesTotal</td><td>Dates processed by the classifier</td></tr><tr><td>datesWithBookings</td><td>Dates with at least one confirmed booking</td></tr><tr><td>datesWithPending</td><td>Dates with at least one pending booking</td></tr><tr><td>datesSold / datesLimited</td><td>Classification counts</td></tr><tr><td>datesStopSold</td><td>Operator stop-sold count</td></tr><tr><td>datesApiBlocked</td><td>Dates where apiPax=0 but confirmedRemaining&gt;0</td></tr><tr><td>datesManifestMismatch</td><td>Dates where Txn sum ≠ Manifest counter</td></tr><tr><td>datesCapped</td><td>Dates with an operator-set sales cap detected</td></tr></tbody></table>

#### monitoring.byDate

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

```
{
  "26/6": {
    "max": 256,
    "confirmed": 2,          // paid pax
    "wcConfirmed": 0,
    "pending": 4,            // in-cart pax
    "wcPending": 0,
    "bookingsConfirmed": 1,
    "bookingsPending": 1,
    "inflight": 4,           // alias of pending (from SQL statusType=6)
    "confirmedRemaining": 254, // classification source
    "apiPax": 250,           // live checkout figure
    "apiBlocked": false,
    "effectiveCap": null,    // operator cap if detected
    "boundBy": "confirmed",  // "capacity" | "confirmed" | "cap"
    "manifestTotalPax": 237,
    "manifestTotalAvailable": 19,
    "manifestDelta": -231,   // negative = Manifest counter ahead of Txn (cancelled bookings not decremented)
    "percentSold": 0.78,
    "isAvailable": true,
    "availabilityString": "plenty"
  }
}
```

#### monitoring.topInflight / topConfirmed

Top-5 dates sorted by inflight (pending) pax or confirmed pax respectively. Useful for ops dashboards.

#### monitoring.manifestMismatches

Dates where the Txn-derived sum (confirmed + pending) differs from the Manifest counter. Reported for visibility of the CustomLinc counter drift bug.

#### monitoring.capped

Dates where an operator-set sales cap was detected (effectiveCap is non-null), sorted by cap size ascending.

#### monitoring.apiBlocked

Detailed array of dates where checkout is blocked by pending bookings (not truly sold). Each entry includes: confirmed, pending, confirmedRemaining.

#### monitoring.health

<table id="bkmrk-fielddescription-sql"><tbody><tr><th>Field</th><th>Description</th></tr><tr><td>sqlRowCount</td><td>Rows returned by SQL query</td></tr><tr><td>sqlExpected</td><td>Expected row count (24 for full season)</td></tr><tr><td>sqlOk</td><td>true if sqlRowCount === sqlExpected</td></tr><tr><td>apiResponseCount</td><td>Number of CustomLinc API chunk responses received</td></tr><tr><td>executionDurationMs</td><td>Classify node execution time in ms</td></tr><tr><td>wcCap</td><td>WC cap constant (3)</td></tr><tr><td>limitedThreshold</td><td>Limited threshold constant (20)</td></tr><tr><td>confirmedFilter</td><td>statusType IN (0,6) — SQL filter in use</td></tr><tr><td>classificationSource</td><td>confirmedRemaining</td></tr><tr><td>detailSource</td><td>apiPax</td></tr><tr><td>inflightSource</td><td>statusType=6</td></tr></tbody></table>

---

## Design Decisions &amp; Rationale

### Why pending-blind classification?

Pending bookings (statusType=6) represent seats in 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 residual: if apiPax &lt; (max - confirmed - pending), the cap must equal (apiPax + confirmed + pending). This is a forward-compatible inference that works regardless of where the cap is stored.

---

## Known Issues

### Silent failure mode

If the CustomLinc availability API returns a response with null/empty `productAvailability`, the workflow still returns HTTP 200 with `sqlOk: true` but no date data in the payload. Queue-it would receive an empty availability structure. This edge case is not yet handled with an explicit error response. **Status: open.**

### 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 support. **Status: vendor bug, monitoring only.**

### Pending booking expiry timing

Pending bookings (statusType=6) auto-expire on a CustomLinc internal timeout. The expiry duration is not documented. During high-demand onsales, many carts may be open simultaneously, causing `apiBlocked` dates to appear transiently. These resolve automatically when carts expire without payment.

---

## Seasonal Maintenance

At the start of each new TOL season, the following constants must be updated in both the SQL query and the Build Date Chunks node:

- `SEASON_START` — first date of the new season
- `SEASON_END` — last date of the new season
- `SQL_EXPECTED_ROWS` — number of dates in the season (for health check)
- SQL WHERE clause date bounds (hardcoded in the Query Bookings node)

Also verify:

- CustomLinc Auth credentials are still valid (Login node)
- The SQL subscriber ID (22) has not changed
- The product code (TOL) matches the new season's product
- Queue-it dynamic message format matches the new season's campaign configuration