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