Foodly schema

All tables live in the foodly schema, so write foodly.customers in your queries.

foodly.customers

One row per customer.

• customer_id (INT) — PK
• first_name (VARCHAR(50))
• last_name (VARCHAR(50))
• email (VARCHAR(120))
• phone (VARCHAR(15))
• signup_date (DATE)
• city (VARCHAR(40))
• marketing_opt_in (BOOLEAN)

foodly.customer_addresses

Saved delivery addresses.

• address_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• address_line1 (VARCHAR(120))
• address_line2 (VARCHAR(120)) — nullable
• city (VARCHAR(40))
• state (VARCHAR(40))
• postal_code (VARCHAR(10))
• is_default (BOOLEAN)
• created_at (TIMESTAMP)

foodly.restaurants

Partner restaurants.

• restaurant_id (INT) — PK
• name (VARCHAR(80))
• city (VARCHAR(40))
• cuisine_type (VARCHAR(30))
• rating (DECIMAL(2,1)) — displayed rating on the app, 1 to 5
• onboarded_at (TIMESTAMP)
• is_active (BOOLEAN)

foodly.menu_items

Dishes on each restaurant's menu.

• menu_item_id (INT) — PK
• restaurant_id (INT) — FK → restaurants.restaurant_id
• item_name (VARCHAR(80))
• category (VARCHAR(30)) — 'starter', 'main', 'dessert', 'beverage'
• price (DECIMAL(8,2)) — current menu price
• is_available (BOOLEAN)
• created_at (TIMESTAMP)

foodly.orders

One row per order.

• order_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• restaurant_id (INT) — FK → restaurants.restaurant_id
• order_time (TIMESTAMP)
• status (VARCHAR(20)) — 'placed', 'accepted', 'preparing', 'out_for_delivery', 'delivered', 'cancelled'
• total_amount (DECIMAL(10,2)) — charged to the customer: items + delivery_fee - discount_amount
• delivery_fee (DECIMAL(8,2))
• discount_amount (DECIMAL(8,2)) — 0 when none
• city (VARCHAR(40)) — city the order was placed in

foodly.order_items

Dishes in each order.

• order_item_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• menu_item_id (INT) — FK → menu_items.menu_item_id
• quantity (INT)
• item_price (DECIMAL(8,2)) — unit price at the time of the order

foodly.delivery_partners

Riders who deliver orders.

• delivery_partner_id (INT) — PK
• full_name (VARCHAR(80))
• phone (VARCHAR(15))
• city (VARCHAR(40))
• rating (DECIMAL(2,1))
• is_active (BOOLEAN)
• onboarded_at (TIMESTAMP)

foodly.deliveries

Delivery assignments. An order can have a cancelled assignment before the one that delivered it.

• delivery_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• delivery_partner_id (INT) — FK → delivery_partners.delivery_partner_id
• pickup_time (TIMESTAMP) — nullable
• dropoff_time (TIMESTAMP) — nullable
• distance_km (DECIMAL(5,1))
• status (VARCHAR(25)) — 'assigned', 'picked_up', 'delivered', 'cancelled_by_partner'
• cancelled_reason (VARCHAR(80)) — nullable

foodly.payments

Payment attempts for orders.

• payment_id (INT) — PK
• order_id (INT) — FK → orders.order_id
• amount (DECIMAL(10,2))
• payment_method (VARCHAR(20)) — 'card', 'upi', 'wallet', 'cod'
• payment_status (VARCHAR(20)) — 'success', 'failed', 'refunded'
• created_at (TIMESTAMP)

foodly.restaurant_reviews

Customer reviews of restaurants.

• review_id (INT) — PK
• customer_id (INT) — FK → customers.customer_id
• restaurant_id (INT) — FK → restaurants.restaurant_id
• rating (INT) — 1 to 5
• review_text (TEXT) — nullable
• created_at (TIMESTAMP)
• is_verified_purchase (BOOLEAN)