optimes

Tables (16)

ai_query_history

  • query_id — INT — Primary key — NOT NULL
  • worker_id — INT — Foreign key → users.user_id — NOT NULL
  • natural_question — TEXT — NOT NULL
  • conversation_id — VARCHAR(255)
  • parent_query_id — INT — Foreign key → ai_query_history.query_id
  • effective_question — TEXT
  • generated_sql — TEXT
  • natural_answer — TEXT
  • result_json — TEXT
  • chart_spec_json — TEXT
  • row_count — INT
  • execution_time_ms — BIGINT
  • model_name — VARCHAR(255)
  • execution_status — VARCHAR(50) — NOT NULL
  • error_log — TEXT
  • created_at — DATETIME(6)

bom_structure

  • bom_id — INT — Primary key — NOT NULL
  • parent_item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • child_item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • quantity — INT — NOT NULL
  • bom_version — VARCHAR(255) — NOT NULL
  • bom_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME(6)

current_inventory

  • inventory_id — INT — Primary key — NOT NULL
  • item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • location_id — INT — Foreign key → warehouse_location.location_id — NOT NULL
  • lot_number — VARCHAR(255)
  • current_qty — INT — NOT NULL
  • first_inbound_date — DATE
  • updated_at — DATETIME(6)

dynamic_batch_schedule

  • schedule_id — INT — Primary key — NOT NULL
  • schedule_name — VARCHAR(255) — NOT NULL
  • cron_expression — VARCHAR(255) — NOT NULL
  • query_id — INT — Foreign key → ai_query_history.query_id — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • last_run_at — DATETIME(6)
  • last_run_status — VARCHAR(255) — NOT NULL
  • worker_id — INT — Foreign key → users.user_id
  • created_at — DATETIME(6)

factory_routing

  • routing_id — INT — Primary key — NOT NULL
  • factory_name — VARCHAR(255) — NOT NULL
  • line_name — VARCHAR(255) — NOT NULL
  • operation_seq — INT — NOT NULL
  • operation_name — VARCHAR(255) — NOT NULL
  • routing_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME(6)

inbound_receipt

  • inbound_id — INT — Primary key — NOT NULL
  • item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • partner_id — INT — Foreign key → partner_master.partner_id — NOT NULL
  • location_id — INT — Foreign key → warehouse_location.location_id — NOT NULL
  • inbound_qty — INT — NOT NULL
  • inbound_date — DATE — NOT NULL
  • worker_id — INT — Foreign key → users.user_id
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME(6)

inventory_transaction_history

  • transaction_id — INT — Primary key — NOT NULL
  • item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • location_id — INT — Foreign key → warehouse_location.location_id — NOT NULL
  • transaction_type — VARCHAR(50) — NOT NULL
  • quantity — INT — NOT NULL
  • reason_desc — VARCHAR(255) — NOT NULL
  • work_order_id — INT — Foreign key → work_order.order_id
  • production_execution_id — INT — Foreign key → production_execution.execution_id
  • original_transaction_id — INT — Foreign key → inventory_transaction_history.transaction_id
  • worker_id — INT — Foreign key → users.user_id
  • created_at — DATETIME(6)

item_master

  • item_id — INT — Primary key — NOT NULL
  • item_code — VARCHAR(255) — NOT NULL
  • item_name — VARCHAR(255) — NOT NULL
  • spec — VARCHAR(255)
  • unit — VARCHAR(255) — NOT NULL
  • item_type — VARCHAR(255) — NOT NULL
  • safety_stock — INT — NOT NULL
  • item_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME(6)

outbound_shipping

  • shipping_id — INT — Primary key — NOT NULL
  • shipping_no — VARCHAR(255) — NOT NULL
  • partner_id — INT — Foreign key → partner_master.partner_id — NOT NULL
  • item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • request_qty — INT — NOT NULL
  • shipped_qty — INT
  • shipping_type — VARCHAR(255)
  • picking_location_id — INT — Foreign key → warehouse_location.location_id
  • vehicle_no — VARCHAR(255)
  • carrier — VARCHAR(255)
  • tracking_no — VARCHAR(255)
  • estimated_delivery — DATE
  • cancel_reason — VARCHAR(255)
  • status — VARCHAR(255) — NOT NULL
  • worker_id — INT — Foreign key → users.user_id
  • shipped_at — DATETIME(6)
  • created_at — DATETIME(6)

partner_master

  • partner_id — INT — Primary key — NOT NULL
  • partner_code — VARCHAR(255) — NOT NULL
  • partner_name — VARCHAR(255) — NOT NULL
  • partner_type — VARCHAR(255) — NOT NULL
  • business_no — VARCHAR(255)
  • representative — VARCHAR(255)
  • contact_phone — VARCHAR(255)
  • contact_email — VARCHAR(255)
  • note — TEXT
  • partner_status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME(6)

production_execution

  • execution_id — INT — Primary key — NOT NULL
  • order_id — INT — Foreign key → work_order.order_id — NOT NULL
  • routing_id — INT — Foreign key → factory_routing.routing_id
  • good_qty — INT — NOT NULL
  • defect_qty — INT — NOT NULL
  • defect_type — VARCHAR(255)
  • defect_reason — VARCHAR(255)
  • reworkable — TINYINT(1)
  • worker_id — INT — Foreign key → users.user_id
  • man_hours_minutes — INT — NOT NULL
  • created_at — DATETIME(6)

refresh_tokens

  • refresh_token_id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.user_id — NOT NULL
  • token_hash — VARCHAR(64) — NOT NULL
  • expires_at — DATETIME(6) — NOT NULL
  • revoked — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6)
  • updated_at — DATETIME(6)

users

  • user_id — INT — Primary key — NOT NULL
  • employee_no — VARCHAR(255) — NOT NULL
  • user_name — VARCHAR(255) — NOT NULL
  • department — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • role — VARCHAR(255) — NOT NULL
  • created_at — DATETIME(6)
  • updated_at — DATETIME(6)

warehouse_location

  • location_id — INT — Primary key — NOT NULL
  • location_code — VARCHAR(255) — NOT NULL
  • warehouse_name — VARCHAR(255) — NOT NULL
  • max_capacity — INT
  • rack_row — VARCHAR(255)
  • rack_column — VARCHAR(255)
  • production_receipt_default — TINYINT(1) — NOT NULL

work_order

  • order_id — INT — Primary key — NOT NULL
  • order_no — VARCHAR(255) — NOT NULL
  • item_id — INT — Foreign key → item_master.item_id — NOT NULL
  • routing_id — INT — Foreign key → factory_routing.routing_id — NOT NULL
  • target_qty — INT — NOT NULL
  • bom_version — VARCHAR(255) — NOT NULL
  • status — VARCHAR(255) — NOT NULL
  • plan_date — DATE — NOT NULL
  • created_at — DATETIME(6)
  • updated_at — DATETIME(6)

work_order_sequence

  • sequence_id — INT — Primary key — NOT NULL
  • plan_date — DATE — NOT NULL
  • last_sequence — INT — NOT NULL