Where it sits: the second stage. Stage ① proved everything lands in Reporting; this is the next hop — the lift (Reporting → Transfer), the ER map that resolves each row to a canonical made_id, how it’s meant to stay in sync, and the human-in-the-loop match & merge on /made-id-review — ending at what the custodian agent must own (page 3).
The “lift” is a family of pg_cron SQL functions that run on the Transfer DB. Each calls Reporting’s PostgREST via the Postgres http extension (auth = vault secret reporting_anon_key), pages the source table (limit 1000 offset…), and upserts into a rpt_*_stage staging table on Transfer. Critically: they only stage — none of them writes er_identity_map, contact_made_id, campaign_dim, or mints/merges/links. And they re-pull the whole table every run (no changed-since cursor) — the change-sync is a full refresh, not a delta.
| Lift function (runs on Transfer) | Reads (Reporting) | Writes (Transfer staging) | Then applied by |
|---|---|---|---|
| sync_persons_from_reporting | persons (contacts+leads+HS) | rpt_persons_stage | apply_sfdc_contact_identity → contact_made_id |
| sync_external_ids_from_reporting | external_ids crosswalk | rpt_external_ids_stage | refresh_crm_identity_bridge |
| sync_crm_campaign_members_from_reporting | campaign members (SFDC+HS) | rpt_campaign_members_stage | campaign_membership build |
| sync_lifecycle_history_from_reporting | lifecycle_stage_history | rpt_lifecycle_stage_history_stage | lifecycle timeline |
| sync_accounts_from_reporting | accounts (unified) | rpt_accounts_stage | account identity |
| sync_sfdc_from_reporting | sfdc_* (accounts/opps/…) | Transfer sfdc mirror / companies join | account matching ladder |
| sync_hubspot_from_reporting · sync_hubspot_companies_from_reporting | hubspot_contacts / hubspot_companies | Transfer HS mirror / companies join | account + contact matching |
Every run re-pages the entire source table into staging. It works at today’s volumes, but there is no “what changed since last run” — so the custodian can’t (yet) react to just new/changed CRM rows. The incremental change-sync is a thing to build, not a thing that exists.
Identity is built into a small set of Transfer tables. The anchor is companies (canonical, one made_id each, with a merged_into_company_id tombstone for merges). er_identity_map is the crosswalk that ties an external record to that anchor; contact_made_id keys people; campaign_dim/campaign_membership key campaigns.
| Table (Transfer) | What identity it holds | Key columns | State |
|---|---|---|---|
| companies | Canonical company anchor — one row per real firm | id, made_id, made_company_id, normalized_name, hubspot_id, merged_into_company_id (tombstone), field_provenance | exists · 4,719 |
| er_identity_map | Crosswalk: (system, external_id) → company_id → made_id | system, external_id, external_name, company_id, made_id, match_method, confidence, status, confirmed_at/by, last_verified_at | exists · 9,336 |
| contact_made_id (+ er_contact_identity_map) | Person identity (SFDC contacts/leads + HS contacts) | contact_made_id, person keys, match_method, status | 112,617 mapped |
| campaign_dim / campaign_membership | Canonical campaign + who was in it | campaign_dim.id, member→(contact_made_id, company made_id), has_responded | crosswalk cosmetic |
From the sync registry (verbatim, 2026-08-10/13): ids.external_ids has no writer — “it is not a broken sync, the sync was never built.” It was populated once on 2026-06-11 (7,783 SFDC / 21,019 HS) as PROJ-057 “MADE ID Unifier” Phase 3, and never got a maintaining job. It splits into a person half (fresh — apply_sfdc_contact_identity runs every 6h) and a dormant account half: external_ids_account (7,030 rows, ~2,396 accounts with both a SFDC + HS id) whose internal_id resolves to nothing, and whose consumer propose_hubspot_account_identity is not scheduled.
Earlier versions of this page implied this dormant crosswalk meant account identity as a whole had stopped. It does not. er_identity_proposals is still being written daily (most recently 2026-09-09 08:30) and er_identity_map holds 9,336 live links. Two further points matter: ids.external_ids_account is registered as a monitor key pointing at a Reporting view, not a table in an ids schema — so nothing should be built named after it; and because its readers are unknown, the right first move is to trace active consumers, then decide maintain / bypass-behind-a-view / retire. “Repairing” a key whose consumers are unidentified risks breaking another project’s feed.
rpt_*_stage (full)refresh_crm_identity_bridge applies the contact identitymade_id_* engine scores + proposes matchesfield_provenance has 0 salesforce / 0 hubspot stampsSystemModstamp/hs_lastmodifieddatesalesforce/hubspot provenance on writeWhen identity isn’t certain, the custodian writes a proposal and a human decides on /made-id-review. Auto-confirm is reserved for the near-certain; everything else is banded for review; merges re-point onto a senior MADE-ID and land losers in a census (never a DELETE).
| Review surface (tab) | What the human resolves | Served by |
|---|---|---|
| Coverage / Census | Unmatched CRM rows awaiting a decision | made-id-census-review EF |
| Duplicates | Two canonical companies that are one firm → merge onto senior made_id | made_id_dedup_scan · preview_company_merge |
| CRM Matching | Account proposals (SFDC/HS → company) | made-id-review EF · er_identity_proposals |
| Contacts | Contact→identity + contact→account rollup (D3b bands, bulk-accept) | made-id-contact-review EF · made_id_contact_* RPCs |
| New Accounts | Mint a net-new company (human-approved) → MADE-ID + enrichment | made-id-new-accounts EF · orphan-seeder |
| Campaigns | SFDC↔HubSpot campaign crosswalk | made-id-campaign-review EF · campaign_dim |
Propose-only. Auto-merge only at ≥0.999. Never made_id written directly, never a silent DELETE — sub-floor candidates become census rows so the next run knows they were already examined. This is what the custodian agent enforces (page 3).
| Piece | Status | Note |
|---|---|---|
| Lift Reporting→Transfer (contacts/persons/campaigns/lifecycle) | exists | full re-pull into rpt_*_stage; person identity applied every 6h |
| er_identity_map + companies anchor + merge tombstone | exists | 9,336 links · 4,719 companies · merged_into_company_id ready |
| Contact identity + rollup (D3b) | partial | 112,617 mapped; ~31% contact→account gap; two proposal tables to consolidate |
Legacy account crosswalk (external_ids_account) | dormant | a monitor key over a dormant Reporting subset, stalled since 2026-06-11; consumer unscheduled. Not proof account matching is broken — proposals still write daily. Trace consumers before repair/retire |
| Abandoned match queues | retire | match_queue 3,731 pending (last enqueued 2026-05-15) · contact_match_queue 2,963 pending (last 2026-07-29) — drains still run every 10 min over stale rows |
Canonical rich person record (public.contacts) | 0 rows | person identity is in made_id_contacts (109,324); the rich table was never filled and consumers routed around it |
| Change-delta sync (react to just-changed rows) | missing | lift is a full re-pull; no watermark |
| salesforce / hubspot provenance stamping | missing | field_provenance has 0 of each |
| Campaign crosswalk (SFDC↔HubSpot merged) | cosmetic | never truly merged; attribution 19% / 6% |
supabase/migrations/20260729b_phase1_reporting_identity_sync_functions.sql; ER-map + crosswalk truth verbatim from the sync_registry seed (20260813_sync_registry_seed.sql) + Transfer OpenAPI RPC inventory; counts read live from Transfer/Reporting 2026-09-09. Companion pages: upstream_truth_map.html (page 1), 03_custodian_agent.html (page 3). Internal — not for external share. No system changed.