Spaces:
Sleeping
Sleeping
File size: 7,346 Bytes
d0d4978 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | -- TRAY POS Schema DDL v2 (dbt-Verified Column Names + Sample Values)
-- Source: INFORMATION_SCHEMA + dbt int_tray_items_clean, int_tray_checks_clean, fact_tray_check_day
-- CRITICAL: ITEMS has NO VOIDED column. Void detection: ACTION LIKE '%VOID%'
-- CRITICAL: Use CHECKID not CHECK_ID, EMPLOYEEID not EMPLOYEE_ID
-- CRITICAL: Use SITE_NAME not LOCATIONNAME, NAME not MENUITEMNAME/TITLE
CREATE TABLE FLORAOS.TRAY.TRAY_SITES (
ID FLOAT, -- Primary key
NAME VARCHAR, -- Site name (identical to SITE_NAME)
SITE_NAME VARCHAR, -- example: ['IHOP #1425', 'IHOP #1427', 'IHOP #1409']
SITE_ID FLOAT, -- Alternate site identifier
STORETIMEZONE VARCHAR, -- example: ['America/Chicago', 'America/New_York']
ADDRESS VARCHAR, -- Physical address
STORENUMBER VARCHAR, -- Store number
MAXREGULARDAYHRS FLOAT, -- Max regular hours per day before OT
MAXREGULARWEEKHRS FLOAT, -- Max regular hours per week before OT
OTRATEMULTIPLIER FLOAT, -- Overtime rate multiplier (e.g. 1.5)
BUSINESSSTARTHOUR VARCHAR -- Business day start hour
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_CHECKS (
ID FLOAT, -- Primary key (referenced as CHECKID from other tables)
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
OPENTIME VARCHAR, -- example: ['2026-04-13 11:23:45 UTC']
CLOSETIME VARCHAR, -- example: ['2026-04-13 12:01:33 UTC', NULL (0.14%)]
EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID (NOT EMPLOYEE_ID)
VOIDED BOOLEAN, -- Check-level void flag (ITEMS has NO VOIDED column)
STATUS VARCHAR, -- Check status
POSTRAFFICCOUNT FLOAT, -- Guest/party count
CLIENTPLATFORM VARCHAR, -- e.g. 'thirdparty' for delivery orders
REVENUECENTERID FLOAT -- FK → revenue centers
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_ITEMS (
ID FLOAT, -- Primary key (action row ID)
CHECKID FLOAT, -- FK → TRAY_CHECKS.ID (NOT CHECK_ID)
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
ACTION VARCHAR, -- example: ['SALE', 'VOID', 'DISCOUNT_APPLIED', 'COMP_APPLIED', 'TRANSFERRED_TO']
PRICE FLOAT, -- example: [8.99, 12.50, 3.25]
TAX FLOAT, -- example: [0.72, 1.00, 0.26]
QUANTITY FLOAT, -- Quantity ordered
ISMODIFIER BOOLEAN, -- TRUE for modifiers (exclude from revenue)
ITEMTYPE VARCHAR, -- example: ['', 'GIFTCARD', 'SERVICECHARGE']
ITEMPLU VARCHAR, -- PLU code — links to TRAY_PRODUCTS.PLU
ACTIONTIME VARCHAR, -- UTC text timestamp of the action
EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
DISCOUNTID VARCHAR, -- Discount reference (NOT the FK to discount_types)
COMBOPARENTID FLOAT -- Combo parent item ID (non-zero = combo component)
-- PRIMARY KEY: ID
-- WARNING: NO VOIDED column. Void detection: ACTION LIKE '%VOID%' or ACTION IN ('VOID','VOIDED','ITEM_VOID')
);
CREATE TABLE FLORAOS.TRAY.TRAY_PAYMENTS (
ID FLOAT, -- Primary key
CHECKID FLOAT, -- FK → TRAY_CHECKS.ID (NOT CHECK_ID)
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
TENDERTYPE VARCHAR, -- example: ['Cash', 'Visa', 'Mastercard', 'DoorDash']
TIPAMOUNT FLOAT, -- example: [0.00, 3.50, 5.00]
AMOUNT FLOAT, -- Payment amount
CARDTYPE VARCHAR, -- Card brand detail
EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
ADDEDGRATUITY FLOAT -- Auto-gratuity amount
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_EMPLOYEES (
ID FLOAT, -- Primary key
FIRSTNAME VARCHAR, -- First name
LASTNAME VARCHAR, -- Last name
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
STATUS VARCHAR, -- Active/Inactive
HIREDATE VARCHAR, -- Hire date text
ISTIPABLE BOOLEAN -- Eligible for tips
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_PRODUCTS (
ID FLOAT, -- Primary key
NAME VARCHAR, -- Product name (NOT MENUITEMNAME)
PLU VARCHAR, -- PLU code — links to TRAY_ITEMS.ITEMPLU
CATEGORYNAME VARCHAR, -- example: ['Pancakes', 'Breakfast Combos', 'Beverages']
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
TYPE VARCHAR, -- Product type
STATUS VARCHAR -- Active/Inactive
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_DISCOUNTS (
ID FLOAT, -- Primary key
CHECKID FLOAT, -- FK → TRAY_CHECKS.ID
DISCOUNTEID FLOAT, -- FK → TRAY_DISCOUNT_TYPES.EID (NOT DISCOUNTID or DISCOUNTTYPEID)
AMOUNT FLOAT, -- Discount dollar amount
EMPLOYEEID FLOAT, -- Who applied the discount
ITEMID FLOAT, -- Item the discount was applied to
DATE VARCHAR -- Date text
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_DISCOUNT_TYPES (
EID FLOAT, -- Primary key (40x Airbyte duplication — MUST dedupe)
ID FLOAT, -- Alternate ID
NAME VARCHAR, -- example: ['Employee Meal', 'Manager Comp', 'Senior Discount']
STATUS VARCHAR, -- Active/Inactive
CHECKLEVEL BOOLEAN, -- Check-level vs item-level discount
DISCOUNTMAINCATEGORY VARCHAR -- Main discount category
-- PRIMARY KEY: EID
-- WARNING: 40x Airbyte duplication. MUST dedupe: ROW_NUMBER() OVER (PARTITION BY EID ORDER BY _AIRBYTE_EXTRACTED_AT DESC)
);
CREATE TABLE FLORAOS.TRAY.TRAY_TIME_ENTRIES (
ID FLOAT, -- Primary key
EMPLOYEEID FLOAT, -- FK → TRAY_EMPLOYEES.ID
SITE_ID FLOAT, -- FK → TRAY_SITES.ID
TIMEIN VARCHAR, -- Clock-in UTC text
TIMEOUT VARCHAR, -- Clock-out UTC text
BASERATE FLOAT, -- example: [10.00, 12.50, 15.00]
ACTUALRATE FLOAT, -- Actual rate paid
JOBID FLOAT, -- FK → TRAY_JOBS
NETSALES FLOAT, -- Net sales during shift
TIPS FLOAT -- Tips earned during shift
-- PRIMARY KEY: ID
);
CREATE TABLE FLORAOS.TRAY.TRAY_JOBS (
EID FLOAT, -- Primary key (17x Airbyte duplication — MUST dedupe)
ID FLOAT, -- Alternate ID
NAME VARCHAR, -- example: ['Server', 'Cook', 'Host', 'Manager']
STATUS VARCHAR, -- Active/Inactive
SITE_ID FLOAT -- FK → TRAY_SITES.ID
-- PRIMARY KEY: EID
-- WARNING: 17x Airbyte duplication. MUST dedupe: ROW_NUMBER() OVER (PARTITION BY EID ORDER BY _AIRBYTE_EXTRACTED_AT DESC)
);
-- FOREIGN KEY GRAPH (dbt-verified join paths):
-- TRAY_ITEMS.CHECKID → TRAY_CHECKS.ID
-- TRAY_CHECKS.SITE_ID → TRAY_SITES.ID
-- TRAY_CHECKS.EMPLOYEEID → TRAY_EMPLOYEES.ID
-- TRAY_ITEMS.ITEMPLU = TRAY_PRODUCTS.PLU (+ SITE_ID)
-- TRAY_PAYMENTS.CHECKID → TRAY_CHECKS.ID
-- TRAY_DISCOUNTS.CHECKID → TRAY_CHECKS.ID
-- TRAY_DISCOUNTS.DISCOUNTEID → TRAY_DISCOUNT_TYPES.EID
-- TRAY_TIME_ENTRIES.EMPLOYEEID → TRAY_EMPLOYEES.ID
-- TRAY_TIME_ENTRIES.SITE_ID → TRAY_SITES.ID
-- TRAY_JOBS.SITE_ID → TRAY_SITES.ID
-- REVENUE FORMULA (from dbt int_tray_check_day_item_metrics):
-- SUM(PRICE) WHERE ACTION='SALE' AND ISMODIFIER=FALSE
-- AND ITEMTYPE NOT IN ('GIFTCARD','SERVICECHARGE')
-- AND product not a donation (CATEGORYNAME not in ('donation','donations'))
-- Simplified: SUM(PRICE + TAX) WHERE ACTION='SALE' AND ISMODIFIER=FALSE
-- BUSINESS DATE (from dbt int_tray_checks_clean):
-- DATE(DATEADD('hour', -4, CONVERT_TIMEZONE('UTC',
-- COALESCE(NULLIF(s.STORETIMEZONE,''), 'America/Chicago'),
-- TRY_TO_TIMESTAMP(COALESCE(c.CLOSETIME, c.OPENTIME), 'YYYY-MM-DD HH24:MI:SS UTC'))))
-- ANTI-PATTERNS (NEVER DO):
-- NEVER join TRAY_ITEMS directly to TRAY_PAYMENTS on CHECKID (N×M fanout)
-- NEVER use PAYMENTTYPE (correct: TENDERTYPE)
-- NEVER use CHECK_ID on items/payments (correct: CHECKID)
-- NEVER use EMPLOYEE_ID (correct: EMPLOYEEID)
-- NEVER use LOCATIONNAME (correct: SITE_NAME)
-- NEVER use i.VOIDED (column doesn't exist on ITEMS)
-- NEVER use HOURLYRATE (correct: BASERATE)
-- NEVER use TITLE on jobs (correct: NAME)
-- NEVER use MENUITEMNAME on products (correct: NAME) |