Database Architecture

Staccato uses Supabase (PostgreSQL) with 14 tables, Row Level Security on all tables, and Realtime enabled for the order queue.

Table Overview

Table	Purpose	RLS
`profiles`	User profiles (extends `auth.users`)	Own data only
`locations`	Store locations	Public read (active)
`menu_categories`	Coffee, Tea, Food, etc.	Public read (active)
`menu_items`	Individual menu items	Public read (available)
`modifier_groups`	Milk, Size, Extras	Public read
`modifiers`	Individual modifiers	Public read (available)
`item_modifier_groups`	Links items to modifier groups	Public read
`orders`	Order headers	Own + staff access
`order_items`	Line items in orders	Own + staff access
`order_item_modifiers`	Modifiers on order items	Own + staff access
`loyalty_accounts`	Stars balance, tier	Own data only
`star_transactions`	Star earned/redeemed history	Own data only
`rewards`	Redeemable reward catalog	Public read (active)
`reward_redemptions`	User redemption records	Own data only

Entity Relationships

profiles ──────────────── loyalty_accounts ──── star_transactions
    │                           │
    │                           │
    ├── orders ─────────────────┤
    │     │                     │
    │     ├── order_items       │
    │     │     │               │
    │     │     └── order_item_modifiers
    │     │
    │     └── reward_redemptions ──── rewards
    │
    │
menu_categories ──── menu_items
                         │
                    item_modifier_groups
                         │
                    modifier_groups ──── modifiers

Key Schema Details

Orders Table

The orders table is the most complex, supporting the full lifecycle:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID REFERENCES locations(id),
    user_id UUID REFERENCES auth.users(id),
    order_number TEXT NOT NULL,         -- e.g., "SC-A042"
    status TEXT DEFAULT 'pending',      -- pending/in_progress/ready/completed/cancelled
    assigned_to UUID REFERENCES auth.users(id),  -- staff who claimed
    claimed_at TIMESTAMPTZ,
    subtotal INT NOT NULL,              -- in cents
    tax INT NOT NULL,
    discount INT DEFAULT 0,
    tip INT DEFAULT 0,
    total INT NOT NULL,
    stars_earned INT DEFAULT 0,
    payment_method TEXT,
    square_payment_id TEXT,
    square_order_id TEXT,
    idempotency_key TEXT UNIQUE,        -- prevents duplicate orders
    version INT DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Loyalty System

Three tiers based on lifetime stars:

Tier	Required Stars	Color
Green	0	`#22C55E`
Gold	300	`#EAB308`
Platinum	600	`#94A3B8`

Stars earned: 2 per $1 spent (calculated from item price).

Row Level Security

All tables have RLS enabled. Key policies:

Menu data: Public read for active/available items
Orders: Users see own orders; staff see all orders (via staff access migration)
Loyalty: Users see only their own account and transactions
Profiles: Users can read/update only their own profile

Indexes

CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_menu_items_featured ON menu_items(is_featured) WHERE is_featured = true;
CREATE INDEX idx_modifiers_group ON modifiers(group_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_loyalty_user ON loyalty_accounts(user_id);
CREATE INDEX idx_star_transactions_account ON star_transactions(loyalty_account_id);

Realtime

Enabled on orders and order_items tables for the staff order queue. Changes push instantly via WebSocket to connected clients.

Auto-Trigger

A trigger automatically creates a profiles row when a new user signs up:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO profiles (id, email, full_name)
    VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'full_name');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

iOS App Edge Functions