12345

Tables (24)

admin_logs

  • id — BIGINT — Primary key — NOT NULL
  • admin_id — BIGINT — Foreign key → users.id — NOT NULL
  • action — ENUM — NOT NULL
  • target_table — VARCHAR(100) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • reason — TEXT
  • created_at — DATETIME — NOT NULL

ai_review_summaries

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • summary — TEXT — NOT NULL
  • review_count — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL — 최소 10개 리뷰 충족 시 TRUE
  • generated_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

cart_items

  • id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → carts.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • quantity — INT — NOT NULL
  • price_snapshot — DECIMAL(10, 0) — NOT NULL
  • is_price_changed — BOOLEAN — NOT NULL — FR-37 가격 변동 안내용
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

carts

  • id — BIGINT — Primary key — NOT NULL
  • buyer_id — BIGINT — Foreign key → users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

chatbot_faqs

  • id — BIGINT — Primary key — NOT NULL
  • question — VARCHAR(500) — NOT NULL
  • answer — TEXT — NOT NULL
  • created_by — BIGINT — Foreign key → users.id — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

chatbot_logs

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id
  • question — TEXT — NOT NULL
  • answer — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

inventory

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • stock_quantity — INT — NOT NULL — 전체 재고수량 합산 캐시 | inventory_batches.stock_quantity SUM으로 관리
  • updated_at — DATETIME — NOT NULL

inventory_batches

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • harvest_date — DATE — NOT NULL — 수확일자 | 배치 식별 기준
  • expiry_date — DATE — NOT NULL — 배치별 유통기한 | SR-21 임박 알림 기준
  • stock_quantity — INT — NOT NULL — 해당 배치 재고수량
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

notifications

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • type — VARCHAR(50) — NOT NULL — FR-21 할인알림, FR-23 구매확정 등
  • message — VARCHAR(500) — NOT NULL
  • target_url — VARCHAR(500)
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

order_items

  • id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • quantity — INT — NOT NULL
  • price — DECIMAL(10, 0) — NOT NULL
  • shipping_fee — DECIMAL(10, 0) — NOT NULL
  • status — ENUM — NOT NULL
  • confirmed_at — DATETIME — FR-23 | 포인트 적립 및 정산 트리거 기준
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

orders

  • id — BIGINT — Primary key — NOT NULL
  • order_number — VARCHAR(100) — NOT NULL
  • buyer_id — BIGINT — Foreign key → users.id — NOT NULL
  • shipping_address — VARCHAR(500) — NOT NULL
  • total_amount — DECIMAL(10, 0) — NOT NULL
  • total_shipping_fee — DECIMAL(10, 0) — NOT NULL
  • payment_method — ENUM — NOT NULL
  • delivery_type — ENUM — NOT NULL
  • pickup_location_id — BIGINT — Foreign key → pickup_locations.id
  • tracking_number — VARCHAR(100)
  • courier_name — VARCHAR(100)
  • status — ENUM — NOT NULL
  • delivered_at — DATETIME — FR-23 구매확정 트리거 기준 | 자동확정 스케줄러용
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

pickup_locations

  • id — BIGINT — Primary key — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • operating_hours — VARCHAR(200)
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

point_histories

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_items.id
  • type — ENUM('earn', 'use', 'expire') — NOT NULL
  • amount — INT — NOT NULL — 양수: 적립(earn) | 음수: 사용(use)·소멸(expire)
  • balance — INT — NOT NULL — 변동 후 잔액
  • description — VARCHAR(200)
  • created_at — DATETIME — NOT NULL

posts

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — 자유게시판 NULL 허용 | 구매후기 카테고리는 필수
  • category — ENUM — NOT NULL — notice: 관리자 전용
  • title — VARCHAR(300) — NOT NULL
  • content — TEXT — NOT NULL
  • view_count — INT — NOT NULL
  • is_pinned — BOOLEAN — NOT NULL — FR-45 공지사항 상단 고정
  • is_deleted — 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
  • is_main — BOOLEAN — NOT NULL
  • sort_order — TINYINT — NOT NULL
  • created_at — DATETIME — NOT NULL

product_recommendations

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — 비로그인 NULL 허용
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • reason — ENUM('purchase_history', 'popular') — NOT NULL — FR-16: purchase_history | FR-20: popular
  • score — DECIMAL(5, 4) — NOT NULL
  • batch_date — DATE — NOT NULL — 배치 실행 날짜 | 최신 batch_date 기준으로 조회
  • generated_at — DATETIME — NOT NULL

products

  • id — BIGINT — Primary key — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • category — VARCHAR(100) — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • description — TEXT
  • price — DECIMAL(10, 0) — NOT NULL
  • discount_price — DECIMAL(10, 0)
  • discount_start_at — DATETIME
  • discount_end_at — DATETIME
  • thumbnail_url — VARCHAR(500)
  • origin — VARCHAR(200) — NOT NULL
  • defect_level — ENUM — NOT NULL — high: 흠집 심함, medium: 보통, low: 경미
  • defect_description — TEXT
  • view_count — INT — NOT NULL
  • status — ENUM — NOT NULL — hidden: 판매취소(주문있음), cancelled: 판매취소(주문없음)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

review_images

  • id — BIGINT — Primary key — NOT NULL
  • review_id — BIGINT — Foreign key → reviews.id — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • sort_order — TINYINT — NOT NULL
  • created_at — DATETIME — NOT NULL

reviews

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_items.id — NOT NULL
  • rating — TINYINT — NOT NULL — CHECK 1~5
  • content — TEXT — NOT NULL
  • is_analyzed — BOOLEAN — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

sales_rankings

  • id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • period_type — ENUM('daily','weekly') — NOT NULL
  • period_date — DATE — NOT NULL
  • sales_count — INT — NOT NULL
  • sales_amount — DECIMAL(12,0) — NOT NULL
  • rank — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

seller_profiles

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • business_number — VARCHAR(20) — NOT NULL
  • bank_account — VARCHAR(100) — NOT NULL
  • farm_name — VARCHAR(200)
  • farm_location — VARCHAR(500)
  • farm_description — TEXT
  • farm_photo_url — VARCHAR(500)
  • farm_philosophy — TEXT
  • rating — DECIMAL(3, 2) — NOT NULL — reviews 테이블 기반 집계값
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

settlements

  • id — BIGINT — Primary key — NOT NULL
  • seller_id — BIGINT — Foreign key → users.id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_items.id — NOT NULL
  • sale_amount — DECIMAL(10,0) — NOT NULL
  • commission — DECIMAL(10,0) — NOT NULL
  • settlement_amount — DECIMAL(10,0) — NOT NULL
  • status — ENUM — NOT NULL
  • settled_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • phone — VARCHAR(20)
  • role — ENUM — NOT NULL
  • point_balance — INT — NOT NULL
  • push_agree — BOOLEAN — NOT NULL
  • email_agree — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • deleted_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

verification_tokens

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — 비회원 NULL 허용
  • target — VARCHAR(255) — NOT NULL — 인증 대상 | 이메일/전화번호
  • token — VARCHAR(100) — NOT NULL
  • purpose — ENUM — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • used_at — DATETIME
  • created_at — DATETIME — NOT NULL