Mehdi Akiki
Published on

A Canonical ID Is an Internal Promise, Not a Provider ID

Authors
  • Mehdi Akiki avatar
    Name
    Mehdi Akiki
    Twitter

Reference

An external provider gives me customer ID 1842. It is tempting to use 1842 as the primary key everywhere.

Later a second provider also has customer 1842, the first provider merges two accounts, or an identifier is reused after deletion. The “global” key was only local to an external namespace.

I create an internal canonical identity and map provider identities to it.

Identity includes a namespace

This value is incomplete:

external_id = 1842

A useful external identity is:

provider + provider_account/tenant + object_type + external_id

For example:

crm-a / account-eu / contact / 1842
crm-b / workspace-7 / contact / 1842

These can refer to different people. A unique constraint on external_id alone can merge them silently.

The canonical ID belongs to the product concept

I issue an opaque internal ID:

person_id = per_01K...

Provider mappings point to it:

create table identity_mapping (
  provider text not null,
  provider_scope text not null,
  object_type text not null,
  external_id text not null,
  canonical_id text not null,
  valid_from timestamptz not null,
  valid_to timestamptz,
  mapping_version bigint not null,
  evidence jsonb not null,
  primary key (provider, provider_scope, object_type, external_id, valid_from)
);

The active uniqueness rule prevents two current targets for the same scoped provider ID unless the domain explicitly permits it.

The internal ID stays stable when a provider renames, migrates, or changes identifier format.

Canonical does not mean objectively real

The product chooses which records represent one entity. Two contacts with similar names may be the same human, two household members, or one shared business inbox.

A canonical ID is a product promise:

Inside this bounded model, these observations currently refer to one entity.

The evidence and decision can be corrected. I do not make the ID look like universal truth by embedding one provider's terminology into it.

Merge without rewriting history blindly

Suppose per_A and per_B are later confirmed as one person. Replacing every historical ID in place destroys the earlier decision and can break references.

I record a merge:

per_B -> merged_into per_A at time T, decision D

Reads resolve aliases to the surviving canonical identity while audit and old events remain interpretable.

The merge workflow checks:

  • conflicting provider mappings;
  • field ownership and consent;
  • entitlements or balances that must not combine automatically;
  • downstream caches and indexes;
  • whether the operation can be reversed.

“Same person” does not mean every account-level resource may merge.

Split is harder and must remain possible

An incorrect match may have combined two people under one canonical ID. A split needs provenance for each field and relationship so records can be reassigned.

If the canonical table stores only one flattened current row with no source mapping, there may be no evidence for which facts belonged to which original entity.

I preserve:

source record identity
mapping decision and version
field-level provenance where needed
merge/split audit trail
downstream materialization generation

This makes identity repair a controlled data operation instead of a manual database edit.

Provider IDs can be reused

Some external systems reuse a short ID after deletion, restore an object with different meaning, or scope IDs differently across environments.

A mapping that lives forever can attach the new object to an old canonical entity.

I use provider evidence such as creation time, immutable provider generation, account scope, or another fingerprint and keep validity intervals:

external 1842 -> per_A, valid until provider deletion
external 1842 -> per_C, valid from later recreation

Webhook and event timestamps then resolve through the mapping valid for their source history, not only today's active row.

Do not derive the canonical ID from mutable attributes

Email address, phone number, display name, and domain can change or be shared. A hash of normalized email is deterministic but still makes email the identity authority.

I use these attributes as matching evidence, not as the durable canonical key.

matching evidence can change
canonical identity remains stable
mapping decision is versioned

For exact matching, normalized matching, and review, see Entity Resolution Before Machine Learning: Exact, Normalized, and Reviewed Matches.

IDs cross authorization boundaries

An opaque ID is not a permission token. A user who learns per_01K... still needs tenant and object authorization.

I also avoid exposing cross-tenant canonical identities when that would reveal that two organisations refer to the same person. The external API may use tenant-scoped aliases even when an internal governed system has a wider identity graph.

The mapping resolver always receives tenant and purpose context.

Reconciliation checks mappings too

Counts can match while identities are crossed. I reconcile:

provider identities with no active canonical mapping
canonical entities with conflicting active mappings
one external identity mapped to multiple current entities
mapping changes without decision evidence
events whose historical identity cannot resolve
merged identities still emitted as active
split decisions not propagated to projections

I sample high-impact matches for human review and maintain an undo path for automated decisions.

A migration sequence

When replacing direct provider IDs with canonical IDs, I use:

1. create canonical entities and mapping table
2. backfill mappings with confidence and evidence
3. dual-resolve reads and compare results
4. write canonical IDs beside existing provider references
5. migrate downstream constraints and joins
6. switch product reads
7. retain source identities for audit and synchronization

I do not delete the provider reference after cutover. It remains essential for fetching, webhook routing, reconciliation, and explaining provenance.

The cutover also keeps a rollback mapping so a failed downstream migration can resolve both key forms without generating a second canonical entity.

A provider ID answers “which object does this external namespace mean?” A canonical ID answers “which internal product entity do we promise to keep stable?” Keeping those questions separate is what lets integrations change without rewriting product identity every time.