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. Authorization still holds either way, but relevance can fail: an empty query or a pattern like %% can match every authorized chunk. Reject empty chat input, and escape % / _ when the user meant a literal character.

What security boundary does a bound SQL parameter protect, and what does it not fix inside LIKE?

Answer

It keeps user data from becoming SQL syntax. It doesn't make % or _ literal characters, so escape those wildcards when literal matching is the product requirement.

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, this schema stores one permitted (chunk_id, principal) pair per row. The retrieval rule is simple:

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 search can now pull a private rule from w02 even though the UI is scoped to Acme:

The safe query wins because it checks both authorization dimensions: current workspace and current principal. Relevance alone doesn't make a row safe to inject into an Acme prompt.

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}

Why is filtering only by principal insufficient when the active support screen is scoped to workspace w01?

Answer

The same principal may have access in another workspace. Every retrieval path must enforce both active workspace and principal, or relevant rows from a different tenant can enter the prompt.

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.

An ingestion transaction inserts a document, then fails while inserting one chunk. What state should remain after rollback?

Answer

Neither the document nor any of its chunks should remain. The transaction preserves the all-or-nothing invariant and prevents a visible partial ingestion.

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 remains request-id-only: the example rejects reuse of one key for a different document_id, but a retry with the same request_id and document_id plus changed chunk_text or title returns "skipped retry" without detecting payload drift. Same key must mean identical payload. Production APIs store a request fingerprint (hash of the body) in the same row and compare on conflict so a changed body can't silently reuse the key.

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.

Indexes on principal and workspace_id help the authorization filters. They don't make LOWER(c.chunk_text) LIKE ? a fast B-tree lookup on chunk_text. Substring LIKE at scale needs full-text search, a trigram index, or a vector path; the demo corpus is small enough that a scan is fine.

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. Empty queries return no rows, so a blank chat message can't match every authorized chunk via %%. 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 forbidden rows too

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 and is off by default. Enable it explicitly (for example SET hnsw.iterative_scan = relaxed_order or strict_order, and the matching ivfflat.iterative_scan setting when you use IVFFlat), then re-measure recall under tenant and permission filters.^{[2]Reference 2pgvectorhttps://github.com/pgvector/pgvector[3]Reference 3pgvector 0.8.0 releasedhttps://www.postgresql.org/about/news/pgvector-080-released-2952/}

Keep three habits in mind:

1. Authorization predicates belong 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 replace designing and testing retrieval behavior.

Database skills used in retrieval

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