Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Warehouse Model (93)

Analytical data warehouse schemas, curated tables, and Redshift/Athena data models.

warehouse-model/docs/features/ETL/warehouse-data/curated-model/pos_catalog_items.md 7,421 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_catalog_items

Purpose

This table stores POS item master data in a reporting-friendly flattened contract. It is the broadest reference surface for item naming, tax, routing, price, configuration, and kitchen-facing attributes.

It is useful for joins from transaction items, menu projections, and configuration diagnostics, but many columns remain source-specific and should be interpreted as legacy POS contract fields.

Schema contract

The deployed SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/ is the source of truth for the current column names, types, and nullability.

Column groups

Business properties describe the business entity, event, fact, or reference record represented by the table.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, slowly changing dimension controls, and ingestion metadata.

Business properties

Column	Type	Nullable	Business Meaning
`id`	`INTEGER`	Yes	Item identifier in the POS source.
`longname`	`VARCHAR(25)`	Yes	Longer item name used in operational displays.
`cost`	`DOUBLE PRECISION`	Yes	Standard cost of the item.
`price`	`DOUBLE PRECISION`	Yes	Selling price of the item.
`mod_price`	`DOUBLE PRECISION`	Yes	Modifier price amount.
`donotshow`	`VARCHAR(1)`	Yes	Flag hiding the item from some displays.
`ownerid`	`INTEGER`	Yes	Owning configuration or location identifier.
`usernumber`	`INTEGER`	Yes	User-visible POS item number.
`shortname`	`VARCHAR(15)`	Yes	Short item name used by the POS.
`longname2`	`VARCHAR(25)`	Yes	Secondary long item name.
`bohname`	`VARCHAR(20)`	Yes	Back-of-house item name.
`taxid`	`INTEGER`	Yes	Primary tax identifier for the item.
`taxid2`	`INTEGER`	Yes	Secondary tax identifier.
`priority`	`INTEGER`	Yes	Priority or sort value for item handling.
`routing`	`INTEGER`	Yes	Routing identifier used for kitchen or production flow.
`surcharge`	`INTEGER`	Yes	Surcharge identifier associated with the item.
`mod1`	`INTEGER`	Yes	Linked modifier group slot 1.
`mod2`	`INTEGER`	Yes	Linked modifier group slot 2.
`mod3`	`INTEGER`	Yes	Linked modifier group slot 3.
`mod4`	`INTEGER`	Yes	Linked modifier group slot 4.
`mod5`	`INTEGER`	Yes	Linked modifier group slot 5.
`mod6`	`INTEGER`	Yes	Linked modifier group slot 6.
`mod7`	`INTEGER`	Yes	Linked modifier group slot 7.
`mod8`	`INTEGER`	Yes	Linked modifier group slot 8.
`mod9`	`INTEGER`	Yes	Linked modifier group slot 9.
`mod10`	`INTEGER`	Yes	Linked modifier group slot 10.
`askprice`	`VARCHAR(1)`	Yes	Flag indicating whether price entry is prompted.
`isrefill`	`VARCHAR(1)`	Yes	Flag indicating refill behavior.
`is_kvi`	`INTEGER`	Yes	Flag or code for key-value item classification.
`price_id`	`INTEGER`	Yes	Price table or price-rule identifier.
`slavetoitm`	`INTEGER`	Yes	Parent item identifier when pricing or behavior is slaved.
`defweight`	`INTEGER`	Yes	Default weight or quantity setting.
`sku`	`VARCHAR(15)`	Yes	SKU or alternate commercial identifier.
`modprice`	`VARCHAR(1)`	Yes	Flag controlling modifier pricing behavior.
`itemmult`	`INTEGER`	Yes	Item multiplier setting.
`itempercnt`	`DOUBLE PRECISION`	Yes	Item percentage used in pricing or allocation.
`modpriceid`	`INTEGER`	Yes	Modifier price rule identifier.
`ampri`	`INTEGER`	Yes	Alternate price indicator or rule code.
`compositid`	`INTEGER`	Yes	Composite-item identifier.
`independnt`	`VARCHAR(1)`	Yes	Flag indicating independent pricing or behavior.
`showndpnt`	`VARCHAR(1)`	Yes	Flag controlling point-of-sale visibility.
`delaytime`	`INTEGER`	Yes	Delay or prep offset configured for the item.
`guests`	`DOUBLE PRECISION`	Yes	Guest count contribution or expected serving value.
`concept`	`INTEGER`	Yes	Concept or brand identifier.
`multiplier`	`INTEGER`	Yes	Quantity or pricing multiplier.
`revitem`	`VARCHAR(1)`	Yes	Flag indicating revenue-item behavior.
`dntshwsm`	`VARCHAR(1)`	Yes	Flag hiding the item from specific menu views.
`guestwght`	`DOUBLE PRECISION`	Yes	Guest-weight contribution of the item.
`pizza`	`VARCHAR(1)`	Yes	Flag indicating pizza-item behavior.
`topping`	`VARCHAR(1)`	Yes	Flag indicating topping-item behavior.
`pizzasize`	`INTEGER`	Yes	Pizza-size identifier.
`fraction`	`VARCHAR(1)`	Yes	Flag indicating fractional-item behavior.
`fracprcovr`	`INTEGER`	Yes	Fraction price-override configuration code.
`type`	`INTEGER`	Yes	Source item type code.
`nozeropr`	`VARCHAR(1)`	Yes	Flag suppressing zero-price behavior.
`category_id`	`VARCHAR(199)`	Yes	Canonical category identifier for the item.
`category_name`	`VARCHAR(255)`	Yes	Readable category name for the item.
`department_name`	`VARCHAR(255)`	Yes	Readable department or higher-level product grouping for the item.
`tax_name`	`VARCHAR(255)`	Yes	Readable tax classification name.
`routing_name`	`VARCHAR(255)`	Yes	Readable kitchen or production routing name for the item.
`is_active`	`BOOLEAN`	Yes	Canonical flag indicating whether the item is active and sellable in the curated model.
`revenue_center_name`	`VARCHAR(255)`	Yes	Readable revenue center name when applicable to the item.
`currency_code`	`VARCHAR(10)`	Yes	ISO or business-standard currency code used for price and cost values.

Technical and ETL properties

Column	Type	Nullable	Technical Meaning	Notes
`catalog_item_key`	`BIGINT`	Yes	Stable warehouse key for the catalog item entity.
`source_system`	`VARCHAR(100)`	Yes	Canonical name of the source platform that produced the item record.
`source_item_id`	`VARCHAR(199)`	Yes	Original item identifier from the source platform.
`tenant_key`	`BIGINT`	Yes	Stable warehouse key for the tenant, brand, or client that owns the catalog item.
`tenant_name`	`VARCHAR(255)`	Yes	Readable tenant, brand, or client name associated with the catalog item.
`effective_from_utc`	`TIMESTAMP`	Yes	UTC timestamp from which the current catalog-item version is valid.
`effective_to_utc`	`TIMESTAMP`	Yes	UTC timestamp until which the current catalog-item version is valid.
`is_current`	`BOOLEAN`	Yes	Canonical flag indicating whether the row is the current catalog-item version.
`_etl_batch_id`	`VARCHAR(256)`	Yes	ETL batch lineage identifier.
`_etl_source_type`	`VARCHAR(100)`	Yes	Source adapter type used by ETL.
`_etl_ingested_at`	`TIMESTAMP`	No	Warehouse ingestion timestamp for the curated row.	Required ingestion audit timestamp.