Data Architecture: Databases, Files, Events, and Knowledge

Why it matters#

Code can be rewritten in an afternoon. Data can't. Once real students have uploaded notes and built months of progress, every mistake in how that data is modeled, stored, or deleted becomes a migration, a cleanup job, or a promise you can't keep.

AI agents make data decisions casually. Asked for a feature, an agent will create a table, a folder of files, a cache, or a new service, because that's the quickest way to make the feature work. Each choice is reasonable on its own. Together they scatter personal data across stores that nobody has listed, and some of them won't be included when you back up, secure, or delete.

The architect's job is to decide where data lives before agents do, by looking at how the data is used, and to keep a map that makes privacy and deletion promises checkable.

Learning objectives#

After this chapter you will be able to:

1. Model a product's data, its relationships, and its constraints, and estimate access patterns and growth before choosing a store.
2. Choose between relational tables, flexible documents, object storage, and caches for each kind of data.
3. Design note search that combines keyword and semantic retrieval, filters by owner, and records where each result came from.
4. Use queues and events safely, with retries, dead letters, and idempotent consumers.
5. Classify data, apply least privilege, and plan backup, recovery, and deletion as one lifecycle.
6. Produce a data inventory and data dictionary that agents and reviewers can check against the code.

7.1 Modeling data before choosing a database#

Entities, relationships, IDs, and constraints#

Start with the things the product keeps track of, not with a database product. Chapter 6 modeled users, profiles, sessions, plans, tasks, and activity. Notes add three more:

• Note: one uploaded file, its title, its status (uploaded, processing, ready, failed, from chapter 5), and its owner.
• Note chunk: a passage of extracted text, a few paragraphs long, that search can find and point back to.
• Feedback: an AI-generated comment on a note, such as a summary or a quiz question, which chapter 9 will produce.

Each needs an ID, a value that identifies it permanently. Use random IDs (UUIDs) for anything a student can see in a URL. Sequential numbers like /plan/42 tell anyone how many plans exist and invite guessing the next one. Random IDs make guessing impractical, but they are not a security control: the ownership checks from chapter 6 are still what keeps students apart.

Then write the rules down as constraints, so the database refuses data that breaks them. PostgreSQL's documentation explains the reason well: data types are too coarse to express rules like “only positive prices,” and constraints let you control the data as tightly as you want, with an error whenever a write would break one (PostgreSQL, Constraints). The ones you'll use most:

• NOT NULL: the value must be present. Every note has an owner.
• UNIQUE: no two rows share the value. One chunk per note, version, and position.
• FOREIGN KEY: the value must match a row in another table. A chunk's note_id must name a real note. With ON DELETE CASCADE, deleting the note deletes its chunks.
• CHECK: any condition you can write. A note's status is one of four values.

The missing foreign key on note_chunks is why Priya's text survived. A constraint the agent didn't write is a rule the database can't enforce.

Read/write access patterns and expected growth#

An access pattern describes how the data is used: what is read or written, how often, by what key, and how fast it must be. Write them down before choosing storage, because the right store depends on the pattern, not on the data's name.

Then estimate growth, with the assumptions in plain sight. At 200 students, 30 notes each, 2 MB per note, files total about 12 GB. If each note splits into 40 chunks, that's 240,000 chunks. If each chunk has an embedding of 1,536 numbers (section 7.3), and pgvector stores each number in 4 bytes plus 8 bytes per vector (pgvector, README), the embeddings take about 1.5 GB.

Those numbers are small. One PostgreSQL database handles them comfortably, which is the first answer to the agent's pull request. Redo the estimate when the assumptions change by ten times, not before.

Structured, semi-structured, and unstructured content#

Data comes in three shapes:

• Structured: fixed fields with known types. Plans, tasks, users. Tables fit.
• Semi-structured: fields that vary from record to record. The settings a student picked, or the raw response from an AI model, which may gain fields over time.
• Unstructured: files and free text. Lecture PDFs, images of handwritten notes.

Most products have all three. The usual mistake is forcing one shape into a store built for another: PDFs stored as giant database values, or plans stored as loose documents that each screen interprets differently.

Lifecycle, retention, and deletion#

Every kind of data is created, used, changed, and eventually deleted or archived. Retention is how long you keep it. Decide that when you create a table, not when someone asks.

A data dictionary records these decisions per field or per table. Here is part of the Learning Coach's:

The retention periods are illustrative assumptions. Yours depend on what you need and on the law where you operate.

7.2 Core data stores#

Relational databases and transactions#

A relational database stores data in tables with rows and columns, links tables with keys, enforces constraints, and groups changes in transactions (chapter 6). PostgreSQL is the book's default, and for most of the Learning Coach's data it is the right home: plans, tasks, users, notes metadata, chunks, and even embeddings.

The case for one database is about the system around it more than performance. One thing to back up, secure, monitor, and delete from. Transactions that cover everything. Constraints between tables. Every additional store multiplies those jobs.

Document stores and flexible schemas#

A document store keeps each record as a self-contained document, usually JSON, and doesn't require every document to have the same fields. That flexibility suits semi-structured data. It also moves the job of checking structure from the database into every piece of code that reads the documents.

Often you don't need a second database for this. PostgreSQL has a jsonb type that stores JSON in a decomposed binary form, which is slightly slower to write but much faster to process, and can be indexed. Its documentation recommends jsonb for most applications (PostgreSQL, JSON Types). A model_response jsonb column on the feedback table gives you a flexible field next to strictly typed ones like note_id and created_at.

Use flexibility for data whose shape you genuinely don't control, like an outside service's response. Don't use it to avoid deciding what a plan looks like. A dedicated document store becomes worth considering when most of your data is semi-structured and you need to spread it across many servers, which is not the Learning Coach's situation.

Object storage and file metadata#

Object storage keeps files (called objects) under a key, like notes/5b1e.../lecture-3.pdf, cheaply and at almost any size. It is the right home for PDFs and images. A database is not.

The pattern is a split: the file goes to object storage; everything you need to query about it (owner, title, size, status, the object key) goes in a database row. Questions like “whose note is this?” are answered from the row, never from the file's name or folder.

Keep the files private and let the server decide who may download them. In Amazon S3, which many storage services imitate, objects are private by default, and the owner can create a presigned URL that grants time-limited permission to download one object (AWS, Sharing objects with presigned URLs). The Learning Coach's API checks ownership, then returns a link that expires in a few minutes. (Service details checked October 2026.)

The split has one cost, and Priya found it: the database can't cascade a delete into object storage. Deleting the row must also schedule deleting the file, which section 7.4 handles.

Caches and ephemeral state#

A cache keeps a copy of data somewhere faster, so repeated reads don't redo slow work. Ephemeral state is data you can afford to lose: a rate-limit counter, a half-finished upload, a cached search result.

Three rules keep caches from becoming bugs:

• A cache is never the only copy. If losing it would lose data, it isn't a cache.
• Every entry expires. Set a time limit so stale data ages out even if you forget to clear it.
• Measure first. Add a cache when a measured read is too slow and can't be fixed in the query. At the Learning Coach's size, an index usually beats a cache server.

The cache in the agent's pull request had no measured slow read behind it, so it was a new store to secure and delete from, with nothing to gain.

7.3 Search and semantic retrieval#

Students asked for “search my notes.” That sounds like one feature. It is two kinds of search, and they find different things.

Keyword indexes#

Keyword search finds passages containing the query's words. A naive version scans every row with LIKE '%mitosis%'. PostgreSQL's full-text search does better: it normalizes words so that satisfies matches satisfy, drops very common words, ranks results by relevance, and can use an index instead of reading every row (PostgreSQL, Full Text Search). Each chunk stores its text in a searchable form (a tsvector column), with an index on it.

Keyword search is precise and explainable. It fails when the student's words differ from the notes' words: a search for “cell division” misses a passage that only says “mitosis.”

Embeddings and vector retrieval#

An embedding is a list of numbers, produced by an AI model, that represents the meaning of a piece of text. Texts with similar meanings get similar lists, so “cell division” and “mitosis” land close together. Vector retrieval finds the chunks whose embeddings are closest to the query's embedding.

The pgvector extension adds this to PostgreSQL, so embeddings can live in the same table as the text they describe. By default it performs exact nearest-neighbor search, which finds the true closest matches. Adding an approximate index, such as HNSW, makes search faster on large tables by giving up some recall: the guarantee of finding every true match (pgvector, README).

At 240,000 chunks split among 200 students, each student's search covers about 1,200 chunks. Exact search over one student's chunks, found through an ordinary index on user_id, is fast and needs no approximate index.

Metadata filters and hybrid search#

Metadata is data about each chunk: its owner, its note, its page, its date. Filtering by metadata does two jobs. It narrows results (“only my biology notes”) and, more importantly, enforces authorization: a student searches only her own chunks.

The order of filtering matters. pgvector's documentation warns that with approximate indexes, filtering happens after the index is scanned. With its default settings, if a condition matches 10% of rows, an HNSW query returns only about four matches on average. It also notes that one approximate index shared by many tenants lets each tenant's vectors affect the others' recall (pgvector, README). That's a reason, at this size, to filter by owner first and search exactly.

Hybrid search runs both kinds of search and merges the lists. A simple, well-tested way to merge is reciprocal rank fusion: each result scores 1/(k + its rank) in each list, the scores are added, and the total decides the order. Its authors fixed k at 60 and found that it almost always improved on the best of the rankings it combined, and consistently beat Condorcet fusion, an established method for merging rankings (Cormack et al., 2009). It needs no tuning, because it uses ranks, not scores that differ in scale between methods.

-- backend/coach/notes/queries.py: SEARCH_CHUNKS
WITH mine AS (
  SELECT id, tsv, embedding FROM note_chunks
  WHERE user_id = %(user_id)s AND chunk_version = %(current_version)s
),
keyword AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank(tsv, q) DESC) AS rank
  FROM mine, plainto_tsquery('english', %(query)s) AS q
  WHERE tsv @@ q
  ORDER BY rank LIMIT 20
),
semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> %(query_embedding)s::vector) AS rank
  FROM mine
  ORDER BY rank LIMIT 20
)
SELECT id, sum(1.0 / (60 + rank)) AS score
FROM (SELECT * FROM keyword UNION ALL SELECT * FROM semantic) AS ranked
GROUP BY id
ORDER BY score DESC
LIMIT 10;

The first block applies the owner filter before anything else. <=> is pgvector's cosine distance operator; smaller means closer.

Versioning, relevance, and provenance#

Search results are only useful if you can say where they came from. Provenance is that record: which note, which page, which version of the extraction, and which embedding model produced the vector. Store it on every chunk:

• note_id and page, so a result links back to the exact place in the student's file.
• chunk_version, which changes when you change how text is split.
• embedding_model, the name and version of the model. Embeddings from different models can't be compared, so when you change models you must re-embed every chunk, and the query must use the same model.

Relevance is whether the results are actually useful, and it has to be checked, not assumed. Keep a small set of real queries with the passages that should come back (say 30 from tester sessions, an assumption) and rerun them whenever you change chunking, models, or ranking. Chapter 9 builds on this when the coach starts answering questions from notes.

7.4 Event-driven data flows#

Synchronous calls vs. asynchronous messages#

A synchronous call waits for an answer: the browser asks for this week's tasks and waits. An asynchronous message is sent and handled later: “note 5b1e was uploaded” goes into a queue, and a worker extracts the text when it can.

Use asynchronous messages when the work is slow (extracting text from a 40-page PDF), when it can fail and be retried without the student waiting (embedding chunks), or when one action should trigger several independent reactions. Keep synchronous calls for anything the student is waiting to see.

Queues, streams, and event schemas#

A queue holds messages until a worker takes one, processes it, and removes it. Each message is handled by one worker. A stream keeps an ordered, durable log of events that many readers can read, each at its own pace. Apache Kafka, a widely used streaming platform, stores events durably for as long as you configure, and doesn't delete them after they are read (Apache Kafka, Introduction). Streams suit many teams reading the same events. A two-person product with one worker needs a queue.

For the Learning Coach, the queue can be a PostgreSQL table. A worker claims the next job with SELECT ... FOR UPDATE SKIP LOCKED, which skips rows other workers have locked. PostgreSQL's documentation says this option is unsuitable for general queries but can avoid contention when several consumers read a queue-like table (PostgreSQL, SELECT). No new service is needed, and jobs are backed up with everything else.

Whatever carries the messages, give them a schema: a fixed, documented shape. CloudEvents, a vendor-neutral specification for event formats, requires every event to carry an id, a source, a type, and a spec version. It also says a producer that resends an event because of a network error may reuse the same id, and consumers may treat events with the same source and id as duplicates (CloudEvents, 2022). Even if you never adopt the specification, copy those fields.

{ "id": "7d1c9f3e-...", "source": "coach/notes", "type": "note.uploaded", "specversion": "1.0",
  "time": "2026-10-12T14:03:00Z", "data": { "note_id": "5b1e...", "user_id": "a83f..." } }

The event carries IDs, not the note's content or the student's email. Anything that needs more looks it up, with an ownership check, from the owning module.

One more problem hides here. If the API saves the note row and then sends the event, a crash between the two leaves a note that never gets processed. If it sends first, a failed save leaves an event for a note that doesn't exist. The transactional outbox pattern solves this: write the event into an outbox table in the same transaction as the note, and let a separate process deliver it. The event is sent if and only if the transaction commits (Richardson, Transactional outbox). With a PostgreSQL queue, the outbox and the queue can be the same table.

Retries, dead letters, and duplicate handling#

Jobs fail: the PDF is corrupt, the embedding service is down. Retry a few times with growing waits (chapter 5), then stop. A dead-letter queue holds messages that failed too many times, so they stop circulating and someone can look at why. Amazon SQS, for example, moves a message to a dead-letter queue after a configured number of receives (AWS, Dead-letter queues). In a PostgreSQL queue, a failed status and an attempts count do the same job. Alert on it, and show the student a failed note with a retry button (chapter 5's R4).

Duplicates are the harder part. Most queues deliver at least once: AWS's documentation tells you to expect occasional repeats and design consumers to be idempotent (AWS, At-least-once delivery). The outbox relay can also deliver twice if it crashes after sending (Richardson, Transactional outbox). So every consumer must be safe to run twice. The failure clinic shows what happens when one isn't.

Eventual consistency#

When work happens asynchronously, different parts of the system briefly disagree. Right after upload, the note exists but search can't find it yet. Werner Vogels defines eventual consistency as a guarantee that, if no new updates are made, all reads eventually return the latest value. The gap before that happens is the inconsistency window (Vogels, 2008).

Eventual consistency is fine when the product admits it. The notes list shows “processing” until the note is searchable, so the student isn't confused by a search that misses a file she just uploaded. It isn't fine for rules that must hold at every moment, such as “a deleted student's notes can't be downloaded.” That check belongs in the synchronous path: the download endpoint refuses as soon as the account is marked for deletion, even while the cleanup job is still running.

7.5 Data governance#

Data classification and least privilege#

Classification sorts data by how much harm its exposure would cause, so that protection matches risk. Four levels cover most products:

Notes are classified sensitive because you can't know what a student uploads. A psychology student's notes may include her own health history. Under the EU's GDPR, data concerning health is one of the special categories whose processing is prohibited unless a listed exception applies, such as explicit consent (GDPR, Article 9). That's a question for legal review, not for an agent.

Least privilege means each user or process gets the minimum access it needs to do its job (NIST, Least privilege). Apply it to the system's own parts, too:

• The API's database role can read and write application tables, but can't drop tables or change roles. Migrations run under a separate role.
• The extraction worker can read uploaded files and write chunks. It can't read users' emails.
• An analytics query runs as a read-only role on aggregated tables.
• Agents get a development database with fake data, never production credentials.

Access logging and audit trails#

Two kinds of records matter. Application logs help you debug. An audit trail records who did what to which data, so you can answer questions later: who downloaded this note, who ran this deletion.

OWASP's logging guidance asks each event to record when, where, who, and what. It lists things that shouldn't be logged directly, including session identifiers, access tokens, passwords, and sensitive personal data, and it suggests removing or pseudonymizing identifiers where the person's identity isn't needed. It also asks you to protect logs from tampering and unauthorized access (OWASP, Logging Cheat Sheet). For the Learning Coach, that meant replacing sending welcome email to priya@... with sending welcome email to user a83f..., which still helps debugging and stops making the logs a second copy of the user table.

Backup and recovery#

Two numbers define what recovery must achieve. The recovery point objective (RPO) is the point in time to which data must be recovered after an outage (NIST, RPO), in practice how much recent data you can afford to lose. The recovery time objective (RTO) is how long recovery can take before the outage seriously harms the mission (NIST, RTO). For an MVP, an RPO of minutes and an RTO of a few hours is a reasonable starting assumption.

Nightly copies give an RPO of up to a day. PostgreSQL's continuous archiving keeps a base backup plus the stream of changes after it, which lets you restore the database to any moment since the base backup, for example just before someone dropped a table (PostgreSQL, Continuous Archiving and PITR). Most managed PostgreSQL hosts offer this as a setting.

Back up object storage too, or turn on versioning, and test the whole thing. A backup you have never restored is a hope, not a plan. Restore to a scratch environment every quarter, time it against your RTO, and check that the app works with the restored data.

Compliance requirements by domain#

Some obligations depend on where you operate and what data you hold. Under the GDPR, people have a right to have their personal data erased without undue delay when, for example, it is no longer needed or they withdraw consent, with listed exceptions such as legal obligations (GDPR, Article 17). Health data carries extra conditions (Article 9, above). Products sold to schools may fall under laws about education records, and products for children under laws about children's data; the Learning Coach's 18+ requirement (NFR-7) keeps it out of the second group, but doesn't settle the first.

The architect's part is to make compliance possible: know where the data is, be able to delete it everywhere, and keep records of what you did. Deciding which laws apply and what they require needs a lawyer who knows your market.

Architect's decision: where should note search live?#

AI collaboration recipe#

Agents are fast at drafting schemas, migrations, and queries, and at listing where data might be hiding in a codebase. They are weak at noticing data they didn't create and at knowing your retention promises. Give them the data dictionary and ask them to prove coverage.

1. Build the data inventory.

Without changing anything, list every place this system stores or sends data about a student: database tables and columns, file storage paths, queue and outbox tables, caches, logs, backups, and outside services (identity provider, email, AI model). For each, give the file and line where it's written, the classification from docs/data-dictionary.md, and whether account deletion currently removes it. Mark anything not in the data dictionary.

2. Propose a schema from access patterns.

Here are our entities and the access-pattern table. Propose PostgreSQL tables with types, primary keys, foreign keys with their ON DELETE behavior, unique and check constraints, and the indexes each access pattern needs. Explain each index by the query it serves. Don't add new data stores.

3. Review a migration for data safety.

Review this migration. For each statement: does it lock a large table, can it lose data, is the downgrade real, and does it keep the data dictionary true? Flag any column holding personal data that has no deletion path.

4. Test the consumers.

For each queue consumer, write a test that delivers the same message twice and checks the result is the same as delivering it once. Then write one that makes the consumer fail midway and checks the retry completes cleanly.

Boundaries to state every time:

• “Don't add a new data store, cache, or queue service without an ADR. Ask first.”
• “Every new table with student data gets a foreign key to its owner with a deletion rule, and a line in the data dictionary.”
• “Never log emails, note content, tokens, or session IDs.”
• “Use the development database with fake data. You have no production access.”

Worked example: deleting a student's data everywhere#

Maya's increment answers Priya's question for every future student.

1. The data inventory. Prompt 1 from the recipe produced a list, which Maya checked by reading the code it cited. It found everything in the opening scenario, plus one place she hadn't thought of: the outbox table, which held note.uploaded events with Priya's user ID for 30 days after delivery.

2. Fix the model. A migration added the missing foreign key from note_chunks.note_id to notes.id with ON DELETE CASCADE, and a user_id foreign key to users. Before adding them, it deleted 3,100 orphaned chunks whose notes no longer existed, which a count query had found. Delivered outbox rows now expire after 7 days.

3. Design the deletion flow. Deletion has a fast synchronous part and a slower asynchronous part:

flowchart TD
    A["Student confirms deletion"] --> B["One transaction:<br/>mark account deleting,<br/>end all sessions,<br/>write account.deletion_requested to outbox"]
    B --> C["Student sees: deletion started,<br/>signed out"]
    B --> D["Deletion worker picks up the event"]
    D --> E["Delete files from object storage<br/>(missing file counts as done)"]
    E --> F["Delete identity provider account"]
    F --> G["Delete user row:<br/>cascade removes profile, plans,<br/>tasks, activity, notes, chunks"]
    G --> H["Write deletion record:<br/>user ID and time only"]
    H --> I["Backups expire within 14 days;<br/>restores replay the deletion records"]

The first transaction makes the account unusable at once: all sessions end, and every endpoint refuses a user marked deleting. The worker does the slow part. Each step is idempotent, so a retry after a crash simply repeats steps that already happened: deleting a file that's already gone counts as success, and deleting a user row that doesn't exist changes nothing.

4. Face the backup conflict. Chapter 3's NFR-6 said deleting an account removes all of the student's data within 7 days. With 30-day backups, that was false. Maya's options were to shorten backups, or to change the promise. She did some of both: backups now expire after 14 days, and NFR-6 now says “Deleting an account removes the student's data from live systems within 24 hours and from backups within 14 days. If a backup is restored, deletions recorded since that backup are applied again before the system goes live.” The deletion records hold only the user ID and time, so keeping them doesn't keep personal data. She marked the new wording for legal review.

5. Test it. An integration test creates a student with a plan, an uploaded note, extracted chunks, and an outbox event; deletes the account; runs the worker twice; and then checks every place in the inventory:

# backend/tests/test_account_deletion.py
def test_deletion_leaves_nothing_behind(student_with_everything, run_worker, files, identity):
    user_id, object_keys = student_with_everything
    request_deletion(user_id)
    run_worker()
    run_worker()

    for table in STUDENT_TABLES:
        assert count_rows(table, user_id=user_id) == 0, table
    assert all(not files.exists(key) for key in object_keys)
    assert identity.deleted == [user_id]
    assert deletion_record_exists(user_id)

STUDENT_TABLES is generated from the data dictionary, so a new table with student data that isn't in the dictionary fails a separate test. Maya ran the test against the old code first: it failed on note_chunks and on the file check. Then she replied to Priya with what had been deleted, what would expire from backups and when, and an apology for the wait.

Verification: does the data match the map?#

Failure clinic: every passage twice#

Symptom. A week after search launched, testers reported that results showed the same passage two or three times. The embedding service's bill for the month was almost double the estimate.

Reproduce. Maya uploaded a 60-page PDF and counted the chunks: 180, where 60 were expected. A small PDF produced the right count.

Diagnose. The extraction worker claimed a job, processed it, and marked it done at the end. The agent had given claimed jobs a lease of 30 seconds: if a job wasn't marked done within that time, it became available again, so a crashed worker's jobs wouldn't be lost. That is the same idea as a visibility timeout in managed queues, where an unfinished message becomes visible to other consumers once the timeout expires (AWS, Visibility timeout). Large PDFs took about 70 seconds. So the job reappeared twice while the first worker was still busy, three workers processed the same note, and each inserted its own chunks and paid for its own embeddings. Small files finished within the lease, which is why every test passed.

Fix. Three changes:

1. Make the consumer idempotent. A unique constraint on (note_id, chunk_version, position), and inserts that do nothing on conflict. A second run can't create a second copy.
2. Extend the lease while working. The worker renews its claim every 20 seconds while processing, so a slow job that is still running isn't handed to another worker.
3. Clean up. A migration kept one chunk per (note_id, chunk_version, position) and deleted the rest, after a count confirmed the numbers.

A test now runs the extraction twice on the same note, and once with a lease shorter than the job, and checks for exactly one set of chunks.

Lesson. Queues, leases, and outbox relays will all hand the same job to your code more than once, sooner or later. Plan for that on day one: a consumer that runs twice should leave the data exactly as one run would. And test with your slowest real input, because a small sample finishes before the lease runs out and hides the problem.

Architect's checklist#

Before agents build on new data:

• ☐ The entity model and access-pattern table exist, with growth assumptions written down.
• ☐ Every table has keys, foreign keys with deliberate ON DELETE rules, and the constraints its rules need.
• ☐ Each kind of data is in a store chosen for its access pattern; any new store has an ADR.
• ☐ Files live in private object storage, with metadata and ownership in the database.
• ☐ Caches are never the only copy, and every entry expires.
• ☐ Search filters by owner first and stores provenance for every result.
• ☐ Events have IDs and schemas, use an outbox when tied to a database change, and every consumer is idempotent.
• ☐ Failed jobs end in a dead-letter state that someone is alerted about.
• ☐ Data is classified; roles follow least privilege; logs hold no personal data.
• ☐ Backups have a stated RPO and RTO, and a restore has been tested.
• ☐ The data inventory covers every store, and the deletion test checks all of them.
• ☐ Sensitive data and compliance questions are flagged for legal review.

Exercises#

Beginner. For the Meal Planner (fridge contents and diet in, a week of meals and a shopping list out), list the entities, write an access-pattern table with five operations and your assumed frequencies, and classify each kind of data. Which data could count as health data? What would you ask a lawyer about it?

Intermediate. For the Trip Planner, design storage for itineraries, saved places, uploaded tickets and booking confirmations, and price lookups from outside providers. Decide which store each goes in and why, write a data dictionary with retention for each, and design the event flow that updates an itinerary when a flight changes. Include the event schema and how the consumer handles a duplicate.

Advanced. For the Shop Help Chat, design hybrid search over the shop's help articles and each customer's own order history. Write the query so a customer can never retrieve another customer's orders, record provenance for every result, and build a relevance set of 20 questions. Then ask an agent to “speed up search with an approximate index,” and test whether the owner filter still returns enough results for a customer with few orders.

Lab, artifact, and review#

Lab. Design the Learning Coach's schema for users, learning artifacts (plans, tasks, notes, chunks), uploads, and AI-generated feedback. Justify where each kind of data is stored, using your access-pattern table, and build the deletion flow with its test.

Deliverable. Add to your portfolio:

• an ER diagram of the full schema, with foreign keys and their ON DELETE rules;
• a data dictionary with classification, owner module, and retention for every table;
• a data inventory covering every store, including logs, backups, and outside services;
• a lifecycle policy: retention, deletion, backup expiry, and what happens on restore;
• a migration plan for one real change, using expand, migrate, contract;
• ADR-003 on where search lives;
• the deletion test and the run-twice consumer tests, each seen failing and then passing.

Reflection#

• If a user of your product asked “is my data gone?” today, could you answer with evidence? What would you have to check?
• Which of your data stores was created by an agent without a decision behind it?
• Where does your system promise something (like deletion within 7 days) that another part of it (like backups) quietly contradicts?
• Which of your background jobs would do harm if it ran twice?

Chapter 8 turns to the edges of the system: the APIs, tools, and outside services the Learning Coach depends on, and how to integrate them without letting their failures become yours.

References#

• Apache Software Foundation. Introduction. Apache Kafka documentation.
• AWS. Amazon SQS at-least-once delivery. Amazon Simple Queue Service Developer Guide.
• AWS. Amazon SQS visibility timeout. Amazon Simple Queue Service Developer Guide.
• AWS. Sharing objects with presigned URLs. Amazon S3 User Guide.
• AWS. Using dead-letter queues in Amazon SQS. Amazon Simple Queue Service Developer Guide.
• CloudEvents. (2022). CloudEvents specification, version 1.0.2. Cloud Native Computing Foundation.
• Cormack, G. V., Clarke, C. L. A., & Büttcher, S. (2009). Reciprocal rank fusion outperforms Condorcet and individual rank learning methods. Proceedings of SIGIR 2009.
• European Union. GDPR Article 9: Processing of special categories of personal data. General Data Protection Regulation.
• European Union. GDPR Article 17: Right to erasure. General Data Protection Regulation.
• NIST. Least privilege. Computer Security Resource Center glossary.
• NIST. Recovery point objective. Computer Security Resource Center glossary.
• NIST. Recovery time objective. Computer Security Resource Center glossary.
• OWASP. Logging Cheat Sheet. OWASP Cheat Sheet Series.
• pgvector. pgvector README. GitHub.
• PostgreSQL Global Development Group. Constraints. PostgreSQL documentation.
• PostgreSQL Global Development Group. Continuous Archiving and Point-in-Time Recovery. PostgreSQL documentation.
• PostgreSQL Global Development Group. Full Text Search: Introduction. PostgreSQL documentation.
• PostgreSQL Global Development Group. JSON Types. PostgreSQL documentation.
• PostgreSQL Global Development Group. SELECT. PostgreSQL documentation.
• Richardson, C. Pattern: Transactional outbox. microservices.io.
• Vogels, W. (2008). Eventually consistent. All Things Distributed.

Links checked October 2026.