Two Records, One Driver: Entity Resolution for People and Vehicles in a Service Backend

The Tuesday Where One Driver Became Four Rows

A customer walked in with a set of winter wheels he had stored with us the previous spring. The counter staffer searched his surname, found nothing useful, and created a new customer row. His wheels were in the rack under a tag that referenced a VIN. His spring visit had been entered from a web form under "Jonathan." The autumn visit before that had been entered by phone under "Jon." A fourth row existed because someone had typed his mobile number with the last digit transposed.

Four rows. One person. One vehicle. The storage tag was the only thing that saved us, and the storage tag only worked because a technician had photographed the VIN plate through the windshield eighteen months earlier.

That is entity resolution, and it is not an exotic problem reserved for banks and hospitals. Any operation that accepts identity from humans under time pressure will manufacture duplicates. Ours does roughly fifteen thousand vehicle interactions a year across tire and oil work, which is small enough that people assume the problem does not exist and large enough that it absolutely does. This is a walkthrough of how we model it: what we normalize, how we generate candidates, how we score them, and — the part most write-ups skip — how we undo a bad decision six months later without corrupting history.

Duplicates Are Manufactured, Not Discovered

It helps to stop thinking of duplicates as data-quality accidents and start thinking of them as the predictable output of specific mechanisms. We catalogued ours. Each one needs a different countermeasure, which is why a single "fuzzy match on name" heuristic never works.

1. Transcription noise under time pressure. During the two-week crunch around a seasonal changeover, intake volume triples and the person entering data is standing, holding a tablet, with three people behind the first. Digit transposition in phone numbers spikes measurably in those weeks. So do truncated surnames.
2. Provenance divergence. A record born in the self-serve web form has a validated email and a legal-form name. A record born at the counter has a nickname and no email. A record born from a voice conversation has whatever the staffer heard. These three describe the same human and share almost no exact tokens.
3. Shared household numbers. Two adults, one landline or one mobile listed for both. Phone agreement is strong evidence for a household and weak evidence for a person, and conflating those two is the single most common way a naive matcher fuses a married couple into one entity.
4. Number recycling. Carriers reissue numbers. A number that identified one customer in 2021 can identify an unrelated person in 2026. Any rule that treats phone equality as identity equality without a temporal guard will eventually merge strangers.
5. Plate transfer. In Alberta the plate follows the registrant, not the vehicle. Someone sells a car, keeps the plate, puts it on the replacement. Plate is therefore a pointer that gets reassigned, and treating it as a vehicle key silently glues two vehicles' histories together.
6. VIN captured from a photograph. The dash plate is behind glass at an angle, often dirty, often in a dark bay. Optical character recognition and human eyes both confuse S with 5, B with 8, and 0 with D. One wrong character produces a vehicle that has never existed.
7. Fleet versus retail confusion. A fleet program account has a dispatcher whose phone appears on forty vehicles. If the dispatcher is modelled as the customer, forty vehicles collapse into one relationship and the per-unit history becomes unusable.
8. Name variants that are not typos. Diacritics dropped, hyphenated surnames split, given names anglicized, married names adopted mid-history. These are legitimately different strings for the same person, and a strict comparison will never join them.
9. Deliberate separation. Occasionally a person wants two profiles — a personal vehicle and a business vehicle — and the "duplicate" is intentional. A resolution system with no way to record "these are known to be distinct" will re-merge them every night.

That last one matters more than it seems. Any deduplication pipeline that cannot store a negative assertion will thrash forever.

Identifier Stability Beats Identifier Convenience

Before writing any comparison logic, rank your identifiers by how stable they are over time and how strongly they bind to the thing you care about. Convenience and stability are almost inversely correlated, which is why systems drift toward the wrong keys.

Notice that the two identifiers a staffer can grab fastest — plate and phone — are both pointers rather than identities. That mismatch is the core tension. You need the fast ones to find candidates and the slow ones to confirm them.

Normalize First, Or Everything Downstream Is Noise

Everything that follows assumes a normalization layer that is deterministic, versioned, and stored separately from the raw input. Three properties matter.

Normalization must be pure and reproducible: given the same raw string and the same normalizer version, you get the same output forever. Store the version alongside the output so you can tell which records were processed by which logic. When you fix a bug in surname folding, you need to know exactly which derived values are now stale.

Normalization must never destroy the original. Persist the raw string the human typed. You will need it during a review, and you will need it when a regulator or a customer asks what you actually recorded. The normalized value is a derived index, not a replacement.

Normalization must fail loudly rather than guess. If a phone string cannot be resolved to a valid North American number, record it as unusable with a reason code instead of coercing it into something plausible. Silent coercion is how you end up with three hundred customers whose phone is +10000000000.

E.164 Is The Only Phone Format Worth Persisting

Every phone comparison in the system operates on E.164 strings and nothing else. The normalizer below handles the input shapes we actually see: parenthesized area codes, dotted separators, a leading long-distance 1, trailing extensions, and pasted international strings that begin with the North American escape prefix.

import re
from typing import NamedTuple, Optional


class PhoneResult(NamedTuple):
    e164: Optional[str]
    reason: str


_EXTENSION = re.compile(r"\b(?:ext|extn|x|poste)\.?\s*\d{1,6}\s*$", re.I)
_NON_DIGIT = re.compile(r"[^0-9]")


def normalize_nanp(raw: str) -> PhoneResult:
    """Reduce a North American phone string to E.164, or explain why not."""
    if not raw:
        return PhoneResult(None, "empty")
    text = _EXTENSION.sub("", raw.strip())
    digits = _NON_DIGIT.sub("", text)
    if digits.startswith("011"):
        return PhoneResult(None, "international_escape_prefix")
    if len(digits) == 11 and digits.startswith("1"):
        digits = digits[1:]
    if len(digits) != 10:
        return PhoneResult(None, "wrong_length:{}".format(len(digits)))
    npa, nxx = digits[0:3], digits[3:6]
    if npa[0] in "01" or nxx[0] in "01":
        return PhoneResult(None, "invalid_npa_or_nxx")
    if npa[1] == "1" and npa[2] == "1":
        return PhoneResult(None, "n11_service_code")
    return PhoneResult("+1" + digits, "ok")

Running it over a handful of representative inputs:

'(403) 555-0142'           -> PhoneResult(e164='+14035550142', reason='ok')
'1-403-555-0142'           -> PhoneResult(e164='+14035550142', reason='ok')
'+1 403 555 0142 ext 12'   -> PhoneResult(e164='+14035550142', reason='ok')
'403.555.014'              -> PhoneResult(e164=None, reason='wrong_length:9')
'011 44 20 7946 0000'      -> PhoneResult(e164=None, reason='international_escape_prefix')
'211-555-0142'             -> PhoneResult(e164=None, reason='n11_service_code')

Two deliberate choices. Extensions are stripped rather than preserved in the match key, because an extension identifies a desk inside an organization and two people at the same organization should not fuse. We keep the extension in the raw record and in a separate column; it just does not participate in comparison. And the N11 guard exists because someone will eventually paste a service number into the field. The 555-01xx numbers used throughout this article are from the range reserved for fictional use, which is why they appear in sample data without hesitation.

Implementing The VIN Check Digit Without Getting It Wrong

The VIN is the only identifier in this domain with built-in error detection, and it is astonishing how many systems store VINs without ever using it. North American VINs assigned since 1981 carry a check digit in position nine. Getting the algorithm right is not optional: a subtly wrong implementation is worse than none, because it rejects good data and passes bad data.

Three details trip people up. First, the letters I, O, and Q are excluded from the VIN alphabet entirely, precisely because they are confusable with 1 and 0. Any VIN containing them is malformed, full stop. Second, the transliteration table is not "position in the alphabet" — it wraps, so J maps to 1 and S maps to 2. Third, position nine contributes weight zero to its own sum, and a remainder of 10 is written as the letter X.

VIN_TRANSLITERATION = {
    "A": 1, "B": 2, "C": 3, "D": 4, "E": 5, "F": 6, "G": 7, "H": 8,
    "J": 1, "K": 2, "L": 3, "M": 4, "N": 5, "P": 7, "R": 9,
    "S": 2, "T": 3, "U": 4, "V": 5, "W": 6, "X": 7, "Y": 8, "Z": 9,
}
VIN_WEIGHTS = (8, 7, 6, 5, 4, 3, 2, 10, 0, 9, 8, 7, 6, 5, 4, 3, 2)
VIN_ALPHABET = set("0123456789") | set(VIN_TRANSLITERATION)


class VinError(ValueError):
    pass


def vin_check_digit(vin: str) -> str:
    """Return the character that belongs in position 9 of a 17-character VIN."""
    vin = (vin or "").strip().upper()
    if len(vin) != 17:
        raise VinError("length {} is not 17".format(len(vin)))
    total = 0
    for position, ch in enumerate(vin):
        if ch not in VIN_ALPHABET:
            raise VinError("character {!r} is illegal at position {}".format(ch, position + 1))
        value = int(ch) if ch.isdigit() else VIN_TRANSLITERATION[ch]
        total += value * VIN_WEIGHTS[position]
    remainder = total % 11
    return "X" if remainder == 10 else str(remainder)


def vin_is_valid(vin: str) -> bool:
    try:
        return vin_check_digit(vin) == vin.strip().upper()[8]
    except VinError:
        return False

Exercising it against a synthetic VIN — ZZZ is not an assigned world manufacturer identifier, so this string cannot collide with a real vehicle — plus the widely published test vector 1M8GDM9AXKP042788, whose check digit is X:

check digit of ZZZTESTV9N9SB4207 is 9 | valid: True
S read as 5: False
B read as 8: False
digits swapped: False
illegal letter Q: False
known reference 1M8GDM9AXKP042788 -> X

Every one of those failure cases is a real transcription error we have seen when a VIN is keyed in from a photograph taken through a windshield.

What The Check Digit Cannot Save You From

Modulo-11 with those weights catches all single-character substitutions and all transpositions of adjacent characters. It does not catch everything. Roughly one in eleven random 17-character strings will pass by coincidence, so validity is evidence, not proof. More importantly, a VIN can be perfectly valid and still be the wrong vehicle — someone reads the VIN off the paperwork of the car parked in the next bay, or a fleet coordinator pastes the wrong row from a spreadsheet.

So we treat check-digit validity as a gate on storage confidence, not on truth. A VIN that fails validation is retained in the raw record, flagged, and excluded from blocking and scoring. A VIN that passes gets a confidence tier based on how it was captured: scanned barcode outranks manual entry, which outranks a value derived from a photograph. When two records disagree on a valid VIN, that disagreement is a hard veto rather than a negative weight, because a confirmed VIN conflict means you are looking at two different vehicles regardless of how much else matches.

There is also a population wrinkle worth knowing. Vehicles built before the 1981 standardization, some imported units, and a scattering of trailers and equipment carry VINs that are shorter than 17 characters or that do not follow the check-digit convention at all. If you serve any commercial customers, you will meet these. Model the field as "vehicle identifier with a scheme tag" rather than "17-character VIN," and your data layer stops lying to you.

Plates Are Leases, Not Primary Keys

Plate handling needs two different canonical forms, and conflating them causes a specific class of bug.

The comparison form strips whitespace and punctuation and uppercases. Nothing more. BXK 4471 and bxk-4471 are the same plate; BXK4471 and 8XK4471 are not, because on a real plate the glyphs are distinct and the registry issued exactly one of them.

The blocking form is deliberately lossier. It folds visually confusable glyph pairs together so that a mis-keyed plate still lands in the same bucket as the correct one. This form exists only to generate candidates. It must never be used to decide anything.

import re
import unicodedata

NAME_SUFFIXES = {"jr", "sr", "ii", "iii", "iv"}
_NAME_JUNK = re.compile(r"[^a-z ]+")
_PLATE_JUNK = re.compile(r"[^A-Z0-9]+")
_GLYPH_FOLD = str.maketrans({"O": "0", "Q": "0", "I": "1", "S": "5", "B": "8", "Z": "2"})


def canonical_plate(raw: str) -> str:
    """Exact-comparison form: case and separators removed, glyphs preserved."""
    return _PLATE_JUNK.sub("", (raw or "").upper())


def plate_block_key(raw: str) -> str:
    """Recall-oriented form: confusable glyphs collapsed. Candidates only."""
    return canonical_plate(raw).translate(_GLYPH_FOLD)


def fold_name(raw: str) -> str:
    if not raw:
        return ""
    decomposed = unicodedata.normalize("NFKD", raw)
    ascii_only = "".join(c for c in decomposed if not unicodedata.combining(c))
    lowered = ascii_only.lower().replace("-", " ").replace("'", "")
    parts = [p for p in _NAME_JUNK.sub(" ", lowered).split() if p not in NAME_SUFFIXES]
    return " ".join(parts)


_SOUNDEX = {}
for group, code in (("bfpv", "1"), ("cgjkqsxz", "2"), ("dt", "3"),
                    ("l", "4"), ("mn", "5"), ("r", "6")):
    for letter in group:
        _SOUNDEX[letter] = code


def soundex(word: str) -> str:
    word = fold_name(word).replace(" ", "")
    if not word:
        return ""
    out = word[0].upper()
    previous = _SOUNDEX.get(word[0], "")
    for ch in word[1:]:
        code = _SOUNDEX.get(ch, "")
        if code and code != previous:
            out += code
        if ch not in "hw":
            previous = code
        if len(out) == 4:
            break
    return (out + "000")[:4]

Beyond canonicalization, plates need a validity window. We store plate observations as time-bounded facts attached to a vehicle, not as a column on the vehicle row. When a customer arrives with a plate that we last saw on a different VIN, that is not a data error to reconcile — it is a transfer event to record. Losing that distinction is how a returning customer's wheel balancing history from a sold vehicle ends up attached to the car they bought last month.

Folding Names Without Flattening People

Name folding is the step where enthusiasm does the most damage. Aggressive folding boosts recall and destroys precision, and in a small population the precision loss is what people notice, because merging two real customers is visible and embarrassing while missing a duplicate is merely inefficient.

Our folding does four things and stops: Unicode decomposition with combining marks removed, lowercasing, hyphen and apostrophe normalization to spaces or nothing, and removal of generational suffixes. D'Angelo-Rossi, Jr becomes dangelo rossi. We do not stem, we do not apply nickname dictionaries at the folding stage, and we do not reorder tokens.

Nickname expansion happens later, as a scoring signal rather than a normalization step, and it is directional. "Jon" being a prefix of "Jonathan" earns partial credit. A curated table maps "Bill" toward "William" and "Sasha" toward "Alexander" — but as evidence with a weight, never as a rewrite. The moment you rewrite the stored value, you have lost the ability to tell a reviewer why the system thought two records matched.

Soundex is the phonetic key we use for blocking. It is old, crude, and heavily biased toward English orthography, which is a genuine weakness in a city as linguistically varied as this one. Its saving grace is that it is cheap and its failure mode is predictable: it over-groups, which is exactly what you want from a candidate generator. Okonkwo and Okonquo both fold to O252. It is not doing the deciding.

Candidate Generation, Or How To Avoid n² Comparisons

With a hundred thousand source records, all-pairs comparison is five billion evaluations. Even at a microsecond each that is over an hour of CPU for a nightly job, and the comparison function is not going to be a microsecond. Blocking is the standard escape: emit one or more keys per record, and only compare records that share a key.

The tradeoff is explicit. A narrow key produces small blocks and fast runs but misses pairs whose linking evidence lives in a field the key ignores. A broad key produces high recall and blocks large enough to reintroduce the quadratic problem inside a single bucket. The answer is several independent narrow keys rather than one clever broad one, with a hard size cap that skips pathological blocks and reports them.

def blocking_keys(rec: dict) -> set:
    keys = set()
    vin = (rec.get("vin") or "").upper()
    if len(vin) == 17:
        keys.add("vin8:" + vin[-8:])
        keys.add("vin6:" + vin[-6:])
    plate = plate_block_key(rec.get("plate"))
    if len(plate) >= 5:
        keys.add("plt:" + plate)
    phone = rec.get("phone_e164") or ""
    if len(phone) == 12:
        keys.add("ph7:" + phone[-7:])
    surname_key = soundex(rec.get("surname"))
    given = fold_name(rec.get("given_name"))
    if surname_key:
        if given:
            keys.add("nm:{}{}".format(surname_key, given[0]))
        if len(phone) == 12:
            keys.add("nmph:{}{}".format(surname_key, phone[-4:]))
        fsa = (rec.get("postal") or "").upper().replace(" ", "")[:3]
        if len(fsa) == 3:
            keys.add("nmfsa:{}{}".format(surname_key, fsa))
    return keys


MAX_BLOCK_SIZE = 60


def candidate_pairs(records):
    from collections import defaultdict
    index = defaultdict(list)
    for rec in records:
        for key in blocking_keys(rec):
            index[key].append(rec["id"])
    pairs, skipped = set(), []
    for key, ids in index.items():
        if len(ids) < 2:
            continue
        if len(ids) > MAX_BLOCK_SIZE:
            skipped.append((key, len(ids)))
            continue
        for i in range(len(ids)):
            for j in range(i + 1, len(ids)):
                pairs.add(tuple(sorted((ids[i], ids[j]))))
    return pairs, skipped

Here is the synthetic fixture used for the rest of this article. Invented people, invented plates, one synthetic VIN.

# synthetic sample data: invented people, invented plates, synthetic VIN
SAMPLE = [
    {"id": "r1", "given_name": "Jonathan", "surname": "Okonkwo", "phone_e164": "+14035550142",
     "plate": "BXK4471", "vin": "ZZZTESTV9N9SB4207", "postal": "T2N 1N4", "origin": "web_form"},
    {"id": "r2", "given_name": "Jon", "surname": "Okonkwo", "phone_e164": "+14035550142",
     "plate": "bxk 4471", "vin": "", "postal": "T2N1N4", "origin": "front_counter"},
    {"id": "r3", "given_name": "Marie", "surname": "Okonkwo", "phone_e164": "+14035550142",
     "plate": "DHR2210", "vin": "", "postal": "T2N1N4", "origin": "front_counter"},
    {"id": "r4", "given_name": "Jonathan", "surname": "Okonkwo", "phone_e164": "+14035550143",
     "plate": "", "vin": "", "postal": "T2N1N4", "origin": "voice_intake"},
    {"id": "r5", "given_name": "Jonathan", "surname": "Okonkwo", "phone_e164": "",
     "plate": "", "vin": "ZZZTESTV9N9SB4207", "postal": "", "origin": "storage_tag"},
]

Picking Block Keys That Recall Without Exploding

The MAX_BLOCK_SIZE cap deserves a paragraph of its own, because it is where blocking meets reality. A key like "soundex of surname plus first initial" behaves beautifully for O252j and catastrophically for the local equivalent of S530j. One oversized bucket can dominate an entire run.

We cap at sixty and emit skipped keys to a metric rather than silently dropping them. If a key is skipped, the pairs inside it were never evaluated, and pretending otherwise makes your recall measurement a fiction. In practice the skipped list is short and dominated by two things: the surname keys for very common names, and the phone key belonging to a dispatcher whose number is attached to an entire commercial vehicle fleet. The second case gets handled structurally rather than by tuning — dispatcher numbers are tagged as organizational and excluded from person-level blocking entirely.

Two more habits earn their keep. Suffix keys beat prefix keys for VINs, because the last eight characters are the sequential serial and carry far more entropy than the manufacturer prefix; blocking on the first eight would put every vehicle from one marque in one bucket. And compound keys beat single-field keys when both components are individually weak: surname phonetic plus last four digits of phone is far more selective than either half, and it survives an area-code error that a full-phone key would not.

Running the generator over the fixture yields nine candidate pairs from five records — a reminder that even tiny populations produce a lot of comparisons once several keys are in play.

Weighting Field Agreement With Numbers You Can Defend

Once you have pairs, you need a score. The Fellegi–Sunter framework is fifty years old and still the right starting point because it forces you to state two probabilities per field and then does the arithmetic honestly.

For each field you estimate m, the probability that the field agrees given the pair is genuinely the same entity, and u, the probability that it agrees given the pair is not. Agreement contributes log2(m/u) bits; disagreement contributes log2((1-m)/(1-u)), which is negative. Missing data contributes zero, which is the property that makes this approach so much better than an ad-hoc percentage: an absent VIN neither helps nor hurts.

import math
from dataclasses import dataclass


@dataclass(frozen=True)
class FieldModel:
    m: float   # P(field agrees | same entity)
    u: float   # P(field agrees | different entities)

    @property
    def agree_weight(self) -> float:
        return math.log2(self.m / self.u)

    @property
    def disagree_weight(self) -> float:
        return math.log2((1.0 - self.m) / (1.0 - self.u))


MODELS = {
    "vin":       FieldModel(m=0.94, u=0.0000005),
    "plate":     FieldModel(m=0.88, u=0.0000020),
    "household": FieldModel(m=0.90, u=0.0035000),
    "surname":   FieldModel(m=0.92, u=0.0030000),
    "given":     FieldModel(m=0.85, u=0.0120000),
    "email":     FieldModel(m=0.55, u=0.0002000),
}
PARTIAL_CREDIT = 0.35


def weight_for(field: str, level: str) -> float:
    model = MODELS[field]
    if level == "agree":
        return model.agree_weight
    if level == "partial":
        return model.agree_weight * PARTIAL_CREDIT
    if level == "disagree":
        return model.disagree_weight
    return 0.0

Which produces this weight table:

vin        agree +20.84  disagree  -4.06
plate      agree +18.75  disagree  -3.06
household  agree  +8.01  disagree  -3.32
surname    agree  +8.26  disagree  -3.64
given      agree  +6.15  disagree  -2.72
email      agree +11.43  disagree  -1.15

Where do the numbers come from? The u values are estimated from the data itself: sample random pairs from your population, measure how often each field agrees by chance, and use that frequency. For VIN the chance-agreement rate is essentially the reciprocal of the distinct-VIN count, hence the tiny value and the enormous weight. The m values are harder and require a labelled sample; start with a defensible guess, then refine against clerical review outcomes. Expectation-maximization can estimate both without labels, but on a population this size the estimates are unstable and I would rather label eight hundred pairs by hand and know what I have.

One refinement that matters: u should be frequency-adjusted for the specific value, not just the field. Agreement on a surname held by 0.4% of your customers is far weaker evidence than agreement on one held by 0.004%. The full treatment computes per-value weights; a cheap approximation is to halve the surname weight whenever the folded value appears in the top fifty most frequent surnames in your own table.

Conditional Independence Is A Lie In A Household

The naive Fellegi–Sunter sum assumes fields are conditionally independent given match status. Phone and postal code are not remotely independent — people who share an address usually share a phone. Summing both as separate evidence double-counts the same underlying fact and inflates scores for exactly the pairs you most need to keep apart: family members.

The pragmatic fix is to collapse correlated fields into a single composite comparison. Our household field takes phone agreement if both phones exist, and falls back to forward sortation area agreement otherwise, capped at partial credit. One field, one weight, no double counting.

def compare_levels(left: dict, right: dict):
    lv, rv = (left.get("vin") or "").upper(), (right.get("vin") or "").upper()
    if len(lv) == 17 and len(rv) == 17:
        yield "vin", "agree" if lv == rv else ("partial" if levenshtein(lv, rv) <= 2 else "disagree")
    else:
        yield "vin", "unknown"

    lp, rp = canonical_plate(left.get("plate")), canonical_plate(right.get("plate"))
    yield "plate", ("agree" if lp == rp else "disagree") if lp and rp else "unknown"

    lph, rph = left.get("phone_e164") or "", right.get("phone_e164") or ""
    lfsa = (left.get("postal") or "").upper().replace(" ", "")[:3]
    rfsa = (right.get("postal") or "").upper().replace(" ", "")[:3]
    if lph and rph:
        if lph == rph:
            yield "household", "agree"
        elif levenshtein(lph, rph) == 1:
            yield "household", "partial"
        else:
            yield "household", "disagree"
    elif lfsa and rfsa:
        yield "household", "partial" if lfsa == rfsa else "disagree"
    else:
        yield "household", "unknown"

    ls, rs = fold_name(left.get("surname")), fold_name(right.get("surname"))
    if ls and rs:
        near = soundex(ls) == soundex(rs) or edit_ratio(ls, rs) >= 0.80
        yield "surname", "agree" if ls == rs else ("partial" if near else "disagree")
    else:
        yield "surname", "unknown"

    lg, rg = fold_name(left.get("given_name")), fold_name(right.get("given_name"))
    if lg and rg:
        clipped = lg.startswith(rg) or rg.startswith(lg)
        yield "given", "agree" if lg == rg else ("partial" if clipped or edit_ratio(lg, rg) >= 0.75 else "disagree")
    else:
        yield "given", "unknown"

    le, rr = (left.get("email") or "").strip().lower(), (right.get("email") or "").strip().lower()
    yield "email", ("agree" if le == rr else "disagree") if le and rr else "unknown"

The two string helpers it leans on are the ordinary dynamic-programming edit distance and its length-normalized complement:

def levenshtein(a: str, b: str) -> int:
    if a == b:
        return 0
    if not a or not b:
        return len(a) + len(b)
    previous = list(range(len(b) + 1))
    for i, ca in enumerate(a, 1):
        current = [i]
        for j, cb in enumerate(b, 1):
            current.append(min(previous[j] + 1, current[j - 1] + 1, previous[j - 1] + (ca != cb)))
        previous = current
    return previous[-1]


def edit_ratio(a: str, b: str) -> float:
    if not a or not b:
        return 0.0
    return 1.0 - levenshtein(a, b) / max(len(a), len(b))

Note that a phone one edit away earns partial credit rather than full — that is the transposition case from intake — while a phone two or more edits away scores as disagreement. The same distance powers the near-miss VIN level, where two characters of slack absorbs a photograph misread without accepting an unrelated unit. Anything looser and you start joining vehicles that differ only in trim code, which the tire fitment catalogue will happily tell you are genuinely different machines.

A Worked Comparison, Bit By Bit

Take r1 (web form, full given name, plate and VIN present) against r3 (counter entry, different given name, same household phone, different plate). Field by field:

• vin: one side is empty, so unknown → 0.00
• plate: BXK4471 versus DHR2210, disagree → −3.06
• household: identical E.164 strings, agree → +8.01
• surname: both fold to okonkwo, agree → +8.26
• given: jonathan versus marie, neither prefix nor near, disagree → −2.72
• email: absent on both sides, unknown → 0.00

Total: 10.49 bits. With an auto-link threshold of 12.0, that lands in the review band — which is the correct answer. These two records probably describe two adults in one household. Without the composite household field, phone and postal would have contributed separately and the pair would have crossed the threshold and merged a couple.

Contrast with r1 against r5, the wheel-storage tag that carries a VIN and a name but no phone or address. VIN agrees (+20.84), surname agrees (+8.26), given name agrees (+6.15), everything else unknown. Total 35.25 bits, comfortably a link, driven almost entirely by one strong identifier. That asymmetry is the whole point: a single high-quality field beats a pile of weak corroboration.

Deterministic Vetoes Over Probabilistic Enthusiasm

Probabilistic scoring is the right default, but there are facts about this domain that no weight should be allowed to outvote. Those go in a veto layer that runs after scoring and can only demote a decision, never promote one.

AUTO_LINK_AT = 12.0
REVIEW_FLOOR_AT = 3.0


def hard_block_reason(levels: dict) -> Optional[str]:
    vehicle_anchor = levels["vin"] == "agree" or levels["plate"] == "agree"
    if levels["vin"] == "disagree":
        return "vin_conflict"
    if levels["given"] == "disagree" and not vehicle_anchor:
        return "given_name_conflict_without_vehicle_anchor"
    return None


def decide(left: dict, right: dict):
    rows = [(f, lvl, weight_for(f, lvl)) for f, lvl in compare_levels(left, right)]
    total = sum(w for _, _, w in rows)
    levels = {f: lvl for f, lvl, _ in rows}
    reason = hard_block_reason(levels)
    if total >= AUTO_LINK_AT:
        verdict = "review" if reason else "link"
    elif total >= REVIEW_FLOOR_AT:
        verdict = "review"
    else:
        verdict = "distinct"
    return {"score": round(total, 2), "verdict": verdict, "veto": reason, "rows": rows}

Full run over the fixture:

r1~r2   37.17 link     -
        vin=unknown plate=agree household=agree surname=agree given=partial email=unknown
r1~r3   10.49 review   given_name_conflict_without_vehicle_anchor
        vin=unknown plate=disagree household=agree surname=agree given=disagree email=unknown
r1~r4   17.21 link     -
        vin=unknown plate=unknown household=partial surname=agree given=agree email=unknown
r1~r5   35.25 link     -
        vin=agree plate=unknown household=unknown surname=agree given=agree email=unknown
r3~r4    8.34 review   given_name_conflict_without_vehicle_anchor
        vin=unknown plate=unknown household=partial surname=agree given=disagree email=unknown

When does each approach win? Here is the honest division of labour:

Our production pipeline is deterministic first, probabilistic second, veto third. Exact agreement on a validated VIN links immediately without scoring. Everything else goes through weights. Vetoes then pull suspicious links down into review.

The Review Band Is Deliberate Engineering

The gap between REVIEW_FLOOR_AT and AUTO_LINK_AT is not an admission that the model is weak. It is a budget. Fellegi–Sunter's original formulation sets the two thresholds from your tolerated false-match and false-non-match rates, and everything in between is routed to a human. Widen the band and you buy accuracy with labour; narrow it and you buy throughput with errors.

Sizing it is an operations question, not a statistics question. We measured how many pairs land in the band per week and set the thresholds so that the queue stays under what one person can clear in twenty minutes a day, with headroom for the changeover crunch when volume triples. If the queue grows faster than it drains, the band is wrong or the upstream capture is degrading; either way it is a signal, not a chore.

The review interface matters more than the model. A reviewer needs the raw values side by side — not the normalized ones — the per-field weight breakdown, the provenance of each record, and the service history of both. Three outcomes are available: link, distinct, or defer with a note. "Distinct" writes a negative assertion that suppresses the pair permanently, which is what stops the nightly job from re-proposing the same married couple every week for a year.

Persist Links, Never Overwrite Rows

Here is the structural decision that separates systems you can live with from systems you cannot: never destructively merge. Keep every source record exactly as captured, immutable, and express identity as a separate layer of assertions on top.

-- Immutable capture. One row per identity assertion made by a human or an import.
CREATE TABLE source_record (
    source_record_id   uuid PRIMARY KEY,
    origin             text NOT NULL,          -- web_form | front_counter | voice_intake | fleet_import | storage_tag
    captured_at        timestamptz NOT NULL,
    location_id        int NOT NULL,
    raw_payload        jsonb NOT NULL,         -- exactly what was typed, never edited
    normalizer_version int,
    given_name_folded  text,
    surname_folded     text,
    surname_phonetic   text,
    phone_e164         text,
    phone_reject_code  text,
    email_normalized   text,
    plate_canonical    text,
    vin_upper          char(17),
    vin_check_ok       boolean,
    postal_fsa         char(3)
);

CREATE INDEX source_record_phone_idx ON source_record (phone_e164) WHERE phone_e164 IS NOT NULL;
CREATE INDEX source_record_vin_idx   ON source_record (vin_upper)  WHERE vin_check_ok;
CREATE INDEX source_record_plate_idx ON source_record (plate_canonical);

-- Pairwise assertions. Positive and negative, both first class.
CREATE TABLE identity_link (
    identity_link_id  bigserial PRIMARY KEY,
    entity_kind       text NOT NULL CHECK (entity_kind IN ('person', 'vehicle')),
    left_record_id    uuid NOT NULL,
    right_record_id   uuid NOT NULL,
    assertion         text NOT NULL CHECK (assertion IN ('same', 'distinct')),
    score_bits        numeric(6,2),
    veto_reason       text,
    decided_by        text NOT NULL,          -- 'auto:v7' or a staff identifier
    decided_at        timestamptz NOT NULL DEFAULT now(),
    retired_at        timestamptz,
    retired_reason    text,
    CHECK (left_record_id < right_record_id),
    FOREIGN KEY (left_record_id)  REFERENCES source_record,
    FOREIGN KEY (right_record_id) REFERENCES source_record
);

CREATE UNIQUE INDEX identity_link_live_idx
    ON identity_link (entity_kind, left_record_id, right_record_id)
    WHERE retired_at IS NULL;

-- Materialized closure. Cheap to rebuild, never a source of truth.
CREATE TABLE identity_cluster (
    cluster_id     uuid PRIMARY KEY,
    entity_kind    text NOT NULL,
    generation     int NOT NULL,
    computed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE cluster_member (
    cluster_id       uuid NOT NULL REFERENCES identity_cluster,
    source_record_id uuid NOT NULL REFERENCES source_record,
    PRIMARY KEY (cluster_id, source_record_id)
);

Three properties fall out of this shape. The raw capture is never mutated, so an audit can always reconstruct what a human actually entered. Identity is a set of retractable assertions, so reversing a decision means retiring a row rather than reconstructing deleted data. And the cluster table is derived, disposable, and versioned by generation, so you can rebuild the whole closure after a weight change and diff the result before promoting it.

The CHECK (left_record_id < right_record_id) constraint plus the partial unique index means one live assertion per unordered pair, enforced by the database rather than by hope.

Materializing Clusters With Union-Find

Turning pairwise assertions into groups is a connected-components problem, and disjoint-set union is the obvious tool. You will write this whether you plan to or not, so write it well.

class DisjointSet:
    def __init__(self):
        self.parent = {}
        self.rank = {}

    def add(self, item):
        self.parent.setdefault(item, item)
        self.rank.setdefault(item, 0)

    def find(self, item):
        self.add(item)
        root = item
        while self.parent[root] != root:
            root = self.parent[root]
        while self.parent[item] != root:          # path compression
            self.parent[item], item = root, self.parent[item]
        return root

    def union(self, a, b):
        ra, rb = self.find(a), self.find(b)
        if ra == rb:
            return False
        if self.rank[ra] < self.rank[rb]:
            ra, rb = rb, ra
        self.parent[rb] = ra
        if self.rank[ra] == self.rank[rb]:
            self.rank[ra] += 1
        return True


def build_clusters(record_ids, active_links):
    dsu = DisjointSet()
    for rid in record_ids:
        dsu.add(rid)
    for left, right in active_links:
        dsu.union(left, right)
    groups = {}
    for rid in record_ids:
        groups.setdefault(dsu.find(rid), []).append(rid)
    return sorted(sorted(v) for v in groups.values())

Feeding it the three auto-links from the fixture gives [['r1', 'r2', 'r4', 'r5'], ['r3']].

Transitive closure is where entity resolution goes wrong at scale. Union is unconditional: if A links to B and B links to C, then A, B, and C are one cluster even if A and C are wildly dissimilar. One bad link can chain two large groups together, and the failure is invisible until someone notices a customer with forty vehicles.

Two guards are worth the effort. First, run a post-closure sanity pass: for every cluster above a size threshold, evaluate the lowest-scoring internal pair and flag the cluster if that score falls below the review floor. That catches chains held together by a single weak edge. Second, respect negative assertions during closure — if any pair inside a proposed cluster carries a live distinct assertion, refuse the merge and route the whole component to review rather than silently overriding a human.

Unmerge Is A First-Class Operation

You will merge two people wrongly. Plan the reversal before you ship the merge.

Because assertions are retractable and source records are immutable, unmerge is mechanically simple: retire the offending identity_link row with a reason, bump the generation, recompute the closure for the affected component. The cluster splits. No data was destroyed, so nothing needs reconstructing.

What is not simple is everything downstream that captured the merged identity. Consider the artifacts a service visit produces: a work order, an invoice, a wheel-storage tag, a tire-and-rim inspection note, a communication log. If those reference cluster_id directly, an unmerge orphans them or silently reassigns them.

The rule we settled on: transactional records reference source_record_id, never cluster_id. The cluster is a read-time lens, resolved through cluster_member at query time or through a materialized view refreshed after each generation. Any record that must survive an identity change — anything with legal, financial, or safety weight — points at immutable capture. A generated invoice is a document; it names whoever it named when it was issued, and it does not retroactively change owners because a clustering job changed its mind.

External systems need a separate escape hatch. Anything you have already exported — an accounting ledger, a message log, a warranty registration filed with a manufacturer — cannot be unmerged by you. Log every identity change as an event with before and after cluster identifiers so that downstream consumers can reconcile at their own pace. We keep that event stream for seven years, which matches the retention on the financial records it explains.

Ownership Has A Start Date And An End Date

Here is the modelling error I see most often: a vehicle table with an owner_id column. That column asserts a fact with no time bounds, and vehicle ownership is one of the most reliably temporary relationships in this domain. Cars get sold. Fleets rotate units between drivers. A leased truck returns to the lessor.

The relationship needs its own table with validity intervals.

CREATE TABLE vehicle (
    vehicle_id       uuid PRIMARY KEY,
    identifier_scheme text NOT NULL CHECK (identifier_scheme IN ('vin17', 'pre1981', 'serial', 'internal')),
    vin_upper        char(17),
    first_seen_at    timestamptz NOT NULL,
    UNIQUE (identifier_scheme, vin_upper)
);

CREATE TABLE vehicle_custody (
    custody_id     bigserial PRIMARY KEY,
    vehicle_id     uuid NOT NULL REFERENCES vehicle,
    person_cluster uuid NOT NULL,
    valid_period   tstzrange NOT NULL,
    evidence       text NOT NULL,     -- observed_at_visit | stated_by_customer | fleet_roster_import
    EXCLUDE USING gist (vehicle_id WITH =, valid_period WITH &&)
);

CREATE TABLE plate_observation (
    observation_id  bigserial PRIMARY KEY,
    vehicle_id      uuid NOT NULL REFERENCES vehicle,
    plate_canonical text NOT NULL,
    observed_at     timestamptz NOT NULL,
    observed_by     text NOT NULL
);

The EXCLUDE USING gist constraint is the important line. It makes overlapping custody intervals for one vehicle physically impossible at the storage layer, which means the "two owners at once" bug cannot be introduced by an application bug, a race, or a careless backfill.

Plate observations are events, not state. The current plate for a vehicle is the most recent observation; the plate history is the full series. When the same canonical plate appears against a second vehicle_id, the system emits a transfer candidate rather than a conflict, and a reviewer decides whether it was a genuine transfer, a typo, or a vehicle record that was created twice.

Reading Service History Through A Temporal Lens

Once custody is temporal, history questions split into two genuinely different queries, and conflating them produces answers that feel wrong to everybody.

"What has been done to this vehicle?" is a question about the machine. It spans owners. If a set of all-weather tires was fitted two owners ago, that is still the tread on the vehicle today and still relevant when the current driver arrives. The same holds for an all-season fitment with three winters on it, or a spare that was never rotated in.

"What has this person had done?" is a question about the human, and it spans vehicles. It should include only the intervals during which they actually held each vehicle.

-- Everything ever done to one vehicle, annotated with who held it at the time.
SELECT v.visit_id,
       v.performed_at,
       v.service_kind,
       c.person_cluster AS holder_at_time
FROM   service_visit v
JOIN   vehicle_custody c
  ON   c.vehicle_id = v.vehicle_id
 AND   c.valid_period @> v.performed_at
WHERE  v.vehicle_id = $1
ORDER  BY v.performed_at DESC;

-- Everything one person had done, across every vehicle they actually held.
SELECT v.visit_id,
       v.performed_at,
       v.service_kind,
       veh.vin_upper
FROM   service_visit v
JOIN   vehicle_custody c
  ON   c.vehicle_id = v.vehicle_id
 AND   c.valid_period @> v.performed_at
JOIN   vehicle veh ON veh.vehicle_id = v.vehicle_id
WHERE  c.person_cluster = $1
ORDER  BY v.performed_at DESC;

The service_kind enum is deliberately narrow, because our work is narrow: tire_installation, seasonal_changeover, rotation, wheel_balance, flat_repair, tpms_service, wheel_storage_in, wheel_storage_out, oil_change. Nine values, all of them things we actually do. A constrained enum is also a quiet integrity check — if a row shows up that does not fit, something upstream is wrong.

There is a third question the temporal model answers well: "is the tread on this vehicle ours, and how old is it?" That drives a genuinely useful prompt when a vehicle arrives for a puncture repair on rubber we fitted four winters ago, and it only works if the fitment event stayed attached to the vehicle across an ownership change.

Fleet Records Violate Every Retail Assumption

Retail intuitions collapse on the commercial side, and they collapse in ways that are worth enumerating because each one breaks a different part of the pipeline.

The phone number belongs to a dispatcher, not a driver, and appears on every unit. Any person-level rule keyed on phone will fuse the entire roster. We tag organizational numbers explicitly and exclude them from person blocking and from the household comparison entirely.

The name attached to a visit is the driver on shift, which changes weekly. Driver names are attributes of the visit, not identity evidence about the account holder. Feeding them into person resolution generates noise proportional to staff turnover.

Vehicles arrive in batches from a roster import, so provenance is a spreadsheet rather than a human at the counter. Roster imports have their own failure mode: duplicated rows, VINs with a stray leading apostrophe from a spreadsheet export, and units that were sold months ago but never removed. We validate check digits on import and quarantine the failures rather than creating vehicles.

Custody is organizational and the individual driver is irrelevant to it. The person_cluster on a fleet vehicle points at an organization entity, and the driver appears only on the visit. This is also why mobile service visits need the vehicle anchor to be rock solid — there is often nobody present who can confirm anything about the account, just a unit number stencilled on a door.

Finally, unit numbers are a fleet's own internal identifier and they are frequently more reliable in practice than anything else available, because the fleet manager maintains them. Treat a customer-supplied internal identifier as a first-class alias with a namespace: fleet:acme-hauling:unit-114. Namespaced aliases are exact-match evidence with a very small u, which makes them powerful and safe.

Retention, Consent, And The Cost Of Remembering

Identity resolution is a system for remembering people harder than they expect, which puts it squarely inside privacy obligations rather than adjacent to them.

Under Canadian private-sector privacy law the relevant principle is purpose limitation: you collect and retain personal information for identified purposes and no longer than needed to fulfil them. Linking records to avoid duplicate profiles is a defensible purpose — it directly serves service quality and safety. Retaining a decade of phone numbers to build behavioural profiles is a different purpose, and you should not quietly acquire it just because your schema makes it possible.

Practical consequences we implemented. Reason codes on every retention decision, so a stored value can always explain why it is still there. Automatic expiry of unusable capture artifacts: a rejected phone string with a wrong_length code has no ongoing value and is purged after ninety days. Separation of identity data from marketing consent, so that resolving two records into one person never silently transfers a communication preference — consent attaches to the channel and the assertion, not to the cluster.

Deletion is where immutability gets interesting. "Source records are never mutated" cannot mean "we ignore erasure requests." The resolution is tombstoning: the row survives with its identifiers replaced by a redaction marker, the cluster membership survives, and the aggregate history survives in a form that no longer identifies anyone. You lose the ability to re-resolve that record later, which is the correct tradeoff.

One more thing worth stating plainly: every merge you make increases the blast radius of a breach, because a resolved cluster is a richer profile than any of its parts. That is not an argument against resolution. It is an argument for encrypting identifier columns at rest, restricting the review interface to named staff, and logging every access to a cluster view.

Precision, Recall, And The Size Of The Queue

You cannot tune what you do not measure, and "the duplicates seem better" is not a measurement.

Build a labelled sample. Draw a few hundred candidate pairs stratified across the score range — deliberately oversampling near the thresholds, because that is where the decision boundary lives — and have someone adjudicate each one with all available evidence. Several hundred labels is enough to estimate precision and recall with useful confidence at this population size, and it is a day of work, not a project.

From that sample: precision is the fraction of auto-linked pairs that were genuinely the same entity, and recall is the fraction of genuine matches the system linked without human help. Both matter, and they trade off against each other along the threshold. Report them together with the threshold values, always, because a precision number without its operating point is meaningless.

Beyond the pair-level metrics, four population-level indicators earn dashboard space.

-- Duplicate rate: clusters holding more than one source record, by capture channel.
SELECT sr.origin,
       count(*) FILTER (WHERE sizes.n > 1)::numeric / nullif(count(*), 0) AS multi_record_rate,
       avg(sizes.n) AS mean_cluster_size
FROM   cluster_member cm
JOIN   source_record sr ON sr.source_record_id = cm.source_record_id
JOIN   (SELECT cluster_id, count(*) AS n FROM cluster_member GROUP BY 1) sizes
  ON   sizes.cluster_id = cm.cluster_id
GROUP  BY sr.origin;

-- Review queue depth and age, which is a staffing signal, not a quality signal.
SELECT count(*) AS pending,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY now() - decided_at) AS median_age
FROM   identity_link
WHERE  assertion IS NULL AND retired_at IS NULL;

-- Reviewer agreement with the model: how often a human overturns an auto-link.
SELECT date_trunc('week', decided_at) AS wk,
       count(*) FILTER (WHERE retired_reason = 'reviewer_overturned')::numeric
         / nullif(count(*), 0) AS overturn_rate
FROM   identity_link
WHERE  decided_by LIKE 'auto:%'
GROUP  BY 1 ORDER BY 1;

The overturn rate is the metric I watch most closely, because it is the only one that updates continuously without new labelling effort. A rising overturn rate means the model and reality have diverged, and it usually shows up weeks before anybody complains about duplicates.

Drift Signals Worth Waking Someone For

Entity resolution degrades quietly. The pipeline keeps running, the job keeps succeeding, and the output keeps getting worse. These are the monitors that have actually caught something.

Score distribution shift. Histogram the scores of all evaluated pairs weekly and compare against the trailing baseline. A shift in mass toward the review band means input quality changed — usually because someone modified an intake form. When we added a field to the self-serve reservation flow, phone completion dropped four points and the histogram showed it before anyone noticed.

Field null-rate by origin. VIN capture rate at the counter is a leading indicator of everything else. During the busiest changeover weeks it falls, and the review queue swells about ten days later. Knowing that lets you staff for it instead of being surprised.

Blocking-key explosion. The count of blocks exceeding MAX_BLOCK_SIZE, and the number of records inside them. A sudden jump almost always means a normalizer regression collapsed a field to a constant — the classic being a plate parser that starts returning empty strings and puts everyone in one bucket.

Cluster size outliers. Alert on any person cluster exceeding a threshold that is generous for a household and impossible for an individual. Fifteen is our line for retail clusters. Anything above it is either a fleet account misfiled as retail or a transitive-closure chain, and both need eyes.

Unmerge frequency by cohort. Group unmerges by the model version that created the link. If a version's unmerge rate is climbing relative to its predecessors, you have a regression with a clear owner.

Normalizer version skew. The proportion of source records whose normalizer_version lags current. If it stops falling after a deploy, your backfill is stuck, and half your comparisons are running against stale derived values.

What I Would Change On A Second Pass

Some of this design has held up for two years and some of it I regret.

I would introduce the vehicle-versus-person split on day one instead of month nine. We started with a single "customer" entity that carried a plate, and untangling that later cost more than building it properly would have. Vehicles and people have different identifiers, different stability, and different lifecycles; they were never the same problem.

I would put the review interface in front of staff earlier. The model improved far faster once the people who create the records could see the consequences of a hurried entry. Nothing sharpened VIN capture like a technician watching a merge decision hinge on a missing one.

I would resist per-value frequency weighting for longer than I did. It is theoretically correct and it made the system noticeably harder to explain to a reviewer, for a precision gain that was real but small. On a hundred thousand records, a well-chosen composite field bought more than sophisticated weighting did.

And I would treat the negative assertion as the primitive, not the afterthought. Half the operational value of this system comes from its ability to remember that two similar-looking records are definitively not the same, and we built that capability third. Positive matching is the interesting problem; recording what is known to be false is the one that stops the queue from filling with the same five pairs forever.

The counter still moves fast during changeover season, staff still type in a hurry, and people still arrive with a phone they share with a spouse and a plate they moved off a car they sold. None of that is fixable at the point of capture. What is fixable is the system's willingness to say "I am not sure," write that uncertainty down, and let a human settle it — for further reading on the identifiers themselves, the sidewall marking reference and the load index explainer cover the vehicle-side data we key off, and the service area map shows the geography that makes postal codes such weak evidence here.