v2

Tables (27)

approval_history

  • id — INT
  • pr_id — INT — Foreign key → purchase_request.id
  • actor_id — INT — Foreign key → user.id
  • action — VARCHAR(20)
  • step — TINYINT
  • comment — TEXT — EX
  • from_status — VARCHAR(20)
  • to_status — VARCHAR(20)
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

approval_line

  • id — INT
  • pr_id — INT — Foreign key → purchase_request.id
  • approver_id — INT — Foreign key → user.id
  • step — TINYINT
  • status — VARCHAR(20)
  • comment — TEXT — EX
  • decided_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

bid_invitation

  • id — INT
  • proposal_id — INT — Foreign key → bid_proposal.id
  • supplier_id — INT — Foreign key → supplier.id
  • sent_at — DATETIME
  • status — VARCHAR(20)
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

bid_proposal

  • id — INT
  • pr_id — INT — Foreign key → purchase_request.id
  • buyer_id — INT — Foreign key → user.id
  • title — VARCHAR(200)
  • description — TEXT
  • deadline — DATE
  • budget_limit — BIGINT
  • status — VARCHAR(20)
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

bid_submission

  • id — INT
  • proposal_id — INT — Foreign key → bid_proposal.id
  • supplier_id — INT — Foreign key → supplier.id
  • amount — BIGINT
  • delivery_days — INT
  • tech_score — DECIMAL(5,2)
  • price_score — DECIMAL(5,2)
  • esg_score — DECIMAL(5,2)
  • total_score — DECIMAL(5,2)
  • status — VARCHAR(20)
  • note — TEXT
  • submitted_at — DATETIME
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

contract

  • id — INT
  • pr_id — INT — Foreign key → purchase_request.id
  • bid_id — INT — Foreign key → bid_submission.id
  • supplier_id — INT — Foreign key → supplier.id
  • buyer_id — INT — Foreign key → user.id
  • title — VARCHAR(200)
  • amount — BIGINT
  • start_date — DATE
  • end_date — DATE
  • status — VARCHAR(20)
  • file_path — VARCHAR(300)
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

delivery

  • id — INT
  • po_id — INT — Foreign key → purchase_order.id
  • supplier_id — INT — Foreign key → supplier.id
  • warehouse_id — INT — Foreign key → warehouse.id
  • transit_id — INT — Foreign key → stock_transit.id
  • scheduled_date — DATE
  • actual_date — DATE
  • status — VARCHAR(20)
  • delay_flag — TINYINT
  • delay_days — INT
  • created_at — DATETIME
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

department

  • Key2 — Primary key — NOT NULL
  • id — INT
  • name — VARCHAR(50)
  • Field

goods_receipt

  • id — INT
  • delivery_id — INT — Foreign key → delivery.id
  • inspector_id — INT — Foreign key → user.id
  • received_qty — INT
  • defect_qty — INT
  • defect_rate — DECIMAL(5,2)
  • result — VARCHAR(20)
  • note — TEXT
  • inspected_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

inventory

  • id — INT
  • warehouse_id — INT — Foreign key → warehouse.id
  • item_id — INT — Foreign key → item.id
  • supplier_id — INT — Foreign key → supplier.id
  • on_hand_qty — INT
  • allocated_qty — INT
  • min_stock — INT
  • max_stock — INT
  • stock_status — VARCHAR(20)
  • last_received — DATE
  • last_issued — DATE
  • updated_at — DATETIME
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

inventory_snapshot

  • id — INT
  • snapshot_date — DATE
  • warehouse_id — INT — Foreign key → warehouse.id
  • item_id — INT — Foreign key → item.id
  • on_hand_qty — INT
  • allocated_qty — INT
  • in_transit_qty — INT
  • available_qty — INT
  • stock_status — VARCHAR(20)
  • created_at — DATETIME
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

item

  • id — INT
  • code — VARCHAR(30)
  • name — VARCHAR(200)
  • spec — TEXT
  • unit — VARCHAR(10)
  • category — VARCHAR(50)
  • created_by — INT — Foreign key → user.id
  • is_deleted — TINYINT
  • deleted_at — DATETIME
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

notification

  • id — INT
  • user_id — INT — Foreign key → user.id
  • type — VARCHAR(50)
  • title — VARCHAR(300)
  • ref_id — INT
  • ref_type — VARCHAR(30)
  • is_read — TINYINT
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

purchase_order

  • id — INT
  • po_number — VARCHAR(30)
  • contract_id — INT — Foreign key → contract.id
  • supplier_id — INT — Foreign key → supplier.id
  • buyer_id — INT — Foreign key → user.id
  • approver_id — INT — Foreign key → user.id
  • item_id — INT — Foreign key → item.id
  • item_name — VARCHAR(200)
  • qty — INT
  • unit — VARCHAR(10)
  • unit_price — BIGINT
  • supply_amount — BIGINT
  • tax_amount — BIGINT
  • total_amount — BIGINT
  • delivery_date — DATE
  • status — VARCHAR(20)
  • memo — TEXT
  • created_at — DATETIME
  • approved_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

purchase_request

  • id — INT
  • requester_id — INT — Foreign key → user.id
  • dept_id — INT — Foreign key → department.id
  • title — VARCHAR(200)
  • urgency — VARCHAR(10)
  • desired_date — DATE
  • status — VARCHAR(20)
  • total_amount — BIGINT
  • reject_reason — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • Key2 — Foreign key → department.Key2 — NOT NULL

purchase_request_item

  • id — INT
  • pr_id — INT — Foreign key → purchase_request.id
  • item_name — VARCHAR(200)
  • spec — TEXT
  • qty — INT
  • unit — VARCHAR(10)
  • unit_price — BIGINT
  • amount — BIGINT
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

reorder_alert

  • id — INT
  • inventory_id — INT — Foreign key → inventory.id
  • item_id — INT — Foreign key → item.id
  • warehouse_id — INT — Foreign key → warehouse.id
  • current_qty — INT
  • min_stock — INT
  • shortage_qty — INT
  • alert_type — VARCHAR(20)
  • status — VARCHAR(20)
  • resolved_at — DATETIME
  • resolved_by — INT — Foreign key → user.id
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

stock_transaction

  • id — INT
  • warehouse_id — INT — Foreign key → warehouse.id
  • item_id — INT — Foreign key → item.id
  • transit_id — INT — Foreign key → stock_transit.id
  • po_id — INT
  • delivery_id — INT
  • type — VARCHAR(20)
  • qty — INT
  • before_qty — INT
  • after_qty — INT
  • handled_by — INT — Foreign key → user.id
  • note — TEXT
  • transacted_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

stock_transit

  • id — INT
  • transit_number — VARCHAR(30)
  • transit_type — VARCHAR(20)
  • item_id — INT — Foreign key → item.id
  • qty — INT
  • from_supplier_id — INT — Foreign key → supplier.id
  • from_warehouse_id — INT — Foreign key → warehouse.id
  • to_warehouse_id — INT — Foreign key → warehouse.id
  • po_id — INT
  • carrier — VARCHAR(100)
  • tracking_number — VARCHAR(100)
  • departed_at — DATETIME
  • expected_arrival — DATETIME
  • actual_arrival — DATETIME
  • status — VARCHAR(20)
  • delay_flag — TINYINT
  • delay_minutes — INT
  • note — TEXT
  • created_by — INT — Foreign key → user.id
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

stock_transit_event

  • id — INT
  • transit_id — INT — Foreign key → stock_transit.id
  • event_type — VARCHAR(30)
  • location — VARCHAR(200)
  • description — VARCHAR(300)
  • recorded_at — DATETIME
  • recorded_by — INT — Foreign key → user.id
  • Key2 — Foreign key → user.Key2 — NOT NULL

supplier

  • id — INT
  • code — VARCHAR(20)
  • name — VARCHAR(100)
  • biz_no — VARCHAR(20)
  • industry — VARCHAR(50)
  • address — VARCHAR(200)
  • contact_name — VARCHAR(50)
  • contact_phone — VARCHAR(20)
  • contact_email — VARCHAR(100)
  • status — VARCHAR(20)
  • is_deleted — TINYINT
  • created_at — DATETIME

supplier_esg

  • id — INT
  • supplier_id — INT — Foreign key → supplier.id
  • evaluator_id — INT — Foreign key → user.id
  • eval_year — INT
  • eval_quarter — TINYINT
  • esg_grade — VARCHAR(5)
  • esg_score — DECIMAL(5,2)
  • env_score — DECIMAL(5,2)
  • social_score — DECIMAL(5,2)
  • gov_score — DECIMAL(5,2)
  • carbon_cert — VARCHAR(100)
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

supplier_performance

  • id — INT
  • supplier_id — INT — Foreign key → supplier.id
  • year_month — VARCHAR(7)
  • delivery_rate — DECIMAL(5,2)
  • quality_score — DECIMAL(3,1)
  • claim_count — INT
  • avg_delivery_days — INT
  • created_at — DATETIME

tax_invoice

  • id — INT
  • invoice_number — VARCHAR(30)
  • po_id — INT — Foreign key → purchase_order.id
  • supplier_id — INT — Foreign key → supplier.id
  • supply_amount — BIGINT
  • tax_amount — BIGINT
  • total_amount — BIGINT
  • issue_date — DATE
  • confirm_date — DATE
  • due_date — DATE
  • status — VARCHAR(20)
  • created_at — DATETIME
  • Key — Foreign key — NOT NULL
  • Key2 — Foreign key — NOT NULL

user

  • id — INT
  • dept_id — INT — Foreign key → department.id
  • name — VARCHAR(50)
  • email — VARCHAR(100)
  • password — VARCHAR(255)
  • role — VARCHAR(20)
  • is_active — TINYINT
  • is_deleted — TINYINT
  • deleted_at — DATETIME
  • created_at — DATETIME
  • Key2 — Foreign key → department.Key2 — NOT NULL

warehouse

  • id — INT
  • site_id — INT — Foreign key → warehouse_site.id
  • code — VARCHAR(20)
  • name — VARCHAR(100)
  • warehouse_type — VARCHAR(20)
  • location_detail — VARCHAR(200)
  • manager_id — INT — Foreign key → user.id
  • capacity — INT
  • status — VARCHAR(20)
  • is_deleted — TINYINT
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL

warehouse_site

  • id — INT
  • code — VARCHAR(20)
  • name — VARCHAR(100)
  • address — VARCHAR(200)
  • manager_id — INT — Foreign key → user.id
  • status — VARCHAR(20)
  • created_at — DATETIME
  • Key2 — Foreign key → user.Key2 — NOT NULL