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_transactions.md 4,649 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_transactions

Purpose

This table stores transaction header facts in the current POS-oriented curated contract. It represents one transaction-level record with time-bucket, employee, revenue, and register context already flattened for analytics.

The shape is still close to a legacy POS warehouse contract, so consumers should treat it as an approved read surface rather than a normalized business model.

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	POS check or transaction number.
`amount`	`DOUBLE PRECISION`	Yes	Main transaction amount.
`openhour`	`INTEGER`	Yes	Hour when the transaction was opened.
`openmin`	`INTEGER`	Yes	Minute when the transaction was opened.
`orderhour`	`INTEGER`	Yes	Hour when the order was placed.
`ordermin`	`INTEGER`	Yes	Minute when the order was placed.
`closehour`	`INTEGER`	Yes	Hour when the transaction closed.
`closemin`	`INTEGER`	Yes	Minute when the transaction closed.
`count`	`INTEGER`	Yes	Legacy count metric carried by the source contract.
`revenue`	`INTEGER`	Yes	Revenue center or revenue bucket identifier.
`unit`	`INTEGER`	Yes	Store or business unit identifier from the POS.
`dob`	`DATE`	Yes	Business date of the transaction.
`occasion`	`INTEGER`	Yes	Occasion identifier such as meal or service occasion.
`employee`	`INTEGER`	Yes	Employee identifier associated with the transaction.
`period`	`INTEGER`	Yes	Business period identifier used by the POS.
`type`	`INTEGER`	Yes	Transaction type code from the POS source.
`typeid`	`INTEGER`	Yes	More specific subtype identifier for the transaction.
`shift`	`INTEGER`	Yes	Shift identifier associated with the transaction.
`typeid2`	`INTEGER`	Yes	Secondary subtype identifier from the source contract.
`revid2`	`INTEGER`	Yes	Secondary revenue identifier.
`store_name`	`VARCHAR(255)`	Yes	Readable store name associated with the transaction.
`revenue_center_name`	`VARCHAR(255)`	Yes	Readable revenue center name associated with the transaction.
`occasion_name`	`VARCHAR(255)`	Yes	Readable occasion or meal-period name associated with the transaction.
`transaction_type_name`	`VARCHAR(255)`	Yes	Readable business label for the transaction type.
`business_timestamp_utc`	`TIMESTAMP`	Yes	UTC timestamp representing the canonical business time of the transaction.
`guest_count`	`INTEGER`	Yes	Canonical guest count associated with the transaction when available.
`net_amount`	`DECIMAL(18, 4)`	Yes	Canonical net transaction amount after discounts and before or after tax according to curated business rules.
`currency_code`	`VARCHAR(10)`	Yes	ISO or business-standard currency code used for transaction monetary values.
`transaction_status_code`	`VARCHAR(100)`	Yes	Standardized canonical code describing the commercial state of the transaction.

Technical and ETL properties

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