EDI Code List and Cross-Reference Mapping

A design reference for a generic infrastructure that maps an external code scheme's codes onto internal records

1. Requirements

1.1 Functional requirements

Maintain a code list: the master record for one external code scheme — title, canonical identifier, version, publisher, description — entered by hand or imported from a standard genericode-format XML file.
Maintain individual codes within a code list — external code value, title, optional description, raw imported data — importable in bulk from the same file, with column selection and row filtering at import time.
Let any code link to any number of internal records of any record type, lookable either direction: record → its code(s) in a given list, or code → the record(s) it applies to.
Enforce that a given internal record is linked to at most one code within the same code list, so the mapping cannot be internally contradictory.
Support a per-list default code, used when a record has no explicit mapping.

1.2 Non-functional requirements

Import safety: the XML parser must not resolve external entities, load a DTD, or make network calls while parsing an uploaded file.
No silent duplication: re-running an import should not need special-casing — the code list is keyed by the scheme’s own canonical identifier.

1.3 Constraints

Only a local, already-uploaded file may be parsed directly; fetching from an arbitrary remote URL supplied by an end user is rejected — only trusted backend code may pull from a URL.
This infrastructure does not decide which records get mapped to which codes — populating the links is left entirely to whoever configures a mapping.

2. High-Level Design

2.2 The import walkthrough (a procedure, not a graph)

Upload or fetch. An admin uploads a genericode file, or trusted backend code supplies a trusted URL; a local-path check rejects any attempt to point the direct upload handler at a remote or file: URL.
Parse the header only. The scheme’s identification block (short name, version, canonical identifiers, publisher, description) becomes the Code List record, keyed by the scheme’s own canonical version identifier — re-importing the same identifier updates the existing record, and a mismatch between the selected list and the file’s own identifier is rejected.
Inspect columns before committing. The pipeline returns column names, up to three example values per column, and which columns are low-cardinality enough to filter by — so the admin picks the code/title/description columns, and optionally a row filter, before anything is written.
Bulk-create. One Common Code record is created per matching row, carrying the mapped columns plus the row’s raw XML as an auxiliary field.

A code list is the scheme; a common code is one entry in it — a strict parent/child relationship (deleting a code list cascades to delete every code within it). Nothing about either record is scheme-specific: the same shape holds for any external standard, statutory or otherwise.

The actual mapping lives one level below the common code, in a table of polymorphic links (record type + record name) attached to it. This is what makes the direction genuinely bidirectional, both implemented as the same join read in opposite orders:

Record → code (“what code applies to this record, in this list?”): join the common code table to the link table, filter by the record’s type/name and by the list, return the code value(s).
Code → record(s) (“what records carry this code?”): the same join, filtered by code value instead, returning every linked record.

A save-time check enforces that a given record cannot be linked to two different codes within the same list — so the first lookup is guaranteed at most one result, while the second can legitimately return several, since one code may apply to many records. A configured default code per list backs the first lookup when no explicit link exists.

The import pipeline performs a different, one-way, one-time translation: copying an external scheme’s rows into Common Code records verbatim. The bidirectional record/code translation above is a separate, ongoing, query-time act performed by the two lookup functions, and depends entirely on the link rows having been populated — which import never does. Import populates the code vocabulary; it does not map anything to it.

3.2 Who actually consumes this

Searching the wider codebase for callers of either lookup function, or any other reference to a code list or common code, turns up nothing outside this module’s own files and its own import UI. No sales, purchasing, inventory, or accounting record type links into this mapping, and no report or job reads one back out. A unit-of-measure master elsewhere carries a same-named field for a statutory unit code, but it is a plain, independent field with no code path into this module — a naming coincidence, not an integration.

A statutory code-and-unit-of-measure validation layer, covered separately, performs its own external-to-internal translation for a regulatory filing use case, in a different, separately installed application, with no reference anywhere to this module. The two solve the same kind of problem independently, sharing nothing.

The honest characterization is that this is general-purpose shared infrastructure with no in-tree consumer at all: an importer and a bidirectional lookup pair, fully implemented, tested, and reachable from the admin UI, but wired into no business process here. It reads as scaffolding for a mapping some not-yet-present integration is expected to populate and query.

3.3 Error handling

A malformed upload (invalid XML) is rejected with a specific parsing-error message, not a raw parser exception.
A remote-URL or file:-scheme upload attempt is rejected before any network call is attempted.
Selecting a code list that does not match the uploaded file’s own canonical identifier is rejected rather than silently overwriting the wrong list.
Linking one record to a second code within a list it is already mapped in is rejected at save time, naming the conflicting link.

4. Scale and Reliability

Import is a bounded, admin-triggered batch operation, not a live traffic path — cost scales with scheme size, not system load elsewhere.
The lookup functions are indexed joins over two small tables, cheap regardless of internal record count, provided the link table stays indexed on the columns the joins filter by.
Because nothing in this codebase currently calls the lookups, there is no real load profile to reason about yet; the open question is adoption, not performance.

5. Trade-off Analysis

Decision	Trade-off
Generic polymorphic link table, rather than a dedicated link field per consuming record type	Any record type can be mapped without a schema change, but nothing enforces that a mapping should exist for a given record, and there is no way to ask “what’s missing a mapping.”
Genericode import as the only bulk-population path; manual linking is the only way to populate the mapping itself	Vocabulary loads easily from a standard file; the mapping (which record gets which code) is entirely manual, so a large mapping effort has no bulk tooling.
At-most-one-code-per-list enforced at save time; many-records-per-code left unrestricted	Keeps “translate this record to its one code” simple, at the cost of an asymmetric reverse query.
Local-file-only direct upload, trusted-URL-only for remote fetch	Closes an obvious server-side-request-forgery path, at the cost of requiring a developer to wire up any future “pull from a public registry” feature.
Shipped as general infrastructure with zero in-tree consumers	Keeps the model reusable across future integrations, but today is pure carrying cost — code and tests maintained for a mapping nothing reads.

6. What to Revisit as the System Grows

Find or build the first real consumer — a single wired-up caller would validate the lookup shape far faster than continuing to build around it unused.
A coverage report (which records of a type have no code in a list) would make “remember to link everything” auditable, and is the natural companion to the bulk import that already exists for codes.
Bulk mapping import would close the gap between loading the code vocabulary (one upload) and populating the mapping (one link at a time).
If the statutory layer’s own code-translation mechanism is ever generalized, this scheme-agnostic, bidirectional module is the more natural home for it than a second bespoke mechanism.