Spaces:
Runtime error
Runtime error
File size: 4,633 Bytes
ac90985 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | -- Customer Agent Database Schema
-- Users table
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email VARCHAR UNIQUE NOT NULL,
name VARCHAR,
hashed_password VARCHAR NOT NULL,
is_active BOOLEAN DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Websites table
CREATE TABLE websites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url VARCHAR NOT NULL,
name VARCHAR NOT NULL,
industry VARCHAR,
tone VARCHAR DEFAULT 'friendly',
is_verified BOOLEAN DEFAULT 0,
owner_id INTEGER NOT NULL,
widget_config JSON,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_scraped DATETIME,
FOREIGN KEY (owner_id) REFERENCES users(id)
);
-- Website content table
CREATE TABLE website_content (
id INTEGER PRIMARY KEY AUTOINCREMENT,
website_id INTEGER NOT NULL,
page_url VARCHAR NOT NULL,
content TEXT NOT NULL,
embedding TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- Chat sessions table
CREATE TABLE chat_sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id VARCHAR(255) UNIQUE NOT NULL,
website_id INTEGER NOT NULL,
ip_address VARCHAR(50),
user_agent TEXT,
visitor_name VARCHAR(255),
visitor_email VARCHAR(255),
country VARCHAR(100),
city VARCHAR(100),
messages JSON,
message_count INTEGER DEFAULT 0,
language_detected VARCHAR(10),
industry VARCHAR(50),
first_query TEXT,
last_query TEXT,
is_active BOOLEAN DEFAULT 1,
ended_reason VARCHAR(100),
avg_response_time INTEGER,
satisfaction_rating INTEGER,
started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_activity_at DATETIME DEFAULT CURRENT_TIMESTAMP,
ended_at DATETIME,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- Chat messages table
CREATE TABLE chat_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id INTEGER NOT NULL,
message TEXT NOT NULL,
is_from_visitor BOOLEAN DEFAULT 1,
is_from_ai BOOLEAN DEFAULT 0,
sender_name VARCHAR,
analysis JSON,
confidence_score FLOAT,
escalated BOOLEAN DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (session_id) REFERENCES chat_sessions(id)
);
-- User settings table
CREATE TABLE user_settings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER UNIQUE NOT NULL,
business_name VARCHAR(255),
business_email VARCHAR(255),
ai_tone VARCHAR(50) DEFAULT 'friendly',
notifications BOOLEAN DEFAULT 1,
auto_response BOOLEAN DEFAULT 1
);
-- FAQ table
CREATE TABLE faqs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
website_id INTEGER NOT NULL,
question TEXT NOT NULL,
answer TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- Unanswered questions table
CREATE TABLE unanswered_questions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
website_id INTEGER NOT NULL,
question TEXT NOT NULL,
count INTEGER DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- Contact requests table
CREATE TABLE contact_requests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
website_id INTEGER NOT NULL,
visitor_name VARCHAR(255),
visitor_email VARCHAR(255),
message TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- User query limits table
CREATE TABLE user_query_limits (
id INTEGER PRIMARY KEY AUTOINCREMENT,
website_id INTEGER NOT NULL,
query_count INTEGER DEFAULT 0,
reset_date DATETIME,
FOREIGN KEY (website_id) REFERENCES websites(id)
);
-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_websites_owner ON websites(owner_id);
CREATE INDEX idx_website_content_website ON website_content(website_id);
CREATE INDEX idx_chat_sessions_website ON chat_sessions(website_id);
CREATE INDEX idx_chat_sessions_ip ON chat_sessions(ip_address);
CREATE INDEX idx_chat_sessions_active ON chat_sessions(is_active);
CREATE INDEX idx_chat_sessions_started ON chat_sessions(started_at);
CREATE INDEX idx_chat_messages_session ON chat_messages(session_id);
CREATE INDEX idx_user_settings_user ON user_settings(user_id);
CREATE INDEX idx_faqs_website ON faqs(website_id);
CREATE INDEX idx_unanswered_questions_website ON unanswered_questions(website_id);
CREATE INDEX idx_contact_requests_website ON contact_requests(website_id);
CREATE INDEX idx_user_query_limits_website ON user_query_limits(website_id);
|