# curated.inventory_items

## Purpose

This table stores inventory master and stock-position data for items tracked outside the live order stream. It is intended for stock monitoring, replenishment logic, waste analysis, and cost-aware operational reporting.

Compared with POS item catalogs, this table focuses on supply and stock state rather than what the guest can order.

## 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 |
|---|---|---|---|---|
| `inventory_id` | `INTEGER` | No | Inventory-system identifier of the stocked item. |  |
| `sku` | `VARCHAR(30)` | No | Stock keeping unit used for procurement and inventory control. |  |
| `item_name` | `VARCHAR(255)` | No | Human-readable inventory item name. |  |
| `pos_item_id` | `INTEGER` | Yes | Optional link to the POS item associated with this stock item. |  |
| `category_name` | `VARCHAR(100)` | Yes | Inventory category name. |  |
| `unit_of_measure` | `VARCHAR(30)` | No | Unit used to measure stock quantities. |  |
| `cost_per_unit` | `DECIMAL(10, 2)` | No | Standard cost for one unit of inventory. |  |
| `quantity_on_hand` | `DECIMAL(10, 2)` | No | Current stock quantity available. |  |
| `par_level` | `DECIMAL(10, 2)` | No | Target stock level to maintain. |  |
| `reorder_point` | `DECIMAL(10, 2)` | No | Threshold that should trigger replenishment. |  |
| `vendor_name` | `VARCHAR(255)` | Yes | Preferred vendor or supplier name. |  |
| `last_updated` | `TIMESTAMP` | Yes | Last time the inventory record was updated in the source. |  |
| `store_id` | `VARCHAR(199)` | Yes | Canonical store identifier associated with the inventory position. |  |
| `store_name` | `VARCHAR(255)` | Yes | Readable store name associated with the inventory position. |  |
| `vendor_id` | `VARCHAR(199)` | Yes | Canonical vendor identifier for the preferred supplier. |  |
| `item_status` | `VARCHAR(100)` | Yes | Business-friendly status of the inventory item such as active, discontinued, or seasonal. |  |
| `last_counted_at_utc` | `TIMESTAMP` | Yes | UTC timestamp of the last physical or system stock count. |  |
| `updated_at_utc` | `TIMESTAMP` | Yes | UTC timestamp when the curated inventory row was last updated. |  |
| `currency_code` | `VARCHAR(10)` | Yes | ISO or business-standard currency code used for item cost values. |  |
| `snapshot_at_utc` | `TIMESTAMP` | Yes | UTC timestamp representing when the stock position snapshot was captured. |  |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `inventory_item_key` | `BIGINT` | Yes | Stable warehouse key for the inventory item entity. |  |
| `source_system` | `VARCHAR(100)` | Yes | Canonical name of the source platform that produced the inventory record. |  |
| `source_inventory_item_id` | `VARCHAR(199)` | Yes | Original inventory item identifier from the source platform. |  |
| `tenant_key` | `BIGINT` | Yes | Stable warehouse key for the tenant, brand, or client that owns the inventory position. |  |
| `tenant_name` | `VARCHAR(255)` | Yes | Readable tenant, brand, or client name associated with the inventory position. |  |
| `_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. |