124 lines
5.9 KiB
SQL
124 lines
5.9 KiB
SQL
CREATE TABLE IF NOT EXISTS customer_external_identities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
identity_id TEXT NOT NULL UNIQUE,
|
|
customer_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
external_subject TEXT NOT NULL,
|
|
display_name_snapshot TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ix_customer_external_identities_channel_subject_unique
|
|
ON customer_external_identities(channel, external_subject);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_identity_id ON customer_external_identities(identity_id);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_customer_id ON customer_external_identities(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_channel ON customer_external_identities(channel);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_external_subject ON customer_external_identities(external_subject);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_created_at ON customer_external_identities(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_customer_external_identities_updated_at ON customer_external_identities(updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id TEXT NOT NULL UNIQUE,
|
|
channel TEXT NOT NULL,
|
|
thread_id TEXT,
|
|
interaction_id TEXT,
|
|
customer_id TEXT,
|
|
agent_profile TEXT NOT NULL DEFAULT 'telegram_support',
|
|
language TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
summary_text TEXT NOT NULL DEFAULT '',
|
|
last_user_message_id TEXT,
|
|
last_ai_message_id TEXT,
|
|
handoff_reason TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
closed_at TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_session_id ON ai_sessions(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_channel ON ai_sessions(channel);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_thread_id ON ai_sessions(thread_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_interaction_id ON ai_sessions(interaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_customer_id ON ai_sessions(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_agent_profile ON ai_sessions(agent_profile);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_language ON ai_sessions(language);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_status ON ai_sessions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_last_user_message_id ON ai_sessions(last_user_message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_last_ai_message_id ON ai_sessions(last_ai_message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_created_at ON ai_sessions(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_updated_at ON ai_sessions(updated_at);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_closed_at ON ai_sessions(closed_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_jobs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
job_id TEXT NOT NULL UNIQUE,
|
|
session_id TEXT NOT NULL,
|
|
thread_id TEXT NOT NULL,
|
|
trigger_message_id TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
next_attempt_at TEXT,
|
|
locked_until TEXT,
|
|
last_error TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_job_id ON ai_jobs(job_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_session_id ON ai_jobs(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_thread_id ON ai_jobs(thread_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_trigger_message_id ON ai_jobs(trigger_message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_status ON ai_jobs(status);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_next_attempt_at ON ai_jobs(next_attempt_at);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_locked_until ON ai_jobs(locked_until);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_created_at ON ai_jobs(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_jobs_updated_at ON ai_jobs(updated_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_turns (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
turn_id TEXT NOT NULL UNIQUE,
|
|
session_id TEXT NOT NULL,
|
|
thread_id TEXT,
|
|
interaction_id TEXT,
|
|
role TEXT NOT NULL,
|
|
source_type TEXT NOT NULL,
|
|
text TEXT NOT NULL,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
model TEXT,
|
|
finish_reason TEXT,
|
|
latency_ms INTEGER,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_turn_id ON ai_turns(turn_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_session_id ON ai_turns(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_thread_id ON ai_turns(thread_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_interaction_id ON ai_turns(interaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_role ON ai_turns(role);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_source_type ON ai_turns(source_type);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_model ON ai_turns(model);
|
|
CREATE INDEX IF NOT EXISTS idx_ai_turns_created_at ON ai_turns(created_at);
|
|
|
|
ALTER TABLE telegram_threads ADD COLUMN ai_session_id TEXT;
|
|
ALTER TABLE telegram_threads ADD COLUMN ai_state TEXT;
|
|
ALTER TABLE telegram_threads ADD COLUMN ai_handoff_reason TEXT;
|
|
ALTER TABLE telegram_threads ADD COLUMN ai_last_model_at TEXT;
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_session_id ON telegram_threads(ai_session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_state ON telegram_threads(ai_state);
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_threads_ai_last_model_at ON telegram_threads(ai_last_model_at);
|
|
|
|
ALTER TABLE telegram_messages ADD COLUMN author_type TEXT NOT NULL DEFAULT 'customer';
|
|
ALTER TABLE telegram_messages ADD COLUMN author_id TEXT;
|
|
UPDATE telegram_messages
|
|
SET author_type = CASE
|
|
WHEN direction = 'outbound' THEN 'human'
|
|
WHEN direction = 'system' THEN 'system'
|
|
ELSE 'customer'
|
|
END
|
|
WHERE author_type IS NULL OR TRIM(author_type) = '';
|
|
UPDATE telegram_messages
|
|
SET author_id = operator_user
|
|
WHERE direction = 'outbound'
|
|
AND operator_user IS NOT NULL
|
|
AND (author_id IS NULL OR TRIM(author_id) = '');
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_messages_author_type ON telegram_messages(author_type);
|
|
CREATE INDEX IF NOT EXISTS idx_telegram_messages_author_id ON telegram_messages(author_id);
|