ER · Canifa AI Chatbot

Database schema · 21 tables, 6 entity groups

Product is the aggregate root — every search, outfit match, and description traces back to it via internal_ref_code. # marks primary keys, → marks foreign keys.

HAS OUTFIT 1 N HAS DESC 1 1 TRACKED IN N 1 ENTITY · AGGREGATE ROOT Product # internal_ref_code text product_name text product_line_vn text gender_by_product enum sale_price real original_price real style bitmap fitting bitmap master_color text tags json[] similar_items json[] size_scale json[] description_text text vector float[] ENTITY Outfit Matches # id integer → anchor_product_code text match_product_code text match_product_name text match_role enum score integer ai_reason text ENTITY Ultra Descriptions # id integer → internal_ref_code text description_data json description_data_cut json clean_description text tags json[] embedding float[] ENTITY Fashion Rules # id integer gender_target enum anchor_category text occasion text target_category text ai_reason text ENTITY Lead History # id integer device_id text message text ai_response text products json[] lead_stage enum LEGEND Aggregate root (Product · 2,540 rows) Entity # Primary key → Foreign key 1 / N Cardinality

AGGREGATE ROOT

Product is the center

2,540 products with 44 columns. Every outfit match, description, and search query traces back to internal_ref_code. Contains BITMAP fields for style/fitting/season filtering.

3-table search architecture

  • Outfit Matches (124K) — score + ai_reason per pair
  • Ultra Descriptions (1.9K) — AI-generated product copy
  • Fashion Rules (698) — occasion-based pairing logic

suggest_items is deprecated

The suggest_items column in Product is unreliable. Use similar_items for self-join, and Outfit Matches for curated outfit pairing with AI reasoning.

Canifa AI Chatbot · ER data model · canifa_ai_dump.sqlite diagram-design skill