05. SQLite for Short-Term

Chapter 5 of 24 · 15 min

KEY INSIGHT

SQLite provides ACID-compliant storage with minimal overhead, making it ideal for short-term memory where reliability matters more than concurrent write performance. SQLite handles short-term memory storage efficiently for single-process agents. The database provides transactional guarantees without requiring a separate server process. Embedded operation eliminates network latency and simplifies deployment. Schema Design Short-term memory stores conversation turns, working context, and transient state. The schema reflects these use cases with tables optimized for recent data access patterns. ```sql CREATE TABLE conversations ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, started_at DATETIME DEFAULT CURRENT_TIMESTAMP, last_activity DATETIME DEFAULT CURRENT_TIMESTAMP, context_summary TEXT, is_active BOOLEAN DEFAULT TRUE ); CREATE TABLE messages ( id INTEGER PRIMARY KEY AUTOINCREMENT, conversation_id INTEGER NOT NULL, role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system')), content TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, metadata JSON, FOREIGN KEY (conversation_id) REFERENCES conversations(id) ); CREATE INDEX idx_messages_conversation_time ON messages(conversation_id, created_at DESC); CREATE TABLE working_memory ( key TEXT PRIMARY KEY, value TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME, access_count INTEGER DEFAULT 0 ); ``` Failure Handling SQLite handles many failure scenarios automatically through its journal mechanism. Incomplete transactions roll back cleanly on restart. Corrupted databases can often be recovered from WAL (Write-Ahead Logging) archives. However, SQLite is not immune to all failures. Disk full conditions require proactive monitoring. Database locks can block operations during high-concurrency scenarios. Large databases may experience performance degradation without regular maintenance. Maintenance Operations Regular maintenance keeps the database healthy. The VACUUM command reclaims deleted space and rebuilds indexes. The ANALYZE command updates query planning statistics. These operations should run during low-activity periods. ```sql -- Clean up expired working memory DELETE FROM working_memory WHERE expires_at IS NOT NULL AND expires_at < CURRENT_TIMESTAMP; -- Archive old conversations INSERT INTO conversations_archive SELECT * FROM conversations WHERE last_activity < datetime('now', '-30 days'); -- Reclaim space VACUUM; ```

EXERCISE

Write a Python function that uses SQLite to store a message in a conversation, updating the conversation's last_activity timestamp in the same transaction. Handle the case where the conversation does not exist.