SQLite Memory Storage
Relevant source files
The following files were used as context for generating this wiki page:
SQLite Memory Storage serves as the durable relational backbone for persisting user context, extracted claims, and structured domain knowledge within the RAGAPPv3 ecosystem. Unlike transient vector embeddings, this storage layer manages the lifecycle of "memories"—discrete pieces of information ranging from raw user preferences to curated wiki claims—ensuring they are vault-scoped, searchable via Full-Text Search (FTS), and referenceable during chat interactions.
The system integrates directly with the RAG pipeline, allowing retrieved memories to be injected into LLM prompts using a distinct citation namespace [M#], which is separate from document sources [S#] and wiki knowledge [W#]. This structured approach enables the application to provide high-fidelity answers grounded in both raw documents and durable user context.
Sources: specs/wire-wiki-chat/SPEC.md, backend/app/models/database.py:101-110
Database Schema & Core Entities
The memory storage is implemented using several specialized tables within the SQLite database. The primary entity is the memories table, which stores the content and metadata for user-provided or extracted context.
Memories Table Structure
The memories table includes fields for content, categorization, and vault scoping to ensure multi-tenant data isolation.
| Field | Type | Description |
|---|---|---|
id | INTEGER | Primary Key (Autoincrement) |
vault_id | INTEGER | Scopes the memory to a specific document collection |
content | TEXT | The actual text of the memory |
category | TEXT | Optional classification (e.g., preference, fact) |
tags | TEXT | JSON array of labels for filtering |
source | TEXT | Reference to the origin of the memory |
created_at | TIMESTAMP | Record creation time |
updated_at | TIMESTAMP | Last modification time |
Sources: backend/app/models/database.py:101-110, frontend/src/lib/api.ts:404-411
Full-Text Search (FTS) Integration
To facilitate efficient retrieval, a virtual table memories_fts is maintained using the SQLite FTS5 engine. This table indexes the content and category fields. Synchronization is handled via database triggers that execute after INSERT, UPDATE, or DELETE operations on the base memories table.
The diagram shows how SQLite triggers ensure the FTS virtual table remains synchronized with the primary data table for efficient searching.
Sources: backend/app/models/database.py:113-136
Memory Retrieval & RAG Integration
Memories are retrieved during the RAG (Retrieval-Augmented Generation) process to provide the LLM with durable user context. The retrieval logic applies relevance thresholds to prevent unrelated information from cluttering the prompt.
Relevance Filtering
Retrieval uses specific thresholds to filter weak candidates:
- Dense Search Similarity: Minimum similarity (e.g., 0.30) for vector-based matches.
- RRF Score: Minimum score (e.g., 0.005) for Reciprocal Rank Fusion results.
- Top-K Capping: Retains only the most relevant memories (default top 3) before prompt injection.
Sources: specs/fix-chat-overlap-memory-interop/SPEC.md:75-81
Citation Namespaces
When the assistant references a memory in a response, it uses a stable 1-indexed label within a dedicated namespace. This allows the frontend to render specific cards for each cited piece of evidence.
| Label Space | Source Type | Description |
|---|---|---|
[S#] | Document Sources | Raw retrieval from indexed files |
[M#] | Memory Records | Durable user context and preferences |
[W#] | Wiki Claims | Compiled, high-fidelity domain knowledge |
Sources: specs/wire-wiki-chat/SPEC.md:144-150
Memory Promotion to Wiki
The system allows for "promoting" existing memories into the structured Wiki system. This process transforms semi-structured memory text into curated WikiPage, WikiClaim, and WikiEntity records.
This sequence illustrates the transformation of a raw memory into structured wiki entities through regex-based extraction and store-level persistence.
Sources: backend/app/services/wiki_compiler.py:168-285, backend/app/api/routes/wiki.py:387-418
Wiki Claim Persistence
When a memory is promoted, it creates a WikiClaim with a status typically set to active or needs_review. The relationship is maintained in the wiki_claim_sources table, which links the claim back to the original memory_id.
| Field | Value |
|---|---|
source_kind | memory |
memory_id | ID of the source memory |
status | active |
confidence | 0.8 (default for promoted memory) |
Sources: backend/app/services/wiki_store.py:64-79, backend/app/services/wiki_compiler.py:241-255
API Implementation
The memory storage is managed through a set of REST endpoints that provide CRUD operations and search capabilities, all of which are strictly vault-scoped.
Key API Endpoints
| Method | Path | Description |
|---|---|---|
GET | /memories | List all memories for a vault |
POST | /memories | Add a new memory record |
PUT | /memories/{id} | Update existing memory content/metadata |
DELETE | /memories/{id} | Permanently remove a memory |
POST | /memories/search | Search memories using FTS and filters |
GET | /wiki/memories/{id}/status | Check if a memory has been promoted to wiki |
Sources: frontend/src/lib/api.ts:805-870, backend/app/api/routes/wiki.py:596-601
Conclusion
SQLite Memory Storage provides the necessary durability and structure for RAGAPPv3 to maintain long-term user context. By separating memories into a distinct citation namespace and providing a path for promotion into high-fidelity wiki knowledge, the system ensures that user-specific information is treated with the same rigor as primary document sources while remaining easily accessible for LLM grounding.
Sources: specs/wire-wiki-chat/SPEC.md:144-150, backend/app/models/database.py:101-110