How to build an invoice and client tracker with ChatGPT

Set up an invoice and client tracker for me in databasedmcp, in one go.

1. Create a database called invoices, described as "Invoice and client tracker: invoices I've sent and payments I've received, recorded by ChatGPT from what I tell it."
2. Create three tables:
   - clients: id, name (required), contact, terms_days (how many days they get to pay, defaults to 30), notes.
   - invoices: id, client_id (references clients, required), number (the number on the invoice, required and unique), description, amount_cents (required, in cents), currency (defaults to EUR), issued_on (date, defaults to today), due_on (date, required), status (sent, paid or void, defaults to sent).
   - payments: id, invoice_id (references invoices, required), amount_cents (required, in cents), paid_on (date, defaults to today), method.
3. Write the database a guide that says what each table is for, to look clients up by name before adding them, that amounts are in cents, that an invoice can have several payments and is marked paid when the payments cover it, and the queries for "what's outstanding, by due date" and "how much was received each month".

From then on: when I mention a new client, add them with their payment terms. When I say I sent an invoice, record it with a due date from the client's terms unless I give one. When I say I got paid, record the payment against the invoice, and mark the invoice paid once its payments cover the amount. Show me the tables when you're finished.

CREATE TABLE clients (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  contact TEXT,
  terms_days INTEGER NOT NULL DEFAULT 30,
  notes TEXT
);

CREATE TABLE invoices (
  id INTEGER PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES clients(id),
  number TEXT NOT NULL UNIQUE,
  description TEXT,
  amount_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'EUR',
  issued_on TEXT NOT NULL DEFAULT (date('now')),
  due_on TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'sent' CHECK (status IN ('sent', 'paid', 'void'))
);

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  invoice_id INTEGER NOT NULL REFERENCES invoices(id),
  amount_cents INTEGER NOT NULL,
  paid_on TEXT NOT NULL DEFAULT (date('now')),
  method TEXT
);

# invoices: clients, invoices, payments

- **clients**: one row per client. Look them up by name (LIKE) before adding. `terms_days` is how long they get to pay.
- **invoices**: one row per invoice I send. Amounts are in cents. status: sent, paid, void. `number` is the number on the invoice.
- **payments**: money received against an invoice, in cents. An invoice can have several. Mark it paid when the payments cover it.

Outstanding: `SELECT i.number, c.name, i.due_on, i.amount_cents - coalesce(sum(p.amount_cents), 0) AS owed_cents FROM invoices i JOIN clients c ON c.id = i.client_id LEFT JOIN payments p ON p.invoice_id = i.id WHERE i.status = 'sent' GROUP BY i.id ORDER BY i.due_on`

Received by month: `SELECT strftime('%Y-%m', paid_on) AS month, sum(amount_cents) / 100.0 AS received FROM payments GROUP BY month ORDER BY month DESC`

INSERT INTO clients (name, contact, terms_days) VALUES (?, ?, ?) RETURNING id
-- params: ["Halvorsen Design","Ingrid Halvorsen",14]

INSERT INTO invoices (client_id, number, description, amount_cents, due_on) VALUES (?, ?, ?, ?, date('now', '+14 days'))
-- params: [1,"2026-031","Brand refresh, phase 1",240000]

INSERT INTO invoices (client_id, number, description, amount_cents, issued_on, due_on) VALUES (?, ?, ?, ?, date('now', '-28 days'), date('now', '-14 days'))
-- params: [1,"2026-027","Website audit",90000]

INSERT INTO payments (invoice_id, amount_cents, method) VALUES (?, ?, ?)
-- params: [1,240000,"bank transfer"]

UPDATE invoices SET status = 'paid' WHERE id = ? AND amount_cents <= (SELECT coalesce(sum(amount_cents), 0) FROM payments WHERE invoice_id = ?)
-- params: [1,1]

SELECT i.number, c.name, i.due_on, (i.amount_cents - coalesce(sum(p.amount_cents), 0)) / 100.0 AS owed, max(0, CAST(julianday('now') - julianday(i.due_on) AS INTEGER)) AS days_late FROM invoices i JOIN clients c ON c.id = i.client_id LEFT JOIN payments p ON p.invoice_id = i.id WHERE i.status = 'sent' GROUP BY i.id ORDER BY i.due_on

SELECT strftime('%Y-%m', paid_on) AS month, sum(amount_cents) / 100.0 AS received FROM payments WHERE paid_on >= date('now', 'start of year') GROUP BY month ORDER BY month

WITH settled AS (SELECT i.client_id, julianday(max(p.paid_on)) - julianday(i.issued_on) AS days FROM invoices i JOIN payments p ON p.invoice_id = i.id WHERE i.status = 'paid' GROUP BY i.id) SELECT c.name, count(*) AS invoices, round(avg(s.days), 1) AS avg_days_to_pay FROM settled s JOIN clients c ON c.id = s.client_id GROUP BY c.id ORDER BY avg_days_to_pay DESC