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/feature-fleet-import/architecture/docs/curated-redshift-schema.md 15,783 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Curated Redshift Schema

Conventions

Nullable columns may be unavailable for some source systems.
Redshift does not enforce primary-key or foreign-key constraints on these tables. Relationships below are logical relationships.
Columns ending in _key are warehouse keys intended for canonical joins.
Columns beginning with _etl_ provide ingestion lineage and audit information.
business_date represents the restaurant's operational date and may differ from the calendar date of a UTC timestamp.
Monetary values should be interpreted using currency_code where that column is available.

Schema Summary

Table	Purpose	Grain
`curated.kitchen_orders`	Kitchen order headers	One row per kitchen order
`curated.kitchen_order_items`	Items prepared by the kitchen	One row per kitchen order item
`curated.kitchen_item_status_events`	Kitchen item status history	One row per item status event
`curated.customers`	Customer records	One row per customer record
`curated.pos_menu_items`	Canonical menu item state	One row per menu item snapshot
`curated.pos_order_items`	POS order lines	One row per POS order item
`curated.pos_orders`	POS order headers	One row per POS order
`curated.employees`	Employee dimension	One row per employee snapshot
`curated.pos_jobs`	POS job or role dimension	One row per job snapshot
`curated.labor_shifts`	Employee labor shifts	One row per shift
`curated.pos_destinations`	Order destination or service channel dimension	One row per destination snapshot

Logical Relationships

Parent	Child	Join condition
`curated.kitchen_orders`	`curated.kitchen_order_items`	`kitchen_orders.guid = kitchen_order_items.order_guid`
`curated.kitchen_order_items`	`curated.kitchen_item_status_events`	`kitchen_order_items.guid = kitchen_item_status_events.item_guid`
`curated.pos_orders`	`curated.pos_order_items`	`pos_orders.order_id = pos_order_items.order_id`
`curated.employees`	`curated.labor_shifts`	`employees.employee_id = labor_shifts.employee_id`
`curated.pos_jobs`	`curated.labor_shifts`	`pos_jobs.job_id = labor_shifts.job_id`

`curated.kitchen_orders`

Kitchen-facing order headers used to analyze order volume, workflow, priority, and preparation performance.

Column	Redshift type	Nullable
`guid`	`VARCHAR(36)`	No
`store_guid`	`VARCHAR(36)`	No
`destination`	`VARCHAR(255)`	Yes
`external_id`	`VARCHAR(199)`	No
`guest_table`	`VARCHAR(199)`	Yes
`is_priority`	`SMALLINT`	No
`items_count`	`INTEGER`	No
`order_type`	`VARCHAR(199)`	Yes
`pos_terminal`	`VARCHAR(199)`	Yes
`preparation_time`	`INTEGER`	Yes
`server_name`	`VARCHAR(199)`	Yes
`user_info`	`VARCHAR(199)`	Yes
`done`	`SMALLINT`	No
`create_time`	`INTEGER`	No
`update_time`	`INTEGER`	No
`upload_time`	`INTEGER`	Yes
`is_deleted`	`SMALLINT`	No
`update_device`	`VARCHAR(36)`	Yes
`phone`	`VARCHAR(65535)`	Yes
`create_local_time`	`INTEGER`	Yes
`is_hidden`	`SMALLINT`	No
`customer_guid`	`VARCHAR(36)`	Yes
`smart_order_start_time`	`INTEGER`	Yes
`order_messages`	`VARCHAR(255)`	No
`customer_name`	`VARCHAR(60)`	Yes
`seat`	`INTEGER`	Yes
`order_status`	`INTEGER`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.kitchen_order_items`

Kitchen order lines with preparation, routing, transfer, and completion attributes.

Column	Redshift type	Nullable
`guid`	`VARCHAR(36)`	No
`order_guid`	`VARCHAR(36)`	No
`external_id`	`VARCHAR(199)`	No
`name`	`VARCHAR(199)`	No
`category`	`VARCHAR(100)`	Yes
`device_id`	`INTEGER`	Yes
`is_priority`	`SMALLINT`	No
`pre_modifier`	`VARCHAR(65535)`	Yes
`item_bump_guid`	`VARCHAR(36)`	Yes
`create_time`	`INTEGER`	No
`update_time`	`INTEGER`	No
`upload_time`	`INTEGER`	Yes
`is_deleted`	`SMALLINT`	No
`update_device`	`VARCHAR(36)`	Yes
`printed_status`	`SMALLINT`	No
`create_local_time`	`INTEGER`	Yes
`is_hidden`	`SMALLINT`	No
`ready_since_local_time`	`INTEGER`	Yes
`preparation_time`	`INTEGER`	Yes
`quantity`	`INTEGER`	No
`count`	`INTEGER`	Yes
`condiments_count`	`INTEGER`	No
`s0`	`VARCHAR(45)`	Yes
`s1`	`VARCHAR(45)`	Yes
`training_video`	`VARCHAR(65535)`	Yes
`transfer_from_device_id`	`INTEGER`	Yes
`transfer_time`	`INTEGER`	Yes
`untransfer_time`	`INTEGER`	Yes
`beeped`	`SMALLINT`	No
`build_card`	`VARCHAR(65535)`	Yes
`recall_time`	`INTEGER`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.kitchen_item_status_events`

Status transitions for kitchen order items, including preparation and completion timing.

Column	Redshift type	Nullable
`guid`	`VARCHAR(36)`	No
`item_guid`	`VARCHAR(36)`	No
`status`	`SMALLINT`	No
`last_status`	`SMALLINT`	Yes
`upload_time`	`INTEGER`	Yes
`update_device`	`VARCHAR(36)`	Yes
`create_local_time`	`INTEGER`	Yes
`prepared_local_time`	`INTEGER`	Yes
`done_local_time`	`INTEGER`	Yes
`prepared_device_id`	`INTEGER`	Yes
`done_device_id`	`INTEGER`	Yes
`hide_level`	`SMALLINT`	No
`last_hide_level`	`SMALLINT`	No
`create_time`	`INTEGER`	No
`update_time`	`INTEGER`	No
`is_deleted`	`SMALLINT`	No
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.customers`

Customer identity and contact records associated with a restaurant store.

Column	Redshift type	Nullable
`guid`	`VARCHAR(36)`	No
`external_id`	`VARCHAR(199)`	Yes
`name`	`VARCHAR(255)`	Yes
`phone`	`VARCHAR(255)`	Yes
`phone2`	`VARCHAR(255)`	Yes
`address`	`VARCHAR(65535)`	Yes
`address2`	`VARCHAR(65535)`	Yes
`city`	`VARCHAR(255)`	Yes
`state`	`VARCHAR(255)`	Yes
`zip`	`VARCHAR(50)`	Yes
`country`	`VARCHAR(255)`	Yes
`email`	`VARCHAR(255)`	Yes
`webmail`	`VARCHAR(255)`	Yes
`store_guid`	`VARCHAR(36)`	No
`create_time`	`INTEGER`	Yes
`update_time`	`INTEGER`	Yes
`upload_time`	`INTEGER`	Yes
`is_deleted`	`SMALLINT`	No
`update_device`	`VARCHAR(36)`	Yes
`create_local_time`	`INTEGER`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.pos_menu_items`

Canonical menu item state, including pricing, category, kitchen routing, availability, and source lineage.

Column	Redshift type	Nullable
`item_id`	`INTEGER`	No
`item_name`	`VARCHAR(255)`	No
`price`	`DECIMAL(10,2)`	No
`category_id`	`INTEGER`	No
`description`	`VARCHAR(65535)`	Yes
`kds_delay`	`INTEGER`	Yes
`category_name`	`VARCHAR(100)`	Yes
`kds_station`	`INTEGER`	Yes
`is_86d`	`DECIMAL(2,0)`	Yes
`86d_reason`	`VARCHAR(65535)`	Yes
`86d_at`	`TIMESTAMP`	Yes
`menu_item_key`	`BIGINT`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_menu_item_id`	`VARCHAR(199)`	Yes
`station_name`	`VARCHAR(255)`	Yes
`availability_status`	`VARCHAR(100)`	Yes
`is_active`	`BOOLEAN`	Yes
`cost`	`DECIMAL(10,2)`	Yes
`snapshot_date`	`DATE`	Yes
`effective_from_utc`	`TIMESTAMP`	Yes
`effective_to_utc`	`TIMESTAMP`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`timezone_name`	`VARCHAR(100)`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.pos_order_items`

Canonical POS order lines for basket, product-mix, pricing, and item-status analysis.

Column	Redshift type	Nullable
`order_id`	`VARCHAR(50)`	No
`item_id`	`INTEGER`	No
`item_name`	`VARCHAR(255)`	No
`quantity`	`INTEGER`	Yes
`unit_price`	`DECIMAL(10,2)`	Yes
`id`	`INTEGER`	No
`size_name`	`VARCHAR(100)`	Yes
`modifiers`	`VARCHAR(65535)`	Yes
`kds_delay`	`INTEGER`	Yes
`order_item_key`	`BIGINT`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_order_item_id`	`VARCHAR(199)`	Yes
`category_id`	`VARCHAR(199)`	Yes
`category_name`	`VARCHAR(255)`	Yes
`item_status`	`VARCHAR(100)`	Yes
`modifier_summary`	`VARCHAR(65535)`	Yes
`created_at_utc`	`TIMESTAMP`	Yes
`business_date`	`DATE`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.pos_orders`

Canonical POS order headers for sales, tax, order status, service channel, and fulfillment analysis.

Column	Redshift type	Nullable
`order_id`	`VARCHAR(50)`	No
`order_type`	`VARCHAR(50)`	Yes
`payment_method`	`VARCHAR(50)`	Yes
`subtotal`	`DECIMAL(10,2)`	Yes
`total`	`DECIMAL(10,2)`	Yes
`created_at`	`TIMESTAMP`	Yes
`customer_number`	`VARCHAR(50)`	Yes
`id`	`INTEGER`	No
`table_number`	`VARCHAR(50)`	Yes
`tax`	`DECIMAL(10,2)`	Yes
`status`	`VARCHAR(50)`	Yes
`terminal_id`	`VARCHAR(50)`	Yes
`points_used`	`INTEGER`	Yes
`additional_payment`	`DECIMAL(10,2)`	Yes
`pos_terminal_id`	`VARCHAR(50)`	Yes
`destination`	`VARCHAR(100)`	Yes
`terminal_name`	`VARCHAR(255)`	Yes
`order_key`	`BIGINT`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_order_id`	`VARCHAR(199)`	Yes
`store_id`	`VARCHAR(199)`	Yes
`store_name`	`VARCHAR(255)`	Yes
`status_name`	`VARCHAR(100)`	Yes
`service_channel`	`VARCHAR(100)`	Yes
`fulfillment_channel`	`VARCHAR(100)`	Yes
`created_at_utc`	`TIMESTAMP`	Yes
`business_date`	`DATE`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`order_status_code`	`VARCHAR(100)`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.employees`

Canonical employee dimension for workforce, order attribution, and labor analysis.

Column	Redshift type	Nullable
`employee_id`	`BIGINT`	No
`display_name`	`VARCHAR(1024)`	Yes
`first_name`	`VARCHAR(1024)`	Yes
`last_name`	`VARCHAR(1024)`	Yes
`email_address`	`VARCHAR(1024)`	Yes
`cell_phone`	`VARCHAR(256)`	Yes
`home_location_id`	`VARCHAR(256)`	Yes
`hire_date`	`TIMESTAMP`	Yes
`termination_date`	`TIMESTAMP`	Yes
`terminated`	`BOOLEAN`	Yes
`is_salaried`	`BOOLEAN`	Yes
`is_exempt`	`BOOLEAN`	Yes
`payroll_id`	`VARCHAR(256)`	Yes
`employee_unique_id`	`VARCHAR(256)`	Yes
`snapshot_date`	`DATE`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_employee_id`	`VARCHAR(199)`	Yes
`employee_key`	`BIGINT`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`is_active`	`BOOLEAN`	Yes
`effective_from_utc`	`TIMESTAMP`	Yes
`effective_to_utc`	`TIMESTAMP`	Yes
`is_current`	`BOOLEAN`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.pos_jobs`

Canonical POS job and employee-role dimension.

Column	Redshift type	Nullable
`job_id`	`BIGINT`	No
`name`	`VARCHAR(1024)`	Yes
`job_unique_id`	`VARCHAR(256)`	Yes
`export_code`	`VARCHAR(256)`	Yes
`is_bartender`	`BOOLEAN`	Yes
`is_delivery_driver`	`BOOLEAN`	Yes
`is_delivery_dispatcher`	`BOOLEAN`	Yes
`is_hostess`	`BOOLEAN`	Yes
`training`	`BOOLEAN`	Yes
`location_type`	`VARCHAR(128)`	Yes
`snapshot_date`	`DATE`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_job_id`	`VARCHAR(199)`	Yes
`job_key`	`BIGINT`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`is_active`	`BOOLEAN`	Yes
`effective_from_utc`	`TIMESTAMP`	Yes
`effective_to_utc`	`TIMESTAMP`	Yes
`is_current`	`BOOLEAN`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.labor_shifts`

Canonical employee shifts for worked-time, overtime, pay-rate, tip, and break analysis.

Column	Redshift type	Nullable
`shift_id`	`VARCHAR(36)`	No
`employee_id`	`BIGINT`	Yes
`job_id`	`BIGINT`	Yes
`business_date`	`DATE`	Yes
`start_time`	`TIMESTAMPTZ`	Yes
`end_time`	`TIMESTAMPTZ`	Yes
`minutes_worked`	`INTEGER`	Yes
`regular_minutes_worked`	`INTEGER`	Yes
`overtime_minutes_worked`	`INTEGER`	Yes
`extended_minutes_worked`	`INTEGER`	Yes
`pay_rate`	`DECIMAL(18,4)`	Yes
`declared_tips`	`DECIMAL(18,4)`	Yes
`shift_number`	`SMALLINT`	Yes
`break_count`	`INTEGER`	Yes
`total_break_minutes`	`INTEGER`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_shift_id`	`VARCHAR(199)`	Yes
`labor_shift_key`	`BIGINT`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

`curated.pos_destinations`

Canonical order destination and service-channel dimension, including delivery behavior.

Column	Redshift type	Nullable
`destination_id`	`BIGINT`	No
`name`	`VARCHAR(1024)`	Yes
`indicator`	`VARCHAR(256)`	Yes
`description`	`VARCHAR(65535)`	Yes
`is_active`	`BOOLEAN`	Yes
`is_delivery`	`BOOLEAN`	Yes
`delivery_minutes`	`INTEGER`	Yes
`snapshot_date`	`DATE`	Yes
`source_system`	`VARCHAR(100)`	Yes
`source_destination_id`	`VARCHAR(199)`	Yes
`destination_key`	`BIGINT`	Yes
`tenant_key`	`BIGINT`	Yes
`tenant_name`	`VARCHAR(255)`	Yes
`currency_code`	`VARCHAR(10)`	Yes
`effective_from_utc`	`TIMESTAMP`	Yes
`effective_to_utc`	`TIMESTAMP`	Yes
`is_current`	`BOOLEAN`	Yes
`_etl_batch_id`	`VARCHAR(256)`	Yes
`_etl_source_type`	`VARCHAR(100)`	Yes
`_etl_ingested_at`	`TIMESTAMP`	No

ETL Audit Columns

Every curated table contains the following common fields:

Column	Purpose
`_etl_batch_id`	Identifies the ingestion batch that produced the row.
`_etl_source_type`	Identifies the source adapter or ingestion type.
`_etl_ingested_at`	Records when the row was written to the curated warehouse layer.

Repository Docs & Knowledge Base

Categories

Branches (328)

Curated Redshift Schema

Conventions

Schema Summary

Logical Relationships

curated.kitchen_orders

curated.kitchen_order_items

curated.kitchen_item_status_events

curated.customers

curated.pos_menu_items

curated.pos_order_items

curated.pos_orders

curated.employees

curated.pos_jobs

curated.labor_shifts

curated.pos_destinations