Pipedrive integration (quotes ↔ deals)

Audience: developers & AI agents · Scope: how ConfigBox quotes sync with Pipedrive deals — the flows, the foundational layer, and a map to the reference / ops / deploy / testing docs · Last reviewed: 2026-07-11

TL;DR: Every website quote can be mirrored as a Pipedrive deal. Data moves both ways: the website pushes the quote's content and status to Pipedrive as a single "update Pipedrive deal" job (it creates the deal if it doesn't exist yet, updates it otherwise), and Pipedrive pushes deal changes back via an inbound webhook. The two directions are kept in parity by a single field map (BcPipedriveConfig::webhookFieldMap()): every editable, quote-owned field the website pushes out (status, lost reason, agent-feedback status/lost-reason, native stage_id, Expected close date) is also read back in. A separate cold-quote flow nudges agents about stale deals. The integration used to split the outbound direction into two confusingly similar queues ("export" and "sync"); these are now one queue. A foundational layer (BcPipedriveConfig, BcPipedriveApi, BcQuotePipedriveGateway) centralizes config, API handling, and DB writes. Every field change in either direction is recorded to an always-on change-log table.

---

The docs

This folder is the Pipedrive integration layer — shared plumbing other systems build on (e.g. Quote Follow-Up). This page is the architecture hub; the rest:

Doc	What it covers
deal-updates.md	Outbound reference — which website actions push to a deal, and exactly which fields land (create vs update, derived values, special cases).
admin-manual.md	Admin & operations — setup, cron jobs, settings reference, day-to-day running, and a symptom→fix troubleshooting runbook.
deployment.md	Go-live runbook — the exact, ordered post-deploy steps (migrations → settings → setup CLI → cron → webhook → smoke test).
testing.md	Testing — the capture/guard gate, the org-6034 safety guard, the test-support endpoint, and the per-action test matrix.
api-access.md	Read-only REST lookups via the tools/pd helper (field keys, deal data, find-by-serial) for debugging.
api-v2-migration.md	API v1 → v2 migration (built 2026-07-29) — deals/persons/organizations/pipelines/stages/dealFields now default to v2 via the pipedrive_api_v2_groups setting (per-environment rollback, no deploy); what broke, how it's normalised inside BcPipedriveApi, and the test gates.

---

The flows

#	Flow	Direction	Trigger	Entry point	Core logic
A	Update Pipedrive deal	Website → PD	any quote content/status change sets pipedrive_update_scheduled=1; cron runs executeScheduledDealUpdates()	CLI cli/cb_pipedrive_update_deals.php	ConfigboxModelBcquotes::updatePipedriveDeal()
B	Inbound webhook	PD → Website	Pipedrive calls the endpoint on any deal change (ingress enqueues); cron drains the queue	ingress ConfigboxControllerBcpipedrive::updateQuote() → enqueueWebhook(); runner CLI cli/cb_pipedrive_process_webhook_queue.php	ConfigboxModelBcPipedrive::processWebhookQueue() → processDealDataChanges()
C	Cold-quote prompts	PD → email/sheet	cron	CLI cb_pipedrive_send_cold_quote_prompts.php, cb_pipedrive_update_cold_quote_sheet.php	ConfigboxModelBcPipedrive::sendColdQuotePrompts()
D	Setup / backfill	admin	manual CLI	cb_pipedrive_setup_integration.php, cb_pipedrive_redo_quote_data.php	ConfigboxModelBcPipedrive::setupIntegration(), redoPipedriveData()

The one outbound queue (Flow A)

There is a single boolean on the quotes table, pipedrive_update_scheduled, with one cron runner. Whether the trigger was a content change (positions, pricing, quantities, alt flags) or an agent-feedback / status change, the quote is flagged the same way (via ConfigboxModelBcquotes::scheduleDealUpdate() → BcQuotePipedriveGateway::scheduleUpdate()) and the runner sends one combined payload: title/value/person/org/custom fields and status/lost-reason/agent-feedback/OOT fields.

History: there used to be two flags — pipedrive_export_scheduled (full content push) and pipedrive_sync_scheduled (status-only push) — each with its own CLI runner (cb_pipedrive_export_scheduled.php, cb_pipedrive_sync_quote_data.php). They both just called updateADeal, so they were merged. The two old CLIs are now thin shims that delegate to the unified runner and log a deprecation notice; crontab should call cb_pipedrive_update_deals.php only.

Create vs. update nuance: updatePipedriveDeal() builds one payload, then looks the deal up (getPipedriveDeal). If the deal exists it calls updateADeal; otherwise addADeal. Pipedrive's POST /deals (add) rejects the lost_reason field that PUT /deals/{id} (update) accepts, so the add path unsets lost_reason — a freshly created deal carries its status, and the lost reason is filled in on the next update once the deal exists.

Bounded retry: a failed push increments pipedrive_update_attempts on the quote; the runner only picks up quotes under the cap (maxDealUpdateAttempts = 3, via COALESCE(pipedrive_update_attempts,0) < 3). After 3 consecutive failures the quote is left scheduled but no longer retried — until a new website change reschedules it, which resets the counter to 0 (scheduleUpdate). Success clears the flag and resets the counter (markUpdated). (Before this, a persistently failing quote was retried every minute forever.)

For a field-by-field reference of which website actions push to a deal and exactly which deal fields are written (create vs. update, derived values, special cases), see deal-updates.md.

The inbound webhook queue (Flow B)

The inbound webhook is queued, not processed inline. Pipedrive bulk actions fire a burst of webhook calls; processing each synchronously (quote matching, field translation, DB writes, sometimes an API call) used to overwhelm the app. So the responsibilities are split:

• Ingress — ConfigboxControllerBcpipedrive::updateQuote() does the bare minimum: authorise (HTTP Basic, see below), store the raw body via enqueueWebhook() (one INSERT into #__configbox_external_pipedrive_webhook_queue), return 200. No processing. A failed enqueue returns 500 so Pipedrive retries and the event isn't lost.
• Processing — the cron runner cli/cb_pipedrive_process_webhook_queue.php calls processWebhookQueue(), which drains pending rows oldest-first through processDealDataChanges(). Inbound changes are applied to the latest revision only: every revision of a serial shares the deal id (the backfill stamps the linkage on all of them so a revision rollback keeps its deal), but older revisions are historical snapshots — getQuotesByDealId() filters on is_latest_revision = 1, mirroring the outbound runner's filter.

Safeguards in processWebhookQueue():

• No overlapping runs — a MySQL advisory lock (GET_LOCK, no wait; name folds in the database name so environments sharing a server don't collide). If a previous run is still going (backlog taking longer than the 1-minute cron interval), the next run exits immediately.
• Bounded run — stops after maxItems (1000) or maxSeconds (50); the rest is picked up next minute, so one invocation never runs away and never straddles two cron ticks.
• Per-item isolation + bounded retry — success deletes the row (queue stays lean); a failure keeps the row with status='failed', attempts+1 and last_error. Failed rows are retried on later runs, up to maxWebhookQueueAttempts (3) total attempts, then left as failed for inspection. A row that fails in a run is not retried again until the next run (the eligible set is snapshotted once per run), so one poison payload can't spin the whole time budget. One bad item never aborts the run.

crontab should call cb_pipedrive_process_webhook_queue.php every minute. Without it, inbound webhooks accumulate in the queue unprocessed.

Webhook changes never echo back out

Applying an inbound change must not turn around and schedule an outbound push (that would bounce the change straight back to Pipedrive and risk a feedback loop). processDealDataChanges() sets BcQuotePipedriveGateway::setSuppressScheduling(true) for its duration (reset in a finally), and scheduleUpdate() is a no-op while suppressed. Today the inbound writes are direct SQL and don't schedule anyway, so this is a defensive guard that keeps the invariant if those writes are ever routed through the scheduling path (a roadmap item). Suppression is process-scoped, so it never affects the separate outbound-runner process.

Change log (always-on field-level audit)

Every field-level change that crosses the boundary — in either direction — is recorded to a DB table, #__configbox_external_pipedrive_change_log, by BcPipedriveChangeLog. This replaces the old opt-in, per-environment diagnostic-log toggles (pipedrive_diag_log_inbound/outbound) and their logInboundDiag() / logOutboundDiag() helpers, which wrote verbose free-text to separate log files: those are gone. The change log is always on, structured, and queryable.

Column	Meaning
logged_at	timestamp in UTC (gmdate), so in/out rows are directly comparable
direction	'in' (Pipedrive → quote) or 'out' (quote → Pipedrive deal)
quote_id, serial, deal_id	which quote / deal the change belongs to
field	human-facing field name (status, lost reason, Agent Feedback Status, stage, Expected close date, value, …)
old_value, new_value	the change (enum ids shown as their labels; NULL / empty when cleared)

Semantics:

• Inbound rows are written by processDealDataChanges() as it applies each changed deal field to the matched quote(s).
• Outbound rows are written by updatePipedriveDeal(), diffing the payload against the deal's current state — so re-sending the idempotent payload doesn't spam the log; only fields that actually change are recorded. A freshly created deal records its initial values (old_value = NULL).
• Recording never throws (a logging failure must not break the sync), and a no-op change (old == new, with NULL/'' treated as equal) writes nothing.

Reading it — the backend browser. Diagnostics → Pipedrive Change Log (ConfigboxModelAdminbcpipedrivechanges + views/adminbcpipedrivechange(s)/) is a read-only list over this table: filters for logged-date / direction / serial / deal id / field, newest-first, with a read-only detail view for the full old_value / new_value. Above the table sits a sync-health summary (getSyncHealth()) — in/out volume over 24h and 7d, distinct quotes touched, time since the last change each way, the outbound backlog (queued / retries exhausted / stalled, i.e. scheduled on a non-latest revision the runner can never select), linked deals, last successful push, and the inbound queue. See admin-manual.md §5.1.

Or query it directly, e.g. the recent history of one quote:

SELECT logged_at, direction, field, old_value, new_value
FROM e5xae_configbox_external_pipedrive_change_log
WHERE serial = 'BC-12345' ORDER BY logged_at DESC;

---

The foundational layer

Three plain (non-namespaced) classes in docroot/components/com_configbox/data/customization/system_overrides/. Like the other Bc* classes there, they are auto-loaded via ConfigboxOverridesHelper::loadOverrideFiles() — no use imports, no autoloader registration.

Class	Responsibility	Replaces
BcPipedriveConfig	One place for every key/ID/field-map/magic literal. Settings-backed values + the hard-coded constants that were scattered.	settings reads, code constants (pipedriveOrgIdDev, deal-type/has-alt field keys + option values), recipient emails
BcPipedriveApi	Thin client wrapper: memoized client, data()/serialize() unwrap + success-check, casing-safe pagination (getAllDealsByFilter()), handleApiException(), and since 2026-07-29 the API v1/v2 switch per endpoint group — v2 responses/payloads are normalised here (via BcPipedriveV2Client) so call sites never learn which version answered.	repeated ->jsonSerialize()->data / ->success boilerplate; the divergent pagination loops
BcQuotePipedriveGateway	The only owner of reads/writes to pipedrive_* quote columns. Explicit column list, named write methods, the single queue-flag setter (scheduleUpdate) and completion stamp (markUpdated).	~15 hand-written UPDATE … pipedrive_* blocks; the implicit "which columns are Pipedrive's"

Fixed along the way: the pagination bug

redoPipedriveData() (Flow D backfill) paginated by reading additionalData->pagination->nextStart (camelCase), but the API returns snake_case (additional_data->…->next_start). The while condition therefore read null and the loop ran once — the backfill silently processed only the first 500 deals. getDealsByFilter() used the correct snake_case, so the two disagreed. BcPipedriveApi::getAllDealsByFilter() reads both casings and is now used by redoPipedriveData(). getDealsByFilter() should be migrated to it too (see Roadmap).

---

Webhook security

The inbound endpoint (ConfigboxControllerBcpipedrive::updateQuote) previously had no authentication — KenedoController::isAuthorized() returns true for any non-admin controller, and nothing else checked credentials. Anyone who knew the URL could POST a payload and flip quote status by serial.

It now supports HTTP Basic auth (Pipedrive can send an HTTP Basic username/password with every webhook call):

• The controller's isAuthorized() reads PHP_AUTH_USER / PHP_AUTH_PW and compares them, constant-time (hash_equals), against two per-environment settings: pipedrive_webhook_auth_user and pipedrive_webhook_auth_password.
• Fail-open: if both settings are blank the endpoint stays open (its previous behaviour), so the change is non-breaking. Configure both to actually secure it. On a mismatch the controller returns 401 with a WWW-Authenticate: Basic challenge.
• setupIntegration() registers the webhook with Pipedrive's native httpAuthUser / httpAuthPassword parameters when they are set, so Pipedrive sends them on every call. The subscription URL itself stays clean (no ?secret= query string).

The auth password is stored in clear text on purpose — the controller must compare it against the plaintext password Pipedrive sends. Use a different value per environment so dev/staging credentials never authorise on live.

---

Configuration: where each value lives

Three storage strategies coexist (the layer hides this, but you should know it when configuring):

1. CB settings (per-environment row; edited in the backend ConfigBox settings, defined in models/adminbcsettings.php): pipedrive_api_token, pipedrive_webhook_auth_user, pipedrive_webhook_auth_password, pipedrive_field_key_quote_serial, pipedrive_field_key_agent_feedback_status, pipedrive_field_key_agent_feedback_lost_reason, pipedrive_field_key_project_phase (legacy, unused), pipedrive_filter_id_deals_with_quote_serial, pipedrive_filter_id_cold_quotes, pipedrive_field_name_is_oot, pipedrive_oot_option_yes/no. (The quote-stage-sync settings quote_stage_sync_sheet / pipedrive_probability_model_version used to be here; they moved to the Quote Follow-Up Landing Page group + its own extension table in 0.5.69 — see quote-follow-up/landing-page.md "Settings" and quote-follow-up/stage-sync.md. Likewise pipedrive_default_pipeline_id — the follow-up default pipeline — moved to the Quote Follow-ups group and its extension table in 0.5.79, joined there by the new pipedrive_default_stage_id; both are now required dropdowns over the imported pipelines/stages, see quote-follow-up/pipeline-stages.md "Default pipeline & stage".)

   Pipedrive settings grouping. These physically live in the extension table #__configbox_external_settings_pipedrive (one row per environment, keyed settings_id → #__configbox_external_settings.id), not in the wide #__configbox_external_settings table itself — each property carries a storeExternally definition so Kenedo joins the value back in transparently on read and upserts it on save (migration 0.5.56; done because the settings table hit MySQL's row-size limit). Keep any new Pipedrive-related setting in the "Pipedrive Integration" group and on this extension table (add the column to #__configbox_external_settings_pipedrive in a migration and put 'storeExternally'=>true + the foreignTable* keys on the prop def). The same pattern is now used for the Quote Follow-Up group (#__configbox_external_settings_quote_follow_up, migration 0.5.69); pick the group/table a setting belongs to by feature. Reading/writing through BcHelper::getCustomSettings() / the settings model is unchanged; only direct SQL against these columns must target the right extension table. (The former pipedrive_diag_log_inbound / pipedrive_diag_log_outbound toggles are removed — see Change log.)

2. Code constants in BcPipedriveConfig (collected from where they were inline): ORG_ID_DEV, FALLBACK_RSM_EMAIL, FIELD_KEY_DEAL_TYPE + DEAL_TYPE_*, FIELD_KEY_HAS_ALTERNATIVES + HAS_ALTERNATIVES_*, cold-quote notification recipients.

3. Literals inside a JSON string — the cold-quotes filter condition (getConditionJsonColdQuotes()) embeds field IDs 12457/12458/12535/12536 and org 56. Still raw; see Roadmap.

---

Deploying & running

Deployment and day-to-day operation live in their own docs, not here:

• Go-live: the ordered post-deploy runbook — migrations → settings → setup CLI → cron → webhook → smoke test — is deployment.md.
• Operations, settings & troubleshooting: admin-manual.md. Where each configured value lives is above.

---

Possible follow-ups

Open, independent improvements (each reduces coupling; none is blocking):

• Caller adoption — route the inbound-webhook and cold-quote flows onto the foundational layer (BcQuotePipedriveGateway / BcPipedriveApi), replacing their remaining inline SQL/clients. If the webhook writes are ever routed through applyChanges(), widen its allow-list first — it currently omits the landing-page stage/date columns and would silently drop them.
• Promote literals to settings — move the BcPipedriveConfig constants and JSON-blob filter IDs into environment-specific settings columns, so dev/staging/live differ without code edits.
• Extract flow classes — split deal-update / webhook / cold-quote / setup out of the two god-models into dedicated services, leaving deprecating shims behind.

---

Gotchas / things people get wrong

• One queue, combined payload — every scheduled update re-sends the full content and status. Don't reintroduce a "status-only" path; if you only changed status, scheduling an update is enough.
• lost_reason on create — addADeal (POST) rejects lost_reason; only updateADeal (PUT) accepts it. updatePipedriveDeal() unsets it on the add path — keep that.
• Field keys vs. field IDs — Pipedrive custom fields have an opaque hash key (used in the API body) and a numeric id (used in filters). They are not interchangeable. Keys are stored in settings by setup; never hard-code them.
• Non-live environments — outside live, all deal/person writes are refused unless they target the dev/test org (BcPipedriveConfig::ORG_ID_DEV = 6034). This is enforced centrally in the BcPipedriveApi write wrappers (addDeal/updateDeal/deleteDeal/addPerson), not inline in updatePipedriveDeal() anymore, and it is stricter than the old inline check. It is always on (not a mode). Don't remove or bypass it. See testing.md.
• Webhook is fail-open — blank pipedrive_webhook_auth_user/password means no auth. Verify both are set on live after deploying, and re-run setup so Pipedrive sends them.
• Inbound webhook is queued, not inline — the endpoint only enqueues; nothing happens until the cb_pipedrive_process_webhook_queue.php cron runs. If quotes stop reflecting Pipedrive changes, check that cron entry first (and the _webhook_queue table for status='failed' rows). Don't move processing back into the controller — inline processing is exactly what a bulk action overwhelmed.
• Retries are bounded (3), both directions — inbound queue rows and outbound pushes each retry up to 3 times, then stop (inbound: row stays failed; outbound: quote stays scheduled but is skipped). A row/quote that reads "stuck" has usually exhausted its retries — check last_error (inbound) or the export log (outbound). An outbound quote un-sticks when a new website change reschedules it (counter resets); an inbound row needs manual attention (flip status back to pending, or fix + re-send from Pipedrive).
• The change log is always on and diffed — there are no diagnostic-log toggles anymore. To see what synced, query #__configbox_external_pipedrive_change_log (UTC times, direction, field, old_value/new_value). Outbound rows appear only for fields that actually differed from the deal, so an empty result for a push means "nothing changed on the deal", not "it didn't run".
• Two-way parity comes from one map — BcPipedriveConfig::webhookFieldMap() is the single source of truth for which deal fields flow back to the quote. If you add an editable, quote-owned field to the outbound payload, add it here too (with any id↔label / label↔id translation in buildInboundColumnChanges()), or it will push out but never come back. Fields derived from the quote (value, org, person, title, deal type, alternatives, OOT) are intentionally not in the map — writing them back would clobber quote-derived data.
• Webhooks v2 only, and it nests custom fields — configure the Pipedrive webhook as v2. In v2 the changed object is under data, the entity is meta.entity, actions are create/change/delete, and custom fields live under data.custom_fields.<key> as {id:…} (options) or {value:…} (text/date). Our serial and Agent Feedback Status are custom fields (the project stage is now the native stage_id, which is already top-level), so a raw v2 payload matches no quote on the custom fields. normalizeWebhookPayload() flattens it up front — do all matching on the normalized current/previous, never on the raw body. In v2 previous holds only the changed fields, so "present in previous" is the change signal (don't infer changes by diffing current against a partial previous). A v1 payload has no meta.entity/data, so it just won't match — it's silently ignored, not supported.
• Backfill is destructive-then-rebuild — redoPipedriveData() first NULLs all pipedrive_* columns, then repopulates from Pipedrive. Run it deliberately, not casually.
• Notes are append-only, not idempotent — the deal-field payload is safe to re-send every run, but a Note is not. The Quote Follow-Up comment is therefore queued in pipedrive_agent_feedback_comment_pending and posted once (then cleared), not included in the payload. Don't move comment-posting into the payload builder or you'll spam duplicate notes on every re-flag. See deal-updates.md.
• Local-only landing-page columns aren't in PIPEDRIVE_COLUMNS — pipedrive_stage_id / _agent_feedback_completion_date / _comments / _updated_on are deliberately not in the gateway's PIPEDRIVE_COLUMNS list, because redoPipedriveData() NULLs everything in that list and these hold data Pipedrive can't repopulate. They're pushed outbound (stage → native stage_id, date as a field, comment as a note) but never cleared by the backfill.

Related docs

• The quote — the object being mirrored. The pipedrive_* columns live on it, and its lifecycle doc covers the "agents only suggest" guard and why the follow-up landing page deliberately bypasses it.
• Scheduled jobs — the full cron inventory, including the outbound runner and the webhook-queue drainer. Monitoring — what watches them (note the Pipedrive metrics land under a namespace named Test1-Pipedrive-Sync).

Key files

• Controller (webhook ingress only — enqueues, no processing): data/customization/controllers/bcpipedrive.php
• Models: data/customization/models/bcpipedrive.php (webhook queue enqueueWebhook / processWebhookQueue; inbound apply processDealDataChanges → normalizeWebhookPayload (v2) → getRelevantChanges → buildInboundColumnChanges; import/cold-quote/setup), data/customization/models/bcquotes.php (outbound deal update; Pipedrive methods around scheduleDealUpdate, executeScheduledDealUpdates, updatePipedriveDeal / recordOutboundDealChanges, getPipedriveDeal)
• Quote Follow-Up landing page (another Flow A producer — status/lost-reason sync, three new local-only fields): data/customization/controllers/bcquotelandingpage.php, models/bcquotelandingpage.php — mapping in deal-updates.md, feature in quote-follow-up/landing-page.md
• Foundational layer: data/customization/system_overrides/BcPipedriveConfig.php (incl. fieldKeyProjectPhase() and the two-way webhookFieldMap()), BcPipedriveApi.php (one wrapper per API op — incl. addActivity() (the follow-up timeline entry; replaced addNote()); every write enforces the org-6034 guard + capture mode inline), BcQuotePipedriveGateway.php (incl. queueCommentNote() / clearPendingCommentNote())
• Change log: data/customization/system_overrides/BcPipedriveChangeLog.php (writer for #__configbox_external_pipedrive_change_log; recordInbound() / recordOutbound())
• Testing support: system_overrides/BcPipedriveTestResponse.php (synthetic captured-write response), BcPipedrivePolicyException.php (thrown by the guard), cli/cb_pipedrive_test_support.php — see testing.md
• Value objects: system_overrides/BcPipedriveDeal.php, BcColdQuotePrompt(Deal).php, BcPipedriveHistoryItem.php
• CLI entry points: cli/cb_pipedrive_update_deals.php (unified outbound runner), cli/cb_pipedrive_process_webhook_queue.php (inbound webhook-queue drainer), cli/cb_pipedrive_import_stages.php (import the follow-up pipeline stages), other cli/cb_pipedrive_*.php — see Scheduled jobs for the full list
• Settings field defs: data/customization/models/adminbcsettings.php
• Migrations: data/customization/updates/0.5.48.php (webhook auth), updates/0.5.49.php (unified queue), updates/0.5.50.php (Project Phase field key + comment-note queue column), updates/0.5.51.php (inbound webhook queue table), updates/0.5.52.php (outbound retry counter + diagnostic-logging toggles), updates/0.5.55.php (change-log table; drops the diagnostic-logging toggles), updates/0.5.63.php (pipeline-id setting), updates/0.5.64.php (imported-stages table), updates/0.5.65.php (native pipedrive_stage_id quote column)
• Follow-up stage entity: data/customization/models/adminbcpipedrivestages.php (+ controller/views; importStages()), embedded in the Pipedrive settings group; consumed by models/bcquotelandingpage.php