How to build a bug tracker with Claude Code

claude mcp add --transport http databased https://databasedmcp.com/mcp

Set up a bug tracker for this repo in databasedmcp, in one go.

1. Create a database called bugs, described as "Bug tracker for this repo: bugs filed from the terminal, closed with the fixing commit".
2. Create two tables:
   - bugs: id, title (required), severity (1 for broken for everyone to 4 for cosmetic, defaults to 3), status (open, doing, fixed or wontfix, defaults to open), path (the file where the bug lives), repro (how to reproduce it), fixed_in (the commit that fixed it), opened_at (defaults to now), closed_at (NULL until it's closed).
   - comments: id, bug_id (references bugs), body (required: what was tried, what was found), at (defaults to now).
3. Write the database a guide that says what each table is for, that comments are appended and never edited, to file a bug with a reproduction whenever I say something is broken and close it with the short commit hash, and the query for "what's open, worst first" to run before starting work.

From then on: when I say something is broken, file a bug with the file and a reproduction. When you start on one, set it to doing and add what you find as comments. When you commit a fix, set it to fixed, with the short commit hash in fixed_in and closed_at set. Show me the tables when you're finished.

CREATE TABLE bugs (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  severity INTEGER NOT NULL DEFAULT 3 CHECK (severity BETWEEN 1 AND 4),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'doing', 'fixed', 'wontfix')),
  path TEXT,
  repro TEXT,
  fixed_in TEXT,
  opened_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at TEXT
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  bug_id INTEGER NOT NULL REFERENCES bugs(id),
  body TEXT NOT NULL,
  at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

# bugs: this repo's bug tracker

- **bugs**: one per bug. severity: 1 (broken for everyone) to 4 (cosmetic). status: open, doing, fixed, wontfix. `path` is the file where it lives, `fixed_in` the commit that fixed it.
- **comments**: what was tried, what was found. Append; never edit.

Before starting work, check what's open: `SELECT id, severity, title, path FROM bugs WHERE status IN ('open', 'doing') ORDER BY severity, id`
File a bug with a reproduction whenever the human says something is broken. Close it with the short commit hash.

INSERT INTO bugs (title, severity, path, repro) VALUES (?, ?, ?, ?) RETURNING id
-- params: ["CSV export breaks on names with commas",3,"src/export/csv.ts","Export a customer named \"Smith, Jane\": the row gets an extra column."]

UPDATE bugs SET status = 'doing' WHERE id = ? AND status = 'open'
-- params: [1]

INSERT INTO comments (bug_id, body) VALUES (?, ?)
-- params: [1,"Fields are joined with commas without quoting. Use the csv-stringify helper the import side already uses."]

UPDATE bugs SET status = 'fixed', fixed_in = ?, closed_at = datetime('now') WHERE id = ?
-- params: ["a41c9e2",1]

SELECT id, severity, title, path FROM bugs WHERE status IN ('open', 'doing') ORDER BY severity, id

SELECT path, count(*) AS bugs FROM bugs WHERE path IS NOT NULL GROUP BY path ORDER BY bugs DESC LIMIT 10

SELECT severity, count(*) AS fixed, round(avg(julianday(closed_at) - julianday(opened_at)), 1) AS days_open FROM bugs WHERE status = 'fixed' GROUP BY severity ORDER BY severity