postgres_memory_schema.sql•2.09 kB
-- PostgreSQL schema for chat memory system
-- Migration from SQLite to PostgreSQL
-- Create memories table
CREATE TABLE IF NOT EXISTS memories (
id SERIAL PRIMARY KEY,
conversation_id TEXT NOT NULL,
content TEXT NOT NULL,
metadata JSONB DEFAULT '{}',
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
role TEXT DEFAULT 'user',
importance REAL DEFAULT 1.0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Create conversation summaries table
CREATE TABLE IF NOT EXISTS conversation_summaries (
conversation_id TEXT PRIMARY KEY,
summary TEXT NOT NULL,
last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW(),
message_count INTEGER DEFAULT 0,
participants JSONB DEFAULT '[]',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_memories_conversation_id ON memories(conversation_id);
CREATE INDEX IF NOT EXISTS idx_memories_timestamp ON memories(timestamp);
CREATE INDEX IF NOT EXISTS idx_memories_importance ON memories(importance);
CREATE INDEX IF NOT EXISTS idx_memories_role ON memories(role);
CREATE INDEX IF NOT EXISTS idx_memories_content_search ON memories USING gin(to_tsvector('english', content));
CREATE INDEX IF NOT EXISTS idx_memories_metadata ON memories USING gin(metadata);
CREATE INDEX IF NOT EXISTS idx_conversation_summaries_last_updated ON conversation_summaries(last_updated);
CREATE INDEX IF NOT EXISTS idx_conversation_summaries_message_count ON conversation_summaries(message_count);
-- Create trigger to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_memories_updated_at BEFORE UPDATE ON memories
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_conversation_summaries_updated_at BEFORE UPDATE ON conversation_summaries
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();