Item: Data Model

The Item module stores a material type’s full descriptor — classification, physical attributes, print sizing, primary/secondary supply, manufacturer, identifiers — in item, with each of an item’s possible supply sources broken out into child rows in item_supply. Four supporting tables (batch_job, item_draft, tenant_settings, user_settings) round out the module but aren’t part of the Item entity itself. This page is the field-complete reference for all six; see Data Models — Reading Guide for the shared envelope shapes referenced throughout, and for why this module predates the occurrence pattern.

Entities and relationships

item_supply is a same-module child table (parent_eid, no FK — cross-Universe rule). item’s own primary_supply_*/secondary_supply_* clusters are a re-derived projection of the authoritative item_supply rows, refreshed on every write rather than being client-authoritative. Both item and item_supply hold floating references into Business Affiliate — manufacturer and supplier respectively.

Stored shape

item

Column	Type	Null	Role
id	UUID	NOT NULL (PK)	identity
effective_as_of	timestamp	NOT NULL	identity
recorded_as_of	timestamp	NOT NULL, default now()	identity
bts_author	varchar(244)	NOT NULL	bookkeeping
eid	UUID	NOT NULL	identity
previous	UUID	NULL, FK → item.id	identity
retired	boolean	NOT NULL, default false	bookkeeping
tenant_id	UUID	NOT NULL	identity
bts_author_sub, bts_note, bts_created_by_sub	varchar	NULL	bookkeeping
created_by, created_at_effective, created_at_recorded	varchar / timestamp	NOT NULL	bookkeeping
item_name	varchar(255)	NOT NULL	payload
description	varchar(8192)	NULL	payload
image_url	varchar(8192)	NULL	payload
classification_type, classification_sub_type	varchar(255)	NULL	payload
use_case	varchar(255)	NULL	payload
gl_code	varchar(255)	NULL	payload
physical_locator_facility/department/location/sub_location	varchar(255)	NULL	payload
internal_sku	varchar(255)	NULL	payload
min_quantity_amount / min_quantity_unit	double / varchar(255)	NULL	payload
notes	varchar(8192)	NULL	payload
card_notes_default	varchar(8192)	NULL	payload
taxable	boolean	NULL	payload
primary_supply_supply_eid	UUID	NULL	payload — pointer into item_supply
primary_supply_supplier_ref_entity_id / _affiliate_eid	UUID	NULL	payload
primary_supply_supplier_ref_name	varchar(255)	NULL	bookkeeping — value-snapshot
primary_supply_supplier_ref_record_id	UUID	NULL	bookkeeping — version-pin
primary_supply_supplier_ref_retired	boolean	NOT NULL, default false	bookkeeping
primary_supply_supplier_ref_provenance_updated_by/_at	varchar / bigint	NULL	bookkeeping
primary_supply_name, primary_supply_sku, primary_supply_order_method, primary_supply_url	varchar	NULL	bookkeeping — re-derived from item_supply every write
primary_supply_order_quantity_amount/_unit, primary_supply_unit_cost_value/_currency, primary_supply_average_lead_time_length/_time_unit	mixed	NULL	bookkeeping — re-derived
primary_supply_ordering_notes	varchar(8192)	NULL	bookkeeping — re-derived from item_supply.ordering_notes
secondary_supply_* (same sub-column set as primary_supply_*)	mixed	NULL	same roles as the primary cluster
default_supply	varchar(255)	NULL	payload
default_supply_eid	UUID	NULL	payload
card_size, label_size, breadcrumb_size, item_color	varchar(255) (enums)	NULL	payload
manufacturer_ref_entity_id / _affiliate_eid	UUID	NULL	payload
manufacturer_ref_name	varchar(255)	NULL	bookkeeping
manufacturer_ref_record_id	UUID	NULL	bookkeeping — version-pin, set only on retirement
manufacturer_ref_retired, manufacturer_ref_provenance_updated_by/_at	mixed	NULL/NOT NULL	bookkeeping
total_inventory_count_amount/_unit	double / varchar(255)	NULL	payload
last_count_date_timestamp/_time_zone	timestamp / varchar(255)	NULL	payload
identifiers_upc/_ean/_gtin/_isbn/_asin	varchar(255)	NULL	payload
additional_qrs, labels	JSONB	NOT NULL, default []	payload
search_text	text, GENERATED STORED	—	bookkeeping — DB-generated search index, excluded from application writes

Constraints: PK id; FK fk_item_previous__id. No supplier/manufacturer FK anywhere — cross-Universe rule.

item_supply (child of item)

Column	Type	Null	Role
id	UUID	NOT NULL (PK)	identity
effective_as_of	timestamp	NOT NULL	identity
recorded_as_of	timestamp	NOT NULL, default now()	identity
bts_author	varchar(244)	NOT NULL	bookkeeping
eid	UUID	NOT NULL	identity
previous	UUID	NULL, no FK	identity
retired	boolean	NOT NULL, default false	bookkeeping
bts_author_sub, bts_note, bts_created_by_sub	varchar	NULL	bookkeeping
created_by, created_at_effective, created_at_recorded	varchar / timestamp(3)	NOT NULL	bookkeeping
parent_eid	UUID	NOT NULL	identity — floats to Item’s eId, not a pinned rId
supplier_ref_entity_id / _affiliate_eid	UUID	NULL	payload
supplier_ref_name	varchar(255)	NOT NULL	bookkeeping — value-snapshot, preserved from the earlier supplier column
supplier_ref_record_id	UUID	NULL	bookkeeping — version-pin
supplier_ref_retired, supplier_ref_provenance_updated_by/_at	mixed	NOT NULL / NULL	bookkeeping
supply_name, sku, order_method, url	varchar	NULL	payload
order_quantity_amount/_unit, unit_cost_value/_currency, average_lead_time_length/_time_unit	mixed	NULL	payload
ordering_notes	varchar(8192)	NULL	payload — ordering information for this item from this supplier

Constraints: PK id; no FK on previous or parent_eid (cross-Universe rule). Application-level uniqueness only: one supply name per parent, enforced by ItemSupplyValidator, not a DB constraint.

Supporting tables

Table	Purpose	Notable columns
batch_job	Bulk-operation job tracking (shared with system.batch, not Item-specific)	job_status, last_event (JSON), success_count, error_count, total, errors (JSON) — plus the standard bitemporal/bookkeeping columns
item_draft	Draft store keyed by entity, not versioned	entity_id (PK, UNIQUE), tenant_id, draft_value (JSON), draft_metadata (JSON), author
tenant_settings	Tenant-scoped printing-settings override document (PDEV-1077)	overrides (JSONB), plus the standard bitemporal/bookkeeping columns
user_settings	Same shape as tenant_settings but unscoped by tenant (a user’s overrides follow them)	subject (varchar, replaces tenant_id), overrides (JSONB)

Projected shape

Item.Entity (the payload of EntityRecord<Item, ItemMetadata>)

Field	Type	Stored or computed	Notes
eId, name, description, imageUrl, classification, useCase, glCode, locator, internalSKU, minQuantity, notes, cardNotesDefault, taxable, defaultSupply, defaultSupplyEId, cardSize, labelSize, breadcrumbSize, itemColor, manufacturer, totalInventoryCount, lastCountDate, identifiers, additionalQrs, labels	1:1 with item columns	Stored
primarySupply, secondarySupply	ItemSupplyReference.Value?	Stored, but re-derived every write	reconcileItemProjection re-projects the slot from the authoritative ItemSupply before persisting
preferredSupply	ItemSupplyReference?	Computed	defaultSupply name match against primary/secondary, else primary-or-secondary
nonPreferredSupply	ItemSupplyReference?	Computed	the slot not selected as preferred

ItemSupply.Entity (child payload)

Field	Type	Stored or computed	Notes
eId, supplier, name, sku, orderMethod, url, orderQuantity, unitCost, averageLeadTime	1:1 with item_supply columns	Stored
orderingNotes	String?	Stored	Free text: how to order this item from this supplier. A supply created through the API carries the notes in the request, or none when the request has none; it is never seeded from the item’s cardNotesDefault. For notes that apply to every supply from a supplier, see the supplier’s BusinessRole.notes in Business Affiliate: Data Model.

ItemReference.Value (the projection published by /distinct, consumed by every module that references an Item)

Field	Type	Stored or computed	Notes
eId, rId, name, retired, provenance	mixed	Computed	ItemReference.Value.fromItem(...) — retired/provenance denormalize the item’s current state at read time

PrintTemplates (settings read)

Field	Type	Stored or computed	Notes
card, label, breadcrumb	Map of size → template config	Stored (packaged JSON) + Computed	the default flag per form is resolved per-caller, not stored on the item row

Cross-module references

Column	Target	Kind	Notes
item_supply.supplier_ref_entity_id	Business Affiliate — BusinessRole (VENDOR)	floating	resolved via resolveVendorRef/resolveRoleRef
item_supply.supplier_ref_record_id	Business Affiliate BusinessRole tombstone	version-pin	pinned only on retirement
item.manufacturer_ref_entity_id	Business Affiliate BusinessRole (MANUFACTURER)	floating	resolved by resolveManufacturer
item.manufacturer_ref_record_id	Business Affiliate BusinessRole tombstone	version-pin	pinned only on retirement
item.primary_supply_* / secondary_supply_*	item.item_supply (authoritative row)	value-snapshot	kept in sync by reconcileItemProjection and the BA→item vendor cascade
item.tenant_id	tenant scope	tenant/scope	item_supply inherits scope via the parent, enforced query-side, not by a stored column

No foreign key exists from item_supply.parent_eid to item — children reference the parent by floating eId, never a DB FK to a specific row (cross-Universe rule).

API surface

Item is Live, on the legacy URL shape. The routes below are the designed shape; today the module answers on POST /v1/item/item and PUT /v1/item/item/{entity-id} — see the API Endpoint Catalog. It is converging on what is recorded here.

Follows the request/response call-shape convention in The Orders v2 Call Shape.

Method	Path	Purpose	Main refusals
POST	item/add	Create an item (supplies + manufacturer resolved in-transaction)
PUT	item/update	Update an item
(framework)	readByEid, readByRId, forceDelete, queryNode, distinctNode, historyNode, bulkNode, draftNode	Generic data-authority CRUD/query/history/bulk/draft surface
POST	print-label, print-breadcrumb	Print card labels / breadcrumbs for a set of items
GET	settings/printing	Effective print-template configuration
POST/PUT/DELETE/GET	{parent-item-id}/supply[/{item-supply-id}]	Add/update/remove/list an item’s supplies
POST/GET	lookup, lookup/{pageId}	Fuzzy lookup over the generated search text
GET	by-code	Exact code resolution (QR/UPC/EAN/GTIN/ISBN/ASIN)
POST	image-upload/request-upload-credentials	Presigned S3 upload credentials + CDN URL
GET	lookup-suppliers, lookup-units, lookup-items, lookup-types, lookup-subtypes, lookup-usecases, lookup-facilities, lookup-departments, lookup-locations, lookup-sublocations	Fuzzy type-ahead lookups
POST	CSV upload routes	Bulk CSV item import (implemented outside this module, in system.batch.csvupload)

Canonical reference-data aliases for the supply routes also exist at /v1/reference-data/item/item-supply/supply/..., alongside legacy /v1/item/item/... paths sharing the same handlers.

Notifications

Emits NewEntity/UpdateEntity on item create/update. Listens for BusinessRole change notifications — VENDOR changes drive the vendor cascade onto linked item_supply rows and their parent item projections; MANUFACTURER changes drive the manufacturer cascade.

Schema notes

• Duplicate index in the migration SQL. idx_item_seondary_supplier_eid (sic, misspelled in the migration itself) is created twice — the second CREATE INDEX is a no-op, not a functional bug, but it’s a pre-existing typo in V010__item_supply.sql, left as found.
• This module predates the occurrence-kind pattern. No ItemOccurrenceKind exists; change notification uses the generic DataAuthorityNotification<Item, ItemMetadata> sealed type (NewEntity/UpdateEntity/DeleteEntity) instead of a domain-specific kind enum.
• An empty supply slot. The item treats a present slot that carries no user data as absent, and drops it on write (ItemSupplyReference.hasSupplyData()). Non-blank orderingNotes count as data, so a slot that carries only notes is not dropped. A slot with notes is refused unless it identifies its supplier: a non-blank name, a vendor link carrying both ids, or, on update, a supply link. On create a supply link does not count, because the create makes a new supply under that id.
• ordering_notes was seeded once from the item’s card notes. V037__item_supply_ordering_notes.sql added the three columns and wrote two kinds of new record. First, one for every live supply of every live item with non-blank card_notes_default, carrying those notes. Then one for each such item, filling the copy on each slot that has a supplier name and identifies its supplier: a non-blank name, a vendor link carrying both its role and affiliate ids, or a link to a live supply of the item (a write drops a supply link that names none). A slot with no supplier name reads as absent whatever it links, and a slot that identifies nothing reads as empty and is dropped on the next write, so neither gets a copy. Each new record follows the entity’s most current record (previous). It is recorded at the migration’s transaction time truncated to the millisecond, and takes effect at that same time, or one millisecond after the current record when that one is dated later, so it is the current record in either case and a read of a future date made after the migration already sees the notes. card_notes_default itself is unchanged, and rows written before the migration read ordering_notes as NULL.
• primary_supply_*/secondary_supply_* on item are a maintained projection, not client-authoritative data — every write re-derives them from the authoritative item_supply rows, so treat item_supply as the source of truth for supply details even though the fields are readable directly off item.