SQL and Data Modeling

Your retriever finds the right paragraph. Then the process restarts, and its document dictionary is empty. The Data Structures for AI lesson made lookup efficient inside one process; now the documents need a home that outlives it.

Structured Query Language (SQL) lets you read and change data in a relational database. The database stores durable state in tables. Named columns describe fields, and each stored fact is a row. Constraints turn assumptions into rules the database can enforce, even when another process writes the data.

Use a small, fictional support-policy corpus for Acme Cloud. Its current API-key policy says "rotate within seven days." An older version says ninety days. Both paragraphs can match a search, but only one should support a current answer. This policy is sample data, not security advice.

SQLite and Python's standard-library sqlite3 interface keep the lab local and runnable. Run the Python cells in order in one session; they share the connection and tables. The later PostgreSQL sections are extensions, not additional software you need for the SQLite lab.

From Python objects to rows

In memory, you might keep documents in a dictionary keyed by document_id. A database makes those identities and their relationships explicit. A workspace separates one customer's data from another's; a chunk is a paragraph we might retrieve; a principal is the user or service whose access we're checking:

Now the links are data rather than a convention in application code. In-memory Python dictionaries let you nest chunks inside documents, but they leave integrity to developer discipline. Separate writes to a persistent store can leave a document without its chunks if an ingestion worker fails halfway through. A relational schema turns relationship assumptions into database-enforced rules: a primary key uniquely identifies each row, while a foreign key ensures references point to existing records. Our references specify NOT NULL, so a chunk can't omit its source document or point into the void. A transaction will later keep the related writes together.

For properties that vary across parsers (like token counts, header hierarchies, or custom chunk metadata), modern AI architectures combine normalized relational columns with hybrid JSON or JSONB attributes. The relational skeleton owns identity, tenancy, and authorization; the hybrid layer carries flexible document metadata without breaking foreign-key guarantees.^{[1]Reference 1Constraintshttps://www.postgresql.org/docs/current/ddl-constraints.html}

Create the smallest useful schema

The first example creates four tables and inserts a tiny corpus. CREATE TABLE declares columns and constraints; INSERT INTO adds rows. TEXT holds strings, NOT NULL requires a value, and UNIQUE rejects a repeated combination of values. A CHECK rejects a row when its condition is false.

The database file lives in a fresh temporary directory so rerunning the lesson won't overwrite earlier work. A file-backed connection can be closed and reopened without losing committed rows. sqlite3.connect(":memory:") would be convenient for scratch work, but it would lose the database when that connection closes.^{[2]Reference 2sqlite3: DB-API interface for SQLite databaseshttps://docs.python.org/3/library/sqlite3.html}

Its validity interval is half-open: active at valid_from, inactive at valid_to. A NULL valid_to means the version has no known end yet. This local SQLite lab stores canonical UTC timestamps as text; the PostgreSQL version later uses TIMESTAMPTZ.

PRAGMA foreign_keys = ON enables SQLite's foreign-key rules on this connection. Don't depend on the build's default setting.^{[3]Reference 3SQLite Foreign Key Supporthttps://www.sqlite.org/foreignkeys.html} SQLite also preserves a historical exception: an ordinary TEXT PRIMARY KEY can accept NULL unless it explicitly says NOT NULL. PostgreSQL primary keys reject NULL automatically, but this lab declares both constraints.^{[4]Reference 4CREATE TABLEhttps://www.sqlite.org/lang_createtable.html}

We commit the seed data so later examples can open and roll back new transactions on purpose.

Those counts describe two workspaces, four document versions, five chunks, and seven permission rows. executemany repeats one parameterized insert for each Python tuple. commit() makes those inserts a completed unit rather than leaving them pending on this connection.

d7-old and d7 share source_key = "api-key-policy", but their validity intervals don't overlap. Ownership and version time live on the document; visibility lives on the chunk. Two paragraphs in one document can have different visibility, and every retrieved paragraph still points at the exact source version.

Select rows with parameters

SELECT chooses columns. WHERE keeps some rows and drops the rest. ORDER BY makes the output order something you can test. This query keeps Acme versions that have no recorded end time.

Keep the query shape and its values separate. Put a placeholder in the statement and pass the value beside it. That split prevents untrusted data from changing the statement's structure through SQL injection.^{[2]Reference 2sqlite3: DB-API interface for SQLite databaseshttps://docs.python.org/3/library/sqlite3.html}

The SQL statement has a ? where a value belongs, and the tuple (workspace_id,) holds that value. execute runs the query; fetchall collects its result rows as Python tuples. These rows have no end time, which isn't yet the same as proving they're active now: a future version could also have valid_to IS NULL.

Placeholder spelling depends on the driver. Python's SQLite interface uses ?; PostgreSQL prepared statements use $1 and $2, as shown in the PostgreSQL PREPARE reference. Use the parameter API your driver gives you. Keep values out of SQL text.

NULL means missing or unknown, not an ordinary value. Comparisons with it yield an unknown truth value, so valid_to = NULL doesn't select open-ended rows. Write valid_to IS NULL. PostgreSQL follows the same three-valued rule in its comparison documentation.

Binding keeps a value from becoming SQL syntax. A chat message is still untrusted input, even when another model wrote it. It must not rewrite the statement shape.

Join a chunk to its source

Matching text isn't enough for an answer generator. The UI also needs the title, version, or document ID that makes the evidence citable. A join combines rows according to a condition. Ours matches a chunk's document reference to the corresponding document identity.

Here, chunks.document_id refers to documents.id. Follow that link and one result set can return both the paragraph and its source title:

For c7a, document_id is d7, so the join attaches the title and dates from d7. It doesn't attach d7-old merely because that row has the same title. AS c and AS d are short table aliases, and c.id identifies which table supplies the column. The pattern %key% was bound as a value, not pasted into SQL text.

In LIKE, % matches any sequence of characters and _ matches one character. Binding doesn't make either wildcard literal. This exploratory query intentionally uses %key% to find key anywhere in the text. Before exposing search to a caller, we'll escape user-supplied wildcards and reject blank input.

It also showed a new problem. Both the retired ninety-day rule and the current seven-day rule match key, so a correct join alone doesn't decide which fact was valid for this request.

Query the version that was valid at a specific time

Now ask a time-bound question: which policy was true at a particular instant? The lab uses a fixed timestamp so every rerun has the same answer. A version is valid when valid_from <= as_of and either its end is unknown or as_of < valid_to:

The half-open interval [valid_from, valid_to) gives a clean boundary: at exactly 2026-07-01T00:00:00Z, d7-old is inactive and d7 is active.

SQLite's text comparison works here because every timestamp uses the same fixed-width UTC format. Mixing offsets or formats would break that ordering.

The schema prevents duplicate start times for a source, but it doesn't reject overlapping intervals. For example, a second row beginning June 15 and ending July 15 could overlap both versions without violating any current constraint. An ingestion transaction must check the existing intervals under appropriate concurrency control, or PostgreSQL can enforce a suitable exclusion constraint.^{[1]Reference 1Constraintshttps://www.postgresql.org/docs/current/ddl-constraints.html}

PostgreSQL should store these instants as TIMESTAMPTZ; it normalizes zoned input internally and converts it for display in the active session time zone.^{[5]Reference 5Date/Time Typeshttps://www.postgresql.org/docs/current/datatype-datetime.html}

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 PostgreSQL extension later adds vector similarity without discarding time, tenant, or permission checks.

Group rows to inspect the corpus

Retrieval returns individual chunks. Operators often need a summary instead: how many current chunks does each Acme source contribute? GROUP BY collects input rows that share the named keys, and COUNT(*) reduces each group to one number.

At AS_OF, predict two chunks for d7 and one for d9; retired d7-old should contribute none. The query makes that prediction testable:

WHERE filters rows before grouping, so retired d7-old never contributes to these counts. To filter completed groups, such as keeping sources with at least two chunks, add HAVING COUNT(*) >= 2 after GROUP BY.^{[6]Reference 6Aggregate Functionshttps://www.postgresql.org/docs/current/tutorial-agg.html}

An ordinary JOIN also drops documents with no matching chunks. For an ingestion audit that must show zero-chunk documents, use LEFT JOIN chunks AS c ON c.document_id = d.id and COUNT(c.id). A left join preserves the document with null chunk fields; counting c.id gives zero, whereas COUNT(*) would count that placeholder row as one.

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, that orphan may show up in retrieval with no trustworthy source.

Predict the next failure: inserting a chunk for missing-document should stop before it becomes data. The foreign key on 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. The explicit NOT NULL catches SQLite's otherwise-accepted missing text identity. Foreign keys catch broken references.

These rules belong in the schema because every writer must obey them, including the current Python script. Remove NOT NULL from chunks.id and the second check stops printing blocked null primary key: SQLite accepts the missing identity even though the column says PRIMARY KEY.

Make permissions queryable data

Integrity tells us which rows connect. It doesn't tell us who may read one. 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. The query below turns that distinction into rows we can inspect:

In an application, the authenticated session supplies principal, and the server verifies the requested workspace against that session. They aren't claims a chat message or model may choose. The function accepts them as arguments here only so the lab can exercise several identities explicitly.

A missing workspace predicate leaks a row

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 scoped query checks both dimensions: current workspace and current principal. Bob can read c99 in a different request, but that doesn't make it valid context for an Acme answer. This is a cross-workspace disclosure in the active request, not evidence that Bob lacks all access to c99.

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.^{[7]Reference 7PostgreSQL Row Security Policieshttps://www.postgresql.org/docs/current/ddl-rowsecurity.html}

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.

Use a transaction for all-or-nothing ingestion

A database transaction groups changes into one unit. A commit makes that unit permanent; a rollback discards it. 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.

Use with conn: here so Python commits an open transaction on success or rolls it back when an exception escapes the block. Catch the exception outside the block: swallowing it inside could let earlier writes commit. The context manager doesn't open a transaction by itself.^{[2]Reference 2sqlite3: DB-API interface for SQLite databaseshttps://docs.python.org/3/library/sqlite3.html}

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:

The transaction protects database rows, not a remote embedding service. You have two reasonable designs: 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.

Atomicity doesn't choose what concurrent readers see

Rollback demonstrates atomicity: one transaction's writes appear together or not at all. Isolation answers a different question: what can one transaction observe while another transaction commits changes?

PostgreSQL defaults to Read Committed isolation. Each ordinary SELECT sees a snapshot taken when that statement begins, so two SELECT statements in one transaction can observe different committed states.

That behavior is fine for many request paths. It can undermine an evaluation export that first counts current chunks and later reads them, because a concurrent ingestion may commit between those statements.^{[8]Reference 8Transaction Isolationhttps://www.postgresql.org/docs/current/transaction-iso.html}

When several reads must describe one stable corpus snapshot, use a read-only Repeatable Read transaction. This is PostgreSQL syntax, not part of the SQLite lab:

Repeatable Read fixes the snapshot after the first non-control statement, so the count and detail rows agree even if another transaction commits later.

Serializable isolation is stricter when concurrent transactions enforce a cross-row business rule, but PostgreSQL may abort one transaction with a serialization failure. Applications using that level must retry the whole transaction rather than the final statement alone.

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 same key must also mean the same input. Store a fingerprint of all input fields alongside it. Here, json.dumps gives a fixed field list a consistent representation, and SHA-256 turns those bytes into a compact comparison value. A changed title or paragraph must raise an error, not be mistaken for a successful retry.

The first call stores the request marker, document, and chunk together. An identical retry skips the write. Reusing req-30 with different text raises an error and leaves the original policy untouched. The fingerprint is an equality check, not authentication; the server still owns workspace and user identity.

This function leaves the new chunk ungranted, so retrieval won't expose it yet. A complete ingestion operation would also write the intended permission rows before publishing the document. Keeping that default closed is safer than treating a missing permission as public access.

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. A plan describes an access strategy, not measured runtime work.^{[9]Reference 9EXPLAIN QUERY PLANhttps://www.sqlite.org/eqp.html}

The exact plan text can vary as engines and statistics change. The habit doesn't: inspect the plan for the production query, with realistic tenant and permission selectivity, rather than assuming an index fixed latency.

In this corpus, a sequential path inspects all seven chunk_permissions rows. An index on (principal, chunk_id) can seek the four rows for agent:u3 (c7-old, c7a, c7b, c99). Workspace and validity predicates then drop the retired version and the other-workspace chunk, leaving c7a and c7b.

Those are candidate-set sizes, not a benchmark or a count of storage operations. An index still has to traverse its own structure, and the planner can choose a different join order. Whatever path it picks, the authorization rule must stay unchanged.

Query-plan check: PostgreSQL uses EXPLAIN for estimated plans and EXPLAIN ANALYZE for actual row counts and timing. The latter executes the statement, including writes. Use a read query here. A surrounding rollback can undo ordinary database writes, but it can't undo every possible effect, such as a function calling an external service. Plans from tiny tables also don't predict plans for large ones.^{[10]Reference 10Using EXPLAINhttps://www.postgresql.org/docs/current/using-explain.html}

Indexes on principal, workspace, and validity time give the planner candidate access paths for authorization and version 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. This demo corpus is small enough that a scan is fine.

Build a small authorized retriever

The schema, filters, and indexes now fit one function a caller can test. It returns permitted matching paragraphs beside their document titles. The query above uses ESCAPE '!': !% matches a literal %, !_ matches a literal _, and !! matches a literal !. Escape those characters in the user's text before adding our own surrounding % wildcards.

This is lexical search: it looks for a literal text fragment. A query of % now searches for an actual percent sign rather than matching every row. Blank queries return no rows. The SQL parameter still protects statement structure; escaping protects the product's substring-search behavior. Neither one supplies authorization.

Passing as_of keeps a retired policy out of current answers and still lets a historical audit reproduce an older one. A retrieval-augmented generation (RAG) system usually also retrieves semantically related text before a large language model (LLM) drafts an answer. That path should reuse the same source, time, and permission model.

Test forbidden rows too

Test forbidden results as well as matches. Make the forbidden fixture match the query first: a cross-workspace test using token would be weak here because c99 contains key, not token. It would return nothing even if the workspace predicate were missing.

The tests guard the central retrieval boundary: context must be relevant, authorized, and valid at the requested time before it reaches an LLM prompt.

The historical assertion also proves that keeping old versions isn't the same as leaking them into current answers.

Close the connection and read the file again

So far, every query has used the same open connection. Test the promise that motivated the database: after committing and closing it, can another connection recover the same authorized rows?

The global conn name is rebound below, so search_support now queries the reopened file. No schema creation or seeding runs a second time:

The file, not the connection object, preserved the rows. Cleanup removes this lab's file only after the check. An application would use a stable path on persistent storage and keep it across runs. Don't rerun the seed script against that existing database; open it and query it. A file inside an ephemeral container filesystem still disappears when that container is removed, so the Docker storage distinction applies here too.^{[2]Reference 2sqlite3: DB-API interface for SQLite databaseshttps://docs.python.org/3/library/sqlite3.html}

Extend the model with PostgreSQL and pgvector

SQLite gave us a fully runnable lab without a separate database server. A PostgreSQL server becomes useful when application processes on several machines need a shared database and its concurrency controls. The table relationships stay useful, but adapt column types, driver placeholders, and transaction behavior rather than assuming the files are interchangeable.

When keyword matching isn't enough, 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 the exact words differ.

This PostgreSQL sketch assumes you've installed pgvector on the server and created the equivalent base tables, using TIMESTAMPTZ for the validity columns. $1 through $5 are server-side query parameters, not literals to paste into an ordinary SQL console. A Python driver may use different placeholder syntax.

Three vector dimensions keep the declaration readable. Choose the dimension required by your embedding model in a real system, and embed the query with the same model and preprocessing as the stored vectors. Matching dimensions alone doesn't make two models' vector spaces compatible. Embeddings live in their own table so each derived feature keeps its source chunk, model identifier, and creation time.

In pgvector, <=> means cosine distance, so 1 - distance is cosine similarity. The query works without an approximate index: pgvector performs exact nearest-neighbor search by default. HNSW (Hierarchical Navigable Small World) builds a graph of nearby vectors to search a promising subset instead of exhaustively comparing every vector.

Add an HNSW index when exact scans no longer meet the latency target, then measure the recall tradeoff introduced by approximate search.^{[11]Reference 11pgvectorhttps://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 workspace or permission filters reject many candidates, a query may return fewer rows than requested even when allowed matches exist.

Iterative index scans continue searching when filters remove too many candidates. Available since pgvector 0.8.0 and off by default, they can be enabled for this HNSW example with SET hnsw.iterative_scan = strict_order. Scanning still stops at configured limits, so this isn't a guarantee of exact recall or a full result set. Re-measure with realistic workspace, model, and permission filters.^{[11]Reference 11pgvectorhttps://github.com/pgvector/pgvector[12]Reference 12pgvector 0.8.0 releasedhttps://www.postgresql.org/about/news/pgvector-080-released-2952/}

Vector check: Four checks carry into vector retrieval:

1. Authorization predicates belong in every retrieval query.
2. Version and embedding-model predicates make current answers reproducible.
3. Approximate vector retrieval still needs plan inspection and recall testing under restrictive filters.
4. A database policy such as PostgreSQL RLS can reinforce authorization, but it doesn't replace designing and testing retrieval behavior.

Keep feature and evaluation lineage in the schema

An embedding is a derived feature. If an evaluation improves after changing the embedding model, you need to know which feature row produced each ranked result. Store that identity instead of keeping only a floating-point score.

The next PostgreSQL tables record one evaluation run and the ranked embedding rows it observed:

embedding_id leads back to the chunk and embedding model. eval_run_id records dataset and query-model versions. Preserve those rows as immutable versions: overwriting the vector or source text in place would change what an old result points to. Use a versioned model identifier and a new chunk identity when the source text changes.

For a reproducible evaluation, also record the retrieval query/configuration, requested policy time, and corpus snapshot. Those fields aren't in this minimal schema yet. A stored foreign key identifies evidence; it doesn't freeze every input that produced the ranking.

A timestamp alone can't answer that question because two runs may use different models at the same time.

This schema stores ranked evidence alongside scores. Computing recall or hit rate also requires relevance labels for each evaluation query; rank and similarity alone don't say whether a result was correct. Keep those labels in the versioned dataset named by the run.

Database skills used in retrieval

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