Study guide: Part 19. Agent Memory with SQLite

Topic: Part 19. Agent Memory on SQLite

Difficulty level: Medium

Estimated study time: 6-8 hours (theory 2 hours, practice 4-6 hours)

Prerequisites: Basic knowledge of SQL and SQLite (creating tables, indexes, simple queries)

Understanding of Qwen Code architecture and the hook system (parts 1-4 of the course)

Experience working with the command line and Python scripts

Familiarity with the concept of SDD (Specification-Driven Development)

Basic understanding of LLM operation and context windows

Learning objectives: Independently deploy a full-fledged agent memory system on SQLite with events and memories tables, including schema, hooks, and context injection mechanism

Critically evaluate which data should be saved to agent memory and which should absolutely not be, and apply privacy and security rules in practice

Implement and configure background memory summarization (analogous to /dream) through dream_sqlite.py scripts with LLM API calls and control its execution

Distinguish the boundary between agent working memory and persistent specifications, migrating stable rules from memories into specs/, QWEN.md, or skills

Optimize request context by applying the "less is better" principle: filter relevant records by tags, limit the number of injected blocks, prevent "context degradation"

Overview: This part of the course is dedicated to creating a local, transparent, and fully controllable memory layer for agent development based on SQLite. The built-in memory of Qwen Code (QWEN.md, /remember, /forget, /dream) is sufficient for most projects, but when audit, portability between environments, or custom cleanup rules are needed, the developer can build their own system. The key idea: events from agent sessions are logged through hooks into the events table, then background summarization compresses them into compact notes in the memories table, which are injected into the context of new sessions. The most important constraint — memory supplements but does not replace specifications: stable rules must migrate to specs/ or QWEN.md. The course covers the database schema, two key hooks (log_event.py and inject_memory.py), integration with Qwen Code, summarization automation, privacy, and principles of context economy to prevent "context degradation".

Key concepts: Two-level memory architecture (events + memories): Raw events are stored in the events table — this is a complete log of agent actions: requests, tool calls, results. Persistent conclusions from these events are compressed into the memories table — compact, structured notes. The separation is critically important: events provide audit and raw material for summarization, memories provide fast relevant context for new sessions. Without this separation, context quickly balloons with unstructured data.

Qwen Code hooks (log_event.py, inject_memory.py): Hooks are executable scripts called at key points in the session lifecycle. log_event.py connects to UserPromptSubmit (request submission), PostToolUse (after tool use), and Stop (session end) events — it writes metadata to events. inject_memory.py connects to SessionStart and UserPromptSubmit — it reads relevant records from memories and adds them to the current session context. Both scripts must be executable and correctly configured in settings.json.

Background summarization (/dream for SQLite): A periodic process of consolidating raw events into persistent notes. Unlike the built-in /dream, the SQLite version works offline: the dream_sqlite.py script reads events for a period (e.g., --since 24h), calls an LLM for summarization via API (DashScope/Bailian), and writes the result to memories. Launched via cron, manually, or after project phases. Key advantage: full transparency — a human can review both raw events and compressed notes.

Context hygiene and "context degradation": A phenomenon where large irrelevant context degrades model response quality worse than short precise context. Research shows: 300 relevant tokens are more effective than 30,000 irrelevant ones. Practical conclusions: inject no more than 3-5 memory blocks, filter by tags/categories, delete outdated rather than "clarify", move frequently needed records to QWEN.md as rules.

Memory vs. specification boundary: Agent memory is a working layer for detectable patterns, preferences, and errors. Specifications (specs/, QWEN.md, skills) are mandatory rules for the product. When background summarization finds a stable rule (e.g., "always update CHANGELOG.md before merging"), it must migrate from memories to the appropriate specification. Memory helps discover, specifications enforce compliance.

Privacy and security of memory: Memory easily becomes a warehouse of sensitive data. Mandatory rules: do not log environment variables, truncate tool responses, do not store secrets and personal data, limit context to 3-5 blocks, regularly clean outdated data, do not commit .db files (only schema and scripts). A .gitignore example is provided in the course.

Full-text search (FTS) in SQLite: The memory_fts table provides fast search across note contents. Uses the snippet() function to display context of found matches. Allows finding relevant records by keywords during context injection or manual audit.

Practice exercises: Name: Deploying the basic schema and first hook

Problem: Create the .qwen/memory directory, copy schema.sql from the tutorial, initialize the SQLite database agent-memory.db. Then create .qwen/hooks, copy log_event.py, make it executable. Configure settings.json to connect the hook to UserPromptSubmit and PostToolUse events. Conduct one test Qwen Code session with a simple request (e.g., "create hello.py"). Verify that events were recorded in the database.

Solution: 1. mkdir -p .qwen/hooks .qwen/memory

1. cp "$TUTORIAL_DIR/examples/sqlite-memory/schema.sql" .qwen/memory/schema.sql
2. sqlite3 .qwen/memory/agent-memory.db < .qwen/memory/schema.sql
3. cp "$TUTORIAL_DIR/examples/sqlite-memory/hooks/log_event.py" .qwen/hooks/
4. chmod +x .qwen/hooks/log_event.py
5. Merge the configuration from settings-hooks.example.json with your settings.json (do not overwrite model and authorization)
6. Launch Qwen Code, execute the request
7. Verify: sqlite3 .qwen/memory/agent-memory.db "select event_name, tool_name, substr(prompt,1,80), timestamp from events order by id desc limit 5;"

Expected result: visible records with UserPromptSubmit and PostToolUse types.

Complexity: beginner

Name: Injecting relevant memory into a session

Problem: Connect inject_memory.py to SessionStart and UserPromptSubmit events. Manually create 2-3 test records in the memories table with different categories (preferences, workflow, project). Launch a new Qwen Code session with a request relevant to one of the categories. Verify through logs or by modifying inject_memory.py with stderr output that the correct records were added to the context.

Solution: 1. cp "$TUTORIAL_DIR/examples/sqlite-memory/hooks/inject_memory.py" .qwen/hooks/

1. chmod +x .qwen/hooks/inject_memory.py
2. Add hooks to settings.json
3. Create records manually via SQL or copy manual-memory-example.sql
4. Temporarily modify inject_memory.py: add print(f"[DEBUG] Injecting: {path}", file=sys.stderr)
5. Launch a session with a request related to your category
6. Check the output: you should see [DEBUG] Injecting: with correct paths
7. Ensure that irrelevant records are not injected

Complexity: intermediate

Name: Background summarization with dry-run

Problem: Install dream_sqlite_qwen_example.py as dream_sqlite.py. Ensure you have the BAILIAN_API_KEY variable configured. Run summarization for the last 24 hours in dry-run mode. Analyze the output: which events were selected, which notes were suggested. Then run without dry-run and verify the result in the memories table.

Solution: 1. cp "$TUTORIAL_DIR/examples/sqlite-memory/dream_sqlite_qwen_example.py" .qwen/memory/dream_sqlite.py

1. export BAILIAN_API_KEY=your_key
2. python .qwen/memory/dream_sqlite.py --since 24h --dry-run
3. Study stdout: it will show the plan — which events were selected, which summaries were generated
4. If the result is satisfactory: python .qwen/memory/dream_sqlite.py --since 24h
5. Verify: sqlite3 .qwen/memory/agent-memory.db "select path, substr(content,1,100), updated_at from memories order by updated_at desc;"
6. Expected result: new compact records in memories reflecting patterns from events

Complexity: intermediate

Name: Migrating a rule from memory to specification

Problem: After several sessions you noticed that the agent systematically forgets to update CHANGELOG.md. This pattern is recorded in memories as workflow/sdd-validation.md with a note. Your task: evaluate whether this has become a stable rule, and correctly transfer it to the project specification, removing from memories or marking as transferred.

Solution: 1. Find the record: sqlite3 .qwen/memory/agent-memory.db "select path, content from memories where path like '%validation%';"

1. Analyze frequency: how many times the error repeated, whether the user confirmed the fix
2. If the rule is stable (≥3 confirmations, critical for the product):

• Create or update specs/workflow/changelog.md or add a section to QWEN.md
• Formulate as a mandatory rule: "Before merging a feature branch into main, update CHANGELOG.md with an Unreleased section"

1. Mark in memories: UPDATE memories SET content = content || '\n\n[TRANSFERRED TO specs/workflow/changelog.md 2024-XX-XX]' WHERE path = 'workflow/sdd-validation.md';

Or delete if you are sure of duplication

1. Verify in a new session: the agent should follow the rule from the specification without requiring memory

Complexity: advanced

Name: Auditing and cleaning sensitive data

Problem: When checking the database you discovered that API key strings accidentally ended up in the prompt field in events (the user pasted it into a request for testing). Also in memories there is a record with a tester's personal email. Perform an audit, find all sensitive records, clean them, and implement protection against recurrence.

Solution: 1. Audit events: sqlite3 .qwen/memory/agent-memory.db "select id, substr(prompt,1,200) from events where prompt like '%sk-%' or prompt like '%Bearer%' or prompt like '%@%.%';"

1. Audit memories: similar query searching content
2. Delete found records: DELETE FROM events WHERE id IN (...); DELETE FROM memories WHERE path = '...';
3. Check FTS: DELETE FROM memory_fts WHERE docid IN (SELECT id FROM memories WHERE ...);
4. Strengthen log_event.py: add filtering — before writing, check prompt against secret regular expressions, replace with [REDACTED]
5. Add to .gitignore: .qwen/memory/agent-memory.db and any .db-journal, .db-wal
6. Document the rule in the project README: "Never paste secrets into prompts"

Complexity: advanced

Case studies: Name: AgentClinic: from operational notes to product specification

Scenario: A startup is developing AgentClinic — a public platform for satirical reviews of AI agents. A team of 4 developers uses Qwen Code with custom SQLite memory for coordination. Over 3 months, 15,000 events and 200 notes accumulated in memories.

Challenge: Problem 1: New session context ballooned to 8000 tokens due to unfiltered injection of all memories — the model started "getting lost" and ignoring key instructions from QWEN.md. Problem 2: The product agreement on review tone ("public and satirical, not private messages") lived only in memories, and different developers interpreted it differently. Problem 3: A new developer couldn't understand why certain decisions were made — memories were not versioned and not committed.

Solution: The team introduced strict filtering in inject_memory.py: only records with tags relevant to the current task (determined by request keywords), maximum 4 blocks of 200 tokens each. For product agreements, they created a "memory triage" process — weekly, a senior developer reviewed new memories and transferred stable rules to specs/. Specifically, the tone agreement migrated to specs/mission.md with the wording "All AgentClinic entries are public by default; satirical tone is mandatory, private messages are prohibited". The schema and scripts were moved to a separate versioned repository, and .db files were added to .gitignore — each developer kept a local copy, syncing through export/import of memories as SQL dumps when needed.

Result: Average session context shrank from 8000 to 1200 tokens, while instruction-following accuracy (measured through manual evaluation of 50 sessions) increased from 64% to 91%. Product agreements became uniform across the team. The new developer was able to understand the rules in 2 hours of reading specs/ instead of 2 days of digging through memory. Time spent on "memory triage" — 30 minutes per week — paid off through reduced interpretation errors.

Lessons learned: Unfiltered injection of all memories is worse than no memory — context degradation is real and measurable

Product rules must migrate to specs/ as quickly as possible; memories are only an incubator

Versioning the schema and scripts is critical, but .db files must not be committed — they contain personal data and the operational layer

Regular manual memory audit (memory triage) is necessary; fully automatic summarization risks entrenching erroneous patterns

Related concepts: Context hygiene and "context degradation"

Memory vs. specification boundary

Privacy and security of memory

Background summarization (/dream for SQLite)

Name: Fintech startup: customer data privacy in agent memory

Scenario: A fintech company with 12 developers used Qwen Code with SQLite memory to develop a payment processing system. Memory helped track complex business rules and frequent errors in integration with banking APIs.

Challenge: During a security audit, it was discovered that log_event.py recorded full bank API responses in the events table, including masked card numbers, transaction amounts, and internal customer identifiers. Also in memories, notes with emails and phone numbers of testers used for debugging SMS notifications accumulated. The agent-memory.db database accidentally ended up in a commit during a rushed fix.

Solution: Immediate response: reverted commit, rotation of all potentially compromised keys, regulatory notification per procedure (data was masked, but the fact of leakage was a risk). Structural changes: log_event.py rewritten with mandatory filtering — regular expressions for PAN (even masked), amounts, emails, phone numbers; API responses truncated to status code and message without payload. A pre-commit hook was implemented blocking commit of any .db files. memories were cleaned via SQL script with operation logging for audit. A "privacy reviewer" role was added to memory triage.

Result: The incident was contained, no fines followed thanks to rapid response and masking of original data. The new filtering system blocked 340 potential leaks over 6 months (measured by regular expression triggers). Developer time spent on filter setup — 4 hours — paid off in the first week. A "privacy by design" culture was embedded in the agent memory process.

Lessons learned: "Masked" data is still sensitive — regulators and ethics require its exclusion from any logs

Technical measure (.gitignore) is insufficient without a pre-commit hook — human factor in a rush is inevitable

Filtering must be in log_event.py, do not rely on "developers will be careful"

Memory audit must be as regular as code audit — data accumulates unnoticed

Related concepts: Privacy and security of memory

Two-level memory architecture (events + memories)

Qwen Code hooks (log_event.py, inject_memory.py)

Study tips: Start with manually walking through the entire chain: create the schema, launch one hook, check the database manually via sqlite3 CLI — this way you will "feel" the data flow before automating

Keep a "lab journal" in a separate file: record which SQL queries you executed, what you expected, what you got — this develops intuition for debugging SQLite memory

Practice the "dry-run first, then reality" principle with dream_sqlite.py: the habit of checking summarization before writing prevents polluting memories with garbage

Create your own "maturity scale" for rules: 1 time — note, 2 times — verify, 3 times — candidate for specification. This simplifies the migration decision

Use the analogy with human memory: events are "everything you saw today", memories are "notes in a diary", specs/ are "signed contracts". Contracts have legal force, a diary is operational assistance

For visual style: draw the data flow diagram from event to injection on paper, marking where leakage or context bloat can occur — this activates spatial thinking

Pair learning: if possible, one person configures log_event.py, another configures inject_memory.py, then swap roles and look for bugs in each other's implementation — the best way to find edge cases

Regularly execute /clear in Qwen Code during training — this trains conscious work with context, not accumulation of everything indiscriminately

Additional resources: Original course examples (schema.sql, hooks, dream sqlite): Repository sdd-qwen-code-ru/examples/sqlite-memory/ — the primary source of all scripts, copy from here, do not rewrite from scratch

SQLite FTS5 documentation: https://www.sqlite.org/fts5.html — for deep understanding of full-text search in the memory_fts table

VentureBeat article on Anthropic agent memory: Mentioned in the course; search by keywords 'Anthropic agent memory sessions patterns' — conceptual foundation of background summarization

Course Part 14 (context hygiene): part-14-build-your-own-workflow.md — essential supplement for understanding why /clear and role separation work

Course Part 4 (DashScope/Bailian API setup): For correct configuration of BAILIAN_API_KEY in dream_sqlite_qwen_example.py

OWASP cheat sheet on security logging: https://cheatsheetseries.owasp.org/cheatsheets/Logging_Cheat_Sheet.html — extension of privacy rules for agent memory

Research 'Lost in the Middle' (Stanford, 2023): Key scientific foundation of the context degradation phenomenon; search by title to understand why information position in context is critical

Summary: Agent memory on SQLite is a local, transparent, and fully controllable layer to supplement the built-in memory of Qwen Code. Key principles: separation of raw events (events) and persistent notes (memories), hooks for logging and injection, offline background summarization via dream_sqlite.py, strict boundary between working memory and mandatory specifications. Main risks — context degradation from excessive injection and leakage of sensitive data. Solutions: relevance filtering, 3-5 block limit, regular audit, secret filtering in hooks, no committing of .db files. Successful implementation requires discipline: memory helps discover rules, but specifications make them mandatory.