Documentation

Appearance

Updated Sep 12, 2026 by Barča Dvořáková · Owner: analysisactiveconceptgeneric Edit on GitHub

Lookup Tables ​

Concept layer — frozen. The Lookup Tables generic. Nothing here is written by a normal playbook run; a project's own feature analysis is the live document and takes every edit. This layer names no project and links to none — the dependency runs one way, from an application to its concept.

Business Context ​

Lookup tables enable a dynamic, maintainable way to manage configurable data across the whole system. These values are not hardcoded and are editable at runtime, so updates reach the application without a code release.

This functionality provides flexibility and agility across business features that rely on static or semi-static data (for example locales, education levels, payment types).

Business-Level Definition ​

The lookup tables serve as a centralized, extensible registry for reusable enumerated values that are:

  • Loosely integrated with the application's business logic, or only minimally involved through simple UI-related attributes — for example adding an isEUCountry boolean to a country lookup table.
  • Changeable without requiring deployment.
  • Shared across multiple domains and features.

Requirements Definition ​

Objective: allow runtime flexibility and easy administration of simple configurable values used by various services and features.

Value added:

  • Simplifies data-driven feature customization.
  • Eliminates code dependency for enumeration-like logic.
  • Enables internationalization and localization via runtime management.

Technical Context ​

User Stories / Use Cases ​

  • As a developer, I want to fetch all active lookup values to populate dropdowns.
  • As an admin, I want to create a new value without redeploying the application.

Functional Requirements ​

  • APIs for CRUD operations on the lookup table entity.
  • Support for query and filtering by key.
  • Values must be returned grouped by lookupKey.
  • Filtering must support active/inactive status.
  • Support for ordering when the values are displayed in a dropdown.
  • The service layer exposes API-like functions that can be used directly within the application code.
  • An update path validates a catalogue reference against active values only when the caller changes it; a value re-sent unchanged is accepted even if it has since been deactivated. State this per referencing field — a replace-set of child records re-submits every reference in it, and each one needs the same exemption or the whole record becomes uneditable the day one value retires.

Non-Functional Requirements ​

  • Fast response time (under 200 ms for reads).
  • Use indexing for the lookupKey and active attributes.
  • Integrity: each lookupKey should follow a dot-notation naming convention.
  • How soon a write is visible everywhere is bounded by the cache below, not immediate. State the bound rather than promising immediacy.

Domain Model ​

API Analysis (API-A) ​

See child page: Generic Lookup Tables API.

Logging & Monitoring ​

.info

  • for each create, update or delete operation.
  • log the request ID, actor ID and an action summary.

Caching & Performance ​

  • Values are cached at service level, and the cache is invalidated on write.
  • A project must say whether that cache is per process or shared, because the two give different guarantees. A shared cache can be invalidated on write and every reader sees the new value at once. A per-process cache receives no invalidation from a write that landed on another replica, so invalidation only corrects the instance that took the write and every other replica stays up to one lifetime behind — which makes a bounded lifetime the only thing that propagates a change at all.
  • Where whole catalogues are handed to a client for the duration of a session, that client's staleness window is the session and is the longer of the two.

The mechanism carries no obligation of its own; the contents of individual catalogues can, and the answers differ per list. These are the questions to settle before shipping.

  • Does a catalogue copy an externally published code list? Country, currency, language and classification codes usually come from a standards body or a public register. If so: which list, at which version, and on what terms may it — and its updates — be reproduced? Some are free and some are licensed. Two questions follow a yes: who keeps the copy in step with the source, and what happens to records already carrying a code the source has withdrawn. Changing the key system itself — from one standard's codes to another's — is an expand-contract migration that touches every soft-referencing column and every label key in one transaction, not an edit to the list.
  • Does a value leave the system inside a document a third party will read? A payment code on an invoice, a country on a filing. Where it does, the permitted values are fixed by that format rather than by what is convenient to add, and a locally invented value produces output that is syntactically valid and formally wrong.
  • Does the catalogue itself hold personal data? Usually not — a list of currencies identifies nobody — but "lookup table" is a shape, not a subject, and the shape invites reuse for whatever small editable list is needed next. Confirm it per list.
  • What does an erasure request actually touch here? Normally the records that reference a catalogue, not the catalogue. A usage table, or a trail over catalogue reads, changes that answer.
  • Is a value ever renamed rather than retired? A rename silently re-labels every historical record pointing at it. Where those records are evidence — invoices, consents, submissions — whether that is acceptable is a records and legal judgement. The technical consequence is unavoidable and is covered under Auditing, Reporting & Measurement.

Cybersecurity Considerations ​

Confidentiality is rarely the concern; integrity almost always is. Catalogues are commonly served to every user and often before authentication — a language picker precedes sign-in — so their contents are public by delivery whatever the read permission says. The consequence that bites later: because the shape is inviting, someone will eventually propose holding something non-public in it, and at that point the delivery mechanism has to change, not merely the read authorisation.

Whoever can write a catalogue can widen what the system accepts. Values are what validators compare submissions against, so adding a row is an authorisation change made through reference data. That is why write access is privileged even though the data looks trivial — and why a project with no write surface should recognise what it has bought:

  • Deployment-only catalogues (values arrive by reviewed migration) remove the entire class of runtime write risk: no endpoint to authorise, no injection surface, no attribution problem. The cost is under Risk Assessment: every change is a release.
  • A runtime administration surface buys the property the concept exists for, and arrives together with uniqueness enforcement, an attribution story and a decision about who may deactivate a value — none of which exist beforehand.

Either way: escaping belongs to the rendering surface, since a value reaches a screen and may reach a generated document unchanged and this concept guarantees only non-empty text; per-row free-form metadata is unvalidated by construction, so its shape is a trust assumption held by each client; removing a value fails quietly, with validators rejecting what they used to accept and nothing announcing it; and write paths that validate against a catalogue fail closed when it cannot be read, which makes a reference table a dependency of flows that look unrelated to it.

Risk Assessment ​

Business Risks ​

Settle the referential question first: what happens to records that already point at a value someone retires? Three regimes, and a project should say which it permits.

  • Deactivate. The value leaves every list that filters on active, so it is no longer offered for new entries while existing references still resolve. The only regime safe by default.
  • Delete. Where references are soft strings with no enforced relationship — usually the point, so that retiring cannot orphan business data — the store will not stop it, and afterwards nothing detects that a stored value has left its catalogue. The records keep their string and lose their meaning.
  • Rename in place. Nothing breaks, nothing errors, and every historical record silently acquires the new label. The dangerous one, precisely because it looks like the tidy option.

A related trap: if an update path re-validates against active values only, editing an old record for an unrelated reason is refused because a value it has always carried has since been retired. The rule under Functional Requirements — validate only what changes — is the answer; the trap is what a project gets by leaving one referencing field, or one replace-set, without it.

The rest are smaller and mostly visible. A missing catalogue or value blocks a task rather than producing a wrong result — the better failure direction, and still customer-facing immediately. A value seeded without display labels renders as a raw code. And turnaround is the standing cost of the deployment-only model: "add a currency" becomes scheduled development work, mitigated by seeding the whole published list rather than the values known to be needed, which trades a longer dropdown for far fewer such requests.

Technical Risks ​

  • Uniqueness within a list. Unconstrained, a duplicate doubles an option in every control and makes validator comparisons ambiguous. While reviewed migrations are the only writer the exposure is review quality; adding the constraint later needs a migration that first checks live data for values it would reject. Where seeding is the only writer, insist on fixed ids and ON CONFLICT (id) DO NOTHING in every catalogue changeset: without a (lookupKey, lookupValue) constraint that is the only thing making a seed that gets included twice idempotent.
  • Clients disagree about what an absent list means. One renders an empty control, another discards its whole start-up payload. Both are defensible — strict for a list the application cannot function without, lenient for an optional one — but a project that ends up with both by accident has made the blast radius of a seeding mistake depend on which application the user opened. Decide per list, not per client.
  • Ordering depends on data, not code. Rows carrying no explicit position fall back to sorting by the stored value: deterministic, and rarely what a dropdown wants. Nothing warns.
  • Soft references are unenforced by design — the correct trade for retirement — and nothing closes the residual: no constraint and normally no report detects a stored value that has left its catalogue. It stays invisible until a person reads an affected record.
  • The dependency runs one way and the symptom misleads. Catalogues depend on nothing but their store, while several features validate against them and fail closed, so a failure here presents as valid input being rejected across unrelated flows.
  • Write code with no caller is dormant, not ready. Create, update and delete paths that no surface reaches are exercised by no test and will be the foundation of a future API that assumes they work.
  • A list nobody reads any more is invisible. Unfiltered readers — a start-up payload that takes every active list — keep serving it, filtered readers never notice it, and nothing reports a lookupKey with no referencing column and no validator. Retire a list by deactivating its rows when its last reader goes, and treat "which lists does anything read" as a question the integrity check under Auditing should answer.

Auditing, Reporting & Measurement ​

Reference data changes rarely and, when it changes, it changes meaning. The event worth recording is therefore not a row was written but what a value now means to every record pointing at it.

  • A rename is invisible from the referencing side. Records are read through the current catalogue, so re-labelling a value re-labels history and nothing in the affected records changes to show it. A change log on the catalogue row does not repair this, because the reader never consults it. The only design that preserves what a record meant when it was created is to retire values and add new ones rather than editing them in place — a rule about how catalogues are maintained, not a feature that can be switched on later. Display labels are translation rows and the same rule governs them; see Translations.
  • Deactivation is the change most worth recording, because it changes what the system will accept from that moment on. Without a trail, when did this option stop being offered is unanswerable — exactly the question asked when a submission starts being rejected.
  • Attribution depends on whether a runtime writer exists at all. With no write surface there is no user action to attribute: the created-by and updated-by columns hold a deployment marker, and the real change record is the migration changeset — a genuine, reviewable trail that lives outside the product and appears in no history a user or auditor is shown. If an administration surface is added, catalogue writes belong in the Audit Log concept.
  • Nothing measures which values are actually chosen. Usage can only be inferred from the referencing business records, and that same count is the only honest basis for deciding whether a value is safe to retire. Without it, retirement is done on judgement.
  • Two cheap reports the concept implies and does not provide. An integrity check — per referencing column, the distinct stored values no longer in their catalogue — is the only thing that surfaces the unenforced-reference risk above. A completeness check — catalogue rows with no display label in a supported language — is the only thing that catches a raw code before a user does. One query each, and neither exists unless a project builds it.

Internationalization ​

Values never vary by locale; labels do. A catalogue row holds the code, and its display text in each supported language is a translation row keyed from the code — see Translations. Four consequences follow.

  • A project fixes exactly one label-key convention for lookup values and one translationType rule, and states them here. Two conventions in one product — one list keyed one way, another list another — means every client and every report has to know which list uses which, and the completeness check under Auditing is only writable once the convention is one rule.
  • A value without a label in a supported locale renders as its raw code. That is the failure the completeness check exists to catch before a user does.
  • Adding a language adds label rows, not catalogue rows. The catalogue is unchanged by a new locale; only the translation set grows.
  • The list of locales is the one catalogue whose contents gate a picker. Adding a row there offers a language the translation set may not yet cover, so the two are released together.

Known instances ​

None recorded in this copy. Add this project's instance here as it adopts the concept — one line naming the feature that implements it. This is the only place a concept may name a project, and the template ships it empty so a fork inherits no other project's entries.