DOA Market Database

Tables (30)

admin_logs

  • id — uuid
  • admin_id — uuid — Foreign key → users.id
  • action — varchar(100)
  • resource_type — varchar(50)
  • resource_id — varchar(255)
  • details — jsonb
  • ip_address — varchar(45)
  • user_agent — text
  • created_at — timestamp

cart_items

  • cart_item_id — uuid
  • cart_id — uuid — Foreign key → carts.cart_id
  • product_id — uuid — Foreign key → products.id
  • quantity — integer
  • options — jsonb
  • created_at — timestamp
  • updated_at — timestamp

carts

  • cart_id — uuid
  • user_id — uuid — Foreign key → users.id
  • created_at — timestamp
  • updated_at — timestamp

categories

  • id — uuid
  • name — varchar(100)
  • slug — varchar(255)
  • description — text
  • image_url — varchar(500)
  • display_order — integer
  • is_active — boolean
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp
  • mpath — varchar(255)

coupons

  • coupon_id — uuid
  • code — varchar(50)
  • name — varchar(255)
  • description — text
  • discount_type — varchar(20)
  • discount_value — decimal(10,2)
  • min_purchase_amount — decimal(10,2)
  • max_discount_amount — decimal(10,2)
  • status — varchar(20)
  • usage_limit — integer
  • usage_count — integer
  • per_user_limit — integer
  • starts_at — timestamp
  • expires_at — timestamp
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp

inquiries

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • type — varchar(20)
  • title — varchar(200)
  • content — text
  • status — varchar(20)
  • priority — varchar(20)
  • order_id — uuid — Foreign key → orders.order_id
  • product_id — uuid — Foreign key → products.id
  • images — jsonb
  • assigned_to — uuid — Foreign key → users.id
  • answered_at — timestamp
  • closed_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp

inquiry_responses

  • id — uuid
  • inquiry_id — uuid — Foreign key → inquiries.id
  • user_id — uuid — Foreign key → users.id
  • is_admin — boolean
  • content — text
  • images — jsonb
  • is_internal — boolean
  • created_at — timestamp
  • updated_at — timestamp

notifications

  • notification_id — uuid
  • user_id — uuid — Foreign key → users.id
  • type — varchar(20)
  • title — varchar(255)
  • message — text
  • is_read — boolean
  • metadata — jsonb
  • created_at — timestamp

order_items

  • order_item_id — uuid
  • order_id — uuid — Foreign key → orders.order_id
  • product_id — uuid — Foreign key → products.id
  • product_name — varchar(255)
  • product_image_url — text
  • sku — varchar(100)
  • quantity — integer
  • unit_price — decimal(10,2)
  • discount_amount — decimal(10,2)
  • total_price — decimal(10,2)
  • options — jsonb
  • created_at — timestamp
  • updated_at — timestamp

orders

  • order_id — uuid
  • user_id — uuid — Foreign key → users.id
  • seller_id — uuid — Foreign key → sellers.seller_id
  • status — varchar(20)
  • payment_status — varchar(20)
  • subtotal — decimal(10,2)
  • shipping_fee — decimal(10,2)
  • discount_amount — decimal(10,2)
  • total_amount — decimal(10,2)
  • recipient_name — varchar(100)
  • recipient_phone — varchar(20)
  • shipping_address_line1 — varchar(255)
  • shipping_address_line2 — varchar(255)
  • shipping_city — varchar(100)
  • shipping_state — varchar(100)
  • shipping_postal_code — varchar(20)
  • shipping_country — varchar(2)
  • tracking_number — varchar(100)
  • courier_company — varchar(100)
  • shipped_at — timestamp
  • delivered_at — timestamp
  • payment_method — varchar(50)
  • payment_transaction_id — varchar(255)
  • paid_at — timestamp
  • order_notes — text
  • cancel_reason — text
  • cancelled_at — timestamp
  • refunded_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp

payments

  • payment_id — uuid
  • order_id — uuid — Foreign key → orders.order_id
  • user_id — uuid — Foreign key → users.id
  • payment_method — varchar(50)
  • status — varchar(20)
  • amount — decimal(10,2)
  • currency — varchar(3)
  • pg_provider — varchar(50)
  • pg_transaction_id — varchar(255)
  • pg_response — jsonb
  • card_number — varchar(20)
  • card_type — varchar(20)
  • card_issuer — varchar(50)
  • paid_at — timestamp
  • cancelled_at — timestamp
  • cancel_reason — text
  • refunded_at — timestamp
  • refund_amount — decimal(10,2)
  • refund_reason — text
  • receipt_url — text
  • failure_reason — text
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp

point_transactions

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • type — varchar(20)
  • source — varchar(20)
  • amount — integer
  • balance — integer
  • reference_id — varchar(255)
  • description — text
  • expires_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp

product_images

  • id — uuid
  • product_id — uuid — Foreign key → products.id
  • image_url — varchar(500)
  • thumbnail_url — varchar(500)
  • display_order — integer
  • is_primary — boolean
  • alt_text — varchar(255)
  • created_at — timestamp

product_qna

  • id — uuid
  • product_id — uuid — Foreign key → products.id
  • user_id — uuid — Foreign key → users.id
  • seller_id — uuid — Foreign key → sellers.seller_id
  • title — varchar(200)
  • question — text
  • answer — text
  • status — varchar(20)
  • is_secret — boolean
  • answered_at — timestamp
  • answered_by — uuid — Foreign key → users.id
  • helpful_count — integer
  • images — jsonb
  • answer_images — jsonb
  • created_at — timestamp
  • updated_at — timestamp

product_variants

  • id — uuid
  • product_id — uuid — Foreign key → products.id
  • sku — varchar(100)
  • name — varchar(255)
  • options — jsonb
  • price — decimal(15,2)
  • stock_quantity — integer
  • image_url — varchar(500)
  • is_active — boolean
  • created_at — timestamp
  • updated_at — timestamp

products

  • id — uuid
  • seller_id — uuid — Foreign key → sellers.seller_id
  • category_id — uuid — Foreign key → categories.id
  • name — varchar(255)
  • slug — varchar(255)
  • description — text
  • price — decimal(15,2)
  • original_price — decimal(15,2)
  • discount_rate — decimal(5,2)
  • status — varchar(20)
  • stock_quantity — integer
  • rating_avg — decimal(3,2)
  • review_count — integer
  • sales_count — integer
  • view_count — integer
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp
  • deleted_at — timestamp

qna_helpful

  • id — uuid
  • qna_id — uuid — Foreign key → product_qna.id
  • user_id — uuid — Foreign key → users.id
  • created_at — timestamp

recently_viewed

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • product_id — uuid — Foreign key → products.id
  • viewed_at — timestamp
  • view_count — integer
  • product_name — varchar(255)
  • product_price — integer
  • product_image — varchar(500)
  • seller_name — varchar(100)
  • created_at — timestamp
  • updated_at — timestamp

refresh_tokens

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • token — text
  • expires_at — timestamp
  • created_at — timestamp

reviews

  • review_id — uuid
  • product_id — uuid — Foreign key → products.id
  • user_id — uuid — Foreign key → users.id
  • order_id — uuid — Foreign key → orders.order_id
  • rating — integer
  • comment — text — ex
  • images — json
  • created_at — timestamp

sellers

  • seller_id — uuid
  • user_id — uuid — Foreign key → users.id
  • type — varchar(20)
  • business_name — varchar(255)
  • business_number — varchar(50)
  • representative_name — varchar(100)
  • business_address — varchar(500)
  • business_phone — varchar(20)
  • business_email — varchar(255)
  • description — text
  • logo_url — varchar(500)
  • banner_url — varchar(500)
  • status — varchar(20)
  • commission_rate — decimal(5,2)
  • bank_name — varchar(100)
  • bank_account — varchar(100)
  • account_holder — varchar(100)
  • total_sales — decimal(15,2)
  • total_products — integer
  • rating_average — decimal(3,2)
  • rating_count — integer
  • approved_at — timestamp
  • approved_by — uuid — Foreign key → users.id
  • rejected_at — timestamp
  • rejected_reason — text
  • settings — jsonb
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp

shipping_addresses

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • recipient_name — varchar(100)
  • recipient_phone — varchar(20)
  • postal_code — varchar(10)
  • address_line1 — varchar(255)
  • address_line2 — varchar(255)
  • city — varchar(100)
  • district — varchar(100)
  • neighborhood — varchar(100)
  • country — varchar(100)
  • is_default — boolean
  • address_type — varchar(20)
  • address_label — varchar(50)
  • delivery_request — text
  • entrance_password — varchar(50)
  • is_verified — boolean
  • last_used_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp

shippings

  • shipping_id — uuid
  • order_id — uuid — Foreign key → orders.order_id
  • user_id — uuid — Foreign key → users.id
  • tracking_number — varchar(100)
  • carrier — varchar(50)
  • shipping_method — varchar(20)
  • status — varchar(20)
  • recipient_name — varchar(100)
  • recipient_phone — varchar(20)
  • address — text
  • postal_code — varchar(20)
  • city — varchar(100)
  • country — varchar(100)
  • shipping_cost — decimal(10,2)
  • estimated_delivery — timestamp
  • actual_delivery — timestamp
  • shipped_at — timestamp
  • delivery_notes — text
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp

tier_history

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • from_tier — varchar(20)
  • to_tier — varchar(20)
  • reason — text
  • purchase_amount_at_change — bigint
  • created_at — timestamp

user_activity

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • activity_type — varchar(50)
  • description — text
  • metadata — jsonb
  • created_at — timestamp

user_points

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • total_points — integer
  • available_points — integer
  • used_points — integer
  • expired_points — integer
  • created_at — timestamp
  • updated_at — timestamp

user_profiles

  • user_id — uuid — Foreign key → users.id
  • display_name — varchar(100)
  • bio — text
  • avatar_url — varchar(500)
  • date_of_birth — date
  • gender — varchar(20)
  • nationality — varchar(20)
  • occupation — varchar(100)
  • company — varchar(255)
  • website — varchar(500)
  • address_line1 — varchar(255)
  • address_line2 — varchar(255)
  • city — varchar(100)
  • state — varchar(100)
  • postal_code — varchar(20)
  • country — varchar(100)
  • shipping_address_line1 — varchar(255)
  • shipping_address_line2 — varchar(255)
  • shipping_city — varchar(100)
  • shipping_state — varchar(100)
  • shipping_postal_code — varchar(20)
  • shipping_country — varchar(100)
  • preferences — jsonb
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp

user_tiers

  • id — uuid
  • user_id — uuid — Foreign key → users.id
  • tier — varchar(20)
  • total_purchase_amount — bigint
  • total_order_count — integer
  • total_review_count — integer
  • tier_points — integer
  • next_tier — varchar(20)
  • next_tier_progress — integer
  • tier_benefits — jsonb
  • achieved_at — timestamp
  • review_period_start — timestamp
  • created_at — timestamp
  • updated_at — timestamp

users

  • id — uuid
  • email — varchar(255)
  • phone_number — varchar(20)
  • password — varchar(255)
  • name — varchar(100)
  • role — varchar(20)
  • status — varchar(20)
  • email_verified — boolean
  • phone_verified — boolean
  • last_login_at — timestamp
  • last_login_ip — varchar(45)
  • failed_login_attempts — integer
  • locked_until — timestamp
  • metadata — jsonb
  • created_at — timestamp
  • updated_at — timestamp
  • deleted_at — timestamp

wishlists

  • wishlist_id — uuid
  • user_id — uuid — Foreign key → users.id
  • product_id — uuid — Foreign key → products.id
  • metadata — jsonb
  • created_at — timestamp