Store the Index in a Database

Until now the index lived in a file, index.json. Every run read the whole file and wrote the whole file again. At work the index is kept in a database: it can hold millions of chunks, and the job that builds the index and the website that answers questions can be two programs that share it. In this lesson you move the index into SQLite.

One row per chunk: the table has a column for each thing a chunk knows: its source, its category, its text, the hash of the text and its vector. SQLite has no column type for a list of numbers, so the list is stored as JSON text: json.dumps on the way in, json.loads on the way out. Here is the same idea on another table, with a list of tags:

tags = ["tea", "gift"]
db.execute("INSERT INTO notes (title, tags) VALUES (?, ?)", ("Teapot", json.dumps(tags)))
db.commit()

row = db.execute("SELECT * FROM notes").fetchone()
print(row["tags"])              # one string: ["tea", "gift"]
print(json.loads(row["tags"]))  # a list again: ['tea', 'gift']

Nothing is really saved until db.commit(). Everything you did since the last commit is saved together at that moment, so another program that reads the table sees the old rows or the new ones, never half.

When your program is finished, the first run embeds every chunk and answers the six questions as before. A second build finds every vector in the table, and after the shop changes one paragraph only that chunk is embedded again:

Embedded 27 of 27 chunks
Q: Can I get my money back?
A: Yes. You can return any item within 14 days of delivery, …
Source: returns.md
…
The chunks table in index.db: 27 rows | every vector is JSON text with 64 numbers: True | every hash belongs to its text: True
The six answers used the chunks loaded from the table: True
Embedded 0 of 27 chunks
Embedded 1 of 27 chunks
Do you ship to Canada? -> Yes. Shipping to Canada costs a flat $18 and takes 7 to 10 business days. ['shipping.md']

The ranking still happens in Python: your program loads every row and scores it. At work this table lives in a vector database, such as Postgres with pgvector, or Chroma, which does the ranking too. The idea is the one you build here: a row for every chunk, with its text, its source and its vector.

Your task: the starting code already opens index.db as db. Create the chunks table with the columns source, category, text, hash and vector. In build_index, empty the table, insert every chunk as a row and commit. Write load_index, which reads the rows back as chunks, and use it in load_vectors and for the index that answers the questions.

Assignment
Follow the Coding Tutorial to create the chunks table, save the index into it and load it back with load_index.

Hint
The vector goes into the table through json.dumps and comes out through json.loads. DELETE FROM chunks goes above the insert loop and db.commit() below it, not inside it.