Handoff

Goal

Use Airtable as the source of truth for pharmacy landing pages and attribute Calendly welcome-call bookings back to the referring pharmacy, then write those bookings into the consolidated Airtable Patients table.

Current State

Airtable-driven partner pages

• Partner pages are generated from Airtable at build time via _data/partners.js.
• Airtable table in use: Referring pharmacies
• Required Airtable fields for partner pages:
  • Name
  • URL slug
• Attribution field in Airtable:
  • QR batch code

Local and Netlify env handling

• There was no pre-existing secret loader beyond process.env.
• _data/partners.js now reads .env.local / .env locally and process.env in Netlify.
• Fallback data exists in _data/partners.static.js if Airtable env vars are missing.

Partner landing page CTA behavior

• Partner landers no longer use HotDoc/Halaxy as the main CTA.
• They now use the shared Calendly welcome-call flow.
• Templates involved:
  • _includes/partner-page-content.njk
  • _includes/patients-page-content.njk
  • _includes/site-js.njk

Attribution logic

Implemented in js/app.js.

Current precedence for Calendly attribution:

1. If the page URL already contains utm_source=pharmacy and utm_content, use that utm_content.
2. Otherwise, on a partner page use Airtable QR batch code.
3. If QR batch code is missing, fall back to the partner slug.

Partner CTA markup now includes:

• data-partner-slug
• data-partner-utm-content

This is deliberate because:

• URL slug is for routing
• QR batch code is the canonical utm_content

Those are not always identical in Airtable.

Calendly event tracking already in browser

Existing browser listener in js/app.js:

• listens for calendly.event_scheduled
• sends analytics events for welcome-call bookings

That listener now also includes utmSource and utmContent in the Rudder event when available.

Netlify webhook receiver

Added:

• netlify/functions/calendly-webhook.js

Configured in:

• netlify.toml

Function endpoint:

• pretty URL: /api/calendly-webhook
• actual Netlify function path: /.netlify/functions/calendly-webhook

What it does:

• accepts POST
• optionally verifies Calendly signature if CALENDLY_WEBHOOK_SIGNING_KEY is present
• ignores non-invitee.created events
• looks up the pharmacy in Referring pharmacies by QR batch code
• creates or updates a lead record in Airtable Patients
• can link the lead to the referring pharmacy record if the Airtable field is a linked-record field
• can enrich phone numbers from Calendly's scheduled event outbound_call location if CALENDLY_PAT is available in Netlify and the invitee payload does not contain a phone

Default lead behavior:

• record category defaults to Lead
• Needs action is set to true on create only
• source defaults to Welcome call
• stage defaults to Welcome call booked

BP patient import

• Added scripts/sync-patients-from-bp.js for reusable Best Practice CSV imports into Airtable Patients.
• Package command: npm run patients:sync-bp.
• Detailed runbook and exception handling is in scripts/README.md.
• Airtable Patients now has a BP patient ID field.
• BP imports use the existing Record category = BP patient import value.
• The importer is dry-run by default and requires --apply before it writes to Airtable.
• The importer writes only:
  • Name
  • Email
  • Phone
  • Record category
  • BP patient ID
• It deliberately does not import DOB, Medicare, address, or other BP demographic/clinical fields.

Files Changed

• _data/partners.js
• _data/partners.static.js
• _data/site.js
• _includes/partner-page-content.njk
• _includes/patients-page-content.njk
• _includes/site-js.njk
• js/app.js
• netlify.toml
• .env.example
• README.md
• netlify/functions/calendly-webhook.js
• netlify/functions/README.md
• scripts/sync-patients-from-bp.js
• scripts/README.md
• package.json

Environment Variables

Already used for Airtable partner-page builds

• AIRTABLE_TOKEN
• AIRTABLE_BASE_ID
• AIRTABLE_TABLE_NAME=Referring pharmacies
• optional AIRTABLE_VIEW

Needed for the Calendly webhook flow

• AIRTABLE_TOKEN
• AIRTABLE_BASE_ID
• AIRTABLE_TABLE_NAME=Referring pharmacies
• AIRTABLE_LEADS_TABLE_NAME=Patients
• AIRTABLE_LEADS_STAGE_FIELD=Lead stage
• AIRTABLE_LEADS_SOURCE_FIELD=Lead source
• AIRTABLE_LEADS_CATEGORY_FIELD=Record category
• optional CALENDLY_PAT if you want phone enrichment from Calendly scheduled events
• optional CALENDLY_WEBHOOK_SIGNING_KEY

Optional overrides if Airtable field names differ

• AIRTABLE_PARTNERS_QR_FIELD
• AIRTABLE_PARTNERS_SLUG_FIELD
• AIRTABLE_LEADS_NAME_FIELD
• AIRTABLE_LEADS_EMAIL_FIELD
• AIRTABLE_LEADS_PHONE_FIELD
• AIRTABLE_LEADS_REFERRING_PHARMACY_FIELD
• AIRTABLE_LEADS_NOTES_FIELD
• AIRTABLE_LEADS_SOURCE_FIELD
• AIRTABLE_LEADS_CATEGORY_FIELD
• AIRTABLE_LEADS_NEEDS_ACTION_FIELD
• AIRTABLE_LEADS_INVITEE_URI_FIELD
• AIRTABLE_LEADS_EVENT_URI_FIELD

Optional overrides for BP CSV imports

• AIRTABLE_PATIENTS_CATEGORY_FIELD=Record category
• AIRTABLE_PATIENTS_BP_CATEGORY=BP patient import
• AIRTABLE_PATIENTS_BP_ID_FIELD=BP patient ID

What Has Been Verified

• Local/static build works without Airtable credentials.
• Live Airtable-backed build works when network access is available.
• Deploy previews are generating partner URLs from Airtable.
• Generated partner pages now render welcome-call CTAs with separate slug and QR batch code attribution values.
• node --check passes for the Netlify webhook function.
• Calendly PAT creation and Calendly API access work for nathan@azurehealth.com.au.
• An organization-scoped Calendly webhook subscription was created successfully for deploy preview 13:
  • callback URL: https://deploy-preview-13--hilarious-jalebi-3fa1c2.netlify.app/api/calendly-webhook
  • scope: organization
  • event: invitee.created
• End-to-end Calendly webhook delivery into Netlify works.
• Airtable lead creation from a real invitee.created webhook works.
• The linked field inside Patients is named Referring pharmacy.
• Netlify env override required for the linked-record field is:
  • AIRTABLE_LEADS_REFERRING_PHARMACY_FIELD=Referring pharmacy
• Partner-page bookings now create leads with:
  • Record category = Lead
  • Needs action = true on create
  • Lead source = Welcome call
  • Lead stage = Welcome call booked
  • linked Referring pharmacy
  • notes containing UTM/source data
• Calendly phone-call bookings require a phone number in the Calendly UI, but Calendly does not include that number on the invitee payload used by the webhook.
• For phone-call events, Calendly stores the number on the scheduled event resource as:
  • location.type = outbound_call
  • location.location = <phone number>
• The webhook was updated to fetch that scheduled event resource and populate Airtable Phone when CALENDLY_PAT is present in Netlify.
• After adding CALENDLY_PAT to Netlify and redeploying, phone numbers populated successfully in Airtable.
• BPS Search Result 2026052750508.csv was imported into Airtable Patients.
• The safe BP import added 26 new Airtable patient records.
• The safe BP import backfilled BP IDs for existing BP-import patients.
• Final BP import verification dry-run showed:
  • Updates: 0
  • Creates: 0
  • Already correct: 246
  • Airtable patients: 274
  • Scoped BP patients: 261

What Has Not Been Verified Yet

• Homepage booking behavior with explicit non-partner UTM parameters
• Production webhook subscription pointed at https://azurehealth.com.au/api/calendly-webhook
• Optional signature verification using CALENDLY_WEBHOOK_SIGNING_KEY
• Whether you want webhook dedupe fields added to Airtable
• BP import exception rows have not been manually resolved yet.

Next Steps

1. Finish homepage / UTM verification

Book a welcome call from:

• homepage without partner attribution
• homepage with explicit UTM params if you want to verify non-partner source data

Then verify in Airtable Patients:

• lead created
• Record category = Lead
• Needs action = true
• Lead source = Welcome call
• Lead stage = Welcome call booked
• Referring pharmacy is blank unless pharmacy attribution is expected
• Notes contain the expected UTM values

2. Move the webhook from preview to production

Current temporary webhook target:

• https://deploy-preview-13--hilarious-jalebi-3fa1c2.netlify.app/api/calendly-webhook

Production target should become:

• https://azurehealth.com.au/api/calendly-webhook

Practical options:

• update the existing Calendly webhook subscription callback URL to production, or
• create a new production webhook subscription and delete the preview one

Reason to leave preview in place temporarily:

• production bookings currently work because Calendly posts server-side events to the configured callback URL, regardless of which frontend environment initiated the booking
• this should still be moved to production once the production deploy contains the same webhook code and env vars

3. Optional signature verification

If you want signed webhook verification in production:

• create/use a Calendly OAuth app
• copy its Webhook signing key
• set CALENDLY_WEBHOOK_SIGNING_KEY in Netlify

Until then, leaving that env var unset keeps the current unsigned webhook behavior.

4. Recommended hardening

Recommended next improvements after webhook testing:

• add Calendly invitee URI field to Patients
• add Calendly event URI field to Patients
• set:
  • AIRTABLE_LEADS_INVITEE_URI_FIELD=Calendly invitee URI
  • AIRTABLE_LEADS_EVENT_URI_FIELD=Calendly event URI

This will give clean dedupe on repeated webhook deliveries or reschedules.

5. Resolve BP import exceptions

The safe BP import intentionally skipped uncertain rows. Use the exception lists from:

npm run patients:sync-bp

Current unresolved buckets after the BPS Search Result 2026052750508.csv import:

• Skipped duplicate source patients: 8
• Skipped duplicate-name creates: 14
• Existing BP patients not matched by this CSV: 15

Work through these manually using the process in scripts/README.md:

• compare BP and Airtable by full name, mobile, email, and DOB if available in BP
• add BP patient ID manually when the existing Airtable record is clearly the same patient
• create a separate Airtable patient only when BP confirms the duplicate-name row is genuinely a different person
• merge/delete duplicate Airtable records only after confirming they are duplicates

Open Questions

• Do we want webhook notes to append to existing notes instead of replacing them on updates?
• Do we want to handle invitee.canceled as well, so stage/status can update on cancellations or reschedules?
• Do we want to keep CALENDLY_PAT in Netlify long-term for phone enrichment, or change the Calendly form to expose phone as a custom question instead?

Decided:

• Production and deploy previews can use the same behavior and configuration to make testing easier
• This means previews can safely use the same Airtable-backed lead-writing flow unless that changes later
• .env.example should stay committed because it contains placeholders only and documents required config

Useful URLs

• Calendly webhook docs: https://developer.calendly.com/receive-data-from-scheduled-events-in-real-time-with-webhook-subscriptions/
• Calendly webhook automation docs: https://developer.calendly.com/trigger-automations-with-other-apps-when-invitees-schedule-or-cancel-events
• Calendly UTM tracking docs: https://help.calendly.com/hc/en-us/articles/1500005575121-How-to-track-conversions-with-UTM-parameters
• Calendly reporting + UTM mention: https://developer.calendly.com/track-and-report-on-all-scheduled-events-across-your-organization