To create a custom chat agent with persistent conversation contexts, you have several options for storing and managing the conversation data. Both SQL and graph databases can be used effectively, depending on your specific requirements and preferences. Here’s a detailed comparison and guidance on implementing persistence with each type:
A SQL database is a good choice if your data model is relatively simple and you prefer a structured, relational approach. Here’s how you could implement it:
- Users Table: Stores user information.
- Conversations Table: Stores conversation metadata, including a reference to the user.
- Messages Table: Stores individual messages, each linked to a conversation.
CREATE TABLE Users (
user_id INT PRIMARY KEY,
username VARCHAR(100)
-- other user-specific fields
);
CREATE TABLE Conversations (
conversation_id INT PRIMARY KEY,
user_id INT,
created_at TIMESTAMP,
updated_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
CREATE TABLE Messages (
message_id INT PRIMARY KEY,
conversation_id INT,
sender VARCHAR(100),
message_text TEXT,
timestamp TIMESTAMP,
FOREIGN KEY (conversation_id) REFERENCES Conversations(conversation_id)
);
- When a user sends or receives a message, insert a new record into the Messages table.
- Update the updated_at field of the corresponding conversation in the Conversations table.
INSERT INTO Messages (conversation_id, sender, message_text, timestamp)
VALUES (?, ?, ?, ?);
UPDATE Conversations
SET updated_at = ?
WHERE conversation_id = ?;
- To retrieve a conversation, join the Conversations and Messages tables based on the conversation ID.
- Use SQL queries to filter, sort, and paginate messages as needed.
SELECT * FROM Messages
WHERE conversation_id = ?
ORDER BY timestamp;
A graph database is a good fit if your data model involves complex relationships and traversals. Here’s how you could implement it:
- Nodes: Users, Conversations, Messages.
- Relationships: User initiates a Conversation, Conversation has Messages.
For example, using Neo4j:
CREATE (u:User {user_id: 1, username: 'JohnDoe'})
CREATE (c:Conversation {conversation_id: 1, created_at: timestamp()})
CREATE (m:Message {message_id: 1, sender: 'JohnDoe', message_text: 'Hello!', timestamp: timestamp()})
CREATE (u)-[:INITIATED]->(c)
CREATE (c)-[:HAS_MESSAGE]->(m);
- Create nodes for each message and connect them to the appropriate conversation node.
MATCH (c:Conversation {conversation_id: 1})
CREATE (m:Message {message_id: 2, sender: 'JohnDoe', message_text: 'How are you?', timestamp: timestamp()})
CREATE (c)-[:HAS_MESSAGE]->(m);
- Use graph traversal queries to fetch messages for a conversation.
MATCH (c:Conversation {conversation_id: 1})-[:HAS_MESSAGE]->(m:Message)
RETURN m ORDER BY m.timestamp;
-
- Pros: Well-understood, robust, good for structured data and straightforward relationships.
- Cons: Can become complex with deeply nested or highly interconnected data.
-
- Pros: Excellent for complex relationships and traversals, flexible schema.
- Cons: Can be overkill for simple, relational data models, learning curve for those unfamiliar with graph concepts.
- Choose a database system (e.g., PostgreSQL for SQL, Neo4j for graph).
- Define your schema based on the examples above.
- Use the OpenAI API to handle message generation and responses.
- Store each interaction in your chosen database.
- Use session management to handle multiple conversations per user.
- Provide endpoints to create, retrieve, and switch between conversations.
- User sends a message.
- Your backend logs the message in the database.
- The message is sent to the OpenAI API.
- The response from OpenAI is logged and returned to the user.
- Users can query past conversations and switch contexts as needed.
By carefully designing your data model and choosing the appropriate database system, you can create a robust chat agent capable of maintaining and switching between multiple conversation contexts.
import requests
import sqlite3 # or your preferred database connector
openai_api_key = 'your_openai_api_key'
openai_url = 'https://api.openai.com/v1/engines/davinci-codex/completions'
conn = sqlite3.connect('chat_agent.db')
def store_message(conversation_id, sender, message_text):
timestamp = datetime.now()
conn.execute(
"INSERT INTO Messages (conversation_id, sender, message_text, timestamp) VALUES (?, ?, ?, ?)",
(conversation_id, sender, message_text, timestamp)
)
conn.execute(
"UPDATE Conversations SET updated_at = ? WHERE conversation_id = ?",
(timestamp, conversation_id)
)
conn.commit()
def retrieve_messages(conversation_id):
cursor = conn.execute(
"SELECT * FROM Messages WHERE conversation_id = ? ORDER BY timestamp",
(conversation_id,)
)
return cursor.fetchall()
def handle_message(user_id, conversation_id, message_text):
store_message(conversation_id, 'user', message_text)
response = requests.post(
openai_url,
headers={"Authorization": f"Bearer {openai_api_key}"},
json={
"prompt": message_text,
"max_tokens": 150
}
).json()
response_text = response['choices'][0]['text'].strip()
store_message(conversation_id, 'ai', response_text)
return response_text
user_id = 1
conversation_id = 1
user_message = "Hello, how are you?"
response = handle_message(user_id, conversation_id, user_message)
print(response)