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

Raw File

Curated Model

Purpose

This folder documents the current shared curated schema created by the bootstrap SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/.

This is the business-facing warehouse layer that AI agents should use first. The model is meant to be shared across integrations, so each table should expose stable business meaning rather than vendor-specific payload shape.

For this folder, the source of truth for table existence and columns is the deployed SQL. The documentation explains:

what business entity each table represents
what value it gives to PO, analysts, and AI agents
what the current schema contract looks like
how the model is intended to support multi-source ingestion

Current priority

Current active curated tables are:

curated.pos_orders
curated.pos_order_items
curated.pos_menu_items
curated.employees
curated.pos_jobs
curated.labor_shifts
curated.pos_destinations

Modeling principles for PO

curated is the semantic contract for downstream AI and business use, not a copy of staging.
The model must stay broad enough to accept data from multiple POS systems.
If a source does not provide a field yet, the field may stay NULL; this is better than silently widening ETL scope.
Shared business terms such as order, item, status, business date, service channel, and source lineage are more important than vendor-native field names.
Order-centric and transaction-centric facts can coexist, but their business meaning must stay explicit.
Source-specific population rules and mappings belong in source-mapping documents, not in the curated schema contract.

Cross-table summary

Table	Primary role	Grain	Key business columns	Strong sides	Main modeling risk	Most important next step
`curated.employees`	Employee dimension	One row per employee snapshot	`employee_id`, `display_name`, `is_active`, `hire_date`	Stable workforce reference for joins and attribution	Snapshot semantics must stay explicit	Keep source-agnostic employee identity fields stable
`curated.labor_shifts`	Labor fact	One row per labor shift	`shift_id`, `employee_id`, `business_date`, `minutes_worked`, `pay_rate`	Shift timing and labor-cost analysis surface	Vendor-specific break semantics can leak through	Keep shift grain explicit
`curated.pos_destinations`	Destination dimension	One row per destination snapshot	`destination_id`, `name`, `is_delivery`, `is_active`	Order-channel and routing context	Snapshot semantics must stay explicit	Keep source-agnostic destination identity fields stable
`curated.pos_jobs`	Job dimension	One row per job snapshot	`job_id`, `name`, `is_bartender`, `is_delivery_driver`	Workforce role context for orders and labor	Source role semantics can drift by vendor	Keep canonical job meaning narrow
`curated.pos_menu_items`	Menu-state snapshot	One row per menu item per snapshot	`item_id`, `item_name`, `price`, `is_active`, `category_id`	Good availability and routing context	Can drift into serving or UI model	Keep it as canonical sellable menu state
`curated.pos_order_items`	Business order-line fact	One row per order line item	`order_id`, `item_id`, `item_name`, `unit_price`, `quantity`	Good basket and order composition context	Can overlap with transaction-line model	Separate order semantics from settlement semantics
`curated.pos_orders`	Business order header fact	One row per business order	`order_id`, `order_type`, `subtotal`, `total`, `business_date`, `service_channel`	Strong commercial order surface	Still partly serving-like and source-shaped	Strengthen canonical order lifecycle and channel semantics

Table type classification

Table	Recommended type
`curated.employees`	`dimension`
`curated.labor_shifts`	`fact`
`curated.pos_destinations`	`dimension`
`curated.pos_jobs`	`dimension`
`curated.pos_menu_items`	`snapshot`
`curated.pos_order_items`	`fact`
`curated.pos_orders`	`fact`

Tables

employees.md
labor_shifts.md
pos_destinations.md
pos_jobs.md
pos_menu_items.md
pos_order_items.md
pos_orders.md
todo.md