# NexusCRM ↔ HubSpot Sync Reconciliation Report

A scheduled GitHub Actions job that rebuilds the daily NexusCRM↔HubSpot sync
reconciliation report and publishes it as the run's **step summary**
(org members open the Actions run and read it directly — no download needed).

## What it produces

`report.md` is rendered into the run's step summary. `report.json` retains the
same complete field inventory and aggregate counts, without record IDs, emails
or source values. Both are uploaded as the report artifact.

- **Headline** — 7-day Leads↔Contacts net/aligned-days, the SFDC-channel share
  vs its target of 0, field alignment, and Account→Company coverage.
- **Daily record-count ledger** — Leads↔Contacts (with per-day and 7-day-total
  SFDC write-share), Opportunities↔Deals, Accounts→Companies (cohort coverage),
  Beijing-day buckets, 7 settled days plus a partial "today" row. Each section
  states its own success target and which row/column to read.
- **Field-level reconciliation** — every target in the full inbound
  `Contact→Lead` mapping, comparing supported `transformExpression` + `valueMap` entries from
  `packs/PingCAP-HubSpot-Companion/field_mappings/contact_lead_inbound.json`
  and diffing against the actual Lead value. Two side-by-side cohorts:
  **yesterday (most recent complete day, authoritative)** and **today (partial, trend)**,
  each with evaluation status / align% / differ / nx-missing / compared / src-empty. Fields are
  ordered by marketing priority first, then alphabetically.

The Python report evaluator supports only `LEFT`, `BLANKVALUE`, `CASE`, `TEXT`,
`DATETIMEVALUE`, and concatenation. Other formulas, including the conditional
epoch-millisecond date formulas, are listed as unsupported with a reason. The
job does not have a configured native Core evaluator; those fields require
separate verification with the exact runtime. Missing physical columns are also
listed, never dropped from the mapped-target count or selected as SQL columns.
Fields present only in today's cohort remain visible. Empty cohorts, excluded
source-empty/both-empty values, unmapped values and evaluation errors have
distinct statuses/counts. A partial comparison can have a rounded 100% rate;
it is not counted as fully matched when evaluation errors or unmapped values
remain. No comparable data is not evidence of alignment.

The report compares normalized values; it does not reproduce every inbound
write decision. `skip_if_set` can intentionally preserve a different populated
NexusCRM value, including boolean `false`. Owner projection derives from
`Lead.OwnerId` and a unique HubSpot user binding. Pairing uses the compatibility
`HubSpot_Id__c` column and email fallback, not finalized connector identity.
Before repairs, verify current canonical identity, the deployed mapping and
field authority. The checked-out mapping can differ from the deployed one.
Dates compare at UTC minute precision; numbers use the existing four-decimal
normalization, and strings collapse whitespace. The existing Company placeholder
equivalence and Industry/Vertical blank-source rules remain in effect.

## Data sources

| Side | How | Credential |
|---|---|---|
| NexusCRM | TiDB direct SQL: configured production database, `lead` / `opportunity` / `account` | `PROD_MIGRATION_TIDB_PASSWORD` (secret) on `datasync-runner` |
| HubSpot | CRM v3 REST (search totals + batch read) | `PROD_HUBSPOT_ACCESS_TOKEN` (secret) |

Both credentials already exist in the repo. No new secrets are required **if**
`PROD_HUBSPOT_ACCESS_TOKEN` carries contacts, deals and companies read scopes.

The client permits only CRM search and batch/read POST endpoints for these
three object types. Calls are serial and paced at no more than four per second;
the [HubSpot search burst limit](https://developers.hubspot.com/docs/developer-tooling/platform/usage-guidelines)
is five per second. Shared account traffic can still cause throttling. HTTP429
is retried at most three times, honoring `Retry-After` seconds or HTTP dates;
absent/invalid headers use 1/2/4-second delays. A delay over 60 seconds fails the
run instead of retrying prematurely. Non429 failures and exhausted retries
remain failures, never empty successful data. There are no provider/DB writes
or messaging steps.

## Schedule

`cron: "0 1 * * *"` = **09:00 Asia/Shanghai**, daily. The authoritative field
cohort is the most recent **complete day (yesterday)**; today's partial day is
the trend cohort. HubSpot attribution settles over ~24h, but the empirical
effect is small (~1pp), so yesterday's complete day is the primary quality read
(its latest-hours records may be <24h settled — disclosed, not hidden). Also
runnable on demand via **workflow_dispatch** (optional `now_override` for a
deterministic window when debugging).

## First-run verification (cannot be validated without prod credentials)

The reconciliation/render logic is validated locally against real pulled data
(it reproduces the manual dashboard's numbers exactly). Three things can only be
confirmed by the first CI run — each fails **loudly** rather than silently:

1. **`lead` columns** — the script preflights that `HubSpot_Id__c` and
   `CreatedDate` exist on `lead`; a missing mapped target column is explicitly
   marked unavailable in the field inventory.
2. **Timezone** — assumes `lead.CreatedDate` is stored in **UTC** (SF export
   convention). If day-boundary counts look shifted by hours, revisit this
   assumption.
3. **HubSpot scope** — the script preflights 1-row contacts, deals and companies
   searches; if `PROD_HUBSPOT_ACCESS_TOKEN` lacks an object read scope it exits with a
   clear message. In that case, add a read-scoped HubSpot private-app token as a
   new secret and point `HUBSPOT_TOKEN` at it in the workflow.

## Files

- `.github/workflows/sync-recon-dashboard.yml` — the scheduled job.
- `scripts/sync_recon/generate.py` — data fetch + reconciliation + `report.md` render.
- `scripts/sync_recon/test_report.py` — fixture-based tests for the report
  builders (no DB/network required); run with `python3 scripts/sync_recon/test_report.py`.
- `scripts/sync_recon/test_transport.py` — real loopback HTTP tests for pacing,
  throttling, retry bounds and read-only endpoint enforcement; run with
  `python3 scripts/sync_recon/test_transport.py`.