How to build a reading log with Grok

Set up a reading log for me in databasedmcp, in one go.

1. Create a database called reading, described as "Reading log: books I want to read, am reading and have finished, with ratings, notes and progress, filed by Grok".
2. Create two tables:
   - books: id, title (required), author (required), pages, tags (a JSON array of lowercase strings like ["fiction", "sci-fi"], defaults to []), status (want, reading, finished or abandoned, defaults to want), started_on (date), finished_on (date), rating (1 to 5), notes (what I thought, in my words), added_at (defaults to now).
   - progress: id, book_id (references books), on_day (date, defaults to today), page (required).
3. Write the database a guide that says what each table is for, that the latest progress row for a book is where I am, to look a book up by title before adding it so there are no duplicates, and the queries for "how's my reading year going" (books, pages and average rating finished this year) and "which tags I read most and rate highest" (json_each over tags).

From then on: when I mention a book I want to read, add it with its author, pages and tags. When I start one, set it to reading with started_on. When I tell you what page I'm on, add a progress row. When I finish, set finished_on, my rating and my notes; when I give up on one, set it to abandoned. Show me the tables when you're finished.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  pages INTEGER,
  tags TEXT NOT NULL DEFAULT '[]',
  status TEXT NOT NULL DEFAULT 'want' CHECK (status IN ('want', 'reading', 'finished', 'abandoned')),
  started_on TEXT,
  finished_on TEXT,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  notes TEXT,
  added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE progress (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  on_day TEXT NOT NULL DEFAULT (date('now')),
  page INTEGER NOT NULL
);

# reading: books and progress

- **books**: one row per book. status: want, reading, finished, abandoned. `tags` is a JSON array of lowercase strings, e.g. ["fiction", "sci-fi"]. `rating` is 1 to 5, set when I finish. `notes` is what I thought, in my words.
- **progress**: the page I'm on, one row each time I say. The latest row per book is where I am.

Look a book up by title (LIKE) before adding it.

This year: `SELECT count(*) AS books, sum(pages) AS pages, round(avg(rating), 1) AS avg_rating FROM books WHERE status = 'finished' AND finished_on >= date('now', 'start of year')`

By tag: `SELECT t.value AS tag, count(*) AS books, round(avg(b.rating), 1) AS avg_rating FROM books b, json_each(b.tags) t WHERE b.status = 'finished' GROUP BY t.value ORDER BY books DESC`

INSERT INTO books (title, author, pages, tags) VALUES (?, ?, ?, ?), (?, ?, ?, ?)
-- params: ["The Salt Orchard","Ilse Varga",342,"[\"fiction\", \"literary\"]","Quiet Engines","Tomas Aalto",214,"[\"fiction\", \"sci-fi\", \"novella\"]"]

UPDATE books SET status = 'reading', started_on = date('now') WHERE id = ? AND status = 'want'
-- params: [1]

INSERT INTO progress (book_id, page) VALUES (?, ?)
-- params: [1,112]

UPDATE books SET status = 'finished', finished_on = date('now'), rating = ?, notes = ? WHERE id = ?
-- params: [4,"The middle dragged, but the last chapter made up for it.",1]

SELECT count(*) AS books, sum(pages) AS pages, round(avg(rating), 1) AS avg_rating FROM books WHERE status = 'finished' AND finished_on >= date('now', 'start of year')

SELECT t.value AS tag, count(*) AS books, round(avg(b.rating), 1) AS avg_rating FROM books b, json_each(b.tags) t WHERE b.status = 'finished' GROUP BY t.value ORDER BY avg_rating DESC, books DESC

SELECT title, author, pages FROM books WHERE status = 'want' AND pages < ? ORDER BY pages
-- params: [300]