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