Database design

Normalized PostgreSQL schema

A relational model for users, auth sessions, restaurants, menu, cart, orders, delivery, payments, wallet, coupons, reviews, notifications, admin, and analytics.

ER diagram

erDiagram
USER ||--o{ USER_ROLE : assigned
ROLE ||--o{ USER_ROLE : grants
ROLE ||--o{ PERMISSION : contains
USER ||--o{ ADDRESS : saves
USER ||--o| WALLET : owns
USER ||--o{ RESTAURANT : owns
RESTAURANT ||--o{ RESTAURANT_BRANCH : has
RESTAURANT ||--o{ FOOD_CATEGORY : groups
FOOD_CATEGORY ||--o{ FOOD_ITEM : contains
FOOD_ITEM ||--o{ FOOD_VARIANT : offers
FOOD_ITEM ||--o{ FOOD_ADDON : supports
FOOD_ITEM ||--o| INVENTORY : tracks
USER ||--o{ CART : owns
CART ||--o{ CART_ITEM : contains
USER ||--o{ ORDER : places
RESTAURANT ||--o{ ORDER : receives
ORDER ||--o{ ORDER_ITEM : contains
ORDER ||--o| DELIVERY : ships
DRIVER ||--o{ DELIVERY : performs
ORDER ||--o| PAYMENT : paid_by
PAYMENT ||--o{ REFUND : refunds
COUPON ||--o{ COUPON_USAGE : redeemed
USER ||--o{ REVIEW : writes
REVIEW ||--o{ REVIEW_IMAGE : includes
USER ||--o{ NOTIFICATION : receives
ORDER ||--o| INVOICE : generates

Flyway baseline SQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  email VARCHAR(320) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(180) NOT NULL,
  phone VARCHAR(180),
  status VARCHAR(180) NOT NULL
);

CREATE TABLE IF NOT EXISTS role (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  name VARCHAR(180) NOT NULL,
  description VARCHAR(180)
);

CREATE TABLE IF NOT EXISTS permission (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  code VARCHAR(180) NOT NULL,
  description VARCHAR(180)
);

CREATE TABLE IF NOT EXISTS user_role (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  role_id UUID NOT NULL REFERENCES role(id)
);

CREATE TABLE IF NOT EXISTS refresh_token (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  token_hash VARCHAR(180) NOT NULL,
  device_fingerprint VARCHAR(180) NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS otp (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID REFERENCES users(id),
  purpose VARCHAR(180) NOT NULL,
  code_hash VARCHAR(180) NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS address (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID REFERENCES users(id),
  label VARCHAR(180) NOT NULL,
  line1 VARCHAR(180) NOT NULL,
  city VARCHAR(180) NOT NULL,
  postal_code VARCHAR(180) NOT NULL,
  latitude NUMERIC(9,6) NOT NULL,
  longitude NUMERIC(9,6) NOT NULL
);

CREATE TABLE IF NOT EXISTS restaurant (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  owner_id UUID NOT NULL REFERENCES users(id),
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL,
  status VARCHAR(180) NOT NULL,
  rating NUMERIC(3,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS restaurant_branch (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  address_id UUID REFERENCES address(id),
  name VARCHAR(180) NOT NULL,
  open BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE IF NOT EXISTS cuisine (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL
);

CREATE TABLE IF NOT EXISTS restaurant_cuisine (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  cuisine_id UUID NOT NULL REFERENCES cuisine(id)
);

CREATE TABLE IF NOT EXISTS restaurant_image (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  url VARCHAR(180) NOT NULL,
  type VARCHAR(180) NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS restaurant_document (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  document_type VARCHAR(180) NOT NULL,
  storage_key VARCHAR(180) NOT NULL,
  verified_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS restaurant_schedule (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  day_of_week SMALLINT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
  opens_at TIME NOT NULL,
  closes_at TIME NOT NULL
);

CREATE TABLE IF NOT EXISTS food_category (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  parent_id UUID REFERENCES food_category(id),
  name VARCHAR(180) NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS food_item (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  category_id UUID REFERENCES food_category(id),
  name VARCHAR(180) NOT NULL,
  description TEXT,
  base_price NUMERIC(12,2) NOT NULL DEFAULT 0,
  food_type VARCHAR(180) NOT NULL,
  preparation_minutes INTEGER NOT NULL DEFAULT 15,
  available BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS food_variant (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  food_item_id UUID NOT NULL REFERENCES food_item(id),
  name VARCHAR(180) NOT NULL,
  price_delta NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS food_addon (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  food_item_id UUID NOT NULL REFERENCES food_item(id),
  name VARCHAR(180) NOT NULL,
  price NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS food_image (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  food_item_id UUID NOT NULL REFERENCES food_item(id),
  url VARCHAR(180) NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  food_item_id UUID NOT NULL REFERENCES food_item(id),
  quantity_available INTEGER NOT NULL DEFAULT 0 CHECK (quantity_available >= 0),
  low_stock_threshold INTEGER NOT NULL DEFAULT 5
);

CREATE TABLE IF NOT EXISTS cart (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  coupon_id UUID REFERENCES coupon(id),
  total_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS cart_item (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  cart_id UUID NOT NULL REFERENCES cart(id),
  food_item_id UUID NOT NULL REFERENCES food_item(id),
  variant_id UUID REFERENCES food_variant(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  line_total NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id),
  order_number VARCHAR(180) NOT NULL,
  status VARCHAR(180) NOT NULL,
  subtotal NUMERIC(12,2) NOT NULL DEFAULT 0,
  grand_total NUMERIC(12,2) NOT NULL DEFAULT 0,
  scheduled_for TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS order_item (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  order_id UUID NOT NULL REFERENCES orders(id),
  food_item_id UUID REFERENCES food_item(id),
  item_name VARCHAR(180) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12,2) NOT NULL DEFAULT 0,
  line_total NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS order_status_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  order_id UUID NOT NULL REFERENCES orders(id),
  from_status VARCHAR(180),
  to_status VARCHAR(180) NOT NULL,
  changed_by_id UUID REFERENCES users(id),
  reason TEXT
);

CREATE TABLE IF NOT EXISTS delivery (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  order_id UUID NOT NULL REFERENCES orders(id),
  driver_id UUID REFERENCES driver(id),
  status VARCHAR(180) NOT NULL,
  pickup_otp_hash VARCHAR(180),
  drop_otp_hash VARCHAR(180)
);

CREATE TABLE IF NOT EXISTS driver (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  status VARCHAR(180) NOT NULL,
  current_latitude NUMERIC(9,6),
  current_longitude NUMERIC(9,6),
  total_earnings NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS vehicle (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  driver_id UUID NOT NULL REFERENCES driver(id),
  vehicle_type VARCHAR(180) NOT NULL,
  plate_number VARCHAR(180) NOT NULL,
  verified_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS payment (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  order_id UUID NOT NULL REFERENCES orders(id),
  provider VARCHAR(180) NOT NULL,
  status VARCHAR(180) NOT NULL,
  amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  provider_reference VARCHAR(180)
);

CREATE TABLE IF NOT EXISTS refund (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payment_id UUID NOT NULL REFERENCES payment(id),
  order_id UUID NOT NULL REFERENCES orders(id),
  amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  status VARCHAR(180) NOT NULL,
  reason TEXT
);

CREATE TABLE IF NOT EXISTS coupon (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  code VARCHAR(180) NOT NULL,
  discount_type VARCHAR(180) NOT NULL,
  discount_value NUMERIC(12,2) NOT NULL DEFAULT 0,
  min_order_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  max_discount_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  expires_at TIMESTAMPTZ NOT NULL,
  usage_limit INTEGER
);

CREATE TABLE IF NOT EXISTS coupon_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  coupon_id UUID NOT NULL REFERENCES coupon(id),
  user_id UUID NOT NULL REFERENCES users(id),
  order_id UUID NOT NULL REFERENCES orders(id),
  discount_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS wallet (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  balance NUMERIC(12,2) NOT NULL DEFAULT 0,
  reward_points INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS wallet_transaction (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  wallet_id UUID NOT NULL REFERENCES wallet(id),
  type VARCHAR(180) NOT NULL,
  amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  reference_id UUID,
  description TEXT
);

CREATE TABLE IF NOT EXISTS review (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  restaurant_id UUID REFERENCES restaurant(id),
  food_item_id UUID REFERENCES food_item(id),
  delivery_id UUID REFERENCES delivery(id),
  rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  reported BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE IF NOT EXISTS review_image (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  review_id UUID NOT NULL REFERENCES review(id),
  url VARCHAR(180) NOT NULL
);

CREATE TABLE IF NOT EXISTS favorite_restaurant (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  restaurant_id UUID NOT NULL REFERENCES restaurant(id)
);

CREATE TABLE IF NOT EXISTS favorite_food (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  food_item_id UUID NOT NULL REFERENCES food_item(id)
);

CREATE TABLE IF NOT EXISTS notification (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  channel VARCHAR(180) NOT NULL,
  title VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  read_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS banner (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  title VARCHAR(180) NOT NULL,
  image_url VARCHAR(180) NOT NULL,
  target_url VARCHAR(180),
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  setting_key VARCHAR(180) NOT NULL,
  setting_value TEXT NOT NULL,
  value_type VARCHAR(180) NOT NULL
);

CREATE TABLE IF NOT EXISTS audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_id UUID REFERENCES users(id),
  action VARCHAR(180) NOT NULL,
  entity_name VARCHAR(180) NOT NULL,
  entity_id UUID,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS support_ticket (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  user_id UUID NOT NULL REFERENCES users(id),
  order_id UUID REFERENCES orders(id),
  subject VARCHAR(180) NOT NULL,
  status VARCHAR(180) NOT NULL,
  priority VARCHAR(180) NOT NULL
);

CREATE TABLE IF NOT EXISTS invoice (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  order_id UUID NOT NULL REFERENCES orders(id),
  invoice_number VARCHAR(180) NOT NULL,
  pdf_url VARCHAR(180),
  tax_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  total_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS tax (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  name VARCHAR(180) NOT NULL,
  rate NUMERIC(5,2) NOT NULL CHECK (rate >= 0),
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS platform_fee (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  name VARCHAR(180) NOT NULL,
  fee_type VARCHAR(180) NOT NULL,
  amount NUMERIC(12,2) NOT NULL DEFAULT 0,
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS delivery_charge (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  area_code VARCHAR(180) NOT NULL,
  base_charge NUMERIC(12,2) NOT NULL DEFAULT 0,
  per_km_charge NUMERIC(12,2) NOT NULL DEFAULT 0,
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
CREATE INDEX IF NOT EXISTS idx_users_email_status ON users (created_at);
CREATE INDEX IF NOT EXISTS uq_role_name ON role (created_at);
CREATE INDEX IF NOT EXISTS uq_permission_code ON permission (created_at);
CREATE INDEX IF NOT EXISTS idx_user_role_user_id ON user_role (user_id);
CREATE INDEX IF NOT EXISTS idx_user_role_role_id ON user_role (role_id);
CREATE INDEX IF NOT EXISTS uq_user_role_pair ON user_role (created_at);
CREATE INDEX IF NOT EXISTS idx_refresh_token_user_id ON refresh_token (user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_user_expires ON refresh_token (created_at);
CREATE INDEX IF NOT EXISTS idx_otp_user_id ON otp (user_id);
CREATE INDEX IF NOT EXISTS idx_otp_user_purpose ON otp (created_at);
CREATE INDEX IF NOT EXISTS idx_address_user_id ON address (user_id);
CREATE INDEX IF NOT EXISTS idx_address_user_city ON address (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_owner_id ON restaurant (owner_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_owner_status ON restaurant (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_branch_restaurant_id ON restaurant_branch (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_branch_address_id ON restaurant_branch (address_id);
CREATE INDEX IF NOT EXISTS idx_branch_restaurant_open ON restaurant_branch (created_at);
CREATE INDEX IF NOT EXISTS uq_cuisine_slug ON cuisine (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_cuisine_restaurant_id ON restaurant_cuisine (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_cuisine_cuisine_id ON restaurant_cuisine (cuisine_id);
CREATE INDEX IF NOT EXISTS uq_restaurant_cuisine_pair ON restaurant_cuisine (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_image_restaurant_id ON restaurant_image (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_image_type ON restaurant_image (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_document_restaurant_id ON restaurant_document (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_document_type ON restaurant_document (created_at);
CREATE INDEX IF NOT EXISTS idx_restaurant_schedule_restaurant_id ON restaurant_schedule (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_schedule_day ON restaurant_schedule (created_at);
CREATE INDEX IF NOT EXISTS idx_food_category_restaurant_id ON food_category (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_food_category_parent_id ON food_category (parent_id);
CREATE INDEX IF NOT EXISTS idx_category_restaurant_parent ON food_category (created_at);
CREATE INDEX IF NOT EXISTS idx_food_item_restaurant_id ON food_item (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_food_item_category_id ON food_item (category_id);
CREATE INDEX IF NOT EXISTS idx_food_restaurant_available ON food_item (created_at);
CREATE INDEX IF NOT EXISTS idx_food_variant_food_item_id ON food_variant (food_item_id);
CREATE INDEX IF NOT EXISTS idx_food_addon_food_item_id ON food_addon (food_item_id);
CREATE INDEX IF NOT EXISTS idx_food_image_food_item_id ON food_image (food_item_id);
CREATE INDEX IF NOT EXISTS idx_inventory_food_item_id ON inventory (food_item_id);
CREATE INDEX IF NOT EXISTS uq_inventory_food_item ON inventory (created_at);
CREATE INDEX IF NOT EXISTS idx_cart_user_id ON cart (user_id);
CREATE INDEX IF NOT EXISTS idx_cart_restaurant_id ON cart (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_cart_coupon_id ON cart (coupon_id);
CREATE INDEX IF NOT EXISTS idx_cart_user_restaurant ON cart (created_at);
CREATE INDEX IF NOT EXISTS idx_cart_item_cart_id ON cart_item (cart_id);
CREATE INDEX IF NOT EXISTS idx_cart_item_food_item_id ON cart_item (food_item_id);
CREATE INDEX IF NOT EXISTS idx_cart_item_variant_id ON cart_item (variant_id);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders (user_id);
CREATE INDEX IF NOT EXISTS idx_orders_restaurant_id ON orders (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_order_user_created ON orders (created_at);
CREATE INDEX IF NOT EXISTS idx_order_restaurant_status ON orders (created_at);
CREATE INDEX IF NOT EXISTS idx_order_item_order_id ON order_item (order_id);
CREATE INDEX IF NOT EXISTS idx_order_item_food_item_id ON order_item (food_item_id);
CREATE INDEX IF NOT EXISTS idx_order_status_history_order_id ON order_status_history (order_id);
CREATE INDEX IF NOT EXISTS idx_order_status_history_changed_by_id ON order_status_history (changed_by_id);
CREATE INDEX IF NOT EXISTS idx_delivery_order_id ON delivery (order_id);
CREATE INDEX IF NOT EXISTS idx_delivery_driver_id ON delivery (driver_id);
CREATE INDEX IF NOT EXISTS idx_delivery_driver_status ON delivery (created_at);
CREATE INDEX IF NOT EXISTS idx_driver_user_id ON driver (user_id);
CREATE INDEX IF NOT EXISTS idx_driver_status_location ON driver (created_at);
CREATE INDEX IF NOT EXISTS idx_vehicle_driver_id ON vehicle (driver_id);
CREATE INDEX IF NOT EXISTS idx_payment_order_id ON payment (order_id);
CREATE INDEX IF NOT EXISTS idx_payment_order_status ON payment (created_at);
CREATE INDEX IF NOT EXISTS idx_refund_payment_id ON refund (payment_id);
CREATE INDEX IF NOT EXISTS idx_refund_order_id ON refund (order_id);
CREATE INDEX IF NOT EXISTS idx_coupon_code_expires ON coupon (created_at);
CREATE INDEX IF NOT EXISTS idx_coupon_usage_coupon_id ON coupon_usage (coupon_id);
CREATE INDEX IF NOT EXISTS idx_coupon_usage_user_id ON coupon_usage (user_id);
CREATE INDEX IF NOT EXISTS idx_coupon_usage_order_id ON coupon_usage (order_id);
CREATE INDEX IF NOT EXISTS idx_wallet_user_id ON wallet (user_id);
CREATE INDEX IF NOT EXISTS idx_wallet_transaction_wallet_id ON wallet_transaction (wallet_id);
CREATE INDEX IF NOT EXISTS idx_review_user_id ON review (user_id);
CREATE INDEX IF NOT EXISTS idx_review_restaurant_id ON review (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_review_food_item_id ON review (food_item_id);
CREATE INDEX IF NOT EXISTS idx_review_delivery_id ON review (delivery_id);
CREATE INDEX IF NOT EXISTS idx_review_image_review_id ON review_image (review_id);
CREATE INDEX IF NOT EXISTS idx_favorite_restaurant_user_id ON favorite_restaurant (user_id);
CREATE INDEX IF NOT EXISTS idx_favorite_restaurant_restaurant_id ON favorite_restaurant (restaurant_id);
CREATE INDEX IF NOT EXISTS uq_favorite_restaurant_pair ON favorite_restaurant (created_at);
CREATE INDEX IF NOT EXISTS idx_favorite_food_user_id ON favorite_food (user_id);
CREATE INDEX IF NOT EXISTS idx_favorite_food_food_item_id ON favorite_food (food_item_id);
CREATE INDEX IF NOT EXISTS uq_favorite_food_pair ON favorite_food (created_at);
CREATE INDEX IF NOT EXISTS idx_notification_user_id ON notification (user_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_actor_id ON audit_log (actor_id);
CREATE INDEX IF NOT EXISTS idx_support_ticket_user_id ON support_ticket (user_id);
CREATE INDEX IF NOT EXISTS idx_support_ticket_order_id ON support_ticket (order_id);
CREATE INDEX IF NOT EXISTS idx_invoice_order_id ON invoice (order_id);

Entity catalog

Every listed entity is included in schema and code generation.

Filter entities

Entity	Table	Fields	Indexes	Description
User	users	8	1	Platform principal for customers, owners, drivers, and admins.
Role	role	5	1	Named authorization role such as CUSTOMER, OWNER, DRIVER, ADMIN.
Permission	permission	5	1	Fine-grained permission for method-level security.
UserRole	user_role	5	1	Join entity assigning roles to users.
RefreshToken	refresh_token	8	1	Rotatable refresh-token session for multi-device login.
OTP	otp	7	1	One-time verification challenge.
Address	address	10	1	Saved address and geospatial point.
Restaurant	restaurant	8	1	Merchant account and storefront profile.
RestaurantBranch	restaurant_branch	7	1	Physical restaurant branch.
Cuisine	cuisine	5	1	Cuisine taxonomy.
RestaurantCuisine	restaurant_cuisine	5	1	Join entity between restaurants and cuisines.
RestaurantImage	restaurant_image	7	1	Object storage image metadata.
RestaurantDocument	restaurant_document	7	1	Verification and compliance document metadata.
RestaurantSchedule	restaurant_schedule	7	1	Opening hours and holiday override.
FoodCategory	food_category	7	1	Restaurant-scoped category hierarchy.
FoodItem	food_item	11	1	Sellable menu item.
FoodVariant	food_variant	6	0	Size or option price override.
FoodAddon	food_addon	6	0	Optional paid add-on.
FoodImage	food_image	6	0	Food item image metadata.
Inventory	inventory	6	1	Stock quantity and thresholds.
Cart	cart	7	1	Customer cart scoped to a restaurant.
CartItem	cart_item	8	0	Cart line item with variant and subtotal.
Order	orders	10	2	Customer purchase order aggregate.
OrderItem	order_item	9	0	Immutable purchased item snapshot.
OrderStatusHistory	order_status_history	8	0	Order transition audit trail.
Delivery	delivery	8	1	Delivery assignment and tracking state.
Driver	driver	8	1	Delivery partner onboarding and earnings profile.
Vehicle	vehicle	7	0	Driver vehicle details.
Payment	payment	8	1	Payment attempt and provider verification state.
Refund	refund	8	0	Full or partial refund transaction.
Coupon	coupon	10	1	Promotion with scope, expiry, and usage limits.
CouponUsage	coupon_usage	7	0	Coupon redemption audit.
Wallet	wallet	6	0	User balance and reward summary.
WalletTransaction	wallet_transaction	8	0	Immutable wallet ledger row.
Review	review	10	0	Restaurant, food, and delivery review.
ReviewImage	review_image	5	0	Image attached to a review.
FavoriteRestaurant	favorite_restaurant	5	1	Customer saved restaurant.
FavoriteFood	favorite_food	5	1	Customer saved food item.
Notification	notification	8	0	Email, SMS, push, and in-app notification.
Banner	banner	7	0	Admin managed promotional banner.
Settings	settings	6	0	Platform key-value settings.
AuditLog	audit_log	8	0	Immutable admin and security audit event.
SupportTicket	support_ticket	8	0	Support workflow for users and orders.
Invoice	invoice	8	0	Generated invoice metadata and totals.
Tax	tax	6	0	Tax configuration.
PlatformFee	platform_fee	7	0	Platform fee configuration.
DeliveryCharge	delivery_charge	7	0	Distance and area based delivery charge rule.