Q3_PO_Module_ERD_v1

Tables (66)

admin_activity_logs

  • id — BIGINT — Primary key — NOT NULL
  • admin_user_id — BIGINT — Foreign key → admin_users.id — NOT NULL
  • action_category — ENUM( 'AUTH', 'PRODUCT', 'SKU', 'INVENTORY', 'INBOUND', 'TRANSFER', 'STOCKTAKE', 'SALES_UPLOAD', 'PRICE', 'SYNC', 'USER', 'SETTING', 'EXPORT', 'VENDOR', 'BROKER', 'DOCUMENT', 'INQUIRY', 'PURCHASE_ORDER', 'APPROVAL', 'OTHER' ) — NOT NULL
  • action_type — ENUM( 'CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'LOGOUT', 'LOGIN_FAILED', 'CONFIRM', 'CANCEL', 'APPROVE', 'REJECT', 'UPLOAD', 'DOWNLOAD', 'EXPORT', 'SYNC_TRIGGER', 'PERMISSION_CHANGE', 'MAGIC_LINK_SEND', 'MAGIC_LINK_ACCESS', 'SUBMIT', 'RECALL', 'ADOPT', 'CONSENT', 'OBJECT', 'OTHER' ) — NOT NULL
  • resource_type — VARCHAR(50)
  • resource_id — BIGINT
  • resource_label — VARCHAR(255)
  • before_data — JSON
  • after_data — JSON
  • changed_fields — JSON
  • description — VARCHAR(500)
  • ip_address — VARCHAR(45)
  • user_agent — VARCHAR(500)
  • request_id — VARCHAR(100)
  • status — ENUM('SUCCESS', 'FAILED', 'PARTIAL') — NOT NULL
  • error_message — TEXT
  • created_at — DATETIME — NOT NULL

admin_users

  • id — BIGINT — Primary key — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • job_rank — ENUM('SAWON', 'DAERI', 'GWAJANG', 'CHAJANG', 'BUJANG', 'ISA', 'DAEPYO') — 직급: 사원/대리/과장/차장/부장/이사/대표
  • job_position — VARCHAR(50) — 직책: 구매팀장 / 사업부장 / 물류팀장 등
  • department — VARCHAR(50) — 소속 부서 (합의선 라우팅 기준)
  • role — ENUM('SUPER_ADMIN', 'ADMIN', 'STAFF', 'MD', 'DESIGN', 'LOGISTICS_DOMESTIC', 'LOGISTICS_OVERSEAS', 'BROKER', 'FINANCE') — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

approval_delegations

  • id — BIGINT — Primary key — NOT NULL
  • delegator_id — BIGINT — Foreign key → admin_users.id — NOT NULL — 위임한 사람
  • delegate_id — BIGINT — Foreign key → admin_users.id — NOT NULL — 대리자
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • reason — VARCHAR(255)
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

approval_lines

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • approval_rule_id — BIGINT — Foreign key → approval_rules.id — 적용된 전결 규정
  • is_manual_override — BOOLEAN — NOT NULL — 16b 에서 수동 조정
  • override_reason — VARCHAR(500) — 수동 조정 시 사유 필수
  • submitted_at — DATETIME
  • completed_at — DATETIME
  • final_result — ENUM('APPROVED', 'REJECTED', 'RECALLED')
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

approval_rule_steps

  • id — BIGINT — Primary key — NOT NULL
  • approval_rule_id — BIGINT — Foreign key → approval_rules.id — NOT NULL
  • step_no — INT — NOT NULL
  • step_type — ENUM('DRAFT', 'REVIEW', 'APPROVE') — NOT NULL — 기안/검토/전결
  • target_position — VARCHAR(50) — 예: 구매팀장
  • target_rank — ENUM('DAERI', 'GWAJANG', 'CHAJANG', 'BUJANG', 'ISA', 'DAEPYO')
  • target_user_id — BIGINT — Foreign key → admin_users.id
  • is_skippable — BOOLEAN — NOT NULL — 해당자 없으면 SKIPPED 처리
  • created_at — DATETIME — NOT NULL

approval_rules

  • id — BIGINT — Primary key — NOT NULL
  • rule_code — VARCHAR(50) — NOT NULL — A-01, A-02 ...
  • rule_name — VARCHAR(255) — NOT NULL
  • trade_type — ENUM('NORMAL', 'MERCHANTING', 'FOC')
  • customs_required — BOOLEAN
  • priority — ENUM('NORMAL', 'HIGH', 'URGENT')
  • amount_min_krw — DECIMAL(15, 2) — 기준통화 환산 금액 하한 (이상)
  • amount_max_krw — DECIMAL(15, 2) — 상한 (미만)
  • final_approver_rank — ENUM('DAERI', 'GWAJANG', 'CHAJANG', 'BUJANG', 'ISA', 'DAEPYO') — NOT NULL
  • priority_order — INT — NOT NULL — 여러 규정 매칭 시 우선순위 (작을수록 먼저)
  • is_active — BOOLEAN — NOT NULL
  • effective_from — DATE
  • effective_to — DATE
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

approval_steps

  • id — BIGINT — Primary key — NOT NULL
  • approval_line_id — BIGINT — Foreign key → approval_lines.id — NOT NULL
  • step_kind — ENUM('APPROVAL', 'CONSENT') — NOT NULL — 결재선 / 합의선
  • step_no — INT — NOT NULL — 결재는 순차, 합의는 병렬(모두 같은 시점)
  • step_type — ENUM('DRAFT', 'REVIEW', 'APPROVE', 'CONSENT') — NOT NULL
  • assignee_id — BIGINT — Foreign key → admin_users.id — NOT NULL
  • assignee_rank_snapshot — VARCHAR(20)
  • assignee_position_snapshot — VARCHAR(50)
  • department — VARCHAR(50) — 합의 단계의 협조부서
  • consent_rule_id — BIGINT — Foreign key → consent_rules.id
  • is_final_approver — BOOLEAN — NOT NULL — 전결권자
  • status — ENUM( 'PENDING', 'WAITING', 'APPROVED', 'CONDITIONAL', 'REJECTED', 'CONSENTED', 'OBJECTED', 'SKIPPED' ) — NOT NULL
  • comment — TEXT — CONDITIONAL / REJECTED / OBJECTED 는 필수
  • delegated_from — BIGINT — Foreign key → admin_users.id — 원 결재자 (이력은 "김지훈 (대리: 박서준)")
  • acted_at — DATETIME
  • due_at — DATETIME — SLA
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

brands

  • id — BIGINT — Primary key — NOT NULL
  • brand_name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

broker_messages

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • sender_type — ENUM('US','BROKER','SYSTEM') — NOT NULL
  • sender_user_id — BIGINT — Foreign key → admin_users.id
  • sender_name — VARCHAR(100)
  • body — TEXT — NOT NULL
  • is_read_by_us — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

brokers

  • id — BIGINT — Primary key — NOT NULL
  • broker_name — VARCHAR(255) — NOT NULL
  • contact_name — VARCHAR(100)
  • contact_email — VARCHAR(255) — NOT NULL — 매직링크 발송용
  • contact_phone — VARCHAR(50)
  • license_no — VARCHAR(50)
  • note — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

business_types

  • id — BIGINT — Primary key — NOT NULL
  • type_name — VARCHAR(50) — NOT NULL
  • is_consignment — BOOLEAN — NOT NULL
  • requires_settlement — BOOLEAN — NOT NULL
  • owns_stock — BOOLEAN — NOT NULL
  • description — VARCHAR(255)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

cafe24_sales_history

  • id — BIGINT — Primary key — NOT NULL
  • cafe24_order_id — VARCHAR(50) — NOT NULL — Cafe24 주문번호
  • ordered_at — BIGINT — NOT NULL — 주문(결제)일시 · epoch초
  • sku_id — BIGINT — Foreign key → skus.id — 매칭 성공 시 skus FK, 실패 시 NULL
  • custom_product_code — VARCHAR(100) — Cafe24 원본 자체상품코드(=우리 skuCode 기대)
  • product_name — VARCHAR(255)
  • quantity — INT — NOT NULL
  • sale_price — DECIMAL(15, 2)
  • line_amount — DECIMAL(15, 2) — 라인 매출 = 단가 × 수량 (정가 기준, 할인 전)
  • order_status — VARCHAR(30)
  • match_status — ENUM('MATCHED', 'UNMATCHED') — NOT NULL
  • imported_at — BIGINT — NOT NULL

cafe24_sync_logs

  • id — BIGINT — Primary key — NOT NULL
  • sync_type — ENUM('PRODUCT', 'SKU', 'FULL') — NOT NULL
  • status — ENUM('RUNNING', 'SUCCESS', 'FAILED') — NOT NULL
  • total_count — INT
  • created_count — INT
  • updated_count — INT
  • failed_count — INT
  • error_message — TEXT
  • started_at — DATETIME — NOT NULL
  • finished_at — DATETIME

consent_rules

  • id — BIGINT — Primary key — NOT NULL
  • rule_code — VARCHAR(50) — NOT NULL — C-01 재무 / C-02 물류 ...
  • rule_name — VARCHAR(255) — NOT NULL
  • department — VARCHAR(50) — NOT NULL — 합의 대상 부서
  • trade_type — ENUM('NORMAL', 'MERCHANTING', 'FOC')
  • customs_required — BOOLEAN
  • amount_min_krw — DECIMAL(15, 2)
  • payment_term_code — VARCHAR(20) — 특정 결제조건일 때만 (예: LC)
  • is_mandatory — BOOLEAN — NOT NULL — TRUE 면 미회신 시 경고 표시
  • sla_hours — INT — 회신 기대 시간 (초과 시 알림)
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

customs_broker_access

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • broker_id — BIGINT — Foreign key → brokers.id — NOT NULL
  • access_token — VARCHAR(255) — NOT NULL
  • can_view_documents — BOOLEAN — NOT NULL
  • can_upload_documents — BOOLEAN — NOT NULL
  • can_update_customs — BOOLEAN — NOT NULL
  • can_view_costs — BOOLEAN — NOT NULL — 매입가 마스킹 해제 (기본 OFF)
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME
  • revoked_by — BIGINT — Foreign key → admin_users.id
  • first_accessed_at — DATETIME
  • last_accessed_at — DATETIME
  • access_count — INT — NOT NULL
  • sent_to_email — VARCHAR(255) — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • sent_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

forwarders

  • id — BIGINT — Primary key — NOT NULL
  • forwarder_name — VARCHAR(255) — NOT NULL
  • forwarder_name_en — VARCHAR(255)
  • contact_name — VARCHAR(100)
  • contact_email — VARCHAR(255)
  • contact_phone — VARCHAR(50)
  • business_no — VARCHAR(50)
  • address — TEXT
  • services — JSON — "SEA","AIR","CUSTOMS","INLAND"
  • default_payment_terms — VARCHAR(100)
  • note — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

fx_snapshots

  • id — BIGINT — Primary key — NOT NULL
  • currency_code — CHAR(3) — NOT NULL
  • rate_to_krw — DECIMAL(15,4) — NOT NULL
  • rate_date — DATE — NOT NULL
  • source — VARCHAR(50)
  • fetched_at — DATETIME — NOT NULL
  • note — TEXT
  • created_at — DATETIME — NOT NULL

hs_codes

  • id — BIGINT — Primary key — NOT NULL
  • hs_code — VARCHAR(20) — NOT NULL
  • description_ko — VARCHAR(500) — NOT NULL
  • description_en — VARCHAR(500)
  • base_duty_rate — DECIMAL(5,2)
  • fta_duty_rates — JSON
  • note — TEXT
  • is_active — BOOLEAN — NOT NULL
  • last_synced_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inbound_cost_adjustments

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • adjustment_type — ENUM( 'FX_DIFF', 'DUTY_FINALIZED', 'FREIGHT_ACTUAL', 'INSURANCE_ACTUAL', 'INLAND_TRANSPORT', 'WAREHOUSE_FEE', 'FORWARDER_FEE', 'DEMURRAGE', 'OTHER' ) — NOT NULL
  • expected_amount_foreign — DECIMAL(15,2)
  • actual_amount_foreign — DECIMAL(15,2)
  • diff_amount_krw — DECIMAL(15,2) — NOT NULL
  • reason — TEXT
  • adjusted_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • adjusted_at — DATETIME — NOT NULL

inbound_documents

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • document_type — ENUM( 'INVOICE', 'PACKING_LIST', 'BL', 'HOUSE_BL', 'SWITCH_BL', 'AWB', 'HOUSE_AWB', 'CO', 'CUSTOMS', 'FOC_STATEMENT', 'PI', 'DOMESTIC_RECEIPT', 'OTHER' ) — NOT NULL
  • requirement — ENUM('REQUIRED', 'CONDITIONAL', 'NOT_APPLICABLE') — NOT NULL
  • file_url — VARCHAR(500) — 미첨부(PENDING) 상태에서는 NULL
  • file_name — VARCHAR(255)
  • file_size — INT
  • mime_type — VARCHAR(50)
  • document_no — VARCHAR(100)
  • issued_date — DATE
  • issued_by — VARCHAR(255) — 발급 주체: 벤더/중개자/포워더/항공사/관세청
  • status — ENUM('PENDING', 'UPLOADED', 'VERIFIED', 'REJECTED') — NOT NULL
  • reject_reason — VARCHAR(500) — 반려 사유 (관세사 재요청 알림에 사용)
  • note — TEXT
  • ocr_data — JSON
  • ocr_processed_at — DATETIME
  • uploaded_by — BIGINT — Foreign key → admin_users.id
  • uploaded_by_type — ENUM('ADMIN', 'STAFF', 'BROKER', 'SYSTEM') — NOT NULL
  • uploaded_at — DATETIME
  • verified_by — BIGINT — Foreign key → admin_users.id
  • verified_at — DATETIME
  • created_at — DATETIME — NOT NULL

inbound_items

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL — 수량 기준은 SKU (#168)
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • purchase_order_item_id — BIGINT — Foreign key → purchase_order_items.id
  • quantity — INT — NOT NULL — 예정 수량 (EA)
  • expected_quantity — INT — 예정 수량 명시값 (검수 비교용)
  • received_quantity — INT — 실제 검수 수량 (EA)
  • defect_quantity — INT — NOT NULL
  • unit_cost — DECIMAL(12, 2) — 매입단가 (원화 환산)
  • unit_cost_foreign — DECIMAL(15, 2) — 외화 단가
  • currency — CHAR(3) — v12 단가 통화 (#167 분리 저장)
  • hs_code — VARCHAR(20)
  • country_of_origin — CHAR(2)
  • validation_status — ENUM('OK', 'WARNING', 'ERROR') — NOT NULL
  • validation_messages — JSON
  • scanned_barcode — VARCHAR(100)
  • inspection_method — ENUM('COUNT', 'MEASUREMENT')
  • inspection_note — TEXT
  • inspection_result — ENUM('PASS', 'FAIL', 'CONDITIONAL')
  • defect_rate — DECIMAL(6, 4) — 불량률 — AQL 대비 판정
  • excel_row_no — INT
  • created_at — DATETIME — NOT NULL

inbound_orders

  • id — BIGINT — Primary key — NOT NULL
  • inbound_no — VARCHAR(50) — NOT NULL — 발번: IN-{yymmdd}-{seq2} 또는 PO코드-{차수}
  • is_overseas — BOOLEAN — NOT NULL
  • customs_required — BOOLEAN — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — 발주 기반 생성 시
  • purchase_order_schedule_id — BIGINT — Foreign key → purchase_order_schedules.id — 차수 1건 = 이 전표 1건
  • purchase_order_no — VARCHAR(100) — deprecated: 발주모듈 도입 전 자유 텍스트
  • purchase_round — VARCHAR(20) — 입고 차수 라벨 (1차, 2차)
  • vendor_id — BIGINT — Foreign key → vendors.id
  • supplier — VARCHAR(255) — deprecated
  • creation_method — ENUM( 'PURCHASE_ORDER', 'MANUAL', 'EXCEL', 'BARCODE', 'API' ) — NOT NULL
  • source_file_url — VARCHAR(500)
  • source_file_name — VARCHAR(255)
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • expected_arrival_date — DATE
  • actual_arrival_date — DATE
  • expected_inspection_date — DATE
  • shipping_method — VARCHAR(50)
  • tracking_no — VARCHAR(100)
  • assignee_id — BIGINT — Foreign key → admin_users.id
  • notify_enabled — BOOLEAN — NOT NULL
  • notify_d_minus_1 — BOOLEAN — NOT NULL
  • notify_d_day — BOOLEAN — NOT NULL
  • notify_d_plus_3 — BOOLEAN — NOT NULL
  • memo — TEXT
  • reason — TEXT
  • total_quantity — INT — NOT NULL — SKU 기준 수량 합계 (EA)
  • expected_total_quantity — INT
  • received_total_quantity — INT
  • inspection_status — ENUM('PENDING', 'PARTIAL', 'MATCHED', 'MISMATCH') — NOT NULL
  • status — ENUM( 'DRAFT', 'PLANNED', 'IN_TRANSIT', 'IN_CUSTOMS', 'ARRIVED', 'INSPECTING', 'CONFIRMED', 'PARTIAL', 'HOLD', 'DELAYED', 'DOCUMENT_MISSING', 'CANCELLED' ) — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_by_type — ENUM('ADMIN', 'STAFF', 'SYSTEM') — NOT NULL
  • confirmed_by — BIGINT — Foreign key → admin_users.id
  • confirmed_at — DATETIME
  • cancelled_by — BIGINT — Foreign key → admin_users.id
  • cancelled_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiries

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_code — VARCHAR(50) — NOT NULL — RFQ-26Q3-TONG-0002
  • vendor_id — BIGINT — Foreign key → vendors.id — NOT NULL
  • title — VARCHAR(255) — 예: 통굽 26SS 발주 협의
  • is_overseas_vendor — BOOLEAN — NOT NULL
  • customs_required — BOOLEAN — NOT NULL — 통관 필요 — 해외 벤더라도 국내 인도면 FALSE
  • trade_type — ENUM('NORMAL', 'MERCHANTING', 'FOC') — NOT NULL — 일반 / 3국거래 / 무환
  • base_currency — CHAR(3) — NOT NULL — 합계 환산 기준통화
  • md_user_id — BIGINT — Foreign key → admin_users.id — NOT NULL — 담당 MD (기안자)
  • reply_due_date — DATE
  • status — ENUM( 'DRAFT', 'SENT', 'REPLIED', 'VENDOR_AGREED', 'AGREED', 'REJECTED', 'EXPIRED' ) — NOT NULL
  • coverage_filled — INT — NOT NULL
  • coverage_total — INT — NOT NULL
  • reconsent_required — BOOLEAN — NOT NULL
  • reconsent_reason — VARCHAR(500)
  • vendor_agreed_at — DATETIME
  • vendor_agreed_by_name — VARCHAR(100) — 벤더 담당자 서명명 (외부인이라 FK 없음)
  • vendor_agreed_ip — VARCHAR(45)
  • memo — TEXT
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_attachments

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • message_id — BIGINT — Foreign key → inquiry_messages.id — 드로어 메시지에 딸린 첨부
  • file_url — VARCHAR(500) — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_size — INT
  • mime_type — VARCHAR(50)
  • uploader_type — ENUM('US', 'VENDOR') — NOT NULL
  • uploader_user_id — BIGINT — Foreign key → admin_users.id
  • uploader_name — VARCHAR(100)
  • category — ENUM('QUOTE', 'DESIGN', 'SPEC', 'INSPECTION', 'CONTRACT', 'OTHER') — NOT NULL
  • carry_to_po — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

inquiry_gate_checks

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • checked_at — DATETIME — NOT NULL
  • tab_code — ENUM('ITEMS', 'MOQ', 'DELIVERY', 'PAYMENT', 'SPECIAL') — NOT NULL
  • filled_count — INT — NOT NULL
  • required_count — INT — NOT NULL
  • is_satisfied — BOOLEAN — NOT NULL
  • blocking_reason — VARCHAR(500) — 예: 지연책임 미확정
  • impact — VARCHAR(255) — 예: 검수기준 불완전 / §D 미완성

inquiry_items

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — SKU 기준. 신규 품목은 NULL + temp_item_name
  • temp_item_name — VARCHAR(255) — SKU 미등록 신규 품목명
  • line_no — INT — NOT NULL — 표시 순서
  • proposed_qty — INT — SKU 기준 수량 (EA)
  • proposed_unit_price — DECIMAL(15, 4)
  • proposed_currency — CHAR(3)
  • counter_qty — INT
  • counter_unit_price — DECIMAL(15, 4)
  • counter_currency — CHAR(3)
  • counter_note — VARCHAR(500) — 예: 공급불가 사유
  • agreed_qty — INT
  • agreed_unit_price — DECIMAL(15, 4)
  • agreed_currency — CHAR(3)
  • origin — ENUM('MD', 'VENDOR') — NOT NULL — VENDOR = 벤더 대체품 제안 행
  • status — ENUM( 'PROPOSED', 'ACTIVE', 'AGREED', 'REJECTED', 'UNAVAILABLE' ) — NOT NULL
  • adopted_by — BIGINT — Foreign key → admin_users.id — 채택(Adopt)한 MD
  • adopted_at — DATETIME
  • is_proxy_input — BOOLEAN — NOT NULL — MD가 벤더 열을 대신 입력
  • proxy_input_by — BIGINT — Foreign key → admin_users.id
  • proxy_input_at — DATETIME
  • source_message_id — BIGINT — 이 행의 근거가 된 드로어 메시지
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_messages

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • sender_type — ENUM('US', 'VENDOR', 'SYSTEM') — NOT NULL
  • sender_user_id — BIGINT — Foreign key → admin_users.id — US 일 때만
  • sender_name — VARCHAR(100) — VENDOR 일 때 표시명
  • body — TEXT — NOT NULL
  • context_tab — ENUM('ITEMS', 'MOQ', 'DELIVERY', 'PAYMENT', 'SPECIAL', 'GENERAL') — NOT NULL
  • is_read_by_us — BOOLEAN — NOT NULL
  • is_read_by_vendor — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

inquiry_moqs

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • inquiry_item_id — BIGINT — Foreign key → inquiry_items.id — NOT NULL
  • requested_qty — INT — 당사 요청 수량 (EA)
  • vendor_moq — INT — 벤더 MOQ (EA)
  • vendor_available_qty — INT — 벤더 공급 가능 수량 (EA)
  • vendor_note — VARCHAR(500)
  • shortfall_action — ENUM( 'RAISE_QTY', 'RAISE_PRICE', 'SPLIT_ROUNDS', 'SWITCH_ITEM', 'DROP_ITEM' ) — 미달 시 처리안 — 결정 주체는 당사(MD)
  • shortfall_note — VARCHAR(500)
  • is_resolved — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_payment_schedules

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_payment_id — BIGINT — Foreign key → inquiry_payments.id — NOT NULL
  • seq_no — INT — NOT NULL — 지급 회차
  • trigger_event — ENUM( 'ON_CONTRACT', 'ON_PRODUCTION', 'ON_SHIPMENT', 'ON_BL_DATE', 'ON_ARRIVAL', 'ON_INSPECTION', 'FIXED_DATE' ) — NOT NULL — 지급 기산점
  • offset_days — INT — 기산점 +N일 (OA 60 → 60)
  • ratio_percent — DECIMAL(5, 2) — 지급 비율 (%) — TT 30/70 의 30
  • fixed_date — DATE
  • note — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

inquiry_payments

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • payment_term_code — ENUM( 'TT', 'TT_ADVANCE', 'CAD', 'OA', 'DA', 'DP', 'LC_SIGHT', 'LC_USANCE', 'STANDBY_LC', 'FOC' ) — NOT NULL — §2 용어집 기준
  • payment_term_detail — VARCHAR(100) — 예: 30/70 · OA 60일
  • settlement_currency — CHAR(3) — NOT NULL — 결제 통화 (품목 통화와 다를 수 있음)
  • vendor_response — ENUM('CONSENTED', 'OBJECTED')
  • vendor_comment — VARCHAR(500)
  • is_finalized — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_schedules

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • round_no — INT — NOT NULL — 차수 (1, 2, 3 ...)
  • proposed_date — DATE — 희망 납기 (MD)
  • proposed_qty — INT — 희망 수량 (EA)
  • counter_date — DATE — 생산 가능일 (벤더)
  • counter_qty — INT
  • agreed_date — DATE — 확정 납기 (MD)
  • agreed_qty — INT — 확정 수량 (EA)
  • delivery_location_id — BIGINT — Foreign key → inventory_locations.id — 자사 창고일 때
  • delivery_place_text — VARCHAR(255) — 외부 인도지 (팝업장 등)
  • is_split_shipment — BOOLEAN — NOT NULL
  • note — VARCHAR(500)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_special_terms

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • clause_no — INT — NOT NULL — 조항 번호 (§1, §2 ...)
  • term_option_id — BIGINT — Foreign key → term_options.id — 표준 템플릿에서 가져온 경우
  • title — VARCHAR(255) — NOT NULL
  • body_text — TEXT — NOT NULL — 발주서 §I 로 인쇄
  • vendor_response — ENUM('CONSENTED', 'OBJECTED')
  • vendor_comment — VARCHAR(500)
  • is_finalized — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inquiry_terms

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • term_code — ENUM('INCOTERM', 'CARRIAGE', 'DAMAGE', 'INSPECTION', 'DELAY', 'RETURN') — NOT NULL
  • term_option_id — BIGINT — Foreign key → term_options.id — 선택한 표준 문구 (term_options FK)
  • term_detail — VARCHAR(255) — 옵션에 딸린 값 (예: FOB → Ningbo)
  • cost_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA') — 비용 부담
  • risk_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA') — 리스크 부담
  • vendor_response — ENUM('CONSENTED', 'OBJECTED')
  • vendor_comment — VARCHAR(500) — 예: 상한 3% 요청
  • is_finalized — BOOLEAN — NOT NULL
  • finalized_by — BIGINT — Foreign key → admin_users.id
  • finalized_at — DATETIME
  • source_message_id — BIGINT — 근거 메시지 앵커
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventories

  • id — BIGINT — Primary key — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • quantity — INT — NOT NULL
  • safety_stock — INT
  • last_movement_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory_locations

  • id — BIGINT — Primary key — NOT NULL
  • location_name — VARCHAR(100) — NOT NULL
  • location_type — ENUM('STORE', 'WAREHOUSE', 'POPUP', 'ONLINE', 'DEFECT', 'ETC') — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

inventory_sections

  • id — BIGINT — Primary key — NOT NULL
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • section_name — VARCHAR(100) — NOT NULL
  • section_code — VARCHAR(50)
  • description — VARCHAR(255)
  • sort_order — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

ip_brands

  • id — BIGINT — Primary key — NOT NULL
  • ip_id — BIGINT — Foreign key → ips.id — NOT NULL
  • brand_id — BIGINT — Foreign key → brands.id — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • contract_started_at — DATE
  • contract_ended_at — DATE
  • note — VARCHAR(500)
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

ips

  • id — BIGINT — Primary key — NOT NULL
  • ip_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

notifications

  • id — BIGINT — Primary key — NOT NULL
  • notification_type — ENUM( 'APPROVAL_REQUESTED', 'CONSENT_REQUESTED', 'APPROVAL_RESULT', 'CONSENT_OBJECTED', 'INQUIRY_REPLIED', 'INQUIRY_DUE', 'RECONSENT_REQUIRED', 'INBOUND_D_MINUS_1', 'INBOUND_D_DAY', 'INBOUND_DELAYED', 'DOCUMENT_MISSING', 'CUSTOMS_CLEARED' ) — NOT NULL
  • recipient_id — BIGINT — Foreign key → admin_users.id — NOT NULL
  • reference_type — VARCHAR(50) — PURCHASE_ORDER / INQUIRY / INBOUND_ORDER
  • reference_id — BIGINT
  • title — VARCHAR(255) — NOT NULL
  • body — VARCHAR(1000)
  • link_url — VARCHAR(500)
  • priority — ENUM('NORMAL', 'HIGH', 'URGENT') — NOT NULL
  • channel_inapp — BOOLEAN — NOT NULL
  • channel_email — BOOLEAN — NOT NULL
  • channel_slack — BOOLEAN — NOT NULL
  • sent_at — DATETIME
  • send_error — VARCHAR(500)
  • is_read — BOOLEAN — NOT NULL
  • read_at — DATETIME
  • created_at — DATETIME — NOT NULL

overseas_shipments

  • id — BIGINT — Primary key — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • departure_country_code — CHAR(2)
  • departure_port_id — BIGINT — Foreign key → ports.id
  • arrival_port_id — BIGINT — Foreign key → ports.id
  • incoterm — ENUM('EXW', 'FOB', 'CIF', 'DAP', 'DDP') — NOT NULL
  • transport_mode — ENUM('SEA', 'AIR', 'LAND', 'COURIER') — NOT NULL
  • currency_code — CHAR(3) — NOT NULL
  • fx_snapshot_id — BIGINT — Foreign key → fx_snapshots.id
  • fx_rate — DECIMAL(15, 4)
  • etd — DATE
  • eta — DATE — NOT NULL
  • atd — DATE
  • ata — DATE
  • bl_number — VARCHAR(100) — Master B/L (선사 발급, 해상)
  • house_bl_number — VARCHAR(100) — House B/L (포워더 발급, LCL / Switch B/L)
  • awb_number — VARCHAR(100) — Master AWB (항공사 발급)
  • house_awb_number — VARCHAR(100) — v12 House AWB (포워더 발급, 항공 혼재)
  • is_switch_bl — BOOLEAN — NOT NULL — v12 3국거래 Switch B/L 여부
  • vessel_name — VARCHAR(100) — 선박명 / 항공편명
  • container_no — VARCHAR(50)
  • booking_no — VARCHAR(50)
  • goods_value_foreign — DECIMAL(15, 2) — NOT NULL
  • freight_cost_foreign — DECIMAL(15, 2)
  • insurance_cost_foreign — DECIMAL(15, 2)
  • duty_cost_foreign — DECIMAL(15, 2)
  • vat_cost_foreign — DECIMAL(15, 2)
  • other_costs_foreign — DECIMAL(15, 2)
  • forwarder_invoice_no — VARCHAR(100)
  • forwarder_fee_krw — DECIMAL(15, 2)
  • total_landed_cost_krw — DECIMAL(15, 2)
  • costs_finalized — BOOLEAN — NOT NULL
  • customs_status — ENUM( 'NOT_REQUIRED', 'PENDING', 'IN_PROGRESS', 'CLEARED', 'REJECTED', 'EXEMPT' ) — NOT NULL
  • customs_declaration_no — VARCHAR(50)
  • customs_cleared_at — DATETIME
  • broker_id — BIGINT — Foreign key → brokers.id
  • forwarder_id — BIGINT — Foreign key → forwarders.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

ports

  • id — BIGINT — Primary key — NOT NULL
  • port_code — VARCHAR(10) — NOT NULL — UN/LOCODE (예: USLAX, KRPUS)
  • port_name — VARCHAR(100) — NOT NULL
  • port_name_local — VARCHAR(100)
  • country_code — CHAR(2) — NOT NULL
  • port_type — ENUM('SEA', 'AIR', 'LAND', 'MULTI') — NOT NULL
  • is_favorite — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_categories

  • id — BIGINT — Primary key — NOT NULL
  • parent_id — BIGINT — Foreign key → product_categories.id
  • category_name — VARCHAR(100) — NOT NULL
  • sort_order — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_images

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • thumbnail_url — VARCHAR(500)
  • alt_text — VARCHAR(255)
  • sort_order — INT — NOT NULL
  • is_main — BOOLEAN — NOT NULL
  • file_size — INT
  • mime_type — VARCHAR(50)
  • uploaded_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

product_price_history

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • sale_price — DECIMAL(12,2) — NOT NULL
  • effective_from — DATETIME — NOT NULL
  • effective_to — DATETIME
  • change_reason — VARCHAR(255)
  • changed_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

products

  • id — BIGINT — Primary key — NOT NULL
  • cafe24_product_no — VARCHAR(50)
  • product_code — VARCHAR(100)
  • product_name — VARCHAR(255) — NOT NULL
  • brand_id — BIGINT — Foreign key → brands.id — NOT NULL
  • ip_id — BIGINT — Foreign key → ips.id — NOT NULL
  • business_type_id — BIGINT — Foreign key → business_types.id — NOT NULL
  • category_id — BIGINT — Foreign key → product_categories.id — NOT NULL
  • main_image_url — VARCHAR(500)
  • sale_price — DECIMAL(12,2)
  • cost_price — DECIMAL(12,2)
  • manufacturing_cost — DECIMAL(12,2)
  • purchase_cost — DECIMAL(12,2)
  • sample_cost — DECIMAL(12,2)
  • shipping_cost — DECIMAL(12,2)
  • lifecycle_status — ENUM('ACTIVE', 'DISCONTINUED', 'OUT_OF_STOCK', 'RESERVED') — NOT NULL
  • approval_status — ENUM( 'DESIGN_PENDING', 'DESIGN_APPROVED', 'SAMPLE_ORDERED', 'SAMPLE_APPROVED', 'MAIN_ORDERED', 'RECEIVED' ) — NOT NULL
  • launched_at — DATE
  • discontinued_at — DATE
  • box_quantity — INT
  • unit — ENUM('EA', 'BOX', 'SET', 'PACK') — NOT NULL
  • weight_g — INT
  • width_mm — INT
  • height_mm — INT
  • depth_mm — INT
  • display_type — ENUM('SHELF', 'HANGER', 'WALL', 'TABLE', 'STAND', 'HOOK', 'BOX', 'ETC')
  • has_expiry — BOOLEAN — NOT NULL
  • default_shelf_life_days — INT
  • is_active — BOOLEAN — NOT NULL
  • last_synced_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

purchase_order_attachments

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • inquiry_attachment_id — BIGINT — Foreign key → inquiry_attachments.id — 협의에서 승계된 경우
  • file_url — VARCHAR(500) — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_size — INT
  • mime_type — VARCHAR(50)
  • uploaded_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • uploaded_at — DATETIME — NOT NULL

purchase_order_documents

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • doc_type — ENUM('SUBMITTED', 'APPROVED', 'VENDOR_ISSUED', 'REVISION') — NOT NULL — 상신본 / 승인본(서명 포함) / 벤더 발송본 / 개정본
  • revision_no — INT — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_hash — VARCHAR(128) — SHA-256 — 위변조 검증
  • page_count — INT
  • verify_token — VARCHAR(255)
  • is_locked — BOOLEAN — NOT NULL — 상신 이후 불변
  • generated_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • generated_at — DATETIME — NOT NULL

purchase_order_items

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • inquiry_item_id — BIGINT — Foreign key → inquiry_items.id — 승계 원천 (수동 발주는 NULL)
  • sku_id — BIGINT — Foreign key → skus.id — SKU 기준 · 신규 품목은 NULL
  • temp_item_name — VARCHAR(255) — §E 신규 품목
  • line_no — INT — NOT NULL
  • qty — INT — NOT NULL — SKU 기준 수량 (EA)
  • unit_price — DECIMAL(15, 4) — NOT NULL
  • currency — CHAR(3) — NOT NULL
  • line_amount — DECIMAL(15, 2) — NOT NULL — qty × unit_price (원통화)
  • line_amount_base — DECIMAL(15, 2) — 기준통화 환산액
  • hs_code — VARCHAR(20)
  • country_of_origin — CHAR(2)
  • note — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

purchase_order_schedules

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • inquiry_schedule_id — BIGINT — Foreign key → inquiry_schedules.id
  • round_no — INT — NOT NULL
  • delivery_date — DATE — NOT NULL — 확정 납기
  • qty — INT — NOT NULL — SKU 기준 수량 합계 (EA)
  • delivery_location_id — BIGINT — Foreign key → inventory_locations.id
  • delivery_place_text — VARCHAR(255)
  • is_split_shipment — BOOLEAN — NOT NULL
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — 자동 생성된 입고예정 전표
  • generated_at — DATETIME
  • note — VARCHAR(500)
  • created_at — DATETIME — NOT NULL

purchase_order_terms

  • id — BIGINT — Primary key — NOT NULL
  • purchase_order_id — BIGINT — Foreign key → purchase_orders.id — NOT NULL
  • section — ENUM('H', 'I') — NOT NULL — H=책임소재 / I=특약사항
  • term_code — VARCHAR(30) — H 일 때 INCOTERM/CARRIAGE/...
  • clause_no — INT — I 일 때 조항 번호
  • label_ko — VARCHAR(255) — NOT NULL — 박제된 문구 (마스터 변경 무관)
  • body_text — TEXT
  • cost_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA')
  • risk_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA')
  • judgement_params — JSON — 검수(14) 자동 판정 파라미터 스냅샷
  • sort_order — INT
  • created_at — DATETIME — NOT NULL

purchase_orders

  • id — BIGINT — Primary key — NOT NULL
  • po_code — VARCHAR(50) — NOT NULL — PO-26Q3-TONG-0002
  • inquiry_id — BIGINT — Foreign key → inquiries.id
  • creation_method — ENUM('FROM_INQUIRY', 'MANUAL') — NOT NULL
  • vendor_id — BIGINT — Foreign key → vendors.id — NOT NULL
  • title — VARCHAR(255)
  • is_overseas_vendor — BOOLEAN — NOT NULL
  • customs_required — BOOLEAN — NOT NULL
  • trade_type — ENUM('NORMAL', 'MERCHANTING', 'FOC') — NOT NULL
  • merchant_vendor_id — BIGINT — Foreign key → vendors.id — 중개자 (3국거래 시 Seller)
  • origin_country_code — CHAR(2) — 원 생산국
  • switch_bl_required — BOOLEAN — NOT NULL
  • base_currency — CHAR(3) — NOT NULL — 발주서 표기 기준통화
  • total_qty — INT — NOT NULL — SKU 기준 총수량 (EA)
  • total_amount_base — DECIMAL(15, 2) — NOT NULL — 기준통화 환산 합계
  • fx_snapshot_id — BIGINT — Foreign key → fx_snapshots.id
  • fx_rate — DECIMAL(15, 4) — 승인 시점 환율 고정
  • fx_fixed_at — DATETIME
  • payment_term_code — ENUM( 'TT', 'TT_ADVANCE', 'CAD', 'OA', 'DA', 'DP', 'LC_SIGHT', 'LC_USANCE', 'STANDBY_LC', 'FOC' )
  • payment_term_detail — VARCHAR(100)
  • settlement_currency — CHAR(3)
  • priority — ENUM('NORMAL', 'HIGH', 'URGENT') — NOT NULL
  • priority_reason — VARCHAR(500) — URGENT 는 사유 필수
  • status — ENUM( 'DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'RECALLED', 'ISSUED', 'RECEIVING', 'CLOSED', 'CANCELED' ) — NOT NULL
  • submitted_at — DATETIME
  • submitted_by — BIGINT — Foreign key → admin_users.id
  • approved_at — DATETIME
  • approved_by — BIGINT — Foreign key → admin_users.id — 전결권자
  • rejected_at — DATETIME
  • reject_reason — VARCHAR(500)
  • recalled_at — DATETIME
  • issued_at — DATETIME — 벤더 발송
  • closed_at — DATETIME
  • canceled_at — DATETIME
  • cancel_reason — VARCHAR(500)
  • md_user_id — BIGINT — Foreign key → admin_users.id — NOT NULL — 기안자 (담당 MD)
  • memo — TEXT
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_upload_items

  • id — BIGINT — Primary key — NOT NULL
  • sales_upload_id — BIGINT — Foreign key → sales_uploads.id — NOT NULL
  • row_number — INT — NOT NULL
  • raw_barcode — VARCHAR(100)
  • raw_product_name — VARCHAR(255)
  • sku_id — BIGINT — Foreign key → skus.id
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • quantity — INT — NOT NULL
  • sold_price — DECIMAL(12,2)
  • sale_date — DATETIME
  • match_status — ENUM('MATCHED', 'UNMATCHED', 'SKIPPED') — NOT NULL
  • created_at — DATETIME — NOT NULL

sales_uploads

  • id — BIGINT — Primary key — NOT NULL
  • upload_no — VARCHAR(50) — NOT NULL
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_path — VARCHAR(500)
  • sale_date_from — DATE
  • sale_date_to — DATE
  • total_rows — INT
  • matched_rows — INT
  • unmatched_rows — INT
  • total_deducted_qty — INT
  • status — ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED') — NOT NULL
  • error_message — TEXT
  • uploaded_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • uploaded_at — DATETIME — NOT NULL
  • completed_at — DATETIME

sku_lots

  • id — BIGINT — Primary key — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • lot_number — VARCHAR(100) — NOT NULL
  • manufactured_at — DATE
  • expiry_date — DATE — NOT NULL
  • received_at — DATE
  • supplier — VARCHAR(255)
  • note — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sku_options

  • id — BIGINT — Primary key — NOT NULL
  • sku_id — BIGINT — NOT NULL
  • option_name — VARCHAR(50) — NOT NULL — 옵션1, 옵션2 ... 또는 색상/사이즈
  • option_value — VARCHAR(100) — NOT NULL — Pink, Small ...
  • sort_order — INT
  • created_at — BIGINT — NOT NULL
  • updated_at — BIGINT — NOT NULL

skus

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • cafe24_variant_code — VARCHAR(100)
  • sku_code — VARCHAR(100) — NOT NULL
  • barcode — VARCHAR(100)
  • internal_barcode — VARCHAR(100)
  • external_barcode — VARCHAR(100)
  • option_name — VARCHAR(255)
  • cost_price — DECIMAL(12,2)
  • image_url — VARCHAR(500)
  • is_limited_edition — BOOLEAN — NOT NULL
  • limited_qty — INT
  • is_active — BOOLEAN — NOT NULL
  • registration_status — ENUM('REGISTERED', 'PENDING', 'TEMPORARY') — NOT NULL
  • temp_created_by — BIGINT — Foreign key → admin_users.id
  • temp_resolved_at — DATETIME
  • last_synced_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

status_histories

  • id — BIGINT — Primary key — NOT NULL
  • entity_type — ENUM( 'INQUIRY', 'INQUIRY_ITEM', 'PURCHASE_ORDER', 'APPROVAL_STEP', 'INBOUND_ORDER', 'CUSTOMS' ) — NOT NULL
  • entity_id — BIGINT — NOT NULL
  • from_status — VARCHAR(30) — NULL = 최초 생성
  • to_status — VARCHAR(30) — NOT NULL
  • actor_id — BIGINT — Foreign key → admin_users.id — NULL = 시스템 자동 전이
  • actor_type — ENUM('USER', 'VENDOR', 'BROKER', 'SYSTEM') — NOT NULL
  • reason — VARCHAR(500)
  • changed_at — DATETIME — NOT NULL

stock_movements

  • id — BIGINT — Primary key — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • movement_type — ENUM( 'INIT', 'INBOUND', 'SALE', 'ADJUSTMENT_PLUS', 'ADJUSTMENT_MINUS', 'RETURN', 'TRANSFER_IN', 'TRANSFER_OUT', 'DEFECT_OUT', 'RESEND_IN', 'EXPIRED_OUT' ) — NOT NULL
  • quantity — INT — NOT NULL
  • stock_before — INT — NOT NULL
  • stock_after — INT — NOT NULL
  • reason — TEXT
  • reference_type — VARCHAR(50)
  • reference_id — BIGINT
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_by_type — ENUM('ADMIN', 'STAFF', 'SYSTEM') — NOT NULL
  • created_at — DATETIME — NOT NULL

stock_transfer_items

  • id — BIGINT — Primary key — NOT NULL
  • transfer_id — BIGINT — Foreign key → stock_transfers.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • quantity — INT — NOT NULL
  • stock_before_from — INT
  • stock_before_to — INT
  • created_at — DATETIME — NOT NULL

stock_transfers

  • id — BIGINT — Primary key — NOT NULL
  • transfer_no — VARCHAR(50) — NOT NULL
  • from_location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • from_section_id — BIGINT — Foreign key → inventory_sections.id
  • to_location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • to_section_id — BIGINT — Foreign key → inventory_sections.id
  • reason — VARCHAR(255) — NOT NULL
  • memo — TEXT
  • expected_date — DATE
  • shipping_method — VARCHAR(50)
  • total_quantity — INT — NOT NULL
  • status — ENUM('DRAFT', 'IN_TRANSIT', 'COMPLETED', 'CANCELLED') — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • shipped_by — BIGINT — Foreign key → admin_users.id
  • completed_by — BIGINT — Foreign key → admin_users.id
  • cancelled_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • shipped_at — DATETIME
  • completed_at — DATETIME
  • cancelled_at — DATETIME

stocktake_items

  • id — BIGINT — Primary key — NOT NULL
  • stocktake_id — BIGINT — Foreign key → stocktakes.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • system_qty — INT — NOT NULL
  • actual_qty — INT — NOT NULL
  • diff_qty — INT — NOT NULL
  • item_reason — TEXT
  • diff_reason — ENUM( 'UNKNOWN', 'MISSING', 'MIS_ENTRY', 'UNRECORDED_SALE', 'DEFECT', 'THEFT', 'OTHER', 'EXPIRED' )
  • resolution_status — ENUM('PENDING', 'INVESTIGATING', 'RESOLVED', 'WRITTEN_OFF') — NOT NULL
  • resolved_by — BIGINT — Foreign key → admin_users.id
  • resolved_at — DATETIME
  • resolution_note — TEXT
  • created_at — DATETIME — NOT NULL

stocktakes

  • id — BIGINT — Primary key — NOT NULL
  • stocktake_no — VARCHAR(50) — NOT NULL
  • location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • title — VARCHAR(255)
  • reason — TEXT — NOT NULL
  • status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • confirmed_by — BIGINT — Foreign key → admin_users.id
  • confirmed_at — DATETIME
  • created_at — DATETIME — NOT NULL

term_options

  • id — BIGINT — Primary key — NOT NULL
  • term_code — ENUM( 'INCOTERM', 'CARRIAGE', 'DAMAGE', 'INSPECTION', 'DELAY', 'RETURN', 'PAYMENT', 'SPECIAL' ) — NOT NULL — 조항 축
  • option_code — VARCHAR(50) — NOT NULL — 옵션 코드 (예: FOB, AQL_2_5, DELAY_01_CAP5)
  • label_ko — VARCHAR(255) — NOT NULL — 화면 표기 문구
  • label_en — VARCHAR(255) — 발주서 영문 인쇄용
  • body_text — TEXT — 발주서 §H/§I 에 인쇄될 전문
  • judgement_params — JSON — 검수·정산 자동 판정용 수치
  • default_cost_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA') — 기본 비용 부담
  • default_risk_bearer — ENUM('US', 'VENDOR', 'SHARED', 'NA') — 기본 리스크 부담
  • is_default — BOOLEAN — NOT NULL — 신규 협의 시 기본 선택값
  • sort_order — INT
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

vendor_access

  • id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
  • vendor_id — BIGINT — Foreign key → vendors.id — NOT NULL
  • access_token — VARCHAR(255) — NOT NULL
  • can_reply — BOOLEAN — NOT NULL — 07 회신 입력
  • can_consent — BOOLEAN — NOT NULL — 07b 조건 동의
  • can_propose_item — BOOLEAN — NOT NULL — 대체품 제안 행 추가
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME
  • revoked_by — BIGINT — Foreign key → admin_users.id
  • first_accessed_at — DATETIME
  • last_accessed_at — DATETIME
  • access_count — INT — NOT NULL
  • sent_to_email — VARCHAR(255) — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • sent_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • created_at — DATETIME — NOT NULL

vendors

  • id — BIGINT — Primary key — NOT NULL
  • vendor_code — VARCHAR(50) — 내부 벤더 코드 (선택)
  • vendor_name — VARCHAR(255) — NOT NULL
  • vendor_name_en — VARCHAR(255) — 영문명 (해외 벤더)
  • is_overseas — BOOLEAN — NOT NULL
  • country_code — CHAR(2) — ISO 3166-1 alpha-2 (해외만)
  • contact_name — VARCHAR(100)
  • contact_email — VARCHAR(255)
  • contact_phone — VARCHAR(50)
  • address — TEXT
  • default_currency — CHAR(3) — ISO 4217 (협의 기본 통화)
  • default_incoterm — ENUM('EXW', 'FOB', 'CIF', 'DAP', 'DDP')
  • default_payment_terms — VARCHAR(100) — deprecated: v12부터 default_payment_term_code 사용
  • default_payment_term_code — ENUM( 'TT', 'TT_ADVANCE', 'CAD', 'OA', 'DA', 'DP', 'LC_SIGHT', 'LC_USANCE', 'STANDBY_LC', 'FOC' ) — 기본 결제조건 코드
  • default_payment_term_detail — VARCHAR(100) — 예: 30/70, 60일
  • default_delivery_place — VARCHAR(255) — 기본 인도지
  • customs_required_default — BOOLEAN — NOT NULL — 통관 필요 기본값 — 해외 벤더라도 국내 인도면 FALSE
  • note — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_by — BIGINT — Foreign key → admin_users.id
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

webhook_events

  • id — BIGINT — Primary key — NOT NULL
  • provider — ENUM('CAFE24', 'OTHER') — NOT NULL
  • event_type — VARCHAR(100) — NOT NULL
  • event_id — VARCHAR(255)
  • event_timestamp — DATETIME
  • raw_headers — JSON
  • raw_payload — JSON — NOT NULL
  • signature_valid — BOOLEAN
  • status — ENUM('RECEIVED','PROCESSING','PROCESSED','IGNORED','FAILED','DUPLICATE') — NOT NULL
  • affected_resource_type — VARCHAR(50)
  • affected_resource_id — BIGINT
  • error_message — TEXT
  • retry_count — INT — NOT NULL
  • next_retry_at — DATETIME
  • received_at — DATETIME — NOT NULL
  • processed_at — DATETIME
  • source_ip — VARCHAR(45)