Which objects deserve one record per real-world thing — answered from the code, not from theory. We read all 193 tables across Perch's three services, traced every dedup and merge mechanism already built, and pressure-tested the rules against 13,239 real Utah contractor records.
Perch has eleven objects that should be canonical — one record per real-world thing, shared across every customer. The surprise from reading the code: Perch already builds toward this — three separate canonicalization systems exist today (a SQL resolution engine, a global knowledge graph, and a project matcher), complete with a merge log and alias table. What's missing isn't machinery. It's one unified canon with one doctrine — plus the industry's real identity key (the contractor license), and fixes to three verified landmines that will silently fuse different companies together as the data grows.
Why a CEO should care: every agent answer, every Workbench card, every network effect Perch will ever sell rests on one question — when two customers talk about "IMS Masonry," is that one record or two? Get it right and Perch compounds into a defensible industry graph. Get it wrong and agents put the wrong fact on the wrong card, pipeline counts inflate, and the reputation network can never be trusted.
● = the link already exists in the schema. organizations.selfCompanyId (a Logo knows its canonical Company) and accounts.landedOrganizationId (a won account becomes a Logo) close the loop between the canon and tenancy — Brock's Company/Logo model is already wired at both ends.
Verdicts: HARDEN already canonical in code — fix keys/rules · PROMOTE machinery exists — elevate to true canon · CREATE doesn't exist yet · GLOBAL ALREADY fine as-is.
| Object | What it is | In the code today (receipt) | Canonical key | Verdict |
|---|---|---|---|---|
| Company | A real construction firm | companies: nullable-org "global graph" rows, canonical-name SQL fn + GLOBAL unique idx, resolution engine, merge log, aliases, encounters | license # (add) › domain+name › review | HARDEN FIX KEYS |
| Person | A human (≠ a login) | contacts: canonical lower(email) unique; name+company fallback; bi-graph matches cross-org by email/name | email › name+company | HARDEN |
| Project | The physical build/site | projects: GLOBAL table, sourceHash unique, lat/lng + locationEvidence; LLM project-resolve; cross-match runs org×project | sourceHash · geo+name | GLOBAL ALREADY |
| Document content | A plan set / spec (the bytes) | documents.contentHash indexed 3 ways — same PDF in two orgs = same hash, still two blobs | contentHash | PROMOTE |
| Message identity | An email that exists once in the world | scanned_emails.internetMessageId (RFC-2822) + references threading — global by design | internetMessageId | GLOBAL ALREADY |
| License | A state contractor credential | absent — zero license fields in 193 tables; DOPL feed exists outside Perch | state + license # | CREATE |
| Trade | What work a firm does | trades table (slug unique, global) — but projects.trades is loose JSONB, not FK'd | slug | HARDEN |
| Market sector | What market a build serves | market_sectors global table; same loose-JSONB gap on projects | slug | HARDEN |
| Project type | Office / retail / tilt-up… | free-text varchar(64) — no enum, no table; spelling drift guaranteed | slug (new) | CREATE |
| Geography | Place: city/county/state | location-utils normalizes city/state; lat/lng on projects; no place entity — fine for now | state+county+city (later: place id) | PROMOTE · LATER |
| Organization (Logo) | A tenant — a Company granted access | organizations global (139 inbound FKs); selfCompanyId→Company; subscriptions = active/paused/churned states | id + selfCompanyId link | GLOBAL ALREADY |
Deliberately NOT canonical — and that's correct: accounts (edge), jobs (work, already edged to Project), scopes/bids/estimates/contracts (work), knowledge units + company brain (a Logo's private IP — sharing is a product decision, not a default), workbench items/messages/scores (events). User (login) is global via email+supabaseId and stays distinct from Person.
We didn't just read the rules — we ran 13,239 real Utah contractor records (DOPL + Procore + AGC) through a conservative resolution pass and checked Perch's current rules against what the field data actually does.
companies_canonical_name_unique is enforced on the normalized name across the entire platform — no org in the index. But company names legitimately collide: our Utah pass found 98 same-name, different-firm collisions (two unrelated "Naycor Construction"s; Cache Valley Electric's separate entities). Under this constraint the second real company either can't exist or silently becomes the first.
match-signals.ts auto-merges two companies the moment their domains match exactly. Field data says shared domain ≠ same company: stakerparson.com spans 5 distinct firms (Western Rock, Jack B. Parson, Hales Sand & Gravel…), burbachexteriors.com spans 4, one Nevada phone line spans 6. Our pass held 321 shared-domain/phone cases for review — every one a would-be false merge under the current rule.
organization_members.userId is globally unique — a person can be a member of exactly one Logo, forever. The field contradicts this: owner-operators run multiple entities (one Las Vegas family operates six sibling LLCs on one phone line), estimators change firms, and consultants span several. Today each case means duplicate human identities.
A false merge is a cross-tenant incident; a false split is a cheap later fix. Deterministic keys auto-resolve, everything ambiguous goes to a review queue, every merge is logged and reversible — and the merge log Perch already built (entity_merge_log) needs only an UNMERGE to complete it.
The real architectural finding: canonicalization exists three times, with three different match rules and no shared doctrine.
| System | Lives in | Matches on | Gap |
|---|---|---|---|
| SQL resolution engine | web_server · business-intelligence/resolution/ | weighted signals (name .30, domain .25 auto, phone/geo/trade .15) | domain auto-merge landmine; no review queue |
| BI knowledge graph | agents · knowledge/bi-graph.service.ts (Neo4j) | "globally canonical — matched by NAME regardless of org" | name-only matching; separate store, can drift from SQL |
| Project matcher | agents · project-resolve + projects.sourceHash | hash + LLM (name, city, state, contractor) | the healthiest of the three — the pattern to standardize on |
Recommendation: one resolution service, one key hierarchy per object, one review queue, one merge log — the SQL engine and the Neo4j graph become views of the same canon instead of independent opinions. The Project pattern (global table + deterministic hash + assisted matching + org-scoped work edging into it) is the house pattern; Company and Document should be shaped exactly like it.
| Move | Why now / why wait | |
|---|---|---|
| NOW | Defuse ①② (drop name-unique constraint in favor of the engine; demote domain auto-merge) · add the review queue · add UNMERGE | Cheap, prevents silent data corruption that grows with every ingested email |
| NOW | Create License (state+number, class, status, expiry, qualifier → Company) and ingest the DOPL corpus against it | The one deterministic industry key; 6,822 Utah records ready to load — other states are a data-acquisition roadmap, not a schema change |
| NOW | FK the loose taxonomies (projects.trades / marketSectors → tables; projectType → new enum) | Free-text taxonomy is quiet entropy; tables already exist |
| NEXT | Unify the three canons behind one service; encounters become the universal provenance feed | Structural, medium effort, huge agent-correctness payoff |
| NEXT | Promote Document content (canonical content row; per-org document references it) | Same plan set uploaded by GC + 3 subs = one analysis, four views — direct compute savings |
| LATER | Corporate-family hierarchy (versioned parent_of edges) · probabilistic matching · place entities · the shared reputation tier | Wait for data volume to force them — the D&B playbook, not day-one machinery |
Nodes are globally canonical — matched by name regardless of which org originally discovered them.precon_agents · knowledge/bi-graph.service.ts — Perch's own code, already declaring the ambition. This document is the plan for finishing it safely.