new erd

Tables (39)

admin_action_history

  • history_id — BIGINT — Primary key — NOT NULL
  • actor_id — BIGINT — NOT NULL
  • target_type — VARCHAR(20) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • action — VARCHAR(20) — NOT NULL
  • reason — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

admin_audit_log

  • id — BIGINT — Primary key — NOT NULL
  • action — VARCHAR(50) — NOT NULL
  • target_resource — VARCHAR(30) — NOT NULL
  • admin_id — BIGINT — NOT NULL
  • admin_username — VARCHAR(50) — NOT NULL
  • method_name — VARCHAR(200)
  • args — VARCHAR(500)
  • result — VARCHAR(10) — NOT NULL
  • error_detail — VARCHAR(500)
  • client_ip — VARCHAR(45)
  • user_agent — VARCHAR(255)
  • occurred_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL

cart

  • cart_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 소유자 ID (1:1)
  • created_at — DATETIME — NOT NULL

cart_item

  • cart_item_id — BIGINT
  • cart_id — BIGINT — Foreign key → cart.cart_id — NOT NULL — 소속 장바구니 ID
  • item_id — BIGINT — Foreign key → product_item.item_id — NOT NULL — 담은 상품 옵션 ID
  • quantity — INT — NOT NULL — 수량
  • created_at — DATETIME — NOT NULL

cart_items

  • cart_item_id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → carts.cart_id — NOT NULL
  • item_id — BIGINT — Foreign key — NOT NULL
  • quantity — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

carts

  • cart_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

category

  • category_id — BIGINT
  • name — VARCHAR(50) — NOT NULL — 카테고리명
  • parent_id — BIGINT — Foreign key → category.category_id — 상위 카테고리 ID (자기 참조)

coupon

  • coupon_id — BIGINT
  • name — VARCHAR(100) — NOT NULL — 쿠폰명
  • discount_type — VARCHAR(20) — NOT NULL — RATE (정률) / FIXED (정액)
  • discount_value — INT — NOT NULL — 할인율(%) 또는 할인 금액
  • min_order_price — BIGINT — 최소 주문 금액
  • max_discount_price — BIGINT — 최대 할인 한도 (RATE)
  • total_quantity — INT — NOT NULL — 총 발급 수량
  • issued_quantity — INT — 현재 발급 수량
  • status — VARCHAR(20) — NOT NULL — ACTIVE / INACTIVE
  • start_at — DATETIME — NOT NULL — 사용 시작일
  • expired_at — DATETIME — NOT NULL — 만료일
  • created_at — DATETIME — NOT NULL

delivery

  • delivery_id — BIGINT — Primary key — NOT NULL
  • order_item_id — BIGINT — Foreign key — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • invoice_number — VARCHAR(50)
  • delivery_company — VARCHAR(50)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

delivery_history

  • history_id — BIGINT — Primary key — NOT NULL
  • delivery_id — BIGINT — Foreign key → delivery.delivery_id — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • changed_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

event_outbox

  • outbox_id — BIGINT
  • topic — VARCHAR(100) — NOT NULL — Kafka 토픽
  • partition_key — VARCHAR(100) — NOT NULL — 파티션 키
  • payload — TEXT — NOT NULL — JSON 데이터
  • status — VARCHAR(20) — NOT NULL — PENDING / PROCESSING / SENT / DEAD
  • retry_count — INT — 재시도 횟수
  • created_at — DATETIME — NOT NULL
  • processed_at — DATETIME — 처리 일시

inventory_history

  • history_id — BIGINT — Primary key — NOT NULL
  • item_id — BIGINT — Foreign key — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • quantity — BIGINT — NOT NULL
  • before_stock — BIGINT — NOT NULL
  • after_stock — BIGINT — NOT NULL
  • reason — VARCHAR(255)
  • ref_type — VARCHAR(20)
  • ref_id — BIGINT
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

notification

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — NOT NULL
  • event_id — VARCHAR(100) — NOT NULL
  • type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • message — VARCHAR(500) — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

order_cancel_history

  • cancel_history_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_items.order_item_id
  • actor_type — VARCHAR(30) — NOT NULL
  • actor_id — BIGINT
  • cancel_type — VARCHAR(30) — NOT NULL
  • reason — VARCHAR(255)
  • cancelled_price — BIGINT — NOT NULL
  • restored_point — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

order_item

  • order_item_id — BIGINT
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL — 소속 주문 ID
  • item_id — BIGINT — Foreign key → product_item.item_id — NOT NULL — 구매한 상품 옵션 ID
  • seller_id — BIGINT — Foreign key → seller.seller_id — NOT NULL — 해당 상품의 판매자 ID
  • item_name — VARCHAR(200) — NOT NULL — 주문 시점 상품+옵션명 (스냅샷)
  • quantity — INT — NOT NULL — 구매 수량
  • price — BIGINT — NOT NULL — 주문 시점 가격 (스냅샷)
  • status — VARCHAR(20) — NOT NULL — ORDERED / CONFIRMED / REJECTED / DELIVERED / CANCELLED
  • created_at — DATETIME — NOT NULL

order_items

  • order_item_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key — NOT NULL
  • item_id — BIGINT — Foreign key — NOT NULL
  • seller_id — BIGINT — Foreign key — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • quantity — INT — NOT NULL
  • price — BIGINT — NOT NULL
  • thumbnail_url — VARCHAR(500)
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

orders

  • order_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 구매자 ID
  • total_price — BIGINT — NOT NULL — 총 주문 금액
  • discount_price — BIGINT — 총 할인 금액
  • final_price — BIGINT — NOT NULL — 최종 결제 금액
  • user_coupon_id — BIGINT — Foreign key → user_coupon.user_coupon_id — 적용한 사용자 쿠폰 ID
  • used_point — INT — 사용한 포인트
  • status — VARCHAR(20) — NOT NULL — PENDING_PAYMENT / PAID / CANCELLED
  • receiver_name — VARCHAR(50) — NOT NULL — 수령인 이름
  • receiver_phone — VARCHAR(20) — NOT NULL — 수령인 연락처
  • receiver_address — VARCHAR(255) — NOT NULL — 배송 주소
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

outbox_event

  • outbox_id — BIGINT — Primary key — NOT NULL
  • event_id — VARCHAR(100) — NOT NULL
  • event_type — VARCHAR(50) — NOT NULL
  • aggregate_type — VARCHAR(50) — NOT NULL
  • aggregate_id — BIGINT — NOT NULL
  • topic — VARCHAR(100) — NOT NULL
  • partition_key — VARCHAR(100) — NOT NULL
  • payload — TEXT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • retry_count — INT — NOT NULL
  • last_error_message — VARCHAR(500)
  • processed_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

payments

  • payment_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key — NOT NULL
  • payment_key — VARCHAR(100) — NOT NULL
  • amount — BIGINT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • method — VARCHAR(20) — NOT NULL
  • approved_at — DATETIME
  • cancelled_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

point

  • point_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 소유자 ID (1:1)
  • balance — INT — NOT NULL — 현재 잔여 포인트
  • version — INT — 낙관적 락 버전
  • updated_at — DATETIME — NOT NULL

point_history

  • history_id — BIGINT
  • point_id — BIGINT — Foreign key → point.point_id — NOT NULL — 포인트 ID
  • amount — INT — NOT NULL — 변동액 (+적립 / -사용)
  • type — VARCHAR(20) — NOT NULL — EARN / USE / EXPIRE / REFUND
  • description — VARCHAR(255) — 설명
  • expire_at — DATE — 적립 포인트 만료일 (+1년)
  • created_at — DATETIME — NOT NULL

point_usage_detail

  • usage_detail_id — BIGINT — Primary key — NOT NULL
  • use_history_id — BIGINT — Foreign key — NOT NULL
  • source_history_id — BIGINT — Foreign key — NOT NULL
  • used_amount — INT — NOT NULL
  • source_expire_at — DATE — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

points

  • point_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key — NOT NULL
  • balance — INT — NOT NULL
  • version — INT — NOT NULL
  • integrity_hash — VARCHAR(64) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product

  • product_id — BIGINT
  • seller_id — BIGINT — Foreign key → seller.seller_id — NOT NULL — 등록 판매자 ID
  • category_id — BIGINT — Foreign key → category.category_id — NOT NULL — 소속 카테고리 ID
  • name — VARCHAR(200) — NOT NULL — 상품명
  • description — TEXT — 상품 설명
  • thumbnail_url — VARCHAR(500) — 대표 이미지 URL
  • status — VARCHAR(30) — NOT NULL — TEMP_SAVED / APPROVE_REQUESTED / APPROVED / REJECTED / DISCONTINUED
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_category_mapping

  • mapping_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key — NOT NULL
  • category_id — BIGINT — Foreign key — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_image

  • image_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • display_order — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_item

  • item_id — BIGINT
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL — 소속 상품 ID
  • option_name — VARCHAR(100) — NOT NULL — 옵션명 (예: 블랙 / XL)
  • price — BIGINT — NOT NULL — 옵션 가격
  • stock — BIGINT — NOT NULL — 잔여 재고 (비관적 락)
  • status — VARCHAR(20) — NOT NULL — ON_SALE / STOP
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_review_summary

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key — NOT NULL
  • pros — TEXT — NOT NULL
  • cons — TEXT — NOT NULL
  • keywords — TEXT — NOT NULL
  • sentiment — VARCHAR(255) — NOT NULL
  • last_included_review_id — BIGINT
  • review_count — BIGINT — NOT NULL
  • average_rating — DOUBLE — NOT NULL
  • version — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

product_tag

  • tag — VARCHAR(30) — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key — NOT NULL

related_product

  • relation_id — BIGINT — Primary key — NOT NULL
  • base_product_id — BIGINT — Foreign key — NOT NULL
  • target_product_id — BIGINT — Foreign key — NOT NULL
  • relation_weight — DOUBLE — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

review

  • review_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 작성자 ID
  • order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL — 1주문상품 1리뷰 (1:1)
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL — 상품 ID
  • rating — TINYINT — NOT NULL — 별점 (1~5)
  • content — TEXT — 리뷰 내용
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

review_image

  • image_id — BIGINT
  • review_id — BIGINT — Foreign key → review.review_id — NOT NULL — 리뷰 ID
  • image_url — VARCHAR(500) — NOT NULL — 이미지 S3 URL

review_summary

  • summary_id — BIGINT
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL — 대상 상품 ID (1:1)
  • summary — TEXT — 전체 요약 텍스트
  • pros — TEXT — 장점 목록 (JSON)
  • cons — TEXT — 단점 목록 (JSON)
  • keywords — TEXT — 핵심 키워드 (JSON)
  • sentiment — VARCHAR(20) — POSITIVE / NEUTRAL / NEGATIVE
  • updated_at — DATETIME — NOT NULL

search_history

  • search_id — BIGINT — Primary key — NOT NULL
  • event_id — VARCHAR(36)
  • keyword — VARCHAR(100) — NOT NULL
  • user_id — BIGINT — Foreign key
  • searched_at — DATETIME — NOT NULL

security_audit_log

  • id — BIGINT — Primary key — NOT NULL
  • event_type — VARCHAR(50) — NOT NULL
  • severity — VARCHAR(10) — NOT NULL
  • category — VARCHAR(10) — NOT NULL
  • actor_user_id — BIGINT
  • actor_email — VARCHAR(100)
  • actor_role — VARCHAR(20)
  • target_resource — VARCHAR(30)
  • target_id — VARCHAR(50)
  • result — VARCHAR(10) — NOT NULL
  • error_code — VARCHAR(30)
  • error_message — VARCHAR(500)
  • method_name — VARCHAR(200)
  • method_args — VARCHAR(500)
  • client_ip — VARCHAR(45)
  • user_agent — VARCHAR(255)
  • request_uri — VARCHAR(255)
  • trace_id — VARCHAR(64)
  • metadata — TEXT
  • occurred_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL

seller

  • seller_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 연결된 user_id (1:1)
  • shop_name — VARCHAR(100) — NOT NULL — 상호명
  • business_number — VARCHAR(20) — NOT NULL — 사업자 등록번호
  • bank_account — VARCHAR(50) — 정산용 계좌번호
  • status — VARCHAR(20) — NOT NULL — PENDING / APPROVED / REJECTED
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

subscription

  • subscription_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 구독자 ID
  • start_at — DATETIME — NOT NULL — 구독 시작일
  • end_at — DATETIME — NOT NULL — 구독 만료일
  • status — VARCHAR(20) — NOT NULL — ACTIVE / EXPIRED / CANCELLED
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_coupon

  • user_coupon_id — BIGINT
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL — 소유자 ID
  • coupon_id — BIGINT — Foreign key → coupon.coupon_id — NOT NULL — 쿠폰 마스터 ID
  • status — VARCHAR(20) — NOT NULL — AVAILABLE / USED / EXPIRED
  • used_at — DATETIME — 사용 일시
  • created_at — DATETIME — NOT NULL

users

  • user_id — BIGINT
  • email — VARCHAR(100) — NOT NULL — 이메일 (로그인 ID)
  • password — VARCHAR(255) — NOT NULL — 비밀번호 (암호화)
  • name — VARCHAR(50) — NOT NULL — 이름
  • phone — VARCHAR(20) — 전화번호
  • address — VARCHAR(255) — 기본 배송지 주소
  • role — VARCHAR(20) — NOT NULL — BUYER / SELLER / ADMIN
  • status — VARCHAR(20) — NOT NULL — ACTIVE / SUSPENDED / WITHDRAWN
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL