Nightly Ticketmaster venue events sync to Airtable

Build me a code workflow that runs every night at 2am UTC and mirrors upcoming Ticketmaster events at a list of tracked venues into Airtable, so my ops team has a clean queryable source of truth without scraping.

Trigger: cron, daily at 02:00 UTC.

Inputs (configurable at deploy time): Airtable base ID, the Tracked Venues table name (default: 'Tracked Venues'), and the Upcoming Events table name (default: 'Upcoming Events').

Step 1: Read the Tracked Venues table from Airtable using the List Records operation. Each row is one venue and has a 'name' field (required) and an optional 'city' field for disambiguation. Page through results until the offset is gone.

Step 2: For each tracked venue, call Ticketmaster Search Venues with the venue name as the keyword and the city as a filter when present. Pick the best match: prefer an exact case-insensitive name match in the right city, otherwise fall back to the first result. If no result, log a warning and skip that venue. Save the resolved Ticketmaster venueId.

Step 3: For each resolved venueId, call Ticketmaster Search Events with venueId, startDateTime = now (ISO 8601, UTC), endDateTime = now + 90 days, size = 100, and page starting at 0. Paginate by incrementing page until totalPages is reached OR page * size would hit the 1000-result deep-paging cap, whichever comes first. Read events out of the response envelope at _embedded.events; the top level is the HATEOAS wrapper, not the array.

Step 4: For each event, build an Airtable row keyed by the Ticketmaster event id with these fields: ticketmaster_event_id (string, the dedupe key), name (event.name), start_datetime (event.dates.start.dateTime), venue (the venue name from the tracked row, not the embedded venue, so it stays stable), headliner (the first entry in _embedded.attractions[].name when present, else null), classification (event.classifications[0].segment.name), min_price (event.priceRanges[0].min when present), max_price (event.priceRanges[0].max when present), on_sale_datetime (event.sales.public.startDateTime), presale_window (a short string built from event.sales.presales[] start/end if present, else null), ticket_url (event.url), and status = 'active'.

Step 5: Upsert into the Upcoming Events table using Airtable's Update Multiple Records operation with performUpsert.fieldsToMergeOn = ['ticketmaster_event_id']. Batch in chunks of 10 records per request (Airtable's limit). Use typecast = true so datetimes and select fields convert cleanly.

Step 6: Track which ticketmaster_event_ids were written in this run. After the upsert pass, fetch all existing Upcoming Events rows where status = 'active' (List Records with a filterByFormula). For any row whose ticketmaster_event_id is NOT in the set written this run, update its status to 'removed' using Update Multiple Records. Never delete rows. Historical reporting depends on them.

Cross-cutting requirements:

- Ticketmaster auth is a Consumer Key sent as the ?apikey= query parameter on every request. No Authorization header.

- Throttle Ticketmaster calls to roughly 4 requests per second to stay under the 5 rps limit. On 429, back off exponentially starting at 1 second, doubling, capped at 30 seconds.

- Airtable rate limit is 5 requests per second per base; on 429 wait 30 seconds and retry.

- Make the run idempotent: re-running on the same day should produce the same Airtable state, not duplicates.

- Log a one-line summary at the end: venues processed, venues unresolved, events upserted, events marked removed.