kopang

Tables (30)

cart

  • cart_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • created_at — timestamp — NOT NULL

cart_item

  • cart_item_id — bigint — Primary key — NOT NULL
  • cart_id — bigint — Foreign key → cart.cart_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • quantity — integer — NOT NULL
  • added_at — timestamp — NOT NULL
  • demo_tag — varchar(20)

categories

  • category_id — bigint — Primary key — NOT NULL
  • name — varchar(50) — NOT NULL
  • parent_id — bigint — Foreign key → categories.category_id
  • depth — integer — NOT NULL

chatbot_history

  • chat_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • question — text — NOT NULL
  • answer — text
  • created_at — timestamp — NOT NULL

churn_daily_metric

  • metric_date — date — Primary key — NOT NULL
  • total_users — integer — NOT NULL
  • at_risk_high — integer — NOT NULL
  • at_risk_mid — integer — NOT NULL
  • intervention_count — integer — NOT NULL
  • conversion_count — integer — NOT NULL
  • retained_count — integer — NOT NULL
  • attributed_revenue — integer — NOT NULL
  • churn_rate — double

churn_score

  • churn_score_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • churn_score — double — NOT NULL
  • risk_level — varchar(10) — NOT NULL
  • risk_type — varchar(30)
  • model_version — varchar(20)
  • source — varchar(10) — NOT NULL
  • scored_at — timestamp — NOT NULL
  • demo_tag — varchar(20)

coupons

  • coupon_id — bigint — Primary key — NOT NULL
  • name — varchar(100) — NOT NULL
  • discount_type — varchar(20) — NOT NULL
  • discount_value — integer — NOT NULL
  • quantity — integer
  • start_date — date
  • end_date — date

faqs

  • faq_id — bigint — Primary key — NOT NULL
  • category — varchar(50) — NOT NULL
  • question — varchar(255) — NOT NULL
  • answer — text — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

inquiries

  • inquiry_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id
  • title — varchar(200) — NOT NULL
  • content — text — NOT NULL
  • type — varchar(20) — NOT NULL
  • status — varchar(20) — NOT NULL
  • answer — text
  • created_at — timestamp — NOT NULL

intervention_outcome

  • outcome_id — bigint — Primary key — NOT NULL
  • intervention_id — bigint — Foreign key → retention_intervention.intervention_id — NOT NULL
  • order_id — bigint
  • converted — boolean — NOT NULL
  • retained — boolean
  • revenue_amount — integer — NOT NULL
  • window_days — integer — NOT NULL
  • measured_at — timestamp — NOT NULL
  • demo_tag — varchar(20)

membership

  • membership_id — bigint — Primary key — NOT NULL
  • name — varchar(50) — NOT NULL
  • price — integer — NOT NULL
  • discount_rate — integer — NOT NULL
  • description — text

notices

  • notice_id — bigint — Primary key — NOT NULL
  • title — varchar(200) — NOT NULL
  • content — text — NOT NULL
  • created_at — timestamp — NOT NULL

notifications

  • notification_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • type — varchar(20) — NOT NULL
  • message — text — NOT NULL
  • ref_id — bigint
  • is_read — boolean — NOT NULL
  • clicked — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • demo_tag — varchar(20)

orders

  • order_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • total_price — integer — NOT NULL
  • payment_status — varchar(30) — NOT NULL
  • order_status — varchar(30) — NOT NULL
  • payment_key — varchar(200)
  • used_point — integer
  • user_coupon_id — bigint
  • ordered_at — timestamp — NOT NULL

orders_item

  • order_item_id — bigint — Primary key — NOT NULL
  • order_id — bigint — Foreign key → orders.order_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • price — integer — NOT NULL
  • quantity — integer — NOT NULL

point_history

  • point_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • amount — integer — NOT NULL
  • type — varchar(20) — NOT NULL
  • description — text
  • created_at — timestamp — NOT NULL

product_images

  • image_id — bigint — Primary key — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • url — text — NOT NULL

product_view_history

  • view_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • viewed_at — timestamp — NOT NULL

products

  • product_id — bigint — Primary key — NOT NULL
  • category_id — bigint — Foreign key → categories.category_id — NOT NULL
  • name — varchar(200) — NOT NULL
  • price — integer — NOT NULL
  • discount_price — integer
  • stock — integer — NOT NULL
  • status — varchar(20) — NOT NULL
  • image_url — text
  • description — text
  • created_at — timestamp — NOT NULL

recommendation_history

  • recommend_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • order_id — bigint
  • score — double
  • reason — text
  • shown — boolean — NOT NULL
  • clicked — boolean — NOT NULL
  • converted — boolean — NOT NULL
  • created_at — timestamp — NOT NULL

retention_intervention

  • intervention_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • churn_score_id — bigint — Foreign key → churn_score.churn_score_id
  • risk_type — varchar(30) — NOT NULL
  • action_type — varchar(20) — NOT NULL
  • channel — varchar(20)
  • status — varchar(20) — NOT NULL
  • is_control — boolean — NOT NULL
  • ref_id — bigint
  • created_at — timestamp — NOT NULL
  • demo_tag — varchar(20)

reviews

  • review_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • rating — double — NOT NULL
  • content — text
  • image — text
  • created_at — timestamp — NOT NULL

satisfaction_survey

  • survey_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • context — varchar(20) — NOT NULL
  • score — integer — NOT NULL
  • reason — varchar(20)
  • created_at — timestamp — NOT NULL

search_history

  • search_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • keyword — varchar(200) — NOT NULL
  • searched_at — timestamp — NOT NULL

user_address

  • address_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • receiver — varchar(50) — NOT NULL
  • phone — varchar(20)
  • zipcode — varchar(10)
  • address — text — NOT NULL
  • detail_address — text
  • is_default — boolean — NOT NULL

user_behavior_log

  • log_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • action — varchar(50) — NOT NULL
  • target_id — bigint
  • created_at — timestamp — NOT NULL

user_coupons

  • user_coupon_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • coupon_id — bigint — Foreign key → coupons.coupon_id — NOT NULL
  • used — boolean — NOT NULL
  • issued_at — timestamp — NOT NULL
  • used_at — timestamp
  • expires_at — date — NOT NULL
  • issued_by — varchar(20)

user_membership

  • user_membership_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • membership_id — bigint — Foreign key → membership.membership_id — NOT NULL
  • status — varchar(20) — NOT NULL
  • start_date — date — NOT NULL
  • end_date — date
  • cancelled_at — timestamp

users

  • user_id — bigint — Primary key — NOT NULL
  • email — varchar(100) — NOT NULL
  • password — varchar(255) — NOT NULL
  • name — varchar(50) — NOT NULL
  • phone — varchar(20)
  • birth_date — date
  • role — varchar(20) — NOT NULL
  • status — varchar(20) — NOT NULL
  • last_login_at — timestamp
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

wishlist

  • wishlist_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • product_id — bigint — Foreign key → products.product_id — NOT NULL
  • created_at — timestamp — NOT NULL
  • demo_tag — varchar(20)