Q2_Inventory_ERD_v7
Tables (25)
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', 'OTHER' ) — NOT NULL
- action_type — ENUM( 'CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'LOGOUT', 'LOGIN_FAILED', 'CONFIRM', 'CANCEL', 'APPROVE', 'REJECT', 'UPLOAD', 'DOWNLOAD', 'EXPORT', 'SYNC_TRIGGER', 'PERMISSION_CHANGE', 'OTHER' ) — NOT NULL
- resource_type — VARCHAR(50) — 대상 테이블/엔티티명 (예: products, inventories)
- resource_id — BIGINT — 대상 PK
- resource_label — VARCHAR(255) — 검색·표시용 라벨 (상품명, SKU코드 등)
- before_data — JSON — 변경 전 데이터 (UPDATE/DELETE만)
- after_data — JSON — 변경 후 데이터 (CREATE/UPDATE만)
- changed_fields — JSON — 변경된 필드명 배열 (UPDATE만, 예: "sale_price","stock")
- description — VARCHAR(500) — 사람이 읽을 수 있는 활동 설명
- ip_address — VARCHAR(45) — IPv4/IPv6
- 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') — 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
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_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
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 — LOT (has_expiry=TRUE면 필수)
- quantity — INT — NOT NULL
- expected_quantity — INT
- received_quantity — INT
- defect_quantity — INT — NOT NULL
- scanned_barcode — VARCHAR(100)
- inspection_method — ENUM('COUNT', 'MEASUREMENT')
- inspection_note — TEXT
- created_at — DATETIME — NOT NULL
inbound_orders
- id — BIGINT — Primary key — NOT NULL
- inbound_no — VARCHAR(50) — NOT NULL
- inbound_type — ENUM('BARCODE', 'MANUAL', 'EXCEL') — NOT NULL
- location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- section_id — BIGINT — Foreign key → inventory_sections.id
- supplier — VARCHAR(255)
- purchase_round — VARCHAR(20) — v9 입고 차수 (1차, 2차, 3차 등)
- memo — TEXT
- reason — TEXT
- expected_arrival_date — DATE
- actual_arrival_date — DATE
- total_quantity — INT — NOT NULL
- expected_total_quantity — INT
- inspection_status — ENUM('PENDING', 'PARTIAL', 'MATCHED', 'MISMATCH') — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', '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
- created_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 — LOT (NULL = 유통기한 미관리)
- 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
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 — NULL = 현재 적용 중
- 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) — v9 제작 단가 (자체제작)
- purchase_cost — DECIMAL(12, 2) — v9 사입 단가 (사입)
- sample_cost — DECIMAL(12, 2) — v9 샘플비
- shipping_cost — DECIMAL(12, 2) — v9 배송비 (입고 시 발생)
- 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 — v9 상품 승인 단계
- launched_at — DATE
- discontinued_at — DATE
- box_quantity — INT
- unit — ENUM('EA', 'BOX', 'SET', 'PACK') — NOT NULL
- weight_g — INT
- width_mm — INT — v9 가로 (mm)
- height_mm — INT — v9 세로 (mm)
- depth_mm — INT — v9 깊이 (mm)
- display_type — ENUM('SHELF', 'HANGER', 'WALL', 'TABLE', 'STAND', 'HOOK', 'BOX', 'ETC') — v9 DP 형태
- has_expiry — BOOLEAN — NOT NULL — 유통기한 관리 여부 (TRUE면 SKU의 lot 등록 필수)
- default_shelf_life_days — INT — 기본 유통 가능 일수 (제조일 기준, lot 등록 시 자동 계산)
- 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 — FIFO로 자동 매칭된 LOT
- quantity — INT — NOT NULL
- sold_price — DECIMAL(12, 2) — v9 판매 시점 단가 (price_history에서 조회 or POS 원본값)
- 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 — LOT 번호 (업체 발급 또는 자체 채번)
- manufactured_at — DATE — 제조일
- expiry_date — DATE — NOT NULL — 유통기한
- received_at — DATE — 최초 입고일 (FIFO 보조)
- supplier — VARCHAR(255) — 공급처 (협업/위탁사)
- note — TEXT
- is_active — BOOLEAN — NOT NULL — FALSE = 폐기/소진 완료
- 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) — deprecated
- 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 — v9 한정판 여부 (결품 워닝 제외 대상)
- limited_qty — INT — v9 한정 수량 (NULL = 미지정)
- 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 — LOT (NULL = 미관리)
- 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 — LOT (NULL = 미관리)
- 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 — LOT (NULL = 미관리)
- 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