# Reading the CRM without write access Read-only routes to definitions, writers and history; how to build the requests file; the normalized file specs; the audit config keys; how to run the scripts; and how to read each rule. Platform facts and their check dates are in [platform-mechanics.md](platform-mechanics.md). ## Principles - Read with the least access that works: an export the user runs, a read-only MCP server, a read-only warehouse role. - Ask for record IDs, values, timestamps and change sources. Contact names, emails, phone numbers and free text are removed before a file is shared. Keep the change source column: user emails there are the writer evidence T05 and T06 need, and they become role labels through the source map. - Never paste raw rows, user emails or other addresses into the conversation. Inspect a file by printing only its header row and counting columns; `normalize_history.py` writes only the mapped columns and masks email-like sources and values it cannot map, and `workflow_audit.py` masks any that still reach it. - Never ask for, print or store credentials, tokens or keys. Calls that need a token are run by the user, who keeps the token. - State the read time, the route and whether every page was read. A partial read is labelled partial. ## Working folder - Work in a folder the user names, never the installed skill folder: a skill update deletes files there, and a repository that vendors skills can commit them. - Keep exports, `source-map.csv`, `workflow-config.json`, the normalized files and every deliverable in the working folder. Call the scripts by their path: ```bash S=path/to/revops-workflow-design python3 "$S/scripts/workflow_audit.py" self-test python3 "$S/scripts/normalize_history.py" self-test ``` - Run from the skill folder only for the fixture runs in [worked-example.md](worked-example.md), and send their `--out` files to a folder outside the skill. ## HubSpot Definitions and current state, through the remote MCP server: - `get_user_details` first: confirms the account and lists the tools this connection actually has. - `get_properties` or `search_properties` for lifecyclestage, hs_lead_status and dealstage: record the internal values, not labels. - `search_owners` for owners (at most 100 results), and `search_crm_objects` or `query_crm_data` (call `tool_guidance` first) for counts and current state (at most 5 filter groups of 6 filters, 200 results per page). Say whether more pages remain. - No MCP tool reads history or workflows. With Sensitive Data on, activities are blocked; read them from an export or the warehouse. History: - Export property history for each workflow property (Settings > Properties > object > hover the property > More > Export property history) with Include Source Information ticked. Needs CRM Export and [Object] Edit All. Dates are UTC; values run newest to oldest, left to right; Record ID is a column. Other headers are not documented: print only the header row, count the columns, find where the first value group starts and pass the group order. - Build the source map without showing raw sources: write the distinct raw sources to a local file, ask the user to fill the label column with TYPE:detail labels (CRM_UI:rep_a, WORKFLOW:mql-scoring), then pass the filled file as `--source-map`: ```bash python3 "$S/scripts/normalize_history.py" wide --in lifecycle_history.csv --list-sources sources-to-label.csv \ --object contacts --field lifecyclestage --id-column "Record ID" --group value,time,source python3 "$S/scripts/normalize_history.py" wide --in lifecycle_history.csv --out transitions.csv \ --object contacts --field lifecyclestage --id-column "Record ID" --group value,time,source \ --source-map sources-to-label.csv --value-map value-map.csv --cap 45 ``` - `--list-sources` prints counts only and writes `raw,label,count`, with labels already known from `--source-map` filled in. `--group` lists the columns inside one revision in order; use `skip` for any column to ignore. `--first-group-column` names the first column of the first group if other columns sit between Record ID and the history. - Changes with the same timestamp keep their order: the right-hand (older) group sorts first, so a clear-then-set in one second stays a clear-then-set. - If the value columns hold labels (Marketing Qualified Lead) instead of internal values (marketingqualifiedlead), map them with `--value-map`. The audit config uses internal values. - Agent CLI: `hubspot objects history --type contacts --properties lifecyclestage --include-ui` (named in the agent-cli-skills bulk-operations skill, not on the CLI guide page, so confirm it with `--help`). Without `--include-ui`, manual edits are missing and every backward move by a rep disappears. Without `--id` it scans a page of records, so check how your CLI version pages. The output format is not documented: look at the keys of the first row only. JSON lines go through `normalize_history.py jsonl --map record=KEY,field=KEY,value=KEY,time=KEY,source=KEY` (dot paths allowed); CSV goes through `long`. - CRM API: a GET with `propertiesWithHistory=` returns one record's history; the user runs it with their own read access. Writers and settings: - `hubspot workflows list` and `get` return v4 flows only. Classic contact workflows are not listed, so build the writer inventory from history sources first. - Read in the UI and record as notes (no API route was confirmed): lifecycle automation settings; the lead pipeline's Create a lead automation and the lifecycle stages that trigger it (it adds a second lead when an intake workflow also creates one); the lifecycle stage setting on every form, copies included; stage calculated property toggles per stage; each rotate action's overwrite and available-users options and owner list; how Away status is set; Sensitive Data; integration field mappings and directions. ## Salesforce - An admin turns on the hosted sobject-reads MCP server (Setup > API Catalog > MCP Servers). Never connect the mutations, deletes or all servers for this skill. - Run `getObjectSchema` on Lead, LeadHistory, Opportunity, OpportunityFieldHistory, OpportunityHistory and OpportunityContactRole first. Confirm field names, picklist values and which Lead Status value is the converted one. Lead has no AccountId before conversion (routing step 4 in [clocks-and-slas.md](clocks-and-slas.md)). - Find the Field values history actually uses before filtering, with an aggregate such as `SELECT Field, COUNT(Id) FROM LeadHistory GROUP BY Field`. - Then read history in date windows with `soqlQuery`, always with WHERE and LIMIT, paging by CreatedDate: ```sql SELECT LeadId, Field, OldValue, NewValue, CreatedDate, CreatedById FROM LeadHistory WHERE Field IN ('created', 'Status', 'Owner') AND CreatedDate >= 2026-04-01T00:00:00Z ORDER BY CreatedDate LIMIT 2000 ``` - Use the Field values the GROUP BY returned; the names above are the common ones, not a guarantee. Stage history comes from OpportunityFieldHistory (StageName changes) or OpportunityHistory (stage snapshots). - History exists only for fields with field history tracking on. If Status, Owner or StageName are untracked, say so and audit from what exists. - An owner change can appear twice, once with IDs and once with names. Keep only the ID form with `--keep-values "Owner=^(005|00G)"`: an OwnerId that starts 005 is a user and one that starts 00G is a queue (confirm on one known user and queue). The regex is matched against the raw value, before `--value-map`. - History starts at the first tracked change. Map `old=OldValue` so the starting value is written, dated at the record's created row. A fixture run, from the skill folder with `$WORK` outside it; the fixture's placeholder IDs start with user- and queue- instead of 005 and 00G: ```bash python3 scripts/normalize_history.py long --in assets/example-sf-lead-history.csv --out "$WORK/transitions-sf.csv" \ --object leads --map record=LeadId,field=Field,value=NewValue,time=CreatedDate,source=CreatedById,old=OldValue \ --only-fields Status,Owner --field-map Status=lead_status,Owner=owner --keep-values "Owner=^(user-|queue-)" \ --source-map assets/example-source-map.csv ``` - That example reads 14 rows, drops the 2 name-form owner rows and writes 13, 5 of them starting values. Audited with [../assets/example-salesforce-config.json](../assets/example-salesforce-config.json), it shows an enrichment integration user reassigning a Lead (T06) and a marketing automation sync flipping a rep's Unqualified back to Open (T03, T05). Full output and handling: [worked-example.md](worked-example.md), second fixture. - Map each integration user ID to a label in the source map. One integration user per integration keeps writers apart. - Assignment rule entries, the default owner in Lead Settings and how each creation path invokes rules are read in Setup by an admin and recorded as notes. ## Attio - Use the Attio MCP server's read tools for objects, lists, records and status attributes. Writes ask for confirmation; decline them. - Historic values come from the list record attribute values and list attribute values for a list entry endpoints with `show_historic`, run by the user with a read-scoped token they keep. Map attribute slug to field, active_from to changed_at and the actor to source. - Never call the write attribute values endpoints: they replace an attribute's whole history. ## Warehouse - A read-only role. Map hubspot__contact_history and hubspot__deal_history (or the connector's property history tables) and the replicated Salesforce history tables into the transitions shape. Column mappings are in [report-pack.md](report-pack.md). - Send warehouse rows through `normalize_history.py` like any export: select the record ID, field, value, time and one raw source column (for HubSpot, `change_source || ':' || COALESCE(change_source_id, '')`) into a CSV, label the sources with `--list-sources` and `--source-map`, then run `long` mode. For owner fields, add `--value-map` so owner IDs or names become the same role labels. Never use a raw change_source_id as the source: it can hold a user email. - Revision caps belong to Export property history files. When transitions come from a warehouse history table landed continuously, set `revision_caps` to null for those objects so T01 prints INFO; with the export caps, busy records get a false T01 WARN, or a FAIL above 5%, on the most complete source you have. ## The requests file One row per request received inside the audit window, built from the source systems. One file holds every class. Columns: - request_id: the source submission or event ID. - record_id: the request record (HubSpot lead, Salesforce Lead, Attio list entry), or the person when there is no episode record. - request_class: a class in the SLA register (hand_raise, booked_at_submit, mql, closed_won_to_cs). For a handoff class, received_at holds the trigger time (closed won) and disposition_at the receiver's decision. - received_at, crm_seen_at, assigned_at, last_assigned_at, first_response_at, disposition, disposition_reason, disposition_at, owner_changes, excluded_reason. Optional: first_connected_at, meeting_booked_at. How each column is filled: - received_at: the form tool's submission log or a server-side field set at submission. Never CRM creation. - crm_seen_at: creation time of the request record in the CRM. - assigned_at: the first owner change to a person after received_at, from the request record's owner history normalized with `normalize_history.py`. A queue counts only when it is staffed and has its own pickup SLA in the register. - last_assigned_at: the latest owner change before the stop event. HubSpot's lead Owner Assigned Date gives only the latest. - owner_changes: reassignments after assigned_at and before the stop event; the first assignment is not counted. - first_response_at: the occurrence time of the earliest call, email or meeting associated with the request record after received_at that counts as a relevant human attempt ([clocks-and-slas.md](clocks-and-slas.md) lists what never counts, including automatic sequence steps). Use the activity's occurrence time, not when it was logged. - disposition and disposition_at: the first accept, reject, recycle or conversion after received_at, from status, lead stage or decision property history. - excluded_reason: spam, test, duplicate delivery or internal; empty for eligible requests. Per CRM: - HubSpot: request records are leads. Owner and stage history come from Export property history on the leads object, or the CLI with `--type` set to the leads object. Activities come from the MCP read tools for calls, emails and meetings associated with the lead, when the connection exposes them (verify object, association and property names with `get_properties` at run time), or from an activities export when Sensitive Data blocks MCP. With no leads object, use the contact and window each request from its received_at to the next request's received_at. - Salesforce: request records are Leads. Owner and Status history from LeadHistory (ID form only); first responses from Task and Event rows on the Lead, using the occurrence time the org records (confirm the field names with getObjectSchema). - Attio: request records are list entries; owner and status from historic values; activities from the user's export. ## Normalized file specs - transitions.csv: `record_id,object,field,value,changed_at,source`. One row per change, changed_at in ISO 8601 UTC, an empty value means the field was cleared, source is a TYPE:detail label. - requests.csv: the columns above, timestamps in ISO 8601 UTC, empty when unknown. ## Config keys [../assets/workflow-config.json](../assets/workflow-config.json) is the Acme example and the starting template; copy it to the working folder. Keys that start with an underscore are notes. - `fields.`: `object`; `ordered` (lowest rank first, with the won value such as closedwon last); `terminal` (the other end states such as closedlost, exempt from skip checks); `unranked` (known values with no order); `allowed_skips` (`"from>to"`); `gates` (value to the prior values it requires); `max_dwell_days` (value to days; start from the handoff SLA, or the p90 of historical dwell where no SLA exists); `loop_values`; `max_entries_90d` (default 2). - The last ordered value and every terminal value are end states: leaving one for any other value counts as backward (T03), closedwon to closedlost included. Listing the won value under `terminal` instead would turn every close from the last open stage into a backward move. - `writers.`: `"*"` lists source prefixes allowed for any value without its own list, `""` lists prefixes allowed to set that value, `""` lists prefixes allowed to clear. A prefix matches from the start of the label, so `CRM_UI:` covers every user. Mirror the register value by value. - `manual_source_prefixes` (default `["CRM_UI:"]`) and `correction_source_prefixes`. - `clear_set_seconds` (60) and `pingpong_hours` (24). - `revision_caps`: contacts 45, companies 20, deals 20, tickets 20 by default; `null` means unknown or not capped (Salesforce history, warehouse tables), and T01 prints INFO. - `conversions`: `[field, from, to, window_days]` pairs for T08 and T09. - `calendars.`: one block per team with `timezone` (IANA), `hours` (mon to sun, `["HH:MM", "HH:MM"]`, or a list of such spans for a break or an overnight shift; `"24:00"` closes at midnight), `holidays`, and `utc_offset` as a fallback when the zone cannot load (daylight saving is then not handled and the run says so). A single `calendar` block still works for classes that name none. - `classes.`: `calendar` (a name in `calendars`), `start` (received_at), `stop` (first_response_at or disposition_at), `owner_start` (last_assigned_at), `target_business_minutes` (required), `target_compliance` (0.9), `max_unassigned_business_minutes` (null or absent means the target). - `thresholds`: `missing_start_fail_rate` 0.01, `ingest_lag_p90_minutes` 5, `owner_changes_warn` 2, `capped_fail_rate` 0.05. ## Running the audit From the working folder: ```bash python3 "$S/scripts/workflow_audit.py" transitions --config workflow-config.json \ --transitions transitions.csv --as-of --since [--json] python3 "$S/scripts/workflow_audit.py" sla --config workflow-config.json \ --requests requests.csv --as-of [--json] ``` - Set `--as-of` to the time the exports were read. Rows after it are ignored, so a run repeats exactly. - Pass `--since` with the first day of the audit window. Findings and cohorts then cover the window only, while the 90 days before it still set each record's starting state; without it, those earlier changes inflate the counts and rates. - Output: a header with the file name, row counts and as-of (the sla header also lists the calendar each class used), then ` : `, INFO lines for metrics, then ` FAIL, WARN, PASS. Nothing was changed.` `--json` gives the same findings with every ID. - Exit 0 with no FAIL, 1 with at least one FAIL, 2 on bad input. A config with the wrong shape (a conversions entry without its window, hours written as "9am", overlapping spans, a class calendar missing from `calendars`) stops with exit 2 and names the key; rows with an unparseable time are skipped and counted under T01. - A file with no rows to audit (a header only, or every row after `--as-of`) stops with exit 2 instead of passing. So does a `--since` after every change, and a sla run where every request is after `--as-of` or excluded. A class with no eligible request prints an S01 INFO line. `normalize_history.py` stops with exit 2 when no row is usable; `skipped: unparseable time` usually means `--group` is in the wrong order. - A field name that differs between the file and the config (lifecycle_stage against lifecyclestage, often from a `--field-map` slip) prints two T01 WARN lines: the configured field has no rows, and the file's field has no `fields` entry. Fix the name and run again before reading any other finding for that field. In the sla run, a configured class with no rows prints an S01 INFO line. - Email-like sources or values that reach the audit print as UNMAPPED_USER labels, and the header says how many; map them with `--source-map` or `--value-map` before sharing results. - Without Python, apply the rules below by hand to a sample of at least 30 records per field and label the audit "script not run". ## What each rule means Denominators: "records" are the records with history for that field in the file; "due" requests are eligible requests minus not yet due; "measured" requests are eligible requests with a start time. - T01 history coverage: records with revisions at the cap may have lost older history. WARN when any record is at the cap; FAIL above 5% of a field's records, because transition counts are then unreliable. Unknown or null caps print INFO: check one busy record by hand. WARN for a configured field with no rows in the file, and for a field in the file with no `fields` entry. - T02 unknown values: a value outside the configured lists, usually a custom option someone added or a writer sending labels. WARN with the writer, as n of N records. - T03 backward moves: a move to a lower rank, or out of an end state (the last ordered value or a terminal value) to any other value. PASS through a correction source, WARN for a manual edit without one, FAIL for anything automated. Rate over records. - T04 gates and skips: FAIL when a gated value (customer without opportunity) is entered without its required prior value, first values included, as n of N records that entered the gated value. WARN for a forward jump that `allowed_skips` does not list, as n of N records. - T05 writer ping-pong: A to B to A within 24 hours where the change into B and the change back come from different sources. FAIL: two writers are fighting. Rate over records. - T06 writers: every change checked against the value-level writer list. FAIL per unexpected source, with the values it set and n of N changes. The INFO line is the observed writer inventory. - T07 aging: records sitting in a stage longer than its dwell limit at the as-of time, as n of the records now in that stage. WARN, oldest first. - T08 and T09: cohort conversion and velocity between first entries. INFO. Immature cohorts print the date they mature. - T10 re-entry loops: more than `max_entries_90d` entries into a loop value within 90 days, as n of N records that entered the value. WARN. - S01 timestamps: FAIL when more than 1% of eligible requests miss the start or any stop precedes its start; WARN for missing crm_seen_at; INFO for a configured class with no rows in the file, or no eligible request. - S02 compliance: on time over due, FAIL below the class target or when the class has no target. The answered-only rate is printed and labelled "not compliance". - S03 unanswered: overdue requests with their business age, as n of N due. FAIL. - S04 clock decomposition: WARN when the p90 received-to-CRM lag exceeds 5 minutes; INFO lines split the rest of the wait, give the baseline for a first target (every due request, overdue ones at their age, with a note under 30 due) and show where coverage hours hide a long buyer wait. - S05 owner churn: 2 or more owner changes before the stop event, as n of N measured. WARN. - S06 owner versus buyer clock: on time for the current owner, late for the buyer. WARN; these are the requests an assignment-based report shows as fine. - S07 unowned: FAIL for no assigned_at, or an assigned_at after the as-of time, once the class limit has passed; WARN for an assigned_at later than the class limit (the request waited for an owner). Both as n of N measured, because a request can be unowned past its limit before it is due. PASS only when neither happened. ## Confirming a finding on one record Before a FAIL goes in the audit, open one example record in the CRM: - HubSpot: the property's history on the record (value, time, source). Salesforce: the record's History related list. Attio: the attribute's history. - Check that the value, time and source match the file, then find the workflow, integration log or user action at that time. - Write "confirmed in the CRM on by " beside the finding, or downgrade it with the reason.