CHATTIE

Tables (42)

ai_agent_data

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • business_name — varchar(255) — NOT NULL
  • business_type — varchar(255) — NOT NULL
  • business_description — text — NOT NULL
  • products_services — text — NOT NULL
  • target_audience — text — NOT NULL
  • business_hours — varchar(500) — NOT NULL
  • contact_information — text — NOT NULL
  • special_offers — text — NOT NULL
  • policies — text — NOT NULL
  • inventory_info — text — NOT NULL
  • pdf_knowledge — text — NOT NULL
  • pdf_file_name — varchar(500) — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

blog_categories

  • id — serial — Primary key
  • name — varchar(100) — NOT NULL
  • slug — varchar(120) — NOT NULL
  • description — text — NOT NULL
  • color — varchar(7) — NOT NULL
  • is_active — boolean — NOT NULL
  • post_count — integer — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

blog_posts

  • id — serial — Primary key
  • title — varchar(200) — NOT NULL
  • slug — varchar(220) — NOT NULL
  • excerpt — text — NOT NULL
  • content — text — NOT NULL
  • featured_image — varchar(500) — NOT NULL
  • meta_title — varchar(200) — NOT NULL
  • meta_description — varchar(300) — NOT NULL
  • author_id — serial — Foreign key → users.id
  • category_id — serial — Foreign key → blog_categories.id
  • status — varchar(20) — NOT NULL
  • published_at — timestamp
  • view_count — integer — NOT NULL
  • like_count — integer — NOT NULL
  • is_featured — boolean — NOT NULL
  • tags — varchar(500) — NOT NULL
  • reading_time — integer — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

blog_settings

  • id — serial — Primary key
  • blog_enabled — boolean — NOT NULL
  • blog_title — varchar(100) — NOT NULL
  • blog_description — varchar(300) — NOT NULL
  • posts_per_page — integer — NOT NULL
  • allow_comments — boolean — NOT NULL
  • auto_approve_comments — boolean — NOT NULL
  • require_approval — boolean — NOT NULL
  • allow_member_posts — boolean — NOT NULL
  • featured_posts_count — integer — NOT NULL
  • seo_enabled — boolean — NOT NULL
  • social_sharing_enabled — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

chat_attachments

  • id — serial — Primary key
  • message_id — serial — Foreign key → chat_messages.id
  • conversation_id — serial — Foreign key → chat_conversations.id
  • file_name — varchar(255) — NOT NULL
  • original_name — varchar(255) — NOT NULL
  • file_type — varchar(100) — NOT NULL
  • file_size — integer — NOT NULL
  • file_path — varchar(500) — NOT NULL
  • uploaded_by — varchar — NOT NULL
  • created_at — timestamp — NOT NULL

chat_conversations

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • visitor_id — varchar — NOT NULL
  • visitor_ip — varchar(45) — NOT NULL
  • asn — varchar(20) — NOT NULL
  • as_name — varchar(255) — NOT NULL
  • as_domain — varchar(255) — NOT NULL
  • country_code — varchar(2) — NOT NULL
  • country — varchar(100) — NOT NULL
  • continent_code — varchar(2) — NOT NULL
  • continent — varchar(50) — NOT NULL
  • last_message_at — timestamp — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

chat_flows

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • name — varchar(255) — NOT NULL
  • description — text — NOT NULL
  • is_active — boolean — NOT NULL
  • priority — integer — NOT NULL
  • frequency_type — varchar(20) — NOT NULL
  • trigger_operator — varchar(10) — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

chat_messages

  • id — serial — Primary key
  • conversation_id — serial — Foreign key → chat_conversations.id
  • message — text — NOT NULL
  • type — varchar — NOT NULL
  • read — boolean — NOT NULL
  • timestamp — timestamp — NOT NULL
  • browser — varchar(255) — NOT NULL
  • country — varchar(255) — NOT NULL
  • attachment_id — serial — Foreign key → chat_attachments.id

collected_emails

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • popup_id — serial — Foreign key → website_popups.id
  • website_id — serial — Foreign key → websites.id
  • path_id — serial — Foreign key → website_paths.id
  • email — varchar — NOT NULL
  • name — varchar
  • status — varchar(20) — NOT NULL
  • source_url — varchar
  • ip_address — varchar
  • user_agent — varchar
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL
  • unsubscribed_at — timestamp

contact_faqs

  • id — serial — Primary key
  • question — varchar(500) — NOT NULL
  • answer — text — NOT NULL
  • order_index — integer — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

contact_settings

  • id — serial — Primary key
  • support_email — varchar(255) — NOT NULL
  • support_phone — varchar(50) — NOT NULL
  • live_chat_hours — varchar(255) — NOT NULL
  • hero_title_line1 — varchar(255) — NOT NULL
  • hero_title_line2 — varchar(255) — NOT NULL
  • hero_description — text — NOT NULL
  • form_section_title — varchar(255) — NOT NULL
  • form_section_description — varchar(500) — NOT NULL
  • faq_section_title — varchar(255) — NOT NULL
  • faq_section_description — varchar(500) — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

contact_stats

  • id — serial — Primary key
  • number — varchar(50) — NOT NULL
  • label — varchar(255) — NOT NULL
  • icon — varchar(10) — NOT NULL
  • order_index — integer — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

dashboard_stats

  • id — serial — Primary key
  • total_websites — integer — NOT NULL
  • total_paths — integer — NOT NULL
  • total_popups — integer — NOT NULL
  • total_conversations — integer — NOT NULL
  • total_ai_responses — integer — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

flow_actions

  • id — serial — Primary key
  • flow_id — serial — Foreign key → chat_flows.id
  • action_type — varchar(50) — NOT NULL
  • action_config — text — NOT NULL
  • delay_ms — integer — NOT NULL
  • order_index — integer — NOT NULL
  • created_at — timestamp — NOT NULL

flow_analytics

  • id — serial — Primary key
  • flow_id — serial — Foreign key → chat_flows.id
  • website_id — serial — Foreign key → websites.id
  • visitor_id — varchar — NOT NULL
  • triggered_at — timestamp — NOT NULL
  • completed — boolean — NOT NULL
  • actions_completed — integer — NOT NULL
  • total_actions — integer — NOT NULL
  • created_at — timestamp — NOT NULL

flow_conditions

  • id — serial — Primary key
  • flow_id — serial — Foreign key → chat_flows.id
  • condition_type — varchar(50) — NOT NULL
  • field_name — varchar(100) — NOT NULL
  • operator — varchar(20) — NOT NULL
  • field_value — varchar(500) — NOT NULL
  • condition_operator — varchar(10) — NOT NULL
  • created_at — timestamp — NOT NULL

flow_triggers

  • id — serial — Primary key
  • flow_id — serial — Foreign key → chat_flows.id
  • trigger_type — varchar(50) — NOT NULL
  • trigger_config — text — NOT NULL
  • created_at — timestamp — NOT NULL

footer_company_links

  • id — serial — Primary key
  • name — varchar(255) — NOT NULL
  • url — varchar(255) — NOT NULL

footer_sitemap_links

  • id — serial — Primary key
  • name — varchar(255) — NOT NULL
  • url — varchar(255) — NOT NULL

invoices

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • invoice_number — varchar(50) — NOT NULL
  • date — timestamp — NOT NULL
  • amount — numeric(10, 2) — NOT NULL
  • status — varchar(20) — NOT NULL
  • created_at — timestamp — NOT NULL

leads

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • user_id — serial — Foreign key → users.id
  • conversation_id — serial — Foreign key → chat_conversations.id
  • sale_alert_id — serial — Foreign key → sale_alerts.id
  • visitor_id — varchar — NOT NULL
  • email — varchar(255) — NOT NULL
  • name — varchar(255) — NOT NULL
  • phone — varchar(50) — NOT NULL
  • company — varchar(255) — NOT NULL
  • lead_source — varchar(50) — NOT NULL
  • lead_status — varchar(20) — NOT NULL
  • product_interest — varchar(500) — NOT NULL
  • estimated_value — numeric(10, 2) — NOT NULL
  • notes — text — NOT NULL
  • last_contact_at — timestamp
  • converted_at — timestamp
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

meta_data

  • id — serial — Primary key
  • logo_url — varchar — NOT NULL
  • dark_logo_url — varchar
  • site_title — varchar — NOT NULL
  • site_description — varchar — NOT NULL
  • site_keywords — varchar — NOT NULL
  • google_analytics_id — varchar — NOT NULL
  • address — varchar — NOT NULL
  • phone — varchar — NOT NULL
  • email — varchar — NOT NULL
  • copyright — varchar — NOT NULL
  • created_at — timestamp — NOT NULL
  • maintenance_mode — boolean — NOT NULL
  • is_initialised — boolean — NOT NULL
  • subscription_enabled — boolean — NOT NULL
  • custom_head_code — text
  • L — text

newsletter_subscriptions

  • id — serial — Primary key
  • email — varchar — NOT NULL
  • subscribed_at — timestamp — NOT NULL

notifications

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • title — varchar(255) — NOT NULL
  • description — text — NOT NULL
  • type — varchar(50) — NOT NULL
  • priority — varchar(20) — NOT NULL
  • read — boolean — NOT NULL
  • action_url — varchar(255)
  • sender_name — varchar(255)
  • sender_avatar — varchar(255)
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

pages

  • id — serial — Primary key
  • name — varchar(255) — NOT NULL
  • content — text — NOT NULL
  • show_in_navbar — boolean — NOT NULL

sale_alerts

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • user_id — serial — Foreign key → users.id
  • conversation_id — serial — Foreign key → chat_conversations.id
  • visitor_id — varchar — NOT NULL
  • alert_type — varchar(50) — NOT NULL
  • confidence_score — numeric(3, 2) — NOT NULL
  • product_mentioned — varchar(500) — NOT NULL
  • conversation_context — text — NOT NULL
  • visitor_message — text — NOT NULL
  • ai_response — text — NOT NULL
  • estimated_value — numeric(10, 2) — NOT NULL
  • priority — varchar(20) — NOT NULL
  • status — varchar(20) — NOT NULL
  • read — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

social_media_links

  • id — serial — Primary key
  • image_url — varchar(255) — NOT NULL
  • name — varchar(255) — NOT NULL
  • url — varchar(255) — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

subscription_limits

  • id — serial — Primary key
  • max_websites — integer — NOT NULL
  • max_paths_per_website — integer — NOT NULL
  • max_popups_per_path — integer — NOT NULL
  • max_chat_conversations — integer — NOT NULL
  • max_ai_responses — integer — NOT NULL
  • max_flow_triggers — integer — NOT NULL
  • allow_advertising — boolean — NOT NULL
  • allow_email_collector — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL
  • subscription_type_id — serial — Foreign key → subscriptions_types.id

subscriptions_features

  • id — serial — Primary key
  • name — varchar(255) — NOT NULL
  • created_at — timestamp — NOT NULL
  • subscription_id — serial — Foreign key → subscriptions_types.id

subscriptions_types

  • id — serial — Primary key
  • name — varchar(255) — NOT NULL
  • status — boolean — NOT NULL
  • price — numeric(10, 2) — NOT NULL
  • yearly_price — numeric(10, 2) — NOT NULL
  • stripe_monthly_link — varchar(255) — NOT NULL
  • stripe_yearly_link — varchar(255) — NOT NULL
  • stripe_monthly_price_id — varchar(255) — NOT NULL
  • stripe_yearly_price_id — varchar(255) — NOT NULL
  • paypal_monthly_plan_id — varchar(255) — NOT NULL
  • paypal_yearly_plan_id — varchar(255) — NOT NULL
  • created_at — timestamp — NOT NULL

ticket_messages

  • id — serial — Primary key
  • ticket_id — serial — Foreign key → user_tickets.ticket_id
  • user_id — serial — Foreign key → users.id
  • subject — varchar — NOT NULL
  • body — varchar — NOT NULL
  • is_admin — boolean — NOT NULL
  • date — timestamp — NOT NULL

user_tickets

  • ticket_id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • is_read — boolean — NOT NULL
  • name — varchar — NOT NULL
  • resolved — boolean — NOT NULL
  • created_at — timestamp — NOT NULL

users

  • id — serial — Primary key
  • name — varchar — NOT NULL
  • email — varchar — NOT NULL
  • imageurl — varchar — NOT NULL
  • role — varchar — NOT NULL
  • subscription — boolean — NOT NULL
  • subscription_ends_at — timestamp
  • number_of_websites — integer — NOT NULL
  • number_of_conversations — integer — NOT NULL
  • number_of_ai_responses — integer — NOT NULL
  • status — varchar — NOT NULL
  • created_at — timestamp — NOT NULL

users_subscriptions

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • subscription_type_id — serial — Foreign key → subscriptions_types.id
  • payment_provider — varchar(20) — NOT NULL
  • stripe_subscription_id — varchar(255)
  • stripe_customer_id — varchar(255)
  • stripe_price_id — varchar(255)
  • paypal_subscription_id — varchar(255)
  • paypal_customer_id — varchar(255)
  • paypal_plan_id — varchar(255)
  • start_date — timestamp — NOT NULL
  • end_date — timestamp — NOT NULL
  • status — varchar(20) — NOT NULL
  • auto_renew — boolean — NOT NULL
  • cancellation_date — timestamp
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

visitor_activities

  • id — serial — Primary key
  • session_id — serial — Foreign key → visitor_sessions.id
  • website_id — serial — Foreign key → websites.id
  • visitor_id — varchar — NOT NULL
  • activity_type — varchar(50) — NOT NULL
  • page_url — varchar(500) — NOT NULL
  • page_title — varchar(255) — NOT NULL
  • time_on_page — integer — NOT NULL
  • metadata — text — NOT NULL
  • created_at — timestamp — NOT NULL

visitor_sessions

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • visitor_id — varchar — NOT NULL
  • session_id — varchar — NOT NULL
  • visitor_ip — varchar(45) — NOT NULL
  • country — varchar(100) — NOT NULL
  • country_code — varchar(2) — NOT NULL
  • city — varchar(100) — NOT NULL
  • browser — varchar(100) — NOT NULL
  • os — varchar(100) — NOT NULL
  • device — varchar(50) — NOT NULL
  • current_page_url — varchar(500) — NOT NULL
  • current_page_title — varchar(255) — NOT NULL
  • referrer_url — varchar(500) — NOT NULL
  • is_active — boolean — NOT NULL
  • last_heartbeat — timestamp — NOT NULL
  • session_start — timestamp — NOT NULL
  • session_end — timestamp
  • total_pages_viewed — integer — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

visitors_ticket_messages

  • id — serial — Primary key
  • email — varchar — NOT NULL
  • name — varchar — NOT NULL
  • subject — varchar — NOT NULL
  • is_read — boolean — NOT NULL
  • body — varchar — NOT NULL
  • date — timestamp — NOT NULL
  • resolved — boolean — NOT NULL

website_paths

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • name — varchar — NOT NULL
  • path — varchar — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

website_popups

  • id — serial — Primary key
  • path_id — serial — Foreign key → website_paths.id
  • order — integer — NOT NULL
  • type — varchar — NOT NULL
  • title — varchar — NOT NULL
  • message — text — NOT NULL
  • icon — varchar — NOT NULL
  • timestamp — varchar — NOT NULL
  • link — varchar
  • button_text — varchar
  • placeholder_text — varchar
  • success_message — varchar
  • delay — integer — NOT NULL
  • duration — varchar — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

websites

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • name — varchar(255) — NOT NULL
  • domain — varchar — NOT NULL
  • favicon — varchar — NOT NULL
  • color — varchar — NOT NULL
  • is_ai_enabled — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

widget_questions

  • id — serial — Primary key
  • website_id — serial — Foreign key → websites.id
  • question — text — NOT NULL
  • order_index — integer — NOT NULL
  • is_active — boolean — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL

widget_settings

  • id — serial — Primary key
  • user_id — serial — Foreign key → users.id
  • primary_color — varchar(7) — NOT NULL
  • header_color — varchar(7) — NOT NULL
  • background_color — varchar(7) — NOT NULL
  • text_color — varchar(7) — NOT NULL
  • button_size — varchar(20) — NOT NULL
  • button_position — varchar(20) — NOT NULL
  • border_radius — varchar(20) — NOT NULL
  • welcome_message — text — NOT NULL
  • placeholder_text — varchar(255) — NOT NULL
  • company_name — varchar(255) — NOT NULL
  • button_text — varchar(255) — NOT NULL
  • button_tooltip_text — varchar(255) — NOT NULL
  • agent_status_text — varchar(255) — NOT NULL
  • online_agent_text — varchar(255) — NOT NULL
  • offline_agent_text — varchar(255) — NOT NULL
  • show_branding — boolean — NOT NULL
  • brand_name — varchar(255) — NOT NULL
  • brand_link — varchar(500) — NOT NULL
  • widget_language — varchar(10) — NOT NULL
  • welcome_title — varchar(255) — NOT NULL
  • faq_title — varchar(255) — NOT NULL
  • start_chat_button_text — varchar(255) — NOT NULL
  • welcome_background_color — varchar(7) — NOT NULL
  • welcome_text_color — varchar(7) — NOT NULL
  • question_button_color — varchar(7) — NOT NULL
  • question_button_text_color — varchar(7) — NOT NULL
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL