konnosDashboard
support-tickets.ts421 lines · main
1import { newId, NotFoundError, ValidationError } from '@briven/shared';
2import { and, asc, desc, eq, isNotNull, isNull, sql } from 'drizzle-orm';
3
4import { getDb } from '../db/client.js';
5import {
6 contactMessageReplies,
7 contactMessages,
8 ticketReplyAuthors,
9 ticketStatuses,
10 type ContactMessage,
11 type ContactMessageReply,
12 type TicketReplyAuthor,
13 type TicketStatus,
14 type TicketTopicCode,
15} from '../db/schema.js';
16
17const NOTES_CAP = 20_000;
18const REPLY_CAP = 8_000;
19const ASSIGNEE_CAP = 200;
20
21/**
22 * The four routing tags the support form serializes into `subject` as
23 * `#support #billing #technical #self-hosting`, mapped to their 3-letter
24 * topic codes. A submission becomes a ticket when ≥1 of these is present;
25 * the primary code is the FIRST one that appears in the subject string.
26 */
27export const TICKET_TAG_TO_CODE = {
28 support: 'SUP',
29 billing: 'BIL',
30 technical: 'TEC',
31 'self-hosting': 'SLF',
32} as const;
33export type RoutingTag = keyof typeof TICKET_TAG_TO_CODE;
34
35/**
36 * Pull the routing tags out of a serialized subject line, in the order
37 * they appear. Pure + DB-free — the unit-tested seam. Recognises `#tag`
38 * tokens; ignores any non-routing chips and de-dupes.
39 */
40export function parseRoutingTags(subject: string | null | undefined): RoutingTag[] {
41 if (!subject) return [];
42 const out: RoutingTag[] = [];
43 const seen = new Set<RoutingTag>();
44 const re = /#([a-z-]+)/gi;
45 let m: RegExpExecArray | null;
46 while ((m = re.exec(subject)) !== null) {
47 const tag = m[1]!.toLowerCase();
48 if (tag in TICKET_TAG_TO_CODE && !seen.has(tag as RoutingTag)) {
49 seen.add(tag as RoutingTag);
50 out.push(tag as RoutingTag);
51 }
52 }
53 return out;
54}
55
56/**
57 * Primary topic code for a subject, or null when no routing tag is present
58 * (→ it stays a plain contact message, no ticket). The code is taken from
59 * the FIRST routing tag in the subject.
60 */
61export function primaryTopicCode(subject: string | null | undefined): TicketTopicCode | null {
62 const tags = parseRoutingTags(subject);
63 return tags.length ? TICKET_TAG_TO_CODE[tags[0]!] : null;
64}
65
66/** UTC calendar-day key ('YYYY-MM-DD') the counter resets on. */
67export function ticketDayKey(now: Date): string {
68 const yyyy = now.getUTCFullYear();
69 const mm = String(now.getUTCMonth() + 1).padStart(2, '0');
70 const dd = String(now.getUTCDate()).padStart(2, '0');
71 return `${yyyy}-${mm}-${dd}`;
72}
73
74/**
75 * Format a ticket number from its parts — WITHOUT the leading '#'
76 * (that's added only at the API/render edge). Shape:
77 * `<CODE><YYMMDD>-<6-digit counter>` e.g. `SUP260629-000001`. Pure +
78 * DB-free — the unit-tested seam for the format. Uses UTC so the day
79 * stamp matches the counter's UTC day key.
80 */
81export function formatTicketNumber(code: string, now: Date, counter: number): string {
82 const yy = String(now.getUTCFullYear()).slice(-2);
83 const mm = String(now.getUTCMonth() + 1).padStart(2, '0');
84 const dd = String(now.getUTCDate()).padStart(2, '0');
85 const seq = String(counter).padStart(6, '0');
86 return `${code}${yy}${mm}${dd}-${seq}`;
87}
88
89/** Render a stored ticket number for API responses (adds the '#'). */
90export function renderTicketNumber(stored: string | null): string | null {
91 return stored ? `#${stored}` : null;
92}
93
94type Db = ReturnType<typeof getDb>;
95type Tx = Parameters<Parameters<Db['transaction']>[0]>[0];
96
97/**
98 * Atomically allocate the next ticket number for (topicCode, today) and
99 * return the formatted value (no leading '#'). Race-safe: the
100 * INSERT ... ON CONFLICT DO UPDATE bumps the per-day counter under the row
101 * lock Postgres takes on the conflicting PK, so two concurrent creations
102 * get distinct, gap-free numbers without any advisory lock. Pass the
103 * enclosing transaction (`exec`) so the counter and the ticket insert
104 * commit together — a number is never burned on a failed insert. `now` is
105 * passed in (never read at module load) so callers/tests control the clock.
106 */
107export async function generateTicketNumber(
108 topicCode: TicketTopicCode,
109 now: Date = new Date(),
110 exec: Db | Tx = getDb(),
111): Promise<string> {
112 const day = ticketDayKey(now);
113 const rows = (await exec.execute(sql`
114 INSERT INTO ticket_counters (topic_code, day, counter)
115 VALUES (${topicCode}, ${day}, 1)
116 ON CONFLICT (topic_code, day)
117 DO UPDATE SET counter = ticket_counters.counter + 1
118 RETURNING counter
119 `)) as unknown as Array<{ counter: number }>;
120 const counter = Number(rows[0]?.counter ?? 1);
121 return formatTicketNumber(topicCode, now, counter);
122}
123
124/* ─── admin reads / writes ───────────────────────────────────────── */
125
126export async function listTicketsForAdmin(
127 opts: { status?: TicketStatus; limit?: number } = {},
128): Promise<ContactMessage[]> {
129 const db = getDb();
130 const limit = Math.min(200, Math.max(1, opts.limit ?? 100));
131 const conds = [isNotNull(contactMessages.ticketNumber)];
132 if (opts.status) conds.push(eq(contactMessages.status, opts.status));
133 return db
134 .select()
135 .from(contactMessages)
136 .where(and(...conds))
137 .orderBy(desc(contactMessages.createdAt))
138 .limit(limit);
139}
140
141export async function getTicketByIdForAdmin(
142 id: string,
143): Promise<{ ticket: ContactMessage; replies: ContactMessageReply[] }> {
144 const db = getDb();
145 const rows = await db
146 .select()
147 .from(contactMessages)
148 .where(and(eq(contactMessages.id, id), isNotNull(contactMessages.ticketNumber)))
149 .limit(1);
150 const ticket = rows[0];
151 if (!ticket) throw new NotFoundError('ticket', id);
152 const replies = await db
153 .select()
154 .from(contactMessageReplies)
155 .where(eq(contactMessageReplies.messageId, id))
156 .orderBy(asc(contactMessageReplies.createdAt));
157 return { ticket, replies };
158}
159
160export interface UpdateTicketInput {
161 status?: string;
162 assignedTo?: string | null;
163 operatorNotes?: string | null;
164}
165
166function assertStatus(s: string): asserts s is TicketStatus {
167 if (!(ticketStatuses as readonly string[]).includes(s)) {
168 throw new ValidationError(`status must be one of: ${ticketStatuses.join(', ')}`);
169 }
170}
171
172export async function updateTicket(
173 id: string,
174 input: UpdateTicketInput,
175): Promise<ContactMessage> {
176 const patch: {
177 status?: TicketStatus;
178 assignedTo?: string | null;
179 operatorNotes?: string | null;
180 } = {};
181 if (input.status !== undefined) {
182 assertStatus(input.status);
183 patch.status = input.status;
184 }
185 if (input.assignedTo !== undefined) {
186 const v = (input.assignedTo ?? '').trim();
187 if (v.length > ASSIGNEE_CAP) {
188 throw new ValidationError(`assignedTo exceeds ${ASSIGNEE_CAP}-character cap`);
189 }
190 patch.assignedTo = v === '' ? null : v;
191 }
192 if (input.operatorNotes !== undefined) {
193 const v = (input.operatorNotes ?? '').trim();
194 if (v.length > NOTES_CAP) {
195 throw new ValidationError(`operatorNotes exceeds ${NOTES_CAP}-character cap`);
196 }
197 patch.operatorNotes = v === '' ? null : v;
198 }
199
200 const db = getDb();
201 // Scope the update to ticketed rows so a non-ticket contact id can never
202 // be promoted into the ticket workflow via this endpoint.
203 const [row] = await db
204 .update(contactMessages)
205 .set(patch)
206 .where(and(eq(contactMessages.id, id), isNotNull(contactMessages.ticketNumber)))
207 .returning();
208 if (!row) throw new NotFoundError('ticket', id);
209 return row;
210}
211
212/**
213 * Append an operator reply to a ticket thread and return the new reply row
214 * plus the parent ticket (so the caller has the sender's email to notify).
215 * Validates the ticket exists + is ticketed first.
216 */
217export async function addReply(
218 id: string,
219 author: TicketReplyAuthor,
220 body: string,
221): Promise<{ reply: ContactMessageReply; ticket: ContactMessage }> {
222 if (!(ticketReplyAuthors as readonly string[]).includes(author)) {
223 throw new ValidationError('invalid reply author');
224 }
225 const trimmed = body.trim();
226 if (!trimmed) throw new ValidationError('reply body is required');
227 if (trimmed.length > REPLY_CAP) {
228 throw new ValidationError(`reply body exceeds ${REPLY_CAP}-character cap`);
229 }
230 const db = getDb();
231 const rows = await db
232 .select()
233 .from(contactMessages)
234 .where(and(eq(contactMessages.id, id), isNotNull(contactMessages.ticketNumber)))
235 .limit(1);
236 const ticket = rows[0];
237 if (!ticket) throw new NotFoundError('ticket', id);
238 const [reply] = await db
239 .insert(contactMessageReplies)
240 .values({ id: newId('crp'), messageId: id, author, body: trimmed })
241 .returning();
242 if (!reply) throw new Error('insert returned no row');
243 return { reply, ticket };
244}
245
246/* ─── all-messages inbox (admin) — tagged OR plain ───────────────── */
247
248/** Filter for the all-messages inbox. 'all' = no ticket filter. */
249export type ContactMessageFilter = 'all' | 'plain' | 'tickets';
250
251/**
252 * List contact messages for the all-messages admin inbox. Unlike
253 * listTicketsForAdmin (which is hard-scoped to ticketed rows), this can
254 * surface EVERY submission so an operator can read + reply to plain
255 * (untagged) messages too. `filter` narrows: 'all' = no ticket filter,
256 * 'plain' = only non-ticketed rows (ticket_number IS NULL), 'tickets' =
257 * only ticketed rows. Newest first; limit capped at 200 (default 100).
258 */
259export async function listContactMessages(
260 opts: { filter?: ContactMessageFilter; limit?: number } = {},
261): Promise<ContactMessage[]> {
262 const db = getDb();
263 const limit = Math.min(200, Math.max(1, opts.limit ?? 100));
264 const filter = opts.filter ?? 'all';
265 const conds =
266 filter === 'plain'
267 ? [isNull(contactMessages.ticketNumber)]
268 : filter === 'tickets'
269 ? [isNotNull(contactMessages.ticketNumber)]
270 : [];
271 const q = db.select().from(contactMessages);
272 const filtered = conds.length ? q.where(and(...conds)) : q;
273 return filtered.orderBy(desc(contactMessages.createdAt)).limit(limit);
274}
275
276/**
277 * One contact message (by id) for the admin inbox, WITH its reply thread.
278 * Mirrors getTicketByIdForAdmin but WITHOUT the isNotNull(ticketNumber)
279 * scope so a plain (untagged) message is openable too.
280 */
281export async function getContactMessageForAdmin(
282 id: string,
283): Promise<{ message: ContactMessage; replies: ContactMessageReply[] }> {
284 const db = getDb();
285 const rows = await db
286 .select()
287 .from(contactMessages)
288 .where(eq(contactMessages.id, id))
289 .limit(1);
290 const message = rows[0];
291 if (!message) throw new NotFoundError('contact message', id);
292 const replies = await db
293 .select()
294 .from(contactMessageReplies)
295 .where(eq(contactMessageReplies.messageId, id))
296 .orderBy(asc(contactMessageReplies.createdAt));
297 return { message, replies };
298}
299
300/**
301 * Append an operator reply to ANY contact message thread — plain or
302 * ticketed. Same reply table + author validation as addReply, keyed by
303 * message_id, but the parent lookup is NOT scoped to ticketed rows.
304 * Returns the new reply plus the parent message (so the caller has the
305 * sender's email + ticketNumber to decide which email to send).
306 */
307export async function addContactReply(
308 id: string,
309 author: TicketReplyAuthor,
310 body: string,
311): Promise<{ reply: ContactMessageReply; message: ContactMessage }> {
312 if (!(ticketReplyAuthors as readonly string[]).includes(author)) {
313 throw new ValidationError('invalid reply author');
314 }
315 const trimmed = body.trim();
316 if (!trimmed) throw new ValidationError('reply body is required');
317 if (trimmed.length > REPLY_CAP) {
318 throw new ValidationError(`reply body exceeds ${REPLY_CAP}-character cap`);
319 }
320 const db = getDb();
321 const rows = await db
322 .select()
323 .from(contactMessages)
324 .where(eq(contactMessages.id, id))
325 .limit(1);
326 const message = rows[0];
327 if (!message) throw new NotFoundError('contact message', id);
328 const [reply] = await db
329 .insert(contactMessageReplies)
330 .values({ id: newId('crp'), messageId: id, author, body: trimmed })
331 .returning();
332 if (!reply) throw new Error('insert returned no row');
333 return { reply, message };
334}
335
336/**
337 * Patch ANY contact message (plain or ticketed) — the all-messages-inbox
338 * counterpart to updateTicket, minus the isNotNull(ticketNumber) scope so
339 * a plain message's status / notes are editable too. Only status +
340 * operatorNotes are exposed here (the inbox has no assignee lane).
341 */
342export async function updateContactMessage(
343 id: string,
344 input: { status?: string; operatorNotes?: string | null },
345): Promise<ContactMessage> {
346 const patch: { status?: TicketStatus; operatorNotes?: string | null } = {};
347 if (input.status !== undefined) {
348 assertStatus(input.status);
349 patch.status = input.status;
350 }
351 if (input.operatorNotes !== undefined) {
352 const v = (input.operatorNotes ?? '').trim();
353 if (v.length > NOTES_CAP) {
354 throw new ValidationError(`operatorNotes exceeds ${NOTES_CAP}-character cap`);
355 }
356 patch.operatorNotes = v === '' ? null : v;
357 }
358 const db = getDb();
359 const [row] = await db
360 .update(contactMessages)
361 .set(patch)
362 .where(eq(contactMessages.id, id))
363 .returning();
364 if (!row) throw new NotFoundError('contact message', id);
365 return row;
366}
367
368/* ─── user (dashboard) reads ─────────────────────────────────────── */
369
370export async function listTicketsForUserEmail(
371 email: string,
372 opts: { limit?: number } = {},
373): Promise<ContactMessage[]> {
374 const db = getDb();
375 const limit = Math.min(100, Math.max(1, opts.limit ?? 50));
376 // Match case-insensitively: contact_messages.email is stored as submitted
377 // (trimmed, not lowercased), while the session user's email is lowercased.
378 return db
379 .select()
380 .from(contactMessages)
381 .where(
382 and(
383 isNotNull(contactMessages.ticketNumber),
384 sql`lower(${contactMessages.email}) = ${email.toLowerCase()}`,
385 ),
386 )
387 .orderBy(desc(contactMessages.createdAt))
388 .limit(limit);
389}
390
391/**
392 * One ticket (by its human ticket number, with or without the leading '#'),
393 * scoped to the owner's email. Returns null when it doesn't exist OR belongs
394 * to someone else — same shape so the endpoint never leaks existence.
395 */
396export async function getTicketForUserByNumber(
397 email: string,
398 ticketNumber: string,
399): Promise<{ ticket: ContactMessage; replies: ContactMessageReply[] } | null> {
400 const stored = ticketNumber.trim().replace(/^#/, '');
401 if (!stored) return null;
402 const db = getDb();
403 const rows = await db
404 .select()
405 .from(contactMessages)
406 .where(
407 and(
408 eq(contactMessages.ticketNumber, stored),
409 sql`lower(${contactMessages.email}) = ${email.toLowerCase()}`,
410 ),
411 )
412 .limit(1);
413 const ticket = rows[0];
414 if (!ticket) return null;
415 const replies = await db
416 .select()
417 .from(contactMessageReplies)
418 .where(eq(contactMessageReplies.messageId, ticket.id))
419 .orderBy(asc(contactMessageReplies.createdAt));
420 return { ticket, replies };
421}