Where it sits: the end of the spine. Once a match is confirmed (stage ④), the identity lives here: a single canonical companies row with a stable made_id, the crosswalk that ties every external record to it, and the provenance that says where each value came from. This is the “one record, everywhere” the whole system exists to produce.
companies is the anchor — one row per real firm, each with a stable made_id. Contacts key to it via contact_made_id; opportunities, campaigns and every downstream fact hang off it.
| Identity column | What it is |
|---|---|
| made_id | The stable canonical identifier — “one company, one ID, everywhere.” The join key on both the CRM side and every app. |
| id | The row’s primary key (the MADE-ID assignment target). |
| normalized_name | Light normalization that keeps industry words (so “Acme Therapeutics” ≠ “Acme Bio”) — the exact-name matching key. |
| company_domain | The canonical web domain — the strongest deterministic match signal. |
| aliases | Learned alternate names (every confirmed merge teaches one) — makes the next match free. |
| name_embedding | 1024-dim vector for semantic candidate retrieval (pgvector). |
| made_company_id · hubspot_id | Denormalized cross-system ids carried on the row. |
| relationship_type · is_icp_cgt | The ICP structure the app scores on (who this company is to Made). |
| merged_into_company_id | The merge tombstone column — present on the schema, ready for a true physical merge (not yet populated — see Provenance). |
er_identity_map is the crosswalk: for every external record (a SFDC account, a HubSpot company, a Hub entity), one row says which canonical company it is and how we know.
| Column | Meaning |
|---|---|
| system | salesforce · hubspot · bc_hub · life-sci — the source system |
| external_id | The record’s id in that system |
| external_name | Its name as that system spells it |
| company_id → made_id | The canonical company it resolves to |
| match_method | How it was matched (domain_exact, alias, llm-adjudicated, human, …) |
| confidence · status | Score + state (confirmed / proposed / rejected) |
| confirmed_by · confirmed_at · last_verified_at | The human + timestamps — the audit of who committed it |
Contacts resolve the same way via contact_made_id (+ er_contact_identity_map, 112,617 rows); campaigns via campaign_dim + campaign_membership (keyed to contact_made_id + company made_id). The census (er_account_census, 22,400) keeps one durable row per (system, external_id) so nothing is ever silently dropped.
made_id_contact_match_proposals (12,489, current to 2026-09-06) is the authoritative path. er_contact_identity_proposals (11,446) stopped writing on 2026-08-26 and is superseded — a migration that was started and not finished. Converging them, with every outstanding legacy decision accounted for, is an early task in the plan.
Every canonical value should carry source · value · evidence · captured_at · run_id in companies.field_provenance — so any report can say “this came from Salesforce on this date” instead of arguing with itself. Two things aren’t true yet.
field_provenance has 0 salesforce and 0 hubspot labels. CRM science that has leaked onto canonical is mis-stamped as hub/inference. Fixing it = add both to the vocabulary and stamp them on the mirror write (stage ②).
The legacy account crosswalk (external_ids_account) has not advanced since 2026-06-11, and its consumer isn’t scheduled. Earlier versions of this page overstated it. It is a monitor key over a Reporting view describing a dormant account subset — its staleness does not prove that account matching as a whole is broken, and account proposals are in fact still being written daily (2026-09-09 08:30). The correct next step is to trace which consumers still read it before repairing, bypassing or retiring it — not to “fix” a key whose readers are unknown.
public.contacts has a full person schema (first_name, email, job_title, company_id, linkedin_url…) and 0 rows. Canonical person identity lives in made_id_contacts (109,324) and per-campaign person detail is denormalized onto campaign_contacts, so consumers routed around the empty table rather than filling it. Publishing one authoritative person read path is an early task in the plan.
Chasing Gap 3 surfaced real bugs, not just modelling debt. (1) Lead-list generation read person fields from the empty contacts table, so any list it produced would be empty while reporting success — across 65 campaigns / 5,207 members the old path resolves 0 leads, the repaired path resolves 4,869 across 62 campaigns. (2) A dashboard contact count probed a view column that does not exist, then fell back to the empty table, returning 0 while reporting success. (3) The HubSpot contact sync discarded its upsert error, so a rejected write could report success and leave the mirror short.
All three were latent rather than active — the lead-list function is deployed on neither database and no cron calls it, the HubSpot sync function last ran 2026-08-06 (Joe’s n8n is the live producer), and the dashboard hook is unreferenced. They were caught before they could cost anything. The HubSpot fix is deployed (v47); the lead-list fix is held pending a smoke test, since deploying it would add an outbound capability rather than repair running code.
A confirmed merge re-points identity (census + idmap) onto the senior made_id and learns the loser’s name as an alias — but both physical rows remain. The merged_into_company_id tombstone exists to build the true physical merge on.
The point of one MADE-ID is that everything downstream joins on it. Once an identity is canonical, it feeds every Made surface without re-resolving.
The Account Atlas / dossiers, scoring (ICP, account score), enrichment (Clay, L3), and the science layer all key off companies.made_id.
Opportunities map won/open → customer/active_prospect on the canonical account; campaigns attribute engagement to it.
Contacts (via contact_made_id) + their canonical employer drive persona, messaging, and the send/response loop.
Every internal report joins on the MADE-ID instead of arguing across three spellings — the reason the spine exists.
Salesforce, HubSpot and the Hub all agree on the same firm, keyed by one MADE-ID, with provenance on every value. That is what the five stages of this spine exist to produce and keep true.
madeid-custodian-deep-dive.md §6–§7 + the Transfer companies/er_identity_map columns read live 2026-09-09. Companion: stage ② Lift & ER map, stage ④ Human-in-the-loop. Internal — not for external share.