BE-5: Database Connection Setup

[tecnodeveloper]

Description:
Set up MongoDB and design a structured data model to support multi-turn conversations, session management, feedback collection, and analytics. Implement collections for users, sessions, messages, and feedback with proper relationships.

---

User Story

Given that the chatbot requires structured conversation tracking
When I design and implement the database
Then the system should store chats, sessions, and feedback in a way that supports analytics and context management

---

Tasks

Database Planning (Project-Specific)

1. Identify Core Collections

   • Users (optional if auth later)
   • Sessions (conversation sessions)
   • Messages (multi-turn chat)
   • Feedback (per response rating)
   • Analytics (optional aggregated data)

---

2. Define Relationships

   • One session → many messages
   • One message → one feedback
   • Session linked with topic/domain
   • Messages linked with session_id

---

Schema Design (MongoDB)

3. Session Schema

   • session_id
   • start_time
   • end_time
   • current_topic (ML, healthcare, etc.)
   • context_window (stored messages)
   • status (active/ended)

---

4. Message Schema

   • message_id
   • session_id
   • user_query
   • bot_response
   • timestamp
   • response_time
   • position_in_session (start/middle/end)
   • detected_topic
   • context_used (for debugging/context mgmt)

---

5. Feedback Schema (IMPORTANT)

   • feedback_id
   • message_id
   • correctness (rating)
   • quality (rating)
   • length (too short / optimal / too long)
   • user_comment (optional)
   • timestamp

---

6. Optional Analytics Schema

   • topic_performance
   • avg_correctness
   • avg_quality
   • avg_response_time
   • position_analysis

---

Model Implementation

7. Create Model Files

   • /app/models/session_model.py
   • /app/models/message_model.py
   • /app/models/feedback_model.py

---

8. Define MongoDB Collections

   • Create collections in MongoDB
   • Ensure indexes (session_id, message_id)

---

Database Utilities

9. Create DB Helper Layer

   • Insert document helper
   • Update document helper
   • Query helper
   • Aggregation helper (for analytics)

---

CRUD Operations

10. Session Operations

• Create session
• Update session (topic change, end session)
• Fetch session

---

11. Message Operations

• Store user query + bot response
• Fetch messages by session
• Track response time

---

12. Feedback Operations

• Store feedback after each response
• Validate rating inputs
• Link feedback with message

---

Context Management Support

13. Store Context Data

• Save previous messages for context window
• Limit context size
• Track topic changes

---

Testing Data Flow

14. Test End-to-End Flow

• Create session
• Send message
• Store response
• Submit feedback
• Retrieve full session data

---

Validation & Optimization

15. Optimize Queries & Structure

• Add indexes for performance
• Optimize aggregation queries
• Ensure minimal redundancy

---

Acceptance Criteria

• MongoDB collections created correctly
• Sessions, messages, and feedback properly linked
• Feedback stores correctness, quality, and length
• Multi-turn conversations stored accurately
• Context data is manageable
• Data supports analytics use cases

---

Testing Steps

1. Start a new session
2. Send multiple queries (multi-turn)
3. Store responses
4. Submit feedback for each response
5. Fetch full session with messages + feedback
6. Verify topic tracking and response time

---

Definition of Done

• Database fully supports chatbot flow
• Feedback system implemented
• Multi-turn session storage working
• Data ready for analytics dashboard
• No critical data issues