#269 Data modelling 7 min
Schema Design for RAG: Chunks, Versions, Sources in SQLite
Asked one question, a one-table design I built without tenant, version or access columns returned 3 chunks in its top 4 that the user should not get: an old version, another tenant's text, and a note she may not read.
Read the three runs
Copy the code folder first. Run python run.py search, python run.py reindex and python run.py delete. For the search run, write down which chunk comes first from the schema and which of the four one-table rows are not labelled ok. Nothing here uses a model or the network.
A hint
The labels old version, other tenant and no access say why a row should not reach the user.
Ask a different question
In run.py, change the QUESTION text to Is shipping free? and run python run.py search. Predict which chunk comes first from the schema. When I ran it, the first schema result was Refund policy v2 chunk 2 at 1.000, then chunk 1 at 0.535. Change the question back to the refund question afterwards, because the next tasks expect it.
A hint
The toy vectors count only five words, including shipping and free.
Ask as another user
In run.py, change every "ana" to "gus" in the search part and run python run.py search. Gus works in support at globex. Predict what the schema returns and which cite line appears. When I ran it, the schema returned only the globex refund chunk at 0.949, cited to the globex address. The header line still says ana, because that text is typed in. Change "gus" back to "ana" afterwards, because the next task starts from the original.
A hint
Members says which tenant and group each user is in.
Ask as the HR user
In run.py, change every "ana" to "hal" in the search part and run python run.py search. Hal is in the hr group at acme. Predict what the schema returns. When I ran it, the only result was the Absence note at 0.707, with a citation to it. The header line still says ana because that text is typed in.
A hint
Only the hr group may read the absence note, and Hal is the only acme member in that group.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Made-up documents, companies and people; the one-table design was written without tenant, version or access columns on purpose; toy five-word vectors instead of an embedding model and no language model; the delete covers this SQLite database only, not backups or copies, and is not legal advice.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
Related videos
- #208 Right to Erasure Across a Pipeline: Delete, Scan, Restore Data governance
- #133 A/B Test Analysis in SQL and Python: z Test and Peeking SQL and analytics
- #157 Model Registry by Hand: Versions, Promote, Roll Back MLOps and deployment
- #238 Event-Driven AI Agents: Queues, Crashes and Duplicates Enterprise architecture and AI agents
