Database Schema

Complete reference for all 14 Supabase tables.

profiles

Extends auth.users with app-specific data.

Column	Type	Constraints	Description
`id`	`UUID`	PK, FK → `auth.users(id)` ON DELETE CASCADE	User ID
`email`	`TEXT`	NOT NULL	Email address
`full_name`	`TEXT`	—	Display name
`phone`	`TEXT`	—	Phone number
`avatar_url`	`TEXT`	—	Profile image URL
`birthday`	`TEXT`	—	Birthday
`role`	`TEXT`	DEFAULT `'customer'`, CHECK `(customer\|employee\|admin)`	User role
`created_at`	`TIMESTAMPTZ`	DEFAULT `NOW()`	Created
`updated_at`	`TIMESTAMPTZ`	DEFAULT `NOW()`	Updated

Column	Type	Constraints	Description
`id`	`UUID`	PK, DEFAULT `gen_random_uuid()`	Location ID
`name`	`TEXT`	NOT NULL	Location name
`address`	`TEXT`	—	Street address
`phone`	`TEXT`	—	Phone number
`email`	`TEXT`	—	Contact email
`is_active`	`BOOLEAN`	DEFAULT `true`	Active flag
`hours`	`JSONB`	—	Operating hours
`created_at`	`TIMESTAMPTZ`	DEFAULT `NOW()`	Created
`updated_at`	`TIMESTAMPTZ`	DEFAULT `NOW()`	Updated

Column	Type	Constraints	Description
`id`	`UUID`	PK	Category ID
`name`	`TEXT`	NOT NULL	Display name
`slug`	`TEXT`	NOT NULL, UNIQUE	URL-friendly name
`description`	`TEXT`	—	Description
`image_url`	`TEXT`	—	Category image
`sort_order`	`INT`	DEFAULT `0`	Display order
`is_active`	`BOOLEAN`	DEFAULT `true`	Active flag

Column	Type	Constraints	Description
`id`	`UUID`	PK	Item ID
`category_id`	`UUID`	FK → `menu_categories(id)` CASCADE	Category
`name`	`TEXT`	NOT NULL	Item name
`description`	`TEXT`	—	Description
`base_price`	`INT`	NOT NULL	Price in cents
`image_url`	`TEXT`	—	Item image
`stars_earned`	`INT`	DEFAULT `5`	Stars per purchase
`is_featured`	`BOOLEAN`	DEFAULT `false`	Featured on home
`is_available`	`BOOLEAN`	DEFAULT `true`	In stock
`sort_order`	`INT`	DEFAULT `0`	Display order
`nutrition_info`	`JSONB`	—	Nutrition data

Column	Type	Constraints	Description
`id`	`UUID`	PK	Group ID
`name`	`TEXT`	NOT NULL	Group name (e.g., “Milk”)
`slug`	`TEXT`	NOT NULL, UNIQUE	URL-friendly name
`description`	`TEXT`	—	Description
`is_required`	`BOOLEAN`	DEFAULT `false`	Must select
`min_selections`	`INT`	DEFAULT `0`	Minimum choices
`max_selections`	`INT`	DEFAULT `1`	Maximum choices
`sort_order`	`INT`	DEFAULT `0`	Display order

Column	Type	Constraints	Description
`id`	`UUID`	PK	Modifier ID
`group_id`	`UUID`	FK → `modifier_groups(id)` CASCADE	Parent group
`name`	`TEXT`	NOT NULL	Modifier name
`price_adjustment`	`INT`	DEFAULT `0`	Price change (cents)
`is_default`	`BOOLEAN`	DEFAULT `false`	Pre-selected
`is_available`	`BOOLEAN`	DEFAULT `true`	In stock
`sort_order`	`INT`	DEFAULT `0`	Display order

Links menu items to their applicable modifier groups.

Column	Type	Constraints
`menu_item_id`	`UUID`	FK → `menu_items(id)` CASCADE
`modifier_group_id`	`UUID`	FK → `modifier_groups(id)` CASCADE

PK: (menu_item_id, modifier_group_id)

Column	Type	Constraints	Description
`id`	`UUID`	PK	Order ID
`location_id`	`UUID`	FK → `locations(id)`	Location
`user_id`	`UUID`	FK → `auth.users(id)`	Customer
`order_number`	`TEXT`	NOT NULL	Display number
`status`	`TEXT`	DEFAULT `'pending'`, CHECK	Order status
`assigned_to`	`UUID`	FK → `auth.users(id)`	Claimed by
`claimed_at`	`TIMESTAMPTZ`	—	Claim time
`subtotal`	`INT`	NOT NULL	Cents
`tax`	`INT`	NOT NULL	Cents
`discount`	`INT`	DEFAULT `0`	Cents
`tip`	`INT`	DEFAULT `0`	Cents
`total`	`INT`	NOT NULL	Cents
`stars_earned`	`INT`	DEFAULT `0`	Stars awarded
`reward_redemption_id`	`UUID`	—	Applied reward
`payment_method`	`TEXT`	—	Payment type
`square_payment_id`	`TEXT`	—	Square ref
`square_order_id`	`TEXT`	—	Square ref
`notes`	`TEXT`	—	Order notes
`pickup_time`	`TIMESTAMPTZ`	—	Scheduled pickup
`completed_at`	`TIMESTAMPTZ`	—	Completion time
`version`	`INT`	DEFAULT `1`	Optimistic lock
`idempotency_key`	`TEXT`	UNIQUE	Duplicate prevention

Column	Type	Constraints
`id`	`UUID`	PK
`user_id`	`UUID`	UNIQUE, FK → `auth.users(id)` CASCADE
`stars_balance`	`INT`	DEFAULT `0`
`lifetime_stars`	`INT`	DEFAULT `0`
`tier`	`TEXT`	DEFAULT `'green'`, CHECK `(green\|gold\|platinum)`
`tier_expiry`	`TIMESTAMPTZ`	—

Column	Type	Constraints
`id`	`UUID`	PK
`loyalty_account_id`	`UUID`	FK → `loyalty_accounts(id)` CASCADE
`order_id`	`UUID`	FK → `orders(id)`
`amount`	`INT`	NOT NULL (negative for redemptions)
`type`	`TEXT`	NOT NULL, CHECK `(earned\|redeemed\|expired\|bonus\|adjustment)`
`description`	`TEXT`	—

Column	Type	Constraints
`id`	`UUID`	PK
`name`	`TEXT`	NOT NULL
`description`	`TEXT`	—
`stars_required`	`INT`	NOT NULL
`reward_type`	`TEXT`	NOT NULL, CHECK `(free_drink\|free_food\|discount_percent\|discount_amount)`
`reward_value`	`JSONB`	—
`min_tier`	`TEXT`	DEFAULT `'green'`, CHECK
`is_active`	`BOOLEAN`	DEFAULT `true`
`sort_order`	`INT`	DEFAULT `0`

Column	Type	Constraints
`id`	`UUID`	PK
`reward_id`	`UUID`	FK → `rewards(id)`
`user_id`	`UUID`	FK → `auth.users(id)`
`order_id`	`UUID`	FK → `orders(id)`
`stars_spent`	`INT`	NOT NULL
`status`	`TEXT`	DEFAULT `'pending'`, CHECK `(pending\|applied\|cancelled)`

Table	SELECT	INSERT	UPDATE
profiles	Own only	Auto (trigger)	Own only
locations	Active only	—	—
menu_categories	Active only	—	—
menu_items	Available only	Admin	Admin
modifier_groups	All	—	—
modifiers	Available only	Admin	Admin
orders	Own + staff	Own	Staff
order_items	Own + staff	Own	—
loyalty_accounts	Own	Own	Own
star_transactions	Own	Own	—
rewards	Active only	—	—
reward_redemptions	Own	Own	—