import * as wmill from "windmill-client"; type ParsedEmail = { headers?: Record; text_body?: string; html_body?: string; }; type EmailEvent = { kind: string; parsed_email?: ParsedEmail; raw_email?: unknown; email_extra_args?: Record; }; // Raw header value lookup (case-insensitive). Address headers (from/to) may be // parsed into structured objects, so we return the raw value, not a string. function rawHeader(headers: Record | undefined, name: string): unknown { if (!headers) return undefined; const want = name.toLowerCase(); for (const [k, v] of Object.entries(headers)) { if (k.toLowerCase() === want) return v; } return undefined; } function headerString(headers: Record | undefined, name: string): string { const v = rawHeader(headers, name); if (v == null) return ""; if (typeof v === "string") return v; if (Array.isArray(v)) return String(v[0] ?? ""); return ""; } // Parse a From header string ("Jane Doe " or "jane@x.com"). function parseFromString(raw: string): { email: string; name: string } { const m = raw.match(/"?([^"<]*?)"?\s*<([^>]+)>/); if (m) return { name: m[1].trim(), email: m[2].trim().toLowerCase() }; return { name: "", email: raw.trim().toLowerCase() }; } // Extract {email, name} from a From header that may be a string, an address // object ({address,name}), a mailparser object ({value:[{address,name}],text}), // or an array of those. Falls back to scanning the raw RFC822 message. function extractFrom(headerVal: unknown, rawEmail: unknown): { email: string; name: string } { const fromObj = (o: any): { email: string; name: string } | null => { if (!o || typeof o !== "object") return null; if (Array.isArray(o.value) && o.value[0]) return { email: String(o.value[0].address ?? "").toLowerCase(), name: String(o.value[0].name ?? "") }; if (o.address) return { email: String(o.address).toLowerCase(), name: String(o.name ?? "") }; if (typeof o.text === "string") return parseFromString(o.text); return null; }; if (typeof headerVal === "string" && headerVal.trim()) return parseFromString(headerVal); const candidate = Array.isArray(headerVal) ? headerVal[0] : headerVal; const fromHeader = fromObj(candidate); if (fromHeader?.email) return fromHeader; // Fallback: scan the raw message for the From: line. if (typeof rawEmail === "string") { const m = rawEmail.match(/^From:\s*(.+)$/im); if (m) return parseFromString(m[1]); } return { email: "", name: "" }; } function stripHtml(html: string): string { return html.replace(//gi, "").replace(/<[^>]+>/g, " ").replace(/\s+/g, " ").trim(); } // Remove the quoted reply history (everything from the first quote marker on) // so a ticket message keeps only the new text the customer actually wrote. function stripQuoted(text: string): string { const lines = text.split(/\r?\n/); const markers = [ /^>/, // quoted line /^On\b.+\bwrote:?\s*$/i, // "On wrote:" /^-{2,}\s*Original Message\s*-{2,}/i, // Outlook /^_{5,}\s*$/, // some clients /^From:\s.+/i, // forwarded/Outlook header block /^Le\b.+\ba écrit\s*:?\s*$/i, // French "Le … a écrit :" ]; let cut = lines.length; for (let i = 0; i < lines.length; i++) { if (markers.some((re) => re.test(lines[i].trim()))) { cut = i; break; } } const result = lines.slice(0, cut).join("\n").trim(); return result || text.trim(); } /** * Email-trigger preprocessor: pull the sender, subject, body, and (if the * reply came back to support+ticket=@…) the ticket id out of the raw email. */ export async function preprocessor(event: EmailEvent) { const pe = event.parsed_email ?? {}; const from = extractFrom(rawHeader(pe.headers, "from"), event.raw_email); const subject = headerString(pe.headers, "subject") || "(no subject)"; const rawBody = (pe.text_body && pe.text_body.trim()) ? pe.text_body.trim() : stripHtml(pe.html_body ?? ""); const body = stripQuoted(rawBody); return { from_email: from.email, from_name: from.name || null, subject, body: body || "(empty message)", ticket_id: event.email_extra_args?.ticket ?? null, message_id: headerString(pe.headers, "message-id") || null, }; } /** * Turn an inbound email into a ticket message. If a ticket id was carried back * on the reply address it appends to that thread (re-opening it); otherwise a * new ticket is created. */ export async function main( from_email: string, from_name: string | null, subject: string, body: string, ticket_id?: string | null, message_id?: string | null ): Promise<{ ticket_id: string; created: boolean }> { const sql = wmill.datatable(); let id = ticket_id ?? ""; let created = false; if (id) { const rows = await sql`SELECT id FROM helpdesk.tickets WHERE id = ${id}::uuid`.fetch(); if (!rows.length) id = ""; // referenced ticket gone → fall through to create } if (!id) { const cleanSubject = subject.replace(/^\s*(re|fwd?):\s*/i, "").trim() || "(no subject)"; const rows = await sql` INSERT INTO helpdesk.tickets (subject, requester_email, requester_name) VALUES (${cleanSubject}, ${from_email}, ${from_name}) RETURNING id `.fetch(); id = (rows[0] as any).id; created = true; } await sql` INSERT INTO helpdesk.messages (ticket_id, direction, from_email, from_name, body, email_message_id) VALUES (${id}::uuid, 'inbound', ${from_email}, ${from_name}, ${body}, ${message_id ?? null}) `.fetch(); await sql` UPDATE helpdesk.tickets SET status = 'open', updated_at = now(), last_message_at = now() WHERE id = ${id}::uuid `.fetch(); return { ticket_id: id, created }; }

Inbound support email handler