How to build a habit tracker with ChatGPT

Set up a habit tracker for me in databasedmcp, in one go.

1. Create a database called habits, described as "Habit tracker: habits with weekly targets and one check-in per habit per day, logged by ChatGPT from what I tell it".
2. Create two tables:
   - habits: id, name (required), target_per_week (how many days a week I'm aiming for, 1 to 7, defaults to 7), started_on (date, defaults to today), archived_at (NULL until I drop the habit).
   - checkins: id, habit_id (references habits), done_on (date as YYYY-MM-DD, defaults to today), note. One check-in per habit per day: make habit_id and done_on unique together.
3. Write the database a guide that says what each table is for, to archive habits instead of deleting them so old check-ins keep their habit, to match habits by name when I mention them, to use INSERT OR IGNORE so logging the same day twice does nothing, and the queries for "how's my week going against my targets" (weeks start Monday) and "my longest streak for a habit" (group consecutive dates by the date minus the row number).

From then on: when I tell you I did something, check it in for today unless I say another day, with anything I said about it as the note. When I drop a habit, set archived_at. Show me the tables when you're finished.

CREATE TABLE habits (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  target_per_week INTEGER NOT NULL DEFAULT 7 CHECK (target_per_week BETWEEN 1 AND 7),
  started_on TEXT NOT NULL DEFAULT (date('now')),
  archived_at TEXT
);

CREATE TABLE checkins (
  id INTEGER PRIMARY KEY,
  habit_id INTEGER NOT NULL REFERENCES habits(id),
  done_on TEXT NOT NULL DEFAULT (date('now')),
  note TEXT,
  UNIQUE (habit_id, done_on)
);

# habits: daily check-ins against weekly targets

- **habits**: one row per habit. `target_per_week` is how many days a week I'm aiming for. Set `archived_at` instead of deleting, so old check-ins keep their habit.
- **checkins**: one row per habit per day I did it. `done_on` is a date (YYYY-MM-DD), today unless I say otherwise. Use INSERT OR IGNORE: logging the same day twice is a no-op.

Match habits by name (LIKE) when I mention them.

This week against target (weeks start Monday): `SELECT h.name, h.target_per_week, count(c.id) AS done FROM habits h LEFT JOIN checkins c ON c.habit_id = h.id AND c.done_on >= date('now', 'weekday 0', '-6 days') WHERE h.archived_at IS NULL GROUP BY h.id ORDER BY h.id`

Longest streak for a habit: group consecutive `done_on` dates by `julianday(done_on) - row_number()`.

INSERT INTO habits (name, target_per_week) VALUES (?, ?), (?, ?), (?, ?)
-- params: ["Run",3,"Read",7,"Meditate",5]

INSERT OR IGNORE INTO checkins (habit_id, note) SELECT id, ? FROM habits WHERE name LIKE ? AND archived_at IS NULL
-- params: ["5k, felt slow","%run%"]

INSERT OR IGNORE INTO checkins (habit_id, done_on) SELECT id, date('now', '-1 day') FROM habits WHERE id IN (?, ?)
-- params: [2,3]

UPDATE habits SET archived_at = datetime('now') WHERE id = ?
-- params: [3]

SELECT h.name, h.target_per_week, count(c.id) AS done FROM habits h LEFT JOIN checkins c ON c.habit_id = h.id AND c.done_on >= date('now', 'weekday 0', '-6 days') WHERE h.archived_at IS NULL GROUP BY h.id ORDER BY h.id

WITH d AS (SELECT done_on, julianday(done_on) - row_number() OVER (ORDER BY done_on) AS grp FROM checkins WHERE habit_id = ?) SELECT min(done_on) AS from_day, max(done_on) AS to_day, count(*) AS days FROM d GROUP BY grp ORDER BY days DESC LIMIT 1
-- params: [2]

SELECT strftime('%Y-%m', c.done_on) AS month, h.name, count(*) AS days FROM checkins c JOIN habits h ON h.id = c.habit_id GROUP BY month, h.id ORDER BY month DESC, h.id