Q2_Inventory_ERD_v1
Tables (15)
brands
- id — BIGINT
- brand_name — VARCHAR(100) — NOT NULL
- is_active — BOOLEAN
- created_at — DATETIME
- updated_at — DATETIME
business_types
- id — BIGINT
- 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
- created_at — DATETIME
- updated_at — DATETIME
cafe24_sync_logs
- id — BIGINT
- 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
- finished_at — DATETIME
inbound_items
- id — BIGINT
- inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- quantity — INT — NOT NULL
- scanned_barcode — VARCHAR(100)
- created_at — DATETIME
- INDEX — idx_inbound
- INDEX — idx_inbound_sku
inbound_orders
- id — BIGINT
- inbound_no — VARCHAR(50) — NOT NULL
- inbound_type — ENUM('BARCODE', 'MANUAL') — NOT NULL
- supplier — VARCHAR(255)
- memo — TEXT
- reason — TEXT
- total_quantity — INT — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED')
- created_by — BIGINT — Foreign key → users.id — NOT NULL
- confirmed_by — BIGINT — Foreign key → users.id
- confirmed_at — DATETIME
- created_at — DATETIME
ips
- id — BIGINT
- brand_id — BIGINT — Foreign key → brands.id — NOT NULL
- ip_name — VARCHAR(100) — NOT NULL
- is_active — BOOLEAN
- created_at — DATETIME
- updated_at — DATETIME
product_categories
- id — BIGINT
- parent_id — BIGINT — Foreign key → product_categories.id
- category_name — VARCHAR(100) — NOT NULL
- sort_order — INT
- is_active — BOOLEAN
- created_at — DATETIME
- updated_at — DATETIME
products
- id — BIGINT
- cafe24_product_no — VARCHAR(50) — NOT NULL
- 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
- is_active — BOOLEAN
- last_synced_at — DATETIME
- created_at — DATETIME
- updated_at — DATETIME
- INDEX — idx_cafe24_no
- INDEX — idx_product_name
- INDEX — idx_brand_ip
- INDEX — idx_business_category
sales_upload_items
- id — BIGINT
- 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
- quantity — INT — NOT NULL
- sale_date — DATETIME
- match_status — ENUM('MATCHED', 'UNMATCHED', 'SKIPPED') — NOT NULL
- created_at — DATETIME
- INDEX — idx_upload
- INDEX — idx_match
- INDEX — idx_sales_sku
sales_uploads
- id — BIGINT
- upload_no — VARCHAR(50) — 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')
- error_message — TEXT
- uploaded_by — BIGINT — Foreign key → users.id — NOT NULL
- uploaded_at — DATETIME
- completed_at — DATETIME
skus
- id — BIGINT
- product_id — BIGINT — Foreign key → products.id — NOT NULL
- cafe24_variant_code — VARCHAR(100) — NOT NULL
- sku_code — VARCHAR(100) — NOT NULL
- barcode — VARCHAR(100)
- option_name — VARCHAR(255)
- current_stock — INT — NOT NULL
- safety_stock — INT
- is_active — BOOLEAN
- last_synced_at — DATETIME
- created_at — DATETIME
- updated_at — DATETIME
- INDEX — idx_barcode
- INDEX — idx_sku_code
- INDEX — idx_product_id
stock_movements
- id — BIGINT
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- movement_type — ENUM( 'INBOUND', 'SALE', 'ADJUSTMENT_PLUS', 'ADJUSTMENT_MINUS', 'RETURN', 'INIT' ) — 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 → users.id — NOT NULL
- created_at — DATETIME
- INDEX — idx_sku_created
- INDEX — idx_type_created
- INDEX — idx_reference
stocktake_items
- id — BIGINT
- stocktake_id — BIGINT — Foreign key → stocktakes.id — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- system_qty — INT — NOT NULL
- actual_qty — INT — NOT NULL
- diff_qty — INT — NOT NULL
- item_reason — TEXT
- created_at — DATETIME
- INDEX — idx_stocktake
- INDEX — idx_stocktake_sku
stocktakes
- id — BIGINT
- stocktake_no — VARCHAR(50) — NOT NULL
- title — VARCHAR(255)
- reason — TEXT — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED')
- created_by — BIGINT — Foreign key → users.id — NOT NULL
- confirmed_by — BIGINT — Foreign key → users.id
- confirmed_at — DATETIME
- created_at — DATETIME
users
- id — BIGINT
- username — VARCHAR(50) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- name — VARCHAR(100) — NOT NULL
- role — ENUM('ADMIN', 'STAFF') — NOT NULL
- is_active — BOOLEAN
- created_at — DATETIME
- updated_at — DATETIME