← Back to learning path
Case · Published

Case study: a local SQLite policy-search RAG

Assemble a reproducible RAG with SQLite FTS and deterministic context, without an external service.

Source: knowledge/p24-b/collection_6e8fbe1e8b1ea569a539e8a5/cases/rag-local-policy-search.mdRAG knowledge engineering
Chapter progress 0%Learning path progress 0% · 0/10 chapters completedContinue learning path →

Problem and boundaries

The team needs to validate a policy-question answering flow locally, but the test environment cannot use real customer data or a paid vector service. The first goal is to verify document versions, permission filtering, and citation links.

Design

Store redacted policy passages in SQLite FTS5. Each result carries a document_id, version, and role tag; the generation stage receives only the first two authorized candidates.

Minimal implementation

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('CREATE VIRTUAL TABLE docs USING fts5(document_id, role, body);')
db.execute('INSERT INTO docs VALUES (?, ?, ?)', ('policy-1', 'support', '退款申请需要订单号'))
rows = db.execute('SELECT document_id, body FROM docs WHERE docs MATCH ? AND role = ?', ('退款', 'support')).fetchall()
assert rows[0][0] == 'policy-1'

Reproduction steps

  1. Prepare two short policies with role tags and retain their version fields.
  2. Write them to the FTS table with parameterized queries; never concatenate user input into SQL.
  3. Filter by role, then combine document ids and passages into cited context.
  4. Use a fixed question set to verify hits, no-result behavior, and unauthorized access.

Verification checklist

Verify that different roles cannot see each other's policies, deleted documents are no longer searchable, empty queries return a clear message, and citation ids match the passages.

Common questions

Is SQLite FTS a production vector database? No. This case validates the flow and permission boundary, not production capacity or retrieval performance.

Can the complete source document be sent to the model? Limit context length and retain the citation position.

Official references

SQLite FTS5; OpenAI Retrieval.

Last checked: 2026-11-16. Follow the linked sources for current interfaces and security boundaries; this case makes no performance or cost claim.

Further reading

KnowledgeAI Agents: From Core Concepts to Production
Product Hub Demo · V0.3