All articles

GHL form submissions → ClickHouse

Server-side pull from HighLevel Forms API using a Private Integration Token (no OAuth app flow). Data lands in ghl_form_submissions only; uts_events is never deleted or overwritten.

Tables

  • ghl_form_submissions — one row per submission (submission_id + location_id), raw JSON, normalized email/phone, optional matched_uts_event_id pointer to uts_events.id.
  • ghl_sync_watermark — last run stats per location (ReplacingMergeTree).

Apply DDL under server/migrations/ghl-form-submissions.sql and server/migrations/ghl-sync-watermark.sql, or run server/migrate.js against ClickHouse.

Configuration

Recommended: open Dashboard → GHL leads (/ghl-leads). Enter each sub-account’s Location ID, site domain, and Private Integration Token (Forms scope), then Save credentials and Sync from GHL. Values persist to server/data/ghl_locations.json (Docker: mount ./server/data → /app/data).

Alternatively set GHL_LOCATIONS_JSON to an array of { "locationId", "siteDomain", "privateIntegrationToken" } for each sub-account (overrides the file when non-empty). Other options: GHL_LOCATIONS_JSON_FILE, GHL_LOCATIONS_JSON_B64, or GHL_LOCATION_STORE_PATH to move the dashboard file.

Optional: GHL_MATCH_WINDOW_HOURS (default 48) — window around submission time when matching to uts_events.

Admin API

Requires Authorization: Bearer <ADMIN_MASTER_KEY> (same as other /admin/* routes).

  • GET /admin/ghl/locations-config — masked tokens + storage path (no secrets in full).

  • POST /admin/ghl/locations-config — body { "locations": [ { "locationId", "siteDomain", "privateIntegrationToken" } ] }. Use "__KEEP_EXISTING__" or omit token to keep the prior key for that locationId.

  • GET /admin/ghl/activity-log — recent save/sync events (in-memory).

  • POST /admin/ghl/sync-form-submissions
    Body (JSON, optional): startDate, endDate, formId, locationId (omit to sync all configured locations), rematchOnly (boolean — skip GHL fetch; only run matcher on rows with no match).

  • GET /admin/ghl/sync-form-submissions/status — recent watermark rows.

Matching order

For each GHL row without matched_uts_event_id, the engine tries, within the time window and site_domain:

  1. email — lead_email
  2. phone — last 10 digits of lead_phone
  3. gclid — from GHL page URL vs uts_events.gclid
  4. ip — weak; only if submitter IP exists on the payload and equals uts_events.ip_address

Only ghl_form_submissions match columns are updated; uts_events payloads are unchanged.

Related