27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
-- Migration: 018_chat_histories
|
|
-- Adds a dedicated table for saving and retrieving agent chat histories.
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_histories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
session_id TEXT NOT NULL,
|
|
title TEXT,
|
|
channel TEXT,
|
|
agent_id TEXT,
|
|
project_id UUID REFERENCES projects(guid) ON DELETE SET NULL,
|
|
messages JSONB NOT NULL DEFAULT '[]',
|
|
summary TEXT,
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_session_id ON chat_histories(session_id);
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_project_id ON chat_histories(project_id) WHERE project_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_channel ON chat_histories(channel) WHERE channel IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_agent_id ON chat_histories(agent_id) WHERE agent_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_created_at ON chat_histories(created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat_histories_fts
|
|
ON chat_histories
|
|
USING GIN (to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(summary, '')));
|