The database, for real

Your app currently has amnesia — refresh and the mock messages reset. This module gives it memory. The database you'll use is SQLite: a real, industrial-grade database that lives in a single file inside your project. No server to run, no account to create, nothing to configure — and it's not a toy: SQLite runs inside every phone and most apps you use. (At deploy time, Module 8 swaps in a hosted database — a contained, one-module swap the course structure has already prepared for, which is why nothing this week will feel wasted.)

The schema: your data model, made law

CREATE TABLE messages (
  id           INTEGER PRIMARY KEY,   -- the unique number each row gets
  sender_name  TEXT NOT NULL,
  sender_email TEXT NOT NULL,
  subject      TEXT NOT NULL,
  body         TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'new',   -- new / replied / done
  received_at  TEXT NOT NULL                  -- ISO date, sorts correctly
);

CREATE TABLE replies (
  id             INTEGER PRIMARY KEY,
  message_id     INTEGER NOT NULL REFERENCES messages(id),
  --             ^ the relationship: every reply belongs to one message
  body           TEXT NOT NULL,
  was_ai_drafted INTEGER NOT NULL DEFAULT 0,  -- 0/1 = no/yes
  sent_at        TEXT NOT NULL
);

Read it once with the assistant, then notice what it is: your spec's data model with rules attached. NOT NULL means the database refuses incomplete rows; REFERENCES means it refuses orphan replies (a gift of the course's database driver — plain SQLite tools leave that check off unless you ask); DEFAULT 'new' means new messages arrive pre-statused. The database enforcing your rules is better than your code remembering to — code forgets, schemas don't. (Learners who go on to Data Foundations will meet this idea at warehouse scale; it's the same principle at every size.)

The only four SQL verbs you'll meet (and mostly won't write)

• SELECT — read rows ('all messages, newest first'). INSERT — add a row. UPDATE — change a row ('set status to replied where id = 7'). DELETE — you know. The assistant writes these; you read them, and the reading is easy because they say what they do.
• The one concept worth internalizing: queries with placeholders (WHERE id = ?) instead of pasted-in values. It's the difference between safe code and the famous injection attack — and it's Module 6's first hardening check, so notice it now when the assistant does it right.