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