Lirouti test erd

Tables (13)

consumable_category

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • default_usage_days — INT
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

consumable_purchase_log

  • id — BIGINT — Primary key — NOT NULL
  • member_consumable_id — BIGINT — Foreign key → member_consumable.id — NOT NULL
  • member_order_id — BIGINT — Foreign key → member_order.id
  • product_id — BIGINT — Foreign key → product.id
  • purchase_type — VARCHAR(50) — NOT NULL
  • purchased_date — DATE — NOT NULL
  • quantity — INT
  • memo — VARCHAR(255)
  • created_at — DATETIME(6) — NOT NULL

housework_template

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(255)
  • default_cycle_days — INT
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

member

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • provider — VARCHAR(50)
  • social_id — VARCHAR(50)
  • role — VARCHAR(50) — NOT NULL
  • onboarding_completed — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_consumable

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • consumable_category_id — BIGINT — Foreign key → consumable_category.id
  • name — VARCHAR(100) — NOT NULL
  • last_purchase_date — DATE
  • average_usage_days — INT — NOT NULL
  • expected_depletion_date — DATE
  • notification_enabled — TINYINT(1) — NOT NULL
  • subscription_enabled — TINYINT(1) — NOT NULL
  • auto_delivery_enabled — TINYINT(1) — NOT NULL
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_housework_completion_log

  • id — BIGINT — Primary key — NOT NULL
  • member_housework_routine_id — BIGINT — Foreign key → member_housework_routine.id — NOT NULL
  • completed_date — DATE — NOT NULL
  • completed_at — DATETIME(6) — NOT NULL
  • canceled_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL

member_housework_routine

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • housework_template_id — BIGINT — Foreign key → housework_template.id
  • name — VARCHAR(100) — NOT NULL
  • last_performed_date — DATE
  • cycle_days — INT — NOT NULL
  • next_due_date — DATE
  • notification_enabled — TINYINT(1) — NOT NULL
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_order

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • member_consumable_id — BIGINT — Foreign key → member_consumable.id
  • order_number — VARCHAR(100) — NOT NULL
  • order_status — VARCHAR(50) — NOT NULL
  • order_type — VARCHAR(50) — NOT NULL
  • total_amount — INT — NOT NULL
  • receiver_name — VARCHAR(50) — NOT NULL
  • receiver_phone — VARCHAR(30) — NOT NULL
  • shipping_address — VARCHAR(255) — NOT NULL
  • ordered_at — DATETIME(6) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_routine

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • member_housework_routine_id — BIGINT — Foreign key → member_housework_routine.id
  • member_consumable_id — BIGINT — Foreign key → member_consumable.id
  • routine_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • next_due_date — DATE
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

notification

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • member_routine_id — BIGINT — Foreign key → member_routine.id
  • notification_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • message — VARCHAR(255) — NOT NULL
  • scheduled_at — DATETIME(6) — NOT NULL
  • sent_at — DATETIME(6)
  • read_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL

order_detail

  • id — BIGINT — Primary key — NOT NULL
  • member_order_id — BIGINT — Foreign key → member_order.id — NOT NULL
  • product_id — BIGINT — Foreign key → product.id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — INT — NOT NULL
  • total_price — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

payment

  • id — BIGINT — Primary key — NOT NULL
  • member_order_id — BIGINT — Foreign key → member_order.id — NOT NULL
  • payment_method — VARCHAR(50) — NOT NULL
  • payment_status — VARCHAR(50) — NOT NULL
  • amount — INT — NOT NULL
  • transaction_id — VARCHAR(100)
  • requested_at — DATETIME(6) — NOT NULL
  • approved_at — DATETIME(6)
  • failed_at — DATETIME(6)
  • fail_reason — VARCHAR(255)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

product

  • id — BIGINT — Primary key — NOT NULL
  • consumable_category_id — BIGINT — Foreign key → consumable_category.id
  • name — VARCHAR(150) — NOT NULL
  • description — TEXT
  • price — INT — NOT NULL
  • stock_quantity — INT — NOT NULL
  • image_url — VARCHAR(500)
  • active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)