# codesystem

*Working draft.* A PostgreSQL extension for **code systems**: SIC, SOC, ISIC, NACE, ISCO, NUTS, GSS, ICD, HS ... -- sets of codes
with a hierarchy and, often, a lineage. Exact validation, compact storage, ordering and prefix ranges that follow the hierarchy,
and first-class support for **derived** systems (a national variant of an international standard).

It is separate from `postal_code` (which is for postal codes) and shares none of its vocabulary.

## Vocabulary

| Term | Meaning |
|---|---|
| **code system** | a named, versioned set of codes: `isic_rev4`, `nace_rev2`, `uk_sic_2007`, `isco_08`, `uk_soc_2020` |
| **code** | one member of a code system, a string whose width belongs to its level |
| **level** | a rank in the hierarchy, with a name (`section`, `division`, `group`, `class`, `subclass`) |
| **derived from** | a system built on another: **refines** (its codes are the parent's plus more characters) or **corresponds** (a mapping) |
| **crosswalk** | the table of correspondences between two code systems, with its cardinality |

## Decisions so far (John, 2026-10-10)

* Its own extension and functions; `postal_code` terminology is not used for anything else. Name: **codesystem**.
* **One generic type**, `code('uk_sic_2007')`, not one type per system: a roll-up or translation must return a typed value, and
  that is not possible when every system is its own type. A column locked with `code('uk_sic_2007')` refuses any other system.
* Derived standards are modelled as **refines** (validated at definition: every derived code's prefix must be a parent code; fail loud)
  or **corresponds** (a crosswalk). Do not assume refinement: ISIC Rev.4 to NACE Rev.2 is probably *not* a strict prefix refinement
  (ISIC 4510 vs NACE 4511 / 4519); let the data decide.
* A code is a string with a fixed width per level. Real data loses leading zeros (see `data/SOURCES.md`); the loader must say so.

## Reusing from `postal_code`

The ranked-pattern engine (`postal_code_pattern.c/h` in https://github.com/John-Cameron-Burns/pg-postcode): rank = position among the
codes in byte order, so order, validity and prefix ranges are exact; the sorted-list builder (the smallest automaton for a list,
`pc_pattern_from_list`, branch `feat/list-sql` there); named parts. This project vendors a renamed copy; the symbols are hidden, so both
extensions can be loaded together.

## Status and layout

Step 1 of the build is done: the engine is vendored and the first two source files are loaded.

    engine/cs_pattern.c, .h     the ranked-pattern engine (vendored, renamed cs_*; Postgres-free)
    test/test_cs_*.c            its tests (28 million checks), copied with it and renamed
    tools/csmodel.py            what a prepared code system is, and the checks every loader must pass
    tools/prepare_isic.py       ISIC Rev.4  (UN)  -> data/prepared/isic_rev4.csv
    tools/prepare_isco.py       ISCO-08     (ILO) -> data/prepared/isco_08.csv
    test/test_prepare.py        the loaders: synthetic defects on purpose, and the real files if fetched
    data/SOURCES.md             where the source files come from, and what was found in them

    make test        engine tests + loader tests          make prepare    build data/prepared/ from data/raw/
    make sanitize    engine under ASan/UBSan (on Linux)

The prepared form is `code, level, parent, label`, validated against the publisher's own counts (ISIC Rev.4: 21 / 88 / 238 / 419;
ISCO-08: 10 / 43 / 130 / 436). Nothing is repaired silently: the one repair so far (ISCO's lost leading zeros in the Armed Forces
group) is reported on every run.

**Not yet verified:** the vendored engine under the sanitizers (the code is unchanged from `postal_code`, where it is clean, but
`make sanitize` has not been run here: AddressSanitizer hangs at start-up on the development Mac). Set up CI before relying on it.

## Next

2. The extension: a registry of code systems, the generic `code('system')` type (system id + rank in the value), levels, loading
   from the prepared form, `part()` / `rollup()`; ISIC and ISCO-08 as the first systems.
3. Derivation: `refines` (checked at definition), `rollup()`, and the `corresponds` crosswalk (real data: SIC 2003 to 2007).

## Status: steps 2 and 3 (0.1.0, not yet released)

Working and tested on PostgreSQL 17 (regression tests `define types hierarchy revisions`; engine suites also clean under
AddressSanitizer/UBSan on Linux): the registry, `define_code_system()`, the `code` type with its column lock, ordering and
indexes, binary I/O, `code_level/parent/ancestor/children/label`, revisions, and ISIC Rev.4 and ISCO-08 loaded from
`tools/load_systems.sql` with the publishers' counts checked.

Things to know:

* **Write the system in SQL text.** PostgreSQL gives a literal's input function no type modifier, so
  `INSERT ... VALUES ('isic_rev4:4711')` is needed; a bare `'4711'` is read from *text* (COPY, a text column,
  `'4711'::text`, `code_from('4711','isic_rev4')`) into a column declared `code('isic_rev4')`.
* **Restoring a dump** needs the `codesystem_*` tables first: use `tools/restore.sh dump newdb` (custom-format dumps). Plain
  `pg_restore` fails loudly if the order is wrong; it never loads wrong data.
* **Values of different revisions are different values**, even for the same code text (the rank can differ). A
  `code_current()` normaliser is the next thing to add.
* **Derived standards (step 3).** `define_code_system(..., refines => 'nace_rev2')` makes a national variant: its top
  levels must be *exactly* the base's (same levels, same codes, same parents), checked at definition with every
  difference reported; it may add deeper levels. `code_rollup(code, 'nace_rev2')` follows the chain upwards.
* **Corresponding standards.** `define_crosswalk('sic2003','sic2007', pairs_query)` ties systems that are not derived from
  one another; every code must exist and every finest-level code should have a counterpart (or `allow_unmapped`).
  `code_map(code, target)`, `code_map_unique()` and the view `code_crosswalks` (one-to-one / many-to-one / ...).
* **Revisions.** `code_current(c)` and `code_is_current(c)` bring values written under different revisions together.

## Pattern systems (0.2.0)

An identifier with a syntax and no list -- the Companies House company number -- is a *pattern system*:

    SELECT define_code_system_pattern('uk_crn', '/\d{8}|(?<prefix>SC|NI)(?<number>\d{6})/', 'number');
    SELECT code_part('uk_crn:SC123456', 'prefix');                       -- SC
    SELECT code_from_padded('1234567', 'uk_crn');                        -- uk_crn:01234567
    WHERE crn BETWEEN code_first_with_prefix('SC','uk_crn') AND code_last_with_prefix('SC','uk_crn')   -- every SC number

A code is its rank among everything the pattern allows, so numbers issued later change nothing and no revisions are needed. A
pattern says what can be a number, not which have been issued. `tools/load_crn.sql` is the real definition (see `docs/CRN.md`).
Upgrade from 0.1.0 with `ALTER EXTENSION codesystem UPDATE`.