Alternative to Day One: a journal with Claude

Set up a journal for me in databasedmcp, in one go.

1. Create a database called journal, described as "My journal: one entry per day or more, with mood, place and tags, written by Claude from what I tell it".
2. Create two tables:
   - entries: id, written_on (date, defaults to today), body (required, in my words, lightly tidied, never summarised away), mood (1 is a rough day, 5 is a great one; optional), place (optional), created_at (defaults to now).
   - entry_tags: entry_id (references entries), tag (lowercase word), one row per pair.
3. Write the database a guide that says what each table is for, to keep my words rather than rewrite them, that adding to today's entry means appending to its body, and the queries for "on this day in past years", "mood by month" and "entries with a tag".

From then on: whenever I describe my day, write an entry for it with a mood if I give one and a few tags. If I add more later the same day, append to that entry. When I ask about the past, search my entries. Show me the tables when you're finished.

CREATE TABLE entries (
  id INTEGER PRIMARY KEY,
  written_on TEXT NOT NULL DEFAULT (date('now')),
  body TEXT NOT NULL,
  mood INTEGER CHECK (mood BETWEEN 1 AND 5),
  place TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE entry_tags (
  entry_id INTEGER NOT NULL REFERENCES entries(id),
  tag TEXT NOT NULL,
  PRIMARY KEY (entry_id, tag)
);

# journal

- **entries**: one row per entry. `body` is in my words, lightly tidied; don't summarise. mood: 1 (rough) to 5 (great), or NULL. To add to today's entry, append to its body.
- **entry_tags**: lowercase tags (family, work, garden), one row per tag per entry.

On this day: `SELECT written_on, mood, body FROM entries WHERE strftime('%m-%d', written_on) = strftime('%m-%d', 'now') AND written_on < date('now') ORDER BY written_on DESC`

Mood by month: `SELECT strftime('%Y-%m', written_on) AS month, round(avg(mood), 1) AS mood, count(*) AS entries FROM entries GROUP BY month ORDER BY month DESC`

By tag: `SELECT e.written_on, e.body FROM entries e JOIN entry_tags t ON t.entry_id = e.id WHERE t.tag = 'garden' ORDER BY e.written_on DESC`

INSERT INTO entries (body, mood, place) VALUES (?, ?, ?) RETURNING id
-- params: ["Took the kids to the lake. Leo swam out to the raft for the first time.",5,"Birchmoor"]

INSERT INTO entry_tags (entry_id, tag) VALUES (?, ?), (?, ?)
-- params: [1,"family",1,"summer"]

UPDATE entries SET body = body || char(10) || ? WHERE id = ?
-- params: ["Dinner at the pier café. The sunset was ridiculous.",1]

INSERT INTO entries (written_on, body, mood) VALUES (date('now', '-1 day'), ?, ?) RETURNING id
-- params: ["Long day at work. The launch slipped again.",2]

INSERT INTO entry_tags (entry_id, tag) VALUES (?, ?)
-- params: [2,"work"]

SELECT written_on, mood, body FROM entries WHERE strftime('%m-%d', written_on) = strftime('%m-%d', 'now') AND written_on < date('now') ORDER BY written_on DESC

SELECT strftime('%Y-%m', written_on) AS month, round(avg(mood), 1) AS mood, count(*) AS entries FROM entries GROUP BY month ORDER BY month DESC

SELECT t.tag, count(*) AS days FROM entry_tags t JOIN entries e ON e.id = t.entry_id WHERE e.mood <= ? GROUP BY t.tag ORDER BY days DESC
-- params: [2]