SQL and Data Modeling

A Python dictionary retriever works while one process holds all the facts. Close that process and the index is gone. Put two support agents on it and you also need shared truth: which workspace owns a document, which chunk may be shown to which agent, and which source supports an answer.

Structured Query Language (SQL) is the language for asking those questions of a relational database. A database stores tables. A table has named columns, and each stored fact becomes a row. Instead of trusting one program's memory, you state durable rules about data and ask the database to enforce them.

The lab builds a tiny support-policy retriever for Acme Cloud. It answers "How do I rotate my API key?" only from chunks that belong to the active workspace and are visible to the active support agent. SQLite and Python's standard-library sqlite3 interface keep the first version local. At the end, the same model extends to PostgreSQL and pgvector for semantic search.

From Python objects to rows

In memory, you might keep documents in a dictionary keyed by document_id. A relational model turns that implicit convention into explicit tables:

A primary key identifies one row in its table. A foreign key states that a value must refer to a row in another table. Here, a chunk can't point to a document that doesn't exist. A permission can't point to a chunk that doesn't exist.

Create the smallest useful schema

The first executable example creates four tables and inserts a tiny corpus. PRAGMA foreign_keys = ON asks SQLite to enforce foreign-key rules in this connection. SQLite requires that setting per connection, so applications shouldn't rely on a default. We commit the seed data so later examples can deliberately open and roll back new transactions.

The expected output confirms that the database holds two workspaces, three documents, four chunks, and six permission rows. The schema keeps ownership at the document level and visibility at the chunk level. That's intentional. Two paragraphs in one document may later have different visibility, and every retrieved paragraph can still lead back to its source document.

Select rows with parameters

SELECT chooses columns from rows. WHERE narrows the rows. ORDER BY makes output deterministic.

Never join user input into SQL text with string formatting. Use placeholders and pass values separately. Python's SQLite documentation recommends placeholders for binding values because constructing query text from input opens SQL injection vulnerabilities.

The SQL statement has a ? where a value belongs, and the tuple (workspace_id,) contains the value. That separation lets the database receive code and data separately.

This pattern stays important in AI applications. A chat message is still untrusted input, even when another model wrote it. Don't let it rewrite query structure.

Binding keeps a value from becoming SQL syntax. It doesn't change the meaning of the SQL operation: inside LIKE, % and _ in a bound value still act as wildcard characters. Escape them when the product needs literal substring matching, or use a search index with the tokenization rules your product expects.

Join a chunk to its source

The answer generator needs more than matching text. It needs the title or document ID that lets the UI cite the evidence. A join combines rows whose keys match.

Here, chunks.document_id refers to documents.id. The query can return a paragraph and its source title in one result set:

LIKE is enough for this tiny vocabulary demonstration. It isn't semantic retrieval: it will miss paraphrases such as "rotate credentials" when no row contains "key." The production extension later adds vector similarity without discarding tenant and permission checks.

Constraints catch broken relationships

Code can be buggy. An ingestion job can create a chunk for a document ID that never arrived. Without a constraint, the orphaned chunk may appear in retrieval results with no trustworthy source.

The foreign key in chunks turns that silent corruption into a visible failure. Under Python's default sqlite3 transaction handling, a data-changing statement opens a transaction implicitly. A failed statement reverts its own change, and rollback() closes the failed unit before the lab continues:

Primary keys catch duplicate identities. Foreign keys catch broken references. A NOT NULL column catches absent required values. These rules belong in the schema because every writer must obey them, including the current Python script.

Make permissions queryable data

An access-control list says which principals may view a resource. Instead of hiding that list in application logic, our beginner schema stores one permitted (chunk_id, principal) pair per row. That gives us one simple rule:

Return a chunk only when its document belongs to the active workspace and a permission row exists for the active principal.

Alice (agent:u1) may read key and account help. Bob (agent:u3) may read key paragraphs for Acme, but not Acme account-help content.

Break it: forget the workspace predicate

Bob also has permission to read one row in w02. That's useful for showing the failure rather than assuming the rule is correct.

Suppose the active support screen is displaying Acme (w01), but a programmer filters only by user and query word. Bob's key result now includes another workspace's private rule:

The safe query isn't safe because its chunks sound relevant. It's safe because both authorization dimensions are present: current workspace and current principal.

For a production PostgreSQL application, row-level security (RLS) can make a database policy enforce an additional boundary. RLS isn't automatic: once it's enabled for a table, normal access must satisfy a policy, and an enabled table with no policy uses default deny. Superusers and roles with BYPASSRLS always bypass those policies. Table owners normally bypass them too unless the table uses FORCE ROW LEVEL SECURITY. Run application queries with a role that's subject to the policy, then test that role directly.^{[1]Reference 1PostgreSQL Row Security Policieshttps://www.postgresql.org/docs/current/ddl-rowsecurity.html}

Use a transaction for all-or-nothing ingestion

A transaction groups changes into one unit. Either every change commits, or a failure rolls them back. That matters when a new document and its chunks must appear together. A visible document with missing chunks is confusing; visible chunks with no source are worse.

The next example uses with conn: so Python commits an open transaction on success or rolls it back when the body raises an exception. The context manager doesn't open a transaction by itself. With Python's default sqlite3 handling, the first INSERT inside the block opens one. The second INSERT intentionally points at a missing source, so its foreign-key error rolls back the document insertion too.

When an embedding service is involved, you have two reasonable designs. You can compute embeddings before the database transaction and commit document plus chunks together, or write a pending ingestion record and publish a new complete version only after all chunks are ready. Don't expose half an index to retrieval without making that state deliberate.

Keep retries from duplicating completed work

A transaction makes one attempt all-or-nothing. It doesn't tell a worker whether the request already succeeded before a timeout. If the worker commits new rows, crashes before sending its response, and retries the same request, a second write attempt can duplicate work.

For retried writes, store a stable idempotency key in the same transaction as the rows created for that request. A uniqueness constraint lets the database accept the first attempt and recognize later repeats even after the original process disappears:

The first call stores the request marker, document, and chunk together. The retry sees the same request key and skips the write. This example also rejects reuse of one key for a different document_id. Production APIs often store a request fingerprint too, so the same key can't silently accept a changed payload.

This pattern works when the protected side effect lives in the same database transaction. A local marker can't atomically cover a deployment API or another remote service. For an external side effect, pass the same key to a downstream API that enforces it, or use a workflow such as an outbox with an idempotent consumer.

Index the predicates you run often

Without an index, a database may need to inspect many rows to answer a filter. An index is an additional data structure that lets the engine reach selected rows more directly, at the cost of storage and write work.

Our authorized query repeatedly filters permissions by principal and follows document ownership. Add indexes for those paths, then ask SQLite for its query plan:

The exact plan text can vary as engines and statistics change. The engineering habit is stable: inspect the query plan for the production query, with realistic tenant and permission selectivity, rather than assuming an index solved a latency problem.

Build a small authorized retriever

Now assemble a function the product could call. It retrieves permitted matching paragraphs and returns document titles beside them. A later answer generator can quote or cite those rows rather than inventing policy text.

This is lexical search: it looks for a word fragment. For a tiny demo, that keeps attention on durable data rules. A retrieval-augmented generation (RAG) system usually also retrieves semantically related text before a large language model (LLM) drafts an answer. That retrieval path should reuse the same source and permission model.

Test boundary rows, not happy paths alone

Tests for retrieval should include forbidden results. A system that finds the best paragraph and leaks it isn't correct.

That final test is short, but it guards the central failure mode: context must be relevant and authorized before it reaches an LLM prompt.

Extend the model with PostgreSQL and pgvector

SQLite gave us a fully runnable lab. Production systems often move shared workloads to PostgreSQL. When keyword matching is insufficient, the pgvector extension adds a vector column and distance operators. A vector is a list of numbers representing an embedding; nearby embeddings can capture semantic similarity even when exact words differ.

This sketch uses three dimensions only so you can read it. In a real system, choose the dimension required by your embedding model and store/query vectors of that same dimension.

In pgvector, <=> means cosine distance, so 1 - distance is cosine similarity. The query works without HNSW: pgvector performs exact nearest-neighbor search by default. Add an HNSW index when exact scans no longer meet the latency target, then measure the recall tradeoff introduced by approximate search. The IS NOT NULL guard keeps chunks that are still waiting for embeddings out of semantic ranking; pgvector doesn't index null vectors.^{[2]Reference 2pgvectorhttps://github.com/pgvector/pgvector}

Filtered search has one caveat. With an approximate vector index, filtering conditions can be applied after the index scans candidates. If tenant or permission filters reject many candidates, a query may return fewer rows than requested even when allowed matches exist. pgvector documents iterative index scans for continuing the search when filtering removes too many candidates; that capability arrived in version 0.8.0.^{[2]Reference 2pgvectorhttps://github.com/pgvector/pgvector[3]Reference 3pgvector 0.8.0 releasedhttps://www.postgresql.org/about/news/pgvector-080-released-2952/}

The rule is precise:

1. Authorization predicates must remain in every retrieval query.
2. Approximate vector retrieval still needs plan inspection and recall testing under restrictive filters.
3. A database policy such as PostgreSQL RLS can reinforce authorization, but it doesn't remove the need to design and test retrieval behavior.

What to remember

You started with four tables, not a production vector stack:

Mastery check

Evaluation rubric

• Foundational: Explains a table, row, primary key, and foreign key using the support-policy schema.
• Developing: Writes a parameterized SELECT and a JOIN that returns chunk text beside its source document.
• Intermediate: Demonstrates why both workspace scope and principal permission belong in an authorized retrieval query.
• Applied: Uses a transaction, durable idempotency key, and indexes to keep ingestion consistent and inspect a real query path.
• Advanced: Extends the authorized schema with vector similarity while preserving permission filters and testing filtered approximate retrieval.

Follow-up questions

Common pitfalls

• A query returns a relevant paragraph from another tenant. Cause: retrieval filters by principal or similarity but omits active workspace_id. Fix: keep both ownership and permission predicates in every context query.
• A retrieved chunk has no document to cite. Cause: ingestion allowed orphaned chunk rows. Fix: require a foreign key from chunks.document_id to documents.id.
• A new document appears with only some chunks available. Cause: multi-row ingestion was committed in fragments. Fix: publish a complete version inside a transaction or after staged ingestion completes.
• A timed-out ingestion creates duplicate rows when retried. Cause: each attempt commits independently with no durable request identity. Fix: store a stable idempotency key in the same transaction and enforce uniqueness.
• A chat message changes query behavior. Cause: application code formatted input into SQL text. Fix: bind user and model-produced values as parameters.
• A filtered vector query returns too few results. Cause: approximate candidates were removed by tenant or permission filtering. Fix: test recall and use appropriate pgvector scan tuning while preserving authorization.