How to build flashcards with spaced repetition with ChatGPT

Set up flashcards with spaced repetition for me in databasedmcp, in one go.

1. Create a database called flashcards, described as "Flashcards with spaced repetition: decks, cards with their next due date, and every review".
2. Create three tables:
   - decks: id, name (required, unique), one per subject.
   - cards: id, deck_id (references decks, required), front (the question, required), back (the answer, required), interval_days (the current gap in days, defaults to 0), due (date of the next review, defaults to today), created_at (defaults to now).
   - reviews: id, card_id (references cards, required), grade (again, hard, good or easy), at (defaults to now).
3. Write the database a guide that says what each table is for and how a quiz works: ask the front, wait for my answer, show the back, ask me to grade it, then log the review and reschedule the card. The rule: again is 1 day, hard keeps the same interval, good doubles it (at least 2 days), easy triples it (at least 4 days), and due moves that many days from today. Include the query for "due now": up to 20 cards in a deck with due on or before today, earliest first.

From then on: when I give you notes, turn them into cards in the deck I name. When I say quiz me, take the due cards one at a time and keep the back to yourself until I've answered. After each grade, log the review and reschedule the card. Show me the tables when you're finished.

CREATE TABLE decks (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE cards (
  id INTEGER PRIMARY KEY,
  deck_id INTEGER NOT NULL REFERENCES decks(id),
  front TEXT NOT NULL,
  back TEXT NOT NULL,
  interval_days INTEGER NOT NULL DEFAULT 0,
  due TEXT NOT NULL DEFAULT (date('now')),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  card_id INTEGER NOT NULL REFERENCES cards(id),
  grade TEXT NOT NULL CHECK (grade IN ('again', 'hard', 'good', 'easy')),
  at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

# flashcards: spaced repetition

- **decks**: one per subject.
- **cards**: front (the question) and back (the answer). `interval_days` is the current gap, `due` the next review date. New cards are due today.
- **reviews**: one row per answer. grade: again, hard, good, easy.

Quiz: ask the front, wait for my answer, show the back, ask me to grade it. Then log the review and reschedule:
again = 1 day, hard = same interval (at least 1), good = double (at least 2), easy = triple (at least 4).

Due now: `SELECT c.id, c.front FROM cards c JOIN decks d ON d.id = c.deck_id WHERE d.name = ? AND c.due <= date('now') ORDER BY c.due, c.id LIMIT 20`

INSERT INTO decks (name) VALUES (?) RETURNING id
-- params: ["Spanish verbs"]

INSERT INTO cards (deck_id, front, back) SELECT ?, json_extract(value, '$.front'), json_extract(value, '$.back') FROM json_each(?)
-- params: [1,"[{\"front\":\"tener\",\"back\":\"to have\"},{\"front\":\"poder\",\"back\":\"to be able to, can\"},{\"front\":\"querer\",\"back\":\"to want, to love\"}]"]

SELECT c.id, c.front FROM cards c JOIN decks d ON d.id = c.deck_id WHERE d.name = ? AND c.due <= date('now') ORDER BY c.due, c.id LIMIT 20
-- params: ["Spanish verbs"]

INSERT INTO reviews (card_id, grade) VALUES (?, ?)
-- params: [1,"good"]

UPDATE cards SET interval_days = CASE ? WHEN 'again' THEN 1 WHEN 'hard' THEN max(1, interval_days) WHEN 'good' THEN max(2, interval_days * 2) ELSE max(4, interval_days * 3) END, due = date('now', '+' || CASE ? WHEN 'again' THEN 1 WHEN 'hard' THEN max(1, interval_days) WHEN 'good' THEN max(2, interval_days * 2) ELSE max(4, interval_days * 3) END || ' days') WHERE id = ?
-- params: ["good","good",1]

SELECT d.name, sum(c.due <= date('now')) AS due_today, sum(c.due <= date('now', '+7 days')) AS due_this_week, count(*) AS cards FROM decks d JOIN cards c ON c.deck_id = d.id GROUP BY d.id ORDER BY due_today DESC

SELECT c.front, c.back, count(*) AS misses FROM reviews r JOIN cards c ON c.id = r.card_id WHERE r.grade = 'again' GROUP BY c.id ORDER BY misses DESC LIMIT 10

SELECT date(at) AS day, count(*) AS reviewed, round(100.0 * sum(grade != 'again') / count(*)) AS pct_right FROM reviews WHERE at >= datetime('now', '-14 days') GROUP BY day ORDER BY day