Alternative to Todoist: a to-do list with Claude

Set up a to-do list for me in databasedmcp, in one go.

1. Create a database called todos, described as "My to-do list: projects and tasks, kept by Claude from what I tell it".
2. Create two tables:
   - projects: id, name (required, unique), archived_at (NULL while the project is active).
   - tasks: id, project_id (references projects, optional), title (required), notes, due (date, optional), priority (1 is most urgent, 4 is someday; defaults to 4), repeat (daily, weekly, monthly or NULL), done_at (NULL until I say it's done), created_at (defaults to now).
3. Write the database a guide that says what each table is for, to look a project up by name before adding one, that finishing a repeating task means marking it done and adding the next one with the due date moved on, and the queries for "what's due today or overdue" and "what did I finish this week".

From then on: when I mention something I need to do, add a task, with a due date and priority if I give them. When I say I did something, find the open task and set done_at. When I ask what's next, show open tasks by due date and priority. Show me the tables when you're finished.

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

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id),
  title TEXT NOT NULL,
  notes TEXT,
  due TEXT,
  priority INTEGER NOT NULL DEFAULT 4 CHECK (priority BETWEEN 1 AND 4),
  repeat TEXT CHECK (repeat IN ('daily', 'weekly', 'monthly')),
  done_at TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

# todos: my to-do list

- **projects**: groups of tasks (Home, Work, Garden). Look one up by name before adding. `archived_at` is set when a project is finished.
- **tasks**: one row per thing to do. priority: 1 (urgent) to 4 (someday). `due` is a date or NULL. `repeat` is daily, weekly, monthly or NULL. `done_at` stays NULL until I say it's done.

Finishing a repeating task: set its done_at, then insert a copy with `due` moved on (+1 day, +7 days or +1 month).

Due today or overdue: `SELECT t.id, t.title, p.name AS project, t.due, t.priority FROM tasks t LEFT JOIN projects p ON p.id = t.project_id WHERE t.done_at IS NULL AND t.due <= date('now') ORDER BY t.due, t.priority`

Done this week: `SELECT title, done_at FROM tasks WHERE done_at >= date('now', 'weekday 1', '-7 days') ORDER BY done_at`

INSERT INTO projects (name) VALUES (?) RETURNING id
-- params: ["Home"]

INSERT INTO tasks (project_id, title, due, priority) VALUES (?, ?, date('now', 'weekday 5'), ?)
-- params: [1,"Renew the car insurance",1]

INSERT INTO tasks (project_id, title, due, priority, repeat) VALUES (?, ?, date('now', 'weekday 2'), ?, ?)
-- params: [1,"Take out the recycling",3,"weekly"]

UPDATE tasks SET done_at = datetime('now') WHERE project_id = ? AND done_at IS NULL AND title LIKE ?
-- params: [1,"%recycling%"]

INSERT INTO tasks (project_id, title, due, priority, repeat) SELECT project_id, title, date(due, '+7 days'), priority, repeat FROM tasks WHERE id = ?
-- params: [2]

SELECT t.id, t.title, p.name AS project, t.due, t.priority FROM tasks t LEFT JOIN projects p ON p.id = t.project_id WHERE t.done_at IS NULL AND t.due <= date('now') ORDER BY t.due, t.priority

SELECT title, due, priority FROM tasks WHERE done_at IS NULL AND due BETWEEN date('now') AND date('now', '+7 days') ORDER BY priority, due

SELECT coalesce(p.name, 'No project') AS project, count(*) AS done FROM tasks t LEFT JOIN projects p ON p.id = t.project_id WHERE t.done_at >= date('now', 'start of month') GROUP BY project ORDER BY done DESC