diff --git a/README.md b/README.md index 10709e2..05218b0 100644 --- a/README.md +++ b/README.md @@ -92,7 +92,7 @@ Staff (`Authorization: Bearer $HAILBOX_STAFF_TOKEN`): | Method and path | Body | | --- | --- | | `GET /tickets?status=&assignee=&queue=&tag=&requester=&limit=&before=` | `assignee=none` for unassigned; `next` is the next `before` | -| `GET /tickets/search?q=` | subject and messages, any case | +| `GET /tickets/search?q=` | full text over subjects and messages; words are stemmed, so `rate limits` finds `rate-limited` | | `GET /tickets/:id` | full thread with notes | | `PATCH /tickets/:id` | `status, priority, assignee, queue, tags` (`""` clears assignee or queue) | | `POST /tickets/:id/messages` | `body, status?, author?` (status defaults to `pending`) | @@ -139,7 +139,7 @@ Tools: | Tool | Does | | --- | --- | | `list_tickets` | filter by status, assignee, queue, tag, requester; paged | -| `search_tickets` | text in subjects and messages | +| `search_tickets` | full-text search over subjects and messages | | `get_ticket` | full thread with notes and SLA | | `create_ticket` | open a ticket for a requester | | `reply_to_ticket` | reply the requester sees | @@ -218,7 +218,6 @@ VS Code (`.vscode/mcp.json`): - Web UI for people who want to watch the queue. - Pausing the SLA clock while a ticket is pending; business hours. -- Full-text search (SQLite FTS5) instead of substring search. - Attachments by reference (URL + digest). - Private-network blocking for webhook targets; a delivery replay endpoint. - Per-agent staff tokens and an audit log of who changed what. diff --git a/migrations/002_search_fts.sql b/migrations/002_search_fts.sql new file mode 100644 index 0000000..6e0ed76 --- /dev/null +++ b/migrations/002_search_fts.sql @@ -0,0 +1,41 @@ +-- Full-text search index for tickets (the old search was a substring scan of subjects and messages). +-- One row per subject and one row per message, so a body can be replaced on its own. +-- The porter tokenizer stems words, so "rate limits" finds a ticket that says "rate-limited". +CREATE VIRTUAL TABLE ticket_search USING fts5 ( + ticket_id UNINDEXED, + msg_id UNINDEXED, -- 0 for the subject row, messages.id for a body row + kind UNINDEXED, -- 'subject' | 'body' + text, + tokenize = 'porter unicode61' +); + +-- Index what is already stored: this migration runs against live helpdesks. +INSERT INTO ticket_search (ticket_id, msg_id, kind, text) SELECT id, 0, 'subject', subject FROM tickets; +INSERT INTO ticket_search (ticket_id, msg_id, kind, text) SELECT ticket_id, id, 'body', body FROM messages; + +CREATE TRIGGER ticket_search_subject_ai AFTER INSERT ON tickets BEGIN + INSERT INTO ticket_search (ticket_id, msg_id, kind, text) VALUES (new.id, 0, 'subject', new.subject); +END; + +CREATE TRIGGER ticket_search_subject_au AFTER UPDATE OF subject ON tickets BEGIN + DELETE FROM ticket_search WHERE ticket_id = new.id AND kind = 'subject'; + INSERT INTO ticket_search (ticket_id, msg_id, kind, text) VALUES (new.id, 0, 'subject', new.subject); +END; + +-- Deleting a ticket cascades to its messages, and those rows are dropped here too. +CREATE TRIGGER ticket_search_ticket_ad AFTER DELETE ON tickets BEGIN + DELETE FROM ticket_search WHERE ticket_id = old.id; +END; + +CREATE TRIGGER ticket_search_body_ai AFTER INSERT ON messages BEGIN + INSERT INTO ticket_search (ticket_id, msg_id, kind, text) VALUES (new.ticket_id, new.id, 'body', new.body); +END; + +CREATE TRIGGER ticket_search_body_au AFTER UPDATE OF body ON messages BEGIN + DELETE FROM ticket_search WHERE ticket_id = new.ticket_id AND msg_id = new.id AND kind = 'body'; + INSERT INTO ticket_search (ticket_id, msg_id, kind, text) VALUES (new.ticket_id, new.id, 'body', new.body); +END; + +CREATE TRIGGER ticket_search_body_ad AFTER DELETE ON messages BEGIN + DELETE FROM ticket_search WHERE ticket_id = old.ticket_id AND msg_id = old.id AND kind = 'body'; +END; diff --git a/src/store.js b/src/store.js index e7b2b16..1bb256b 100644 --- a/src/store.js +++ b/src/store.js @@ -12,6 +12,15 @@ const RETRY_MS = [60_000, 5 * 60_000, 30 * 60_000, 2 * 3600_000]; const VERIFIED = Symbol("verified requester"); const sha = (s) => createHash("sha256").update(s).digest("hex"); +// A query is turned into FTS5 "stemmed prefix" terms joined by AND: every word must appear, and a word +// matches anything it is a prefix of. Splitting on everything that is not a letter, digit or underscore +// means quotes, dashes, parentheses and FTS5 operators in the input are separators, not syntax. +function ftsMatch(q) { + const words = String(q).toLowerCase().match(/[\p{L}\p{N}_]+/gu); + if (!words) return null; + return words.map((w) => `"${w}"*`).join(" AND "); +} + export class Hailbox { constructor({ db, path, now = () => Date.now(), slaHours = DEFAULT_SLA_HOURS, fetch = globalThis.fetch, allowHttpWebhooks = false } = {}) { this.db = db || openDb(path); @@ -100,15 +109,19 @@ export class Hailbox { return { tickets, next: more ? tickets[tickets.length - 1].id : null }; } - // Case-insensitive substring search over subjects and messages (internal notes included: staff only). + // Full-text search over subjects and messages (internal notes included: staff only), backed by the + // FTS5 index in migrations/002_search_fts.sql. Words are stemmed and matched as prefixes, so + // "rate limits" finds a ticket that says "rate-limited", and "needle" finds "needles". searchTickets(query, { limit = 20 } = {}) { - const q = text(query, "query", { max: 200 }).toLowerCase(); + const q = text(query, "query", { max: 200 }); const n = Math.min(Math.max(Number(limit) || 20, 1), 100); + const match = ftsMatch(q); + if (!match) return []; const rows = this.db - .prepare(`SELECT t.* FROM tickets t WHERE instr(lower(t.subject), ?) > 0 - OR EXISTS (SELECT 1 FROM messages m WHERE m.ticket_id = t.id AND instr(lower(m.body), ?) > 0) + .prepare(`SELECT t.* FROM tickets t + WHERE t.id IN (SELECT ticket_id FROM ticket_search WHERE ticket_search MATCH ?) ORDER BY t.updated_at DESC, t.id DESC LIMIT ?`) - .all(q, q, n); + .all(match, n); return rows.map((r) => this.#out(r)); } diff --git a/test/store.test.js b/test/store.test.js index 1380dea..f57b9e4 100644 --- a/test/store.test.js +++ b/test/store.test.js @@ -89,6 +89,36 @@ test("list filters, paging and search", () => { assert.throws(() => box.listTickets({ status: "lost" }), /status/); }); +test("search is stemmed and indexed, so rate limits finds rate-limited", () => { + const { box } = makeBox(); + box.createTicket({ requester: "agent_a", subject: "Latency on /v1/pay", body: "Calls have been rate-limited since 10:00 UTC." }); + box.createTicket({ requester: "agent_b", subject: "Invoice wrong", body: "plain", tags: ["billing"] }); + assert.deepEqual(box.searchTickets("rate limits").map((t) => t.id), [1]); + assert.deepEqual(box.searchTickets("RATE-LIMITED").map((t) => t.id), [1]); + assert.deepEqual(box.searchTickets("reopening").map((t) => t.id), [], "words still have to appear"); + assert.deepEqual(box.searchTickets("invoices").map((t) => t.id), [2], "a word matches what it prefixes"); + assert.deepEqual(box.searchTickets("???").map((t) => t.id), [], "punctuation matches nothing"); + assert.throws(() => box.searchTickets(""), /query/); + + // Notes are indexed like any other message, and a new message is picked up by its trigger. + box.addNote(1, { author: "bob", body: "internal needle" }); + assert.deepEqual(box.searchTickets("needle").map((t) => t.id), [1]); + box.reply(1, { author: "sup", body: "The upstream was throttling; not rate-limited any more." }); + assert.deepEqual(box.searchTickets("throttling").map((t) => t.id), [1]); + + // Editing a subject or a body in place keeps the index honest. + box.db.prepare("UPDATE tickets SET subject = ? WHERE id = ?").run("Refund required", 2); + assert.deepEqual(box.searchTickets("invoices").map((t) => t.id), []); + assert.deepEqual(box.searchTickets("refunds").map((t) => t.id), [2]); + const firstMessage = box.getTicket(1).messages[0].id; + box.db.prepare("UPDATE messages SET body = ? WHERE id = ?").run("nothing to see here", firstMessage); + assert.deepEqual(box.searchTickets("latency").map((t) => t.id), [1], "the subject row is untouched"); + + // Dropping a ticket drops its rows: no dangling hits. + box.db.prepare("DELETE FROM tickets WHERE id = ?").run(1); + assert.deepEqual(box.searchTickets("latency").map((t) => t.id), []); +}); + test("macros render variables, set status and add tags", () => { const { box } = makeBox(); box.createTicket({ requester: "agent_a", subject: "Key leaked", body: "b" }); @@ -178,7 +208,7 @@ test("a database file keeps its data and migrations run once", async () => { a.db.close(); const b = new Hailbox({ path }); assert.equal(b.getTicket(1).subject, "persisted"); - assert.equal(b.db.prepare("SELECT COUNT(*) n FROM schema_migrations").get().n, 1); + assert.equal(b.db.prepare("SELECT COUNT(*) n FROM schema_migrations").get().n, 2); b.db.close(); rmSync(dir, { recursive: true, force: true }); });