Week 3 • Day 6Week 3
Day 6:Building an AI Conversation Memory & Token Cost Audit Database
Battle-tested daily lesson note • 100% runnable code, interview Q&A, test suite & interactive quiz
Week 3 — Day 6: Building an AI Conversation Memory & Token Cost Audit Database
Mentor's Note (10+ YOE Staff AI Engineer POV): Every production Chatbot (ChatGPT, Claude, or our Month 5 SRE Incident Operations Platform) needs a Persistent Conversation Memory & Token Cost Ledger. Why?
- LLMs are Stateless! Every time a user sends a 5th message in a chat, the LLM has zero memory of Messages 1–4 unless your backend loads the last messages from your database (
SELECT role, content FROM chat_messages WHERE session_id = ? ORDER BY id DESC LIMIT ?) and sends them in themessages=[...]array!- Sliding Context Window Management: If a conversation has 500 messages, sending all 500 overflows the context window and wastes money. Your database query fetches the most recent messages in chronological order!
Part 1: Production Schema for AI Chatbots (chat_sessions + chat_messages)
text
+-------------------------------+ 1 : N +------------------------------------+
| chat_sessions | --------------------> | chat_messages |
+-------------------------------+ +------------------------------------+
| session_id (TEXT PRIMARY KEY) | | id (INTEGER PRIMARY KEY) |
| user_id (TEXT NOT NULL) | | session_id (TEXT FOREIGN KEY) |
| system_prompt (TEXT NOT NULL) | | role ('user' | 'assistant' | 'tool')|
| created_at (TEXT) | | content (TEXT NOT NULL) |
+-------------------------------+ | prompt_tokens (INTEGER) |
| completion_tokens (INTEGER) |
| cost_usd (REAL) |
| metadata_json (TEXT) |
+------------------------------------+
Part 2: Hands-On Lab — Persistent AI Chat Memory & Cost Ledger (chat_memory_store.py)
Check out src/chat_memory_store.py (in Tab 2: Codes — click Run in Browser to execute) and run the script and tests:
powershell
python week03_sql_databases_from_zero_to_pro/day06_ai_conversation_memory_and_cost_audit_db/src/chat_memory_store.py
python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day06_ai_conversation_memory_and_cost_audit_db/tests -v