Suppliers & Entity Resolution

The suppliers table

From chapter 01:

CREATE TABLE suppliers (
  supplier_id TEXT PRIMARY KEY,
  customer_id TEXT NOT NULL REFERENCES customers,
  canonical_name TEXT NOT NULL,
  parent_supplier_id TEXT REFERENCES suppliers,
  country TEXT,
  tax_id TEXT,
  industry TEXT,
  first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (customer_id, canonical_name)
);

Field-by-field rationale

• supplier_id: platform-assigned. Stable across re-resolutions.
• customer_id: tenant boundary. Two customers' "Acme Corp" are distinct rows.
• canonical_name: the chosen canonical form. "Acme Corp" (stripped of suffixes; consistent casing).
• parent_supplier_id: self-reference for hierarchies. Null at the top of a chain.
• country: ISO country code. Important for tax / jurisdiction logic.
• tax_id: when available, the gold-standard resolution key. Often missing.
• industry: SIC / NAICS code or a flat string. Useful for category-level analytics.
• first_seen_at: when this supplier first entered the customer's data. Useful for cohort-style supplier analysis.

Why entity resolution is hard

The same supplier appears under many names across a customer's corpus:

• "Acme Corp"
• "Acme Corporation"
• "ACME Corp."
• "Acme Corp, Inc."
• "acme corp"
• "Acme Cloud Services" (a subsidiary)
• "AcmeCorp" (typo / abbreviation)
• "Acme - Cloud Division" (internal naming)

Some are the same entity; some are distinct (Acme Cloud Services might be a separate legal entity that bills separately). Without resolution, you have multiple supplier rows for what's actually one supplier — and your spend rollups, renewal counts, and supplier-count metrics are all wrong.

Customers typically have 3,000–10,000 supplier rows in their ERP, of which 50–70% are duplicates. Consolidation is one of the platform's headline value props.

Resolution techniques

Resolution is layered. Each layer handles a fraction of the corpus.

1. Exact tax_id match

When you have the tax identifier (EIN, VAT number), exact match is definitive. Resolves the easy 30–50% of cases where tax_id is consistently captured.

2. Exact name match (case-and-whitespace normalized)

Normalize: lowercase, strip whitespace, strip common suffixes (Inc, Corp, LLC, Ltd). Exact match on normalized form. Catches another 20–30%.

3. Fuzzy name match

Edit-distance metrics (Levenshtein, Jaro-Winkler) or string similarity (trigram, n-gram). Below a threshold, candidate-match — route to validation. Catches typos and minor variations; ~15% of remaining.

4. Embedding similarity

Embed canonical names with a sentence-transformer; nearest-neighbor lookup. Handles paraphrasing better than edit distance. Catches "Acme Cloud" vs "Acme Cloud Services" pattern.

5. Customer's supplier master

The customer's ERP or procurement system often has an authoritative supplier list. Use it as a source-of-truth for canonical names. Pull it in at deployment time; sync periodically.

6. LLM-assisted resolution

For hard cases (one is a subsidiary of the other? a typo? a different legal entity?), send the candidate pair to an LLM with context. Expensive per call; reserve for ambiguous cases the cheaper layers couldn't resolve.

7. Manual review

The residual that nothing else resolved. Send to a human reviewer with the candidate matches and the supporting context. Standard part of the HITL workflow.

def resolve_supplier(raw_name: str, raw_tax_id: str | None, customer_id: str) -> dict:
    # 1. Tax ID exact
    if raw_tax_id:
        match = db.query("SELECT supplier_id FROM suppliers WHERE customer_id=%s AND tax_id=%s",
                         customer_id, raw_tax_id)
        if match:
            return {"supplier_id": match[0]["supplier_id"], "method": "exact_tax_id", "confidence": 1.0}

    # 2. Exact name match (normalized)
    norm = normalize_name(raw_name)
    match = db.query("SELECT supplier_id FROM suppliers WHERE customer_id=%s AND canonical_name=%s",
                     customer_id, norm)
    if match:
        return {"supplier_id": match[0]["supplier_id"], "method": "exact_name", "confidence": 0.95}

    # 3. Fuzzy candidates
    candidates = fuzzy_search(customer_id, raw_name, top_k=5)
    if candidates and candidates[0]["score"] > 0.85:
        return {"supplier_id": candidates[0]["supplier_id"], "method": "fuzzy_name",
                "confidence": candidates[0]["score"]}

    # 4. Customer-master lookup
    if customer_master_match := lookup_customer_master(customer_id, raw_name):
        return {"supplier_id": customer_master_match, "method": "customer_map", "confidence": 0.9}

    # 5. Create new supplier; flag for review if any candidates were close-but-below-threshold
    return create_new_supplier(raw_name, raw_tax_id, customer_id, flag_for_review=bool(candidates))

Parent hierarchy

Suppliers reference parent suppliers via parent_supplier_id. The hierarchy is a tree (a supplier has at most one parent).

Where parent assignments come from

• The customer's procurement-master often encodes parent relationships explicitly.
• Third-party corporate-hierarchy data (Dun & Bradstreet, OpenCorporates).
• Manual assignment by the customer's data team.

Rolling up to root parent

The standard query — find the root parent for each supplier — uses a recursive CTE:

WITH RECURSIVE chain AS (
  -- Roots: suppliers with no parent
  SELECT supplier_id, supplier_id AS root_id, 0 AS depth
  FROM suppliers WHERE parent_supplier_id IS NULL
  UNION ALL
  -- Recurse: children inherit their parent's root
  SELECT s.supplier_id, c.root_id, c.depth + 1
  FROM suppliers s
  JOIN chain c ON c.supplier_id = s.parent_supplier_id
)
SELECT supplier_id, root_id, depth FROM chain;

Cycle protection

If the data ever has a cycle (supplier A → B → A), the recursive CTE will loop. Add a depth cap or cycle-detection set in application code if you can't fully trust the data.

Raw vs canonical names

The single most important practice in supplier resolution: never delete raw extracted names. Store both.

The reasons:

• If a resolution turns out to be wrong (Acme Corp was incorrectly merged with Acme Holdings), you can re-resolve without re-extracting.
• If you change the resolution algorithm (new fuzzy-match threshold, new embedding model), you can re-run resolution over the historical raw names.
• Audit: when a customer asks "where did Acme Cloud go?", you can show the raw extraction and the resolution decision.

The supplier_name_resolutions table holds the raw-to-canonical mapping with method and confidence. The suppliers table holds the canonical form.

Working with the customer's supplier master

Almost every enterprise customer has an internal supplier master — usually in their ERP. Treating it as the source-of-truth for canonical names is the right default:

• Their master is what their finance / procurement teams already use.
• Their downstream systems (BI dashboards, payment systems) reference IDs from their master.
• Resolving to their master means platform output integrates cleanly with their existing workflows.

The sync pattern

At deployment time, ingest the customer's supplier master via API or extract. Map their supplier IDs to your suppliers table. Sync periodically (weekly or daily) to keep up with their additions.

What to do when their master is also wrong

Customer's supplier master often has its own duplicates (Acme Corp + Acme Corporation as separate rows). Your platform can surface the duplicates as a cleanup recommendation. Don't silently merge — propose the merge and let them confirm.

Common operations

Adding a new supplier

When extraction encounters a name that doesn't resolve to any existing supplier:

1. Create a new row in suppliers with the normalized name as canonical_name.
2. Log the raw name to supplier_name_resolutions with method new_creation.
3. If the new supplier looks similar to existing ones (below the resolution threshold), flag for human review.

Merging two suppliers

Discovered that "Acme Corp" and "Acme Corporation" are the same:

1. Pick the canonical supplier_id to keep (usually the older one).
2. Update all references to the to-be-merged supplier_id → the canonical one. UPDATE agreements SET supplier_id = canonical WHERE supplier_id = to_merge; Same for spend and supplier_name_resolutions.
3. Don't delete the merged supplier row; mark it with a status (or move to an archived_suppliers table). Future re-extractions that produce raw names matching the archived supplier should now resolve to the canonical.

Splitting a supplier

Discovered that "Acme Corp" was incorrectly merged from two distinct entities:

1. Create a new supplier_id for the split-off entity.
2. Move references that belong to the new entity: identify by the raw_supplier_name on extracted records.
3. Update supplier_name_resolutions rows for the raw names that should now point to the new ID.

Splitting is risky — easy to move the wrong rows. Test on a sandbox first; require human sign-off.

Setting a parent

One-line update:

UPDATE suppliers SET parent_supplier_id = 'sup_acme_holdings'
WHERE supplier_id = 'sup_acme_cloud';

Auditing a resolution

When a customer asks "why does this contract show supplier X?":

SELECT
  snr.raw_supplier_name,
  s.canonical_name,
  snr.resolution_method,
  snr.confidence,
  snr.resolved_at,
  snr.resolved_by
FROM supplier_name_resolutions snr
JOIN suppliers s ON s.supplier_id = snr.resolved_supplier_id
WHERE snr.raw_supplier_name ILIKE '%acme%'
  AND snr.customer_id = 'cust_acme'
ORDER BY snr.resolved_at DESC;