Q2_Inventory_ERD_v8

Tables (37)

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', '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', '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
  • role — ENUM('SUPER_ADMIN', 'ADMIN', 'STAFF', 'MD', 'DESIGN', 'LOGISTICS_DOMESTIC', 'LOGISTICS_OVERSEAS', 'BROKER') — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • 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

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 — 매칭 성공 시 skus FK, 실패 시 NULL
  • custom_product_code — VARCHAR(100) — Cafe24 원본 자체상품코드(=우리 skuCode 기대)
  • product_name — VARCHAR(255) — 원본 상품명(미매칭도 보이게)
  • quantity — INT — NOT NULL
  • order_status — VARCHAR(30) — 결제완료/취소/환불 등
  • match_status — ENUM('MATCHED', 'UNMATCHED') — NOT NULL
  • imported_at — BIGINT — NOT NULL — 백필 실행 시각 epoch초

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

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 — 토큰 만료 (기본 통관 완료 + 7일)
  • 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 — 1 통화 단위 → 원화
  • rate_date — DATE — NOT NULL
  • source — VARCHAR(50) — KEB, BOK, MANUAL 등
  • 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 — HS 코드 (예: 9503.00 또는 9503.00.10)
  • description_ko — VARCHAR(500) — NOT NULL
  • description_en — VARCHAR(500)
  • base_duty_rate — DECIMAL(5, 2) — 기본 관세율 (%)
  • fta_duty_rates — JSON — FTA별 관세율 {"KORUS":0,"KORCHN":2.5}
  • 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', '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', 'AWB', 'CO', 'CUSTOMS', 'PI', 'DOMESTIC_RECEIPT', 'OTHER' ) — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • 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
  • note — TEXT
  • ocr_data — JSON
  • ocr_processed_at — DATETIME
  • uploaded_by — BIGINT — Foreign key → admin_users.id — NOT NULL
  • uploaded_by_type — ENUM('ADMIN', 'STAFF', 'BROKER', 'SYSTEM') — NOT NULL
  • uploaded_at — DATETIME — NOT NULL
  • verified_by — BIGINT — Foreign key → admin_users.id
  • verified_at — DATETIME

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
  • section_id — BIGINT — Foreign key → inventory_sections.id
  • lot_id — BIGINT — Foreign key → sku_lots.id
  • quantity — INT — NOT NULL — 예정 수량 (등록 시)
  • expected_quantity — INT — 예정 수량 명시값 (검수 비교용)
  • received_quantity — INT — 실제 검수 수량
  • defect_quantity — INT — NOT NULL
  • unit_cost — DECIMAL(12, 2) — 매입단가 (원화 환산)
  • unit_cost_foreign — DECIMAL(15, 2) — 외화 단가 (해외)
  • hs_code — VARCHAR(20)
  • country_of_origin — CHAR(2) — 원산지 ISO 3166-1 alpha-2
  • validation_status — ENUM('OK', 'WARNING', 'ERROR') — NOT NULL
  • validation_messages — JSON
  • scanned_barcode — VARCHAR(100)
  • inspection_method — ENUM('COUNT', 'MEASUREMENT')
  • inspection_note — TEXT
  • excel_row_no — INT — 엑셀 원본 행번호 (creation_method=EXCEL)
  • created_at — DATETIME — NOT NULL

inbound_orders

  • id — BIGINT — Primary key — NOT NULL
  • inbound_no — VARCHAR(50) — NOT NULL — 발번: IN-{yymmdd}-{seq2}
  • is_overseas — BOOLEAN — NOT NULL — 국내/해외 구분 (탭 분기 기준)
  • purchase_order_no — VARCHAR(100) — 발주번호 (자유 텍스트, 검증 X)
  • purchase_round — VARCHAR(20) — 입고 차수 (1차, 2차, 3차 등)
  • vendor_id — BIGINT — Foreign key → vendors.id — 벤더 FK
  • supplier — VARCHAR(255) — deprecated: 마이그레이션 후 NULL
  • creation_method — ENUM( 'MANUAL', 'EXCEL', 'BARCODE', 'API' ) — NOT NULL
  • source_file_url — VARCHAR(500) — 원본 엑셀 파일 (creation_method=EXCEL)
  • 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
  • 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', '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

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

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 — ISO 4217 (USD/JPY/CNY/EUR)
  • fx_snapshot_id — BIGINT — Foreign key → fx_snapshots.id
  • fx_rate — DECIMAL(15, 4) — denormalized for query speed
  • etd — DATE — Estimated Time of Departure
  • eta — DATE — NOT NULL — Estimated Time of Arrival
  • atd — DATE — Actual Time of Departure
  • ata — DATE — Actual Time of Arrival
  • bl_number — VARCHAR(100) — Master B/L (실제 선사 발급, 해상)
  • house_bl_number — VARCHAR(100) — v11.3 House B/L (포워더 발급, LCL 화물)
  • awb_number — VARCHAR(100) — AWB 번호 (항공)
  • vessel_name — VARCHAR(100) — 선박명/항공편
  • container_no — VARCHAR(50) — 컨테이너 번호
  • booking_no — VARCHAR(50) — v11.3 포워더 부킹 번호
  • goods_value_foreign — DECIMAL(15, 2) — NOT NULL — 상품가 (PI 기준)
  • 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) — v11.3 포워더 청구서 번호
  • forwarder_fee_krw — DECIMAL(15, 2) — v11.3 포워더 수수료 (원화)
  • total_landed_cost_krw — DECIMAL(15, 2)
  • costs_finalized — BOOLEAN — NOT NULL — 통관 후 실비용 확정 여부
  • customs_status — ENUM( '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 — ISO 3166-1 alpha-2
  • port_type — ENUM('SEA', 'AIR', 'LAND', 'MULTI') — NOT NULL
  • is_favorite — BOOLEAN — NOT NULL — 자주 쓰는 항구 (UI 우선 노출)
  • 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

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

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

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

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) — 결제 조건 (예: T/T 30%, L/C)
  • 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 — 이벤트 유형 (예: product.created, product.updated)
  • event_id — VARCHAR(255) — 발신 시스템이 제공한 이벤트 고유 ID
  • event_timestamp — DATETIME — 발신 시스템 기준 이벤트 발생 시각
  • raw_headers — JSON — 요청 헤더 전체 (X-Cafe24-Signature 포함)
  • raw_payload — JSON — NOT NULL — 요청 본문 전체
  • signature_valid — BOOLEAN — HMAC 서명 검증 결과 (NULL=미검증)
  • status — ENUM( 'RECEIVED', 'PROCESSING', 'PROCESSED', 'IGNORED', 'FAILED', 'DUPLICATE' ) — NOT NULL
  • affected_resource_type — VARCHAR(50) — 예: products, skus
  • affected_resource_id — BIGINT — 우리 시스템의 PK
  • error_message — TEXT
  • retry_count — INT — NOT NULL
  • next_retry_at — DATETIME — 재시도 예정 시각 (NULL=재시도 안 함)
  • received_at — DATETIME — NOT NULL — 우리 서버 도착 시각
  • processed_at — DATETIME — 처리 완료 시각
  • source_ip — VARCHAR(45) — 발신 IP (IPv4/IPv6)