Final assignment

What you will be able to do

Use every module of the course on a new storage problem. You design and build the storage of a second Larkfield service, the document assistant, from the access patterns to the restore drill. Then you prove with tests, plans and measurements that the database keeps its rules, that its queries are safe and fast, and that you can get the data back and delete it.

Before you startWhat to know and to have readyAbout 180 minutes

• You completed Modules 1 to 5. Each part below names the lessons that it uses.
• You do not need the case studies for this assignment. The certificate asks for at least one of them.
• Your ticket-api at the end of Module 5, or the snapshot end-of-m05, as Use a snapshot describes. You start a new project, but you may copy code from ticket-api: the migration runner, the pool, the FileStore contract and the scripts. Say in your report what you copied and what you changed.
• Docker with PostgreSQL 18 and pgvector, and Azurite, as in the course. No account and no money are needed. Hosted storage is optional.
• The embedding model is optional. Without it, your chunks have no vectors, but Part 1 still asks you to design for them.
• You can do the assignment in more than one session. Run the tests after each change.

The situation: Larkfield’s document assistant

In the first module, you placed the data of a document assistant in stores, on paper. Now Larkfield builds it. Staff upload the help desk’s policy documents. Agents ask questions while they work on a ticket, and the assistant answers from the documents, with citations. Priya wants every model call recorded, to compare models and prompts. Grace asks her usual question: “If a staff member leaves, or a document is withdrawn, can we delete it everywhere?”

The service is called docs-assistant. It has its own folder, ~/projects/docs-assistant, and its own database, docs. It stores:

• Documents: each upload is one version of one document, with a title, an owner, a status (draft, published or withdrawn), dates and who may read it. The original file goes to object storage.
• Chunks: pieces of the text of each document version, with their position, and an optional vector with its embedding version.
• Questions and answers: who asked, the ticket that the question came from, the answer, and the chunks that the answer cites.
• AI runs: each model call, with the model, the prompt version, tokens in and out, the cost, the time and the status.

The tickets stay in ticket-api. A question stores the ticket’s ID as text, but it cannot have a foreign key to a table in another service’s database. Decide what that means for your rules, and write it down.

Your data. Use the 36 policy documents in data/source/documents/ of your ticket-api as the uploads. They are Markdown files, CC0, the RAG course’s collection. Write a seeded script that generates the questions, answers and AI runs. Make two sizes, as the course did. The small one you can read row by row. In the large one, the query that you tune in Part 3 clearly reads a whole table. All the data is made up: say so in your report.

Part 1: the inventory and the schema

Uses Access-pattern inventory, Storage categories, Ownership and source of truth, Entities and relationships and Integrity constraints.

1. Write the access-pattern inventory: each entity, its queries, how often it changes, how much there is, and how fresh each answer must be. Name the three queries that run most often.
2. Write the storage map. For each piece of data, give its store, and say whether it is an original or a copy. For a copy, say what it is made from and how to make it again.
3. Draw the ERD, with the cardinality of every relationship.
4. Write the schema as plain-SQL migrations, applied by a migration runner that records a checksum of each file. Give every table a primary key. Add NOT NULL, unique, CHECK rules and foreign keys where a rule exists. At least these: one published version per document at a time; a stored file has a size and a checksum; a cost is never negative, and it is not a floating-point number; a citation points to a chunk that exists; every vector names its embedding version, and exactly one version is current.
5. Choose the delete rule of every foreign key (CASCADE, RESTRICT or SET NULL). Give one sentence of reason for each.

Part 2: safe access from the API

Uses Transactions, Safe queries, Connection management and Migrations.

1. Put every query in a repository module. Every value that comes from a request is a parameter. If a name must go into the SQL text, it comes from your code or your database, never from the request. Say why in a comment.
2. Add at least these endpoints: upload a document (Part 4), list the questions of one ticket, list the AI runs for Priya with filters on model and prompt version (Part 3), and record an answer.
3. Record an answer in one transaction: the question, the answer, its citations and its AI run. If any part fails, nothing is stored.
4. Use a connection pool that opens at start-up and closes at shutdown. No connection is held while the API talks to object storage or to a model.
5. Write one migration that changes an existing table after data is in it. Rehearse it on your large data, and report its time and whether reads waited.

Part 3: a measured index improvement

Uses Read a query plan, Index deliberately and Bound result sizes.

1. Load the large data. Choose one slow endpoint from your inventory, for example Priya’s list of AI runs for one model and prompt version, newest first.
2. Measure it before any change, with a fixed workload: the same requests, the same concurrency and the same number of passes. Record the median and the p95, and save the result in a file. Read the plan of each query that the endpoint sends, and find where the time goes.
3. Make one change at a time: an index for a measured query, fewer round trips, keyset pagination, named columns. After each change, read the plan again and run the same workload.
4. Measure what each new index costs: its size, and the time of a batch of inserts with it and without it.
5. Show that the result repeats: two runs after the change, close to each other, and a check that the workload was the same.

Part 4: files, retention and recovery

Uses Object-storage patterns, Vector storage and Recovery and retention.

1. Store the document files through a FileStore contract: Azurite in development, and a filesystem fake in the tests. Uploads and downloads use signed URLs with a short expiry. The row starts as pending, and becomes stored only after the API has checked the real size and checksum. Choose a size limit, and enforce it at complete and in a CHECK.
2. Write a backup script that takes the dump and the manifest from one snapshot. Write a restore drill that restores into an empty database and runs its checks, including a check that the stored files are in object storage.
3. Delete everywhere, in two cases. First, a document is withdrawn: its files, chunks, vectors and any cached answers go, but the AI runs keep their cost. Second, a staff member leaves: delete their questions and answers, or remove the person’s details from them. Delete the rows in one transaction with an outbox for the files, then delete the files.
4. Write a scan that finds orphan files, missing files, waiting deletions and stale chunks: chunks made from a text that has changed since.
5. Write the retention plan on one page. Say what you back up, how often and for how long, and how you prove a backup. List where each piece of data lives, and how long you keep questions and AI runs. Say what happens to a deletion when you restore an older backup.

The failure paths you must check

A working happy path is not enough. Show each of these with a test or a recorded run, and say which:

1. The database rejects a duplicate, an orphan and a value that breaks a CHECK. The error comes from PostgreSQL, not only from your Python code.
2. An injection input, such as x' OR '1'='1, in a filter returns no extra rows.
3. A failure in the middle of “record an answer” leaves no partial answer, and afterwards the pool has all its connections free.
4. Editing an applied migration stops the runner with a clear message.
5. A signed URL fails for another file name, for the wrong action and after it expires (403). A file larger than the limit ends as rejected, and the file is gone.
6. A restore drill fails for a real reason that you constructed, for example a missing file or a manifest that does not match. Label it as constructed.
7. A deletion that stops after the database step (constructed) is found by the scan, and the scan finishes it.
8. If you have vectors: a search never compares two embedding versions, and a filtered search returns enough results.

Fresh-environment run

Before you submit, prove that the project works from nothing, on your computer:

1. Copy the project to a new folder, without .venv, .env, backups/ and the generated data.
2. Delete the database and the files with docker compose down --volumes, then start them again.
3. Make a new virtual environment, install from requirements.txt, and make .env from .env.example.
4. Apply the migrations, load the small data, and run the tests. Then generate and load the large data, and run your benchmark once.
5. Make a backup, and run the restore drill: every check passes.

Paste the commands and their outputs into your report. If a step needed something that is not in the README, fix the README, and run the steps again.

What to submit

Your docs-assistant folder (without .env, .venv, backups/ and the large data), with your migrations, code, tests, scripts, the benchmark files and REPORT.md:

# Larkfield document assistant: storage design
## Inventory and storage map        (the entities, the top queries, what is an original and what is a copy)
## Schema and constraints           (the ERD, the migrations, each rule and delete rule with its reason)
## Safe access                      (the repository, the one-transaction answer, the pool, the rehearsed migration)
## Measured improvement             (before and after: plans, workload, median and p95, index sizes, write cost)
## Files                            (the FileStore contract, the signed URLs, the size limit)
## Backup, restore and retention    (the drill, the failed drill, the deletions, the scan, the one-page plan)
## Failure paths                    (each of the eight, with the test or the run that shows it)
## Fresh-environment run            (the commands and their outputs)
## Limits                           (made-up data, your computer, what you copied, what you did not test)

How it is marked

A reviewer marks each required item demonstrated, partially demonstrated or not yet demonstrated, with a concrete reason. To complete the course, all four must be demonstrated. You can improve an item and submit it again.

Required item	Demonstrated	Partially demonstrated, for example	Not yet demonstrated, for example
Schema and constraints	The migrations apply to an empty database. Every rule of Part 1 is a constraint, and a test shows that PostgreSQL rejects a duplicate, an orphan and a broken CHECK. Each delete rule has a reason. A file row becomes stored only after a check.	The rules exist, but one is only in Python. Or a delete rule has no reason. Or the cost is a real.	A table has no primary key. Or the database accepts a citation of a chunk that does not exist.
Parameterized queries	Every request value is a parameter, and the injection input returns no extra rows. “Record an answer” is one transaction: a failure stores nothing and returns its connection.	All values are parameters, but a failed answer leaves a question with no answer. Or a new connection is opened for each request.	A query is built from request text with an f-string or +, even once.
Measured index improvement	The before and after runs use the same data and workload, and a check proves it. Each change has its plan, and the improvement repeats. Each new index has its size and write cost.	The endpoint is faster, but only one run was measured. Or the plans are missing, or the write cost was not measured.	Indexes were added with no measurement before them. Or the two runs used different data.
Backup, restore and retention plan	The drill passes in the fresh-environment run, and a constructed failure makes it fail. The deletions reach the rows, the files and the vectors, and the scan finds a half-finished one. The plan answers every question of Part 4, step 5.	The backup works, but the drill does not check the files. Or the plan does not say what happens to a deletion after a restore.	No restore was ever run. Or a deletion leaves files that nothing finds.

Whatever else is strong, the work is not complete while a data-integrity check, a signed-URL access check or a deletion check fails. A fast endpoint does not make up for a rule that the database does not enforce.

The course’s own results can help you judge yours, but they are not targets. On the course’s computer, the ticket list went from 375 ms to 2.8 ms per page, and the restore drill passed 7 of 7 checks. Your service, your data and your computer are different.

AI assistants

You may use an AI assistant to practise, to explain an error, or to suggest a query or a test. Check each suggestion, and be ready to explain your final work. For each constraint, index and deletion step, say what it does, why you chose it, and which test or measurement proves it. Mark in your report the decisions that are your own, and the suggestions that you rejected, with the reason. An assistant may not produce your evidence: every plan, time and count in your report comes from a run on your computer.

Common mistake

The endpoint is fast, but the benchmark is not the same

Symptom: the “after” median is 50 times lower than the “before” median. But between the two runs, you also loaded the small data instead of the large data, or changed the page size.

Cause: the comparison measures two changes at once. The faster result can come from less data, not from your index.

Fix: save the workload with each result, and compare the two files before you compare their numbers, as in Bound result sizes. Load the same data again, and change one thing at a time.

Recap

In this course, you gave an AI service a storage design that it can trust:

• Choose storage by workload: an inventory of access patterns, a store for each piece of data, and one original for every copy.
• Design the schema: keys, constraints and delete rules that the database enforces, and transactions that keep a change whole.
• Access it safely: parameterized queries, a connection pool, and migrations rehearsed on representative data.
• Make it fast with measurements: query plans, one index for one measured query, keyset pages, and the same workload before and after.
• Files, vectors, backups and deletion: signed URLs, embedding versions, a restore drill that can fail, and a deletion that reaches every store.

If you have not done one yet, choose a case study for the certificate: Design and tune storage for an open public dataset or Vector search over an open document collection.