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_transaction_items.md 6,453 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_transaction_items

Purpose

This table stores transaction item facts in a POS-oriented analytical shape. Each row represents a sold item or related transaction line with enough dimensional context to support sales, mix, tax, and operational reporting.

The structure is close to a legacy reporting contract, so many columns preserve source-specific identifiers and coded values rather than normalized business names.

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
`check`	`INTEGER`	Yes	Check or transaction number that owns the item row.
`item`	`INTEGER`	Yes	Sold item identifier.
`category`	`INTEGER`	Yes	Category identifier for the item.
`hour`	`INTEGER`	Yes	Hour bucket associated with the item transaction.
`minute`	`INTEGER`	Yes	Minute bucket associated with the item transaction.
`revid`	`INTEGER`	Yes	Revenue identifier used by the source contract.
`price`	`DOUBLE PRECISION`	Yes	Unit or recorded price for the item row.
`unit`	`INTEGER`	Yes	Store or business unit identifier.
`dob`	`DATE`	Yes	Business date of the transaction item.
`occasion`	`INTEGER`	Yes	Occasion identifier such as meal or service occasion.
`quantity`	`DOUBLE PRECISION`	Yes	Quantity sold for the item line.
`discpric`	`DOUBLE PRECISION`	Yes	Discounted price recorded for the item.
`bohname`	`VARCHAR(20)`	Yes	Back-of-house item name.
`orgprice`	`DOUBLE PRECISION`	Yes	Original price before discounts or overrides.
`type`	`INTEGER`	Yes	Transaction item type code from the source POS.
`parent`	`INTEGER`	Yes	Parent item identifier for combo or nested item structures.
`mode`	`INTEGER`	Yes	Mode identifier used by the POS contract.
`period`	`INTEGER`	Yes	Business period identifier.
`taxid`	`INTEGER`	Yes	Primary tax identifier applied to the item.
`termid`	`INTEGER`	Yes	Terminal identifier associated with the sale.
`menu`	`INTEGER`	Yes	Menu identifier associated with the item.
`origin`	`INTEGER`	Yes	Origin code indicating where the item came from.
`modcode`	`INTEGER`	Yes	Modifier code applied to the item when relevant.
`seat`	`INTEGER`	Yes	Seat number associated with the item.
`entryid`	`INTEGER`	Yes	Entry identifier for the item row inside the transaction.
`taxid2`	`INTEGER`	Yes	Secondary tax identifier applied to the item.
`revid2`	`INTEGER`	Yes	Secondary revenue identifier.
`concept`	`INTEGER`	Yes	Concept or brand identifier in the source contract.
`incltax`	`DOUBLE PRECISION`	Yes	Inclusive tax amount recorded for the item.
`course`	`INTEGER`	Yes	Course identifier for kitchen or service sequencing.
`excltax`	`INTEGER`	Yes	Exclusive-tax indicator or amount field from the source contract.
`parentmod`	`INTEGER`	Yes	Parent modifier identifier for nested modifier structures.
`etime_hour`	`INTEGER`	Yes	Event-time hour component recorded by the source contract.
`etime_min`	`INTEGER`	Yes	Event-time minute component recorded by the source contract.
`etime_sec`	`INTEGER`	Yes	Event-time second component recorded by the source contract.
`tableid`	`INTEGER`	Yes	Table identifier associated with the item sale.
`qtyunit`	`DOUBLE PRECISION`	Yes	Quantity expressed in the source unit of measure.
`substitutd`	`INTEGER`	Yes	Substitution indicator or related code.
`portion`	`INTEGER`	Yes	Portion identifier or portion size code.
`occtaxid`	`INTEGER`	Yes	Occasion tax identifier.
`parentenid`	`INTEGER`	Yes	Parent entry identifier for nested lines.
`item_name`	`VARCHAR(255)`	Yes	Readable sellable item name in canonical business form.
`category_name`	`VARCHAR(255)`	Yes	Readable category name associated with the transaction item.
`revenue_center_name`	`VARCHAR(255)`	Yes	Readable revenue center name associated with the transaction item.
`terminal_name`	`VARCHAR(255)`	Yes	Readable terminal or order-origin name associated with the sale.
`service_channel`	`VARCHAR(100)`	Yes	Standardized business channel such as dine-in, takeout, kiosk, or delivery.
`business_timestamp_utc`	`TIMESTAMP`	Yes	UTC timestamp representing the canonical business moment of the transaction item.
`discount_amount`	`DECIMAL(18, 4)`	Yes	Monetary discount applied to the transaction item line.
`currency_code`	`VARCHAR(10)`	Yes	ISO or business-standard currency code used for item-level monetary values.

Technical and ETL properties

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