ERD_update_06/30

Tables (18)

consultation

  • id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • user_id — UUID — Foreign key → user.id — NOT NULL
  • consulted_service_id — UUID — Foreign key → service.id
  • session_no — INT
  • stage — VARCHAR(20) — NOT NULL
  • summary — TEXT
  • source_type — VARCHAR(10) — NOT NULL
  • raw_text — TEXT — NOT NULL
  • visit_purpose — TEXT
  • experience_note — TEXT
  • positive_signal — TEXT
  • extra_note — TEXT
  • ai_parsed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

consultation_signal

  • id — UUID — Primary key — NOT NULL
  • consultation_id — UUID — Foreign key → consultation.id — NOT NULL
  • signal_type — VARCHAR(50) — NOT NULL
  • signal_value — VARCHAR(50) — NOT NULL
  • confidence — VARCHAR(20)
  • evidence_text — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

contact_result

  • id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • follow_up_id — UUID — Foreign key → follow_up.id
  • result_status — VARCHAR(20) — NOT NULL
  • result_date — DATE — NOT NULL
  • memo — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

customer

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • registered_service_id — UUID — Foreign key → service.id
  • name — VARCHAR(50) — NOT NULL
  • gender — VARCHAR(10)
  • birth_date — DATE
  • phone_num — VARCHAR(20)
  • preferred_contact_channel — VARCHAR(30)
  • inflow_path — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • first_consult_at — DATE — NOT NULL
  • latest_consult_at — DATE — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

customer_activity_timeline

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • actor_user_id — UUID — Foreign key → user.id — NOT NULL
  • activity_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • related_type — VARCHAR(50)
  • related_id — UUID
  • before_value — JSONB
  • after_value — JSONB
  • occurred_at — TIMESTAMPTZ — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

customer_ai_insight

  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • lead_temperature — VARCHAR(20)
  • temperature_basis — TEXT
  • priority_score — INT
  • analyzed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

event

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • service_id — UUID — Foreign key → service.id
  • title — VARCHAR(200) — NOT NULL
  • event_type — VARCHAR(30) — NOT NULL
  • description — TEXT
  • discount_rate — DECIMAL(5,2)
  • img_url — VARCHAR(255)
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

event_target

  • id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • event_id — UUID — Foreign key → event.id — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

follow_up

  • id — UUID — Primary key — NOT NULL
  • consultation_id — UUID — Foreign key → consultation.id — NOT NULL
  • recommend_contact_date — DATE — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • snoozed_until — DATE
  • memo — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

follow_up_ai_insight

  • follow_up_id — UUID — Foreign key → follow_up.id — NOT NULL
  • persuasion_point — JSONB — NOT NULL
  • caution_note — TEXT
  • action_basis — JSONB — NOT NULL
  • analyzed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

interest_service

  • id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • service_id — UUID — Foreign key → service.id — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

message_template

  • id — UUID — Primary key — NOT NULL
  • follow_up_id — UUID — Foreign key → follow_up.id
  • event_target_id — UUID — Foreign key → event_target.id
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • message_id — VARCHAR(100)
  • content — TEXT — NOT NULL
  • version_type — VARCHAR(20) — NOT NULL
  • tone_preset — VARCHAR(20) — NOT NULL
  • delivery_status — VARCHAR(20)
  • scheduled_at — TIMESTAMPTZ — NOT NULL
  • generated_at — TIMESTAMPTZ — NOT NULL
  • sent_at — TIMESTAMPTZ
  • delivered_at — TIMESTAMPTZ
  • updated_at — TIMESTAMPTZ — NOT NULL

non_conversion_reason

  • id — UUID — Primary key — NOT NULL
  • customer_id — UUID — Foreign key → customer.id — NOT NULL
  • consultation_id — UUID — Foreign key → consultation.id
  • reason_type — VARCHAR(50) — NOT NULL
  • role — VARCHAR(20)
  • reason_basis — TEXT
  • confidence — VARCHAR(20)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

schedule

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • customer_id — UUID — Foreign key → customer.id
  • consultation_id — UUID — Foreign key → consultation.id
  • title — VARCHAR(200) — NOT NULL
  • schedule_type — VARCHAR(30) — NOT NULL
  • start_at — TIMESTAMPTZ — NOT NULL
  • end_at — TIMESTAMPTZ — NOT NULL
  • memo — TEXT
  • created_by — UUID — Foreign key → user.id — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

service

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • price — DECIMAL(10,2)
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

store

  • id — UUID — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • store_type — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

task_checklist

  • id — UUID — Primary key — NOT NULL
  • schedule_id — UUID — Foreign key → schedule.id
  • customer_id — UUID — Foreign key → customer.id
  • title — VARCHAR(200) — NOT NULL
  • task_type — VARCHAR(30) — NOT NULL
  • due_date — DATE
  • is_done — BOOLEAN — NOT NULL
  • done_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

user

  • id — UUID — Primary key — NOT NULL
  • store_id — UUID — Foreign key → store.id — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • role — VARCHAR(30) — NOT NULL
  • password — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL