OptimaOrder

Tables (16)

Category

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL

Inventory

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • quantity — DOUBLE PRECISION — NOT NULL
  • unit — VARCHAR(50) — NOT NULL
  • item_type — VARCHAR(100)
  • threshold — DOUBLE PRECISION
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL
  • auto_order_enabled — BOOLEAN — NOT NULL
  • min_stock_threshold — DOUBLE PRECISION
  • order_quantity — DOUBLE PRECISION
  • estimated_delivery_days — INTEGER
  • pack_amount — DOUBLE PRECISION — NOT NULL
  • last_low_stock_notified_at — TIMESTAMP

InventoryLog

  • id — SERIAL — Primary key — NOT NULL
  • inventory_id — INTEGER — Foreign key → Inventory.id — NOT NULL
  • change — DOUBLE PRECISION — NOT NULL
  • reason — VARCHAR(255) — NOT NULL
  • order_id — INTEGER — Foreign key → Order.id
  • created_at — TIMESTAMP — NOT NULL

Notification

  • id — SERIAL — Primary key — NOT NULL
  • message — TEXT — NOT NULL
  • read — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • type — VARCHAR(50) — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL

Option

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • price — INTEGER — NOT NULL
  • option_group_id — INTEGER — Foreign key → OptionGroup.id — NOT NULL

OptionGroup

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL

Order

  • id — SERIAL — Primary key — NOT NULL
  • total_amount — INTEGER — NOT NULL
  • total_cost — DOUBLE PRECISION
  • created_at — TIMESTAMP — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • weather — VARCHAR(255)
  • temperature — DOUBLE PRECISION

OrderItem

  • id — SERIAL — Primary key — NOT NULL
  • order_id — INTEGER — Foreign key → Order.id — NOT NULL
  • product_id — INTEGER — Foreign key → Product.id — NOT NULL
  • quantity — INTEGER — NOT NULL
  • price_per_item — INTEGER — NOT NULL
  • cost_per_item — DOUBLE PRECISION
  • selected_options — JSONB

Product

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • description — TEXT
  • price — INTEGER — NOT NULL
  • stock — INTEGER — NOT NULL
  • image_url — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL
  • category_id — INTEGER — Foreign key → Category.id
  • is_event_product — BOOLEAN — NOT NULL
  • event_price — INTEGER

ProductInventoryUsage

  • id — SERIAL — Primary key — NOT NULL
  • product_id — INTEGER — Foreign key → Product.id — NOT NULL
  • inventory_id — INTEGER — Foreign key → Inventory.id — NOT NULL
  • usage_amount — DOUBLE PRECISION — NOT NULL
  • usage_unit — VARCHAR(50)

PurchaseOrder

  • id — SERIAL — Primary key — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • ordered_at — TIMESTAMP
  • estimated_delivery_at — TIMESTAMP
  • delivered_at — TIMESTAMP
  • status — VARCHAR(50) — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL
  • supplier_id — INTEGER — Foreign key → Supplier.id

PurchaseOrderItem

  • id — SERIAL — Primary key — NOT NULL
  • purchase_order_id — INTEGER — Foreign key → PurchaseOrder.id — NOT NULL
  • product_id — INTEGER — Foreign key → Product.id
  • inventory_id — INTEGER — Foreign key → Inventory.id
  • quantity — DOUBLE PRECISION — NOT NULL
  • defective_quantity — DOUBLE PRECISION

PushSubscription

  • id — SERIAL — Primary key — NOT NULL
  • endpoint — TEXT — NOT NULL
  • keys — JSONB — NOT NULL
  • store_id — UUID — Foreign key → User.store_id — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

Supplier

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • contact — VARCHAR(255)
  • email — VARCHAR(255)
  • address — TEXT
  • store_id — UUID — Foreign key → User.store_id — NOT NULL

SupplierInventory

  • id — SERIAL — Primary key — NOT NULL
  • supplier_id — INTEGER — Foreign key → Supplier.id — NOT NULL
  • inventory_id — INTEGER — Foreign key → Inventory.id — NOT NULL
  • price — DOUBLE PRECISION
  • lead_time_days — INTEGER
  • pack_amount — DOUBLE PRECISION — NOT NULL

User

  • id — SERIAL — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255)
  • firebase_uid — VARCHAR(255)
  • store_name — VARCHAR(255) — NOT NULL
  • store_id — UUID — NOT NULL
  • billing_key — VARCHAR(255)
  • customer_key — VARCHAR(255)
  • card_company — VARCHAR(100)
  • card_number — VARCHAR(100)
  • business_registration_number — VARCHAR(100)
  • business_license_image_url — TEXT
  • store_address — TEXT
  • last_cleaned_at — TIMESTAMP