# 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 | Notes |
|---|---|---|---|---|
| `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. |