Skip to main content

Database Schema

Both the local on-premise MongoDB and the Cloud MongoDB Atlas share a nearly identical schema to facilitate seamless synchronization. The primary difference is that the Cloud DB partitions data by tenantId and outletId.

Core Collections​

1. orders​

Stores the canonical state of a customer transaction.

  • _id: String (UUID)
  • tenantId: String
  • outletId: String
  • order_no: Number (Auto-incrementing daily sequence)
  • order_type: Enum (DINE_IN, TAKEAWAY, DELIVERY, AGGREGATOR)
  • status: Enum (NEW, ACCEPTED, FOOD_READY, DISPATCHED, COMPLETED, CANCELLED, PAID, CLOSED)
  • items: Array of embedded OrderItem objects
  • subtotal, tax_total, discount_total, grand_total: Numbers
  • payment_status: Enum (PENDING, PARTIAL, PAID)
  • created_at, updated_at: ISO Dates

2. menu_items​

Stores the catalog of sellable items.

  • _id: String (UUID)
  • name: String
  • category_id: String (Ref to categories)
  • price: Number
  • is_active: Boolean
  • food_type: Enum (VEG, NON_VEG, EGG)
  • modifiers: Array of embedded ModifierGroup objects

3. inventory_items​

Tracks raw ingredients and physical stock.

  • _id: String (UUID)
  • name: String
  • unit: String (e.g., kg, liters, pcs)
  • current_stock: Number
  • reorder_level: Number
  • cost_per_unit: Number

4. recipes (BOM - Bill of Materials)​

Maps menu_items to inventory_items for auto-deduction.

  • menu_item_id: String (Ref to menu_items)
  • ingredients: Array of Objects
    • inventory_item_id: String (Ref to inventory_items)
    • quantity: Number (Amount to deduct per sale)

5. expenses / petty_cash​

Tracks day-to-day operational costs.

  • _id: String (UUID)
  • amount: Number
  • category: String (e.g., Groceries, Travel, Maintenance)
  • description: String
  • recorded_by: String (Ref to users)
  • date: ISO Date

Indexes​

For performance, ensure the following compound indexes are present, especially on the Cloud DB:

  • orders: { tenantId: 1, outletId: 1, created_at: -1 }
  • orders: { tenantId: 1, outletId: 1, order_type: 1 }
  • menu_items: { tenantId: 1, outletId: 1, category_id: 1 }