Design: Item Identifiers

Overview

The Item module gains three additive capabilities. First, the Item payload acquires five optional standard identifiers — upc, ean, gtin, isbn, asin — modeled as a flat ItemIdentifiers value object (per DQ-002) and shape-validated without check digits. Second, two list fields ride the item row as jsonb arrays (per DQ-003): additionalQrs (text values to be rendered as QR codes) and labels (arbitrary free-form tags). Third, two search endpoints: /lookup, fuzzy matching over a database-generated search_text column with a single GIN trigram index (per DQ-007), following the /query route family’s shape and cursor pagination (per DQ-004); and /by-code, exact resolution of a scanned or typed code against the Additional QRs and all standard identifiers (per DQ-005), bridging GTIN zero-padding families by query-time candidate expansion (per DQ-006).

Everything is additive: no existing field, route, or behavior changes. The frontend phase-1 deliverable is confined to the BFF: proxy routes for both endpoints, TypeScript types and mappers, unified identifier validation utilities (extending the prototype’s UPC/EAN/ISBN set with GTIN-14 and ASIN), and the new fields joining the SSRM Search-box accessors (per DQ-008). UI enablement is a separate phase-2 project.

Decision Summary

#	Decision	Chosen Option
DQ-001	Standard identifier set	Union: UPC, EAN, GTIN, ISBN, ASIN
DQ-002	Identifier field shape	Flat ItemIdentifiers value object, shape-validated
DQ-003	QR/label storage	jsonb arrays on the item row
DQ-004	/lookup API shape	POST .../lookup?lookup=<s> + Query body; GET .../lookup/{page}?lookup=<s>
DQ-005	/by-code scope	QRs + all standard identifiers; renamed from /by-qr-code
DQ-006	GTIN normalization	Query-time candidate expansion
DQ-007	Lookup indexing	Generated search_text + single GIN trigram index
DQ-008	FE phase-1 scope	BFF routes, types, validators, SSRM accessors; UI in phase 2
DQ-009	search_text fields	Curated surface + left(notes, 256); card_notes_default excluded
DQ-010	ISBN handling	Both shapes accepted; 10↔13 conversion in CodeCandidates
DQ-011	Lookup ranking	Word distance first, caller sort tiebreak, eid stable
DQ-012	/by-code shape	GET + code query parameter; plain array capped at 50
DQ-013	Machinery location	Operations-local under cards.arda.operations.common.lib
DQ-014	Validation/uniqueness	400 on invalid shape; no uniqueness constraint
DQ-015	List constraints	Labels ≤128, QRs ≤64, ≤256 chars/entry, dedupe, order preserved
DQ-016	Performance	p95 ≤100 ms lookup / ≤20 ms by-code @50k; EXPLAIN CI test + seeding script

Full rationale in the Decision Log.

---

Structural Design

Class Diagram

The diagram covers all four layers of the module. ItemEndpoint handles request validation and response formatting only; ItemService coordinates the business logic — code-candidate expansion and the result cap for /by-code — and establishes the transaction boundary around each read; ItemUniverse assembles the tenant-scoped bitemporal queries; persistence flattens the typed identifiers into prefixed columns, stores the lists as jsonb, and derives a generated search_text column. Each identifier kind is a distinct value class (never a naked String), shape-validated in its smart constructor.

Per DQ-002, the identifiers flatten into prefixed columns through the module’s Component.Sub pattern (as Quantity and ItemSupplyReference do today); per DQ-003, the two lists use the Exposed jsonb() column type already proven in the demand module (DEMAND_ITEM_TABLE).

Key Classes and Interfaces

ItemIdentifiers and the identifier value classes

• Package: cards.arda.operations.reference.item.domain
• Responsibility: Typed identifiers — one @JvmInline value class per identifier kind (Upc, Ean, Gtin, Isbn, Asin; never naked String) — and the ItemIdentifiers.Value value object holding the five optional fields.
• Key fields: Value(upc: Upc?, ean: Ean?, gtin: Gtin?, isbn: Isbn?, asin: Asin?).
• Key methods: each value class follows the module’s smart-constructor idiom — private constructor plus companion operator fun invoke(value: String): Result<T> — rejecting values that fail its shape regex (UPC-A ^\d{12}$, EAN ^\d{8}$|^\d{13}$, GTIN ^\d{14}$, ISBN ^\d{9}[\dX]$|^\d{13}$ — both ISBN-10 and ISBN-13 accepted per DQ-010 — ASIN ^B[A-Z0-9]{9}$ with a must-contain-digit guard mirroring the prototype). Validating KSerializers (the LocalPart/EmailAddress precedent) route JSON deserialization through the smart constructors, so a malformed identifier on the wire is a 400. No check-digit validation on write (DQ-002, DQ-014: no per-tenant uniqueness constraint). The companion factory ItemIdentifiers(upc: String?, …): Result<Value?> is the raw-string boundary: it trims, normalizes blank to null, collects all shape errors (AppError.Composite), and returns null when every field is absent.
• Design decision: DQ-001, DQ-002 (as amended — typed identifiers), DQ-010, DQ-014.

CodeCandidates

• Package: cards.arda.operations.reference.item.domain
• Responsibility: Expand a scanned/typed code into its GTIN zero-padding equivalence set for exact matching.
• Key methods: expand(code: String): Set<String> — for an all-digit code of length 12/13/14 produces the verbatim form plus its zero-stripped and zero-padded siblings (e.g. 13-digit input with leading 0 → {13-digit verbatim, 12-digit stripped, 14-digit padded}); 8-digit codes add only the 14-digit padded form; non-digit codes (ASIN, QR text) return the verbatim singleton. Per DQ-010, ISBN forms convert both ways — an ISBN-10 input adds its 978-prefixed ISBN-13 (mod-10 check digit recomputed), and a 978… ISBN-13 adds its ISBN-10 (mod-11); 979… ISBN-13s have no ISBN-10 equivalent and expand one-directionally. The check-digit computation exists only inside this conversion; write-time validation remains shape-only.
• Design decision: DQ-006, DQ-010.

Item / Item.Entity (modified)

• Package: cards.arda.operations.reference.item.business
• Responsibility: Adds identifiers: ItemIdentifiers.Value?, additionalQrs: List<String> (default empty), labels: List<String> (default empty) to the sealed interface, Entity, ItemSerializer, and ItemInput (api/Model.kt). validate() gains the list-constraint checks per DQ-015: labels ≤ 128 entries, additional QRs ≤ 64 entries, ≤ 256 characters per entry, exact duplicates rejected; order is preserved as written (limits configurable in module config).
• Design decision: DQ-001, DQ-003, DQ-015.

ITEM_TABLE / ItemRecord (modified)

• Package: cards.arda.operations.reference.item.persistence
• Responsibility: Five nullable varchar identifier columns via a new itemIdentifiersComponent column cluster (Component.Sub prefixing yields identifiers_upc, identifiers_ean, identifiers_gtin, identifiers_isbn, identifiers_asin); additional_qrs and labels as jsonb(..., ListSerializer(String.serializer())), NOT NULL DEFAULT '[]'; search_text is a database-generated stored column that Exposed neither declares for writes nor maps into the payload — it is referenced only inside the lookup predicate and ordering expressions.
• Design decision: DQ-003, DQ-007.

ItemService (modified)

• Package: cards.arda.operations.reference.item.service
• Responsibility: The service layer coordinates the business logic of both search flows and establishes the transaction boundaries — each method wraps its universe read in inTransaction(db), mirroring the existing ItemService operations:
  • lookupItems(lookup: String, query: Query, asOf: TimeCoordinates): Result<PageResult> — delegates to ItemUniverse.lookupPage inside the transaction.
  • findByCode(code: String, asOf: TimeCoordinates): Result<List<EntityRecord>> — expands the code once via CodeCandidates.expand, calls ItemUniverse.byCode(candidates) inside the transaction, and applies the DQ-012 server-side result cap (50, configurable).
• Design decision: DQ-006, DQ-011, DQ-012; layering per the module’s four-layer Data Authority pattern (endpoint → service → universe → table).

ItemEndpoint (modified)

• Package: cards.arda.operations.reference.item.api.rest
• Responsibility: Three new resource-scoped routes. The endpoint implements no business logic — it validates the request (lookup/code non-blank → 400, malformed Query body → 400), resolves the time coordinates, delegates to ItemService, and formats the response (200 PageResult / 200 array, standard error mapping).
• Design decision: DQ-004, DQ-005, DQ-012.

ItemUniverse (modified)

• Package: cards.arda.operations.reference.item.persistence
• Responsibility: Two new query-assembly methods, both under ItemUniversalCondition tenant scoping and bitemporal current-version predicates:
  • lookupPage(lookup: String, query: Query, asOf): Result<PageResult> — combines the fuzzy predicate search_text % lookup OR search_text ILIKE '%'||lookup||'%' (both index-supported by the GIN trigram index) with the caller’s Query.filter, orders per DQ-011 by trigram word distance (search_text <->> lookup) first, then the caller’s Query.sort, then eid as a stable tiebreak, and paginates via the standard cursor machinery.
  • byCode(code: String, candidates: Set<String>, asOf): Result<List<Item.Entity>> — receives the verbatim code plus the pre-expanded candidate set from ItemService and assembles the OR of indexed equalities identifiers_upc/ean/gtin/isbn/asin IN candidates and jsonb containment additional_qrs @> to_jsonb(code) — QR matching is exact on the verbatim code; normalization applies to the standard identifiers only (DQ-005). Per DQ-013 the containment operator (and any other reusable lookup helpers) live in the operations-wide cards.arda.operations.common.lib package — same recipe as common-module’s TrigramMatch operators, kept cleanly separated from reference/item to ease later promotion to common-module.
• Design decision: DQ-006, DQ-007, DQ-011, DQ-013.

Persistence and Migrations

Two migrations continue the module’s sequence (current head V023):

• V024__item_identifiers.sql — adds the five identifier columns, the two jsonb columns (NOT NULL DEFAULT '[]'::jsonb), and the generated column. The generation expression must be immutable, so it uses coalesce(col, '') || ' ' || ... concatenation (not concat_ws, which PostgreSQL marks stable) and the immutable jsonb → text cast for labels:

  Per DQ-009 the generated column concatenates: name, description, internal_sku, gl_code, use_case, classification type/subtype, the four locator fields, supplier/supply names and SKUs (both supply slots), manufacturer name, the five identifiers, the complete labels array, and the leading 256 characters of notes (card_notes_default is excluded):

  search_text text GENERATED ALWAYS AS (
    coalesce(item_name, '') || ' ' || coalesce(description, '') || ' ' ||
    coalesce(internal_sku, '') || ' ' || coalesce(gl_code, '') || ' ' ||
    /* ... use_case, classification_*, physical_locator_*,
       primary/secondary_supply name + sku + supplier_ref_name, manufacturer_ref_name ... */
    coalesce(identifiers_upc, '') || ' ' || coalesce(identifiers_ean, '') || ' ' ||
    coalesce(identifiers_gtin, '') || ' ' || coalesce(identifiers_isbn, '') || ' ' ||
    coalesce(identifiers_asin, '') || ' ' ||
    coalesce(labels::text, '') || ' ' ||
    left(coalesce(notes, ''), 256)
  ) STORED

  Labels participate with their complete contents — the only truncation is on notes; the DQ-015 per-entry and per-list bounds are what keep the labels contribution predictable (worst case ≈ 32 KiB, TOASTed and GIN-indexed without issue).

• V025__item_identifiers_indexes.sql (+ .sql.conf with executeInTransaction=false, following the V015 precedent) — CREATE INDEX CONCURRENTLY:

  • GIN gin_trgm_ops on search_text (the only fuzzy index — DQ-007);
  • partial b-trees (tenant_id, <identifier>) WHERE <identifier> IS NOT NULL for each of the five identifier columns;
  • GIN jsonb_path_ops on additional_qrs.

Because the table is bitemporal (every update inserts a full new version row), the single-search-index design bounds GIN write amplification to one entry per version. The generated column is computed by the database per row, so historical versions carry the search_text matching their own field values — time-travel lookups (effective-as-of) remain correct.

---

Behavioral Design

Sequence Diagrams

The /lookup flow below shows a client posting a lookup string with a standard Query body. The endpoint only validates the request and formats the response; ItemService coordinates the read and establishes the transaction boundary; the universe composes the fuzzy predicate with the caller’s filter inside the tenant-scoped bitemporal query, orders by trigram word distance, and returns the standard cursor-paged PageResult.

Subsequent pages arrive via GET /lookup/{page}?lookup=s, mirroring GET /query/{pageId}: the cursor decodes to the original Query advanced one page; the lookup string is re-supplied as a query parameter (DQ-004).

In the /by-code flow the endpoint validates the request and hands off to ItemService, which owns the business logic: it expands the code once into its equivalence set (GTIN zero-padding families plus ISBN 10↔13 conversion per DQ-010), opens the transaction, and resolves the set with one indexed query OR-ing identifier equalities and jsonb containment on the QR list, applying the DQ-012 result cap.

Error paths (both flows): blank/missing lookup or code → 400; malformed Query body → 400; authentication/tenant failures → 401/403 by the existing endpoint machinery; database errors surface through the module’s standard Result/AppError channel as 5xx.

API Contract

POST /v1/item/item/lookup (resource-scoped)

• Method: POST
• Path: /v1/item/item/lookup?lookup=<string>[&effective-as-of=…][&recorded-as-of=…]
• Authentication: Bearer token + X-Tenant-Id (same as /query)
• Request schema: serialized Query { filter?, sort?, paginate? } — identical to the /query body. filter composes (AND) with the fuzzy predicate; ordering is relevance first, then sort, then eid (DQ-011).
• Response schema: PageResult { thisPage, nextPage, previousPage?, results: [EntityRecord], totalCount? }
• Error responses: 400 — blank/missing lookup or malformed body; 401/403 — auth/tenant; 500 — internal.

GET /v1/item/item/lookup/{page} (resource-scoped)

• Mirrors GET /v1/item/item/query/{pageId}; requires lookup=<string>; same response and errors (404 for an undecodable cursor).

GET /v1/item/item/by-code (resource-scoped)

• Method: GET (DQ-012)
• Path: /v1/item/item/by-code?code=<string>[&effective-as-of=…][&recorded-as-of=…] — the code text travels as the code query parameter.
• Authentication: Bearer token + X-Tenant-Id
• Response schema: JSON array of full item EntityRecords; expected cardinality 0–few (duplicates legal per DQ-014); server-side cap of 50 results (configurable).
• Error responses: 400 — blank/missing code; 401/403 — auth/tenant; 500 — internal.

BFF routes (arda-frontend-app, phase 1)

Route	Forwards to	Notes
POST /api/arda/items/lookup?lookup=<s>	POST ${BASE_URL}/v1/item/item/lookup	processJWTForArda + buildArdaHeaders, standard {ok, status, data} envelope
GET /api/arda/items/by-code?code=<c>	GET ${BASE_URL}/v1/item/item/by-code	same wrapper

Frontend types: identifiers, additionalQrs, labels added to the raw backend mirror (src/types/arda-api.ts), the frontend Item (src/types/items.ts), and the mappers (src/lib/mappers/ardaMappers.ts). A unified identifier validation module (src/lib/shared/identifiers.ts) supersedes the prototype’s divergent validators, covering UPC-A, EAN-8/13, GTIN-14, ISBN-10/13 (DQ-010), and ASIN. The SSRM accessor map (src/app/items/filteringProperties.ts) gains accessors for the five identifiers and the two lists so the Items-page Search box matches them (DQ-008).

---

Implementation Scope

Files to Create

File	Package/Path	Purpose
ItemIdentifiers.kt	operations:reference/item/domain	Identifier value object + shape validation
CodeCandidates.kt	operations:reference/item/domain	GTIN zero-padding expansion
ItemIdentifiersComponent.kt	operations:reference/item/domain/persistence	Column cluster for the five identifiers
JsonbContains.kt	operations:cards.arda.operations.common.lib	Exposed @> containment operator (DQ-013: operations-wide commons, not module-local)
seed-item-lookup-benchmark script	operations (test tooling)	Repeatable seeding for the DQ-016 manual benchmark
V024__item_identifiers.sql	operations:src/main/resources/reference/item/database/migrations	Columns + generated search_text
V025__item_identifiers_indexes.sql (+ .sql.conf)	same	Concurrent index creation
identifiers.ts	arda-frontend-app:src/lib/shared	Unified identifier validators (UPC/EAN/GTIN/ISBN/ASIN)
lookup/route.ts	arda-frontend-app:src/app/api/arda/items/lookup	BFF proxy for /lookup
by-code/route.ts	arda-frontend-app:src/app/api/arda/items/by-code	BFF proxy for /by-code

Files to Modify

File	Change Description
operations:business/Item.kt	Add the three fields to Item, Entity, ItemSerializer; extend validate()
operations:api/Model.kt	Add the fields to ItemInput
operations:persistence/ItemPersistence.kt	Columns, fillPayload, record mapping
operations:persistence/ItemUniverse.kt	lookupPage, byCode (query assembly)
operations:service/ItemService.kt	lookupItems, findByCode — business logic, candidate expansion, result cap, transaction boundaries
operations:api/rest/ItemEndpoint.kt	Three new resource-scoped routes — request validation + response formatting, delegating to ItemService
operations:CHANGELOG.md	Added entry (minor bump)
arda-frontend-app:src/types/arda-api.ts, src/types/items.ts, src/lib/mappers/ardaMappers.ts	New fields in raw type, frontend type, mappers
arda-frontend-app:src/app/items/filteringProperties.ts	SSRM accessors for identifiers, QRs, labels
documentation:current-system/functional/reference-data/item/q2-2026/index.md	New fields + endpoints in the module reference
documentation:domain/information-model/assets/items.md	Item entity gains identifiers, additionalQrs, labels

Out of Scope

• UI enablement — item form fields, grid columns, scan-flow integration (phase-2 project).
• Check-digit validation on write — shape-only per DQ-002/DQ-014 (the ISBN 10↔13 conversion inside CodeCandidates computes check digits internally, per DQ-010, but writes are never rejected on check-digit grounds).
• Uniqueness enforcement of identifiers per tenant (DQ-014: explicitly not enforced).
• common-module generalization of the lookup machinery (DQ-013: operations-local under cards.arda.operations.common.lib; promotion deferred until a second consumer exists).
• Widening the Amazon import DTO to carry EAN/ISBN (currently dropped after filtering) — phase-2 candidate alongside UI auto-fill.
• Backfill of the prototype’s notes-embedded “Barcode:” workaround (local-demo only; no production data).
• Changes to the existing lookup-* typeahead endpoints — they remain as-is.

---

Testing Strategy

Unit Tests

Test	Target	Validates
Shape validation accepts/rejects per field	ItemIdentifiers	Valid shapes pass; wrong lengths/characters fail with the module’s error channel; blanks normalize to null
Serializer round-trip with new fields	ItemSerializer / ItemInput	JSON round-trip including empty and populated lists
Candidate expansion families	CodeCandidates	8/12/13/14-digit expansion sets; ASIN and QR text verbatim; ISBN 10↔13 conversion incl. 979… one-directionality (DQ-010)
Service coordination	ItemService.lookupItems / findByCode	Candidate expansion happens in the service; result cap applied; universe called once inside the transaction (MockK universe)
Validator parity	identifiers.ts (FE)	Same accept/reject sets as the backend for all five identifier kinds (ISBN-10 and ISBN-13 both accepted)
List bounds from config	Item.validate()	Labels ≤128, QRs ≤64, ≤256 chars/entry, duplicate rejection — asserted from module config, not hardcoded (DQ-015)

Integration Tests

Test	Setup	Validates
Migration applies cleanly	ContainerizedPostgres, module Flyway	V024+V025 apply; generated column populated; indexes exist
Persistence round-trip	seeded universe	identifiers + lists survive write/read; defaults on legacy rows
Fuzzy lookup ranks and filters	seeded items across tenants	Match on name/identifier/label; ranking by word distance; Query.filter composes; tenant isolation; effective-as-of correctness
By-code resolves normalized forms	seeded UPC/EAN/GTIN/ISBN/ASIN/QR items	EAN-13 scan finds UPC-A item and vice versa; ISBN-13 scan finds ISBN-10 item; QR containment exact; tenant isolation
Index usage	EXPLAIN on both queries	No sequential scan on the item table (per DQ-016)

Manual benchmark (non-CI, per DQ-016): a checked-in seeding script populates 50k items/tenant so the p95 targets (≤ 100 ms /lookup, ≤ 20 ms /by-code) can be measured repeatably per release.

API Tests

Test	Method	Path	Expected
Lookup happy path + paging	POST/GET	/v1/item/item/lookup, /lookup/{page}	200 PageResult, stable cursors
Lookup blank string	POST	/v1/item/item/lookup?lookup=	400
By-code exact + normalized	GET	/v1/item/item/by-code?code=…	200 array; normalized-family hit
By-code miss	GET	/v1/item/item/by-code?code=nope	200 empty array

Frontend: Jest route tests for both BFF proxies (MSW upstream), filterEngine tests proving the Search box matches the new accessors.

---

References

• Decision Log
• Project Plan
• Goal
• Existing patterns: operations AbstractUniverse.lookup() (trigram fuzzy), DEMAND_ITEM_TABLE (Exposed jsonb), V015__item_bitemporal_indexes.sql (concurrent index migration), common-module TrigramMatch.kt (custom Exposed operators)
• Prototype: arda-frontend-app branch callil/pdev-1161-scan-to-item-inspect — identifier-mode.ts, asin.ts, enrichment engine identifier namespaces

---

---

Copyright: (c) Arda Systems 2025-2026, All rights reserved