83 lines
4.9 KiB
SQL
83 lines
4.9 KiB
SQL
ALTER TABLE ai_sessions ADD COLUMN IF NOT EXISTS call_id TEXT;
|
|
CREATE INDEX IF NOT EXISTS idx_ai_sessions_call_id ON ai_sessions(call_id);
|
|
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS voice_session_id TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS ai_session_id TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS ai_state TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS ai_handoff_reason TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS ai_last_model_at TEXT;
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_voice_session_id ON asterisk_call_links(voice_session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_ai_session_id ON asterisk_call_links(ai_session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_ai_state ON asterisk_call_links(ai_state);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_ai_last_model_at ON asterisk_call_links(ai_last_model_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS voice_ai_sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
session_id TEXT NOT NULL UNIQUE,
|
|
call_id TEXT NOT NULL UNIQUE,
|
|
linked_id TEXT,
|
|
interaction_id TEXT,
|
|
customer_id TEXT,
|
|
queue_id TEXT,
|
|
ai_session_id TEXT,
|
|
agent_profile TEXT NOT NULL DEFAULT 'voice_support',
|
|
language TEXT,
|
|
asr_provider TEXT,
|
|
tts_provider TEXT,
|
|
status TEXT NOT NULL DEFAULT 'queued',
|
|
handoff_reason TEXT,
|
|
handoff_target_queue_id TEXT,
|
|
disclosure_played_at TEXT,
|
|
last_user_utterance_at TEXT,
|
|
last_ai_reply_at TEXT,
|
|
started_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
ended_at TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_session_id ON voice_ai_sessions(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_call_id ON voice_ai_sessions(call_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_linked_id ON voice_ai_sessions(linked_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_interaction_id ON voice_ai_sessions(interaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_customer_id ON voice_ai_sessions(customer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_queue_id ON voice_ai_sessions(queue_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_ai_session_id ON voice_ai_sessions(ai_session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_agent_profile ON voice_ai_sessions(agent_profile);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_language ON voice_ai_sessions(language);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_asr_provider ON voice_ai_sessions(asr_provider);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_tts_provider ON voice_ai_sessions(tts_provider);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_status ON voice_ai_sessions(status);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_handoff_target_queue_id ON voice_ai_sessions(handoff_target_queue_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_disclosure_played_at ON voice_ai_sessions(disclosure_played_at);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_last_user_utterance_at ON voice_ai_sessions(last_user_utterance_at);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_last_ai_reply_at ON voice_ai_sessions(last_ai_reply_at);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_started_at ON voice_ai_sessions(started_at);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_updated_at ON voice_ai_sessions(updated_at);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_ai_sessions_ended_at ON voice_ai_sessions(ended_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS voice_transcript_segments (
|
|
id SERIAL PRIMARY KEY,
|
|
segment_id TEXT NOT NULL UNIQUE,
|
|
session_id TEXT NOT NULL,
|
|
call_id TEXT NOT NULL,
|
|
interaction_id TEXT,
|
|
speaker TEXT NOT NULL,
|
|
source_type TEXT NOT NULL,
|
|
sequence_no INTEGER NOT NULL,
|
|
text TEXT NOT NULL,
|
|
confidence DOUBLE PRECISION,
|
|
is_final BOOLEAN NOT NULL DEFAULT TRUE,
|
|
barge_in_interrupted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
payload_json TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_segment_id ON voice_transcript_segments(segment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_session_id ON voice_transcript_segments(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_call_id ON voice_transcript_segments(call_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_interaction_id ON voice_transcript_segments(interaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_speaker ON voice_transcript_segments(speaker);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_source_type ON voice_transcript_segments(source_type);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_sequence_no ON voice_transcript_segments(sequence_no);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_is_final ON voice_transcript_segments(is_final);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_barge_in_interrupted ON voice_transcript_segments(barge_in_interrupted);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_created_at ON voice_transcript_segments(created_at);
|