# Crosswalk Table for AI Agents: Map IDs Safely

Build a crosswalk table that maps source IDs to canonical records with review status, evidence, and exact Rowset lookups for AI agents.

A crosswalk table maps a value or identifier from one system to its corresponding value in another
system. For AI-agent workflows, use it to preserve each source-local ID, point it at one canonical
record, and store enough review evidence that an agent can translate IDs without guessing or
silently merging uncertain matches.

For example, the same customer might be `cus_1842` in billing, `contact_731` in a CRM, and
`person_009` in a support tool. A crosswalk keeps all three aliases while giving the workflow one
approved canonical ID. The agent can look up an alias exactly, inspect the mapping status, and stop
for review when the evidence is incomplete.

This guide uses a five-part **TRACE mapping contract**:

1. **Target:** assign the canonical entity ID the workflow will use.
2. **Route:** name the source system and the mapping direction.
3. **Alias:** preserve the exact source-local identifier.
4. **Confidence:** separate proposed, approved, rejected, and retired mappings.
5. **Evidence:** record why the mapping exists, who reviewed it, and which version produced it.

## In this guide

- [What is a crosswalk table?](#what-is-crosswalk-table)
- [When does an AI agent need one?](#when-agent-needs-crosswalk)
- [The TRACE mapping contract](#trace-mapping-contract)
- [A crosswalk table schema](#crosswalk-table-schema)
- [Build the crosswalk in Rowset](#build-crosswalk-rowset)
- [How should an agent use it?](#agent-crosswalk-workflow)
- [Handle ambiguous and changing mappings](#ambiguous-changing-mappings)
- [Crosswalk table versus related patterns](#crosswalk-versus-other-patterns)
- [Crosswalk table FAQ](#crosswalk-table-faq)

<a id="what-is-crosswalk-table"></a>
## What is a crosswalk table?

A crosswalk table is a directional mapping between source values and target values. Each row says,
"in this context, this source value corresponds to this target value." The values may be field
names, categories, controlled-vocabulary codes, or identifiers for real workflow entities.

The term often refers to a **schema crosswalk**. A 2023 *Data Intelligence* paper defines a
crosswalk as a chart or table representing semantic or technical mappings from elements in a source
schema to elements with similar meaning or function in a target schema
([Wu et al., 2023](https://direct.mit.edu/dint/article/5/1/100/113281/An-Analysis-of-Crosswalks-from-Research-Data)).
The Dublin Core Metadata Initiative similarly describes a crosswalk as a semantic mapping across
metadata schemas ([DCMI glossary, checked August
2026](https://www.dublincore.org/groups/tools/glossary/)).

This guide focuses on an **identifier crosswalk**: mapping source-local IDs to a canonical entity
ID. The row shape is similar, but the operating risk is different. A wrong field mapping may break
an import. A wrong identity mapping can send an agent to the wrong customer, product, ticket, or
content record.

Mapping direction matters. HL7's FHIR `ConceptMap` standard treats mappings as source-to-target,
warns that the reverse cannot be assumed, and allows context-dependent or multiple targets
([FHIR R5 ConceptMap, checked August 2026](https://www.hl7.org/fhir/conceptmap.html)). The same
discipline applies to identifiers. `billing customer -> canonical person` does not automatically
prove that every canonical person has one billing customer, or that the inverse mapping is unique.

<a id="when-agent-needs-crosswalk"></a>
## When does an AI agent need a crosswalk table?

Use a crosswalk table when two or more systems assign different identifiers to the same logical
thing and the agent must move between those systems. Common cases include:

| Workflow | Source aliases | Canonical target |
|---|---|---|
| Customer operations | billing ID, CRM contact ID, support user ID | `person_id` |
| Product catalog | supplier SKU, marketplace listing ID, warehouse item ID | `product_id` |
| Content operations | CMS entry ID, repository slug, newsletter issue ID | `content_id` |
| Issue tracking | support ticket ID, GitHub issue number, internal incident ID | `case_id` |
| Research intake | source URL, DOI, repository accession | `source_id` |

Do not add a crosswalk when one stable identifier already travels through every system. If the
billing platform, CRM, and support tool all preserve the same immutable `person_id`, that field is
already the cross-system contract. The [business-key decision guide](/blog/rowset-id-vs-business-keys)
helps decide whether an existing field is safe enough to serve as identity.

Also do not confuse a crosswalk with record matching. Entity resolution decides whether two messy
records probably describe the same entity. A crosswalk stores the durable result after that
decision, along with its status and evidence. Rowset can hold proposed and approved mappings, but it
does not claim to be an entity-resolution engine.

The [data-matching workflow for AI agents](/blog/data-matching-ai-agents) covers the earlier stage:
how to generate candidate pairs, preserve comparison evidence, review uncertainty, and hand only
approved decisions to the crosswalk.

<a id="trace-mapping-contract"></a>
## Use the TRACE mapping contract

TRACE turns a two-column lookup into an operating contract a future agent can inspect.

### 1. Target: choose the canonical identity

The target is the ID your workflow uses after translation. It must identify one canonical entity,
not one source record. For a customer workflow, use `person_id`; for a catalog, use `product_id`.

The canonical ID may come from an upstream system of record or from a dedicated canonical-entities
dataset. Write down which system owns it. An agent should never mint a replacement merely because
one lookup failed.

### 2. Route: name the source and direction

Store `source_system` as part of every mapping. `1842` from billing and `1842` from a CRM are
different aliases unless the systems explicitly share an ID namespace.

Write the direction in the dataset instructions:

```text
Translate (source_system, source_id) to canonical_id.
Do not assume canonical_id can be translated back to one source row.
```

This is especially important when one canonical entity has several aliases inside one source, such
as a customer with a retired account and a current account.

### 3. Alias: preserve the source-local value exactly

Keep the original source ID unchanged. Do not trim leading zeroes, lowercase case-sensitive IDs, or
reuse a display name as identity. If normalization is required, store the normalized match input in
a separate field and preserve the original alias for audit and replay.

The pair `(source_system, source_id)` is the mapping row's business identity. PostgreSQL documents
that a unique constraint can cover a group of columns so the combination is unique even when each
column repeats ([PostgreSQL 18 constraints, checked August
2026](https://www.postgresql.org/docs/current/ddl-constraints.html)). At a single-index API
boundary, encode that pair deterministically using the same approach as the
[composite-primary-key guide](/blog/composite-primary-key-ai-agents).

### 4. Confidence: make uncertainty explicit

Use a workflow state, not a floating-point score alone:

- `proposed`: an agent or import produced a candidate mapping
- `approved`: a trusted rule or reviewer accepted it
- `rejected`: the candidate was checked and found wrong
- `retired`: the mapping used to be valid but should no longer drive new work

A numeric match score can remain as evidence, but it must not decide write authority by itself. The
agent's safe default is to act only on approved mappings. Proposed mappings go to a
[human review step](/blog/human-in-the-loop-ai-agents).

### 5. Evidence: keep the mapping explainable

Store the method, source timestamp, reviewer, and mapping version. Useful evidence fields include:

```text
match_method = exact_external_reference | imported_crosswalk | reviewed_candidate
evidence_ref = source file, task, ticket, or run ID
mapped_at = timestamp when the mapping was created
reviewed_by = person or trusted process that approved it
mapping_version = version of the rules or import
```

Evidence lets an agent answer "why do we believe these IDs match?" before it changes another
system. It also gives you a bounded set of rows to re-review when a matching rule changes.

<a id="crosswalk-table-schema"></a>
## What should a crosswalk table contain?

A useful identity crosswalk needs more than `old_id` and `new_id`. Start with this schema:

| Column | Purpose | Example |
|---|---|---|
| `mapping_id` | deterministic row identity | `billing|cus_1842` |
| `source_system` | namespace that issued the alias | `billing` |
| `source_id` | exact source-local identifier | `cus_1842` |
| `canonical_id` | approved target entity | `person_009` |
| `status` | review and lifecycle state | `approved` |
| `match_method` | how the candidate was produced | `exact_external_reference` |
| `evidence_ref` | traceable source or review item | `import_run_2026_08_07` |
| `mapped_at` | mapping creation time | `2026-08-07T05:30:00Z` |
| `reviewed_by` | approving actor, when required | `rasul` |
| `mapping_version` | rules/import version | `trace-v1` |

Enforce uniqueness over `(source_system, source_id)` in a relational source. A canonical ID may
repeat because several aliases can point to one entity. If the canonical entities live in another
table, a foreign key can require each target to exist; PostgreSQL defines that constraint as a
source value matching a row in a referenced table to maintain referential integrity
([PostgreSQL 18 constraints, checked August
2026](https://www.postgresql.org/docs/current/ddl-constraints.html)).

For a flat file or spreadsheet, add duplicate and missing-target checks before publishing the
crosswalk. A spreadsheet can be a reasonable review surface for a small mapping set, but it should
not become the only copy if agents need authenticated exact lookup, persistent instructions, or
relationship enforcement.

<a id="build-crosswalk-rowset"></a>
## How do you build a crosswalk table in Rowset?

Create one private dataset for mappings and, when needed, a second dataset for canonical entities.
Keep the crosswalk indexed by a deterministic `mapping_id` derived from the source pair.

```json
{
  "name": "Customer identity crosswalk",
  "description": "Maps source-local customer IDs to canonical people",
  "instructions": "Use mapping_id for exact lookup. Treat only approved mappings as actionable. Never replace source_id or canonical_id from fuzzy matching alone. Send proposed mappings to review.",
  "headers": [
    "mapping_id",
    "source_system",
    "source_id",
    "canonical_id",
    "status",
    "match_method",
    "evidence_ref",
    "mapped_at",
    "reviewed_by",
    "mapping_version"
  ],
  "index_column": "mapping_id",
  "column_types": {
    "mapping_id": {
      "type": "text",
      "description": "Deterministic source-system and source-ID lookup key"
    },
    "source_system": {
      "type": "choice",
      "choices": ["billing", "crm", "support"]
    },
    "status": {
      "type": "choice",
      "choices": ["proposed", "approved", "rejected", "retired"]
    },
    "mapped_at": "datetime"
  },
  "metadata": {
    "identity_contract": "TRACE",
    "mapping_direction": "source_alias_to_canonical_person",
    "actionable_statuses": ["approved"],
    "mapping_version": "trace-v1"
  }
}
```

The [schema design guide](/docs/design-schema) explains how column descriptions, dataset
instructions, choice values, and metadata carry this context into later agent sessions. If a
`People` dataset is indexed by `person_id`, link `canonical_id` to it with a Rowset relationship.
With enforcement enabled, Rowset rejects a non-blank canonical ID that does not match a target row;
the [relationship documentation](/docs/link-datasets) covers creation and resolution through MCP
and REST.

<a id="agent-crosswalk-workflow"></a>
## How should an AI agent use the crosswalk?

Use an inspect, translate, verify, act sequence:

1. **Inspect:** call `get_dataset` and confirm the index, instructions, schema, and relationships.
2. **Translate:** build `mapping_id` from the exact source system and source ID, then read by index.
3. **Verify:** require `status=approved`, check `canonical_id`, and inspect evidence when the action
   is sensitive.
4. **Act:** resolve the canonical entity and perform the requested operation against that explicit
   target.
5. **Read back:** verify the destination state after a write or uncertain response.

The crosswalk lookup is deterministic; matching is not. If no approved row exists, the agent should
search for evidence and create a `proposed` mapping or review task. It should not select the closest
name and continue as though identity were confirmed.

Stable `mapping_id` values also make retries safer. If a create response times out, the agent can
read the same mapping by index before deciding whether to create another row. The
[idempotent-update guide](/blog/idempotent-ai-agent-updates) provides the full recovery pattern for
agent-managed rows.

<a id="ambiguous-changing-mappings"></a>
## How do you handle ambiguous or changing mappings?

Keep ambiguity in rows rather than hiding it in agent reasoning. If one alias could map to several
entities, create candidate evidence outside the approved crosswalk or create proposed rows with
distinct candidate IDs and a review task. Do not let two approved rows share the same
`(source_system, source_id)` identity.

When a source system merges accounts, retire the old mapping and add the replacement with fresh
evidence. When a canonical entity is split, treat it as a migration: identify affected mappings,
propose new targets, review them, cut over dependent workflows, and preserve the old records for
audit. Silent in-place rewrites remove the explanation future agents need.

Version the rules that created mappings. A new normalization or matching model should write
`trace-v2` proposals without re-labeling `trace-v1` decisions. That makes review queues and rollback
bounded by version.

<a id="crosswalk-versus-other-patterns"></a>
## Is a crosswalk table the same as a join table?

No. A crosswalk translates values between namespaces. A join table models a many-to-many
relationship between entities. They can have similar two-key shapes, but their meaning and
lifecycle differ.

Use these distinctions:

| Pattern | Main job | Example |
|---|---|---|
| Schema crosswalk | map fields between schemas | `dc:title -> schema:name` |
| Value crosswalk | standardize categories or codes | `NYC -> New York City` |
| Identifier crosswalk | translate aliases to canonical entities | `crm:731 -> person:009` |
| Join table | record a relationship between two entities | `person:009 -> team:042` |
| Entity resolution | decide which records probably refer to one entity | compare name, address, and email evidence |

Civis Analytics documents a value-crosswalk workflow that maps unstandardized values to standard
categories and materializes a table that can be joined to the original data
([Civis Data Crosswalk, updated July
2025](https://support.civisanalytics.com/hc/en-us/articles/24787326654989-Data-Crosswalk)).
That is useful for categorization. An identity crosswalk needs stricter review and provenance
because its target determines which real record an agent will act on.

Use [Rowset pricing](/pricing) when you need private hosted crosswalk datasets with MCP and REST
access, or start with the [Rowset quickstart](/docs/quickstart) to create the mapping dataset and
test one exact lookup.

<a id="crosswalk-table-faq"></a>
## Crosswalk table FAQ

### What is a crosswalk table?

A crosswalk table is a directional mapping from values in one system to corresponding values in
another. It can map schema fields, categories, codes, or identifiers. For agent workflows, include
the source namespace, exact source ID, canonical target ID, review status, evidence, and mapping
version.

### How do you create a crosswalk table?

Choose the target namespace, list each source namespace and alias, assign a canonical target only
when evidence supports it, and add review and provenance fields. Enforce uniqueness for each source
pair, keep mappings directional, and test unmapped, ambiguous, retired, and duplicate cases before
an agent acts on the table.

### Can you make a crosswalk table in Excel?

Yes. Excel is adequate for a small, human-reviewed crosswalk if you validate duplicate source pairs
and missing targets. Use a database or private dataset API when agents need authenticated lookup,
durable instructions, concurrent updates, relationship enforcement, or a reliable audit trail.

### Can one source ID map to multiple targets?

It can be proposed, but an operational identity crosswalk should not silently approve several
targets for one source pair. Keep candidates in review, record the context that disambiguates them,
or refine the source identity. Multiple approved targets make exact agent actions unsafe.

### Should an AI agent approve its own identity mappings?

Only when a trusted deterministic rule provides authoritative evidence, such as an external ID
already stored by both systems. Fuzzy name, address, or email similarity should produce a proposal,
not an approved mapping. Keep a human or separately authorized process in the approval path for
ambiguous matches.