STOCKIT

Tables (19)

ai_order_recommendation

  • id — BIGINT — Primary key — NOT NULL
  • warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • vendor_id — BIGINT — Foreign key → vendor.id — NOT NULL
  • current_qty — INT — NOT NULL
  • safety_stock — INT — NOT NULL
  • recommended_qty — INT — NOT NULL
  • basis_period_days — INT
  • avg_daily_outbound — BIGINT
  • status — ENUM('PENDING','APPROVED','REJECTED') — NOT NULL
  • reviewed_by — BIGINT — Foreign key → member.id
  • reviewed_at — DATETIME
  • created_at — DATETIME — NOT NULL

category

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL

delivery

  • id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • store_id — BIGINT — Foreign key → store.id — NOT NULL
  • vehicle_info — VARCHAR(255)
  • status — ENUM('PREPARING','SHIPPING','DELIVERED') — NOT NULL
  • estimated_arrival — DATETIME
  • departed_at — DATETIME
  • arrived_at — DATETIME

member

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(255) — NOT NULL
  • birth_date — DATE — NOT NULL
  • role — ENUM('MASTER','STORE_MANAGER','WAREHOUSE_MANAGER') — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

notification

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • type — ENUM('ORDER','STOCK_ALERT','DELIVERY','SYSTEM','AI_RECOMMENDATION') — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • message — VARCHAR(255)
  • ref_id — BIGINT
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

order_item

  • id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — BIGINT — NOT NULL

orders

  • id — BIGINT — Primary key — NOT NULL
  • store_id — BIGINT — Foreign key → store.id — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • status — ENUM('PENDING','APPROVED','SHIPPING','COMPLETED','REJECTED','CANCELLED') — NOT NULL
  • total_price — BIGINT
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

product

  • id — BIGINT — Primary key — NOT NULL
  • category_id — BIGINT — Foreign key → category.id
  • name — VARCHAR(255) — NOT NULL
  • code — VARCHAR(255) — NOT NULL
  • unit_price — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

purchase_order

  • id — BIGINT — Primary key — NOT NULL
  • warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • vendor_id — BIGINT — Foreign key → vendor.id — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • recommendation_id — BIGINT — Foreign key → ai_order_recommendation.id
  • status — ENUM('PENDING','APPROVED','SHIPPING','COMPLETED','REJECTED') — NOT NULL
  • total_price — BIGINT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

purchase_order_item

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_order.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — BIGINT — NOT NULL

sales

  • id — BIGINT — Primary key — NOT NULL
  • store_id — BIGINT — Foreign key → store.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — BIGINT — NOT NULL
  • sold_at — DATETIME — NOT NULL

stock_transfer

  • id — BIGINT — Primary key — NOT NULL
  • from_warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • to_warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • status — ENUM('PENDING','IN_TRANSIT','COMPLETED') — NOT NULL
  • requested_by — BIGINT — Foreign key → member.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • completed_at — DATETIME

store

  • id — BIGINT — Primary key — NOT NULL
  • store_name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255)
  • region — VARCHAR(255)
  • manager_id — BIGINT — Foreign key → member.id
  • created_at — DATETIME — NOT NULL

store_inventory

  • id — BIGINT — Primary key — NOT NULL
  • store_id — BIGINT — Foreign key → store.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • safety_stock — INT — NOT NULL
  • updated_at — DATETIME

store_receiving

  • id — BIGINT — Primary key — NOT NULL
  • delivery_id — BIGINT — Foreign key → delivery.id — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • store_id — BIGINT — Foreign key → store.id — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • status — ENUM('PENDING','INSPECTED','CONFIRMED') — NOT NULL
  • confirmed_at — DATETIME
  • created_at — DATETIME — NOT NULL

vendor

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • contact_name — VARCHAR(255)
  • contact_phone — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

vendor_product

  • id — BIGINT — Primary key — NOT NULL
  • vendor_id — BIGINT — Foreign key → vendor.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • supply_price — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL

warehouse

  • id — BIGINT — Primary key — NOT NULL
  • warehouse_name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255)
  • region — VARCHAR(255)
  • manager_id — BIGINT — Foreign key → member.id
  • created_at — DATETIME — NOT NULL

warehouse_inventory

  • id — BIGINT — Primary key — NOT NULL
  • warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • safety_stock — INT — NOT NULL
  • pending_in — INT — NOT NULL
  • pending_out — INT — NOT NULL
  • status — ENUM('AVAILABLE','ON_HOLD','DEFECTIVE') — NOT NULL
  • updated_at — DATETIME