FX

Tables (13)

categories

  • id — BIGINT — Primary key
  • name — VARCHAR(100) — NOT NULL
  • parent_id — BIGINT — Foreign key → categories.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

delivery (deliveries)

  • id — BIGINT — Primary key
  • order_id — BIGINT — Foreign key → orders.id
  • driver_user_id — BIGINT — Foreign key → users.id
  • status — VARCHAR(20) — NOT NULL
  • assigned_at — DATETIME
  • shipped_at — DATETIME
  • delivered_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

order_items

  • id — BIGINT — Primary key
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • quantity — DECIMAL(18,3) — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • unit_price — DECIMAL(18,2)
  • created_at — DATETIME — NOT NULL

orders

  • id — BIGINT — Primary key
  • order_no — VARCHAR(50) — NOT NULL
  • customer_user_id — BIGINT — Foreign key → users.id
  • status — VARCHAR(20) — NOT NULL
  • recipient_name — VARCHAR(100) — NOT NULL
  • recipient_phone — VARCHAR(30) — NOT NULL
  • address_line1 — VARCHAR(300) — NOT NULL
  • address_line2 — VARCHAR(300)
  • delivery_note — VARCHAR(500)
  • scheduled_delivery_date — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

products

  • id — BIGINT — Primary key
  • code (product_code) — VARCHAR(100) — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • category_id — BIGINT — Foreign key → categories.id — NOT NULL
  • storage_temp — VARCHAR(20) — NOT NULL
  • default_shelf_life_days — INT — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

roles

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

stock

  • id — BIGINT — Primary key
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • lot — VARCHAR(100) — NOT NULL
  • location_id — BIGINT — Foreign key → warehouse_location.id — NOT NULL
  • initial_quantity — DECIMAL(18,3) — NOT NULL
  • current_quentity — DECIMAL(18,3)
  • manufactured_at — DATETIME
  • expired_at — DATETIME — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

stock_history

  • id — BIGINT — Primary key
  • stock_id — BIGINT — Foreign key → stock.id — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • quantity — DECIMAL(18,3) — NOT NULL
  • actor_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id
  • reason — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

stock_reservations

  • id — BIGINT — Primary key
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • stock_id — BIGINT — Foreign key → stock.id — NOT NULL
  • reserved_quantity — DECIMAL(18,3) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_roles

  • id — BIGINT — Primary key
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • role_id — BIGINT — Foreign key → roles.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

users

  • id — BIGINT — Primary key
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

warehouse_location (warehouse_locations)

  • id — BIGINT — Primary key
  • warehouse_id — BIGINT — Foreign key → warehouses.id — NOT NULL
  • zone — VARCHAR(20) — NOT NULL
  • temp — VARCHAR(20)
  • code — VARCHAR(100) — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

warehouses

  • id — BIGINT — Primary key
  • name — VARCHAR(200) — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL