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)