How to build a support ticket desk with Claude

Set up a support desk for me in databasedmcp, in one go.

1. Create a database called support, described as "Support desk: customers, tickets and the replies on each, logged by Claude from messages I paste".
2. Create three tables:
   - customers: id, name (required), email, company, created_at (defaults to now).
   - tickets: id, customer_id (references customers, required), subject (required), topic (one or two words, reused across tickets, like billing, shipping or login), priority (1 urgent to 3 whenever, defaults to 2), status (open, waiting or solved, defaults to open), opened_at (defaults to now), solved_at (NULL until it's solved).
   - messages: id, ticket_id (references tickets, required), from_me (1 for my replies, 0 for theirs, defaults to 0), body (required), at (defaults to now).
3. Write the database a guide that says what each table is for: open means it's my turn, waiting means it's theirs. It should say to look customers up by email or name before adding them, to summarise long emails in two or three sentences, and that when a customer writes about something they already have an open ticket for, the message goes on that ticket and it goes back to open. Include the query for my queue: open tickets with the customer's name, most urgent first, then oldest first.

From then on: when I paste a customer's message, log it as a ticket (adding the customer if they're new) or add it to their open one. When I send a reply, log it as a message from me. When I say we're waiting on them, set the ticket to waiting; when it's solved, set solved and solved_at. Show me the tables when you're finished.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  company TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  subject TEXT NOT NULL,
  topic TEXT,
  priority INTEGER NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 3),
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'waiting', 'solved')),
  opened_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  solved_at TEXT
);

CREATE TABLE messages (
  id INTEGER PRIMARY KEY,
  ticket_id INTEGER NOT NULL REFERENCES tickets(id),
  from_me INTEGER NOT NULL DEFAULT 0 CHECK (from_me IN (0, 1)),
  body TEXT NOT NULL,
  at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

# support: tickets from pasted messages

- **customers**: one per person who writes in. Look them up by email or name before adding.
- **tickets**: one per problem. status: open (my turn), waiting (their turn), solved. priority: 1 (urgent) to 3 (whenever). `topic` is one or two words, reused across tickets (billing, shipping, login). Set `solved_at` when it's solved.
- **messages**: what was said, in order. `from_me` is 1 for my replies, 0 for theirs. Summarise long emails in two or three sentences.

If a customer writes about something they already have an open ticket for, add a message to it and set it back to open.

My queue: `SELECT t.id, c.name, t.subject, t.priority, t.opened_at FROM tickets t JOIN customers c ON c.id = t.customer_id WHERE t.status = 'open' ORDER BY t.priority, t.opened_at`

INSERT INTO customers (name, email, company) VALUES (?, ?, ?) RETURNING id
-- params: ["Anna Berg","anna@fernwood-bakery.example","Fernwood Bakery"]

INSERT INTO tickets (customer_id, subject, topic, priority) VALUES (?, ?, ?, ?) RETURNING id
-- params: [1,"Checkout charges shipping twice","billing",1]

INSERT INTO messages (ticket_id, from_me, body) VALUES (?, ?, ?)
-- params: [1,1,"Found the cause: a discount rule added shipping a second time. Fixed now, and refunds for affected orders go out today."]

UPDATE tickets SET status = 'waiting' WHERE id = ?
-- params: [1]

UPDATE tickets SET status = 'solved', solved_at = datetime('now') WHERE id = ? AND status != 'solved'
-- params: [1]

SELECT t.id, c.name, t.subject, t.priority, t.opened_at FROM tickets t JOIN customers c ON c.id = t.customer_id WHERE t.status = 'open' ORDER BY t.priority, t.opened_at

SELECT topic, count(*) AS tickets, round(avg(julianday(solved_at) - julianday(opened_at)), 1) AS days_to_solve FROM tickets WHERE status = 'solved' GROUP BY topic ORDER BY tickets DESC

SELECT t.id, c.name, t.subject, max(m.at) AS last_message FROM tickets t JOIN customers c ON c.id = t.customer_id JOIN messages m ON m.ticket_id = t.id WHERE t.status = 'waiting' GROUP BY t.id HAVING last_message < datetime('now', '-5 days') ORDER BY last_message