Code catalogs

Most business databases carry a handful of small tables that barely change: payment methods, order statuses, transaction types, currencies. Rows elsewhere store the code; screens and documents have to show the name.

A code catalog is how TesseraQL answers that. The catalog is loaded whole and held, so resolving a name costs no query at all. A page showing twenty coded columns still makes one query — the one that fetched the page’s rows.

Declaring a catalog

Catalogs live in catalogs/*.yml under the app home. Files merge into one app-wide namespace, and a name declared twice is a build error.

version: tesseraql/v1
catalogs:
  payment_method:
    table: code_master
    where: { code_type: 'PAY' }   # this catalog's slice of a shared master
    key:   code
    label: label
    order: sort_order             # the order a form offers the codes in
    active: active                # which codes may still be chosen

where: is what makes one physical table serve many catalogs. A general code master usually holds every kind of code, keyed by a type column. Pin the type here and the catalog itself is single-keyed, which is what lets a field reference it.

Two keys are worth understanding early.

active: marks the codes a form still offers. It does not affect resolution: names resolve over every row, so a retired code still renders on last year’s orders and is simply not offered on today’s form.

order: decides the order of a form’s options. Sorting by label would be wrong — collation is locale-dependent, and code masters already carry a display-order column.

Showing the name

In a hand-written HTML template, the catalogs are in the model under codes:

<td th:text="${codes.payment_method.of(row.payment_method)}">Bank transfer</td>

of(...) returns the name, or the code itself when the catalog has no name for it. A missing name is a gap in the master data, not a reason to blank a cell in a document someone is reading.

codes is on every HTML page, whether the template was written by hand or ejected from a view. It is not in an export’s template or a mail’s: no export writes a catalog name — an export writes the code — and a name in a document comes through enrich: or a named query the export declares.

In a declarative view, point the column at a domain instead:

columns:
  - name: order_no
  - name: payment_method
    domain: payment_method

version: tesseraql/v1
domains:
  payment_method:
    type: string
    maxLength: 8
    codes: payment_method   # the catalog this field's values come from

One declaration, and the name renders everywhere that column does: a list, a detail view’s field, a detail view’s child table, a dashboard’s table panel, and the template the page is ejected into. Ejecting keeps calling the catalog, so a code renamed next month renames on the ejected page too.

Validation and form options

The same codes: reference gives the field its rules on the write side.

The input binder accepts only the catalog’s active codes. The violation is the enum field error, because a catalog is a dynamic enum — nothing downstream learns a second shape. A value the held copy does not carry is re-read from the source before it is refused, so a code added a minute ago is never rejected for the length of the hold.

On a form, a codes: field renders as a <select> whose options are the active codes in the catalog’s order. An ejected form keeps reading them rather than freezing today’s codes into markup.

Names in more than one language

Language is a dimension of the catalog, not part of its key. Add the column and the call site does not change:

  payment_method:
    table: code_master
    where: { code_type: 'PAY' }
    key: code
    label: label
    language: language

One code, one row per language it is written in:

code	language	label
`TRANSFER`	`en`	Bank transfer
`TRANSFER`	`ja`	振込
`CASH`	`en`	Cash
`CASH`	`ja`	現金

The catalog answers in the surface’s locale. On a route that is the request’s resolved locale, negotiated exactly as internationalization describes. A code with no name in that language falls back to the default language, not to the raw code.

The key set never narrows with the labels. Whether a code may be written is a question about the key set, so a missing translation can never turn into a failed transaction.

An export renders no catalog name in any language: it writes the code, and a name in a document comes through enrich: or a named query. An export’s locale: is the locale its numbers, dates and times are formatted in (file-transfers.md); the export-declaration rules judge it, and no catalog rule asks for it.

When a table and filters are not enough

Some masters keep the codes in one table and their per-language names in another. table:/where: cannot express that join, so declare the SQL instead:

  currency:
    file: currency.sql                    # resolved under catalogs/
    tables: [currency, currency_name]     # what the SQL reads
    key: currency_code
    label: name
    language: language
    active: active

key:/label:/language:/active: name that SELECT’s result columns, exactly as they name a table’s. tables: is what lets a maintenance screen invalidate the catalog without anything having to parse SQL. The SQL owns its own filtering and ordering, so where: and order: are refused beside file:.

Names from the message catalog

A code’s name is often already a translated string. Take it from the message catalog rather than adding a per-language table beside the codes:

  priority:
    table: priority_master
    key: priority
    label: { message: "code.priority.{key}" }
    order: sort_order

code:
  priority:
    H: High
    L: Low

code:
  priority:
    H: 高
    L: 低

The load then says only which codes exist. Each code becomes one entry per locale the app supports, so the language dimension comes from the translation workflow the Studio message editor already serves.

Keeping a catalog fresh

A catalog is held for cache.maxAge (an hour by default). Three things keep it current.

A maintenance write says which table it touched. The command drops every catalog that reads that table, so the next screen shows what was just saved:

steps:
  - id: upsert
    sql: { file: upsert-code.sql }
invalidates: [code_master]

It names the table, not the catalog. A maintenance screen for a shared master writes a row whose kind is request data, so which catalog is affected is not known until the row is written. Over-invalidating costs nothing here: a catalog is small enough to hold whole, so reloading all twenty is twenty small queries. scaffold crud writes the declaration itself for a table a catalog reads.

The same key sits on every writer with a commit: a webhook, a queue-consume route, a file-import route (applied when the import’s transaction commits, which is after its response), a file-export route (when its after: statement commits — with the extraction, or on the first fetch of a download-timed one) and a job, after its run (jobs.md). A recipe with no commit — a read, or an export with no after: — cannot declare it (TQL-FIELD-4620).

Other runtimes learn from a version row. The write also raises a per-table version, and every runtime re-reads that table on a short interval — one small query for all catalogs at once. A source that holds its rows (response-shaping.md, cache: {maxAge, tables}) reads the same row: one invalidates: drops the catalogs and the held results that read the table, on this node at once and on the others within the interval.

An operator can look and refresh. GET /_tesseraql/ops/catalogs reports what each catalog holds, when it last loaded, and the message of its last failed refresh. A catalog serving a previous load while its refresh keeps failing shows both facts. POST /_tesseraql/ops/catalogs/{name}/refresh re-reads one, requiring the caller’s tql.ops.run.<name> atom for the application.

None of this is the guarantee. invalidates: is an optimization — a master written by another system raises nothing. Underneath sit the hold’s expiry and the validation path’s re-read, so a stale catalog is a display delay and never a wrong rejection.

A catalog that cannot load fails the screens that read it, and only those. A catalog loads on the first read that asks for it. So a table missing on one environment — or a datasource down at first touch — answers TQL-APP-4206 (a 500: the fault is the server’s) on the pages that render its codes and on the commands that validate against it, while every other route serves. The failed load is held for a few seconds rather than retried on every request; the log names the catalog and its tables, and GET /_tesseraql/ops/catalogs shows the catalog as never loaded with the error beside it. Once the table exists, the next interval — or a maintenance write naming the table, or the operations refresh — loads it.

Catalog or enrichment?

The choice is size, not key arity.

A master of tens or hundreds of rows that barely changes is a catalog: loaded once, resolved from memory, no query per request. A master of thousands that moves is a reference, fetched by key with enrich:.

Both resolve composite keys, and both read the same at the call site.

Deliberately out of scope

Sorting, searching or paginating by a resolved name. Resolution happens after the query, and only SQL can order a result set. A screen that must sort by name joins the master instead. A sortable: coded column orders by the code.
Reading a catalog from SQL. Catalogs are a rendering and validation facility. A value your SQL must compute with — a tax rate — belongs in a table you join, keyed by (code, valid_from), and the result belongs on the document you wrote it onto.
Per-tenant catalogs. A catalog is held app-wide, so an app that declares catalogs alongside per-tenant datasources is refused rather than serving one tenant’s codes to another.

Lint and error surface

Code	What it catches
`TQL-FIELD-4616`	a catalogs document is malformed or declares an unknown key
`TQL-FIELD-4617`	a catalog name is declared twice
`TQL-FIELD-4618`	a catalog names something that is not a legal identifier
`TQL-FIELD-4619`	per-language names in an app that negotiates one locale
`TQL-FIELD-4620`	`invalidates:` on a recipe with no commit, or naming a table no catalog and no held source reads
`TQL-FIELD-4621`	a contradictory source, or a `file:` that is not there
`TQL-APP-4206`	a catalog could not be loaded and has never loaded; its readers answer 500 until it does
`TQL-APP-4207`	catalogs declared alongside per-tenant datasources

In the editor

catalogs/*.yml has its own JSON Schema, scaffolded into .vscode/ and associated with catalogs/**/*.yml, so completion and validation work offline like every other authored document. The VS Code extension lists the catalogs — and the SQL a file: catalog reads — in its explorer, completes codes: from the declared names, and navigates from one to its declaration.

declarative-validation.md — field domains, where codes: is declared.
response-shaping.md — enrich:, for the masters too large to hold.
internationalization.md — how a request’s locale is resolved.