aa
Tables (38)
approval_document
- id — BIGINT — Primary key — NOT NULL
- doc_type — VARCHAR(50) — NOT NULL — PURCHASE_REQUEST/PURCHASE_ORDER/CONTRACT 등
- doc_id — BIGINT — NOT NULL — 참조 문서 ID
- title — VARCHAR(255) — NOT NULL
- requester_id — BIGINT — Foreign key → user.id — NOT NULL
- status — VARCHAR(20) — NOT NULL — PENDING/IN_PROGRESS/APPROVED/REJECTED/CANCELLED
- submitted_at — DATETIME
- completed_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
approval_history
- id — BIGINT — Primary key — NOT NULL
- doc_id — BIGINT — Foreign key → approval_document.id — NOT NULL
- line_id — BIGINT — Foreign key → approval_line.id
- actor_id — BIGINT — Foreign key → user.id — NOT NULL
- action — VARCHAR(20) — NOT NULL — SUBMIT/APPROVE/REJECT/CANCEL/RECALL
- comment — TEXT — EXT NUL
- created_at — DATETIME — NOT NULL
approval_line
- id — BIGINT — Primary key — NOT NULL
- doc_id — BIGINT — Foreign key → approval_document.id — NOT NULL
- step — INT — NOT NULL — 결재 순서
- approver_id — BIGINT — Foreign key → user.id — NOT NULL
- status — VARCHAR(20) — NOT NULL — PENDING/APPROVED/REJECTED/SKIPPED
- comment — TEXT — EXT NUL
- acted_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
barcode_scan_log
- id — BIGINT — Primary key — NOT NULL
- barcode — VARCHAR(100) — NOT NULL
- delivery_id — BIGINT — Foreign key → delivery.id
- scan_type — VARCHAR(20) — NOT NULL — DEPARTURE/WAYPOINT/ARRIVAL
- scan_lat — DECIMAL(10, 7)
- scan_lng — DECIMAL(10, 7)
- expected_lat — DECIMAL(10, 7)
- expected_lng — DECIMAL(10, 7)
- distance_m — DECIMAL(10, 2)
- is_valid — TINYINT(1) — NOT NULL
- scanned_by — BIGINT — Foreign key → user.id
- scanned_at — DATETIME — NOT NULL
bid_proposal
- id — BIGINT — Primary key — NOT NULL
- pr_id — BIGINT — Foreign key → purchase_request.id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- deadline — DATETIME
- budget_limit — DECIMAL(18, 2)
- status — VARCHAR(20) — NOT NULL — OPEN/CLOSED/AWARDED/CANCELLED
- created_by — BIGINT — Foreign key → user.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
bid_submission
- id — BIGINT — Primary key — NOT NULL
- proposal_id — BIGINT — Foreign key → bid_proposal.id — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- amount — DECIMAL(18, 2) — NOT NULL
- 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) — NOT NULL — SUBMITTED/REVIEWED/SELECTED/REJECTED
- note — TEXT
- submitted_at — DATETIME — NOT NULL
contract
- id — BIGINT — Primary key — NOT NULL
- bid_id — BIGINT — Foreign key → bid_submission.id
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- amount — DECIMAL(18, 2) — NOT NULL
- start_date — DATE — NOT NULL
- end_date — DATE
- status — VARCHAR(20) — NOT NULL — ACTIVE/EXPIRED/TERMINATED
- buyer_name — VARCHAR(100)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
delivery
- id — BIGINT — Primary key — NOT NULL
- delivery_type — VARCHAR(20) — NOT NULL — INBOUND/OUTBOUND
- reference_type — VARCHAR(20) — PO/SO/TRANSFER
- reference_id — BIGINT
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id
- po_id — BIGINT — Foreign key → purchase_order.id
- scheduled_date — DATE
- actual_date — DATE
- status — VARCHAR(20) — NOT NULL — SCHEDULED/IN_TRANSIT/ARRIVED/DELAYED/CANCELLED
- delay_flag — TINYINT(1) — NOT NULL
- delay_days — INT — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
department
- id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- parent_id — BIGINT — Foreign key → department.id
- field — VARCHAR(50) — 부서 유형
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
esg_indicator_scores
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- eval_year — SMALLINT — NOT NULL
- e_score — DECIMAL(5,2) — NOT NULL
- s_score — DECIMAL(5,2) — NOT NULL
- g_score — DECIMAL(5,2) — NOT NULL
- total_score — DECIMAL(5,2) — NOT NULL
- grade_label — VARCHAR(10)
- converted_at — DATETIME
- history_id — BIGINT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
external_esg_history
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- source — VARCHAR(100) — NOT NULL — 평가 출처(KCGS 등)
- eval_year — SMALLINT — NOT NULL
- eval_quarter — TINYINT
- e_grade — VARCHAR(10)
- s_grade — VARCHAR(10)
- g_grade — VARCHAR(10)
- total_grade — VARCHAR(10)
- raw_response — TEXT
- fetched_at — DATETIME
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inbound
- id — BIGINT — Primary key — NOT NULL
- inbound_no — VARCHAR(50) — NOT NULL
- inbound_order_id — BIGINT — Foreign key → inbound_order.id
- po_id — BIGINT — Foreign key → purchase_order.id
- delivery_id — BIGINT — Foreign key → delivery.id
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- inspection_id — BIGINT
- inbound_date — DATETIME — NOT NULL
- ordered_qty — INT — NOT NULL
- received_qty — INT — NOT NULL
- passed_qty — INT — NOT NULL
- unit_price — DECIMAL(18, 2)
- status — VARCHAR(20) — NOT NULL — RECEIVED/INSPECTING/COMPLETED/RETURNED
- lot_no — VARCHAR(100)
- receiver_id — BIGINT — Foreign key → user.id
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inbound_order
- id — BIGINT — Primary key — NOT NULL
- order_no — VARCHAR(50) — NOT NULL
- po_id — BIGINT — Foreign key → purchase_order.id
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- ordered_qty — INT — NOT NULL
- scheduled_date — DATE
- status — VARCHAR(20) — NOT NULL — ISSUED/IN_PROGRESS/COMPLETED/CANCELLED
- issuer_id — BIGINT — Foreign key → user.id
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inventory
- id — BIGINT — Primary key — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- current_qty — INT — NOT NULL — 현재고
- allocated_qty — INT — NOT NULL — 예약/할당
- available_qty — INT
- min_stock — INT — NOT NULL
- max_stock — INT
- safety_stock — INT — NOT NULL
- stock_status — VARCHAR(20) — NOT NULL — NORMAL/LOW/OVER/OUT
- last_received — DATE
- last_issued — DATE
- updated_at — DATETIME — NOT NULL
item
- id — BIGINT — Primary key — NOT NULL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(255) — NOT NULL
- spec — TEXT
- unit — VARCHAR(20) — NOT NULL
- category — VARCHAR(100)
- is_deleted — TINYINT(1) — NOT NULL
- deleted_at — DATETIME
- created_by — BIGINT — Foreign key → user.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
item_price_history
- id — BIGINT — Primary key — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- contract_id — BIGINT
- unit_price — DECIMAL(18, 2) — NOT NULL
- currency — VARCHAR(10) — NOT NULL
- price_type — VARCHAR(20) — 계약가/견적가/시장가
- effective_date — DATE — NOT NULL
- end_date — DATE
- change_rate — DECIMAL(7, 2)
- note — VARCHAR(500)
- created_at — DATETIME — NOT NULL
notification
- id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → user.id — NOT NULL
- type — VARCHAR(50) — NOT NULL — APPROVAL/DELIVERY/INVENTORY 등
- title — VARCHAR(255) — NOT NULL
- ref_type — VARCHAR(50) — 참조 테이블
- ref_id — BIGINT — 참조 ID
- is_read — TINYINT(1) — NOT NULL
- read_at — DATETIME
- created_at — DATETIME — NOT NULL
outbound
- id — BIGINT — Primary key — NOT NULL
- outbound_no — VARCHAR(50) — NOT NULL
- outbound_order_id — BIGINT — Foreign key → outbound_order.id
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- outbound_date — DATETIME — NOT NULL
- requested_qty — INT — NOT NULL
- issued_qty — INT — NOT NULL
- outbound_type — VARCHAR(20) — NOT NULL
- destination — VARCHAR(255)
- issuer_id — BIGINT — Foreign key → user.id
- status — VARCHAR(20) — NOT NULL — IN_PROGRESS/COMPLETED/CANCELLED
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
outbound_order
- id — BIGINT — Primary key — NOT NULL
- order_no — VARCHAR(50) — NOT NULL
- warehouse_id — BIGINT — Foreign key → warehouse.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- requested_qty — INT — NOT NULL
- scheduled_date — DATE
- destination — VARCHAR(255)
- outbound_type — VARCHAR(20) — NOT NULL — SALE/TRANSFER/DISPOSAL/RETURN
- status — VARCHAR(20) — NOT NULL — ISSUED/PICKING/COMPLETED/CANCELLED
- issuer_id — BIGINT — Foreign key → user.id
- requester_id — BIGINT — Foreign key → user.id
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
position
- id — BIGINT — Primary key — NOT NULL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL
- level — INT — NOT NULL — 직급 레벨
- is_approver — TINYINT(1) — NOT NULL
- approval_limit — DECIMAL(18, 2) — 결재 한도금액
- description — VARCHAR(255)
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
purchase_order
- id — BIGINT — Primary key — NOT NULL
- po_number — VARCHAR(50) — NOT NULL
- pr_id — BIGINT — Foreign key → purchase_request.id
- contract_id — BIGINT — Foreign key → contract.id
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- buyer_id — BIGINT — Foreign key → user.id
- order_date — DATE — NOT NULL
- delivery_date — DATE
- total_amount — DECIMAL(18, 2) — NOT NULL
- tax_amount — DECIMAL(18, 2) — NOT NULL
- status — VARCHAR(20) — NOT NULL — DRAFT/APPROVED/SENT/PARTIAL/COMPLETED/CANCELLED
- memo — TEXT
- approved_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
purchase_order_item
- id — BIGINT — Primary key — NOT NULL
- po_id — BIGINT — Foreign key → purchase_order.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- qty — INT — NOT NULL
- unit_price — DECIMAL(18,2) — NOT NULL
- amount — DECIMAL(18,2) — NOT NULL
- received_qty — INT — NOT NULL
- delivery_date — DATE
purchase_request
- id — BIGINT — Primary key — NOT NULL
- pr_number — VARCHAR(50) — NOT NULL
- title — VARCHAR(255) — NOT NULL
- urgency — VARCHAR(20) — NOT NULL — NORMAL/URGENT/CRITICAL
- desired_date — DATE
- status — VARCHAR(20) — NOT NULL — DRAFT/SUBMITTED/APPROVED/REJECTED/CLOSED
- total_amount — DECIMAL(18, 2)
- reject_reason — TEXT
- requester_id — BIGINT — Foreign key → user.id — NOT NULL
- dept_id — BIGINT — Foreign key → department.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
purchase_request_item
- id — BIGINT — Primary key — NOT NULL
- pr_id — BIGINT — Foreign key → purchase_request.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- qty — INT — NOT NULL
- unit_price — DECIMAL(18,2)
- amount — DECIMAL(18,2)
- note — VARCHAR(500)
qr_code
- id — BIGINT — Primary key — NOT NULL
- qr_uuid — VARCHAR(64) — NOT NULL
- qr_type — VARCHAR(30) — NOT NULL — ITEM/LOT/LOCATION/INBOUND_ORDER/OUTBOUND_ORDER/DELIVERY
- reference_id — BIGINT — NOT NULL
- qr_image_url — VARCHAR(500)
- status — VARCHAR(20) — NOT NULL — ACTIVE/INACTIVE/EXPIRED
- printed_at — DATETIME
- expired_at — DATETIME
- created_by — BIGINT — Foreign key → user.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
qr_scan_log
- id — BIGINT — Primary key — NOT NULL
- qr_code_id — BIGINT — Foreign key → qr_code.id — NOT NULL
- scanned_by — BIGINT — Foreign key → user.id
- scanned_at — DATETIME — NOT NULL
- scan_purpose — VARCHAR(30) — INBOUND/OUTBOUND/INVENTORY_CHECK/LOCATION_MOVE
- device_info — VARCHAR(255)
- location_lat — DECIMAL(10, 7)
- location_lng — DECIMAL(10, 7)
- related_transaction_id — BIGINT
- note — TEXT
quality_inspection
- id — BIGINT — Primary key — NOT NULL
- inspection_no — VARCHAR(50) — NOT NULL
- purchase_order_id — BIGINT — Foreign key → purchase_order.id
- inbound_id — BIGINT — Foreign key → inbound.id
- delivery_id — BIGINT — Foreign key → delivery.id
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- item_id — BIGINT — Foreign key → item.id — NOT NULL
- inspected_qty — INT — NOT NULL
- passed_qty — INT — NOT NULL
- defect_qty — INT — NOT NULL
- defect_rate — DECIMAL(5,2) — NOT NULL
- defect_type — VARCHAR(100)
- defect_detail — TEXT
- action_taken — VARCHAR(255)
- inspector_id — BIGINT — Foreign key → user.id
- inspected_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL
role_menu_mapping
- id — BIGINT — Primary key — NOT NULL
- role_code — VARCHAR(50) — NOT NULL
- menu_code — VARCHAR(50) — NOT NULL
- menu_name — VARCHAR(100)
- can_read — TINYINT(1) — NOT NULL
- can_write — TINYINT(1) — NOT NULL
- can_delete — TINYINT(1) — NOT NULL
- can_approve — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
supplier
- id — BIGINT — Primary key — NOT NULL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(255) — NOT NULL
- biz_no — VARCHAR(20) — 사업자번호
- industry — VARCHAR(100)
- address — VARCHAR(500)
- latitude — DECIMAL(10, 7)
- longitude — DECIMAL(10, 7)
- contact_name — VARCHAR(100)
- contact_phone — VARCHAR(50)
- contact_email — VARCHAR(255)
- status — VARCHAR(20) — NOT NULL — ACTIVE/SUSPENDED/BLACKLIST
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
supplier_documents
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- doc_type — VARCHAR(50) — NOT NULL — 사업자등록증/계약서 등
- doc_name — VARCHAR(255) — NOT NULL
- file_path — VARCHAR(500) — NOT NULL
- file_size — BIGINT
- issue_date — DATE
- expire_date — DATE
- status — VARCHAR(20) — NOT NULL
- uploaded_by — BIGINT — Foreign key → user.id
- reviewed_by — BIGINT — Foreign key → user.id
- reviewed_at — DATETIME
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
supplier_esg
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- eval_year — SMALLINT — NOT NULL
- eval_quarter — TINYINT
- esg_score — DECIMAL(5,2) — NOT NULL
- env_score — DECIMAL(5,2) — NOT NULL
- social_score — DECIMAL(5,2) — NOT NULL
- gov_score — DECIMAL(5,2) — NOT NULL
- carbon_cert — VARCHAR(100)
- supplier_eg_grade — VARCHAR(10)
- evaluator_id — BIGINT — Foreign key → user.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
supplier_grade
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- eval_year — SMALLINT — NOT NULL
- eval_quarter — TINYINT
- grade_name — VARCHAR(10) — NOT NULL — A/B/C/D 등급
- final_score — DECIMAL(5, 2) — NOT NULL
- created_at — DATETIME — NOT NULL
supplier_quality_stats
- id — BIGINT — Primary key — NOT NULL
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- stat_year — SMALLINT — NOT NULL
- stat_month — TINYINT
- stat_quarter — TINYINT
- total_inspected_qty — INT — NOT NULL
- total_defect_qty — INT — NOT NULL
- avg_defect_rate — DECIMAL(5,2) — NOT NULL
- on_time_delivery_rate — DECIMAL(5,2) — NOT NULL
- quality_score — DECIMAL(5,2) — NOT NULL
- inspection_count — INT — NOT NULL
- aggregated_at — DATETIME
tax_invoice
- id — BIGINT — Primary key — NOT NULL
- invoice_number — VARCHAR(50) — NOT NULL
- po_id — BIGINT — Foreign key → purchase_order.id
- supplier_id — BIGINT — Foreign key → supplier.id — NOT NULL
- tax_amount — DECIMAL(18, 2) — NOT NULL
- total_amount — DECIMAL(18, 2) — NOT NULL
- issue_date — DATE — NOT NULL
- confirm_date — DATE
- due_date — DATE
- status — VARCHAR(20) — NOT NULL — ISSUED/CONFIRMED/PAID/CANCELLED
- memo — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
user
- id — BIGINT — Primary key — NOT NULL
- dept_id — BIGINT — Foreign key → department.id
- position_id — BIGINT — Foreign key → position.id
- name — VARCHAR(100) — NOT NULL
- email — VARCHAR(255) — NOT NULL
- password — VARCHAR(255) — NOT NULL — bcrypt/argon2 해시
- role — VARCHAR(50) — NOT NULL
- is_active — TINYINT(1) — NOT NULL
- is_deleted — TINYINT(1) — NOT NULL
- deleted_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
warehouse
- id — BIGINT — Primary key — NOT NULL
- site_id — BIGINT — Foreign key → warehouse_site.id — NOT NULL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(255) — NOT NULL
- location — VARCHAR(255)
- capacity — INT
- status — VARCHAR(20) — NOT NULL
- latitude — DECIMAL(10,7)
- longitude — DECIMAL(10,7)
- gps_tolerance_m — INT — NOT NULL
- manager_id — BIGINT — Foreign key → user.id
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
warehouse_site
- id — BIGINT — Primary key — NOT NULL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(255) — NOT NULL
- address — VARCHAR(500)
- manager_id — BIGINT — Foreign key → user.id
- is_deleted — TINYINT(1) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
weight_config
- id — BIGINT — Primary key — NOT NULL
- config_name — VARCHAR(100) — NOT NULL
- e_weight — DECIMAL(5,2) — NOT NULL
- s_weight — DECIMAL(5,2) — NOT NULL
- g_weight — DECIMAL(5,2) — NOT NULL
- quality_weight — DECIMAL(5,2) — NOT NULL
- esg_weight — DECIMAL(5,2) — NOT NULL
- effective_from — DATE — NOT NULL
- effective_to — DATE
- is_active — TINYINT(1) — NOT NULL
- created_by — BIGINT — Foreign key → user.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL