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)

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.

branches/life-cycle/warehouse-model/docs/features/ETL/warehouse-data/curated-model/pos_orders.md 9,517 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

curated.pos_orders

Purpose

This table stores operational POS order headers in a flattened, application-friendly format. It complements the heavier transaction schema with a simpler order-centric view that can be used by APIs, exports, and troubleshooting tools.

The shape mixes commercial facts, delivery/export status, and operational message fields, so it is best treated as a serving contract rather than a pure canonical order model.

Warehouse design assessment

DW fit: Good order-header fact table if it stays at one row per business order and separates descriptive channel fields from numeric measures.
Cross-client contract: Standardize order lifecycle, payment summary, fulfillment channel, and customer linkage across all source POS systems.
IDs vs business values: order_id, terminal_id, and customer_number are necessary, but business analysis also needs store_name, terminal_name, status_name, and normalized channel labels.
AI and business usability: AI agents will use this table much more reliably when it exposes direct business terms such as dine-in, delivery, completed, cancelled, and paid.
Recommended extensions: Add order_key, source_system, source_order_id, store_id, store_name, status_name, service_channel, fulfillment_channel, created_at_utc, and business_date.

Detailed modeling guidance

Recommended role: Use this table as the canonical commercial order header with one row per business order.
Key differences from a stronger curated model: The model should favor standardized order lifecycle and channel semantics over source-specific order-type behavior or convenience payment fields.
What is already useful: Order identifiers, totals, created-at time, and customer linkage already make this table highly practical.
What should move later to serving: Customer-facing order summaries, operational KPI labels, and narrative order explanations belong in serving.

Curated contract columns

Column	Type	Monitor status	Purpose	Monitor rationale
`order_id`	`VARCHAR(50)`	Used by monitors	Business or external order identifier.	Core order key for linking order-level facts back to customer and item behavior. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`order_type`	`VARCHAR(50)`	Used by monitors	Order type such as dine-in, carryout, or delivery.	Distinguishes service channels and supports customer behavior segmentation by fulfillment mode. Monitors: customer-profiles, segment-customers.
`payment_method`	`VARCHAR(50)`	Used by monitors	Payment method summary for the order.	Useful as a simplified payment preference attribute when full tender joins are not required. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`subtotal`	`DECIMAL(10, 2)`	Used by monitors	Order subtotal before tax and other adjustments.	Supports spend analysis and basket-size interpretation before taxes. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`total`	`DECIMAL(10, 2)`	Used by monitors	Final order total.	Main order-value measure used in customer spend and value analysis. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`created_at`	`TIMESTAMP`	Used by monitors	Timestamp when the order was created.	Core event time for customer recency, visit patterns, and segmentation windows. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`customer_number`	`VARCHAR(50)`	Used by monitors	Customer or loyalty account number attached to the order.	Main identity bridge from orders to loyalty customer records. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers.
`id`	`INTEGER`	Available for future monitors	Internal identifier of the curated order row.	Useful for traceability inside the serving contract, but current customer monitors usually join on `order_id` or `customer_number`. Potential monitors: customer-profiles.
`table_number`	`VARCHAR(50)`	Available for future monitors	Dining table or location marker for the order.	Adds dine-in context, but current customer monitors can work without table-level detail. Potential monitors: customer-profiles, segment-customers.
`tax`	`DECIMAL(10, 2)`	Available for future monitors	Total tax amount for the order.	Helps break down spend composition, but current customer monitors focus more on total spend than tax structure. Potential monitors: customer-profiles.
`status`	`VARCHAR(50)`	Available for future monitors	Current business status of the order.	Could help future filters that separate completed and cancelled orders more explicitly. Potential monitors: customer-churn-risk, customer-profiles.
`terminal_id`	`VARCHAR(50)`	Available for future monitors	Terminal identifier used for the order.	Can hint at drive-thru or kiosk behavior, but current customer monitors do not depend on terminal analysis. Potential monitors: customer-profiles, segment-customers.
`points_used`	`INTEGER`	Available for future monitors	Loyalty points redeemed on the order.	Adds loyalty-engagement context that could enrich churn or high-value customer scoring. Potential monitors: customer-churn-risk, identify-high-value-customers.
`additional_payment`	`DECIMAL(10, 2)`	Available for future monitors	Additional payment amount applied outside the main method.	Can explain split-pay behavior, but current customer monitors do not use it directly. Potential monitors: customer-profiles.
`pos_terminal_id`	`VARCHAR(50)`	Available for future monitors	POS-native terminal identifier when different from the serving field.	Can help channel segmentation if the team later distinguishes kiosk, web, or staffed terminals. Potential monitors: customer-profiles, segment-customers.
`destination`	`VARCHAR(100)`	Available for future monitors	Operational destination or queue label for the order.	Can help split dine-in, pickup, and other destinations in future customer behavior analysis. Potential monitors: customer-profiles, segment-customers.
`terminal_name`	`VARCHAR(255)`	Available for future monitors	Human-readable terminal name.	Easier-to-read terminal context could improve future channel diagnostics. Potential monitors: customer-profiles.
`order_key`	`BIGINT`	Recommended extension	Stable warehouse key for the order entity.	Recommended to create one canonical order identifier across clients and source systems.
`source_system`	`VARCHAR(100)`	Recommended extension	Canonical name of the source platform that produced the order row.	Recommended to preserve lineage in the shared curated schema.
`source_order_id`	`VARCHAR(199)`	Recommended extension	Original order identifier from the source platform.	Recommended to support reconciliation and drill-through into staging.
`store_id`	`VARCHAR(199)`	Recommended extension	Canonical store identifier associated with the order.	Recommended to make store joins consistent across different client systems.
`store_name`	`VARCHAR(255)`	Recommended extension	Readable store name associated with the order.	Recommended because business users and AI agents need store names, not only IDs.
`status_name`	`VARCHAR(100)`	Recommended extension	Readable business label for the order status.	Recommended to make lifecycle analysis easier than relying only on raw status fields.
`service_channel`	`VARCHAR(100)`	Recommended extension	Standardized business channel such as dine-in, takeout, drive-thru, or delivery.	Recommended to normalize order-channel analysis across clients.
`fulfillment_channel`	`VARCHAR(100)`	Recommended extension	Standardized fulfillment method used to complete the order.	Recommended to improve operational and customer behavior analysis across source systems.
`created_at_utc`	`TIMESTAMP`	Recommended extension	UTC timestamp when the order was created in canonical warehouse time.	Recommended to standardize timestamps across tenants and time zones.
`business_date`	`DATE`	Recommended extension	Business date associated with the order in curated reporting logic.	Recommended to simplify grouping and filtering for downstream analysis.
`tenant_key`	`BIGINT`	Recommended extension	Stable warehouse key for the tenant, brand, or client that owns the order.	Recommended because a shared curated schema should preserve explicit cross-client context for core commercial facts.
`tenant_name`	`VARCHAR(255)`	Recommended extension	Readable tenant, brand, or client name associated with the order.	Recommended to improve business and AI usability in cross-client reporting.
`currency_code`	`VARCHAR(10)`	Recommended extension	ISO or business-standard currency code used for order monetary values.	Recommended because commercial analysis across clients should not assume one currency.
`order_status_code`	`VARCHAR(100)`	Recommended extension	Standardized canonical code for the business order lifecycle state.	Recommended to complement `status_name` with a stable machine-friendly business status contract.