USE_CASE_ID: crm_pull_enrich_update NAME: CRM pull, enrich, and update in place (multi-source) CATEGORY: CRM hygiene The user wants to pull a slice of records (contacts, companies) from a CRM into a Floqer workflow, run an enrichment / scoring / writing step per row, and write the enriched value back to the SAME source record. End state: a CRM property gets populated or refreshed, the record IDs never leave the source system. Supports HubSpot today; Salesforce and Pipedrive are scaffolded below but not yet fleshed out — wire them up before claiming the use case. For routing, file structure, and cross-cutting principles, see https://floqer.com/docs/use-case-catalog.txt. This file does not repeat them. INDEX: 1. When to use / when not to use 2. Inputs and pre-flight clarifications 3. Outputs 4. Workflow design 5. Implementation 5.1 HubSpot (verified end-to-end) 5.2 Salesforce (scaffold — not yet documented) 5.3 Pipedrive (placeholder — not yet exposed via Floqer API) 6. Best practices 7. Common variations 8. Failure modes and mitigations 9. Related use cases ================================================================================ 1. WHEN TO USE / WHEN NOT TO USE ================================================================================ USE WHEN: - The user has a CRM and wants to enrich / score / annotate / refresh a property on a defined slice of records (a list, saved view, filter expression), then write the result back to the same records. - The enrichment is computable per row in Floqer — e.g. a JS formatter that normalises a phone, an LLM-written note based on existing fields, a score derived from firmographics. - The user is OK with a one-time or scheduled batch — not a real-time webhook-on-write. DO NOT USE WHEN: - The user wants to CREATE new CRM records from external data (e.g. push enriched leads from a CSV into HubSpot for the first time). Use `hubspot_create_object` or `hubspot_upsert_object` driven by the external data source instead — no source-based pull needed. - The user wants to MOVE records between Floqer and a different destination (sub-sheet, sequencer, spreadsheet). Use `extract_data_from_crm` for snapshots and `push_data_to_sheet` for fan-out instead. - The user only wants to compute a score in Floqer for inspection — without writing back. Drop the writeback action from this chain or use `account_research_and_scoring`. - The user wants to monitor for CHANGES on the CRM side and react. Use `crm_contact_change_detection` instead — it's designed around re-scraping + diffing, not push-back. ================================================================================ 2. INPUTS AND PRE-FLIGHT CLARIFICATIONS ================================================================================ Before designing the workflow, confirm with the user: - Which CRM (HubSpot today; Salesforce / Pipedrive scaffolded) - How the slice of records is selected: • A specific list (HubSpot) / view (Salesforce) • A property filter expression (HubSpot native_filters, Salesforce SOQL-style) - The record ID property to use as the upsert / update key: • HubSpot: `hs_object_id` is the canonical numeric record ID; the source pushes it under the field name `uid` on each row (see §5.1). • Salesforce: `Id` / external-id field - The TARGET property to write to. Get the user to NAME the property explicitly — don't infer from the enrichment description. The chain succeeds only if the chosen property is writable for the API key's connection. - One-time pull or scheduled re-pull? (Sets `list_type` to `static` or `active`.) - Row cap. For a first run, default to 3–5 rows. Source create accepts `max_count`; never run wide on first build. Pre-flight existence checks (one HTTP call each): - Confirm CRM is connected (HubSpot lists call → 200; not connected → 424). - Confirm the target property exists in the writable property catalogue. For HubSpot: POST /api/v1/workflows/{wid}/sheets/{wid}/actions/{updater_aid}/options/hubspot_object_properties Body: { "context": { "hubspot_object": "contacts" } } Grep the response for the property name the user named. If absent, stop and clarify — don't pick a near-name. ================================================================================ 3. OUTPUTS ================================================================================ CRM-side (what changed in the source system): - On each updated record: the chosen target property now carries the enriched value. For HubSpot, the action returns the full properties object on `hubspot_update_object.properties` so you can confirm the write landed. Floqer-side (rows on the workflow sheet): - One row per pulled CRM record, with the inputs from the field mapping plus the enrichment cell(s) and the writeback cell. - Per-cell status on the writeback action is the load-bearing success signal — use that, not `row_status`, when reporting "did N writes land?" (see C3 stickiness note in §8). ================================================================================ 4. WORKFLOW DESIGN ================================================================================ Chain (linear, three real actions): → format_data_using_js_expression (or llm_models / web agent chain — the enrichment) → hubspot_update_object (or salesforce_update_record / pipedrive_*_update when added) The source is NOT an action — it lives outside the chain. It creates rows on the workflow's sheet via the sync mechanism (POST /sources/{src_id}/sync). The first action in the chain runs against those rows as they arrive. Why this shape — not a `*_lookup_object` chain: - Lookup actions return records as a structured_array INSIDE one cell on one row. You'd then need a transformer + push to a sub-sheet to get one record per row. That's the `extract_data_from_crm` pattern — better for snapshots that don't push back. - Sources push directly to ROWS on the workflow's main sheet, so each CRM record gets its own row from the start. Update actions running off that sheet hit one record per row naturally. Workflow inputs: - One per field you need to reference downstream. At minimum: the record ID (for the writeback key) and any field the enrichment reads. - Declare types as metadata only — they are NOT enforced at row ingest. Numeric IDs from CRMs come through as strings; that's fine. ================================================================================ 5. IMPLEMENTATION ================================================================================ 5.1 — HUBSPOT (verified end-to-end 2026-05-26) ---------------------------------------------- End-to-end recipe with the exact bodies that worked. Replace placeholder IDs with the ones returned by each step. Step 1 — Create the workflow. POST /api/v1/workflows Body: {"name": "HubSpot enrich-in-place"} → returns workflow_id (also the main sheet_id). Step 2 — Add inputs to the main sheet. Decide which CRM fields you need downstream. For a contact pull with a writeback keyed on the HubSpot record ID: POST /api/v1/workflows/{wid}/sheets/{wid}/inputs Body: [ {"name": "email", "type": "email"}, {"name": "hs_object_id", "type": "string"}, {"name": "firstname", "type": "string"} ] The `hs_object_id` input is what you'll feed back into `hubspot_update_object.hubspot_object_id` to address the record to update. Step 3 — Verify the HubSpot connection and pick a list. POST /api/v1/sources/hubspot_contact_added/options/hubspot_lists Body: {} → returns the user's HubSpot list catalogue. Pick the list whose contacts you want to enrich. Caveat: each option's `extras.listType` field is misleading on current API — it returns HubSpot's internal objectTypeId (`"0-1"` = contacts) and NOT a static-vs-active indicator. Confirm static/active with the user from the list name or by asking; don't read it off this field. Step 4 — Create the source. POST /api/v1/sources/hubspot_contact_added Body: { "name": "Enrich-in-place 2026-05-26", "import_mode": "from_list", "object_type": "contacts", "lists": [""], "max_count": 3, "list_type": "static", "pull_existing": true } → returns {source_id, name, created_at}. Hard rule on first build: max_count ≤ 5. Each row will trigger the full enrichment + writeback chain at run time, including any paid action. Scale up only after you've confirmed the writeback landed correctly in HubSpot. Source preview (`POST /sources/hubspot_contact_added/preview`) returns each field as `{label, value}` — wrapped display metadata, capped at 100 rows regardless of max_count. The actual sync flattens to raw values; build your `field_mapping` against the bare field names you see as the keys in preview records (e.g. `"email"`, not `"email.value"`). Step 5 — Sync the source to the workflow. POST /api/v1/sources/{source_id}/sync Body: { "workflow_id": "", "field_mapping": { "input.email": "email", "input.hs_object_id": "uid", "input.firstname": "firstname" }, "push_existing": true, "run": "none" } Field-mapping notes: - Keys are workflow input references WITHOUT braces, exactly as returned by List Inputs (`{{input.email}}` → `"input.email"`). - The HubSpot record ID arrives on each record under the top-level field name `uid` (not `hs_object_id`), so map `"input.hs_object_id": "uid"`. - `run: "none"` loads the rows without running the chain yet — useful when you want to inspect the imported rows before burning credits. - `run: "first_10"` / `"all"` run the chain immediately on the backfill. Rows arrive within ~5–15 seconds of the sync call. List rows (`POST /sheets/{wid}/rows/list` with `{}`) until you see them. Step 6 — Add the enrichment action. POST /api/v1/workflows/{wid}/sheets/{wid}/actions/add Body: {"action_id": "format_data_using_js_expression", "name": "Enrichment"} → returns the new `action_instance_id` (call it ``). PATCH /api/v1/workflows/{wid}/sheets/{wid}/actions/{fmt_aid} Body: { "inputs": { "data_formatter": "return \"Floqer enriched \" + new Date().toISOString().slice(0,10) + \" for \" + \"{{input.firstname}}\";" } } Two recurring formatter gotchas to mind: - Wrap string-typed variable refs with `"..."`, not backticks. Backticks bake literal JSON quotes into the resulting string (`"\"X\""` instead of `"X"`). - Formatter output is typed `string` regardless of what the JS returns. Don't gate `filter` / `run_if` with numeric operators on this output — use string sentinels (`"yes"`/`"no"` + `is`). Substitute any other enrichment shape here as needed — `llm_models` for writing a note (use `output_format: {field: "string"}`; the `"json"` type currently crashes the cell), `llm_web_agents` for research, a chain of both for research-then-write. Step 7 — Add the HubSpot updater. POST /api/v1/workflows/{wid}/sheets/{wid}/actions/add Body: {"action_id": "hubspot_update_object", "name": "Write enriched value to HubSpot"} → returns ``. The add response may omit `hubspot_object_id` from its `inputs[]` array — `GET` the action immediately after if you want to see the full input shape, OR refer to the hubspot_update_object action-detail file. The field is required to address the record to update. Step 8 — Resolve writable property options and configure the updater. POST /api/v1/workflows/{wid}/sheets/{wid}/actions/{upd_aid}/options/hubspot_object_properties Body: {"context": {"hubspot_object": "contacts"}} → returns ~400 writable properties for contacts. Find the one the user named as the target. Common safe-for-test fields: `hs_cross_account_note` (textarea, "Cross-Account Note"), `hs_content_membership_notes` (textarea, "Membership Notes"). Note on context key: this action-level options call wants `{"hubspot_object": "contacts"}`. The corresponding source-level options call wants `{"object_type": "contacts"}`. Mirror what the action-detail file shows, not the source-detail file (the two diverge on this key). PATCH /api/v1/workflows/{wid}/sheets/{wid}/actions/{upd_aid} Body: { "inputs": { "choose_hubspot_object": "contacts", "hubspot_object_id": "{{input.hs_object_id}}", "hubspot_object_properties": [ { "name": "hs_cross_account_note", "value": "{{.formatted_data}}" } ] } } On readback (GET), the engine auto-injects `label: ` on each property entry — `{name, label, value}` where `label === name`. Harmless, just don't expect byte-equal round-tripping. Associating a contact with a company on UPDATE: include `associatedcompanyid` in `hubspot_object_properties` with the target Company's HubSpot record ID as the value. Same shortcut works for `hubspot_create_object` and `hubspot_upsert_object` (only the PRIMARY contact↔company link; other associations still need the associations API). Step 9 — Run. POST /api/v1/workflows/{wid}/sheets/{wid}/run-all Body: {} Or target specific rows with `POST /sheets/{wid}/run` and `{"row_ids": ["", ...]}` — first run, prefer this so you can point at the 3 imported rows explicitly. Step 10 — Verify the write landed. POST /api/v1/workflows/{wid}/sheets/{wid}/rows/list Body: {} → for each row, inspect `cells[""].status` (should be `complete`) and `cells[""].outputs.properties[]` (should match the enrichment value). HubSpot itself: open the contact record by `hs_object_id` in the HubSpot UI and confirm the property is set. Don't skip the UI check on first run — `cells..status: complete` means Floqer's API call returned 2xx, not necessarily that HubSpot persisted the value as you'd visually expect. 5.2 — SALESFORCE (scaffold — not yet documented) ------------------------------------------------ The pattern is conceptually identical: → enrichment → salesforce_update_record (or salesforce_upsert_record) Open questions before this section can be written: - Does Floqer expose a `salesforce_*` source equivalent to `hubspot_contact_added` that pushes Salesforce records into a workflow as rows? If yes, document its create / sync body shape and field-mapping rules here. If no, document the workaround: `salesforce_lookup_record` → JS slim → r_to_s → push_data_to_sheet to fan one row per record into a sub-sheet, then run the update from that sub-sheet. - Confirm whether `salesforce_update_record` and `salesforce_upsert_record` are both exposed. If only one, note which. - For the upsert variant: which external-ID field strategies are practical (the same gotcha as the in-house notes — the standard `Email` field usually isn't flagged as External ID, so users typically need a custom `email__c` field flagged External ID, OR a manual upsert path: `salesforce_lookup_record` → `workflow_if_else` → `salesforce_create_record` / `salesforce_update_record`). Until those are answered, do not point users at this section as implementation guidance. Verify the chain end-to-end on a real Salesforce sandbox and replace this scaffold with concrete bodies. 5.3 — PIPEDRIVE (placeholder — not yet exposed via Floqer API) --------------------------------------------------------------- Pipedrive is not currently exposed via Floqer's source or action API. There is no `pipedrive_*_source`, no `pipedrive_lookup_*`, no `pipedrive_update_*`. If the user asks for this: - Confirm with them which Pipedrive object (Person, Deal, Organization) and which property they'd write to. Capture the requirement. - Offer the workaround: `http_api_call` against Pipedrive's REST API for both pull and update. The user supplies the Pipedrive API token; the chain becomes: http_api_call (GET /persons or /persons/search) → raw_to_structured_array → push_data_to_sheet → → http_api_call (PUT /persons/{id}) - Flag that this loses the native ergonomics (typed property options, OAuth, retry handling) the first-party HubSpot / Salesforce actions provide. Until Pipedrive is a first-class Floqer integration, this section is a placeholder. Don't write it as if it were. ================================================================================ 6. BEST PRACTICES ================================================================================ - **Always test on ≤5 rows first.** Set `max_count` on source create, not `run: "all"` on sync. Verify the writeback landed in the CRM UI before scaling up. A misconfigured updater can overwrite a load-bearing property on every record in a 10k-row list. - **Pick the target property explicitly with the user.** Don't guess from the enrichment description. Resolve the property options call, grep for the user's named field, confirm match, then PATCH. - **Avoid load-bearing CRM properties on test runs.** For dry runs, prefer a notes / text-area field (`hs_cross_account_note`, `hs_content_membership_notes`) over `lifecyclestage`, `email`, `firstname`, `phone`, status fields, or anything that triggers a workflow on the CRM side. - **Verify the writeback cell's status, not the row status.** `row_status` aggregates with `has_failures` stickiness — once any cell on the row failed, the row stays `has_failures` even if the writeback succeeded. The truth is in `cells[].status`. - **Surface the link to the user with the right view.** After a run, link to the Data view (`action=table&sheetId=`) so they can scan per-row results. After a chain change, link to the Floq view (`action=build`). Always include `sheetId`. - **Schedule = `list_type: "active"` + `schedule` cron.** For a recurring enrichment loop, set `list_type: "active"` on source create and pass a cron in `schedule`. Each scheduled re-import brings the current contents of the list into the workflow and re-runs the chain. Pair with cell-level caching considerations if the enrichment is expensive — re-runs against unchanged upstream may or may not hit cache depending on action. ================================================================================ 7. COMMON VARIATIONS ================================================================================ - **Enrich, but only write back for rows that meet a condition.** Insert a `filter` between the enrichment and the updater. Gate on the enrichment output (e.g. `is "yes"` if the formatter emitted a sentinel). Use the in-chain filter operator dialect (`is`, `is not`, `contains`, `greater than`, etc.) — NOT the rows/list dialect (`is equal to`, `not equal to`, ...). Wrong dialect silently accepted, every row becomes `condition_not_met`. - **Write to multiple properties at once.** `hubspot_object_properties` accepts an array of `{name, value}` entries — extend the array with one entry per target property. All updates land in a single HubSpot API call. - **LLM-written note instead of JS-derived value.** Swap the formatter for `llm_models` with `output_format: {note: "string"}` and pipe `{{.note}}` into the updater's value. Avoid the `"json"` output type — it crashes the cell with a cryptic null error today; use `"string"` and return JSON in the field if you need structure. - **Web research → write note.** Chain `llm_web_agents` → `llm_models` → `hubspot_update_object`. Web agent does research with citations; LLM writes the CRM-targeted note; updater writes it back. Never let the web agent author the final note directly — its self-reported output drifts (research vs writing are separate jobs). - **Source-less variant: enrich a known-ID CSV.** When the user already has a CSV of CRM record IDs (e.g. from an export, a separate tool's output), skip the source step. Add rows via `POST /rows` directly with the CSV fields as inputs, then run the enrichment + updater chain as in §5.1. ================================================================================ 8. FAILURE MODES AND MITIGATIONS ================================================================================ - **Updater cell fails with auth error.** The HubSpot connection must live on the API key's user account. If the connection was set up by a different team member, the Get Action Field Options call returns 424 and the updater's run-time call fails. Provision the API key from the user who owns the connection. - **Updater cell completes but the HubSpot property doesn't change in the UI.** Likely causes: (a) wrong property name (HubSpot accepts unknown names silently in some cases — verify against the options call), (b) the value is the same as what's already there (no observable diff), (c) the property is a calculated / read-only field disguised as writable. Inspect the `hubspot_update_object.properties` output to see what HubSpot echoed back. - **Row arrives without the field you expected.** Re-check the `field_mapping` — keys are case-sensitive, values must match a field name actually present in the source preview. For HubSpot contact record IDs, the field name is `uid` (not `hs_object_id`); writing `"input.hs_object_id": "hs_object_id"` silently leaves the input empty. - **`row_status: has_failures` even though writeback succeeded.** Sticky once set — earlier cell in the chain failed (often a formatter throwing an error, or a previous action's auth issue). Per-cell status on the updater is the truth. - **Source sync looks successful but no rows appear.** The list may genuinely be empty matching the filter, or `pull_existing` may have been false. Re-check the source create body. Also wait a full 15s — async import is slower than the call returns. - **Enrichment value contains characters HubSpot rejects.** Most free-text fields are forgiving; enumeration / dropdown properties reject anything not in the option list. If the enrichment is for an enum-typed property, sentinel the JS output to a fixed list and verify against `hubspot_object_properties.extras.options` for the target property before running wide. ================================================================================ 9. RELATED USE CASES ================================================================================ - `extract_data_from_crm` — for a SNAPSHOT of CRM records into a Floqer sub-sheet (no writeback). Use when the goal is analysis, not in-place enrichment. - `crm_contact_change_detection` — for detecting drift on the CRM side (still at company / title changes) via LinkedIn re-scrape. Use when the goal is monitoring, not enrichment. - `account_research_and_scoring` — for scoring + tiering accounts on an in-Floqer table (not writing back to the CRM). Pair with this use case if you want to score AND push the score back to a CRM property. ================================================================================ This file is maintained manually. Last updated: 2026-05-26. Full interactive reference: https://floqer.com/docs/reference Use case catalog: https://floqer.com/docs/use-case-catalog.txt