Skip to main content

Orders

CaterCow Marketplace: Order & Fulfillment Data Models

This document outlines the primary data models that support the complete lifecycle of an order on the CaterCow marketplace. The system is designed to handle everything from initial order placement and group polling to delivery and post-order reviews.

A key architectural principle is the use of point-in-time references. When an order is created, many associated objects (like menu items, delivery rules, and pricing) are copied and stored with the order. This ensures that historical orders are not affected by subsequent changes to menus or delivery policies, providing a stable and accurate record for accounting, reporting, and customer service.

For group ordering and polls, see Group Orders.

Core Models

Order

The orders table is the central hub for every transaction. It holds all the primary information about a catering request and links to all other related data.

  • Description: Represents a single customer order from placement to completion. An order can be for a "full menu" from a caterer or, more commonly, for a specific package.
  • Key Columns from structure.sql:
    • id: Unique identifier for the order.
    • user_id: The user who placed the order.
    • team_id: The team associated with the order, if applicable.
    • brand_id: The catering brand fulfilling the order.
    • kitchen_id: The specific kitchen location that will prepare the food.
    • package_id: Foreign key to the packages table if the order is for a package. This is a critical field for calculating contents and price. If NULL, the order is a "full menu" order. For more on packages, see Packages.
    • headcount: The number of people the order is intended to serve. For package orders, this is a crucial component for validating that enough options have been selected. For full menu orders, it's informational.
    • address_id: A reference to the delivery address.
    • start_at_utc: The scheduled date and time for the delivery in UTC.
    • submitted_at: Timestamp for when the user officially submitted the order.
    • status: The current state of the order (e.g., accepted, declined, canceled).
    • cancellation_policy: The cancellation policy (flexible, moderate, strict) at the time of order.

OrderItem (Order Add-ons)

  • Description: This polymorphic order_items table holds items added to an order. These can be standard MenuItems, items from a Package, or even custom items added by an admin. Crucially, it stores a shallow copy of the item's details at the time of the order. This includes the item's name, description, price, and any applied modifiers.
  • Schema Table: order_items
  • Key Columns:
    • order_id: Links to the parent order.
    • orderable_type & orderable_id: Defines the source of the item (e.g., MenuItem, Package, CustomItem).
    • name, description, price: Point-in-time values copied from the source.
    • qty: The quantity of this item.
    • modifiers_json: A JSON blob containing the state of the source item's modifiers when it was added to the order. This ensures validation logic uses the correct historical rules.
  • Example: The add_ons array.
    "add_ons":[
  {
    "id": 387442,
    "name": "Small Tray of Tea Leaf Salad",
    "description": "Traditional hand-picked tea leaf dressing...",
    "price": 70.0,
    "qty": 1,
    "type": "MenuItem",
    "menu_item_id": 141252
  }
]

PackageChoiceSelection (Chosen Package Options)

  • Description: This is the analogue to an OrderItem but is used specifically for orders associated with a Package. When a user selects options within a package (e.g., "choose 3 sandwiches"), each selection is stored here. It records the chosen option, the quantity, and the historical pricing rules.
  • Schema Table: package_choice_selections
  • Key Columns:
    • order_id: Links to the parent order.
    • package_item_option_id: The specific option selected from the package.
    • qty: The quantity selected for this option.
    • price_premium: The additional cost for this option at the time of the order.
  • Example: The chosen_options array.
    "chosen_options":[
  {
    "qty": 3,
    "option_name": "Basil Lemongrass Chicken Bowl",
    "package_item_option_id": 272491,
    "price_premium": 0.5
  },
  ...
]

OrderPricing

  • Description: To maintain accuracy and allow for efficient reporting, the complete pricing breakdown of an order is calculated every time the order is saved and is persisted in the order_pricing table. This provides a single source of truth for all financial aspects of an order.
  • Schema Table: order_pricing
  • Key Pricing Fields: The pricing logic aggregates numerous values. Below are the key fields and their purpose:
    • Core Charges:
      • base_price: The foundational price of the package or menu selection, often calculated against the headcount.
      • add_ons_price: Total price for all additional menu items (OrderItem records).
      • premium_options_price: The sum of all upcharges for selected premium package options.
      • custom_items_price: Total for items manually added by an admin.
      • utensils_price: The charge for place settings and/or serving utensils.
      • delivery_fee: The charge for delivery, determined by the KitchenDeliveryArea.
      • extra_portions_price / extra_choices_price: Charges for selecting more items than the package allows.
    • Fees & Taxes:
      • customer_fee: The CaterCow service fee applied to the order.
      • caterer_fee: The fee CaterCow charges the caterer.
      • sales_tax: The final calculated sales tax based on the taxable amount.
    • Adjustments & Discounts:
      • coupon_discount: The total value discounted by a customer-applied coupon.
      • poll_selection_overages: Charges incurred when team members in a group order select items that exceed a preset budget.
      • customer_adjustment_dollars: Manual credit or debit applied to the customer's total by an admin.
      • caterer_penalty_dollars: A penalty applied to the caterer's payout, usually for order issues.
    • Totals:
      • order_items_price / feeable_price: The subtotal of all items before fees, tips, and taxes are applied.
      • customer_price: The final grand total charged to the customer.
      • caterer_price: The subtotal due to the caterer before their fees.
      • caterer_price_with_tip_and_penalty: The final payout amfunt for the caterer.

Example

  "pricing": {
    "add_ons_price": 70.0,
    "base_price": 335.79,
    "coupon_discount": 0,
    "customer_fee": 0,
    "customer_price": 484.52,
    "order_items_price": 422.29,
    "utensils_price": 0.0,
    "premium_options_price": 16.5,
    "upgrades_price": 0,
    "extra_choices_price": 0,
    "extra_portions_price": 0,
    "total_price": 524.58,
    "gross_tip_amount": 20.0,
    "feeable_price": 464.52,
    "caterer_menu_upcharge": 0,
    "applied_sales_tax": 40.06,
    "delivery_fee": 42.23,
    "sales_tax": 40.06
  }

Associated Models

Address

An Order's delivery location is a critical piece of historical data. The address_id on the orders table points to an entry in the addresses table. This record contains the full, verified address and delivery instructions as they were at the time of the order.

KitchenDeliveryArea

Delivery fees and minimums are often based on geographic zones. When an Order is placed, the relevant KitchenDeliveryArea is copied to the orders table (delivery_area in the JSON response). This ensures that the delivery rules and pricing applied to the order are the ones that were in effect at the time of purchase. This data is only re-fetched if the delivery address is changed and falls into a new zone.

OrderPoll

  • Description: For group orders, a poll can be created to collect individual choices from team members. The order_polls table manages this process. When the poll is submitted, the collected selections are copied into the package_choice_selections for the final order.
  • Schema Table: order_polls
  • Key Columns:
    • order_id: The order this poll is attached to.
    • code: A unique, shareable code for team members to access the poll.
    • team_id: The team participating in the poll.

Review

After an Order is completed, users can leave a review. The reviews table stores this feedback.

  • Schema Table: reviews
  • Key Columns:
    • order_id: The order being reviewed.
    • user_id: The user leaving the review.
    • overall_rating: The primary numeric rating (e.g., 1-5 stars).
    • body: The text content of the review.
    • public: A boolean indicating if the review is visible to the public.
Contributors: Nate Mortensen