FreeScout Pipeline

Syncs Rondo Club member data to FreeScout helpdesk as customers, enriching support tickets with member context. Also downloads FreeScout conversations and creates activities in Rondo Club.

Schedule

Runs daily at 8:00 AM (Amsterdam time).

scripts/sync.sh freescout           # Production (with locking + email report)
node pipelines/sync-freescout.js --verbose    # Direct execution (verbose)

Pipeline Flow

pipelines/sync-freescout.js
├── Check credentials (FREESCOUT_API_KEY + FREESCOUT_URL)
├── steps/submit-freescout-sync.js
│   ├── steps/prepare-freescout-customers.js   → data/freescout-sync.sqlite
│   └── Submit to FreeScout API          → FreeScout customers
└── Conversations pipeline
    ├── steps/download-freescout-conversations.js  → data/freescout-sync.sqlite
    ├── steps/prepare-freescout-conversations.js   → activity payloads
    └── steps/submit-freescout-activities.js       → Rondo Club activities

Customer Sync

Credential Check

Before running, pipelines/sync-freescout.js verifies that FREESCOUT_API_KEY and FREESCOUT_URL are configured in .env. If not, the pipeline exits with an error.

Customer Preparation

Script: steps/prepare-freescout-customers.js (called internally by steps/submit-freescout-sync.js)

1. Reads member data from data/rondo-sync.sqlite → rondo_club_members
2. Reads team assignments from data/rondo-sync.sqlite → rondo_club_work_history
3. Reads contribution data from data/nikki-sync.sqlite → nikki_contributions
4. Builds customer records with:
   • Name, email, phone from Rondo Club member data
   • Photo URL from member data
   • Website URLs from contact info
   • Team memberships (comma-separated)
   • KNVB ID, member since date
   • Latest Nikki contribution balance and status
5. Computes source_hash per customer
6. Upserts into data/freescout-sync.sqlite → freescout_customers

Customer Submit

Script: steps/submit-freescout-sync.js Function: runSubmit({ logger, verbose, force })

1. Reads customers from data/freescout-sync.sqlite where source_hash != last_synced_hash
2. For each changed customer:
   • No freescout_id: POST /api/customers (create new customer)
   • Has freescout_id: PUT /api/customers/{freescout_id} (update existing)
3. After creating/updating, syncs custom fields via PUT /api/customers/{id}/customer_fields
4. Stores returned FreeScout customer ID as freescout_id
5. Updates last_synced_hash on success
6. Rate limited: exponential backoff on 5xx errors (1s, 2s, 4s)

Output: { total, synced, created, updated, skipped, deleted, errors }

Field Mappings

Standard Customer Fields

Sent to POST/PUT /api/customers:

FreeScout Field	Source	Origin
firstName	acf.first_name	rondo_club_members.data_json
lastName	acf.last_name	rondo_club_members.data_json
emails[].value	Email from contact_info repeater	rondo_club_members.data_json
phones[].value	Mobile from contact_info repeater	rondo_club_members.data_json
photoUrl	Photo URL	rondo_club_members.data_json
websites[].value	Website URLs from contact_info repeater	rondo_club_members.data_json

Custom Fields

Sent to PUT /api/customers/{id}/customer_fields:

FreeScout Custom Field	Field ID	Source	Origin
union_teams	1	All current team names, comma-separated	rondo_club_work_history
public_person_id	4	KNVB ID	rondo_club_members
member_since	5	acf['lid-sinds']	rondo_club_members
nikki_saldo	7	Most recent year’s outstanding balance	nikki_contributions
nikki_status	8	Most recent year’s payment status	nikki_contributions

Field IDs are configurable via FREESCOUT_FIELD_* environment variables.

RelationEnd Field

The RelationEnd field from Sportlink member functions is included in FreeScout customer sync data. This tracks when a member’s club-level function ended (e.g., when they stopped being “Voorzitter”).

Conversations Pipeline

The conversations pipeline downloads conversations from FreeScout and creates corresponding activities in Rondo Club, providing a unified timeline of member interactions.

Flow

1. Download - Fetches conversations from FreeScout API
2. Prepare - Matches conversations to Rondo Club persons via email/customer ID
3. Submit - Creates activities on person records in Rondo Club

Tracking

Conversations are tracked in data/freescout-sync.sqlite → freescout_conversations table to avoid duplicate activity creation. Each conversation is stored with its FreeScout ID and sync state.

Integration

The conversations pipeline is:

• Integrated into the main FreeScout pipeline orchestrator
• Runs as part of the daily cron schedule
• Visible on the sync dashboard

Database Tables Used

Database	Table	Usage
rondo-sync.sqlite	rondo_club_members	Member data (name, contact, KNVB ID)
rondo-sync.sqlite	rondo_club_work_history	Current team assignments
nikki-sync.sqlite	nikki_contributions	Financial contribution data
freescout-sync.sqlite	freescout_customers	Customer → FreeScout ID mapping + hashes
freescout-sync.sqlite	freescout_conversations	Conversation tracking for activity sync

CLI Flags

Flag	Effect
--verbose	Detailed per-customer logging
--force	Skip change detection, sync all customers

Error Handling

• Missing credentials cause immediate exit (not a silent skip)
• Individual customer sync failures don’t stop the pipeline
• 5xx errors trigger exponential backoff (up to 3 retries)
• Conversation sync failures are tracked independently
• All errors collected in summary report

Source Files

File	Purpose
pipelines/sync-freescout.js	Pipeline orchestrator (customers + conversations)
steps/submit-freescout-sync.js	FreeScout API sync + customer preparation
steps/prepare-freescout-customers.js	Customer data preparation
steps/download-freescout-conversations.js	Download conversations from FreeScout
steps/prepare-freescout-conversations.js	Match conversations to persons
steps/submit-freescout-activities.js	Create activities in Rondo Club
lib/freescout-db.js	FreeScout SQLite operations (customers + conversations)
lib/freescout-client.js	FreeScout HTTP client + credential check
lib/rondo-club-db.js	Rondo Club data lookup
lib/nikki-db.js	Nikki contribution lookup
lib/http-client.js	HTTP request utilities