Section A · Core entities

Suppliers & Entity Resolution

The supplier model, parent hierarchies, resolution techniques (exact, fuzzy, customer-master, manual), and the raw-vs-canonical storage pattern that lets you re-resolve when the algorithm changes.

The suppliers table

From chapter 01:

suppliers.sql

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.

layered_resolution.py

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:

root_parent.sql

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 query above does not loop. It starts from suppliers with no parent, and a cycle cannot be reached from any root, so its members silently drop out of the result. Compare the row count of the result with the row count of suppliers, or add a check for suppliers that never appear in the chain.

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:

Create a new row in suppliers with the normalized name as canonical_name.
Log the raw name to supplier_name_resolutions with method new_creation.
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:

Pick the canonical supplier_id to keep (usually the older one).
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.
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:

Create a new supplier_id for the split-off entity.
Move references that belong to the new entity: identify by the raw_supplier_name on extracted records.
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;

✓ Check yourself

Answer from memory before opening each one.

Resolution tries tax ID, then normalized exact name, then fuzzy match. Why that order, and what goes wrong if fuzzy match runs first?

Show answer
The order runs from most certain and cheapest to least certain and dearest. A tax ID match is definitive, so nothing later should override it. If fuzzy runs first, a 0.9 score between two different legal entities can win over an exact identifier and merge them. See resolution techniques.
Three months after launch you learn that Acme Corp and Acme Holdings were merged by mistake. What lets you fix it without re-extracting, and what would have made it impossible?

Show answer
Raw extracted names and each resolution decision are kept in supplier_name_resolutions. You create the split-off supplier and repoint the resolutions and records by raw name, with human sign-off. Had the raw names been replaced by canonical ones, there would be no way to tell which rows belonged to which entity. See raw vs canonical names and splitting a supplier.
A fuzzy match scores 0.82 between "Northwind Logistics" and "Northwind Logistics Holdings", and the threshold is 0.85. What does the layered code do with it, and is that the right default?

Show answer
It skips the fuzzy layer, tries the customer master, and otherwise creates a new supplier flagged for review because candidates were close. That is a sound default: a duplicate is cheap to merge later, while a wrong merge corrupts rollups and is hard to see. See resolution techniques.
After a data load, the rollup query returns fewer suppliers than the suppliers table holds, and no error. What do you suspect, and why does it stay silent?

Show answer
A cycle in parent_supplier_id, such as A to B to A. The recursive query starts from suppliers with no parent, and a cycle is never reachable from a root, so its members drop out of the result without looping. Compare the result count with the table count to catch it. See parent hierarchy.
The customer's ERP supplier master lists Acme Corp and Acme Corporation as two rows. Do you merge them in your table, and why?

Show answer
Not silently. Their downstream systems reference IDs from their master, so a quiet merge on your side can break joins in their dashboards. Surface the pair as a cleanup recommendation and let the customer confirm it. See working with the customer's supplier master.

Takeaway

Resolution is layered from certain and cheap, such as tax ID and exact name, to uncertain and dear, such as fuzzy match, a model and human review.
Never delete raw extracted names; keeping them with the method and confidence of each decision is what makes merges reversible.
When a candidate is close but below the threshold, create a new supplier and flag it. A duplicate is cheaper to fix than a wrong merge.
The customer's supplier master is the default source of truth for canonical names, and duplicates inside it are proposed for cleanup, not merged silently.
A parent hierarchy is a tree walked from the roots, so a cycle drops its members from the result instead of failing. Check row counts.
Merging and splitting suppliers repoint records but never delete the old row, and splits need review.

Documents are the physical files that agreements and extractions hang from. → Documents